[SQL 전문가] 과목II. 제3장 관리 구문

과목 II. SQL 기본과 활용

📖제3장 관리 구문

• 제1절 DML (데이터 입력∙수정∙삭제)
• 제2절 TCL (변경된 데이터 반영∙취소)
• 제3절 DDL (테이블 구조 생성∙변경∙삭제)
• 제4절 DCL (데이터 권한 부여∙회수)
• 장 요약
• 연습문제

by yunamom

✨제1절 DML

---

1. INSERT 

◾️단일 행 INSERT 문은 VALUES 절을 포함하며, 한 번에 한 행만 입력된다.

INSERT INTO 테이블명 [(칼럼1, 칼럼2, ...)] VALUES (값1, 값2, ...);

◾️서브 쿼리를 이용한 다중 행 INSERT 문

INSERT INTO 테이블명 [(칼럼1, 칼럼2, ...)]
서브 쿼리;

-- [예제]

INSERT
  INTO TEAM (TEAM_ID, REGION_NAME, TEAM_NAME, ORIG_YYYY, STADIUM_ID)
SELECT REPLACE(TEAM_ID, 'K', 'A') AS TEAM_ID
     , REGION_NAME, REGION_NAME || '올스타' AS TEAM_NAME
     , 2019 AS ORIG_YYYY, STADIUM_ID
  FROM TEAM
WHERE REGION_NAME IN ('성남', '인천');

2 개의 행이 만들어졌습니다.

2. UPDATE 

◾️데이터 수정

UPDATE 테이블명
   SET 수정할 칼럼명1 = 수정될 새로운 값1
   [ , 수정할 칼럼명2 = 수정될 새로운 값2 ]
[ WHERE 수정 대상 식별 조건식 ] ;

-- [예제] WHERE 절을 사용하지 않으면 테이블의 전체 데이터가 수정된다. (주의)

UPDATE PLAYER
   SET BACK_NO = 99;
   
480 행이 갱신됐습니다.

-- [예제 2] 

UPDATE PLAYER
  SET POSITION = 'MF'
WHERE POSITION IS NULL;

3 행이 갱신됐습니다.

3. DELETE 

◾️데이터 삭제 (*주의 : WHERE 절을 사용하지 않을시 테이블의 전체 데이터가 삭제된다.)

DELETE [FROM] 테이블명
[WHERE 삭제 대상 식별 조건식];

-- [예제] 주의 : WHERE 절을 사용하지 않을시 테이블의 전체 데이터가 삭제된다.

DELETE FROM PLAYER;

480행이 삭제됐습니다.

-- [예제 2] 

DELETE PLAYER
  WHERE POSITION = 'DF'
     AND JOIN_YYYY < 2010;
     
35행이 삭제됐습니다.

4. MERGE 

◾️MERGE 문을 사용하면 새로운 행을 입력하거나, 기존 행을 수정하는 작업을 한 번에 할 수 있다.

MERGE
 INTO 타겟 테이블명
USING 소스 테이블명
   ON (조인 조건식)
 WHEN MATCHED THEN
    UPDATE
       SET 수정할 칼럼명1 = 수정될 새로운 값1
       [ , 수정할 칼럼명2 = 수정될 새로운 값2, ... ]
 WHEN NOT MATCHED THEN
    INSERT [(칼럼1, 칼럼2, ...)]
    VALUES (값1, 값2, ...);

MERGE 문 설명
MERGE	MERGE 다음에 입력∙수정 돼야 할 타겟 테이블명을 입력
USING	입력∙수정에 사용할 소스 테이블을 입력
ON	타깃 테이블과 소스 테이블 간의 조인 조건식을 기술해, 입력∙수정할 대상을 결정
WHEN MATCHED THEN	ON 절의 조인 조건에 따라 성공한 행들에 대해서 아래 UPDATE 구문을 수행
WHEN NOT MATCHED THEN	조인에 실패한 행들에 대해서는 아래 INSERT 구문을 수행

 

✨제2절 TCL

---

1. 트랜잭션 개요 

