1. 데이터베이스 기본
01. 트랜잭션 ⭐⭐⭐
1) 트랜잭션
① 트랜잭션
- 인가받지 않은 사용자로부터 데이터를 보장하기 위해 DBMS가 가져야 하는 특성이자, 데이터베이스 시스템에서 하나의 논리적 기능을 정상적으로 수행하기 위한 작업의 기본 단위
② 트랜잭션의 특성 ⭐ ACID
| 특성 | 설명 | 주요기법 |
|---|---|---|
| 원자성 (Atomicity) | - 트랜잭션을 구성하는 연산 전체가 모두 정상적으로 실행되거나 모두 취소되어야 하는 성질 - 트랜잭션의 연산 전체가 성공 또는 실패(All or Nothing) 되어야 하는 성질 | - Commit/Rollback - 회복성 보장 |
| 일관성 (Consistency) | - 시스템이 가지고 있는 고정요소는 트랜잭션 수행 전과 트랜잭션 수행 완료 후의 상태가 같아야 하는 성질 | - 무결성 제약조건 - 동시성 제어 |
| 격리성=고립성 (Isolation) | - 동시에 실행되는 트랜잭션들이 서로 영향을 미치지 않아야 한다는 성질 | - Read Uncommitted - Read Commited - Repeatable Read - Serializable |
| 영속성 (Durability) | - 성공이 완료된 트랜잭션의 결과는 영속적으로 데이터베이스에 저장되어야 하는 성질 | - 회복 기법 |
③ 트랜잭션의 상태 변화
[모두의 SQL] 트랜잭션의 상태 변화와 트랜잭션 제어어
- 실행(active) : 트랜잭션이 실행 중
- 부분 완료(partially committed) : DML 등 트랜잭션의 마지막 명령을 실행한 후
- 완료(committed) : 트랜잭션이 성공적으로 완료
- 실패(failed) : 더 이상 정상적으로 실행될 수 없음을 발견
- 철회(aborted) : 트랜잭션이 복원되어 트랜잭션 수행 이전 상태로 돌아감
④ 트랜잭션 제어
- 트랜잭션 제어언어는 TCL(Transaction Control Language)이라고 하며, 트랜잭션의 결과를 허용하거나 취소하는 목적으로 사용되는 언어를 지칭
| 명령어 | 핵심 | 설명 |
|---|---|---|
| COMMIT | 트랜잭션 확정 | 트랜잭션을 메모리에 영구적으로 저장하는 명령어 |
| ROLLBACK | 트랜잭션 취소 | 트랜잭션 내역을 저장 무효화시키는 명령어 |
| CHECKPOINT | 저장 시기 설정 | ROLLBACK을 위한 시점을 지정하는 명령어 |
⑤ 병행 제어(일관성 주요 기법)
-
병행 제어의 목적
- 데이터베이스의 공유를 최대화
- 시스템의 활용도를 최대화
- 데이터베이스의 일관성 유지
- 사용자에 대한 응답시간 회소화
-
병행 제어 미보장 시 문제점
- 갱신 손실(Lost Update)
: 먼저 실행된 트랜잭션의 결과를 나중에 실행된 트랜잭션이 덮어 쓸 때 발생하는 오류 - 현황 파악오류(Dirty Read)
: 트랜잭션의 중간 수행 결과를 다른 트랜잭션이 참조하여 발생하는 오류 - 모순성(Inconsistency)
: 두 트랜잭션이 동시에 실행되어 데이터베이스의 일관성이 결여되는 오류 - 연쇄복귀(Cascading Rollback)
: 복수의 트랜잭션이 데이터 공유 시 특정 트랜잭션이 처리를 취소할 경우 트랜잭션이 처리한 곳의 부분을 취소하지 못하는 오류
- 갱신 손실(Lost Update)
-
병행 제어 기법 종류
기법 설명 로킹
(Locking)하나의 트랜잭션을 실행하는 동안 특정 데이터 항목에 대해서 다른 트랜잭션이
동시에 접근하지 못하도록 상호배제 기능을 제공하는 기법낙관적 검증
(Optimistic Validation)트랜잭션이 어떠한 검증도 수행하지 않고 일단 트랜잭션을 수행하고,
트랜잭션 종료 시 검증을 수행하여 데이터베이스에 반영하는 기법타임 스탬프 순서
(Time Stamp Ordering)트랜잭션과 트랜잭션이 읽거나 갱신한 데이터에 대해 트랜잭션이 실행을 시작하기
전에 타임 스탬프를 부여하여 부여된 시간에 따라 트랜잭션 작업을 수행하는 기법다중버전 동시성 제어
(MVCC; Multi Version
Concurrency Control)트랜잭션의 타임스탬프와 접근하려는 데이터의 타임스탭프를 비교하여
직렬가능성이 보장되는 적절한 버전을 선택하여 접근하도록 하는 기법
⑥ 데이터베이스 고립화 수준(격리성 주요 기법)
- 다른 트랜잭션이 현재의 데이터에 대한 무결성을 해치지 않기 위해 잠금을 설정하는 정도
- Read Uncommitted
: 한 트랜잭션에서 연산(갱신) 중인(아직 커밋되지 않은) 데이터를 다른 트랜잭션이 읽는 것을 허용, 연산은 불허 - Read Committed
: 한 트랜잭션에서 연산(갱신)을 수행할 때, 연산이 완료될 때까지 연산 대상 데이터에 대한 읽기를 제한, 연산이 완료되어 커밋된 데이터는 다른 트랜잭션이 읽는 것을 허용 - Repeatable Read
: 선행 트랜잭션이 특정 데이터를 읽을 때, 트랜잭션 종료 시까지 해당 데이터에 대한 갱신/삭제를 제한 - Serializable Read
: 선행 트랜잭션이 특정 데이터 영역을 순차적으로 읽을 때, 해당 데이터 영역 전체에 대한 접근을 제한
- Read Uncommitted
⑦ 회복 기법(영속성 주요 기법) ⭐
- 트랜잭션을 수행하는 도중 장애로 인해 속상된 데이터베이스를 손상되기 이전의 정상적인 상태로 복구시키는 작업
- REDO
- 데이터베이스가 비정상적으로 종료되었을 때 디스크에 저장된 로그를 분석하여 트랜잭션의 시작과 완료에 대한 기록이 있는 트랜잭션들의 작업을 재작업하는 기법
- 데이터베이스 내용 자체가 손상된 경우, 가장 최근의 복제본을 적재한 후 일어난 변경만을 로그를 이용하여 재실행함으로써 데이터베이스 복원
- UNDO
- 데이터베이스가 비정상적으로 종료되었을 때 디스크에 저장된 로그를 분석하여 트랜잭션의 시작은 있지만, 완료에 대한 기록이 없는 트랜잭션들이 작업한 변경 내용들을 모두 취소하는 기법
- 데이터베이스 내용 자체는 손상되지 않았지만, 변경 중이거나 변경된 내용에 대한 신뢰성을 잃어버린 경우, 모든 변경 내용을 취소하여 복원하는 기법
- 회복 기법 종류
- 로그 기반 회복 기법
- 지연 갱신 회복 기법(Deffered Update)
: 트랜잭션이 완료되기 전까지 데이터베이스에 기록하지 않는 기법 - 즉각 갱신 회복 기법(Immediate Update)
: 트랜잭션 수행 중 갱신 결과를 바로 DB에 반영하는 기법
- 지연 갱신 회복 기법(Deffered Update)
- 체크 포인트 회복 기법(Checkpoint Recovery)
: 장애 발생 시 검사점 이후에 처리된 트랜잭션에 대해서만 장애 발생 이전의 상태로 복원시키는 회복 기법 - 그림자 페이징 회복 기법(Shadow Paging Recovery)
: 데이터베이스 트랜잭션 수행 시 복제본을 생성하여 데이터베이스 장애 시 이를 이용해 복구하는 방법
- 로그 기반 회복 기법
출처 : https://artist-developer.tistory.com/39
2) DDL
① 데이터 정의어(DDL; Data Definition Language)
- 데이터를 담는 그릇을 정의하는 언어
- 테이블과 같은 데이터 구조를 정의
② DDL의 대상
| DDL 대상 | 설명 |
|---|---|
| 도메인 (Domain) | - 하나의 속성이 가질 수 있는 원자값들의 집합 - 속성의 데이터 타입과 크기, 제약조건 등의 정보 |
| 스키마 (Schema) | - 데이터베이스의 구조, 제약조건 등의 정보를 담고 있는 기본적인 구조 |
| 테이블 (Table) | - 데이터 저장 공간 |
| 뷰 (View) | - 하나 이상의 물리 테이블에서 유도되는 가상의 테이블 |
| 인덱스 (Index) | - 검색을 빠르게 하기 위한 데이터 구조 |
-
스키마(Schema)
ⓐ 스키마의 구성
계층 설명 외부 스키마
(External Schema)- 사용자나 개발자의 관점에서 필요로 하는 데이터베이스의 논리적 구조
- 사용자 뷰를 나타냄
- 서브 스키마로 불림개념 스키마
(Conceptual Schema)- 데이터베이스의 전체적인 논리적 구조
- 전체적인 뷰를 나타냄
- 개체 간의 관계, 제약조건, 접근 권한, 무결성, 보안에 대해 정의내부 스키마
(Internal Schema)- 물리적 저장 장치의 관점에서 보는 데이터베이스 구조
- 실제로 데이터베이스에 저장될 레코드의 형식을 정의하고 저장 데이터 항목의 표현 방법,
내부 레코드의 물리적 순서 등을 표현
-
테이블(Table)
ⓐ 테이블의 개념
- 테이블은 데이터를 저장하는 항목인 필드(Field)들로 구성된 데이터의 집합체
- 하나의 DB 내에 여러 개의 테이블로 구성될 수 있고, 릴레이션(Relation) 혹은 엔터티(Entity)라고도 불림
ⓑ 테이블의 용어
출처 : [Inpa Dev] [DB] 📚 테이블 용어 🕵️ 정리
계층 설명 튜플(Tuple) / 행(Row) - 테이블 내의 행을 의미하여 레코드(Record)라고도 함
- 튜플은 릴레이션(Realtion)에서 같은 값을 가질 수 없음
- 행의 개수를 카디널리티(Cardinality)라고 함애트리뷰트(Attribute)
/ 열(Column)- 테이블 내의 열을 의미
- 열의 개수를 차수(Degree)라고 함식별자(Identifier) - 여러 개의 집합체를 담고 있는 관계형 데이터베이스에서 각각의 구분할 수 있는 논리적인 집합 도메인(Domain) - 하나의 애트리뷰트가 취할 수 있는 같은 타입의 원자값 들의 집합 -
뷰(View)
ⓐ 뷰의 개념
- 뷰는 논리 테이블로서 사용자에게(생성 관점 아닌 사용 관점에서) 테이블과 동일
- 뷰와 같은 결과를 만들기 위해 조인 기능을 활용할 수 있으나, 뷰가 만들어져 있다면 사용자는 조인 없이 하나의 테이블을 대상으로 하는 단순한 질의어를 사용할 수 있음
- 아래 그림에서 왼쪽 'Theater', 'Opera', 'Cinema'는 물리 테이블을 의미하고, 'Executed View'는 세 개의 테이블을 이용하여 생성한 뷰를 의미
출처 : [Learn Sql] What is an SQL View?
ⓑ 뷰의 특징
특징 설명 논리적 데이터 독립성 제공 데이터베이스에 영향을 주지 않고 애플리케이션이 원하는 형태로 데이터에 접근 가능 데이터 조작 연산 간소화 애플리케이션이 원하는 형태의 논리적 구조를 형성하여 데이터 조작 연산을 간소화
예) 회원 테이블에서 우수 회원을 뷰로 생성하여 활용보안 기능(접근제어) 제공 특정 필드만을 선택해 뷰를 생성할 경우 애플리케이션은 선택되지 않은 필드의 조회 및 접근 불가 뷰 변경 불가 뷰 정의는 ALTER 문을 이용하여 변경할 수 없음
(뷰는 CREATE 문을 사용하여 정의, 뷰를 제거할 때에는 DROP 문을 사용)ⓒ 뷰의 목적
- 뷰를 사용하는 주된 이유는 단순 질의어를 사용할 수 있기 때문
- FROM 절에 있는 하나의 뷰를 통해 뷰를 구성하는 복수의 테이블을 대체하는 단순성에 의의
- 장점
- 논리적 독립성 제공
- 사용자 데이터 관리 용이
- 데이터 보안의 용이
- 단점
- 뷰 자체 인덱스 불가
- 뷰 정의 변경 불가
- 데이터 변경 제약 존재
-
인덱스(Index)
ⓐ 인덱스의 개념
- 검색 연산의 최적화를 위해 데이터베이스 내 값에 대한 주소 정보로 구성된 데이터 구조
- 인덱스는 데이터를 빠르게 찾을 수 있는 수단으로서, 테이블에 대한 조회 속도를 높여 주는 자료 구조
- 테이블의 특정 레코드 위치를 알려주는 용도로 사용
ⓑ 인덱스의 특징
- 기본 키(PK; Primary Key) 컬럼은 자동으로 인덱스가 생성
- 연월일이나 이름을 기준으로 하는 인덱스는 자동으로 생성되지 않음
- 테이블의 컬럼에 인덱스가 없는 경우, 테이블의 전체 내용을 검색(테이블 전체 스캔; Table Full Scan)
- 인덱스가 생성되어 있을 때 데이터를 빠르게 찾을 수 있음(인덱스 범위 스캔; Index Range Scan)
- 조건절에 '='로 비교되는 컬럼을 대상으로 인덱스를 생성하면 검색 속도를 높일 수 있음
ⓒ 인덱스의 종류
유형 설명 순서 인덱스
(Ordered Index)- 데이터가 정렬된 순서로 생성되는 인덱스
- B-Tree 알고리즘 활용(오름차순/내림차순 지정가능)해시 인덱스
(Hash Index)- 해시 함수에 의해 직접 데이터에 키 값으로 접근하는 인덱스
- 데이터 접근 비용이 균일, 튜플(Row) 양에 무관비트맵 인덱스
(Bitmap Index)- 각 컬럼에 적은 개수 값이 저장된 경우 선택하는 인덱스
- 수정 변경이 적을 경우 유용(생년월일, 상품번호 등)함수기반 인덱스
(Functional Index)- 수식이나 함수를 적용하여 만든 인덱스 단일 인덱스
(Singled Index)- 하나의 컬럼으로만 구성한 인덱스
- 주 사용 컬럼이 하나일 경우 사용결합 인덱스
(Concatenated Index)- 두 개 이상의 컬럼으로 구성한 인덱스
- WHERE 조건으로 사용하는 빈도가 높은 경우 사용클러스터드 인덱스
(Clustered Index)- 기본 키(PK) 기준으로 레코드를 묶어서 저장하는 인덱스
- 저장 데이터의 물리적 순서에 따라 인덱스가 생성
- 특정 범위 검색 시 유리함
③ DDL 명령어
| 구분 | DDL 명령어 | 설명 |
|---|---|---|
| 생성 | CREATE | 데이터베이스 오브젝트 생성하는 명령어 |
| 수정 | ALTER | 데이터베이스 오브젝트 변경하는 명령어 |
| 삭제 | DROP TRUNCATE | 데이터베이스 오브젝트 삭제하는 명령어 데이터베이스 오브젝트 내용 삭제하는 명령어 |
④ TABLE 관련 DDL
-
CREATE TABLE
ⓐ CREATE TABLE 기본문법CREATE TABLE 테이블명 ( 컬럼명 데이터타입 [제약조건], ··· );ⓑ CREATE TABLE 상세문법
CREATE TABLE 테이블명 ( 컬럼명 데이터타입 PRIMARY KEY, -- 기본키 설정, 컬럼명 데이터타입 FOREIGN KEY REFERENCES 참조테이블(기본키), --외래키 설정 컬럼명 데이터타입 UNIQUE, 컬럼명 데이터타입 NOT NULL, 컬럼명 데이터타입 CHECH(조건식), --제약조건 설정 컬럼명 데이터타입 DEFAULT 값 );ⓒ CREATE TABLE 예시
CREATE TABLE 사원 ( 사번 VARCHAR(10) PRIMARY KEY, 업무 VARCHAR(20) FOREIGN KEY REFERENCES 부서(부서코드), 이름 VARCHAR(10) UNIQUE, 생년월일 CHAR(8) NOT NULL, 성별 CHAR(1) CHECK (성별 = 'M' OR 성별 = 'F'), 입사일 DATE DEFAULT SYSDATE -- SYSDATE는 현재시간/날짜 );ⓓ CREATE TABLE 제약조건
제약조건 설명 PRIMARY KEY- 테이블의 기본 키를 정의
- 유일하게 테이블의 각 행을 식별FOREIGN KEY- 외래 키를 정의
- 참조 대상을 테이블(컬럼명)로 명시
- 열과 참조된 테이블의 열 사이의 외래 키 관계를 적용하고 설정UNIQUE- 테이블 내에서 얻은 유일한 값을 갖도록 하는 제약조건 NOT NULL- 해당 컬럼은 NULL 값을 포함하지 않도록 하는 제약조건 CHECK- 개발자가 정의하는 제약조건
- 참이어야 하는 조건을 지정DEFAULT- 데이터를 INSERT 할 때 해당 컬럼의 값을 넣지 않는 경우 기본값으로 설정해주는 제약조건 -
ALTER TABLE
ⓐ ALTER TABLE 컬럼 추가- CREATE TABLE의 컬럼에 사용되는 제약조건 사용 가능
ALTER TABLE 테이블명 ADD 컬럼명 데이터타입 [제약조건];- 예시
ALTER TABLE 사원 ADD 전화번호 VARCHAR(11) UNIQUE;→ 사원 테이블의 전화번호라는 컬럼에 대해 타입이 VARCHAR(11)이면서 UNIQUE 제약 조건을 걸도록 추가
ⓑ ALTER TABLE 컬럼 수정
- CREATE 문에 제약조건을 명시 후에 ALTER를 통해 테이블 제약조건 변경 가능
ALTER TABLE 테이블명 MODIFY 컬럼명 데이터타입 [제약조건];- 예시
ALTER TABLE 사원 MODIFY 이름 VARCHAR(30) NOT NULL;→ 사원 테이블의 이름이라는 컬럼에 대해 타입이 VARCHAR(30)이면서 NOT NULL 제약 조건을 걸도록 수정
ⓒ ALTER TABLE 컬럼 삭제
ALTER TABLE 테이블명 DROP COLUMN 컬럼명;- 예시
ALTER TABLE 사원 DROP COLUMN 생년월일;→ 사원 테이블에 생년월일이라는 컬럼 삭제
-
DROP TABLE
DROP TABLE 테이블명 [CASCADE | RESTRICT];- 예시
DROP TABLE 사원;→ 사원 테이블 삭제
-
CASCADE와 RESTRICT의 경우 외래 키(FOREIGN KEY)가 걸려 있을 때 해당
옵션 설명 CASCADE참조하는 테이블까지 연쇄적으로 제거하는 옵션 RESTRICT다른 테이블이 삭제할 테이블을 참조 중이면 제거하지 않는 옵션
-
TRUNCATE TABLE
TRUNCATE TABLE 테이블명;- 예시
TRUNCATE TABLE 사원;→ 사원 테이블 내의 모든 데이터를 삭제
⑤ VIEW 관련 DDL
-
CREATE VIEW
CREATE VIEW 뷰이름 AS 조회쿼리;- 예시
CREATE VIEW 사원뷰 AS SELECT 사번, 이름 FROM 사원 WHERE 성별 = 'M';→ 사원 테이블에서 성별 값이 'M'을 가진 사번, 이름으로 생성된 사원뷰라는 이름의 뷰 생성
- VIEW 테이블의 SELECT 문에는
UNION이나ORDER BY절을 사용할 수 없음UNION: 집합연산자로 중복 행이 제거된 쿼리 결과 집합ORDER BY: 속성값을 정렬하고자 할 때 사용
- 컬럼명을 기술하지 않으면 SELECT 문의 컬럼명이 자동으로 사용
-
CREATE OR REPLACE VIEW
- 뷰를 교체
CREATE OR REPLACE VIEW 뷰이름 AS 조회쿼리; -
DROP VIEW
- 뷰를 삭제
DROP VIEW 뷰이름;
⑥ INDEX 관련 DDL
-
CREATE INDEX
UNIQUE는 생략 가능하고, 인덱스 걸린 컬럼에 중복 값을 허용하지 않음- 복수 컬럼을 인덱스로 걸 수 있음
CREATE [UNIQUE] INDEX 인덱스명 ON 테이블명(컬럼명1, 컬럼명2);- 예시
CREATE INDEX 사번인덱스 ON 사원(사번);→ 사원 테이블의 사번 컬럼에 대해 사번인덱스라는 인덱스 명으로 인덱스 생성
-
ALTER INDEX
- 일부 DBMS는 ALTER INDEX를 제공하지 않음
- 기존 인덱스를 삭제하고 신규 인덱스를 생성하는 방식으로 사용 권고
ALTER [UNIQUE] INDEX 인덱스명 ON 테이블명(컬럼명1, 컬럼명2);- 예시
ALTER INDEX 사번인덱스 ON 사원(사번);→ 사원 테이블의 사번 컬럼에 대해 사번인덱스라는 인덱스 명으로 인덱스 수정
-
DROP INDEX
DROP INDEX 인덱스명;
3) DML ⭐⭐⭐
① 데이터 조작어(DML; Data Manipulation Language)
- 데이터베이스에 저장된 자료들을 입력, 수정, 삭제, 조회하는 언어
② DML 명령어
| 유형 | 동작 | 설명 |
|---|---|---|
SELECT | 조회 | 테이블 내 칼럼에 저장된 데이터를 조회 |
INSERT | 삽입 | 테이블 내 칼럼에 데이터를 추가 |
UPDATE | 갱신 | 테이블 내 칼럼에 저장된 데이터를 수정 |
DELETE | 삭제 | 테이블 내 칼럼에 저장된 데이터를 삭제 |
③ SELECT
-
SELECT 명령어
- 속성명 별칭은
AS를 사용 DISTINCT: 중복된 속성 중 하나만HAVING:GROUP BY에 의해 분류한 후 그룹에 대한 조건 지정SELECT [ALL | DISTICT] 속성명1, 속성명2, ... FROM 테이블명1, ... [WHERE 조건] [GROUP BY 속성명 1, ...] [HAVING 그룹조건] [ORDER BY 속성 [ASC | DESC] ];
ⓐ SELECT 절
[성적 테이블]
이름 과목 학점 김철수 C언어 A 한유리 자료구조 A 신짱구 자료구조 A 이훈이 알고리즘 B SELECT DISTINCT 과목 FROM 성적 WHERE 학점 = 'A'과목 C언어 자료구조 SELECT COUNT(DISTINCT 과목) FROM 성적;DISTINCT 과목 3 ⓑ WHERE 절
- 비교
=: 값이 같은 경우 조회<>, !=: 값이 다른 경우 조회<, <=, >, >=: 비교 연산에 해당하는 데이터 조회
- 범위
SELECT * FROM PRODUCT WHERE PRICE BETWEEN 50000 AND 80000; -- = WHERE PRICE >=50000 AND PRICE <= 80000; - 집합
IN,NOT IN
SELECT * FROM PRODUCT WHERE PRICE IN (40000, 50000, 80000); - 패턴
%: 0개 이상의 문자열과 일치[ ]: 1개 문자와 일치[^]: 1개 문자와 불일치_: 특정 위치의 1개의 문자와 일치
SELECT * FROM PRODUCT WHERE PRICE IN (40000, 50000, 80000); - NULL
IS NULL,IS NOT NULL
SELECT * FROM PRODUCT WHERE PRICE IS NULL; - 복합조건
AND,OR,NOT
ⓒ GROUP BY
[급여 테이블]
이름 직책 부서 급여 김철수 차장 마케팅 5000 한유리 차장 전산 4800 신짱구 사원 마케팅 2500 이훈이 사원 마케팅 2700 SELECT 직책, 부서, SUM(급여) AS 급여합계 FROM 급요 GROUP BY 직책, 부서직책 부서 급여합계 차장 마케팅 5000 차장 전산 4800 사원 마케팅 5200 SELECT COUNT(*) FROM 급여;COUNT(*) 4 ⓓ HAVING 절
SELECT 직책, 부서, SUM(급여) AS 급여합계 FROM 급요 GROUP BY 직책, 부서 HAVING 급여합계 >= 5000;직책 부서 급여합계 차장 마케팅 5000 사원 마케팅 5200 - 속성명 별칭은
-
JOIN
- 두 개 이상의 테이블을 결합하여 하나의 튜플로 만드는 연결 방법
- 조인 예시
[도서] [도서가격]책번호 책명 책번호 가격 111 운영체제 111 20000 222 자료구조 222 25000 555 컴퓨터구조 333 10000 444 15000
ⓐ 내부 조인(Inner Join) : 공통 존재 컬럼의 값이 같은 경우 추출
SELECT A.컬럼1, A.컬럼2, ..., B.컬럼1, B.컬럼2, ... FROM 테이블1 A [INNER] JOIN 테이블2 B ON 조인조건 [WHERE 검색조건];SELECT A.책번호, A.책명, B.가격 FROM 도서 A JOIN 도서가격 B ON A.책번호 = B.책번호책번호 책명 가격 111 운영체제 20000 222 자료구조 25000 ⓑ 외부 조인(Outer Join)
1) 왼쪽 외부 조인 : 왼쪽 테이블의 모든 데이터와 오른쪽 테이블의 동일 데이터 추출SELECT A.컬럼1, A.컬럼2, ..., B.컬럼1, B.컬럼2, ... FROM 테이블1 A LEFT [OUTER] JOIN 테이블2 B ON 조인조건 [WHERE 검색조건];SELECT A.책번호, A.책명, B.책번호, B.가격 FROM 도서 A LEFT JOIN 도서가격 B ON A.책번호 = B.책번호;책번호 책명 책번호 가격 111 운영체제 111 20000 222 자료구조 222 25000 555 컴퓨터구조 NULL NULL 2) 오른쪽 외부 조인 : 오른쪽 테이블의 모든 데이터와 왼쪽 테이블의 동일 데이터 추출
SELECT A.컬럼1, A.컬럼2, ..., B.컬럼1, B.컬럼2, ... FROM 테이블1 A RIGHT [OUTER] JOIN 테이블2 B ON 조인조건 [WHERE 검색조건];SELECT A.책번호, A.책명, B.책번호, B.가격 FROM 도서 A RIGHT JOIN 도서가격 B ON A.책번호 = B.책번호;책번호 책명 책번호 가격 111 운영체제 111 20000 222 자료구조 222 25000 NULL NULL 333 10000 NULL NULL 444 15000 3) 완전 외부 조인 : 양쪽의 모든 데이터를 추출
SELECT A.컬럼1, A.컬럼2, ..., B.컬럼1, B.컬럼2, ... FROM 테이블1 A FULL [OUTER] JOIN 테이블2 B ON 조인조건 [WHERE 검색조건];SELECT A.책번호, A.책명, B.책번호, B.가격 FROM 도서 A FULL JOIN 도서가격 B ON A.책번호 = B.책번호;책번호 책명 책번호 가격 111 운영체제 111 20000 222 자료구조 222 25000 NULL NULL 333 10000 NULL NULL 444 15000 555 컴퓨터구조 NULL NULL ⓒ 교차 조인(Cross Join) : 조인 조건이 없는 모든 데이터 조합을 추출 ⭐
SELECT 컬럼1, 컬럼2, ..., FROM 테이블1 A CROSS JOIN 테이블2 BSELECT A.책번호, A.책명, B.책번호, B.가격 FROM 도서 A CROSS JOIN 도서가격 B;책번호 책명 책번호 가격 111 운영체제 111 20000 111 운영체제 222 25000 111 운영체제 333 10000 111 운영체제 444 15000 222 자료구조 111 20000 222 자료구조 222 25000 222 자료구조 333 10000 222 자료구조 444 25000 555 컴퓨터구조 111 20000 555 컴퓨터구조 222 25000 555 컴퓨터구조 333 10000 555 컴퓨터구조 444 25000 ⓓ 셀프 조인(Self Join) : 자기 자신에게 별칭을 지정한 후 다시 조인
SELECT A.컬럼1, A.컬럼2, ..., B.컬럼1, B.컬럼2, ... FROM 테이블1 A [INNER] JOIN 테이블1 B ON 조인조건 [WHERE 검색조건];[도서]
책번호 책명 선수과목_책번호 111 운영체제 222 222 자료구조 555 555 컴퓨터구조 NULL SELECT A.책번호, A.책명, B.책번호, B.책명 FROM 도서 A FULL JOIN 도서 B ON A.선수과목_책번호 = B.책번호;책번호 책명 책번호 책명 111 운영체제 222 자료구조 222 자료구조 555 컴퓨터구조
-
서브쿼리(Sub-query)
- SQL 문 안에 포함된 또 다른 SQL 문
- 용도는 알려지지 않은 기준을 위한 검색을 위해 사용
- 메인쿼리와 서브쿼리는 주종 관계로서, 서브쿼리에 사용되는 컬럼 정보는 메인 쿼리의 컬럼 정보를 사용할 수 있으나, 역으로는 성립 X
ⓐ FROM 절 서브쿼리
- 서브쿼리가 FROM 절 안에 들어있는 형태
- 인라인 뷰라고 불림
- 뷰처럼 결과가 동적으로 생성된 테이블 형태로 사용 가능
SELECT MAX(가격) AS 가격 FROM 도서가격 A, (SELECT 책번호 FROM 도서 WHERE 책명='자료구조') B WHERE A.책번호 = B.책번호;가격 25000 ⓑ WHERE 절 서브쿼리
- 서브쿼리가 WHERE 절 안에 들어있는 형태
- 중첩 서브쿼리(Nested Sub-Query)라도고 불림
SELECT MAX(가격) AS 가격 FROM 도서가격 A, WHERE 책번호 IN (SELECT 책번호 FROM 도서 WHERE 책명='자료구조');가격 25000
④ INSERT(데이터 삽입)
- 속성과 데이터 개수, 데이터 타입이 일치해야 함
- 속성명은 생략 가능
- 속성의 타입이 숫자인 경우 따옴표를 붙이지 않아도 되며, 문자열인 경우 붙여야 함
INSERT INTO 테이블명(속성명1, ...)
VALUES (데이터1, ...);- 예시
INSERT INTO 학생(학번, 성명, 학년, 수강과목)
VALUES (6677, '장길산', 3, '수학');⑤ UPDATE(데이터 갱신)
WHERE절을 통해 어떤 조건이 만족할 경우에만 특정 컬럼의 값을 수정하는 용도로 자주 사용됨
UPDATE 테이블명
SET 속성명 = 데이터, ...
WHERE 조건;- 예시
UPDATE 학생
SET 주소='인천'
WHERE 이름='장길산';⑥ DELETE(데이터 삭제)
- 모든 레코드를 삭제할 때는 WHERE 절 없이 DELETE만 사용
- ⭐ 레코드를 삭제해도 테이블 구조는 남아 있어서 디스크에서 테이블을 완전히 삭제하는
DROP명령과는 다름
DELETE FROM 테이블명
WHERE 조건;- 예시
DELETE FROM 학생
WHERE 이름='장길산';4) DCL
① 데이터 제어어(DCL; Data Control Language)
- 데이터베이스 관리자가 데이터 보안, 무결성 유지, 병행 제어, 회복을 위해 관리자(DBA)가 사용하는 제어용 언어
| 유형 | 동작 | 설명 |
|---|---|---|
GRANT | 사용 권한 부여 | 관리자(DBA)가 사용자에게 데이터베이스에 대한 권한을 부여하는 명령어 |
REVOKE | 사용 권한 취소 | 관리자(DBA)가 사용자에게 부여했던 권한을 회수하기 위한 명령어 |
- GRANT(권한 부여) 명령어
- 데이터베이스 관리자(DBA; Database Administrator)가 사용자에게 데이터베이스에 대한 권한을 부여하는 명령어
GRANT 권한 ON 테이블 TO 사용자;- 예시
→ 관리자가 사용자 장길산에게 '학생' 테이블에 대해 UPDATE 할 수 있는 권한 부여GRANT UPDATE ON 학생 TO 장길산;
- REVOKE(권한 회수) 명령어
- 데이터베이스 관리자(DBA)가 사용자에게 부여했던 권한을 회수하기 위한 명령어
REVOKE 권한 ON 테이블 FROM 사용자;- 예시
→ 관리자가 사용자 장길산에게 '학생' 테이블에 대해 UPDATE 할 수 있는 권한 회수REVOKE UPDATE ON 학생 FROM 장길산;
이세계 개발자입니다.