[2과목] - SQL 기본

SQL 문장들의 종류

명령어의 종류	명령어	설명
데이터 조작어 (DML : Data Manipulation Language)	SELECT	데이터베이스에 들어 있는 데이터를 조회하거나 검색하기 위한 명령어를 말하는 것으로 RETRIEVE 라고도 한다.
	INSERT UPDATE DELETE	데이터베이스의 테이블에 들어 있는 데이터에 변형을 가하는 종류의 명령어들을 말한다. 예를 들어 데이터를 테이블에 새로운 행을 집어넣거나, 원하지 않는 데이터를 삭제하거나 수정하는 것들의 명령어들을 DML이라고 부른다.
데이터 정의어 (DDL : Data Definition Language)	CREATE ALTER DROP RENAME	테이블과 같은 데이터 구조를 정의하는데 사용되는 명령어들로 그러한 구조를 생성하거나 변경하거나 삭제하거나 이름을 바꾸는 데이터 구조와 관련된 명령어들을 DDL이라고 부른다.
데이터 제어어 (DCL : Data Control Language)	GRANT REVOKE	데이터베이스에 접근하고 객체들을 사용하도록 권한을 주고 회수하는 명령어를 DCL이라고 부른다.
트랜잭션 제어어 (TCL : Transaction Control Language)	COMMIT ROLLBACK	논리적인 작업의 단위를 묶어서 DML에 의해 조작된 결과를 작업단위(트랜잭션) 별로 제어하는 명령어를 말한다.

 

---

데이터 조작어(DML)

As-Is : 비절차적 데이터 조작어(DML)는 사용자가 무슨(What) 데이터를 원하는 지만을 명세함.
To-Be : 비절차적 데이터 조작어(DML)는 사용자가 무슨(What) 데이터를 원하는 지만을 명세하지만, 절차적 데이터 조작어는 어떻게 (How) 데이터를 접근해야 하는지 명세 한다. 절차적 데이터 조작어로는 PL/SQL(오라클), T-SQL(SQL Server 등이 있다.)
+) 호스트 프로그램 속에 삽입되어 사용되는 DML 명령어들을 데이터 부속어(Data Sub Language)라고 한다.

---

 

PK 제약조건 추가

ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건명 PRIMARY KEY (PK컬럼명);

테이블 칼럼에 대한 정의 변경

• [Oracle] ALTER TABLE 테이블명 MODIFY (칼럼명1 데이터 유형 [DEFAULT 식] [NOT NULL], 칼럼명2 데이터 유형 ...);
• [SQL Server] ALTER TABLE 테이블명 (칼럼명1 데이터 유형 [DEFAULT 식] [NOT NULL], 칼럼명2 데이터 유형...);

+) SQL Server에서는 여러개의 칼럼을 동시에 수정하는 구문은 지원하지 않는다.
+) SQL Server에서는 괄호를 사용하지 않는다.

---

 

제약조건의 종류

• PRIMARY KEY(기본키)
• UNIQUE KEY(고유키) - NULL 입력도 가능
• NOT NULL
• CHECK
• FOREIGN KEY(외래키)

 

---

 

테이블 생성의 주의사항

• 테이블명은 객체를 의미할 수 있는 적절한 이름을 사용한다. 가능한 단수형을 권고한다.
• 테이블 명은 다른 테이블의 이름과 중복되지 않아야 한다.
• 한 테이블 내에서는 칼럼명이 중복되게 지정될 수 없다.
• 테이블 이름을 지정하고 각 컬럼들은 괄호 "( )"로 묶어 지정한다.
• 각 컬럼들은 콤마 ","로 구분되고, 테이블 생성문의 끝은 항상 세미콜롬 ";"으로 끝난다.
• 칼럼에 대해서는 다른 테이블까지 고려하여 데이터베이스 내에서는 일관성 있게 사용하는 것이 좋다.
• 칼럼 뒤에 데이터 유형은 꼭 지정되어야 한다.
• 테이블명과 칼럼명은 반드시 문자로 시작해야 하고, 벤더별로 기리에 대한 한계가 있다.
• 벤더에서 사전에 정의한 예약어(Reserved word)는 쓸 수 없다.
• A-Z, a-z, 0-9, _, $, # 문자만 허용된다.

 

---

 

DDL로 칼럼 삭제 시 활용되는 문장

