관리구문
DML (Data Manipulation Language)
데이터의 삽입(INSERT), 수정(UPDATE), 삭제(DELETE), 병합(MERGE).
저장(commit) 혹은 취소(rollback) 반드시 필요.
INSERT
테이블에 행을 삽입할 때 사용.
한 번에 한 행만 입력 가능.(SQL Server - 여러 행 동시 삽입 가능)
하나의 컬럼에는 한 값만 삽입 가능.
컬럼 별 데이터타입과 사이즈에 맞게 삽입.
INTO 절에 컬럼명을 명시하여 일부 컬럼만 입력 가능. 작성하지 않은 컬럼은 NULL이 입력된다. (NOT NULL 컬럼의 경우 오류 발생.)
전체 컬럼에 대한 데이터 입력 시 테이블명 뒤의 컬럼명 생략 가능.
UPDATE
데이터 수정할 때 사용. 컬럼 단위 수행. 다중 컬럼 수정 가능.
DELETE
데이터 삭제할 때 사용. 행 단위 수행.
MERGE
데이터 병합. 참조 테이블과 동일하게 맞추는 작업.(참조테이블의 데이터 입력, 참조테이블의 값으로 수정 등) INSERT, UPDATE, DELETE 작업을 동시에 수행.
수정할 테이블명을 MERGE INTO 절에 명시. 참조테이블을 USING절에 명시.
두 테이블의 데이터를 참조할 참조 조건을 ON절 명시.(괄호 필수)
UPDATE문에서는 테이블명 명시하지 않는다.
SET 절의 왼쪽이 수정테이블, 오른쪽이 참조 테이블 컬럼.
INSERT문에는 INTO절 없이 VALUES로 참조 컬럼명 전달.
TCL (Transaction Control Language)
트랜잭션 제어어로 COMMIT, ROLLBACK이 포함. DML에 의해 조작된 결과를 작업단위(트랜잭션) 별로 제어하는 명령어. DML 수행 후 트랜잭션을 정상 종료하지 않는 경우 LOCK 발생할 수 있다.
잠금(LOCK): 트랜잭션이 수행하는 동안 특정 데이터에 대해서 다른 트랜잭션이 동시에 접근하지 못하도록 제한. 잠금이 걸린 데이터는 잠금을 실행한 트랙잭션만이 접근 및 해제 가능.(관리자 권한 계정 제외)
트랜잭션
트랜잭션은 데이터베이스의 논리적 연산 단위.(하나의 연속적인 업무 단위).
하나의 트랜잭션에는 하나 이상의 SQL 문장이 포함.
분할 할 수 없는 최소의 단위.
ALL OR NOTHING 개념.(모두 COMMIT 하거나 ROLLBACK 처리 해야 한다.)
- 트랜잭션의 특성
원자성(atomicity) : 트랜잭션 정의된 연산들 모두 성공적으로 실행되던지 아니면 전혀 실행되지 않은 상태로 남아 있어야 한다.
일관성(consistency) : 트랜잭션 실행 전 데이터베이스 내용이 잘못되어 있지 않다면 트랜잭션 실행 이후에도 데이터베이스 내용의 잘못이 있으면 안된다.
고립성(isolation) : 트랜잭션 실행 도중 다른 트랜잭션의 영향을 받아 잘못된 결과를 만들어서는 안된다.
지속성(durability) : 트랜잭션이 성공적으로 수행되면 갱신한 데이터베이스 내용이 영구적으로 저장된다.
COMMIT
입력, 수정, 삭제한 데이터에 이상이 없을 경우 데이터를 저장하는 명령어.
한 번 COMMIT을 수행하면 COMMIT 이전에 수행된 DML은 모두 저장되며 되돌릴 수 없다.
ORACLE은 DDL 시 AUTO COMMIT(23c버전부터 비활성화 가능) 이지만 SQL Server는 AUTO COMMIT 비활성화 설정 가능.
ROLLBACK
테이블 내 입력한 데이터나 수정한 데이터, 삭제한 데이터에 대해 변경을 취소하는 명령어.
데이터베이스에 저장되지 않고 최종 COMMIT 지점/변경 전/특정 SAVEPOINT 지점으로 원복된다.
최종 COMMIT 지점 이전까지 ROLLBACK 가능.
SAVEPOINT 를 설정하여 최종 COMMIT 지점이 아닌, 그 이후의 원하는 지점으로의 원복 가능.
- SAVEPOINT
트랜잭션 내에서 롤백을 부분적으로 수행하기 위해 사용되는 지점을 설정하는 데 사용.
사용자가 원하는 위치에 원하는 이름으로 설정 가능.
ROLLBACK TO savepoint_name 으로 원하는 지점으로 원복 가능.(단, COMMIT 이전으로는 원복 불가!)
SAVEPOINT 이전에 수행한 UPDATE는 취소되지 않는다.
DDL (Data Definition Language)
데이터 정의어.
데이터 구조 정의(객체 생성, 삭제, 변경) 언어.
CREATE(객체 생성), ALTER(객체 변경), DROP(객체 삭제), TRUNCATE(데이터 삭제).
AUTO COMMIT (명령어를 수행하면 즉시 저장, 원복 불가).
CREATE
테이블이나 인덱스와 같은 객체를 생성하는 명령어.
테이블 생성 시 각 컬럼의 제약조건 및 기본값은 생략 가능.
테이블 생성 시 소유자 명시 가능.(생략 시 명령어 수행 계정 소유)
숫자컬럼의 경우 컬럼 사이즈 생략 가능.(날짜 컬럼은 사이즈 명시하지 않는다.)
- 특징
복제테이블의 컬럼명과 컬럼의 데이터 타입이 복제된다.
SELECT 문에서 컬럼별칭 사용 시 컬럼별칭 이름으로 생성.
CREATE 문에서 컬럼명 변경 가능.
NULL 속성도 복제된다.
테이블에 있는 제약조건, INDEX 등은 복제되지 않는다.
데이터타입
- CHAR(n) : 고정형 문자 타입으로 사이즈 전달 필수. 사이즈만큼 확정형 데이터가 입력된다.(빈자리수는 공백으로 채워진다.)
- VARCHAR2(n) : 가변형 문자 타입으로 사이즈 전달 필수. 사이즈보다 작은 문자값이 입력되더라도 입력값 그대로 유지.
- NUMBER(p, s): 숫자형 타입으로 자리수 생략 가능. 소수점 자리 제한 시 s전달.(p는 총 자리수)
- DATE : 날짜타입으로 사이즈 전달 불가
SQL Server의 경우도 유사. VARCHAR2 → VARCHAR 사용, NUMBER → NUMERIC 사용.
SQL Server의 경우 문자타입도 사이즈 생략 가능.(생략 시 1)
ALTER
테이블 구조 변경.(컬럼명, 컬럼 데이터타입, 컬럼사이즈, default 값, 컬럼삭제, 컬럼추가, 제약조건)
컬럼순서 변경 불가.(재생성으로 해결)
컬럼 추가
새로 추가된 컬럼위치는 맨 마지막.(절대 중간 위치에 추가 불가) 컬럼 추가 시 데이터 타입 필수. default 값, 제약조건을 명시할 수 있다. 여러 컬럼 동시 추가 가능.(반드시 괄호 사용)
여러 컬럼 추가 시 반드시 괄호 필수.
컬럼 추가 시 NOT NULL 속성 전달 불가(컬럼 추가 시 모두 NULL인 값을 갖고 추가되므로).
컬럼 추가 시 DEFAULT를 선언하면 NOT NULL 속성을 갖는 컬럼 추가 가능.
순서 주의.(NOT NULL은 DEFAULT 값 선언 뒤)
컬럼(속성) 변경
컬럼 사이즈, 데이터 타입, DEFAULT값 변경 가능. 여러 컬럼 동시 변경 가능.
괄호 생략 가능.
- 컬럼 사이즈 변경: 컬럼 사이즈 증가는 항상 가능. 컬럼 사이즈 축소는 데이터 존재 여부에 따라 제한.(데이터가 있는 경우 데이터의 최대 사이즈만큼 축소 가능.) 동시 변경 가능.(반드시 괄호 필요)
- 데이터 타입 변경: 빈 컬럼일 경우만 데이터 타입 변경 가능. CHAR, VARCHAR 타입일 경우 데이터가 있어도 서로 변경 가능.
- DEFAULT 값 변경: DEFAULT값이란특정 컬럼에 값이 생략될 경우(입력 시 언급되지 않을 경우) 자동으로 부여되는 값. INSERT 시 DEFAULT값이 선언된 컬럼에 NULL을 직접 입력할 때는 DEFAULT값이 아닌 NULL이 입력된다. 이미 데이터가 존재하는 테이블에 DEFAULT값 선언 시 기존 데이터 수정 안된다.(이후 입력된 데이터부터 적용) DEFAULT값 해제 시 DEFAULT값을 NULL로 선언.
컬럼 이름 변경
항상 가능.동시 여러 컬럼 이름 변경 불가.(괄호 전달 불가) ALTER ... RENAME 명령어로 처리.
컬럼 삭제
데이터 존재 여부와 상관없이 언제나 가능. RECYCLEBIN에 남지 않는다.(FLASHBACK으로 복구 불가.) 동시 삭제 불가.
DROP
객체(테이블, 인덱스 등) 삭제. DROP 후에는 조회 불가.
TRUNCATE
구조 남기고 데이터만 즉시 삭제, 즉시 반영.(AUTO COMMIT) RECYCLEBIN에 남지 않는다.
DELETE/DROP/TRUNCATE
DELETE : 데이터 일부 또는 전체 삭제, 롤백 가능.
DROP : 데이터와 구조를 동시 삭제, 즉시 반영(롤백 불가).
TRUNCATE : 데이터 전체 삭제만 가능(일부 삭제 불가) , 즉시 반영(롤백 불가).
제약조건
데이터 무결성을 위해 각 컬럼에 생성하는 데이터의 제약 장치.
테이블 생성 시 정의 가능, 컬럼 추가 시 정의 가능, 이미 생성된 컬럼에 제약 조건만 추가 가능.
PRIMARY KEY (기본키)
유일한 식별자.(각 행을 구별할 수 있는 식별자 기능)
중복 허용 X, NULL 허용 X → UNIQUE + NOT NULL
특정 컬럼에 PRIMARY KEY 생성하면 NOT NULL 속성 자동 부여.(CTAS로 테이블 복사 시 복사되지 않는다.)
하나의 테이블에 여러 기본키를 생성할 수 없다.
하나의 기본키는 여러 컬럼을 결합하여 생성할 수 있다.
PRIMARY KEY 생성 시 자동으로 UNIQUE INDEX 생성.
제약조건 생성 시 이름을 설정하지 않으면 자동 부여.
CREATE 문 밑에 제약조건 이름과 함께 전달 가능.
UNIQUE
중복을 허용하지 않는다. NULL은 허용. UNIQUE INDEX 자동 생성.
NOT NULL
다른 제약조건과 다르게 컬럼의 특징을 나타낸다. → CTAS로 복제 시 따라간다.
컬럼 생성 시 NOT NULL을 선언하지 않으면 Nullable 컬럼으로 생성된다.
이미 만들어진 컬럼에 NOT NULL 선언 시 제약조건 생성이 아닌 컬럼 수정(MODIFY)으로 해결.
FOREIGN KEY
참조테이블의 참조 컬럼에 있는 데이터를 확인하면서 본 테이블 테이터를 관리할 목적으로 생성.
반드시 참조(부모)테이블의 참조 컬럼(REFERENCE KEY)이 사전에 PK 혹은 UNIQUE KEY를 가져야 한다.
FOREIGN KEY 옵션 (생성 시 정의, 변경 불가 → 재생성)
- ON DELETE CASCADE : 부모 데이터 삭제 시 자식 데이터 함께 삭제.
- ON DELETE SET NULL : 부모 데이터 삭제 시 자식 데이터의 참조값은 NULL로 수정.
CHECK
직접적으로 데이터의 값 제한.(양수, (1,2,3,4) 중 하나)
기타 오브젝트
- 뷰(VIEW): 저장공간을 가지지는 않지만 테이블처럼 조회 및 수정할 수 있는 객체.
뷰(VIEW)의 종류
- 단순뷰 : 하나의 테이블 조회 뷰(VIEW).
- 복합뷰 : 둘 이상의 테이블 조인 뷰(VIEW).
뷰(VIEW)의 특징
- 뷰(VIEW)는 기본테이블로부터 유도된 테이블이기에 기본 테이블과 같은 형태의 구조를 가지고 있으며, 조작도 기본 테이블과 거의 같다.
- 뷰는 가상의 테이블이기에 물리적으로 구현되어 있지 않으면 저장공간을 차지하지 않는다.
- 데이터를 안전하게 보호 가능.
- 이미 정의되어 있는 뷰(VIEW)는 다른 뷰(VIEW)의 정의에 기초가 될 수 있다.
- 기본 테이블이 삭제되면 그 테이블을 참조하여 만든 뷰 역시 삭제된다.
뷰(VIEW)의 장점
- 논리적 독립성을 제공.
- 데이터의 접근을 제어함으로써 보안 유지.
- 사용자의 데이터 관리 단순화.
- 데이터의 다양한 지원가능.
뷰(VIEW)의 단점
- 뷰의 정의 변경 불가.
- 삽입, 삭제, 갱신 연산에 제한.
- 인덱스 구성불가.
- 시퀀스(SEQUENCE): 자동으로 연속적인 숫자를 부여해주는 객체.
- 시노님(SYNONYM): 테이블 별칭 생성.
DCL (Data Control Language)
데이터 제어어로 객체에 대한 권한을 부여(GRANT)하거나 회수(REVOKE)하는 기능. 테이블 소유자는 타계정에 테이블 조회 및 수정 권한 부여 및 회수 가능.
권한
일반적으로 본인(접속한 계정) 소유가 아닌 테이블은 원칙적으로 조회 불가.(권한 통제)
업무적으로 필요 시 테이블 소유자가 아닌 계정에 테이블 조회, 수정 권한 부여 가능.
권한 종류
- 오브젝트 권한: 테이블에 대한 권한 제어. ex. 특정 테이블에 대한 SELECT, INSERT, UPDATE, DELETE, MERGE 권한. 테이블 소유자는 타계정에 소유 테이블에 대한 조회 및 수정 권한 부여 및 회수 가능.
- 시스템 권한: 시스템 작업(테이블생성 등) 등을 제어. ex. 테이블 생성 권한, 인덱스 삭제 권한. 관리자 권한만 권한 부여 및 회수 가능.
GRANT
권한 부여 시 반드시 테이블 소유자나 관리자계정(SYS, SYSTEM)으로 접속하여 권한을 부여하여햐 한다.
동시에 여러 유저에 대한 권한 부여 가능.
동시 여러 권한 부여 가능.
동시 여러 객체 권한 부여 불가.
REVOKE
동시 여러 권한 회수 가능.
이미 회수된 권한 재회수 불가.
동시 여러 유저로부터의 권한 회수 가능.
롤 (ROLE)
권한의 묶음.(생성 가능한 객체)
SYSTEM 계정에서 ROLE 생성 가능.
권한부여 옵션 (중간관리자의 권한)
- WITH GRANT OPTION
WITH GRANT OPTION으로 받은 오브젝트 권한을 다른 사용자에게 부여할 수 있다.
중간관리자(WITH GRANT OPTION으로 권한을 부여받은 자)가 부여한 권한은 중간관리자만 회수 가능.
중간관리자에게 부여된 권한 회수 시 제 3자에게 부여된 권한도 함께 회수된다. - WITH ADMIN OPTION
WITH ADMIN OPTION을 통해 부여받은 시스템 권한/롤 권한을 다른 사용자에게 부여할 수 있다.
중간관리자를 거치지 않고 직접 회수 가능.
중간관리자 권한 회수 시 제 3자에게 부여된 권한은 함께 회수되지 않는다(남아있다).
중간 관리자의 시스템 권한을 회수하더라도 중간관리자가 제 3의 계정에 부여한 권한은 회수되지 않으므로 생성이 가능하다.
