[SQLD] SQL기본과 활용

Posted Nov 8, 2024

By Kang Minjun

42 min read

[SQLD] SQL기본과 활용

SQL 기본

관계형 데이터베이스

1960년대
- 플로우차트 중심의 개발. 파일 구조를 통해 데이터를 저장/관리
1970년대
- DB관리 기법이 태동하던 시기. 계층형(Hierarchical DB), 망형(Network DB)같은 구조가 상용화
1980년대
- 현재 대부분의 기업에서 사용되는 관계형DB의 상용화. Oracle, Sysbase, DB2의 등장
1990년대
- 인터넷 환경의 급속한 발전과 객체 지향 정보를 지원하기 위해 객체 관계형 DB로 발전

관계형 DB
1970suseo E.F.Codd박사의 논문에서 처음 소개
릴레이션에 데이터를 저장, 관리하며 릴레이션을 사용해 연산
관계(Relation)과 조인 연산을 통해 합집합/교집합/차집합 등을 만들 수 있음

DB와 DBMS의 차이점

DB는 데이터를 어떤 형태의 자료구조를 사용하느냐에 따라 나뉜다.

DB종류
- 계층형 DB
  - Tree 형태에 데이터를 저장, 관리
  - 1:N 관계를 표현
- 네트워크형 DB
  - 오너(Owner)와 멤버(Member)형태로 데이터를 저장, 관리
- 관계형 DB
  - 릴레이션에 데이터를 저장, 관리
  - 릴레이션을 사용해 집합, 관계 연산 수행
DBMS
- 계층형/네트워크형/관계형DB등을 관리하기 위한 SW
- Oracle, MS-SQL, MySQL, Sybase등

관계형 DB의 집합 연산

집합 연산	설명
합집합(Union)	두 릴레이션을 하나로 합하는 것 중복된 튜플은 한 번만 조회
차집합(Difference)	본래 릴레이션에는 존재하고 다른 릴레이션에는 존재하지 않는 것을 조회
교집합(Intersection)	두 릴레이션 간 공통된 것을 조회
곱집합(Cartesian product)	각 릴레이션에 존재하는 모든 데이터를 조합하여 연산

관계형 DB의 관계 연산

관계 연산	설명
선택 연산 (Selection)	릴레이션에서 조건에 맞는 행 만을 조회
투영 연산 (Projection)	릴레이션에서 조건에 맞는 속성만을 조회
결합 연산 (Join)	여러 릴레이션의 공통된 속성을 사용해 새로운 릴레이션을 만듬
나누기 연산 (Division)	기존 릴레이션에서 나누는 릴레이션이 가진 속성과 동일한 값을 가지는 튜플을 추출하고 나누는 릴레이션의 속성을 삭제한 후 중복된 행을 제거

SQL의 종류

SQL이란?
관계형 DB에서 데이터 정의/조작/제어를 위해 사용하는 절차형 언어
ANSI/ISO 표준을 준수하므로 DBMS가 변경되어도 그대로 사용할 수 있다.

SQL 표준
표준 설명
ANSI/ISO SQL 표준 INNER JOIN, NATURAL JOIN, USING 조건, ON 조건절 사용
ANSI/ISO SQL3 표준 DBMS벤더 별 차이를 표준화하여 제정

표준	설명
ANSI/ISO SQL 표준	INNER JOIN, NATURAL JOIN, USING 조건, ON 조건절 사용
ANSI/ISO SQL3 표준	DBMS벤더 별 차이를 표준화하여 제정

SQL 실행 순서

개발자가 작성한 SQL문(DDL, DML, DCL 등)은 3단계를 걸쳐서 실행된다.
파싱(문법 검사 - 구문분석) - 실행 - 인출

SQL 실행 순서	설명
파싱 (Parsing)	SQL문의 문법 확인, 구문 분석 구문 분석한 SQL문은 Library Cache에 저장
실행 (Execution)	옵티마이저가 수립한 실행계획에 따라 실행
인출 (Fetch)	데이터를 읽어서 전송

파싱에 대해서

파싱은 소프트 파싱과 하드 파싱이 있다.