• ALTER TABLE 테이블명 DROP COLUMN 칼럼명;

 

테이블 이름 변경(ANSI 표준 기준, 오라클과 동일)

RENAME 변경전_테이블명 TO 변경후_테이블명

---

 

참조 동작(Referential Action)

- Delete(/Modify) Action : Cascade, Set Null, Set Default, Restrict (부서 - 사원)

1. Cascade : Master 삭제 시 Child 같이 삭제
2. Set Null : Master 삭제 시 Child 해당 필드 Null
3. Set Default : Master 삭제 시 Child 해당 필드 Default 값으로 설정
4. Restrict : Child 테이블에 PK 값이 없는 경우만 Master 삭제 허용
5. No Action : 참조무결성을 위반하는 삭제/수정 액션을 취하지 않음

- Insert Action : Automatic, Set Null, Set Default, Dependent (부서 - 사원)

1. Automatic : Master 테이블에 PK가 없는 경우 Master PK를 생성 후 Child 입력
2. Set Null : Master 테이블에 PK가 없는 경우 Child 외부키를 Null 값으로 처리
3. Set Default : Master 테이블에 PK가 없는 경우 Child 외부키를 지정된 기본값으로 입력
4. Dependent : Master 테이블에 PK가 존재할 때만 Child 입력 허용
5. No Action : 참조무결성을 위반하는 입력 액션을 취하지 않음

 

---

 

테이블에 데이터를 입력하는 두 가지 유형

• INSERT INTO 테이블명 (COLUMN_LIST) VALUES (COLUMN_LIST에 넣을 VALUE_LIST);
• INSERT INTO 테이블명 VALUES (전체 COLUMN에 넣을 VALUE_LIST);

 

입력된 데이터의 수정

• UPDATE 테이블명 SET 수정되어야 할 칼럼명 = 수정되기를 원하는 새로운 값;

---

 

DROP vs TRUNCATE vs DELETE

DROP	TRUNCATE	DELETE
DDL	DDL (일부 DML 성격 가짐)	DML
Rollback 불가능	Rollback 불가능	Commit 이전 Rollback 가능
Auto Commit	Auto Commit	사용자 Commit
테이블이 사용했던 Storage를 모두 Release	테이블이 사용했던 Storage중 최초 테이블 생성시 할당된 Storage만 남기고 Release	데이터를 모두 Delete해도 사용했던 Storage는 Release되지 않음
테이블의 정의 자체를 완전히 삭제함	테이블을 최초 생성된 초기상태로 만듬	데이터만 삭제

• DROP : 테이블의 데이터를 모두 삭제하고 디스크 사용량도 없애며(초기화) 테이블의 스키마 정의도 함께 삭제
• TRUNCATE : 특정 테이블의 모든 데이터를 삭제하고, 디스크 사용량을 초기화
• DELETE : 테이블의 데이터를 모두 삭제하지만, 디스크 사용량을 초기화 하지는 않는다.

 

---

 

트랜잭션의 특성

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

 

---

 

트랜잭션에 대한 격리성이 낮은 경우 발생할 수 있는 문제점

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

 

---

트랜잭션(Transaction)은 데이터베이스의 논리적 연산단위로서 밀접히 관련되어 분리될 수 없는 한 개 이상의 데이터베이스 조작을 가리킨다.
트랜잭션의 종료를 위한 대표적 명령어로서는 데이터에 대한 변경사항을 데이터베이스에 영구적으로 반영하는 커밋(Commit)과 데이터에 대한 변경사항을 모두 폐기하고 변경전의 상태로 되돌리는 롤백(Rollback)이 있다.

BEGIN TRANSACTION (BEGIN TRAN 구문도 가능)으로 트랜잭션을 시작하고 COMMIT TRANSACTION(TRANSACTION은 생략 가능) 또는 ROLLBACK TRANSACTION(TRANSACTION은 생략 가능)으로 트랜잭션을 종료한다.
ROLLBACK 구문을 만나면 최초의 BEGIN TRANSACTION 시점까지 모두 ROLLBACK이 수행된다.

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

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

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

---

 

연산자의 우선순위

1. 괄호로 묶은 연산
2. 부정 연산자(NOT)
3. 비교 연산자(=, >=, <, <=)와 SQL 비교 연산자(BETWEEN a AND b, IN (list), LIKE, IS NULL)
4. 논리 연산자 중 AND, OR의 순으로 처리

