지구정복

[SQL] 12/01 | 서브쿼리(단일행, 복수행), 조인(CARTESIAN, INNER, OUTER, EQUIL, NON-EQUIL, SELF), DDL(CREATE, ALTER, DROP), DML(INSERT, UPDATE, DELETE), 연습문제 본문

데이터 엔지니어링 정복/SQL

[SQL] 12/01 | 서브쿼리(단일행, 복수행), 조인(CARTESIAN, INNER, OUTER, EQUIL, NON-EQUIL, SELF), DDL(CREATE, ALTER, DROP), DML(INSERT, UPDATE, DELETE), 연습문제

eeaarrtthh 2020. 12. 1. 18:25
728x90
반응형

서브쿼리

두 개의 질의가 결합

최고 급여를 받은 사원에 대한 사원정보 출력

 

단일행 서브쿼리

단일행 서브쿼리는 비교연산자(=, >, >=, <=, <)만 사용가능하며

서브쿼리의 출력결과는 하나의 컬럼에 하나의 행만 출력해야한다.

 

1. 최고급여

SELECT MAX(SAL) 
FROM EMP;

+----------+
| MAX(SAL) |
+----------+
|  5000.00 |
+----------+
1 row in set (0.005 sec)

 

2.최고 급여와 같은 사원정보

SELECT EMPNO, ENAME 
FROM EMP 
WHERE SAL = MAX(SAL);

 

이 둘의 쿼리를 결합한다.

SELECT EMPNO, ENAME
FROM EMP
WHERE SAL = (
	SELECT MAX(SAL)
	FROM EMP);
    
+-------+-------+
| EMPNO | ENAME |
+-------+-------+
|  7839 | KING  |
+-------+-------+
1 row in set (0.008 sec)

 

SCOTT의 급여보다 이상인 사원번호, 사원명, 급여를 출력하라

SELECT EMPNO, ENAME, SAL
FROM EMP
WHERE SAL >= (
	SELECT SAL
	FROM EMP
	WHERE ENAME = 'SCOTT');
    
+-------+-------+---------+
| EMPNO | ENAME | SAL     |
+-------+-------+---------+
|  7788 | SCOTT | 3000.00 |
|  7839 | KING  | 5000.00 |
|  7902 | FORD  | 3000.00 |
+-------+-------+---------+
3 rows in set (0.001 sec)

 

20번 부서의 최고 급여를 받은 사원에 대한 사원번호, 사원명, 부서번호, 급여 출력하라.

SELECT EMPNO, ENAME, DEPTNO, SAL
FROM EMP
WHERE SAL = (
	SELECT MAX(SAL)
	FROM EMP
	WHERE DEPTNO = 20);
    
+-------+-------+--------+---------+
| EMPNO | ENAME | DEPTNO | SAL     |
+-------+-------+--------+---------+
|  7788 | SCOTT |     20 | 3000.00 |
|  7902 | FORD  |     20 | 3000.00 |
+-------+-------+--------+---------+
2 rows in set (0.000 sec)

 

복수행 서브쿼리

복수행 서브쿼리는 하나의 컬럼의 여러 행을 출력할 수 있다.

< ALL (서브쿼리), >ALL (서브쿼리), <ANY (서브쿼리), >ANY (서브쿼리) 등이 있다.
ALL은 서브쿼리의 결과중에서 최대값보다 큰 모든 행 또는 최소값보다 작은 모든 행을 의미

ANY는 서브쿼리의 결과중에서 최대값보다 작은 모든 행 또는 최소값보다 큰 모든 행을 의미

 

직책이 MANAGER인 사원의 최소 급여보다 큰 사원에 대한 사원정보 출력하라.

SELECT EMPNO, ENAME, JOB, SAL
FROM EMP
WHERE SAL < ALL (
	SELECT MIN(SAL)
	FROM EMP
	WHERE JOB = 'MANAGER');
    
+-------+--------+----------+---------+
| EMPNO | ENAME  | JOB      | SAL     |
+-------+--------+----------+---------+
|  7369 | SMITH  | CLERK    | 1600.00 |
|  7499 | ALLEN  | SALESMAN | 1600.00 |
|  7521 | WARD   | SALESMAN | 1250.00 |
|  7844 | TURNER | SALESMAN | 1500.00 |
|  7876 | ADAMS  | CLERK    | 1100.00 |
|  7900 | JAMES  | CLERK    |  950.00 |
|  7934 | MILLER | CLERK    | 1300.00 |
+-------+--------+----------+---------+
7 rows in set (0.002 sec)

 

부서별 최대급여를 받는 사원들의 사번, 이름, 부서코드, 급여를 출력하라.

SELECT EMPNO, ENAME, DEPTNO, SAL
FROM EMP
WHERE SAL IN (
	SELECT MAX(SAL)
	FROM EMP
	GROUP BY DEPTNO)
ORDER BY 3;

+-------+-------+--------+---------+
| EMPNO | ENAME | DEPTNO | SAL     |
+-------+-------+--------+---------+
|  7839 | KING  |     10 | 5000.00 |
|  7902 | FORD  |     20 | 3000.00 |
|  7788 | SCOTT |     20 | 3000.00 |
|  7698 | BLAKE |     30 | 2850.00 |
+-------+-------+--------+---------+
4 rows in set (0.001 sec)

 

조인(JOIN)

연관관계(Relation)를 가지는 두 개의 테이블을 결합시켜서 결과를 출력하는 것

이때 행*행을 카테시안 프로덕트(=행렬곱 =프로덕트곱)

카테시안 프로덕트는 CROSS JOIN 을 사용한다.

 

SELECT * 
FROM EMP CROSS JOIN DEPT;