분류	소프트 파싱	하드 파싱
행위	SQL 파싱 정보를 저장하는 라이브러리 캐시(Library Cache)에서 파싱된 정보를 찾을 수 있는 경우	라이브러리 캐시에서 파싱된 정보를 찾을 수 없는 경우
동작	파싱 단계를 거치지 않고 바로 실행 단계로 넘어간다.	파싱 후 실행 단계로 넘어간다.
효율성	효율성이 높다	처음 하드 파싱 후 변수의 값만 다른 SELECT문은 소프트 파싱된다.

SQL 명령어의 종류

명령어의 종류	명령어	설명
데이터 조작어 (DML)	* SELECT * INSERT * UPDATE * DELETE	데이터베이스 테이블에 들어있는 데이터에 변형을 가하는 종류의 명령어들을 의미한다. SELECT는 데이터베이스에 들어있는 데이터를 조회하거나 검색하기 위한 명령어이다 (RETRIEVE)
데이터 정의어 (DDL)	* CREATE * ALTER * DROP * RENAME	테이블과 같은 데이터 구조를 정의하는데 사용되는 명령어들로 그러한 구조를 생성하거나 변경하거나 삭제하거나 이름을 바꾸는 데이터 구조와 관련된 명령어들을 DDL이라고 부른다.
데이터 제어어 (DCL)	* GRANT * REVOKE	데이터베이스에 접근하고 객체들을 사용하도록 권한을 주고 회수하는 명령어를 DCL이라고 부른다.
트랜잭션 제어어 (TCL)	* COMMIT * ROLLBACK	논리적인 작업의 단위를 묶어서 DML에 의해 조작된 결과를 작업단위(트랜잭션) 별로 제어하는 명령어를 말한다.

DB는 DDL명령어와 DML명령어를 다르게 처리한다.
- DDL명령어는 직접 DB테이블에 영향을 미치기 때문에 DDL 명령어를 입력하는 순간 명령어에 해당하는 작업이 즉시 완료(AUTO COMMIT) 된다.
- 하지만 DML명령어는 조작하려는 테이블을 메모리 버퍼에 올리고 작업하기 때문에 테이블에 실시간으로 영향을 미치지는 않는다.
  - 따라서 버퍼에서 처리한 DML명령어가 실제 테이블에 반영되기 위해서는 COMMIT명령어를 입력하여 TRANSACTION을 종료해야한다.
  - 하지만 SQL Server의 경우, DML명령어도 DDL명령어 처럼 AUTO COMMIT 처리되므로 COMMIT 명령어가 필요없다.
작업 순서
1. DCL (권한 부여)
2. DDL (테이블 구조 정의)
3. DML (데이터 조회 등)

트랜잭션(Transaction)

DB의 작업을 처리하는 단위

트랜잭션의 특성	설명
원자성 (Atomicity)	DB연산의 전부 또는 일부 실행만이 있다. 트랜잭션의 처리가 완전히 끝나지 않았을 때는 전혀 이루어지지 않은 것과 같아야한다.
일관성 (Consistency)	트랜잭션의 실행 결과로 DB 상태에 모순이 없고, 실행 후에도 일관성이 유지되어야한다.
고립성 (Isolation)	트랜잭션의 실행 중 생성하는 연산의 중간 결과는 다른 트랜잭션이 접근할 수 없다 (부분적인 실행 결과를 다른 트랜잭션이 볼 수 없다)
연속성 (Durability)	트랜잭션이 그 실행을 성공적으로 완료하면 그 결과는 영구적으로 보장되어야한다.

DDL (Data Definition Language)

DB를 사용하기 위헤서는 테이블을 먼저 생성해야 한다.
테이블관리 SQL문

SQL 문	설명
Create Table	새로운 테이블을 생성한다. 테이블을 생성할 때 기본키/외래키/제약사항등을 설정할 수 있다.
Alter Table	생성된 테이블을 변경한다. 칼럼을 추가/변경/삭제할 수 있고 기본키/외래키를 설정할 수 있다.
Drop Table	해당 테이블을 삭제한다. 테이블의 데이터구조와 저장된 데이터 모두 삭제된다.

Create Table문의 구조

CREATE TABLE 테이블명 (칼럼명1 DATATYPE [DEFAULT 형식] primary key, 칼럼명2 DATATYPE [DEFAULT 형식] , ...);

