[SQL] SQL 종류 - DML / TCL / DCL / TRANSACTION

본 강의를 수강 후 정리한 글입니다 .

https://www.youtube.com/playlist?list=PLlCujDgOz8x6nDWtRr5AlHWB9Xp-Lxf-z 

01. SQL 기본

---

DML

• DML (Data Manipulation Language) : 데이터 조작어
• 테이블에 있는 행과 열을 조작하는 언어
• 정의된 데이터베이스에 입력된 레코드를 조회하거나 수정하거나 삭제하는 등의 역할
• 데이터베이스 사용자가 질의어를 통하여 저장된 데이터를 실질적으로 처리하는 데 사용하는 언어
•  COMMIT 하거나 ROLLBACK 가능

1. SELECT

• 데이터 조회 / 데이터베이스에서 데이터를 검색할 때 사용
• 아래와 같은 순서대로 기술되어야 함

SELECT [DISTINCT] { * | column(s) | expr(s) [alias] } -> 테이블 컬럼의 순서와는 무관
	FROM table_name(s) //여기까지만 기술되어도 됨.
[WHERE conditions]
[GROUP BY columns]
[HAVING group_conditions]
[ORDER BY columns [ASC┃DESC]] ;

SELECT구문 - 산술 표현식

• 산술 연산자 종류 : + - * /
• 우선 순위 : ( * , / )이 ( +, - ) 보다 우선 순위가 높다.
•  우선 순위가 같을 때 : 왼쪽에서 오른쪽으로 적용
•  우선순위 배분 : 괄호를 사용
•  사용할 수 없는 곳 : FROM 절

SELECT구문 - NULL 값

• 널 null 은 알 수 없는 값
• 0 도 공백도 아님.
•  널 을 포함한 사칙연산의 결과는 널
•  숫자를 0으로 나누면 에러가 발생되지만 널로 나누면 널.

SELECT구문 - 별칭 ( Alias )

• 컬럼에 대한 별칭

SELECT
	{column1 | expression} [AS](생략가능) descriptive_name
	FROM table_name ;

• 별칭에 큰 따옴표

SELECT
	{column1 | expression} [AS] "descriptive name" <- 대소문자, 특수문자 , 공백
FROM table_name ;

• 테이블에 대한 별칭

SELECT
	{column1 | expression} [AS] "descriptive name"
FROM table_name table_alias; < - AS 없이 바로 별칭 적어줌

SELECT구문 - Literal

• Literal은 SELECT 목록에 포함된 문자, 숫자, 날짜 등과 같은 상수
• Literal은 SELECT 목록의 칼럼과 동일하게 취급
• Literal 중에서 숫자를 제외한 문자와 날짜는 반드시 작은따옴표로 묶어야 한다.
• 각 Literal은 추출되는 행 row 만큼 출력된다.

SELECT구문 - 연결 연산자

• 2 개의 수직 바 (||)로 표시
• 연산자의 좌우에 있는 칼럼이 결합되어 하나의 칼럼으로 표시됨.
• 칼럼을 다른 칼럼, 산술 계산식 또는 상수 값에 연결하여 문자식을 생성
• 문자열에 널 값을 결합할 경우 결과는 문자열

SELECT구문 - DISTINCT

• SELECT 키워드 바로 다음에 기술
• DISTINCT 사용 시 하나의 칼럼을 지정하면 중복되는 값은 하나만 출력
• DISTINCT 다음에 여러 개의 칼럼을 지정한 경우 지정된 모든 칼럼에 영향

SELECT구문 - Pseudo칼럼

• 가상 칼럼, 실제 테이블에는 존재하지 않으나 쿼리에서 문제가 되지 않음. 
• 쿼리를 통해 읽어들인 값을 통해서 순번값을 나타냄
• ORACLE의 ROWNUM은 칼럼과 비슷한 성격의  Pseudo 칼럼으로 SQL 처리 결과 집합의 각 행에 대해
  임시로 부여되는 일련번호이며, 테이블이나 결과 집합에서 원하는 만큼의 행만 가져오고 싶을 때,
  WHERE 절에서 찾는 행의 개수를 제한하는 용도로 사용한다. 
• ROWNUM은 각 검색된 행의 일련번호로, ORDER BY에 의한 정렬 이전에 부여된다.
• ROWID는 테이블 내의 특정한 행을 유일하게 구별해주는 값이며, 데이터 타입은 ROWID이다.

2. INSERT

• 데이터 삽입 / 테이블에서 행을 입력

INSERT INTO table
		(col_1, col_2, ... ) // 전체 칼럼일 경우 생략 가능
VALUES (val_1, val_2, ... ) ;

👉 col 의 갯수 = val 의 갯수 

👉 입력안한 컬럼은 null 값이 들어감.

 

INSERT ~ SELECT

INSERT INTO table (column1, column2, ... )
SELECT expression1, expression2, ...
 FROM source_table
[ WHERE conditions ];

👉대량의 데이터를 넣을 때 주로 사용됨.

