[SQL, SQLD] 노랑이 책 정리 - II. SQL 기본 및 활용

이수빈·2023년 7월 13일

SQL

목록 보기

3/3

I-1. SQL 기본

[1 - 5] 관계형 DB 개요 및 명령어

DML SELECT INSERT, DELETE, UPDATE
DDL CREATE ALERT RENAME DROP
DCL REVOKE GRANT
TCL COMMIT ROLLBACK

[4]

절차적 DML (PL/SQL, T-SQL) - WHAT + HOW
비절차적 DML (SQL) - WHAT

[6 - 12] DDL (`ON DELETE`, 제약 조건)

[11] 물리적 테이블명

EMP100 (문자 시작, $ # 허용)

# Oracle
CREATE TABLE 테이블명
(컬럼명       데이터타입     (default) (null)
,PROD_ID    VARCHAR(10)             NOT NULL

,CONSTRAINT 조건명          PRIMARY KEY (컬럼명)
,CONSTRAINT table-pk      PRIMARY KEY (PROD_ID)
);

# ALTER
ALTER TABLE 테이블명       ADD CONSTRAINT 조건명     PRIMARY KEY (컬럼명);
ALTER TABLE 테이블명       DROP COLUMN    컬럼명;

# 컬럼명 변경
RENAME TABLE 이전명 TO 변경명;

# SQL server
CREATE TABLE table-name
(
컬럼명       데이터타입      (NOT NULL)       (PRIMARY KEY),
PROD_ID    INTEGER                         PRIMARY KEY,

PRIMARY KEY(컬럼명)
);

# ON DELETE CASCADE -> 참조 테이블의 row가 삭제되면 따라서 row 삭제됨.
컬럼명       INTEGER REFERENCES 테이블명(컬럼명)    ON DELETE CASCADE,
COL1       INTEGER REFERENCES T(C)            ON DELETE CASCADE) 

# ALTER
ALTER TABLE 테이블명       ALTER COLUMN 컬럼명     데이터타입    널여부

[9] ON DELETE, ON INSERT

ON DELETE
- CASCADE : orininal 삭제 → child 에서 삭제
- RESTRICT : original 삭제 → child에 pk 없는 경우에만 허용
ON INSERT
- AUTOMATIC : original pk X → pk 생성 후 child 입력
- DEPENDENT : original pk X → 입력 허용 X

[13 - 26] DML

INSERT INTO 테이블명                VALUES
			(모든 컬럼 값),
			(모든 컬럼 값),
			...
			(모든 컬럼 값);

INSERT INTO 테이블명 (특정 컬럼들)   VALUES
			(특정 컬럼 값),
			(특정 컬럼 값),
			...
			(특정 컬럼 값);

# 컬럼명 변경
UPDATE      테이블명              SET 컬럼명=컬럼값

[23 24 25] DROP vs. TRUNCATE vs. DELETE

	DROP	TRUNCATE	DELETE
데이터	X	X	X
데이터 사용량	X	X	O
테이블 구조(스키마)	X	O	O
AUTO COMMIT	DDL → O	DDL → O	DML → X
LOG	DDL → X	DDL → X	DML → O

[10 14 15 19 ] 제약조건

제약조건
- PRIMARY KEY : UNIQUE + NOT NULL → 테이블 당 1개만 생성 가능
- UNIQUE KEY
- NOT NULL
- FOREIGN KEY → 테이블 多 개 가능
- CHECK → 무결성 유지를 위해

[27 - 32] TCL (ACID, `ROLLBACK`, `COMMIT` )

[28] DB에서 발생 가능한 문제

Non-repeatable Read: 1 trx, same query의 결과가 다름.

[29] oracle에서는, DDL 문장이 수행되면 묵시적으로 COMMIT 수행된다.

[30] DDL 의 트랜젝션 종료 기능

oracle에서는 DDL이 trx 종료를 의미하고 묵시적으로 COMMIT 이 실행된다.

*ACID 알쥐 알쥐

[31, 32] TCL

BEGIN TRANSACTION
	...
SAVE TRANSACTION SP1        # ROLLBACK 시 체크포인트이당
	...
SAVE TRANSACTION SP2
	...
