Study/Database

[SQLP] 과목 2. SQL 기본 및 활용 - (1) SQL 기본

옥이b. 2021. 12. 28. 00:06

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

1. 데이터베이스

 - 데이터를 일정한 형태로 저장해 놓은 것

 - DBMS : 데이터의 손상을 피하고 필요시 데이터를 복구하기 위한 기능의 소프트웨어

  1) 데이터베이스의 발전

1960년대 플로우 차트 중심의 개발
1970년대 계층형 데이터베이스, 망형 데이터베이스
1980년대 관계형 데이터베이스 상용화 (Oracle, Sybase, DB2 등)
1990년대 객체 관계형 데이터베이스로 발전

  2) 관계형 데이터베이스

    • 이상 현상 제거

    • 데이터 중복 방지

    • 동시성 관리

    • 병행 제어

    • 메타 데이터 총괄 관리를 통한 데이터 표준화 가능

    • 갑작스런 장애 시 데이터가 제대로 반영될 수 있도록 보장

    • 시스템 다운, 재해 등의 상황에서도 데이터를 복구할 수 있는 기능 제공

2. SQL (Structured Query Language)

 - 관계형 데이터베이스에서 데이터 정의, 데이터 조작, 데이터 제어를 하기 위해 사용하는 언어

 - SQL 교육 : 정확한 데이터를 출력하는 것이 목표

 - SQL 튜닝 : 시스템에 영향을 주는 SQL 을 효과적(응답시간, 자원 활용 최소화)으로 작성하는 것이 목표

 - ANSI/ISO 표준을 따름

 - 데이터를 집합으로 취급

명령어 종류 명령어 설명
데이터 조작어 DML
(Data Manipulation Language)		 SELECT 데이터를 조회하거나 검색 (RETRIEVE)
INSERT
UPDATE
DELETE		 데이터에 변형을 가하는 종류의 명령어
데이터 정의어 DDL
(Data Definition Language)		 CREATE
ALTER
DROP
RENAME		 테이블과 같은 구조를 정의하는데 사용되는 명령어
데이터 제어어 DCL
(Data Control Language)		 GRANT
REVOKE		 DB에 접근하고 권한을 관리하는 명령어
트랜잭션 제어어 TCL
(Transaction Control Language)		 COMMIT
ROLLBACK		 DML에 의해 조작된 결과를 트랜잭션 별로 제어하는 명령어

3. TABLE

 - 데이터가 저장되는 기본 형태

 - 어느 특정한 주제와 목적으로 만들어지는 일종의 집합

 - 데이터를 저장하는 객체

 - 관계형 데이터베이스의 기본 단위

 - 정규화 : 테이블을 분할하여 데이터의 불필요한 중복을 줄이는 것 (데이터 정합성 확보, 이상현상 방지)

 - 기본키(PK) : 각 행을 한 가지 의미로 특정할 수 있는 한 개 이상의 컬럼

 - 외부키(FK) : 다른 테이블의 기본키로 사용되면서 테이블과의 관계를 연결하는 컬럼

4. ERD (Entity Relationship Diagram)

 - 테이블 간 서로의 상관 관계를 그림으로 도식화한 것

 - 엔터티, 관계, 속성 으로 표현

 - IE 표기법, Barker 표기법 등

 

Part 2. DDL

1. 데이터 유형

유형 설명 ORACLE
CHARACTER(s) 고정 길이 문자열 정보, 공백으로 채움 최대 2,000 byte
VARCHAR(s) 가변 길이 문자열 정보, 공백으로 채우지 않음 최대 4,000 byte
NUMERIC 정수, 실수 등 숫자 정보  
DATETIME 날짜와 시간 정보 1초 단위

 - CHAR 의 경우, 'AA' = 'AA ' (공백은 무시)

 - VARCHAR 의 경우, 'AA' != 'AA '

2. CREATE TABLE

가. 테이블 생성

