[SQLD] 5장. SQL 관리 구문

DML(Data Manipulation Language)

데이터 삽입(INSERT), 수정(UPDATE), 삭제(DELETE), 병합(MERGE)을 할 수 있는 SQL 언어입니다. 저장(COMMIT) 혹은 롤백(ROLLBACK)이 반드시 필요하며 이를 통해 트랜잭션을 제어할 수 있습니다.

INSERT

• 테이블에 행을 삽입할 때 사용하는 명령어로 한 번에 한 행만 입력이 가능합니다.
• (SQL Server는 여러 행을 동시에 삽입할 수 있습니다.)
• 하나의 컬럼에는 한 값만 삽입 가능하며, 컬럼별 데이터 타입과 사이즈에 맞게 삽입해야 합니다.
• INTO 절에 컬럼명을 명시하여 일부 컬럼만 입력할 수 있으며, 작성하지 않은 컬럼은 NULL이 입력됩니다.
• 전체 컬럼에 대해서는 데이터 입력 시 테이블 뒤에 컬럼명을 생략할 수 있습니다.

// 전체 컬럼의 값을 삽입
INSERT INTO 테이블 VALUES (값1, 값2);
// 선택 컬럼의 값을 삽입
INSERT INTO 테이블 (컬럼1, 컬럼2) VALUES (값1, 값2);

// ORACLE에서 여러 행을 삽입 시 서브 쿼리 사용
INSERT INTO 테이블명 (컬럼1, 컬럼2, 컬럼3)
SELECT 컬럼1, 컬럼2, 컬럼3
FROM 테이블명2
WHERE 조건;

UPDATE

• 데이터를 수정할 때 사용하는 명령어입니다.
• 컬럼 단위로 수행되며, 여러 컬럼을 수정할 수 있습니다.
• 수정 값은 상수로 할 수 있고, 서브쿼리를 통해 설정할 수도 있습니다.

// 단일 컬럼 수정
UPDATE 테이블명
SET 수정할컬럼 = 수정값
WHEREa 조건;

// 다중 컬럼 수정
UPDATE 테이블명
SET
	수정할컬럼1 = 수정값1,
    수정할컬럼2 = 수정값2
WHERE 조건;

// 서브쿼리를 통한 다중 컬럼 동시 수정
UPDATE 테이블명
SET (수정할컬럼1, 수정할컬럼2) = (SELECT 수정값1, 수정값2)
WHERE 조건;

DELETE

• 데이터를 삭제할 때 사용하는 명령어로, 행 단위로 실행됩니다.
• 조건절을 명시하지 않으면 전체 데이터가 삭제될 수 있습니다.

DELETE FROM 테이블명
[WHERE 조건];

MERGE

• 데이터를 병합할 때 사용하는 명령어로, 참조 테이블과 동일하게 맞추는 작업을 합니다.
• INSERT, UPDATE, DELETE를 동시에 수행합니다.

MERGE INTO OLD_테이블명 O1
USING NEW_테이블명 N1
ON (O1.NO = N1.NO) -- 괄호 필수
WHERE MATCHED THEN
UPDATE -- 테이블명 생략
SET O1.컬럼명 = N1.컬럼명
DELETE (조건) -- 괄호 생략 가능
WHEN NOT MATCHED THEN
INSERT VALUES (값1, 값2);

TCL(Transaction Control Language)

트랜잭션 제어어로 COMMIT, ROLLBACK이 있습니다. DML에 의해 조작된 결과를 트랜잭션별로 제어하는 명령어로, DML 수행 후 트랜잭션을 정상 종료하지 않는 경우 LOCK이 발생할 수 있습니다.

• LOCK : 트랜잭션이 수행하는 동안 특정 데이터에 의해 다른 트랜잭션이 동시에 접근하지 못하도록 제한하는 기능
  • *잠금이 걸린 데이터는 잠금을 실행한 트랜잭션만 접근 및 해제가 가능합니다. (관리자 권한 계정 제외)

트랜잭션

트랜잭션이란, 데이터베이스의 논리적 연산 단위로 더 이상 분할할 수 없는 하나의 연속적인 업무 단위입니다. 트랜잭션은 ALL OR NOTHING의 개념으로 모두 COMMIT 하거나 ROLLBACK 처리해야 합니다. 그리고 하나의 트랜잭션에는 최소 하나 이상의 SQL 문장이 포함됩니다.

