3장. 관리 구문

1절. DML

 

- DML vs DDL

DDL은 명령어 수행과 동시에 즉시 반영

DML은 COMMIT 명령을 통해 트랜잭션 종료 (단, SQL Server는 auto commit)

1. INSERT

INSERT INTO 테이블명 [칼럼들] VALUES 값들;

INTO 절의 칼럼명과 VALUES 절의 값을 1:1 매핑

 

2. UPDATE

UPDATE 테이블명
SET 칼럼명 = 값
...
[WHERE 조건식]

 

3. DELETE

- WHERE 절을 기술하지 않으면 전체 삭제

- 전체 테이블 삭제 시 부하가 적은 TRUNCATE TABLE 사용 권고

- TRUNCATE TABLE은 ROLLBACK이 불가능 (단, SQL Server는 가능)

DELETE [FROM] 테이블명
[WHERE 조건식]

 

4. MERGE

MERGE
INTO 타겟 테이블
USING 소스 테이블
ON (조인 조건)
WHEN MATCHED THEN
    ...
WHEN NOT MATCHED THEN
    ...

 

 

2절. TCL

 

1. 트랜잭션 개요

 

- 특징

1. 원자성(atomicity): all or nothing

2. 일관성(consistency): 트랜잭션 이후 오류가 없음을 보장

3. 고립성(isolation): COMMIT, ROLLBACK 이전엔 잠금(Locking)

4. 지속성(durability): 트랜잭션 수행 시 영구 저장

잠금(Locking): 트랜잭션 수행 동안 다른 트랜잭션이 동시에 접근하는 것을 방지

 

- 종류

1. AUTO COMMIT (SQL Server)

자동 시작, 자동 COMMIT/ROLLBACK

    * 예외

       1) 접속 단절 시 롤백

 

2. 암시적 트랜잭션 (Oracle)

자동 시작, 수동 COMMIT/ROLLBACK

    * 예외

        1) DDL 실행 시 오토커밋

        2) 접속 정상종료 시 오토커밋

        3) 접속 단절 시 롤백

 

3. 명시적 트랜잭션

수동 시작, 수동 COMMIT/ROLLBACK

 

- 효과

1. 데이터 무결성

2. 변경사항 확인

3. 그룹핑: 논리적으로 연관된 작업을 묶어 처리

 

4. SAVEPOINT

- 동일이름으로 여러 개의 저장점을 정의했을 때는 마지막 정의점만 유효

- 특정 저장점까지 롤백하면 이후 저장점은 무효

 

 

3절. DDL

 

1. CREATE TABLE

CREATE TABLE 테이블명 (
    칼럼명1 데이터유형 [default] [NOT NULL],
    ...
);

 

- 주의사항

1. 유일한 대문자 단수(테이블명과 칼럼명은 중복 X, 대소문자 구분 X, 테이블명은 가능한 단수형 권고)

2. A-Z, a-z, 0-9, _, $, #만 허용

3. DATETIME 유형은 크기 제약 X

4. 문자 유형은 반드시 최대 길이 표시

 

- 제약 조건의 종류

1. PRIMARY KEY(기본키): 고유키 + NOT NULL

2. UNIQUE(고유키)

3. NOT NULL

4. CHECK: 값의 범위 제한

5. FOREIGN KEY(외래키)

 

- 테이블 구조 확인

--Oracle
DESCRIBE 테이블명;
DESC 테이블명;

--SQL Server
sp_help 'dbo.테이블명'

 

- SELECT 문을 이용한 테이블 생성

--Oracle: CTAS
--NOT NULL 제약만 적용
CREATE TABLE TEAM_TEMP AS SELECT * FROM TEAM

--SQL Server
--NOT NULL, Identity 적용
SELECT * INTO TEAM_TEMP FROM TEAM

 

2. ALTER TABLE

- ADD : 마지막 칼럼에 생성

ALTER TABLE 테이블명
ADD (칼럼명1 데이터유형 [default] [NOT NULL],
     ...
);

 

- DROP

ALTER TABLE 테이블명 DROP (칼럼명, ...);

 

- MODIFY

ALTER TABLE 테이블명
MODIFY (칼럼명1, 데이터유형 [default] [NOT NULL],
        ...
);

 

    * 제약사항

1. NULL 뿐: 칼럼 크기를 줄일 수 있음, 데이터 유형 변경 가능, NOT NULL 제약 추가 가능

2. DEFAULT값 변경: 이후 발생하는 행 삽입에만 영향

 

- RENAME COLUMN

-- Oracle
ALTER TABLE 테이블명 RENAME COLUMNS 기존 칼럼명 TO 새로운 칼럼명;

-- SQL Server
sp_rename '기존 칼럼명', '새로운 칼럼명', 'COLUMN';

 

- DROP CONSTRAINT

ALTER TABLE 테이블명 DROP CONSTRAINT 제약조건명;

 

- ADD CONSTRAINT

ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건명 제약조건 (칼럼명);

 

3. RENAME TABLE

--Oracle
RENAME 기존 테이블명 TO 새로운 테이블명;

--SQL Server
sp_rename '기존 테이블명', '새로운 테이블명';

 

4. DROP TABLE

- 테이블 구조까지 완전 삭제

DROP TABLE 테이블명 [CASCADE CONSTRAINT];

 

5. TRAUNCATE TABLE

- 테이블 구조를 남겨둠

TRUNCATE TABLE 테이블명;

 

 

4절. DCL

 

2. 유저와 권한

1) Oracle

 유저를 통해 DB 접속 및 권한 부여

* 오라클 제공 유저

