SQLD 자격증 공부 SQL 기본-DML, TCL (SQL 전문가 가이드)

*본 게시물은 2013년도 SQL 전문가 가이드 교재(일명 '노랭이')를 참고하여 공부하고 정리한 게시물입니다

 

2과목 SQL 기본 및 활용: 제1장 SQL 기본

1. DML(Data Manipulation Language)

테이블에 데이터 입력/수정/삭제/조회

1) INSERT

테이블에 데이터를 입력(한 번에 한 건만)

*사용 유형

INSERT INTO 테이블명 (COLUMN_LIST)
VALUES (COLUMN_LIST에 넣을 VALUE_LIST);

INSERT INTO 테이블명
VALUES (전체 COLUMN_LIST에 넣을 VALUE_LIST)

2) UPDATE

테이블의 데이터 수정

UPDATE 테이블명
SET    수정되어야 할 칼럼명 = 수정되기를 원하는 새로운 값;

3) DELETE

테이블의 정보가 필요 없게 되었을 경우 데이터 삭제

DELETE [FROM] 삭제를 원하는 정보가 들어있는 테이블명;

4) SELECT

사용자가 입력한 데이터를 테이블로 조회

SELECT [ALL/DISTINCT] 보고 싶은 칼럼명, 보고 싶은 칼럼명, ...
FROM   해당 칼럼들이 있는 테이블명;

- ALL : DEFAULT 옵션이며, *을 대신 입력해도 됨(중복된 데이터까지 전부 출력)
- DISTINCT : 중복된 데이터가 있으면 1건으로 처리해서 출력

 

*ALIAS/ALIASES 부여

- 조회된 결과에 일종의 별명을 부여해서 칼럼 레이블 변경(AS는 생략해도 됨)

SELECT PLAYER_NAME AS 선수명, POSTION AS 포지션, HEIGHT AS 키, WEIGHT AS 몸무게
FROM PLAYER;

5) 산술 연산자와 합성 연산자

(1) 산술 연산자

• NUMBER, DATE 자료형에 적용
• 수학의 사칙연산과 동일하게 ( ), *, /, +, -의 우선순위를 가짐

산술 연산자	설명
( )	연산자 우선순위 변경을 위한 괄호(괄호 안의 연산이 우선)
*	곱하기
/	나누기
+	더하기
-	빼기

(2) 합성(CONCATENATION) 연산자

• 문자-문자 연결 시
  :2개의 수직 바(| |)에 의해 이루어짐(Oracle), +표시에 의해 이루어짐(SQL Server)
• CONCAT(string1, string2) 함수 사용 가능
• 칼럼과 문자, 또는 칼럼과 다른 칼럼을 연결
• 문자 표현식의 결과에 의해 새로운 칼럼 생성

[Oracle]
SELECT PLAYER_NAME || '선수,' || HEIGHT || 'cm,' || WEIGHT || 'kg' 체격정보
FROM PLAYER;

[SQL Server]
SELECT PLAYER_NAME + '선수, ' + HEIGHT + 'cm, ' + WEIGHT + 'kg' 체격정보

2. TCL(Transaction Control Language)

1) 트랜잭션 개요

데이터베이스의 논리적 연산단위로, 밀접히 관련되어 분리할 수 없는 한 개 이상의 데이터베이스 조작
-> 분할할 수 없는 최소 단위로, 연산을 전부 적용하거나 전부 취소함(ALL OR NOTHING)

특성	설명
원자성(atomicity)	트랜잭션에서 정의된 연산들은 모두 성공적으로 실행되거나, 전혀 실행되지 않아야 함
일관성(consistency)	트랜잭션이 실행되기 전의 DB 내용이 잘못되어 있지 않다면, 트랜잭션 실행 이후에도 동일해야 함
고립성(isolation)	트랜잭션이 실행되는 도중에 다른 트랜잭션의 영향을 받아 잘못된 결과를 만들어서는 안 됨
지속성(durability)	트랜잭션이 성공적으로 수행되면 그 트랜잭션이 갱신한 DB의 내용은 영구적으로 저장됨

