LoginSignup
0
0

More than 1 year has passed since last update.

@koojiiiii(こじ)

MySQLについて[まとめ２]

Last updated at Posted at 2021-11-16

集計関数

DROP TABLE IF EXISTS posts;
CREATE TABLE posts (
  id INT NOT NULL AUTO_INCREMENT,
  message VARCHAR(140), 
  likes INT,
  PRIMARY KEY (id)
);

INSERT INTO posts (message, likes) VALUES 
  ('post-1', 12),
  ('post-2', 8),
  ('post-3', 11),
  ('post-4', 3),
  ('post-5', NULL),
  ('post-6', 9),
  ('post-7', 4),
  ('post-8', NULL),
  ('post-9', 31);

このデータから集計処理を行う

  • COUNT 関数 データの個数を調べる
SELECT COUNT(likes) FROM posts;

likes にはふたつ NULL があるが、 likes の NULL を除いた個数を調べるには COUNT(likes) とする

SELECT COUNT(id) FROM posts;

単に全体の行数を取得したいなら主キーで数えればいいのでidを入れる

SELECT COUNT(*)

これでも全体の行数を取得できる

+--------------+
| COUNT(likes) |
+--------------+
|            7 |
+--------------+
+-----------+
| COUNT(id) |
+-----------+
|         9 |
+-----------+
+----------+
| COUNT(*) |
+----------+
|        9 |
+----------+
  • １番上がNULLを抜かした個数
  • 下２つは全体の数
SELECT SUM(likes) FROM posts;
SELECT AVG(likes) FROM posts;
SELECT MAX(likes) FROM posts;
SELECT MIN(likes) FROM posts;

上から

  • 合計
  • 平均
  • 最大値
  • 最小値
+------------+
| SUM(likes) |
+------------+
|         78 |
+------------+
+------------+
| AVG(likes) |
+------------+
|    11.1429 |
+------------+
+------------+
| MAX(likes) |
+------------+
|         31 |
+------------+
+------------+
| MIN(likes) |
+------------+
|          3 |
+------------+

グループ化

DROP TABLE IF EXISTS posts;
CREATE TABLE posts (
  id INT NOT NULL AUTO_INCREMENT,
  message VARCHAR(140), 
  likes INT,
  area VARCHAR(20),
  PRIMARY KEY (id)
);

INSERT INTO posts (message, likes, area) VALUES 
  ('post-1', 12, 'Tokyo'),
  ('post-2', 8, 'Fukuoka'),
  ('post-3', 11, 'Tokyo'),
  ('post-4', 3, 'Osaka'),
  ('post-5', 8, 'Tokyo'),
  ('post-6', 9, 'Osaka'),
  ('post-7', 4, 'Tokyo'),
  ('post-8', 10, 'Osaka'),
  ('post-9', 31, 'Fukuoka');

カラムにareaを追加した

  • DISTINCT 重複を省いたデータを抽出することができる
SELECT DISTINCT area FROM posts;

このデータにどの area が含まれているか、一覧で見ることができる

+---------+
| area    |
+---------+
| Tokyo   |
| Fukuoka |
| Osaka   |
+---------+
  • GROUP BY レコードをグループ化する
SELECT area, SUM(likes) FROM posts GROUP BY area;

area ごとにlikes数の合計を求めることができる

+---------+------------+
| area    | SUM(likes) |
+---------+------------+
| Fukuoka |         39 |
| Osaka   |         22 |
| Tokyo   |         35 |
+---------+------------+

抽出条件の指定

  • HAVING

GROUP BY した結果に条件を付けて抽出できる
WHERE が GROUP BY より前に処理されるので、後ろに書いてはいけないというルールがあるので
HAVINGを使う必要がある

SELECT 
  area, 
  SUM(likes) 
FROM 
  posts 
GROUP BY 
  area 
HAVING
  SUM(likes) > 30;

+---------+------------+
| area    | SUM(likes) |
+---------+------------+
| Fukuoka |         39 |
| Tokyo   |         35 |
+---------+------------+

SUM(likes)の合計が30以上のものを抽出している

  • WHEREを使うとき
SELECT 
  area, 
  SUM(likes) 
FROM 
  posts 
WHERE 
  likes > 10
GROUP BY 
  area;

GROUP BY より前に書く必要がある
ここでは、
posts の全てのレコードから likes が 10 より大きいものだけを抽出したあとに GROUP BY する
という意味になるので、

('post-1', 12, 'Tokyo')
('post-3', 11, 'Tokyo'),
('post-9', 31, 'Fukuoka');

グループ化されるのはこれだけで、ここから集計が行われる

+---------+------------+
| area    | SUM(likes) |
+---------+------------+
| Fukuoka |         31 |
| Tokyo   |         23 |
+---------+------------+

IF()､CASE

likesをチーム分けする（IF）

SELECT
  *,
  IF(likes > 10, 'A', 'B') AS team
FROM 
  posts;

10 より大きかったら A チーム、それ以外は B チームとし、またteam という別名にして、
抽出するという意味

+----+---------+-------+---------+------+
| id | message | likes | area    | team |
+----+---------+-------+---------+------+
|  1 | post-1  |    12 | Tokyo   | A    |
|  2 | post-2  |     8 | Fukuoka | B    |
|  3 | post-3  |    11 | Tokyo   | A    |
|  4 | post-4  |     3 | Osaka   | B    |
|  5 | post-5  |     8 | Tokyo   | B    |
|  6 | post-6  |     9 | Osaka   | B    |
|  7 | post-7  |     4 | Tokyo   | B    |
|  8 | post-8  |    10 | Osaka   | B    |
|  9 | post-9  |    31 | Fukuoka | A    |
+----+---------+-------+---------+------+

となる

likesをチーム分けする（CASE）

SELECT
  *,
  CASE
    WHEN likes > 10 THEN 'A'
    WHEN likes > 5 THEN 'B'
    ELSE 'C'
  END AS team
FROM 
  posts;

CASE の場合はいくらでも条件を増やせる
ここではlikes が 10 より大きかったら A チーム、 10 以下だけど 5 より大きかったら B チーム、そしてそれ以外は C チームという意味

