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

Part1, DML

📍DML : Data Manipulation Language

• 삽입INSETRT, 수정UPDATE, 삭제DELETE, 병합MERGE
• 저장commit 혹은 취소rollback 반드시 필요

 

📍1. INSERT (INTO 컬럼 VALUES( ))

• 테이블에 행을 삽입할 때 사용
  • 오라클 : 한번에 한 행씩 → 한번에 여러개 할라면 서브쿼리 작성해야함
  • SQL server : 한번에 여러행 동시 삽입 가능
   
• 컬럼별 데이터 타입과 사이즈에 맞게 삽입

 

컬럼수 만큼 value 추가해줘야 함, 2개만 넣을거면 앞에 컬럼명 명시해

 

 

📍2. UPDATE (SET 컬럼 = 수정값)

• 데이터를 컬럼단위로 수정할 때
• 다중컬럼 수정 가능 (콤마로 구분 or 서브쿼리)

단일컬럼 수정

다중컬럼 ① 콤마로 구분해서 SET에 넣어주기

 

 

📍3. DELETE (FROM  컬럼)

• 데이터를 행단위로 삭제할때 사용

 

📍4. MERGE (INTO 새롭게 수정할 테이블명 USING 참조테이블 ON (A.컬럼명 = B.컬럼명))

• 데이터 병합
• 참조테이블과 동일하게 맞추는 작업, 참조테이블에만 있다면 데이터가 입력되고 아니면, 그 값으로 수정
• INSERT, UPDATE, DELETE 작업을 동시에 수행하므로 주의

 

 

 

---

 

 

 

📍TCL : Transaction Control Language

• 트랜잭션 제어어, commit, rollback이 포함됨
  • 트랜잭션 : 분할할 수 없는 최소 단위, 어떤 트랜잭션이 발생했을 때 확정하고싶으면 커밋, 실수했다면 원복위한 롤백

❉ 트랜잭션의 특징
① 원자성 : 모두 동시에 성공적으로 롤백되거나, 동시에 커밋되어야 한다! 
② 일관성 : 트랜잭션 실행 전과 실행 이후에도 데이터베이스 내용의 잘못이 생기면 안됨
③ 고립성 : 트랜잭션 실행 도중 다른 트랜잭션의 영향을 받아 잘못된 결과를 만들어서는 안됨
④ 지속성 : 트랜잭션이 한번 성공적으로 수행되면 데이터베이스 내용이 영구적으로 저장

 

• DML 수행 후 커밋하지 않고, 정상 종료하지 않으면 LOCK 발생
  • 잠금 LOCK : 트랜잭션이 수행하는 동안 특정 데이터에 대해서 다른 트랜잭션이 동시에 접근하지 못하도록 제한
  • 잠금이 걸린 데이터는 잠금을 실행한 트랜잭션만이 접근 및 해제 가능 (관리자 권한 제외)

 

📍1. COMMIT

• 입력, 수정, 삭제한 데이터에 이상이 없을 경우 데이터를 저장하는 명령어
•  한번 COMMIT을 수행하면, COMMIT 이전에 수행된 모든 DML은 모두 저장되며 되돌릴 수 없음

 

📍2. ROLLBACK

• 테이블 내 입력, 수정, 삭제한 데이터에 대해 변경을 취소하는 명령어
• 데이터베이스에 저장되지 않고 최종 COMMIT 직전까지/변경전/특정 savepoint 지점으로 원복됨
  • savepoint : 트랜잭션 내에서 롤백을 부분적으로 수행하기 위해 사용자가 직접 지점을 지정하는 데 사용
  • 원하는 위치, 원하는 이름으로 설정가능 (책갈피 역할)
  • SAVEPOINT savepoin_name;
  • ROLLBACK TO savepoint_name 으로 원하는 지점까지 원복 가능
• 최종 COMMIT 시점 이전까지 ROLLBACK 가능

 

---

 

 

 

📍DDL : Data Definition Language

• 데이터 정의어
• 데이터 구조 정의 (테이블과 같은 객체를 생성, 삭제, 변경하는 언어) 
• 객체 생성-CREATE, 객체 변경-ALTER, 객체 삭제-DROP, 데이터 삭제-TRUNCATE
  • TRUNCATE : 구조는 남기고 데이터는 몽땅 삭제 ➡️ 어떻게 보면 DML의 특성이 아닐까..? 싶지만?
  • DDL에 포함된 이유는 AUTO COMMIT 특성 때문
  • 즉시 삭제, 원복 불가
• DDL을 수행하게 되면 자동 확정됨! 테이블 생성하면 rollback 불가, 자동저장

 

 

📍1. CREATE TABLE 테이블명(컬럼명 데이터타입, ••• )

