같은 컬럼일 경우,
BETWEEN : AND
IN : OR
우선순위 : OR보다 AND가 먼저, AND보다 IN이 먼저
■TCL(Transaction Control Language) : 트랜잭션 제어어
■트랜잭션
하나의 서비스를 구현하기 위해서 작성했던 쿼리들의 묶음(쿼리가 하나일 수도 여러개 일수도 있음), 하나의 단위
하나의 작업 단위
여러 개의 SQL문의 묶음
1.COMMIT
모든 작업(트랜잭션)을 확정하는 명령어
2.ROLLBACK
이전 커밋 시점으로 되돌아가는 명령어
DDL 복구 안됨 : TRUNCATE, DROP
DML 복구 됨 : DELETE
TCL은 DML과 관련됨.
AUTO >> 자동으로 커밋되도록 설정
**
TRUNCATE는 테이블 내용을 전체 삭제하므로, DELETE보다 빠르게 처리할 수 있다.
대용량 데이터 처리에 유리하지만 복구가 불가능하기 때문에
복구가 가능한 DELETE를 사용하는 것이 좋다.
-------------------------------------------------------------------------------
■정규화
삽입/수정/삭제의 이상현상을 제거하기 위한 작업
데이터의 중복을 최소화하는 데에 목적이 있다.
6차 정규화까지 있으나 3차 정규화까지만 진행한다.
■1차 정규화
같은 성격과 내용의 컬럼이 연속적으로 나타날 경우
| 상품명1 |
상품명2 |
상품명3 |
| 바지1 |
바지2 |
바지3 |
* 조회가 힘들다.
-1차 정규화 진행
■2차 정규화
조합키(복합키)로 구성되었을 경우 조합키의 일부분에만 종속되는 속성이 있을 경우
| FLOWER |
|
|
|
| 이름P |
색상P |
꽃말 |
과 |
| 수국 |
파란색 |
냉정 |
범의과 |
| 개나리 |
노란색 |
희망 |
풀무레나무과 |
과만 보고도 꽃이름을 알 수 있으니까 과는 2차 정규화 대상임.
조합키니까 이름과 색상을 봐야 꽃말과 과를 알 수 있어야 함.
* 이름으로만 가지고 과를 알 수 있다.(일부만 가지고도 알 수 있다.)
-2차 정규화 진행
| FLOWER |
|
|
| 이름P |
색상P |
꽃말 |
| 수국 |
파란색 |
냉정 |
| 개나리 |
노란색 |
희망 |
| FLOWER_TYPE |
|
| 이름P |
과 |
| 수국 |
범의과 |
| 개나리 |
풀무레나무과 |
■3차 정규화
PK가 아닌 컬럼이 다른 컬럼을 결정하는 경우
이행함수 종속을 제거해야 한다.
| 회원번호 |
이름 |
시 |
구 |
동 |
우편번호 |
| 1 |
가나다 |
천안시 |
동남구 |
다가동 |
12345 |
| 2 |
마바사 |
천안시 |
서북구 |
두정동 |
98765 |
*우편번호가 시,구,동을 결정한다.
- 3차 정규화 진행
| 회원번호P |
이름 |
우편번호 |
| 1 |
가나다 |
12345 |
| 2 |
마바사 |
98765 |
| 우편번호P |
시 |
구 |
동 |
| 12345 |
천안시 |
동남구 |
다가동 |
| 98765 |
천안시 |
서북구 |
두정동 |
다른 컬럼과의 관계를 끊어주고, PK로만 컬럼을 구분해야 한다.
-----------------------------------------------------------------------
■데이터베이스에서 정규화가 필요한 이유
데이터베이스를 잘 못 설계하면 불필요한 데이터 중복으로 인해 공간이 낭비된다.
이런 현상을 이상(Anomaly)현상이라고 한다.
회원번호와 프로젝트코드 두 컬럼의 조합키로 설정되어 있는 테이블이고
한 사람은 하나의 부서만 가질 수 있다.
| 회원번호P |
이름 |
부서 |
프로젝트 코드P |
급여 |
부서별 명수 |
| 20212013 |
김개발 |
개발팀 |
ABC0001 |
3000 |
4 |
| 20212013 |
김개발 |
개발팀 |
DEF0002 |
2000 |
4 |
| 20212013 |
김개발 |
개발팀 |
CBA0003 |
4000 |
4 |
| 20171011 |
박기획 |
기획팀 |
FED2125 |
6000 |
2 |
| 20193020 |
송그림 |
디자인팀 |
DEF3214 |
5000 |
3 |
■이상현상의 종류
- 삽입 이상
새 데이터를 삽입하기 위해 불필요한 데이터도 삽입해야 하는 문제
담당 프로젝트가 정해지지 않은 사원이 있다면,
프로젝트 코드에 NULL을 작성할 수 없으므로 이 사원은 테이블에 추가될 수 없다.
따라서 '미정'이라는 프로젝트 코드를 따로 만들어서 삽입해야 한다.
- 갱신 이상
중복 행 중에서 일부만 변경하여 데이터가 불일치하게 되는 모순의 문제
한 명의 사원은 반드시 하나의 부서에만 속할 수 있다.
만약 "김개발"이 보안팀으로 부서를 옮길 시 3개 모두 갱신해주지 않는다면
개발팀인지 보안팀인지 알 수 없다.
- 삭제 이상
행을 삭제하면 꼭 필요한 데이터까지 함께 삭제되는 문제
"송그림"이 담당한 프로젝트를 박살내서 드랍된다면 "이순신" 행을 모두 삭제하게 된다.
따라서 프로젝트에서 드랍되면 정보를 모두 드랍하게 된다.
이러한 이상 현상이 발생하는 이유는 테이블이 정규화가 되어 있지 않기 때문이다.
정규화를 진행하기 위해서는 각 컬럼간의 관련성을 파악해야 하고,
이 관련성을 "함수적 종속성(Functional Dependency)"이라고 한다.
따라서 하나의 테이블에서는 반드시 하나의 함수적 종속성만 존재하도록 정규화를 진행한다.
함수
X -> Y
X : 결정자
Y : 종속자
Y가 X에 의존한다.
---------------------------------------------------------------------
1차 정규화 > PASS
2차 정규화(조합키)
| 회원번호P |
프로젝트 코드P |
급여 |
| 20212013 |
ABC0001 |
3000 |
| 20212013 |
DEF0002 |
2000 |
| 20212013 |
CBA0003 |
4000 |
| 20171011 |
FED2125 |
6000 |
| 20193020 |
DEF2314 |
5000 |
| 회원번호P |
이름 |
부서 |
부서별 명수 |
| 20212013 |
김개발 |
개발팀 |
4 |
| 20171011 |
박기획 |
기획팀 |
2 |
| 20193020 |
송그림 |
디자인팀 |
3 |
3차 정규화
X->Y->Z (X)
X->Y (O)
| 회원번호P |
이름 |
부서F |
| 20212013 |
김개발 |
개발팀 |
| 20171011 |
박기획 |
기획팀 |
| 20193020 |
송그림 |
디자인팀 |
| 20212003 |
오코딩 |
개발팀 |
| 부서P |
부서별 명수 |
| 개발팀 |
4 |
| 기획팀 |
2 |
| 디자인팀 |
3 |
---------------------------------------------------------------------
★NULL
정의되지 않은 값
빈 값 대신 미정 값을 부여할 때 사용
PK는 불가능, FK는 가능, UK는 가능
NOT NULL 제약조건
ALTER TABLE [테이블명] MODIFY NOT NULL
//NULL을 만드는 게 아니라 NULL칸에 체크하는 걸로 수정한다고 봐야 함.> MODIFY
■제약조건 삭제
ALTER TABLE [테이블명] DROP CONSTRAINT [제약조건 이름]
■조건식
컬럼명 IS NULL : 컬럼 값이 NULL이면 참
컬럼명 IS NOT NULL : 컬럼 값이 NULL이 아니면 참
■NULL 값이 다른 값으로 변경
NVL(컬럼명, '값') : NULL 값 대신 다른 값으로 변경 후 조회
NVL2(컬럼명, 'NULL이 아닐 때 값', 'NULL일 때 값') : NULL일 때의 값, NULL이 아닐 때의 값을 각각 설정
//보여지는 결과 테이블에 NULL 대신 다른 원하는 값으로 보여짐. 원본 데이터 업데이트X
결과 테이블에서 표현하는 것만 바뀌는 것임.
//보여지는 게 바뀌는 것이기 때문에 항상 SELECT 뒤에서 사용.
★AS(ALIAS) : 별칭
SELECT : 컬럼명 뒤에 AS 또는 한 칸 띄어쓰고 작성한다.
CONCATENATINO : 연결, ||
- JAVA에서 +와 동일하다
★LIKE : 포함된 문자열 값을 찾고, 문자의 개수도 제한을 줄 수 있다.
- JAVA에서 CONTAINS와 동일하다
[컬럼명] LIKE '';
% : 모든 것
_ : 글자 수
예)
'%A' : A로 끝나는 모든 값(EWINVODA, OXPA, ...)
'A%' : A로 시작하는 모든 값(ASDOF, APPLE, ...)
'%A%' : A가 포함된 모든 값(SLDASDKF, AISJDF, SKNA, ...)
'A___' : A로 시작하며 3글자인 값(ABC, ADD, APP, ...)
'_A' : A로 끝나며 2글자인 값(BA, CA, ...)
★집계 함수 : 결과 1개
※ 주의사항 : NULL은 포함시키지 않는다.
※ WHERE절에서 사용 불가
평균 : AVG(컬럼)
최대값 : MAX(컬럼)
최소값 : MIN(컬럼)
총 합 : SUM(컬럼)
개수 : COUNT(컬럼)
★정렬
ORDER BY 컬럼명, ... ASC : 오름차순
ORDER BY 컬럼명, ... DESC : 내림차순
생략하면 오름차순
★GROUP BY : ~별 (예: 포지션 별 평균 키)
GROUP BY 컬럼명 HAVING 조건식
※ WHERE절에서 우선적으로 처리할 조건식을 작성해야 속도가 빠르다.
WHERE절 먼저
HAVING 에서는 집계 함수 쓸 수 있음.
//ORDER BY 1 : 회사에서 쓰면 안 됨.
★SUB QUERY
FROM절 : IN LINE VIEW
SELECT절 : SCALAR SUB QUERY
WHERE절 : SUB QUERY
-----------------------------------------------------------
★JOIN
여러 테이블이 흩어져 있는 정보 중 사용자가 필요한 정보만 가져와서 가상의 테이블처럼 만들고 결과를 보여주는 것.
정규화를 통해 조회 테이블이 너무 많이 쪼개져 있으면 작업이 불편하기 때문에
입력, 수정, 삭제의 성능을 향상시키기 위해서 JOIN을 통해서 합친 후 사용한다.
■내부 조인(INNER JOIN)
조건이 일치하는 값만 합쳐서 조회
//조건이 일치 하지 않는다? 외부 조인, 잘 사용 안함.
FROM
[테이블명A] (INNER) JOIN [테이블명B]
ON [조건식]
INNER 생략 가능
1.등가 조인
조건식에 = 있으면 등가 조인
2.비등가 조인
조건식에 = 없으면 비등가 조인
SQL 실행 순서
FROM > ON > JOIN > WHERE > GROUP BY > HAVING > SELECT > ORDER BY
//데이터의 양이 적은 걸 선행으로 적어야 함.
선행과 후행의 관점은 데이터의 행의 개수가 적은 것을 앞에다 쓰는 것이 효율적인 코드이다.
BETWEEN A AND B : 이상, 이하