+----+---------+-------+---------+------+
| id | message | likes | area    | team |
+----+---------+-------+---------+------+
|  1 | post-1  |    12 | Tokyo   | A    |
|  2 | post-2  |     8 | Fukuoka | B    |
|  3 | post-3  |    11 | Tokyo   | A    |
|  4 | post-4  |     3 | Osaka   | C    |
|  5 | post-5  |     8 | Tokyo   | B    |
|  6 | post-6  |     9 | Osaka   | B    |
|  7 | post-7  |     4 | Tokyo   | C    |
|  8 | post-8  |    10 | Osaka   | B    |
|  9 | post-9  |    31 | Fukuoka | A    |
+----+---------+-------+---------+------+

抽出結果を別のテーブルにする

  • CREATE TABLE テーブル名 AS 抽出条件

抽出結果を別テーブルとして切り出す

DROP TABLE IF EXISTS posts_tokyo;
CREATE TABLE posts_tokyo AS SELECT * FROM posts WHERE area = 'Tokyo';

DROP TABLE IF EXISTS posts_copy;
CREATE TABLE posts_copy AS SELECT * FROM posts;

DROP TABLE IF EXISTS posts_skeleton;
CREATE TABLE posts_skeleton LIKE posts;

SHOW TABLES;
SELECT * FROM posts_tokyo;
SELECT * FROM posts_copy;
SELECT * FROM posts_skeleton;

上から、

  • area が Tokyo のレコードだけを抽出し、 posts_tokyo というテーブルを作成

  • 全てのレコードを抽出し、 posts_copy テーブルを作成
    要はコピーを作成している

  • データの構造だけコピーし,posts_skeletonテーブルを作成

  • SHOW TABLES;でテーブルの一覧を表示

DROP TABLE IF EXISTS

これを毎回つけているのは、それぞれ作成したコピーが既に存在しているとエラーとなるので、
既にあったテーブルをその都度削除して、テーブルを表示させるということ

+-----------------+
| Tables_in_myapp |
+-----------------+
| posts           |
| posts_copy      |
| posts_skeleton  |
| posts_tokyo     |
+-----------------+
+----+---------+-------+-------+
| id | message | likes | area  |
+----+---------+-------+-------+
|  1 | post-1  |    12 | Tokyo |
|  3 | post-3  |    11 | Tokyo |
|  5 | post-5  |     8 | Tokyo |
|  7 | post-7  |     4 | Tokyo |
+----+---------+-------+-------+
+----+---------+-------+---------+
| id | message | likes | area    |
+----+---------+-------+---------+
|  1 | post-1  |    12 | Tokyo   |
|  2 | post-2  |     8 | Fukuoka |
|  3 | post-3  |    11 | Tokyo   |
|  4 | post-4  |     3 | Osaka   |
|  5 | post-5  |     8 | Tokyo   |
|  6 | post-6  |     9 | Osaka   |
|  7 | post-7  |     4 | Tokyo   |
|  8 | post-8  |    10 | Osaka   |
|  9 | post-9  |    31 | Fukuoka |
+----+---------+-------+---------+

VIEW

VIEW という仕組みを使えば元テーブルと連動する仮想的なテーブルを作ることができる

DROP VIEW IF EXISTS posts_tokyo_view;
CREATE VIEW posts_tokyo_view AS SELECT * FROM posts WHERE area = 'Tokyo';

UPDATE posts SET likes = 15 WHERE id = 1;

元データをアップデートしても、VIEWを使えば派生して作ったデータにもちゃんと反映される

UNION

2 つのクエリのカラム数とデータ型が一致している場合、結果を縦に繋げて抽出できる

(SELECT * FROM posts ORDER BY likes DESC LIMIT 3)
UNION ALL
(SELECT * FROM posts ORDER BY likes LIMIT 1);

UNION ALLで２つのクエリ（命令）をつなげて、ひとつのクエリとしている
なので、；は最後にしかつかないので注意

+----+---------+-------+---------+
| id | message | likes | area    |
+----+---------+-------+---------+
|  9 | post-9  |    31 | Fukuoka |
|  1 | post-1  |    12 | Tokyo   |
|  3 | post-3  |    11 | Tokyo   |
|  4 | post-4  |     3 | Osaka   |
+----+---------+-------+---------+

レコードの上位３つと下位１つが合体して抽出されている

サブクエリ

クエリの中で使うクエリのこと

SELECT 
  *, 
  (SELECT AVG(likes) FROM posts) AS avg 
FROM 
  posts;

SELECTの中でSELECTを使いレコードごとに集計関数を使っている

+----+---------+-------+---------+---------+
| id | message | likes | area    | avg     |
+----+---------+-------+---------+---------+
|  1 | post-1  |    12 | Tokyo   | 10.6667 |
|  2 | post-2  |     8 | Fukuoka | 10.6667 |
|  3 | post-3  |    11 | Tokyo   | 10.6667 |
|  4 | post-4  |     3 | Osaka   | 10.6667 |
|  5 | post-5  |     8 | Tokyo   | 10.6667 |
|  6 | post-6  |     9 | Osaka   | 10.6667 |
|  7 | post-7  |     4 | Tokyo   | 10.6667 |
|  8 | post-8  |    10 | Osaka   | 10.6667 |
|  9 | post-9  |    31 | Fukuoka | 10.6667 |
+----+---------+-------+---------+---------+

相関サブクエリ

大本のクエリと関連付けながら、実行しているサブクエリのこと

area ごとの平均も表示したかった場合には、それぞれのareaごとのレコードを
抜き出し、平均を計算する

SELECT 
  *, 
  (SELECT AVG(likes) FROM posts) AS avg,
  (SELECT AVG(likes) FROM posts AS t2 WHERE t1.area = t2.area) AS area_avg
FROM 
  posts AS t1;

  • 大元のテーブルを別名t1とする

  • likes全体の平均のカラムをavg

  • WHERE 条件で全体の area と、今処理しているレコードのテーブルを
    t2とし、 area が一緒のものを抽出し、そのカラムをarea_avgとする

  • t1からこの２つのクエリを抽出する