• 테이블, 인덱스 와 같은 객체를 생성하는 명령어
• 테이블 생성 시 테이블명, 컬럼명, 컬럼순서, 컬럼 크기, 컬럼의 데이터 타입 정의 필수 (컬럼의 제약조건 및 기본값은 생략 가능)
• 테이블 생성시 소유자 명시 가능 (생략시 명령어 수행 계정 소유)

테이블 생성

 

• 테이블 복제 시 컬럼별칭으로 생성 가능
• CREATE 문에서 컬럼명 변경 가능 (쿼리에서 AS로 별칭 지정, 혹은 CREATE TABLE TEST2(A,B) 하면 A,B 가 컬럼명)
• NULL 속성도 복제됨
• 그러나, 테이블에 있는 제약조건, INDEX 등은 복제되지 않는다.

테이블 복제 (셀렉트 쿼리 결과 그대로, 원하는 컬럼이나 순서 지정 가능)

WHERE 1=2 무조건 거짓 → 데이터 없이 그냥 무조건 구조만 복제됨

 

 

📍데이터 타입

• SQL server는 문자 타입도 사이즈 생략 가능 → 디폴트값 1

데이터 타입	구분	설명
CHAR(n)		고정형(사이즈 전달 필수) 5를 쓰면 5바이트 까지, 3바이트 넣어도 무조건 5바이트로 저장됨 부족한 자릿수를 공백으로 채움
VARCHAR2(n)	오라클에서 주로 사용 ↔️ SQL : VARCHAR	가변형(사이즈 전달 필수), 적은 값이 입력되더라도 입력사이즈 그대로 반영
NUMBER(p, s)	오라클에서 주로 사용 ↔️ SQL : numeric	숫자형 타입, 자리수 생략가능, (총자리수, 소숫점 자릿수) 총자릿수보다 커지면 오류
DATE

 

 

 

📍2. ALTER

• 테이블 구조 변경(컬럼명, 컬럼 데이터타입, 컬럼 사이즈, 디폴트값, 컬럼 삭제/추가, 제약조건)
• 그러나 컬럼순서는 변경 불가~!!!! → 재생성으로 해결
• ① 컬럼을 추가 : ALTER TABLE 테이블명 ADD 컬럼명 VARCHAR2(30)
  • 무조건 새로 추가되는 컬럼은 맨 뒤에
  • 컬럼 추가시 데이터 타입 필수 (디폴트값, 제약조건은 생략가능)
  • 여러 컬럼 동시에 추가 가능 - 복수개일때는 반드시 괄호 사용, 하나일 때는 괄호 생략 가능
  • 이미 저장된 행들에는 다 NULL로 들어가는데, 거기에 NOT NULL 추가하면 안됨
    • 만약 저장된 데이터가없었으면 NOT NULL 조건 가능
    • 디폴트 값을 설정해두어도 데이터가 있을 때도 NOT NULL 조건 가능 
      ALTER TABLE TEMP
      ADD HPAGE VARCHAR2(30)
      DEFAULT 'WWW.ITWILL.CO.KR' NOT NULL; ➡️ 위에 빈칸들이 다 이 디폴트 값으로 채워질테니

 

② 컬럼(속성) 변경

• 컬럼의 사이즈, 데이터 타입, 디폴트값 변경
• 여러개 동시에 변경 가능 (여러개일 때는 괄호로 묶어줘야 함!)
• 1) 컬럼 사이즈 변경 : ALTER TABLE 테이블명 MODIFY (컬럼명1 NUMBER(10), 컬럼명2 VARCHAR(6));
  • 컬럼 사이즈 증가는 항상 가능하지만, 기존 데이터보다 작게하는건 불가능
    (데이터가 있을 경우 그 데이터의 최대 사이즈 만큼 축소 가능)

2) 데이터 타입 변경 : ALTER TABLE 테이블명 MODIFY 컬럼명 VARCHAR2(13)

• 빈 컬럼의 경우, 데이터가 비어있을 때만 데이터 타입 변경 가능 
  ex) 숫자 데이터가 있는 컬럼의 경우 데이터 타입 변경 오류!!!!
• CHAR, VARCHAR 타입의 경우 데이터가 있어도 그 둘간의 데이터 타입은 변경가능
  ex) CHAR → VARCHAR, VARCHAR → CHAR은 가능

 

3) 디폴트 값 변경 : ALTER TABLE 테이블명 MODIFY (컬럼명 DEFUALT 3000);

• 특정 컬럼에 값이 생략된 경우, 언급되지 않은 경우 자동으로 부여되는 값
• 이미 디폴트 값이 선언된 컬럼에 NULL을 INSERT하면, 디폴트값 아니고 NULL도 값이니까 NULL이 입력됨
• 디폴트 값을 해제할 때는 디폴트값을 null로 설정하면 됨
• 디폴트 값을 변경하더라도, 이미 입력된 데이터값에는 절~~대 영향을 미치지 않음, new 디폴트 값 선언 이후에 생성된 데이터부터 !

 

 

