oracle, ms-sql DDL ,DML ,DCL ,TCL정리

1.DML(Data Manipulation Language)

-데이터 조작어로 테이블에 입력, 수정, 삭제,검색하는 언어이다.

 

-INSERT, UPDATE, DELETE, SELECT(DML이라고도 불리고, DQL이라고도 불린다.)

 

1.INSERT(데이터 삽입)

ex)

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

 

-칼럼명의 기술 순서는 테이블에 정의된 칼럼 순서와 동일할 필요는 없으며, INTO 절에 기술하지 않은 칼럼은 Defalut로 NULL값이 입력된다.

 

-해당 칼럼의 데이터 유형이 CHAR나 VARCHAR2등 문자 유형일 경우 ''(Single Quotation)과 함게 입력한다.

 

-숫자 유형일 경우엔 ''없이 숫자만 입력한다.

 

2.UPDATE(데이터 수정)

ex)

UPDATE 테이블명

SET 수정할 칼럼명1 = 수정될 새로운 값1

[, 수정할 칼럼명2 = 수정될 새로운 값2]

[, ... ]

[WHERE 수정 대상 식별 조건식];

 

-WHERE 절을 사용하지 않는다면 테이블의 전체 데이터가 수정된다.

 

-SET 절에 서브 쿼리를 사용할 수 있으며, 서브 쿼리의 결과로 값이 수정된다.

 

3.DELETE(데이터 삭제)

ex)

DELETE [FROM] 테이블명

[WHERE 삭제 대상 식별 조건식];

 

-WHERE절에 서브 쿼리를 사용하면, 다른 테이블을 참조해 삭제할 행을 식별 할 수 있다.

 

4.SELECT(데이터 검색)

ex)

