[SQLD] 2과목. SQL 기본 및 활용

01. SQL 기본

기본 SQL

WHERE절

SQL을 사용해 DB에서 데이터를 조회할 때 원하는 데이터만 검색하기 위해 FROM절 다음에 위치해 조건을 설정해 조회

구성
- 칼럼명(보통 조건식의 좌측)
- 비교 연산자
- 문자/숫자/표현식(보통 조건식 우측에 위치)
- 비교 칼럼명(JOIN사용 시)

연산자

비교 연산자	=, >, <, >=, <=
SQL 연산자	BETWEEN a AND b	a와 b의 값 사이에 있다
	IN (List)	리스트에 있는 값 중 어느 하나라도 일치 하는지
	LIKE '비교문자열'	비교문자열과 형태가 일치하는지
	IS NULL	NULL인 경우
논리 연산자	AND	앞과 뒤의 조건이 참이면 결과도 참
	OR	앞이 참이거나 뒤가 참이어야 결과도 참
	NOT	뒤에 오는 조건에 반대되는 결과
부정 비교 연산자	!=, ^=, <>	같지 않다
	NOT 칼럼명 =	~와 같지 않다
	NOT 칼럼명 >	~보다 크지 않다
부정 SQL 연산자	NOT BETWEEN a AND b	a와 b의 값 사이에 있지 않다
	NOT IN (List)	List 값과 일치하지 않는다
	IS NOT NULL	NULL 값을 갖지 않는다

연산자 우선순위

괄호로 묶은 연산
부정 연산자(NOT)
비교 연산자(=, <, >, <=, >=)와 SQL 연산자(BETWEEN, IN, LIKE, IS NULL)
논리 연산자 중 AND, OR 순

NULL

모르는 값. 값의 부재
공백 문자 혹은 숫자 0과 동일하지 않음
NULL과의 모든 비교(IS NULL 제외)는 알 수 없음을 반환
Oracle에서 ''를 NULL로 처리
테이블을 생성할 때, NOT NULL 또는 PRIMARY KEY로 정의되지 않은 모든 데이터 유형은 NULL값 포함 가능
결괏값을 NULL이 아닌 다른 값을 얻고자 할 때, NVL/ISNULL 함수를 사용

NULL 연산

NULL값과의 연산(+, -, *, /)은 NULL 값을 Return
NULL값과의 비교 연산은 거짓(FALSE)을 Return
특정 값보다 크다, 적다고 표현할 수 없음

DML(데이터 조작어)

DB에 들어있는 데이터를 조회하거나 검색, 혹은 데이터에 변형을 가하는 종류의 명령어

DB 사용자가 응용프로그램이나 질의어를 통해 저장된 DB를 실질적으로 접근하는 데 사용

SELECT
INSERT
UPDATE
DELETE

종류

절차적 DML : 사용자가 어떻게(How) 데이터를 접근해야 하는지 명세
비절차적 DML : 사용자가 무슨(What) 데이터를 원하는 지만을 명세

ALL / DISTINCT

ALL : Default 옵션
DISTINCT : 중복된 데이터가 있는 경우 1건으로 처리해 출력

INSERT 규칙

NO ACTION	참조 무결성을 위반하는 입력 액션 취하지 않음
SET NULL	Master 테이블에 PK가 없을 경우, Child 외부키를 Null값으로 처리
SET DEFAULT	Master 테이블에 PK가 없을 경우, Child 외부키를 지정된 기본값으로 처리
AUTOMATIC	Master 테이블에 PK가 없을 경우, Master PK 생성 후 Child 입력
DEPENDENT	Master 테이블에 PK가 존재할 때만 Child 입력 허용

INSERT를 사용할 땐 삽입 칼럼을 명시하지 않은 경우 모든 칼럼을 삽입해야 함

INSERT INTO 테이블명 (COL1, COL3, COL5) VALUES(1, 3, 5);

INSERT INTO 테이블명 VALUES(1, 2, 3, 4, 5);

UPDATE

UPDATE 테이블명 SET 컬럼명='값';

DELETE / MODIFY 규칙

