MySQL 3일차

1. 조인( join )

1) query 작업 ( select 문 )
- selection: 행 선택, where 절 이용
- projection : 컬럼 선택, select 절 이용
- join : 여러 테이블 연결

2) 개념
- select 해서 출력하고자 하는 데이터가 하나의 테이블이 아닌
  여러 테이블에 분산되어 있는 경우에 테이블을 연결해서 원하는 
  데이터를 출력 할 수 있다.

3) 조인 방법 종류 1

가. inner 조인
- 2 개의 테이블 연결시 반드시 일치되는 데이터만 반환.
  즉, 일치하지 않으면 누락됨.

나. outer 조인
- inner 조인 + 일치되지 않아서 누락된 데이터 포함 반환.

4) 조인 방법 종류 2

가. ANSI 조인 ( * )
- DBMS 독립적

나. 비 ANSI 조인
ex) Oracle 조인

4) 조인 방법 종류 3 ( ANSI 문법 )

# inner 조인방법
- cross join
- natural join
- join ~ on
- join ~ using

# outer 조인방법
- left outer join ~ on | using
- right outer join ~ on | using

- full outer join ~ on | using ( MySQL 지원안됨. Oracle 지원됨 )

2.  2개의 테이블을 연결하는 방법


가. 5가지 제약조건 타입 ( constrainsts )
용도: 테이블에 저장되는 데이터 검증용. ( 무결성 데이터 보장 )

=> 테이블의 컬럼단위로 지정.
=> 제약조건 정보보기
SELECT * FROM information_schema.table_constraints
WHERE TABLE_NAME = 'emp';

- primary key ( pk 라고 부름) => not null 제약조건 + unique 제약조건
목적: 레코드 식별용
특징: - 값이 존재 ( null 값 불가, not null 제약조건 )
        - 중복 불가 ( unique 제약조건 )
        - 테이블당 하나만 설정 가능
        - 복합컬럼 가능
    ex) 수강테이블에서 ( 학번, 과목)
       - 제약조건을 추가(*) 하는 기능
emp 테이블의 pk 는 empno,
dept 테이블의 pk 는 deptno

- unique
목적: 중복값 저장 불가
특징: - 값이 없어도 된다. ( null 값 허용 )
        - 테이블당 여러개 설정 가능
        - 복합컬럼 가능
        - 제약조건을 추가(*) 하는 기능
        - 자동으로 인덱스( 색인표 역할 ) 추가됨.

- check
목적: 비즈니스 로직 검증 역할로서
        저장되는 데이터의 범위를 제한.
        ex) 성별
----
남 / 여 ( * ) 
남자 / 여자
M / F
0 / 1

gender check ( gender in ( '남', '여' ) )
하나만 쓰겠다 제약설정
age check ( age < 100 )


- foreign key ( fk, 참조키, 외래키 라고 부른다. )
목적: 다른 테이블을 참조하는 역할.
ex)
* emp 테이블 ( slave, child 테이블 )
empno(pk), ename, ... , deptno(fk)
-------------------------------
7459   SMITH   ...   20
  50 ( 저장불가 )
     null ( 가능 )

* dept 테이블 ( master table , parent table )
deptno(pk), dname, ... , loc
-------------------------------
10
20     RESERCH    DALLS
30
40

특징: - 반드시 fk 는 master 의 PK 로 된 컬럼 또는 UK 로 된 컬럼만
          참조할 수 있다.
        - fk 가 가질수 있는 값은 master 의 PK 로 된 컬럼의 값 또는
          UK 로 된 컬럼의 값만 저장할 수 있다.
          추가로 null 값 저장 가능
          결론은 fk 가 가질수 있는 값은 3가지 ( master 의 pk + uk + null )
        - 테이블당 여러개 설정 가능
        - 제약조건을 추가(*) 하는 기능

- not null
목적: null 허용 불가 ( 반드시 값을 가져야 됨. 중복 가능 )
특징: - 기본적으로 모든 테이블의 컬럼은 null 허용한다.

          null -----------> not null
                   변경

        - 제약조건을 변경(*) 하는 기능     


나. join 기본 동작