+-------+--------+-----------+------+------------+---------+--------+--------+--------+------------+----------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM   | DEPTNO | DEPTNO | DNAME      | LOC      |
+-------+--------+-----------+------+------------+---------+--------+--------+--------+------------+----------+
|  7369 | SMITH  | CLERK     | 7902 | 2010-12-17 | 1600.00 | 300.00 |     30 |     10 | ACCOUNTING | NEW YORK |
|  7369 | SMITH  | CLERK     | 7902 | 2010-12-17 | 1600.00 | 300.00 |     30 |     20 | RESEARCH   | DALLAS   |
|  7369 | SMITH  | CLERK     | 7902 | 2010-12-17 | 1600.00 | 300.00 |     30 |     30 | SALES      | CHICAGO  |
|  7369 | SMITH  | CLERK     | 7902 | 2010-12-17 | 1600.00 | 300.00 |     30 |     40 | OPERATIONS | BOSTON   |
|  7499 | ALLEN  | SALESMAN  | 7698 | 2011-02-20 | 1600.00 | 300.00 |     30 |     10 | ACCOUNTING | NEW YORK |
|  7499 | ALLEN  | SALESMAN  | 7698 | 2011-02-20 | 1600.00 | 300.00 |     30 |     20 | RESEARCH   | DALLAS   |
|  7499 | ALLEN  | SALESMAN  | 7698 | 2011-02-20 | 1600.00 | 300.00 |     30 |     30 | SALES      | CHICAGO  |
|  7499 | ALLEN  | SALESMAN  | 7698 | 2011-02-20 | 1600.00 | 300.00 |     30 |     40 | OPERATIONS | BOSTON   |
|  7521 | WARD   | SALESMAN  | 7698 | 2011-02-22 | 1250.00 | 500.00 |     30 |     10 | ACCOUNTING | NEW YORK |
|  7521 | WARD   | SALESMAN  | 7698 | 2011-02-22 | 1250.00 | 500.00 |     30 |     20 | RESEARCH   | DALLAS   |
|  7521 | WARD   | SALESMAN  | 7698 | 2011-02-22 | 1250.00 | 500.00 |     30 |     30 | SALES      | CHICAGO  |
|  7521 | WARD   | SALESMAN  | 7698 | 2011-02-22 | 1250.00 | 500.00 |     30 |     40 | OPERATIONS | BOSTON   |
|  7566 | JONES  | MANAGER   | 7839 | 2011-04-02 | 2975.00 |   NULL |     20 |     10 | ACCOUNTING | NEW YORK |
|  7566 | JONES  | MANAGER   | 7839 | 2011-04-02 | 2975.00 |   NULL |     20 |     20 | RESEARCH   | DALLAS   |
|  7566 | JONES  | MANAGER   | 7839 | 2011-04-02 | 2975.00 |   NULL |     20 |     30 | SALES      | CHICAGO  |
|  7566 | JONES  | MANAGER   | 7839 | 2011-04-02 | 2975.00 |   NULL |     20 |     40 | OPERATIONS | BOSTON   |
|  7698 | BLAKE  | MANAGER   | 7839 | 2011-05-01 | 2850.00 |   NULL |     30 |     10 | ACCOUNTING | NEW YORK |
|  7698 | BLAKE  | MANAGER   | 7839 | 2011-05-01 | 2850.00 |   NULL |     30 |     20 | RESEARCH   | DALLAS   |
|  7698 | BLAKE  | MANAGER   | 7839 | 2011-05-01 | 2850.00 |   NULL |     30 |     30 | SALES      | CHICAGO  |
|  7698 | BLAKE  | MANAGER   | 7839 | 2011-05-01 | 2850.00 |   NULL |     30 |     40 | OPERATIONS | BOSTON   |
|  7782 | CLARK  | MANAGER   | 7839 | 2011-06-09 | 2450.00 |   NULL |     30 |     10 | ACCOUNTING | NEW YORK |
|  7782 | CLARK  | MANAGER   | 7839 | 2011-06-09 | 2450.00 |   NULL |     30 |     20 | RESEARCH   | DALLAS   |
|  7782 | CLARK  | MANAGER   | 7839 | 2011-06-09 | 2450.00 |   NULL |     30 |     30 | SALES      | CHICAGO  |
|  7782 | CLARK  | MANAGER   | 7839 | 2011-06-09 | 2450.00 |   NULL |     30 |     40 | OPERATIONS | BOSTON   |
|  7788 | SCOTT  | ANALYST   | 7566 | 2017-07-13 | 3000.00 |   NULL |     20 |     10 | ACCOUNTING | NEW YORK |
|  7788 | SCOTT  | ANALYST   | 7566 | 2017-07-13 | 3000.00 |   NULL |     20 |     20 | RESEARCH   | DALLAS   |
|  7788 | SCOTT  | ANALYST   | 7566 | 2017-07-13 | 3000.00 |   NULL |     20 |     30 | SALES      | CHICAGO  |
|  7788 | SCOTT  | ANALYST   | 7566 | 2017-07-13 | 3000.00 |   NULL |     20 |     40 | OPERATIONS | BOSTON   |
|  7839 | KING   | PRESIDENT | NULL | 2011-11-17 | 5000.00 |   NULL |     10 |     10 | ACCOUNTING | NEW YORK |
|  7839 | KING   | PRESIDENT | NULL | 2011-11-17 | 5000.00 |   NULL |     10 |     20 | RESEARCH   | DALLAS   |
|  7839 | KING   | PRESIDENT | NULL | 2011-11-17 | 5000.00 |   NULL |     10 |     30 | SALES      | CHICAGO  |
|  7839 | KING   | PRESIDENT | NULL | 2011-11-17 | 5000.00 |   NULL |     10 |     40 | OPERATIONS | BOSTON   |
|  7844 | TURNER | SALESMAN  | 7698 | 2011-09-08 | 1500.00 |   0.00 |     30 |     10 | ACCOUNTING | NEW YORK |
|  7844 | TURNER | SALESMAN  | 7698 | 2011-09-08 | 1500.00 |   0.00 |     30 |     20 | RESEARCH   | DALLAS   |
|  7844 | TURNER | SALESMAN  | 7698 | 2011-09-08 | 1500.00 |   0.00 |     30 |     30 | SALES      | CHICAGO  |
|  7844 | TURNER | SALESMAN  | 7698 | 2011-09-08 | 1500.00 |   0.00 |     30 |     40 | OPERATIONS | BOSTON   |
|  7876 | ADAMS  | CLERK     | 7788 | 2017-07-13 | 1100.00 |   NULL |     20 |     10 | ACCOUNTING | NEW YORK |
|  7876 | ADAMS  | CLERK     | 7788 | 2017-07-13 | 1100.00 |   NULL |     20 |     20 | RESEARCH   | DALLAS   |
|  7876 | ADAMS  | CLERK     | 7788 | 2017-07-13 | 1100.00 |   NULL |     20 |     30 | SALES      | CHICAGO  |
|  7876 | ADAMS  | CLERK     | 7788 | 2017-07-13 | 1100.00 |   NULL |     20 |     40 | OPERATIONS | BOSTON   |
|  7900 | JAMES  | CLERK     | 7698 | 2011-12-03 |  950.00 |   NULL |     30 |     10 | ACCOUNTING | NEW YORK |
|  7900 | JAMES  | CLERK     | 7698 | 2011-12-03 |  950.00 |   NULL |     30 |     20 | RESEARCH   | DALLAS   |
|  7900 | JAMES  | CLERK     | 7698 | 2011-12-03 |  950.00 |   NULL |     30 |     30 | SALES      | CHICAGO  |
|  7900 | JAMES  | CLERK     | 7698 | 2011-12-03 |  950.00 |   NULL |     30 |     40 | OPERATIONS | BOSTON   |
|  7902 | FORD   | ANALYST   | 7566 | 2011-12-03 | 3000.00 |   NULL |     20 |     10 | ACCOUNTING | NEW YORK |
|  7902 | FORD   | ANALYST   | 7566 | 2011-12-03 | 3000.00 |   NULL |     20 |     20 | RESEARCH   | DALLAS   |
|  7902 | FORD   | ANALYST   | 7566 | 2011-12-03 | 3000.00 |   NULL |     20 |     30 | SALES      | CHICAGO  |
|  7902 | FORD   | ANALYST   | 7566 | 2011-12-03 | 3000.00 |   NULL |     20 |     40 | OPERATIONS | BOSTON   |
|  7934 | MILLER | CLERK     | 7782 | 2012-01-23 | 1300.00 |   NULL |     10 |     10 | ACCOUNTING | NEW YORK |
|  7934 | MILLER | CLERK     | 7782 | 2012-01-23 | 1300.00 |   NULL |     10 |     20 | RESEARCH   | DALLAS   |
|  7934 | MILLER | CLERK     | 7782 | 2012-01-23 | 1300.00 |   NULL |     10 |     30 | SALES      | CHICAGO  |
|  7934 | MILLER | CLERK     | 7782 | 2012-01-23 | 1300.00 |   NULL |     10 |     40 | OPERATIONS | BOSTON   |
+-------+--------+-----------+------+------------+---------+--------+--------+--------+------------+----------+

 또 하나의 방법은 INNER JOIN이라고 쓴다.

SELECT * 
FROM EMP INNER JOIN DEPT;

 

EQUIL JOIN

연관관계를 가지는 두 개의 테이블에서 같은 컬럼이 있을 경우 두 테이블을 같은 컬럼을 기준으로 합쳐주는 것

아래는 DEPTNO컬럼을 기준으로 묶어주었다.

SELECT * 
FROM EMP CROSS JOIN DEPT
WHERE (EMP.DEPTNO = DEPT.DEPTNO);