+----+---------+-------+---------+---------+----------+
| id | message | likes | area    | avg     | area_avg |
+----+---------+-------+---------+---------+----------+
|  1 | post-1  |    12 | Tokyo   | 10.6667 |   8.7500 |
|  2 | post-2  |     8 | Fukuoka | 10.6667 |  19.5000 |
|  3 | post-3  |    11 | Tokyo   | 10.6667 |   8.7500 |
|  4 | post-4  |     3 | Osaka   | 10.6667 |   7.3333 |
|  5 | post-5  |     8 | Tokyo   | 10.6667 |   8.7500 |
|  6 | post-6  |     9 | Osaka   | 10.6667 |   7.3333 |
|  7 | post-7  |     4 | Tokyo   | 10.6667 |   8.7500 |
|  8 | post-8  |    10 | Osaka   | 10.6667 |   7.3333 |
|  9 | post-9  |    31 | Fukuoka | 10.6667 |  19.5000 |
+----+---------+-------+---------+---------+----------+

抽出条件にサブクエリを使う

SELECT
  *
FROM
  posts
WHERE
  likes = (SELECT MAX(likes) FROM posts);

WHEREを抽出条件として使う
ここでは1 番大きな likes を持つレコードを抽出している

likes が全体の最大値と同じものという設定をしている

+----+---------+-------+---------+
| id | message | likes | area    |
+----+---------+-------+---------+
|  9 | post-9  |    31 | Fukuoka |
+----+---------+-------+---------+

抽出元にサブクエリを使う

SELECT area, COUNT(*) AS n FROM posts GROUP BY area;

+---------+---+
| area    | n |
+---------+---+
| Fukuoka | 2 |
| Osaka   | 3 |
| Tokyo   | 4 |
+---------+---+

ここで抽出したデータをもとに新しいデータを抽出する

SELECT 
  AVG(n) 
FROM 
  (SELECT area, COUNT(*) AS n FROM posts GROUP BY area) AS t;

さっき出したデータをサブクエリとして使い別名tとしている

+--------+
| AVG(n) |
+--------+
| 3.0000 |
+--------+

ウィンドウ関数

テーブルを パーティション(PARTITION) と呼ばれる単位で集計して
その結果を各レコードの横に追加できる

  • OVER()
SELECT 
  *, 
  AVG(likes) OVER () AS avg
FROM 
  posts;

+----+---------+-------+---------+---------+
| id | message | likes | area    | avg     |
+----+---------+-------+---------+---------+
|  4 | post-4  |     3 | Osaka   | 10.6667 |
|  7 | post-7  |     4 | Tokyo   | 10.6667 |
|  5 | post-5  |     8 | Tokyo   | 10.6667 |
|  2 | post-2  |     8 | Fukuoka | 10.6667 |
|  6 | post-6  |     9 | Osaka   | 10.6667 |
|  8 | post-8  |    10 | Osaka   | 10.6667 |
|  3 | post-3  |    11 | Tokyo   | 10.6667 |
|  1 | post-1  |    12 | Tokyo   | 10.6667 |
|  9 | post-9  |    31 | Fukuoka | 10.6667 |
+----+---------+-------+---------+---------+

postsテーブル全体をパーティションとして見て、全体の平均を横に出している
全体をひとつのパーティションとしているのでOVER()にして、ただの丸かっこでいい

  • PARTITION BY
SELECT 
  *, 
  AVG(likes) OVER () AS avg,
  AVG(likes) OVER (PARTITION BY area) AS area_avg,
  SUM(likes) OVER (PARTITION BY area) AS area_sum
FROM 
  posts

OVER のなかでパーティションを指定することでarea ごとの平均、合計を出している

+----+---------+-------+---------+---------+----------+----------+
| id | message | likes | area    | avg     | area_avg | area_sum |
+----+---------+-------+---------+---------+----------+----------+
|  2 | post-2  |     8 | Fukuoka | 10.6667 |  19.5000 |       39 |
|  9 | post-9  |    31 | Fukuoka | 10.6667 |  19.5000 |       39 |
|  6 | post-6  |     9 | Osaka   | 10.6667 |   7.3333 |       22 |
|  8 | post-8  |    10 | Osaka   | 10.6667 |   7.3333 |       22 |
|  4 | post-4  |     3 | Osaka   | 10.6667 |   7.3333 |       22 |
|  3 | post-3  |    11 | Tokyo   | 10.6667 |   8.7500 |       35 |
|  5 | post-5  |     8 | Tokyo   | 10.6667 |   8.7500 |       35 |
|  1 | post-1  |    12 | Tokyo   | 10.6667 |   8.7500 |       35 |
|  7 | post-7  |     4 | Tokyo   | 10.6667 |   8.7500 |       35 |
+----+---------+-------+---------+---------+----------+----------+
  • postsテーブル全体をパーティションとして見た平均
  • areaごとに区切った平均
  • areaごとに区切った合計

を抽出している

また、

SELECT 
  *, 
  AVG(likes) OVER () AS avg,
  AVG(likes) OVER w AS area_avg,
  SUM(likes) OVER w AS area_sum
FROM
  posts
WINDOW w AS (PARTITION BY area);

このようにWINDOWを使って(PARTITION BY area)をwと別名にすることで、
２行を短く書くこともできる

FRAMEの設定

フレームを使って累計を集計する

SELECT 
  *, 
  SUM(likes) OVER (
    PARTITION BY area 
    ORDER BY likes
    ) AS area_sum
FROM 
  posts;

areaごとに、小さい順に並べて、

+----+---------+-------+---------+----------+
| id | message | likes | area    | area_sum |
+----+---------+-------+---------+----------+
|  2 | post-2  |     8 | Fukuoka |        8 |
|  9 | post-9  |    31 | Fukuoka |       39 |
|  4 | post-4  |     3 | Osaka   |        3 |
|  6 | post-6  |     9 | Osaka   |       12 |
|  8 | post-8  |    10 | Osaka   |       22 |
|  7 | post-7  |     4 | Tokyo   |        4 |
|  5 | post-5  |     8 | Tokyo   |       12 |
|  3 | post-3  |    11 | Tokyo   |       23 |
|  1 | post-1  |    12 | Tokyo   |       35 |
+----+---------+-------+---------+----------+

