[SQLD 자격증 대비반] 챕터 10. TCL과 DCL

집계 함수

• 여러 데이터들의 정보를 집계하여 연산을 해주는 함수
• GROUP BY 절에서 작성한 칼럼 기준으로, 그룹으로 모인 상태에서 각 그룹의 집계를 계산하는 데 사용
• 기본 구조
  • ALL
    - 모든 값을 기준으로 집계할 때 사용하는 옵션
    - 기본값이므로 생략 가능
  • DISTINCT
    - 같은 값을 하나의 데이터로 간주할 때 사용하는 옵션
    - SELECT 문의 결과에서 유일한 하나의 행만 출력

집계 함수명( [DISTINCT | ALL] 칼럼이나 표현식 )

 

집계 함수 종류

COUNT(*)	NULL 값을 포함한 행의 수 출력
COUNT(표현식)	표현식 값이 NULL 값인 것을 제외한 행의 수 출력
SUM([DISTINCT | ALL] 표현식)	표현식의 NULL 값을 제외한 합계 출력
AVG([DISTINCT | ALL] 표현식)	표현식의 NULL 값을 제외한 평균 출력
MAX([DISTINCT | ALL] 표현식)	표현식의 NULL 값을 제외한 최댓값 출력 (문자, 날짜 타입 사용 가능)
MIN([DISTINCT | ALL] 표현식)	표현식의 NULL 값을 제외한 최솟값 출력 (문자, 날짜 타입 사용 가능)
STDDEV([DISTINCT | ALL] 표현식)	표현식의 NULL 값을 제외한 표준 편차 출력
VARIAN([DISTINCT | ALL] 표현식)	표현식의 NULL 값을 제외한 표현식의 분산 출력
기타 통계 함수	DBMS를 제공하는 회사 별로 다양한 통계식 제공 표현식의 NULL 값 제외

 

집계 함수 특징

• 집계 함수는 COUNT(*)를 제외하고는 NULL 값 제외
• 집계 함수는 WHERE 절에 올 수 없음
  ➡️WHERE 절이 GROUP BY 절보다 먼저 수행되기 때문에 행들이 소그룹으로 묶이기 전에 집계 함수가 실행되어 제대로된 집계를 할 수 없어지기 때문

 

GROUP BY 절

• 데이터들을 작은 그룹으로 분류하여 해당 그룹에 대한 항목별 정보를 얻고자 할 때 사용
• GROUP BY 절을 사용할 때는 SELECT 절과는 다르게 ALIAS 명을 사용할 수가 없음
• WHERE 절은 전체 데이터를 GROUP으로 나누기 전에 행들을 미리 주어진 조건에 맞춰 가져옴
• GROUP BY로 그룹 지은 상태에서 원하는 조건으로 필터링하려면 HAVING 절 사용
  HAVING 절은 GROUP BY 절의 기준 항목이나 소그룹의 집계 함수를 이용한 조건 표시 가능
• GROUP BY 절에 의한 소그룹 별로 만들어진 집계 데이터 중, HAVING 절에서 제한 조건을 두어 만족하는 내용만 출력 가능
• 원칙적으로 관계형 데이터베이스 환경에서는 ORDER BY 절을 명시해야 데이터가 정렬됨
• GROUP BY 절보다 WHERE 절이 먼저 수행되기 때문에 집계 함수는 WHERE 절에 올 수 없음

SELECT [DISTINCT] 칼럼명 [ALIAS명] 
FROM 테이블명 
[WHERE 조건식] 
[GROUP BY 칼럼이나 표현식] 
[HAVING 그룹조건식] ;

 

 

HAVING 절

• WHERE절과 같이 특정 조건을 만족하는 결과 데이터만 표시할 수 있으나, 집계된 결과에 대해 사용한다는 것이 특징
• 집계된 결과를 기준으로 조건이 필요한 경우 HAVING절을 활용
• WHERE 절과 비슷하지만 그룹을 나타내는 결과의 행에 조건이 적용된다는 점에서 차이가 있음
• GROUP BY 절과 함께 사용하여 특정한 제한 조건의 그룹화된 내용을 필터링

	HAVING	WHERE