CREATE TABLE TABLE_NAME (
    COLUMN_NAME DATATYPE [DEFAULT],
    ...,
    CONSTRAINT PK_NAME PRIMARY KEY ( COLUMN_NAME, ... ),
    CONSTRAINT FK_NAME FOREIGN KEY ( COLUMN_NAME, ... )
                       REFERENCES REF_TABLE ( COLUMN_NAME )
);

 - 테이블명은 적절한 단수명 사용

 - 다른 테이블 이름과 중복되지 않아야 함

 - 한 테이블 내에서는 컬럼명 중복 안됨

 - 컬럼에 대해서, 다른 테이블까지 고려하여 일관성 있게 사용 (데이터 표준화 관점)

 - 데이터 타입은 꼭 지정되어야 함

 - 테이블명, 컬럼명은 반드시 문자로 시작, 길이 제한 있음

 - 벤더에서 사전 정의한 예약어는 사용 불가

 - A-Z, a-z, 0-9, _, $, # 만 허용

 - 대소문자 구분 없음

 - DATETIME 에는 크기 지정 안함

 - 문자 데이터는 최대 길이 지정

 - 제약조건은 CONSTRAINT 를 이용하여 추가

나. 제약조건

 - 사용자가 원하는 조건의 데이터만 유지하기 위한 제약

 - 데이터 무결성을 유지하기 위한 방법

 - 테이블의 특정 컬럼에 설정하는 제약

구분 설명
기본키 PRIMARY KEY 하나의 테이블에 하나의 기본키 제약만 가능, 자동으로 UNIQUE INDEX 생성, NULLABLE
고유키 UNIQUE KEY 고유하게 식별하기 위한 고유키 정의, NULL 허용
NOT NULL NULL 입력 금지
CHECK 값의 범위 제한, TRUE or FALSE 형태의 논리식 지정
외래키 FOREIGN KEY 기본키를 다른 테이블의 외래키로 복사하는 경우, 참조무결성 제약 옵션 선택 가능

다. 생성된 테이블 구조 확인

DESC TABLE_NAME;
DESCRIBE TABLE_NAME;

라. SELECT 문장을 통한 테이블 생성 사례

 - CTAS 기법 ( CREATE TABLE ~ AS SELECT ~ )

 - 컬럼별로 데이터 유형을 다시 재정의 하지 않아도 됨

 - NOT NULL 만 복제됨 (기본키, 고유키, 외래키, CHECK 등은 별도 지정 필요)

[ORACLE]
CREATE TABLE NEW_TABLE_NAME
AS SELECT * FROM OLD_TABLE_NAME;

[SQL SERVER]
SELECT * INTO NEW_TABLE_NAME
FROM OLD_TABLE_NAME;

3. ALTER TABLE

가. ADD COLUMN

ALTER TABLE TABLE_NAME
ADD ( COLUMN_NAME DATATYPE, ... );

 - 마지막 컬럼으로 추가됨

나. DROP COLUMN

ALTER TABLE TABLE_NAME
DROP COLUMN COLUMN_NAME;

 

 - 한 번에 하나의 컬럼만 삭제 가능

 - 데이터가 있거나 없거나 모두 삭제 가능

 - 삭제된 컬럼은 복구 불가능

 - 삭제 후 하나 이상의 컬럼이 테이블에 남아있어야 함

다. MODIFY COLUMN

ALTER TABLE TABLE_NAME
MODIFY ( COLUMN_NAME DATATYPE [DEFAULT] [NOT NULL], ... );

 - 컬럼의 크기를 늘릴수는 있지만 줄이지는 못함 (기존 데이터 훼손 방지)

 - NULL 값만 가지고 있는 경우 크기를 줄일 수 있음

 - NULL 값만 가지고 있는 경우 데이터유형을 변경할 수 있음

 - DEFAULT 값을 변경하는 경우, 변경시점 이후의 데이터부터 적용됨

 - NULL 값이 없는 경우에만 NOT NULL 추가 가능

라. RENAME COLUMN

ALTER TABLE TABLE_NAME
RENAME COLUMN OLD_COLUMN_NAME TO NEW_COLUMN_NAME;

 - 해당 컬럼과 관계된 제약조건에 대해서도 자동으로 변경 (일부 DBMS에서만 지원)

마. DROP CONSTRAINT

ALTER TABLE TABLE_NAME
DROP CONSTRAINT CONSTRAINT_NAME;

바. ADD CONSTRAINT

ALTER TABLE TABLE_NAME
ADD CONSTRAINT CONSTRAINT_NAME CONSTRAINT_DETAIL ( COLUMN_NAME );

4. RENAME TABLE