- pk 와 fk 이용
pk : master 의 pk
fk : slave 의 fk

==> 연산자를 사용한다면 동등연산자 ( = ) 사용.
==> equi 조인이라고도 부른다.
==> 조인 이라고 하면 equi 조인 ( pk 와 fk 사용 ) 을 의미한다.
==> inner 조인

- 일반컬럼 과 일반컬럼 이용
==> 범위로 조인하기 때문에 non-equi 조인이라고 부른다.
==> inner 조인

ex)
* emp 테이블
emp, ename,  ... , sal
-------------------------
7379 SMITH        800


* salgrade 테이블
grade   losal       hisal
--------------------------
  1         700      1200


3. cross join
1) 문법:
SELECT
FROM 테이블1 CROSS JOIN 테이블2;

2) 동작
   테이블1의 하나의 행과 테이블2의 여러 행과 JOIN 됨.
   따라서 결과가 ``테이블1의 행갯수 * 테이블2의 행갯수``
   한 결과가 나옴.

3) 특징
- 데이터로 사용 불가

==> 조인할 때는 조인 조건을 생략하거나 또는 잘못지정한 경우에는
       잘못된 결과를 반환할 수 있다.
==> 테이블이 n 개인 경우 반드시 ``n-1`` 개의 조인이 필요하다.

4. natural join
1) 문법1:
SELECT
FROM 테이블1 NATURAL JOIN 테이블2;
2) 문법2:
SELECT 별칭.컬럼명, ... 별칭.공통컬럼          // oracle 에서는 공통컬럼에 별칭사용불가
FROM 테이블1 별칭 NATURAL JOIN 테이블2 별칭; // as 사용불가.

2) 동작
- 테이블1 과 테이블2에서 공통컬럼을 찾아서 조인한다. ( 조인조건 )
- 만약 공통컬럼이 2개라면 쌍으로 비교한다.

3) 특징
- 반드시 공통컬럼이 존재해야 된다.
- 단점은 어떤 컬럼으로 조인헀는지를 추가로 확인하는 작업이 필요하다. ( 가독성 떨어짐 )
- inner 조인
  따라서 실습에서는 dept 테이블의 deptno 가 40인 레코드는 반환이 안됨.

4) 검색조건
- where 절 이용
- 문법
SELECT    // 3번
FROM 테이블 1 NATURAL JOIN 테이블2 // 1번
WHERE 조건식;    // 2번

=> ANSI 조인 특징
조인조건은 FROM 절에서 지정하고 검색조건은 WHERE 절에서 지정한다.

Oracle 조인은 WHERE 절에서 조인조건과 검색조건을 같이 지정한다.
ex)
SELECT
FROM emp, dept
WHERE e.deptno = d.deptno // 조인조건

SELECT
FROM emp, dept
WHERE e.deptno = d.deptno // 조인조건
AND e.deptno = 30;  // 검색조건

5. using 절

1) 문법 1:
SELECT
FROM 테이블1 JOIN 테이블2 USING(공통컬럼);

  문법 2:
SELECT 별칭.컬럼명,......, 별칭.공통컬럼
FROM 테이블1 별칭 JOIN 테이블2 별칭 USING(공통컬럼);

2) 동작
- 테이블1 과 테이블2 에서 using 절에 지정한 공통컬럼을 찾아서 조인한다. ( 조인조건 )

3) 특징:
- using 절 지정하는 공통컬럼에는 별칭사용 불가.
ex) using(별칭.공통컬럼); // 에러

4) 검색조건 지정 가능
SELECT  // 3번
FROM 테이블1 JOIN 테이블2 USING(공통컬럼)    // 1번 조인조건
WHERE 조건식; // 2번 검색조건

==> NATURAL JOIN 또는 USING(공통컬럼) 모두 공통컬럼이 반드시 존재해야됨.



6. ON 절 ( 가장 범용적으로 사용됨 )

1) 문법 1:
SELECT
FROM 테이블1 [INNER] JOIN 테이블2 ON 테이블1.컬럼 = 테이블2.컬럼;

   문법 2:
SELECT
FROM 테이블1 별칭1 [INNER] JOIN 테이블2 별칭2 ON 별칭1.컬럼 = 별칭2.컬럼;