◾️트랜잭션(Transaction) 이란 밀접히 관련돼 분리될 수 없는 한 개 이상의 데이터베이스 조작을 가리킨다. 

하나의 트랜잭션에는 하나 이상의 SQL 문장이 포함된다. 트랜잭션은 분할할 수 없는 최소의 단위다. 은행에서의 계좌이체 상황을 연상하면 트랜잭션을 이해하는 데 도움이 된다. 예 ) - STEP1. 100번 계좌의 잔액에서 10,000원을 뺀다. - STEP2. 200번 계좌의 잔액에 10,000원을 더한다.

트랜잭션의 특성	설명
💡원자성 (중요) (atomicity)	트랜잭션에 정의된 연산들은 모두 성공적으로 실행되던지 아니면 전혀 실행되지 않은 상태로 남아 있어야 한다(all or nothing)
일관성 (consistency)	트랜잭션이 실행되기 전의 데이터베이스 내용이 잘못 돼 있지 않다면 트랜잭션이 실행된 이후에도 데이터베이스의 내용에 잘못이 있으면 안 된다.
고립성 (isolation)	트랜잭션이 실행되는 도중에 다른 트랜잭션의 영향을 받아 잘못된 결과를 만들어서는 안 된다.
지속성 (durability)	트랜잭션이 성공적으로 수행되면, 그 트랜잭션이 갱신한 데이터베이스의 내용은 영구적으로 저장된다.

계좌이체는 한 계좌에서 현금이 인출된 후에 다른 계좌로 입금되는데, 현금이 인출되기 전에 다른 계좌에 입금되는것은 문제를 발생시킬 수 있다. 그리고 이체가 결정되기 전까지는 다른 사람이 이 계좌의 정보를 변경할 수 없다. 
이것을 보통 문에 자물쇠를 채우듯이 한다고 해 잠금(LOCKING)이라고 표현한다.
트랜잭션의 특성(특히 원자성)을 충족하기 위해 데이터베이스는 다양한 레벨의 잠금 기능을 제공한다. 잠금은 기본적으로 트랜잭션이 수행하는 동안 특정 데이터에 대해서 다른 트랜잭션이 동시에 접근하지 못하도록 제한하는 기법이다. 잠금이 걸린 데이터는 잠금을 실행한 트랜잭션만 독점적으로 접근할 수 있고, 다른 트랜잭션으로부터 간섭이나 방해를 받지 않는 것이 보장된다. 잠금이 걸린 데이터는 잠금을 수행한 트랜잭션만 해제할 수 있다.

 

2. COMMIT 

입력, 수정, 삭제한 데이터에 대해 전혀 문제가 없다고 판단됐을 경우 COMMIT 명령어로 트랜잭션을 완료할 수 있다.

COMMIT이나 ROLLBACK 이전의 데이터 상태

데이터의 변경을 취소해 이전 상태로 복구 가능하다.

현재 사용자는 SELECT 문장으로 결과를 확인 가능하다.

다른 사용자는 현재 사용자가 수행한 명령의 결과를 볼 수 없다.

변경된 행은 잠금(LOCKING)이 설정돼서 다른 사용자가 변경할 수 없다.

COMMIT 이후의 데이터 상태

데이터에 대한 변경 사항이 데이터베이스에 반영된다.

이전 데이터는 영원히 잃어버리게 된다.

모든 사용자는 결과를 볼 수 있다.

관련된 행에 대한 잠금(LOCKING)이 풀리고, 다른 사용자들이 행을 조작할 수 있게 된다.

 

3. ROLLBACK 

◾️테이블 내 입력한 데이터나 수정한 데이터, 삭제한 데이터에 대해 COMMIT 이전에는 변경 사항을 취소할 수 있다. 

-- [예제] SQL Server

BEGIN TRAN

DELETE FROM PLAYER;

(480개 행이 영향을 받음)

ROLLBACK;

명령이 완료됐습니다.

COMMIT과 ROLLBACK 의 장점

데이터 무결성 보장

영구적인 변경을 하기 전에 데이터의 변경 사항 확인 가능

논리적으로 연관된 작업을 그룹핑해 처리 가능

 

