SQL_DAY3

이정찬·2023년 1월 17일
0

SQL

목록 보기
3/4

2일차 추가

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
  • MySQL은 Oracle에 비해 엄격하지 않다. check(마지막) 쿼리도 에러가 원래는 나야 한다. 실행해보면 20, 2073이 나오는데, 부서번호와 급여평균이 연관이 없다.

그룹함수에 명시되지 않은 컬럼이 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(마지막) 쿼리가 에러가 난다.

WHEREHAVING을 구분하자. HAVINGGROUP 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에서는 작동한다.

ROLLUP

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

Subquery란, 하나의 SQL문 안에 포함되어 있는 또 다른 SQL문을 말한다.
Subquery는 ?에 해당하는 부분을 먼저 검색하는데 사용된다. 일반적으로 Subquery가 먼저 실행되고 그 결과를 사용하여 Main query에 대한 질의 조건이 완성된다.
Subquery에서 ORDER BY를 사용해도 되지만, 의미가 없다. WHERE, HAVING, FROM절에서 각각 Subquery가 사용될 수 있다.

EX) CLERK의 급여(?)보다 많은 급여를 받는 사람을 조회하세요.

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); -- 에러
  • empno에 해당하는 mgr 번호가 NULL인 사원이 있다. INOR, 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;

JOIN

Primary key를 가진 테이블이 부모, Foreign key를 가진 테이블이 자식이다. OOP 개념과 달라서 상속 개념은 없지만, 아무튼 의존성은 갖고 있다.

하나 이상의 테이블에서 질의를 던지는 경우, 일반적인 경우 PK나 FK의 연관에 의해서 조인이 성립된다. PK, FK의 관계가 없어도 논리적인 값의 연관만으로 JOIN이 가능하다. Equi Join, Self Join, Outer Join 등이 있다.

JOIN은 그냥 사용하면 카티시안 곱으로 작용한다. 두 행의 갯수의 곱만큼 전체 행의 갯수가 출력된다.

  1. Equi join (=연산자를 사용)
    2개 이상의 테이블에서 공통적으로 존재하는 컬럼값을 연결한다.
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; -- 같은 결과
  1. Outer join
    A, B 테이블을 JOIN 할 경우, 조건에 맞지 않는 데이터도 표시하고 싶을 때 OUTER JOIN을 사용한다. LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN이며, 데이터 있는 쪽을 기준으로 한다.
    행이 조인조건을 만족하지 않으면, 행은 질의에 나타나지 않는다. 예를 들어, emp, dept 테이블에서 equijoin 조건에서 부서 OPERATIONS는 해당부서에 아무도 없기 때문에 나타나지 않는다.
-- 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인 사원은 없으므로, 조인조건을 만족하지 않는다.
  • MySQL은 FULL OUTER JOIN이 없다. 대신 UNION을 사용한다.
SELECT sawonid FROM OUTERA UNION SELECT sawonid FROM OUTERB;
profile
개발자를 꿈꾸는 사람

0개의 댓글