create table 문	설명
CREATE TABLE	`CREATE TABLE A` 는 A라는 테이블을 생성하라는 뜻 괄호 사이에 칼럼을 쓰고 세미콜론으로 끝낸다.
칼럼 정보	테이블에 생성되는 칼럼이름(영문자)와 데이터 타입을 입력한다
데이터 타입	`number`: 숫자형 / `varchar`: 가변길이 문자열 / `char`: 고정 길이 문자열 / `date`: 날짜형
기본키	칼럼 옆에 primary key를 입력하여 기본키로 지정

SELECT문장을 통한 테이블 생성, CTAS

CREATE TABLE 테이블명 AS SELECT 칼럼명 FROM 복사할 테이블명;

칼럼별로 데이터 유형을 다시 재정의하지 않아도된다.
하지만, 기존 테이블의 제약조건 중에 NOT NULL만 새로운 복제 테이블에 적용되고, PK, FK, 고유키, CHECK등의 다른 제약 조건은 없어진다.

테이블의 구조 확인하기

DESC 테이블명;

CONSTRAINT제약조건 사용하기
- 제약조건이란 사용자가 원하는 조건의 데이터만 유지하기 위한, 즉 데이터의 무결성을 위한 방법으로 테이블의 특정 칼럼에 설정하는 제약이다.
- 테이블을 생성할 때 제약조건을 반드시 기술할 필요는 없지만, 이후 ALTER TABLE을 이용하여 추가, 수정하는 경우 데이터가 이미 입력된 상태라면 처리 과정이 쉽지 않아서 초기 테이블 생성 시점부터 적합한 제약조건에 대한 검토가 필요하다.
- 칼럼명 앞에 CONSTRAINT를 입력하여 제약조건을 설정한다.
종류
- PRIMARY KEY
  - 테이블에 저장된 행 데이터를 고유하게 식별하기 위한 기본키를 결정한다. 하나의 테이블에 하나의 기본키 제약만 정의할 수 있다.
  - 기본키 제약을 정의하면 DBMS는 자동으로 UNIQUE 인덱스를 생성하며 기본키를 구성하는 컬럼에는 NULL값이 올 수 없다.
  - 즉, 기본키 제약 = UNIQUE & NOT NULL 제약이 된다.
- UNIQUE KEY
  - 테이블에 저장된 행 데이터를 고유하게 식별하기 위한 고유키를 정의한다.
  - 단 NULL은 고유키 제약의 대상이 아니므로, NULL값을 가진 행이 여러개 있더라도 고유키 제약 위반이 되지 않는다.
- NOT NULL
  - NULL값의 입력을 금지한다.
  - 디폴트 상태에서는 NULL을 허가한다.
  - NOT NULL을 CHECK의 부분 집합으로 이해할 수 있다.
- CHECK
  - 입력할 수 있는 값의 범위 등을 제한한다.
  - CHECK제약으로는 TRUE, FALSE로 평가할 수 있는 논리식을 지정한다.
- FOREIGN KEY
  - 관계형 데이터베이스에서 테이블 간의 관계를 정의하기 위해 기본키를 다른 테이블의 외래키로 복사하는 경우 외래키가 생성된다.
  - 외래키 지정시 참조 무결성 제약 옵션을 선택할 수 있다.

참조 무결성 제약??

마스터 테이블에는 해당 컬럼이 없는데 슬레이브 테이블에는 그 칼럼이 있는 경우
CASCADE 옵션은 참조관계(PK와 FK관계)가 있을 경우 참조되는 데이터도 자동으로 삭제할 수 있는 것이다.
- 다른 테이블을 참조하는 컬럼 앞에 ON DELETE CASCADE를 입력하여 사용할 수 있다.
  - 이 옵션을 통해서 참조 무결성을 준수할 수 있다.

Alter Table문의 구조

테이블 변경은 ALTER TABLE문을 사용한다.
- ALTER TABLE문은 칼럼 추가/변경/삭제 등을 할 수 있다.

테이블 명 변경

ALTER TABLE 기존테이블명 RENAME TO 새로운테이블명;

칼럼 추가

ALTER TABLE 테이블명 ADD (추가할 컬럼명 데이터유형);

칼럼 변경

ALTER TABLE 테이블명 MODIFY (컬럼명 데이터유형)

