[SQLD/개정] 요약 정리(2/4) - 2과목 1장, SQL 기본
제 1절. 관계형 데이터베이스 개요
데이터베이스
- “특정 기업이나 조직 또는 개인이 필요에 의해 데이터를 일정한 형태로 저장한 것”
SQL
- “관계형데이터베이스에서 데이터 정의 데이터 조작, 데이터 제어를 하기 위해 사용하는 언어”
1. 데이터 조작어 (DML) : SELECT, INSERT, DELETE, UPDATE, MERGE
- DB 내 데이터에 대한 조회, 검색, 변형을 위한 명령어 (M : Manipulation)
- Not Auto Commit, Rollback 가능, 테이블 row단위로 데이터 컨트롤
- 비절차적 데이터 조작어, 데이터 부속어 라고도 표현
2. 데이터 정의어 (DDL) : CREATE, ALTER, DROP, RENAME, TRUNCATE
- 스키마, 도메인, 테이블, 뷰, 인덱스 등을 정의, 변경, 제거. 데이터 구조와 관련된 명령어
- Auto Commit, Rollback 불가, 테이블 단위 컨트롤
3. 데이터 제어어 (DCL) : GRANT, REVOKE
- DB에 접근하고 객체 사용 권한을 주거나 회수하는 명령어
4. 트랜젝션 제어어 (TCL) : COMMIT, ROLLBACK, SAVEPOINT
- DML에 의해 조작된 결과를 작업단위(트랜젝션)별로 제어하는 명령어
제 2절. DDL
데이터 유형
- CHARACTER(s), VARCHAR(s), NUMERIC, DATE
1. CREATE TABLE
CREATE TABLE 테이블이름
(칼럼명 CHAR(7) NOT NULL,
칼럼명2 DATE NULL,
CONSTRAINT 제약조건이름1 PRIMARY KEY (PLAYER_ID),
CONSTRAINT 제약조건이름2 FOREIGN KEY (TEAM_ID) REFERENCES TEAM (TEAM_ID));
2. ALTER TABLE
- SQL은 여러 컬럼/테이블은 하나의 명령으로 작업할 수 X (Oracle에서는 가능)
ALTER TABLE 테이블이름 ADD 칼럼명1 VARCHAR(80); -- 추가
ALTER TABLE 테이블이름 ALTER COLUMN 칼럼명2 VARCHAR(8) NOT NULL; -- 속성명변경
ALTER TABLE 테이블이름 DROP COLUMN 칼럼명3; -- 속성 삭제
- 테이블을 생성한 후에 제약조건을 추가하고 싶은 경우에는
ALTER TABLE과ADD추가
ALTER TABLE 테이블이름 ADD CONSTRAINT 제약조건이름3 PRIMARY KEY (col_1, col_2)
3. RENAME TABLE
ALTER TABLE 테이블이름
RENAME COLUMN 현재컬럼명 TO 바꿀컬럼명;
4. DROP TABLE
DROP TABLE 테이블이름;
ALTER TABLE 테이블이름 DROP COLUMN 컬럼이름;
- 테이블 이름이나 컬럼 삭제, ROLLBACK 불가
5. TRUNCATE TABLE
TRUNCATE TABLE 테이름이름 DROP COLUMM 컬럼이름;
- 테이블 형태 유지, 데이터만 제거
+ 제약조건(CONSTRAINT)
- NOT NULL
- UNIQUE : UNIQUE는 NULL 값을 가질 수 있다
- PRIMARY KEY : NOT NULL & UNIQUE
- 테이블에 PK를 복수로 생성할 수 없다.
- 하지만
CONSTRAINT를 활용하여 속성들을 괄호 안에 설정하면 생성할 수 있다.PRIMARY KEY(col_1, col_2) - 즉 기본 키를 구성하는 칼럼은 복수일 수 있지만, 기본 키가 복수일 수는 없다.
- FOREIGN KEY : 참조무결성 제약
- CHECK : 해당 조건을 만족하는 값으로만 입력 제한, 데이터의 무결성 유지를 위해 특정 컬럼에 설정
제 4절. TCL
트렌젝션 : “데이터베이스의 논리적 연산 단위”
- COMMIT, ROLLBACK, SAVEPOINT
- 하나의 트렌젝션에는 하나 이상의 SQL 문장이 포함.
- 분리될 수 없는 한 개 이상의 DB 조작 -> 전부 적용 or 전부 취소
트렌젝션의 4가지 특징
- 원자성 : all or nothing
- 일관성 : 트렌젝션 전에 괜찮으면 이후에도 다른 잘 못이 있으면 안 된다.
- 고립성 : 트렌젝션 중에는 다른 트렌젝션의 영향을 받지 X
- 지속성 : 트렌젝션 실행되고 나면 갱신된 내용이 영구적으로 저장
1. COMMIT
UPDATE PLAYER SET HEIGHT = 100;
COMMIT;
+ ★ Oracle와 SQL server의 COMMIT 명령어 차이
- Oracle
- DCL : AUTO COMMIT
- DDL/DML : 수동 COMMIT
- SQL Server
- AUTO COMMIT MODE = TRUE : DCL/DDL/DML AUTO COMMIT
- AUTO COMMIT MODE = FALSE : DCL/DDL/DML 수동 COMMIT
2. ROLLBACK
UPDATE PLAYER SET HEIGHT = 100;
ROLLBACK;
BEGIN TRAN UPDATE PLAYER SET HEIGHT = 100;
ROLLBACK;
3. SAVEPOINT0 : 현 시점에서 SAVEPOINT까지 트렌젝션 일부를 ROLLBACK
SAVEPOINT 세이브포인트이름;
ROLLBACK TO 세이브프인트이름;
제 5절. WHERE절 : 내가 원하는 자료만을 검색해보자
개요
- 비교 연산자, SQL 연산자, 논리 연산자, 부정 비교 연산자, 부정 SQL 연산자
- 괄호로 묶은 연산이 가장 먼저 처리됨
- 처리 순서 : 부정 연산자 -> 비교 연산자 -> 논리 연산자
SQL 연산자
- IN 연산자, LIKE 연산자, BETWEEN a AND b 연산자, IS NULL 연산자
1. IN (list) : 리스트에 있는 값 중 어느 하나라도 일치
SELECT ENAME, JOB, DEPTNO FROM EMP WHERE (JOB, DEPTNO) IN (('MANAGER', 20), ('CLERK', 30));
2. LIKE 비교문자열 : 비교문자열과 형태가 일치
SELECT E_POSITION, HEIGHT FROM PLAYER WHERE E_POSITION LIKE 'MF';
SELECT E_POSITION, HEIGHT FROM PLATER WHERE NAME LIKE '김%';
- 와일드카드
- % : 0개 이상의 어떤 문자
- _ : 1개의 단일 문자 \
3. BETWEEN a AND b : a 이상 b 이하
SELECT NAME, HEIGHT FROM PLAYER
WHERE HEIGHT BETWEEN 170 AND 180;
4. IS NULL / IS NOT NULL ★★★
SELECT NAME 선수이름, E_POSITION 포지션 FROM PLAYER WHERE E_POSITION IS NULL;
- NULL 사칙연산 연산 : NULL 리턴
- NULL의 비교 연산 : FALSE 리턴
- VARCHAR2 데이터에 ‘‘를 INSERT하면 자동적으로 NULL로 바뀜 (56p 37번)
- ORACLE은 공백 문자열이 저장되는걸 허락하지 않음
- 즉 ‘‘를 INSERT 한 뒤 ‘‘를 조회하면 ORACLE에서는 조회X, SQL에서는 조회 O
+ SQL자격검정시험 57p 38번 문제
SELECT SUM(매출금액) FROM 월별매출
WHERE
년 = 2014 AND 월 BETWEEN 11 AND 12
OR
년 = 2015 AND 월 BETWEEN 01 AND 03;
- 2014년 11월부터 2015년 3월까지 매출금액 합계를 출력하기
+ SQL자격검정시험 64p 47번 문제
- 0과 NULL을 포함한 나눗셈
- 숫자/0 -> error
- 숫자/NULL -> NULL
제 6절. 함수
1. 단일 행 함수와 다중 행 함수
단일 행 함수
- 추출되는 행마다 작업을 수행하고, 각 행마다 하나의 결과를 반환
- SELECT, WHERE, ORDER BY, UPDATE의 SET 절에 사용 가능
- 데이터 타입 변경 가능, 중첩 사용 가능
- 그룹(집계) 함수를 제외하면 대부분은 단일 행 함수
다중 행 함수
- 여러 개의 행이 입력되고, 하나의 값을 변환
- ex : SUM, AVG, MAX, MIN, COUNT …
내장 함수 : 입력되는 값이 많아도 출력은 하나만 (M:1)
- LOWER, UPPER, ASCII, CONCAT, SUBSTR, LEN, LTRIM(RTRIM,TRIM), RTRIM
문자형 함수
-
REPLACE(C1, CHR(10)) : 줄바꿈을 empty string으로 바꿔줌 (60p 41번)
A A LENGTH(C1) -- 3 ↓ REPLACE(C1, CHR(10)) ↓ AA LENGTH(C1) -- 2
숫자형 함수
날짜형 함수
변환형 함수
CASE 함수 : IF-THEN-ELSE (61p 43번)
1. SIMPLE_CASE_EXPRESSION
SELECT LOC,
CASE LOC WHEN 'NEW YORK' THEN 'EAST'
ELSE 'ETC'
END as AREA
TROM DEPT;
2. SEARCHED_CASE_EXPRESSION
SELECT LOC,
CASE WHEN LOC = 'NEW YORK' THEN 'EAST'
ELSE 'ETC'
END as AREA
TROM DEPT;
NULL 함수 ★★★★
SELECT ENAME, COMM, COALESCE(COMM, SAL) COAL FROM EMP;
1. NVL (Oracle) / ISNULL (SQL Server)
- NVL : Null Value의 약어
- 표현식1 결과 값이 NULL이면 표현식2 리턴
- NVL(K,0) : K 컬럼이 NULL이면 0으로 바꿈
2. NVL2
- NVL2(K,1,-1) : 컬럼이 NULL이 아니면 1을, NULL이면 -1 리턴
2. NULLIF (63p 47번)
- 두 값이 같으면 NULL을, 같지 않으면 첫 번쨰 값을 반환
- NVL(exp1, exp2) : exp1 = exp2 이면 NULL / exp1 != exp2 이면 exp1 리턴
3. COALESCE(표현식1, 표현식 2 …)
- COALESCE의 사전적 정의 : “큰 덩어리로 합치다”
- 여러 표현식에서 행 기준으로 NULL이 아닌 최초의 표현식을 리턴
- 모든 표현식이 NULL이면 NULL 리턴
-
COALESCE(Phone, tel) : 휴대폰번호가 있으면 휴대폰 번호를, 일반 전화번호가 있으면 tel을 리턴하여 취합할 수 있음
A B COALESCE(A,B) 10 10 20 20 10 20 10 NULL
+ SQL자격검정시험 64p 48번 문제 : COALESCE 연산자
| C1 | C2 | C3 |
|----|----|----|
| 1 | 2 | 3 | coalesce(C1,C2,C3) → 1
| | 2 | 3 | coalesce(C1,C2,C3) → 2
| | | 3 | coalesce(C1,C2,C3) → 3
SELECT SUM(COALESCE(C1,C2,C3)) FROM 테이블이름;
-- 1+2+3 = 6
제 7절. GROUP BY, HAVING 절
https://ggmouse.tistory.com/423
1. GROUP BY
| item | cost |
|---|---|
| pencil | 100 |
| knife | 200 |
| pencil | 150 |
| pencil | 130 |
| knife | 190 |
SELECT item, SUM(cost) sum
FROM 테이블
GROUP BY item
--결과
| item | cost |
|--------|------|
| pencil | 390 |
| knife | 380 |
2. ROLL UP
- GROUP BY 후 오는 항목을을 오른쪽에서 왼쪽으로 묶어 집계
SELECT store,
item,
SUM(cnt) AS all_cnt,
SUM(cost) AS all_cost
FROM sql_test_a
GROUP BY ROLLUP (store, item)
3. GROUPING
- 소계 컬럼이 null로 보이는게 거슬린다면 사용
- 결과가 NULL일때 1, 아닐 때 0을 반환
SELECT
GROUPING(store) AS grp_store,
store,
GROUPING(item) AS grp_item,
item,
SUM(cnt) AS all_cnt,
SUM(cost) AS all_cost
FROM 테이블
GROUP BY ROLLUP (store, item)
4. CUBE
- GROUP BY 항목들 간 모든 경우의 수로 그룹을 생성
- ROLL UP 보다 더 상세한 결과를 냄
5. GROUPING SETS
- 특정 항목에 대한 집계
- UNION ALL과 같은 결과
SELECT store, item,
SUM(cnt) AS all_cnt,
SUM(cost) AS all_cost
FROM sql_test_a
GROUP BY GROUPING SETS (store, item)
그룹 묶음 방식의 차이
- ROLL(a,b,c) : (a,b,c) / (a,b) / (a) / (b) / (c) / ()
- CUBE(a,b,c) : (a,b,c) / (a,b) / (a,c) / (b,c) / (a) / (b) / (c) / ()
- GROUPING SETS(a,b,c) : (a) / (b) / (c) / ()
제 8절. ORDER BY
SELECT 문장 실행 순서 (71p 59번)
| 입력순서 | 실행순서 | 절 |
|---|---|---|
| 1 | 5 | SELECT |
| 2 | 1 | FROM |
| 3 | 2 | WHERE |
| 4 | 3 | GROUP BY |
| 5 | 4 | HAVING |
| 6 | 6 | ORDER BY |
- FROM : 대상 테이블을 참조
- WHERE : 대상 데이터가 아닌 것 제외
- GROUP BY : ROW를 소그릅
- HAVING : 그루핑된 조건에 맞는 것만 출력
- FROM : 데이터 값을 출력/계산
- ORDER BY : 데이터 정렬
TOP N 쿼리
SELECT TOP(3) 팀명, 승리건수 FROM 팀별성적 ORDER BY 승리건수 DESC;
-- 승리건수가 동일한 팀 중 하나만 출력
SELECT TOP(3) WITH TIES 팀명, 승리건수 FROM 팀별성적 ORDER BY 승리건수 DESC;
-- 승리건수가 동일한 팀이 있다면 함께 출력
제 9절. 조인
1. EQUI JOIN
-- 아래 두 구문은 같은 EQUI JOIN의 예시
SELECT PLAYER.PLAYER_NAME 선수명, TEAM.TEAM_NAME 팀명 FROM PLAYER, TEAM
WHERE PLAYER.TEAM_ID = TEAM.TEAM_ID;
SELECT PLAYER.PLAYER_NAME 선수명, TEAM.TEAM_NAME 팀명 FROM PLAYER, TEAM
INNER JOIN TEAM ON PLAYER.TEAM_ID = TEAM.TEAM_ID;
- ’=’ 연산자에 의해서만 수행됨
2. Non EQUI JOIN
-- 아래 두 구문은 같은 Non EQUI JOIN의 예시
SELECT P.PLAYER_NAME 선수명, P.POSITION 포지션, T.REGION_NAME 연고지, T.TEAM_NAME 팀명, S.STADIUM_NAME 구장명
FROM PLAYER P, TEAM T, STADIUM S
WHERE P.TEAM_ID = T.TEAM_ID
AND T.STADIUM_ID = S.STATIUM_ID
ORDER BY 선수명;
SELECT P.PLAYER_NAME 선수명, P.POSITION 포지션, T.REGION_NAME 연고지, T.TEAM_NAME 팀명, S.STADIUM_NAME 구장명
FROM PLAYER P
INNER JOIN TEAM T ON P.TEAM_ID = T.TEAM_ID
INNER JOIN STADIUM S ON T.STADIUM_ID = S.STADIUM_ID
ORDER BY 선수명;
- 두 테이블 간 칼럼 값들이 서로 정확하게 일치하지 않을 때 사용
- ’=’을 JOIN을 위한 연산자로 사용하지 않음 (BETWEEN, LIKE, >=, <=, >, < 등을 사용)
+ SQL자격검정시험 72p 62번 문제
SELECT 영화.영화명, 배우.배우명, 출연.출연료
FROM 영화, 배우, 출연
WHERE 영화.영화번호 = 출연.영화번호
AND 배우.배우번호 = 출연.배우번호
AND 출연.출연료 >= 8888;
SELECT 영화.영화명, 배우.배우명, 출연.출연료
FROM 출연
INNER JOIN 배우 ON 출연.배우번호 = 배우.배우번호
INNER JOIN 영화 ON 출연.영화번호 = 영화.영화번호
WHERE 출연료 >= 8888;
+ SQL자격검정시험 74p 64번 문제
EMP_TBL RULE_TBL
| EMPNO | ENAME | | RULE_NO | RULE |
|-------|-------| |---------|------|
| 1000 | SMITH | | 1 | S% |
| 1050 | ALLEN | | 2 | %T% |
| 1100 | SCOTT |
SELECT COUNT(*)
FROM EMP_TBL A, RULE_TBL B
WHERE A.ENAME LIKE B.RULE;
RULE 테이블과 아래처럼 EMP 테이블과의 연결이 2번 행해지기 때문에 총 4건을 찾을 수 있음
WHERE A.ENAME LIKE S% -- 2개 (SMITH, SCOTT)
WHERE A.ENAME LIKE %T% -- 2개 (SMITH, SCOTT)
+ SQL자격검정시험 74p 64번 문제
구매이력이 있는 고객 중 구매횟수가 3회 이상인 고객의 이름과 등급 출력하기
-- 테이블 스키마 정보
-- 고객(고객번호(PK), 이름, 등급)
-- 구매정보(구매번호(PK), 구매금액, 고객번호(FK))
SELECT A.이름, A등급
FROM 고객 A
INNER JOIN 구매정보 B ON A.고객번호 = B.고객번호
GROUP BY A.이름, A.등급
HAVING COUNT(B.구매번호) >= 3;