+-------+--------+-----------+------+------------+---------+--------+--------+--------+------------+----------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM   | DEPTNO | DEPTNO | DNAME      | LOC      |
+-------+--------+-----------+------+------------+---------+--------+--------+--------+------------+----------+
|  7369 | SMITH  | CLERK     | 7902 | 2010-12-17 | 1600.00 | 300.00 |     30 |     30 | SALES      | CHICAGO  |
|  7499 | ALLEN  | SALESMAN  | 7698 | 2011-02-20 | 1600.00 | 300.00 |     30 |     30 | SALES      | CHICAGO  |
|  7521 | WARD   | SALESMAN  | 7698 | 2011-02-22 | 1250.00 | 500.00 |     30 |     30 | SALES      | CHICAGO  |
|  7566 | JONES  | MANAGER   | 7839 | 2011-04-02 | 2975.00 |   NULL |     20 |     20 | RESEARCH   | DALLAS   |
|  7698 | BLAKE  | MANAGER   | 7839 | 2011-05-01 | 2850.00 |   NULL |     30 |     30 | SALES      | CHICAGO  |
|  7782 | CLARK  | MANAGER   | 7839 | 2011-06-09 | 2450.00 |   NULL |     30 |     30 | SALES      | CHICAGO  |
|  7788 | SCOTT  | ANALYST   | 7566 | 2017-07-13 | 3000.00 |   NULL |     20 |     20 | RESEARCH   | DALLAS   |
|  7839 | KING   | PRESIDENT | NULL | 2011-11-17 | 5000.00 |   NULL |     10 |     10 | ACCOUNTING | NEW YORK |
|  7844 | TURNER | SALESMAN  | 7698 | 2011-09-08 | 1500.00 |   0.00 |     30 |     30 | SALES      | CHICAGO  |
|  7876 | ADAMS  | CLERK     | 7788 | 2017-07-13 | 1100.00 |   NULL |     20 |     20 | RESEARCH   | DALLAS   |
|  7900 | JAMES  | CLERK     | 7698 | 2011-12-03 |  950.00 |   NULL |     30 |     30 | SALES      | CHICAGO  |
|  7902 | FORD   | ANALYST   | 7566 | 2011-12-03 | 3000.00 |   NULL |     20 |     20 | RESEARCH   | DALLAS   |
|  7934 | MILLER | CLERK     | 7782 | 2012-01-23 | 1300.00 |   NULL |     10 |     10 | ACCOUNTING | NEW YORK |
+-------+--------+-----------+------+------------+---------+--------+--------+--------+------------+----------+
13 rows in set (0.003 sec)

INNER JOIN 또는 JOIN으로 사용해보자.

똑같은 결과를 얻을 수 있다.

SELECT * 
FROM EMP INNER JOIN DEPT
WHERE (EMP.DEPTNO = DEPT.DEPTNO);

SELECT * 
FROM EMP JOIN DEPT
WHERE (EMP.DEPTNO = DEPT.DEPTNO);

같은 컬럼명이있으면 USING을 사용해도 된다.

SELECT * 
FROM EMP INNER JOIN DEPT USING(DEPTNO);

SELECT * 
FROM EMP JOIN DEPT USING(DEPTNO);

SELECT * 
FROM EMP CROSS JOIN DEPT USING(DEPTNO);

 

직책이 CLERK인 사원의 사원번호, 사원명, 직책, 부서번호, 부서이름, 부서위치를 출력하라.

----USING 사용
SELECT EMPNO, ENAME, JOB, DEPTNO, DNAME, LOC
FROM EMP INNER JOIN DEPT USING(DEPTNO)
WHERE JOB = 'CLERK';

SELECT EMPNO, ENAME, JOB, DEPTNO, DNAME, LOC
FROM EMP CROSS JOIN DEPT USING(DEPTNO)
WHERE JOB = 'CLERK';

SELECT EMPNO, ENAME, JOB, DEPTNO, DNAME, LOC
FROM EMP CROSS JOIN DEPT USING(DEPTNO)
WHERE JOB = 'CLERK';

----WHERE절 사용
SELECT EMPNO, ENAME, JOB, EMP.DEPTNO, DNAME, LOC
FROM EMP INNER JOIN DEPT 
WHERE JOB = 'CLERK' AND (EMP.DEPTNO = DEPT.DEPTNO);

SELECT EMPNO, ENAME, JOB, EMP.DEPTNO, DNAME, LOC
FROM EMP CROSS JOIN DEPT
WHERE JOB = 'CLERK' AND (EMP.DEPTNO = DEPT.DEPTNO);

SELECT EMPNO, ENAME, JOB, EMP.DEPTNO, DNAME, LOC
FROM EMP JOIN DEPT
WHERE JOB = 'CLERK' AND (EMP.DEPTNO = DEPT.DEPTNO);

+-------+--------+-------+--------+------------+----------+
| EMPNO | ENAME  | JOB   | DEPTNO | DNAME      | LOC      |
+-------+--------+-------+--------+------------+----------+
|  7369 | SMITH  | CLERK |     30 | SALES      | CHICAGO  |
|  7876 | ADAMS  | CLERK |     20 | RESEARCH   | DALLAS   |
|  7900 | JAMES  | CLERK |     30 | SALES      | CHICAGO  |
|  7934 | MILLER | CLERK |     10 | ACCOUNTING | NEW YORK |
+-------+--------+-------+--------+------------+----------+
4 rows in set (0.000 sec)

WHERE절을 사용할 때는 SELECT 출력컬럼에 정확히 어떤테이블의 DEPTNO인지를 정확히 명시해주어야 한다.

EMP.DEPTNO ( O ) / DEPTNO ( X 에러 )

이때 WHERE절을 사용하면 너무 길어지므로 테이블 약자를 사용한다.

SELECT EMPNO, ENAME, JOB, E.DEPTNO, DNAME, LOC
FROM EMP E INNER JOIN DEPT D
WHERE JOB = 'CLERK' AND (E.DEPTNO = D.DEPTNO);

 

NON EQUIL JOIN

조인을 수행하는 기준 컬럼이 = 이 아닌 범위로 수행할 때를 의미한다.

사원의 급여를 기준으로 호봉수를 알고 싶을 때 아래와 같이 쿼리를 작성한다.

SELECT EMPNO, ENAME, SAL, GRADE
FROM EMP E INNER JOIN SALGRADE S
WHERE E.SAL >= S.LOSAL AND E.SAL <= S.HISAL;

SELECT EMPNO, ENAME, SAL, GRADE
FROM EMP E INNER JOIN SALGRADE S
ON E.SAL >= S.LOSAL AND E.SAL <= S.HISAL;


+-------+--------+---------+-------+
| EMPNO | ENAME  | SAL     | GRADE |
+-------+--------+---------+-------+
|  7369 | SMITH  | 1600.00 |     3 |
|  7499 | ALLEN  | 1600.00 |     3 |
|  7521 | WARD   | 1250.00 |     2 |
|  7566 | JONES  | 2975.00 |     4 |
|  7698 | BLAKE  | 2850.00 |     4 |
|  7782 | CLARK  | 2450.00 |     4 |
|  7788 | SCOTT  | 3000.00 |     4 |
|  7839 | KING   | 5000.00 |     5 |
|  7844 | TURNER | 1500.00 |     3 |
|  7876 | ADAMS  | 1100.00 |     1 |
|  7900 | JAMES  |  950.00 |     1 |
|  7902 | FORD   | 3000.00 |     4 |
|  7934 | MILLER | 1300.00 |     2 |
+-------+--------+---------+-------+
13 rows in set (0.001 sec)

보통 조인을 할 때에는 WHERE보다 ON을 많이 쓴다.

 

이번에는 20번 부서의 사원들의 급여에 대해 GRADE를 출력해보자.

SELECT EMPNO, ENAME, SAL, GRADE
FROM EMP E INNER JOIN SALGRADE S
ON (E.SAL >= S.LOSAL AND E.SAL <= S.HISAL)
WHERE DEPTNO = 20;


+-------+-------+---------+-------+
| EMPNO | ENAME | SAL     | GRADE |
+-------+-------+---------+-------+
|  7876 | ADAMS | 1100.00 |     1 |
|  7566 | JONES | 2975.00 |     4 |
|  7788 | SCOTT | 3000.00 |     4 |
|  7902 | FORD  | 3000.00 |     4 |
+-------+-------+---------+-------+
4 rows in set (0.001 sec)

 

이번에는 BETWEEN 구문으로 똑같은 결과를 출력해보자.

SELECT EMPNO, ENAME, SAL, GRADE
FROM EMP E INNER JOIN SALGRADE S
ON (E.SAL BETWEEN S.LOSAL AND S.HISAL)
WHERE DEPTNO = 20;

+-------+-------+---------+-------+
| EMPNO | ENAME | SAL     | GRADE |
+-------+-------+---------+-------+
|  7876 | ADAMS | 1100.00 |     1 |
|  7566 | JONES | 2975.00 |     4 |
|  7788 | SCOTT | 3000.00 |     4 |
|  7902 | FORD  | 3000.00 |     4 |
+-------+-------+---------+-------+
4 rows in set (0.002 sec)

 

이번에는 NON-EQUIL JOIN과 EQUIL조인을 동시에 사용해보자.

사원번호, 사원이름, 급여, 호봉, 부서이름, 부서위치를 출력해보자.