4. SAVEPOINT 

◾️현 시점에서 SAVEPOINT까지 트랜잭션의 일부만 ROLLBACK 할 수 있다.

-- [예제] Oracle

SAVEPOINT SVPT1;

-- 저장점까지 롤백할 때는 ROLLBACK 뒤에 저장점 명을 지정한다.

ROLLBACK TO SVPT1;

-- [예제] SQL Server

SAVE TRANSACTION SVTR1;

ROLLBACK TRANSACTION SVTR1;

 

✨제3절 DDL

---

1. CREATE TABLE 

◾️테이블은 데이터베이스의 가장 기본적인 객체로, 행과 열의 구조로 데이터를 저장한다.

테이블 생성 시에 주의해야할 규칙

테이블명은 객체를 의미할 수 있는 적절한 이름을 사용한다. 가능한 단수형을 권고한다.

테이블 명은 다른 테이블의 이름과 중복되지 않아야 한다.

한 테이블 내에서는 칼럼명이 중복되게 지정될 수 없다.

테이블 이름을 지정하고 각 칼럼들은 괄호 '()' 로 묶어 지정한다.

각 칼럼들은 콤마 ',' 로 구분되고, 테이블 생성문의 끝은 항상 세미콜론 ';' 으로 끝난다.

칼럼에 대해서는 다른 테이블까지 고려해 데이터베이스 내에서는 일관성 있게 사용하는 것이 좋다(데이터 표준화 관점)

칼럼 뒤에 데이터 유형은 꼭 지정돼야 한다.

테이블명과 컬럼명은 반드시 문자로 시작해야 하고, 벤더별로 길이에 대한 한계가 있다.

벤더에서 사전에 정의한 예약어(Reserved word)는 쓸 수 없다.

A-Z, a-z, 0-9, _, $, # 문자만 허용된다.

테이블 생성시 대소문자 구분은 하지 않지만, 기본적으로 테이블이나 칼럼명은 대문자로 만들어진다.

DATETIME 데이터 유형에는 별도로 크기를 지정하지 않는다.

문자 데이터 유형은 반드시 가질 수 있는 최대 길이를 표시해야 한다.

칼럼과 칼럼의 구분은 콤마로 하되, 마지막 칼럼은 콤마를 찍지 않는다.

칼럼에 대한 제약조건이 있으면 CONSTRAINT를 이용해 추가할 수 있다.

제약조건의 종류	설명
PRIMARY KEY (기본키)	테이블에 저장된 행 데이터를 고유하게 식별하기 위한 기본키를 정의한다. 하나의 테이블에 하나의 기본키 제약만 정의할 수 있다. 기본키 제약을 정의하면 DBMS는 자동으로 UNIQUE 인덱스를 생성하며, 기본키를 구성하는 칼럼에는 NULL을 입력할 수 없다. 결국 '기본키 제약 = 고유키 제약 & NOT NULL 제약' 이 된다.
UNIQUE (고유키)	테이블에 저장된 행 데이터를 고유하게 식별하기 위한 고유키를 정의한다. 단 NULL은 고유키 제약의 대상이 아니므로, NULL 값을 가진 행이 여러 개가 있더라도 고유키 제약 위반이 되지 않는다.
NOT NULL	이 제약을 지정함으로써 해당 칼럼은 필수로 입력해야 한다.
CHECK	입력할 수 있는 값의 범위 등을 제한한다. CHECK 제약으로는 TRUE or FALSE로 평가할 수 있는 논리식을 지정한다.
FOREIGN KEY (외래키)	관계형 데이터베이스에서 테이블 간의 관계를 정의하기 위해 기본키를 다른 테이블의 외래키로 복사하는 경우 외래키가 생성된다. 외래키 지정시 참조 무결성 제약 옵션을 선택할 수 있다.

 

2. ALTER TABLE 

◾️칼럼을 추가/삭제하거나 제약조건을 추가/삭제하는 작업

