SQL 알아두면 좋은 것들 가볍게 정리

SELECT * FROM WORDS WHERE A = 1 AND (B = 1 OR B = 2);

SELECT * FROM WORDS WHERE NOT (B = 1 OR B = 2);

수치형(INT), 문자열형(VARCHAR) 정렬 주의

수치형	문자열형
1	1
2	10
10	2

문자형태로 숫자를 저장하면 1부터 10까지 차례로 정렬되는 것이 아니라 문자 순서대로 정렬된다.

NULL 값 정렬순서

NULL 값을 가지는 행(row)은 가장 먼저 표시되거나 가장 나중에 표시 (데이터베이스 제품에 따라 기준 다름)

NULL 값 연산

NULL + 1, 1 / NULL 등 어떤 연산을 해도 결과는 NULL

LIMIT

SELECT * FROM WORDS WHERE B = 1 OR B = 2 ORDER BY A LIMIT 5;

처리순서 : WHERE → SELECT → ORDER BY → LIMIT

(SELECT에서 지정한 컬럼 별명(Alias)은 WHERE에서 쓸 수 없음 - WHERE가 SELECT보다 먼저 처리되기 때문)

(ORDER BY에선 사용 가능 - SELECT 이후에 처리되기 때문)

CASE 문

> 검색 CASE

CASE 
    WHEN SEX = 1 THEN '남자'
    WHEN SEX = 2 THEN '여자'
    WHEN SEX IS NULL THEN '널'
    ELSE '없음'
END

> 단순 CASE

CASE SEX
    WHEN 1 THEN '남자'
    WHEN 2 THEN '여자'
    WHEN NULL THEN '널'
    ELSE '없음'
END

단순 CASE 문은 SEX = NULL로 처리해서 NULL값 처리가 안됨. (NULL은 IS NULL로 처리해야함)

* ELSE는 생략 가능 (생략하면 ELSE NULL로 처리)

물리삭제와 논리삭제

물리삭제 : DELETE 명령으로 직접 데이터 삭제

논리삭제 : 삭제플래그로 삭제한 것 처럼 (삭제되기 전 상태로 되돌릴 수 있음. 삭제해도 저장공간 안 늘어남.)

seq	word	flag (0: 삭제 1: 사용)
1	aaa	1
2	bbb	0
3	ccc	0

집계함수, 그룹화

복수의 값에서 하나의 값을 계산

집합 안에 NULL 값이 있을 경우 이를 제외하고 처리

COUNT(*)는 NULL 값도 카운트

WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

(WHERE에선 집계함수 사용X / HAVING으로 조건 걸어줌 / 별명(Alias) 사용X)

그룹화하면 하나의 값만 반환하는데 그룹화 되지 않은 것이 포함되어 있으면 어떤 것을 반환할지 몰라서 에러 발생

SELECT A, B FROM WORD GROUP BY A;

(B는 그룹화 되어 있지 않으므로 에러)

서브쿼리

스칼라 값 : SELECT 명령으로 반환하는 하나의 값, 단일 값

스칼라 서브쿼리 : 스칼라 값을 반환하는 서브쿼리

MySQL (FROM 생략 가능)

SELECT
    SELECT A, B FROM WORD.
    SELECT C, D FROM WORD2;
    

Oracle (FROM DUAL)

SELECT
    SELECT A, B FROM WORD.
    SELECT C, D FROM WORD2
FROM DUAL;
    

EXISTS, NOT EXISTS (상관 서브쿼리)

UPDATE MAN SET JOB = '학생'
WHERE EXISTS (SELECT * FROM STUDENT WHERE STUDENT.NO = MAN.NO);

STUDENT 테이블에 MAN의 번호가 있으면 참을 반환하고 해당 번호의 MAN 직업을 "학생"으로 업데이트

(거짓을 반환하려면 NOT EXISTS)

상관 서브쿼리 : 부모 명령과 자식인 서브쿼리가 특정 관계를 맺는 것

(상관 서브쿼리는 부모와 연결되어 처리되서 서브쿼리 부분만 따로 실행할 수 없음)

데이터베이스 객체

데이터베이스 내에 실체를 가짐 (테이블, 뷰, 인덱스 등)