RENAME OLD_TABLE_NAME TO NEW_TABLE_NAME;

5. DROP TABLE

DROP TABLE TABLE_NAME [CASCADE CONSTRAINT];

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

6. TRUNCATE TABLE

TRUNCATE TABLE TABLE_NAME;

 - 테이블 자체는 삭제되지 않음

 - 모든 행들이 제거

 - 저장공간을 재사용 가능하도록 해제

 - 내부처리 방식 및 Auto Commit 특성 등으로 인해 DDL 로 분류

 - 테이블의 전체 데이터를 삭제할 때는 시스템 부하가 적은 TRUNCATE  권고

 - 단, 복구 불가능

 

Part 3. DML

1. INSERT

INSERT INTO TABLE_NAME ( COLUMN_LIST )
VALUES ( VALUE_LIST );

INSERT INTO TABLE_NAME
VALUES ( 전체 COLUMN VALUE_LIST );

 - 한 번에 한 건만 입력

 - 정의하지 않은 컬럼은 NULL 로 입력됨 (PK, NOT NULL 인 경우 오류 발생)

 - 순서대로 빠짐없이 데이터가 입력되어야 함

2. UPDATE

UPDATE TABLE_NAME
SET COLUMN_NAME = NEW_VALUE, ...
WHERE 조건;

3. DELETE

DELETE FROM TABLE_NAME
WHERE 조건;

 - DML의 경우, 메모리 버퍼에 올려놓고 작업함 → 실시간 반영 X

4. SELECT

SELECT [DISTINCT] COLUMN_NAME, ...
FROM TABLE_NAME
WHERE 조건;

 - DISTINCT : 중복된 데이터가 있는 경우, 1건으로 처리해서 출력 (group by 랑 성능상 큰 차이 없음)

 - * (wildcard) : 모든 컬럼 정보

 - Alias 부여 가능

5. 산술 연산자와 합성 연산자

  1) 산술 연산자

연산자 설명
( ) 연산자 우선순위를 변경하기 위한 괄호 (괄호 안의 연산이 우선)
* 곱하기
/ 나누기
+ 더하기
- 빼기

  2) 합성 연산자

    - 컬럼과 컬럼을 연결하여 새로운 컬럼 생성

    - || (ORACLE), + (SQL Server), CONCAT

 

Part 4. TCL

1. 트랜잭션 개요

 - 데이터베이스의 논리적 연산단위

 - 밀접히 관련되어 분리될 수 없는 한 개 이상의 데이터베이스 조작

 - 하나의 트랜잭션에는 하나 이상의 SQL 문장이 포함됨

 - 분할할 수 없는 최소 단위

 - 전부 적용하거나 전부 취소

 - 하나의 논리적인 작업 단위를 구성하는 세부적인 연산들의 집합

명령어 설명
COMMIT 올바르게 반영된 데이터를 데이터베이스에 반영시키는 작업
ROLLBACK 트랜잭션 시작 이전의 상태로 되돌리는 작업
SAVEPOINT 롤백 지점 저장
특성 설명
원자성 Automicity 모두 성공적으로 실행되던지 전혀 실행되지 않은 상태로 남아있어야 함 (All or Nothing)
일관성 Consistency 트랜잭션이 실행되기 전 내용이 잘못되어 있지 않다면, 실행 이후에도 잘못되어 있으면 안됨
고립성 Isolation 다른 트랜잭션의 영향을 받아 잘못된 결과를 만들어서는 안됨
지속성 Durability 트랜잭션이 갱신한 데이터베이스의 내용은 영구적으로 저장

2. COMMIT

 - 트랜잭션을 완료

 - COMMIT, ROLLBACK 데이터 상태

COMMIT, ROLLBACK 전 COMMIT 이후
- 데이터 변경 이전 상태로 복구 가능
- 현재 사용자는 SELECT 문장으로 결과 확인 가능
- 다른 사용자는 현재 사용자가 수행한 명령의 결과를 볼 수 없음
- 변경된 행은 LOCKING되어 다른 사용자가 변경 불가		 - 변경사항이 DB 에 반영됨
- 이전 데이터는 영원히 잃어버림
- 모든 사용자가 결과 볼 수 있음
- LOCKING 해제, 다른 사용자가 행 조작 가능

 - SQL Server 의 경우,

  1) AUTO COMMIT (default) :  DML, DDL 수행시마다 자동 COMMIT, ROLLBACK

  2) 암시적 트랜잭션 : ORACLE 과 같은 방식

  3) 명시적 트랜잭션 : 트랜잭션의 시작과 끝을 모두 사용자가 정함