조건에서 집계 함수 사용 가능 여부	가능	불가능
단독 사용 가능 여부	불가능 GROUP BY와 함께 사용 가능	가능 GROUP BY 이전에 WHERE 절 먼저 실행
기타 특징	SELECT 절에 사용되지 않은 칼럼이나 집계 함수가 아니더라도 GROUP BY 절의 기준 항목이나 집계 함수를 이용하여 조건을 표시할 수 있음	WHERE 절이 GROUP BY 보다 먼저 실행되기 때문에 그룹으로 묶인 데이터의 개수가 WHERE의 조건에 따라 다를 수 있음

 

 

집계 함수와 NULL

• 조회 결과 중 NULL이 있는 경우 집계 연산을 하기 위해 NVL 혹은 ISNULL 함수를 사용하여 0으로 변경 가능
  ➡️ 함수를 집계 함수 내부에 넣어 사용하는 경우 불필요한 부하 발생
• 집계 함수는 NVL, ISNULL과 같은 함수를 내부에서 사용하지 않아도 NULL 데이터는 집계 함수의 대상에서 제외하고 함수 연산 처리 ( COUNT(*)는 NULL을 포함하여 모든 칼럼을 계산해서 제외)

 

ORDER BY

• SQL 문장으로 조회되는 데이터들을 특정 칼럼을 기준으로 출력하기 위해 사용
• ORDER BY절에 칼럼명 대신 ALIAS 사용 가능 또는 1 부터 칼럼의 순서를 정수로 명시해도 됨
• 기본적으로는 오름차순 정렬이며 내림차순으로 정렬하기 위해서는 DESC 옵션 추가
  - 숫자는 작은 값부터 오름차순 정렬
  - 날짜는 가장 빠른 날짜부터 오름차순 정렬
• NULL에 대하여 ORDER BY 했을 때 처리하는 부분이 DBMS를 제공하는 회사별로 처리 방식 다름
  - Oracle은 NULL을 가장 큰 값으로 간주
  - SQL Server는 NULL을 가장 작은 값으로 간주

 

SELECT 문 실행 순서

FROM > WHERE > GROUP BY > HAVING > SELECT > ORHER BY > LIMIT

• GROUP BY 절에서 그룹화될 칼럼을 정의하게 되면 그룹될 칼럼과 집계 함수에 사용될 수 있는 숫자형 데이터 칼럼의 집합을 새로 만들게 됨
• 그렇게 되면 GROUP BY 이후 실행되는 SELECT 절이나 ORDER BY 절에서는 새롭게 만들어진 데이터에 접근하기 때문에 기존 테이블의 일반 칼럼을 사용할 경우 에러 발생
• 다만 ORDER BY 절은 집계 함수를 이용한 정렬은 정상적으로 이루어짐

SELECT job, sal FROM emp GROUP BY job HAVING COUNT(*) > 0 ORDER BY sal;
--> Error 발생, sal 은 GROUP BY로 job 이 그룹화 될 것이기 때문에 
-- 일반 칼럼 sal 사용으로 에러가 발생

SELECT job FROM emp GROUP BY job HAVING COUNT(*) > 0 ORDER BY sal;
--> Error 발생, ORDER BY 에서 일반 칼럼 sal 을 사용함

SELECT job FROM emp GROUP BY job HAVING COUNT(*) > 0 
ORDER BY MAX(empno), MAX(mgr), SUM(sal), COUNT(deptno), MAX(hiredate);
--> 정상 조회 가능, ORDER BY 에서 집계 함수를 이용한 정렬은 가능함

 

 

TOP N 쿼리

• 상위 N개의 데이터를 보여주기 위한 쿼리
• ROWNUM(Oracle)
  • 조회된 데이터에 번호를 매겨 원하는 개수의 데이터를 가져올 수 있음
  • 정렬 후 데이터 일부가 ROWNUM으로 추출되는 것이 아니라 WHERE절에 작성된 ROWNUM으로 데이터의 일부가 먼저 추출이 되고 나중에 데이터 정렬 작업이 일어남 (추출 후 정렬)
    ➡️즉, 상/하위 n개의 데이터 조회가 아닌 랜덤한 n개를 가져온 후 정렬