2) 동작
- 테이블1 과 테이블2에서 on절에 지정한 조건으로 조인한다. ( 조인조건 )
- 조건은 동등(=) 이거나 부등 ( between A and B, in 등 ) 도 가능하다.

3) 특징
- 공통컬럼은 반드시 별칭사용 해야 됨. ( using 절과 반대 )

4) 검색조건 지정 가능

SELECT     // 3번
FROM 테이블1 [INNER] JOIN 테이블2 ON 테이블1.컬럼 = 테이블2.컬럼 // 1번, 조인조건
WHERE 조건식;     // 2번, 검색조건


7. self 조인
1) 개념
- 자신이 자신을 조인하는 개념

2) 샘플 예
- 사원명과 관리자명 출력하기

3) 구현방법
- 별칭 이용
* 원본 emp
empno, ename, job, mgr, hiredate, sal, comm, deptno
---------------------------------------------------------------
7369   SMITH         7902

...

7902    FORD  

* 가상 테이블   emp e
empno   ename    mgr
--------------------------
7369     SMITH   7902

* 가상 테이블 emp m
empno   ename
-------------------
7369     SMITH 
7902     FORD


SELECT *
FROM emp e JOIN emp m ON e.mgr = m.empno;

===> 조인조건이 일치한 행만 반환하는 INNER 조인이다.
          일치하지 않은 행은 누락됨.


8. outer 조인

1) 개념: 조인조건이 일치한 행만 반환하는 INNER 조인이다.
           일치하지 않은 행은 누락됨.
           이때, 누락된 행까지 포함해서 반환받을 수 있는 방법이다.

2) 문법:
SELECT
FROM 테이블1 LEFT OUTER JOIN 테이블2 USING(공통컬럼) | ON 절;

==> 테이블1의 모든 행이 반환됨. ( 일치하지 않아도 )

SELECT
FROM 테이블1 RIGHT OUTER JOIN 테이블2 USING(공통컬럼) | ON 절;

==> 테이블2의 모든 행이 반환됨. ( 일치하지 않아도 )

* 테이블1 과 테이블2 양쪽 모두 누락된 테이터를 한번에 반환은 불가
( Oracle 에서는 FULL OUTER JOIN 지원됨 )

단, union( 합집합 ) 이용하면 반환 가능함.
       select 
      from  테이블1  LEFT OUTER JOIN  테이블2  using(공통컬럼)|on 절
       union
       select 
       from  테이블1  RIGHT OUTER JOIN  테이블2  using(공통컬럼)|on 절;

3) 검색조건

SELECT // 3번
FROM 테이블1 LEFT OUTER JOIN 테이블2 USING(공통컬럼) | ON 절 // 1번 조인조건
WHERE 조건식; // 2번 검색조건

#########################################################################

9. 서브쿼리 ( subquery )

1) 개념(목적)
- 한 번의 select 문으로는 원하는 결과를 못 얻을 때,
  여러 개의 select 문으로 사용해서 원하는 결과를 얻을 수 있음.

2) 문법
ex)  emp 테이블에서 SCOTT 사원보다 많은 급여를 받는 사원의 이름을 검색하시오.

가. scott 의 sal?   3000

select sal
from emp
where ename = 'scott';

나. 3000 보다 많은 급여 사원?

select *
from emp
where sal > 3000;

==> 문법은 3000 위치에 SELECT 문으로 치환하자.

# main query
SELECT * 
FROM emp
WHERE sal >  # sub query
     ( SELECT sal
      FROM emp
      WHERE ename = 'scott' );

3) 특징
- 소괄호를 사용
- 일반적으로 소괄호를 감싼 subquery 가 먼저 실행되고 실행된
  결과를 가지고 mainquery 사용됨. ( main query 가 먼저 실행될 수도 있음 )
- main query 의 where 절에 사용가능.
  하지만 거의 모든 sql 에 사용이 가능하다.
  from, having, select, insert, delete, update, create 모두 사용 가능. 
- main query 에서만 order by 사용 가능

4) 주의할 점은 사용되는 연산자가 2가지가 있음.