종류	예시
ADD COLUMN (칼럼 추가)	ALTER TABLE PLAYER ADD (ADDRESS VARCHAR2(80)); *주의 : 새로 추가된 칼럼은 테이블의 마지막 칼럼이 되며 칼럼의 위치를 지정할 수는 없다.
DROP COLUMN (칼럼 삭제)	ALTER TABLE PLAYER DROP (ADDRESS);
MODIFY COLUMN (칼럼 수정)	Oracle ALTER TABLE PLAYER MODIFY (ADDRESS VARCHAR2(100)); SQL Server ALTER TABLE PLAYER MODIFY ADDRESS VARCHAR(100);
RENAME COLUMN (칼럼명 변경)	ALTER TABLE PLAYER RENAME COLUMN ADDRESS TO HOME;
DROP CONSTRAINT (제약조건 삭제)	ALTER TABLE PLAYER DROP CONSTRAINT PLAYER_FK;
ADD CONSTRAINT (🔑제약조건 추가)	ALTER TABLE PLAYER ADD CONSTRAINT PLAYER_FK FOREIGN KEY (TEAM_ID) REFERENCES TEAM(TEAM_ID); *주의 : 외래 키🔑에 의해 참조되는 테이블은 삭제할수 없다. ORA-02449 에러 발생 *주의 : PLAYER 테이블이 참조하는 TEAM 테이블의  데이터 또한 삭제할 수 없다. ORA-02292 : 무결성 제약조건(U_DDL.PLAYER_FK)이 위배 - 자식 레코드 발견

 

3. RENAME TABLE 

◾️RENAME 명령어를 사용해 테이블의 이름을 변경할 수 있다.

-- [예제] Oracle

RENAME TEAM TO TEAM_BACKUP;

-- [예제] SQL Server

RENAME TABLE TEAM TO TEAM_BACKUP;

4. DROP TABLE 

◾️테이블을 삭제하는 명령어

-- [예제]

DROP TABLE PLAYER;

5. TRUNCATE TABLE 

◾️TRUNCATE TABLE 은 테이블 자체가 삭제되는 것이 아니고, 해당 테이블에 들어 있던 모든 행들이 제거되고 저장 공간을 재사용 가능하도록 해제한다. 테이블 구조를 완전히 삭제하기 위해서는 DROP TABLE을 실행하면 된다.

-- [예제]

TRUNCATE TABLE TEAM;

테이블의 전체 데이터를 삭제하는 경우, 
시스템 활용 측면에서는 DELETE 보다는 시스템 부하가 적은 TRUNCATE TABLE을 권고한다.
*TRUNCATE TABLE의 경우 정상적인 복구가 불가능하므로 주의해야 한다.

 

✨제4절 DCL

---

1. DCL 개요 

◾️유저 생성 및 권한을 제어할 수 있는 DCL(Data Control Language) 명령어

 

2. 유저와 권한 

◾️데이터 공유를 위해 새로운 유저를 생성하고, 테이블 및 오브젝트 에 대한 접근 권한을 부여한다.

Oracle 에서 제공하는 유저들	역할
SCOTT	Oracle 테스트용 샘플 계정 Default 패스워드 : TIGER
SYS	백업 및 복구 등 데이터베이스 상의 모든 관리 기능을 수행할 수 있는 최상위 관리자 계정
SYSTEM	백업, 복구 등 일부 관리 기능을 제외한 모든 시스템 권한을 부여받은 DBA 계정 (Oracle 설치 시에 패스워드 설정)

오브젝트 권한과 오브젝트와의 관계 (Oracle 사례)

객체 권한	테이블	VIEWS	SEQUENCE	PROCEDURE
ALTER	O		O
DELETE	O	O
EXECUTE				O
INDEX	O
INSERT	O	O
REFERENCES	O
SELECT	O	O	O
UPDATE	O	O

오브젝트 권한과 오브젝트와의 관계 (SQL Server 사례)

객체 권한	테이블	VIEWS	FUNCTION	PROCEDURE
ALTER	O		O
DELETE	O	O	O
EXECUTE				O
INDEX	O
INSERT	O	O
REFERENCES	O
SELECT	O	O	O
UPDATE	O	O

 

3. Role을 이용한 권한 부여

