MariaDB(MySQL)でビューの作成（CREATE VIEW）

テスト用データベースとテーブルの作成

説明のためにテスト用データベースとテーブル構造の作成です。

-- データベース作成
CREATE DATABASE IF NOT EXISTS db_test CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE db_test;
-- テーブル作成
CREATE TABLE t_kind(kind_id INT, kind_name VARCHAR(100));
CREATE TABLE t_item(item_id INT, kind_id INT, item_name VARCHAR(100));
-- テーブルにレコードを挿入
INSERT INTO t_kind values(1,'野菜'),(2,'日用品');
INSERT INTO t_item values(1,1,'キャベツ'),(2,1,'レタス'),(3,1,'人参'),(4,1,'大根'),(5,2,'洗剤'),(6,2,'シャンプー');

ER図は以下です。

t_item(商品テーブル)
  item_id                      t_kind(種別テーブル)
  kind_id  ←─────────  kind_id
  item_name                      kind_name

ビューの作成

ビューの作成は以下の構文となります。
CREATE VIEW 「作成するビューの名前」 AS 「SELECT文」;
以下は種類ごとの商品数を集計するSQLをビュー「v_item_count」として作成します。

  CREATE VIEW v_item_count AS
    SELECT t_kind.kind_id, t_kind.kind_name, COUNT(t_kind.kind_id) AS item_count
    FROM t_item INNER JOIN t_kind ON t_item.kind_id=t_kind.kind_id
    GROUP BY t_kind.kind_id, t_kind.kind_name;

ビューを使う

ビューを作成したらビューの名前を使ってSELECT FROMで使用することができます。

SELECT * FROM v_item_count;

+---------+-----------+------------+
| kind_id | kind_name | item_count |
+---------+-----------+------------+
|       1 | 野菜      |          4 |
|       2 | 日用品    |          2 |
+---------+-----------+------------+
2 rows in set (0.002 sec)

SHOW TABLES;
で一覧に表示されます。

SHOW TABLES;
+-------------------+
| Tables_in_db_test |
+-------------------+
| t_item            |
| t_kind            |
| v_item_count      |
+-------------------+
3 rows in set (0.002 sec)

DESC v_item_count;
でカラム一覧が表示されます。

DESC v_item_count;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| kind_id    | int(11)      | YES  |     | NULL    |       |
| kind_name  | varchar(100) | YES  |     | NULL    |       |
| item_count | bigint(21)   | NO   |     | 0       |       |
+------------+--------------+------+-----+---------+-------+
3 rows in set (0.016 sec)

SHOW CREATE VIEW v_item_count;
でSQLが確認できます。

SHOW CREATE VIEW v_item_count;
省略
+-------------------------------------------------------------------+
| Create View                                                       |
+-------------------------------------------------------------------+
| (見やすく整形しています)                                          |
| CREATE VIEW v_item_count AS                                       |
| select t_kind.kind_id AS kind_id,                                 |
| t_kind.kind_name AS kind_name,                                    |
| count(t_kind.kind_id) AS item_count                               |
| from (t_item join t_kind on(t_item.kind_id = t_kind.kind_id))     |
| group by t_kind.kind_id,t_kind.kind_name                          |
+-------------------------------------------------------------------+
省略