SQLノート(MySQL)

MySQLをベースとしたデータベース(SQL)全般の汎用的な知識をまとめたノート。

間違ってるもしくはよくないって箇所があればご指摘お願いします。

命名規則

基本はプロジェクトのルールに則る。個人制作の場合、ルールアプリケーションの特性などに合わせて決めるのが一番いいとは思うが、素人のうちはこれを基準にする。

色々参考にして、こうすることにした

  • 大文字は使わない
  • なるべく略さない
  • 区切りはアンダーバー
    • ハイフンはエラー
  • 場合によっちゃ接頭辞を設ける
    • 「t_<TABLE>」とか「m_<TABLE>」とか
    • トランザクション、マスタなど
  • カラム名は基本「users_id」ではなく「id」とする
    • t_users.idと参照できるため
    • 結合でカラム名が被る場合は別名をつける
    • 結合時は別名で重複を防ぐ
  • 必須カラム(id, created_at, updated_at)
    • 主キーと作成日時と更新日時
    • いずれもデフォルトで挿入されるように設定する
  • flgは使わない
    • 「delete_flg」ではtrueがどっちか分かりにくい
    • 「is_deleted」のように「is_<項目名>」とする
    • これはSQL以外にも通ずる事かもしれない
  • 命名時に予約語(SELECT, WHERE, INなど)は使わない
    • 予約語は、RDBMSによっても変わる
    • MySQLではバッククォートで囲むと予約語も使えるが、控える
    • MySQLの予約語一覧

高速化

まとめると

  • 全射影(SELECT *)は避ける
  • COUNT(*)も避ける
  • 副問合せはINよりEXISTS
  • INのリストには確率の高いものから先に書く
  • WHERE句の条件は確率の高いものから先に書く
  • UNIONよりUNION ALL
  • 困ったらEXPLAIN句で実行計画を探る
  • INDEXは適切に設定する
    • 絞り込み条件、結合条件、並び替え条件によくつかうカラムに対して設定すると効果を得られやすい
    • 更新、削除、挿入時にINDEX登録するためオーバーベッドが増すことを頭に入れておく

その他

  • SELECT句などの予約語や関数は大文字を使う
  • 改行やインデントはその時々で見やすいようにする

DATABASE作成

CREATE DATABASE <DB_NAME>

例

CREATE DATABASE application;

TABLE作成

CREATE TABLE <TABLE_NAME> (<COLUMN_NAME> <DATA_TYPE> [<その他プロパティ> ...], ...)

例