SELECT EMPNO, ENAME, SAL, GRADE, DNAME, LOC
FROM EMP E INNER JOIN DEPT D ON (E.DEPTNO = D.DEPTNO)
    INNER JOIN SALGRADE S ON (E.SAL BETWEEN S.LOSAL AND S.HISAL);
    
    
+-------+--------+---------+-------+------------+----------+
| EMPNO | ENAME  | SAL     | GRADE | DNAME      | LOC      |
+-------+--------+---------+-------+------------+----------+
|  7369 | SMITH  | 1600.00 |     3 | SALES      | CHICAGO  |
|  7499 | ALLEN  | 1600.00 |     3 | SALES      | CHICAGO  |
|  7521 | WARD   | 1250.00 |     2 | SALES      | CHICAGO  |
|  7566 | JONES  | 2975.00 |     4 | RESEARCH   | DALLAS   |
|  7698 | BLAKE  | 2850.00 |     4 | SALES      | CHICAGO  |
|  7782 | CLARK  | 2450.00 |     4 | SALES      | CHICAGO  |
|  7788 | SCOTT  | 3000.00 |     4 | RESEARCH   | DALLAS   |
|  7839 | KING   | 5000.00 |     5 | ACCOUNTING | NEW YORK |
|  7844 | TURNER | 1500.00 |     3 | SALES      | CHICAGO  |
|  7876 | ADAMS  | 1100.00 |     1 | RESEARCH   | DALLAS   |
|  7900 | JAMES  |  950.00 |     1 | SALES      | CHICAGO  |
|  7902 | FORD   | 3000.00 |     4 | RESEARCH   | DALLAS   |
|  7934 | MILLER | 1300.00 |     2 | ACCOUNTING | NEW YORK |
+-------+--------+---------+-------+------------+----------+
13 rows in set (0.001 sec)

 

OUTER JOIN

두 개의 테이블을 결합할 때 어느 하나의 테이블의 모든 행을 다 출력하는 것을 의미한다.

왼쪽 테이블의 행을 모두 불러올 때는 LEFT OUTER JOIN

오른쪽 테이블의 행을 모두 불러올 때는 RIGHT OUTER JOIN을 사용한다.

 

현재 부서테이블의 40부서는 어떠한 사원도 없다. 이때 EMP와 DEPT 테이블을 결합하는데 사원이 없어도

부서 전체의 행을 보고 싶을 때 아래와 같이 SQL문을 작성한다.

SELECT DNAME, EMPNO, ENAME
FROM DEPT D LEFT OUTER JOIN EMP E
ON (D.DEPTNO = E.DEPTNO);


+------------+-------+--------+
| DNAME      | EMPNO | ENAME  |
+------------+-------+--------+
| ACCOUNTING |  7839 | KING   |
| ACCOUNTING |  7934 | MILLER |
| RESEARCH   |  7566 | JONES  |
| RESEARCH   |  7788 | SCOTT  |
| RESEARCH   |  7876 | ADAMS  |
| RESEARCH   |  7902 | FORD   |
| SALES      |  7369 | SMITH  |
| SALES      |  7499 | ALLEN  |
| SALES      |  7521 | WARD   |
| SALES      |  7698 | BLAKE  |
| SALES      |  7782 | CLARK  |
| SALES      |  7844 | TURNER |
| SALES      |  7900 | JAMES  |
| OPERATIONS |  NULL | NULL   |
+------------+-------+--------+
14 rows in set (0.001 sec)

 

이번에는 DEPT의 부서번호까지 출력해보자.

SELECT D.DEPTNO, DNAME, EMPNO, ENAME
FROM DEPT D LEFT OUTER JOIN EMP E
ON (D.DEPTNO = E.DEPTNO);

+--------+------------+-------+--------+
| DEPTNO | DNAME      | EMPNO | ENAME  |
+--------+------------+-------+--------+
|     10 | ACCOUNTING |  7839 | KING   |
|     10 | ACCOUNTING |  7934 | MILLER |
|     20 | RESEARCH   |  7566 | JONES  |
|     20 | RESEARCH   |  7788 | SCOTT  |
|     20 | RESEARCH   |  7876 | ADAMS  |
|     20 | RESEARCH   |  7902 | FORD   |
|     30 | SALES      |  7369 | SMITH  |
|     30 | SALES      |  7499 | ALLEN  |
|     30 | SALES      |  7521 | WARD   |
|     30 | SALES      |  7698 | BLAKE  |
|     30 | SALES      |  7782 | CLARK  |
|     30 | SALES      |  7844 | TURNER |
|     30 | SALES      |  7900 | JAMES  |
|     40 | OPERATIONS |  NULL | NULL   |
+--------+------------+-------+--------+
14 rows in set (0.001 sec)

이번에는 EMP의 부서번호를 출력해보자.

SELECT E.DEPTNO, DNAME, EMPNO, ENAME
FROM DEPT D LEFT OUTER JOIN EMP E
ON (D.DEPTNO = E.DEPTNO);


+--------+------------+-------+--------+
| DEPTNO | DNAME      | EMPNO | ENAME  |
+--------+------------+-------+--------+
|     10 | ACCOUNTING |  7839 | KING   |
|     10 | ACCOUNTING |  7934 | MILLER |
|     20 | RESEARCH   |  7566 | JONES  |
|     20 | RESEARCH   |  7788 | SCOTT  |
|     20 | RESEARCH   |  7876 | ADAMS  |
|     20 | RESEARCH   |  7902 | FORD   |
|     30 | SALES      |  7369 | SMITH  |
|     30 | SALES      |  7499 | ALLEN  |
|     30 | SALES      |  7521 | WARD   |
|     30 | SALES      |  7698 | BLAKE  |
|     30 | SALES      |  7782 | CLARK  |
|     30 | SALES      |  7844 | TURNER |
|     30 | SALES      |  7900 | JAMES  |
|   NULL | OPERATIONS |  NULL | NULL   |
+--------+------------+-------+--------+
14 rows in set (0.000 sec)

널값이 나오는 것을 알 수 있다.

FROM절의 테이블의 위치를 바꾸고 RIGHT OUTER JOIN을 사용하면 똑같은 결과가 나온다.

SELECT E.DEPTNO, DNAME, EMPNO, ENAME
FROM EMP E RIGHT OUTER JOIN DEPT D
ON (D.DEPTNO = E.DEPTNO);


+--------+------------+-------+--------+
| DEPTNO | DNAME      | EMPNO | ENAME  |
+--------+------------+-------+--------+
|     10 | ACCOUNTING |  7839 | KING   |
|     10 | ACCOUNTING |  7934 | MILLER |
|     20 | RESEARCH   |  7566 | JONES  |
|     20 | RESEARCH   |  7788 | SCOTT  |
|     20 | RESEARCH   |  7876 | ADAMS  |
|     20 | RESEARCH   |  7902 | FORD   |
|     30 | SALES      |  7369 | SMITH  |
|     30 | SALES      |  7499 | ALLEN  |
|     30 | SALES      |  7521 | WARD   |
|     30 | SALES      |  7698 | BLAKE  |
|     30 | SALES      |  7782 | CLARK  |
|     30 | SALES      |  7844 | TURNER |
|     30 | SALES      |  7900 | JAMES  |
|   NULL | OPERATIONS |  NULL | NULL   |
+--------+------------+-------+--------+
14 rows in set (0.000 sec)

 

보통 OUTER JOIN은 상품과 주문의 관계에서 많이 사용된다.

예를들면 주문이 된 상품과 주문이 되지 않은 상품을 알 수 있어서 각 상품의 재고상태를 파악할 수 있다.

또 다른 예로는 회원과 주문의 관계에서 사용된다.

주문을 한 회원과 주문을 하지 않은 회원은 구별할 수 있다.

 

SELF JOIN

하나의 테이블 안에 연관된 컬럼이 있을 경우를 의미한다.

예를 들어 EMP 의 모든 사원을 출력해보면 아래와 같다.

SELECT * FROM EMP;