칼럼 삭제

ALTER TABLE 테이블명 DROP COLUMN 삭제할컬럼명;

컬럼명 변경

ALTER TABLE 테이블명 RENAME COLUMN 기존칼럼명 TO 새로운칼럼명;

제약조건 추가

ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건명 제약조건 (컬럼명);

제약조건 삭제

ALTER TABLE 테이블명 DROP CONSTRAINT 제약조건명;

Drop Table문의 구조

drop table은 테이블의 구조와 데이터를 모두 삭제한다.

DROP TABLE 테이블명;

CASCADE CONSTRAINT 옵션

CONTRAINT 옵션은 해당 테이블과 관계가 있던 참조되는 제약조건에 대해서도 삭제한다는 것을 의미한다.

DROP TABLE 테이블명 CASCADE CONSTRAINT;

TRUNCATE TABLE, DROP TABLE과 DELETE

구분	테이블 정의 존재 유무	저장공간	작업속도	SQL구분
DROP	삭제	반납	빠름	DDL
TRUNCATE	존재	반납	빠름	DDL
DELETE	존재	유지	느림	DML

TRUNCATE TABLE은 테이블 자체가 삭제되는 것이 아니고, 해당 테이블에 들어있던 모든 행들이 제거되고 저장 공간을 재사용 가능하도록 해제한다.
- 빠른 삭제를 위해 로그도 기록하지 않는다.
테이블 구조를 완전히 삭제하기 위해서는 DROP TABLE을 사용하면 된다.

View의 생성과 삭제

뷰(View)는 테이블로부터 유도된 가상의 테이블
실제 데이터를 가지고 있지 않고 테이블을 참조해서 원하는 칼럼만을 조회할 수 있게 한다.
뷰는 데이터 딕셔너리에 SQL문 형태로 저장하고 실행 시에 참조된다.

뷰의 생성

CREATE VIEW 뷰명 AS SELECT 칼럼명 FROM 참조할테이블명;

뷰의 조회

SELECT 칼럼명 FROM 뷰명;

뷰의 삭제

DROP VIEW 뷰명;

뷰의 특징

참조한 테이블이 변경되면 뷰도 변경된다.
뷰의 검색은 참조한 테이블과 동일하게 할 수 있지만, 뷰에 대한 입력/수정/삭제에는 제약이 있다.
뷰는 특정 칼럼만 조회하므로 보안성을 향상시킨다.
한 번 생성된 뷰는 변경이 불가능하다.
- 변경을 원하면 삭제 후 재생성해야 한다.

뷰의 장점	뷰의 단점
특정 칼럼만 조회할 수 있기 때문에 보안성이 있다.	독자적 인덱스를 만들 수 없다.
데이터 관리와 SELECT문이 간단해진다.	삽입/수정/삭제 연산에 제약이 있다.
한 테이블에 여러 뷰를 생성할 수 있다.	데이터 구조 변경이 불가능하다.

DML(Data Manipulation Language)

INSERT문

특정 테이블에 데이터를 입력하는 DML문

INSERT INTO 테이블명 (컬럼명1, 컬럼명2, ...) VALUES (값1, 값2, 값3, ...);

또는

INSERT INTO 테이블명 VALUES (값1, 값2, ...);

주의해야할 점
- 만약, 테이블의 모든 칼럼에 데이터를 입력할 경우 빠짐없이 데이터가 입력되어야한다.
- INSERT 문을 실행했다고 데이터 파일에 저장되는 것은 아니고 최종적으로 TCL문인 COMMIT을 실행해야한다.

select 문을 활용하여 테이블1의 특정 칼럼을 테이블2에 입력하기

INSERT INTO 테이블2 SELECT 칼럼명 FROM 테이블1;

select문을 활용하여 테이블1의 모든 데이터를 테이블2에 입력하기

INSERT INTO 테이블2 SELECT * FROM 테이블1;

No Logging 옵션

DB에 데이터를 입력하면 로그파일에 그 정보를 기록한다.
Check Point라는 이벤트가 발생하면 로그파일 데이터를 데이터 파일에 저장한다.
NoLogging 옵션은 로그파일의 기록을 최소화시켜 입력 시 성능을 향상시키는 방법으로, Buffer Cache라는 메모리 영역을 생략하고 기록한다.
INSERT문에만 효과가 있다.
- DELETE와 UPDATE에에서는 지원되지 않는다.

