[신세계I&C KDT][MySQL] #3 제약 조건, 조인 Join (0401)

1. 제약조건 (constraints) ★

1) 용도

: 테이블에 저장되는 데이터 검증용 (무결성 데이터 보장)
: 테이블의 컬럼 단위로 지정
: 제약조건 정보 보는 방법

select *
from information_schema.table_constraints
where table_name = 'emp';

2) primary key (pk)

목적 : 레코드 식별용
특징 : 값이 존재 (null 값 불가. not null 제약 조건)
중복 불가 (unique 제약 조건)
테이블당 하나만 지정 가능
복합 컬럼 가능 (예> 수강테이블에서 (학번, 과목))
추가되는 제약 조건 add
자동으로 인덱스 (색인표 역할) 추가됨

3) unique

목적 : 중복값 저장 불가
특징 : 값이 없어도 됨 (null 값 허용)
레코드 식별용으로 사용 불가
테이블당 여러 개 설정 가능
복합컬럼 가능
추가되는 제약 조건 add
자동으로 인덱스 추가됨

4) check

목적 : 비즈니스 로직 검증 역할로서 저장되는 데이터의 범위를 제한한다
예> gender check (gender in ('남', '여'));

5) foreign key (fk, 참조키, 외래키)

목적 : 다른 테이블 참조 (2개 테이블의 연결 고리)
특징 : child - parent 테이블 관계
반드시 외래키는 parent 테이블의 primary key, unique 지정된 컬럼만 참조할 수 있다
null 값 가능
추가되는 제약 조건 add

6) not null

목적 : null 허용 불가 (반드시 값을 가져야 됨. 중복 가능)
특징 : 기본적으로 모든 테이블의 컬럼은 null을 허용
null -----(변경)------> not null
변경되는 제약 조건 modify

조인 기본 동작

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

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

2. 조인 (join) ★

https://docs.oracle.com/cd/E17952_01/mysql-8.0-en/join.html

1) query 작업 (select 문)

• selection : 행 선택, where 절 이용
• projection : 컬럼 선택, select 절 이용
• join : 여러 테이블 연결

2) 개념

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

3) 조인 방법 종류 1

(1) ANSI 조인

• DBMS 독립적

(2) 비 ANSI 조인

• DBMS 의존적
  예> Oracle 조인

4) 조인 방법 종류 2

(1) inner 조인

: 2개의 테이블 연결시 반드시 일치되는 데이터만 반환
일치하지 않으면 누락됨

• cross join
• natural join
• join ~ on
• join ~ using(공통컬럼)
• self join : 자기 자신과 조인

(2) outer 조인

: inner 조인 + 일치되지 않는 데이터까지 포함해서 반환

• left outer join ~ on | using
• right outer join ~ on | using
• full outer join ~ on | using (Oracle에서 지원됨. mysql에서는 union으로 쓰기)

3. cross join

1) 문법:

select
from 테이블1  cross join 테이블2;

2) 동작

테이블1의 하나의 행과 테이블2의 여러 행과 조인됨.
따라서 테이블1의 행 개수 * 테이블2의 행 개수한 결과가 나옴

3) 특징

• 데이터로 사용 불가
• 조인할 때는 조인 조건을 생략하거나 잘못 지정한 경우에는
  잘못된 결과를 반환할 수 있다
• 테이블이 n개인 경우 반드시 n-1 개의 조인이 필요하다.

4. natural join

1) 문법

SELECT 컬럼명
FROM 테이블1 별칭 NATURAL JOIN 테이블2 별칭; // 별칭에 as 사용 불가

2) 동작

• 테이블1과 테이블2에서 공통 컬럼을 찾아 조인한다
• 만약 공통 컬럼이 2개라면 쌍으로 비교한다

3) 특징

• 반드시 공통 칼럼이 존재해야 된다
• 단점은 어떤 컬럼으로 조인했는지 추가로 확인하는 작업이 필요하다 (가독성 떨어짐)
• inner 조인

4) 검색 조건

• where 절 이용

SELECT 컬럼명
FROM 테이블1 별칭 NATURAL JOIN 테이블2 별칭
WHERE 조건식;

5. using 절

1) 문법 :

select
from 테이블1 [inner] join 테이블2 using(공통컬럼);

select 별칭.컬럼명,...., 별칭.공통컬럼
from 테이블1 별칭 join 테이블2 별칭 using(공통컬럼);

2) 동작

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

3) 특징