上から、
最初のレコードのフレームはこの一行なので、合計は 8
２行目のレコードのフレームはパーティションの先頭からそのレコードまでなので、 8 + 31 で 39
次のレコードではパーティションが変わるので、パーティションの先頭からの合計で 3

...というようになっている

パーティションの中で前後一行をフレームにしたい場合は、

SELECT 
  *, 
  SUM(likes) OVER (
    PARTITION BY area 
    ORDER BY likes
    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
  ) AS area_sum
FROM 
  posts;

ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING を入れると

+----+---------+-------+---------+----------+
| id | message | likes | area    | area_sum |
+----+---------+-------+---------+----------+
|  2 | post-2  |     8 | Fukuoka |       39 |
|  9 | post-9  |    31 | Fukuoka |       39 |
|  4 | post-4  |     3 | Osaka   |       12 |
|  6 | post-6  |     9 | Osaka   |       22 |
|  8 | post-8  |    10 | Osaka   |       19 |
|  7 | post-7  |     4 | Tokyo   |       12 |
|  5 | post-5  |     8 | Tokyo   |       23 |
|  3 | post-3  |    11 | Tokyo   |       31 |
|  1 | post-1  |    12 | Tokyo   |       23 |
+----+---------+-------+---------+----------+

例えばTokyo の例を見ると、１行目はパーティションの先頭なので前の１行はないが、
次の行との合計で 12になっている
次のレコードのフレームは前後１行ずつなので、 4 と 11 と 8 を足し 23 になっている

OVER を使った時だけ使える関数

ROW_NUMBER()

likes の小さい順に並び替えたあとにその連番が欲しい場合

SELECT
  *,
  ROW_NUMBER() OVER (ORDER BY likes) AS num,
FROM
  posts;

今回パーティションは全体を対象にしているので、パーティションを省略して ORDER BY だけになっている

+----+---------+-------+---------+-----+
| id | message | likes | area    | num |
+----+---------+-------+---------+-----+
|  4 | post-4  |     3 | Osaka   |   1 |
|  7 | post-7  |     4 | Tokyo   |   2 |
|  5 | post-5  |     8 | Tokyo   |   3 |
|  2 | post-2  |     8 | Fukuoka |   4 |
|  6 | post-6  |     9 | Osaka   |   5 |
|  8 | post-8  |    10 | Osaka   |   6 |
|  3 | post-3  |    11 | Tokyo   |   7 |
|  1 | post-1  |    12 | Tokyo   |   8 |
|  9 | post-9  |    31 | Fukuoka |   9 |
+----+---------+-------+---------+-----+

RANK()とDENSE_RANK()

連番ではなくて、順位が知りたい場合にはこれらを使う

DROP TABLE IF EXISTS posts;
CREATE TABLE posts (
  id INT NOT NULL AUTO_INCREMENT,
  message VARCHAR(140), 
  likes INT,
  area VARCHAR(20),
  PRIMARY KEY (id)
);

INSERT INTO posts (message, likes, area) VALUES 
  ('post-1', 12, 'Tokyo'),
  ('post-2', 8, 'Fukuoka'),
  ('post-3', 11, 'Tokyo'),
  ('post-4', 3, 'Osaka'),
  ('post-5', 8, 'Tokyo'),
  ('post-6', 9, 'Osaka'),
  ('post-7', 4, 'Tokyo'),
  ('post-8', 10, 'Osaka'),
  ('post-9', 31, 'Fukuoka');

SELECT
  *,
  ROW_NUMBER() OVER (ORDER BY likes) AS num,
  RANK() OVER (ORDER BY likes) AS rank,
  DENSE_RANK() OVER (ORDER BY likes) AS dense
FROM
  posts;

これで、RANKとDENSEそれぞれの順位が出る

  • RANK() の場合はその次の順位が飛ばされている
  • DENSE_RANK() の場合は次の順位が飛ばされない
+----+---------+-------+---------+-----+------+-------+
| id | message | likes | area    | num | rank | dense |
+----+---------+-------+---------+-----+------+-------+
|  4 | post-4  |     3 | Osaka   |   1 |    1 |     1 |
|  7 | post-7  |     4 | Tokyo   |   2 |    2 |     2 |
|  5 | post-5  |     8 | Tokyo   |   3 |    3 |     3 |
|  2 | post-2  |     8 | Fukuoka |   4 |    3 |     3 |
|  6 | post-6  |     9 | Osaka   |   5 |    5 |     4 |
|  8 | post-8  |    10 | Osaka   |   6 |    6 |     5 |
|  3 | post-3  |    11 | Tokyo   |   7 |    7 |     6 |
|  1 | post-1  |    12 | Tokyo   |   8 |    8 |     7 |
|  9 | post-9  |    31 | Fukuoka |   9 |    9 |     8 |
+----+---------+-------+---------+-----+------+-------+

n 個前、 n 個後のレコードの値を求める

LAG()とLEAD()

SELECT
  *,
  -- LAG(likes, 1) OVER (ORDER BY likes) AS lag,
  -- LEAD(likes, 1) OVER (ORDER BY likes) AS lead
  LAG(likes) OVER (ORDER BY likes) AS lag,
  LEAD(likes) OVER (ORDER BY likes) AS lead  
FROM
  posts;
  • LAG()は1 個前のレコードの likes の値を求めている
  • LEAD()は1 個後のレコードの likes の値を求めている

コメントの部分はそれぞれ下２行と同じ意味になっている

+----+---------+-------+---------+------+------+
| id | message | likes | area    | lag  | lead |
+----+---------+-------+---------+------+------+
|  4 | post-4  |     3 | Osaka   | NULL |    4 |
|  7 | post-7  |     4 | Tokyo   |    3 |    8 |
|  5 | post-5  |     8 | Tokyo   |    4 |    8 |
|  2 | post-2  |     8 | Fukuoka |    8 |    9 |
|  6 | post-6  |     9 | Osaka   |    8 |   10 |
|  8 | post-8  |    10 | Osaka   |    9 |   11 |
|  3 | post-3  |    11 | Tokyo   |   10 |   12 |
|  1 | post-1  |    12 | Tokyo   |   11 |   31 |
|  9 | post-9  |    31 | Fukuoka |   12 | NULL |
+----+---------+-------+---------+------+------+

