DML (Data Manipulation Language)
- 데이터의 삽입(INSERT), 수정(UPDATE), 삭제(DELETE), 병합(MERGE)
- 저장(commit)혹은 취소(rollback) 반드시 필요
INSERT
- 테이블에 행을 삽입할 때 사용
- 한 번에 한 행만 입력 가능(SQL Server, 여러행 동시 삽입 가능)
- 컬럼별 데이터타입과 사이즈에 맞게 삽입
- 작성하지 않은 컬럼은 NULL이 입력됨 (NOT NULL 컬럼일 경우 오류)
INSERT INTO 테이블 VALUES(값1, 값2 ...) // 전체 컬럼
INSERT INTO 테이블(컬럼1, 컬럼2) VALUES(값1, 값2 ...) // 선택한 컬럼만 데이터 입력
//서브쿼리를 이용한 여러 행 INSERT
INSERT INTO EMP3(EMPNO, ENAME, DEPTNO)
SELECT EMPNO, ENAME, DEPTNO FROM EMP WHERE DEPTNO = 20;UPDATE
- 데이터 수정 시 사용
- 컬럼 단위 수행
- 다중 컬럼 수행 가능
UPDATE 테이블명
SET 수정할 컬럼 = 수정값
WHERE 조건;
// 서브쿼리의 결과가 수정할 각 행의 값마다 하나씩 전달되야 함
UPDATE EMP
SET (SAL, COMM) = (SELECT MAX(SAL), MAX(COMM) FROM EMP)
WHERE ENAME = 'SMITH';DELETE
- 데이터 삭제할 때 사용
- 행 단위 실행
DELETE [FROM] 테이블명 [WHERE 조건];
DELETE MERGE_OLD WEHRE NO=3;MERGE
- 데이터 병합
- 참조 테이블과 동일하게 맞추는 작업
INSERT,UPDATE,DELETE 작업을 동시에 수행
MERGE INTO 테이블명
USING 참조테이블
ON (연결조건)
WHEN MATCHED THEN
UPDATE
SET 수정할내용
WHEN NOT MATCHED THEN
INSERT VALUES(삽입할 내용)
MERGE INTO MERGE_OLD M1 // 수정할 테이블 명을 MERGE INTO절에 명시
USING MERGE_NEW M2 // 참조테이블을 USING절에 명시
ON (M1.NO = M2.NO) // 참조 조건 괄호 필수
WHEN MATCHED THEN
UPDATE
SET M1.PRICE = M2.PRICE // UPDATE 문에서는 테이블명 명시 X
WHEN NOT MATCHED THEN
INSERT VALUES(M2.NO, M2.NAME, M2.PRICE);
// INSERT 문에는 INTO절 없이 VALUES로 참조 컬럼명 전달TCL (Transaction Control Language)
- 트랜잭션 제어어로 COMMIT, ROLLBACK이 포함됨
- DML에 의해 조작된 결과를 작업단위(트랜잭션) 별로 제어하는 명령어
- DML 수행 후 트랜잭션을 정상 종료하지 않는 경우 LOCK 발생할 수 있음
트랜잭션
- 데이터베이스의 논리적 연산 단위 (하나의 연속적인 업무 단위)
- 하나의 트랜잭션에는 하나 이상의 SQL 문장이 포함
- 분할 할 수 없는 최소의 단위
- ALL OR NOTHING (모두 COMMIT OR ROLLBACK 처리)
특징
- 원자성(atomicity)
모두 성공적으로 실행 or 실행되지 않은 상태로 남아있어야함 - 일관성(consistency)
트랜잭션 실행 전 DB내용이 잘못되어있지 않다면 실행 이후에도 잘못되어있으면 안됨 - 고립성(isolation)
트랜잭션 실행 도중 다른 트랜잭션의 영향을 받아 잘못된 결과를 만들어서는 안됨 - 지속성(durability)
트랜잭션이 성공적으로 수행되면 갱신한 DB내용이 영구적으로 저장
COMMIT
- 입력, 수정, 삭제한 데이터에 이상이 없을 경우 데이터를 저장하는 명령어
- 한번 COMMIT을 수행하면 COMMIT 이전에 수행된 DML은 모두 저장되며 되돌릴 수 없음
ROLLBACK
- 테이블 내 입력, 수정, 삭제한 데이터에 대해 변경을 취소하는 명령어
- DB에 저장되지 않고 최종 COMMIT 지점/변경 전/특정 SAVEPOINT지점으로 원복
- 최종 COMMIT 시점 이전까지 ROLLBACK 가능
DDL (Data Definition Language)
- 데이터 정의어
- 데이터 구조 정의 (객체 생성, 삭제, 변경) 언어
- CREATE(객체 생성), ALTER(객체 변경), DROP(객체 삭제), TRUNCATE(데이터 삭제)
- AUTO COMMIT
CREATE
- 테이블, 인덱스 같은 객체를 생성하는 명령어
- 테이블 생성 시 소유자 명시 가능 (생략 시 명령어 수행 계정 소유)
CREATE TABLE [소유자.]테이블명(
컬럼1 데이터타입 [DEFAULT 기본값] [제약조건],
컬럼2 데이터타입 [DEFAULT 기본값] [제약조건],
....);
// 테이블 복제 (제약조건, INDEX등은 복제안됨)
CREATE TABLE 테이블명
AS
SELECT * FROM 복제 테이블명;ALTER
- 테이블 구조 변경 (컬럼명, 데이터타입, 사이즈, default, 컬럼 삭제, 컬럼추가, 제약조건)
- 컬럼 순서 변경 불가 (재생성으로 해결)
컬럼 추가
- 추가된 컬럼 위치는 맨 마지막
- 여러 컬럼 동시 추가 가능 (괄호사용)
- 컬럼 추가 시 NOT NULL 속성 전달 불가 (컬럼 추가시 모두 NULL값을 갖고 추가됨)
- DEFAULT 선언 시 NOT NULL 속성을 갖는 컬럼 추가 가능
ALTER TABLE 테이블명 ADD 컬럼명 데이터타입 [DEFAULT] [제약조건]
// 컬럼추가
ALTER TABLE EMP_T1 ADD (BIRTHDAY3 DATE, BIRTHDAY4 DATE);
// 컬럼 속성 변경 (사이즈, 데이터타입, default)
ALTER TABLE 테이블명 MODIFY(컬럼명 DEFAULT 값);
// 컬럼 사이즈 변경
ALTER TABLE 테이블명 MODIFY(컬럼명 NUMBER(10));
// 빈 컬럼일 경우 데이터 타입 변경 가능
ALTER TABLE 테이블명 MODIFY 컬럼명 변경할 데이터타입;
// default 값 변경
ALTER TABLE 테이블명 MODIFY 컬럼명 default 값;
// 컬럼 이름 변경 (동시 여러 컬럼 변경 불가)
ALTER TABLE 테이블명 RENAME COLUMN 기존컬럼명 TO 새컬럼명;
// 컬럼 삭제 (데이터 존재 여부와 상관없이 삭제 가능, 동시에 여러컬럼 삭제 불가)
ALTER TABLE 테이블명 DROP COLUMN 컬럼명
DROP
- 객체(테이블, 인덱스 등) 삭제
DROP TABLE 테이블TURNCATE
- 구조 남기고 데이터만 즉시 삭제, 즉시 반영(AUTO COMMIT)
TURNCATE TABLE 테이블명;DELETE/DROP/TURNCATE 차이
- DELETE : 데이터 일부 또는 전체 삭제, 롤백 가능
- DROP : 데이터와 구조 동시 삭제, 롤백 불가
- TURNCATE : 데이터 전체 삭제만 가능, 롤백 불가
제약조건
- 데이터 무결성을 위해 각 컬럼에 생성하는 데이터의 제약 장치
- 테이블 생성 시 정의 가능, 컬럼 추가 시 정의 가능, 이미 생성된 컬럼에 제약조건만 추가 가능
PRIMARY KEY(기본키)
- 유일한 식별자
- 중복, NULL 허용 X (Unique + Not Null)
- 테이블 복사 시 복사되지 않음
- 하나의 테이블에 여러 기본키 생성 불가능 (여러 컬럼을 결합하여 생성은 가능)
// 테이블 생성 시 제약조건 생성
CREATE TABLE 테이블명 (
컬럼1 데이터타입 [DEFAULT 기본값] [제약조건] ...);
// 컬럼 추가 시 제약조건 생성
ALTER TABLE 테이블명 ADD 컬럼명 데이터타입 [DEFAULT 기본값] [제약조건];
// 이미 생성된 컬럼에 제약조건만 추가
ALTER TABLE 테이블명 ADD [CONSTRAINT 제약조건명] 제약조건 종류;
// 제약조건 삭제
ALTER TABLE 테이블명 DROP CONSTRAINT 제약조건명;UNIQUE
- 중복을 허용하지 않음
- NULL은 허용
- UNIQUE INDEX 자동 생성
NOT NULL
- 다른 제약조건과 다르게 컬럼의 특징을 나타냄 (복제시 따라감)
- 이미 만들어진 컬럼에 NOT NULL선언 시 제약조건 생성(ADD)이 아닌 컬럼 수정(MODIFY)로 해결
FOREIGN KEY
- 참조테이블의 참조 컬럼에 있는 데이터를 확인하면서 본 테이블 데이터를 관리할 목적으로 생성
- 반드시 참조(부모)테이블의 참조 컬럼(Reference key)이 사전에 PK혹은 Unique Key를 가져야함
CHECK
- 직접적으로 데이터의 값 제한 (양수, (1,2,3,4)중 하나)
기타 오브젝트
View
- 저장공간을 가지지는 않지만 테이블 처럼 조회 및 수정할 수 있는 객체
// 뷰 생성 및 조회
CREATE VIEW VIEW_EMP_DEPT
AS
SELECT E.EMPNO, E.ENAME, E.SAL, D.DNAME
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO;
SELECT * FROM VIEW_EMP_DEPT;SEQUENCE
- 자동으로 연속적인 숫자 부여해주는 객체
SYNONYM
- 테이블 별칭 생성
DCL (Data Control Language)
- 데이터 제어어로 객체에 대한 권한을 부여(GRANT)하거나 회수(REVOKE)하는 기능
- 테이블 소유자는 타계정의 테이블 조회 및 수정권한 부여 및 회수 가능
권한 종류
- 오브젝트권한
- 테이블에 대한 권한 제어 (SELECT 포함, DML 권한) - 시스템권한
- 시스템 작업(테이블생성 등)등 제어
GRANT
- 권한 부여 시 반드시 테이블 소유자나 관리자계정으로 접속하여 권한을 부여해야함.
- 동시에 여러 유저에 대한 권한 부여 가능
GRNAT 권한 ON 테이블명 TO 유저;
// 오브젝트 권한 부여
GRANT SELECT, INSERT ON PROFESSOR TO HR, BI;
// 시스템 권한 부여
GRANT CREATE TABLE, DROP ANY TABLE TO HRREVOKE
- 동시 여러 권한 회수 가능
REVOKE 권한 ON 테이블명 FROM 유저;ROLE
- 권한의 묶음 (생성 가능한 객체)
CREATE ROLE 롤이름;권한부여 옵션(중간관리자의 권한)
WITH GRANT OPTION
- 오브젝트 권한을 다른 사용자에게 부여할 수 있음
- 중간관리자가 부여한 권하는 중간관리자만 회수 가능
WITH ADMIN OPTION
- 시스템 권한/롤 권한을 다른 사용자에게 부여할 수 있음
- 중간관리자를 거치지않고 직접 회수 가능
- 중간관리자 권한 회수 시 제 3자에게 부여된 권한도 함께 회수되지 않음
홍쌤의 데이터랩
SQLD 2과목 PART3. 관리 구문 완벽정리 (2024년 신유형 반영) 강의에 대해
공부 및 개인적으로 정리한 글 입니다.