ex)

ALTER TABLE 테이블명 NOLOGGING;

UPDATE문

입력된 데이터의 값을 수정하려면 UPDATE문을 사용한다.
- 원하는 조건으로 검색해서 해당 데이터를 수정하는 것이다.
- UPDATE문에 조건을 입력하지 않으면 모든 데이터가 수정되므로 유의해야한다.

UPDATE 테이블명 SET 수정할칼럼명 = 새로운 값 WHERE 조건;

DELETE문

원하는 조건을 검색해서 해당되는 행을 삭제한다.
- 조건문을 입력하지 않으면 모든 데이터가 삭제된다.

DELETE FROM 테이블명 WHERE 조건; # 테이블 내 행 삭제

DELETE 삭제할 테이블명; # 테이블 삭제

SELECT문

테이블에 입력된 데이터를 조회하기 위해서 사용한다.
- SELECT문은 특정 칼럼이나 특정 행만을 조회할 수 있다.

SELECT 조회할데이터 FROM 테이블명 WHERE 조건;

SELECT 칼럼 지정 예제
- SELECT A || '맛' FROM FLAVOUR;
  - FLAVOUR테이블의 모든 행에서 A칼럼을 조회한다.
  - A칼럼 뒤에 ‘맛’이라는 문자를 결합한다.
  - ‘A맛’이라는 형태로 출력된다.
DISTINCT와 ALIAS
- DISTINCT
  - 컬럼명 앞에 지정하여 중복된 데이터를 한 번만 조회하게 한다.
  - 모든 조인을 다 실행한 다음에 중복을 제거한다.
    - 그래서 효율적이지는 않다.
- ALIAS
  - 조회된 결과에 일종의 별명을 부여해서 칼럼명/테이블명을 변경할 수 있다.
  - SELECT 칼럼명 [AS] 별칭 FROM 테이블명 WHERE 조건;
Order by를 사용한 정렬
- Order by는 데이터를 오름차순 또는 내림차순으로 출력 직전에 정렬한다.
- 기본 값은 ASC(오름차순) 이며 내림차순으로 정렬하고 싶을 때는 DESC(내림차순)을 명시한다.
- Order by는 정렬하므로 DB메모리를 많이 사용하여 성능 저하가 발생ㅎ나다.
```
SELECT 칼럼명 FROM 테이블 ORDER BY 컬럼명 [ASC | DESC]
```
Index를 사용한 정렬 회피
- 정렬은 데이터베이스에 부하를 주기 때문에 인덱스(기본키)를 사용하여 회피할 수 있다.
- 기본키를 지정하면 자동으로 기본키에 대한 오름차순 인덱스가 생성된다.
- 내림차순으로 출력하고 싶다면 힌트의 개념을 사용한다.
  - SELECT 칼럼명 /* +INDEX_DESC(테이블명) */ FROM 테이블명;
  - 위처럼 INDEX_DESC 힌트를 사용한 SELECT문을 사용하면 PK에 대해 내림차순으로 출력된다.

WHERE문

연산자의 종류

문자 유형 비교법

구분	비교 방법
비교 연산자의 양쪽이 모두 `CHAR`유형인 경우	1. 길이가 서로 다른 CHAR형 타입이면 작은 쪽에 space를 추가하여 길이를 같게 한 후에 비교한다. 2. 서로 다른 문자가 나올 때까지 비교한다. 3. 달라진 첫 번째 문자의 값에 따라 크기를 결정한다. 4. BLANK의 수만 다르다면 서로 같은 값으로 결정한다.
비교 연산자의 어느 한 쪽이 `VARCHAR` 유형인 경우	1. 서로 다른 문자가 나올 때까지 비교한다. 2. 길이가 다르다면 짧은 것이 끝날 때까지만 비교한 후에 길이가 긴 것이 크다고 판단한다. 3. 길이가 같고 다른 것이 없다면 같다고 판단한다. 4. VARCHAR는 NOT NULL까지의 길이를 말한다.
상수값과 비교할 경우	1. 상수 쪽을 변수 타입과 동일하게 바꾸고 비교한다. 2. 변수 쪽이 CHAR유형 타입이면 위의 CHAR유형 타입의 경우를 적용한다. 3. 변수 쪽이 VARCHAR유형 타입이면 위의 VARCHAR 유형 타입의 경우를 적용한다.