CREATE TABLE users (
    id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name varchar(255) NOT NULL,
    age int NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

構成確認

SHOW COLUMNS FROM users;
-- MySQLはDESC t_users;でも同じ
-- +------------+--------------+------+-----+-------------------+-----------------------------+
-- | Field      | Type         | Null | Key | Default           | Extra                       |
-- +------------+--------------+------+-----+-------------------+-----------------------------+
-- | id         | int(11)      | NO   | PRI | NULL              | auto_increment              |
-- | name       | varchar(255) | NO   |     | NULL              |                             |
-- | age        | int(11)      | NO   |     | NULL              |                             |
-- | created_at | datetime     | NO   |     | CURRENT_TIMESTAMP |                             |
-- | updated_at | datetime     | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
-- +------------+--------------+------+-----+-------------------+-----------------------------+

INSERTデータ挿入

一行追加

INSERT INTO <DB_NAME> (
    <COLUMN_NAME1>,
    <COLUMN_NAME2>,
    ...
) VALUES (
    <DATA1>,
    <DATA2>,
    ...
);

複数行追加

INSERT INTO <DB_NAME> (
    <COLUMN_NAME1>,
    <COLUMN_NAME2>,
    ...
) VALUES (
    <DATA1>,
    <DATA2>,
    ...
),(
    <DATA3>,
    <DATA4>,
    ...
), ...;

例

INSERT INTO users (
    name,
    age
) VALUES (
    "kazz",
    20
);
-- +----+------+-----+---------------------+---------------------+
-- | id | name | age | created_at          | updated_at          |
-- +----+------+-----+---------------------+---------------------+
-- |  1 | kazz |  20 | 2019-03-23 23:44:40 | 2019-03-23 23:44:40 |
-- +----+------+-----+---------------------+---------------------+
INSERT INTO users (
    name,
    age
) VALUES (
    "A",
    14
),(
    "B",
    39
),(
    "C",
    23
);
-- +----+------+-----+---------------------+---------------------+
-- | id | name | age | created_at          | updated_at          |
-- +----+------+-----+---------------------+---------------------+
-- |  1 | kazz |  20 | 2019-03-23 23:44:40 | 2019-03-23 23:44:40 |
-- |  2 | A    |  14 | 2019-03-24 11:17:21 | 2019-03-24 11:17:21 |
-- |  3 | B    |  39 | 2019-03-24 11:17:21 | 2019-03-24 11:17:21 |
-- |  4 | C    |  23 | 2019-03-24 11:17:21 | 2019-03-24 11:17:21 |
-- +----+------+-----+---------------------+---------------------+

INSERTにSELECTの問い合わせ結果を代入することもできる。t_example1のcol1~col3にt_example2のcolA~colCを抽出行INSERTする。

INSERT INTO t_example1 (
    col1,
    col2,
    col3
) SELECT colA, colB, colC FROM t_example2;

全列挿入する場合は、テーブル名の後の（）を省略できるが、そんな使わない。

SELECT抽出

記述順

  • SELECT
  • DISTINCT
  • FROM
  • JOIN
  • ON
  • WHERE
  • GROUP BY
  • HAVING
  • ORDER BY
  • LIMIT

評価順

いくらLIMITで絞ってもDB容量が大きいと処理時間がかかることがわかりますね。WHEREやHAVINGで十分に条件を絞ることで、少しは高速化が望めそうです。

  • FROM
  • ON
  • JOIN
  • WHERE
  • GROUP BY
  • HAVING
  • SELECT
  • DISTINCT
  • ORDER BY
  • LIMIT

基本形

SELECT * FROM <TABLE_NAME>;
SELECT <COL1>, <COL2>, ... FROM <TABLE_NAME>;

例

SELECT id,name FROM users;

条件指定

SELECT * FROM <TABLE_NAME> WHERE <条件>;
SELECT id, name FROM users WHERE (id = 1 OR id = 2) AND name = "kazz";

INでまとめて(2つは結果が同じ)

SELECT id, name FROM users WHERE id = 1 OR id = 2 OR id = 3;
SELECT id, name FROM users WHERE id IN (1,2,3);

NOT INで含まれていない行

SELECT id, name FROM users WHERE id NOT IN (1,2,3);

BETWEENで範囲指定(2つは結果が同じ)

SELECT id, name FROM users WHERE 1 <= id AND id <= 10;
SELECT id, name FROM users WHERE id BETWEEN 1 AND 10;

BETWEENでこんな指定も

BETWEEN "2018-01-01 00:00:00" AND "2018-03-01 00:00:00"
BETWEEN "A" AND "Z"

LIKEで曖昧検索

SELECT id, name FROM users WHERE name LIKE <検索文字>;

例

「%」 : 0文字以上
「_」 : 1文字

例えば
"%一郎"だったら、「〇〇 一郎」さんにヒット。
"user___%"だったら、「user[三文字以上]」にヒット。
"%SQL%"だったら、「SQL」が含まれていたらヒット。

SELECT id, name FROM users WHERE name LIKE "%kazz%";

NOT LIKEで一致しない行

SELECT id, name FROM users WHERE name NOT LIKE "%kazz%";

NULL判定

SELECT id, name FROM users WHERE name IS NULL;
SELECT id, name FROM users WHERE name IS NOT NULL;

ANY/ALL一括比較

後述のサブクエリで使います

DISTINCTで重複行削除

SELECTの後にDISTINCTで適用されます

SELECT DISTINCT name FROM users;

WHERE結合

これは古臭いうえにあまり使わないでほしいらしい。国家試験に出がちなイメージ。

WHERE句で結合するタイプのやつ。異なるテーブルのカラム同士を突き合わせて、条件に合う行だけを選択します。

SELECT *
FROM <TABLE1>, <TABLE2>
WHERE <TABLE1>.<COL1> = <TABLE2>.<COL2>;

例) 社員テーブルの部署IDと部署テーブルのIDで結合して、かつ部署名が営業部の社員名を抽出する。

SELECT employees.name AS employee_name
FROM employees, departments
WHERE 
    employees.department_id = departments.id
    AND　employees.name = "営業部";