◾️유저들과 권한들 사이에서 중개 역할을 하는 ROLE

 

구분	예시
ROLE 생성	CREATE ROLE [롤이름];
권한 부여	GRANT CREATE SESSION , CREATE TABLE TO [롤이름];
권한 회수	REVOKE CREATE SESSION , CREATE TABLE FROM [롤이름];

 

CONNECT ROLE과 RESOURCE ROLE에 포함된 권한 목록 (Oracle 사례)

CONNECT	RESOURCE
CREATE SESSION	CREATE CLUSTER
	CREATE INDEXTYPE
	CREATE OPERATOR
	CREATE PROCEDURE
	CREATE SEQUENCE
	CREATE TABLE
	CREATE TRIGGER
	CREATE TYPE

 SQL Server   에서는 기본적으로 제공되는 ROLE에 멤버로 참여하는 방식으로 사용한다.

특정 로그인이 멤버로 참여할 수 있는  서버 수준 역할 (SQL SERVER 사례) 는 아래와 같다.

역할명	설명
public	모든 SQL Server 로그인은 PUBLIC 서버 역할에 속한다.
bulkadmin	BULK INSERT 문을 수행할 수 있다.
dbcreator	데이터베이스를 생성, 변경, 삭제, 복원할 수 있다.
diskadmin	디스크 파일을 관리하는 데 사용된다.
processadmin	SQL Server의 인스턴스에서 실행중인 프로세스를 종료할 수 있다.
securityadmin	로그인 및 해당 속성을 관리한다. 서버 및 데이터베이스 수준의 사용 권한을 부여(GRANT), 거부(DENY), 취소(REVOKE)할 수 있다. 또한 로그인 암호를 다시 설정할 수 있다.
serveradmin	서버 차원의 구성 옵션을 변경하고 서버를 종료할 수 있다.
setupadmin	연결된 서버를 추가하거나 제거할 수 있다.
sysadmin	서버에서 모든 작업을 수행할 수 있다. 기본적으로 Windows BUILTIN \ Administrators 그룹의 멤버인 로컬 관리자 그룹은 sysadmin 고정 서버 역할의 멤버이다.

 데이터베이스 수준 역할명(SQL Server 사례) 

역할명	설명
db_accessadmin	Windows 로그인, WIndows 그룹 및 SQL Server 로그인의 데이터베이스에 대한 액세스를 추가하거나 제거할 수 있다
db_backupoperator	데이터베이스를 백업할 수 있다.
db_datareader	모든 사용자 테이블의 모든 데이터를 읽을 수 있다.
db_datawriter	모든 사용자 테이블에서 데이터를 추가, 삭제, 변경할 수 있다.
db_ddladmin	데이터베이스에서 모든 DDL 명령을 수행할 수 있다.
db_denydatareader	데이터베이스 내에 있는 사용자 테이블의 데이터를 읽을 수 없다.
db_denydatawriter	데이터베이스 내의 모든 사용자 테이블에 있는 데이터를 추가, 삭제, 변경할 수 없다.
db_owner	데이터베이스 내에 있는 모든 구성 및 유지관리 작업을 수행할 수 있고, 데이터베이스를 삭제할 수도 있다.
db_securityadmin	역할 멤버 자격을 수정하고 사용 권한 관리를 할 수 있다. 이 역할에 보안 주체를 추가하면 원하지 않는 권한 상승이 설정될 수 있다.

 

✨장 요약

명령어	역할	예
DML	데이터(행)를 검색, 수정, 삽입, 삭제	INSERT INTO UPDATE DELETE MERGE (INSERT + UPDATE)
TCL	트랜잭션을 관리, 제어하는 명령어를 의미	COMMIT ROLLBACK SAVEPOINT
DDL	데이터 테이블(셋)을 생성, 수정, 삭제	CREATE TABLE ALTER TABLE RENAME TABLE / SP_RENAME '테이블명','새로운테이블명' DROP TABLE *TRUNCATE TABLE
DCL	데이터 사용자 또는 객체의 사용권한을 관리하는 명령어	GRANT 권한 위임 REVOKE 권한 회수