[SQLD] 4. SQL 기본 - 2

 

트랜잭션

 
원자성(atomicity) : 트랜잭션에서 정의된 연산들은 모두 성공적으로 실행되던지
아니면 전혀 실행되지 않은 상태로 남아 있어야 한다. (All or Nothing)
 
일관성(consistency) : 트랜잭션이 실행 되기 전의 데이터베이스 내용이 잘못 되어 있지 않다면 
트랜잭션이 실행된 이후에도 데이터베이스의 내용에 잘못이 있으면 안된다. 
 
고립성(isolation) : 트랜잭션이 실행되는 도중에 다른 트랜잭션의 영향을 받아 잘못된 결과를 만들어서는 안된다. 
 
지속성(durability) : 트랜잭션이 성공적으로 수행되면 그 트랜잭션이 갱신한 데이터베이스의 내용은 영구적으로 저장된다. 
 
 

격리성(isolation)으로 인해 나타날 수 있는 문제점

 

Dirty Read

다른 트랜잭션에 의해 수정됐지만 아직 커밋되지 않은 데이터를 읽는 것을 말합니다.
 
 

Non-Repeatable Read

한 트랜잭션 내에서 같은 Key를 가진 Row를 두 번 읽었는데 그 사이에 값이 변경되거나 삭제되어 결과가 다르게 나타나는 현상을 말합니다.
 
 

Phantom Read

한 트랜잭션 내에서 같은 쿼리를 두 번 수행했는데, 첫 번째 쿼리에서 없던 유령(Phantom) 레코드가 두 번째 쿼리에서 나타나는 현상을 말합니다.
 
 
 

트랜잭션 제어 명령어(COMMIT , ROLLBACK ) 

트랜잭션 제어 명령어	기능
COMMIT	보류중인 모든 데이터 변경사항을 영구적으로 적용. 현재 트랜잭션 종료
ROLLBACK	보류중인 모든 데이터 변경사항을 폐기. 현재 트랜잭션 종료, 직전 커밋 직후의 단계로 회귀(되돌아가기) 전체 트랜잭션을 롤백함

 
[ORACLE]
DDL 문장 수행 후 자동으로 COMMIT 을 수행한다. 
 
ex) ORACLE 에서는 CREATE TABLE 문장을 수행한 후 , 묵시적으로 COMMIT 이 수행 
 
[SQL Server]
DDL 문장 수행 후 자동으로 COMMIT 을 수행하지 않는다 
 
ex) SQL Server 에서는 ROLLBACK 문장에 의해 UPDATE가 취소된다.
 
 
 

BEGIN TRANSACTION (트랜잭션 시작)

 
ROLLBACK 명령어를 만나면 최초의 시점까지 모두 롤백된다. 
 
ROLLBACK 구문은 COMMIT 되지 않은 상위의 모든 트랜잭션을 모두 롤백한다. 
 
 

SAVE TRANSACTION ( 트랜잭션 저장점) 

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

조건식(WHERE 절)

원하는 데이터만을 검색하기 위해 제한하는 것
데이터의 조건을 설정하여 데이터를 제한 
 
FROM 다음에 위치 
 
SELECT 컬럼명
FROM 테이블명
WHERE 조건식 
 

연산자의 우선순위

 
 

NULL의 연산

NULL 은 0 과 공백이랑은 다르다. 
 
NULL은 특정 값보다 크다,적다라고 표현할 수 없다.
 
NULL값 과의 연산은 NULL값을 리턴한다. ( 비교연산은 FALSE 리턴 ) 
 
NULL + 40 = NULL 
 
NULL 값을 조건절에서 사용하는 경우 IS NULL , IS NOT NULL 이란 키워드 사용 

ISNULL/NVL 함수

표현식1의 결과값이 NULL이면 표현식2의 값을 출력한다.

NVL(표현식1, 표현식2)
ISNULL(표현식1, 표현식2)

--예시--
ISNULL(Colum,0)

NULLIF 함수

표현식1이 표현식2와 같으면 NULL을 , 같지 않으면 표현식1을 리턴한다.

NULLIF(표현식1, 표현식2)

COALESCE 함수

임의의 개수 표현식에서 NULL 이 아닌 최초의 표현식을 나타낸다.
모든 표현식이 NULL이라면 NULL 을 리턴한다.

COALESCE(표현식1, 표현식2, … )

 

COUNT 함수

COUNT(*) : NULL 값을 포함한 행의 수 출력

COUNT(표현식) : 표현식의 값이 NULL 값인 것을 제외한 행의 수 출력

 
 

NULL값과 공백 비교 

 
Oracle에서는 Empty string('')을 Null로 처리 한다.
 
오라클은 NULL값과 공백('')값을 똑같이 NULL로 처리한다
 
 
서비스명 values('')
SELECT * FROM 서비스 WHERE 서비스명 IS NULL;
 
 

BETWEEN a AND b 

a 와 b 의 값 사이 ( a와b 값이 포함 ) 
 
 

 

