## 공부정리
과목2 1장 SQL 기본 2편
4) TCL (COMMIT, ROLLBACK)
5) WHERE 절
6) 함수
#4 TCL (COMMIT, ROLLBACK)
1 TCL
- 데이터 무결성 보장을 목적으로 함
- 논리적 작업단위를 묶어 DML에 조작된 결과를 작업단위 별로 제어
- 일부에서는 DCL로 분류하기도 한다
2 트랜잭션
- 데이터베이스의 논리적인 연산 단위
- 트랜잭션에는 하나 이상의 SQL 문장이 포함된다
- 트랜잭션은 밀접히 관련되어 분리될 수 없는 한 개 이상의 DB조작을 가리킴
- 분할 할 수 없는 최소단위, 전부 적용하거나 전부 취소해야한다
3 트랜잭션의 특징
| 특성 | 설명 |
| 원자성 (atomicity) |
트랜잭션에서 정의된 연산들은 모두 성공적으로 실행되던지 아니면 전혀 실행되지 않은 상태로 남아 있어야 한다.(all or nothing) |
| 일관성 (consistency) |
트랜잭션이 실행되기 전의 데이터베이스 내용이 잘못 되어 있지 않다면 트랜잭션이 실행된 이후에도 데이터베이스의 내용에 잘못이 있으면 안된다. |
| 고립성 (isolation) |
트랜잭션이 실행되는 도중에 다른 트랜잭션의 영향을 받아 잘못된 결과를 만들어서는 안된다. |
| 지속성 (durability) |
트랜잭션이 성공적으로 수행되면 그 트랜잭션이 갱신한 데이터베이스의 내용은 영구적으로 저장된다. |
4 트랜잭션을 컨트롤하는 TCL(Transaction Control Language)
커밋 (COMMIT) : 올바르게 반영된 데이터를 데이터베이스에 반영시키는 것
롤백 (ROLLBACK) : 트랜잭션 시작 이전의 상태로 되돌리는 것
저장점(SAVEPOINT) : 저장점 기능
잠금 (LOCKING) : 다른 트랜잭션이 동시에 접근하지 못하도록 제한
* 트랜잭션 대상이 되는 SQL
- UPDATE, INSERT, DELETE 등 데이터를 수정하는 DML 문
- SELECT FOR UPDATE 등 배타적 LOCK을 요구하는 SELECT 문
5 COMMIT - 입력/수정/삭제한 자료가 문제가 없을 경우 변경 사항 적용
Oracle >>
UPDATE PLAYER SET HEIGHT=100;
COMMIT;
SQL>>
UPDATE PLAYER SET HEIGHT=100;
① COMMIT 이전 상태
- 단지 Memory Buffer에만 영향을 주고, 이전 상태로 복구 가능
- 현재 사용자는 SELECT 문으로 변경 결과를 확인 가능
- 다른 사용자는 현재 사용자가 수행한 결과의 확인 불가능
- 변경된 행은 아직 잠금(Locking) 설정되어 다른 사용자가 변경 불가능
② COMMIT 이후 상태
- 데이터에 대한 변경사항을 데이터베이스에 영구반영
- 이전 데이터는 영원히 잃어버림
- 모든 사용자가 결과 조회 가능
- 변경된 행은 잠금(Locking)이 해제되어 다른 사용자가 변경 가능
* Auto COMMIT
[Oracle] 임의의 COMMIT 혹은 ROLLBACK을 수행해 주어야 트랜잭션이 종료
[SQL] 기본적으로 AUTO COMMIT 모드, DML 구문이 성공이면 자동으로 COMMIT이 되고 오류가 발생할 경우 자동으로 ROLLBACK 처리
6 ROLLBACK - COMMIT 이전으로 되돌림
Oracle >>
UPDATE PLAYER SET HEIGHT=100;
ROLLBACK;
SQL>>
BEGIN TRAN UPDATE PLAYER SET HEIGHT=100;
ROLLBACK;
- 테이블에 입력/수정/삭제한 데이터에 대해 COMMIT 이전에 변경사항을 취소
- 이전 데이터가 다시 재저장됨
- 관련 행에 대한 잠금이 풀리고, 다른 사용자들이 데이터 변경 가능
7 COMMIT과 ROLLBACK을 사용함으로써 얻을 수 있는 효과
- 데이터 무결성 보장
- 영구적인 변경을 하기 전에 데이터의 변경사항을 확인 가능
- 논리적으로 연관된 작업을 그룹핑하여 처리 가능
8 SAVEPOINT - 저장점, 데이터 변경을 사전에 지정한 저장점까지만 롤백
[Oracle] SAVEPOINT 포인트이름;
-> ROLLBACK TO 포인트이름'
[SQL] SAVE TRANSATION 포인트이름;
->ROLLBACK TRANSACTION 포인트이름;
- 저장점을 정의하면 롤백을 할 경우 전체 롤백이 아닌 저장점까지의 일부만 롤백
- SAVEPOINT는 여러 개 지정할 수 있음
- 동일 이름으로 저장점 지정 시 가장 나중에 정의한 저장점이 유효
- point1으로 되돌리고 나면 그보다 미래인 point2로는 되돌릴 수 없다
- 저장점 없이 롤백하면 모든 변경사항을 취소
#5 WHERE 절
1 WHERE
- 자신이 원하는 자료만을 검색하기 위해 이용
- WHERE 절에 조건이 없는 FTS(Full Table Scan) 문장은 SQL 튜닝 1차 검토 대상 (FTS가 무조건 나쁜건 아님 -> 병렬 처리를 이용해 유용하게 사용가능)
SELECT[DISTINCT/ALL] 칼럼명 [ALIAS명]FROM 테이블명 WHERE 조건식;
SELECT PLAYER_NAME FROM PLAYER WHERE TEAM_ID="K02";
SELECT PLAYER_NAME, POSITION, BACK_NO, HEIGHT FROM PLAYER WHERE HEIGHT >=170;
2 WHERE 연산자의 종류
- 처리 순서 : 부정 연산자 -> 비교 연산자 -> 논리 연산자
| 연상 우선순위 | 설명 |
| 1 | 괄호 () |
| 2 | NOT 연산자 |
| 3 | 비교 연산자, SQL 비교 연산자 |
| 4 | AND |
| 5 | OR |
* 문자열 비교 방법
| 구분 | 비교 방법 |
| 비교 연산자의 양쪽이 모두 CHAR 유형 타입인 경우 | 길이가 서로 다른 CHAR형 타입이면 작은 쪽에 SPACE를 추가하여 길이를 같에 한 후에 비교한다. |
| 서로 다른 문자가 나올 때까지 비교한다. | |
| 달라진 첫 번째 문자의 값에 따라 크기를 결정한다. | |
| BLANK의 수만 다르다면 서로 같은 값으로 결정한다. | |
| 비교 연산자의 어느 한 쪽이 VARCHAR 유형 타입인 경우 | 서로 다른 문자가 나올 때까지 비교한다. |
| 길이가 다르다면 짧은 것이 끝날 때까지만 비교한 후에 길이가 긴 것이 크다고 판단한다. | |
| 길이가 같고 다른 것이 없다면 같다고 판단한다. | |
| VARCHAR는 NOT NULL까지 길이를 말한다. | |
| 상수값과 비교할 경우 | 상수 쪽을 변수 타입과 동일하게 바꾸고 비교한다. |
| 변수 쪽이 CHAR 유형 타입이면 위의 CHAR 유형 타입의 경우를 적용한다. | |
| 변수 쪽이 VARCHAR 유형 타입이면 위의 VARCHAR 유형 타입의 경우를 적용한다. |
3 IS NULL / IS NOT NULL
- NULL(ASCll 00) : 값이 존재하지 않는 것으로 확정되지 않은 값을 표현할 때 사용
- 어떤 값보다 크거나 작지도 않고 ' '(공백)이나 0(Zero)과 달리 비교 자체가 불가능한 값
- 어떤 값과 비교할 수도 없으며, 특정 값보다 크다, 적다라고 표현할 수 없다.
SELECT PLAYER_NAME 선수이름, POSITION 포지션, TEAM_ID FROM PLAYER WHERE POSITION IS NULL;
결과>> 선수이름 포지션 TEAM_ID -----> 홍길동 K08 유관순 K08 안중근 K08
* IS NULL : NULL 값이면 True 아니면 False
- NULL 값과의 수치 연산은 NULL 값을 리턴
- NULL 값과의 비교 연산은 거짓(False)을 리턴
* IS NOT NULL : NULL이 아닌 경우를 찾기 위해 사용
- NULL과 모든 사칙연산의 결과는 NULL 이다
4 ROWNUM / TOP - 행의 개수를 제한
① ROWNUM (Oracle)
- Oracle의 ROWNUM은 칼럼과 비슷한 성격의 Pseudo Column
- SQL 처리 결과 집합의 각 행에 대해 임시로 부여되는 일련번호
- 테이블/집합에서 원하는 만큼의 행만 가져올 때 WHERE 절에서 행의 개수를 제한
- 1건의 행은 [=] 연산자 사용 가능, 2건 이상부터는 [=] 사용 불가
Oracle>>
"MY_TABLE이라는 테이블의 첫번 째 칼럼을 '고유한 키'값 혹은 '인덱스 값'으로 설정하라!"
-> 새롭게 넘버링 칼럼을 설정하고, 그 값을 기 테이블의 '고유한 키' 값 혹은 '인덱스 값'으로 설정
UPDATE MY_TABLE SET COLUMN1=ROWNUM;
"PLAYER 테이블에서 PLAYER_NAME 번호가 3 이하인 선수 이름을 출력하라"
SELECT PLAYER_NAME FROM PLAYER WHERE ROWNUM <= 3;
[Oracle] SELECT 칼럼명 FROM 테이블명 ROWNUM <= N or ROWNUM < N;
고유키나 인덱스 생성 가능 -> UPDATE 테이블명 SET 칼럼명 = ROWNUM
② TOP (SQL server)
- SQL는 TOP 절을 사용하여 결과 집합으로 출력되는 행의 수를 제한
- TOP(Expression) [PERCENT] [WITH TIES];
- Expression : 반환할 행의 수를 지정
- PERCENT : 쿼리 결과 집합에서 처음 Expression%의 행만 반환됨을 나타냄
- WITH TIES : ORDER BY 절이 지정된 경우만 사용 가능, 마지막 행 같은 값 추가 출력
SQL>>
TOP (Expression) [PERCENT] [WITH TIES];
"PLAYER 테이블에서 1~5행까지의 PLAYER_NAME을 출력하라"
SELECT TOP(5) PLAYER_NAME FROM PLAYER;
[SQL] SELECT TOP(N) 칼럼명 FROM 테이블명;
-> TOP(Expression) / PERCENT / WITH TIES
#6 함수
1 내장함수 (Built In Function) - SQL을 더욱 강력하게 해주고 데이터 값을 간편 조작하는데 사용
- 벤더에서 제공하는 함수인 내장 함수 (Built In Function)
- 사용자가 정의할 수 있는 함수 (User Defined Function)
- 핵심적인 기능들은 이름/표기버이 달라도 대부분의 데이터베이스가 공통적으로 제공
- 내장함수는 다시 함수의 입력 값에 따라 단일행 함수 / 다중행 함수
- 함수는 입력값이 아무리 많아도 출력값은 하나라는 M:1 관계라는 중요한 특징을 가짐
- 단일행 함수 : 단일 행 내에 있는 하나의 값 또는 여러 값이 입력 인수로 사용
- 다중행 함수 : 여러 레코드의 값들을 입력 인수로 사용
① 단일행 함수
| 종류 | 내용 | 함수의 예 |
| 문자형 함수 | 문자를 입력하면 문자나 숫자 값을 반환한다. | LOWER, UPPER, SUBSTR/SUBSTRING, LENGTH/LEN, LTRIM, RTRIM, TRIM, ASCII |
| 숫자형 함수 | 숫자를 입력하면 숫자 값을 반환 한다. | ABS, MOD, ROUND, TRUNC, SIGN, CHR/CHAR, CEIL/CEILING, FLOOR, EXP, LOG, LN, POWER, SIN, COS, TAN |
| 날짜형 함수 | DATE 타입의 값을 연산한다. | SYSDATE/GETDATE, EXTRACT/DATEPART, TO_NUMBER(TO_CHAR(d, 'YYYY'|'MM'|'DD'))/CAST, CONVERT |
| 변환형 함수 | 문자, 숫자, 날짜형 값의 데이터 타입을 변환한다. | TO_NUMBER, TO_CHAR, TO_DATE/CAST, CONVERT |
| NULL 관련 함수 | NULL을 처리하기 위한 함수 | NVL/ISNULL, NULLIF, COALESCE |
* 주:Oracle함수/SQL함수 표기, '/' 없는 것은 공통 함수
- 추출되는 각 행마다 작업을 수행
- 각 행마다 하나의 결과를 반환
- SELECT, WHERE, ORDER BY, UPDATE의 SET절에 사용가능
- 데이터 타입 변경 가능
- 중첩해서 사용 가능
② 다중 행 함수
- 여러 개의 행이 입력, 하나의 값 반환
- 그룹(집계) 함수가 다중 행 함수
- SUM, AVG, MAX, MIN, COUNT 등
2 단일행 - 문자형 함수
* 문자형 함수 적용되었을 때 리턴되는 값
3 단일행 - 숫자형 함수
* 숫자형 함수 적용되었을 때 리턴되는 값
4 단일행 - 날짜형 함수
Oracle>>
"사원(EMP) 테이블의 입사일자에서 년, 월, 일 데이터를 각각 출력하라"
EXTRACT(MONTH FROM HIREDATE) 입사월,EXTRACT(DAY FROM HIREDATE) 입사일 FROM EMP;
SQL>>
"사원(EMP) 테이블의 입사일자에서 년, 월, 일 데이터를 각각 출력하라."
SELECT ENAME, HIREDATE, DATEPART(TEAR, HIREDATE)입사년도,
DATEPART(MONTH,HIREDATE)입사월, DATEPART(DAY,HIREDATE)입사일 FROM EMP;
or
SELECT ENAME, HIREDATE, YEAR(HIREDATE) 입사년도, MONTH(HIREDATE) 입사월,
DAY(HIREDATE) 입사일 FROM EMP;
5 단일행 - 변환형 함수
6 CASE 표현(IF-THEN-ELSE논리와 유사한 방식)
- CASE 표현은 IF-THEN-ELSE 논리와 유사한 방식으로 표현식을 작성
- SQL의 비교 연산 기능을 보완하는 역할
- ANSI/ISO 표준에는 CASE Expression 이라고 표시
- 함수와 같은 성격을 가지고 있으며 Oracle의 DECODE 함수와 같은 기능
* 단일행 CASE 표현의 종류
| CASE 표현 | 함수 설명 |
| CASE SIMPLE_CASE_EXPRESSION 조건 ELSE 표현절 END |
SIMPLE_CASE_EXPRESSION 조건이 맞으면 SIMPLE_CASE_EXPRESSION 조건내의 THEN 절을 수행하고, 조건이 맞지 않으면 ELSE 절을 수행한다. |
| CASE SEARCHED_CASE_EXPRESSION 조건 ELSE 표현절 END |
SEARCHED_CASE_EXPRESSION 조건이 맞으면 SEARCHED_CASE_EXPRESSION 조건내의 THEN 절을 수행하고, 조건이 맞지 않으면 ELSE 절을 수행한다. |
| DECODE(표현식, 기준값1, 값1 [, 기준값2, 값2, ..., 디폴트값) |
Oracle에서만 사용되는 함수로, 표현식의 값이 기준값1이면 값1을 출력하고, 기준값2이면 값2를 출력한다. 그리고 기준값이 없으면 디폴트 값을 출력한다. CASE 표현의 SIMPLE_CASE_EXPRESSION 조건과 동일하다. |
SELCET 칼럼명,
CASE
WHEN 조건
THEN 조건이 TRUE일 때 반환
ELSE 조건이 FALSE일 때 반환
END AS 칼럼명
FROM 테이블명;
SQL>>
"사원 정보에서 급여가 3000 이상이면 상등급으로, 1000 이상이면 중등급으로, 1000 미만이면 하등급으로 분류하라."
SELECT ENAME,
CASE WHEN SAL >= 3000 THEN'HIGH' WHEN SAL >= 1000 THEN 'MID'
ELSE 'LOW' END
AS SALARY_GRADE FROM EMP;
7 NULL 관련 함수
① NVL / ISNULL 함수
- 표현식 1의 값이 NULL이면 표현식2 값을 출력한다.
- 결과값을 NULL이 아닌 다른 값을 얻고자 할 때 NVL/ISNULL 함수를 사용한다.
- NULL 값의 대상이 숫자 유형 데이터인 경우는 주로 0(ZERO)으로 표현한다.
- 문자 유형 데이터인 경우는 블랭크보다는 'x' 같이 시스템에서 의미 없는 문자로 바꾼다.
* NULL 포함 연산의 결과
| 연산 | 연산의 결과 |
| NULL + 2, 2 + NULL | NULL |
| NULL - 2, 2 - NULL | NULL |
| NULL * 2, 2 * NULL | NULL |
| NULL / 2, 2 / NULL | NULL |
* 단일행 NULL 관련 함수의 종류
| 일반형 함수 | 함수 설명 |
| NVL(표현식1, 표현식2) / ISNULL(표현식1, 표현식2) |
표현식1의 결과값이 NULL이면 표현식2의 값을 출력한다. 단, 표현식1과 표현식2의 결과 데이터 타입이 같아야 한다. NULL 관련 가장 많이 사용되는 함수이므로 상당히 중요하다. |
| NULLIF(표현식1, 표현식2) | 표현식1이 표현식2와 같으면 NULL을, 같이 않으면 표현식1을 리턴한다. |
| COALESCE(표현식1, 표현식2, ...) |
임의의 개수 표현식에서 NULL이 아닌 최초의 표현식을 나타낸다. 모든 표현식이 NULL이라면 NULL을 리턴한다. |
② NULL과 공집합
- SELECT 1 FROM DUAL WHERE 1 = 2;와 같은 조건이 대표적인 공집합 발생 쿼리
- 조건에 맞는 데이터가 한 건도 없는 경우를 공집합
- NULL 데이터와는 또 다르게 이해해야 한다
③ NULLIF (표현식1, 표현식2)
- 표현식1이 표현식2와 같으면 NULL을, 같지 않으면 표현식1을 리턴
SQL>>
"사원 테이블에서 MGR과 7698이 같으면 NULL을 표시하고, 같지 않으면 MGR을 표시한다."
SELECT ENAME, EMPNO, MGR, NULLIF(MGR, 7698) AS NULLIF_COLUMN FROM EMP;
④ COALESCE (표현식1, 표현식2, ...)
- 임의의 개수 표현식에서 NULL이 아닌 최초의 표현식
- COALESCE 함수는 인수의 숫자가 한정되어 있지 않음
- 임의의 개수 EXPR에서 NULL이 아닌 최초의 EXPR을 나타낸다.
- 만일 모든 EXPR이 NULL이라면 NULL을 리턴
SQL>>
"사원테이브레서 커미션(COMM)을 1차 선택값으로, 급여(SAL)를 2차 선택값으로 선택하되 두 칼럼 모두 NULL인 경우는 NULL로 표시한다."
SELECT ENAME, COMM, SAL, COALESCE(COMM, SAL) COAL FROM EMP;'SQL 공부일지' 카테고리의 다른 글
| SQL 공부 일지 8일차 230823 (0) | 2023.08.23 |
|---|---|
| SQL 공부 일지 7일차 230820 (1) | 2023.08.20 |
| SQL 공부 일지 5일차 230816 (0) | 2023.08.17 |
| SQL 공부 일지 4일차 230814 (0) | 2023.08.14 |
| SQL 공부 일지 3일차 230812 (0) | 2023.08.12 |
