국비 TIL(Today I Learned) 20220526 mariaDB join, 서브쿼리

테이블의 조인 & 서브쿼리 등등

 

실무에서는 테이블이 300개 정도 된데

 

 

 

테이블 복사

 

오늘 배운 쿼리문

USE mydb;

-- cross join: 두 테이블간에 어떤 공통필드 없이 그냥 m by n의 조합을 만든다. 평상시 거의 안씀
--             가짜로 대량의 데이터 만들때나 쓰고 안씀

SELECT ename, dname, hiredate FROM emp, dept;



-- inner join 지정한 컬럼의 조건이 맞을때 join된다.
-- 두개의 테이블 양쪽에 있는 것만 join 된다.
SELECT ename, dname, hiredate FROM emp INNER JOIN dept ON emp.deptno=dept.deptno;
-- 보통은 테이블 명을 새로 준다. ailasing
SELECT ename, dname, hiredate FROM emp A INNER JOIN dept B ON A.deptno=B.deptno;


-- from절 쪽에 있는 테이블을 왼쪽
-- 부서를 할당받지 못한 직원은 join시 데이터 안나옴
-- 신설된 부서는 직원은 아직 배당받지 않은 부서는 안나옴
-- 문제점 그래서 outer 조인을 더 많이 사용.

INSERT INTO emp(empno, ename)VALUES(8000, '홍길동');
INSERT INTO emp(empno, ename)VALUES(8001, '임꺽정');
INSERT INTO emp(empno, ename)VALUES(8002, '장실산');

INSERT INTO dept(deptno, dname, loc)VALUES(50, '개발1부', '서울');
INSERT INTO dept(deptno, dname, loc)VALUES(60, '개발2부', '부산');

SELECT ename, dname, hiredate, A.deptno FROM emp A LEFT OUTER JOIN dept b ON a.deptno=b.deptno;
SELECT ename, dname, hiredate, A.deptno FROM emp A right OUTER JOIN dept b ON b.deptno=b.deptno;


USE classicmodels;

DESC customers; -- salesRepEmployeeNumber
DESC employees; -- employeeNumber

SELECT * FROM customers;
SELECT * FROM employees;

-- 고객이름 판 사람이름(inner join)
-- concat : 문자열 합치는 함수
SELECT customerName, concat(firstName, "", lastName) as salesName FROM customers a INNER JOIN employees b ON a.salesRepEmployeeNumber = b.employeeNumber;


-- 판매자 이름에 Martin 이라는 계약한 사람의 고객이름, 신용한도 출력하기 
SELECT CONCAT(A.contactFirstName, "", A.contactLastName) as customName, A.creditLimit FROM customers A LEFT OUTER JOIN employees B ON A.salesRepEmployeeNumber = B.employeeNumber
WHERE B.firstName = 'Martin' OR B.lastName = 'Martin';


-- 런던의 계약내용
SELECT A.officecode, city, A.firstName
FROM employees A
INNER JOIN offices B ON A.officeCode=B.officeCode
WHERE city = 'london';

SELECT A.contactFirstName, B.firstName, C.city
FROM customers A
INNER JOIN employees B ON A.salesRepEmployeeNumber = B.employeeNumber
INNER JOIN offices C ON B.officeCode = C.officeCode
WHERE C.city = 'london';



SELECT A.orderNumber, A.orderDate, A.status, A.customerNumber, B.productCode, B.quantityOrdered, B.priceEach
FROM orders A
LEFT OUTER JOIN orderdetails B ON A.orderNumber = B.orderNumber;

-- distinct 중복값 제거해서 결과값 보여줌
SELECT distinct STATUS FROM orders;

SELECT A.orderNumber, A.orderDate, A.status, A.customerNumber
		,C.customerName, CONCAT(C.contactLastName, ' ', C.contactFirstName) AS NAME
		,B.productCode, B.quantityOrdered, B.priceEach
FROM orders A
LEFT OUTER JOIN orderdetails B ON A.orderNumber = B.orderNumber
LEFT OUTER JOIN customers C ON A.customerNumber = C.customerNumbermydbmydb
WHERE STATUS = 'Shipped';

-- orders, orderdetails, products 
-- 주문번호, 주문상태, 상품코드, 제품명, 제품라인
SELECT B.orderNumber,  A.`status`, C.productCode, C.productName, C.productLine
FROM orders A
LEFT OUTER JOIN orderdetails B ON A.orderNumber = B.orderNumber
LEFT OUTER JOIN products C ON B.productCode = C.productCode;


