Search
🏫

[데이터베이스] 3. SQL

Created
2023/04/20 04:07
Tags
CS
Database
Last edited time
2023/06/17 06:25
Status
Done
Search
[데이터베이스] 10. 회복 시스템
CS
Database
2023/06/07 15:51
[데이터베이스] 10. 회복 시스템
CS
Database
2023/06/07 15:51

1. SQL 개요

1.1. SQL의 개요

SQL (Structured Query Language)은 관계 대수에 기초하여 RDBMS의 데이터 관리를 위해 설계된 언어

1.2. SQL의 구성

DDL (데이터 정의 언어)
CREATE, ALTER, DROP
DML (데이터 조작 언어)
INSERT, UPDATE, DELETE, SELECT

1.3. DDL(데이터 정의 언어)

객체 종류
데이터 저장: 테이블, 인덱스, 뷰
데이터 조작: 트리거, 프로시저, 함수 등
명령어
CREATE
ALTER
DROP
객체
TABLE
INDEX
VIEW
SCHEMA

1.4. 스키마, 테이블

스키마 정의
스키마 = 데이터베이스
한조직의 데이터베이스 시스템의 운영에 필요한 테이블, 인덱스, 뷰 등의 데이터베이스 객체의 집합
스키마 관리 방법
Forward Engineer
SQL에디터
내비게이터 패널
SQL
CREATE SCHEMA 스키마 이름 DROP SCHEMA 스키마 이름
SQL
복사

1.5. 데이터 타입

컬럼이 가질 수 있느 값의 범위, 즉 도메인을 결정
프로그래밍 언어에서의 변수를 생성하는 데이터 타입과 매우 유사
종류
설명
추가 예시
[정수]
TINYINT
나이, 학년 등 크기가 작은 정수
1바이트 정수 (-128~127)
SMALLINT
물품번호, 인원 등 중간 크기의 정수
2바이트 정수 (-32768~32767)
INT
물품 금액, 전화번호 등 일반 크기의 정수
4바이트 정수 (-20억~20억)
BIGINT
계좌 전고, 천문학적인 크기의 정수
8바이트 정수
[실수]
DECIMAL(M, N)
(고정 소수형) 전체 M자리, 소숫점 N 자리의 숫자를 표현
DECIMAL(5,2): -999.99~999.99
NEMERIC
(고정 소수형) DECIMAL과 동일
FLOAT
(부동 소수형) 4바이트 크기 부동 소수
FLOAT(P)
(부동 소수형) 소수점 이하 P개자리 부동 소수
DOUBLE
(부동 소수형) 8바이트 크기 부동 소수
[날짜 및 시간]
DATE
‘YYYY-MM-DD’
YEAR
‘YYYY’
TIME
‘HH:MI:SS’
DATETIME
‘YYYY-MM-DD HH-MI-SS’
TIMESTAMP
DATETIME과 동일
[문자]
CHAR(N)
최대길이 N인 고정길이 문자열
VARCHAR(N)
최대길이 N인 가변길이 문자열
TEXT, CLOB
길이가 최대 2~4GB 가변길이 문자열
ENUM
유한개의 문자열 집합 중 하나의 값을 선택
ENUM(’남’, ‘여’)

1.6. 테이블

생성
CREATE TABLE 테이블이름 ( [<컬럼1> <데이터타입1> [제약조건]], [<컬럼2> <데이터타입2> [제약조건]], [PRIMARY KEY 컬럼명] [UNIQUE 컬럼명] [FOREIGN KEY 컬럼 REFERENCES 테이블이름(컬럼)] }
SQL
복사
예시
수정
ALTER TABLE 테이블 이름 ADD COLUMN 컬럼 이름 데이터 타입 [제약 조건] DROP COLUMN 컬럼 이름 CHANGE COLUMN 수정전 컬럼 수정후 컬럼 MODIFY COLUMN 컬럼 데이터 타입
SQL
복사
예시
삭제
DROP TABLE 테이블 이름
SQL
복사