• 트랜잭션의 특성
  • 원자성(automicity) : 트랜잭션 정의된 연산들이 모두 성공적으로 실행되거나 전혀 실행되지 않은 상태로 돌아가야 합니다.
  • 일관성(consistency) : DB 내용이 잘못되어 있지 않았다면 트랜잭션 실행 이후에도 DB 내용이 잘못되면 안됩니다.
  • 고립성(isolation) : 트랜잭션은 실행 도중에 다른 트랜잭션의 영향을 받아 잘못된 결과를 만들면 안 됩니다.
  • 지속성(durability) : 트랜잭션이 성공적으로 수행하면 갱신한 DB 내용이 영구적으로 저장됩니다.

COMMIT

• 입력, 수정, 삭제한 데이터에 이상이 없을 경우 데이터를 저장하는 명령어입니다.
• 한 번 COMMIT을 수행하면 COMMIT 이전에 수행된 DML은 모두 저장되며 되돌릴 수 없습니다.
• ORACLE은 DDL 시 AUTO COMMIT 되며, SQL Server는 AUTO COMMIT을 비활성화할 수 있습니다.

ROLLBACK

• 테이블 내 입력한 데이터나 수정한 데이터, 삭제한 데이터에 대한 변경을 취소하는 명령어입니다.
• 데이터베이스에 저장되지 않고 최종 COMMIT 지점이나 변경 전, 혹은 특정 SAVEPOINT 지점으로 원복 됩니다.
• 최종 COMMIT 시점 이전까지 ROLLBACK이 가능합니다.
• SAVEPOINT를 설정하여 최종 COMMIT이 아닌 그 이후에 원하는 시점으로 원복 할 수 있습니다.
• SAVEPOINT 이전에 수행한 UPDATE는 취소되지 않습니다.

SAVEPOINT
트랜잭션 내에서 롤백을 부분적으로 수행하기 위해 사용되는 지점을 지정하는 데 사용합니다.
사용자가 원하는 위치에 원하는 이름으로 설정이 가능합니다.
SAVEPOINT 세이브포인트이름
ROLLBACK TO 세이브포인트이름

DDL(Data Definition Language)

데이터 정의어로 데이터의 구조를 정의(생성, 삭제, 변경)합니다. CREATE(객체 생성), ALTER(객체 변경), DROP(객체 삭제), TRUNCATE(데이터 삭제)가 있으며, AUTO COMMIT으로 명령어를 수행하는 즉시 저장되고 되돌릴 수 없습니다.

TRUNCATE
TRUNCATE는 DML의 특성을 가지지만 AUTO COMMIT 되기 때문에 DDL로 분류됩니다.

CREATE(객체 생성)

• 테이블명이나 인덱스와 같은 객체를 생성하는 명령어입니다.
• 테이블명, 컬럼명, 컬럼 순서, 컬럼 크기, 컬럼 데이터 타입을 모두 정의해야 합니다.
• 컬럼의 제약 조건 또는 Default(기본값)은 생략할 수 있습니다.
• 소유자를 명시할 수 있습니다.
• 숫자 컬럼의 경우에는 컬럼 사이즈를 생략할 수 있고, 날짜 컬럼은 사이즈를 명시하면 안 됩니다.
• 문자 컬럼은 ORACLE의 경우 반드시 컬럼 사이즈를 지정해야 합니다.

CREATE TABLE [소유자.]테이블명 
(
    컬럼1 데이터타입 [DEFAULT 기본값] [제약조건],
    컬럼2 데이터타입 [DEFAULT 기본값] [제약조건]
)

// 테이블 복제
CREATE 신규테이블명
AS
SELECT * FROM 복제할테이블명

• 테이블 복제 시 특징
  • 복제 테이블의 컬럼명과 컬럼의 데이터 타입이 복제됩니다.
  •  SELECT문에서 컬럼 별칭 사용 시 컬럼 별칭 이름으로 생성됩니다.
  • CREATE 문에서 컬럼명 변경이 가능합니다.
  • NULL 속성도 복제 죕니다.
  • *테이블에 있는 제약조건, 인덱스 등은 복제되지 않습니다.

