while (1): study();
1장. SQL 기본 본문
1절. 관계형 데이터베이스 개요
1. 데이터베이스
- 특정 기업이나 조직, 개인이 필요에 따라 데이터를 일정한 형태로 저장해 놓은 것
Ex) Oracle, SyBase, Informix, DB2, Teradata, SQL server (STD ISO)
- 기능
1) 정규화: 이상 제거, 중복 배제
2) 동시성: 다중 사용자 지원
3) 메타 데이터: 체계적 관리
4) 표준화: 데이터 품질 확보
5) 보안
6) 무결성
2. SQL
1) 데이터 조작어(DML)
SELECT
INSERT
UPDATE
DELTE
2) 데이터 정의어(DDL)
CREATE
ALTER
RENAME
DROP
3) 데이터 제어어(DCL)
GRANT
REVOKE
4) 트랜잭션 제어어(TCL)
COMMIT
ROLLBACK
3. Standard SQL 개요
- ANSI/ISO (SQL:2003) 표준
- 대표적인 표준 SQL 기능
1) STANDARD JOIN
ex) CROSS, OUTER JOIN
2) 새로운 SUBQUERY 기능
ex) SCALAR SUBQUERY, TOP-N SUBQUERY
3) 새로운 리포팅 기능
ex) ROLLUP, CUBE, GROUPING SETS
4) 새로운 분석 기능
ex) WINDOW FUNCTION
- 일반 집합 연산자
1) UNION: UNION(정렬), UNION ALL(비정렬, 더 효율적)
2) INTERSECTION: INTERSECT
3) DIFFERENCE: EXCEPT(오라클은 MINUS)
4) PRODUCT: CROSS JOIN (CARTESIAN PRODUCT)
- 순수 관계 연산자
1) SELECT: WHERE
2) PROJECT: SELECT
3) JOIN: 다양한 JOIN으로 발전
4) DIVIDE: X
6. 데이터 유형
| ORACLE | SQL SERVER | |
| CHARACTER(s) | CHAR | CHAR |
| 최대 2000b | 최대 8000b | |
| VARCHAR(s) | VARCHAR2 | VARCHAR |
| 최대 4000b | 최대 8000b | |
| NUMERIC | NUMBER | * |
| DATETIME | DATE | DATETIME |
| 1초 단위 | 3.33ms 단위 |
2절. SELECT 문
1. SELECT
SELECT [ALL/DISTINCT] 출력 대상 칼럼명, 출력 대상 칼럼명, ...
FROM 출력 대상 테이블명
- 별명 중간에 공백이 들어가는 경우 " " 사용
SELECT PLAYER_NAME AS "선수 이름", POSITION AS "포지션"
FROM PLAYER;
- 산술 연산자
SELECT PLAYER_NAME AS "선수명", ROUND(WEIGHT / ((HEIGHT / 100) * (HEIGHT / 100)), 2)
AS BMI지수
FROM PLAYER
- 합성 연산자
1) Oracle: ||
2) SQL server: +
3) CONCAT(string1, string2)
SELECT PLAYER_NAME + " 선수, " + STR(HEIGHT) + " cm, " + STR(WEIGHT) + " kg, " AS 체격정보
FROM PLAYER;
3절. 함수
1. 내장 함수 개요
- 함수 - 사용자 정의 함수
- 내장 함수 - 단일행 함수
- 다중행 함수
2. 문자형 함수
- LOWER(문자열)
- UPPER(문자열)
- ASCII(문자열)
- CHR/CHAR(ascii 번호)
- CONCAT(문자열1, 문자열2)
- SUBSTR/SUBSTRING(문자열, m[, n ])
- LENGTH/LEN(문자열)
- LTRIM(문자열, [,지정문자(oracle)]), RTRIM(문자열, [,지정문자(oracle)])
- TRIM([leading | trailing | both(oracle)] 지정문자 FROM 문자열)
* Dual 테이블(Oracle) : 사용자 테이블이 필요없는 경우 사용하는 일종의 dummy 테이블
3. 숫자형 함수
- ABS(숫자)
- SIGN(숫자)
- MOD(숫자1, 숫자2)
- CEIL/CEILING(숫자)
- FLOOR(숫자)
- ROUND(숫자 [, m])
- TRUNC(숫자 [, m])
- SIN, COS, TAN, ...
- EXP(숫자)
- POWER(숫자1, 숫자2)
- SQRT(숫자)
- LOG(밑, 지수) / LOG(지수, 밑)
- LN(숫자) (Oracle) : 자연로그
4. 날짜형 함수
- SYSDATE / GETDATE() : 현재 날짜
- EXTRACT('YEAR' | 'MONTH' | 'DAY" FROM d) / DATEPART('YEAR' | 'MONTH' | 'DAY', d)
- TO_NUMBER(TO_CHAR(d, format)) / YEAR(d), MONTH(d), DAY(d)
5. 변환형 함수
- Oracle
- TO_NUMBER, TO_CHAR, TO_DATE
- SQL Server
- CAST(표현식 AS 타입[(길이)])
- CONVERT(타입[(길이)], 표현식 [, 스타일])
6. CASE 표현
CASE
SIMPLE_CASE_EXPRESSION 조건 OR SEARCHED_CASE_EXPRESSION 조건
[ELSE 디폴트값]
END예시)
SELECT LOC
, CASE LOC
WHEN 'NEW YORK' THEN 'EAST'
WHEN 'BOSTON' THEN 'EAST'
WHEN 'CHICAGO' THEN 'CENTER'
WHEN 'DALLAS' THEN 'CENTER'
ELSE 'ETC'
END AS AREA
FROM DEPT;7. NULL 관련 함수
- NVL/ISNULL(판단 대상, 대체값): NULL이면 대체
- NULLIF(표현식1, 표현식2): 두 표현식이 같으면 NULL, 아니면 표현식1
- COALESCE(표현식1, 표현식2, ...): NULL이 아닌 최초의 표현식
* NULL과 공집합
- 공집합은 데이터가 없으므로 집계함수로 NULL 출력 후 NVL/ISNULL로 대체
예시)
SELECT NVL(MAX(MGT), 9999) AS MGR
FROM EMP
WHERE ENAME = 'JSC';
4절. WHERE 절
1. WHERE 조건절 개요
- 기능
1) 조인 조건을 기술
2) 결과를 제한하기 위한 조건을 기술
* FTS(Full Table Scan)은 성능을 저하시킬 수도 있음
2. 연산자의 종류
1) 비교연산자
=, >, >=, <, <=
* 문자 유형 비교 방법
달라진 첫 번째 문자의 값에 따라 크기 결정
2) SQL연산자
BETWEEN a AND b, IN (list), LIKE '비교문자열', IS NULL
* 다중리스트를 사용한 IN 연산자
SELECT ENAME, JOB, DEPTNO
FROM EMP
WHERE (JOB, DEPTNO) IN (('MANAGER', 20), ('CLERK', 30));* 와일드카드 문자
% : 0개 이상의 어떤 문자
_ : 1개의 어떤 문자
* NULL값의 비교 연산은 항상 FALSE를 반환
3) 논리연산자
AND, OR, NOT
4) 부정비교연산자
!=, ^=, <> (ANSI/ISO 표준), NOT 칼럼명 =, NOT 칼럼명 >
5) 부정SQL연산자
NOT BETWEEN a AND b, NOT IN (list), IS NOT NULL
- 연산자의 우선순위
괄호 > 비교연산자,SQL연산자 > NOT > AND > OR
5절. GROUP BY, HAVING 절
1. 집계함수(Aggregate Function)
1) 사용처
SELECT절, HAVING절, ORDER BY절 (GROUP BY가 뭉쳐준 뒤)
2) 종류
COUNT(*) (NULL 포함), COUNT(표현식), SUM, AVG, MAX, MIN, STDDEV, VARIANCE/VAR
* 유형 관계없이 사용 가능한 집계 함수 : MAX, MIN, COUNT
2. GROUP BY 절
- ALIAS 사용 불가 (ALIAS가 사용 가능한 것 SELECT와, 그 이후의 ORDER BY 뿐)
- HAVING 절은 '일반적으로' GROUP BY 절 뒤에 위치
- WHERE 절: 데이터 값 변경 가능 / HAVING 절: 출력 레코드 개수만 변경
5. 집계함수와 NULL 처리
- 가능한 ELSE 절에 상수값을 지정하지 않거나, ELSE절(default NULL)을 작성하지 않도록 -> 자원 사용의 효율성
- 출력 시 0을 표시하고 싶은 경우 NVL(SUM(SAL), 0) 혹은 ISNULL(SUM(SAL), 0) 사용
6절. ORDER BY 절
1. ORDER BY 정렬
- ALIAS, 칼럼 순서 사용 가능
- default 오름차순(ASC)
- Oracle: NULL이 가장 큰 값 / SQL Server: NULL이 가장 작은 값
2. SELECT 문장 실행 순서
5. SELECT 칼럼명 [ALIAS]
1. FROM 테이블명
2. [WHERE 조건식]
3. [GROUP BY 칼럼 or 표현식]
4. [HAVING 그룹조건식]
6. [ORDER BY 칼럼 or 표현식 [ASC | DESC]]* 옵티마이저가 SQL문을 해석하는 논리적 순서, 물리적 순서가 아님
- SELECT에 나타나지 않은 항목을 포함할 수 있음 (메모리에서 가져옴)
- 단 서브쿼리 범위(인라인 뷰 등)를 벗어나면 더 이상 사용할 수 없음
- GROUP BY 절에서 개별데이터를 버림 -> 이후 절차에선 개별 데이터를 사용할 수 없음
7절. 조인
1. 조인 개요
- 조인 : 두 개 이상의 테이블들을 연결해 데이터를 출력
- FROM 절에 여러 테이블이 나열되어도 처리할 때는 두 개 집한 간에만 조인 발생
- ex) (((A JOIN D) JOIN C) JOIN B)
2. EQUI JOIN
- 두 개의 테이블 간 칼럼 값이 정확히 일치하는 경우
- N개 테이블 조인 시 N - 1개의 조인 조건 필요
- ALIAS를 사용한 3개 이상 테이블 조인
-
SELECT A.PLAYER_NAME AS 선수명, A.POSITION AS 포지션, B.REGION_NAME AS 연고지, B.TEAM_NAME AS 팀명, C.STADIUM_NAME AS 구장명 FROM PLAYER A, TEAM B, STADIUM C WHERE B.TEAM_ID = A.TEAM_ID AND C.STADIUM_ID = B.STADIUM_ID ORDER BY 선수명; - ALIAS를 사용할 시 SELECT와 WHERE절은 칼럼명을 사용할 수 없음
-
- ALIAS를 사용한 3개 이상 테이블 조인
3. Non EQUI JOIN
칼럼 값들이 서로 일치하지 않는 경우에 사용
SELECT A.ENAME, A.JOB, A.SAL, B.GRADE
FROM EMP A, SALGRADE B
WHERE A.SAL BETWEEN B.LOSAL AND B.HISAL
5. OUTER JOIN
조인 조건을 만족하지 않는 행들도 반환
SELECT A.STADIUM_NAME, A.STADIUM_ID, A.SEAT_COUNT, A.HOMETEAM_ID, B.TEAM_NAME
FRO STADIUM A, TEAM B
WHERE B.TEAM_ID(+) = A.HOMETEAM_ID
ORDER BY A.HOMETEAM_ID* (Oracle) (+) 표시의 반대편에 있는 테이블이 OUTER JOIN의 기준 테이블
8절. 표준 조인
1. FROM절 조인 형태
- INNER JOIN
- NATURAL JOIN
- USING 조건절
- ON 조건절
- CROSS JOIN
- OUTER JOIN
2. INNER JOIN
- 조인 조건을 만족하는 행들만 반환
- USING 절이나 ON 절을 필수적으로 사용
- 디폴트이기 때문에 INNER 생략 가능
- 조인 기준 칼럼: 둘로 처리 / ALIAS, 접두사 필수
SELECT A.EMPNO, A.ENAME, B.DEPTNO, B.DNAME
FROM EMP A (INNER) JOIN DEPT B
ON A.DEPTNO = B.DEPTNO;
3. NATURAL JOIN
- 동일한 이름의 모든 칼럼들에 대해 EQUI JOIN 수행
- USING, ON, WHERE 조인 조건 사용 불가
- SQL Server에선 사용 불가
- 조인 기준 칼럼: 하나로 처리 / ALIAS, 접두사 불가 / 순서 지정하지 않을 시 먼저 출력
SELECT A.EMPNO, A.ENAME, DEPTNO, B.DNAME
FROM EMP A NATURAL JOIN DEPT B;
4. USING 조건절
- 원하는 칼럼에 대해서만 조인 수행
- 조인 기준 칼럼: 하나로 처리 / ALIAS, 접두사 불가 / 순서 지정하지 않을 시 먼저 출력
SELECT *
FROM DEPT A JOIN DEPT_TEMP B
USING (DEPTNO);
5. ON 조건절
- 조인 서술부(ON 조건절)와 비조인 서술부(WHERE 조건절) 분리
- 칼럼명 달라도 조인 가능
- 조인 기준 칼럼: 둘로 처리 / ALIAS, 접두사 필수
SELECT A.EMPNO, A.ENAME, B.DEPTNO, B.DNAME
FROM EMP A JOIN DEPT B
ON (B.DEPTNO = A.DEPTNO);SELECT A.PLAYER_NAME AS 선수명, A.POSITION AS 포지션,
B.REGION_NAME AS 연고지명, B.TEAM_NAME AS 팀명,
C.STADIUM_NAME AS 구장명
FROM PLAYER A JOIN TEAM B
ON B.TEAM_ID = A.TEAM_ID
JOIN STADIUM C
ON C.STADIUM_ID = B.STADIUM_ID
WHERE A.POSITION = 'GK'
ORDER BY 선수명;6. CROSS JOIN
- CARTESIAN PRODUCT, CROSS PRODUCT
- 튜닝, 리포트 / DW의 개별 차원을 FACT 칼럼과 조인 시 사용
SELECT A.ENAME, B.DNAME
FROM EMP A CROSS JOIN DEPT B
ORDER BY A.ENAME;
7. OUTER JOIN
- USING, ON 조건절 필수
- LEFT OUTER JOIN
SELECT A.STADIUM_NAME, A.STADIUM_ID, A.SEAT_COUNT, A.HOMETEAM_ID,
B.TEAM_NAME
FROM STADIUM A LEFT (OUTER) JOIN TEAM B
ON B.TEAM_ID = A.HOMETEAM_ID
ORDER BY A.HOMETEAM_ID;- RIGHT OUTER JOIN
SELECT A.ENAME, B.DEPTNO, B.DNAME, B.LOC
FROM EMP A RIGHT (OUTER) JOIN DEPT B
ON B.DEPTNO = A.DEPTNO;- FULL OUTER JOIN
SELECT *
FROM DEPT A FULL (OUTER) JOIN DEPT_TEMP B
ON B.DEPTNO = A.DEPTNO;Note
- 유형 관계없이 사용 가능한 집계 함수
MAX, MIN, COUNT
- 레코드 건수가 다른 SQL문은?
- SELECT COUNT(*) FROM EMP WHERE 1 = 0;
- SELECT MAX(EMPNO) FROM EMP WHERE 1 = 0;
- SELECT NVL(EMPNO, 9999) FROM EMP WHERE 1 = 0;
- SELECT NVL(MAX(EMPNO, 0000) FROM EMP WHERE 1 = 0;
1. 1개(0)
2. 1개(NULL)
3. 0개(공집합)
4. 1개(0000)
추가
테이블 별칭
테이블에 별칭을 지정하고 나면, SELECT나 WHERE절에서 반드시 별칭을 사용해야만 한다.
단, 단일 테이블 조회의 경우 별칭으로 칼럼을 한정하지 않아도 된다.
Q. SQL의 수행 결과가 아래와 같도록 빈칸 ㄱ에 들어갈 표현식을 고르시오.
| C1 | C2 |
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 1 |
| 5 | 2 |
SELECT 6 - A.C1 AS C1,
CASE
WHEN A.C1 >= 4 THEN 'A'
WHEN A.C2 IN (1, 3) THEN 'B'
ELSE 'C'
END AS C2
FROM T1 A
ORDER BY [ ㄱ ];| C1 | C2 |
| 4 | C |
| 5 | B |
| 3 | B |
| 2 | A |
| 1 | A |
별칭과 칼럼명이 같은 경우 별칭이 우선순위가 높음
A.C1 != C1
A.C2 != C2
A. C2 DESC, A.C1
CASE 표현식
맨 처음 일치하는 표현식을 반환
GROUP BY
SELECT, WHERE에 GROUP BY 절 or 집계함수 표현식만 기술 가능
카티션 곱
조인 시, 조인 조건이 누락되면 카티션 곱 집합이 생성
문자의 대소 비교
처음 달라지는 문자의 크기
아우터 조인
무조건 기준 테이블의 행을 모두 결과에 포함
모든 조인 조건에 (+)가 필요
Q. 다음 중 아래 SQL과 결과가 항상 동일한 SQL을 고르시오.
SELECT *
FROM T1 A, T2 B
WHERE A.C2 IN ('B', 'C')
AND B.C1(+) = A.C1
AND B.C2 <= 2;B.C2 <= 2에서 (+)가 없으므로 INNER JOIN을 수행함
A.
SELECT *
FROM T1 A INNER JOIN T2 B
ON B.C1 = A.C1
WHERE A.C2 IN ('B', 'C')
AND B.C2 <= 2;
셀프 조인
Q. 다음 중 아래 SQL의 수행 결과를 고르시오.
| 주문번호 | 고객번호 | 주문일자 | 주문금액 |
| 510 | 1 | 20190501 | 20000 |
| 612 | 1 | 20190523 | 5000 |
| 728 | 1 | 20200311 | 25000 |
| 904 | 1 | 20200715 | 10000 |
| 404 | 2 | 20191208 | 7000 |
| 603 | 2 | 20200428 | 6000 |
| 807 | 2 | 20200527 | 17000 |
SELECT MIN(주문합계금액) AS 최저주문합계금액
FROM (SELECT A.고객번호, A.주문일자, SUM(B.주문금액) AS 주문합계금액
FROM 주문 A, 주문 B
WHERE B.고객번호 = A.고객번호
AND B.주문일자 <= A.주문일자
GROUP BY A.고객번호, A.주문일자);B.고객번호 = A.고객번호에서 카티션 곱 생성
A 이전 날짜에 대해서 주문일자로 그룹핑 및 B의 합계 도출
→ 누적주문금액(Running Total)
A. 7000
연산자 우선 순위
Q. 다음 중 아래 SQL의 수행 결과를 고르시오.
| C1 | C2 | C3 |
| 1 | A | 1000 |
| 2 | B | 800 |
| 3 | A | NULL |
| 4 | B | 1200 |
| 5 | C | 3000 |
| 6 | B | 1500 |
SELECT SUM(C3) AS R1
FROM T1
WHERE C1 >= 4
OR C2 IN ('A', 'B')
AND C3 NOT BETWEEN 1000 AND 2000;괄 > 비,S > NOT > AND > ALL
A. 6500
함수
SUBSTR(칼럼명, m, n)
m번째 인덱스부터 n개의 문자
TRUNC(a, b)
a로부터 b까지만 표현하고 나머지는 버린다.
ADD_MONTHS(날짜, a)
날짜 값에 a만큼 개월 수를 더한다.
Ex)
-- 오늘 날짜 2020년 7월 27일 14시 30분 00초
TRUNC(ADD_MONTHS(SYSDATE, -1), 'DD) - 1/24/60/60
-- 출력 2020년 7월 26일 23시 59분 59초
DECODE(칼럼, A, a, B, b, ..., c)
칼럼값이 A이면 a, B이면 b, ... 모두 아니면 NULL (혹은 c) 반환
NULLIF(칼럼, a)
칼럼값이 NULL이면 a 반환
'학습 > SQLD' 카테고리의 다른 글
| 데이터베이스와 성능 (0) | 2021.08.13 |
|---|---|
| 3장. 관리 구문 (0) | 2021.07.26 |
| 2장. SQL 활용 (0) | 2021.07.21 |
| 2장. 데이터 모델과 SQL (0) | 2021.07.13 |
| 1장. 데이터 모델링의 이해 (0) | 2021.07.11 |