LIKE문의 사용

LIKE문은 와일드카드를 사용해서 데이터를 조회할 수 있다.
LIKE문 뒤에 와일드카드를 사용하지 않으면 =과 같다.

와일드 카드	설명
%	어떤 문자를 포함한 모든 것을 조회한다. 정상적인 Index Range Scan이 불가능하다.
-	한 개인 단일 문자를 의미한다.
*	all, 모든 것을 의미한다.

BETWEEN문의 사용

BETWEEN문은 지정된 범위에 있는 값을 조회한다.

SELECT 칼럼명 FROM 테이블명 WHERE 칼럼명 BETWEEN 조건1 AND 조건2;

IN문의 사용
- OR의 의미를 가지고 있어서 OR조건 중 하나만 만족해도 조회가 가능하다.
```
SELECT * FROM EMP WHERE JOB IN ('CLERK', 'MANAGER');
```
- IN 조건에 여러 개의 칼럼을 사용할 수도 있다.
```mysql SELECT * FROM EMP WHERE (JOB, ENAME) IN ((‘CLERK’, ‘SALES’),(‘AAA’, ‘BBB’));

NULL값 조회

NULL의 특징

NULL은 모르는 값, 값의 부재를 의미한다.
NULL과의 모든 비교는 알 수 없음 을 반환한다.
NULL에 숫자/날짜를 더하면 NULL이 된다.
NULL은 비교연산자로 비교할 수 없다.
- 비교한다면 무조건 FALSE가 나온다.

NULL값 조회하기

SELECT * FROM 테이블명 WHERE 칼럼명 IS NULL
SELECT * FROM 테이블명 WHERE 칼럼명 IS NOT NULL

NULL 관련 함수

NULL 함수	예시
`NVL()`	NVL(MGR, 0); MGR칼럼이 NULL이면 0으로 바꾼다.
`NVL2(식, 참일 경우, 거짓일 경우)`	NVL2(MGR, 1, 0); MGR칼럼이 NULL이 아니면 1을 NULL이면 0을 반환한다.
`NULLIF()`	NULLIF(n1, n2); n1과 n2가 같으면 NULL을 같지 않으면 n1을 반환한다.
`COALESCE()`	주어진 인자들 중 NULL이 아닌 첫 번째 값을 반환한다. 여러개의 인자를 받을 수 있다. `SELECT COALESCE(col1, col2, col3, ..., 'default') FROM table;`

GROUP 연산

GROUP BY문

GROUP BY는 테이블에서 행을 소규모 그룹화하여 합계, 평균, 최대, 최소 등을 계산할 수 있다. (집계)
HAVING절에 조건문을 사용한다.
ORDER BY절도 사용할 수 있다.

SELECT DPT_ID, SUM(SAL) FROM EMP GROUP BY DPT_ID;

예시에서 조회하는 것은 부서 아이디와 부서 아이디별 직원들의 월급 합계이다.

HAVING문

GROUP BY문에 조건절을 사용하려면 HAVING문을 사용해야한다.
만약, WHERE절에 조건문을 사용하게 된다면 GROUP BY 대상에서 제외된다.
- 이는 WHERE절에서 필터링 된 결과에 대해서 GROUP BY가 이루어지기 때문이다.

SELECT DPT_ID, SUM(SAL) FROM EMP GROUP BY DPT_ID HAVING SUM(SAL) > 30000;

예시에서는 부서 아이디 별 직원들의 월급 합계를 출력하지만, HAVING문의 조건으로 인해 월급 합계가 30000을 넘는 행들만 조회한다.

집계함수의 종류

WHERE절이 집계 함수보다 먼저 평가되므로, WHERE절에 집계함수를 사용할 수 없다.

집계함수	설명
COUNT()	행 수를 조회한다. `COUNT(*)`: NULL행 포함해서 계산 `COUNT(컬럼)`: NULL값을 제외한 행 수 계산
SUM()	합계를 계산한다.
AVG()	평균을 계산한다.
MAX(), MIN()	최댓값/최솟값을 계산한다.
STDDEV()	표준편차를 계산한다.
VARIAN()	분산을 계산한다.

⭐️ SELECT문의 실행 순서

GROUP BY 절과 ORDER BY 절이 같이 사용될 때, SELECT 문장을 6개의 절로 구성된다.
- SELECT
- FROM
- WHERE
- GROUP BY
- HAVING
- ORDER BY
이 때 수행 단계는 아래의 순서를 따른다.
1. 발췌 대상 테이블 참조 (FROM)
2. 발췌 대상 데이터가 아닌 것은 제거 (WHERE)
3. 행들을 소그룹화 (GROUP BY)
4. 그룹핑된 값의 조건에 맞는 것만을 출력 (HAVING)
5. 데이터를 출력/계산한다 (SELECT)
6. 데이터를 정렬한다 (ORDER BY)

명시적 형변환과 암시적 형변환

형변환이란?
- 2개의 데이터의 데이터 타입이 일치하도록 변환하는 것
- 명시적 형변환과 암시적 형변환으로 구분
**인덱스 칼럼에 형변환을 수행한다면 인덱스를 사용하지 못한다.**

명시적 형변환 vs 암시적 형변환

명시적 형변환
- 형변환 함수를 사용하여 데이터 타입을 일치시키는 것
- 개발자가 SQL을 사용할 때 형변환 함수를 사용해야한다.
암시적 형변환
- 개발자가 형변환을 하지 않은 경우, DBMS가 자동으로 형변환하는 것

형변환 함수	설명
TO_NUMBER(문자열)	문자열을 숫자로 변환
TO_CHAR(숫자/날짜, [FORMAT])	숫자/문자를 지정된 FORMAT의 문자로 변환
TO_DATE(문자열, FORMAT)	문자열을 지정된 FORMAT의 날짜형으로 변환

내장형 함수 (BUILT-IN FUNCTION)

모든 DB는 SQL에서 사용할 수 있는 내장형 함수를 가진다.

DUAL 테이블

Oracle DB에 의해 자동으로 생성되는 테이블
- Oracle DB 사용자가 임시로 사용할 수 있는 테이블로, 내장형 함수를 실행할 때도 사용할 수 있다.
- Oracle DB의 모든 사용자가 사용할 수 있다.
- Oracle은 기본적으로 DUAL테이블이라는 Dummy테이블이 존재한다.

SELECT 1 FROM DUAL
	UNION SELECT 2 FROM DUAL
	UNION SELECT 1 FROM DUAL;

위 쿼리 실행 결과는 1, 2이다.

내장형 함수의 종류

종류	내용	함수의 예
문자형 함수	문자를 입력하면 문자나 숫자 값을 반환	LOWER, UPPER, SUBSTR/SUBSTRING, LENGTH/LEN, LTRIM, RTRIM, TRIM, ASCII
숫자형 함수	숫자를 입력하면 숫자 값을 반환한다.	ABS, MOD, ROUND, TRUNC, SIGN, CHR/CHAR, CEIL/CEILING, FLOOR, EXP, LOG, LN, POWER, SIN, COS, TAN
날짜형 함수	DATE타입의 값을 연산한다.	SYSDATE/GETDATE, EXTRACT/DATEPART, TO_NUMBER(TO_CHAR(d, ‘YYYY’\|’MM’\|’DD’))/YEAR\|MONTH\|DAY
변환형 함수	문자, 숫자, 날짜형 값의 데이터 타입을 변환한다.	TO_NUMBER, TO_CHAR, TO_DATE/CAST, CONVERT
NULL관련 함수	NULL을 처리하기 위한 함수	NVL/ISNULL, NULLIF, COALESCE

DECODE문과 CASE문

DECODE문
- 특정 조건이 참과 거짓일 경우를 분기하여 값을 지정할 수 있다.
- `SELECT DECODE(DPT_ID, 23, ‘TRUE’, ‘FALSE’) FROM EMP;
CASE문
- WHEN ~ THEN ELSE -END 구문을 상요해 조건문으로 사용한다.
```
SELECT 
	CASE
		WHEN DPT_ID = 23 THEN 'GROUP A'
		WHEN DPT_ID = 24 THEN 'GROUP B'
		ELSE 'GROUP C'
    END
		FROM EMP;
```
위 쿼리에서 DPT_ID가 23이면 GROUP A를, 24면 GROUP B를 그렇지 않으면 GROUP C를 출력한다.