3. ROLLBACK

 - 데이터 변경사항이 취소됨

 - 이전 데이터 상태로 복구

 - LOCKING 해제, 다른 사용자가 행 조작 가능

COMMIT, ROLLBACK 사용 효과?
- 데이터 무결성 보장
- 영구적 변경 전 데이터 변경사항 확인 가능
- 논리적으로 연관된 작업을 그룹핑하여 처리

4. SAVEPOINT

 - ROLLBACK 시 SAVEPOINT 지정하면, 해당 시점까지만 롤백 가능

SAVEPOINT POINT_NAME;
ROLLBACK POINT_NAME;

 - 단, 미래 방향으로 되돌릴 수는 없음

    ex) A → B → C 순서로 포인트 생성 후, A 로 롤백 시, B, C 에는 접근 불가

트랜잭션 특성?
- DDL 실행 후에는 자동 COMMIT (CREATE, ALTER, DROP, RENAME, TRAUNCATE TABLE 등)
- DML 실행 후 COMMIT 없이 DDL 이 실행되는 경우, 자동 COMMIT
- DB 를 정상적으로 접속 종료 → COMMIT
- DB 를 비정상적으로 접속 종료 → ROLLBACK

 

Part 5. WHERE 절

1. WHERE 조건절 개요

 - 조회하려는 데이터에 특정 조건을 부여할 목적

조건절 구성?
- 컬럼명
- 비교연산자
- 문자, 숫자, 표현식
- 비교 컬럼명 (JOIN 시)

2. 연산자의 종류

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

 - 우선순위

괄호( ) → NOT 연산자 → 비교 연산자, SQL 비교 연산자 → AND → OR

3. 비교 연산자

연산자 의미 부정연산자 의미
= 같다 !=, ^=, <>, NOT 컬럼명 = ~ 같지 않다, ~와 같지 않다
> 보다 크다 NOT 컬럼명 > ~ ~보다 크지 않다
>= 보다 크거나 같다    
< 보다 작다    
<= 보다 작거나 같다    

 - 문자 유형간의 비교

문자열1 문자열2 비교방법
CHAR(5) CHAR(3) - 문자열2(작은쪽)에 SPACE를 추가하여 비교
- 서로 다른 문자가 나올 때까지 비교
- 달라진 첫번째 문자의 값에 따라 크기 결정
- BLANK의 수만 다른 경우, 같은 값
VARCHAR(5) CHAR(3) - 서로 다른 문자가 나올 때까지 비교
- 문자열2(작은쪽)이 끝날 때까지만 비교 → 긴 것이 크다고 판단
- NOT NULL 까지의 길이
상수 문자형 - 문자열1을 문자열2 타입과 동일하게 변경

4. SQL 연산자

 - 기본적으로 예약이 되어 있는 연산자

연산자 의미 부정연산자 의미
BETWEEN A AND B A와 B 값 사이 (A, B 포함) NOT BETWEEN A AND B A와 B 사이에 있지 않음 (A, B 미포함)
IN ( LIST ) 리스트에 있는 값 중 하나라도
일치 (다중리스트 가능)		 NOT IN ( LIST ) 리스트에 있는 값과 일치하지 않음
LIKE '비교문자열' 비교 문자열과 형태 일치
 • % : 0개 이상의 문자
 • _ : 1개 단일 문자		    
IS NULL 값이 NULL 인 경우 IS NOT NULL 값이 NULL이 아닌 경우

5. 논리 연산자

 - 여러개의 조건들을 연결시키기 위해 사용되는 연산자

AND, OR, NOT

6. ROWNUM, TOP

 - SQL 처리 결과 집합의 각 행에 대해 임시로 부여되는 일련번호

 - WHERE 절에서 행의 개수를 제한하는 목적

[ORACLE]
SELECT ROWNUM, ...
FROM TABLE_NAME
WHERE ROWNUM < N;

