관계형 DB 개요
- DB : 데이터를 일정한 형태로 저장해놓은 것
- DBMS : 효율적으로 데이터를 관리하고 데이터 손상 복구가 가능하게 하는 소프트웨어
- 관계형 데이터베이스(RDB; Relational Database) : 정규화를 통해 이상현상 및 중복 데이터를 제거하고 동시성 관리와 병행 제어를 통해 데이터 동시 조작을 가능하게 한다.
- 연산
- 집합 연산
- Union(합집합)
- Difference(차집합)
- Intersection(교집합)
- 관계 연산
- Selection(선택 연산) : 조건에 맞는 행(튜플) 조회
- Projection(투영 연산) : 조건에 맞는 칼럼(속성) 조회
- Join(결합 연산) : 공통 속성을 사용하여 새로운 릴레이션 생성
- Division(나누기 연산) : 공통 요소를 추출하고 분모 릴레이션의 속성을 삭제한 후 중복된 행 제거
- Cartesian Product(곱집합) : 각 릴레이션에 존재하는 모든 데이터를 조합
- 테이블 : DB 기본 단위, 데이터를 저장하는 객체
- 로우(가로, 행, 튜플, 인스턴스)
- 컬럼(세로, 열)
- 테이블의 분할
- 정규화를 통해 데이터의 불필요한 중복 제거하고 이상현상을 방지한다.
- SQL : 관계형 DB에서 데이터 정의, 조작, 제어를 위해 사용하는 언어
- DML(Data Manipulation Language, 데이터 조작어, 로우 단위) : ISUD
- insert,update,delete : 데이터 변형 명령어
- select : 데이터 조회 명령어
- DDL(Data Definition Laguage, 데이터 정의어, 테이블 단위) : CA_D
- create, alter, drop, rename
- DCL(Data Control Language, 데이터 제어어): DB 접근 권한 부여 및 회수 명령어
- TCL(Transaction Control Language, 트랜잭션 제어어) : DML로 조작한 결과를 논리적인 작업단위 별로 제어
- ERD(Entity Relationship Diagram)
- 구성요소 : 엔터티, 관계, 속성
- 표기법 : IE(Information Engineering) 표기법, Barker(Case Method) 표기법
DDL, Data Definition Language
- 명명 규칙
- 테이블 명은 다른 테이블의 이름과 중복되어서는 안 된다.
- 테이블 내의 칼럼명은 중복될 수 없다.
- 각 칼럼들은
쉼표( , )
로 구분되고 세미콜론( ; )
으로 끝난다.
- 데이터 유형은 꼭 지정되어야한다.
- 테이블명과 칼럼명은 반드시 문자로 시작한다.
[A-Z, a-z, 0-9, _, $, #]
만 사용가능
- 데이터 유형
CHAR(len)
: 고정 길이 문자열, 최대 길이만큼 공백으로 공간을 채운다.
VARCHAR(len)
, VARCHAR2(len)
: 가변 길이 문자열, 할당되는 최대 값이 len이다. MS-SQL, MySQL은 VARCHAR을 사용하고 ORACLE은 VARCHAR2를 사용한다.
NUMBER(len, dot)
: 정수, 실수 등 숫자 정보 (len: 전체 자리수; 1~38 기본값 38, dot: 소수점 자리수; -84 ~ 127 기본값 0)
DATE
, DATETIME
: 날짜와 시각 정보
- 제약조건 : 데이터 무결성 유지 목적
PRIMARY KEY
: 기본키, 테이블 당 하나의 기본키만 정의 가능, 기본키 생성시 DBMS가 자동으로 인덱스 생성, NOT NULL
FOREIGN KEY
: 외래키, 다른 테이블의 기본키를 외래키로 지정, NULL 가능, 여러 속성 가능, 참조 무결성 제약조건
UNIQUE
: 고유키, 행 데이터를 식별하기 위해 생성, NULL 가능
DEFAULT
: ‘DEFAULT 값’으로 기본값 설정
NOT NULL
: NULL 값 입력 금지
CHECK
: 입력값의 종류 및 범위 제한
- CREATE TABLE : 테이블 생성
CREATE TABLE 테이블명 (
칼럼명_1 CHAR(7) NOT NULL,
칼럼명_2 VARCHAR2(30) NOT NULL
);
- ALTER TABLE : 테이블 구조 변경
-
칼럼 추가
ALTER TABLE 테이블명 ADD(칼럼명_3 VARCHAR2(20));
-
칼럼 삭제
ALTER TABLE 테이블명 DROP COLUMN 칼럼명_3;
-
칼럼 수정
ALTER TABLE 테이블명 MODIFY (칼럼명_2 DATE DEFAULT SYSDATE NOT NULL);
- CONSTRAINT : 제약조건
- DROP : 테이블의 데이터와 구조 삭제, 복구 불가
DROP TABLE 테이블명;
- TRUNCATE : 테이블의 전테 데이터 삭제, 로그를 기록하지 않아 ROLLBACK 불가
TRUNCATE TABLE 테이블명;
- RENAME : 이름 변경
-
테이블 이름 변경
ALTER TABLE 테이블명 RENAME TO 테이블명_2;
-
칼럼 이름 변경
ALTER TABLE 테이블명 RENAME COLUMN 칼럼명 TO 칼럼명_2;
-
제약조건명 변경
ALTER TABLE 테이블명 RENAME CONSTRAINT 제약조건명 TO 제약조건명_2
DML, Data Manipulation Language
DDL 명령어는 실행시 AUTO COMMIT 하지만 DML의 경우 COMMIT을 입력해야한다.
INSERT INTO 테이블명 (칼럼명, ) VALUES (필드 값, );
INSERT INTO 테이블명 VALUES (필드값, );
UPDATE 테이블명 SET 칼럼명=필드값;
- DELETE : 데이터 삭제 TRUNCATE로 삭제하는 것과 달리 DELETE로 데이터를 삭제해도 테이블 용량은 초기화되지 않는다. DROP 명령어는 데이터가 아닌 객체를 삭제한다.
DELETE FROM 테이블명 WHERE 조건절;
- SELECT : 데이터 선택
-
산술 연산자 우선순위(NUMBER와 DATE에 적용)
-
와일드 카드
-
앨리어스(alias)
-
합성 연산자(Concatenation) : 문자와 문자 연결
- 오라클 :
||
- SQL Server :
+
- 공통 :
CONCAT(str_1, str2)
-
DUAL : 오라클의 기본 더미 테이블, 연산 수행을 위해 사용됨
-
칼럼 별 데이터 선택
SELECT 칼럼명 FROM 테이블명;
-
데이터 중복 없이 선택
SELECT DISTINCT 칼럼명 FROM 테이블명;
TCL, Transaction Control Language
데이터 무결성 보장을 목적으로 한다. 영구 변경 전 확인과 연과 작업 동시처리가 가능하다.
- 특징
- 오라클은 SQL 문장을 실행하면 트랜잭션이 시작되고 TCL을 실행하면 트랜잭션이 종료된다.
- DDL을 실행하면 자동 커밋
- DB를 정상적으로 종료하면 자동 커밋, 애플리케이션 등의 이상으로 DB 접속이 단절되면 자동 롤백
- COMMIT : 문제없이 처리된 트랜잭션을 데이터베이스에 영구 반영, COMMIT시 LOCKING이 해제됨, SQL Server는 자동 커밋
- 커밋 전
- 데이터 변경이 메모리 버퍼에만 영향을 받았기 때문에 복구 가능
- 현재 사용자는 SELECT 문으로 변경 결과 확인 가능
- 다른 사용자는 현재 사용자가 수행한 결과 확인 불가능
- 변경된 행은 잠금(LOCKING)이 설정되어있어 다른 사용자가 변경 불가능
- 커밋 후
- 데이터에 대한 변경사항 DB 영구 반영
- 이전 데이터 소실
- 모든 사용자 조회 가능
- 모든 행 잠금 해제, 다른 사용자 행 조작 가능
- ROLLBACK : 트랜잭션 시작 이전의 상태로 되돌림, 커밋 되지 않은 모든 트랜잭션을 롤백
- 롤백 후
- 데이터에 대한 변경사항 취소
- 이전 데이터 재저장
- 관련 행 잠금해제, 다른 사용자 행 조작 가능
⇒ COMMIT과 ROLLBACK 사용으로
WHERE 절
SELECT 칼럼명 FROM 테이블며 WHERE 조건절;
- 비교 연산자 :
=
, >
, ≥
, <
, ≤
- 부정 비교 연산자 :
NOT 칼럼명 비교 연산자
와 동일
- SQL 연산자 (입력값을 비교하여 논리값 출력)
- BETWEEN a AND b : a와 b 값 사이
- IN(list) : 리스트에 있는 값 중 어느 하나라도 일치
- LIKE ‘문자열’ : 문자열의 형태와 일치하는 값
%
는 0개 이상의 문자, _
는 1개의 단일 문자
- IS NULL : NULL 값인 경우(NULL은 등호로 판단 불가)
- IS NOT NULL : NULL 값이 아닌 경우
- NOT IN(list) : 리스트에 있는 값과 일치하지 않음
- 논리 연산자 : AND, OR, NOT
- 연산자 우선순위 :
()
→ NOT
→ 비교연산자
→ AND
→ OR
- 부분 범위 처리
- ROWNUM : SQL 처리 결과 집합의 각 행에 임시로 부여되는 번호, 원하는 만큼 행을 가져올 때 사용, WHERE 절에서 행의 개수를 제한하는 목적으로 사용 ⇒ROWNUM 조건이 ORDER BY 절보다 먼저 처리되는 WHERE 절에서 처리되어 인라인 뷰에서 먼저 정렬을 수행한 후 메인 쿼리에서 ROWNUM 조건을 사용해야한다.
- TOP : 출력 행의 수 제한 함수
TOP(N)
로 N개 행 출력, 각 행에 개별적으로 작용, 여러 이자를 입력해도 단 하나의 결과만 출력
함수
- 사용자 정의 함수(User Defined Function)
- 내장함수( Built-In Function) : 벤더에서 제공하는 함수
- 단일행 함수 : 단일행 내에 있는 하나의 값 또는 여러 값이 입력 인수로 사용
- 다중행 함수 : 여러 레코드의 값들을 입력 인수로 사용
단일행 함수
- 단일행 함수의 종류 : 문자형, 숫자형, 날짜형, 변환형, NULL 관련 함수
- 특징
- SELECT, WHERE, ORDER BY 절에서 사용 가능
- 행에 개별적 조작
- 여러 인자가 있어도 결과는 1개만 출력
- 함수 인자에 상수, 변수, 표현식 사용 가능
- 함수 중첩 가능
- 문자형 함수
- LOWER : 대문자 → 소문자
- UPPER : 소문자 → 대문자
- ASCII : 문자의 ASCII 값 반환
- CHR : ASCII 값에 해당하는 문자 반환
- CONCAT : 두 문자열을 연결
CONCAT('문자열_1','문자열_2');
- SUBSTR : 문자열 중 m 위치에서 n개의 문자를 반환
SUBSTR('문자열입니다.',3,3);
- LENGTH : 문자열 길이를 반환
- LTRIM(문자열, 지정문자) : 문자열 왼쪽부터 확인해 지정 문자가 처음 나타나는 동안 해당 문자를 제거, 기본값 빈 칸
- RTRIM(문자열, 지정문자) : 문자열 오른쪽부터 확인해 지정 문자가 처음 나타나는 동안 해당 문자를 제거, 기본값 빈 칸
- TRIM(문자열, 지정문자) : 문자열 머리말,꼬리말, 양쪽 확인해 지정 문자가 처음 나타나는 동안 해당 문자를 제거, 기본값 빈 칸
- 숫자형 함수
- SIGN(num) : 숫자가 양수일 경우 1, 음수는 -1 0일 경우 0 반환
- MOD(num_1, num_2) : 숫자1을 숫자2로 나눈 나머지 반환
- CEIL(num) : 소수점 올림, 정수 반환
- FLOOR(num) : 소수점 내림, 정수 반환
- ROUND(num_1, num_2) : 소수점 num_2 자리에서 반올림, 기본값 0
- TRUNC(num_1,num_2) : 소수점 num_2 자리 뒤 절삭, 가본값 0
- SIN, COS, TAN : 삼각함수 값 반환
- EXP(), POWER(), SQRT(), LOG(), LN() : 지수, 거듭제곱, 제곱근, 자연로그
- 날짜형 함수
- SYSDATE()/GETDATE() : 현재 날짜와 시각 반환
- EXTRACT(’YEAR’ | ‘MONTH’ | ‘DAY’ from data) : 년/월/일 데이터 추출
- DATEPART(’YEAR’ | ‘MONTH’ | ‘DAY’ | ‘HOUR’ | ‘MINUTE’ | ‘SECOND’, data) : 해당 데이터 추출
- TO_NUMBER(TO_CHAR(data, ‘YYYY’)) / YEAR(data) : 해당 데이터 추출
- 1=하루, 1/24=1시간, 1/24/60=1분
- 날짜±숫자=날짜, 날짜-날짜=날짜 수, 날짜+숫자/24=날짜+시간
- 변환형 함수
- 명시적 변환(Explicit) 변환 : 데이터 변환형 함수로 변환하도록 명시
- 암시적 변환(Implicit) 변환 : 데이터베이스가 자동으로 변환하여 계산
- NULL 관련 함수
- NVL(expr_1, expr_2) : expr_1의 값이 NULL 이면 expr_2 출력
- ISNULL(expr_1, expr_2) : expr_1의 값이 NULL 이면 expr_2 출력
- NULLIF(expr_1, expr_2) : expr_1의 값이 expr_2와 값으면 NULL을 아니면 expr_1을 출력
- COALESCE(expr_1, expr_2) : NULL이 아닌 첫번째 값 출력
- 조건문
- CASE WHEN 조건절 THEN 출력값 ELSE 기본값 END : ELSE 생략 시 NULL 출력
GROUP BY HAVING 절
SELECT DISTINCT 칼럼명 ALIAS명
FROM 테이블명
WHERE 조건식
GROUP BY 칼럼/표현식
HAVING 그룹의 조건식;
집계함수(Aggregate Function)
여러 행들의 그룹이 모여서 그룹당 단 하나의 결과를 돌려주는 함수
- 집계 함수는 WHERE 절에 올 수 없다.
- 집계함수의 통계 정보는 NULL 값을 가진 행을 제외하고 수행한다.
- SELECT, HAVING, ORDER BY 절에 사용 가능
- ALL : 기본 옵션, 생략 가능
- DISTINCT : 같은 값을 하나의 데이터로 간주 옵션
- 주로 숫자 유형에 사용하며 MAX, MIN,COUNT 함수는 문자, 날짜에도 적용가능하다.
- 종류
- COUNT() : NULL 값을 포함한 모든 행의 수를 출력
- COUNT(expr) : NULL 값인 것을 제외한 행의 수를 출력
- SUM() : NULL을 제외한 합계 출력
- AVG() : NULL을 제외한 평균 출력
- MAX() : 최대값
- MIN() : 최소값
- STDDEV() : 표준 편차를 출력
- VARIAN() : 분산을 출력
GROUP BY, HAVING 절
FROM 절과 WHERE 절 뒤에 오며, 데이터들을 작은 그룹으로 분류하여 소그룹에 대한 항목별 통계 정보를 얻을 때 추가로 사용한다.
- GROUP BY
- GROUP BY 절은 행들을 소그룹화
- GROUP BY 절에서는 ALIAS 사용 불가
- HAVING
- HAVING 절은 일반적으로 GROUP BY 뒤에 위치
- HAVING 절에는 집계함수를 이용하여 조건 표시
ORDER BY 절
- ORDER BY 정렬 : 특정 칼럼을 기준으로 정렬, 기본값은 오름차순(ASC)
- SQL 문장으로 조회된 데이터들을 다양한 목적에 맞게 특정한 칼럼을 기준으로 정렬
- ORDER BY 절에 칼럼명 대신 ALIAS 명이나 칼럼 순서를 나타내는 정수도 사용 가능
- DESC 옵션으로 내림차순 정렬
- SQL 문장 제일 마지막에 위치
- SELECT로 정의하지 않은 칼럼 사용가능
- 오라클에서는 NULL이 가장 크고 SQL Server에서는 가장 작다.
- SELECT 문장 실행 순서
- FROM : 발췌 대상 테이블 참조
- WHERE : 발췌 대상 데이터가 아닌 것은 제거
- GROUP BY : 행들을 소그룹화
- HAVING : 그룹핑된 값의 조건에 맞는 것만을 출력
- SELECT : 데이터 값을 출력 및 계산
- ORDER BY : 데이터를 정렬
- WITH TIES : ORDER BY 절의 조건 기준으로 TOP N의 마지막 행으로 표시되는 추가 행의 데이터가 같을 경우 N + 동일 정렬 순서 데이터를 추가로 반환하도록 하는 옵션
JOIN
두 개 이상의 테이블들을 연결 또는 결합하여 데이터를 출력하는 것
- 일반적으로 PK나 FK 값의 연관에 의해 JOIN이 성립되나 PK, FK 관계가 없어도 논리적인 값들의 연관만으로 JOIN이 성립가능하다.
- 두 개의 집합 간에만 JOIN이 일어난다.
- FROM 절에 3 개의 테이블이 나열되어도 특정 2개의 테이블만 먼저 조인되고 그 조인된 새로운 결과 집합과 남은 테이블이 다음 차례로 조인되는 것
- EQUI JOIN : 2개의 테이블 간에 칼럼 값들이 서로 정확하게 일치하는 경우에 사용, 대부분 PK, FK의 관계를 기반으로 한다.
- JOIN 조건은
=
연산자를 사용해 WHERE 절에 기술한다.
- N개의 테이블 조인에서 JOIN 조건은 N-1개 이상 필요
- 조건절에 ALIAS명 사용이 가능하다 ⇒ 이 경우 WHERE절과 SELECT 절에서는 테이블명이 아닌 ALIAS를 사용해야한다.
- NON EQUI JOIN : 2개의 테이블 간에 칼럼 값들이 서로 정확하게 일치하지 않는 경우에 사용