+-------+--------+-----------+------+------------+---------+--------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM   | DEPTNO |
+-------+--------+-----------+------+------------+---------+--------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 2010-12-17 | 1600.00 | 300.00 |     30 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 2011-02-20 | 1600.00 | 300.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 2011-02-22 | 1250.00 | 500.00 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 2011-04-02 | 2975.00 |   NULL |     20 |
|  7698 | BLAKE  | MANAGER   | 7839 | 2011-05-01 | 2850.00 |   NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 2011-06-09 | 2450.00 |   NULL |     30 |
|  7788 | SCOTT  | ANALYST   | 7566 | 2017-07-13 | 3000.00 |   NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 2011-11-17 | 5000.00 |   NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 2011-09-08 | 1500.00 |   0.00 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 2017-07-13 | 1100.00 |   NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 2011-12-03 |  950.00 |   NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 2011-12-03 | 3000.00 |   NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 2012-01-23 | 1300.00 |   NULL |     10 |
+-------+--------+-----------+------+------------+---------+--------+--------+
13 rows in set (0.001 sec)

여기서 SMITH의 MGR(매니저)는 FORD이고, 

FORD의 매니저는 JONES이고,

JONES의 매니저는 KING이고 KING은 매니저가 없다.

 

즉 계층구조 혹은 부모자식과 비슷한 구조를 가지고있다.

이를 간단히 도식화하면 아래와 같다.

1	2	3	4
---------------------------------------
KING
	JONES
		FORD
			SMITH

이제 셀프조인을 사용해보자. 셀프조인은 자기 자신의 테이블과 조인하는 것이므로 반드시 테이블 별칭을 써줘야 한다.

SELECT E.ENAME 사원, M.ENAME 매니저
FROM EMP E INNER JOIN EMP M
ON (E.MGR = M.EMPNO);

+--------+--------+
| 사원   | 매니저 |
+--------+--------+
| SMITH  | FORD   |
| ALLEN  | BLAKE  |
| WARD   | BLAKE  |
| JONES  | KING   |
| BLAKE  | KING   |
| CLARK  | KING   |
| SCOTT  | JONES  |
| TURNER | BLAKE  |
| ADAMS  | SCOTT  |
| JAMES  | BLAKE  |
| FORD   | JONES  |
| MILLER | CLARK  |
+--------+--------+
12 rows in set (0.001 sec)

이때 매니저가 없는 사원들을 보고 싶으면 OUTER JOIN을 사용한다.

SELECT E.EMPNO, E.ENAME 사원, E.MGR, M.ENAME 매니저
FROM EMP E LEFT OUTER JOIN EMP M
ON (E.MGR = M.EMPNO);


+-------+--------+------+--------+
| EMPNO | 사원   | MGR  | 매니저 |
+-------+--------+------+--------+
|  7369 | SMITH  | 7902 | FORD   |
|  7499 | ALLEN  | 7698 | BLAKE  |
|  7521 | WARD   | 7698 | BLAKE  |
|  7566 | JONES  | 7839 | KING   |
|  7698 | BLAKE  | 7839 | KING   |
|  7782 | CLARK  | 7839 | KING   |
|  7788 | SCOTT  | 7566 | JONES  |
|  7839 | KING   | NULL | NULL   |
|  7844 | TURNER | 7698 | BLAKE  |
|  7876 | ADAMS  | 7788 | SCOTT  |
|  7900 | JAMES  | 7698 | BLAKE  |
|  7902 | FORD   | 7566 | JONES  |
|  7934 | MILLER | 7782 | CLARK  |
+-------+--------+------+--------+
13 rows in set (0.000 sec)

즉 SMITH의 매니저는 FORD이고, ALLEN의 매니저는 BLAKE임을 알 수 있고,

OUTER JOIN을 수행해서 매니저가 없는 KING은 널값이 나옴을 알 수 있다.

즉 KING은 최상위 사원임을 알 수 있다.

 

이번에는 매니저가 없으면 NULL 대신 '관리자 없음'이 나오게 출력해보자.

SELECT E.EMPNO 사원번호, E.ENAME 사원, IFNULL(E.MGR, '관리자없음') 관리자번호, M.ENAME 매니저
FROM EMP E LEFT OUTER JOIN EMP M
ON (E.MGR = M.EMPNO);


+----------+--------+------------+--------+
| 사원번호 | 사원   | 관리자번호 | 매니저 |
+----------+--------+------------+--------+
|     7369 | SMITH  | 7902       | FORD   |
|     7499 | ALLEN  | 7698       | BLAKE  |
|     7521 | WARD   | 7698       | BLAKE  |
|     7566 | JONES  | 7839       | KING   |
|     7698 | BLAKE  | 7839       | KING   |
|     7782 | CLARK  | 7839       | KING   |
|     7788 | SCOTT  | 7566       | JONES  |
|     7839 | KING   | 관리자없음 | NULL   |
|     7844 | TURNER | 7698       | BLAKE  |
|     7876 | ADAMS  | 7788       | SCOTT  |
|     7900 | JAMES  | 7698       | BLAKE  |
|     7902 | FORD   | 7566       | JONES  |
|     7934 | MILLER | 7782       | CLARK  |
+----------+--------+------------+--------+
13 rows in set (0.001 sec)

 

 

DDL

데이터를 담을 상자를 만들기

1. 데이터베이스

--데이터베이스 만들기
CREATE DATABASE TEST1;

--데이터베이스 삭제하기
DROP DATABASE TEST1;

2. 테이블

CREATE TABLE 테이블명(
컬럼명 데이터타입(크기) 옵션,
컬럼명 데이터타입(크기) 옵션,
컬럼명 데이터타입(크기) 옵션,
);

*참고: 리눅스는 대소문자를 구별하고, 윈도우는 대소문자 구분이 없다.

 

데이터 타입

문자열형
	CHAR(크기)	-고정형	-CHAR(10) 'ABC' : 10자리 중에 3자리(7자리남음)
	VARCHAR(크기)	-가변형	-VARCHAR(10) 'ABC' : 10자리였지만 3자리로 바뀜
	TEXT	

숫자형
	INT = INTEGER	-정수형
		TINY
		MEDIUM
		BIG
	NUMBER 		-실수형

날짜형
	DATE
	TIME
	DATETIME

 

테이블 만들기

CREATE TABLE DEPT2 (
DEPTNO INT(2),
DNAME VARCHAR(10),
LOC VARCHAR(10)
);

Query OK, 0 rows affected (0.022 sec)
CREATE TABLE EMP2 (
	EMPNO 	INT(4),
	ENAME	VARCHAR(10),
	JOB	VARCHAR(9),
	MGR	INT(4),
	HIREDATE DATE,
	SAL	DECIMAL(7,2),
	COMM	DECIMAL(7,2),
	DEPTNO 	INT(2)
);


Query OK, 0 rows affected (0.022 sec)

 

다른 테이블 참조해서 만들기

CREATE TABLE DEPT2
AS SELECT * FROM DEPT;

//
Query OK, 4 rows affected (0.028 sec)
Records: 4  Duplicates: 0  Warnings: 0

//
SELECT * FROM DEPT2;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+
4 rows in set (0.001 sec)


----특정 컬럼만을 참조해서 만들기
CREATE TABLE DEPT3
AS SELECT LOC, DEPTNO 
FROM DEPT
WHERE DEPTNO = 10;

//
Query OK, 1 row affected (0.029 sec)
Records: 1  Duplicates: 0  Warnings: 0

SELECT * FROM DEPT3;
+----------+--------+
| LOC      | DEPTNO |
+----------+--------+
| NEW YORK |     10 |
+----------+--------+
1 row in set (0.000 sec)


----컬럼명을 바꿔서 저장하기
CREATE TABLE DEPT4
AS SELECT LOC LOCATION, DEPTNO NO
FROM DEPT;

Query OK, 4 rows affected (0.021 sec)
Records: 4  Duplicates: 0  Warnings: 0

SELECT * FROM DEPT4;
+----------+----+
| LOCATION | NO |
+----------+----+
| NEW YORK | 10 |
| DALLAS   | 20 |
| CHICAGO  | 30 |
| BOSTON   | 40 |
+----------+----+
4 rows in set (0.000 sec)

 

EMP_YEAR테이블을 만드는데 EMP 테이블의 사원번호, 사원이름, 급여, 연봉, 입사일자가 포함되도록 만들어보자.

CREATE TABLE EMP_YEAR
AS SELECT EMPNO 사원번호, ENAME 사원이름, SAL 급여, TRUNCATE(SAL*12+IFNULL(COMM,0), 0) 연봉, HIREDATE 입사일자
FROM EMP; 

Query OK, 13 rows affected (0.022 sec)
Records: 13  Duplicates: 0  Warnings: 0