ROLLBACK TRANSACTION SP2    # SP2까지의 명령어를 롤백
COMMIT

[33 - 39] `WHERE` 절

[34] 연산자 우선순위

괄호 → NOT → >, <=. =, BETWEEN, LIKE, ... → AND → OR

[40 - 50] 함수 (문자열 함수, 숫자 함수, `NULL` 관련 함수, `CASE .. WHEN .. THEN ..` ), NULL 연산

비교연산 → FALSE
사칙연산 → NULL
‘’ → NULL (oracle), "" (sql server)
$\infin$ (oracle), $-\infin$ (sql server)

IS NULL (va1, val2)
NVL (val1, val2)          # val1 if val1 IS NOT NULL else val2
NVL2(val1, val2, val3)    # val2 if val1 IS NOT NULL else val3

NULLIF (va1, val2)        # NULL if val1 == val2 else val1

COALESCE (exp1, exp2,...) # exp에서 NULL이 아닌 최초의 표현식 ret. all NULL->NULL ret.

[43] case expression

CASE
	 WHEN 컬럼명=값 THEN 값1
		ELSE 값2
END

CASE 컬럼명 WHEN 값 THEN 값1 ELSE 값2 END

[51 - 54] `GROUP BY`, `HAVING` 절

[55 - 61] `ORDER BY` 절

[56] !?!.. SELECT → ORDER BY 지만,
oracle은 select 시에 행을 로드하기 때문에 select 구문에 없는 속성으로도 정렬할 수 있다는디;;

[57] ORDER BY 절에서 alias, 컬럼 순서 정수 혼용 可

[59] FROM - WHERE - GROUP BY - HAVING - SELECT - ORDER BY

ORDER BY 가 제일 마지막

ORDER BY 컬럼명 (ASC), 컬럼명2 (DESC) ...

[60] TOP(3)

SELECT TOP(3)           컬럼명1, 컬럼명2        # 기본형
SELECT TOP(3) WITH TIES 컬럼명1, 컬럼명2        # 만약 공동 등수가 있다면 함꼐 출력하기

[62 - 64] `JOIN`

(INNER) JOIN 테이블명2 ON 테이블명1.컬럼명 = 테이블명2.컬럼명
NATURAL JOIN 테이블명2                                 # 동일한 컬럼은 모두 조인됨

II-2. SQL 활용

[65 - 78] 순수 관계 연산자 $\ni$ `SELECT`, `PROJECT`, `JOIN`, `DIVIDE`

[65] 순수 관계 연산자 $\ni$ SELECT, PROJECT, JOIN, DIVIDE

순수관계 연산자: RDB에 적용할 수 있도록 특별히 개발한 연산자
논리 연산자
집한 연산자
etc

[66] NOT EXISTS (subquery) →subquery에 존재하지 않는지

[70] USING 에서는 ALIAS 사용 불가능

SELECT *
FROM
USING (컬럼명)

# 이지랄하면 안 됨.
USING (A.ID = B.ID)

[72] FROM → ON → JOIN → WHERE 따라서 ON에서 original table 이 필터링 되지 않는다

[77] oracle JOIN 명령어 문법

# ANSI
FROM 테이블명1 LEFT OUTER JOIN 테이블명2 
ON   (컬럼명1=컬럼명2    AND     컬럼명2=값)

# oracle
WHERE 컬럼명1=컬럼명2(+)
AND   컬럼명2=값(+)

[79 - 86] 집합 연산자

UNION                     # DUP X, sort O          -> slow
UNION ALL                 # DUP O, sort X          -> fast
INTERSECT
MINUS (ESCEPT)

[80] 동일한|다른 결과를 도출하는 SQL 문장 고르기 유형

총 row 개수가 다름
문법 오류

[87 - 91] 계층형 쿼리

START WITH   컬럼명=값                    # 루트 지정
CONNECT BY   PRIOR 컬1=컬2               # 컬1에서 출발. 컬1 값을 컬2에서 찾기
             AND 조건절               # i) 부모는 무조건 포함 -> ii) 자식만 필터링
ORDER SIBLINGS BY 컬럼명

[91 - 104] 서브쿼리 (스칼라|인라인뷰|..)

[96 - 100] 서브쿼리