• TOP(SQL Server)
  • ORDER BY로 데이터 정렬 후 원하는 데이터 일부를 추출 가능 (정렬 후 추출)
    ➡️상/하위 n개의 데이터 조회 가능

 

TCL(Transaction Control Language)

• 데이터 삽입, 삭제, 수정하는 DML 수행 이후 COMMIT이나 ROLLBACK을 하기 위해 사용
• 트랜잭션 : 논리적인 연산의 단위
• 트랜잭션의 기본 특성(ACID)
  • 원자성(Atomicity)
    - 모든 연산은 '최소한의 조작 묶음'이기 때문에 하나의 트랜잭션을 완료했다는 말은 모든 연산이 정확하게 수행되었다는 것을 의미
    - 조작 과정에서 하나의 논리적인 연산이라도 실패하게 된다면 단순하게 실패 상태로 남는 것이 아닌 아무것도 수행하지 않은 상태로 남아있어야 함 (All or Nothing)
  • 일관성(Consistency)
    - 트랜잭션이 수행하기 전의 상태 그리고 수행하고 나서의 상태도 잘못된 내용이 없어야 함
  • 고립성/격리성(Isolation)
    - 트랜잭션이 다른 트랜잭션에 영향을 받아 잘못된 결과를 만들어선 안됨
    - 각각의 트랜잭션은 개별적으로 동작이 보장되어야 함
  • 지속성(Durability)
    - 트랜잭션이 성공적으로 수행된다면 그 결과를 갱신한 데이터베이스는 내용을 영구적으로 유지하고 있어야 함
    - 특정 시간만 유지되고 다시 돌아가는 불안정한 부분이 있어선 안됨
• COMMIT
  • 입력한 자료나 수정한 자료 혹은 삭제한 자료에 대해 문제가 없다고 판단되었을 때 최종 트랜잭션을 확정하는 명령어
  • COMMIT하지 않아도 변경된 내용을 SELECT문을 통해 조회 가능하지만 다른 사용자는 현재 변경된 내용에 접근하여 데이터의 변화를 확인할 수 없으며 변경중인 데이터베이스도 Locking되어 다른 사용자가 변경 불가능
  • SQL Server는 기본적으로 AUTO COMMIT 모드이므로 사용자가 직접 COMMIT 호출할 필요 없음
    만약 에러가 말생하면 자동적으로 ROLLBACK 처리가 되어 이전 데이터 값으로 복구됨

-- Oracle
SQL 쿼리문
COMMIT;

-- SQL Server
BEGIN {TRANSACTION|TRAN}
SQL 쿼리문
COMMIT [TRANSACTION];

 

• ROLLBACK
  • 트랜잭션 진행중 에러가 발생하거나 잘못된 연산이 이뤄진 경우 변경 전 가장 최신 상태로 되돌리는 명령어
  • COMMIT되지 않은 모든 데이터 변경 사항들이 취소되어 데이터 이전 상태로 복구되고 관련된 Locking이 풀리며 다른 사용자들이 데이터 변경 가능해짐

-- Oracle
SQL 쿼리문
ROLLBACK;

-- SQL Server
BEGIN {TRANSACTION|TRAN}
SQL 쿼리문
ROLLBACK [TRANSACTION];

• COMMIT과 ROLLBACK 사용시 장점
  • 데이터 무결성 보장
  • 영구적 변경 전 데이터의 변경 사항 확인 가능
  • 논리적으로 연관된 작업을 묶어(Grouping) 처리 가능
• COMMIT과 ROLLBACK 사용 없이 트랜잭션이 종료되는 경우
  • DDL 문장 실행 시 자동으로 COMMIT
  • DML 문장 이후 COMMIT 없이 DDL 문장 실행될 때
  • 정상적으로 DB 접속 종료하면 COMMIT
  • 장애로 인한 비정상 종료로 DB 접속이 단절되었을 경우 AUTO ROLLBACK
