[SQLD] 2과목 1장 SQL 기본 정리

1. 관계형 데이터베이스 개요

- SQL

- 정규화 : 테이블을 분할하여 데이터의 정합성을 확보하고, 불필요한 중복을 줄이는 프로세스

- ERD : 구성요소-> 엔터티, 관계, 속성

2. DDL

- 데이터 정의 언어
- 데이터베이스 구조를 정의하고 조작하는 데 사용되는 SQL 명령어들을 의미한다.

 

[1] 데이터 유형

- CHARACTER : 고정 길이 문자열 (CHAR)

- VARCHAR : 가변 길이 문자열

- NUMBRIC : 숫자 정보 (Oracle : NUMBER / SQL Server : 10가지 이상)

- DATETIME : 날짜와 시각 정보 (Oracle : DATE, SQL Server : DATETIME)

 

[2] CREATE TABLE

- 테이블명, 칼럼명 : 문자로 시작, 길이의 한계 존재, 예약어 사용 불가, 특수문자는 '_', '$', '#'만 허용

- 대소문자를 구분하지 않음

- DATETIME 유형은 별도로 크기를 지정하지 않음

- 문자 데이터는 최대 길이 표시

 

[3] CONSTRAINT(제약조건)

- PRIMARY KEY : 기본키 정의(고유키 + NOT NULL)

- UNIQUE KEY : 고유키 정의(NULL 가능)

- NOT NULL

- CHECK : 범위 제한(논리식 지정)

- FOREIGN KEY : 참조 무결성 제약 옵션 선택 가능

* 참조 무결성 제약조건 : 데이터베이스의 정확성, 일관성을 보장하기 위해 저장, 삭제, 수정 등을 제약하기 위한 조건

 

[4] 테이블 구조 확인

- 오라클 : DESCRIBE 테이블 명; or DESC 테이블명;

- SQL Server : sp_helo 'dbo.테이블명'

 

[5] CTAS(Create Table As Select)

- SELECT 문장을 활용해 테이블 생성

- Oracle :  CREATE TABLE <NEW_TABLE_NAME> AS SELECT *; or CREATE TABLE NEW_TABLE AS SELECT C1, C2 FROM OLD_TABLE ;

- SQL Sever : SELECT * INFO <NEW_TABLE_NAME> FROM; or SELECT C1, C2 INTO NEW_TABLE FROM OLD_TABLE ;

- 칼럼 별로 데이터 유형을 재정의할 필요 없음. NOT NULL 제외한 제약조건 사라짐.

 

[6] ALTER TABLE

1) ADD/DROP COLUMN : 칼럼 추가/삭제

- 칼럼 위치 지정불가

- 삭제 후 최소 하나 이상의 칼럼이 존재해야 함, 복구 불가능

 

2) MODIFY COLUMN : 칼럼 수정

- 칼럼의 데이터 유형(NULL일 때 가능), 디폴트 값, NOT NULL 조건에 대해 변경 가능

- 칼럼 크기는 늘릴 수 있지만 줄이기 불가(아무 행도 없거나 NULL 값만 있으면 줄이기 가능)

 

3) RENAME COLUMN : ORACLE 등 일부에서만 동작

 

4) ADD/DROP CONSTRAINT : 제약조건 추가/삭제

- FK조건 설정

 

[7] RENAME TABLE

- SQL Server: sp_rename을 이용해 테이블명 변경

 

[8] DROP TABLE

- CASCADE CONSTRAINT : 참조되는 제약조건에 대해서도 삭제

- SQL Server에서는 CASCADE 옵션이 존재하지 않으며, 삭제 전 참조하는 조건이나 테이블 우선 삭제

 

[9] TRUNCATE TABLE

- 테이블 자체가 아닌 모든 행 제거, 저장 공간을 재사용하도록 해제

- auto commit(복구 불가능)

 

3. DML

- 데이터 조작 언어
- 데이터를 삽입하거나 수정하거나 삭제하거나 조회할 수 있다.

- SELECT, INSERT, UPDATE, DELETE : 트랜젝션 처리를 별도로 해야 함

- SELECT : 데이터 조회 / DISTINCT : 중복제거

- 연산자 : 합성연산자 - '||', CONCAT(A, B)

 

4. TCL

[1] 트랜잭션 : 데이터베이스의 논리적 연산 단위(DML)

 

[2] 특징

- 원자성 : 트랜잭션에서 정의된 연산은 모두 성공하던지 아니면 전혀 실행되지 않아야 한다.(ALL OR NOTHING)

- 일관성 : 트랜잭션이 실행되지 전 데이터베이스에 이상이 없다면 실행 후에도 이상이 없어야 한다.