단일 행 서브쿼리 =, <, ≤, <>, … → 실행 결과가 항상 1건 이하
다중 행 서브쿼리 IN, ALL, EXISTS, … → 실행 결과 1개 이상
다중 칼럼 서브쿼리 → 여러 컬럼 반환
연관 서브쿼리 → 메인 쿼리 컬럼을 포함한다.

[92- 93] 셀프 조인 (ex) 누적매출액 쿼리)

SELECT      A.일자 AS 일자 SUM(B.매출액) AS 누적매출액
FROM        일자별매출 A JOIN 일자별매별 B
                   ON (A.일자 <= B.일자)
GROUP BY    A.일자

[101-104] VIEW → 👍🏻 독편보

[105 - 111]그룹 함수 (`ROLLUP()`, `CUBE()`, `GROUPING SETS()`, `GROUPING` )

GROUPING (col1)     # 1 -> 합계 콜럼, 2 -> 합계 콜럼이 아님.
GROUP BY ROLLUP     (col1, col2, ...)         # OX OO, 계층이 O 순서 O
GROUP BY CUBE       (col1, col2, ...)         # XX XO OX OO
GROUP BY GROUPING   (co1, co2, ...)           # OX XO, 계층이 X 순서 X

[112 - 118] 윈도우 함수(`RANK()`, `DENSE_RANK()`, `ROW_NUMBER()`)

	RANK()	DENSE_RANK()	ROW_NUMBER()
☑️ what) 1등 점수가 2개일 때 랭킹 결과	1 1 3 4	1 1 2 3	1 2 3 4
▶️ when)

[119 - 122] DCL

[123 - 127] 절차형 SQL

[101-104] VIEW → 👍🏻 독편보

II-3. SQL 최적화 기본 원리

[128 - 133] 옵티마이저 & 실행계획

[128] 옵티마이저

☑️ what) SQL문에 대한 최적의 실행 계획 선택

종류
1)비용 기반 옵티마이저(Cost Based Optim.): 통계 정보 활용 → 시간 및 자원량을 계산 → 효율적인 실행계획 선택
2)규칙 기반 옵티마이저: rowid로 접근하는 것 → … → 전체 테이블 스캔

[129 130 131] 실행계획

☑️ what) SQL 실행 절차 + 실행 방법ㅂ

$\ni$ 조인 기법, 조인 순서, access 기법 등

[132 133] SQL 처리 흐름도

파악 可 → 처리 순서, 엑세스 기법(idx, all table), 성능 표현, 시각화
파악 不可 → 실행 시간

[134 - 140] 인덱스 기본

[134] 인덱스 종류

기본 idx(Primary Key): 중복 X, NULL X
보조 idx :유니크 X

❓ for) 테이블 조회 성능 향상

🥲 pb) INSERT, UPDATE, DELETE 등 DML 처리 성능 저하 可

	B-TREE idx	BITMAP idx	CLUSTERED idx
☑️ what)	브랜치(분기) + 리프(컬럼값정렬)	리프 == 데이페이지	1 키 엔트리 → 多 행 포인터
▶️ gd )	데이터 10% 이하를 보는 경우
▶️ when)	OLTP 등의 업무 목록 처리		구현 시 알 수 없는 경우, DW

[141 - 146] 조인 수행 원리

	Nested Loop Join	Sort Merge Join	Hash Join
☑️ what)	index 사용 O	index 사용 X	index 사용 X
EQUI JOIN에서만 동작
데이터 읽기	랜덤 엑세스	스캔	선행-build input
후행-prove input
🥲 pb)	driving 집합 大 → 부하 大 TT	External Sorting → IO 시간 大
▶️ gd )	- NATURAL JOIN 인 경우 gd
- 적당한(유니크) index가 있는 경우		- 한 쪽이 memory-fit한 경우
- 해시 키 속성에 중복 값이 적은 경우
▶️ when)		- 적당한 index X여서 NL이 비효율적인 경우	- 두 테이블 大 SMJ가 비효율적인 경우
- index가 없어서 NLJ가 비효율적인 경우

이수빈

이전 포스트

[SQL, SQLD] 노랑이 책 정리 - II. SQL 기본 및 활용

SQL