ROWNUM과 ROWID

ROWNUM
- SELECT문의 결과에 대해 논리적인 일련번호를 부여
  - 주로, 조회되는 행 수를 제한할 때 자주 사용된다.
- ROWNUM을 사용해서 한 개의 행을 가져올 수는 있지만 여러 개의 행을 가지고 올 때는 인라인 뷰를 사용하고 ROWNUM에 별칭을 붙여야한다.
```
SELECT * FROM EMP WHERE ROWNUM <= 1; # 한 행 조회
SELECT * FROM (SELECT ROWNUM list, ENAME FROM EMP) WHERE LIST <= 5;
```

ROWID

모든 테이블은 ROWID를 가진다.
ROWID는 오라클 DB내에서 데이터를 구분할 수 있는 유일한 값이다.
- SELECT ROWID, EMPNO FROM EMP 와 같은 select문으로 확인할 수 있다.
- ROWID는 데이터가 어떤 데이터 파일, 어떤 블록에 저장되어 있는지를 알 수 있다.

SELECT ROWID FROM 테이블명;

구조	길이	설명
오브젝트 번호	1~6	오브젝트 별로 가진 유일한 값. 해당 오브젝트가 속한 값
상대 파일 번호	7~9	tablespace에 속한 데이터 파일에 대한 상대 파일 번호
블록 번호	10~15	데이터가 데이터 파일 내부의 어느 블록에 있는 지를 의미
데이터 번호	16~18	데이터가 데이터 블록에 저장된 순서를 의미

