SQL全部まとめたい | 基礎編

はじめに

SQLに出会って４年ほど経って、DB設計も学習レベルとプライベートで何回も経験したし
データベースについて考えるのって面白いよね
（実務はやったことない素人）

というわけで、それなりにまとめてみよう。ついでに応用編も勉強したよ

※基本 MySQL ですが、たまに SQL Server, Oracle が混ざっています。

SQLの概要

分類	SQL	役割
データ操作言語	SELECT INSERT UPDATE DELETE	データ検索 データ追加 データ更新 データ削除
データ定義言語	CREATE ALTER DROP	オブジェクト作成 オブジェクト変更 オブジェクト削除
データ制御言語	GRANT REVOKE COMMIT ROLLBACK	権限を与える 権限を取り消す トランザクションをコミット トランザクションを取り消す

基礎編では、「データ操作言語」と「データ定義言語」を
応用編では、「データ制御言語」をまとめました
SQL以外のDBに関する理論とかはまた別にしました

• SQL応用編はこちら

• DB設計はこちら

基礎編（データ操作言語・データ定義言語）

全部の説明書いたら大変なのでいい感じに割愛したい。
実際にやってみる人は、《 CREATE文 > ALTER文 > INSERT文 > SELECT文 > UPDATE文 > DELETE文 > DROP文 》の順番で試すべきだと思うよ。

データ操作言語

ややこしいけど、それぞれの操作の頭文字をとって「CRUD」とか言う

SELECT文

データを取得（READ）する文

SELECT * FROM users;
SELECT name + 'さん' AS 名前, mail AS メール FROM users;
SELECT CONCAT(name, 'さん') AS 名前, mail AS メール FROM users;
SELECT id, salary * 1.75 + 100000 AS ボーナス FROM users;
SELECT DISTINCT department_id AS 部門番号 FROM users;

* ：すべて
AS ：列に対する別名をつける
+ CONCAT ：文字列連結演算子
+ - * / ：算術演算子
DISTINCT ：重複排除

ORDER BY句

データの並び替え

SELECT * FROM users
    ORDER BY department_id ASC

ASC ：昇順（小さい順）
DESC ：降順（大きい順）

SQLの GROUP BY / ORDER BY には数字を指定しよう

WHERE句

データの絞り込み
利用できる演算子は以下13種類

• 比較演算子 = < > <= >= <>
• 論理演算子 AND OR NOT
• BETWEEN
• IN
• IS NULL
• LIKE

SELECT * FROM users
    WHERE salary < 375000
    OR hire_date BETWEEN '2020/04/01' AND '2024-03-31'

SELECT * FROM users
    WHERE name LIKE '_田%'

_ ：任意の１文字
% ：ワイルドカード

集計関数などは後述してます

INSERT文

データを挿入（CREATE）する文

-- 基本構文
INSERT INTO users (name,department_id,state)
    VALUES('okano',1,0);
-- 全カラム指定
INSERT INTO users
    VALUES('tanisaki',1,NULL,NULL,NULL,0);
-- 複数行
INSERT INTO tbl_name
    (a,b,c)
VALUES
    (1,2,3),
    (4,5,6),
    (7,8,9);

AUTO_INCREMENTとNULL許容の列は省略可
すべての列を指定する場合は列名の記述を省略可

UPDATE文

データを更新（UPDATE）する文

UPDATE users SET name = 'sakurai' WHERE id = 3;

DELETE文

データを削除（DELETE）する文
WHERE句を省略した場合、すべての行が消えるので注意

DELETE FROM users WHERE id = 1;

データ定義言語

CREATE文

オブジェクト（データベースやテーブル）を作成する文

1. データベースを作成

   CREATE DATABASE sampleDB;
   

2. 使用するデータベースに移動
   MySQL の場合 SHOW が使えて便利

   SHOW DATABASES;
   USE sampleDB;
   SHOW TABLES;
   

