CREATE VIEW
Grammar Description
A view is a visual table based on the result set of SQL statements.
The view contains rows and columns, like a real table. A field in a view is a field from a real table in one or more databases.
You can add SQL functions, WHERE or JOIN statements to the view, and you can also present data, similarly as if this data comes from a single table.
The CREATE VIEW statement is used to create a view.
Grammar Structure
> CREATE [ OR REPLACE ] VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;
Note
The view always displays the latest data. Whenever you query a view, the database engine rebuilds the data by using the view's SQL statements.
Example
- Example 1:
CREATE TABLE t00(a INTEGER);
INSERT INTO t00 VALUES (1),(2);
CREATE TABLE t01(a INTEGER);
INSERT INTO t01 VALUES (1);
CREATE OR REPLACE VIEW v0 AS SELECT t00.a, t01.a AS b FROM t00 LEFT JOIN t01 USING(a);
mysql> SELECT t00.a, t01.a AS b FROM t00 LEFT JOIN t01 USING(a);
+------+------+
| a | b |
+------+------+
| 1 | 1 |
| 2 | NULL |
+------+------+
2 rows in set (0.01 sec)
mysql> SELECT * FROM v0 WHERE b >= 0;
+------+------+
| a | b |
+------+------+
| 1 | 1 |
+------+------+
1 row in set (0.01 sec)
mysql> SHOW CREATE VIEW v0;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| View | Create View |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| v0 | CREATE OR REPLACE VIEW v0 AS SELECT t00.a, t01.a AS b FROM t00 LEFT JOIN t01 USING(a) |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)
- Example 2:
drop table if exists t1;
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);
insert into t1 values(1,1,4,3,1113.32,111332,1113.32,'hello','subquery','2022-04-28','2022-04-28 22:40:11');
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');
insert into t1 values(3,6,6,3,3663.21,366321,3663.21,'hi','subquery','2022-04-28','2022-04-28 22:40:11');
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');
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');
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');
insert into t1 values(7,4,4,3,7443.11,744311,7443.11,'yes','subquery','2022-04-28','2022-04-28 22:40:11');
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');
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');
drop table if exists t2;
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);
insert into t2 values(1,1,4,3,1113.32,111332,1113.32,'hello','subquery','2022-04-28','2022-04-28 22:40:11');
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');
insert into t2 values(3,6,6,3,3663.21,366321,3663.21,'hi','subquery','2022-04-28','2022-04-28 22:40:11');
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');
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');
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');
insert into t2 values(7,4,4,3,7443.11,744311,7443.11,'yes','subquery','2022-04-28','2022-04-28 22:40:11');
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');
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');
mysql> select * from (select * from t1) sub where id > 4;
+------+-----+-----+------+-------+-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| id | ti | si | bi | fl | dl | de | ch | vch | dd | dt |
+------+-----+-----+------+-------+-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 5 | 1 | 2 | 6 | 51.26 | 5126 | 51 | byebye | is subquery? | 2022-04-28 | 2022-04-28 22:40:11 |
| 6 | 3 | 2 | 1 | 632.1 | 6321 | 632 | good night | maybe subquery | 2022-04-28 | 2022-04-28 22:40:11 |
| 7 | 4 | 4 | 3 | 7443.11 | 744311 | 7443 | yes | subquery | 2022-04-28 | 2022-04-28 22:40:11 || 8 | 7 | 5 | 8 | 8758 | 875800 | 8758 | nice to meet | just subquery | 2022-04-28 | 2022-04-28 22:40:11 |
| 9 | 8 | 4 | 9 | 9849.312 | 9849312 | 9849 | see you | subquery | 2022-04-28 | 2022-04-28 22:40:11 |
+------+------+------+------+----------+---------+------+--------------+----------------+------------+---------------------+
5 rows in set (0.01 sec)
create view v1 as select * from (select * from t1) sub where id > 4;
create view v2 as select ti as t,fl as f from (select * from t1) sub where dl <> 4;
create view v3 as select * from (select ti as t,fl as f from t1 where dl <> 4) sub;
create view v4 as select id,min(ti) from (select * from t1) sub group by id;
create view v5 as select * from (select id,min(ti) from (select * from t1) t1 group by id) sub;
mysql> select * from v1;
+------+------+------+------+----------+---------+------+--------------+----------------+------------+---------------------+
| id | ti | si | bi | fl | dl | de | ch | vch | dd | dt |
+------+------+------+------+----------+---------+------+--------------+----------------+------------+---------------------+
| 5 | 1 | 2 | 6 | 51.26 | 5126 | 51 | byebye | is subquery? | 2022-04-28 | 2022-04-28 22:40:11 |
| 6 | 3 | 2 | 1 | 632.1 | 6321 | 632 | good night | maybe subquery | 2022-04-28 | 2022-04-28 22:40:11 |
| 7 | 4 | 4 | 3 | 7443.11 | 744311 | 7443 | yes | subquery | 2022-04-28 | 2022-04-28 22:40:11 |
| 8 | 7 | 5 | 8 | 8758 | 875800 | 8758 | nice to meet | just subquery | 2022-04-28 | 2022-04-28 22:40:11 |
| 9 | 8 | 4 | 9 | 9849.312 | 9849312 | 9849 | see you | subquery | 2022-04-28 | 2022-04-28 22:40:11 |
+------+------+------+------+----------+---------+------+--------------+----------------+------------+---------------------+
5 rows in set (0.00 sec)
mysql> select * from v2;
+------+----------+
| t | f |
+------+----------+
| 1 | 1113.32 |
| 2 | 2252.05 |
| 6 | 3663.21 |
| 7 | 4715.22 |
| 1 | 51.26 |
| 3 | 632.1 |
| 4 | 7443.11 |
| 7 | 8758 |
| 8 | 9849.312 |
+------+----------+
9 rows in set (0.00 sec)
mysql> select * from v3;
+------+----------+
| t | f |
+------+----------+
| 1 | 1113.32 |
| 2 | 2252.05 |
| 6 | 3663.21 |
| 7 | 4715.22 |
| 1 | 51.26 |
| 3 | 632.1 |
| 4 | 7443.11 |
| 7 | 8758 |
| 8 | 9849.312 |
+------+----------+
9 rows in set (0.00 sec)
mysql> select * from v4;
+------+---------+
| id | min(ti) |
+------+---------+
| 1 | 1 |
| 2 | 2 |
| 3 | 6 |
| 4 | 7 |
| 5 | 1 |
| 6 | 3 |
| 7 | 4 |
| 8 | 7 |
| 9 | 8 |
+------+---------+
9 rows in set (0.00 sec)
mysql> select * from v5;
+------+---------+
| id | min(ti) |
+------+---------+
| 1 | 1 |
| 2 | 2 |
| 3 | 6 |
| 4 | 7 |
| 5 | 1 |
| 6 | 3 |
| 7 | 4 |
| 8 | 7 |
| 9 | 8 |
+------+---------+
9 rows in set (0.01 sec)