• COMMIT과 ROLLBACK 사용시 차이
  1. DML의 데이터 삽입, 수정, 삭제 이후 COMMIT or ROLLBACK 전 데이터 상태
     - 데이터베이스에 최종적으로 적용된 상태가 아니라 메모리 영역에만 적용된 상태이기 때문에 변경 전 상태로 복구 가능
     - SELECT 문을 통해 삽입, 수정, 삭제 결과 조회 가능 (작업자 외 다른 사람은 불가능)
     - 변경된 행은 Locking 설정으로 다른 사용자가 변경 불가능
  2. DML의 데이터 삽입, 수정, 삭제 이후 COMMIT한 상황
     - 데이터 변경 내역이 DB에 완전하게 반영
     - COMMIT 실행 시점 이후에는 해당 구문의 실행으로 인한 변화로 이전 데이터 완전 삭제
     - 작업자 포함 모든 사람이 SELECT 문을 통해 동일한 결과 얻음
     - 변경된 행의 Locking이 모두 풀리고 모든 사용자는 특정 행에 대한 조작 가능
• SAVEPINT
  • 트랜잭션의 크기가 크고 시스템 부하를 많이 주는 경우 그냥 ROLLBACK하는 경우 처음으로 되돌아가기 때문에 비효율적일 수 있음
  • 이때 트랜잭션 중간에 SAVEPOINT를 설정하면 해당 지점까지만 되돌려서 시스템 부하를 줄일 수 있음

-- Oracle
SAVEPOINT 저장점_이름;
ROLLBACK TO 저장점_이름;

-- SQL Server
SAVE TRANSACTION 저장점_이름;
ROLLBACK TRANSACTION 저장점_이름;

•  

트랜잭션 병렬 처리시 문제점

• Dirty Read
  • 다른 c트랜잭션에 의해 수정이 되었지만 아직 커밋되지 않은 데이터를 읽는 경우 발생 가능
  • 이때 중간에 트랜잭션이 취소되는 경우 잘못된 정보를 읽은 상태가 되기 때문에 문제 발생
    
• Non-Repeatable Read
  • 한 트랜잭션 내에서 같은 쿼리를 두 번 수행했는데, 그 사이에 다른 트랜잭션이 값을 수정 또는 삭제해서 두 쿼리 결과가 다르게 나타나는 현상 의미
    
• Phantom Read
  • 같은 쿼리를 두 번 수행했는데 중간에 다른 트랜잭션으로 인해 값이 추가되어 적용된다면 첫 번째 쿼리에서 없던 유령(Phantom) 데이터가 두 번째 쿼리 결과로 나타나는 문제점 발생
    

 

DCL

• 특정 유저의 권한을 제어할 수 있는 명령어
• 권한을 부여할 수 있는 GRANT문과 권한을 회수할 수 있는 REVOKE문
• DDL과 마찬가지로 COMMIT, ROLLBACK 없이 실행하는 순간 적용
• 사용자(User) : 사용자 계정과 패스워드를 통해 DBMS에 접속 가능
• 스키마(Schema) : 사용자가 소유하고 있는 오브젝트의 모음

사용자 계정 생성	CREATE USER user IDENTIFIED BY “password”;
사용자 비밀번호 변경	ALTER USER user IDENTIFIED BY “password”;
사용자 계정 잠금/해제	ALTER USER user ACCOUNT LOCK/UNLOCK;
사용자 계정 삭제	DROP USER user [CASCADE];
사용자 권한 부여	시스템 권한 GRANT privilege TO user [WITH ADMIN OPTION]; 오브젝트 권한 GRANT privilege ON object TO [WITH GRANT OPTION]; *privilege : 부여할 권한 *object : 대상 오브젝트 *WITH ADMIN/GRANT OPTION : 부여 받은 권한을 다른 사용자에게 부여하거나 회수할 수 있음
사용자 권한 회수	시스템 권한 REVOKE privilege FROM user; 오브젝트 권한 REVOKE privilege ON object FROM user;

