DML, DCL, 윈도함수, 그룹함수

데이터 조작어(DML)

• 데이터 조작어는 DB에 저장된 자료들을 SELECT, INSERT, UPDATE, DELTE하는 언어이다.

 

SELECT: 데이터 조회

SELECT [ALL|DISTINCT] column_name(s)
FROM table_name
[WHERE condition]
[GROUP BY column_name(s)]
[HAVING group_condition]
[ORDER BY column_name(s)[ASC|DESC]];

• ALL(default): 모든 튜플을 검색 / DISTINCT: 중복된 칼럼값이 조회되면 하나만 검색
• GROUP BY: 칼럼값을 기준으로 그룹으로 분류
• HAVING: GROUP BY에 의해 생성된 결과 중 원하는 조건에 부합하는 데이터만 확인
• ORDER BY: 칼럼값을 정렬, ASC/DESC 생략시 ASC로 정렬

// beverage 테이블에서 종류별로 등록된 음료의 수를 검색하세요.
SELECT kind, count(*) FROM beverage GROUP BY kind;

// 위와 같은 조건 + 단, 10가지 이상의 종류를 가진 음료만 검색하세요.
SELECT kind, count(*) FROM beverage GROUP BY kind HAVING count(kind)>=10;

 

INSERT: 데이터 삽입

INSERT INTO table_name(column_name(s))
VALUES(data(s));

• 칼럼, 데이터 개수, 데이터 타입이 일치해야함
• column_name은 생략 가능

 

UPDATE: 데이터 수정

UPDATE table_name
SET column_name(s) = data(s)
WHERE condition;

• 주로 WHERE을 통해 어떤 조건을 만족할 때만 특정 컬럼의 값을 수정하는 용도

 

DELETE: 데이터 삭제

DELETE FROM table_name
WHERE condition;

• 모든 레코드를 삭제할 때는 WHERE 없이 사용
• 레코드를 삭제해도 테이블 구조는 남아있어서 테이블을 완전히 삭제하려면 DROP을 써야함

---

데이터 제어어(DCL)

• 데이터 제어어는 DBA가 데이터 보안, 무결성 유지, 병행 제어, 회복을 위해 사용하는 언어
• 그 중 COMMIT, ROLLBACK, SAVEPOINT는 DCL이자 TCL(Transaction Control Language)라고 불림

 

GRANT: 사용권한 부여

• GRANT 권한 TO 사용자 → DBA가 사용자에게 테이블/뷰/프로시저 등을 생성,삭제할 수 있는 권한 부여
• GRANT 권한 ON 테이블 TO 사용자 → DBA가 사용자에게 테이블을 select,insert,update,delete하고 프로시저 실행할 권한 부여
• WITH GRANT OPTION → 다른 사용자에게도 권한을 부여할 수 있는 권한 부여

 

REVOKE: 사용권한 회수

• REVOKE 권한 FROM 사용자 → DBA가 사용자에게 테이블/뷰/프로시저 등을 생성,삭제할 수 있는 권한 회수
• REVOKE 권한 ON 테이블 FROM 사용자 → DBA가 사용자에게 테이블을 select,insert,update,delete하고 프로시저 실행할 권한 회수
• CASCADE CONSTRAINT → WITH GRANT OPTION으로 부여된 사용자들의 권한까지 회수

 

COMMIT: 트랜잭션 확정 (내용 업데이트 영구적으로 확정)

 

ROLLBACK: 트랜잭션 취소 (업데이트 오류 발생시 이전으로 되돌림)

 

SAVEPOINT 이름: 저장 시점 설정

 

ROLLBACK TO SAVEPOINT 이름: SAVEPOINT로 지정한 시점 이후에 발생한 트랜잭션 취소

INSERT INTO A VALUES(1);
SAVEPOINT SP; 
INSERT INTO A VALUES(2);
ROLLBACK TO SAVEPOINT SP; // 2 사라짐
COMMIT; // 1 확정
INSERT INTO A VALUES(3);
ROLLBACK; // 3 사라짐

 

---

윈도함수

• 행과 행간의 관계를 쉽게 정의하기 위해 만든 언어
• OLAP 함수: 사용자가 동일한 데이터를 여러 기준을 통해 다양한 방식으로 바라보며 의사결정을 하도록 도와주는 함수
• 집계함수, 순위함수, 행순서 함수, 그룹내 비율함수

SELECT WINDOW_FUNCTION(ARGUMENTS) OVER 
([PARTITION BY column_name(s)] 
[ORDER BY column_name(s)])
FROM table_name;

• OVER은 윈도함수에서 필수로 포함되어야 하고, 내부에 PARTITION BY와 ORDER BY가 들어간다.
• PARTITION BY: 순위를 정할 대상 범위의 컬럼을 지정 (GROUP BY와는 다르게 행 수가 줄어들지 않음)
• ORDER BY: 어떤 컬럼을 기준으로 순위를 정할지를 지정

GROUP BY

PARTITION BY

 

---

그룹함수

• 전체 행을 하나 이상의 컬럼을 기준으로 컬럼 값에 따라 그룹화하여 그룹별로 결과(소계,총계)를 출력하는 함수
• ROLLUP, CUBE, GROUPING SETS가 있다.

 

ROLLUP: 소그룹간 소계 출력

SELECT column_name(s), 집계함수
FROM table_name
[WHERE condition]
GROUP BY [column_name(s)] ROLLUP (column_name)
[HAVING group_condition]
[ORDER BY column_name(s)]

 

CUBE: GROUP BY 항목들과 다차원 소계 출력

SELECT column_name(s), 집계함수
FROM table_name
[WHERE condition]
GROUP BY [column_name(s)] CUBE (column_name)
[HAVING group_condition]
[ORDER BY column_name(s)]

 

GROUPING SETS: 특정 항목에 대한 소계 출력

SELECT column_name(s), 집계함수
FROM table_name
[WHERE condition]
GROUP BY [column_name(s)] GROUPING SETS (column_name)
[HAVING group_condition]
[ORDER BY column_name(s)]