1.7. 제약 조건

테이블과 테이블에 존재하는 데이터를 보다 무결하게 관리하기 위한 목적으로 사용
종류
PRIMARY KEY: 기본키 지정, UNIQUE와 NOT NULL 특성
FOREIGN KEY
NOT NULL
UNIQUE
AUTO_INCREMENT
CHECK: 컬럼 값이 특정 조건 준수 여부 설정

2. 데이터 삽입, 수정, 삭제

2.1. 데이터 조작 언어

DML
Data Manipulation Language
정의된 테이블의 레코드를 CRUD 수행하는 명령어

2.2. INSERT문

INSERT INTO 테이블이름 VALUES (1,2, ... 값n) INSERT INTO 테이블이름(컬럼1, 컬럼2, ... 컬럼n) VALUES (1,2, ... 값n)
SQL
복사

2.3. UPDATE 문

UPDATE 테이블이름 SET 컬럼1=1, 컬럼2=값n, ... 컬럼n=값n WHERE 조건 UPDATE 테이블이름 SET 컬럼1=수식1, 컬럼2=수식n, ... 컬럼n=수식n WHERE 조건
SQL
복사

2.4. DELETE문

DELETE FROM 테이블이름 [WHERE 조건]
SQL
복사

2.5. SAFE UPDATES 모드

where절이 없는 UPDATE / DELETE 문은 테이블 전체 레코드의 변경 삭제를 유발
MySQL은 이를 위해 SAFE UPDATES 모드 지원
기본키가 아닌 컬럼을 대상으로 수정/삭제 조건을 명시할 경우 실행 여부 결정
SET SQL_SAFE_UPDATES = 0 or 1
SQL
복사

3. 데이터 검색

3.1. SELECT문

SELECT 컬럼1, 컬럼2, ... 컬럼n FROM 테이블이름 WHERE 조건 SELECT * FROM 테이블이름
SQL
복사

3.2. 연산자

산술 연산자
DIV
/
%, MOD
+
*
비교 연산자
=
<
<=
>
>=
!= or <>
논리 연산자
AND or &&
OR or ||
NOT or !

3.4. 특수 연산자

BETWEEN
LIKE
IN

3.3. 데이터 정렬

ASC
DESC

3.4. 함수

특정 기능을 목적을 수행하도록 사전에 정의된 연산 및 기능을 수행한 후 결과값을 반환하는 명령어 집합
종류
문자 함수
숫자 함수
날짜 및 시간 함수

3.5. 그룹 질의

집계 함수
특정 컬럼에 집계 함수를 통해 다양한 연산을 수행할 수 있는 기능
SELECT 절 또는 HAVING 절에 기술
COUNT, SUM, AVG, MAX, MIN
그룹 질의
SELECT 절에 그룹의 기준과 집계 함수 이외의 컬럼은 포함될 수 없음
SELECT 질의 GROUP BY 컬럼
SQL
복사
HAVING 절
그룹 질의의 결과 레코드에 대해 출력 조건을 기술하기 위한 절
SELECT 질의 GROUP BY 컬럼 HAVING 조건
SQL
복사

3.6. 중첩 질의

SELECT 문 내부에 독립적으로 실행가능한 또다른 SELECT문이 내포된 질의
일반적으로 내부 질의의 처리 결과를 외부 질의에서 재사용
중첩 질의의 종류
FROM 절에서 중첩 질의 사용
FROM 절에서의 결과 집합을 SELECT에서 재검색
WHERE 절에서 중첩 질의 사용
WHERE 절에서의 결과 집합을 활용하여 외부 질의에서 레코드의 출력 여부 결정
IN, NOT IN, EXISTS, NOT EXISTS 사용
중첩 질의의 형식
SELECT 컬럼1, 컬럼2, .. 컬럼n FROM (SELECT 컬럼1, 컬럼2, ... 컬럼 n FROM 테이블 WHERE 조건) WHERE 조건
SQL
복사

