SQL 더 쉽게, 더 깊게

1. DB 구성

클라이언트 : SQL을 주는(요청을 하는) 프로그램

서버: SQL을 처리하는 프로그램

 

2. SQL

DDL : Data Definition Language: CREATE, DROP, ALTER

DML : Data Manipulation Language: SELECT, INSERT, DELETE

DCL : Data Control Language: COMMIT(실행 확정), ROLLBACK(실행 취소), GRANT(권한 부여), REVOKE(권한 제거)

 

3. SQL작성 규칙

- 마지막에 ;

- 대문자, 소문자 구분이 없다.

 

4. 이름 짓기 규칙

- 영문자, 숫자, 언더바(_)만 가능

- 첫글자는 영어

 

5. SQL 규칙

1) 집약과 정렬

- WHERE은 FROM 바로 뒤에 온다.

- GROUPBY 는 WHERE 뒤에 온다.

- FROM -> WHERE -> GROUP BY -> HAVING -> ORDER BY

: 실행 순서는 WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY.

(따라서 GROUP BY에는 약칭 사용 불가)

(따라서 ORDER BY에는 약칭 사용 가능/ 집약 함수 사용 가능)

- 주석은 -- 와 /* */

- GROUP BY: 행에 대한 조건

- HAVING: 그룹에 대한 조건

 

6. 연산자

- =와 <>

- IS NULL ( NULL은 비교 연산시 -> 불명(UNKNOWN)

- AVG

 

7. transaction

세트로 실행해야할 하나 이상의 갱신 처리 집합

BEGIN TRANSACTION;

COMMIT;

 

ACID 특성

Atomic(원자성): 트랜잭션이 끝난 시점에 모든 갱신 처리가 실행된 상태/모두 실행되지 않은 상태로 종료되는 것을 보증

Consistency(일관성): 트랜잭션의 처리는 데이터베이스의 제약(NOT NULL 등)을 지킨다.

Isolation(독립성): 트랜잭션간에 서로 간섭하지 않는다.

Durability(지속성): 트랜젝션이 종료되면 해당 시점의 데이터 상태가 지속된다.

 

8. 뷰

- ORDER BY 사용 불가

- DISTINCT, GROUP BY, HAVING 사용 안하는 경우, FROM에 테이블이 하나인 경우, 갱신 가능

 

9. 서브쿼리

- 마지막에 AS로 이름을 붙인다.

- 스칼라 서브쿼리: 값이 하나인 서브쿼리 -> SELECT문이나 WHERE에 사용 가능

SELECT goods_id FROM Goods

WHERE sell_price > (SELECT AVG(sell_price) FROM Goods);

- 상품 분류별 평균 판매 가격을 비교하자.

SELECT goods_id, goods_name

FROM Goods AS S1

WHERE sell_price > (SELECT AVG(sell_price) FROM Goods AS S2

WHERE S1.goods_class = S2.goods_class 

GROUP BY goods_classs);

 

10. 함수

1) 함수

- || : 문자열 연결( 'abc' 'de -> 'abcde'

- LENGTH : 문자열 길이

- REPLACE(<col>, <from>, <to>)

- SUBSTRING(<col> FROM <start> TO <counts>)

- CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP

- EXTRACT(<datetime type> FROM <date>) : 날짜 요소 추출

- CAST(<pre> AS <post>) : 형변환

- COALESCE(<data1> <data2> ...): 처음으로 NULL이 아닌 값 반환

 

2) 술어

 - LIKE , %

- BETWEEN:

SELECT * FROM <table> WHERE sell_price BETWEEN 10 AND 100;

- IN: 서브쿼리 사용 가능

SELECT * FROM <table> WHERE goods_id IN (1,2,3);

SELECT * FROM <table> WHERE goods_id IN (SELECT goods_id FROM Storegoods

WHERE sell_price > 500);

- EXIST: 조건을 만족하는 레코드가 존재하는가?

SELECT * FROM <table> WHERE EXISTS (SELECT goods_id FROM Storegoods AS TS

WHERE TS.sell_price > 500

AND TS.store_id = '00C');

 

3) CASE

검색 case문

SELECT goods_name, 

CASE WHEN goods_class  = '의류' THEN 'A:' || goods_class

 WHEN goods_class  = '사무용품' THEN 'B:' || goods_class

 WHEN goods_class  = '주방용품' THEN 'C:' || goods_class

 ELSE NULL

END AS abc_class

FROM Goods;

 

단순 case문

SELECT goods_name,

CASE goods_class  WHEN '의류' THEN 'A:' || goods_class

 goods_class WHEN '사무용품' THEN 'B:' || goods_class

 goods_class WHEN '주방용품' THEN 'C:' || goods_class

 ELSE NULL

END AS abc_class

FROM Goods;

 

4) 집합 연산

<SELECT문>

UNION

<SELECT문>

 - 열의 수와 데이터 타입이 동일할것

- UNION ALL은 중복 허용

- INTERSECT

- EXCEPT: 차집합

 

5) JOIN

INNER JOIN, OUTER JOIN

FROM 뒤에 온다. FROM과 한몸!

FROM -> JOIN -> WHERE

 

11. 윈도우 함수

<윈도우 함수> OVER (PARTITION BY <columns> ORDER BY <columns>)

PARTITION BY 생략 가능

 

1) RANK : 순위

SELECT goods_name, RANK() OVER (PARTITION BY goods_class ORDER BY sell_price)

FROM Goods;

2) DENSE_RANK: 중복이 있어도 후순위를 건너뛰지 않음.

3) ROW_NUMBER: 순위에 상관 없이 연속 번호

 

4) 집약 함수 사용

-1) SUM

SELECT goods_name, SUM(sell_price) OVER (ORDER BY goods_id) AS current_sum

FROM Goods;

누적 합계

 

-2) 이동평균

SELECT goods_name, SUM(sell_price) OVER (ORDER BY goods_id ROWS 2 PRECEDING) AS current_sum

FROM Goods;

앞의 2 행을 포함한 이동평균.

FOLLOWING 사용 가능

ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING

 

12. GROUPIN 함수

1) ROLLUP : 그룹별 값 -> 그룹을 통합한 값

2) GROUPIN

3) CUBE

4) SETS

 

 

 

CREATE DATABASE <>;

CREATE TABLE <>;

DROP TABLE <>;

◆ ALTER TABLE <> ADD COLUMN <column name> <datatype> <constraint>;

◆ ALTER TABLE <> DROP COLUMN <>;

◆ ALTER TABLE <> RENAME TO <>; RENAME TABLE <> TO <>;

 

◆ SELECT '상수' AS <sangsu column name> FROM <>;

◆ SELECT DISTINCT <> FROM <>;

 

◆ SELECT DISTINCT COUNT(goods_classify) FROM GOODS; 행수를 센 뒤에 중복 제거

◆ SELECT SUM(DISTINCT sell_price) FROM GOODS; 중복 제거 뒤 합.

 

◆

INSERT INTO <table name> (<column names>) VALUES (<values>);

INSERT INTO <table name> VALUES (<values>);

INSERT INTO <table name> (<column names>)

SELECT <columns> FROM <table name>;

 

DELETE <> FROM <> WHERE ;

UPDATE <table name> SET <column name> = <value> WHERE <>;

UPDATE <table name> SET sell_price = sell_price * 10;

 

CREATE VIEW <>  (<view colums>)

AS

<select 문>;