SQL文でよく見かけるクエリの自分用メモ【随時更新】

自分用クエリメモ
Microsoft SQL Server2022で動作確認済み

サンプルテーブル定義

部署テーブル（departments）

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(50) NOT NULL,
    location VARCHAR(50),
    manager_id INT
);

-- サンプルデータ
INSERT INTO departments VALUES (1, '営業部', '東京', 101);
INSERT INTO departments VALUES (2, '開発部', '大阪', 201);
INSERT INTO departments VALUES (3, '人事部', '名古屋', 301);
INSERT INTO departments VALUES (4, '経理部', '福岡', 401);
INSERT INTO departments VALUES (5, '総務部', '札幌', 501);

社員テーブル（employees）

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    phone_number VARCHAR(20),
    hire_date DATE NOT NULL,
    job_title VARCHAR(50) NOT NULL,
    salary DECIMAL(10, 2) NOT NULL,
    commission_pct DECIMAL(4, 2),
    manager_id INT,
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(department_id),
    FOREIGN KEY (manager_id) REFERENCES employees(employee_id)
);

-- サンプルデータ
INSERT INTO employees VALUES (101, '太郎', '山田', '[e-mail]', '090-1111-2222', '2015-04-01', '営業部長', 800000, 0.20, NULL, 1);
INSERT INTO employees VALUES (102, '花子', '佐藤', '[e-mail]', '090-2222-3333', '2016-07-01', '営業担当', 500000, 0.15, 101, 1);
INSERT INTO employees VALUES (103, '一郎', '鈴木', '[e-mail]', '090-3333-4444', '2017-10-01', '営業担当', 450000, 0.10, 101, 1);
INSERT INTO employees VALUES (201, '次郎', '田中', '[e-mail]', '090-4444-5555', '2014-01-15', '開発部長', 900000, NULL, NULL, 2);
INSERT INTO employees VALUES (202, '三郎', '伊藤', '[e-mail]', '090-5555-6666', '2015-11-01', 'シニア開発者', 750000, NULL, 201, 2);
INSERT INTO employees VALUES (203, '四郎', '渡辺', '[e-mail]', '090-6666-7777', '2018-03-15', '開発者', 600000, NULL, 201, 2);
INSERT INTO employees VALUES (204, '五郎', '加藤', '[e-mail]', '090-7777-8888', '2019-06-01', 'ジュニア開発者', 400000, NULL, 202, 2);
INSERT INTO employees VALUES (301, '六郎', '高橋', '[e-mail]', '090-8888-9999', '2013-05-01', '人事部長', 850000, NULL, NULL, 3);
INSERT INTO employees VALUES (401, '七郎', '中村', '[e-mail]', '090-9999-0000', '2012-08-01', '経理部長', 880000, NULL, NULL, 4);
INSERT INTO employees VALUES (501, '八郎', '小林', '[e-mail]', '080-1111-2222', '2011-12-01', '総務部長', 830000, NULL, NULL, 5);

基本的な SELECT 文

概要

ここに SELECT 文の基本的な説明を記述します。

構文

SELECT カラム1, カラム2, ...
FROM テーブル名;

使用例

-- 例1: 全カラムの取得
SELECT * FROM テーブル名;

-- 例2: 特定のカラムの取得
SELECT カラム1, カラム2 FROM テーブル名;

実践例

-- 実際のテーブルを使った例
SELECT * FROM employees;

-- 特定のカラムのみ取得
SELECT employee_id, first_name, last_name FROM employees;

WHERE 句による絞り込み

概要

ここに WHERE 句の説明を記述します。

構文

SELECT カラム1, カラム2, ...
FROM テーブル名
WHERE 条件式;

使用例

-- 例1: 等価条件
SELECT * FROM テーブル名 WHERE カラム名 = 値;

-- 例2: 比較条件
SELECT * FROM テーブル名 WHERE カラム名 > 値;

-- 例3: 複数条件（AND）
SELECT * FROM テーブル名 WHERE 条件1 AND 条件2;

