[MySQL] MySQLでJOINを使ったVIEW(ビュー)を作成する

ITMySQL

MySQLでVIEWが使えることをつい最近知り、試しにJOINを含むVIEWを作成してみました。

[検証環境]
MySQL 8.0.19

会社の従業員に関するサンプルテーブルを作成し、JOINを使ったemployees_info_view ビューを作成します。

(先にVIEW作成のクエリをみたい方はこちら->https://nobuhiroharada.com/2020/02/09/how-to-make-view-schema-by-mysql/#CREATE_VIEW)

サンプルテーブル一覧

  • employees テーブル
  • prefectures テーブル
  • regions テーブル
  • divisions テーブル

employees テーブル

mysql> SELECT * FROM employees;
+----+---------------+-------------+-----------+------------+-----------+----------+-----------------------------------------------------------------------------------+---------------+-----------------------------+---------------------+---------------------+
| id | prefecture_id | division_id | gender_id | first_name | last_name | postcode | address                                                                           | phone_number  | email                       | created_at          | updated_at          |
+----+---------------+-------------+-----------+------------+-----------+----------+-----------------------------------------------------------------------------------+---------------+-----------------------------+---------------------+---------------------+
|  1 |            18 |           4 |         1 | 直子       | 三宅      | 4478225  | 8886074  栃木県渡辺市北区加納町近藤10-1-4                                         | 04542-1-2160  | kazuya.uno@hotmail.co.jp    | 2020-02-09 11:43:25 | 2020-02-09 11:43:25 |
|  2 |            28 |           4 |         2 | 零         | 原田      | 8007374  | 7906362  京都府喜嶋市北区木村町桐山7-7-1                                          | 080-6964-1220 | soutaro51@miyazawa.com      | 2020-02-09 11:43:25 | 2020-02-09 11:43:25 |
|  3 |            27 |           4 |         3 | 香織       | 青山      | 2786634  | 6837263  茨城県吉田市南区田中町渡辺1-5-6 ハイツ宇野110号                          | 080-6748-7399 | xkoizumi@kudo.biz           | 2020-02-09 11:43:25 | 2020-02-09 11:43:25 |
|  4 |             7 |           1 |         2 | 直子       | 山岸      | 4016422  | 6436776  栃木県江古田市西区宮沢町渡辺10-10-10 ハイツ高橋109号                     | 0862-99-3096  | kanou.taro@hotmail.co.jp    | 2020-02-09 11:43:25 | 2020-02-09 11:43:25 |
|  5 |            32 |           4 |         2 | 真綾       | 笹田      | 1954652  | 9285446  愛媛県野村市西区中津川町原田6-10-7 ハイツ宮沢104号                       | 090-7308-5876 | satomi.hirokawa@yahoo.co.jp | 2020-02-09 11:43:25 | 2020-02-09 11:43:25 |
|  6 |            28 |           2 |         1 | 充         | 三宅      | 2573963  | 4571631  兵庫県近藤市中央区小泉町笹田5-3-7 ハイツ浜田110号                        | 090-6127-9971 | gyoshimoto@sakamoto.biz     | 2020-02-09 11:43:25 | 2020-02-09 11:43:25 |
|  7 |            34 |           5 |         2 | 晃         | 吉本      | 9791973  | 7783228  北海道田辺市東区大垣町吉本4-6-8                                          | 0080-28-1985  | yoshimoto.miki@yahoo.co.jp  | 2020-02-09 11:43:25 | 2020-02-09 11:43:25 |
|  8 |            18 |           1 |         2 | 春香       | 廣川      | 8144775  | 9676419  岡山県加藤市北区工藤町佐藤1-1-3                                          | 0673-64-4746  | taro.kato@tsuda.info        | 2020-02-09 11:43:25 | 2020-02-09 11:43:25 |
|  9 |            25 |           4 |         2 | 聡太郎     | 若松      | 7945472  | 9095073  岩手県笹田市西区渡辺町宮沢4-7-4                                          | 0238-07-1767  | tanabe.miki@nishinosono.com | 2020-02-09 11:43:25 | 2020-02-09 11:43:25 |
| 10 |            10 |           1 |         3 | 直子       | 工藤      | 8767919  | 6849950  愛知県野村市南区田辺町工藤6-1-7 コーポ吉田107号                          | 01246-4-5726  | yamada.tsubasa@yahoo.co.jp  | 2020-02-09 11:43:25 | 2020-02-09 11:43:25 |
+----+---------------+-------------+-----------+------------+-----------+----------+-----------------------------------------------------------------------------------+---------------+-----------------------------+---------------------+---------------------+

prefectures テーブル

mysql> SELECT * FROM prefectures;
+----+-----------+--------------+--------------------+---------------------+---------------------+
| id | region_id | name         | name_kana          | created_at          | updated_at          |
+----+-----------+--------------+--------------------+---------------------+---------------------+
|  1 |         1 | 北海道       | ホッカイドウ       | 2020-02-09 11:43:25 | 2020-02-09 11:43:25 |
|  2 |         2 | 青森県       | アオモリケン       | 2020-02-09 11:43:25 | 2020-02-09 11:43:25 |
|  3 |         2 | 岩手県       | イワテケン         | 2020-02-09 11:43:25 | 2020-02-09 11:43:25 |
|  4 |         2 | 宮城県       | ミヤギケン         | 2020-02-09 11:43:25 | 2020-02-09 11:43:25 |
|  5 |         2 | 秋田県       | アキタケン         | 2020-02-09 11:43:25 | 2020-02-09 11:43:25 |
|  6 |         2 | 山形県       | ヤマガタケン       | 2020-02-09 11:43:25 | 2020-02-09 11:43:25 |
|  7 |         2 | 福島県       | フクシマケン       | 2020-02-09 11:43:25 | 2020-02-09 11:43:25 |
|  8 |         3 | 茨城県       | イバラキケン       | 2020-02-09 11:43:25 | 2020-02-09 11:43:25 |
|  9 |         3 | 栃木県       | トチギケン         | 2020-02-09 11:43:25 | 2020-02-09 11:43:25 |
| 10 |         3 | 群馬県       | グンマケン         | 2020-02-09 11:43:25 | 2020-02-09 11:43:25 |
| 11 |         3 | 埼玉県       | サイタマケン       | 2020-02-09 11:43:25 | 2020-02-09 11:43:25 |
| 12 |         3 | 千葉県       | チバケン           | 2020-02-09 11:43:25 | 2020-02-09 11:43:25 |
| 13 |         3 | 東京都       | トウキョウト       | 2020-02-09 11:43:25 | 2020-02-09 11:43:25 |
| 14 |         3 | 神奈川県     | カナガワケン       | 2020-02-09 11:43:25 | 2020-02-09 11:43:25 |
| 15 |         4 | 新潟県       | ニイガタケン       | 2020-02-09 11:43:25 | 2020-02-09 11:43:25 |
| 16 |         4 | 富山県       | トヤマケン         | 2020-02-09 11:43:25 | 2020-02-09 11:43:25 |
| 17 |         4 | 石川県       | イシカワケン       | 2020-02-09 11:43:25 | 2020-02-09 11:43:25 |
| 18 |         4 | 福井県       | フクイケン         | 2020-02-09 11:43:25 | 2020-02-09 11:43:25 |
| 19 |         4 | 山梨県       | ヤマナシケン       | 2020-02-09 11:43:25 | 2020-02-09 11:43:25 |
| 20 |         4 | 長野県       | ナガノケン         | 2020-02-09 11:43:25 | 2020-02-09 11:43:25 |
| 21 |         4 | 岐阜県       | ギフケン           | 2020-02-09 11:43:25 | 2020-02-09 11:43:25 |
| 22 |         4 | 静岡県       | シズオカケン       | 2020-02-09 11:43:25 | 2020-02-09 11:43:25 |
| 23 |         4 | 愛知県       | アイチケン         | 2020-02-09 11:43:25 | 2020-02-09 11:43:25 |
| 24 |         5 | 三重県       | ミエケン           | 2020-02-09 11:43:25 | 2020-02-09 11:43:25 |
| 25 |         5 | 滋賀県       | シガケン           | 2020-02-09 11:43:25 | 2020-02-09 11:43:25 |
| 26 |         5 | 京都府       | キョウトフ         | 2020-02-09 11:43:25 | 2020-02-09 11:43:25 |
| 27 |         5 | 大阪府       | オオサカフ         | 2020-02-09 11:43:25 | 2020-02-09 11:43:25 |
| 28 |         5 | 兵庫県       | ヒョウゴケン       | 2020-02-09 11:43:25 | 2020-02-09 11:43:25 |
| 29 |         5 | 奈良県       | ナラケン           | 2020-02-09 11:43:25 | 2020-02-09 11:43:25 |
| 30 |         5 | 和歌山県     | ワカヤマケン       | 2020-02-09 11:43:25 | 2020-02-09 11:43:25 |
| 31 |         6 | 鳥取県       | トットリケン       | 2020-02-09 11:43:25 | 2020-02-09 11:43:25 |
| 32 |         6 | 島根県       | シマネケン         | 2020-02-09 11:43:26 | 2020-02-09 11:43:26 |
| 33 |         6 | 岡山県       | オカヤマケン       | 2020-02-09 11:43:26 | 2020-02-09 11:43:26 |
| 34 |         6 | 広島県       | ヒロシマケン       | 2020-02-09 11:43:26 | 2020-02-09 11:43:26 |
| 35 |         6 | 山口県       | ヤマグチケン       | 2020-02-09 11:43:26 | 2020-02-09 11:43:26 |
| 36 |         7 | 徳島県       | トクシマケン       | 2020-02-09 11:43:26 | 2020-02-09 11:43:26 |
| 37 |         7 | 香川県       | カガワケン         | 2020-02-09 11:43:26 | 2020-02-09 11:43:26 |
| 38 |         7 | 愛媛県       | エヒメケン         | 2020-02-09 11:43:26 | 2020-02-09 11:43:26 |
| 39 |         7 | 高知県       | コウチケン         | 2020-02-09 11:43:26 | 2020-02-09 11:43:26 |
| 40 |         8 | 福岡県       | フクオカケン       | 2020-02-09 11:43:26 | 2020-02-09 11:43:26 |
| 41 |         8 | 佐賀県       | サガケン           | 2020-02-09 11:43:26 | 2020-02-09 11:43:26 |
| 42 |         8 | 長崎県       | ナガサキケン       | 2020-02-09 11:43:26 | 2020-02-09 11:43:26 |
| 43 |         8 | 熊本県       | クマモトケン       | 2020-02-09 11:43:26 | 2020-02-09 11:43:26 |
| 44 |         8 | 大分県       | オオイタケン       | 2020-02-09 11:43:26 | 2020-02-09 11:43:26 |
| 45 |         8 | 宮崎県       | ミヤザキケン       | 2020-02-09 11:43:26 | 2020-02-09 11:43:26 |
| 46 |         8 | 鹿児島県     | カゴシマケン       | 2020-02-09 11:43:26 | 2020-02-09 11:43:26 |
| 47 |         8 | 沖縄県       | オキナワケン       | 2020-02-09 11:43:26 | 2020-02-09 11:43:26 |
+----+-----------+--------------+--------------------+---------------------+---------------------+

regions テーブル

mysql> SELECT * FROM regions;
+----+-----------------+-----------------------------+---------------------+---------------------+
| id | name            | name_kana                   | created_at          | updated_at          |
+----+-----------------+-----------------------------+---------------------+---------------------+
|  1 | 北海道地方      | ホッカイドウチホウ          | 2020-02-09 11:43:26 | 2020-02-09 11:43:26 |
|  2 | 東北地方        | トウホクチホウ              | 2020-02-09 11:43:26 | 2020-02-09 11:43:26 |
|  3 | 関東地方        | カントウチホウ              | 2020-02-09 11:43:26 | 2020-02-09 11:43:26 |
|  4 | 中部地方        | チュウブチホウ              | 2020-02-09 11:43:26 | 2020-02-09 11:43:26 |
|  5 | 近畿地方        | キンキチホウ                | 2020-02-09 11:43:26 | 2020-02-09 11:43:26 |
|  6 | 中国地方        | チュウゴクチホウ            | 2020-02-09 11:43:26 | 2020-02-09 11:43:26 |
|  7 | 四国地方        | シコクチホウ                | 2020-02-09 11:43:26 | 2020-02-09 11:43:26 |
|  8 | 九州地方        | キュウシュウチホウ          | 2020-02-09 11:43:26 | 2020-02-09 11:43:26 |
+----+-----------------+-----------------------------+---------------------+---------------------+

divisions テーブル

mysql> SELECT * FROM divisions;
+----+-----------------+--------------------------------+---------------------+---------------------+
| id | name            | name_kana                      | created_at          | updated_at          |
+----+-----------------+--------------------------------+---------------------+---------------------+
|  1 | 総務部          | ソウムブ                       | 2020-02-09 11:43:26 | 2020-02-09 11:43:26 |
|  2 | 人事部          | ジンジブ                       | 2020-02-09 11:43:26 | 2020-02-09 11:43:26 |
|  3 | 営業部          | エイギョウブ                   | 2020-02-09 11:43:26 | 2020-02-09 11:43:26 |
|  4 | 海外事業部      | カイガイジギョウブ             | 2020-02-09 11:43:26 | 2020-02-09 11:43:26 |
|  5 | IT事業部        | アイティージギョウブ           | 2020-02-09 11:43:26 | 2020-02-09 11:43:26 |
+----+-----------------+--------------------------------+---------------------+---------------------+

CREATE VIEW

それではVIEWを作成します。

CREATE VIEW employees_info_view (id, last_name, first_name, prefecture, region, division)
AS
SELECT employees.id, employees.last_name, employees.first_name, prefectures.name, regions.name, divisions.name
FROM employees 
INNER JOIN prefectures ON prefectures.id = employees.prefecture_id
INNER JOIN regions ON regions.id = prefectures.region_id
INNER JOIN divisions ON divisions.id = employees.division_id;

※VIEWのカラム数とSELECTするカラム数が合わないとエラーになるので注意しましょう。

成功すると、このようになるか思います。

mysql> SELECT * FROM employees_info_view;
+----+-----------+------------+------------+--------------+-----------------+
| id | last_name | first_name | prefecture | region       | division        |
+----+-----------+------------+------------+--------------+-----------------+
|  1 | 三宅      | 直子       | 福井県     | 中部地方     | 海外事業部      |
|  2 | 原田      | 零         | 兵庫県     | 近畿地方     | 海外事業部      |
|  3 | 青山      | 香織       | 大阪府     | 近畿地方     | 海外事業部      |
|  4 | 山岸      | 直子       | 福島県     | 東北地方     | 総務部          |
|  5 | 笹田      | 真綾       | 島根県     | 中国地方     | 海外事業部      |
|  6 | 三宅      | 充         | 兵庫県     | 近畿地方     | 人事部          |
|  7 | 吉本      | 晃         | 広島県     | 中国地方     | IT事業部        |
|  8 | 廣川      | 春香       | 福井県     | 中部地方     | 総務部          |
|  9 | 若松      | 聡太郎     | 滋賀県     | 近畿地方     | 海外事業部      |
| 10 | 工藤      | 直子       | 群馬県     | 関東地方     | 総務部          |
+----+-----------+------------+------------+--------------+-----------------+

employees_info_view ビューが作成されています。

mysql> SHOW TABLES;
+---------------------+
| Tables_in_homestead |
+---------------------+
| divisions           |
| employees           |
| employees_info_view | <------
| failed_jobs         |
| migrations          |
| password_resets     |
| prefectures         |
| regions             |
| users               |
+---------------------+

ビューを削除したい場合は drop view をします。

mysql> DROP VIEW employees_info_view;

以上簡単にですが、「MySQLでJOINを含むVIEW(ビュー)を作成する」でした。

ここまでお読みいただきありがとうございました。

スポンサーリンク

Posted by nobuhiro harada