CREATE VIEW

CREATE VIEW

Description

The view is a virtual table based on the result-set of an SQL statement.

A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.

You can add SQL statements and functions to a view and present the data as if the data were coming from one single table.

A view is created with the CREATE VIEW statement.

Syntax

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

Note

A view always shows up-to-date data! The database engine recreates the view, every time a user queries it.

Examples

Example 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 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 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)

Example 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;
41. mysql> select * from v1;
42. +------+------+------+------+----------+---------+------+--------------+----------------+------------+---------------------+
43. | id   | ti   | si   | bi   | fl       | dl      | de   | ch           | vch            | dd         | dt                  |
44. +------+------+------+------+----------+---------+------+--------------+----------------+------------+---------------------+
45. |    5 |    1 |    2 |    6 |    51.26 |    5126 |   51 | byebye       |  is subquery?  | 2022-04-28 | 2022-04-28 22:40:11 |
46. |    6 |    3 |    2 |    1 |    632.1 |    6321 |  632 | good night   | maybe subquery | 2022-04-28 | 2022-04-28 22:40:11 |
47. |    7 |    4 |    4 |    3 |  7443.11 |  744311 | 7443 | yes          | subquery       | 2022-04-28 | 2022-04-28 22:40:11 |
48. |    8 |    7 |    5 |    8 |     8758 |  875800 | 8758 | nice to meet | just subquery  | 2022-04-28 | 2022-04-28 22:40:11 |
49. |    9 |    8 |    4 |    9 | 9849.312 | 9849312 | 9849 | see you      | subquery       | 2022-04-28 | 2022-04-28 22:40:11 |
50. +------+------+------+------+----------+---------+------+--------------+----------------+------------+---------------------+
51. 5 rows in set (0.00 sec)

53. mysql> select * from v2;
54. +------+----------+
55. | t    | f        |
56. +------+----------+
57. |    1 |  1113.32 |
58. |    2 |  2252.05 |
59. |    6 |  3663.21 |
60. |    7 |  4715.22 |
61. |    1 |    51.26 |
62. |    3 |    632.1 |
63. |    4 |  7443.11 |
64. |    7 |     8758 |
65. |    8 | 9849.312 |
66. +------+----------+
67. 9 rows in set (0.00 sec)

69. mysql> select * from v3;
70. +------+----------+
71. | t    | f        |
72. +------+----------+
73. |    1 |  1113.32 |
74. |    2 |  2252.05 |
75. |    6 |  3663.21 |
76. |    7 |  4715.22 |
77. |    1 |    51.26 |
78. |    3 |    632.1 |
79. |    4 |  7443.11 |
80. |    7 |     8758 |
81. |    8 | 9849.312 |
82. +------+----------+
83. 9 rows in set (0.00 sec)

85. mysql> select * from v4;
86. +------+---------+
87. | id   | min(ti) |
88. +------+---------+
89. |    1 |       1 |
90. |    2 |       2 |
91. |    3 |       6 |
92. |    4 |       7 |
93. |    5 |       1 |
94. |    6 |       3 |
95. |    7 |       4 |
96. |    8 |       7 |
97. |    9 |       8 |
98. +------+---------+
99. 9 rows in set (0.00 sec)

101. mysql> select * from v5;
102. +------+---------+
103. | id   | min(ti) |
104. +------+---------+
105. |    1 |       1 |
106. |    2 |       2 |
107. |    3 |       6 |
108. |    4 |       7 |
109. |    5 |       1 |
110. |    6 |       3 |
111. |    7 |       4 |
112. |    8 |       7 |
113. |    9 |       8 |
114. +------+---------+
115. 9 rows in set (0.01 sec)