[SQL] 관리구문 - DDL, DML, DCL, TCL

DDL : Data Definition Language

1. create

-- DB 생성
CREATE DATABASE db_name;

-- Table 생성
CREATE TABLE table_name(
	column_name type constraint
);

2. alter

-- add column type 테이블에 컬럼 추가
ALTER TABLE persons ADD email VARCHAR(255);
ALTER TABLE emp_sub ADD deptno int DEFAULT 10; -- default값 설정

-- add constraint 제약조건명 제약조건(컬럼명) : 제약조건 추가
ALTER TABLE tcons ADD CONSTRAINT tcons_no_pk PRIMARY KEY(NO); 

-- modify, rename column 테이블의 컬럼 정보 변경
ALTER TABLE persons MODIFY COLUMN city VARCHAR(255); -- 컬럼 타입 변경
ALTER TABLE emp_sub RENAME COLUMN deptno TO dcode; -- 컬럼명 변경

-- drop column 테이블의 컬럼 삭제
ALTER TABLE persons DROP COLUMN email;

3. drop

-- DB 삭제
DROP DATABASE db_name;

-- Table 삭제
DROP TABLE table_name;

4. truncate

-- truncate table 비우기 
SELECT * FROM emp_10;
TRUNCATE TABLE emp_10;

---

DML : Data Manipulation Language

1. insert

-- 모든 컬럼에 삽입하기 (컬럼 순서와 일치해야 함)
-- insert into table_name values (value1, value2, ...);
INSERT INTO user VALUES('park', '박길동');

-- 특정 컬럼에 삽입하기
-- insert into table_name (col1, col2, ...) values (value1, value2, ...);
INSERT INTO user (id, NAME) VALUES ('kong', '공길동');
INSERT INTO user (NAME, id) VALUES ('공길동', 'kong');

-- select 결과값을 삽입하기
INSERT INTO emp_sub (id, NAME) 
SELECT empno, ename FROM emp WHERE deptno = 10;

2. delete

-- delete from table_name where 조건;

-- emp에서 이름이 hong인 데이터 삭제
DELETE FROM emp WHERE ename = 'hong';

3. update

-- update table_name set col1 = val1, col2 = val2, ... where 조건;

-- emp에서 hang이 담당업무가 CLERK로 변경, 담당매니저가 7782로 변경
UPDATE emp SET job = 'CLERK', mgr = 7782 WHERE ename='hong';

4. select

---

DCL : Data Controll Language

1. grant

-- 계정 생성 (root 만 가능)
CREATE user kosta IDENTIFIED BY '1234'; 

-- 계정의 비밀번호 변경
ALTER user kosta IDENTIFIED BY '2345'; 

-- 계정 삭제
DROP user kosta;

-- kosta 계정에 kotest SELECT, INSERT, UPDATE 권한 부여
GRANT SELECT,INSERT, UPDATE ON kotest.* TO kosta;

-- kosta 계정에 kotest의 모든 권한 부여
GRANT ALL PRIVILEGES ON kotest.* TO kosta;

-- kosta 계정에 모든 DB의 모든 권한 부여
grant ALL PRIVILEGES ON *.* TO kosta;

2. revoke

-- kosta 계정에 kotest SELECT, INSERT, UPDATE 권한 삭제
REVOKE SELECT, INSERT, UPDATE ON kotest.* FROM 'kosta';

-- kosta 계정에서 update 권한 삭제
REVOKE UPDATE ON kotest.* FROM 'kosta';

-- kosta 계정에서  모든 권한 삭제
REVOKE ALL PRIVILEGES ON *.* from 'kosta';

3. roll

-- DBMS 권한 중개 역할
CREATE ROLL roll_name;
GRANT 권한 TO roll_name;
GRANT roll_name TO 사용자;

---

TCL : Transaction Controll Language

1. commit : 변경사항 확정, 반영
2. rollback : 변경사항 취소
3. savepoint : rollback 범위 지정

START TRANSACTION; -- 트랜잭션 시작

DELETE FROM emp WHERE deptno = 40; -- 1

UPDATE emp SET job = 'CLERK', mgr = 7782 WHERE ename='hong'; -- 2

SAVEPOINT S1; -- savepoint 생성

UPDATE emp SET comm = 100 WHERE comm IS NULL OR comm = 0; -- 3

ROLLBACK TO S1; 

UPDATE emp SET comm = comm+sal*0.1 WHERE deptno = 10; -- 4

COMMIT;

-- 결과 : ROLLBACK TO S1에 의해 1, 2, 4 쿼리만 실행된다

---

DROP vs. TRUNCATE vs. DELETE

DROP	TRUNCATE	DELETE
DDL	DDL/DML	DML
Auto-Commit / Rollback 안 됨	Auto-Commit / Rollback 안 됨	User-Commit / Rollback 가능
log 남지 않음	log 남지 않음	log 남음
테이블의 정의 삭제	최초의 상태, 디스크 초기화, 테이블 재사용	데이터만 삭제, 용량 유지