NO ACTION	참조무결성을 위반하는 삭제/수정 액션 취하지 않음
RESTICT	Child 테이블에 PK값이 없는 경우만 Master 삭제 허용
CASCADE	Master 삭제 시 Child 같이 삭제
SET NULL	Master 삭제 시 Child 해당 필드 NULL 값으로 설정
SET DEFAULT	Master 삭제 시 Child 해당 필드 Default 값으로 설정

DDL(데이터 정의어)

테이블의 구조를 생성, 변경, 삭제, 이름을 바꾸는 것과 같은 데이터 구조 정의하는 데 사용되는 명령어

스키마, 도메인, 테이블, 뷰, 인덱스를 정의하거나 변경 또는 제거할 때 사용

CREATE
ALTER
DROP
RENAME

TABLE 생성 시 제약 조건

PRIMARY KEY	테이블에 저장된 행 데이터를 고유하게 식별하기 위한 기본키 하나의 테이블엔 하나의 기본키(UNIQUE), NOT NULL의 특성
UNIQUE KEY	테이블에 저장된 행 데이터를 고유하게 식별하기 위한 고유키 정의 테이블에 중복되는 값은 없지만 NULL 입력이 가능
NOT NULL	NULL 값의 입력을 금지
CHECK	입력할 수 있는 값의 범위 등을 제한. (TRUE or FALSE로 평가할 수 있는 논리식을 지정)
FOREIGN KEY	테이블 간의 관계를 정의하기 위해 기본키를 다른 테이블의 외래키로 복사 테이블 생성시 설정 가능 / NULL 값 가질 수 있음 한 테이블에 여러 개 존재 가능 / 참조 무결성 제약 받을 수 있음

TABLE 생성 시 주의사항

테이블명은 객체를 의미할 수 있는 적절한 이름 사용. 가능한 단수형
다른 테이블명과 중복 X
한 테이블 내에서 칼럼명 중복 X
테이블 이름을 지정하고 각 칼럼들은 괄호 ()로 묶어 지정
각 칼럼들은 콤마 , 로 구분하고, 생성문의 끝은 항상 세미콜론 ;으로 끝남
칼럼 뒤에 항상 데이터 유형이 지정되어야 하고, 칼럼에 대해서는 다른 테이블까지 고려해 DB내에 일관성 있게 사용
테이블명, 칼럼명은 반드시 문자로 시작하고, 사전에 정의한 예약어 사용 불가
A-Z, a-z, 0-9, _, $, # 문자만 허용

ALTER 사용

SQLServer
- 동시에 여러 개의 칼럼을칼럼을 수정하는 구문은 지원 X
- 괄호 사용 X

ALTER TABLE 테이블명 ALTER COLUMN 칼럼명 데이터유형 [DEFAULT 식][NOT NULL];

Oracle

ALTER TABLE 테이블명 MODIFY COLUMN 컬럼명 데이터유형 [DEFAULT 식][NOT NULL];

불필요한 컬럼 삭제

ALTER TABLE 테이블명 DROP COLUMN 컬럼명;

TABLE 이름 변경

RENAME 테이블명 TO 변경할 테이블명

TABLE 삭제

구분	DROP	TRUNCATE	DELETE
언어	DDL	DDL(일부는 DML 성격)	DML
의미	테이블 구조 완전 삭제	테이블을 최초 생성된 초기상태로 만듬	데이터만 삭제
Rollback	불가능	불가능	Commit이전에는 가능
Commit	Auto	Auto	사용자 Commit
데이터 삭제	O	O	O
디스크 사용량 초기화	O	O	X
스키마 정의 삭제	O	X	X

TRUNCATE는 UNDO를 위한 데이터를 생성하지 않아서 동일 데이터량 삭제 시 DELETE보다 빠름

DCL(데이터 제어어)

DB에 접근하고 객체들을 사용하도록 권한을 주고 회수하는 명령어

GRANT
REVOKE

TCL(트랜잭션 제어어)

논리적인 작업의 단위를 묶어 DML에 의해 조작된 결과를 작업 단위별로 제어하는 명령어

Commit : 데이터에 대한 변경사항을 DB에 영구적으로 반영
Rollback : 데이터에 대한 변경사항을 모두 폐기하고 변경 전의 상태로 되돌리는 것
Savepoint : 롤백 시 트랜잭션에 포함된 전체 작업이 아닌 현시점에서 SAVEPOINT까지의 일부 작업만 롤백 가능

