1. 관계형 데이터베이스 개요
1-1) 데이터베이스
일상적인 정보들을 모아 놓은 것 자체를 의미한다.
일반적으로 DB라고 말할 때는 특정 기업이나 조직 또는 개인이 필요한 데이터를 일정한 형태로 저장해 놓을 것을 의미한다.
1-2) 데이터베이스 관리 시스템 (DBMS)
사용자들은 보다 효율적인 데이터 관리뿐만 아니라 예기치 못한 사건으로 인한 데이터의 손상을 피하고 필요할 때 데이터를 복구하기 위한 강력한 기능의 소프트웨어를 필요로 하는데 이러한 기본적인 요구사항을 만족시켜주는 시스템을 의미한다.
1-3) 관계형 데이터베이스 관리 시스템 (RDBMS)
관계형 데이터베이스(RDB) 는 정규화 이론에 근거한 합리적인 데이터 모델링을 통해 데이터 이상 현상 및 불필요한 데이터 중복 현상을 피할 수 있다. 이러한 RDB를 관리하는 시스템 소프트웨어를 관계형 데이터베이스 관리 시스템 (RDBMS) 이라고 한다.
RDBMS의 주요 기능
- 동시성 관리 및 병행 제어를 통해 많은 사용자들이 동시에 데이터를 공유 및 조작할 수 있는 기능을 제공한다.
- 메타 데이터를 총괄 관리할 수 있기 때문에 데이터의 성격, 속성 또는 표현 방법등을 체계화할 수 있고 데이터 표준화를 통한 데이터 품질을 확보할 수 있는 장점이 있다.
- 인증된 사용자만이 참조할 수 있도록 보안 기능을 제공하고 테이블 생성 시에 사용할 수 있는 다양한 제약조건을 이용하여 사용자 실수로 인한 잘못된 데이터 입력 및 관계성이 있는 중요 데이터의 삭제를 방지하여 데이터 무결성을 보장한다.
- 시스템의 갑작스러운 장애로부터 사용자가 입력/수정/삭제하는 데이터가 데이터베이스에 제대로 반영될 수 있도록 보장해주는 기능과 시스템 ShutDown, 재해 등의 상황에서도 데이터를 회복/복구할 수 있는 기능을 제공한다.
1-4) SQL
관계형 데이터베이스에서 데이터 정의, 데이터 조작, 데이터 제어를 하기 위해 사용하는 언어이다.
- 사용자는 SQL문으로 하고자 하는 작업을 요청하고 DBMS는 데이터베이스에서 사용자가 요청한 데이터를 제공하거나 작업 성공 여부를 리턴해준다.
1-5) SQL문의 종류
데이터 조작어 (DML)
: Data Manipulation Language
| 명령어 | 설명 |
|---|---|
| SELECT | 데이터베이스에 들어있는 데이터를 조회하거나 검색하기 위한 명령어 |
| INSERT UPDATE DELETE | 데이터베이스에 들어있는 데이터에 변형을 가하는데 사용하는 명령어 |
데이터 정의어 (DDL)
: Data Definition Language
- 데이터 구조를 생성/변경/삭제하거나 이름을 바꾸는데 사용하는 명령어들
| 명령어 | 설명 |
|---|---|
| CREATE ALTER DROP RENAME | 테이블/인덱스 등과 같은 데이터베이스 객체의 구조를 정의하는데 사용하는 명령어 |
데이터 제어어 (DCL)
: Data Control Language
| 명령어 | 설명 |
|---|---|
| GRANT REVOKE | 데이터베이스에 접근하고 객체들을 사용하도록 권한을 부여하고 회수하는 명령어 |
트랜잭션 제어어 (TCL)
: Transaction Control Language
| 명령어 | 설명 |
|---|---|
| COMMIT ROLLBACK | 논리적인 작업 단위를 묶어서 DML에 의해 조작된 결과를 작업단위별로 적용 및 취소하는 명령어 |
1-6) 테이블
: 데이터를 저장하는 객체로서 RDB의 기본 단위를 의미한다.
- RDB에서는 모든 데이터를 칼럼과 행의 2차원 구조로 나타낸다.
- 칼럼과 행으로 이루어져 있고 칼럼 중에서 기본키를 지정한다.
- 칼럼 : 세로 방향
- 행 : 가로 방향
- 필드 : 칼럼과 행이 겹치는 하나의 공간
용어
| 종류 | 설명 |
|---|---|
| 테이블 | 칼럼과 행의 2차원 구조(면적)를 가진 데이터이 저장소 |
| 칼럼 | 테이블에서 세로 방향으로 이루어진 하나하나의 속성 (더 이상 나눌 수 없는 것) |
| 행 | 테이블에서 가로 방향으로 이루어진 데이터 |
| 정규화 | 테이블을 분할하여 데이터의 정합성을 확보하고 불필요한 중복을 줄이는 프로세스 |
| 기본키 | 테이블에 존재하는 각 행을 한 가지 의미로 특정할 수 있는 1개 이상의 칼럼 ex) 주문 테이블의 기본키는 주문번호 / 고객 테이블의 기본키는 고객번호 |
| 외래키 | 다른 테이블의 기본키로 사용되고 있는 관계를 연결하는 칼럼 ex) 주문 테이블의 고객번호 칼럼은 주문 테이블이 가지고 있는 FK / 고객번호 칼럼은 고객 테이블의 기본키 |
1-7) ERD
: 테이블 간 서로의 상관 관계를 그림으로 도식화한 것
- ERD의 구성요소는 엔터티, 관계, 속성 3가지이며 현실 세계에서 발생하는 데이터는 이 3가지 구성요소로 모두 표현이 가능하다.
2. DDL
2-1) DDL의 정의
DB를 구성하고 있는 다양한 객체(사용자, 테이블, 인덱스, 뷰, 트리거, 프로시저, 사용자 정의 함수 등)를 정의/변경.제거하는데 사용한다.
- 물리적 DB객체의 구조를 정의하는데 사용된다.
2-2) 주요 데이터형(타입) 정리
테이블 생성 시 테이블 내에 사용될 칼럼을 정의한다.
선언한 유형이 아닌 다른 종류의 데이터가 들어오려고 하면 DBMS는 에러를 발생시킨다.
CHAR(L)
- 고정길이 문자열
- 고정길이를 가지고 있으므로 저장되는 값의 길이가 L값보다 작을 경우 그 차이만큼 공백으로 채워진다.
- 만약 CHAR(5)인데 '1234'를 저장한다면 끝에 공백 하나가 자동으로 채워진다.
VARCHAR(L) or VARCHAR2(L)
- 가변길이 문자열
- L값만큼의 최대 길이를 가진다.
- L값보다 작을 경우 입력하는 값만큼만 공간을 차지한다.
- 만약 VARCHAR(5)인데 '1234'를 저장한다면 그대로 '1234'가 저장된다.
- VARCHAR2는 공백도 문자로 취급한다.
- 만약 VARCHAR2(5)인데 '1234 '를 저장한다면 그대로 '1234 '가 저장된다.
NUMBER(L,D)
- 정수 및 실수를 저장한다.
- L값은 전체 자릿수, D값은 소수점 자릿수이다.
- 만약 NUMBER(12,2)이면 999999999999.99까지 저장할 수 있다.
DATE
- 날짜와 시각정보를 저장한다. (년월일시분초를 저장)
2-3) CREATE TABLE
: 신규 테이블을 생성하는 SQL문
주의사항
- 테이블명은 단수형을 사용할 것을 권고한다.
- 테이블명은 특정 사용자가 가지고 있는 테이블 내에서 다른 테이블과 중복되면 안된다.
- 한 테이블 내에서 동일한 칼럼명이 존재하면 안된다.
- 테이블 생성문 끝은 ';' 로 끝나야 한다.
- 칼럼의 데이터형은 반드시 지정해야 한다.
- 테이블명과 칼럼명은 반드시 문자로 시작해야 한다.
- 테이블명에 사용하는 문자는 A-Z , a-z , 0-9 , _ , $ , # 문자만 허용한다.
2-4) 제약조건
: 데이터의 무결성을 유지하기 위한 DBMS의 보편적인 방법으로 테이블의 특정 칼럼(하나 혹은 그 이상)에 설정하는 제약을 말한다.
- 테이블에 입력되는 데이터가 사용자가 원하는 조건을 만족하는 데이터만 입력되는 것을 보장한다.
기본키
- 테이블에 저장된 행들 중에서 특정 행을 고유하게 식별하기 위해서 사용한다.
- 하나의 테이블에 단 하나의 기본키만 정의할 수 있다.
- 기본키 생성 시 DBMS는 유일 인덱스를 자동으로 생성한다.
- 기본키 칼럼에는 NULL입력이 불가능하다.
- 기본키는 UNIQUE 제약조건과 NOT NULL 제약조건을 만족해야 한다. (UNIQUE+NOT NULL)
고유키
- 테이블에 저장된 행들 중에서 특정 행을 고유하게 식별하기 위해 생성한다.
- 고유키는 NULL입력이 가능하.
- 고유키는 UNIQUE 제약조건만 만족하면 NULL 입력이 가능하다.
(UNIQUE+NULL)
NOT NULL
- 해당 칼럼에는 NULL 입력을 금지하므오 어떤 값이라도 들어와야 한다.
- 이 칼럼은 필수 값 칼럼이 된다.
CHECK
- 입력할 수 있는 값의 종류 혹은 범위를 제한한다.
외래키
- 다른 테이블의 기본키를 외래키로 지정하는 경우 생성한다.
- 참조 무결성 제약조건이라고도 한다.
디폴트 값(=기본값)
- 해당 칼럼에 아무런 값도 입력하지 않았을 때 지정한 디폴트 값으로 데이터가 입력된다.
- 제약조건과는 별도로 데이터 입력 시에 칼럼의 값이 저장되어 있지 않을 경우 사전에 설정할 수 있다.
2-5) ALTER TABLE
: 칼럼 및 제약조건을 추가/수정/제거하는데 이용하는 SQL문
ALTER TABLE ~ ADD
: 칼럼을 추가할 수 있다.
ALTER TABLE 테이블명 ADD 칼럼명
:외래키를 생성활 수 있다.
ALTER TABLE 테이블명 ADD CONSTRAINT 외래키명 FOREIGN KEY(제약조건)REFERENCES
ALTER TABLE ~ DROP
: 칼럼을 제거할 수 있다.
ALTER TABLE 테이블명 DROP 칼럼명
:외래키를 제거할 수 있다.
ALTER TABLE 테이블명 DROP CONSTRAINT 외래키명
ALTER TABLE ~ MODIFY
: 칼럼의 데이터형 및 제약조건을 변경할 수 있다.
ALTER TABLE 테이블명 MODIFY 칼럼명(제약조건)
ALTER TABLE ~ RENAME
: 칼럼명을 변경할 수 있다.
ALTER TABLE 테이블명 RENAME 원하는칼럼명 TO 이전 칼럼명
TRUNCATE
: 테이블에 저장된 데이터를 제거할 수 있다.
- 테이블 객체는 그대로 두고 내부의 데이터만 영구적을 제거한다.
ROLLBACK문을 이용한 복구가 불가능
TRUNCATE 테이블명
DROP TABLE
: 테이블 객체를 제거할 수 있다.
DROP TABLE 테이블명
3. DML
3-1) DML의 정의
테이블의 데이터를 입력 / 수정 / 삭제 / 조회하는 역할을 한다.
- 4가지의 종류가 있으며 해당 SQL문을 실행 후 영구적으로 저장(COMMIT)하거나 SQL문의 수행을 취소(ROLLBACK)할 수 있다.
3-2) INSERT문
: 테이블에 데이터를 신규로 입력할 때 사용된다.
INSERT INTO 테이블명 (데이터를 넣고 싶은 칼럼명) VALUES (데이터값);
3-3) UPDATE문
: 테이블 내 행의 칼럼값을 수정할 때 사용된다.
- 한 번의 UPDATE문 실행으로 여러 개의 행에 대한 여러 개의 칼럼을 수정할 수 있다.
UPDATE 테이블명 SET 수정값 WHERE 수정하고 싶은 칼럼명;
3-4) DELETE문
: 테이블 내의 행을 삭제할 때 사용된다.
- 한 번의 DELETE문 실행으로 여러 개의 행을 삭제할 수 있다.
- 조건에 부합하는 행을 삭제할 수 있으며 수행 후 커밋 혹은 롤백할 수 있다.
DELETE FROM 테이블명 WHERE 원하는 행의 조건;
3-5) SELECT문
: 테이블에서 데이터를 조회하는데 사용된다.
SELECT 조회하고 싶은 칼럼들 FROM 테이블명 WHERE 조회 칼럼 조건;
SELECT *
: FRO절 내 테이블(들)이 가지고 있는 모든 칼럼을 출력한다.
SELECT * FROM 테이블명
3-6) 앨리어스(Alias)
- 테이블 및 칼럼에 앨리어스를 지정할 수 있다.
- 테이블 및 칼럼 이외에도 뷰 혹은 인라인뷰에도 사용이 가능하다.
- 칼럼 뒤에는 AS를 이용하여 지정한다.
- 칼럼에 지정한 앨리어스대로 SQL문의 해더 값이 출력된다.
- 앨리어스의 사용은 SQL문의 개발 생산성을 향상시켜주고 유지보수에 도움을 준다.
3-7) 합성 연산자의 사용
: SELECT절에서 여러 개의 칼럼을 출력하는 경우 합성 연산자 ("||") 로 연결하면 결과 값이 하나의 칼럼으로 출력된다.
SELECT 칼럼명1 || 칼럼명2 FROM 테이블명
3-8) DUAL 테이블을 이용한 연산 수행
DUAL 테이블
: 오라클 DB를 설치하면 기본적으로 DUAL 테이블이 존재한다.
- 단 1건의 데이터가 저장되어 있다.
- 칼럼은 DUMMY 칼럼 단 1개만 저장되어 있다.
- 저장되어 있는 DUMMY 칼럼값은 'X' 이다.
- DUAL 테이블을 이용해서 다양한 연산을 할 수 있다.
SELECT 원하는 연산 FROM DUAL;
4. TCL
4-1) TCL의 정의
데이터의 입력 / 수정 / 삭제 후 커밋 혹은 롤백을 하는데 사용하는 SQL문이다.
- DML문의 입력, 수정, 삭제를 실행하면 DB에 곧바로 적용되지 않고 커밋 명령을 실행해야만 DB에 최종 적용된다.
- 데이터를 변경한 후, 커밋 명령을 실행하지 않은 상태라면 롤백명령으로 데이터의 변경을 취소할 수 있다.
4-2) 트랜잭션의 특성
트랜잭션
: DB의 논리적 연산 단위로 1개의 트랜잭션에는 1개 이상의 SQL문이 포함된다.
- 분할할 수 없는 최고의 단위
| 특성 | 설명 |
|---|---|
| 원자성(Atomicity) | 트랜잭션에서 정의된 연산들은 모두 성공적으로 끝나거나 모두 실패해야 한다. (ALL OR NOTHING) |
| 일관성(Consistency) | 트랜잭션이 실행되기 전, 데이터베이스의 내용이 잘못되어 있지 않다면 실행된 이후에도 데이터베이스의 내용에 잘못이 있으면 안된다. |
| 고립성(Isolation)) | 트랜잭션이 실행되는 도중에 다른 트랜잭션의 영향을 받아 잘못된 결과를 만들어서는 안된다. |
| 지속성(Durability) | 트랜잭션이 성공적으로 수행되면 그 트랜잭션이 갱신한 데이터베이스의 내용은 영구적으로 저장된다. |
4-3) 트랜잭션의 요점
- 테이블 내에서 데이터의 변경을 발생시키는 입력 / 수정 / 삭제 수행 시 그 변경되는 데이터의 무결성을 보장하는 것이 커밋과 롤백의 목적이다.
- 오라클 DBMS의 트랜잭션은 트랜잭셩의 대상이 되는 SQL문을 실행하면 자동으로 시작되고 커밋 또는 롤백을 실행한 시점에서 종료된다.
4-4) COMMIT과 ROLLBACK
COMMIT
: 입력 / 수정 / 삭제한 자료에 대해서 문제가 전혀 없다고 판단되었을 경우 트랜잭션을 완료한다.
- "변경된 데이터를 테이블에 영구적으로 반영하라"
COMMIT이후의 상태
- 데이터의 입력 / 수정 / 삭제 내역이 DB에 완전히 반영된다.
- 변경 사용자 및 변경 사용자가 아닌 다른 모든 사용자는 SELECT문으로 입력 / 수정 / 삭제 결과를 볼 수 있다.
- 관련된 행에 대한 락이 풀리고 모든 사용자들이 행을 조작할 수 있게 된다.
ROLLBACK
: 입력 / 수정 / 삭제한 자료에 대해서 문제가 있다고 판단되었을 경우 트랜잭션을 취소한다.
- 관련된 행에 대한 락이 해제되어 다른 사용자들이 데이터 변경을 할 수 있게 된다.
- "변경 전으로 복구하라"
4-5) COMMIT이나 ROLLBACK 이전의 데이터 상태
- 최종 적용된 상태가 아니고 메모리에만 적용된 상태이므로 변경 이전 상태로 복구가 가능하다.
- 입력 / 수정 / 삭제를 실행한 사용자는 SELECT문으로 입력 / 수정 / 삭제 결과를 확인할 수 있다.
- 입력 / 수정 / 삭제를 실행한 사용자 외 다른 사용자는 SELECT문으로 입력 / 수정 / 삭제 결과를 확인할 수 없다.
- 변경된 행은 락이 설정되므로 다른 사용자가 변경할 수 없다.
4-6) SAVEPOINT
: 롤백 시 트랜잭션에 포함된 모든 입력 / 수정 / 삭제를 취소하는 것이 아니라 현 시점에서 SAVEPOINT 지점까지의 데이터 변경만 취소할 수 있다.
- 복잡한 대규모 트랜잭션에서 에러가 발생했을 때, SAVEPOINT까지의 트랜잭션만 취소하고 실패한 부분부터 다시 실행할 수 있다.
- "데이터 변경 사항의 취소를 지정한 지점까지만 취소하라"
4-7) 트랜잭션 적용 처리가 일어나는 상황
- 오라클 DBMS를 기준으로 DDL문장을 실행하면 그 실행 전과 실행 후 시점에 자동으로 커밋된다.
- DML문을 실행 후 커밋을 하지 않고 DDL문을 실행하면 DDL문 실행 전에 DML문의 변경 사항이 자동으로 커밋된다.
- DB에 대한 접속을 종료하면 자동으로 이전 DML의 변경사항이 커밋된다.
- 애플리케이션의 이상 동작으로 인해 DBMS에 대한 접속이 단절되었을 때는 데이터의 변경 사항이 자동으로 롤백된다.
5. WHERE절
5-1) WHERE 조건절 개요
: 필요한 데이터만을 조회, 추출하기 위해 추출되는 행을 제한한다.
- WHERE절 사용 시 일반적으로 조회 대상이 되는 칼럼은 좌측에 위치하며 입력값은 우측에 위치합니다.
- 대상 칼럼과 입력값 사이에는 "LIKE", "=", "<", ">" 과 같은 연산자를 이용한다.
5-2) 연산자의 종류
비교 연산자
: 숫자 값 혹은 문자 값의 크기를 비교하는데 사용한다.
| 연산자 | 의미 |
|---|---|
| = | ~와 같다. |
| > | ~보다 크다. |
| >= | ~보다 크거나 같아. |
| < | ~보다 작다. |
| <= | ~보다 작거나 같다. |
SQL연산자
: SQL문에서 사용하도록 기본적으로 예약되어 있는 연산자이다.
| 연산자 | 의미 |
|---|---|
| BETWEEN A AND B | A와 B 사이에 있으면 된다. |
| IN(리스트) | 리스트에 있는 값 중 하나라도 있으면 된다. |
| LIKE '비교문자열' | 비교문자열의 형태와 일치하면 된다.(와일드카드 사용↓) |
| IS NULL | 값이 NULL이면 된다. |
| IS NOT NULL | 값이 NULL이 아니면 된다.(NULL에 대한 부정 비교 시 사용) |
와일드카드
- % : 0개 이상의 어떤 문자를 의미한다.
- _ : 1개의 단일 문자를 의미한다.
논리 연산자
: 비교 연산자 혹은 SQL 연산자로 이루어진 여러 개의 조건들을 논리적으로 조합하기 위해서 사용하는 연산자
| 연산자 | 의미 |
|---|---|
| AND | 앞 조건과 뒤 조건이 모두 참이어야 한다. |
| OR | 앞 조건과 뒤 조건 중 하나라도 참이어야 한다. |
| NOT | 조건이 거짓이면 된다. |
부정 연산자
: 비교 연산자의 부정표현을 '부정 비교 연산자' 로 할 수 있고, SQL연산자의 부정표현을 '부정 SQL 연산자' 로 할 수 있다.
[부정 비교 연산자]
| 연산자 | 의미 |
|---|---|
| != | 같지 않다. |
| <> | 같지 않다. |
| ^= | 같지 않다. |
| NOT 칼럼명 = | ~칼럼의 값이 ~와 같지 않다. |
| NOT 칼럼명 > | ~칼럼의 값이 ~보다 크지 않다. |
| NOT 칼럼명 < | ~칼럼의 값이 ~보다 작지 않다. |
[부정 SQL 연산자]
| 연산자 | 의미 |
|---|---|
| NOT BETWEEN A AND B | A와 B의 값 사이에 있지 않다. |
| NOT IN(리스트) | 같지 않다. |
| IS NOT NULL | NULL값이 아니다. |
5-3) CHAR vs VARCHAR2
양쪽이 모두 CHAR 타입인 경우
- 길이가 서로 다르면 작은 쪽에 공백을 추가하여 길이를 같게 한다.
- 서로 다른 문자가 나올 때까지 비교한다.
- 달라진 첫 번째 값에 따라 크기를 결정한다.
- 공백의 수만 다르다면 같은 값으로 결정한다.
길이가 서로 달라도 공백만 다르다면 같다고 판단한다.
-> 공백은 결과집합에 영향을 주지 않는다.
비교 연산자 중 한쪽이 VARCHAR2 타입인 경우
- 서로 다른 문자가 나올 때까지 비교한다.
- 길이가 다르다면 짧은 것이 끝날 때까지만 비교한 후 길이가 긴 것이 크다고 판단한다.
- 길이가 같고 다른 것이 없다면 같다고 판단한다.
- VARCHAR2는 공백도 문자로 판단한다.
상수 값과 비교
- 상수 쪽을 칼럼의 데이터형과 동일하게 바꾸고 비교한다.
- 칼럼이 CHAR타입이면 CHAR타입인 경우를 적용한다.(공백은 문자로 판단하지 않는다.)
- 칼럼이 VARCHAR2타입이면 VARCHAR2 타입인 경우를 적용한다. (공백도 문자로 판단한다.)
5-4) WHERE절에 사용하는 ROWNUM
ROWNUM
: 특정 테이블에서 데이터가 조회될 때 출력되는 행의 순번을 의미하는 Pseudo칼럼이다.
- WHERE절의 조건으로 이용하여 결과 행의 건수를 제한할 수 있다.
SELECT 원하는 칼럼들 FROM 테이블명 WHERE ROWNUM 제한조건;
6. 함수
6-1) 단일행 함수의 주요 특징
단일행 함수
: 내장 함수 중 입력값에 대해서 단 하나의 출력값을 리턴하는 함수를 말한다.
- SELECT, WHERE, ORDER BY 절에 사용 가능하다.
- 각 행들에 대해 개별적으로 작용하여 데이터 값들을 조작하고 각각의 행에 대한 조작 결과를 리턴한다.
- 여러 인자를 입력해도 단 하나의 결과만 리턴한다.
- 함수의 인자로 상수, 변수, 표현식이 사용 가능하고 하나의 인수를 가지는 경우도 있지만 여러 개의 인수를 가질 수도 있다.
- 함수의 인자로 함수 호출 자체를 사용하는 함수의 중첩도 가능하다.(함수 안에서 함수를 호출하는 것이 가능하다.)
6-2) 단일행 함수의 종류
문자형 함수
: 문자를 입력하면 문자나 숫자 값을 반환한다.
LOWER, UPPER, SUBSTR, LENGTH, LTRIM, RTRIM, TRIM, ASCII
| SQL | 설명 |
|---|---|
| LOWER | 소문자로 변환한다. |
| UPPER | 대문자로 변환한다. |
| SUBSTR | 문자열의 원하는 문자의 처음과 끝을 지정하여 출력한다. |
| LENGTH | 문자열의 길이를 구한다. |
| CONCAT | 원하는 문자열들을 합친다. |
| RTRIM | 오른쪽에 있는 공백을 제거한다. |
| LTRIM | 왼쪽에 있는 공백을 제거한다. |
| ASCII | 알파벳에 해당하는 아스키 코드값을 반환한다. |
| CHR | 아스키 코드에 해당하는 문자값을 반환한다. |
숫자형 함수
: 숫자를 입력하면 숫자 값을 반환한다.
ABS, MOD, ROUND, TRUNC, SIGN, CHR, CEIL, FLOOR, EXP, LOG, LN, POWER, SIN, COS, TAN
| SQL | 설명 |
|---|---|
| ABS | 절대 값을 반환한다. |
| SIGN | 입력값이 양수면 1, 음수면 -1, 0이면 0을 리턴한다. |
| MOD | 나머지 값을 반환한다. |
| CEIL | 무조건 올림한다. |
| FLOOR | 무조건 내림한다. |
| ROUND | 소수점 n번째 자리에서 반올림한다. ex)ROUND(숫자,원하는 소수점 자리) |
날짜형 함수
: DATE 타입의 값을 연산한다.
SYSDATE, EXTRACT, TO_NUMBER
| SQL | 설명 |
|---|---|
| SYSDATE | 현재 년월일시분초를 출력한다. |
| SYSDATE -(1/24) | 현재에서 1시간 빼기 |
| SYSDATE -(1/24/60) | 현재에서 1분 빼기 |
| EXTRACT | 년 ,월 ,일 택하여 출력한다. ex) EXTRACT(YEAR FROM SYSDATE),EXTRACT(MONTH FROM SYSDATE) |
| TO_NUMBER | 년, 월, 일, 시, 분, 초 택하여 출력한다. ex) TO_NUMBER(TO_CHAR(SYSDATE, 'YYYY')) |
| TO_CHAR | 년, 월, 일, 시, 분, 초 택하여 문자열로 출력한다. ex) TO_CHAR(SYSDATE, 'YYYY') |
변환형 함수
: 문자, 숫자, 날짜형의 데이터형을 다른 데이터형으로 형변환한다.
TO_NUMBER, TO_CHAR, TO_DATE, CONVERT
NULL 관련 함수
: NULL을 처리하기 위한 함수이다.
NVL, NULLIF, COALESCE
| SQL | 설명 |
|---|---|
| NULLIF('문자열','문자열' | 두 문자열이 다르면 첫 번째 문자열을 출력한다. |
| NVL(NULLIF('문자열','문자열'),'같음') | 두 문자열이 같아 NULL을 출ㄹ력하고 NVL함수로 NULL이면 '같음'으로 출력한다. |
| COALESCE(NULL, NULL, '문자열') | NULL이 아닌 첫 번째 인자를 출력한다. |
6-4) 데이터 형변환의 방식
모든 데이터는 자신만의 데이터형(데이터 타입)을 가지고 있다.
데이터형을 변환하는 것을 데이터 형변환 이라고 한다.
명시적 형변환
: 데이터 형변환 함수로 데이터형을 변환하도록 명시해 주는 경우이다.
| SQL | 설명 |
|---|---|
| TO_CHAR(SYSDATE, 'YYYY/MM/DD' | 날짜형을 문자형으로 변환한다. |
| TO_CHAR(10.25, '$999.999.999') | 숫자형을 문자형으로 변환한다. |
| TO_NUMBER('100')+TO_NUMBER('100') | 문자형을 숫자형으로 변환한다. |
| TO_DATE(TO_CHAR(SYSDATE,'YYYYMMDD'),'YYYYMMDD') | 날짜형을 문자형으로 변환 후 문자형을 다시 날짜형으로 변환한다. |
암시적 형변환
: DBMS가 자동으로 데이터형을 변환하는 경우이다.
- SQL문 작성 시 문자형과 숫자형을 서로 비교할 때 명시적으로 형변환을 하지 않으면 DBMS 내부에서 자동으로 2개의 각기 다른 데이터형을 동일한 데이터형으로 변환한 후 연산을 처리하게 된다.
- SQL문 작성자가 의도하지 않은 데이터 형변환이 자동으로 발생하는 것이다.
-> 암시적 형변환의 발생으로 인해 칼럼에 존재하는 인덱스를 정상적으로 스캔하지 못하여 SQL문의 성능이 현저하게 느려기지도 한다.(SQL문 작성 시 가급적 명시적 형변환을 하여 데이터형을 동일하게 설정하는 것이 좋다.)
6-5) 단일행 CASE 표현의 종류
CASE 표현
: 특정 값에 대해서 조건에 따라 각기 다른 값을 리턴하도록 하는 것
| 종류 | 설명 |
|---|---|
| CASE WHEN 조건 THEN 값 혹은 SQL문 ELSE 값 혹은 SQL문 END | 조건이 맞으면 THEN절을 수행하고 그렇지 않으면 ELSE절을 수행한다. |
| DECODE(조건1, 값1, 조건2, 값2, 디폴트 값) | 조건1이 TRUE면 값1을 가져오고 그렇지 않고 조건2가 TRUE이면 값2를 가져오고 그렇지 않으면 디폴트 값을 가져온다. |
7. GROUP BY, HAVING절
7-1) 집계 함수
: 각각의 그룹별로 단 하나의 행을 리턴해주는 함수를 말한다.
- GROUP BY절에 기재한 칼럼을 기준으로 그룹으로 모인 상태에서 각 그룹의 집계를 계산하는 데 사용된다.
- 여러 행들의 그룹이 모여서 그룹당 단 하나의 결과 행을 되돌려주는 함수이다.
- SELECT절, HAVING절, GROUP BY절에 사용할 수 있다.
7-2) ALL과 DISTINCT
ALL
DEFAULT 옵션으로 생략이 가능하다.
DISTINCT
유일한 값을 출력한다.
- 중복이 제거된 값만 출력한다.
7-3) 집계 함수의 종류
| 항목 | 결과 |
|---|---|
| COUNT(*) | NULL값을 포함한 행의 수를 출력한다. |
| COUNT(표현식) | 표현식의 값이 NULL이 아닌 행의 수를 출력한다. |
| SUM(표현식) | 표현식이 NULL 값인 것을 제외한 합계를 출력한다. |
| AVG(표현식) | 표현식이 NULL 값인 것을 제외한 평균을 출력한다. |
| MAX(표현식) | 표현식이 NULL 값인 것을 제외한 최대값을 출력한다. |
| MIN(표현식) | 표현식이 NULL 값인 것을 제외한 최소값을 출력한다. |
| STDDEV(표현식) | 표현식이 NULL 값인 것을 제외한 표준편차를 출력한다. |
| VARIAN(표현식) | 표현식이 NULL 값인 것을 제외한 분산을 출력한다. |
7-4) GROUP BY절
- 일반적으로 FROM절 아래에 위치한다.
- GROUP BY절에 기재한 칼럼을 기준으로 결과집합을 그룹화한다.
- GROUP BY절을 통해 소그룹별 기준 칼럼을 정한 후, SELECT절에서는 집계 함수를 사용한다.
- 집계 함수의 결과는 NULL 값을 가진 행을 제외하고 수행한다.
- GROUP BY절에서는 SELECT절과는 달리 ALIAS명을 사용할 수 없다.
- GROUP BY절보다 WHERE절이 먼저 수행되므로 집계 함수는 WHERE절에 올 수 없다.
- WHERE절은 전체 데이터를 GROUP으로 나누기 전에 행들을 필터 처리한다.
-> WHERE절에 의해 리턴되는 행들을 대상으로 GROUP BY를 한다.
(집계 함수는 WHERE절에는 올 수가 없다.
7-5) HAVING절
: WHERE절과 유사한 기능을 하지만 WHERE절이 테이블에서 추출할 행을 제한한다고 하면 HAVING절은 그룹핑한 결과집합에 대한 조건을 주어 추출할 집계 데이터를 제한하는 역할을 한다.
- WHERE절에서는 집계 함수를 쓸 수 없다.
- 집계된 결과집합을 기준으로 특정 조건을 주고 싶은 경우 HAVING절을 이용한다.
- HAVING절은 WHERE절과 비슷하지만 그룹을 나타내는 결과집행의 행에 조건이 적용된다는 점에서 차이가 있다.
- HAVING절에 들어가는 조건은 GROUP BY절의 기준 항목이나 소그룹의 집계 함수가 사용된다.
- GROUP BY절에 의한 소그룹별로 만들어진 집계 데이터 중, HAVING절에서 제한 조건을 두어 조건을 만족하는 내용만 출력한다.
- HAVING절은 일반적으로 GROUP BY절 뒤에 위치한다.
- HAVING절이 GROUP BY절 앞에 나와도 결과는 동일하다.
7-6) CASE WHEN문 사용
결과집합을 출력할 때 사용자가 원하는 조건에 따라 분기처리하여 결과집합을 출력할 수 있다.
8. ORDER BY절
8-1) ORDER BY절의 개요
: SELECT문에서 조회한 데이터 집합을 특정 칼럼 기준으로 정렬한 후 데이터를 출력하는 역할이다.
- SQL문장으로 조회된 데이터(Rows)를 다양한 목적에 맞게 특정 칼럼을 기준으로 정렬하여 출력하는데 사용한다.
- 칼럼명 대신에 SELECT절에서 사용한 앨리어스명이나 칼럼 순서를 나타내는 정수도 사용 가능하다.
- 칼럼 뒤에 정렬 방식을 기재할 수 있으며 오름차순(ASC), 내림차순(DESC)이 있다.(정렬방식을 지정하지 않으면 기본적으로 오름차순으로 적용)
- 숫자형 데이터형은 오름차순으로 정렬했을 경우에 가장 작은 값부터 출력, 날짜형 데이터형은 오름차순으로 정렬했을 경우 가장 빠른 값이 먼저 출력된다.
- 오라클 DBMS기준으로 NULL 값을 가장 큰 값으로 간주하여 오름차순으로 정렬했을 경우에는 가장 마지막에 내림차순으로 정렬했을 경우에는 가장 먼저 위치한다.
8-2) NULL의 정렬
오라클은 ORDER BY절에 기재한 칼럼의 값이 NULL이면 가장 큰 값이라고 인식한다.
- ORDER BY 절에서 NULL값이 존재하는 칼럼을 기재하고 내림차순 정렬을 한다면 해당 행의 칼럼값이 NULL인 행이 결과집합의 맨 위로 올라오게 된다.
8-3) SELECT문의 실행 순서
- FROM절
: 조회 테이블을 참조한다. - WHERE절
: 조회 대상 행을 조회한다. - GROUP BY절
: 대상 행을 그룹화한다. - HAVING절
: 그룹화한 값에서 조건에 맞는 것을 출력한다. - SELECT절
: SELECT절에 기재한 칼럼이나 식을 계산한다. - ORDER BY절
: 출력되는 결과집합을 정렬한다.
8-4) GROUP BY절 사용 시 정렬작업
ORDER BY절을 사용할 떄 SELECT문에 GROUP BY절을 사용한 경우 반드시 SELECT절에 기재한 칼럼 혹은 표현식을 ORDER BY절에 기재해야 한다.
8-5) ORDER BY절과 부분 범위 처리
부분 범위 처리
조회를 원하는 데이터 집합이 특정 칼럼의 정렬 기준에 따라 부분적으로 조회하고 조회 연산을 멈추게 하는 것을 말한다.
OREDR BY절 + ROWNUM 조건을 결합
9. 조인
9-1) 조인이란?
: 2개 이상의 테이블을 연결 또는 결합하여 데이터를 출력하는 것을 말한다.
- 일반적으로 사용되는 SQL문의 상당수가 조인으로 이루어져 있다.
- 정규화된 데이터 모델은 여러 개의 테이블에 나누어 저장되므로 조인이 필연적으로 발생한다.
- 일반적인 경우 기본키와 외래키의 값 연관에 의해 조인이 이루어지며 기본키, 외래키 관계와는 별도로 일반 칼럼끼리 조인이 이루어질 수도 있다.
