[TIL] SQL 베이직 - 5회차

1. 관리구문

- DML

 Data Manipulation Language. 데이터 입력/수정/삭제하는 명령어이다.

- TCL

 Transaction Control Language. 트랜잭션(Transaction)을 제어하는 명령어이다.

 트랜잭션이란 쪼갤 수 없는 업무처리 단위를 말한다. 무조건 한 세트로 묶여야 하는 논리적인 업무 단위로 2가지 액션이 있을 때 하나로 묶여 한 번에 동작이 되어야 한다.

- DDL

 Data Definition Language. 데이터를 정의하는 명령어이다.

- DCL

 Data Control Language. user를 생성하고 권한을 부여하는 명령어이다.

구분	명령어	설명
-	SELECT	-
DML	INDERT	데이터 입력
	UPDATE	데이터 수정
	DELETE	데이터 삭제
TCL	COMMIT	INSERT/UPDATE/DELETE 명령어를 확 정/반영하는 명령어
	ROLLBACK	INSERT/UPDATE/DELETE 명령어를 취소 하는 명령어
	SAVEPOINT	ROLLBACK 수행 시 전체 작업을 되돌리는 것이 아니라 일부만 되돌릴 수 있게 하는 기 능을 가짐
DDL	CREATE	테이블 생성
	ALTER	테이블 구조 변경 (ex. 새로운 컬럼 추가)
	DROP	테이블 삭제
	RENAME	테이블명 변경
	TRUNCATE	테이블에 저장되어 있는 데이터 모두 삭제
DCL	GRANT, REVOKE, CREATE USER, ALTER USER, DROP USER	권한 관련 명령어

 

2. DML 구문

- INSERT: 테이블에 데이터 입력

-- 특정 컬럼에만 값을 넣을 경우
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

-- 모든 컬럼에 값을 넣을 경우 column 생략 가능.
-- 테이블에 있는 컬럼의 순서와 같은 순서로 값을 넣어주어야함. 
INSERT INTO table_name
VALUES (value1, value2, value3, ...);

 

- UPDATE: 테이블에 있는 데이터 수정

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

 

- DELETE: 데이블에 있는 데이터 삭제

DELETE FROM table_name WHERE condition;

 

3. TCL 구문

- COMMIT

 INSERT/UPDATE/DELETE 명령어를 확정(반영)하는 명령어이다.

- ROLLBACK

 INSERT/UPDATE/DELETE 를 취소하는 명령어로 COMMIT을 취소하는 명령어는 아니다.

- SAVEPOINT

ROLLBACK 을 수행할 때 전체 작업을 되돌리지 않고 일부만 되돌릴 수 있게 하는 기능을 가진 명령어이다.

SAVEPOINT 세이브포인트명; (ex. SAVEPOINT A)
ROLLBACK TO 세이브포인트명 (ex. ROLLBACK TO A)

 

- AUTO COMMIT 설정 확인

show variables like 'autocommit%';
SELECT @@autocommit; -- 0이면 false, 1이면 true

 

- 트랜잭션과 섹션

 1) 트랜잭션: 데이터베이스 작업의 한 단위로 트랜잭션 내에서 이루어진 변경사항은 COMMIT을 실행하기 전까지 다른 세션에서는 보이지 않는다.

 2) 세션: 각 데이터베이스 클라이언트는 독립적인 세션을 가진다. 세션 간에는 트랜잭션이 격리되어 있으며 한 세션에서의 트랜잭션 변경 사항은 COMMIT 이 실행되기 전까지는 다른 세션에서 보이지 않는다(동일한 세션의 경우 COMMIT을 실행하지 않았더라도 트랜잭션이 동일한 세션 내에서 발생한 것이므로 보임).

 

4. DDL 구문

- CREATE

CREATE TABLE STUDENT(
STD_NO INT NOT NULL,
STD_NAME VARCHAR(50) NOT NULL,
STD_YN VARCHAR(2) DEFAULT 'N');

SELECT * FROM STUDENT;

 

- ALTER

ALTER TABLE STUDENT ADD STD_SCORE FLOAT; -- 컬럼 추가
-----------------------------------------------------------------------------------------------
ALTER TABLE STUDENT DROP COLUMN STD_SCORE; -- 컬럼 삭제
-----------------------------------------------------------------------------------------------
ALTER TABLE STUDENT MODIFY STD_NAME VARCHAR(100); -- 컬럼 변경
-----------------------------------------------------------------------------------------------
ALTER TABLE STUDENT RENAME COLUMN STD_NO TO STD_NUMBER; -- 컬럼명 변경

 

- RENAME

RENAME STUDENT TO STD -- 테이블 명 변경
-----------------------------------------------------------------------------------------------
RENAME STD TO STUDENT -- 테이블 명 변경

 

- TRUNCATE TABLE

TRUNCATE TABLE STUDENT  -- 테이블 내 저장되어 있는 모든 데이터 제거. 
-- delete 와 유사하지만 roll back 불가능함으로 조심 !

 