가. 단일행 연산자
- subquery 실행결과가 하나의 행만 반환되는 경우에 사용되는 연산자 의미.
- 비교 연산자 ( =, >, >=, <, <= )

나. 복수행 연산자
- subquery 실행결과가 여러 행 반환되는 경우에 사용되는 연산자 의미
- IN, > any, > all , < any, < all, exists 


5) 복수행 연산자

가. > all : 서브쿼리의 최대값 보다 큰 값을 반환함.
            : max( sal ) 로도 구현 가능

ex)
SELECT
FROM
WHERE sal > all (   SELECT sal
FROM emp  );

SELECT
FROM
WHERE sal > (  SELECT  max( sal )
FROM emp );

SELECT
FROM
WHERE sal > all (   100
200
300  );

나. < all : 서브쿼리의 최소값 보다 작은 값을 반환함.
           : min( sal ) 으로도 구현 가능

ex)
SELECT
FROM
WHERE sal < all (   SELECT sal
FROM emp  );

SELECT
FROM
WHERE sal < (  SELECT  min( sal )
FROM emp );

SELECT
FROM
WHERE sal < all (    100
 200
 300  );


다. > any : 서브쿼리의 최소값보다 큰 값을 반환함.
: min( sal )

ex)
SELECT
FROM
WHERE sal > any (   SELECT sal
FROM emp  );

SELECT
FROM
WHERE sal >  (  SELECT  min( sal )
FROM emp );

SELECT
FROM
WHERE sal > any (   100
 200
 300  );

라. < any  : 서브쿼리의 최대값보다 작은 값을 반환함.
 : max( sal )

ex)
SELECT
FROM
WHERE sal < any (   SELECT sal
FROM emp  );

SELECT
FROM
WHERE sal <  (  SELECT  max( sal )
FROM emp );

SELECT
FROM
WHERE sal < any (   100
 200
 300  );


> all : 최대값보다 큰 값 반환, ( > ( select max(sal) ... )
< all : 최소값보다 작은 값 반환, ( < ( select min(sal) ... )
> any : 최소값보다 큰 값 반환, ( > ( select min(sal) ... )
< any : 최대값보다 작은 값 반환, ( < ( select max(sal) ... )

마. exists

문법:
SELECT
FROM
WHERE EXIST ( 서브쿼리 )

서브쿼리가 실행후 결과가 있으면 main query 를 실행하고
서브쿼리가 실행후 결과가 없으면 main query 를 실행안함.



10. 인라인 뷰 ( inline view )

1) 개념
메인쿼리에 from 절에 오는 서브쿼리를 의미한다.

2) 문법
SELECT
FROM ( subquery ) 별칭
WHERE ~ ;

3) 용도
- 성능이슈

 

  예>
   --  emp 와 dept 테이블에서 부서별 sal총합과 평균을 출력?
select  e.deptno, sum(sal), ROUND(avg(sal)), count(*)
from emp e join dept d on e.deptno = d.deptno  -- emp: 15개와 dept: 4개가 조인에 참여함
group by deptno;

 SELECT e.deptno, total_sum, total_avg, cnt
 FROM (  SELECT deptno, SUM(sal) total_sum, ROUND(AVG(sal)) total_avg,COUNT(*) cnt
                 FROM emp
                 GROUP BY deptno) 
     e JOIN dept d ON e.deptno = d.deptno;  -- 4개의 서브쿼리 dept: 4개가 조인에 참여함

use testdb;

SELECT * FROM information_schema.table_constraints
WHERE TABLE_NAME = 'emp';

SELECT * FROM information_schema.table_constraints
WHERE TABLE_NAME = 'dept';

######################################################

# 조인

# INNER JOIN --------------------------------

-- 1) cross join
SELECT empno, ename, dname
FROM emp CROSS JOIN dept;

-- 2) natural join
SELECT empno, ename, sal, dname, loc, deptno
FROM emp NATURAL JOIN dept;

-- 별칭
SELECT empno, ename, sal, dname, loc, deptno
FROM emp e NATURAL JOIN dept d;

