[멋사 백엔드 19기] TIL 18일차 Database 활용

Seonghun Park

Developer.

[멋사 백엔드 19기] TIL 18일차 Database 활용

Sep 11, 2025

멋쟁이사자처럼
멋사
백엔드
TIL
Database

📂 목차

System Table/Metadata Table
SQL 기초
- DDL
- DML
  - SELECT
  - 함수
  - UPDATE
  - DELETE
  - INSERT
  - DO
  - CALL
Relational Model
- Structure of Relational Databases
  - Domain Atomicity
  - Domain Nullity
- Relational Model 의 한계

📚 본문

System Table/Metadata Table

보통 기본적으로 생성되어 있는 테이블을 시스템 테이블, 메타데이터 테이블이라고 부른다.

여기에는 mysql. 의 명칭으로 시작하는

사용자 계정
권한
스토리지 엔진 정보
테이블 구조

등등의 관리에 필요한 정보들이 테이블로 정의되어 있고, 이를 수정하면 사용자 계정이 추가된다던지, 권한이 바뀐다던지 할 때 이 테이블이 수정되게 된다. 하지만 이를 직접 접근해서 수정하면 권한에 문제가 생기기 때문에 보통은 create user / grant / drop user 등으로 관리한다.

유저 생성

-- 모든 호스트에서 접속 가능한 사용자
CREATE USER 'seonghun'@'%' IDENTIFIED BY '1234';

-- 로컬에서만 접속 가능한 사용자
CREATE USER 'seonghun'@'localhost' IDENTIFIED BY '1234';

여기서 seonghun 이라는 user 속성 그리고 host 속성으로는 localhost 로 지정하여 mysql.user 라는 테이블에 유저를 생성함을 볼 수 있다. 이렇게 생성된 유저는 localhost 에서만 접속 가능하다.

모든 호스트에서 접속이 가능하게 하려면 전자의 명령어에서 와일드카드 %를 쓰자.

권한 부여

-- 특정 데이터베이스에 대한 모든 권한
GRANT ALL PRIVILEGES ON liondb.* TO 'seonghun'@'%';
GRANT ALL PRIVILEGES ON liondb.* TO 'seonghun'@'localhost';

GRANT ON TO 문을 사용하여 특정 데이터베이스 개체에 대한 특정 유저의 접근 권한을 설정할 수 있다.

위 예제는 liondb 라는 명을 가지는 database 의 하위(테이블, 레코드) 모든 개체에 대한 권한을 주는 것을 볼 수 있다.

-- 사용자 확인
SELECT user, host FROM mysql.user WHERE user = 'carami';

-- 사용자 삭제
DROP USER 'carami'@'%';
DROP USER 'carami'@'localhost';

전자의 명령어는 직접 메타테이블을 조회하는 쿼리이지 수정은 하지 않는다. 따라서 그렇게 주의할 작업은 아니며, 하위 제공되어지는 명령어을 사용하자.

권한 해제

권한을 주는게 있었다면 권한을 빼는 것도 있어야 한다.

-- 모든 권한 취소
REVOKE ALL PRIVILEGES ON liondb.* FROM 'carami'@'%';

-- 특정 권한만 취소
REVOKE INSERT, UPDATE ON liondb.* FROM 'carami'@'%';

-- 권한 적용
FLUSH PRIVILEGES;

여기서 FLUSH PRIVILEGES 가 궁금할 수 있는데 bash 같은 거의 환경 변수 설정 수정을 다 하고 실제로 ./zshrc 와 같이 실행을 시켜줘야 적용되는 것처럼 이 또한 해당 명령어를 넣어주어야 한다.

이제 본격적으로 Mysql 을 사용하자.

SQL 기초

SQL 도 언어이기 때문에 계산이 된다.

-- 수식 계산
SELECT 3 + 5;
SELECT 10 * 2.5;

-- 함수 사용
SELECT SIN(PI()/4), (4+1)*5;

-- 여러 문장 실행
SELECT VERSION(); SELECT NOW();

-- 여러 줄에 걸쳐 작성
SELECT 
    USER(),
    CURRENT_DATE;

구문은 보통 ; 로 구분된다. 또한 정해놓은 함수(SIN, COS 등등) 을 사용할 수 있다. 하지만 굳이 이 정도의 계산을 할거였으면 이런 어플리케이션을 만들지 않았을 것이다.