- DROP

DROP TABLE STUDENT; --테이블 삭제

 

4. 그룹 함수 - 소계(총계) 함수

 특정 컬럼을 기준으로 데이터를 요약해서 비교하고 싶을 때 GROUP BY + 집계 함수를 사용한다. 집계함수의 종류로는 COUNT, SUM, AVG, MAX, MIN 등이 있다.

 My SQL의 대표적인 소계(총계) 함수에는 ROLLUP이 있다. ROLLUP 함수는 SQL 집계 데이터를 생성할 때 유용한 기능으로 여러 수준의 집계 데이터를 한 번에 쉽게 생성할 수 있다.

 복잡한 집계 쿼리를 간단하게 작성할 수 있어 쿼리의 가독성을 높이고 유지보수를 용이하게 하는 장점이 있다.

구분	설명
ROLLUP (A)	- A로 그룹핑 - 총합계
ROLLUP(A, B)	- A, B로 그룹핑 - A로 그룹핑 - 총합계
ROLLUP(A, B, C)	- A, B, C로 그룹핑 - A, B로 그룹핑 - A로 그룹핑 - 총합계

ORDER_DT를 ROLLUP / ORDER_DT, ORDER_ITEM을 ROLLUP

-- order_dt를 ROLLUP
SELECT
	ORDER_DT,
	COUNT(*)
FROM
	ORDERS
GROUP BY
	ROLLUP(order_dt)
    /*(ORDER_Dt) with ROLLUP 도 동일한 기능을 합니다. 버전에 따라 이 구문만 실행 가능한 DB도 있어요.*/
ORDER BY
	ORDER_DT;
-----------------------------------------------------------------------------------------------
-- order_dt, order_item를 ROLLUP
SELECT
	ORDER_DT,
	order_item, 
	COUNT(*)
FROM
	ORDERS
GROUP BY
	ROLLUP(order_dt, order_item)
	/*또는 order_dt, order_item WITH ROLLUP*/
ORDER BY
	ORDER_DT, ORDER_ITEM;

 

5. WINDOW 함수 (feat. OVER 절)

 WINDOW 함수는 현재 행과 관련된 일련의 행에서 계산을 수행하는 함수 클래스이다. OVER() 절과 함께 사용되며 결과 집합의 특정 행 WINDOW를 기반으로 계산을 수행할 수 있다.

-- [] 안에 있는 내용은 옵션으로, 필수적으로 작성해야하는 것이 아니라 필요에 따라 작성하면 됩니다. 
SELECT 윈도우함수([대상]) OVER ([PARTITION BY 컬럼] -- 연산할 그룹 묶기
				[ORDER BY 컬럼 ASC|DESC]  -- 정렬 순서 지정
		 		[ROW|RANGE BETWEEN A AND B]) -- 연산범위 설정
-----------------------------------------------------------------------------------------------
-- 스칼라 서브쿼리 사용시
SELECT 이름, 나이, (SELECT AVG(나이) FROM THEGLORY) FROM THEGLORY; 
-----------------------------------------------------------------------------------------------
-- WINDOW 함수 사용시
SELECT 이름, 나이, AVG(나이) OVER() FROM THEGLORY; 
-- 이때 윈도우함수는 'AVG', 대상은 '나이', partition by와 order by 절은 생략.

구분	함수	설명
(1) 순위 함수	RANK	순위를 매기면서 같은 순위가 존재하면 존재하는 수만큼 다음 순위를 건너 뜀
	DENSE_RANK	순위를 매기면서 같은 순위가 존재하더라도 다음 순위를 건너 뛰지 않고 이어서 매김
	ROW_NUMBER	순위를 매기면서 동일한 값이라도 각기 다른 순위를 부여
(2) 집계 함수	SUM	데이터의 합계를 구하는 함수
	MAX	데이터의 최댓값을 구하는 함수
	MIN	데이터의 최솟값을 구하는 함수
	AVG	데이터의 평균 값을 구하는 함수
	COUNT	데이터의 건수를 구하는 함수
(3) 행 순서 함수	FIRST_VALUE	파티션 별 가장 선두에 위치한 데이터를 구하는 함수
	LAST_VALUE	파티션 별 가장 끝에 위치한 데이터를 구하는 함수
	LAG	파티션 별 특정 수만큼 앞선 데이터를 구하는 함수
	LEAD	파티션 별 특정 수만큼 뒤에 있는 데이터를 구하는 함수
(4) 비율 함수	CUME_DIST	파티션 별 누적 백분율을 구하는 함수(결과값음 0보다 크고 1보다 작거나 같은 값을 가짐)
	PERCENT_RANK	파티션 별 맨 위 끝 행을 0, 맨 아래 끝 행을 1로 놓고 현재 행이 위치하는 백분위 순위 값을 구하는 함수
	NTILE	구어진 수만큼 행을 n등분한 후 현재 행에 해당하는 등급을 구하는 함수