데이터 타입	설명
CHAR(n)	고정형 문자 타입으로 사이즈 전달이 필수. 사이즈 만큼 확정형 데이터가 입력 (빈자리 수는 공백으로)
VARCHAR2(n)	가변형 문자 타입으로 사이즈 전달이 필수. 사이즈보다 작은 문자값이 입력되더라도 입력 값이 그대로 유지
NUMBER(p, s)	숫자형 타입으로 자리수 생략이 가능. 소수점 자리 제한 시 s를 전달 (p는 총 자리수)
DATE	날짜 타입으로 사이즈 전달이 불가능

SQL Server에서는 VARCHAR2를 VARCHAR로, NUMBER를 NUMERIC으로 사용하며 문자 타입은 사이즈를 생략할 수 있습니다.
생략 시 1로 설정됩니다.

ALTER(객체 변경)

테이블의 구조를 변경하는 명령어입니다.

컬럼명, 컬럼 데이터타입, 컬럼 사이즈, default 값, 컬럼 삭제, 제약 조건을 변경할 수 있습니다.

컬럼의 순서는 변경할 수 없어 필요 시 재생성으로 해결해야 합니다.

• 컬럼 추가
  • 컬럼 추가는 무조건 맨 마지막에 추가됩니다.
  • 컬럼 추가 시 데이터 타입은 필수적으로 명시해야 합니다.
  • 괄호를 사용하여 여러 컬럼을 동시에 추가할 수 있습니다.
  • 해당 테이블에 데이터가 없는 경우에는 NOT NULL 속성을 지정할 수 있습니다.
  • 해당 테이블에 데이터가 있는 경우에는 컬럼 추가 시 모두 NULL 값을 가져야 하므로 NOT NULL 속성은 지정할 수 없습니다.
  • DEFAULT 값은 선언하면 NOT NULL 속성을 가지는 컬럼 추가가 가능합니다.
• 컬럼 사이즈 변경
  • 컬럼 사이즈의 증가는 항상 가능하나, 축소는 데이터가 존재할 경우 데이터의 최대 길이까지만 축소할 수 있습니다.
  • 동시에 여러 컬럼을 변경할 수 있습니다.
• 데이터 타입 변경
  • 데이터가 없는 경우에 자유롭게 변경이 가능합니다.
  • CHAR, VARCHAR의 경우에는 데이터가 있어도 서로 변경이 가능합니다.
• DEFAULT 값 변경
  • DEFAULT 값이란 특정 컬럼에 값이 입력되지 않을 경우 자동으로 부여되는 값입니다.
  • INSERT 시 DEFAULT 값이 선언된 컬럼에 NULL을 직접 입력해야 NULL이 저장됩니다.
  • 이미 데이터가 존재하는 테이블에 DEFAULT 값을 선언해도 기존 데이터를 수정되지 않습니다.
  • DEFAULT 값을 해제할 경우 DEFAULT 값을 NULL로 선언하면 됩니다.
• 컬럼명 변경
  • 컬럼명은 항상 변경할 수 있습니다.
  • 동시 여러 컬럼의 이름을 변경이 불가능합니다.
• 컬럼 삭제
  • 데이터 존재 여부와 상관없이 항상 가능합니다.
  • RECYCLEBIN에 남지 않아 복구가 불가능합니다.
  • 동시에 삭제가 불가능합니다.

// 컬럼 추가
ALTER TABLE 테이블명 ADD 컬럼명 데이터타입 [DEFAULT] [제약조건]

// 컬럼 타입 변경
ALTER TABLE 테이블명 MODIFY (컬럼명 데이터타입);

// 컬럼 사이즈 변경
ALTER TABLE 테이블명 MODIFY (컬럼명 NUMBER(200), 컬럼명2 VARCHAR(10));

// DEFAULT 값 변경
ALTER TABLE 테이블명 MODIFY (컬럼명 DEFAULT 값);

// 컬럼명 변경
ALTER TABLE 테이블명 RENAME COLUMN 기존컬럼명 TO 변경할컬럼명;

// 컬럼 삭제
ALTER TABLE 테이블명 DROP COLUMN 컬럼명;

DROP(객체 삭제)

• 테이블 또는 INDEX와 같은 객체를 삭제하는 명령어입니다.
• DROP 이후에는 해당 테이블 조회가 불가능합니다.
• PURGE로 테이블을 삭제할 경우에는 RECYCLEBIN에서 조회할 수 없습니다.

DROP TABLE 테이블명 [PURGE];