SELECT e.empno, e.ename, e.sal, d.dname, d.loc, d.deptno
FROM emp e NATURAL JOIN dept d;

-- 참고) Oracle 에서는 공통컬럼에 별칭사용불가
SELECT e.empno, e.ename, e.sal, d.dname, d.loc, deptno
FROM emp e NATURAL JOIN dept d;

-- 검색조건
SELECT empno, ename, sal, dname, loc, deptno
FROM emp NATURAL JOIN dept
WHERE deptno = 30;

-- 3) using 절
SELECT empno, ename, dname
FROM emp INNER JOIN dept USING(deptno);

SELECT e.empno, e.ename, d.dname, e.deptno
FROM emp e JOIN dept d USING(deptno);

-- 4) on 절
-- 공통컬럼인 deptno 는 반드시 별칭사용해야 된다.
SELECT empno, ename, dname, loc , e.deptno, sal
FROM emp e JOIN dept d ON e.deptno = d.deptno;

SELECT empno, ename, dname, loc , sal, e.deptno
FROM emp e JOIN dept d ON e.deptno = d.deptno
WHERE sal > 2500;

-- sal 등급 구하기 ( 부등조인: non-equi 조인 )
SELECT `empno`,`ename`,`sal`,`grade`
FROM emp e JOIN salgrade s 
ON e.`sal` BETWEEN s.`losal` AND s.`hisal`;

SELECT `empno`, `ename`, `dname`, `sal`, `grade`
FROM emp e JOIN dept d ON e.`deptno` = d.`deptno`
	       JOIN salgrade s ON e.`sal` BETWEEN s.`LOSAL` AND s.`HISAL`;


-- self 조인
SELECT e.`ename` as '사원', m.`ename` as '관리자'
FROM emp e JOIN emp m ON e.`mgr` = m.`empno`;

# OUTER JOIN --------------------------------

SELECT empno, ename, dname, d.deptno
FROM emp e RIGHT OUTER JOIN dept d ON e.deptno = d.deptno;

SELECT empno, ename, dname, d.deptno
FROM emp e LEFT OUTER JOIN dept d ON e.deptno = d.deptno;

# 신입사원 추가
INSERT INTO emp VALUES ( 9000, 'TEST','SALESMAN', 7499,
'90/01/01', 600, NULL, NULL );
COMMIT;

-- 신입사원과 부서번호 40 같이 출력하자.
SELECT empno, ename, dname, d.deptno
FROM emp e RIGHT OUTER JOIN dept d ON e.deptno = d.deptno
UNION
SELECT empno, ename, dname, d.deptno
FROM emp e LEFT OUTER JOIN dept d ON e.deptno = d.deptno;



# 서브쿼리
# 서브쿼리 정의
-- - SELECT 문 (Mainquery) 에 포함되어 있는 별도 SELECT 문 (Subquery)이다.
-- - 여러 번의 SELECT문을 수행해야 얻을 수 있는 결과를 하나의 중첩된 SELECT
--   문으로 쉽게 얻을 수 있도록 해준다.

-- 1) 단일행 서브쿼리
-- - 서브쿼리가 한 개의 행을 리턴.
-- - 반드시 단일행 연산자를 사용해야 한다. ( = , > , < , >= , <= , != )

-- 샘플) EMP 테이블에서 SCOTT의 급여보다 많은 사원의 사원번호, 이름, 담당업무, 급여
-- 출력 ?
SELECT empno, ename, job, sal
FROM emp
WHERE sal > ( SELECT sal
			  FROM emp
		      WHERE ename = 'scott');

-- 샘플) EMP 테이블에서 사원번호가 7521의 업무와 같고 급여가 7934보다 많은 사원의
-- 사원번호, 이름, 담당업무,입사일자, 급여 출력 ?
SELECT empno, ename, job, sal
FROM emp
WHERE job = ( SELECT job
FROM emp
WHERE empno = 7521 )
AND sal > ( SELECT sal
	        FROM emp
		    WHERE empno = 7934 );
            
-- 샘플) EMP 테이블에서 급여의 평균보다 적은 사원의 사원번호, 이름, 담당업무, 급여,
--      부서번호를 출력 ?
SELECT empno, ename, job, sal, deptno
FROM emp
WHERE sal < (SELECT AVG(sal)
			 FROM emp );
             