前のレコードからの差分を表現する

likes から 1 つ前の likes を引いてあげれば差分になるので、

SELECT
  *,
  likes - LAG(likes) OVER (ORDER BY likes) AS diff
FROM
  posts;

と書くと、

+----+---------+-------+---------+------+
| id | message | likes | area    | diff |
+----+---------+-------+---------+------+
|  4 | post-4  |     3 | Osaka   | NULL |
|  7 | post-7  |     4 | Tokyo   |    1 |
|  5 | post-5  |     8 | Tokyo   |    4 |
|  2 | post-2  |     8 | Fukuoka |    0 |
|  6 | post-6  |     9 | Osaka   |    1 |
|  8 | post-8  |    10 | Osaka   |    1 |
|  3 | post-3  |    11 | Tokyo   |    1 |
|  1 | post-1  |    12 | Tokyo   |    1 |
|  9 | post-9  |    31 | Fukuoka |   19 |
+----+---------+-------+---------+------+

トランザクション

トランザクションとは、一連の処理中に別の人にデータベースを処理されて、
整合性が取れなくならないようにすること

START TRANSACTION;
UPDATE posts SET likes = likes - 1 WHERE id = 1;
UPDATE posts SET likes = likes + 1 WHERE id = 2;
COMMIT;
SELECT * FROM posts;

START TRANSACTIONとCOMMITで処理を囲むことで、処理中に自分以外に触られないようにしている

ROLLBACK

途中でなんらかの障害が起きた場合にはCOMMITではなくROLLBACKを使い、処理をなかったことにできる

START TRANSACTION;
UPDATE posts SET likes = likes - 1 WHERE id = 1;
ROLLBACK;

SELECT * FROM posts;

テーブルを分割

複数のテーブルを用意して、

DROP TABLE IF EXISTS posts;
CREATE TABLE posts (
  id INT NOT NULL AUTO_INCREMENT,
  message VARCHAR(140), 
  PRIMARY KEY (id)
);

DROP TABLE IF EXISTS comments;
CREATE TABLE comments (
  id INT NOT NULL AUTO_INCREMENT,
  post_id INT,
  comment VARCHAR(140), 
  PRIMARY KEY (id)
);

INSERT INTO posts (message) VALUES 
  ('post-1'),
  ('post-2'),
  ('post-3');

INSERT INTO comments (post_id, comment) VALUES 
  (1, 'comment-1-1'),
  (1, 'comment-1-2'),
  (3, 'comment-3-1'),
  (4, 'comment-4-1');

SELECT * FROM posts;
SELECT * FROM comments;

+----+---------+
| id | message |
+----+---------+
|  1 | post-1  |
|  2 | post-2  |
|  3 | post-3  |
+----+---------+
+----+---------+-------------+
| id | post_id | comment     |
+----+---------+-------------+
|  1 |       1 | comment-1-1 |
|  2 |       1 | comment-1-2 |
|  3 |       3 | comment-3-1 |
|  4 |       4 | comment-4-1 |
+----+---------+-------------+
  • idは自動で振られている
  • postsテーブルのidとcommentが紐づけられている

内部結合と外部結合について

内部結合

SELECT 
  *
FROM 
  -- posts INNER JOIN comments ON posts.id = comments.post_id;
  posts JOIN comments ON posts.id = comments.post_id;

INNNER JOINで内部結合している
コメントの部分は同じ意味である

comment がついている投稿だけが抽出できる

+----+---------+----+---------+-------------+
| id | message | id | post_id | comment     |
+----+---------+----+---------+-------------+
|  1 | post-1  |  1 |       1 | comment-1-1 |
|  1 | post-1  |  2 |       1 | comment-1-2 |
|  3 | post-3  |  3 |       3 | comment-3-1 |
+----+---------+----+---------+-------------+
  • 特定のカラムだけを抽出したい場合
SELECT 
  posts.id, message, comment 
FROM 
  -- posts INNER JOIN comments ON posts.id = comments.post_id;
  posts JOIN comments ON posts.id = comments.post_id;

posts の id と posts の message と comments の comment だけ抽出する
コメント部分は同じ意味

+----+---------+-------------+
| id | message | comment     |
+----+---------+-------------+
|  1 | post-1  | comment-1-1 |
|  1 | post-1  | comment-1-2 |
|  3 | post-3  | comment-3-1 |
+----+---------+-------------+

外部結合

SELECT 
  *
FROM 
  posts JOIN comments ON posts.id = comments.post_id;

SELECT 
  *
FROM 
  -- posts LEFT OUTER JOIN comments ON posts.id = comments.post_id;
  posts LEFT JOIN comments ON posts.id = comments.post_id;

SELECT 
  *
FROM 
  posts RIGHT JOIN comments ON posts.id = comments.post_id;

+----+---------+----+---------+-------------+
| id | message | id | post_id | comment     |
+----+---------+----+---------+-------------+
|  1 | post-1  |  1 |       1 | comment-1-1 |
|  1 | post-1  |  2 |       1 | comment-1-2 |
|  3 | post-3  |  3 |       3 | comment-3-1 |
+----+---------+----+---------+-------------+
+----+---------+------+---------+-------------+
| id | message | id   | post_id | comment     |
+----+---------+------+---------+-------------+
|  1 | post-1  |    1 |       1 | comment-1-1 |
|  1 | post-1  |    2 |       1 | comment-1-2 |
|  3 | post-3  |    3 |       3 | comment-3-1 |
|  2 | post-2  | NULL |    NULL | NULL        |
+----+---------+------+---------+-------------+
+------+---------+----+---------+-------------+
| id   | message | id | post_id | comment     |
+------+---------+----+---------+-------------+
|    1 | post-1  |  1 |       1 | comment-1-1 |
|    1 | post-1  |  2 |       1 | comment-1-2 |
|    3 | post-3  |  3 |       3 | comment-3-1 |
| NULL | NULL    |  4 |       4 | comment-4-1 |
+------+---------+----+---------+-------------+

