SQLD(SQLP) 과목2 - 3장. 관리 구문

DML (data manipulation language)

관리할 자료들을 테이블에 입력, 수정, 삭제

INSERT

1. 단일 행 INSERT 문

한 번에 한 행만 입력됨

INSERT INTO 테이블명 [(칼럼1, 칼럼2, ...)] VALUES (값1, 값2, ...);

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

▶ 칼럼명의 기술 순서는 테이블에 정의된 칼럼 순서와 동일할 필요 없음

▶ INTO 절에 기술하지 않은 칼럼은 DEFAULT로 NULL 값이 입력됨(단, Primary Key 제약 또는 NOT NULL 제약이 지정된 칼럼은 오류가 발생 - NULL 값을 허용하지 않으므로)

▶ 데이터가 문자 유형인 경우 SINGLE QUOTATION(')과 함께 값을 ㅇㅂ력

[예제]

INSERT INTO PLAYER (PLAYER_ID, PLAYER_NAME, TEAM_ID, POSITION, HEIGHT, WEIGHT, BACK_NO) 
VALUES ('2002007', '박지성', 'K07', 'MF', 178, 73, 7);

현재 사용 중인 PLAYER_ID에 1을 더한 값으로 PLAYER_ID 설정하기

INSERT INTO PLAYER (PLAYER_ID, PLAYER_NAME, TEAM_ID)
VALUES ((SELECT TO_CHAR(MAX(TO_NUMBER(PLAYER_ID))+1) FROM PLAYER), '홍길동', 'K06');

2. 서브 쿼리를 이용한 다중 행 INSERT 문

서브 쿼리의 결과가 다중 행이면, 한 번에 여러 건이 입력됨

▶ INTO 절의 칼럼명 개수와 서브 쿼리의 SELECT 절 칼럼 개수가 일치해야 함

INSERT INTO 테이블명 [(칼럼1, 칼럼2, ...)] 서브쿼리;

[예제]

INSERT INTO TEAM (TEAM_ID, REGION_NAME, TEAM_NAME, ORIG_YYYY, STADIUM_ID) 
SELECT 
    REPLACE(TEAM_ID, 'K', 'A') AS TEAM_ID, -- TEAM_ID에서 K를 A로 변경
	REGION_NAME, 
	REGION_NAME || '올스타' AS TEAM_NAME, -- REGION_NAME + 올스타
	2019 AS ORIG_YYYY,
	STADIUM_ID
FROM TEAM
WHERE REGION_NAME IN ('성남', '인천');

INSERT INTO PLAYER (PLAYER_ID, PLAYER_NAME, TEAM_ID, POSITION)
SELECT 
    'A' || SUBSTR(PLAYER_ID, 2) AS PLAYER_ID, -- A + (PLAYER_ID에서 앞의 2개를 제외한 나머지)
	PLAYER_NAME,
	REPLACE(TEAM_ID, 'K', 'A') AS TEAM_ID, -- TEAM_ID에서 K를 A로 변경
	POSITION
FROM PLAYER
WHERE TEAM_ID IN ('K04', 'K08');

UPDATE

UPDATE 테이블 명
	SET 수정할 칼럼명1 = 수정될 새로운 값1
    [, 수정할 칼럼명2 = 수정될 새로운 값2]
    [, ...                             ]
[WHERE 수정 대상 식별 조건식];

[예제]

UPDATE PLAYER
	SET BACK_NO = 99;

UPDATE PLAYER
	SET POSITION = 'MF'
WHERE POSITION IS NULL;

▶ SET 절에 서브 쿼리 사용 시 서브 쿼리의 결과로 값이 수정됨

UPDATE TEAM A
	SET A.ADDRESS = (SELECT B.ADDRESS FROM STADIUM B WHERE B.HOMETEAM_ID = A.TEAM_ID)
WHERE A.ORIG_YYYY > 2000;

UPDATE STADIUM A
	SET (A.DDD, A.TEL) = (SELECT B.DDD, B.TEL FROM TEAM B WHERE A.HOMETEAM_ID = B.TEAM_ID);

UPDATE STADIUM A
	SET (A.DDD, A.TEL) = (SELECT B.DDD, B.TEL FROM TEAM B WHERE A.HOMETEAM_ID = B.TEAM_ID)
WHERE EXISTS (SELECT 1 FROM TEAM X WHERE X.TEAM_ID = A.HOMETEAM_ID);

위의 구문은 TEAM 테이블을 2번 조회하기 때문에 비효율이 발생할 수 있음.

따라서 다음과 같이 MERGE 문을 사용하여 TEAM 테이블을 1번만 조회하여 데이터를 수정.

MERGE INTO STADIUM T
USING TEAM S
ON (T.HOMETEAM_ID = S.TEAM_ID)
WHEN MATCHED THEN 
	UPDATE 
	SET T.DDD = S.DDD, T.TEL = S.TEL;

DELETE

DELETE [FROM] 테이블명
[WHERE 삭제 대상 식별 조건식];

[예제]

DELETE FROM PLAYER
WHERE POSITION = 'DF' AND JOIN_YYYY < 2010;

▶ WHERE 절에 서브 쿼리 사용 시 다른 테이블을 참조해 삭제할 행을 식별할 수 있음

DELETE FROM PLAYER A
WHERE EXISTS (SELECT 1 FROM TEAM B WHERE A.TEAM_ID = B.TEAM_ID AND B.ORIG_YYYY < 1980);

위의 예제에 나는 다음과 같이 작성하였다

DELETE FROM PLAYER A
WHERE A.TEAM_ID IN (SELECT TEAM_ID FROM TEAM B WHERE B.ORIG_YYYY < 1980);

DELETE FROM PLAYER
WHERE TEAM_ID IN (SELECT TEAM_ID FROM PLAYER GROUP BY TEAM_ID HAVING COUNT(*) <= 10);

MERGE

MERGE문 사용 시 새로운 행을 입력하거나 기존 행을 수정하는 작업을 한 번에 할 수 있음

MERGE 
INTO 타겟 테이블명
USING 소스 테이블 명
ON (조인 조건식)

WHEN MATCHED THEN
	UPDATE
    SET 수정할 칼럼명1 = 수정될 새로운 값1
    [, 수정할 칼럼명2 = 수정될 새로운 값2, ...]
WHEN NOT MATCHED THEN
	INSERT [(칼럼1, 칼럼2, ...)]
    VALUES (값1, 값2, ...);

MERGE + 입력 또는 수정되어야 할 타깃 테이블 명을 입력
USING 절: 입력 및 수정에 사용할 소스 테이블을 입력
ON 절: 타겟 테이블과 소스 테이블 간의 조인 조건식을 기술
ON 절의 조인 조건에 따라
- 1. 조인에 성공한 행들에 대해서는 WHEN MATCHED THEN 아래 UPDATE 구문을 수행
- 2. 조인에 실패한 행들에 대해서는 WHEN NOT MATCHED THEN 아래 INSERT 구문을 수행

[예제]

연습을 위해 TEAM_TMP 테이블을 임시로 생성

CREATE TABLE TEAM_TMP AS
SELECT 
    NVL(B.TEAM_ID, 'K' || ROW_NUMBER() OVER(ORDER BY B.TEAM_ID, A.STADIUM_ID)) AS TEAM_ID,
	SUBSTR(A.STADIUM_NAME, 1, 2) AS REGION_NAME,
	SUBSTR(A.STADIUM_NAME, 1, 2) || NVL2(B.TEAM_NAME, 'FC', '시티즌') AS TEAM_NAME,
	A.STADIUM_ID, A.DDD, A.TEL
FROM STADIUM A, TEAM B
WHERE B.STADIUM_ID(+) = A.STADIUM_ID;

MERGE 
INTO TEAM T
USING TEAM_TMP S
ON (T.TEAM_ID = S.TEAM_ID)

WHEN MATCHED THEN

UPDATE 
    SET T.REGION_NAME = S.REGION_NAME,
	T.TEAM_NAME = S.TEAM_NAME,
	T.DDD = S.DDD,
	T.TEL = S.TEL

WHEN NOT MATCHED THEN
	INSERT (T.TEAM_ID, T.REGION_NAME, T.TEAM_NAME, T.STADIUM_ID, T.DDD, T.TEL)
	VALUES (S.TEAM_ID, S.REGION_NAME, S.TEAM_NAME, S.STADIUM_ID, S.DDD, S.TEL);

▶ USING 절에 소스 테이블 대신 서브 쿼리를 사용해 입력 및 수정할 수 있음

MERGE 
INTO TEAM T
USING (SELECT * FROM TEAM_TMP WHERE REGION_NAME IN ('성남', '부산', '대구', '전주')) S ON (T.TEAM_ID = S.TEAM_ID)
ON (T.TEAM_ID = S.TEAM_ID)

WHEN MATCHED THEN

UPDATE 
    SET T.REGION_NAME = S.REGION_NAME,
	T.TEAM_NAME = S.TEAM_NAME,
	T.DDD = S.DDD,
	T.TEL = S.TEL

WHEN NOT MATCHED THEN
	INSERT (T.TEAM_ID, T.REGION_NAME, T.TEAM_NAME, T.STADIUM_ID, T.DDD, T.TEL)
	VALUES (S.TEAM_ID, S.REGION_NAME, S.TEAM_NAME, S.STADIUM_ID, S.DDD, S.TEL);

MERGE 
INTO TEAM T
USING TEAM_TMP S
ON (T.TEAM_ID = S.TEAM_ID)

WHEN NOT MATCHED THEN

INSERT (T.TEAM_ID, T.REGION_NAME, T.TEAM_NAME, T.STADIUM_ID, T.DDD, T.TEL)
VALUES (S.TEAM_ID, S.REGION_NAME, S.TEAM_NAME, S.STADIUM_ID, S.DDD, S.TEL);

유의할 점

DDL 명령어와 DML 명령어의 명령어 처리 방식 차이
1. DDL 명령어(CREATE, ALTER, RENAME, DROP): 데이터 구조의 변경이 DDL 명령어 수행이 완료됨과 동시에 즉시 반영됨
2. DML 명령어(INSERT, DELETE, UPDATE, SELECT): 데이터의 변경 사항을 테이블에 영구적으로 저장하기 위해서는 COMMIT 명령을 수행해 TRANSACTION을 종료해야 함
  ** SQL Server의 경우 기본적으로 DML 명령어를 AUTO COMMIT 방식으로 처리 → 즉시 반영 **
테이블의 전체 데이터를 사용하는 경우 DELETE TABLE 보다는 TRUNCATE TABLE을 권고
→ DELETE TABLE은 삭제된 데이터를 로그로 저장하기 때문에 시스템 부하가 클 수 있음
→ TRUNCATE TABLE은 삭제된 데이터의 로그가 없어 ROLLBACK 불가능
** SQL Server의 경우 사용자가 임의적으로 트랜잭션 시작 후 TRUNCATE TABLE을 이용해 데이터를 삭제한 이후 오류가 발견되어 복구를 원할 경우 ROLLBACK문을 이용해 원 상태로 되돌릴 수 있음 **

TCL (transaction control language)

= ALL OR NOTHING

트랜잭션의 대상이 되는 SQL 문

DML문

** 이때 SELECT문은 직접적인 트랜잭션의 대상은 아니지만 만약 SELECT FOR UPDATE 등 배타적 LOCK을 요구하는 SELECT문의 경우 트랜잭션의 대상이 될 수 있음 **

트랜잭션의 특성

원자성 (Atomicity)	all or nothing
일관성 (Consistency)	트랜잭션이 실행되기 전의 데이터베이스 내용이 잘못되어 있지 않다면 트랜잭션이 실행된 이후에도 데이터베이스의 내용에 잘못이 있으면 안됨
고립성 (Isolation)	트랜잭션이 실행되는 도중에 다른 트랜잭션의 영향을 받아 잘못된 결과를 만들어서는 안됨
지속성 (Durability)	트랜잭션이 성공적으로 수행되면, 그 트랜잭션이 갱신한 데이터베이스의 내용은 영구적으로 저장됨

COMMIT

입력, 수정, 삭제한 데이터에 대해 전혀 문제가 없다고 판단했을 경우 COMMIT 명령어로 트랜잭션을 완료

COMMIT이나 ROLLBACK 이전의 상태

데이터 변경을 취소해 이전 상태로 복구 가능
현재 사용자는 SELECT 문장으로 결과 확인 가능
다른 사용자는 현재 사용자가 수행한 명령의 결과 볼 수 없음
변경된 행은 잠금(LOCKING)이 설정되어 다른 사용자가 변경 불가

COMMIT 이후의 상태

데이터에 대한 변경 사항이 데이터 베이스에 반영됨
이전 데이터는 영원히 잃어버리게 됨
모든 사용자는 결과를 볼 수 있음
관련된 행에 대한 잠금(LOCKING)이 풀리고, 다른 사용자들이 행을 조작할 수 있게 됨

[예제]

INSERT 
INTO PLAYER (PLAYER_ID, TEAM_ID, PLAYER_NAME, POSITION, HEIGHT, WEIGHT, BACK_NO)
VALUES ('1997035', 'K02', '이운재', 'GK', 182, 82, 1);

COMMIT;

SQL Server의 COMMIT

Oracle의 경우 DBMS가 트랜잭션을 내부적으로 실행하며 DML 문장 수행 후 사용자가 임의로 COMMIT 혹은 ROLLBACK을 수행해 주어야 트랜잭션이 종료됨(일부 툴에서는 AUTO COMMIT으로 선택 가능)

SQL Server의 경우 기본적으로 AUTO COMMIT 모드이므로 DML 수행 후 사용자가 COMMIT이나 ROLLBACK을 처리할 필요가 없음

→ DML 구문 성공 시 자동으로 COMMIT

→ 오류 발생 시 자동으로 ROLLBACK 됨

SQL Server에서 트랜잭션은 다음과 같이 3가지 방식으로 이루어짐

1. AUTO COMMIT

DML, DDL 수행할 때마다 DBMS가 트랜잭션을 컨트롤

→ 명령어가 성공적으로 수행되는 경우 자동으로 COMMIT

→ 오류 발생 시 자동으로 ROLLBACK

2. 암시적 트랜잭션

Oracle과 같은 방식, 트랜잭션의 시작은 DBMS가 처리하고 끝은 사용자가 명시적으로 COMMIT 또는 ROLLBACK으로 처리

→ 인스턴스 단위 또는 세션 단위로 설정 가능

→ 인스턴스 단위로 설정: 서버 속성 창의 연결화면에서 기본 연결 옵션 중 암시적 트랜잭션에 체크

→ 세션 단위로 설정: SET IMPLICIT TRANSACTION ON 사용

3. 명시적 트랜잭션

트랜잭션의 시작과 끝을 모두 사용자가 명시적으로 지정

→ 트랜잭션 시작: BEGIN TRANSACTION ( = BEGIN TRAN)

→ 트랜잭션 종료: COMMIT TRANSACTION ( = COMMIT), ROLLBACK TRANSACTION ( = ROLLBACK)

** ROLLBACK 구문을 만나면 BEGIN TRANSACTION 시점까지 모두 ROLLBACK이 수행됨 **

ROLLBACK

테이블 내 입력한 데이터나 수정한 데이터, 삭제한 데이터에 대해 COMMIT 이전에는 변경사항을 취소할 수 있음

▶ 데이터 변경 사항이 취소되어 데이터가 이전 상태로 복구됨

▶ 관련된 행에 대한 잠금(LOCKING)이 해제되어 다른 사용자들이 데이터 변경을 할 수 있게 됨

▶ SQL Server의 경우 AUTO COMMIT이 DEFAULT이므로 ROLLBACK 수행을 위해서는 명시적으로 트랜잭션을 수행해야 함

ROLLBACK 이후의 상태

데이터에 대한 변경 사항이 취소됨
데이터가 트랜잭션 시작 이전의 상태로 되돌려짐
관련된 행에 대한 잠금(LOCKING)이 풀리고, 다른 사용자들이 행을 조작할 수 있게 됨

[예제]

INSERT
INTO PLAYER (PLAYER_ID, TEAM_ID, PLAYER_NAME, POSITION, HEIGHT, WEIGHT, BACK_NO)
VALUES ('1997035', 'K02', '이운재', 'GK', 182, 82, 1);

ROLLBACK;

SAVEPOINT

저장점(SAVEPOINT) 정의 시 롤백할 때 트랜잭션에 포함된 전체 작업을 롤백하는 것이 아니라 현시점에서 SAVEPOINT까지 트랜잭션의 일부만 롤백 가능

** 일부 툴에서는 지원하지 않을 수 있음 **

Oracle 문법

SAVEPOINT 지정

SAVEPOINT SVPT1;

SVPT1까지 롤백

ROLLBACK TO SVPT1;

SQL Server 문법

SAVEPOINT 지정

SAVE TRANSACTION SVPT1;

SVPT1까지 롤백

ROLLBACK TRANSACTION SVPT1;

COMMIT, ROLLBACK의 목적

해당 테이블에 데이터의 변경을 발생시키는 INSERT, UPDATE, DELETE 명령어 수행 시 변경되는 데이터의 무결성 보장

DDL (data definition language)

CREATE TABLE

CREATE TABKE  XODKQNFAYD (
	칼럼명1 데이터 유형 [기본 값] [NOT NULL],
    칼럼명2 데이터 유형	[기본 값] [NOT NULL],
    ...
);

주의해야할 규칙

테이블 명은 객체를 의미할 수 있는 적절한 이름 사용
** 가능한 단수형 권고 **
테이블 명은 다른 테이블의 이름과 중복되지 않아야 함
한 테이블 내에서는 칼럼명이 중복되게 지정될 수 없음
테이블 이름을 지정하고 각 칼럼들은 괄호('()')로 묶어 지정
각 칼럼들은 콤마(', ')로 구분되고, 테이블 생성문의 끝은 세미콜론(';')으로 끝남
** 마지막 칼럼은 콤마를 찍지 않음 **
칼럼에 대해서는 다른 테이블까지 고려해 데이터 베이스 내에서는 일관성 있게 사용하는 것이 좋음(데이터 표준화 관점)
칼럼 뒤에 데이터 유형은 꼭 지정되어야 함
테이블명과 칼럼명은 반드시 문자로 시작해야 하고, 벤더 별로 길이에 대한 한계가 있음
벤더에서 사전에 정의된 예약어(Reserved word)는 사용 불가
A-Z, a-z, 0-9 _, $, # 문자만 허용됨
테이블 생성 시 대소문자 구분 X
** 기본적으로 테이블이나 칼럼명은 대문자로 만들어짐 **
DATETIME 데이터 유형에는 별도로 크기 지정 X
문자 데이터 유형은 반드시 가질 수 있는 최대 길이를 표시해야 함
칼럼에 대한 제약 조건이 있으면 CONSTRAINT를 이용해 추가 가능

[예시 - Oracle]

CREATE TABLE TEAM (
    TEAM_ID CHAR(3) NOT NULL,
    REGION_NAME VARCHAR2(8) NOT NULL,
    TEAM_NAME VARCHAR2(40) NOT NULL,
    E_TEAM_NAME VARCHAR2(50),
    ORIG_YYYY CHAR(4),
    STADIUM_ID CHAR(3) NOT NULL,
    ZIP_CODE1 CHAR(3),
    ZIP_CODE2 CHAR(3),
    ADDRESS VARCHAR2(80),
    DDD VARCHAR2(3),
    TEL VARCHAR2(10),
    FAX VARCHAR2(10),
    HOMEPAGE VARCHAR2(50),
    OWNER VARCHAR2(10),
    CONSTRAINT TEAM_PK PRIMARY KEY (TEAM_ID)
)

CREATE TABLE PLAYER (
    PLAYER_ID CHAR(7) NOT NULL,
    PLAYER_NAME VARCHAR2(20) NOT NULL,
    TEAM_ID CHAR(3) NOT NULL,
    E_PLAYER_NAME VARCHAR2(40),
    NICKNAME VARCHAR2(30),
    JOIN_YYYY CHAR(4),
    POSITION VARCHAR2(10),
    BACK_NO NUMBER(2),
    NATION VARCHAR2(20),
    BIRTH_DATE DATE,
    SOLAR CHAR(1),
    HEIGHT NUMBER(3),
    WEIGHT NUMBER(3),
    CONSTRAINT PLAYER_PK PRIMARY KEY (PLAYER_ID),
    CONSTRAINT PLAYER_FK FOREIGN KEY (TEAM_ID) REFERENCES TEAM (TEAM_ID)
);

[예시 - SQL Server]

CREATE TABLE TEAM (
    TEAM_ID CHAR(3) NOT NULL,
    REGION_NAME VARCHAR(8) NOT NULL,
    TEAM_NAME VARCHAR(40) NOT NULL,
    E_TEAM_NAME VARCHAR(50),
    ORIG_YYYY CHAR(4),
    STADIUM_ID CHAR(3) NOT NULL,
    ZIP_CODE1 CHAR(3),
    ZIP_CODE2 CHAR(3),
    ADDRESS VARCHAR(80),
    DDD VARCHAR(3),
    TEL VARCHAR(10),
    FAX VARCHAR(10),
    HOMEPAGE VARCHAR(50),
    OWNER VARCHAR(10),
    CONSTRAINT TEAM_PK PRIMARY KEY (TEAM_ID)
)

CREATE TABLE PLAYER (
    PLAYER_ID CHAR(7) NOT NULL,
    PLAYER_NAME VARCHAR(20) NOT NULL,
    TEAM_ID CHAR(3) NOT NULL,
    E_PLAYER_NAME VARCHAR(40),
    NICKNAME VARCHAR(30),
    JOIN_YYYY CHAR(4),
    POSITION VARCHAR(10),
    BACK_NO TINYINT,
    NATION VARCHAR(20),
    BIRTH_DATE DATE,
    SOLAR CHAR(1),
    HEIGHT SMALLINT,
    WEIGHT SMALLINT,
    CONSTRAINT PLAYER_PK PRIMARY KEY (PLAYER_ID),
    CONSTRAINT PLAYER_FK FOREIGN KEY (TEAM_ID) REFERENCES TEAM (TEAM_ID)
);

제약 조건

PRIMARY KEY (기본 키)	▶ 테이블에 저장된 행 데이터를 고유하게 식별하기 위한 기본키 정의 ▶ 하나의 테이블에 하나의 기본키 제약만 정의 가능 ▶ 기본키 제약을 정의하면 DBMS는 자동으로 UNIQUE 인덱스를 생성하며 기본키를 구성하는 칼럼에는 NULL 입력 불가 ▶ 기본키 제약 = 고유키 제약 + NOT NULL
UNIQUE (고유 키)	▶ 테이블에 저장된 행 데이터를 고유하게 식별하기 위한 고유키 정의 ▶ NULL은 고유키 제약의 대상이 아니므로 NULL 값을 가진 행이 여러 개 있더라도 고유키 제약 위반이 되지 않음
NOT NULL	▶ NULL 값 입력 금지 ▶ 디폴트 상태: 모든 칼럼에서 NULL 허용 ▶ NOT NULL을 CHECK의 일부분으로 이해할 수도 있음
CHECK	▶ 입력할 수 있는 값의 범위 등을 제한 ▶ CHECK 제약으로는 TRUE or FALSE로 평가할 수 있는 논리식 지정
FOREIGN KEY (외래 키)	▶ 관계형 데이터 베이스에서 테이블 간의 관계를 정의하기 위해 기본키를 다른 테이블의 외래키로 복사하는 경우 외래키가 생성됨 ▶ 외래키 지정시 참조 무결성 제약 옵션을 선택할 수 있음

생성된 테이블 구조 확인

Oracle: DESCRIBE 테이블명; / DESC 테이블명;

DESCRIBE PLAYER;

DESC PLAYER;

SQL Server: sp_help 'dbo. 테이블명';

sp_help 'dbo.PLAYER'

SELECT 문장으로 테이블 생성 사례 (CTAS - Create Table ~ As Select ~)

장점: 칼럼별로 데이터 유형을 다시 정의하지 않아도 됨
주의해야 할 점: 기존 테이블의 제약 조건 중 NOT NULL만 적용됨 / 기본키, 고유키, 외래키, CHECK 등의 다른 제약조건들은 없어짐

Oracle

CREATE TABLE TEAM_TMP AS SELECT * FROM TEAM;

SQL Server

SELECT * INTO TEAM_TMP FROM TEAM;

ALTER TABLE

칼럼을 추가/삭제 or 제약조건을 추가/삭제

ADD COLUMN

Oracle

ALTER TABLE 테이블명
	ADD (	추가할 칼럼명1 	데이터유형	[기본값]	[NOT NULL]
    [, 추가할 칼럼명2		데이터유형	[기본값]	[NOT NULL]
    , ...] );

SQL Server

ALTER TABLE 테이블명
	ADD 추가할 칼럼명1	데이터유형	[기본값]	[NOT NULL]
    [, 추가할 칼럼명2		데이터유형	[기본값]	[NOT NULL]
    , ...];

[예제]

ALTER TABLE PLAYER
	ADD (ADDRESS VARCHAR(80));

ALTER TABLE PLAYER
	ADD ADDRESS VARCHAR(80);

DROP COLUMN

Oracle

ALTER TABLE 테이블명
	DROP ( 삭제할 칼럼명1 [, 삭제할 칼럼명2, ...] );

SQL Server

ALTER TABLE 테이블명
	DROP COLUMN 삭제할 칼럼명1 [, 삭제할 칼럼명2, ...];

[예제]

ALTER TABLE TEAM
	DROP (ADDRESS);

ALTER TABLE TEAM
	DROP ADDRESS;

MODIFY COLUMN

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

ALTER TABLE 테이블명 ALTER COLUMN 칼럼명 데이터유형 [NOT NULL];

칼럼 변경 시 고려사항

해당 칼럼의 크기를 늘릴 수는 있지만 테이블의 데이터가 존재한다면 칼럼의 크기를 줄이는 데는 제약이 있음
해당 칼럼이 NULL 값만 가지고 있거나 테이블에 아무 행도 없으면 칼럼의 크기를 줄일 수 있음
해당 칼럼이 NULL 값만을 가지고 있으면 데이터 유형을 변경할 수 있음
해당 칼럼의 DEFAULT 값을 바꾸면 변경 작업 이후 발생하는 행 삽입에만 영향을 미침
해당 칼럼에 NULL 값이 없을 경우에만 NOT NULL 제약 조건을 추가할 수 있음

[예제]

Oracle

ALTER TABLE TEAM
MODIFY (ORIG_YYYY VARCHAR2(8) DEFAULT '20020129' NOT NULL);

SQL Server

ALTER TABLE TEAM ALTER COLUMN ORIG_YYYY VARCHAR(8) NOT NULL;

ALTER TABLE TEAM ADD CONSTRAINT DF_ORIG_YYYY DEFALUT '20020129' FOR ORIG_YYYY;

RENAME COLUMN

ADD/DROP COLUMN 기능처럼 ANSI/ISO에 명시되어 있는 기능은 아나지미나 RENAME COLUMN으로 칼럼명이 변경되면 해당 칼럼과 관계된 제약 조건에 대해서도 자동으로 변경되는 장점이 있음

Oracle

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

SQL Server

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

[예제]

Oracle

ALTER TABLE PLAYER RENAME COLUMN PLAYER_ID TO TEMP_ID;

SQL Server

sp_rename 'dbo.PLAYER.PLAYER_ID', 'TEMP_ID', 'COLUMN';

DROP CONSTRAINT

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

[예제]

ALTER TABLE PLAYER
DROP CONSTRAINT PLAYER_PK;

ADD CONSTRAINT

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

[예시]

ALTER TABLE PLAYER
	ADD CONSTRAINT PLAYER_FK FOREIGN KEY (TEAM_ID) REFERENCES TEAM(TEAM_ID);

RENAME TABLE

Oracle

RENAME 기존테이블명 TO 새로운테이블명

SQL Server

sp_name '기존 테이블명', '새로운 테이블명'

[예제]

Oracle

RENAME TEAM TO TEMP;

SQL Server

sp_rename 'dbo.TEAM', 'TEMP';

DROP TABLE

DROP TABLE 테이블명 [CASCADE CONSTRAINT]

CASCADE CONSTRAINT: 해당 테이블과 관계가 있었던 참조되는 제약조건에 대해서도 삭제함을 의미

[예제]

DROP TABLE PLAYER;

TRUNCATE TABLE

테이블 자체가 삭제되는 것이 아니고, 해당 테이블에 들어 있던 모든 행들이 제거되고 저장공간을 재사용 가능하도록 해제

→ 테이블 구조를 완전히 삭제하기 위해서는 DROP TABLE을 실행하면 됨

** 전체 데이터를 삭제하는 경우 DELETE 보다 해당 방법을 사용하는 것을 권고하지만 이후 정상적인 복구가 불가능함 **

TRUNCATE TABLE 테이블명

DCL (data control language)

유저를 생성하고 권한 제어 가능

Oracle의 사용자 아키텍처

Oracle에서 제공하는 유저들은 다음과 같이 3가지이다.

SCOTT	▶ Oracle 테스트용 샘플 계정 ▶ 기본 패스워드: TIGER
SYSTEM	▶ 백업, 복구 등 일부 관리 기능을 제외한 모든 시스템 권한을 부여받은 DBA 계정 ▶ Oracle 설치 시에 패스워드 설정
SYS	▶ 백업 복구 등 데이터 베이스 상의 모든 관리 기능을 수행 가능한 최상위 관리자 계정

Oracle은 유저를 통해 데이터 베이스에 접속하는 형태
= 아이디 + 비밀번호로 인스턴스에 접속하고 그에 해당하는 스키마에 오브젝트 생성 등의 권한 부여받음

SQL Server의 사용자 아키텍처

SQL Server는 인스턴스 접속을 위해 로그인을 생성
→ 인스턴스 내에 존재하는 다수의 데이터베이스에 연결해 작업하기 위해 유저와 로그인을 매핑
특정 유저는 특정 데이터 베이스 내의 특정 스키마에 대해서 권한을 부여받을 수도 있음

로그인의 2가지 방법

1. 윈도우 인증
: 마이크로소프트 윈도우 운영체제 인증 방식으로 윈도우에 로그인한 정보를 가지고 SQL Server에 접속하는 방식

윈도우 사용자 계정을 통해 연결되면 윈도우 보안 주체 토큰을 사용해 계정 이름과 암호가 유효한지 확인
윈도우에서 사용자 ID를 확인하는 것이며 SQL Server에서는 암호를 요청하지 않고 유효성 검사 또한 진행하지 않음
기본 인증 모드
SQL Server보다 훨씬 안전
(Kerberos 보안 프로토콜 사용, 암호 정책을 적용해 강력한 암호에 대해 적합한 복잡성 수준 유지하도록 함, 계정 잠금 및 암호 만료 지원)
트러스트 된 연결이라고도 부름 (SQL Server가 윈도우에서 제공하는 자격증명을 신뢰하므로)

2. 혼합 모드 (Windows 인증 모드 OR SQL 인증)

: 기본적으로 Windows 인증으로도 SQL Server에 접속 가능하며, Oracle의 인증과 같은 방식으로 사용자 아이디 + 비밀번호로 SQL Server에 접속하는 방식

SQL Server 인증 사용 시에는 강력한 암호(숫자 + 문자 + 특수문자 등) 사용해야 함
경로 예제: SCOTT LOGIN INST1 → SCOTT USER → PRODUCT SCHEMA
SCOTT라는 LOGIN 이름으로 인스턴스 INST1에 접속하여 미리 매핑되어 있는 SCOTT라는 유저를 통해 PRODUCT 스키마에 속한 ITEM이라는 테이블의 데이터 접근

유저와 권한

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

1. 유저 생성 권한 부여: CREATE USER

[Oracle]

STEP1. SCOTT 유저로 접속한 다음 SQLD 유저(패스워드 DB2019)를 생성해 본다

CONN SCOTT/TIGER

CREATE USER SQLD IDENTIFIED BY DB2019;

▶ 현재 SCOTT 유저는 유저를 생성할 권한을 부여받지 못했기 때문에 오류 발생

STEP2. SCOTT 유저에게 유저 생성 권한을 부여한 후 다시 SQLD 유저를 생성한다

GRANT CREATE USER TO SCOTT;

CONN SCOTT/TIGER

CREATE USER SQLD IDENTIFIED BY DB2019;

[SQL Server]

STEP1. 로그인 생성 - sa로 로그인한 다음 SQL 인증을 사용하는 SQLD라는 로그인(패스워드: DB2019)을 생성해 본다. 로그인 후 최초로 접속할 DB는 AdventureWorks 데이터 베이스로 설정한다.

** SQL Server는 유저 생성 전에 로그인을 생성해야 한다. 로그인을 생성할 수 있는 권한을 가진 로그인은 기본적으로 sa이다. **

CREATE LOGIN SQLD WITH PASSWORD = 'DB2019', DEFAULT_DATABASE = AdventureWorks;

STEP2. 생성하고자 하는 유저가 속할 데이터 베이스로 이동

SQL Server에서의 유저는 데이터 베이스마다 존재하므로 생성하고자 하는 유저가 속할 데이터 베이스로 이동한 후 처리해야 한다.

USE ADVENTUREWORKS;
GO
CREATE USR SQLD FOR LOGIN SQLD WITH DEFAULT_SCHEMA = dbo;

2. 로그인 위한 권한 부여: CREATE SESSION

SQLD 유저가 생성되었지만 아무런 권한도 부여받지 못하였기 때문에 로그인 시 CREATE SESSION 권한이 없다는 오류가 발생한다.

[Oracle]

STEP1. SYSTEM 유저로 접속하여 CREATE SESSION 권한 부여

CONN SYSTEM/MANAGER;

GRANT CREATE SESSION TO SQLD;

CONN SQLD/DB2019;

STEP2. SQLD 유저로 테이블을 생성

SELECT * FROM USER_TABLES;

CREATE TABLE MENU (MENU_SEQ INT NOT NULL, TITLE VARCHAR(10));

▶ SQLD 유저는 로그인 권한만 부여되었으므로 테이블을 생성하려면 테이블 생성 권한(CREATE TABLE)이 불충분하다는 오류가 발생

STEP3. SYSTEM 유저 통해 SQLD 유저에게 CREATE TABLE 권한을 부여한 후 다시 테이블 생성

CONN SYSTEM/MANAGER;

GRANT CREATE TABLE TO SQLD;

CONN SQLD/DB2019;

CREATE TABLE MENU (MENU_SEQ NUMBER NOT NULL, TITLE VARCHAR2(10));

[SQL Server]

STEP1. sa로 로그인해 SQLD 유저에게 CREATE TABLE 권한을 부여한 후 다시 테이블 생성

USER ADVENTUREWORKS;
GO
GRANT CREATE TABLE TO SQLD;

GRANT CONTROL ON SCHEMA::DBO TO SQLD;

CREATE TABLE MENU (MENU_SEQ INT NOT NULL, TITLE VARCHAR(10));

OBJECT에 대한 권한 부여

오브젝트 권한? 특정 오브젝트인 테이블, 뷰 등에 대한 SELECT, INSERT, DELETE, UPDATE 작업 명령어를 의미

[Oracle]

객체 권한	테이블	VIEWS	SEQUENCE	PROCEDURE
ALTER	O		O
DELETE	O	O
EXECUTE				O
INDEX	O
INSERT	O	O
REFERENCES	O
SELECT	O	O	O
UPDATE	O	O

[SQL Server]

객체 권한	테이블	VIEWS	SEQUENCE	PROCEDURE
ALTER	O		O
DELETE	O	O	O
EXECUTE				O
INDEX	O
INSERT	O	O
REFERENCES	O
SELECT	O	O	O
UPDATE	O	O

ROLE을 이용한 권한 부여

부여할 수 있는 권한은 약 100여 개로 매우 많다. 이 권한들을 일일이 부여하려면 엄청나게 힘이 들 것이다. 따라서 ROLE을 이용해 권한을 부여한다.

데이터 베이스 관리자는 ROLE을 생성하고, ROLE에 각종 권한들(시스템 상 + 오브젝트 권한 모두 가능)을 부여한다. 이후 ROLE을 다른 ROLE이나 유저에게 부여할 수 있다.

[Oracle 예제]

CONN SYSTEM/MANAGER;

CREATE ROLE LOGIN_TABLE;

GRANT CREATE SESSION, CREATE TABLE TO LOGIN_TABLE;

GRANT LOGIN_TABLE TO SQLD;

CREATE TABLE MENU (MENU_SEQ NUMBER NOT NULL, TITLE VARCHAR2(10));

** 번외: 권한 회수 - REVOKE **

[Oracle]

REVOKE CREATE TABLE, CREATE_SESSION FROM SQLD;

[SQL Server]

USE ADVENTUREWORKS;
GO
REVOKE CREATE TABLE FROM SQLD;

기본적으로 제공하는 ROLE

[Oracle] 보통 CONNECT ROLE과 RESOURCE ROLE을 자주 사용

CONNECT: CREATE SESSION 권한 포함 (로그인 권한)
RESOURCE: CREATE CLUSTER / CREATE INDEXTYPE / CREATE OPERATOR / CREATE PROCEDURE / CREATE SEQUENCE / CREATE TABLE / CREATE TRIGGER / CREATE TYPE 권한 포함 (오브젝트 생성 권한)

[SQL Server] 보통 ROLE을 생성해 사용하기보다는 기본적으로 제공되는 ROLE에 멤버로 참여하는 방식 이용

1. 서버 수준

서버 수준 역할명	설명
public	▶ 모든 SQL Server 로그인은 PUBLIC 서버 역할에 속함 ▶ 서버 보안 주체에게 보안 객체에 대한 특정 사용 권한이 부여되지 않았거나 거부된 경우 PUBLIC으로 부여된 사용 권한을 상속 받음 ▶ 모든 사용자가 개체를 사용할 수 있도록 하려는 경우에만 개체에 PUBLIC 권한을 할당해야 함
bulkadmin	▶ BULK INSERT문 수행 가능
dbcreator	▶ DB 생성, 변경, 삭제, 복원 가능
diskadmin	▶ 디스크 파일 관리
processadimin	▶ SQL Server의 인스턴스에서 실행중인 프로세스 종료 가능
securityadmin	▶ 로그인 및 해당 속성 관리 ▶ 서버 및 데이터 베이스 수준의 사용 권한을 부여(GRANT), 거부(DENY), 취소(REVOKE) 할 수 있음 ▶ 로그인 암호 다시 설정 가능
serveradmin	▶ 서버 차원의 구성 옵션을 변경하고 서버 종료 가능
setupadmin	▶ 연결된 서버를 추가하거나 제거 가능
sysadmin	▶ 서버에서 모든 작업 수행 가능 ▶ 기본적으로 Windows BULTIN \Administrators 그룹의 멤버인 로컬 관리자 그룹은 sysadmin 고정 서버 역할의 멤버임

2. DB 수준

DB 수준 역할명	설명
db_accessadmin	▶ windows 로그인, windows 그룹 및 sql server 로그인의 데이터베이스에 대한 액세스 추가, 제거 가능
db_backupoperator	▶ 데이터베이스 백업 가능
db_datereader	▶ 모든 사용자 테이블의 모든 데이터를 읽을 수 있음
db_datawriter	▶ 모든 사용자 테이블에서 데이터를 추가, 삭제, 변경 가능
db_ddladmin	▶ 데이터베이스에서 모든 ddl 명령 수행 가능
db_denydatareader	▶ 데이터베이스 내에 있는 모든 사용자 테이블의 데이터를 읽을 수 없음
db_denydatawriter	▶ 데이터베이스 내의 모든 사용자 테이블에 있는 데이터를 추가, 삭제, 변경 불가
db_owner	▶ 데이터베이스 내에 있는 모든 구성 및 유지관리 작업 수행 가능 ▶ 데이터베이스 삭제 가능
db_securityadmin	▶ 역할 멤버 자격을 수정하고 사용 권한 관리 가능 ▶ 이 역할에 보안 주체를 추가하면 원하지 않는 권한 상승이 설정될 수 있음

'자격증 공부 > SQLD 자격증' 카테고리의 다른 글

SQLD 합격 후기 / 공부 방법 (0)	2025.10.21
SQLD(SQLP) 과목1 - 2장. 데이터 모델의 이해 (1)	2025.03.07
SQLD(SQLP) 과목2 - 2장. SQL 활용 (1)	2025.03.03
SQLD(SQLP) 과목2 - 1장. SQL 기본 (1)	2025.02.28
SQLD(SQLP) 과목1 - 1장. 데이터 모델의 이해 (0)	2025.02.25

SQLD(SQLP) 과목2 - 3장. 관리 구문

DML (data manipulation language)

INSERT

1. 단일 행 INSERT 문

2. 서브 쿼리를 이용한 다중 행 INSERT 문

UPDATE

DELETE

MERGE

유의할 점

TCL (transaction control language)

트랜잭션의 대상이 되는 SQL 문

트랜잭션의 특성

COMMIT

COMMIT이나 ROLLBACK 이전의 상태

COMMIT 이후의 상태

SQL Server의 COMMIT

ROLLBACK

ROLLBACK 이후의 상태

SAVEPOINT

Oracle 문법

SQL Server 문법

COMMIT, ROLLBACK의 목적

DDL (data definition language)

CREATE TABLE

주의해야할 규칙

제약 조건

생성된 테이블 구조 확인

SELECT 문장으로 테이블 생성 사례 (CTAS - Create Table ~ As Select ~)

ALTER TABLE

ADD COLUMN

DROP COLUMN

MODIFY COLUMN

RENAME COLUMN

DROP CONSTRAINT

ADD CONSTRAINT

RENAME TABLE

DROP TABLE

TRUNCATE TABLE

DCL (data control language)

Oracle의 사용자 아키텍처

SQL Server의 사용자 아키텍처

로그인의 2가지 방법

유저와 권한

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

OBJECT에 대한 권한 부여

ROLE을 이용한 권한 부여

기본적으로 제공하는 ROLE

'자격증 공부 > SQLD 자격증' 카테고리의 다른 글

관련글

티스토리툴바