TRUNCATE(데이터 삭제)

• 객체의 구조는 남기고 데이터만 삭제하는 명령어입니다.
• AUTO COMMIT이기 때문에 RECYCLEBIN에 남지 않아 복구가 불가능합니다.

TRUNCATE TABLE 테이블명;

제약조건

데이터 무결성을 위해 각 컬럼에 생성하는 데이터의 제약 장치입니다. 테이블 생성 시와 컬럼 추가 시 정의가 가능합니다.

// 테이블 생성 시
CREATE TABLE 테이블명 (
	컬럼1 데이터 타입 [DEFAULT 기본값] [[CONSTRAINT 제약조건명] 제약조건종류],
    컬럼2 데이터 타입 [DEFAULT 기본값] [[CONSTRAINT 제약조건명] 제약조건종류],
	...
);

// 컬럼 추가 시
ALTER TABLE 테이블명
ADD 컬럼명 데이터타입 [DEFAULT 기본값] [[CONSTRAINT 제약조건명] 제약조건종류];

// 컬럼 변경 시
ALTER TABLE 테이블명
ADD [CONSTRAINT 제약조건명] 제약조건 종류;

// 제약 조건 삭제
ALTER TABLE 테이블명 DROP CONSTRAINT 제약조건명;

• PRIMARY KEY
  • 각 행을 구별할 수 있는 유일한 식별자입니다.
  • 중복, NULL을 허용하지 않습니다. (UNIQUE + NOT NULL)
  • 특정 컬럼에 PRIMARY KEY를 생성하면 NOT NULl 속성이 자동으로 부여됩니다.
  • CTAS로 테이블 복사 시 PK와 NOT NULL 속성이 복사되지 않습니다.
  • 하나의 테이블에 여러 기본키를 생성할 수 없습니다.
  • 하나의 기본키를 여러 컬럼을 결합하여 생성할 수 있습니다.
  • PK 생성 시 자동으로 UNIQUE INDEX를 생성할 수 있습니다.
• UNIQUE
  • 중복을 허용하지 않는 제약조건입니다.
  • NULL은 허용됩니다.
• NOT NULL
  • 다른 제약조건과 달리 컬럼의 특징을 나타냅니다. CTAS로 복제가 가능합니다.
  • 컬럼 생성 시 NOT NULL을 선언하지 않으면 Nullable 컬럼으로 생성됩니다.
  • 이미 있는 컬럼에 NOT NULL 선언을 하고 싶으면 MODIFY 해야합니다.
• FOREIGN KEY
  • 참조 테이블의 참조 컬럼에 있는 데이터를 확인하며 기존 테이블 데이터를 관리할 목적으로 생성됩니다.
  • 반드시 참조 테이블의 참조 컬럼이 PK 혹은 UNIQUE KEY를 가져야 합니다.
  • ON DELETE CASCADE : 부모 데이터 삭제 시 자식 데이터도 함께 삭제됩니다.
  • ON DELETE SET NULL : 부모 데이터 삭제 시 자식 데이터의 참조 값이 NULL로 변경됩니다.
• CHECK
  • 직접 데이터의 값 범이(도메인)을 제한하는 제약조건입니다.

뷰(View)

테이블처럼 물리적으로 디스크에 저장되지는 않지만 메타 데이터가 생성되어 조회 및 수정할 수 있는 객체입니다. 자주 사용하는 쿼리를 View로 설정하면 별칭처럼 간단하게 사용할 수 있게 됩니다.

CREATE [OR REPLACE] VIEW 뷰이름
AS
조회쿼리;

DROP VIEW 뷰명;

• 뷰의 종류
  • 단순 뷰 : 하나의 테이블을 조회하는 뷰입니다.
  • 복합 뷰 : 둘 이상의 테이블을 조회하는 뷰입니다.
• 뷰의 특징
  • 뷰는 기본 테이블과 같은 형태의 구조를 가집니다.
  • 뷰는 저장공간을 차지하지 않습니다.
  • 뷰는 데이터를 안전하게 보호할 수 있습니다.
  • 기본 테이블이 삭제되면 뷰 또한 삭제됩니다.
• 장점
  • 논리적 독립성을 제공합니다.
  • 데이터의 접근을 제어함으로써 보안을 유지합니다.
  • 사용자의 데이터 관리를 단순화합니다.