- 고립성 : 트랜잭션이 실행되는 도중에 다른 트랜잭션의 영향을 받아선 안된다.

- 지속성 : 트랜잭션이 성공적으로 수행되면 영구적으로 저장된다.

 

[3] COMMIT : 변경 작업 완료, 데이터베이스에 반영

- 변경된 행은 잠금(LOCKING)이 설정되어 다른 사용자가 변경할 수 없음 -> 데이터베이스에 반영 시 잠금이 풀려 조작 가능

- SQL Server : 기본적으로 AUTO COMMIT 모드

 

[4] ROLLBACK : COMMIT 이전의 변경 사항 취소

* COMMIT, ROLLBACK 효과?

1. 데이터 무결성 보장

2. 영구적 변경 전 데이터 변경 사항 확인 가능

3. 논리적으로 연관된 작업을 그룹핑해 처리 가능

 

[5] SAVEPOINT : 현시점에서 저장점까지 트랜젝션 일부만 롤백

5. WHERE 절

- 조인 조건이나 결과 제한을 위한 조건 기술

- FTS(Full Table Scan) : 조건이 없는 문장, 튜닝의 1차적 검토대상

- 비교 연산자, SQL 연산자, 논리 연산자

* 우선순위 : () -> not -> 비교 -> and -> or

- ROWNUM : 임시 일련번호, 고유한 키나 인덱스 값 생성가능

- TOP 절 : 출력되는 행의 수 제한 가능

* SELECT TOP(1) : 맨 위 한 건만

* SELECT TOP(N) : 맨 위 N건만

* SELECT TOP(N) PERCENT : 맨 위 N%만 반환

* SELECT TOP(N) PERCENT WITH TIES ... ORDER BY : N% 마지막 행과 값이 같은 행을 추가적으로 같이 출력

 

6. 함수

[1] 문자형 함수

[2] 숫자형 함수

[3] 날짜형 함수

[4] 변환형 함수

1. 명시적 데이터유형 변환 : 데이터 변환형 함수로 데이터 유형을 변환하도록 명시

2. 암시적 데이터유형 변환 : 데이터베이스가 자동으로 데이터 유형 변환, 성능저하나 에러 발생가능성이 있기 때문에 명시적 데이터 유형 변환을 사용하는 것을 추천.

[5] CASE

SELECT CASE 칼럼명 
		WHEN 조건1 THEN 조건2
                ...............
                ELSE 결과 N
FROM 테이블명;

[6] NULL 관련 함수

1) NVL/ISNULL(칼럼명, 결과)

 

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

- 표현식 1 = 표현식 2 -> NULL RETURN

- 표현식 1 != 표현식 2 -> 표현식 1 RETURN

 

3) COALESCE(표현식 1, 2, 3,...)

- NULL이 아닌 최초의 표현식 리턴, 모두 NULL 이면 NULL 리턴

 

4) NULL 함수와 공집합

cf) 결과가 공집합일 때 무엇인가 출력하고 싶으면 집계함수 이용

SELECT NVL(MAX(칼럼명), '출력')
FROM ...

7. GROUP BY, HAVING 절

[1] 집계함수

- 집계함수명([DISTINCT | ALL] 칼럼명)

- WHERE절 뒤에 집계함수 사용

 

1. COUNT(*) : NULL 포함 COUNT(칼럼) : NULL 제외

2. SUM(칼럼) : NULL 제외

3. AVG(칼럼) : NULL 제외

4. MAX(칼럼) : NULL 제외 MIN(칼럼) : NULL 제외

5. STDDEV(칼럼) : 표준편차

6. VARIAN(칼럼) : 분산

 

- GROUP BY, HAVING

SELECT DISTINCT 칼럼명 ALIAS명 (GROUP BY에서 쓴 칼럼만 쓸 수 있음)
FROM 테이블명
WHERE 조건식
GROUP BY 칼럼이나 표현식
HAVING 그룹조건식;

- 집계함수와 NULL

1. 다중행 함수에 NVL함수를 사용하면 부하 발생 -> 굳이 사용할 필요가 없다

2. SUM(NVL(SAL, O)) -> X

3. NVL(SUM(SAL), O) -> O

 

8. ORDER BY

SELECT 칼럼명 ALIAS명
FROM 테이블명
WHERE 조건식
GROUP BY 칼럼이나 표현식
HAVING 그룹조건식
ORDER BY 칼럼이나 표현식 [ASC 또는 DESC];

ASC : 오름차순 정렬(기본 값)
DESC : 내림차순 정렬