부정 비교 연산자

• != : 같지 않다.
• ^= : 같지 않다.
• <> : 같지 않다.(ISO 표주느 모든 운영체제에서 사용 가능)
• NOT 칼럼명 = : ~와 같지 않다.
• NOT 칼럼명 > : ~보다 크지 않다.

 

---

함수는 벤더에서 제공하는 함수인 내장함수(Built-in Function)와 사용자가 정의할 수 있는 함수(User Defined Function)로 나눌 수 있다.

내장함수는 다시 단일행 함수(Single-Row Function)와 다중행 함수(Multi-Row Function)로 나눌 수 있으며, 다중행 함수는 집계 함수(Aggregate Function), 그룹 함수(Group Function), 윈도우 함수(Window Function) 구분된다.

---

단일행 문자형 함수의 종류

문자형 함수	함수 설명
LOWER(문자열)	문자열의 알파벳 문자를 소문자로 바꾸어 준다.
UPPER(문자열)	문자열의 알파벳 문자를 대문자로 바꾸어 준다.
ASCII(문자)	문자나 숫자를 ASCII 코드 번호로 바꾸어 준다.
CHR/CHAR(ASCII번호)	ASCII 코드 번호를 문자나 숫자로 바꾸어 준다.
CONCAT (문자열1, 문자열2)	Oracle, My SQL에서 유효한 함수이며 문자열1과 문자열2를 연결한다. 합성 연산자 'll'(Oracle)나 '+'(SQL Server)와 동일하다.
SUBSTR/SUBSTRING(문자열, m[, n ])	문자열 중 m위치에서 n개의 문자 길이에 해당하는 문자를 돌려준다. n이 생략되면 마지막 문자까지이다.
LENGTH/LEN(문자열)	문자열의 개수를 숫자값으로 돌려준다.
LTRIM (문자열 [, 지정문자])	문자열의 첫 문자부터 확인해서 지정 문자가 나타나면 해당 문자를 제거한다. (지정 문자가 생략되면 공백 값이 디폴트) SQL Server에서는 LTRIM 함수에 지정문자를 사용할 수 없다. 즉, 공백만 제거할 수 있다.
RTRIM (문자열 [, 지정문자])	문자열의 마지막 문자부터 확인해서 지정 문자가 나타나는 동안 해당 문자를 제거한다. (지정 문자가 생략되면 공백 값이 디폴트) SQL Server에서는 LTRIM 함수에 지정문자를 사용할 수 없다. 즉, 공백만 제거할 수 있다.
TRIM ([leading l trailing l both] 지정문자 FROM 문자열)	문자열에서 머리말, 꼬리말, 또는 양쪽에 있는 지정 문자를 제거한다. (leading l trailing l both 가 생략되면 both가 디폴트) SQL Server에서는 TRIM 함수에 지정문자를 사용할 수 없다. 즉, 공백만 제거할 수 있다.

