DAYNAME(${1 ~ 7})
함수를 사용하면, 월요일이 1로 시작하여 이름을 붙여줄 수 있다.
다른말로 집계함수라고도 한다.
COUNT(${컬럼 명 | 숫자})
, MIN()
, MAX()
, SUM()
, AVG()
, MEAN()
NULL
값을 제외하고 처리한다. 단, COUNT(*)
는 제외SUM()
, AVG()
는 숫자에만 적용이 되지만, MAX()
, MIN()
, COUNT()
는 숫자가 아니더라도 적용이 된다.SELECT COUNT(*) FROM emp; -- 전체 행 개수 출력
-- 위 쿼리문은 데이터가 많거나, 시스템에 조금이라도 부하가 걸리면 제대로된 값을 리턴하지 않을 수 있다.
SELECT COUNT(-1) FROM emp; -- 이게 훨씬 더 좋은 기법이고, 위 쿼리문과 같은 결과를 리턴한다.
SELECT COUNT(empno) FROM emp; -- 또는 단순히 PK의 컬럼명을 넣어줘도 된다.
SELECT ROUND(AVG(sal)), SUM(sal), MIN(sal), MAX(sal) FROM emp; -- 평균, 합, 최소, 최대 출력
SELECT AVG(comm) FROM emp;
-- NULL값은 계수하지않기 때문에, comm이 NULL인 사원을 세지 않는다. 따라서, 평균이 제대로 구해지지 않는다.
SELECT ROUND(AVG(IFNULL(comm, 0))) FROM emp; -- 정상작동
SELECT COUNT(DISTINCT(deptno)) FROM emp; -- 중복 제거한 갯수 출력
SELECT deptno, ROUND(AVG(sal)) FROM emp; -- check
그룹함수에 명시되지 않은 컬럼이
SELECT
절에 나열되어서는 안 된다.
사용하려면 반드시GROUP BY
를 사용하여 연관을 지어줘야 한다.
SELECT deptno, ROUND(AVG(sal)) FROM emp GROUP BY deptno; -- 각 부서별 평균 급여 정상작동
SELECT deptno AS d, ROUND(AVG(sal)) AS avgsal FROM emp GROUP BY d; -- 정상작동
-- GROUP BY는 SELECT보다 먼저 실행되기 떄문에, 에러가 발생해야 정상이지만, 작동이 된다.
-- MySQL만 작동이 되며, GROUP BY에서의 AS 사용을 허가하기 때문에 여기서만 된다.
SELECT LEFT(hiredate, 4), COUNT(empno) FROM emp GROUP BY(LEFT(hiredate, 4)); -- 입사년도별 사원 수
SELECT YEAR(hiredate), COUNT(empno) FROM emp GROUP BY(YEAR(hiredate)); -- 동일
SELECT YEAR(hiredate), COUNT(empno) FROM emp GROUP BY 1; -- 동일
SELECT DATE_FORMAT(hiredate, '%Y'), COUNT(empno) FROM emp GROUP BY DATE_FORMAT(hiredate, '%Y'); -- 동일
SELECT deptno, AVG(sal) FROM emp WHERE AVG(sal) >= 2000 GROUP BY deptno; -- check
WHERE
절에서는 그룹함수를 쓸 수 없다. 따라서, check(마지막) 쿼리가 에러가 난다.
WHERE
과HAVING
을 구분하자.HAVING
은GROUP BY
가 완료된 값을 출력 직전에 다시 추출할 때 사용한다.
SELECT deptno, AVG(sal) FROM emp GROUP BY deptno HAVING AVG(sal) >= 2000; -- 정상작동
SELECT deptno, AVG(sal) FROM emp HAVING AVG(sal) >= 2000 GROUP BY deptno; -- MySQL은 에러, Oracle에서는 작동한다.
GROUP BY
의 확장 개념이다. ROLLUP
은 그룹 별 중간 합계나, 그룹 별 총합이 필요한 경우에 사용되는 기능이다.
ROLLUP
의 인수는 계층구조를 이루기 때문에 인수 순서가 바뀌면 수행 결과도 달라진다.
SELECT deptno, COUNT(empno), SUM(sal) FROM emp GROUP BY deptno WITH ROLLUP;
-- 각 부서 번호, 각 부서별 인원, 각 부서별 급여 합이 나오고, 마지막 행에 deptno를 제외한 다른 column의 합이 나온다.
SELECT deptno, job, SUM(sal) FROM emp GROUP BY deptno, job WITH ROLLUP;
-- 각 부서 번호의 직무별 임금 합이지만, deptno = 10, 20, 30의 급여 합, 전체 급여 합이 각각 부서번호 마지막 행 다음에 나온다.
Subquery란, 하나의 SQL문 안에 포함되어 있는 또 다른 SQL문을 말한다.
Subquery는 ?에 해당하는 부분을 먼저 검색하는데 사용된다. 일반적으로 Subquery가 먼저 실행되고 그 결과를 사용하여 Main query에 대한 질의 조건이 완성된다.
Subquery에서 ORDER BY
를 사용해도 되지만, 의미가 없다. WHERE
, HAVING
, FROM
절에서 각각 Subquery가 사용될 수 있다.
SELECT sal FROM emp WHERE ename='CLERK'; -- ? 부분, 결과: 2450
SELECT ename, sal FROM emp WHERE sal > 2450; -- 메인 부분
SELECT ename, sal FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename='CLERK'); -- 합친 결과
이 예제는 단일행 서브쿼리, 즉, 결과가 1개 행으로 표시되는 서브쿼리 예제이다. 다중행 서브쿼리도 존재하며, 이럴 경우 다른 처리가 좀 더 필요하다.
또, 단일행 서브쿼리긴 하지만, 1 row, 1 column인 경우가 존재한다. 이럴 때에는, Scalar subquery라고 표현한다.
Subquery의 결과가 1개가 나오면, 즉, 단일행 서브쿼리라면, 비교 연산자도 단일행 비교연산자(
=
,>=
등)을 사용해야 한다.
-- job별 가장 작은 평균급여 검색
SELECT job, AVG(sal) FROM emp GROUP BY job
HAVING AVG(sal) = (SELECT MIN(AVG(sal)) FROM emp GROUP BY job); -- 작동X, MySQL은 그룹함수 중첩이 안된다.
SELECT job, MIN(t.avgsal) FROM
(SELECT job, AVG(sal) AS avgsal FROM emp GROUP BY job) AS t; -- 정상작동
SELECT job, AVG(sal) AS asal FROM emp GROUP BY job ORDER BY asal LIMIT 1; -- 너무 복잡하게 생각하지 말자. 이것도 똑같다.
ANY
: 메인쿼리의 비교조건이 서브쿼리의 검색결과와 하나 이상만 일치하면 참ALL
: 메인쿼리의 비교조건이 서브쿼리의 검색결과와 모든 값이 일치하면 참IN
: 여러 개 중에서 같은 값을 찾음-- 다중행 연산자 예시
-- 각 그룹별 최소 급여를 받는 사람
SELECT empno, ename FROM emp WHERE sal IN (SELECT MIN(sal) FROM emp GROUP BY deptno);
-- 급여가 어떤 CLERK 보다도 작으면서 CLERK이 아닌 사원 검색
SELECT ename, sal, job FROM emp WHERE sal < ANY (SELECT sal FROM emp WHERE job = 'CLERK') AND job != 'CLERK';
-- 급여가 모든 CLERK 보다도 작으면서 CLERK이 아닌 사원
SELECT ename, sal, job FROM emp WHERE sal < ALL (SELECT sal FROM emp WHERE job = 'CLERK') AND job != 'CLERK';
-- 모든 30번 부서 사람들보다 급여가 높은 사람들
SELECT ename, sal, deptno FROM emp WHERE sal > ALL (SELECT sal FROM emp WHERE deptno = 30);
-- 부서번호가 30번인 사람들의 급여 중 가장 낮은 급여보다 높은 급여를 받는 사원 검색
-- 단일행 연산자
SELECT ename, sal FROM emp WHERE sal > (SELECT MIN(sal) FROM emp WHERE deptno = 30);
-- 다중행 연산자
SELECT ename, sal FROM emp WHERE sal > ANY (SELECT sal FROM emp WHERE deptno = 30);
-- 부하직원을 거느린 사원 검색
SELECT ename, empno FROM emp WHERE empno IN (SELECT mgr FROM emp);
SELECT e.ename, e.empno FROM emp AS e
WHERE e.empno IN (SELECT m.mgr FROM emp AS m);
-- 부하직원을 거느리지 않는 사원 검색
SELECT ename, empno FROM emp WHERE empno NOT IN (SELECT mgr FROM emp); -- 에러
NULL
인 사원이 있다. IN
은 OR
, AND
처럼 작동하기 때문에, 비교가 되지 않는다. 따라서 마지막 쿼리가 제대로 작동하지 않는다.-- 부하직원을 거느리지 않는 사원 검색
SELECT ename, empno FROM emp WHERE empno NOT IN (SELECT IFNULL(mgr, -1) FROM emp); - 정상작동
-- 부하직원을 거느린 사원을 검색, 상사이름과 상사의 사원번호가 검색되도록.
SELECT t1.ename, t2.ename AS 상사이름, t2.empno AS 상사사원번호 FROM emp AS t1,
(SELECT ename, empno FROM emp) AS t2 WHERE t1.mgr = t2.empno;
Primary key
를 가진 테이블이 부모, Foreign key
를 가진 테이블이 자식이다. OOP 개념과 달라서 상속 개념은 없지만, 아무튼 의존성은 갖고 있다.
하나 이상의 테이블에서 질의를 던지는 경우, 일반적인 경우 PK나 FK의 연관에 의해서 조인이 성립된다. PK, FK의 관계가 없어도 논리적인 값의 연관만으로 JOIN이 가능하다. Equi Join, Self Join, Outer Join 등이 있다.
JOIN
은 그냥 사용하면 카티시안 곱으로 작용한다. 두 행의 갯수의 곱만큼 전체 행의 갯수가 출력된다.
=
연산자를 사용)SELECT * FROM emp, dept WHERE emp.deptno = dept.deptno; -- 중복 없음
SELECT ename, sal, deptno, dname, loc FROM emp AS e, dept AS d
WHERE e.deptno = d.deptno; -- SELECT의 deptno 애매함 에러. e.deptno로 해야한다.
SELECT * FROM (SELECT empno, ename, mgr FROM emp) AS e, (SELECT empno, ename FROM emp) AS m WHERE e.mgr = m.empno;
SELECT e.empno, e.ename AS 사원이름, m.ename AS 상사이름 FROM emp AS e, emp AS m WHERE e.mgr = m.empno; -- 같은 결과
JOIN
할 경우, 조건에 맞지 않는 데이터도 표시하고 싶을 때 OUTER JOIN을 사용한다. LEFT OUTER JOIN
, RIGHT OUTER JOIN
, FULL OUTER JOIN
이며, 데이터 있는 쪽을 기준으로 한다.-- OUTER JOIN
SELECT e.ename, e.deptno, d.dname FROM emp AS e, dept AS d WHERE e.deptno = d.deptno;
-- RIGHT OUTER JOIN 사용
-- JOIN 수행 시 우측테이블이 기준이 되어서 결과를 생산한다.
SELECT e.ename, e.deptno, d.dname FROM emp AS e
RIGHT OUTER JOIN dept AS d ON e.deptno = d.deptno; -- 우측 테이블의 dname이 나온다.
-- LEFT OUTER JOIN 사용
-- JOIN 수행 시 좌측테이블이 기준이 되어서 결과를 생산한다.
SELECT e.ename, e.deptno, d.dname FROM emp AS e
LEFT OUTER JOIN dept AS d ON e.deptno = d.deptno; -- 좌측 테이블의 dname이 나온다.
SELECT CONCAT(e.ename, ' 의 매니저는 ', m.ename, ' 입니다.') AS Info FROM emp AS e
LEFT OUTER JOIN emp AS m ON e.mgr = m.empno;
-- KING은 상사가 없다. empno가 NULL인 사원은 없으므로, 조인조건을 만족하지 않는다.
FULL OUTER JOIN
이 없다. 대신 UNION
을 사용한다.SELECT sawonid FROM OUTERA UNION SELECT sawonid FROM OUTERB;