上から、

  • 内部結合で、投稿もコメントもあるものだけが抽出されている
  • 左外部結合で、 posts から全てレコードが抽出され、それに関連する comment があれば取得し、なければ NULL で埋めてくれる
  • 右外部結合で、まず comments から全てのレコードが抽出され、それに関連する投稿があれば 取得して、なければ NULL で埋めてくれる

外部キー制約

変なデータが入り込まないようにするための外部キー制約

comments テーブルの post_id に対して、 posts テーブルの id にその値が
存在しなかったら弾いてくれるように設定する

DROP TABLE IF EXISTS comments;
DROP TABLE IF EXISTS posts;

CREATE TABLE posts (
  id INT NOT NULL AUTO_INCREMENT,
  message VARCHAR(140), 
  PRIMARY KEY (id)
);

CREATE TABLE comments (
  id INT NOT NULL AUTO_INCREMENT,
  post_id INT,
  comment VARCHAR(140), 
  PRIMARY KEY (id),
  FOREIGN KEY (post_id) REFERENCES posts(id)
);

INSERT INTO posts (message) VALUES 
  ('post-1'),
  ('post-2'),
  ('post-3');

INSERT INTO comments (post_id, comment) VALUES 
  (1, 'comment-1-1'),
  (1, 'comment-1-2'),
  (3, 'comment-3-1');
  -- (4, 'comment-4-1');

SELECT * FROM posts;
SELECT * FROM comments;

FOREIGN KEY (post_id) REFERENCES posts(id)

この部分は、FOREIGN KEY としてあげて、 post_id に対して posts テーブルの id を参照して、
そこに値がなければ弾くという意味
ここで外部キーが設定されたが、ここで紐付けた posts テーブルのほうにも制限がかかる

DROP TABLE IF EXISTS posts;
...

DROP TABLE IF EXISTS comments;
...

いつもはテーブルごとにDROPを設定しているが、これだと
posts テーブルが先に削除されるが、 posts テーブルは今 comments テーブルに紐づいているので、
このまま削除すると comments テーブルのデータが宙ぶらりんになって整合性が取れなくなる

なのでposts テーブルを削除する前に外部キーの設定がされている comments テーブルを先に
削除することで、それを防ぐ

+----+---------+
| id | message |
+----+---------+
|  1 | post-1  |
|  2 | post-2  |
|  3 | post-3  |
+----+---------+
+----+---------+-------------+
| id | post_id | comment     |
+----+---------+-------------+
|  1 |       1 | comment-1-1 |
|  2 |       1 | comment-1-2 |
|  3 |       3 | comment-3-1 |
+----+---------+-------------+

データの整合性を保つ

ON DELETE CASCADE

posts のほうでレコードが削除されたら、合わせて comments のほうでも紐づくレコードが
削除されるように設定

ON UPDATE CASCADE

posts のデータが更新されると、commentsでも紐づくデータを更新されるように設定

INSERT INTO posts (message) VALUES 
  ('post-1'),
  ('post-2'),
  ('post-3');

INSERT INTO comments (post_id, comment) VALUES 
  (1, 'comment-1-1'),
  (1, 'comment-1-2'),
  (3, 'comment-3-1');

DELETE FROM posts WHERE id = 3;
UPDATE posts SET id = 100 WHERE id = 1;

SELECT * FROM posts;
SELECT * FROM comments;

+-----+---------+
| id  | message |
+-----+---------+
|   2 | post-2  |
| 100 | post-1  |
+-----+---------+
+----+---------+-------------+
| id | post_id | comment     |
+----+---------+-------------+
|  1 |     100 | comment-1-1 |
|  2 |     100 | comment-1-2 |
+----+---------+-------------+

LAST_INSERT_ID()

LAST_INSERT_ID()

直前に挿入されたレコードの id を調べる命令

INSERT INTO posts (message) VALUES 
  ('new post!');
INSERT INTO comments (post_id, comment) VALUES 
  (LAST_INSERT_ID(), 'new comment');

SELECT * FROM posts;
SELECT * FROM comments;

１番上でpostsテーブルに新たに投稿を追加し、commentsテーブルのpost_idに番号を入れるが、
LAST_INSERT_ID()は自動で直前の番号を調べて新たな数字を入れてくれる

+----+-----------+
| id | message   |
+----+-----------+
|  1 | post-1    |
|  2 | post-2    |
|  3 | post-3    |
|  4 | new post! |
+----+-----------+
+----+---------+-------------+
| id | post_id | comment     |
+----+---------+-------------+
|  1 |       1 | comment-1-1 |
|  2 |       1 | comment-1-2 |
|  3 |       3 | comment-3-1 |
|  4 |       4 | new comment |
+----+---------+-------------+

コメントにコメントをつける

parent_id

コメントにコメントをつけるには、comment がどの親を comment に持つかをデータ管理する