-- 例4: 複数条件（OR）
SELECT * FROM テーブル名 WHERE 条件1 OR 条件2;

-- 例5: IN演算子
SELECT * FROM テーブル名 WHERE カラム名 IN (値1, 値2, ...);

-- 例6: BETWEEN演算子
SELECT * FROM テーブル名 WHERE カラム名 BETWEEN 値1 AND 値2;

-- 例7: LIKE演算子
SELECT * FROM テーブル名 WHERE カラム名 LIKE 'パターン';

-- 例8: IS NULL / IS NOT NULL
SELECT * FROM テーブル名 WHERE カラム名 IS NULL;
SELECT * FROM テーブル名 WHERE カラム名 IS NOT NULL;

実践例

-- 給与が600,000円以上の社員を検索
SELECT * FROM employees WHERE salary >= 600000;

-- 開発部（department_id = 2）の社員を検索
SELECT * FROM employees WHERE department_id = 2;

-- 給与が500,000円以上800,000円以下の社員を検索
SELECT * FROM employees WHERE salary BETWEEN 500000 AND 800000;

-- 名前が「太郎」または「次郎」の社員を検索
SELECT * FROM employees WHERE first_name IN ('太郎', '次郎');

-- メールアドレスがexample.comドメインの社員を検索
SELECT * FROM employees WHERE email LIKE '%example.com';

-- コミッションがNULLでない社員を検索
SELECT * FROM employees WHERE commission_pct IS NOT NULL;

ORDER BY 句によるソート

概要

検索結果を特定のカラムで昇順/降順に並び替える機能を提供します。デフォルトは昇順（ASC）で、降順は DESC を指定します。

構文

SELECT カラム1, カラム2, ...
FROM テーブル名
ORDER BY ソートキー1 [ASC|DESC], ソートキー2 [ASC|DESC];

使用例

-- 単一カラムのソート
SELECT * FROM テーブル名 ORDER BY カラム名 DESC;

-- 複数カラムのソート
SELECT * FROM テーブル名 ORDER BY カラム1 ASC, カラム2 DESC;

-- 数値位置指定でのソート
SELECT カラム1, カラム2 FROM テーブル名 ORDER BY 2 DESC;

実践例

-- 給与の高い順に社員を表示
SELECT employee_id, last_name, salary
FROM employees
ORDER BY salary DESC;

-- 部署別・給与降順で表示
SELECT department_id, last_name, salary
FROM employees
ORDER BY department_id ASC, salary DESC;

-- 採用日の新しい順に表示
SELECT first_name, last_name, hire_date
FROM employees
ORDER BY hire_date DESC;

GROUP BY 句と HAVING 句

概要

GROUP BY で指定したカラムごとにデータをグループ化し、HAVING 句でグループ化後の条件を指定します。集計関数（COUNT, SUM, AVG など）と組み合わせて使用します。

構文

SELECT カラム1, 集計関数(カラム2)
FROM テーブル名
GROUP BY カラム1
HAVING 条件式;

使用例

-- 部署ごとの平均給与
SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id;

-- 人数が3人以上の部署を表示
SELECT department_id, COUNT(*)
FROM employees
GROUP BY department_id
HAVING COUNT(*) >= 3;

-- 給与合計が2,000,000円以上の部署
SELECT department_id, SUM(salary)
FROM employees
GROUP BY department_id
HAVING SUM(salary) >= 2000000;

実践例

-- 各部署の最高給与と最低給与
SELECT department_id, MAX(salary), MIN(salary)
FROM employees
GROUP BY department_id;

-- 平均給与が600,000円以上の部署
SELECT department_id, AVG(salary) avg_salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) >= 600000;

-- 役職別社員数（5人以上の役職のみ表示）
SELECT job_title, COUNT(*) employee_count
FROM employees
GROUP BY job_title
HAVING COUNT(*) >= 3;

JOIN 操作

概要

複数のテーブルを結合して関連データを取得します。主な結合方法には INNER JOIN、LEFT JOIN、RIGHT JOIN、FULL OUTER JOIN があります。