I-1. SQL 기본

[1 - 5] 관계형 DB 개요 및 명령어

[6 - 12] DDL (`ON DELETE`, 제약 조건)

[13 - 26] DML

[27 - 32] TCL (ACID, `ROLLBACK`, `COMMIT` )

[33 - 39] `WHERE` 절

[40 - 50] 함수 (문자열 함수, 숫자 함수, `NULL` 관련 함수, `CASE .. WHEN .. THEN ..` ), NULL 연산

[51 - 54] `GROUP BY`, `HAVING` 절

[55 - 61] `ORDER BY` 절

[62 - 64] `JOIN`

II-2. SQL 활용

[65 - 78] 순수 관계 연산자 $\ni$ `SELECT`, `PROJECT`, `JOIN`, `DIVIDE`

[79 - 86] 집합 연산자

[87 - 91] 계층형 쿼리

[91 - 104] 서브쿼리 (스칼라|인라인뷰|..)

[92- 93] 셀프 조인 (ex) 누적매출액 쿼리)

[105 - 111]그룹 함수 (`ROLLUP()`, `CUBE()`, `GROUPING SETS()`, `GROUPING` )

[112 - 118] 윈도우 함수(`RANK()`, `DENSE_RANK()`, `ROW_NUMBER()`)

[119 - 122] DCL

[123 - 127] 절차형 SQL

II-3. SQL 최적화 기본 원리

[128 - 133] 옵티마이저 & 실행계획

[134 - 140] 인덱스 기본

[141 - 146] 조인 수행 원리

[SQL, SQLD] 노랑이 책 정리 - I. 데이터 모델링의 이해

0개의 댓글

[SQL, SQLD] 노랑이 책 정리 - II. SQL 기본 및 활용

SQL

I-1. SQL 기본

[1 - 5] 관계형 DB 개요 및 명령어

[6 - 12] DDL (ON DELETE, 제약 조건)

[13 - 26] DML

[27 - 32] TCL (ACID, ROLLBACK, COMMIT )

[33 - 39] WHERE 절

[40 - 50] 함수 (문자열 함수, 숫자 함수, NULL 관련 함수, CASE .. WHEN .. THEN .. ), NULL 연산

[51 - 54] GROUP BY, HAVING 절

[55 - 61] ORDER BY 절

[62 - 64] JOIN

II-2. SQL 활용

[65 - 78] 순수 관계 연산자 ∋\ni∋ SELECT, PROJECT, JOIN, DIVIDE

[79 - 86] 집합 연산자

[87 - 91] 계층형 쿼리

[91 - 104] 서브쿼리 (스칼라|인라인뷰|..)

[92- 93] 셀프 조인 (ex) 누적매출액 쿼리)

[105 - 111]그룹 함수 (ROLLUP(), CUBE(), GROUPING SETS(), GROUPING )

[112 - 118] 윈도우 함수(RANK(), DENSE_RANK(), ROW_NUMBER())

[119 - 122] DCL

[123 - 127] 절차형 SQL

II-3. SQL 최적화 기본 원리

[128 - 133] 옵티마이저 & 실행계획

[134 - 140] 인덱스 기본

[141 - 146] 조인 수행 원리

[SQL, SQLD] 노랑이 책 정리 - I. 데이터 모델링의 이해

0개의 댓글

[6 - 12] DDL (`ON DELETE`, 제약 조건)

[27 - 32] TCL (ACID, `ROLLBACK`, `COMMIT` )

[33 - 39] `WHERE` 절

[40 - 50] 함수 (문자열 함수, 숫자 함수, `NULL` 관련 함수, `CASE .. WHEN .. THEN ..` ), NULL 연산

[51 - 54] `GROUP BY`, `HAVING` 절

[55 - 61] `ORDER BY` 절

[62 - 64] `JOIN`

[65 - 78] 순수 관계 연산자 $\ni$ `SELECT`, `PROJECT`, `JOIN`, `DIVIDE`

[105 - 111]그룹 함수 (`ROLLUP()`, `CUBE()`, `GROUPING SETS()`, `GROUPING` )

[112 - 118] 윈도우 함수(`RANK()`, `DENSE_RANK()`, `ROW_NUMBER()`)