3.7. 조인 질의

테이블 간의 관련성을 이용하여 두개 이상의 테이블에서 데이터를 검색하는 질의 기법
ER 모델링 및 정규화 기법으로 여러 테이블로 분리된 정보를 통합하여 검색 시 유용
종류
내부 조인
외부 조인

3.8. 내부 조인

두개의 테이블에서 조인 조건을 만족하는 레코드만 결합하여 출력 결과에 포함하는 연산
조인 조건은 WHERE 절이 아닌 ON 절에 기록
ANSI SQL 표준과 사실상의 표준인 Oracle 사가 제안한 조인 형식이 사용

3.9. 자연 조인

두개 이상의 테이블을 하나의 테이블로 결합하는 내부조인과 매우 유사한 기능
두 테이블에 동일한 이름의 컬럼에 대해 같은 레코드를 결합하는 내부 조인
구문 형식
SELECT 컬럼1, 컬럼2... FROM 테이블1 NATURAL JOIN 테이블2 WHERE 조건
SQL
복사

3.10. 외부 조인

내부 조인은 조인 조건에 일치하는 레코드만 결합하여 결과를 생성
조인 결과에 정보 손실이 발생
외부 조인은 조인 조건에 맞지 않는 레코드도 질의 결과에 포함시키는 질의
외부 조인 종류
LEFT OUTER JOIN
RIGHT OUTER JOIN
FULL OUTER JOIN
비교

3.11. 셀프 조인

한 테이블이 자기 자신과 조인되는 형태
동일한 이름의 테이블에 대한 조인이므로 반드시 테이블 이름에 대한 별칭이 의무적으로 사용

4. 뷰의 사용

4.1. 뷰의 개념

데이터를 저장하고 있는 하나 이상의 테이블을 유도하여 생성하는 가상의 테이블
데이터 독립성
원본 테이블 구조 바뀌어도 영향 없음
데이터 보안
원본 테이블이 아닌 뷰를 통한 접근
다양한 구조의 테이블 사용
작업의 단순화
복잡한 질의를 뷰로 단순화
데이터 무결성
WITH CHECK OPTION을 이용하여 뷰 생성에 위배되는 수정 작업 거부

4.2. 뷰의 생성

생성되는 뷰의 구조는 SELECT 문의 결과로 결정
구문 형식
CREATE VIEW 뷰이름 AS (SELECT 컬럼1, 컬럼2... 컬럼n FROM 테이블 WHERE 조건) [WITH CHECK OPTION]
SQL
복사

4.3. 뷰의 수정과 삭제

뷰의 수정은 동일하게 새로운 SELECT 문의 결과로 변경
ALTER VIEW 뷰이름(컬럼1, 컬럼2... 컬럼n) AS (SELECT 컬럼1, 컬럼2... 컬럼n FROM 테이블 WHERE 조건)
SQL
복사
DROP VIEW 뷰이름
SQL
복사

4.4. 뷰를 이용한 데이터 검색

뷰는 가상의 테이블이므로 데이터 조작은 테이블 조작과 동일하게 수행
SELECT 컬럼1, 컬럼2.. 컬럼n FROM 뷰이름 WHERE 조건
SQL
복사

4.5. 뷰를 이용한 데이터 삽입

뷰에 대한 INSERT문은 원본 테이블에서 실행
PK, NOT NULL 등의 제약 사항이 위배되는 경우 삽입 불가능
원본 테이블에 존재하는 컬럼이지만 뷰에 없는 컬럼에 삽입하는 경우 실행 불가능
조인 질의, 그룹 질의가 적용된 뷰는 데이터 삽입 및 수정이 불가능
WITH CHECK OPTION이 적용된 뷰는 위배되는 사항은 없지만 뷰에 맞지 않는 조건일 경우 실행 불가능