構文

SELECT カラムリスト
FROM テーブル1
[INNER|LEFT|RIGHT|FULL OUTER] JOIN テーブル2
ON 結合条件;

使用例

-- 内部結合（共通部分のみ）
SELECT *
FROM テーブルA
INNER JOIN テーブルB ON A.id = B.id;

-- 左外部結合（左側全件＋一致する右側）
SELECT *
FROM テーブルA
LEFT JOIN テーブルB ON A.id = B.id;

-- 3テーブルの結合
SELECT *
FROM テーブルA
JOIN テーブルB ON A.id = B.a_id
JOIN テーブルC ON B.id = C.b_id;

実践例

-- 部署情報と社員情報の結合
SELECT e.last_name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;

-- マネージャー情報を含む社員一覧
SELECT e.last_name AS employee, m.last_name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id;

-- 全部署の情報（社員のいない部署も含む）
SELECT d.department_name, e.last_name
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id;

サブクエリ

概要

SQL 文内にネストされたクエリで、主に WHERE 句や FROM 句、SELECT 句で使用されます。単一行サブクエリと複数行サブクエリに分類されます。

構文

SELECT カラム
FROM テーブル
WHERE カラム 演算子 (サブクエリ);

使用例

-- 単一行サブクエリ
SELECT *
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- 複数行サブクエリ（IN演算子）
SELECT *
FROM employees
WHERE department_id IN (SELECT department_id FROM departments WHERE location = '東京');

-- 相関サブクエリ
SELECT e.last_name, e.salary
FROM employees e
WHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id);

実践例

-- 平均給与より高い給与の社員
SELECT last_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- 東京の部署に所属する社員
SELECT last_name, department_id
FROM employees
WHERE department_id IN (
  SELECT department_id
  FROM departments
  WHERE location = '東京'
);

-- 部署ごとの最高給与取得者
SELECT department_id, last_name, salary
FROM employees e1
WHERE salary = (
  SELECT MAX(salary)
  FROM employees e2
  WHERE e2.department_id = e1.department_id
);

INSERT 文

概要

INSERT 文は、テーブルに新しいデータを挿入するために使用します。データを挿入するカラムを指定する方法と、すべてのカラムにデータを挿入する方法があります。

構文

-- カラムを指定してデータを挿入
INSERT INTO テーブル名 (カラム1, カラム2, ...)
VALUES (値1, 値2, ...);

-- 全カラムにデータを挿入（カラム名省略）
INSERT INTO テーブル名
VALUES (値1, 値2, ...);

使用例

-- 部署テーブルに新しい部署を追加
INSERT INTO departments (department_id, department_name, location, manager_id)
VALUES (6, 'マーケティング部', '仙台', 601);

-- 社員テーブルに新しい社員を追加
INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, job_title, salary, department_id)
VALUES (601, '九郎', '佐々木', '[e-mail]', '2025-03-01', 'マーケティング担当', 550000, 6);

実践例

-- 新しい部署の追加
INSERT INTO departments (department_id, department_name, location)
VALUES (7, 'ITサポート部', '広島');

-- 新しい社員の追加（コミッションなし）
INSERT INTO employees (employee_id, first_name, last_name, email, phone_number, hire_date, job_title, salary, department_id)
VALUES (701, '十郎', '藤田', '[e-mail]', '090-1234-5678', '2025-03-15', 'ITサポート担当', 480000, 7);

UPDATE 文

概要

UPDATE 文は、既存のデータを更新するために使用します。特定の条件を指定して、特定の行のみを更新することができます。

構文

UPDATE テーブル名
SET カラム1 = 値1, カラム2 = 値2, ...
WHERE 条件;

使用例

-- 社員の給与を更新
UPDATE employees
SET salary = 600000
WHERE employee_id = 103;

-- 部署名を変更
UPDATE departments
SET department_name = 'リサーチ部'
WHERE department_id = 3;

-- 条件なしで全件更新（注意）
UPDATE employees
SET commission_pct = NULL;

