트랜잭션 - 데이터베이스릐 논리적 연산단위 - 하나의 트랜잭션에는 하나 이상의 SQL 문장 포함 - 밀접히 관련되어 분리될 수 없는 한 개 이상의 데이터베이스 조작 - 그렇기 떄문에 전부 적용하거나 전부 취소. ALL OR NOTHING의 개념 A-> B 100만원 이체 프로세스 1. A 계좌 잔액 조회 2. A 계좌 100만원 차감 3. B 계좌 잔액 조회 4. B 계좌 100만원 추가
트랜잭션: 트랜잭션 시작 이후 실행되는 쿼리를 하나의 작업으로 처리 - 트랜잭션 범위 내에서 실행되는 쿼리 결과는 커밋 전까지는 DB에 반영되지 않음 트랜잭션 커밋(=SAVE): 트랜잭션 시작 이후 에러가 발생하지 않으면 쿼리 결과를 DB에 반영 트랜잭션 롤백(=LOAD): 에러가 발생하면 실행된 쿼리 결과를 DB에 반영하지 않고 취소
트랜잭션 특성 - 원자성: 트랜잭션에서 정의된 연산들은 모두 성공적으로 실행되던지 아니면 전혀 실행되지 않은 상태로 남아 있어야 함 - 일관성: 트랜잭션이 실행되기 전의 데이터베이스 내용이 잘못되어 있지 않다면 트랜잭션이 실행된 이후에도 데이터베이스의 내용에 잘못이 있으면 안됨 - 고립성: 트랜잭션이 실행되는 도중에 다른 트랜잭션의 영향을 받아 잘못된 결과를 만들어서는 안됨 - 지속성: 트랜잭션이 성공적으로 수행되면 그 트랜잭션이 갱신한 데이터베이스의 내용은 영구적으로 저장됨
잠금: 원자성과 관련도니 것으로 어떤 트랜잭션이 진행 중일 떄 방해받지 못하게 함(ex: 중복 이체)
COMMIT: 입력한 자료나 수정한 자료에 대해서 또는 삭제한 자료에 대해서 전혀 문제가 없다고 판단되었을 경우 -> 트랜잭션 완료 COMMIT이나 ROLLBACK 이전의 상태 - 메모리 버퍼에만 영향을 받았기 때문에 데이터의 변경 이전 상태로 복구 가능 - 현재 사용자는 SELECT 문장으로 결과 확인 가능 - 다른 사용자는 현재 사용자가 수행한 명령의 결과를 볼 수 없음 - 변경된 행은 잠금이 설정되어서 다른 사용자가 변경 불가 COMMIT 이후의 데이터 상태 - 데이터에 대한 변경 사항이 데이터베이스에 반영 - 이전 데이터는 영원히 손실 - 모든 사용자는 결과를 볼 수 있음 - 관련된 행에 대한 잠금이 풀리고, 다른 사용자들이 행을 조작할 수 있음 SQL Server에서 COMMIT - DLL 뿐만 아니라 DML도 AUTO COMMIT -> COMMIT 필요X - 명령어가 성공하면 COMMIT, 실패하면 ROLLBACK
ROLLBACK - 테이블 내 입력한 데이터나, 수정한 데이터, 삭제한 데이터에 대해 COMMIT 이전에는 변경 사항을 취소할 수 있는데 데이터베이스에서는 롤백 기능 사용 - 롤백은 데이터 변경 사항이 취소되어 데이터의 이전 상태로 복구되며, 관련된 행에 대한 잠금이 풀리고 다른 사용자들이 데이터 변경을 할 수 있음 ROLLBACK 후의 데이터 상태 - 데이터에 대한 변경사항 취소 - 이전 데이터는 다시 재저장 - 관련된 행에 대한 잠금이 풀리고 다른 사용자들이 행을 조작할 수 있게 됨 SQL Server에서의 ROLLBACK - AUTO COMMIT이므로, ROLLBACK을 위해서는 명시적으로 트랜잭션을 선언해야 함 - BEGIN TRAN INSERT INTO PLAYER(PLAYER_ID, TEAM_ID, PLAYER_NAME) VALUES('1999035', 'K02', '가나다'); ROLLBACK; - BEGIN TRAN UPDATE PLAYER SET TEAM_ID='K01'; ROLLBACK; - BEGIN TRAN DELETE FROM PLAYER; ROLLBACK; - ROLLBACK을 누르면 BEGIN TRAN으로 돌아감. BEGIN TRAN 뒤에 sql 문이 취소됨 COMMIT과 ROLLBACK 효과 - 데이터 무결성 보장 - 영구적인 변경을 하기 전에 데이터의 변경사항 확인 가능 - 논리적으로 연관된 작업을 그룹핑하여 처리 가능
SAVEPOINT - 롤백할 때 트랜잭션에 포함된 전체 작업을 롤백하는 것이 아니라 현 시점에서 SAVEPOINT까지 트랜잭샨의 일부만 롤백 - 복잡한 대규모 트랜잭션에서 에러가 발생했을 때 SAVEPOINT까지의 트랜잭션만 롤백하고 실패한 부분에 대해서만 다시 실행 가능 - 복수의 SAVEPOINT를 정의할 수 있으며, 동일이름으로 SAVEPOINT를 정의했을 때는 나중에 정의한 SAVEPOINT가 유효
WHERE 절
WERER - 원하는 자료만을 검색하기 위해서 SQL 문장에 WHERE 절을 이용하여 자료들에 대해 제한할 수 있음 - WHERE 조건절을 사용하지 않고 필요 없는 많은 자료들을 데이터베이스로부터 요청하는 SQL 문장은 대량의 데이터를 점색하기 위해 서버의 CPU나 MEMORY와 같은 시스템 자원들을 과다하게 사용. 네트워크에 부하도 일으킴 - 이런 문제점을 방지하기 위해 WHERE 절에 조건이 없는 FTS 문장은 SQL 튜닝의 1차적인 검토 대상이 됨.(FTS가 무조건 나쁜 것은 아니며 병렬 처리 등을 이용해 유용하게 사용하는 경우도 많음 - SELECT ~ FROM ~ WHERE 순서
연산자의 종류 - K-리그 일부 선수들의 이름과 포지션, 백넘버를 알고싶음 - 조건은 소속팀이 삼성블루윙즈이거나 전남드래곤즈에 소속된 선수들 중에서 포지션이 미드필드이면서, 키는 170cm 이상, 180cm 이하여야 함 - 조건을 주체적으로 설정하기 위해 다향한 연산자의 조합이 필요
관련 연산 - WHERE TEAM_ID = 'K02' - WHERE POSITION = "MF" - WHERE HEIGHT >= 170 - WHERE HEIGHT <= 180 -> '180'이라고 표현하더라도, HEIGHT라는 칼럼이 숫자 유형의 변수이므로 내부적으로 '180'이라는 문자열을 숫자 유형 180으로 바꾸어 처리
관련 연산 - WHERE TEAM_ID IN ('K02', 'K07') - WHERE POSITION LIKE 'MF' WHERE HEIGHT BETWEEN 170 AND 180
- 조건은 소속팀이 삼성블루윙즈이거나 전남드래곤즈에 소속된 선수들 중에서 포지션이 미드필드이면서, 키는 170cm 이상, 180cm 이하여야 함 - SELECT PLAYER_NAME 선수이름, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키 FROM PLAYER WHERE (TEAM_ID='K02' OR TEAM_ID='K07') AND POSITION='MF AND HEIFHT>=170 AND HEIGHT<=180; - SELECT PLAYER_NAME 선수이름, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키 FROM PLAYER WHERE TEAM_ID IN('K02', 'K07') AND POSITION='MF AND HEIFHT 170 BETWEEN 180; ROWNUM - Oracle의 ROWNUM은 칼럼과 비슷한 성격의 Pseudo Column으로써 SQL 처리 결과 집합의 각 행에 대해 임시로 부여되는 일련번호 - 테이블이나 집합에서 원하는 만큼의 행만 가져오고 싶을 때 WHERE 절에서 행의 개수를 제한하는 목적으로 사용(MYSQL의 limit와 유사) - 1건의 행만 가져오고 싶을 때 - SELECT PLAYER_NAME FROM PLAYER WHERE ROWNUM = 1; - SELECT PLAYER_NAME FROM PLAYER WHERE ROWNUM <= 1; - SELECT PLAYER_NAME FROM PLAYER WHERE ROWNUM < 2; - 2건 이상의 N 행을 가져오고 싶을 때 - SELECT PLAYER_NAME FROM PLAYER WHERE ROWNUM <= N; - SELECT PLAYER_NAME FROM PLAYER WHERE ROWNUM < N+1;
TOP 절 - SQL Server는 TOP 절을 사용하여 결과 집합으로 출력되는 행의 수 제한 - TOP (Expression) [PERCENT] [WITH TIES] - Expression: 반환할 행의 수를 지정하는 숫자 - PERCENT: 쿼리 결과 집합에서 처음 Expression%의 행만 반환됨 - WITH TIES: ORDER BY 절이 지정된 경우에만 사용할 수 있으며, TOP N(PERCERNT)의 마지막 행과 같은 값이 있는 경우 추가 행이 출력 - 1건의 행만 가져오고 싶을 떄 - SELECT TOP(1) PLAYER_NAME FROM PLAYER; - 2건 이상의 N 행을 가져오고 싶을 때 - SELECT TOP(N) PLAYER_NAME FROM PLAYER;
함수
함수의 분류 - 벤더에서 제공하는 함수인 내장 함수(ex: Oracle, MYSQL 등) - 사용자가 정의할 수 있는 함수
내장 함수 - SQL을 더욱 강력하게 해주고 데이터 값을 간편하게 조작하는데 사용 - 벤더별로 가장 큰 차이를 보이나, 핵심적인 기능들은 이름이나 표현법이 다르더라도 대부분의 데이터베이스가 공통적으로 제공 - 단일행 값이 입력되는 단일행 함수 - 여러 행의 값이 입력되는 다중행 함수 - 집계 함수, 그룹 함수, 윈도우 함수 단일행 함수의 특징 - SELECT, WHERE, ORDER BY 절에 사용 가능 - 각 행들에 대해 개별적으로 작용하여 데이터 값들을 조작하고, 각각의 행에 대한 조작 결과를 리턴 - 여러 인자를 입력해도 단 하나의 결과만 리턴 - 함수의 인자로 상수, 변수, 표현식이 사용 가능하고, 하나의 인수를 가지는 경우도 있지만 여러 개의 인수를 가질 수도 있음 - 특별한 경우가 아니면 함수의 인자로 함수를 사용하는 함수의 중첩 가능 DUAL 테이블 - 사용자 SYS가 소유하며 모든 사용자가 액세스 가능 - SELECT~FROM~의 형식을 갖추기 위한 일종의 DUMMY 테이블 - SQL Server의 경우 SELECT만으로도 SQL 문장 실행 가능(DUAL 필요X)
날짜형 함수 - 데이터베이스는 날짜를 지정할 때 내부적으로 세기, 년, 월, 일, 시, 분, 초와 같은 숫자 형식으로 변환하여 저장 - 날짜는 여러 가지 형식으로 출력이 되고 날짜 계산에도 사용되기 때문에 그 편리성을 위해서 숫자형으로 저장하는 것 - 날짜를 숫자로 저장하기 때문에 덧셈, 뺄셈 같은 산술 연산자로도 계산 가능
연산
결과
설명
날짜 + 숫자
날짜
숫자만큼의 날수를 날짜에 더한다.
날짜 - 숫자
날짜
숫자만큼의 날수를 날짜에서 뺀다.
날짜1 - 날짜2
날짜수
날짜에서 다른 날짜를 빼면 일수가 나옴
날짜 + 숫자/24
날짜
시간을 날짜에 더한다
변환형 함수 - 명시적 데이터 유형 반환: 데이터 변환형 함수로 데이터 유형을 변환하도록 명시해주는 경우 - 암시적 데이터 유형 반환: 데이터베이스가 자동으로 데이터 유형을 반환하여 계산하는 경우 - 암시적 데이터 유형 반환의 경우 성능 저하가 발생할 수 있으며, 자동적으로 데이터베이스가 알아서 계산하지 않는 경우가 있어 에러를 발생할 수 있으므로 명시적인 데이터 유형 변환 방법을 사용하는 것이 바람직함 NULL의 특징 - 널 값은 아직 정의되지 않은 값으로 0 또는 공백과 다름 - 0은 숫자, 공백은 문자 - 테이블을 생성할 때 NOT NULL 또는 PROMARY KEY로 정의되지 않은 모든 데이터 유형은 NULL 값을 포함할 수 있음 - NULL 값을 포함하는 연산의 경우 결과 값도 NULL 값임 - 결과값을 NULL이 아닌 다른 값을 얻고자 할 때 NVL(Oracle)/ISNULL(SQLServer) 함수 사용 - NULL 값의 대상이 숫자 유형 데이터인 경우는 주로 0으로, 문자 유형 데이터인 경우는 공백보다는 'x' 같이 해당 시스템에서 의미 없는 문자로 바꾸는 경우가 많음
- NVL 함수를 다중행 함수의 인자로 사용하는 경우는 오히려 불필요한 부하를 발생할 수 있으므로 굳이 NVL 함수를 사용할 필요x