CREATE TABLE comments (
  id INT NOT NULL AUTO_INCREMENT,
  post_id INT,
  comment VARCHAR(140), 
  parent_id INT,
  PRIMARY KEY (id),
  FOREIGN KEY (post_id) REFERENCES posts(id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

INSERT INTO posts (message) VALUES 
  ('post-1'),
  ('post-2'),
  ('post-3');

INSERT INTO comments (post_id, comment, parent_id) VALUES 
  (1, 'comment-1-1', NULL),
  (1, 'comment-1-2', NULL),
  (3, 'comment-3-1', NULL),
  (1, 'comment-1-2-1', 2),
  (1, 'comment-1-2-2', 2),
  (1, 'comment-1-2-1-1', 4);

/*
post-1
  comment-1-1
  comment-1-2
    comment-1-2-1
      comment-1-2-1-1
    comment-1-2-2
post-2
post-3
  comment-3-1
*/

SELECT * FROM posts;
SELECT * FROM comments;

+----+---------+
| id | message |
+----+---------+
|  1 | post-1  |
|  2 | post-2  |
|  3 | post-3  |
+----+---------+
+----+---------+-----------------+-----------+
| id | post_id | comment         | parent_id |
+----+---------+-----------------+-----------+
|  1 |       1 | comment-1-1     |      NULL |
|  2 |       1 | comment-1-2     |      NULL |
|  3 |       3 | comment-3-1     |      NULL |
|  4 |       1 | comment-1-2-1   |         2 |
|  5 |       1 | comment-1-2-2   |         2 |
|  6 |       1 | comment-1-2-1-1 |         4 |
+----+---------+-----------------+-----------+

コメントのコメントを抽出

SELECT * FROM comments WHERE parent_id = 2
UNION ALL
SELECT
  comments.*
FROM
  comments JOIN (
    SELECT * FROM comments WHERE parent_id = 2
  ) AS t
ON
  comments.parent_id = t.id;

上から、
2 番目の comment に付いているすべての comment を抽出している
まず 2 番目の comment を親に持つ comment を抽出すればいい
その後に、次の階層の comment を取得したい場合、
こちらの comments テーブルから parent_id がここで抽出した id と
一致するものを抽出
つまり、comments テーブルとこの結果を comments テーブルの parent_id と
この結果の id で内部結合

+----+---------+
| id | message |
+----+---------+
|  1 | post-1  |
|  2 | post-2  |
|  3 | post-3  |
+----+---------+
+----+---------+-----------------+-----------+
| id | post_id | comment         | parent_id |
+----+---------+-----------------+-----------+
|  1 |       1 | comment-1-1     |      NULL |
|  2 |       1 | comment-1-2     |      NULL |
|  3 |       3 | comment-3-1     |      NULL |
|  4 |       1 | comment-1-2-1   |         2 |
|  5 |       1 | comment-1-2-2   |         2 |
|  6 |       1 | comment-1-2-1-1 |         4 |
+----+---------+-----------------+-----------+
+----+---------+-----------------+-----------+
| id | post_id | comment         | parent_id |
+----+---------+-----------------+-----------+
|  4 |       1 | comment-1-2-1   |         2 |
|  5 |       1 | comment-1-2-2   |         2 |
|  6 |       1 | comment-1-2-1-1 |         4 |
+----+---------+-----------------+-----------+

CTE(Common Table Expression)

クエリ内で使える一時的なテーブル

  • 再帰的なCTE
    階層が深くなっていくデータを処理するための方法

  • 再帰的でないCTE
    サブクエリを分かりやすく書き換えるためのもの

再帰的でないCTEを使って前回のコメントのコメント抽出を行うと、

WITH t AS (
  SELECT * FROM comments WHERE parent_id = 2
)
SELECT
  comments.*
FROM
  comments JOIN t
ON
  comments.parent_id = t.id;

WITHから最後の行までが１つのクエリである

+----+---------+-----------------+-----------+
| id | post_id | comment         | parent_id |
+----+---------+-----------------+-----------+
|  6 |       1 | comment-1-2-1-1 |         4 |
+----+---------+-----------------+-----------+

再帰的なCTE

WITH RECURSIVE ... AS ...

WITH RECURSIVE t AS (
  -- n = 1
  SELECT * FROM comments WHERE parent_id = 2
  UNION ALL
  -- n >= 2
  SELECT
    comments.*
  FROM
    comments JOIN t
  ON
    comments.parent_id = t.id
)
SELECT * FROM t;

2 番目のコメントに付いた全てのコメントが一気に取得できる
再帰的なCTEは、処理が終わったらその結果を t にして、結果が
無くなるまでこちらの処理を再起的に実行してくれるので、どれだけ階層が
深くなっても、コメントを取得してくれる

+------+---------+-----------------+-----------+
| id   | post_id | comment         | parent_id |
+------+---------+-----------------+-----------+
|    4 |       1 | comment-1-2-1   |         2 |
|    5 |       1 | comment-1-2-2   |         2 |
|    6 |       1 | comment-1-2-1-1 |         4 |
+------+---------+-----------------+-----------+

TRIGGERを設定

あるテーブルで何らかの変更が起きた時に、それをトリガーにして何らかの
処理をすることができる仕組み

CREATE TRIGGER

DROP TABLE IF EXISTS comments;
DROP TABLE IF EXISTS posts;
DROP TABLE IF EXISTS logs;
DROP TRIGGER IF EXISTS posts_update_trigger;

CREATE TABLE posts (
  id INT NOT NULL AUTO_INCREMENT,
  message VARCHAR(140),
  PRIMARY KEY (id)
);

CREATE TABLE logs (
  id INT NOT NULL AUTO_INCREMENT,
  message VARCHAR(140),
  created DATETIME DEFAULT NOW(),
  PRIMARY KEY (id)
);

CREATE TRIGGER
  posts_update_trigger
AFTER UPDATE ON
  posts
FOR EACH ROW
  INSERT INTO
    logs (message)
  VALUES
    ('Updated');

INSERT INTO posts (message) VALUES
  ('post-1'),
  ('post-2'),
  ('post-3');

SELECT * FROM posts;

logs テーブルで、posts テーブルが更新されたら、ログが残るようにしておく
更新されたひとつひとつの行に対して logs テーブルにデータを挿入していく
DEFAULT で挿入された日時にしておく

AFTER UPDATE ON としてあげて posts が更新されたあとにトリガーが動く

トリガーはアップデートだけに使えるというわけではなく、 INSERT や DELETE のタイミングでも使えるし、
AFTER ではなくて BEFORE とすれば処理前にトリガーを実行することができる

１番上はDROP TRIGGER 文を入れる

実際にトリガーを使ってみる

CREATE TRIGGER
  posts_update_trigger
AFTER UPDATE ON
  posts
FOR EACH ROW
  INSERT INTO
    logs (message)
  VALUES
    (CONCAT(OLD.message, ' -> ', NEW.message));

INSERT INTO posts (message) VALUES
  ('post-1'),
  ('post-2'),
  ('post-3');
UPDATE posts SET message = 'post-1 updated' WHERE id = 1;

SELECT * FROM posts;
SELECT * FROM logs;

-- SHOW TRIGGERS;
SHOW TRIGGERS\G

CONCAT 関数を使いログに更新前、更新後の値を含める
するとmessage の更新前、更新後の値を挿入してくれる

SHOW TRIGGERSで設定されているトリガーの一覧を見れるが、見づらいので
\Gを最後につけると、整頓されて見える

+----+----------------+
| id | message        |
+----+----------------+
|  1 | post-1 updated |
|  2 | post-2         |
|  3 | post-3         |
+----+----------------+
+----+--------------------------+---------------------+
| id | message                  | created             |
+----+--------------------------+---------------------+
|  1 | post-1 -> post-1 updated | 2021-11-18 21:14:40 |
+----+--------------------------+---------------------+
*************************** 1. row ***************************
             Trigger: posts_update_trigger
               Event: UPDATE
               Table: posts
           Statement: INSERT INTO
    logs (message)
  VALUES

    (CONCAT(OLD.message, ' -> ', NEW.message))
              Timing: AFTER
             Created: 2021-11-18 21:14:40.18
            sql_mode: STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
             Definer: dbuser@%
character_set_client: utf8mb4
collation_connection: utf8mb4_general_ci
  Database Collation: utf8mb4_general_ci

長い文章が一覧

外部ファイルからデータを読みこむ

data.csvが別にあるとして、

DROP TABLE IF EXISTS posts;

CREATE TABLE posts (
  id INT NOT NULL AUTO_INCREMENT,
  message VARCHAR(140), 
  likes INT,
  area VARCHAR(20),
  PRIMARY KEY (id)
);

LOAD DATA LOCAL INFILE 'data.csv' INTO TABLE posts
  FIELDS TERMINATED BY ','
  LINES TERMINATED BY '\n'
  IGNORE 1 LINES
  (message, likes, area);

SELECT * FROM posts;

  • LOAD DATA LOCAL INFILE
    同じサーバにある外部ファイルを読み込む

  • INTO TABLE
    どのテーブルに流し込むかを指定

  • FIELDS TERMINATED BY
    項目の区切りを指定

  • LINES TERMINATED BY
    行の区切りを指定

クエリ分析

DROP TABLE IF EXISTS posts;

CREATE TABLE posts (
  id INT NOT NULL AUTO_INCREMENT,
  message VARCHAR(140), 
  likes INT,
  area VARCHAR(20),
  PRIMARY KEY (id)
);

LOAD DATA LOCAL INFILE 'data.csv' INTO TABLE posts
  FIELDS TERMINATED BY ','
  LINES TERMINATED BY '\n'
  IGNORE 1 LINES
  (message, likes, area);

SHOW INDEX FROM posts\G

主キーには自動的にインデックスが作られているので、
SHOW INDEX FROM posts\Gで確認すると

*************************** 1. row ***************************
        Table: posts
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 66
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment:

id のフィールドに PRIMARY というインデックスが設定されているのでOK

実際に id を使った検索で、このインデックスが使われているかどうか、
確認する場合には、１番最後に

EXPLAIN SELECT * FROM posts WHERE id = 30\G

これを書いてあげると

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: posts
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 1
        Extra:

見るべき項目は key と rows で、 key はクエリで実際に使われたインデックスの名前、
rows は検索対象となるレコード数の見積もり

今回は PRIMARY キーが使われ、 rows は一行だけなので高速に動作しているがわかる

インデックスを設定

インデックスが設定されていないメディアを使ったクエリについて調べてみると、

LOAD DATA LOCAL INFILE 'data.csv' INTO TABLE posts
  FIELDS TERMINATED BY ','
  LINES TERMINATED BY '\n'
  IGNORE 1 LINES
  (message, likes, area);
EXPLAIN SELECT * FROM posts WHERE area = 'Kyoto'\G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: posts
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 199
        Extra: Using where

key を見ると NULL になっていて、インデックスが使われていないことがわかる
rows は 200 件になっているので、最初から最後まで見てから検索結果を返して
いるので、低速なクエリであることもわかる

ALTER TABLE ... ADD INDEX ...

インデックスを追加する

LOAD DATA LOCAL INFILE 'data.csv' INTO TABLE posts
  FIELDS TERMINATED BY ','
  LINES TERMINATED BY '\n'
  IGNORE 1 LINES
  (message, likes, area);
ALTER TABLE posts ADD INDEX index_area(area);
SHOW INDEX FROM posts\G
EXPLAIN SELECT * FROM posts WHERE area = 'Kyoto'\G

インデックスは、あとから付け外しすることが多いので ALTER TABLE を使う

*************************** 1. row ***************************
        Table: posts
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 28
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 2. row ***************************
        Table: posts
   Non_unique: 1
     Key_name: index_area
 Seq_in_index: 1
  Column_name: area
    Collation: A
  Cardinality: 28
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: posts
         type: ref
possible_keys: index_area
          key: index_area
      key_len: 83
          ref: const
         rows: 9
        Extra: Using index condition
*************************** 1. row ***************************
        Table: posts
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 28
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment:

インデックスの一覧では PRIMARY と area カラムにインデックスが
設定されていて、 EXPLAIN の結果を見ると今度は key で index_area
が使われている
rows も大幅に減っているのでこのクエリが高速に動作するということがわかる

ALTER TABLE ... DROP INDEX ...

インデックスを外す

LOAD DATA LOCAL INFILE 'data.csv' INTO TABLE posts
  FIELDS TERMINATED BY ','
  LINES TERMINATED BY '\n'
  IGNORE 1 LINES
  (message, likes, area);
ALTER TABLE posts ADD INDEX index_area(area);
ALTER TABLE posts DROP INDEX index_area;
SHOW INDEX FROM posts\G

*************************** 1. row ***************************
        Table: posts
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 28
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment:

インデックスを追加しているのが、そのあとにインデックスを外しているので
SHOW INDEX では PRIMARY KEY だけが表示される

データが増えてきてパフォーマンスが落ちてきたら EXPLAIN を使って調査しつつ、
適切にインデックスを設定すること

参考

ドットインストール　MySQL入門　応用編

SQLについて[まとめ]

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0