MySQLについて[まとめ２]



### 集計関数

~~~
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について[まとめ]](https://qiita.com/koojiiiii/items/17ff796eb58f7590fa84)