実践例

-- 特定の社員の役職と給与を更新
UPDATE employees
SET job_title = 'シニア営業担当', salary = 550000
WHERE employee_id = 102;

-- 部署の場所情報を更新
UPDATE departments
SET location = '京都'
WHERE department_id = 2;

-- 全社員の給与を10%増加（現在の給与に基づく計算）
UPDATE employees
SET salary = salary * 1.10;

DELETE 文

概要

DELETE 文は、テーブルから特定の行を削除するために使用します。削除対象は WHERE 句で指定します。WHERE を省略するとすべての行が削除されるため注意が必要です。

構文

DELETE FROM テーブル名 WHERE 条件;

使用例

-- 特定の社員を削除
DELETE FROM employees
WHERE employee_id = 204;

-- 特定の部署を削除（関連データがある場合はエラーになる可能性あり）
DELETE FROM departments
WHERE department_id = 6;

-- 条件なしで全件削除（注意）
DELETE FROM employees;

実践例

-- コミッションがNULLの社員を削除
DELETE FROM employees
WHERE commission_pct IS NULL;

-- 福岡にある部署を削除（部署IDが一致する場合のみ）
DELETE FROM departments
WHERE location = '福岡';

-- 給与が400000円未満の社員を削除（条件付き）
DELETE FROM employees
WHERE salary < 400000;

CREATE TABLE 文

概要

CREATE TABLE 文は、新しいテーブルを作成するために使用します。各カラムにはデータ型や制約（PRIMARY KEY、NOT NULL など）を指定できます。

構文

CREATE TABLE テーブル名 (
  カラム名 データ型 制約,
  カラム名 データ型 制約,
  ...
);

使用例

-- プロジェクトテーブルの作成
CREATE TABLE projects (
  project_id INT PRIMARY KEY,
  project_name VARCHAR(100) NOT NULL,
  start_date DATE,
  end_date DATE,
  budget DECIMAL(15, 2)
);

-- サンプルデータ挿入例
INSERT INTO projects VALUES (1, '新製品開発プロジェクト', '2025-01-01', NULL, 5000000);

実践例

-- 顧客テーブルの作成（外部キー制約なし）
CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  customer_name VARCHAR(100) NOT NULL,
  contact_email VARCHAR(100),
  phone_number VARCHAR(20),
  address VARCHAR(200)
);

-- サンプルデータ挿入例
INSERT INTO customers VALUES (1, '株式会社ABC', '[e-mail]', '03-1234-5678', '東京都港区');
INSERT INTO customers VALUES (2, '有限会社XYZ', NULL, NULL, '大阪府大阪市');

ALTER TABLE 文

概要

ALTER TABLE 文は、既存のテーブル構造を変更するために使用します。カラムの追加、削除、変更や制約の追加などが可能です。

構文と使用例

カラムの追加:

ALTER TABLE テーブル名 ADD 新カラム名 データ型 制約;

ALTER TABLE employees ADD birth_date DATE;

カラムの削除:

ALTER TABLE テーブル名 DROP COLUMN 削除カラム名;

ALTER TABLE employees DROP COLUMN birth_date;

カラムの変更:

ALTER TABLE テーブル名 MODIFY カラム名 新データ型 制約;
ALTER TABLE テーブル名 CHANGE 古いカラム名 新しいカラム名 データ型 制約;

ALTER TABLE employees MODIFY salary DECIMAL(12, 2);
ALTER TABLE employees CHANGE phone_number contact_number VARCHAR(25);

制約の追加:

ALTER TABLE テーブル名 ADD CONSTRAINT 制約名 制約内容;

ALTER TABLE employees ADD CONSTRAINT fk_department FOREIGN KEY (department_id) REFERENCES departments(department_id);

制約の削除:

ALTER TABLE テーブル名 DROP CONSTRAINT 制約名;

ALTER TABLE employees DROP FOREIGN KEY fk_department;

インデックス

概要