DDL

데이터 정의어로 테이블을 생성하자.

-- employees 테이블 생성
CREATE TABLE employees (
    employee_id INT(11) UNSIGNED NOT NULL,
    first_name VARCHAR(20),
    last_name VARCHAR(25) NOT NULL,
    email VARCHAR(25) NOT NULL,
    phone_number VARCHAR(20),
    hire_date DATE NOT NULL,
    job_id VARCHAR(10) NOT NULL,
    salary DECIMAL(8, 2) NOT NULL,
    commission_pct DECIMAL(2, 2),
    manager_id INT(11) UNSIGNED,
    department_id INT(11) UNSIGNED,
    PRIMARY KEY (employee_id)
);

-- 테이블 목록 확인
SHOW TABLES;

-- 테이블 구조 확인
DESC employees;

NOT NULL 로 보이는 것이 integrity constraints 중 하나이다.

CREATE DATABASE

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
    [create_option] ...

정의서를 보면 create database 나 create schema 는 동일하게 동작한다. if not exists 옵션을 통해 이미 같은 데이터베이스가 존재할 시 생성하지 않도록 할 수 도 있다.

create_option: [DEFAULT] {
    CHARACTER SET [=] charset_name
  | COLLATE [=] collation_name
  | ENCRYPTION [=] {'Y' | 'N'}
}

데이터베이스 생성 시 추가 설정 옵션이다. =은 생략 가능하며, 문자열 세트로 사용할 문자 인코딩이나, COLLATE 와 같은 문자 비교 방식(정렬 순서, 대소문자 구분 등) 을 정할 수 있다.

CHARACTER SET: DB 에서 사용할 문자 인코딩 설정
COLLATE: 문자 비교 방식 설정 (정렬 순서, 대소문자 구분 등)
ENCRYPTION: DB 파일 자체를 암호화할지 여부

각 옵션에는 DEFAULT 라는 옵션을 넣을 수 있고, charset_name, collation_name 은 다음을 통해 볼 수 있다.

SHOW CHARACTER SET;
+---------+----------------------+----------------+
| Charset | Description          | Default collation |
+---------+----------------------+----------------+
| utf8mb4 | UTF-8 Unicode        | utf8mb4_0900_ai_ci |
| latin1  | cp1252 West European | latin1_swedish_ci  |
+---------+----------------------+----------------+

SHOW COLLATION;
+---------------------+---------+---------+----------+---------+
| Collation           | Charset | Default | Compiled | Sortlen |
+---------------------+---------+---------+----------+---------+
| utf8mb4_general_ci  | utf8mb4 | Yes     | Yes      | 1       |
| utf8mb4_unicode_ci  | utf8mb4 | No      | Yes      | 8       |
+---------------------+---------+---------+----------+---------+

CREATE PROCEDURE and CREATE FUNCTION

CREATE
    [DEFINER = user]
    PROCEDURE [IF NOT EXISTS] sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body

CREATE
    [DEFINER = user]
    FUNCTION [IF NOT EXISTS] sp_name ([func_parameter[,...]])
    RETURNS type
    [characteristic ...] routine_body

proc_parameter:
    [ IN | OUT | INOUT ] param_name type

func_parameter:
    param_name type

type:
    Any valid MySQL data type

characteristic: {
    COMMENT 'string'
  | LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
}

routine_body:
    SQL routine

길지만 거의 모든 옵션들이 유사하다. DEFINER 는 프로시저를 정의한 계정을 지정할 수 있다.

([proc_parameter[,…]]): 입력/출력 파라미터 목록
routine_body; 실제 실행할 SQL 코드 블록

function 은 procedure 와 비슷하지만 무조건 반환값이 있어야 한다.

[ IN | OUT | INOUT ] param_name type

IN: 입력용
OUT: 출력용
INOUT: 입출력용
type: INT, VARCHAR 등 타입

Any valid MySQL data type: MySQL 에서 지원하는 모든 데이터 타입 가능

characteristics

{
    COMMENT 'string'
  | LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
}

추후에 공부해도 됨

CREATE TABLE

가장 중요한 DDL 이다.

check_constraint_definition:
    [CONSTRAINT [symbol]] CHECK (expr) [[NOT] ENFORCED]

reference_definition:
    REFERENCES tbl_name (key_part,...)
      [MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]
      [ON DELETE reference_option]
      [ON UPDATE reference_option]