WITH구문

with 구문은 서브쿼리를 사용해 임시 테이블이나 뷰처럼 사용할 수 있다.
서브쿼리 블록에 별칭을 지정할 수도 있다.

WITH 임시테이블명 AS (SELECT 칼럼명 FROM 테이블명);
SELECT * FROM 임시테이블 명;

특징	임시테이블(CTE)	뷰 (View)
정의와 사용 목적	* `WITH` 구문을 사용하여 정의되며, 쿼리 내에서만 유효 * 복잡한 쿼리를 보다 간결하게 작성하기 위해 주로 사용 * CTE는 재귀적인 쿼리를 작성할 수 있는 기능을 제공	* 데이터베이스에 저장된 객체로, 물리적으로 존재하며 다른 쿼리에서 재사용할 수 있다. * 특정 쿼리의 결과를 테이블처럼 사용할 수 있게 해주며, 복잡한 쿼리를 단순화하는데 유용 * 뷰는 데이터베이스 스키마의 일부로 존재하고, 여러 사용자가 동시에 접근할 수 있음
생명주기	* CTE는 정의된 쿼리 내에서만 존재하며, 해당 쿼리의 실행이 끝나면 사라짐 * 쿼리 내에서 여러 번 참조할 수 있지만 쿼리가 끝나면 더 이상 접근할 수 없음	* 뷰는 데이터베이스에 저장되며, 한 번 정의하면 필요할 때마다 여러 쿼리에서 사용할 수 있음 * 뷰를 삭제하지 않는 한, 데이터베이스에 계속 존재
성능	* 주로 쿼리의 가독성을 높이는데 사용되며, 성능에 큰 영향을 미치진 않는다. 하지만, 복잡한 재귀CTE는 성능에 영향을 줄 수 있다.	* 뷰의 성능은 기본 테이블의 구조 및 인덱스에 따라 달라질 수 있다. 뷰를 사용하는 쿼리가 복잡한 경우 성능에 영향을 줄 수 있다.
업데이트 가능성	* CTE는 일반적으로 단순히 데이터를 조회하는데 사용되므로, 직접적인 업데이트는 지원하지 않음	* 뷰는 특정 조건을 만족하는 경우 업데이트가 가능하며, 기본 테이블에 대한 변경사항을 반영할 수 있음. 하지만, 모든 뷰가 업데이트 가능한 것은 아님.

Certificate, SQLD

db sql

This post is licensed under CC BY 4.0 by the author.