インデックスは、テーブル内のデータへのアクセスを高速化するために使用されます。特定のカラムにインデックスを作成することで、検索やソートのパフォーマンスを向上させることができます。ただし、インデックスの作成にはストレージコストがかかり、頻繁な更新操作ではパフォーマンスに影響を与える場合があります。

構文

-- 単一カラムのインデックス作成
CREATE INDEX インデックス名 ON テーブル名 (カラム名);

-- 複数カラムのインデックス作成
CREATE INDEX インデックス名 ON テーブル名 (カラム1, カラム2);

-- ユニークインデックス
CREATE UNIQUE INDEX インデックス名 ON テーブル名 (カラム名);

-- インデックスの削除
DROP INDEX インデックス名 ON テーブル名;

使用例

-- 社員テーブルのメールアドレスにインデックスを作成
CREATE INDEX idx_email ON employees (email);

-- 部署テーブルの部署名と場所に複合インデックスを作成
CREATE INDEX idx_department_location ON departments (department_name, location);

-- 社員テーブルの電話番号にユニークインデックスを作成
CREATE UNIQUE INDEX idx_phone_number ON employees (phone_number);

-- インデックスの削除例
DROP INDEX idx_email ON employees;

実践例

-- 給与でソートするクエリを高速化するためにインデックスを作成
CREATE INDEX idx_salary ON employees (salary);

-- 部署テーブルのマネージャーIDにインデックスを作成（検索高速化）
CREATE INDEX idx_manager_id ON departments (manager_id);

-- メールアドレスが一意であることを保証するユニークインデックスを作成
CREATE UNIQUE INDEX idx_unique_email ON employees (email);

ビュー

概要

ビューは、複雑なクエリ結果を仮想的なテーブルとして定義する機能です。ビューは実際には物理的なデータを保持せず、基になるテーブルへの参照として機能します。ビューを使用すると、簡潔なクエリやセキュリティ向上が可能になります。

構文

-- ビューの作成
CREATE VIEW ビュー名 AS
SELECT カラム1, カラム2, ...
FROM テーブル名
WHERE 条件;

-- ビューの削除
DROP VIEW ビュー名;

使用例

-- 部署ごとの平均給与ビューを作成
CREATE VIEW avg_salary_per_department AS
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id;

-- ビューからデータ取得
SELECT * FROM avg_salary_per_department;

-- ビューの削除例
DROP VIEW avg_salary_per_department;

実践例

-- 営業部社員一覧ビュー（営業部のみ表示）
CREATE VIEW sales_department_employees AS
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE department_id = 1;

-- 営業部社員一覧ビューから取得
SELECT * FROM sales_department_employees;

-- 高給与社員ビュー（給与が800,000円以上）
CREATE VIEW high_salary_employees AS
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE salary >= 800000;

-- 高給与社員ビューから取得
SELECT * FROM high_salary_employees;

トランザクション

概要

トランザクションは、複数の SQL 操作を1つのまとまった処理として実行する仕組みです。トランザクション内でエラーが発生した場合、すべての操作を元に戻す（ロールバック）ことができます。これにより、データベースの整合性が保たれます。

構文と使用例

トランザクション開始と終了:

BEGIN TRANSACTION; -- トランザクション開始

UPDATE テーブル名 SET カラム = 値 WHERE 条件; -- 操作1

INSERT INTO テーブル名 (カラム1, カラム2) VALUES (値1, 値2); -- 操作2

COMMIT; -- トランザクション確定（保存）

ROLLBACK; -- トランザクション中止（元に戻す）

自動コミット無効化:

SET AUTOCOMMIT = 0; -- 自動コミット無効化

UPDATE テーブル名 SET カラム = 値 WHERE 条件; -- 操作

COMMIT; -- 明示的なコミットで保存

SET AUTOCOMMIT = 1; -- 自動コミット有効化（元に戻す）

実践例

-- 部署と社員情報の一括更新（失敗時はロールバック）
BEGIN TRANSACTION;

UPDATE departments
SET location = '仙台'
WHERE department_id = 3;