reference_option:
    RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT

하나하나씩 살펴본다.

일반 테이블 생성

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    (create_definition,...)
    [table_options]
    [partition_options]

TEMPORARY: 임시 테이블 생성
create_definition: 컬럼, 인덱스, 제약 조건 등등 정의
table_options: 문자셋, 정렬, 스토리지 엔진 등 테이블 옵션
partition_options: 파티션 기능 사용 시 옵션

table options

table_options:
    table_option [[,] table_option] ...

table_option: {
    AUTOEXTEND_SIZE [=] value
  | AUTO_INCREMENT [=] value
  | AVG_ROW_LENGTH [=] value
  | [DEFAULT] CHARACTER SET [=] charset_name
  | CHECKSUM [=] {0 | 1}
  | [DEFAULT] COLLATE [=] collation_name
  | COMMENT [=] 'string'
  | COMPRESSION [=] {'ZLIB' | 'LZ4' | 'NONE'}
  | CONNECTION [=] 'connect_string'
  | {DATA | INDEX} DIRECTORY [=] 'absolute path to directory'
  | DELAY_KEY_WRITE [=] {0 | 1}
  | ENCRYPTION [=] {'Y' | 'N'}
  | ENGINE [=] engine_name
  | ENGINE_ATTRIBUTE [=] 'string'
  | INSERT_METHOD [=] { NO | FIRST | LAST }
  | KEY_BLOCK_SIZE [=] value
  | MAX_ROWS [=] value
  | MIN_ROWS [=] value
  | PACK_KEYS [=] {0 | 1 | DEFAULT}
  | PASSWORD [=] 'string'
  | ROW_FORMAT [=] {DEFAULT | DYNAMIC | FIXED | COMPRESSED | REDUNDANT | COMPACT}
  | START TRANSACTION 
  | SECONDARY_ENGINE_ATTRIBUTE [=] 'string'
  | STATS_AUTO_RECALC [=] {DEFAULT | 0 | 1}
  | STATS_PERSISTENT [=] {DEFAULT | 0 | 1}
  | STATS_SAMPLE_PAGES [=] value
  | tablespace_option
  | UNION [=] (tbl_name[,tbl_name]...)
}

넘어간다.

create_definition

create_definition: {
    col_name column_definition
  | {INDEX | KEY} [index_name] [index_type] (key_part,...)
      [index_option] ...
  | {FULLTEXT | SPATIAL} [INDEX | KEY] [index_name] (key_part,...)
      [index_option] ...
  | [CONSTRAINT [symbol]] PRIMARY KEY
      [index_type] (key_part,...)
      [index_option] ...
  | [CONSTRAINT [symbol]] UNIQUE [INDEX | KEY]
      [index_name] [index_type] (key_part,...)
      [index_option] ...
  | [CONSTRAINT [symbol]] FOREIGN KEY
      [index_name] (col_name,...)
      reference_definition
  | check_constraint_definition
}

기본적으로 정의서에서 {} 로 되어 있는건 여러 개가 올 수 있다는 의미다.

컬럼 정의: col_name column_definition
- 여러개 올 수 있음
인덱스: INDEX KEY [index_name] [index_type] (key_part, …) [index_option]
FULLTEXT / SPATIAL 인덱스: 텍스트 검색 또는 공간 데이터 용
PRIMARY KEY, UNIQUE, FOREIGN KEY 는 가볍게 읽고 넘기자. symbol 은 해당 constraint 명칭이다.

index

index_type:
    USING {BTREE | HASH}

index_option: {
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name
  | COMMENT 'string'
  | {VISIBLE | INVISIBLE}
  |ENGINE_ATTRIBUTE [=] 'string'
  |SECONDARY_ENGINE_ATTRIBUTE [=] 'string'
}

BTREE, HASH 둘 중 사용할 수 있는데 보통은 HASH 를 사용하는게 빠르지만, 범위 검색이나 정렬에는 부적합하게 된다. 따라서 대부분 BTREE 를 사용하며 이게 기본값이다.

KEY_BLOCK_SIZE: 인덱스 블록의 크기를 바이트 단위로 지정가능

column_definition

