SQL 第2版 ゼロからはじめるデータベース操作 アウトプット（第8回）

## 概要
- 本記事は[SQL 第2版 ゼロからはじめるデータベース操作](https://www.shoeisha.co.jp/book/detail/9784798144450)で学んだ内容をまとめたものです。
  - 前回は「**第4章：データの更新**」4-4: トランザクション  で学んだ内容をまとめました：[SQL 第2版 ゼロからはじめるデータベース操作 アウトプット（第7回）](https://qiita.com/takumi_links/items/37a9b761b939559e3193)
  - 今回は「**第5章：複雑な問い合わせ**」についてまとめていきます！

### ビュー
- ビュー（View）：テーブルの内容を参照するための仮想的なテーブル
  - ビューは実際のデータを持たず、基となるテーブルのデータを参照する
  - ビューを使うことで、よく使うSQLをビューとして保存し、再利用することができる
  - `CREATE VIEW ビュー名 AS SELECT 文;`

```sql
mysql> CREATE VIEW ShohinSum (shohin_bunrui, cnt_shohin) AS SELECT shohin_bunrui, COUNT(*) FROM Shohin GROUP BY shohin_bunrui;
Query OK, 0 rows affected (0.01 sec)
mysql> SELECT * FROM ShohinSum;
+--------------------+------------+
| shohin_bunrui      | cnt_shohin |
+--------------------+------------+
| 衣服               |          2 |
| 事務用品           |          2 |
| キッチン用品       |          4 |
+--------------------+------------+
```

- ビューをFROM句で指定した時の検索は以下
  - ビューに定義されたSELECT文が実行される（ビューの定義に基づく）
  - その結果に対して、ビューをFROM句で指定した時の検索が行われる

#### 注意点
- ビューを重ねて使うと、パフォーマンスが低下する可能性がある
  - ビューを多重に使用する場合は、パフォーマンスに注意する必要がある

- ビュー定義でORDER BY句は使える
```sql
mysql> CREATE VIEW ShohinSum (shohin_bunrui, cnt_shohin) AS SELECT shohin_bunrui, COUNT(*) FROM Shohin GROUP BY shohin_bunrui ORDER BY shohin_bunrui;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM ShohinSum;
+--------------------+------------+
| shohin_bunrui      | cnt_shohin |
+--------------------+------------+
| キッチン用品       |          4 |
| 事務用品           |          2 |
| 衣服               |          2 |
+--------------------+------------+
3 rows in set (0.01 sec)
```

- ビューに対する更新（INSERT、UPDATE、DELETE）は、条件次第で可能
  - SELECT句にDISTINCTが含まれている場合、更新できない
  - FROM句に含まれているテーブルが1つだけの場合、更新できる
  - GROUP BY句が含まれている場合、更新できない
  - HAVING句が含まれている場合、更新できない

```sql
mysql> INSERT INTO ShohinSum VALUES ('test', 1);
ERROR 1471 (HY000): The target table ShohinSum of the INSERT is not insertable-into
mysql> CREATE VIEW ShohinJim (shohin_id, shohin_mei, shohin_bunrui, hanbai_tanka, shiire_tanka, torokubi) AS SELECT shohin_id, shohin_mei, shohin_bunrui, hanbai_tanka, shiire_tanka, torokubi FROM Shohin WHERE shohin_bunrui = '事務用品'
  [2025-06-07 18:17:35] 10 ms で完了しました
mysql> SELECT * FROM ShohinJim;
+-----------+--------------------+---------------+--------------+--------------+------------+
| shohin_id | shohin_mei         | shohin_bunrui | hanbai_tanka | shiire_tanka | torokubi   |
+-----------+--------------------+---------------+--------------+--------------+------------+
| 0002      | 穴あけパンチ       | 事務用品      |          500 |          320 | 2009-09-11 |
| 0008      | ボールペン         | 事務用品      |          100 |         NULL | 2009-11-11 |
+-----------+--------------------+---------------+--------------+--------------+------------+
2 rows in set (0.00 sec)
```

- ビュー経由での更新・登録
```sql
mysql> SELECT * FROM ShohinJim;
+-----------+--------------------+---------------+--------------+--------------+------------+
| shohin_id | shohin_mei         | shohin_bunrui | hanbai_tanka | shiire_tanka | torokubi   |
+-----------+--------------------+---------------+--------------+--------------+------------+
| 0002      | 穴あけパンチ       | 事務用品      |          500 |          320 | 2009-09-11 |
| 0008      | ボールペン         | 事務用品      |          100 |         NULL | 2009-11-11 |
+-----------+--------------------+---------------+--------------+--------------+------------+
2 rows in set (0.00 sec)

mysql> UPDATE ShohinJim SET hanbai_tanka = 400 WHERE shohin_id = '0002';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM Shohin WHERE shohin_id = '0002';
+-----------+--------------------+---------------+--------------+--------------+------------+
| shohin_id | shohin_mei         | shohin_bunrui | hanbai_tanka | shiire_tanka | torokubi   |
+-----------+--------------------+---------------+--------------+--------------+------------+
| 0002      | 穴あけパンチ       | 事務用品      |          400 |          320 | 2009-09-11 |
+-----------+--------------------+---------------+--------------+--------------+------------+
1 row in set (0.01 sec)

mysql> INSERT INTO ShohinJim VALUES ('0009', '印鑑','事務用品', 95, 10, '2025-06-07');
mysql> SELECT * FROM Shohin;
+-----------+-----------------------+--------------------+--------------+--------------+------------+
| shohin_id | shohin_mei            | shohin_bunrui      | hanbai_tanka | shiire_tanka | torokubi   |
+-----------+-----------------------+--------------------+--------------+--------------+------------+
| 0001      | Tシャツ               | 衣服               |         1000 |          500 | 2009-09-20 |
| 0002      | 穴あけパンチ          | 事務用品           |          400 |          320 | 2009-09-11 |
| 0003      | カッターシャツ        | 衣服               |         4000 |         2800 | NULL       |
| 0004      | 包丁                  | キッチン用品       |         3000 |         2800 | 2009-09-20 |
| 0005      | 圧力鍋                | キッチン用品       |         6800 |         5000 | 2009-01-15 |
| 0006      | フォーク              | キッチン用品       |          500 |         NULL | 2009-09-20 |
| 0007      | おろしがね            | キッチン用品       |          880 |          790 | 2008-04-28 |
| 0008      | ボールペン            | 事務用品           |          100 |         NULL | 2009-11-11 |
| 0009      | 印鑑                  | 事務用品           |           95 |           10 | 2025-06-07 |
+-----------+-----------------------+--------------------+--------------+--------------+------------+
9 rows in set (0.00 sec)
```

### サブクエリ
- サブクエリ（Subquery）：他のSQL文の中に含まれるSELECT文
  - 一言で言うと「使い捨てのビュー」
  - ビュー定義のSELECT文をそのままFROMに持ち込んでしまった

- サブクエリは**「内側から最初に実行される」**

```sql
-- ShohinSumは使い捨てのビュー
mysql> SELECT shohin_bunrui, cnt_shohin FROM (SELECT shohin_bunrui, COUNT(*) AS cnt_shohin FROM Shohin GROUP BY shohin_bunrui) AS ShohinSum;
+--------------------+------------+
| shohin_bunrui      | cnt_shohin |
+--------------------+------------+
| 衣服               |          2 |
| 事務用品           |          2 |
| キッチン用品       |          4 |
+--------------------+------------+
3 rows in set (0.00 sec)
```

- FROM句で複数のサブクエリを使うこともできる
```sql
mysql> SELECT shohin_bunrui, cnt_shohin FROM (SELECT * FROM (SELECT shohin_bunrui, COUNT(*) AS cnt_shohin FROM Shohin GROUP BY shohin_bunrui) AS ShohinSum
    -> WHERE cnt_shohin = 4) AS ShohinSum2;
+--------------------+------------+
| shohin_bunrui      | cnt_shohin |
+--------------------+------------+
| キッチン用品       |          4 |
+--------------------+------------+
1 row in set (0.00 sec)
```

#### スカラ・サブクエリ
- スカラ・サブクエリ（Scalar Subquery）：1つの値を返すサブクエリ
  - **必ず1行1列だけの戻り値を返す**サブクエリ

```sql
-- 販売単価が、全体の平均販売単価より高い商品だけを検索する
mysql> SELECT shohin_id, shohin_mei, hanbai_tanka FROM Shohin WHERE hanbai_tanka >= (SELECT AVG(hanbai_tanka) FROM Shohin);
+-----------+-----------------------+--------------+
| shohin_id | shohin_mei            | hanbai_tanka |
+-----------+-----------------------+--------------+
| 0003      | カッターシャツ        |         4000 |
| 0004      | 包丁                  |         3000 |
| 0005      | 圧力鍋                |         6800 |
+-----------+-----------------------+--------------+
3 rows in set (0.00 sec)


mysql> SELECT shohin_bunrui, AVG(hanbai_tanka) FROM Shohin GROUP BY shohin_bunrui HAVING AVG(hanbai_tanka) >  (SELECT AVG(hanbai_tanka) FROM Shohin);
+--------------------+-------------------+
| shohin_bunrui      | AVG(hanbai_tanka) |
+--------------------+-------------------+
| 衣服               |         2500.0000 |
| キッチン用品       |         2795.0000 |
+--------------------+-------------------+
2 rows in set (0.00 sec)
```

### 相関サブクエリ
- 相関サブクエリ（Correlated Subquery）：外側のクエリの値を参照するサブクエリ
  - 外側のクエリの値に依存しているため、外側のクエリが実行されるたびに内側のサブクエリが実行される
  - **外側のクエリの行数分だけ、内側のサブクエリが実行される**

```sql
mysql> select shohin_bunrui, hanbai_tanka from Shohin;
+--------------------+--------------+
| shohin_bunrui      | hanbai_tanka |
+--------------------+--------------+
| 衣服               |         1000 |
| 事務用品           |          400 |
| 衣服               |         4000 |
| キッチン用品       |         3000 |
| キッチン用品       |         6800 |
| キッチン用品       |          500 |
| キッチン用品       |          880 |
| 事務用品           |          100 |
+--------------------+--------------+
8 rows in set (0.00 sec)

-- 各商品分類ごとの平均単価
mysql> SELECT AVG(hanbai_tanka) FROM Shohin GROUP BY shohin_bunrui;
+-------------------+
| AVG(hanbai_tanka) |
+-------------------+
|         2500.0000 |
|          250.0000 |
|         2795.0000 |
+-------------------+
3 rows in set (0.00 sec)

-- 各商品ごとに、平均販売単価より高いものを検索する
-- それぞれの商品テーブルの各レコードと、それぞれの商品分類の平均販売単価を比較する
mysql> SELECT shohin_bunrui, shohin_mei, hanbai_tanka FROM Shohin AS S1 WHERE hanbai_tanka > (SELECT AVG(hanbai_tanka) FROM Shohin AS S2 WHERE S1.shohin_bunrui = S2.shohin_bunrui GROUP BY shohin_bunrui);
+--------------------+-----------------------+--------------+
| shohin_bunrui      | shohin_mei            | hanbai_tanka |
+--------------------+-----------------------+--------------+
| 事務用品           | 穴あけパンチ          |          400 |
| 衣服               | カッターシャツ        |         4000 |
| キッチン用品       | 包丁                  |         3000 |
| キッチン用品       | 圧力鍋                |         6800 |
+--------------------+-----------------------+--------------+
```


### 練習問題を解いてみる
- 5.1 - 5.4を解いてみた内容を掲載

#### 5.1
- ビューの作成

```sql
mysql> CREATE VIEW ViewRenshu5_1 AS SELECT shohin_mei, hanbai_tanka, torokubi FROM Shohin WHERE hanbai_tanka >= 1000 AND torokubi = '2009-09-20';
Query OK, 0 rows affected (0.02 sec)

mysql> SELECT * FROM ViewRenshu5_1;
+------------+--------------+------------+
| shohin_mei | hanbai_tanka | torokubi   |
+------------+--------------+------------+
| Tシャツ    |         1000 | 2009-09-20 |
| 包丁       |         3000 | 2009-09-20 |
+------------+--------------+------------+
2 rows in set (0.00 sec)
```

#### 5-2
- ビューへのデータ登録

```sql
mysql> INSERT INTO ViewRenshu5_1 VALUES ('TEST', 300, '2025-06-10');
ERROR 1423 (HY000): Field of view 'ViewRenshu5_1' underlying table doesn't have a default value
```

#### 5-3
- サブクエリの作成

```sql
-- 集約関数を使った場合は、「定数・GROUP BYで指定した列・集約関数の結果」しか使えない
mysql> SELECT shohin_id, shohin_mei, shohin_bunrui, hanbai_tanka, AVG(hanbai_tanka) AS hanbai_tanka_all FROM Shohin;
ERROR 1140 (42000): In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'Shohin.shohin_id'; this is incompatible with sql_mode=only_full_group_by

-- スカラ・サブクエリを使って、商品全体の平均販売単価を求める
mysql> SELECT shohin_id, shohin_mei, shohin_bunrui, hanbai_tanka,(SELECT AVG(hanbai_tanka) FROM Shohin) AS hanbai_tanka_all FROM Shohin;
+-----------+-----------------------+--------------------+--------------+------------------+
| shohin_id | shohin_mei            | shohin_bunrui      | hanbai_tanka | hanbai_tanka_all |
+-----------+-----------------------+--------------------+--------------+------------------+
| 0001      | Tシャツ               | 衣服               |         1000 |        2085.0000 |
| 0002      | 穴あけパンチ          | 事務用品           |          400 |        2085.0000 |
| 0003      | カッターシャツ        | 衣服               |         4000 |        2085.0000 |
| 0004      | 包丁                  | キッチン用品       |         3000 |        2085.0000 |
| 0005      | 圧力鍋                | キッチン用品       |         6800 |        2085.0000 |
| 0006      | フォーク              | キッチン用品       |          500 |        2085.0000 |
| 0007      | おろしがね            | キッチン用品       |          880 |        2085.0000 |
| 0008      | ボールペン            | 事務用品           |          100 |        2085.0000 |
+-----------+-----------------------+--------------------+--------------+------------------+
8 rows in set (0.01 sec)
```

#### 5-4
- ビューの作成（応用）

```sql
-- 商品分類ごとの平均販売単価を求めるビューを作成
-- 
mysql> CREATE VIEW AvgTankaByBunrui AS SELECT shohin_id, shohin_mei, shohin_bunrui, hanbai_tanka, (SELECT AVG(hanbai_tanka) FROM Shohin S2 WHERE S1.shohin_bunrui = S2.shohin_bunrui GROUP BY shohin_bunrui) AS avg_hanbai_tanka FR
OM Shohin S1;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM AvgTankaByBunrui;
+-----------+-----------------------+--------------------+--------------+------------------+
| shohin_id | shohin_mei            | shohin_bunrui      | hanbai_tanka | avg_hanbai_tanka |
+-----------+-----------------------+--------------------+--------------+------------------+
| 0001      | Tシャツ               | 衣服               |         1000 |        2500.0000 |
| 0002      | 穴あけパンチ          | 事務用品           |          400 |         250.0000 |
| 0003      | カッターシャツ        | 衣服               |         4000 |        2500.0000 |
| 0004      | 包丁                  | キッチン用品       |         3000 |        2795.0000 |
| 0005      | 圧力鍋                | キッチン用品       |         6800 |        2795.0000 |
| 0006      | フォーク              | キッチン用品       |          500 |        2795.0000 |
| 0007      | おろしがね            | キッチン用品       |          880 |        2795.0000 |
| 0008      | ボールペン            | 事務用品           |          100 |         250.0000 |
+-----------+-----------------------+--------------------+--------------+------------------+
8 rows in set (0.01 sec)
```

### まとめ
- 本記事では「**第5章：複雑な問い合わせ**」 についてまとめました！
- 次回は「**第6章：関数、述語、CASE式**」 についてまとめていきます！

## 参考
- [SQL 第2版 ゼロからはじめるデータベース操作](https://www.shoeisha.co.jp/book/detail/9784798144450)