UPDATE employees
SET salary = salary * 1.05
WHERE department_id = 3;

COMMIT;

-- エラー発生時はロールバックで元に戻す例:
BEGIN TRANSACTION;

UPDATE employees
SET salary = salary * 1.10
WHERE department_id = 2;

INSERT INTO departments VALUES (8, '新規部署', '京都', NULL); -- エラー発生時

ROLLBACK;

集計関数

概要

集計関数は、データの集計や統計を計算するために使用されます。主に SELECT 文と GROUP BY 句で使用され、データの合計、平均、最大値、最小値、件数などを取得できます。

主な集計関数一覧

関数	説明
COUNT()	行数をカウント
SUM()	合計値を計算
AVG()	平均値を計算
MAX()	最大値を取得
MIN()	最小値を取得

使用例

-- テーブル内の行数を取得
SELECT COUNT(*) FROM employees;

-- 部署ごとの社員数を取得
SELECT department_id, COUNT(*) AS employee_count
FROM employees
GROUP BY department_id;

-- 全社員の給与合計を取得
SELECT SUM(salary) AS total_salary
FROM employees;

-- 部署ごとの平均給与を取得
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id;

-- 全社員の最高給与と最低給与を取得
SELECT MAX(salary) AS max_salary, MIN(salary) AS min_salary
FROM employees;

実践例

-- コミッションが設定されている社員の人数をカウント
SELECT COUNT(*) AS commission_count
FROM employees
WHERE commission_pct IS NOT NULL;

-- 各役職ごとの最高給与を取得
SELECT job_title, MAX(salary) AS max_salary
FROM employees
GROUP BY job_title;

-- 部署ごとの給与合計が1,500,000円以上の部署のみ表示
SELECT department_id, SUM(salary) AS total_salary
FROM employees
GROUP BY department_id
HAVING SUM(salary) >= 1500000;

文字列関数

概要

文字列関数は、文字列データを操作するために使用されます。データベース内の文字列データの加工や変換に便利です。

主な文字列関数一覧

関数	説明
CONCAT()	文字列を結合
SUBSTRING()	文字列の一部を抽出
LENGTH()	文字列の長さ（バイト数）を返す
CHAR_LENGTH()	文字列の長さ（文字数）を返す
UPPER()	文字列を大文字に変換
LOWER()	文字列を小文字に変換
TRIM()	前後の空白や指定した文字を削除
REPLACE()	文字列内の特定部分を別の文字列に置換
LEFT() / RIGHT()	左側または右側から指定した長さ分だけ抽出

使用例

-- 名前（姓と名）の結合
SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM employees;

-- メールアドレスのドメイン部分だけ抽出
SELECT SUBSTRING(email, LOCATE('@', email) + 1) AS domain
FROM employees;

-- 社員名をすべて大文字に変換
SELECT UPPER(first_name), UPPER(last_name)
FROM employees;

-- 電話番号からハイフンを削除
SELECT REPLACE(phone_number, '-', '') AS phone_number_no_hyphen
FROM employees;

-- 名前が5文字以上か確認（5文字未満は「短い」と表示）
SELECT first_name,
CASE WHEN CHAR_LENGTH(first_name) >= 5 THEN '長い' ELSE '短い' END AS name_length_category
FROM employees;

実践例

-- 社員名（姓・名）と役職名を結合してフル情報表示
SELECT CONCAT(last_name, ' ', first_name, ' - ', job_title) AS full_info
FROM employees;

-- メールアドレスが「example.com」ドメインか判定（結果: YES/NO）
SELECT email,
CASE WHEN SUBSTRING(email, LOCATE('@', email) + 1) = 'example.com' THEN 'YES' ELSE 'NO' END AS is_example_domain
FROM employees;

-- 社員名から最初の3文字だけ抽出（ニックネーム生成）
SELECT first_name, LEFT(first_name, 3) AS nickname
FROM employees;

日付関数

概要

日付関数は、日付や時刻データを操作するために使用されます。日付データの抽出、フォーマット変更、加算・減算などが可能です。