SELECT *FROM EMP_YEAR;

+----------+----------+---------+-------+------------+
| 사원번호 | 사원이름 | 급여    | 연봉  | 입사일자   |
+----------+----------+---------+-------+------------+
|     7369 | SMITH    | 1600.00 | 19500 | 2010-12-17 |
|     7499 | ALLEN    | 1600.00 | 19500 | 2011-02-20 |
|     7521 | WARD     | 1250.00 | 15500 | 2011-02-22 |
|     7566 | JONES    | 2975.00 | 35700 | 2011-04-02 |
|     7698 | BLAKE    | 2850.00 | 34200 | 2011-05-01 |
|     7782 | CLARK    | 2450.00 | 29400 | 2011-06-09 |
|     7788 | SCOTT    | 3000.00 | 36000 | 2017-07-13 |
|     7839 | KING     | 5000.00 | 60000 | 2011-11-17 |
|     7844 | TURNER   | 1500.00 | 18000 | 2011-09-08 |
|     7876 | ADAMS    | 1100.00 | 13200 | 2017-07-13 |
|     7900 | JAMES    |  950.00 | 11400 | 2011-12-03 |
|     7902 | FORD     | 3000.00 | 36000 | 2011-12-03 |
|     7934 | MILLER   | 1300.00 | 15600 | 2012-01-23 |
+----------+----------+---------+-------+------------+
13 rows in set (0.000 sec)

 

다음으로 ALL_EMP테이블을 만드는데

사원번호, 사원이름, 입사일자, 부서번호, 부서명, 부서위치가 나오도록 만들어보자.

CREATE TABLE EMP_ALL
AS SELECT EMPNO, ENAME, HIREDATE, DNAME, LOC
FROM EMP E INNER JOIN DEPT D
ON (E.DEPTNO = D.DEPTNO);

SELECT * FROM EMP_ALL;

+-------+--------+------------+------------+----------+
| EMPNO | ENAME  | HIREDATE   | DNAME      | LOC      |
+-------+--------+------------+------------+----------+
|  7369 | SMITH  | 2010-12-17 | SALES      | CHICAGO  |
|  7499 | ALLEN  | 2011-02-20 | SALES      | CHICAGO  |
|  7521 | WARD   | 2011-02-22 | SALES      | CHICAGO  |
|  7566 | JONES  | 2011-04-02 | RESEARCH   | DALLAS   |
|  7698 | BLAKE  | 2011-05-01 | SALES      | CHICAGO  |
|  7782 | CLARK  | 2011-06-09 | SALES      | CHICAGO  |
|  7788 | SCOTT  | 2017-07-13 | RESEARCH   | DALLAS   |
|  7839 | KING   | 2011-11-17 | ACCOUNTING | NEW YORK |
|  7844 | TURNER | 2011-09-08 | SALES      | CHICAGO  |
|  7876 | ADAMS  | 2017-07-13 | RESEARCH   | DALLAS   |
|  7900 | JAMES  | 2011-12-03 | SALES      | CHICAGO  |
|  7902 | FORD   | 2011-12-03 | RESEARCH   | DALLAS   |
|  7934 | MILLER | 2012-01-23 | ACCOUNTING | NEW YORK |
+-------+--------+------------+------------+----------+
13 rows in set (0.001 sec)

 

이번에는 데이터는 없이 컬럼만 참조해서 테이블을 만들어보자.

--방법1
CREATE TABLE EMPTY_DEPT
AS SELECT * 
FROM DEPT
WHERE 1 != 1;

--방법2
CREATE TABLE EMPTY_DEPT1 LIKE DEPT;


DESC EMPTY_DEPT;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| DEPTNO | int(2)      | NO   |     | NULL    |       |
| DNAME  | varchar(14) | YES  |     | NULL    |       |
| LOC    | varchar(13) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.006 sec)


SELECT * FROM EMPTY_DEPT;
Empty set (0.000 sec)

 

 

테이블 수정하기

ALTER 구문 사용

컬럼
	추가
	내용변경
	삭제

제약조건

먼저 새로운 테이블을 만들어준다.

CREATE TABLE EMP_YEAR
AS SELECT EMPNO, ENAME, HIREDATE
FROM EMP
WHERE 1 != 1;


DESC EMP_YEAR;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| EMPNO    | int(4)      | NO   |     | NULL    |       |
| ENAME    | varchar(10) | YES  |     | NULL    |       |
| HIREDATE | date        | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.004 sec)

 

컬럼추가하기

ALTER TABLE EMP_YEAR
ADD JOB VARCHAR(10);

DESC EMP_YEAR;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| EMPNO    | int(4)      | NO   |     | NULL    |       |
| ENAME    | varchar(10) | YES  |     | NULL    |       |
| HIREDATE | date        | YES  |     | NULL    |       |
| JOB      | varchar(10) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.008 sec)

 

컬럼 데이터 타입 수정하기

ALTER TABLE EMP_YEAR
MODIFY JOB VARCHAR(20);

DESC EMP_YEAR;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| EMPNO    | int(4)      | NO   |     | NULL    |       |
| ENAME    | varchar(10) | YES  |     | NULL    |       |
| HIREDATE | date        | YES  |     | NULL    |       |
| JOB      | varchar(20) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.009 sec)

 

컬럼명 수정하기

ALTER TABLE EMP_YEAR
RENAME COLUMN JOB TO WORK;

DESC EMP_YEAR;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| EMPNO    | int(4)      | NO   |     | NULL    |       |
| ENAME    | varchar(10) | YES  |     | NULL    |       |
| HIREDATE | date        | YES  |     | NULL    |       |
| WORK     | varchar(20) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.007 sec)

 

컬럼 삭제하기

ALTER TABLE EMP_YEAR
DROP WORK;

DESC EMP_YEAR;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| EMPNO    | int(4)      | NO   |     | NULL    |       |
| ENAME    | varchar(10) | YES  |     | NULL    |       |
| HIREDATE | date        | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.008 sec)

 

존재하면 특정컬럼 또는 테이블 지우기

DROP TABLE IF EXISTS EMP2;

 

테이블 생성구문 확인하기

SHOW CREATE TABLE DEPT;