3. テーブルを作成

   CREATE TABLE `users` (
   `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
   `name` varchar(255) NOT NULL,
   `department_id` int(11) NOT NULL,
   `salary` int(11),
   `mail` varchar(255),
   `hire_date` date,
   `state` int(11) NOT NULL
   );
   
   CREATE TABLE `departments` (
   `id` int(11) NOT NULL COMMENT '部署ID' AUTO_INCREMENT PRIMARY KEY,
   `name` varchar(255) NOT NULL COMMENT '部署名',
   `state` int(11) NOT NULL COMMENT '状態(0:通常,1:削除)'
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '部署';
   

   • バッククォートはいらない
   • コメントがあるとDBMSやデータベースクライアントで見たときにわかりやすい
   • ENGINE や DEFAULT CHARSET はデータベースの設定に依存するため、明示的に指定する必要はない
   • データ型: INT(11) はMySQLにおいて、単に INT としても同じ意味ですが、正確性のため INT だけで十分
   • データ型についてはこちら
4. VIEWテーブルの作成
   ビューとは仮想的な表のこと
   テーブルと同じように利用できる「SELECT文」をデータベース内に定義しておく感じ
   テーブル結合めんどいやつとか、アクセス制限したいやつとかに使う
   ビューとストアドプロシージャの比較と使い分け

   CREATE VIEW view_show_users AS
   SELECT
   u.id AS user_id,
   u.name AS user_name,
   d.name AS department_name
   FROM users u
   LEFT JOIN departments d
   ON u.department_id = d.id
   WHERE u.state = 0
   ;
   

5. 中間テーブル
   カーディナリティが多対多のとき必要（1対多と多対1にわける）

   CREATE TABLE user_departments (
       user_id INT NOT NULL,
       department_id INT NOT NULL,
       PRIMARY KEY (user_id, department_id),
       FOREIGN KEY (user_id) REFERENCES users(U_id),
       FOREIGN KEY (department_id) REFERENCES departments(D_id)
   ) COMMENT '中間テーブル社員部署';
   

6. 外部キー制約
   1対多のときは多のほうのテーブルにつけます
   1対1の関係では、どちらのテーブルに外部キー制約を設定しても構いませんが、通常は「子」側のテーブルに外部キー制約を設定することが一般的

   CREATE TABLE profiles (
       id INT PRIMARY KEY AUTO_INCREMENT,
       user_id INT UNIQUE,
       -- その他のプロフィール情報のカラム
       FOREIGN KEY (user_id) REFERENCES users(id)
   );
   

   UNIQUE：指定されたカラムの値が重複しないようにする

ALTER文

オブジェクト（データベースやテーブル）を変更する文
テーブル制約以外あんまりやったことないから適当に書いとこ

• 基本構文

  ALTER TABLE table_name action column_name data_type;
  

  table_name ：操作対象のテーブル名
  action ：おこなう操作（例えばADD、DROP、ALTER、RENAMEなど）
  column_name ：操作対象の列名
  data_type ：データ型を表す

  ALTER TABLE departments ADD COLUMN created_at date;
  ALTER TABLE departments ALTER COLUMN created_at TYPE int(11);
  ALTER TABLE departments RENAME COLUMN created_at TO color;
  ALTER TABLE departments DROP COLUMN color;
  

• users.id をオートインクリメントに変更

  ALTER TABLE users ADD PRIMARY KEY (id);
  ALTER TABLE users CHANGE id id INT AUTO_INCREMENT;
  

• テーブル制約をつける

  ALTER TABLE `users`
  ADD CONSTRAINT `departments_users` FOREIGN KEY (`department_id`) REFERENCES `departments` (`id`);
  

  バッククォートはいらない

DROP文

オブジェクト（データベースやテーブル）を削除する文
中身ごと消え去るので注意

DROP TABLE users;
DROP DATABASE sampleDB;
DROP VIEW view_show_users;

集計関数とグループ化

集計関数： SUM AVG MAX MIN COUNT

これでいろんな統計がとれますね

集計関数	意味
SUM	合計
AVG	平均
MAX	最大値
MIN	最小値
COUNT	行数のカウント

SELECT
    SUM(salary) AS みんなの給与の合計,
    AVG(salary) AS みんなの給与の平均,
    AVG(ISNULL(salary,0)) AS みんなの給与の平均(NULLの人も含める),
    COUNT(*) AS 人数
    COUNT(mail) AS メール登録している人数
    FROM users;

グループ化 GROUP BY

グループごとの分析ができちゃう
構文

SELECT 列名 1, 列名 2, …
    FROM テーブル名
    WHERE 検索する行の条件
    GROUP BY 列名 1, 列名 2, ･･･
    ［ HAVING 絞込み条件 ］

SELECT AVG(salary), department_id
    FROM users
    GROUP BY department_id

集計関数じゃなくて列名指定したら一番上の行の値を取得する
（トランプをまとめたら一番上のカードしか見えないイメージ）

-- メール登録してる人が５人以上いる部署を取得
SELECT department_id
    FROM users              -- usersデッキを用意
    WHERE mail IS NOT NULL  -- メール登録してないカードを排除
    GROUP BY department_id  -- 部署ごとに分ける
    HAVING COUNT(*) >= 5    -- ５枚未満の部署を排除
    ORDER BY COUNT(*) DESC  -- 多い順に並べる

LIMIT, OFFSET

件数や開始位置の指定
LIMIT ：表示件数
OFFSET ：スキップ

-- 1～13 を抽出
SELECT id FROM users LIMIT 13; -- 13行表示
-- 6～9 を抽出
SELECT id FROM users LIMIT 5,4; -- インデックスの5から4行表示
SELECT id FROM users LIMIT 4 OFFSET 5; -- 5行飛ばして4行表示

JOIN（テーブル結合）

条件を先に考慮してからSELECTする。メモリを圧迫しない

• 内部結合 INNER JOIN
  条件に合致する行のみ取得

  SELECT 選択リスト
      FROM 表A a
      INNER JOIN 表B b
      ON a.結合キー列 = b.結合キー列
  

  下記の書き方もありえる

  SELECT 選択リスト
      FROM 表A a, 表B b
      WHERE a.結合キー列 = b.結合キー列
  

• 外部結合 OUTER JOIN
  片方の表にしかないデータも取得する。空きは NULL で埋められる

  SELECT
      u.id AS user_id,
      u.name AS user_name,
      d.name AS department_name
      FROM users u
      LEFT OUTER JOIN departments d
      ON u.department_id = d.id
      WHERE u.state = 0
  ;
  

  「LEFT, RIGHT, FULL」の中から選ぶのじゃ

• JOIN ... ON ... を複数書いて無限に結合できる

• 自己結合（同じ表を結合）とかもできる

• OUTER は省略可（LEFT JOIN, RIGHT JOIN, FULL JOIN）

• OUTER JOIN のみ記述したら、デフォルトの LEFT JOIN になる

• JOIN のみ記述したら、デフォルトの INNER JOIN になる

SELECT文の基礎まとめ

SELECT文の基礎句全盛
記述の順番と処理の順番は違うよ

記述順	処理順	説明
SELECT	6	選択
FROM	1	テーブル決定
JOIN	2	結合
WHERE	3	絞り込み
GROUP BY	4	グループ化
HAVING	5	グループの絞り込み
ORDER BY	7	並び替え
LIMIT	8	行数制限

参考記事

サブクエリ（副問い合わせ）

直積（全組み合わせ表）を作った後にSELECTする（重いよ）

SELECT 選択リスト
    FROM 表A a
    WHERE 列 = (SELECT文)
SELECT 選択リスト
    FROM 表A a, 表B b
    WHERE ...

SELECT *
    FROM 表A a
    WHERE salary = (SELECT MAX(salary) FROM 表B)

UNION（連結）（和集合）

複数のSELECTをまとめて表示する
同じ数の列数じゃないとできないよ

SELECT文1 UNION [ALL] SELECT文2

UNION ：重複しているものは一つだけ表示
UNION ALL ：重複しているものも全て表示
UNION [ALL] SELECT文2 を複数書いて無限に連結できる

SELECT * FROM users WHERE id in (1,3,4)
    UNION ALL
    SELECT * FROM users WHERE salary = 450000
;

INTERSECT（積集合） や EXCEPT（差集合） は結合でできるっぽい
参考記事

CASE 式

• プログラム言語の Switch みたいな条件分岐ができる

  -- 基本構文
  CASE［ 式 ］
      WHEN 条件 1
          THEN 条件 1 を満たしている場合の値
      WHEN 条件 2
          THEN 条件 2 を満たしている場合の値
      ELSE
          すべての条件を満たしていない場合の値
      END
  

  -- サンプル１
  SELECT name, salary,
      CASE
          WHEN salary >= 100000 THEN '管理職'
          WHEN salary >= 50000 THEN 'スタッフ'
          ELSE 'インターン'
      END AS position
  FROM users;
  

  -- サンプル２
  SELECT
      id,
      CASE 部門番号
          WHEN 10 THEN '総務部'
          WHEN 20 THEN '営業部'
          ELSE 'その他'
      END,
      name
  FROM users;
  

• SQLのCASE式を用いてORDER BY でソート列を作るテクニック

  SELECT key
      FROM Tests
      ORDER BY CASE key
          WHEN 'B' THEN 1
          WHEN 'A' THEN 2
          WHEN 'D' THEN 3
          WHEN 'C' THEN 4
          ELSE NULL
      END;
  

  ソート列も出力させたい場合

  SELECT
      key,
      CASE key
          WHEN 'B' THEN 1
          WHEN 'A' THEN 2
          WHEN 'D' THEN 3
          WHEN 'C' THEN 4
          ELSE NULL
      END AS sort_col
  FROM Tests
  ORDER BY sort_col;
  

  （インデント適当すぎ）

クロス集計

2つ以上のカテゴリーに関連するデータを交差させ、各組み合わせの集計を行う
CASE式をSUM関数の中で使用

-- 男女別どっちなんだい
SELECT
    breakfast_flag
    ,SUM(CASE gender WHEN 'm' THEN 1 ELSE 0 END) AS male
    ,SUM(CASE gender WHEN 'f' THEN 1 ELSE 0 END) AS female
FROM peoples
GROUP BY breakfast_flag
-- ＋
UNION ALL
-- 全体
SELECT
    NULL AS breakfast_flag
    ,SUM(CASE gender WHEN 'm' THEN 1 ELSE 0 END) AS male
    ,SUM(CASE gender WHEN 'f' THEN 1 ELSE 0 END) AS female
FROM peoples
;

参考記事

COALESCE関数

データの欠失値をデフォルト値に置換

COALESCE(列a,0) -- 列aの値がNULLなら0に置換

OVER句（ウィンドウ関数）

データの順序（ランキング）を取り扱ったり、集計する範囲を指定したりする
集約関数を使いたいが各行の値も取得したい時、GROUP BY ではなく OVER を使う
グループ化せずに様々な集計が可能となる

-- 基本構文
関数名(列名) OVER(
    [PARTITION BY(分割する列名リスト)]
    [ORDER BY(整列列名リスト)]
    [フレーム句]
)

• 関数名 ：集約関数もしくはウィンドウ関数
  《ウィンドウ関数の種類（一部）》
  • ROW_NUMBER() ：各行に一意となる行番号を付与
  • RANK() ：ランキング（同率は番号を飛ばす）を付与
  • DENSE_RANK() ：ランキング（同率は番号を飛ばさない）を付与
  • LAG(列名[,n]) ：n 行前を取得（n 省略時は１）
  • LEAD(列名[,n]) ：n 行後を取得（n 省略時は１）
• PARTITION BY ：データをどのようにグループ化するかを指定
• ORDER BY ：データをどのような順序で処理するかを指定
• フレーム句 ：
  • CURRENT ROW ：現在の行
  • n PRECEDING ：n 行前
  • n FOLLOWING ：n 行後

-- サンプル１
-- 給与のランキングを表示
SELECT 
    id,
    salary,
    RANK() OVER (
        ORDER BY salary DESC
    ) AS salary_rank
FROM users;

-- サンプル２
-- 各行の前後2つの行に対して平均給与が計算
SELECT 
    id,
    salary,
    AVG(salary) OVER (
        ORDER BY salary
        ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING
    ) AS avg_salary
FROM users;

ROLLUP,CUBE

階層的な集計を行うことができる
CUBEは多次元データベースの分析
参考記事

WITH句（一時テーブル、一時ビュー）

副問い合わせに使う仮想テーブルをあらかじめ作る

WITH [RECURSIVE]
    問合せ名・テーブル名(列名リスト)
    AS(問合せ内容)

可読性UP
参考記事

PIVOT

行列変換（縦横を入れ替える）
データの可視化や分析において重要らしい
ピボットテーブルのVIEWを作成すると効率化なる
MySQLでは直接サポートされてない