SQLD 이론 요약 - part 4. 관리 구문
📗 Part 4. 관리 구문
1. DML (Data Manipulation Language)
| DML 종류 | DDL에서 정의한대로 데이터를 입력하고, 입력한 데이터를 수정, 삭제, 조회하는 명령어 |
|---|---|
| INSERT | 테이블에 데이터 입력 |
| UPDATE | 이미 저장된 데이터 수정하고 싶을 경우 사용 |
| DELETE | 이미 저장된 데이터를 삭제하고 싶은 경우 사용 DELETE는 커밋 하기전 롤백 가능함 |
TRUNCATE : 테이블 전체 데이터를 삭제하고 싶은 경우 사용 (테이블 삭제 아님)WHERE 절 없는 DELETE보다 시스템 부하 측면에서 유리 TRUNCATE는 별도의 로그를 쌓지 않아 롤백 불가능 |
|
| MERGE | 새로운 데이터 입력하거나, 이미 저장된 데이터에 대한 변경 작업을 한번에 하고 싶은 경우 사용 |
# MEMBER 테이블 컬럼 - id, name, age가 있을 경우
INSERT INTO member(id, name) values ('1' , '사자'); # age 필드에는 null이 들어감
# 전체 컬럼에 대한 data가 테이블 컬럼 순서대로 모두 나열해야 함
# 컬럼개수 및 데이터 유형 불일치시 에러 발생
INSERT INTO member values ('2' , '펭귄', '30');
# 여러 컬럼 수정하려면 SET절에 ,(콤마)로 이어서 명시함
UPDATE member SET age='15', name='오리' WHERE id = 2;
UPDATE member SET age = '10'; # WHERE절 없으면 테이블의 모든 ROW가 변경됨
DELETE FROM member WHERE id = 1;
DELETE FROM member; # WHERE절 없으면 테이블의 모든 ROW가 삭제됨
TRUNCATE TABLE member; # 전체 데이터를 삭제하고 싶으면 TRUNCATE가 성능이 더 좋음
# 백업용 member_backup 테이블에 지속적으로 member 테이블과 동기화시키는 작업
MERGE
INTO member_backup b # (데이터를 수정/생성할) 타겟 테이블명
USING member m # (수정/생성시 기준이 될) 비교 테이블명
ON b.id = m.id # 조건 : b.id와 m.id가 동일한 값이 있는지가 조건이 됨
WHEN MATCHED THEN # 조건에 맞는 데이터가 있으면 수정
UPDATE SET b.name = m.name, b.age = m.age
WHEN NOT MATCHED THEN # 조건에 맞는 데이터가 없으면 생성
INSERT (b.id, b.name, b.age) VALUES (m.id, m.name, m.age); # 모든 데이터를 백업하는 것이 아닌, 특정 조건의 데이터만 백업하고 싶은 경우
# member 테이블에서 deleted_at이 null인 데이터만 member_backup 테이블에 반영
# WHEN MATCHED THEN 과 WHEN NOT MATCHED THEN은 선택해서 하나만 사용할 수도 있음
MERGE INTO member_backup b
USING ( SELECT * FROM member WHERE deleted_at IS NULL ) m
ON b.id = m.id
WHEN MATCHED THEN
UPDATE SET b.name = m.name, b.age = m.age2. TCL (Transaction Control Language)
- 트랜잭션 이란?
- 쪼개질 수 없는 업무 처리의 단위
- 예시) 고객이 상품A 를 결제한다. + 상품A의 재고가 차감된다. (2가지 액션은 하나로 묶여 동작됨)
- 트랜잭션 특징
- ACID : 원자성(Atomicity), 일관성(Consistency), 고립성(Isolation), 지속성(Durability)
| 특징 | 트랜잭션 특징 설명 / 예시 |
|---|---|
| 원자성 | 트랜잭션의 모든 작업은 모두 성공하거나 모두 실패해야 함 작업 중 하나라도 실패하면 모든 변경이 원래 상태로 되돌아감 |
| 계좌 이체의 경우, 송금과 수취는 하나의 트랜잭션으로 묶여야 함 송금이 성공했지만 수취가 실패하면, 송금도 취소되어야 함 |
|
| 일관성 | 트랜잭션 완료 후에는 DB가 일관된 상태를 유지해야 함 DB의 규칙(예: 무결성 제약 조건 등)이 위반되지 않도록 해야 함 |
| A 상품의 판매수량 + 재고량 = 처음 보유하고 있었던 수량 외래키 제약 조건 : member_id를 FK로 참조하려면, 고객 테이블에 존재하는 id여야 함 |
|
| 고립성 | 동시에 실행되는 트랜잭션들은 서로 영향을 미치지 않도록 독립적으로 실행되어야 함 트랜잭션이 완료되기 전에는 다른 트랜잭션이 해당 트랜잭션의 중간 상태를 볼 수 없어야 함 |
| 두 사용자가 동시에 같은 상품을 구매할 때, 한 사용자의 구매가 완료되기 전에는, 다른 사용자가 해당 상품의 재고를 변경할 수 없음 |
|
| 지속성 | 트랜잭션 완료되면 결과가 영구적으로 DB에 저장되야 함 시스템 장애가 발생해도 해당 결과가 손실되지 않아야 함 모든 트랜잭션은 로그에 기록된 후 COMMIT 되어야 하며, 장애 발생시에도 복구 가능해야 함 |
| 사용자가 결제를 완료한 후, 시스템이 중단되더라도 결제 정보는 DB에 남아 있어야 함 |
| TCL | 트랜잭션을 제어하는 명령어 |
|---|---|
| COMMIT | insert, delete, update 후 변경된 내용을 확정, 반영하는 명령 COMMIT을 실행해야, 최종적으로 데이터 파일에 기록되고, 트랜잭션이 완료됨 COMMIT을 실행하지 않으면 → 휘발성 메모리까지만 반영됨 |
| ROLLBACK | insert, delete, update 후 변경된 내용을 취소하는 명령어 롤백을 하면 변경하기 이전 값으로 복구됨 |
| SAVEPOINT | 롤백을 수행할 때 전체 작업을 되돌리지 않고, 일부만 되돌릴 수 있게 하는 기능 ROLLBACK 뒤에 특정 SAVEPOINT를 지정해주면 그 지점까지만 데이터가 복구됨 |
- ⚠️ UPDATE시 장시간 커밋이나 롤백을 하지 않으면 LOCK이 걸려 다른 사용자가 변경 불가능해짐
INSERT INTO test value (1, 'A');
SAVEPOINT A; # savepoint 저장
UPDATE test SET name = 'B' WHERE id = 1;
SAVEPOINT B;
ROLLBACK TO A; # A지점으로 롤백
DELETE FROM test where id = 1;
COMMIT; # 커밋3. DDL (Date Definition Langauge)
- DDL 및 데이터 유형
-
DDL = 데이터를 정의하는 SQL
- create 쿼리 수행시 테이블 생성하면서 그 안에 담을 데이터에 대한 데이터 유형을 정함
- name 컬럼에는 문자를 담을거다라고 정하고, SQL에 명시해줌으로써 데이터를 정의해주는 것
-
⭐️ DDL 종류 ⭐️
CREATE,ALTER,MODIFY COLUMN,DROP TABLE,RENAME TABLE,TRUNCATE TABLE -
선언한 데이터 유형과 다른 유형의 데이터를 저장하려고 하면 에러 발생함
-
| 데이터 유형 | 데이터 타입 |
|---|---|
| 문자 | CHAR VARCHAR CLOB |
| 숫자 | NUMBER |
| 날짜 | DATE |
| 사이즈 - 영어 한글자 : 1byte - 한글 한글자 : 3byte |
APPLE 저장하는 경우- VARCHAR(20) 정의시 5byte만 사용 - CHAR(20) 정의시 그대로 20byte 모두 사용 space(빈칸)를 문자로 보는지에 따라 다름 - VARCHAR는 'ABC' ≠ 'ABC ' - CHAR는 'ABC' = 'ABC ' ( 빈칸 문자 아님) |
(1) CREATE
- CREATE
- 테이블 생성 명령어
- CREATE TABLE 시
제약 조건 (CONSTRAINT)정의 가능 (필수 요소는 아님)- 제약 조건은 테이블에 저장될 데이터의 무결성을 위반하지 않기 위한 장치
- 데이터의 정확성과 일관성을 유지하고, 데이터 결손과 부정합이 없음을 보증해 줌
CREATE TABLE member (
id NUMBER NOT NULL;
name VARCHAR(20)
school_id NUMBER,
del_yn CHAR(1) DEFAULT 'N', # 기본값 설정(별도값 명시하지 않으면 NULL 대신 'N'저장)
CONSTRAINT member_pk PRIMARY KEY(id),
CONSTRAINT member_fk FOREIGN KEY(scool_id) REFERENCES school(id)
CONSTRAINT chk_yn CHECK(del_yn IN('Y','N'))
); # 삭제여부 컬럼에 'Y'나 'N'만 입력될 수 있도록 chk_yn 이름의 제약조건을 정의함
# 새로운 테이블 생성이 아닌, 기존에 존재하던 테이블을 복사해서 생성하고 싶은 경우
# CTAS 사용 (Create Table ~ As Select ~ )
# 컬럼별로 데이터 유형을 다시 명시하지 않아도 되는 장점 있음
# 단, 제약 조건 중 NOT NULL 조건만 복사됨, 다른 제약 조건은 초기화되므로 ALTER로 수정 필요
CREATE TABLE member_backup AS select * from member;| 제약 조건 종류 | |
|---|---|
| PRIMARY KEY ( 기본키 ) | ROW의 고유성 보장 (한 테이블에 한개씩만 정의 가능) NULL 불가 + 자동 UNIQUE 인덱스로 생성됨 |
| UNIQUE KEY ( 고유키 ) | PRIMARY KEY와 유사하게 ROW의 고유성 보장 ( 단, NULL 허용됨 ) |
| NOT NULL | 해당 컬럼에 NULL 값 입력 허용하지 않는 제약 조건 |
| CHECK | 컬럼에 저장될 수 있는 값 범위 제한 |
| FOREIGN KEY ( 외래키 ) | 하나의 테이블이 다른 테이블을 참조하고자 할 때 FK를 정의함 참조 무결성 제약 옵션은 별도 선택 가능 |
[ 참조 무결성 규정 관련 옵션 ]CASCADE : 부모값 삭제시 자식값도 함께 삭제 SET NULL : 부모값 삭제시 자식의 해당 컬럼 NULL 처리SET DEFAULT : 부모값 삭제시 자식의 해당 컬럼 DEFAULT 값으로 변경RESTRICT : 자식 테이블에 해당 데이터가 PK로 존재하지 않는 경우에만 부모값 삭제 및 수정 가능 NO ACTION : 참조 무결성 제약이 걸려있는 경우, 삭제 및 수정 불가 |
- 테이블 생성시 반드시 지켜야할 규칙
테이블명과한 테이블 내에서 컬럼명은 고유해야 하며, 숫자로 시작 불가- 컬럼명 뒤에
데이터 유형과데이터 크기가 명시되어야 함 - 컬럼에 대한 정의는 괄호 안에 기술하며, 각 컬럼들은 콤마로 구분됨
- 마지막은 세미콜론 ; 으로 끝남
- (선택사항) 에러는 발생시키지 않지만 지키면 좋은 규칙
- 테이블명은 정체성 나타내는 것이 좋음 ( TABLE25 같이 무의미한 이름은 지양 )
- 컬럼명은 다른 테이블과 통일성 갖는게 좋음 ( 동일한 id인데 USER_ID, MEMBER_ID 혼용 지양)
(2) ALTER
- ALTER
- 테이블 구조 변경시 사용하는 명령어
- ALTER는 컬럼 추가, 컬럼변경, 컬럼 삭제, 제약조건 추가, 제약조건 삭제 등 할 수 있음
# ADD 컬럼명 : 새로운 컬럼 추가 (컬럼 위치는 맨 끝이며, 별도 위치 지정 불가)
ALTER TABLE member ADD birthday VARCHAR2(10);
# Drop Column : 기존 컬럼 삭제 (삭제한 컬럼은 복구 불가)
ALTER TABLE member DROP COLUMN address;
# MODIFY 컬럼 : 기존 컬럼 변경
# 데이터 유형, default값, not null 제약 조건에 대한 변경 가능
ALTER TABLE member MODIFY (birthday VARCHAR2(8) DEFAULT '99999999' NOT NULL);
# 컬럼에 저장된 모든 데이터 크기가 8보다 작아야만 줄일 수 있음 (크기 늘리는 것은 데이터와 상관없음)
# 데이터 유형 변경은 컬럼에 저장된 데이터가 없어야 가능
# default 값 변경하면, 변경 이후에 저장되는 데이터에만 적용됨
# not null 추가시, 현재 null 값이 저장되어 있지 않은 컬럼에만 조건 추가 가능함
# Rename Column : 기존 컬럼 이름 변경
ALTER TABLE member RENAME COLUMN department_id TO dept_id
# Add Constraint : 제약 조건 추가
ALTER TABLE member ADD CONSTRAINT
school_fk FOREIGN KEY school_id REFERNECES SCHOOL(id);
(3) Drop Table, Rename Table , Truncate Table
# Drop Table : 테이블 삭제
# 해당 테이블을 참조하고 있는 다른 테이블이 존재하는 경우, CASCADE 옵션 없으면 삭제되지 않음
Drop Table subject; # Teacher 테이블이 Subject테이블을 참조하고 있다면 오류 발생
# Cascade Constraint : 참조 제약 조건도 삭제하겠다는 의미
Drop Table subject CASCADE CONSTRAINT;
# Rename Table : 테이블명 변경
RENAME TABLE teacher To new_teacher;
# Truncate Table : 테이블에 저장되어 있는 데이터를 모두 삭제
# Delete 명령어와 차이점: 저장 공간이 재사용되도록 초기화됨
# 롤백이 불가능하여 DDL로 분류됨
TRUNCATE TABLE teacher;4. DCL (Data Control Language)
- DCL
- USER를 생성하고, 권한을 부여/회수하는 명령어
- DCL 종류
- User 관련 명령어 :
CREATE USER,ALTER USER,DROP USER - 권한 관련 명령어 :
GRANT,REVOKE - ROLE 관련 명령어 :
CREATE ROLE
- User 관련 명령어 :
- User 관련 명령어
- 하나의 Database에는 여러개의 USER를 가질 수 있음
- ex. 데이터베이스를 건물에 비유 → 건물의 거주자를 정하고, 거주자마다 건물 출입 비밀번호를 부여함
# Create User : 사용자 생성 (Create User 권한이 있어야 수행 가능)
CREATE USER {사용자명} IDENTIFIED BY {패스워드};
ALTER USER {사용자명}; # 사용자 변경
DROP USER {사용자명} ; # 사용자 삭제- 권한 관련 명령어 : 데이터베이스를 이용할 수 있는 권한 부여 / 회수
# GRANT {권한} TO {사용자명}; 사용자에게 권한 부여 명령어
GRANT CREATE SESSION TO soobin;
GRANT CREATE USER TO soobin;
GRANT CREATE TABLE TO soobin;
# REVOKE {권한} FROM {사용자명}; 사용자에게 권한 회수 명령어
REVOKE CREATE TABLE FROM soobin;- ROLE 관련 명령어
- ROLE 이란? 특정 권한들을 하나의 세트 처럼 묶는 것
- ex.
CREATE_SESSION,CREATE_USER,CREATE_TABLE권한을 묶어서 CREATE_R이라 명명
# create role {롤명} : ROLE을 생성
CREATE ROLE CREATE_R;
# grant {권한} to {롤명} : ROLE에 권한 부여
GRANT CREATE SESSION, CREATE USER, CREATE TABLE TO CREATE_R;
# grant {롤명} to {사용자명} : ROLE을 사용자에게 부여
GRANT CREATE_R TO soobin;