-- self join : 같은 테이블을 가지고 join, 드문데 우리나라 많이 쓴데
USE mydb;

SELECT * FROM emp;

/*
mgr (내 상사의 사원번호)
내이름, 상사이름
emp A             emp B
    mgr           empno가 일치되면 상사이름이 된다.

*/

SELECT A.ename, B.ename AS mgrName
FROM emp A
LEFT OUTER JOIN emp B ON A.mgr =B.empno;


/* 서브쿼리
   서브쿠리는 쿼리가 두개 이상으로 이루어진다.
   주쿼리와 서리쿼리로 나뉜다. 서브쿼리가 먼저 실행되고 나서 다른 쿼리가 실행된다.
   서브쿼리 실행결과로 데이터가 없거나 하나만 나오는 경우를 단일행 서브쿼리,
   두개이상의 결과를 반환하는 경우 다중행 서브쿼리라 한다
   서브쿼리는 아무곳에서나 사용가능하다.
   select절에서 서브쿼리는 단일행만 가능하다. 
	from절에서 서브쿼리는 단일행이든 다중행이든 상관없다.
	where 절의 서브쿼리는 단일행과 다중행의 사용방법이 많이 다른데 실제 많이 사용하지는 않는다.
	보통 select절과 from절에서 많이 다룬다.
*/

-- 서브쿼리를 써서 사원명이랑 부서명 출력
SELECT ename, deptno
FROM emp;

SELECT dname
FROM dept
WHERE deptno= 20;

SELECT ename, (SELECT dname FROM dept WHERE dept.deptno=emp.deptno) dname
FROM emp;


-- join의 결과와 서브쿼리 결과가 동일하면 보통은 join연산이 빠르다. 가급적 join연산을 사용.

SELECT ename
,(SELECT dname FROM dept WHERE dept.deptno=emp.deptno) dname
,(SELECT loc FROM dept WHERE dept.deptno=emp.deptno) loc
FROM emp;


-- 위의 쿼리 join으로 하면
SELECT ename, dname, loc
FROM emp A
LEFT OUTER JOIN dept B ON A.deptno = B.deptno;



SELECT * FROM
(
	SELECT ename
	,(SELECT dname FROM dept WHERE dept.deptno=emp.deptno) dname
	,(SELECT loc FROM dept WHERE dept.deptno=emp.deptno) loc
	FROM emp
)A WHERE A.loc = 'CHICAGO';

-- 위의 쿼리 join으로 하면
SELECT ename, dname, loc
FROM emp A
LEFT OUTER JOIN dept B ON A.deptno = B.deptno
WHERE loc = 'CHICAGO';




-- where절에서 서브쿼리 사용할때
-- scott 하고 같은 부서에 있는데 사람중에서 연봉 젤 센 사람

SELECT deptno FROM emp WHERE ename='scott';

-- 현재 있는 값중에 젤 큰값을 가져온다.
SELECT MAX(sal) FROM emp WHERE deptno = 20;

SELECT ename
FROM emp
WHERE sal = (SELECT MAX(sal) FROM emp WHERE deptno = 20);



-- comm이 null인 데이터
SELECT * FROM emp WHERE comm IS NULL;
SELECT * FROM emp WHERE comm IS not NULL;


-- update 데이터 변경하기
UPDATE emp SET gender='F';

SELECT * FROM emp;

UPDATE emp SET gender='M' WHERE empno IN (7369, 7521, 7782, 7839,7566, 8000, 8001, 8002);

SELECT * FROM emp;

UPDATE emp SET deptno=30, mgr=7698, sal=3000, comm=800
WHERE empno=8000;

SELECT * FROM emp;


-- delete
DELETE FROM emp WHERE empno= 8000;

SELECT * FROM emp;



-- 테이블 복사 명령어 따로 없고 서브쿼리를 이용해서 테이블 만들기
-- 1. 구조복사 1=0는 where 조건절이 언제가 false라서 데이터 안도고 구조만 복사
-- primary key나 foreign key등은 안 온다.
CREATE TABLE emp2 AS SELECT * FROM emp WHERE 1=0;
SELECT * FROM emp2;
DESC emp2;

-- 2. 전부복사
CREATE TABLE emp3 AS SELECT * FROM emp;

-- 3. 특정필드만 특정 데이터만 복사
CREATE TABLE emp4 AS SELECT empno, ename, sal FROM emp
WHERE deptno IN (10, 30);
SELECT * FROM emp4;