主な日付関数一覧

関数	説明
NOW()	現在の日付と時刻
CURDATE()	現在の日付
CURTIME()	現在の時刻
DATE_FORMAT()	日付フォーマット変更
DATEDIFF()	2つの日付間の日数差
DATE_ADD()	日付に指定した期間を加算
DATE_SUB()	日付から指定した期間を減算
YEAR(), MONTH(), DAY()	年、月、日だけ抽出

使用例

-- 現在の日付と時刻を取得
SELECT NOW();

-- 現在の日付のみ取得
SELECT CURDATE();

-- 採用日の年だけ抽出
SELECT first_name, last_name, YEAR(hire_date) AS hire_year
FROM employees;

-- 採用日から100日後の日付を計算
SELECT first_name, last_name, DATE_ADD(hire_date, INTERVAL 100 DAY) AS after_100_days
FROM employees;

-- 採用日から現在までの日数差分を計算
SELECT first_name, last_name, DATEDIFF(CURDATE(), hire_date) AS days_since_hire
FROM employees;

実践例

-- 採用月ごとの社員数集計（入社月でグループ化）
SELECT MONTH(hire_date) AS hire_month, COUNT(*) AS employee_count
FROM employees
GROUP BY MONTH(hire_date);

-- 採用年が2020年以降の社員リスト表示（条件付き）
SELECT first_name, last_name, hire_date
FROM employees
WHERE YEAR(hire_date) >= 2020;

-- 採用日から半年後の日付と曜日表示（日付フォーマット変更）
SELECT first_name,
DATE_FORMAT(DATE_ADD(hire_date, INTERVAL 6 MONTH), '%Y-%m-%d (%W)') AS six_months_later
FROM employees;

数値関数

概要

数値関数は、数値データを操作するために使用されます。四捨五入、切り捨て、絶対値の取得など、数学的な計算や変換を行うことができます。

主な数値関数一覧

関数	説明
ABS()	数値の絶対値を返す
CEIL()	数値を切り上げ
FLOOR()	数値を切り捨て
ROUND()	指定した小数点以下で四捨五入
MOD()	割り算の余りを返す
POWER()	指定した数値のべき乗を計算
SQRT()	数値の平方根を返す
RAND()	0以上1未満のランダムな数値を生成

使用例

-- 給与の絶対値（負の値がない場合でも使用可能）
SELECT salary, ABS(salary) AS absolute_salary
FROM employees;

-- 給与を1000円単位で四捨五入
SELECT salary, ROUND(salary, -3) AS rounded_salary
FROM employees;

-- 各社員の給与に対して平方根を計算
SELECT salary, SQRT(salary) AS sqrt_salary
FROM employees;

-- ランダムな数値を生成（例: ID順にランダムな値を割り当て）
SELECT employee_id, RAND() AS random_value
FROM employees;

-- 給与を10万円単位で切り上げ
SELECT salary, CEIL(salary / 100000) * 100000 AS rounded_up_salary
FROM employees;

実践例

-- 部署ごとの給与合計を100万円単位で四捨五入
SELECT department_id, ROUND(SUM(salary), -6) AS total_salary_rounded
FROM employees
GROUP BY department_id;

-- 社員IDで割り算した余り（MOD関数）を計算
SELECT employee_id, MOD(employee_id, 5) AS remainder
FROM employees;

-- 給与が500,000円以上の社員に対してべき乗計算（給与の2乗）
SELECT employee_id, salary, POWER(salary, 2) AS squared_salary
FROM employees
WHERE salary >= 500000;

CASE 式

概要

CASE 式は、条件に基づいて異なる結果を返すことができる制御構文です。複雑な条件分岐やデータ変換に便利です。

構文

CASE
  WHEN 条件1 THEN 値1
  WHEN 条件2 THEN 値2
  ...
  ELSE 値N
END AS 別名;

使用例

