CREATE VIEW

语法说明

视图是基于 SQL 语句的结果集的可视化的表。

视图包含行和列，就像一个真实的表。视图中的字段就是来自一个或多个数据库中的真实的表中的字段。

您可以向视图添加 SQL 函数，WHERE 或者 JOIN 语句，也同样可以呈现数据，类似于这些数据来自于某个单一的表一样。

CREATE VIEW 语句用于创建一个视图。

语法结构

  1. > CREATE [ OR REPLACE ] VIEW view_name AS
  2.   SELECT column1, column2, ...
  3.   FROM table_name
  4.   WHERE condition;

Note

视图总是显示最新的数据。每当你查询视图时，数据库引擎通过使用视图的 SQL 语句重建数据。

示例

  • 示例 1：
  1. CREATE TABLE t00(a INTEGER);
  2. INSERT INTO t00 VALUES (1),(2);
  3. CREATE TABLE t01(a INTEGER);
  4. INSERT INTO t01 VALUES (1);
  5. CREATE OR REPLACE VIEW v0 AS SELECT t00.a, t01.a AS b FROM t00 LEFT JOIN t01 USING(a);
  7. mysql> SELECT t00.a, t01.a AS b FROM t00 LEFT JOIN t01 USING(a);
  8. +------+------+
  9. | a    | b    |
  10. +------+------+
  11. |    1 |    1 |
  12. |    2 | NULL |
  13. +------+------+
  14. 2 rows in set (0.01 sec)
  16. mysql> SELECT * FROM v0 WHERE b >= 0;
  17. +------+------+
  18. | a    | b    |
  19. +------+------+
  20. |    1 |    1 |
  21. +------+------+
  22. 1 row in set (0.01 sec)
  24. mysql> SHOW CREATE VIEW v0;
  25. +------+---------------------------------------------------------------------------------------+
  26. | View | Create View                                                                           |
  27. +------+---------------------------------------------------------------------------------------+
  28. | v0   | CREATE OR REPLACE VIEW v0 AS SELECT t00.a, t01.a AS b FROM t00 LEFT JOIN t01 USING(a) |
  29. +------+---------------------------------------------------------------------------------------+
  30. 1 row in set (0.00 sec)
  • 示例 2：
  1. drop table if exists t1;
  2. create table t1 (id int,ti tinyint unsigned,si smallint,bi bigint unsigned,fl float,dl double,de decimal,ch char(20),vch varchar(20),dd date,dt datetime);
  3. insert into t1 values(1,1,4,3,1113.32,111332,1113.32,'hello','subquery','2022-04-28','2022-04-28 22:40:11');
  4. insert into t1 values(2,2,5,2,2252.05,225205,2252.05,'bye','sub query','2022-04-28','2022-04-28 22:40:11');
  5. insert into t1 values(3,6,6,3,3663.21,366321,3663.21,'hi','subquery','2022-04-28','2022-04-28 22:40:11');
  6. insert into t1 values(4,7,1,5,4715.22,471522,4715.22,'good morning','my subquery','2022-04-28','2022-04-28 22:40:11');
  7. insert into t1 values(5,1,2,6,51.26,5126,51.26,'byebye',' is subquery?','2022-04-28','2022-04-28 22:40:11');
  8. insert into t1 values(6,3,2,1,632.1,6321,632.11,'good night','maybe subquery','2022-04-28','2022-04-28 22:40:11');
  9. insert into t1 values(7,4,4,3,7443.11,744311,7443.11,'yes','subquery','2022-04-28','2022-04-28 22:40:11');
  10. insert into t1 values(8,7,5,8,8758.00,875800,8758.11,'nice to meet','just subquery','2022-04-28','2022-04-28 22:40:11');
  11. insert into t1 values(9,8,4,9,9849.312,9849312,9849.312,'see you','subquery','2022-04-28','2022-04-28 22:40:11');
  12. drop table if exists t2;
  13. create table t2 (id int,ti tinyint unsigned,si smallint,bi bigint unsigned,fl float,dl double,de decimal,ch char(20),vch varchar(20),dd date,dt datetime);
  14. insert into t2 values(1,1,4,3,1113.32,111332,1113.32,'hello','subquery','2022-04-28','2022-04-28 22:40:11');
  15. insert into t2 values(2,2,5,2,2252.05,225205,2252.05,'bye','sub query','2022-04-28','2022-04-28 22:40:11');
  16. insert into t2 values(3,6,6,3,3663.21,366321,3663.21,'hi','subquery','2022-04-28','2022-04-28 22:40:11');
  17. insert into t2 values(4,7,1,5,4715.22,471522,4715.22,'good morning','my subquery','2022-04-28','2022-04-28 22:40:11');
  18. insert into t2 values(5,1,2,6,51.26,5126,51.26,'byebye',' is subquery?','2022-04-28','2022-04-28 22:40:11');
  19. insert into t2 values(6,3,2,1,632.1,6321,632.11,'good night','maybe subquery','2022-04-28','2022-04-28 22:40:11');
  20. insert into t2 values(7,4,4,3,7443.11,744311,7443.11,'yes','subquery','2022-04-28','2022-04-28 22:40:11');
  21. insert into t2 values(8,7,5,8,8758.00,875800,8758.11,'nice to meet','just subquery','2022-04-28','2022-04-28 22:40:11');
  22. insert into t2 values(9,8,4,9,9849.312,9849312,9849.312,'see you','subquery','2022-04-28','2022-04-28 22:40:11');
  24. mysql> select * from (select * from t1) sub where id > 4;
  25. +------+------+------+------+----------+---------+------+--------------+----------------+------------+---------------------+
  26. | id   | ti   | si   | bi   | fl       | dl      | de   | ch           | vch            | dd         | dt                  |
  27. +------+------+------+------+----------+---------+------+--------------+----------------+------------+---------------------+
  28. |    5 |    1 |    2 |    6 |    51.26 |    5126 |   51 | byebye       |  is subquery?  | 2022-04-28 | 2022-04-28 22:40:11 |
  29. |    6 |    3 |    2 |    1 |    632.1 |    6321 |  632 | good night   | maybe subquery | 2022-04-28 | 2022-04-28 22:40:11 |
  30. |    7 |    4 |    4 |    3 |  7443.11 |  744311 | 7443 | yes          | subquery       | 2022-04-28 | 2022-04-28 22:40:11 |
  31. |    8 |    7 |    5 |    8 |     8758 |  875800 | 8758 | nice to meet | just subquery  | 2022-04-28 | 2022-04-28 22:40:11 |
  32. |    9 |    8 |    4 |    9 | 9849.312 | 9849312 | 9849 | see you      | subquery       | 2022-04-28 | 2022-04-28 22:40:11 |
  33. +------+------+------+------+----------+---------+------+--------------+----------------+------------+---------------------+
  34. 5 rows in set (0.01 sec)
  36. create view v1 as select * from (select * from t1) sub where id > 4;
  37. create view v2 as select ti as t,fl as f from (select * from t1) sub where dl <> 4;
  38. create view v3 as select * from (select ti as t,fl as f from t1 where dl <> 4) sub;
  39. create view v4 as select id,min(ti) from (select * from t1) sub group by id;
  40. create view v5 as select * from (select id,min(ti) from (select * from t1) t1 group by id) sub;
  42. mysql> select * from v1;
  43. +------+------+------+------+----------+---------+------+--------------+----------------+------------+---------------------+
  44. | id   | ti   | si   | bi   | fl       | dl      | de   | ch           | vch            | dd         | dt                  |
  45. +------+------+------+------+----------+---------+------+--------------+----------------+------------+---------------------+
  46. |    5 |    1 |    2 |    6 |    51.26 |    5126 |   51 | byebye       |  is subquery?  | 2022-04-28 | 2022-04-28 22:40:11 |
  47. |    6 |    3 |    2 |    1 |    632.1 |    6321 |  632 | good night   | maybe subquery | 2022-04-28 | 2022-04-28 22:40:11 |
  48. |    7 |    4 |    4 |    3 |  7443.11 |  744311 | 7443 | yes          | subquery       | 2022-04-28 | 2022-04-28 22:40:11 |
  49. |    8 |    7 |    5 |    8 |     8758 |  875800 | 8758 | nice to meet | just subquery  | 2022-04-28 | 2022-04-28 22:40:11 |
  50. |    9 |    8 |    4 |    9 | 9849.312 | 9849312 | 9849 | see you      | subquery       | 2022-04-28 | 2022-04-28 22:40:11 |
  51. +------+------+------+------+----------+---------+------+--------------+----------------+------------+---------------------+
  52. 5 rows in set (0.00 sec)
  54. mysql> select * from v2;
  55. +------+----------+
  56. | t    | f        |
  57. +------+----------+
  58. |    1 |  1113.32 |
  59. |    2 |  2252.05 |
  60. |    6 |  3663.21 |
  61. |    7 |  4715.22 |
  62. |    1 |    51.26 |
  63. |    3 |    632.1 |
  64. |    4 |  7443.11 |
  65. |    7 |     8758 |
  66. |    8 | 9849.312 |
  67. +------+----------+
  68. 9 rows in set (0.00 sec)
  70. mysql> select * from v3;
  71. +------+----------+
  72. | t    | f        |
  73. +------+----------+
  74. |    1 |  1113.32 |
  75. |    2 |  2252.05 |
  76. |    6 |  3663.21 |
  77. |    7 |  4715.22 |
  78. |    1 |    51.26 |
  79. |    3 |    632.1 |
  80. |    4 |  7443.11 |
  81. |    7 |     8758 |
  82. |    8 | 9849.312 |
  83. +------+----------+
  84. 9 rows in set (0.00 sec)
  86. mysql> select * from v4;
  87. +------+---------+
  88. | id   | min(ti) |
  89. +------+---------+
  90. |    1 |       1 |
  91. |    2 |       2 |
  92. |    3 |       6 |
  93. |    4 |       7 |
  94. |    5 |       1 |
  95. |    6 |       3 |
  96. |    7 |       4 |
  97. |    8 |       7 |
  98. |    9 |       8 |
  99. +------+---------+
  100. 9 rows in set (0.00 sec)
  102. mysql> select * from v5;
  103. +------+---------+
  104. | id   | min(ti) |
  105. +------+---------+
  106. |    1 |       1 |
  107. |    2 |       2 |
  108. |    3 |       6 |
  109. |    4 |       7 |
  110. |    5 |       1 |
  111. |    6 |       3 |
  112. |    7 |       4 |
  113. |    8 |       7 |
  114. |    9 |       8 |
  115. +------+---------+
  116. 9 rows in set (0.01 sec)