+) CHR(10) → 줄바꿈
+) REPLACE : 문자열을 치환하는 함수 (REPLACE(C1, CHR(10)) → 줄바꿈 제거

---

 

단일행 함수의 종류

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

 

---

 

단일행 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을 리턴한다.

 

---

 

집계 함수의 종류

집계 함수	사용 목적
COUNT(*)	NULL 값을 포함한 행의 수를 출력한다.
COUNT(표현식)	표현식의 값이 NULL 값인 것을 제외한 행의 수를 출력한다.
SUM([DISTINCT | ALL] 표현식)	표현식의 NULL 값을 제외한 합계를 출력한다.
AVG([DISTINCT | ALL] 표현식)	표현식의 NULL 값을 제외한 평균을 출력한다.
MAX([DISTINCT | ALL] 표현식)	표현식의 최대값을 출력한다. (문자, 날짜 데이터 타입도 사용가능)
MIN([DISTINCT | ALL] 표현식)	표현식의 최소값을 출력한다. (문자, 날짜 데이터 타입도 사용가능)
STDEV([DISTINCT | ALL] 표현식)	표현식의 표준 편차를 출력한다.
VARIAN([DISTINCT | ALL] 표현식)	표현식의 분산을 출력한다.
기타 통계 함수	벤더별로 다양한 통계식을 제공한다.

 

---

 

GROUP BY 절과 HAVING 절의 특성

• GROUP BY 절을 통해 소그룹별 기준을 정한 후, SELECT 절에 집계 함수를 사용한다.
• 집계 함수의 통계 정보는 NULL 값을 가진 행을 제외하고 수행한다.
• GROUP BY 절에서는 SELECT 절과는 달리 ALIAS 명을 사용할 수 없다.
• 집계 함수는 WHERE 절에는 올 수 없다.(집계 함수를 사용할 수 있는 GROUP BY 절보다 WHERE 절이 먼저 수행된다)
• WHERE 절은 전체 데이터를 GROUP으로 나누기 전에 행들을 미리 제거시킨다.
• HAVING 절은 GROUP BY 절의 기준 항목이나 소그룹의 집계 함수를 이용한 조건을 표시할 수 있다.
• GROUP BY 절에 의한 소그룹별로 만들어진 집계 데이터 중, HAVING 절에서 제한 조건을 두어 조건을 만족하는 내용만 출력한다.
• HAVING 절은 일반적으로 GROUP BY 절 뒤에 위치한다.

+) GROUP BY를 사용하지 않을 경우 ORDER BY 절에 SELECT 절에 기술되지 않은 칼럼으로도 정렬 가능(오라클은 행기반 DATABASE 이기에 데이터를 액세스할 때 행 전체 칼럼을 메모리에 로드한다.

+) GROUP BY를 사용할 경우 ORDER BY 절에 SELECT 절에 기술되지 않은 칼럼과 GROUP BY 표현식이 아닌 값은 기술될 수 없다. 하지만, SELECT 절의 표현식을 변수로 표현하면 ORDER BY에 기술할 수 있으므로 주의

 

---

ORDER BY 절 특징

• Oracle에서는 NULL 값을 가장 큰 값으로 간주하는 반면, SQL Server에서는 NULL 값을 가장 작은 값으로 간주한다.
• ORDER BY 절에 컬럼명 대신 Alias 명이나 컬럼 순서를 나타내는 정수를 혼용하여 사용할 수 있다.

 

---

 

SELECT 문장 실행 순서

1. FROM : 발췌 대상 테이블을 참조한다.
2. WHERE : 발췌 대상 데이터가 아닌 것은 제거한다.
3. GROUP BY : 행들을 소그룹화 한다.
4. HAVING : 그룹핑된 값의 조건에 맞는 것만을 출력한다.
5. SELECT : 데이터 값을 출력/계산한다.
6. ORDER BY : 데이터를 정렬한다.

 

---

 

TOP N 질의문

TOP N 질의문에서 N에 해당하는 값이 동일한 경우 함께 출력되도록 하는 WITH TIES 옵션을 ORDER BY 절과 함께 사용하여야 한다.

ex) SELECT TOP(2) WITH TIES ENAME, SAL FROM EMP ORDER BY SAL DESC;

 

---

 

EQUI JOIN 문장

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

 FROM 테이블1, 테이블2

WHERE 테이블1.칼럼명1 = 테이블2.칼럼명2;

→ WHERE 절에 JOIN 조건을 넣는다.

 

ANSI / ISO SQL 표준 EQUI JOIN 문장

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

 FROM 테이블1 INNER JOIN 테이블2

     ON 테이블1.칼럼명1 = 테이블2.칼럼명2;

→ ON 절에 JOIN 조건을 넣는다.

 

---

 

JOIN

• 일반적으로 JOIN은 PK와 FK 값의 연관성에 의해 성립한다. 하지만, 어떤 경우에는 이러한 PK, FK의 관계가 없어도 논리적인 값들의 연관만으로 JOIN이 성립 가능하다.
• DBMS 옵티마이저는 FROM 절에 나열된 테이블이 아무리 많아도 항상 2개의 테이블씩 짝을 지어 JOIN을 수행한다.
• EQUI JOIN은 JOIN에 관여하는 테이블 간의 칼럼 값들이 정확하게 일치하는 경우에 사용되는 방법이다.
• EQUI JOIN은 '=' 연산자에 의해서만 수행되며, 그 이외의 비교 연산자를 사용하는 경우에는 모두 Non EQUI JOIN이다.
• 대부분 Non EQUI JOIN을 수행할 수 있지만, 때로는 설계상의 이유로 수행이 불가능한 경우도 있다.