- ORDER BY절에 칼럼명 대신 SELECT절에서 사용한 ALIAS명이나 칼럼 순서를 나타내는 정수도 사용가능.

 

9. JOIN

[1] EQUI JOIN : 두 개의 테이블 간에 칼럼 값들이 정확하게 일치하는 경우(INNER JOIN)

[2] NON EQUI JOIN

SELECT EMPNO
FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO

SELECT EMPNO
FROM EMP, INNER JOIN DEPT
ON EMP.DEPTNO = DEPT.DEPTNO

SELECT EMPNO
FROM EMP JOIN DEPT
ON EMP.DEPTNO = DEPT.DEPTNO

3. TABLE JOIN

SELECT P.PLAYER_NAME 선수명,
 P.POSITION 포지션,
 T.REGION_NAME 연고지,
 T.TEAM_NAME 팀명,
 S.STADIUM_NAME 구장명
FROM PLAYER P, TEAM T, STADIUM S
WHERE P.TEAM_ID = T.TEAM_ID
	AND T.STADIUM_ID = S.STADIUM_ID
ORDER BY 선수명;

SELECT P.PLAYER_NAME 선수명,
 P.POSITION 포지션,
 T.REGION_NAME 연고지,
 T.TEAM_NAME 팀명,
 S.STADIUM_NAME 구장명
FROM PLAYER P INNER JOIN TEAM T ON P.TEAM_ID = T.TEAM_ID INNER JOIN STADIUM S ON T.STADIUM_ID = S.STADIUM_ID
ORDER BY 선수명;

4. NATURAL INNER JOIN

- 테이블 간 동일한 칼럼명을 갖는 모든 칼럼들을 EQUI JOIN

- 기준이 되는 칼럼을 하나로 처리하고 첫 번째로 출력

- 조인에 이용되는 칼럼은 명시하지 않아도 자동으로 조인에 사용된다.
- 동일한 이름을 갖는 칼럼이 있지만 데이터 타입이 다르면 에러가 발생한다.
- 조인하는 테이블 간의 동일 칼럼이 Select 절에 기술되어도 테이블 이름을 생략해야 한다.
- ON 절이나 USING 절을 사용하지 않는다.

SELECT department_id 부서, department_name 부서이름, location_id 지역번호, city 도시 FROM departments
NATURAL JOIN locations
WHERE city = 'Seattle';

 

- 사용불가 예제

SELECT EMP.DEPTNO, EMPNO, ENAME, DNAME
FROM EMP NATURAL JOIN DEPT;

SELECT ... 
FROM ... NATURAL JOIN
ON | USING

SELECT DEPTNO DEP
FROM ... NATURAL JOIN...
WHERE 조인조건절

5. CROSS JOIN

 

- 양쪽집합의 N*N건의 데이터 조합발생

cf. NATURAL JOIN과 다르게 WHERE절에 조인가능 그러나 이는 INNER JOIN과 같기 때문에 CROSS JOIN을 사용하는 의미가 없다.

 

6. OUTER JOIN

1) LEFT JOIN

2) RIGHT JOIN

3) FULL(OUTER) JOIN

SELECT L.DEPTNO, L.DNAME, L.LOC, R.DEPTNO, R.DNAME, R.LOC 
FROM DEPT L LEFT OUTER JOIN DEPT_TEMP R 
ON L.DEPTNO = R.DEPTNO

UNION

SELECT L.DEPTNO, L.DNAME, L.LOC, R.DEPTNO, R.DNAME, R.LOC 
FROM DEPT L RIGHT OUTER JOIN DEPT_TEMP R 
ON L.DEPTNO = R.DEPTNO

7. USING ON

1) USING(칼럼)

- 기준이 되는 칼럼을 하나로 처리, 첫 번째로 출력

 

2) ON(조건식)

- 괄호는 선택사항

 

- Using절은 조인에 사용될 칼럼을 지정한다. 
- Natural 절과 Using절은 함께 사용할 수 없다.
- 조인에 이용되지 않는 동일 이름을 가진 칼럼은 칼럼명 앞에 테이블명을 기술한다.
- 조인 칼럼은 괄호로 묶어서 기술해야 한다.
- 공통 칼럼의 이름이 같지 않아도 되며, 여러 개의 칼럼을 기준으로 조인할 수 있다.
- Join 대상 테이블에서 중복된 칼럼이 있을 경우, SELECT 절에서 명시하지 않는 한 각 칼럼을 한 번만 표시한다.

SELECT department_id 부서, department_name 부서이름, location_id 지역번호, city 도시 FROM departments
JOIN locations USING (location_id);