-- 給与カテゴリ分け（高給与、中給与、低給与）
SELECT employee_id,
       CASE 
           WHEN salary >= 800000 THEN '高給与'
           WHEN salary BETWEEN 500000 AND 799999 THEN '中給与'
           ELSE '低給与'
       END AS salary_category 
FROM employees;

-- 部署名が NULL の場合は「未所属」と表示する
SELECT employee_id,
       CASE 
           WHEN department_id IS NULL THEN '未所属'
           ELSE '所属済み'
       END AS department_status 
FROM employees;

-- コミッション率による報酬計算（NULLの場合は0として扱う）
SELECT employee_id,
       salary,
       CASE 
           WHEN commission_pct IS NOT NULL THEN salary * commission_pct
           ELSE 0
       END AS commission_amount 
FROM employees;

実践例

-- 部署ごとの場所カテゴリ分け（東京、大阪、それ以外）
SELECT department_name,
       CASE location
           WHEN '東京' THEN '首都圏'
           WHEN '大阪' THEN '関西圏'
           ELSE 'その他'
       END AS location_category 
FROM departments;

-- 採用年による世代分類（2020年以降: 新世代、それ以前: 従来世代）
SELECT first_name, hire_date,
       CASE 
           WHEN YEAR(hire_date) >= 2020 THEN '新世代'
           ELSE '従来世代'
       END AS generation_category 
FROM employees;

-- 給与に応じたボーナス率設定（高給与: 20%、中給与: 15%、低給与: 10%）
SELECT employee_id, salary,
       CASE 
           WHEN salary >= 800000 THEN salary * 0.20
           WHEN salary BETWEEN 500000 AND 799999 THEN salary * 0.15
           ELSE salary * 0.10
       END AS bonus_amount 
FROM employees;

INNER JOIN と LEFT JOIN

概要

テーブル結合は、複数のテーブルから関連するデータを取得するために使用されます。INNER JOIN は共通するデータのみを取得し、LEFT JOIN は左側のテーブルのすべての行を取得し、右側に一致するデータがない場合は NULL を返します。

構文

-- INNER JOIN構文
SELECT カラム1, カラム2, ...
FROM テーブルA
INNER JOIN テーブルB ON テーブルA.カラム = テーブルB.カラム;
-- LEFT JOIN構文
SELECT カラム1, カラム2, ...
FROM テーブルA
LEFT JOIN テーブルB ON テーブルA.カラム = テーブルB.カラム;

使用例

-- INNER JOIN例: 社員とその所属部署を取得
SELECT employees.first_name, employees.last_name, departments.department_name
FROM employees
INNER JOIN departments ON employees.department_id = departments.department_id;

-- LEFT JOIN例: 全社員とその所属部署（未所属の場合はNULL）
SELECT employees.first_name, employees.last_name, departments.department_name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.department_id;

LIKE と NOT LIKE

概要

LIKE は文字列のパターンマッチングを行うために使用されます。一方、NOT LIKE は指定したパターンに一致しないデータを取得します。

使用例

-- 名前が「山」で始まる社員を検索
SELECT * FROM employees WHERE first_name LIKE '山%';

-- メールアドレスが「example.com」を含む社員を検索
SELECT * FROM employees WHERE email LIKE '%example.com';

-- 名前が「山」で始まらない社員を検索
SELECT * FROM employees WHERE first_name NOT LIKE '山%';

-- メールアドレスが「example.com」を含まない社員を検索
SELECT * FROM employees WHERE email NOT LIKE '%example.com';

COALESCE 関数

概要

COALESCE 関数は、引数の中で最初に NULL でない値を返します。これにより、NULL 値を別の値に置き換えることができます。

構文

COALESCE(値1, 値2, ..., デフォルト値)

使用例

-- 例: コミッション率が NULL の場合は 0 に置き換え
SELECT employee_id, COALESCE(commission_pct, 0) AS adjusted_commission 
FROM employees;

-- 複数カラムの統合（name1 が NULL の場合は name2、それも NULL の場合は「名無し」）
SELECT employee_id, COALESCE(name1, name2, '名無し') AS full_name 
FROM customers;