👉모든 테이블은 기본적으로 로그가 관리됨.

👉시간적으로 문제가 생김. =>로깅을 적게 발생시키게 하면 시간적으로 아낄 수 있음.

ALTER TABLE new_emp NOLOGGING;

👉작업이 끝난 후 ALTER TABLE new_emp LOGGING;

 

3. UPDATE

• 테이블에 저장된 데이터를 수정 / 행을 변경

UPDATE table
	SET
	column_name1 = expr1 ,
	column_name2 = expr2 ,
	...
 [ WHERE conditions ];

👉WHERE 절이 없으면 전체 테이블 데이터를 업데이트 하겠다는 뜻!

 

SELECT문을 이용한 UPDATE

UPDATE table1
	SET
	column1 = (SELECT expression1
			FROM table2
			WHERE conditions) => 한 건 의 데이터만 조회 / 두건 이상인 경우 에러
[ WHERE conditions ];

 

4. DELETE

• 데이터 삭제 /  행을 삭제

DELETE [ FROM ](생략가능) table
[ WHERE conditions ];

 

5. MERGE

• 존재하지 않으면 INSERT, 존재하면 UPDATE를 수행한다.
• UPDATE + INSERT 하나의 SQL 문으로 INSERT, UPDATE 또는 이들 두 작업을 한 번에 실행
• 하나의 목표 테이블에 입력 및 수정이 가해짐.
• 데이터 존재 여부는 조인을 통해 확인

MERGE INTO table_name table_alias
			  USING (table | view | sub-query) alias
		ON (join condition) <- 데이터가 있는지 없는지 확인 
	WHEN MATCHED THEN
		UPDATE SET col1 = col_val1,
						col2 = col_val2
	WHEN NOT MATCHED THEN
			INSERT (column_list)
			VALUES (column_values) ;

 

6. MULTI-TABLE INSERT

• 다중테이블 입력 / 하나의 INSERT 문으로 하나의 테이블 또는 여러 개의 테이블에 데이터를 입력

무조건부 INSERT ALL

INSERT ALL
	INTO mytab (col_1, col_2, ...,col_n )
		VALUES (expr1, expr2, ...,exprn )
	INTO mytab (col_1, col_2, ...,col_n )
		VALUES (expr1, expr2, ...,exprn )
	INTO mytab (col_1, col_2, ...,col_n )
		VALUES (expr1, expr2, ...,exprn )
SELECT * FROM dual;

조건 부 INSERT ALL

INSERT ALL
	WHEN deptno IN (SELECT deptno FROM dept) THEN
		INTO regular_emp VALUES (empno, ename, deptno)
	WHEN job IN ('CLERK', 'SALESMAN') THEN
		INTO sale_emp VALUES (empno, ename, job)
SELECT empno, ename, job, deptno
	FROM emp;

조건부 INSERT FIRST

INSERT FIRST
	WHEN sal > 10000 THEN
		INTO special_empno VALUES (empno, sal)
	WHEN sal > 5000 THEN
		INTO high_empno VALUES (empno, sal)
	WHEN sal > 3000 THEN
		INTO normal_empno VALUES (empno,sal)
	ELSE
		INTO low_empno VALUES (empno, sal)
SELECT empno, sal
FROM emp;

👉최초로 만족한 조건에 들어감

 

---

TCL 

• DML문에 의한 데이터 변경과 같은 작업은 트랜잭션으로 볼 수 있는데, 이러한 작업을 완료 또는 취소하기 위한 명령

COMMIT

• 트랜잭션의 작업이 정상적으로 완료되었음을 관리자에게 알려줌.

ROLLBACK

• 트랜잭션의 작업이 비정상적으로 종료 되었을 때 원래의 상태로 복구

SAVEPOINT

---

• 명시적 트랜잭션 제어
  • COMMIT, SAVEPOINT name, ROLLBACK
•  암시적 트랜잭션 제어
  • 자동 COMMIT/ROLLBACK
• => 명시적으로 COMMIT과 ROLLBACK이 되도록 하는 게 좋음

---

DCL

• (Data Control Language) : 데이터 제어어
• 데이터베이스 및 해당 구조에 대한 액세스 권한을 부여하거나 제거한다. 
• 데이터베이스에 접근하거나 객체에 권한을 주는 등의 역할. 데이터를 제어하는 언어
• 데이터의 보안, 무결성, 회복 등을 정의하는데 사용

GRANT

• 특정 데이터베이스 사용자에게 특정 작업에 대한 수행권한 부여

REVOKE

• 특정 데이터베이스 사용자에게 특정 작업에 대한 권한을 박탈, 회수

---

 TRANSACTION

• 구성
  • DML (INSERT, UPDATE, DELETE)의 집합
  •  DDL 이나 DCL은 한 문장이 트랜잭션으로 처리됨.
•  트랜잭션 정의
  •  시작
    •  명시적인 트랜잭션 시작 명령 없음 ( 타 DBMS와의 차이점 )
    • 첫 DML이 시작되면 트랜잭션도 시작