③ 컬럼 이름 변경 : ALTER TABLE 테이블명 RENAME COLUMN A TO B;

• 데이터가 있건 없건 항상 가능
• 문법에 컬럼이 들어갈 때에는 여러개 컬럼 괄호로 여러개 전달? 절대 불가!!!! 무조건 한 컬럼씩!

 

 

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

• 데이터가 있건 없건 항상 가능 → 휴지통에 들어가지 않고 바로 삭제 되어서 위험!!
• 동시에 여러컬럼 삭제 불가!!!! 괄호하든말든 불가!!!!

 

 

📍3. DROP : DROP TABLE 테이블명 PURGE

• 테이블이나 인덱스 객체 삭제
• 드랍 후에는 구조를 삭제하는 거니까 조회 불가 ↔️ TRUNCATE는 데이터 알맹이만 삭제하는거지 구조는 남아있어서 데이터는 없지만view 가능
  • TRUNCATE는 데이터알맹이만 삭제하는 대신 즉시 삭제 & 즉시 반영, AUTO COMMIT
  • 휴지통에도 안남음, 복구 불가능
• DROP TABLE 테이블명 PURGE : 영구삭제
• DROP TABLE 테이블명 : 휴지통(recyclebin), 테이블 삭제 복원 가능

❉ 삭제하는 것들 차이 비교 DELETE/DROP/TRUNCATE 차이
- [DML] DELETE : 데이터 한 행씩⭕️ or 전체 삭제, 롤백 가능
- [DDL] TRUNCATE : 데이터 내용 전체 삭제만 가능 (일부 삭제 불가), 즉시 반영 (롤백 불가)
- [DDL] DROP : 와랄락 삭제 (한 행씩 삭제 불가❌)데이터&구조 동시 삭제, 즉시 반영 (롤백 불가)

 

 

📍제약조건 : PRIMARY KEY, UNIQUE, NOT NULL, FOREIGN KEY

• 데이터 무결성을 위해 각 컬럼에 생성하는 데이터의 제약 장치
• 테이블 생성시, 컬럼 추가시 정의 가능, 이미 생성된 컬럼에 추가 가능!
• ① PRIMARY KEY : 각 행을 구별할 수 있는 유일한 식별자
  • 키를 구성하는 컬럼은 여러개가 될 수 잇음, 그러나 하나의 테이블에 여러 기본키를 생성할 수 없음
  • 중복허용 X, NULL 허용 X ➡️ UNIQUE + NOT NULL
  • NUMBER(10) CONSTRAINT + 제약 조건 이름

 

② UNIQUE : 중복을 허용하지 않음

• NULL은 패스 → 값이 정해진게 아니니까 중복은 아님.
• UNIQUE INDEX 자동 생성

A, A 중복되면 오류

 

 

③  NOT NULL

• 다른 제약조건이랑 다르게 제약조건을 추가하는 방식이 아님
• 처음에 not null 조건을 쓰지 않으면 nullable 한 상태. 거기서 not null로 바꿔야 함! (추가x → modify로 해결)

 

④ FOREIGN KEY → 자식 테이블에 걸음

• 다른 부모 테이블을 참조하면서 데이터를 관리하겠다는 목적으로 생성
• 반드시 부모 데이터의 참조컬럼이 사전에 PK 혹은 UNIQUE KEY를 가져야 함!

 

• 사원과 부서 중, 사원이 자식, 부서가 부모 → 사원보다 부서정보가 먼저 만들어져야 사원 데이터가 생길 때 부서 정보 언급 가능
• EMP에 foreign 키를 걸겠다. (사전에 부모 테이블에 리퍼런스키를 찾아서 pk, uk를 먼저 만들어줘야함)
• 부모 테이블의 DEPTNO에 PK로 설정하고
• 자식 테이블의 DEPTNO에는 foreign key 걸고, 부모쪽을 참조하겠다.

 

• 자식 테이블은 어떤 제한이 걸리나? : 부모에 있는 정보로만 삽입(INSERT)하거나 수정(UPDATE)할 수 있음), 하지만 삭제SMS 가능
• 반대로 부모 테이블은 값을 삭제(DELETE)할 수 없고 수정(UPDATE)도 안됨

 

❉ FOREIGN KEY 옵션 (생성시 정의, 변경 불가 → 재생성) ★
1) ON DELETE CASCADE : 부모 데이터 삭제 시 자식 데이터 함께 동시에 삭제
2) ON DELETE SET NULL : 부모 데이터 삭제 시 부모 키를 가지고 있는 정보만 자식 데이터의 참조값은 NULL로 수정

 

 

 

④ CHECK