[ORACLE]
SAVEPOINT SVPT1;
...
ROLLBACK TO SVPT1;

[SQL SERVER]
SAVE TRANSACTION SVTR1;
...
ROLLBACK TRANSACTION SVTR1;

특징

Oracle에서는 DDL문장 수행 후 자동 Commit 수행
Oracle에서 DDL문장의 수행은 내부적으로 트랜젝션을 종료시키므로 앞의 명령어는 수행됨
SQL Server에서는 DDL문장 수행 후 자동으로 Commit 수행하지 않음
SQL Server에서는 자동 Commit이 진행되지 않기 때문에 ROLLBACK문장으로 인해 앞의 명령어는 수행 X

트랜잭션

DB의 논리적 연산 단위로 밀접히 관련되어 분리될 수 없는 한 개 이상의 DB조작을 가리킴

특성

원자성(Atomicity)	트랜잭션에 정의된 연산들은 모두 성공적으로 실행되던지 전혀 실행되지 않은 상태로 남아 있어야함
일관성(Consistency)	트랜잭션이 실행되기 전의 DB내용에 오류가 없다면 실행 이후에도 DB내용에 오류가 있으면 안됨
고립성(Isolation)	트랜잭션이 실행되는 도중에 다른 트랜잭션의 영향을 받아 잘못된 결과를 만들어서는 안됨
지속성(Durability)	트랜잭션이 성공적으로 수행되면 갱신한 DB의 내용은 영구적으로 저장됨

격리성이 낮은 경우 발생하는 문제점

Dirty Read	다른 트랜잭션에 의해 수정되었지만 아직 Commit되지 않은 데이터를 읽는 것
Non-Repeatable Read	한 트랜잭션 내에서 같은 쿼리 두 번 수행했는데, 그 사이 다른 트랜잭션이 값을 수정 또는 삭제해 쿼리 결과가 다르게 나타나는 현상
Phantom Read	한 트랜잭션 내에서 같은 쿼리 두 번 수행했는데, 첫 번째 쿼리에서 없던 유령 레코드가 두 번째 쿼리에서 나타나는 현상

함수

종류

내장 함수 : 벤더에서 제공하는 함수
- 단일행 함수
- 다중행 함수 : 집계 함수, 그룹 함수, 윈도 함수
사용자 정의 함수

단일행 함수

SELECT, WHERE, ORDER BY, UPDATE의 SET 절에 사용 가능
1:M의 두 테이블을 조인할 경우라도 M 쪽에서 출력된 행이 하나씩 단일행 함수의 입력값으로 사용되므로 사용 가능
다중행 함수도 동일하게 단일 값만을 반환

Oracle/SQL Server함수로 표시 ('/'가 없는 것은 공통 함수)

문자형 함수	문자를 입력하면 문자나 숫자 값 반환	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')) / YEAR\|MONTH\|DAY
변환형 함수	문자, 숫자, 날짜형 값의 데이터 타입을 변환	TO_NUMBER, TO_CHAR, TO_DATE / CAST, CONVERT
NULL 관련 함수	NULL을 처리하기 위한 함수	NVL / ISNULL, NULLIF, COALESCE

LOWER(문자열), UPPER(문자열)	문자열의 알파벳 문자를 소문자로 바꿔줌, 문자열의 알파벳 문자를 대문자로 바꿔줌
ASCII(문자)	문자나 숫자를 ASCII 코드 번호로 바꿔줌
CHR/CHAR(ASCII번호)	ASCII 코드 번호를 문자나 숫자로 바꿔줌
CONCAT(문자열1, 문자열2)	문자열 1과 문자열 2를 연결('\|\|'나 '+'와 동일)
SUBSTR/SUBSTRING(문자열, m[, n])	문자열 중 M위치에서 N개의 문자 길이에 해당하는 문자를 반환
LENGTH/LEN(문자열)	문자열 개수를 숫자값으로 반환
LTRIM(문자열 [, 지정문자])	문자열의 첫 문자로 확인해 지정문자가 나타나면 해당 문자 제거
RTRIM(문자열 [, 지정문자])	문자열의 마지막 문자부터 확인해 지정 문자가 나타나는 동안 해당 문자 제거
TRIM	문자열에서 머리말, 꼬리말, 또는 양쪽에 있는 지정 문자를 제거

단일행 NULL 관련 함수