JOIN結合

結合は、内部結合と外部結合がある。

内部結合は、結合元のテーブルと結合できた行だけを返す。外部結合は、結合元のテーブルは固定で、キーが一致しない場合はNULLが埋められる。

INNER JOIN内部結合

SELECT カラム名
FROM テーブル名
INNER JOIN 結合するテーブル名
ON 結合条件;

さっきのWHERE結合をJOINで書き換えるならこう。

SELECT employees.name
INNER JOIN departments
ON employees.department_id = departments.id
WHERE employees.name = "営業部";
employees
+----+-----------+---------------+
| id | name      | department_id |
+----+-----------+---------------+
|  1 | 松本 人志 |             3 |
+----+-----------+---------------+
|  2 | 浜田 雅功 |             1 |
+----+-----------+---------------+
|  3 | 山崎 方正 |             6 |
+----+-----------+---------------+
departments
+----+-----------+
| id | name      |
+----+-----------+
|  1 | 営業部    |
+----+-----------+
|  2 | 開発部    |
+----+-----------+
|  3 | 経理部    |
+----+-----------+
INNER JOINの場合
+----+-----------+---------------+----+-----------+
| id | name      | department_id | id | name      |
+----+-----------+---------------+----+-----------+
|  1 | 松本 人志 |             3 |  3 | 開発部    |
+----+-----------+---------------+----+-----------+
|  2 | 浜田 雅功 |             1 |  1 | 営業部    |
+----+-----------+---------------+----+-----------+
結果
+-----------+
| name      |
+-----------+
| 浜田 雅功 |
+-----------+

高速化の小技で、内部結合の場合、結合条件に絞り込み条件を書いても同じ動きをする。WHERE句が結合後に絞り込みを行い、一方ON句は結合前に絞り込むため、外部結合では適切な結果が得られない。

SELECT employees.name
FROM employees
INNER JOIN departments
ON
    employees.department_id = departments.id
    AND employees.name = "営業部";

LEFT (OUTER) JOIN外部結合

OUTERは省略可。LEFT JOINでも同じです。

SELECT カラム名
FROM テーブル名
LEFT JOIN 結合するテーブル名
ON 結合条件;

例) INNER JOINの結合条件と同じ

SELECT employees.name
FROM employees
LEFT JOIN departments
ON employees.department_id = departments.id
WHERE departments.name = "営業部";

結果は同じですが、結合直後の表は違います。

LEFT JOINの場合
+----+-----------+---------------+------+-----------+
| id | name      | department_id | id   | name      |
+----+-----------+---------------+------+-----------+
|  1 | 松本 人志 |             3 |    3 | 経理部    |
+----+-----------+---------------+------+-----------+
|  2 | 浜田 雅功 |             1 |    1 | 営業部    |
+----+-----------+---------------+------+-----------+
|  3 | 山崎 方正 |             6 | NULL | NULL      |
+----+-----------+---------------+------+-----------+
結果
+-----------+
| name      |
+-----------+
| 浜田 雅功 |
+-----------+

RIGHT (OUTER) JOIN

RIGHT OUTER JOINにすると、JOINしたテーブルが結合元になります。文字通り、左右が入れ替わっただけですね。これはあんまり使わないイメージ。

FROMのテーブルとJOINのテーブルを入れ替えたら、LEFT JOINと同じ動きをします。

SELECT employees.name
FROM departments
RIGHT JOIN employees
ON employees.department_id = departments.id
WHERE employees.name = "営業部";

その他のJOIN

お互いのテーブル全てに結合するALL OUTER JOINや互いの突き合わせ結合するCROSS JOINなどがある。

めったに使わないと思う。

集計

集計関数

グループ化

サブクエリー

SELECTの中でSELECTを使うみたいな感じのテクニック。副問合せともいったりする。これは処理が重くなるので、他のアプローチがないかを考えて思いつかなかった場合に使う。

一行のサブクエリー

The present writer kazz.

関連記事

+‪ラズパイと温度センサ(LM75B)で室温の推移をグラフ化する‬
+LINEBOTで画像文字起こしと有害検出
+コマンドで画面の明るさをコントロールする
+自然言語処理のCOTOHA APIをPHPで使ってみた
+傘が必要かをLEDで教えてくれるIoTをESPrで作った