[SQL Server]
SELECT TOP (반환행수) [PERCENT] [WITH TIES], ...
FROM TABLE_NAME;

 

Part 6. 함수 FUNCTION

 - 입력되는 값이 아무리 많아도 출력은 하나만 됨 (M:1 관계)

단일행 함수 특징?
- SELECT, WHERE, ORDER BY 절에 사용 가능
- 각 행들에 대해 개별적으로 작용
- 여러 인자를 입력해도 단 하나의 결과 만 리턴
- 인자로 상수, 변수, 표현식 가능
- 특별한 경우가 아니면 함수의 중첩이 가능

1. 문자형 함수

문자형 함수 설명
LOWER(문자열) 문자열의 알파벳을 소문자로 변경
UPPER(문자열) 문자열의 알파벳을 대문자로 변경
ASCII(문자) ASCII 코드 번호로 변경
CHR/CHAR(ASCII번호) 아스키코드 번호에 해당하는 문자로 변경
CONCANT(문자열1, 문자열2) 문자열 연결 ( ||, + 와 동일 )
SUBSTR/SUBSTRING(문자열, m [ , n ]) m 에서 n 개 문자
LENGTH/LEN(문자열) 문자열 개수
LTRIM(문자열, 지정문자) 왼쪽 끝에 지정문자가 있는 경우 제거 ( default. 공백 )
RTRIM(문자열, 지정문자) 오른쪽 끝에 지정문자가 있는 경우 제거 ( default. 공백 )
TRIM(지정문자 FROM 문자열) 양쪽에 있는 지정문자 제거 ( default. 공백 )

2. 숫자형 함수

숫자형 함수 설명
ABS(숫자) 숫자의 절대값
SIGN(숫자) 양수, 음수, 0 구분
MOD(숫자1, 숫자2) 숫자1을 숫자2로 나눈 나머지
CEIL/CEILING(숫자) 숫자보다 크거나 같은 최소 정수 리턴
FLOOR(숫자) 숫자보다 작거나 같은 최대 정수 리턴
ROUND(숫자 [ , m ]) 숫자를 소수 m번째 자리에서 반올림하여 리턴
TRUNC(숫자 [ , m ]) 숫자를 소수 m번째 자리에서 내림하여 리턴
SIN, COS, TAN, ... 삼각함수 결과 값 리턴
EXP(), POWER(), SQRT(). LOG(), LN() 지수, 거듭제곱, 제곱근, 자연로그 값 리턴

3. 날짜형 함수

날짜형 함수 설명
SYSDATE/GETDATE() 현재 날짜와 시각
EXTRACT/DATEPART(YEAR|MONTH|DAY FROM DATE) 날짜데이터에서 년, 월, 일 데이터 출력

 - 날짜형 데이터 연산

연산 결과 설명
날짜 + 숫자 날짜 숫자 만큼의 날수를 날짜에 더함
날짜 - 숫자 날짜 숫자 만큼의 날수를 날짜에서 뺌
날짜1 - 날짜2 날짜수 날짜 차이
날짜 + 숫자/24 날짜 시간을 날짜에 더함

4. 변환형 함수

종류 설명
명시적 데이터 유형 변환 데이터 유혀을 변환하도록 명시해주는 경우
암시적 데이터 유형 변환 자동으로 데이터 유형을 변환하여 계산하는 경우
변환형 함수 설명
TO_NUMBER 문자열을 숫자로 변환
TO_CHAR FORMAT 에 맞춰 문자열 타입으로 변환
TO_DATE FORMAT 형태로 날짜 데이터로 변환

5. CASE 표현

CASE COLUMN_NAME
    WHEN 조건 THEN 값
    ...
    ELSE 값
END

OR

CASE
    WHEN 조건 THEN 값
    ...
    ELSE 값
END

OR

DECODE(COLUMN_NAME, 조건1, 값1, ....., 기타)

6. NULL 관련 함수

일반형 함수 설명
NVL(표현식1, 표현식2)/ISNULL(표현식1, 표현식2) 표현식1이 NULL 인 경우 표현식2의 값 출력
NULLIF(표현식1, 표현식2) 표현식1이 표현식2와 같으면 NULL, 다르면 표현식1을 리턴
COALESCE(표현식1, 표현식2, ...) NULL이 아닌 최초의 표현식을 나타냄

 