-- primary key 주고나서
ALTER TABLE emp ADD CONSTRAINT PK_EMP PRIMARY KEY (empno);
SELECT * FROM emp;
DESC emp;

CREATE TABLE emp5 AS SELECT * FROM emp;
DESC emp5; -- primary key는 복사되지 않는다.


USE classicmodels;

-- LIMIT
-- 데이터 10개만 가져온다.
SELECT * FROM customers LIMIT 10;
-- 9번째 레코드부터 5개 가져오기
SELECT * FROM customers LIMIT 9,5;

-- 고객번호 desc 정렬을 해서 limit를 가져온다, paging 할때 많이 씀
SELECT * FROM customers ORDER BY customerNumber DESC
LIMIT 0, 10;

SELECT * FROM customers ORDER BY customerNumber DESC
LIMIT 10, 10;

SELECT * FROM customers ORDER BY customerNumber DESC
LIMIT 20, 10;



-- 테이블 건수 세기
SELECT COUNT(*) FROM customers;



USE mydb;

-- empno 필드에 null이 있으면 카운트에서 빠진다.
SELECT COUNT(empno) FROM emp;
SELECT COUNT(deptno) FROM emp;
SELECT COUNT(comm) FROM emp;

-- *로하면 제일 많은걸로 가지고옴
SELECT COUNT(*) FROM emp;



-- 그룹함수가 max, min, sum, avg, count 있음
SELECT MAX(sal), MIN(sal), SUM(sal), AVG(sal), COUNT(*) FROM emp;


-- 부서별로 인원이 몇명씩 있나 확인
SELECT deptno, COUNT(*)
FROM emp
GROUP BY deptno;

SELECT deptno, MAX(sal),MIN(sal)
FROM emp
GROUP BY deptno;


-- sal은 데이터 개수만큼 나오고 deptno는 그룹별로 나옴, GROUP BY 뒤에 있는 값이 SELECT 뒤에 나와줘야함
-- 밑에 쿼리는 원래 에러 나와야 하는데 오라클 이런데서
-- 마리아db는 뭔가 나옴, 원래는 밑에 밑에 쿼리 처럼 조인으로 해야함
SELECT sal, deptno, MAX(sal),MIN(sal)
FROM emp
GROUP BY deptno;


SELECT empno, ename, sal, maxsal, minsal
FROM emp A
LEFT OUTER JOIN(
	SELECT deptno, MAX(sal) maxsal ,MIN(sal) minsal
	FROM emp
	GROUP BY deptno
)B ON A.deptno = B.deptno;



USE classicmodels;

-- 주문 중에서 상태별로 각 몇건인지(orders)
SELECT status, COUNT(*)
FROM orders
GROUP BY STATUS
ORDER BY COUNT(*) DESC;


-- 각 지사에 판매직원이 몇명인지

/*SELECT city, COUNT(*)
FROM offices A
LEFT OUTER JOIN(
	SELECT officeCode
	FROM employees
	GROUP BY officeCode
)B ON A.officeCode = B.officeCode
*/

SELECT officeCode, COUNT(*)
FROM employees
GROUP BY officeCode;
-- 위에 먼저 작성해주고, 넣어줌

SELECT city, cnt
FROM (
	SELECT officeCode, COUNT(*) cnt
	FROM employees
	GROUP BY officeCode
)A
LEFT OUTER JOIN offices B ON A.officeCode= B.officecode;


-- 서브쿼리
SELECT officeCode, COUNT(*) cnt,
(SELECT city FROM offices B WHERE A.officecode = b.officecode)
city
FROM employees A
GROUP BY officeCode;



-- 각 직원별로 몇건의 계약이 있는지
SELECT firstname, COUNT(*)
FROM (
	SELECT A.contactFirstName, B.firstName
	FROM customers A 
	left outer join employees B ON A.salesRepEmployeeNumber = B.employeeNumber
)A
GROUP BY firstname
ORDER BY COUNT(*) DESC;

 

아 조인이랑 서브쿼리 하나도 모르겠어ㅠㅠㅠㅠ

조인을 좀 알겠다하니 서브쿼리가 뛰어나옴

 

 

 

 

 

sql 문제 풀수 있는 사이트

https://www.hackerrank.com/challenges/earnings-of-employees/problem?h_r=internal-search 

Top Earners | HackerRank

https://www.youtube.com/watch?v=r2xJ7A6e6o0 

- select에 쓴 as(alias)는 where절에는 못씀

 

 

풀어보자

https://programmers.co.kr/learn/challenges?tab=sql_practice_kit 

코딩테스트 연습