오토에버 클라우드 2기 7일차
조인에는 크게 2가지 종류가 있다 ANSI JOIN으로 SQL의 표준으로 사용하는 ANSI JOIN과 이를 좀 더 편하게 사용하기 위해서 나온 ORACLE JOIN이 존재한다
먼저 ANSI JOIN의 종류를 보면 7가지가 된다
ANSI JOIN
1. INNER JOIN(JOIN)
INNER JOIN은 각 테이블에서 조건이 일치하는 경우만 데이터를 가져온다 보통 다이어그램에서 교집합을 의미한다
select * from DEPT inner join EMP
on DEPT.DEPTNO = EMP.DEPTNO;
#만약 ON 뒤 칼럼 이름이 같다면 USING(DEPTNO)이렇게도 사용 가능
조건에 부합하는 데이터만 가져온다
2. OUTER JOIN
조건에 일치하지 않는 데이터라도 가져온다 만약 조건에 일치하는 데이터가 없다면 NULL을 반환한다
LEFT JOIN(LEFT OUTER JOIN): 왼쪽 테이블을 기준으로 왼쪽에는 데이터가 있지만 오른쪽 테이블에 데이터가 없다면 NULL을 넣는다
RIGHT JOIN(RIGHT OUTER JOIN): 오른쪽 테이블을 기준으로 오른쪽에는 있지만 왼쪽 테이블에 데이터가 없다면 NULL을 넣어서 데이터를 출력한다
FULL OUTER JOIN: 양쪽 테이블 모두가 기준이 되어서 왼쪽 테이블에만 존재하면 오른쪽에 NULL, 오른쪽 테이블에만 존재하면 왼쪽에 NULL을 넣은 결과를 출력한다
마리아나DB는 이 연산을 지원하지 않아 SET연산으로 이 기능을 구현
3. CROSS JOIN
select * FROM EMP
cross join
DEPT;
두 테이블의 행*행의 경과를 출력한다
4. SELF JOIN
하나의 테이블을 가지고 조인 하나의 테이블에 동일한 의미를 가지는 컬럼이 2개 이상인 경우 사용 EMP테이블에서 EMPNO는 사원 번호 MGR관리자 사원 번호인 경우 사원의 이름과 관리자의 이름을 동시에 조회하고자 하는 경우에 사용
1
2
3
4
5
SELECT *
FROM EMP E1
JOIN
EMP E2
ON E1.MGR = E2.EMPNO;
ORACLE JOIN
오라클 조인은 가져오고자 하는 컬럼이 포함된 테이블을 모두 FROM에 작성한다
1.INNER JOIN
2개의 테이블에 동일한 의미를 갖는 컬럼이 있는 경우 두 개의 컬럼이 같은 값만 조인한다 where절에 조인 조건을 기술
1
2
3
select * # 여기서 출력할때 공통된 부분이 존재한다면 새이름을 지정하고 별칭 또는 테이블 이름을 이용해 출력
from emp e, dept d
where e.deptno = d.deptno
만약 조회하고자 하는 컬럼들이 하나의 테이블에 존재하는 경우는 조인 대신 서브 쿼리를 이용하는 것이 효율적이다
2. OUTER JOIN
LEFT JOIN: 왼쪽테이블 기준, 기준이 아닌 테이블 뒤
(+)를 작성SELECT * FROM EMP E, TEST T WHERE E.A = T.A(+);RIGHT JOIN: 오른쪽 테이블 기준
SELECT * FROM EMP E, TEST T WHERE E.A(+) = T.A;
3. CROSS JOIN
select *
from emp e1, emp e2
4. SELF JOIN
1
2
3
select e1.ename, e2.ename
from emp e1, emp e2
where e1.mgr = e2.empno;
다른 분류
위 경우가 아니라 join을 다르게 분류 할 수 있는데 EQUI JOIN과 NON EQUI JOIN가 존재한다
두 테이블간의 조인 조건이 =인 경우에 EQUI JOIN이라고 한다
NON EQUI JOIN
두개의 테이블간의 칼럼 값이 정확하게 일치하지 않을 때 사용한다
1
2
3
select ename, sal, GRADE
from EMP, SALGRADE
where SAL between LOSAL and HISAL;
| JOIN 분류 | 설명 | 예시 문법 |
|---|---|---|
| EQUI JOIN | 조인 조건이 등가(=) 비교인 경우 — 대부분의 INNER JOIN, LEFT/RIGHT JOIN 등에서 기본적으로 사용 | … ON A.col = B.col |
| NON‑EQUI JOIN | 조인 조건이 등가 비교가 아닌 경우 — 부등호( <,>,<=,>=), BETWEEN, LIKE 등을 사용하는 조인 | … ON A.value BETWEEN B.min_val AND B.max_val |
DB 설계
데이터 설계시 주의
- 삽입이상: 데이터를 삽입 할 때 삽입하지 못하는 현상
- 삭제 이상: 삭제할 때 필요한 정보도 같이 삭제되는 현상
- 변경 이상: 데이터를 수정할 때 한 번만 수정하면 되는데 여러 번 수정해야 하는 현상
보통 이상현상은 테이블을 너무 크게 만들어서 발생한다
함수적 종속
어느 하나의 속성이 다른 속성의 데이터를 1개로 구별할 때 함수적으로 종속한다 ex) 주민등록 번호를 알면 이름을 알 수 있다 주민등록번호는 이름을 함수적으로 종속한다고 하고 “주민등록번호 → 이름”으로 표기
- 완전 함수적 종속: 기본키가 여러개의 속성으로 구성될 때 기본키 전체를 알아야 하나의 값을 결정
- 부분 함수적 종속: 기본키가 여러개의 속성으로 구성될 때 기본키의 일부만으로 하나의 값을 결정
- 이행적 함수적 종속: 하나의 속성이 다른 하나의 속성의 값을 결정하고 결정되는 속성이 또다른 속성의 값을 결정하는 경우
정규화
논리 데이터 모델을 일관성이 있고 안정성 있는 자료 구조로 만드는 과정
데이터를 분해하는 과정으로 중복 값이 줄어들고 삽입, 삭제, 갱신에 유리
- 제 1정규형: 모든 속성 값은 원자값이다
- 제 2정규형: 1정규형에서 부분 함수적 종속 제거
- 제 3정규형: 2정규형에서 이행 함수적 종속 제거
- BCNF: 3정규형에서 모든 결정자(함수적 종속에서 왼쪽편의 속성)를 후보키(속성의 개수를 최소로 하는 슈퍼키)로 만드는 작업
문제점
빈번한 join연산 증가, 부자연스러운 DB semantic 초래, 조회/검색 위주의 응용 시스템에 부적합
따라서 업무 특성과 성능 향상을 위해서 역정규화를 진행하기도 한다
반정규화
시스템의 성능 향상 및 개발과 운영의 단순화를 위해 중복, 통합, 분리등을 수행하는 데이터 모델링 기법
통계 테이블 추가, 이력 테이블 추가, 부분 테이블 추가
- 파티셔닝(하나의 테이블 데이터를 분리 열단위, 행단위)
- 범위 분할, 해시 분할, 목록 분할, 합성 분할(위 방식으 조합)
테이블 생성
| 카테고리 | 주요 자료형 | 설명 |
|---|---|---|
| 1. 수치형 (Numeric) | TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT, DECIMAL, FLOAT, DOUBLE, BIT | 정수, 고정소수점, 부동소수점, 비트 필드 등 숫자 연산에 사용([MariaDB][1]) |
| 2. 문자형 (String / Text) | CHAR, VARCHAR(가변), TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT, ENUM, SET | 고정/가변 길이 문자열, 열거형, 집합형 등 텍스트 저장([MariaDB][2]) |
| 3. 날짜/시간형 (Date & Time) | DATE, DATETIME, TIMESTAMP, TIME, YEAR | 날짜·시간 저장 및 계산용([TutorialsPoint][3]) |
| 4. 이진형 (Binary) | BINARY, VARBINARY, TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB | 바이너리 데이터(이미지, 파일 등) 저장([MariaDB][2]) |
| 5. 부울형 (Boolean) | BOOLEAN, BOOL (→ TINYINT(1) alias) | True/False용 (0/1)([MariaDB][1]) |
| 6. JSON | JSON (alias of LONGTEXT) | JSON 문서 저장, 쿼리 가능([MariaDB][2]) |
| 7. 공간형 (Spatial) | GEOMETRY, POINT, LINESTRING, POLYGON, MULTIPOINT, … | 지리공간(위치) 데이터 저장([아이오노스][4]) |
option
- auto_increment=초기값 : 초기 값 설정 자동 증가
- DEFAULT CHARSET=<캐릭터셋> :설정하지 않는다면 한글 불가능할 수 도 있음 `DEFAULT CHARSET=utf8mb4`
컬럼 추가
alter table 테이블이름 add 컬럼이름 자료형 [first 또는 after 컬럼이름]
컬럼 삭제
alter table 테이블이름 drop 컬럼이름
컬럼 수정
이름과 자료형을 변경 alter table 테이블이름 change 이전컬럼이름 새로운컬럼이름 자료형
자료형 변경 alter table 테이블이름 modify 컬럼이름 자료형
not null을 수정할 때도 자료형 변경 명령으로 수행한다
테이블 이름 수정
alter table 이전테이블이름 rename 새로운테이블이름
제약 조건
테이블의 무결성 유지를 위해서 지켜야 하는 조건
not null: 필수입력
- check 제약조건: 대입되는 값을 점검하기 위한 제약조건
1 2 3 4
create table smaple( gender varchar(1) check(gender='M' or gender='F'), age int check(age >= 1 and age <= 100) );
unique: 데이터 값이 1개만 존재하도록 하는 제약조건 이 제약 조건이 설정된 곳에는 null인된다는 것이 기본키와 다른점 컬럼 제약조건, 테이블 제약조건
primary key: 기본키 NOT NULL이고 UNIQUE하다
foreign key: 외래키 또는 참조키로 다른 테이블의 데이터를 참조하기 위해서 설정하는 키 현재 테이블에서는 어떤 컬럼이든 외래키가 될 수 있지만 이 컬럼은 다른 테이블에서 unique거나 primary key여야 한다
- 외래키 옵션:
ON DELETE {NO ACTION | CASCADE | SET NULL}참조 당하는 데이터가 삭제될 때 no action 아무것도 안함, cascade 참조 데이터도 같이 삭제, set null 참조 데이터 null로 변경 - ON UPDATE는 수정될 때
- 외래키 옵션: