SQLの実行順序おさらい

 

SELECT
  列の抽出対象
FROM
  テーブル名
WHERE 行の絞り込み条件
GROUP BY hoge_colum
HAVING  100 > amount

1. Aテーブルの列と行を切り出しイメージをする
2. Bテーブルの列と行を切り出しイメージをする
3. ONで結合条件を指定
4. WHEREで行を絞りこむ
5. GROUP BYでグループ化
6. HAVINGでGROUP BYでのグループに条件で行を絞り混む

1. FROM

主となるテーブルを作成

 

2. ON

結合条件を指定する

 

3. WHERE

ONで絞りこみの結果が出てから、WHEREで行数を絞りこみが発動する

 

4. GROUP BY

WHEREで絞りこんでからGROUP BYが実行される

 

5. HAVING

GROUP BY でグループでまとめられてから実行される

 

6. SELECT

対象の列を抽出する

 

集合のおさらい

LEFT OUTER JOIN (= LEFT JOIN)

外部結合。

 

INNER JOIN

内部結合。

LEFT OUTER JOIN + WHERE カラム名 IS NOT NUL、WHERE カラム名 IS NULLを利用することで、外部結合から内部結合と同じ結果を表現できる。

 

EXISTとINの使い分け

• 主テーブルの選択度が高 +従属テーブルの 選択度が低
  →EXIST +サブクエリ
• 主テーブルの選択度が低 +従属テーブルの選択度が高
  →IN + サブクエリ
• 主テーブルの選択度が高 +従属テーブルの選択度が高
  →EXIST + サブクエリ

 

CASEでいいねフラグ作成

いいね機能をつくるよ。

• ユーザが自分で押したかどうか is_nice_flag
• 会員すべてが記事にいいねを押した総数 nice_count

これが必要

 

MySQLサーバを作る on Docker

docker-compose.yml

version: '3.3'

services:
   db:
     image: mysql:5.7
     container_name: "mysql57-test"
     volumes:
       - ./db_data:/var/lib/mysql
     restart: always
     command:
       - --sql-mode=NO_ENGINE_SUBSTITUTION
     environment:
       MYSQL_ROOT_PASSWORD: root
   pma:
     container_name: "pma-test"
     image: phpmyadmin/phpmyadmin:latest
     environment:
       PMA_HOST: db
     ports:
     - "18080:80"
     restart: always
     links:
       - db

MySQL5.7以上から『only_full_group_by』によってGROUP BYでグループ化できない。この挙動を忘れてると苦しむので対策しておきたい。

 

起動

# docker-compose up -d

 

phpMyAdminにアクセス
http://localhost:18080

 

only_full_group_by対策

docker-compose.yml

     command:
       - --sql-mode=NO_ENGINE_SUBSTITUTION

 

my.cnf

[mysqld]
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

 

テーブルの作成

test.sql

-- phpMyAdmin SQL Dump
-- version 4.9.1
-- https://www.phpmyadmin.net/
--
-- ホスト: db
-- 生成日時: 2019 年 12 月 21 日 12:07
-- サーバのバージョン： 5.7.28
-- PHP のバージョン: 7.2.22

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- データベース: `test`
--

-- --------------------------------------------------------

--
-- テーブルの構造 `nices`
--

CREATE TABLE `nices` (
  `user_id` int(11) NOT NULL,
  `post_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- テーブルのデータのダンプ `nices`
--

INSERT INTO `nices` (`user_id`, `post_id`) VALUES
(1, 1),
(1, 2),
(1, 3),
(2, 2),
(2, 3),
(3, 1),
(4, 2),
(4, 3);

-- --------------------------------------------------------

--
-- テーブルの構造 `posts`
--

CREATE TABLE `posts` (
  `post_id` int(11) NOT NULL,
  `post_name` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- テーブルのデータのダンプ `posts`
--

INSERT INTO `posts` (`post_id`, `post_name`) VALUES
(1, 'articleA'),
(2, 'articleB'),
(3, 'articleC'),
(4, 'articleD');

-- --------------------------------------------------------

--
-- テーブルの構造 `users`
--

CREATE TABLE `users` (
  `user_id` int(11) NOT NULL,
  `user_name` varchar(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- テーブルのデータのダンプ `users`
--

INSERT INTO `users` (`user_id`, `user_name`) VALUES
(1, 'yuu'),
(2, 'satou'),
(3, 'suzuki'),
(4, 'iwata');

--
-- ダンプしたテーブルのインデックス
--

--
-- テーブルのインデックス `posts`
--
ALTER TABLE `posts`
  ADD PRIMARY KEY (`post_id`);

--
-- テーブルのインデックス `users`
--
ALTER TABLE `users`
  ADD PRIMARY KEY (`user_id`);

--
-- ダンプしたテーブルのAUTO_INCREMENT
--

--
-- テーブルのAUTO_INCREMENT `posts`
--
ALTER TABLE `posts`
  MODIFY `post_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

--
-- テーブルのAUTO_INCREMENT `users`
--
ALTER TABLE `users`
  MODIFY `user_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

 

 

記事一覧表示のクエリの作成

記事の一覧を表示は当然として、

• 閲覧者がいいねを押したか？
• いいねの総数はいくつか？

この2点を踏まえてSQLを作成するよ。

SELECT
  p.post_id,
  p.post_name,
  COALESCE(is_nice_flag, 0) AS is_nice_flag,
  COALESCE(g.nice_count, 0) AS nice_count
FROM 
  posts AS p
  
  
LEFT OUTER JOIN
  (SELECT 
      post_id,
     (CASE
        WHEN u.user_id = 1 THEN 1
        ELSE 0
     END) AS is_nice_flag
   FROM
     nices AS n 
   LEFT JOIN
     users AS u 
   ON u.user_id = n.user_id
   GROUP BY post_id
  ) m
ON p.post_id = m.post_id


LEFT OUTER JOIN 
  (SELECT COUNT(*) AS nice_count,post_id 
   FROM
     nices 
   GROUP BY post_id) g
ON g.post_id = p.post_id


ORDER BY p.post_id ASC

 

 

結果

+---------+-----------+--------------+---------------------------+
| post_id | post_name | is_nice_flag | nice_count                |
+---------+-----------+--------------+---------------------------+
|       1 | articleA  |            1 |                         2 |
|       2 | articleB  |            1 |                         3 |
|       3 | articleC  |            1 |                         3 |
|       4 | articleD  |            0 |                         0 |
+---------+-----------+--------------+---------------------------+
4 rows in set (0.01 sec)

記事の一覧表示を想定しています。

 

いいね総数が0でnullになってしまうので、nullを指定した値に代入してくれるCOALESCE()を利用しています。

COALESCE(g.nice_count, 0)

 

SQLが苦手な人へ

下記の本がおすすめだぞ！😊