Theory
Database
SQL 문법

DB

  • docker compose
services:
  mysql:
    image: mysql:8.4.3
    environment:
      MYSQL_ROOT_PASSWORD: df159357
      MYSQL_DATABASE: gamemuncheol
    ports:
      - "3306:3306"
    volumes:
      - mysql-data:/var/lib/mysql
volumes:
  mysql-data:
  • 접속 방법
mysql -h 0.0.0.0 -P 3306 -u root -p

테스트 용 테이블

CREATE DATABASE test;
USE test;
CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    name VARCHAR(50),
    department VARCHAR(50),
    salary INT
);
 
INSERT INTO employees (emp_id, name, department, salary) VALUES
(1, 'Alice', 'IT', 6000),
(2, 'Bob', 'IT', 7000),
(3, 'Charlie', 'IT', 7000),
(4, 'David', 'HR', 5000),
(5, 'Eve', 'HR', 6000),
(6, 'Frank', 'HR', 4500),
(7, 'Grace', 'Sales', 5500),
(8, 'Hank', 'Sales', 7000),
(9, 'Ivy', 'Sales', 7000);

SUBSTRING()

SUBSTRING(string_expression, start, length)
  • string_expression : 문자열
  • start : 시작 위치 (인덱스 1부터 시작)
  • length : 길이
SELECT SUBSTRING('Hello, World!', 1, 5) AS result;

예시

mysql> SELECT SUBSTRING(name,1,3) FROM employees;
+---------------------+
| SUBSTRING(name,1,3) |
+---------------------+
| Ali                 |
| Bob                 |
| Cha                 |
| Dav                 |
| Eve                 |
| Fra                 |
| Gra                 |
| Han                 |
| Ivy                 |
+---------------------+
9 rows in set (0.01 sec)

RANK(), RANK_PERCENT()

  • RANK(): 동일한 값이 있을 경우 같은 순위를 부여하고 다음 순위를 건너뛴다.
  • PERCENT_RANK(): 순위를 백분율(0~1)로 변환하여 출력한다.
RANK() OVER (PARTITION BY column_name ORDER BY column_name DESC)
PERCENT_RANK() OVER (PARTITION BY column_name ORDER BY column_name DESC)

예시

  • 전체 직원의 급여 순위 계산
mysql> SELECT name, department, salary,
    ->        RANK() OVER (ORDER BY salary DESC) AS salary_rank,
    ->        PERCENT_RANK() OVER (ORDER BY salary DESC) AS percent_salary_rank
    -> FROM employees;
+---------+------------+--------+-------------+---------------------+
| name    | department | salary | salary_rank | percent_salary_rank |
+---------+------------+--------+-------------+---------------------+
| Bob     | IT         |   7000 |           1 |                   0 |
| Charlie | IT         |   7000 |           1 |                   0 |
| Hank    | Sales      |   7000 |           1 |                   0 |
| Ivy     | Sales      |   7000 |           1 |                   0 |
| Alice   | IT         |   6000 |           5 |                 0.5 |
| Eve     | HR         |   6000 |           5 |                 0.5 |
| Grace   | Sales      |   5500 |           7 |                0.75 |
| David   | HR         |   5000 |           8 |               0.875 |
| Frank   | HR         |   4500 |           9 |                   1 |
+---------+------------+--------+-------------+---------------------+
9 rows in set (0.00 sec)

DATE_FORMAT()

  • 예시 데이터 생성
CREATE TABLE test_table (
    id INT AUTO_INCREMENT PRIMARY KEY,
    created_at DATETIME NOT NULL
);
INSERT INTO test_table (created_at) VALUES (NOW());
  • 날짜 포맷 적용
mysql> SELECT DATE_FORMAT(created_at, '%Y-%m-%d %H:%i:%s') AS formatted_date FROM test_table;
+---------------------+
| formatted_date      |
+---------------------+
| 2025-02-23 23:27:14 |
+---------------------+
1 row in set (0.00 sec)
 
mysql> SELECT DATE_FORMAT(created_at, '%Y-%m-%d -T') AS formatted_date FROM test_table;
+----------------+
| formatted_date |
+----------------+
| 2025-02-23 -T  |
+----------------+
1 row in set (0.01 sec)

Reference

SQL IndexN+1