column_definition: {
    data_type [NOT NULL | NULL] [DEFAULT {literal | (expr)} ]
      [VISIBLE | INVISIBLE]
      [AUTO_INCREMENT] [UNIQUE [KEY]] [[PRIMARY] KEY]
      [COMMENT 'string']
      [COLLATE collation_name]
      [COLUMN_FORMAT {FIXED | DYNAMIC | DEFAULT}]
      [ENGINE_ATTRIBUTE [=] 'string']
      [SECONDARY_ENGINE_ATTRIBUTE [=] 'string']
      [STORAGE {DISK | MEMORY}]
      [reference_definition]
      [check_constraint_definition]
  | data_type
      [COLLATE collation_name]
      [GENERATED ALWAYS] AS (expr)
      [VIRTUAL | STORED] [NOT NULL | NULL]
      [VISIBLE | INVISIBLE]
      [UNIQUE [KEY]] [[PRIMARY] KEY]
      [COMMENT 'string']
      [reference_definition]
      [check_constraint_definition]
}

NULL > DEFAULT > AUTOINCREMENT > UNIQUE > PRIMARY 혹은
AS > UNIQUE > PRIMARY 순서

SELECT를 통한 테이블 생성

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    [(create_definition,...)]
    [table_options]
    [partition_options]
    [IGNORE | REPLACE]
    [AS] query_expression

AS SELECT 문을 통해 SELECT 결과를 바로 테이블로 생성
IGNORE / REPLACE: 기존 테이블과 충돌 처리

LIKE를 통한 기존 테이블을 복제

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    { LIKE old_tbl_name | (LIKE old_tbl_name) }

기존 테이블 구조(컬럼, 인덱스)만 복제하며, 데이터는 복제되지 않는다. 즉 스키마만 복제

DML

데이터 조작어를 살펴보자.

SELECT

MySQL 8.4 SELECT 문, 위 테이블에 들어갈 레코드들을 조회하는 SELECT 문을 보자.