1. SCOTT: 샘플 계정 (default password : TIGER)

2. SYS: 최상위 관리자 계정

3. SYSTEM: DBA 계정(백업, 복구 등 일부 기능만 제외)

 

2) SQL Server

로그인과 유저 매핑 (LOGIN → USER → SCHEMA → TABLE)

* 유형

1. MS 윈도우 운영체제 인증 방식 (=트러스트된 연결)

1) 기본 인증 모드

2) Kerberos 보안 프로토콜

3) 강력한 암호 정책

2. 혼합 모드

    오라클처럼 아이디, 비밀번호로 접속

 

- 유저 생성과 시스템 권한 부여

/* Oracle */

-- 유저 생성
CONN SYSTEM/MANAGER;
CREATE USER SQLD IDENTIFIED BY DB2019;

-- 권한 부여
GRANT CREATE SESSION TO SQLD;
GRANT CREATE TABLE TO SQLD;


/* SQL Server */

-- 유저 생성
(login sa)
CREATE LOGIN SQLD WITH PASSWORD='DB2019', DEFAULT_DATABAESE=AdventureWorks;
USE AdventureWorks;
CREATE USER SQLD FOR LOGIN SQLD WITH DEFAULT_SCHEMA = dbo;

-- 권한 부여
GRANT CREATE TABLE TO SQLD;
GRANT CONTROL ON SCHEMA::DBO TO SQLD;

 

- 객체(object) 권한 부여

GRANT [SELECT, INSERT, DELETE, UPDATE, ...] ON MENU TO SCOTT;
REVOKE [SELECT, INSERT, DELETE, UPDATE, ...] ON MENU FROM SCOTT;

 

- 유저 삭제

DROP USER SQLD CASCADE;

CASCADE: 해당 유저가 생성한 오브젝트를 먼저 삭제한 후 유저 삭제

 

3. Role을 이용한 권한 부여

 ROLE을 생성 → ROLE에 권한 부여 → ROLE을 다른 ROLE이나 유저에 부여

CREATE ROLE LOGIN_TABLE;
GRANT CREATE SESSION, CREATE TABLE TO LOGIN_TABLE;
GRANT LOGIN_TABLE TO SQLD;

 

* Oracle의 기본 ROLE

1. CONNECT: 로그인 권한

2. RESOURCE: 오브젝트 생성 권한

 

* SQL Server의 기본 ROLE

1) 서버 수준 : 인스턴스 수준 로그인

public: 모든 SQL Server 로그인

bulkadmin: BULK INSERT

dbcreator: DB 생성, 변경, 삭제, 복원

diskadmin: 디스크 파일 관리

proessadmin: 실행 중인 프로세스 종료

securityadmin: 로그인 관리

serveradmin: 구성 옵션 변경, 서버 종료

setupadmin: 연결된 서버 추가, 제거

sysadmin: 모든 작업 수행

 

2) DB 수준 : 데이터베이스 수준 로그인

db_accessadmin: 액세스 추가, 제거

db_backupoperator: 백업

db_datareader: 모든 데이터 읽기

db_datawriter: 모든 데이터 추가, 삭제, 변경

db_denydatareader: 모든 데이터 못 읽음

db_denydatawriter: 모든 데이터 추가, 삭제, 변경 불가

db_owner: 구성 관리 및 삭제

db_securityadmin: 권한 관리

 

---

정리

1) DML

INSERT, UPDATE, DELETE, MERGE

 

2) TCL

COMMIT, ROLLBACK, SAVEPOINT

 

3) DDL

CREATE TABLE, ALTER TABLE, DROP TABLE

 

4) DCL

CREATE USER, DROP USER, GRANT, REVOKE

---

추가

트랜잭션

오라클: UPDATE 문이 시작된 시점을 기준으로 갱신 대상을 식별

SQL Server: 레코드에 도달한 시점을 기준으로 갱신 대상을 식별

* 블로킹된 SQL은 사라지는게 아니라 보류되는 것!

 

DML vs DDL

DDL은 암시적으로 COMMIT을 수행

DELECT는 DML, TRUNCATE는 DDL

 

INSERT

INSERT + NOT EXISTS = 중복 필터링

 

UPDATE

UPDATE + EXITSTS = MERGE = NULL 입력 방지

조인뷰를 이용한 업데이트 가능

 

MERGE

USING 절 소스테이블의 조인 컬럼 기준 유일성을 보장해야함

 

DROP

FK 제약 조건에 의해 참조되는 테이블을 제거하려면 먼저 FK 제약 조건을 제거

 

제약 조건

1) PK

    오라클: NOT NULL 자동 제약

    SQL Server: 명시적으로 NOT NULL 제약

2) UNIQUE

    오라클: NULL값 얼마든지 입력

    SQL Server: NULL값도 하나만 입력

 

DCL

1) 로그인: CREATE SESSION

2) 테이블 생성: CREATE TABLE - 자신 스키마에 테이블 생성

                    CREATE ANY TABLE - 모든 스키마에 테이블 생성

3) 행 삽입: QUOTA 100M - 100MB 한도 설정

              QUOTA UNLIMITED - 무제한 권한 설정

              UNLIMITED TABLESPACE - 모든 테이블스페이스에 무제한 권한 설정

 

객체 권한

- GRANT [권한명] ON [객체명] TO ~

- REVOKE [권한명] ON [객체명] FROM ~

Table: EXECUTE 빼고 전부

View: 선택, 갱신, 삽입, 삭제

Sequence: ALTER, SELECT

Procedure: EXECUTE

 

ROLE

CREATE ROLE

DROP ROLE