5.SELECT [*ALL/*DISTINCT] 출력 대상 칼럼명, 출력 대상 칼럼명,...

1.FROM 출력 대상 칼럼들이 있는 테이블명

2.[WHERE 조건식]

3.[GROUP BY 칼럼이나 표현식]

4.[HAVING 그룹조건식]

6.[ORDER BY 칼럼이나 표현식 [ASC 또는 DESC, ASC이 default값]];

//각 SQL문 앞에 적힌 숫자는 SELECT문장의 수행 순서를 의미

 

1.발췌 대상 테이블을 참조한다(FROM)

2.발췌 대상 데이터가 아닌 것은 제거한다(WHERE)

3.행들을 소그룹화한다(GROUP BY)

4.그룹핑된 값의 조건에 맞는 것만을 출력한다(HAVING)

5.데이터 값을 출력.계산한다(SELECT)

6.데이터를 정렬한다(ORDER BY)

 

 

*ALL: Default 옵션이므로 별도로 표시하지 않아도 된다. 즉 중복된 데이터가 있어도 모두 출력한다.

*DISTINCT: 중복된 데이터가 있을 경우 1건으로 처리해 출력한다.

 

-GROUP BY 절에 의한 소그룹별로 만들어진 집계 데이터 중, HAVING 절에서 제한 조건을 두어 조건을 만족하는 내용만 출력한다.

 

-HAVING 절은 GROUP BY절의 기준 항목이나 소그룹의 집계함수를 이용한 조건을 표시 할 수 있다.

일반적으로 GROUP BY 절 뒤에 위치한다.

---

 

2.DDL(Data Definition Language)

1.CREATE TABLE(테이블 생성)

-테이블은 데이터베이스의 가장 기본적인 객체로, 행과 열의 구조로 데이터를 저장한다.

 

-테이블 생성을 위해서는 해당 테이블에 입력될 데이터를 정의하고, 정의한 데이터를 어떠한 데이터 유형으로 선언할 것인지를 결정해야 한다.

 

ORACLE

ex)

CREATE TABLE 테이블명(

컬럼명 데이터타입(데이터길이) NOT NULL,

컬럼명2 데이터타입(데이터길이),

...

CONSTRAINT 제약조건명 PRIMARY KEY (컬럼명)

);

 

SQL Server

ex)

CREATE TABLE 테이블명(

컬럼명 데이터타입(데이터길이) NOT NULL,

컬럼명2 데이터타입(데이터길이),

...

CONSTRAINT 제약조건명 PRIMARY KEY (컬럼명)

);

 

-테이블 생성 시 주의사항

1.대소문자 구분은 하지 않는다.

2.DATETIME 데이터 유형에는 별도로 크기를 지정하지 않는다.

3.문자 데이터 유형은 반드시 가질 수 있는 최대 길이를 표시해야 한다.

4.칼럼과 칼럼의 구분은 콤마로 하되, 마지막 칼럼은 콤마를 찍지 않는다.

5.칼럼에 대한 제약조건이 있으면 CONSTRAINT를 이용해 추가할 수 있다.

 

2.ALTER TABLE(테이블 변경)

-한 번 생성된 테이블은 특별히 사용자가 구조를 변경하기 전까지 생성 당시의 구조를 유지하게 된다.

 

-ADD COLUMN(컬럼 추가)

ORACLE

ex)

ALTER TABLE 테이블명

ADD(추가할 컬럼명1 데이터유형 [기본 값] [NOT NULL]);

 

SQL Server

ex)

ALTER TABLE 테이블명

ADD 추가할 칼럼명1 데이터유형 [기본 값] [NOT NULL]);

 

-DROP COLUMN(컬럼 삭제)

ORACLE

ex)

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

 

SQL Server

ex)

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

 

-MODIFY COLUMN(컬럼 변경)

ORACLE

ex)

ALTER TABLE 테이블명

MODIFY(칼럼명1 데이터유형 [기본 값] [NOT NULL]);

 

SQL Server

ex)

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

 

칼럼을 변경할 때 고려할 사항

1.해당 칼럼의 크기를 늘릴 수 있지만, 테이블에 데이터가 존재한다면 칼럼의 크기를 줄이는 데는 제약이 있다.(기존의 데이터가 훼손될 수 있기 때문이다.)

2.해당 칼럼이 NULL 값만 갖고 있거나 테이블에 아무 행도 없으면 칼럼의 크기를 줄일 수 있다.

3.해당 칼럼이 NULL 값만을 갖고 있으면 데이터 유형을 변경할 수 있다.

4.해당 칼럼의 DEFAULT 값을 바꾸면 변경 작업 이후 발생하는 행 삽입에만 영향을 미치게 된다.

5.해당 칼럼에 NULL값이 없을 경우에만 NOT NULL 제약조건을 추가할 수 있다.

 

-RENAME COLUMN(컬럼 이름 변경)

ORACLE

ex)

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

 

SQL Server

ex)

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

 

-DROP CONSTRAINT(제약조건 삭제)

ORACLE

ex)

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

 

SQL Server

ex)

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

 

-ADD CONSTRAINT(제약조건 추가)

ORACLE

ex)

ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건명 FOREIGN KEY (컬럼명) REFERENCES 참조테이블명(참조테이블의 컬럼명)

 

SQL Server

ex)

ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건명 FOREIGN KEY (컬럼명) REFERENCES 참조테이블명(참조테이블의 컬럼명)

 

3.RENAME TABLE(테이블 이름 재정의)

ORACLE

ex)

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

 

SQL Server

ex)

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

 

4.DROP TABLE

ex)

DROP TABLE 테이블명 [CASCADE CONSTRAINT];

 

-CASCADE CONSTRAINT 옵션은 해당 테이블과 관계가 있었던 참조되는 제약조건에 대해서도 삭제함을 의미한다.

 

5.TRUNCATE TABLE

ex)

TRUNCATE TABLE 테이블명;

 

-테이블 자체가 삭제되는 것이 아니라, 해당 테이블에 들어 있떤 모든 행들이(인스턴스들이) 제거되고 저장 공간을 재사용 가능하도록 해제한다.

(DROP TABLE이 테이블 구조까지 삭제시킴)

---

 

3.DCL(Data Control Language)

1.시스템 권한 부여

ex)

GRANT 시스템 권한 TO 유저명;

 

2.Role을 이용한 권한 부여

-어떠한 역할군에 포함되어있는 권한들이 필요한 유저들에게 역할군을 지정해줌으로써 권한을 쉽게부여

 

1.CREATE ROLE 롤 이름;

 

2.GRANT 권한명,권한명,... TO 롤 이름;

 

3.GRANT 롤 이름 TO 유저명;

---

4.TCL(Transaction Control Language)

1.트랜잭션 개요

-트랜잭션은 데이터베이스의 논리적 연산단위다.

 

-밀접히 관련돼 분리될 수 없는 한 개 이상의 데이터베이스 조작을 가리킨다.

 

-하나의 트랜잭션에는 하나 이상의 SQL 문장이 포함된다.

 

-분할할 수 없는 최소한의 단위이므로 전부 적용하거나(ALL) 전부 취소한다(OR NOTHING).

 

-트랜잭션의 특성

1.원자성(atomicity):

트랜잭션에서 정의된 연산들은 모두 성공적으로 실행되던지 아니면 전혀 실행되지 않은 상태로 남아 있어야 한다(ALL OR NOTHING)

 

2.일관성(consistency):

트랜잭션이 실행되기 전의 데이터베이스 내용이 잘못 돼 있지 않다면 트랜잭션이 실행된 이후에도 데이터베이스의 내용에 잘못이 있으면 안 된다.

 

3.고립성(isolation):

트랜잭션이 실행되는 도중에 다른 트랜잭션의 영향을 받아 잘못된 결과를 만들어서는 안 된다.

 

4.지속성(durability):

트랜잭션이 성공적으로 수행되면, 그 트랜잭션이 갱신한 데이터베이스의 내용은 영구적으로 저장된다.

 

2.COMMIT

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

 

-COMMIT이나 ROLLBACK 이전의 데이터 상태

1.데이터의 변경을 취소해 이전 상태로 복구 가능하다.

2.현재 사용자는 SELECT 문장으로 결과를 확인 가능하다.

3.다른 사용자는 현재 사용자가 수행한 명령의 결과를 볼 수 없다.

4.변경된 행은 잠금(LOCKING)이 설정돼서 다른 사용자가 변경할 수 없다.

 

-COMMIT이후의 데이터 상태

1.데이터에 대한 변경 사항이 데이터베이스에 반영된다.

2.이전 데이터는 영원히 잃어버리게 된다.

3.모든 사용자는 결과를 볼 수 있다.

4.고나련된 행에 대한 잠금(LOCKING)이 풀리고, 다른 사용자들이 행을 조작할 수 있게 된다.

 

-SQL Server(MS-SQL_의 COMMIT)

oracle은 DML을 실행하는 경우 DBMS가 트랜잭션을 내부적으로 실행하며, DML문장 수행 후 사용자가 임의로 COMMIT 혹은 ROLLBACK을 수행해 주어야 트랜잭션이 종료된다.

 

하지만 SQL Server는 기본적으로 AUTO COMMIT 모드이기 때문에 DML 수행 후 사용자가 COMMIT이나 ROLLBACK을 처리할 필요가 없다.

 

oracle

ex)

DELETE FROM 테이블명;

COMMIT;

 

SQL Server

ex)

DELETE FROM 테이블명;

 

1)AUTO COMMIT

-SQL Server의 기본 방식이며, DML꽈 DDL을 수행할 때마다 DBMS가 트랜잭션을 컨트롤하는 방식이다.

 

-명령어가 성공적으로 수행되면 자동으로 COMMIT을 수행하고, 오류가 발생하면 자동으로 ROLLBACK을 수행한다.

 

2)암시적 트랜잭션

-Oracle과 같은 방식으로 처리된다.

 

-트랜잭션의 시작은 DBMS가 처리하고 트랜잭션의 끝은 사용자가 명시적으로 COMMIT 또는 ROLLBACK으로 처리한다.

 

-인스턴스 단위 또는 세션 단위로 설정할 수 있다. 인스턴스 단위로 설정하려면 서버 속성 창의 연결화면에서 기본연결 옵션 중 암시적 트랜잭션에 체크해 주면 된다.

 

-세션 단위로 설정하기 위해서는 세션 옵션 중 SET IMPLICIT TRANSACTION ON을 사용하면 된다.

 

3)명시적 트랜잭션(SQL Server에서 사용가능)

0트랜잭션의 시작과 끝을 모두 사용자가 명시적으로 지정하는 방식이다.

 

-BEGIN TRANSACTION(BEGIN TRAN 구문도 가능)으로 트랜잭션을 시작, COMMIT TRANSACTION(COMMIT 구문도 가능) 또는 ROLLBACK TRANSACTION(ROLLBACK 구문도 가능)으로 트랜잭션을 종료한다.

 

3.ROLLBACK

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

 

-롤백은 데이터 변경 사항이 취소돼 데이터가 이전상태로 복구되며, 관련된 행에 대한 잠금(LOCKING)이 풀리고 다른 사용자들이 데이터 변경을 할 수 있게 된다.

 

-ROLLBACK 후의 데이터 상태

1.데이터에 대한 변경 사항은 취소된다.

2.데이터가 트랜잭션 시작 이전의 상태로 되돌려진다.

3.관련된 행에 대한 잠금(LOCKING)이 풀리고, 다른 사용자들이 행을 조작할 수 있게 된다.

 

-COMMIT과 ROLLBACK을 사용함으로써의 효과

1.데이터 무결성 보장

2.영구적인 변경을 하기 전에 데이터의 변경 사항 확인 가능

3.논리적으로 연관된 작업을 그룹핑해 처리 가능

 

4.SAVEPOINT

-저장점을 정의하면 롤백할 때 트랜잭션에 포함된 전체 작업을 롤백하는 것이 아니라, 현 시점에서 SAVEPOINT까지 트랜잭션의 일부만 롤백할 수 있다.

 

-복잡한 대규모 트랜잭션에서 에러가 발생했을 때 SAVEPOINT까지의 트랜잭션만 롤백하고 실패한 부분에 대해서만 다시 실행할 수 있다

 

ORACLE

ex)

SAVEPOINT SVPT1;

 

SQL Server

ex)

SAVE TRANSACTION SVTR1;