• using절 지정하는 공통컬럼에는 별칭사용 불가
  예> using(별칭.공통컬럼); // 에러
• inner 조인

4) 검색 조건 지정 가능

select                                  // 3번
from 테이블1  join 테이블2 using(공통컬럼)   // 1번 조인조건
where 조건식;                            // 2번 검색조건

====> natural join 또는 using(공통컬럼) 모두 공통컬럼이 반드시 존재해야 됨

6. on 절

: 가장 범용적으로 사용됨

1) 문법

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

2) 동작

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

3) 특징

• 공통 컬럼은 반드시 별칭 사용해야됨 (using절과 반대)

4) 검색 조건

SELECT 
FROM 테이블1 [INNER] JOIN 테이블2 ON 테이블1.컬럼 = 테이블2.컬럼
WHERE 조건식;

7. self 조인

1) 개념

• 자신이 자신을 조인하는 개념

2) 구현 방법

• 별칭 이용

3) 샘플 예

• 사원명과 관리자명 출력하기

가상 테이블  emp e                 가상테이블  emp m

 empno ename  mgr                  empno ename
 ----------------                ---------------
 7369  SMITH  7902                  7369  SMITH
                                    7902  FORD

select *
from emp e join emp m ON e.mgr = m.empno;

8. OUTER 조인

1) 개념

: 조인 조건이 일치한 행만 반환하는 것은 INNER 조인. 이 때 일치하지 않는 행은 누락됨. 누락된 행까지 포함해서 반환할 수 있는 방법 OUTER 조인이다

2) 문법

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

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

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

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

cf) 테이블1과 테이블2의 누락된 데이터를 모두 같이 보려면 FULL OUTER JOIN을 써야된다. 하지만 Oracle 에서만 가능 !!
아니면 UNION (합집합) 이용해도 가능하다

9. 서브쿼리

1) 개념 (목적)

• 한 번의 select문으로는 원하는 결과를 못 얻을 때, 여러 개의 select문을 사용해서 원하는 결과를 얻을 수 있다

2) 문법

select *
from emp   
where sal >  (select sal  
	          from emp
	          where ename = 'scott');

3) 특징

• 대부분 소괄호로 감싼 서브쿼리가 먼저 실행되고 그 결과를 가지고 메인쿼리에 사용한다
• 거의 모든 sql에 사용 가능
  (where, from, having, select, insert, delete, update, create)
• 메인쿼리에서만 order by 사용 가능

4) 주의할 점

: 사용되는 연산자가 2가지

(1) 단일행 연산자

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

(2) 복수행 연산자

• 서브쿼리가 여러 개의 행을 리턴
• 반드시 복수행 연산자를 사용해야 한다. (IN, ANY, ALL, EXISTS)

5) 복수행 연산자

(1) > all

: 서브쿼리 값의 최대값보다 큰 값을 반환함
: MAX(SAL)로도 구현 가능

SELECT 
FROM
WHERE SAL > ALL (select sal
				 from emp);
SELECT 
FROM
WHERE SAL > ALL (100
				200
                300 );

(2) < all

: 서브쿼리 값의 최소값보다 작은 값을 반환함
: MIN(SAL)로도 구현 가능

SELECT 
FROM
WHERE SAL < ALL (select sal
				 from emp);
SELECT 
FROM
WHERE SAL < ALL (100
				200
                300 );

(3) > any

: 서브쿼리 값 중 하나라도 만족하는 것을 반환 (서브쿼리의 최소값보다 큰 값을 반환)
: MIN(SAL)로도 구현 가능

SELECT 
FROM
WHERE SAL > any (select sal
				 from emp);
SELECT 
FROM
WHERE SAL > any (100
				200
                300 );

(4) < any

: 서브쿼리의 최대값 보다 작은 값을 반환
: MAX(SAL)로도 구현 가능

SELECT 
FROM
WHERE SAL < any (select sal
				 from emp);
SELECT 
FROM
WHERE SAL < any (100
				200
                300 );

(5) exists

: 서브쿼리에서 검색된 결과가 하나라도 존재하는지 여부를 확인할 때 사용
문법

SELECT *
FROM 
WHERE EXISTS (서브쿼리);

-> 서브쿼리의 결과가 있으면 메인쿼리 실행

10. 인라인 뷰 (inline view)

1) 개념

메인쿼리에서 from 절에 오는 서브쿼리를 의미한다

2) 문법

SELECT
FROM (서브쿼리) 별칭
[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
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개가 조인에 참여함