CREATE VIEW

Create a new Logical View of a Table. The Logical View does not store any physical data, when we access a logical view, it will convert the sql into the subqery format to finish it.

For example, if you create a Logical View like:

CREATE VIEW view_t1 AS SELECT a, b FROM t1;

And do a query like:

SELECT a from view_t1;

the result equals the below query

SELECT a from (SELECT a, b FROM t1);

So, if you delete the table which the view depends on, it occurs error that the origin table not exists. And you may need to drop and recreate the view you need.

Syntax

CREATE VIEW [IF NOT EXISTS] [db.]view_name AS SELECT query

Examples

mysql> create view tmp_view as select number % 3 as a, avg(number) from numbers(1000) group by a order by a;

mysql> select * from tmp_view;
+------+-------------+
| a    | avg(number) |
+------+-------------+
|    0 |       499.5 |
|    1 |         499 |
|    2 |         500 |
+------+-------------+