MySql学习12---视图

一. 视图介绍

1.1 视图的含义

视图是一个虚拟表，是从数据库中一个或多个表中导出来的表。视图还可以从已经存在的视图的基础上定义。

通俗的讲，视图就是一条SELECT语句执行后返回的结果集。所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上。

1.2 视图的特点

视图一经定义便存储在数据库中，与其相对应的数据并没有像表那样在数据库中再存储一份，通过视图看到的数据只是存放在基本表中的数据。对视图的操作与对表的操作一样，可以对其进行查询、修改和删除。当对通过视图看到的数据进行修改时，相应的基本表的数据也要发生变化；同时，若基本表的数据发生变化，则这种变化也可以自动地反映到视图中。

1.3 视图的作用

方便操作，特别是查询操作，减少复杂的SQL语句，增强可读性；

更加安全，数据库授权命令不能限定到特定行和特定列，但是通过合理创建视图，可以把权限限定到行列级别；

二. 创建视图

视图中包含了SELECT查询的结果，因此视图的创建基于SELECT语句和已存在的数据表。

2.1 如何创建视图

创建视图就是采用 create view 视图名 as select语句就好。

2.2 在单表上创建视图

 1 mysql> create table user(id int,name varchar(30));
 2 Query OK, 0 rows affected (0.05 sec)
 3 
 4 mysql> insert into user(id,name) values(1,'zhangsan'),(2,'lisi'),(3,'wangwu');
 5 Query OK, 3 rows affected (0.02 sec)
 6 Records: 3  Duplicates: 0  Warnings: 0
 7 
 8 mysql> select *from user;
 9 +------+----------+
10 | id   | name     |
11 +------+----------+
12 |    1 | zhangsan |
13 |    2 | lisi     |
14 |    3 | wangwu   |
15 +------+----------+
16 3 rows in set (0.00 sec)
17 
18 mysql> create view view_user as select name from user;
19 Query OK, 0 rows affected (0.02 sec)
20 
21 mysql> select *from view_user;
22 +----------+
23 | name     |
24 +----------+
25 | zhangsan |
26 | lisi     |
27 | wangwu   |
28 +----------+
29 3 rows in set (0.00 sec)
30 
31 mysql> create view view_user2(view_name) as select name from user;
32 Query OK, 0 rows affected (0.02 sec)
33 
34 mysql> select *from view_user2;
35 +-----------+
36 | view_name |
37 +-----------+
38 | zhangsan  |
39 | lisi      |
40 | wangwu    |
41 +-----------+
42 3 rows in set (0.00 sec)
43 
44 mysql>

View Code

说明：在创建视图view_user和view_user2的时候，可以指定视图中应该包含的字段名字。如果不指定，就默认时表格中的字段名字。反正属性是一样的，比如都是varchar(30)

2.3 在多个表上创建视图

比如：有一个student表，包含学号和姓名；还有一个表info表，包含学号，班级，成绩；但是最终成绩单上只想保存学号，姓名，成绩这3个字段该怎么办呢，这个时候就可以在多个表上创建一个视图t，让它只包含这三个字段。

 1 mysql> create table stu(
 2     -> id int,
 3     -> name varchar(30));
 4 Query OK, 0 rows affected (0.05 sec)
 5 
 6 mysql> insert into stu(id,name) values(1,'zhangsan'),(2,'lisi'),(3,'wangwu');
 7 Query OK, 3 rows affected (0.01 sec)
 8 Records: 3  Duplicates: 0  Warnings: 0
 9 
10 mysql> select *from stu;
11 +------+----------+
12 | id   | name     |
13 +------+----------+
14 |    1 | zhangsan |
15 |    2 | lisi     |
16 |    3 | wangwu   |
17 +------+----------+
18 3 rows in set (0.00 sec)

View Code

 1 mysql> create table info(
 2     -> id int,
 3     -> classNum int,
 4     -> grade int);
 5 Query OK, 0 rows affected (0.04 sec)
 6 
 7 mysql> insert into info(id,classNum,grade) values(1,1,98),(2,2,89),(3,3,78);
 8 Query OK, 3 rows affected (0.01 sec)
 9 Records: 3  Duplicates: 0  Warnings: 0
10 
11 mysql> select *from info;
12 +------+----------+-------+
13 | id   | classNum | grade |
14 +------+----------+-------+
15 |    1 |        1 |    98 |
16 |    2 |        2 |    89 |
17 |    3 |        3 |    78 |
18 +------+----------+-------+

View Code

创建视图：

 1 mysql> create view t as select stu.id,stu.name,info.grade from stu,info where stu.id=info.id;
 2 Query OK, 0 rows affected (0.02 sec)
 3 
 4 mysql> select *from t;
 5 +------+----------+-------+
 6 | id   | name     | grade |
 7 +------+----------+-------+
 8 |    1 | zhangsan |    98 |
 9 |    2 | lisi     |    89 |
10 |    3 | wangwu   |    78 |
11 +------+----------+-------+
12 3 rows in set (0.00 sec)

View Code

三. 查看视图

类似表格中的查看语句

3.1 采用DESCRIBE语句查看视图基本信息

DESCRIBE 视图名；

1 mysql> describe t;
2 +-------+-------------+------+-----+---------+-------+
3 | Field | Type        | Null | Key | Default | Extra |
4 +-------+-------------+------+-----+---------+-------+
5 | id    | int(11)     | YES  |     | NULL    |       |
6 | name  | varchar(30) | YES  |     | NULL    |       |
7 | grade | int(11)     | YES  |     | NULL    |       |
8 +-------+-------------+------+-----+---------+-------+
9 3 rows in set (0.00 sec)