내장함수(Built-in Function) 

 
함수의 입력 행수에 따라 단일행,다중행으로 구분 
 
단일행 함수 : 단일행 값이 입력되는 함수[문자형, 숫자형, 날짜형, 변환형, NULL관련 함수 등]
 
다중행 함수 : 다중행 값이 입력되는 함수[집계 함수, 그룹함수, 윈도우 함수 등]
 
다중행 함수도 단일행 함수와 동일하게 단일 값만을 반환한다. 
 

문자열 함수

CONCAT(문자열1, 문자열2)

문자열1과 문자열2를 결합     → Oracle, My SQL에서 유효한 함수     → "| |"(Oracle)나 "+"(SQL Server)를 사용하기도 함

LENGTH/LEN(문자열)

문자열의 길이를 숫자값으로 변환     → 공백이나 줄바꿈도 count!!!     → REPLACE(칼럼명, CHR(길이))를 사용하면 줄바꿈이 제거됨

EXTRACT('YEAR' | 'MONTH' | 'DAY' from d)/ DATEPART('YEAR' | 'MONTH' | 'DAY' ,d)

날짜 데이터에서 년/월/일 데이터를 반환 (시간/분/초도 가능) ex1> EXTRACT(MONTH FROM DATE '2020-12-20') → 12이 출력 ex2> EXTRACT(MONTH FROM DATE '2020-01-01') → 01이 아닌 1이 출력

 

날짜형 함수

EXTRACT('YEAR' | 'MONTH' | 'DAY' from d)/ DATEPART('YEAR' | 'MONTH' | 'DAY' ,d)

날짜 데이터에서 년/월/일 데이터를 반환 (시간/분/초도 가능) ex1> EXTRACT(MONTH FROM DATE '2020-12-20') → 12이 출력 ex2> EXTRACT(MONTH FROM DATE '2020-01-01') → 01이 아닌 1이 출력

1/24/60은 24시간으로 나누고 60분으로 나눴으므로 1분을 의미
1/24/(60/10)은 괄호연산부터 먼저하면 1/24/6과 동일하므로 10분을 의미
 
 

 

조건에 따라서 값을 지정(CASE)

 
[SEARCHED_CASE]
SELECT LOC,   CASE WHEN LOC = 'NEW YORK' THEN 'EAST'     ELSE 'ETC'   END as AREA
 
[SIMPLE_CASE]

SELECT LOC,   CASE LOC WHEN 'NEW YORK' THEN 'EAST'

 

CASE 문의 형식은  

 

CASE 컬럼

WHEN 조건1 THEN 값1 

WHEN 조건2 THEN 값2 

ELSE 값3 

END 

 

 

컬럼이 조건1 일때는 값1 , 조건2 일때는 값2 반환 

조건에 맞지 않는 경우 값3 반환 

 

 

데이터 그룹화/집계 (GROUP BY)

같은 값을 가진 행끼리 하나의 그룹으로 뭉침

장르를 기준으로 그룹

SELECT 집계함수(컬럼1), 집계함수(컬럼2) ..
FROM 테이블
GROUP BY 컬럼

HAVING

그룹에 대한 조건절 , 그룹 중 조건에 맞는 그룹만 남깁니다.

테이블 조회 정렬 ( ORDER BY )

테이블에서 SELECT로 데이터를 조회할 때, ORDER BY를 추가하여 지정된 컬럼을 기준으로 정렬 할 수 있습니다.

 

오름차순(ASC) 또는 내림차순(DESC) 두 가지 정렬 방법이 있습니다.

 

기본은 오름차순(ASC)입니다

 

 

SELECT 문장 실행 순서

 

FROM - WHERE - GROUP BY - HAVING - SELECT - ORDER BY 

 

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

2. 데이터가 아닌 것은 제거 (WHERE)

3. 그룹화 (GROUP BY)

4. 그룹핑 조건 (HAVING)

5. 출력 (SELECT)

6. 정렬 (ORDER BY)

 

 

상위 N개의 데이터 출력(TOP)

 

SELECT TOP(3) 팀명, 승리건수 

FROM 팀별성적 

 

 

TOP WITH TIES

 

TOP 과 동일하게 상위 N개의 데이터를 조회한다.

동일한 데이터가 있을 경우 함께 출력 한다. 

 

TOP WITH TIES를 사용하기 위해서는 ORDER BY 절이 반드시 함께 와야 한다. (TOP의 경우 없어도 가능)

동일한 데이터는 ORDER BY절 뒤의 오는 컬럼이 기준이 된다.

 

 

 

EQUI JOIN 

 

두 개 이상의 테이블 들을 연결 또는 결합하는 것 

 

SELECT 테이블1.컬럼명 , 테이블2.컬럼명 

FROM 테이블1, 테이블2

WHERE 테이블1.컬럼 = 테이블2.컬럼

 

 

LIKE 연산자 

 

 문자열에서 와일드카드(%)를 사용하여 원하는 문자가 포함된 자료를 쉽게 검색할 수 있다.