视图

1.虚拟表，一条查询语句就可以生成一张视图，但毫无意义，一般通过联表查询生成视图，视图在数据库目录下其实只是一个表结构文件，不包含数据

单张表

mysql> create view v1 as select * from class;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from v1;
+-----+--------------+
| cid | caption      |
+-----+--------------+
|   1 | 三年二班     |
|   2 | 三年三班     |
|   3 | 一年二班     |
|   4 | 二年九班     |
+-----+--------------+
4 rows in set (0.00 sec)

联表：

mysql> create view v2 as select class.caption,course.cname from class inner join course on class.cid=course.cid;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from v2;
+--------------+--------+
| caption      | cname  |
+--------------+--------+
| 三年二班     | 生物   |
| 三年三班     | 物理   |
| 一年二班     | 体育   |
| 二年九班     | 美术   |
+--------------+--------+
4 rows in set (0.00 sec)

mysql> 

 

 

查询视图：

show tables;(普通表和视图都会查询出来)

show table  status  where  comment ='view';

mysql> show table  status  where  comment ='view';
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------+----------+----------------+---------+
| v1   | NULL   |    NULL | NULL       |    0 |              0 |           0 |               0 |            0 |         0 |           NULL | 2021-01-28 21:22:40 | NULL        | NULL       | NULL      |     NULL | NULL           | VIEW    |
| v2   | NULL   |    NULL | NULL       |    0 |              0 |           0 |               0 |            0 |         0 |           NULL | 2021-01-28 21:25:51 | NULL        | NULL       | NULL      |     NULL | NULL           | VIEW    |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------+----------+----------------+---------+
2 rows in set (0.00 sec)