SELECT
    [ALL | DISTINCT | DISTINCTROW ]
    [HIGH_PRIORITY]
    [STRAIGHT_JOIN]
    [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
    [SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr [, select_expr] ...
    [into_option]
    [FROM table_references
      [PARTITION partition_list]]
    [WHERE where_condition]
    [GROUP BY [ {col_name | expr | position}, ... [WITH ROLLUP]
              | ROLLUP ({col_name | expr | position}, ...)] ]
    [HAVING where_condition]
    [WINDOW window_name AS (window_spec)
        [, window_name AS (window_spec)] ...]
    [ORDER BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [into_option]
    [FOR {UPDATE | SHARE}
        [OF tbl_name [, tbl_name] ...]
        [NOWAIT | SKIP LOCKED]
      | LOCK IN SHARE MODE]
    [into_option]

into_option: {
    INTO OUTFILE 'file_name'
        [CHARACTER SET charset_name]
        export_options
  | INTO DUMPFILE 'file_name'
  | INTO var_name [, var_name] ...
}

export_options:
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]

기본적으로 위처럼 MySQL 문 정의서가 작성되어 있는데, 기본 구조는 다음과 같다:

SELECT
    [ALL | DISTINCT | DISTINCTROW ]
    [HIGH_PRIORITY]
    [STRAIGHT_JOIN]
    [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
    [SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr [, select_expr] ...

[

] 는 BNF 에서 쓰이는 표기법인데, 그냥 Data Dictionary 와 유사하다.

는 or 을 의미하기 때문에 안의 요소중 하나가 들어간다고 볼 수 있다.

ALL / DISTINCT / DISTINCTROW: 중복 행 처리 옵션
HIGH_PRIORITY: 테이블이 바쁠 때 먼저 처리
STRAIGHT_JOIN: 조인 순서를 강제시키는 옵션
SQL_SMALL_RESULT, SQL_BIG_RESULT, SQL_BUFFER_RESULT: 결과 집합 최적화 힌트, 세 옵션 전부 다 들어갈 수 있음
SQL_NO_CACHE: 쿼리 캐시 사용 안함
SQL_CALC_FOUND_ROWS: LIMIT 와 함께 전체 행 수 계산
select_expr [, select_expr] … : 선택할 컬럼 또는 계산식

[into_option]

...

into_option: {
    INTO OUTFILE 'file_name'
        [CHARACTER SET charset_name]
        export_options
  | INTO DUMPFILE 'file_name'
  | INTO var_name [, var_name] ...
}

export_options:
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]

into 는 결과를 내보낼 때 다시 보면 좋을 듯 하다.

[FROM table_references
  [PARTITION partition_list]]
[WHERE where_condition]

FROM 뒤에 여러 태이블 참조 및 필요 시 특정 파티션 선택 가능

[GROUP BY [ {col_name | expr | position}, ... [WITH ROLLUP] | ROLLUP ({col_name | expr | position}, ...)] ]
[HAVING where_condition]

groupby 와 having 은 서로 같이 사용하며 groupby 만 사용해도 무관하다. having 은 where 절과 마찬가지로 조건문을 넣는다.

사용할 때 살펴보기

[WINDOW window_name AS (window_spec)
    [, window_name AS (window_spec)] ...]
[ORDER BY {col_name | expr | position}
  [ASC | DESC], ... [WITH ROLLUP]]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]

SELECT 문은 옵션 → 컬럼 → INTO → FROM → WHERE → GROUP BY → HAVING → WINDOW → ORDER BY → LIMIT → FOR UPDATE 순서로 구성되며 이 순서를 반드시 지키자.

DUAL 테이블

SELECT 1 + 1 FROM DUAL;
    -> 2

보통 SELECT 만 사용하고 싶을때 뒤에 FROM DUAL 이 생략된다. 즉 단순 계산할 때 SELECT 1+1 만 넣으면 알아서 FROM DUAL 이 들어가게 된다.

보통 SELET 절에 있는 1+1 과 같은 수식을 expr 라고 하는데, 값을 계산하거나 반환하는 식을 의미한다.
SELECT 1; – 숫자 상수
SELECT ‘hello’; – 문자열 상수
SELECT TRUE; – 논리값
SELECT 3 + 5; – 산술 연산
SELECT salary * 1.1; – 컬럼과 수치 연산
SELECT salary / 12; – 나눗셈
SELECT SIN(PI()/4); – 수학 함수
SELECT UPPER(first_name); – 문자열 함수
SELECT DATE_ADD(hire_date, INTERVAL 1 YEAR); – 날짜 함수
SELECT salary > 5000; – TRUE/FALSE
SELECT department_id IN (10, 20, 30); – IN 연산
SELECT first_name LIKE ‘S%’; – 패턴 매칭
SELECT NOT (department_id = 90); – NOT 연산
SELECT commission_pct IS NULL; – NULL 체크
SELECT IFNULL(commission_pct, 0); – NULL 대체

명명된 테이블의 모든 열들을 가져오기

SELECT t1.*, t2.* FROM t1 INNER JOIN t2 ...

명명된 테이블의 모든 열을 가져오되 순서를 정하기

SELECT id, t1.* FROM t1

이때 t1.* 이 아닌 그냥 *로 하면 오류가 발생한다.

AS 사용하여 별칭 지정하기

SELECT CONCAT(last_name,', ',first_name) AS full_name
  FROM mytable ORDER BY full_name;

AS 를 통해 열 이름을 정할 수 있고, 이를 다시 절에 활용할 수도 있다. 이는 FROM 절에서도 사용가능하다.

SELECT CONCAT(last_name,', ',first_name) AS full_name
  FROM mytable AS t ORDER BY full_name;

테이블 별칭은 AS 를 생략해도 자동으로 적용이 됨을 알고 있자.

SELECT WHERE

SELECT t1.name, t2.salary FROM employee AS t1, info AS t2
  WHERE t1.name = t2.name;

SELECT t1.name, t2.salary FROM employee t1, info t2
  WHERE t1.name = t2.name;

조건문 또한 WHERE 절을 통해 추가 가능하다.

-- AND 연산
SELECT * FROM employees 
WHERE salary >= 10000 AND department_id = 90;

-- OR 연산
SELECT * FROM employees 
WHERE department_id = 90 OR department_id = 100;

-- NOT 연산
SELECT * FROM employees 
WHERE NOT department_id = 90;

-- 복합 조건
SELECT * FROM employees 
WHERE (department_id = 90 OR department_id = 100)
  AND salary >= 10000;

자바와는 다르게 OR, AND 를 통해 명제끼리 연결시킨다.

-- IN 사용
SELECT * FROM employees 
WHERE department_id IN (90, 100, 110);

-- NOT IN 사용
SELECT * FROM employees 
WHERE department_id NOT IN (90, 100);

-- 범위 검색
SELECT * FROM employees 
WHERE salary BETWEEN 5000 AND 10000;

-- 날짜 범위
SELECT * FROM employees 
WHERE hire_date BETWEEN '2005-01-01' AND '2005-12-31';

날짜 범위는 위와 같이 사용된다.

LIKE

정규표현식처럼 문자열에 대해 문자열의 패턴이 맞는지 확인하도록 조건을 줄 수 있다.

-- S로 시작하는 이름
SELECT * FROM employees 
WHERE first_name LIKE 'S%';

-- n으로 끝나는 이름
SELECT * FROM employees 
WHERE first_name LIKE '%n';

-- a를 포함하는 이름
SELECT * FROM employees 
WHERE first_name LIKE '%a%';

-- 두 번째 글자가 o인 이름
SELECT * FROM employees 
WHERE first_name LIKE '_o%';

-- 정확히 5글자인 이름
SELECT * FROM employees 
WHERE first_name LIKE '_____';

-- a로 시작하고 5글자인 이름
SELECT * FROM employees 
WHERE first_name LIKE 'a____';

ORDER BY

SELECT college, region, seed FROM tournament
  ORDER BY region, seed;

SELECT college, region AS r, seed AS s FROM tournament
  ORDER BY r, s;

SELECT college, region, seed
FROM tournament
ORDER BY 2, 3;

SELECT first_name, last_name, age
FROM mytable
ORDER BY last_name ASC, age DESC;

맨 하위가 의문일 수 있는데, 기본적으로 MySQL 은 중복된 열의 이름을 허용한다. 따라서 이를 index 로 지정하는 것을 볼 수 있다.

GROUP BY, HAVING

SELECT COUNT(col1) AS col2 FROM t GROUP BY col2 HAVING col2 = 2;

col2 가 2인 레코드들을 집계하여 보여주는 쿼리이다. 이때 having 절은 group by 와 함께 가져가는 predicate 이다. group by 가 없는데도 having 을 쓰게 되면 문제가 생길 수 있다.

LIMIT

SELECT * FROM tbl LIMIT 5,10;  # Retrieve rows 6-15

5 이후부터 10개를 가져온다.

Placeholders

플레이스 홀더는 프로그래밍 언어에서 변수와 비슷한 개념이다.

SET @a=1;
PREPARE STMT FROM 'SELECT * FROM tbl LIMIT ?';
EXECUTE STMT USING @a;

STMT 를 중간 코드와 같이 저으이하고, 해당 STMT 를 EXECUTE 문으로 실행함을 볼 수 있다. 인자를 넣을 때는 USING 을 통해 넣어주게 된다.

DISTINCT

-- 중복된 값 제거
SELECT DISTINCT department_id 
FROM employees;

-- 여러 컬럼의 조합으로 중복 제거
SELECT DISTINCT department_id, job_id
FROM employees;

NULL 처리

자바와는 다르게 sql 은 NULL 을 비교연산자 =, != 으로 못한다. 지정해놓은 IS NULL, IS NOT NULL 을 통해 비교할 수 있다.

-- NULL인 데이터 찾기
SELECT * FROM employees 
WHERE commission_pct IS NULL;

-- NULL이 아닌 데이터 찾기
SELECT * FROM employees 
WHERE commission_pct IS NOT NULL;

함수

UPPER
LOWER
CONCAT
SUBSTRING
LENGTH
TRIM, LTRIM, RTRIM
LPAD, RPAD
ROUND, CEIL, FLOOR
ABS
MOD
POWER
SQRT
GREATEST, LEAST
CURDATE, CURTIME, NOW
DATE_FORMAT
DATE_ADD, DATE_SUB
DATEDIFF
YEAR, MONTH, DAY, DAYOFWEEK

위 내용들은 전부 응용 개념이라 많이 사용해봐야 외어지므로 그냥 그러려니 하고 넘어간다.

UPDATE

update-set-where 의 구문으로 보통 외우고 있으면 다 된다. where 절의 expression 넣는 구문은 select 구문과 동일하다. 실제 API 를 보자.

UPDATE [LOW_PRIORITY] [IGNORE] table_reference
    SET assignment_list
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

[LOW_PRIORITY] : 다른 쿼리보다 실행 우선순위를 낮춤
[IGNORE] : 업데이트 중 발생하는 일부 오류를 무시하고 계속 실행
table_reference : 업데이트 할 테이블 이름
SET assignment_list : 수정할 컬럼과 값을 지정
[WHERE where_condition] : 조건을 만족하는 행만 업데이트
[ORDER BY …] : 특정 순서대로 행을 업데이트 (특히 LIMIT과 함께 사용)
[LIMIT row_count] : 한 번에 업데이트할 행 수 제한

assignment_list 에는 수정할 컬럼들이 들어가는데, assignment 각각은 다음과 같이 정의된다.

assignment:
    col_name = value

value:
    {expr | DEFAULT}

한 컬럼에 대해 어떤 값으로 변경할지 지정하는 곳이다. 여기서 value는 DEFAULT 혹은 표현식이 들어가면 된다.

DELETE

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name [[AS] tbl_alias]
    [PARTITION (partition_name [, partition_name] ...)]
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

LOW_PRIORITY 설명은 생략

QUICK: 인덱스가 존재하면 더 빠르게 삭제
IGNORE: 삭제 과정에서 오류가 발생 시 무시하고 계속 진행할지 여부

FROM tbl_name [[AS] tbl_alias]
[PARTITION (partition_name [, partition_name] ...)]
[WHERE where_condition]

삭제할 테이블 이름 지정 및 파티션 테이블에서 특정 파티션만 대상으로 삭제 가능하며 조건을 만족하는 행만 삭제가 가능하다(생략시 테이블 전체가 삭제되버림).

[ORDER BY ...]
[LIMIT row_count]

삭제 순서를 ORDER 을 통해 지정할 수 있고, 주로 LIMIT 와 함께 사용가능하다.

INSERT

insert 동작은 좀 복잡하다.

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [(col_name [, col_name] ...)]
    { {VALUES | VALUE} (value_list) [, (value_list)] ... }
    [AS row_alias[(col_alias [, col_alias] ...)]]
    [ON DUPLICATE KEY UPDATE assignment_list]

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    SET assignment_list
    [AS row_alias[(col_alias [, col_alias] ...)]]
    [ON DUPLICATE KEY UPDATE assignment_list]

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [(col_name [, col_name] ...)]
    { SELECT ... 
      | TABLE table_name 
      | VALUES row_constructor_list
    }
    [ON DUPLICATE KEY UPDATE assignment_list]

value:
    {expr | DEFAULT}

value_list:
    value [, value] ...

row_constructor_list:
    ROW(value_list)[, ROW(value_list)][, ...]

assignment:
    col_name = 
          value
        | [row_alias.]col_name
        | [tbl_name.]col_name
        | [row_alias.]col_alias

assignment_list:
    assignment [, assignment] ...

INSERT 문은 크게 3가지로 나뉘는데 각각을 보자.

VALUES 를 사용하는 일반 INSERT

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [(col_name [, col_name] ...)]
    { {VALUES | VALUE} (value_list) [, (value_list)] ... }
    [AS row_alias[(col_alias [, col_alias] ...)]]
    [ON DUPLICATE KEY UPDATE assignment_list]

DELAYED: 테이블이 바쁠 때 INSERT 를 지연시킨다.
IGNORED: 삽입 중 오류 발생 무시하고 실행
VALUES / VALUE (value_list) : 삽입할 값
- 여러 행 삽입 가능: (v1, v2, …) , (v3, v4, …)
ON DUPLICATE KEY UPDATE: 기본키/유니크키 충돌 시 수행 할 업데이트

INSERT-INTO-VALUES 로 자주 사용한다.

ON DUPLICATE KEY UPDATE

INSERT INTO t1 (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=c+1;

UPDATE t1 SET c=c+1 WHERE a=1;

기존에 primary key 혹은 unique 가 a 라는 attribute 이고, 초기 데이터가 a=1 인 행이 이미 있다고 치자, 그러면 (1, 2, 3) 이라는 행을 insert 하는 작업은 비정상적이다. 이때 ON DUPLICATE KEY UPDATE 절로 기존에 있던 행에 대해 업데이트하는 작업을 수행할 수 있다.

SET을 사용하는 INSERT

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    [PARTITION (...)]
    SET assignment_list
    [AS row_alias[(col_alias ...)]]
    [ON DUPLICATE KEY UPDATE assignment_list]

SELECT / TABLE / VALUES 를 이용한 INSERT

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    [PARTITION (...)]
    [(col_name [, ...])]
    { SELECT ... 
      | TABLE table_name 
      | VALUES row_constructor_list
    }
    [ON DUPLICATE KEY UPDATE assignment_list]

value:
    {expr | DEFAULT}

value_list:
    value [, value] ...

row_constructor_list:
    ROW(value_list)[, ROW(value_list)][, ...]

DO

표현식을 실행하지만 결과를 반환하지 않는 명령이다.

SELECT SLEEP(5);

위의 구문과 아래 구문의 차이는 출력이 있냐 없냐 뿐이다.

DO SLEEP(5);

CALL

이전에 정의해놓은 프로시저(함수)를 호출하고 싶을 때 사용한다.

CALL sp_name([parameter[,...]])
CALL sp_name[()]

여기서 [()] 가 되어 있는데, 이는 인자가 필요 없는 함수에 대해 소괄호를 생략해도 된다는 의미이다.

인자를 placeholder 로 사용하여 넣고 함수를 호출하는 예제이다.

DELIMITER //

CREATE PROCEDURE p (OUT ver_param VARCHAR(25), INOUT incr_param INT)
BEGIN
  # Set value of OUT parameter
  SELECT VERSION() INTO ver_param;
  # Increment value of INOUT parameter
  SET incr_param = incr_param + 1;
END //

DELIMITER ;

DELIMITER //: 문장 구분자를 바꾸는 명령, ; 를 //로 바꾸겠다
OUT: 출력용 파라미터
INOUT: 입력과 출력 둘 다 가능한 파라미터
BEGIN: 프로시저 시작
END //: 프로시저 끝 및 문장의 종료
DELIMITER ;: 문장의 구분자를 // 에서 ; 로 바꾸겠다.

mysql> SET @increment = 10;
mysql> CALL p(@version, @increment);
mysql> SELECT @version, @increment;
+----------+------------+
| @version | @increment |
+----------+------------+
| 8.4.6   |         11 |
+----------+------------+

Relational Model

기본적인 SQL 문을 다루는 법은 살펴봤으니 그 뒷단의 이론을 보자.

Structure of Relational Databases

Relational DB 는 여러 개의 테이블로 이루어져 있고, 이런 테이블들의 한 행은 특정 ID 와 그에 대응되는 열들의 값들 간의 Relation 으로 표현되는 것을 볼 수 있다.

이러한 Relation 을 기본적으로 수학적 개념이고, 이 관계들에 실체 값들을 즉, 행들을 Tuple 이라고 부르며, 테이블의 개념은 이러한 관계들의 Set 이 되겠다.

Tuple $\in$ Relation $\subset$ Table

여기서 튜플은 우리가 다루는 데이터들 일텐데, 데이터의 상태는 항상 바뀌기 마련이다. 따라서 이를 구분하기 위해 Relation Instance 라는 개념이 있는데, Relation 의 특정 한 시점을 나타내게 된다.

또한 튜플들의 각 성분에 대해 가질 수 있는 값들의 범위를 Attribute 이라고 한다.

Domain Atomicity

도메인이 원자적이어야 한다는 것은 도메인 원소가 더 이상 쪼갤 수 없는 단위(indivisible unit)로 되어야 함을 의미한다.

여기서 중요한 점은 도메인 자체가 무엇이냐가 아니라, 우리가 데이터베이스에서 도메인 원소를 어떻게 다루느냐 이다. 가령 phone_number 속성이 단일 전화번호 하나만 저장한다고 하더라도, 만약 우리가 그 값을 국가 코드, 지역 코드, 국번호 등으로 쪼개어 다룬다면 이는 여전히 비원자적(non-atomic) 값으로 취급되는 것이다.

Domain Nullity

이런 원자성만 갖추면 될 것 같지만, 값에는 아무 것도 없는 null 이라는 의미를 가진 값이 존재할 수 있다. 초반의 예제에서는 이게 없다고 가정하고 다루지만, 보통은 null 값을 어떻게 다룰지, 조회하거나 갱신할 때 null 이 미치는 영향들을 추후에 살펴보자.

Relational Model 의 한계

관계형 모델의 비교적 엄격한 구조는 데이터 저장과 처리에서 여러 가지 중요한 실질적 장점을 가져오지만, 엄격한 구조는 잘 정의되어 있고 비교적 정적인 응용 분야에는 적합하지만, 데이터뿐만 아니라 데이터의 유형과 구조 자체가 시간에 따라 변하는 응용 분야에는 적합하지 않다.