(SELECT, INSERT 등의 명령은 실제가 없음)

스키마

데이터베이스 객체는 스키마 안에 만들어 짐.

스키마 설계 : 데이터베이스에 테이블을 구축해나가는 작업 (DDL로 정의)

* DDL, DML, DCL

<출처 : https://www.w3schools.in/mysql/ddl-dml-dcl/>

테이블 삭제

DROP TABLE 테이블명

테이블 전체 삭제 (공간도 삭제), Rollback 불가능

TRUNCATE TABLE 테이블명

데이터, 인덱스 등 삭제 (테이블은 존재함), Rollback 불가능

DELETE FROM 테이블명

데이터 지움, WHERE 사용가능(원하는 데이터만 삭제 가능), Rollback 가능

https://wikidocs.net/4021 여기에서 그림보면 바로 이해가능 !

제약 추가

열 제약 : 열에 대해 정의 (NOT NULL, UNIQUE)

테이블 제약 : 복수 열에 제약(PRIMARY KEY(a, b))

제약 이름 : CONSTRAINT pkey_name PRIMARY KEY(a, b)

테이블 변경할 때 제약 위반하는 데이터가 있는지 먼저 검사, 있다면 에러 발생

ALTER TABLE 테이블명 MODIFY a VARCHAR(30) NOT NULL;

a열(컬럼)에 NULL이 있다면 에러발생 (ALTER 처리 X)

제약 삭제

ALTER TABLE 테이블명 DROP CONSTRAINT pkey_name;

제약명을 지정해 삭제

(기본키는 테이블당 하나만 설정할 수 있어서 제약명 지정안하고 삭제 가능 - DROP PRIMARY KEY)

기본키 복수 지정 (기본키 여러개)

컬럼명1 PRIMARY KEY
컬럼명2 PRIMARY KEY

불가능(기본키는 하나만!)

컬럼명1
컬럼명2
CONSTRANT 기본키이름 PRIMARY KEY(컬럼명1, 컬럼명2)

가능(두 컬럼 합쳐서 기본키 하나로 간주되서 가능)

인덱스

대부분 데이터베이스는 테이블 삭제하면 인덱스도 삭제된다.

인덱스는 대표적으로 '이진 트리' 알고리즘 쓰임

CREATE INDEX 인덱스명 ON 테이블명(인덱스로 지정할 컬럼명)

인덱스 생성

SELECT * FROM 테이블명 WHERE 인덱스 지정한 컬럼

빨라진다! 컬럼만 사용해도 자동으로 인덱스를 탐

INSERT는 최신 상태로 인덱스를 갱신하는 처리가 늘어나서 처리속도가 떨어짐

WHERE에 인덱스로 지정한 컬럼을 사용하지 않으면 인덱스를 안탐

(데이터베이스의 HINT를 활용하면 강제로 인덱스를 타게할 수는 있다)

EXPLAIN

EXPLAIN SELECT * FROM 테이블명 WHERE a = 'a';

해당 쿼리가 인덱스를 타는지 확인 가능

뷰

SELECT는 이름 지정도 안되고 데이터베이스에 등록도 안되므로 객체가 아님.

뷰 : 이런 SELECT 명령을 객체로 이름을 붙여 관리할 수 있도록 한 것

뷰는 저장공간을 가지지 않기 때문에 SELECT 명령에서만 사용하기를 권장

저장공간 사용하지 않는 대신 CPU를 사용 (Materialized View, 함수 테이블)

뷰 생성

CREATE VIEW 뷰명(컬럼명1, 컬럼명2) AS SELECT 컬럼명1, 컬럼명2 FROM 테이블명;

컬럼명 생략하면 SELECT 구에서 지정하는 컬럼으로 자동 지정

* 물리명 : 컬럼명(보통 영어), 논리명 : 알아보기 쉬운 이름(보통 한글)

* LOB형 : 큰 데이터 다루는 자료형, 인덱스 지정 X

* LEFT, RIGHT, INNER, OUTER 조인

* HASH, SORT, NL 조인

* 관계형 모델

* 정규화

Reference

SQL 첫걸음 (아사이 아츠시 저)