• 유저와 권한
  • 데이터 공유를 위해 기존 운영자가 아닌 새로운 대상에게 데이터베이스를 오픈해야 하는 경우 DCL을 이용하여 유저에 권한을 부여하여 데이터 보호 가능
  • 권한이란 특정 타입의 SQL 문을 실행하거나 데이터베이스나 객체에 접근할 수 있는 권리를 의미
  • 오라클 기본 유저
    

    SYS	DBA ROLE을 부여받은 최상위 유저 DB 생성/제거 가능	CHANGE_ON_INSTALL
    SYSTEM	DBA ROLE을 부여받은 유저 DB 생성/제거 불가능	MANAGER

  • Oracle은 유저를 통해 데이터베이스에 접속하는 형태
    즉, 아이디와 비밀번호 방식을 통해 인스턴스에 접속하고 그에 해당하는 스키마에 권한을 부여받는 구조
  • SQL Server는 유저 생성 전 먼저 로그인을 생성해야 함. 유저 생성 후 로그인과 유저를 매핑
    로그인을 생성할 수 있는 권한을 가진 로그인은 기본적으로 sa
    특정 유저는 특정 DB 내 특정 스키마에 대한 권한 부여받을 수 있음

--1. Oracle SQL Plus 사용
CONN SCOTT/TIGER;

--2. ERROR 발생
CREATE USER PJS IDENTIFIED BY KOREA7; 

--3. 유저 생성 권한 부여
CONN SYSTEM/비밀번호;
GRANT CREATE USER TO SCOTT; 

--4. 정상 동작
CONN SCOTT/TIGER;
CREATE USER PJS IDENTIFIED BY KOREA7;  

--5. ERROR 발생 
CONN PJS/KOREA7; 

--6. 로그인 권한 부여
CONN SYSTEM/비밀번호;
GRANT CREATE SESSION TO PJS;  

--7. 정상 동작
CONN PJS/KOREA7;  

--8. ERROR 발생
CREATE TABLE MENU (MENU_SEQ NUMBER NOT NULL, TITLE VARCHAR2(10));  

--9. 테이블 생성 권한 부여
CONN SYSTEM/비밀번호;
GRANT CREATE TABLE TO PJS;  

--10. 정상 동작
CONN PJS/KOREA7;
CREATE TABLE MENU (MENU_SEQ NUMBER NOT NULL, TITLE VARCHAR2(10));

-- SQL Server
--1. 유저 매핑
CREATE LOGIN PJS WITH PASSWORD='KOREA7', DEFAULT_DATABASE=AdventureWorks

--2. 데이터베이스로 이동하여 유저 생성
USE ADVENTUREWORKS;
GO CREATE USER PJS FOR LOGIN PJS WITH DEFAULT_SCHEMA = dbo;

--3. ERROR 발생
CREATE TABLE MENU (MENU_SEQ INT NOT NULL, TITLE VARCHAR(10)); 

--4. 권한 부여
GRANT CREATE TABLE TO PJS;  

--5. 스키마에 권한 부여
GRANT Control ON SCHEMA::dbo TO PJS 

--6. 정상 동작
CREATE TABLE MENU (MENU_SEQ INT NOT NULL, TITLE VARCHAR(10));

 

 

권한

• 사용자가 실행하는 모든 DDL 문장은 그에 해당하는 적절한 권한이 있어야 문장 실행 가능
• 크게 시스템 권한, 객체 권한, ROLE을 이용한 권한으로 나눠짐
• 시스템 권한
  • 사용자가 DB에서 특정 작업을 수행할 수 있도록 함
  • system_privilege : 부여할 시스템 권한 이름
  • role : 부여할 DB 역할 이름
  • user, role : 부여할 사용자 이름과 다른 DB 역할 이름
  • PUBLIC : 시스템권한 또는 DB 역할을 모든 사용자에게 부여 가능
  • WITH ADMIN OPTION : 권한을 부여받은 사용자도 부여받은 권한을 다른 사용자 또는 역할로 부여 가능

-- 기본 구조
GRANT [system_privilege|role] TO [user|role|PUBLIC]
[WITH ADMIN OPTION];

