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 ~ using ( 공통컬럼)

- join ~ on : 범용적

- self join ==> 자신이 자신을 조인하는 방법
                      ex) 사원의 관리자명 조회

# outer 조인방법
- left outer join ~ on | using
- right outer join ~ on | using
==> 양쪽 테이블에 누락된 레코드 조회는 union(합집합) 으로 가능하다.


- 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개가 조인에 참여함.

 

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

인덱스 ( B-tree 인덱스 )

1) 테이블 정렬( 오름차순 )
ex) emp 의 empno

1
2
3
...
50
...
100
=> 인덱스 50을 기준으로 작은인덱스 인지 큰인덱스인지 파악해서 찾는 값을 더 빠르게 찾을수 있다.
     인덱스에는 주소값을 가지고있는데 관련된 데이터가 있는 곳으로 바로 이동한다.
      만약 50이후의 숫자를 삭제한다면 남은 인덱스에서 중간값을 기준으로 계속 균형있게 작업한다.


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