+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                          |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| DEPT  | CREATE TABLE `dept` (
  `DEPTNO` int(2) NOT NULL,
  `DNAME` varchar(14) DEFAULT NULL,
  `LOC` varchar(13) DEFAULT NULL,
  PRIMARY KEY (`DEPTNO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.002 sec)

 

DESC와 같은 테이블의 컬럼값들 확인하기

SHOW COLUMNS FROM DEPT;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| DEPTNO | int(2)      | NO   | PRI | NULL    |       |
| DNAME  | varchar(14) | YES  |     | NULL    |       |
| LOC    | varchar(13) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.006 sec)

 

테이블에 대한 상태 확인하기(행의 수 , 데이터 길이 등)

 SHOW TABLE STATUS;
+----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+------------------+-----------+
| Name     | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation       | Checksum | Create_options | Comment | Max_index_length | Temporary |
+----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+------------------+-----------+
| dept     | InnoDB |      10 | Dynamic    |    4 |           4096 |       16384 |               0 |            0 |         0 |           NULL | 2020-11-30 00:27:46 | NULL                | NULL       | utf8_general_ci |     NULL |                |         |                0 | N         |
| emp      | InnoDB |      10 | Dynamic    |   13 |           1260 |       16384 |               0 |        16384 |         0 |           NULL | 2020-11-30 00:28:40 | 2020-11-30 09:53:11 | NULL       | utf8_general_ci |     NULL |                |         |                0 | N         |
| emp_year | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |               0 |            0 |         0 |           NULL | 2020-12-01 14:51:45 | 2020-12-01 14:51:45 | NULL       | utf8_general_ci |     NULL |                |         |                0 | N         |
| salgrade | InnoDB |      10 | Dynamic    |    5 |           3276 |       16384 |               0 |            0 |         0 |           NULL | 2020-11-30 00:37:09 | NULL                | NULL       | utf8_general_ci |     NULL |                |         |                0 | N         |
+----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+------------------+-----------+
4 rows in set (0.004 sec)

 

 

DML (INSERT, UPDATE, DELETE)

새로운 테이블을 만들고 실습해보자.

CREATE TABLE DEPT2
AS SELECT * FROM DEPT
WHERE 1 != 1;

Query OK, 0 rows affected (0.016 sec)
Records: 0  Duplicates: 0  Warnings: 0

SELECT * FROM DEPT2;

Empty set (0.000 sec)

 

INSERT 문

새로운 테이블에 데이터를 집어넣어보자.

아래 구문으로 데이터를 집어넣고 값은 컬럼에 데이터 타입과 크기에 맞게 넣어야한다.

이때 컬럼의 데이터타입이 문자이고 크기가 10이면 자릿수 10자리를 의미하고

숫자의 경우 바이트수를 의미한다.

--1 : 전체 컬럼에 데이터를 집어넣을 때
INSERT INTO 테이블명 VALUES(컬럼1의값, 컬럼2의값, 컬럼3의값);

--2 : 특정 컬럼에만 데이터를 집어넣을 때
INSERT INTO 테이블명(컬럼명1, 컬럼명2) VALUES (컬럼1의값, 컬럼2의값);
MariaDB [sample]> INSERT INTO DEPT2 VALUES(10, '영업', '서울');
Query OK, 1 row affected (0.004 sec)

MariaDB [sample]> SELECT*FROM DEPT2;
+--------+-------+------+
| DEPTNO | DNAME | LOC  |
+--------+-------+------+
|     10 | 영업  | 서울 |
+--------+-------+------+
1 row in set (0.000 sec)
MariaDB [sample]> INSERT INTO DEPT2(DEPTNO, DNAME, LOC) VALUES(20, '연구', '대전');
Query OK, 1 row affected (0.002 sec)

MariaDB [sample]> SELECT * FROM DEPT2;
+--------+-------+------+
| DEPTNO | DNAME | LOC  |
+--------+-------+------+
|     10 | 영업  | 서울 |
|     20 | 연구  | 대전 |
+--------+-------+------+
2 rows in set (0.000 sec)

 

 

UPDATE 문

특정컬럼의 데이터값을 바꿀 때 사용

UPDATE 테이블명 SET 컬럼명 = 값 
[WHERE 컬럼명 = 값];

[]는 생략가능

일단 새로운 테이블을 만든다.

DROP TABLE DEPT2;
Query OK, 0 rows affected (0.011 sec)

CREATE TABLE DEPT2
AS SELECT * FROM DEPT;
Query OK, 4 rows affected (0.024 sec)
Records: 4  Duplicates: 0  Warnings: 0

SELECT * FROM DEPT2;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+
4 rows in set (0.000 sec)

전체 부서위치를 서울로 변경해보자.

UPDATE DEPT2 SET LOC='서울';
Query OK, 4 rows affected (0.002 sec)
Rows matched: 4  Changed: 4  Warnings: 0

SELECT * FROM DEPT2;
+--------+------------+------+
| DEPTNO | DNAME      | LOC  |
+--------+------------+------+
|     10 | ACCOUNTING | 서울 |
|     20 | RESEARCH   | 서울 |
|     30 | SALES      | 서울 |
|     40 | OPERATIONS | 서울 |
+--------+------------+------+
4 rows in set (0.000 sec)

 

이번에는 10번부서만 위치를 부산으로 변경해보자.

UPDATE DEPT2 SET LOC='부산'
WHERE DEPTNO = 10;


SELECT * FROM DEPT2;
+--------+------------+------+
| DEPTNO | DNAME      | LOC  |
+--------+------------+------+
|     10 | ACCOUNTING | 부산 |
|     20 | RESEARCH   | 서울 |
|     30 | SALES      | 서울 |
|     40 | OPERATIONS | 서울 |
+--------+------------+------+
4 rows in set (0.000 sec)

 

DELETE 문

특정값을 가진 컬럼의 데이터를 지우기

DELETE FROM 테이블명 [WHERE 컬럼명 = 값];

10번부서만 지워보자.

DELETE FROM DEPT2 WHERE DEPTNO = 10;

SELECT * FROM DEPT2;
+--------+------------+------+
| DEPTNO | DNAME      | LOC  |
+--------+------------+------+
|     20 | RESEARCH   | 서울 |
|     30 | SALES      | 서울 |
|     40 | OPERATIONS | 서울 |
+--------+------------+------+
3 rows in set (0.000 sec)

 

 

데이터무결성

테이블 내에서 데이터들이 제대로 유지되게 하는 조건

-NULL 조건 : NOT NULL 이면 무조건 데이터를 집어넣어야 함 / NULL이면 데이터를 집어넣지 않아도됨.

 NULL은 기본값이기 때문에 안써도된다.

CREATE TABLE DEPT2(
DEPTNO INT(2) NOT NULL,
DNAME VARCHAR(14),


DESC DEPT2;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| DEPTNO | int(2)      | NO   |     | NULL    |       |
| DNAME  | varchar(14) | YES  |     | NULL    |       |
| LOC    | varchar(15) | NO   |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.009 sec)

 

이제 데이터를 집어넣어보자.

데이터를 ''으로 하면 공백인 문자가 들어간 것이고 아예 데이터를 주지 않으려면 NULL이라고 써줘야한다.

즉 공백과 NULL은 다른 것이다.

INSERT INTO DEPT2 VALUES(10, '영업', '서울');

INSERT INTO DEPT2 VALUES(20, '', '서울');

INSERT INTO DEPT2 VALUES(30, NULL, '서울');

INSERT INTO DEPT2 VALUES(40, '연구', NULL); -- 에러

INSERT INTO DEPT2 VALUES(40, '연구', '');

SELECT * FROM DEPT2;
+--------+-------+------+
| DEPTNO | DNAME | LOC  |
+--------+-------+------+
|     10 | 영업  | 서울 |
|     20 |       | 서울 |
|     30 | NULL  | 서울 |
|     40 | 연구  |      |
+--------+-------+------+
4 rows in set (0.000 sec)

 

이는 UPDATE문에서 NOT NULL인 컬럼을 NULL로 바꾸려고하면 에러가 난다.

UPDATE DEPT2 SET LOC=NULL
WHERE DEPTNO = 20;

ERROR 1048 (23000): Column 'LOC' cannot be null

 

여러 개의 INSERT문 사용하기

INSERT INTO DEPT2 VALUES (50, '개발', '부산'), (60, '생산', '광주');

SELECT * FROM DEPT2;
+--------+-------+------+
| DEPTNO | DNAME | LOC  |
+--------+-------+------+
|     10 | 영업  | 서울 |
|     20 |       | 서울 |
|     30 | NULL  | 서울 |
|     40 | 연구  |      |
|     50 | 개발  | 부산 |
|     60 | 생산  | 광주 |
+--------+-------+------+
6 rows in set (0.000 sec)

 

여러 개의 INSERT문 서브쿼리 이용해서 데이터 집어넣기

INSERT INTO DEPT2 SELECT * FROM DEPT;

SELECT * FROM DEPT2;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | 영업       | 서울     |
|     20 |            | 서울     |
|     30 | NULL       | 서울     |
|     40 | 연구       |          |
|     50 | 개발       | 부산     |
|     60 | 생산       | 광주     |
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+
10 rows in set (0.000 sec)

 

서브쿼리로 컬럼명의 순서를 지정해서 데이터를 넣을 수 있다.

INSERT INTO DEPT2 (LOC, DNAME, DEPTNO) SELECT LOC, DNAME, DEPTNO FROM DEPT;

SELECT * FROM DEPT2;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | 영업       | 서울     |
|     20 |            | 서울     |
|     30 | NULL       | 서울     |
|     40 | 연구       |          |
|     50 | 개발       | 부산     |
|     60 | 생산       | 광주     |
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+
14 rows in set (0.000 sec)

 

-DEFAULT 조건 : 컬럼의 기본값을 설정한다. 

INSERT INTO DEPT2 VALUES (DEFAULT, '개발', '대전');

SELECT * FROM DEPT2;
+--------+-------+------+
| DEPTNO | DNAME | LOC  |
+--------+-------+------+
|     10 | 개발  | 대전 |
+--------+-------+------+
1 row in set (0.000 sec)

DEFALUT를 쓸 때는 DEFAULT라고 써주어야 한다. 그냥 NULL을 쓰면 NULL이 들어간다. 공백도 마찬가지이다.

 

 

 

 

연습문제

1. 사원 테이블에서 BLAKE 보다 급여가 많은 사원들의 사번, 이름, 급여를 검색하시오.

2. 사원 테이블에서 MILLER보다 늦게 입사한 사원의 사번, 이름, 입사일을 검색하시오.

3. 사원 테이블에서 사원 전체 평균 급여보다 급여가 많은 사원들의 사번, 이름, 급여를 검색하시오.

4. 사원 테이블에서 CLARK와 같은 부서이며, 사번이 7698인 직원의 급여보다 많은 급여를 받는 
사원들의 사번, 이름, 급여를 검색하시오.

1. 부서 테이블과 사원 테이블에서 사번, 사원명, 부서코드, 부서명을 검색하시오. 
단, 출력시, 사원명을 기준으로 오름차순으로 정렬하시오.

2. 부서 테이블과 사원 테이블에서 사번, 사원명, 급여, 부서명을 검색하시오. 
단, 급여가 2000 이상인 사원에 대하여 급여를 기준으로 내림차순으로 정렬하시오.

3. 부서 테이블과 사원 테이블에서 사번, 사원명, 업무, 급여, 부서명을 검색하시오. 
단, 업무가 MANAGER이며 급여가 2500 이상인 사원에 대하여 사번을 기준으로 오름차순으로 정렬하시오.

4. 사원 테이블과 급여등급 테이블에서 사번, 사원명, 급여, 등급을 검색하시오. 
단, 등급은 급여가 하한값과 상한값 범위에 포함되고 등급이 4이며 급여를 기준으로 내림차순으로 정렬하시오.

5. 부서 테이블, 사원 테이블, 급여등급 테이블에서 사번, 사원명, 부서명, 급여, 등급를 검색하시오. 
단, 등급은 급여가 하한값과 상한값 범위에 포함되며 등급을 기준으로 내림차순으로 정렬하시오.

6. 사원 테이블에서 사원명과 해당 사원의 관리자명을 검색하시오.

7. 사원 테이블에서 사원명, 해당 사원의 관리자명, 해당 사원의 관리자의 관리자명을 검색하시오.

8. 7번 결과에서 상위 관리자가 없는 모든 사원의 이름도 사원명에 출력되도록 수정하시오.

 

해답

1. 사원 테이블에서 BLAKE 보다 급여가 많은 사원들의 사번, 이름, 급여를 검색하시오.
SELECT EMPNO, ENAME, SAL
FROM EMP
WHERE SAL > (
	SELECT SAL
	FROM EMP
	WHERE ENAME = 'BLAKE');

2. 사원 테이블에서 MILLER보다 늦게 입사한 사원의 사번, 이름, 입사일을 검색하시오.
SELECT EMPNO, ENAME, HIREDATE
FROM EMP
WHERE HIREDATE > (
	SELECT HIREDATE
	FROM EMP
	WHERE ENAME = 'MILLER');

3. 사원 테이블에서 사원 전체 평균 급여보다 급여가 많은 사원들의 사번, 이름, 급여를 검색하시오.
SELECT EMPNO, ENAME, SAL
FROM EMP
WHERE SAL > (
	SELECT AVG(SAL)
	FROM EMP);

4. 사원 테이블에서 CLARK와 같은 부서이며, 사번이 7698인 직원의 급여보다 많은 급여를 받는 
사원들의 사번, 이름, 급여를 검색하시오.
SELECT EMPNO, ENAME, SAL
FROM EMP
WHERE DEPTNO = (
	SELECT DEPTNO
	FROM EMP
	WHERE ENAME = 'CLARK')
AND SAL > (
	SELECT SAL
	FROM EMP
	WHERE EMPNO = 7698);

1. 부서 테이블과 사원 테이블에서 사번, 사원명, 부서코드, 부서명을 검색하시오. 
단, 출력시, 사원명을 기준으로 오름차순으로 정렬하시오.
SELECT EMPNO, ENAME, E.DEPTNO, DNAME
FROM EMP E INNER JOIN DEPT D
ON (E.DEPTNO = D.DEPTNO)
ORDER BY ENAME;

2. 부서 테이블과 사원 테이블에서 사번, 사원명, 급여, 부서명을 검색하시오. 
단, 급여가 2000 이상인 사원에 대하여 급여를 기준으로 내림차순으로 정렬하시오.
SELECT EMPNO, ENAME, SAL, DNAME
FROM EMP INNER JOIN DEPT USING(DEPTNO)
WHERE SAL >= 2000
ORDER BY SAL DESC;

3. 부서 테이블과 사원 테이블에서 사번, 사원명, 업무, 급여, 부서명을 검색하시오. 
단, 업무가 MANAGER이며 급여가 2500 이상인 사원에 대하여 사번을 기준으로 오름차순으로 정렬하시오.
SELECT EMPNO, ENAME, JOB, SAL, E.DEPTNO
FROM EMP E INNER JOIN DEPT D 
ON E.DEPTNO = D.DEPTNO
WHERE JOB = 'MANAGER' 
AND SAL >= 2500
ORDER BY EMPNO;

4. 사원 테이블과 급여등급 테이블에서 사번, 사원명, 급여, 등급을 검색하시오. 
단, 등급은 급여가 하한값과 상한값 범위에 포함되고 등급이 4이며 급여를 기준으로 내림차순으로 정렬하시오.
SELECT EMPNO, ENAME, SAL, GRADE
FROM EMP E INNER JOIN SALGRADE S
ON SAL BETWEEN S.LOSAL AND S.HISAL
WHERE GRADE = 4
ORDER BY SAL DESC;

5. 부서 테이블, 사원 테이블, 급여등급 테이블에서 사번, 사원명, 부서명, 급여, 등급를 검색하시오. 
단, 등급은 급여가 하한값과 상한값 범위에 포함되며 등급을 기준으로 내림차순으로 정렬하시오.
SELECT EMPNO, ENAME, DNAME, SAL, GRADE
FROM EMP E INNER JOIN DEPT D ON E.DEPTNO = D.DEPTNO
INNER JOIN SALGRADE S ON SAL BETWEEN S.LOSAL AND S.HISAL
ORDER BY GRADE DESC;

6. 사원 테이블에서 사원명과 해당 사원의 관리자명을 검색하시오.
SELECT E.ENAME, M.ENAME
FROM EMP E INNER JOIN EMP M
ON E.EMPNO = M.MGR;

7. 사원 테이블에서 사원명, 해당 사원의 관리자명, 해당 사원의 관리자의 관리자명을 검색하시오.
SELECT E.ENAME 사원명, M1.ENAME 관리자1, M2.ENAME 관리자2
FROM EMP M2 INNER JOIN EMP M1 ON M2.EMPNO = M1.MGR
INNER JOIN EMP E ON M1.EMPNO = E.EMPNO;

8. 7번 결과에서 상위 관리자가 없는 모든 사원의 이름도 사원명에 출력되도록 수정하시오.
SELECT E.ENAME 사원명, M1.ENAME 관리자1, M2.ENAME 관리자2
FROM EMP M2 RIGHT OUTER JOIN EMP M1 ON M2.EMPNO = M1.MGR
RIGHT OUTER JOIN EMP E ON M1.EMPNO = E.EMPNO;

 

728x90
반응형
저작자표시 동일조건

'데이터 엔지니어링 정복 > SQL' 카테고리의 다른 글

[SQL] 프로그래머스-입양시각구하기(2)  (0) 2021.04.11
[SQL] 12/02 | 제약조건(not null, unique, 기본키, 외래키), 뷰(뷰사용, 뷰조인, 인라인뷰), 인덱스, 데이터베이스 사용자, DCL, 데이터베이스 백업(덤프, 복원)  (0) 2020.12.02
[SQL] 11/30 | SELECT문(WHERE, LIKE, IS NULL, IN, BETWEEN, %, _, NOT, ORDER BY, LIMIT), 내장함수(단일행함수, 그룹함수), GROUP BY절, 연습문제  (0) 2020.11.30
[SQL] 11/27 | 데이터베이스 개념, mariaDB설치, 기본명령어(show, use, desc), select문(alias, 연산, distinct, where, 날짜비교, 아스키코드비교)  (0) 2020.11.30
[SQL] 오라클 데이터베이스 & SQL Developer 설치  (0) 2020.11.11
'데이터 엔지니어링 정복/SQL' Related Articles more
Comments