-- 샘플) EMP 테이블에서 20번 부서의 최소 급여보다 많은 모든 부서를 출력 ?
SELECT deptno, MIN(sal)
FROM emp
GROUP BY deptno
HAVING MIN(sal) > ( SELECT MIN(sal)
				    FROM emp
					WHERE deptno = 20 );
                    
-- 2) 복수행 서브쿼리
-- - 서브쿼리가 여러 개의 행을 리턴.
-- - 반드시 복수행 연산자를 사용해야 한다. ( IN , ANY ,ALL ,EXISTS )

-- IN 연산자
-- - WHERE 절에서 사용하는 일반 비교연산자와 동일하다.
-- - 메인쿼리의 비교 조건이 서브쿼리의 결과 중에서 하나라도 일치하면 검색가능.

-- 샘플) EMP 테이블에서 업무별로 최소 급여를 받는 사원의 사원번호, 이름, 업무, 입사일자,
--      급여, 부서번호를 출력 ?
SELECT empno, ename, job, hiredate, sal, deptno
FROM emp
WHERE sal IN ( SELECT MIN(sal)
			   FROM emp
			   GROUP BY job );

-- ALL 연산자
-- -복수행 서브쿼리 결과가 메인 쿼리의 WHERE 절에서 부등호 조건으로 비교할 때
--  사용된다. ( 원래 부등호 조건은 단일행에서 사용됨 )
-- -서브쿼리에서 반환되는 행들 전체에 대해 모두 조건을 만족해야 된다.


-- EXISTS
-- 서브쿼리에서 검색된 결과가 하나라도 존재하는지 여부를 확인할 때 사용된다.
-- 만일 서브쿼리에서 검색된 결과가 하나도 없으면 메인 쿼리의 조건절이
-- 거짓이 메인 쿼리가 실행되지 않고 결과가 하나라도 있으면 메인 쿼리가 실행된다.

SELECT empno, ename, job, hiredate, sal, deptno
FROM emp
WHERE EXISTS ( SELECT empno
			   FROM emp
			   WHERE comm IS NOT NULL );
               
               
SELECT empno, ename, job, hiredate, sal, deptno
FROM emp
WHERE EXISTS ( SELECT empno
			   FROM emp
			   WHERE sal > 10000 ); -- 메인쿼리 실행x

-- 다중컬럼 서브쿼리
-- 서브쿼리에서 여러개의 컬럼값을 검색하여 메인쿼리의 조건절과 비교하는 서브쿼리이다.
-- 메인쿼리의 조건절에서도 서브쿼리의 컬럼수만큼 지정해야 된다.
-- - 컬럼을 쌍으로 묶어서 동시에 비교한다.

-- 샘플) 부서별로 가장 많은 sal을 받는 사원 정보 출력 ? 
SELECT deptno, empno, ename, sal
FROM emp
WHERE (deptno, sal ) IN ( SELECT deptno, MAX(sal)
FROM emp
GROUP BY deptno);

-- 인라인뷰
-- from절 뒤에 테이블 명이 나와야 되지만, 서브 쿼리가 하나의 가상 테이블을 반환하는
-- 형태로 사용되는 경우를 의미한다.
-- 메인쿼리에 from 절에 오는 서브쿼리를 의미한다.

-- 샘플> emp 와 dept 테이블에서 부서별 sal 총합과 평균을 출력?
SELECT e.`deptno`, SUM(sal), ROUND(AVG(sal)), COUNT(*)
FROM emp e JOIN dept d ON e.deptno = d.deptno
GROUP BY deptno; -- emp : 15개와 dept: 4개가 조인에 참여.


SELECT e.deptno, total_sum, total_avg, cnt
FROM ( SELECT deptno, SUM(sal) total_sum, ROUND(AVG(sal)) total_avg,
	          COUNT(*) cnt
	   FROM emp
	   GROUP BY deptno) 
e JOIN dept d ON e.deptno = d.deptno; -- 4개의 서브쿼리 dept: 4개가 조인에 참여.