View Code

3.2 使用SHOW CREATE VIEW语句查看视图详细信息

SHOW CREATE VIEW 视图名；

四. 修改视图

MySql中通过CREATE OR REPLACE VIEW语句和ALTER语句来修改视图。

4.1 使用CREATE OR REPLACE VIEW语句修改视图

 1 mysql> create or replace view t as select * from stu;
 2 Query OK, 0 rows affected (0.02 sec)
 3 
 4 mysql> select *from t;
 5 +------+----------+
 6 | id   | name     |
 7 +------+----------+
 8 |    1 | zhangsan |
 9 |    2 | lisi     |
10 |    3 | wangwu   |
11 +------+----------+
12 3 rows in set (0.00 sec)

View Code

4.2 使用ALTER语句修改视图

 1 mysql> alter view t as select name from user;
 2 Query OK, 0 rows affected (0.02 sec)
 3 
 4 mysql> select *from t;
 5 +----------+
 6 | name     |
 7 +----------+
 8 | zhangsan |
 9 | lisi     |
10 | wangwu   |
11 +----------+
12 3 rows in set (0.00 sec)

View Code

五. 更新视图

更新视图是指通过视图来插入、更新、删除表中的数据。因为视图是一个虚拟表，其中没有数据。通过视图更新的时候都是转到基本表上进行更新的，如果对视图增加或者删除记录，实际上是对其基本表增加或者删除记录。

5.1 使用update语句更新视图

 1 mysql> select *from stu;
 2 +------+----------+
 3 | id   | name     |
 4 +------+----------+
 5 |    1 | zhangsan |
 6 |    2 | lisi     |
 7 |    3 | wangwu   |
 8 +------+----------+
 9 3 rows in set (0.00 sec)
10 
11 mysql> create view t1 as select name from stu where id=1;
12 Query OK, 0 rows affected (0.02 sec)
13 
14 mysql> select *from t1;
15 +----------+
16 | name     |
17 +----------+
18 | zhangsan |
19 +----------+
20 1 row in set (0.00 sec)
21 
22 mysql> create view t2 as select name from stu where id=2;
23 Query OK, 0 rows affected (0.02 sec)
24 
25 mysql> select *from t2;
26 +------+
27 | name |
28 +------+
29 | lisi |
30 +------+
31 1 row in set (0.00 sec)
32 
33 mysql> update t1 set name='zhangsan2';
34 Query OK, 1 row affected (0.01 sec)
35 Rows matched: 1  Changed: 1  Warnings: 0
36 
37 mysql> select *from t1;
38 +-----------+
39 | name      |
40 +-----------+
41 | zhangsan2 |
42 +-----------+
43 1 row in set (0.00 sec)
44 
45 mysql> select *from stu;
46 +------+-----------+
47 | id   | name      |
48 +------+-----------+
49 |    1 | zhangsan2 |      //发现对视图的修改，更新也会同步到基本表中；反之也是可以的
50 |    2 | lisi      |
51 |    3 | wangwu    |
52 +------+-----------+
53 3 rows in set (0.00 sec)
54 
55 mysql>

View Code

5.2 使用insert语句在基本表中插入一条记录

 1 mysql> select *from t1;
 2 +-----------+
 3 | name      |
 4 +-----------+
 5 | zhangsan2 |
 6 +-----------+
 7 1 row in set (0.00 sec)
 8 
 9 mysql> select *from t2;
10 +------+
11 | name |
12 +------+
13 | lisi |
14 +------+
15 1 row in set (0.00 sec)
16 
17 mysql> insert into stu values(1,'add1'),(2,'add2');
18 Query OK, 2 rows affected (0.01 sec)
19 Records: 2  Duplicates: 0  Warnings: 0
20 
21 mysql> select *from t1;
22 +-----------+
23 | name      |
24 +-----------+
25 | zhangsan2 |
26 | add1      |
27 +-----------+
28 2 rows in set (0.00 sec)
29 
30 mysql> select *from t2;
31 +------+
32 | name |
33 +------+
34 | lisi |
35 | add2 |
36 +------+
37 2 rows in set (0.00 sec)
38 
39 mysql>
40 
41 说明：因为t1视图的查询语句是select name from stu where id=1
42               t2视图的查询语句是select name from stu where id=2
43 因此，当执行一条insert语句后，id=1,2的地方都增加了数据，因此视图中也会增加数据

View Code

5.3 使用delete语句删除视图中的一条记录

 1 mysql> select *from stu;
 2 +------+-----------+
 3 | id   | name      |
 4 +------+-----------+
 5 |    1 | zhangsan2 |
 6 |    2 | lisi      |
 7 |    3 | wangwu    |
 8 |    1 | add1      |
 9 |    2 | add2      |
10 +------+-----------+
11 5 rows in set (0.00 sec)
12 
13 mysql> delete from t1 where name='zhangsan2';
14 Query OK, 1 row affected (0.01 sec)
15 
16 mysql> select *from stu;
17 +------+--------+
18 | id   | name   |
19 +------+--------+
20 |    2 | lisi   |
21 |    3 | wangwu |
22 |    1 | add1   |
23 |    2 | add2   |
24 +------+--------+
25 4 rows in set (0.00 sec)

View Code

Note:当视图中包含如下内容时，视图的更新操作将不能被执行：

视图中不包含基表中被定义为非空的列；
在定义视图的SELECT语句后的字段列表中使用了数学表达式；
在定义视图的SELECT语句后的字段列表中使用聚合函数；
在定义视图的SELECT语句中使用了DISTINCT，UNION, TOP, GOURP BY或HAVING子句

六. 删除视图

直接使用 DROP VIEW 视图名；