• Oracle 예시 

-- SYS 권한으로 접속합니다.	
CONN sys/비밀번호 

-- scott 사용자에게 사용자를 생성, 수정, 삭제 할 수 있는 권한을 부여하고
-- scott 사용자도 다른 사용자에게 그 권한을 부여 할 수 있도록 권한 부여합니다.
GRANT CREATE USER, ALTER USER, DROP USER TO scott
WITH ADMIN OPTION;

-- scott 사용자에게 부여한 생성, 수정, 삭제 권한을 회수합니다.
REVOKE CREATE USER, ALTER USER, DROP USER
FROM scott;

• 객체권한
  • USER가 소유하고 있는 특정 객체를 다른 사용자들이 엑세스하거나 조작할 수 있게 하기 위해 생성
  • 객체 권한을 통해 특정 객체인 테이블 등에 대한 SELECT, INSERT, DELETE, UPDATE 작업 명령어 수행 가능
  • 모든 유저는 각각 자신이 생성한 테이블 외에 다른 유저의 테이블에 접근하려면 해당 테이블에 대한 오브젝트 권한을 소유자로부터 부여받아야 함
  • 객체 소유자는 다른 사용자에게 특정 객체권한을 부여할 수 있으며 기본적으로 소유한 객체에 대해서는 모든 권한이 자동적으로 획득됨
    

    

  • SQL Server도 Oracle과 같은 방식으로 동작하지만 한가지 다른 점은 SQL Server 유저는 단지 스키마에 대한 권한만을 가진다는 점
• 객체 권한 부여

GRANT object_privilege [column]
ON object
TO {user[.user]|role|PUBLIC}
[WITH GRANT OPTION];

• 객체 권한 회수

REVOKE {privilege[.privilege] | ALL}
ON object
FROM {user[.user] | role | PUBLIC}
[CASCADE CONSTRAINTS];

• WITH ADMIN OPTION
  • 시스템 권한을 가진 계정인 DB만 부여가 가능합니다.
  • 자신이 부여받은 권한에 대해서 다른 계정의 사용자에게 권한을 부여할 수 있습니다.
  • 권한 회수 시 회수한 유저의 권한만 회수됩니다.
• WITH GRANT OPTION
  • 자신이 부여받은 권한에 대해서 다른 계정의 사용자에게 권한을 부여할 수 있습니다.
  • 권한을 회수할 경우 연쇄적으로 권한이 회수됩니다.
• ROLE
  • 사용자에게 허가할 수 있는 권한들의 집합
  • DB 관리자는 ROLE을 생성하고 각종 권한들을 부여한 후 이를 다른 ROLE이나 유저에게 부여 가능
    

CONN SYSTEM/비밀번호;

-- ① ROLE의 생성합니다.
CREATE ROLE LOGIN_TABLE; 
-- ② ROLE에 권한 부여합니다.
GRANT CREATE SESSION, CREATE TABLE TO LOGIN_TABLE; 
-- ③ ROLE을 사용자 또는 ROLE에게 부여합니다.
GRANT LOGIN_TABLE TO CHELSEA;

• ROLE을 삭제하기 위해선 USER를 DROP하는 쿼리 작성

CONN SYSTEM/비밀번호;

-- CHELSEA가 생성한 오브젝트를 삭제한 이후에 유저를 삭제합니다.
DROP USER CHELSEA CASCADE;

• CONNECT ROLE : 사용자가 DB에 접속 가능하도록 하기 위해 사전 정의한 가장 기본적인 시스템 권한

-- CONNECT ROLE (Release 11.2 버전)
CREATE SESSION

• RESOURCE ROLE : 사용자가 객체를 생성할 수 있도록 하기 위해서 시스템 권한을 묶어놓은 것 (8가지)

-- RESOURCE ROLE
CREATE TRIGGER, CREATE SEQUENCE, CREATE TYPE , CREATE PROCEDURE,
CREATE CLUSTER, CREATE OPERATOR, CREATE INDEXTYPE, CREATE TABLE