Part 7. GROUP BY, HAVING 절

1. 집계 함수

 - GROUP BY 절은 행들을 소그룹화 함

 - SELECT 절, HAVING 절, ORDER BY 절에 사용

집계 함수 설명
COUNT(*) NULL 값을 포함한 행의 수
COUNT(표현식) NULL 값을 제외한 행의 수 출력
SUM(표현식) NULL 값을 제외한 합계 출력
AVG(표현식) NULL 값을 제외한 평균 출력
MAX(표현식) 최대값 출력
MIN(표현식) 최소값 출력
STDDEV(표현식) 표준편차 출력
VARIAN(표현식) 분산 출력
기타 통계 함수 통계식 제공

2. GROUP BY 절

 - 데이터들을 작은 그룹으로 분류하여 소그룹에 대한 항목별 통계 정보를 얻을 때 사용

 - GROUP BY 절 : 소그룹별 기준 정함 → SELECT 절 : 집계 함수 사용

 - ALIAS 명 사용 불가

 - GROUP BY 절보다 WHERE 절이 먼저 수행되므로, WHERE 절에는 집계함수 사용 불가

 - HAVING 절 : 집계 함수를 이용한 조건

3. HAVING 절

 - 그룹을 나타내는 결과 집합의 행에 조건이 적용됨

4. 집계함수와 NULL

 - CASE 표현에서 ELSE 절 생략 시 NULL → 연산의 대상이 아님

 - DECODE 의 네번째 인자 생략과 동일

 

Part 8. ORDER BY 절

1. ORDER BY 정렬

 - 특정 컬럼을 기준으로 정렬하여 출력하는데 사용

 - SELECT 절에 사용한 ALIAS 명 사용 가능

 - 컬럼 순서를 나타내는 정수도 사용 가능

 - DEFAULT 는 오름차순(ASC), 내림차순은 DESC
 - NULL : Oracle 에서는 가장 큰 값으로 처리, SQL Server 에서는 가장 작은 값으로 처리

2. SELECT 문장 실행 순서

5. SELECT 컬럼명 (ALIAS명)
1. FROM 테이블명
2. WHERE 조건식
3. GROUP BY 컬럼, 표현식
4. HAVING 그룹조건식
6. ORDER BY 컬럼, 표현식

 - 옵티마이저가 SQL 문장의 SYNTAX, SEMANTIC 에러를 점검하는 순서와 동일

3. TOP N

 - 우선순위가 높은 N 개의 열을 추출할 때

 - 인라인뷰에서 정렬 후 ROWNUM 적용 (Oracle)

 - SQL Server 에서는 TOP 으로 바로 추출

 

Part 9. JOIN

1. JOIN 개요

 - 두 개 이상의 테이블 들을 연결 또는 결합하여 데이터를 출력하는 것

 - 관계형 데이터베이스의 가장 큰 장점이면서 대표적인 기능

2. EQUI JOIN

 - 등가 조인

 - 두 개의 테이블 간에 컬럼 값들이 서로 정확하게 일치하는 경우

 - PK ↔ FK 관계 기반

 - JOIN 조건은 WHERE 절에 기술

SELECT TABLE1.COLUMN_NAME, TABLE2.COLUMN_NAME
FROM TABLE1, TABLE2
WHERE TABLE1.COLUMN_NAME = TABLE2.COLUMN_NAME

 - INNER JOIN 으로도 가능

SELECT TABLE1.COLUMN_NAME, TABLE2.COLUMN_NAME
FROM TABLE1 INNER JOIN TABLE2
    ON TABLE1.COLUMN_NAME = TABLE2.COLUMN_NAME;

3. NOT EQUI JOIN

 - 비등가 조인

 - 두 개의 테이블 간에 컬럼 값들이 서로 정확하게 일치하지 않는 경우

 - BETWEEN, >, >=, <, <= 등

SELECT TABLE1.COLUMN_NAME, TABLE2. COLUMN_NAME
FROM TABLE1, TABLE2
WHERE TABLE1.COLUMN_NAME BETWEEN TABLE2.COLUMN_NAME AND TABLE2.COLUMN_NAME;

4. 3개 이상 TABLE JOIN

 - FROM 절에 명시된 순서대로 JOIN

저작자표시 비영리 동일조건 (새창열림)