2) COMMIT 

입력하거나 수정한 자료 또는 삭제한 자료에 전혀 문제가 없다고 판단되었을 경우, COMMIT 명령어로 트랜잭션 완료

[Oracle]
UPDATE PLAYER
SET HEIGHT = 100;
-- 480개의 행이 수정됨

COMMIT;
-- 커밋 완료

[SQL Server]
-- 기본: AUTO COMMIT

(1) COMMIT 이전의 데이터 상태

• 메모리 BUFFER만 영향을 받았기 때문에, 데이터의 변경 이전 상태로 복구 가능
• 현재 사용자는 SELECT 문장으로 결과 확인 가능, 다른 사용자는 현재 사용자가 수행한 명령의 결과를 볼 수 없음
• 변경된 행은 잠금(LOCKING)이 설정되어 다른 사용자가 변경 불가

(2) COMMIT 이후의 데이터 상태

• 데이터의 변경 사항이 데이터베이스에도 반영됨
• 이전 데이터는 영원히 잃어버리게 됨
• 모든 사용자는 결과 확인 가능
• 관련된 행에 대한 잠금(LOCKING)이 풀리고, 다른 사용자가 행 조작 가능

*SQL Server의 COMMIT 방식 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(TRANSACTION 생략 가능) 또는 ROLLBACK TRANSACTION(TRANSACTION 생략 가능)으로 트랜잭션 종료
   -> ROLLBACK 구문을 만나면 최초 BEGIN TRANSACTION 시점까지 모두 ROLLBACK 수행

3) ROLLBACK

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

[Oracle]
UPDATE PLAYER
SET HEIGHT = 100;
-- 480개의 행이 수정됨

ROLLBACK;
-- 롤백 완료

[SQL Server]
-- 기본: AUTO COMMIT

(1) ROLLBACK 이전의 데이터 상태

COMMIT 이전의 데이터 상태와 같음

(2) ROLLBACK 이후의 데이터 상태

• 데이터 관련 변경 사항 취소(이전 데이터가 다시 저장됨)
• 관련된 행에 대한 잠금(LOCKING)이 풀리고, 다른 사용자들이 행 조작 가능 

*SQL Server의 ROLLBACK

AUTO COMMIT이 기본 방식이므로, 임의적으로 ROLLBACK을 선언하려면 명시적으로 트랜잭션 선언 필요

BEGIN TRAN
INSERT INTO PLAYER
(PLAYER_ID, TEAM_ID, PLAYER_NAME, POSITION, HEIGHT, WEIGHT, BACK_NO)
VALUES('1999035', 'K02', '이운재', 'GK', 182, 82, 1);
--1개 행이 생성됨

ROLLBACK;
--롤백으로 인해 생성된 행이 삭제됨

COMMIT, ROLLBACK의 공통 장점

• 데이터 무결성 보장
• 영구적인 변경을 하기 전에 데이터 변경 사항 확인 가능
• 논리적으로 연관된 작업을 그룹핑하여 처리 가능

4) SAVEPOINT

• 저장점을 정의하여 저장점까지의 트랜잭션 일부만 ROLLBACK 할 수도 있게 함
  -> 복잡한 대규모 트랜잭션에서 에러 발생 시, 일부만 ROLLBACK하고 실패한 부분만 다시 실행 가능
      (일부 툴에서는 지원되지 않을 수 있음) 
• 여러 개의 저장점을 정의할 수 있는데, 동일한 이름으로 설정하면 나중에 정의한 저장점이 유효함

[Oracle]
SAVEPOINT 저장점 이름;    --저장점 정의
ROLLBACK TO 저장점 이름;  --저장점까지 ROLLBACK

[SQL Server]
SAVE TRANSACTION 저장점 이름;     --저장점 정의
ROLLBACK TRANSACTION 저장점 이름; --저장점까지 ROLLBACK