我的书签
添加书签
移除书签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)