• 단점
  • 정의 변경이 불가능합니다.
  • 삽입, 삭제, 갱신 연산에 제한이 있습니다.
  • 인덱스 구성이 불가능합니다.

시퀀스(SEQUENCE)

자동으로 연속적인 숫자를 부여해 주는 객체입니다.

CREATE SEQUENCE 시퀀스명
INCREMENT BY
START WITH
MAXVALUE
MINVALUE
CYCLE | NOCYCLE
CACHE N
;

시노님(SYNONYM)

테이블의 별칭을 생성해 주는 객체입니다. 본인 소유 테이블이 아니더라도 테이블의 별칭을 붙여 간단하게 조회할 수 있습니다.

• OR REPLACE : 기존에 같은 이름으로 시노님이 생성되어 있는 경우에 대체가 가능합니다.
• PUBLIC : 시노님을 생성한 유저만 사용 가능한 PRIVATE SYNONYM의 반대로 누구나 사용이 가능합니다.
• PUBLIC으로 생성한 시노님은 반드시 PUBLIC으로 삭제해야 합니다.

DCL(Data Control Language)

객체에 대한 권한을 부여(GRANT)하거나 회수(REVOKE)하는 기능을 사용해 데이터를 제어할 수 있습니다. 테이블 소유자는 타계정에 테이블 조회 및 수정 권한을 부여하거나 회수할 수 있습니다.

• 권한 : 일반적으로 본인 소유가 아닌 테이블은 원칙적으로 조회가 불가능하나 권한을 부여하여 테이블 소유자가 아닌 계정도 테이블을 조회하거나 수정할 수 있습니다.
  • 권한 종류
    • 오브젝트 권한
      • 테이블에 대한 권한을 제어합니다. (SELECT, INSERT, UPDATE, DELETE, MERGE)
      • 테이블 소유자는 타계정 소유 테이블에 대해 조회/수정 권한을 부여하거나 회수할 수 있습니다.
    • 시스템 권한
      • 시스템 작업에 대한 권한을 제어합니다. (테이블 생성, 인덱스 삭제)
      • 관리자 권한만 권한을 부여하거나 회수할 수 있습니다.

GRANT

• 권한을 부여하는 명령어입니다.
• 권한 부여 시 반드시 테이블 소유자나 관리자 계정으로 접속해서 권한을 부여해야 합니다.
• 동시에 여러 유저에 대한 권한 부여가 가능하지만, 여러 객체에 대한 권한은 부여가 불가능합니다.

GRANT 권한(SELECT/UPDATE...) ON 테이블명 TO 유저명;

REVOKE

• 권한을 회수하는 명령어입니다.
• 이미 회수한 권한을 재회수할 경우에는 오류가 발생합니다.
• 동시에 여러 유저로부터 권한을 회수할 수 있습니다.

REVOKE 권한(SELECT/UPDATE...) ON 테이블명 FROM 유저명1, 유저명2;

ROLE

• SYSTEM 계정에서 ROLE을 생성할 수 있습니다.
• 권한의 묶음으로, CREATE로 생성할 수 있는 객체입니다.
• ROLE에서 회수한 권한은 즉시 반영되기 때문에 다시 ROLE을 부여할 필요가 없습니다.

// 롤 생성
CREATE ROLE 롤이름;

// 롤 부여
GRANT 롤이름 TO 부여대상;

// 롤에서 권한 제외
REVOKE SELECT ON 권한 FROM ROLE이름

권한 부여 옵션 : 중간 관리자의 권한

• WITH GRANT OPTION
  • WITH GRANT OPTION으로 받은 오브젝트 권한을 다른 사용자에게 부여할 수 있습니다.
  • 중간 관리자가 부여한 권한은 중간 관리자만 회수할 수 있습니다.
  • 중간 관리자에게 부여한 권한이 회수될 경우 제 3자에게 부여된 권한도 같이 회수됩니다.
• WITH ADMIN OPTION
  • WITH ADMIN OPTION을 통해 부여받은 시스템 권한이나 롤 권한을 다른 사용자에게 부여할 수 있습니다.
  • 중간 관리자가 부여한 권한도 총괄 관리자가 직접 회수할 수 있습니다.
  • 중간 관리자에게 부여한 권한이 회수될 경우 제 3자에게 부여된 권한은 회수되지 않고 남아있습니다.