• 직접적으로 데이터의 값 제한 (양수 1,2,3,4 중 하나)
• 이 컬럼의 범위가 이 안에서만 정해져야 한다!는 제약조건 추가 (연봉은 항상 0보다 크다고 설정함)

 

 

+) 기타 오브젝트

• 1. 뷰 VIEW : 저장공간을 가지지는 않지만 테이블 처럼 조회 및 수정할 수 있는 객체 (쿼리 알리아스 같은 느낌)
  • 종류 
    • 단순뷰 : 하나의 테이블 조회 뷰
    • 복합뷰 : 둘 이상의 테이블 조인 뷰
   

 

2. 시퀀스 SEQUENCE : 자동으로 연속적인 숫자 부여해주는 객체

- START WITH : 시작값

- CYCLE : 마지막 값까지 왔을 때는 다시 올라가서 MINVALUE, MAXVALUE가 되기도 (제한값이면서 동시에 재시작점)

- CACHE : 여러개 미리 단어를 할당해 놓음

 

 

 

 

3. 시노님 SYNONYM : 테이블 별칭 생성

- SCOTT.EMP 말고 그냥 EMP로 조회가능하게

- PUBLIC을 넣으면 시노님을 생성한 유저만 사용가능 ↔️ PRIVATE SYNONYM

 

 

---

 

 

 

📍DCL : Data Control Language

• 데이터 제어어로 객체에 대한 권한을 부여GRANT 하거나 회수 REVOKE하는 기능
• 테이블을 일부 사람이 볼 수 있도록 (인사팀 테이블은 인사팀만 볼 수 있도록 통제)

 

📍권한의 종류

• 1) 오브젝트 권한 : 테이블에 대한 권한 제어
  • 특정 테이블에 대한 SELECT, INSERT, UPDATE, DELETE, MERGE 권한
  • 테이블 소유자는 타 계정에 테이블 조회 및 수정 권한 부여 및 회수 가능
• 2) 시스템 권한 : 시스템 작업(테이블 생성)을 제어
  • 테이블 생성 권한, 인덱스 삭제 권한
  • 관리자 권한만 권한 부여 및 회수 가능

 

📍GRANT : 권한을 부여하는 것

• 권한 부여 시 반드시 테이블 소유자나 관리자 계정 sys, system으로 접속하여 권한을 부여해야 함
• 동시에 여러 유저, 여러 권한 부여 가능하지만, 동시에 여러 객체(테이블)의 권한을 부여하는 것은 불가능!!!
• GRANT 권한종류 ON 테이블명 TO 유저

 

📍REVOKE : 권한을 회수하는 것

• 동시에 여러 권한 회수 가능, 동시에 여러 유저한테 권한 회수 가능
• 이미 회수된 권한 재회수 불가
• REVOKE 권한이름 ON 테이블명 FROM 유저 ; 

 

 

📍ROLE : 롤 (객체, 권한의 묶음)

• CREATE로 생성하는 객체
• 권한의 묶음 (생성 가능한 객체) → 권한 통째로 제공하고 효율적으로 관리하기 위해
• SYSTEM 계정에서 ROLE 생성 가능 
• 롤의 권한을 바구니 여러개 담고, 롤이 유저인것마냥 이 롤에 이런 권한들을 담아두겠다. 하고 그 롤을 HR한테 전달하면 여러 권한 묶음을 받게 됨

 

 

+) 롤을 통해 부여한 권한은 직접 회수가 안되고, 롤에서 권한을 회수해야 한다.

HR에게 롤을 통해서 권한을 줬지만, 직접 REVOKE하면 안먹음

 

 

📍권한 부여 옵션 (중간 관리자의 권한 → 팀대표한테 권리 일임)  ★★★

회수 관계가 반대!!

• 1) WITH GRANT OPTION - 오브젝트 권한
  • A라는 유저한테 윗 그랜트 옵션으로 EMP 테이블에 대해 권한을 부여했다면, A는 다른 유저한테 EMP 테이블 권한을 줄 수 있음
  • 중간 관리자가 부여한 권한은  반드시, ★중간 관리자만★ 회수 가능 (직접 회수 불가능)
  • 중간 관리자한테 부여된 권환 회수 시 제 3자에게 부여됨 권한도 ★함께 회수됨★
• 2) WITH ADMIN OPTION - 롤, 시스템 권한
  • 윗 어드민 옵션을 통해 부여받은 롤, 시스템 권한을 다른 사용자한테 부여할 수 있음
  • 중간 관리자를 거치지 않고 ★직접 회수가능★
  • but, 중간 관리자한테 부여한 권한을 회수하더라도 제 3자에게 부여된 권한은 ★남아있음!★
   

그랜트 옵션은 중간관리자 권한 회수하면 제 3자도 함께 불가능

 

시스템은 관리자 권한 회수해도 제 3자는 계속 가능