MySQL之视图详解_mysql视图

什么是视图？

• 视图（view）是一种虚拟存在的表，视图中的数据并不在数据库中实际存在，视图中的数据来自定义视图时select查询的表，并且视图是动态生成的。
• 通俗的讲：视图只保存了select的SQL逻辑，不保存查询结果。所以我们在创建视图时，主要工作就落在创建select查询语句上。
• MySQL 视图是一个虚拟的表，它由一个 SQL 查询定义，并且不存储实际的数据。视图的数据来自于查询执行的结果，并且可以像表一样被查询、更新和删除。视图提供了一种简化复杂查询的方法，并且可以用来限制用户对数据库中特定数据的访问。
• 视图的定义保存在数据字典内，创建视图所基于的表称为“基表”

创建视图

mysql> create [or replace] view 视图名[(字段列表)] as select语句 [with [cascaded/local] check option];
1

注：视图的字段列表写不写都无所谓

mysql> select * from stu;
+-----+--------+-----+---------+
| id  | name   | age | classid |
+-----+--------+-----+---------+
| 101 | 小天   |  18 |    1001 |
| 102 | 小明   |  20 |    1003 |
| 103 | 小红   |  13 |    1002 |
+-----+--------+-----+---------+


mysql> create or replace view view_stu as select id,name from stu where age>15;
Query OK, 0 rows affected (0.02 sec)


mysql> show tables;
+-----------------+
| Tables_in_huazi |
+-----------------+
| account         |
| class           |
| department      |
| employee        |
| mytable         |
| stu             |
| view_employee   |
| view_stu        |
+-----------------+


mysql> select * from view_stu;
+-----+--------+
| id  | name   |
+-----+--------+
| 101 | 小天   |
| 102 | 小明   |
+-----+--------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

mysql> select * from employee;
+------+-----------+-----------------+--------+-------+--------+
| id   | name      | job             | salary | bonus | job_id |
+------+-----------+-----------------+--------+-------+--------+
|  101 | 麦当      | 后端研发        |  25000 |  5000 |   1003 |
|  102 | 咕咚      | 网络运维        |  15000 |  3000 |   1003 |
|  103 | 迪亚      | 测试工程师      |  12000 |  2000 |   1003 |
|  104 | 米龙      | 后端开发        |  20000 |  3500 |   1003 |
|  105 | 极光      | 前端开发        |  15000 |  2500 |   1003 |
|  106 | 村长      | 人力资源        |  10000 |   500 |   1001 |
|  107 | 五条人    | 销售工程师      |  14000 |  7000 |   1002 |
|  108 | 皇帝      | 董事长          |  30000 | 10000 |   1004 |
+------+-----------+-----------------+--------+-------+--------+


mysql> create or replace view view_employee(id,name,salary) as select * from employee where salary>15000;
ERROR 1353 (HY000): In definition of view, derived table or common table expression, SELECT list and column names list have different column counts
#报错原因：视图的字段列表和select的字段列表必须一致，否则就会报错


mysql> create or replace view view_employee(id,name,salary) as select id,name,salary from employee where salary>15000;
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
+-----------------+
| Tables_in_huazi |
+-----------------+
| account         |
| class           |
| department      |
| employee        |
| mytable         |
| stu             |
| view_employee   |
+-----------------+


mysql> select * from view_employee;
+------+--------+--------+
| id   | name   | salary |
+------+--------+--------+
|  101 | 麦当   |  25000 |
|  104 | 米龙   |  20000 |
|  108 | 皇帝   |  30000 |
+------+--------+--------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45

查询

查看创建视图语句

mysql> show create view 视图名\G;
1

mysql> show create view view_employee;
+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View          | Create View

                                         | character_set_client | collation_connection |
+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| view_employee | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_employee` (`id`,`name`,`salary`) AS select `employee`.`id` AS `id`,`employee`.`name` AS `name`,`employee`.`salary` AS `salary` from `employee` where (`employee`.`salary` > 15000) | utf8mb4              | utf8mb4_0900_ai_ci   |
+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+



mysql> show create view view_employee\G;
*************************** 1. row ***************************
                View: view_employee
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_employee` (`id`,`name`,`salary`) AS select `employee`.`id` AS `id`,`employee`.`name` AS `name`,`employee`.`salary` AS `salary` from `employee` where (`employee`.`salary` > 15000)
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

查看视图数据

mysql> select * from 视图名;
1

mysql> select * from view_employee;
+------+--------+--------+
| id   | name   | salary |
+------+--------+--------+
|  101 | 麦当   |  25000 |
|  104 | 米龙   |  20000 |
|  108 | 皇帝   |  30000 |
+------+--------+--------+
1
2
3
4
5
6
7
8

修改视图结构

方式1

mysql> create [or replace] view 视图名[(字段列表)] as select语句 [with [cascaded/local] check option];
1

mysql> select * from view_employee;
+------+--------+--------+
| id   | name   | salary |
+------+--------+--------+
|  101 | 麦当   |  25000 |
|  104 | 米龙   |  20000 |
|  108 | 皇帝   |  30000 |
+------+--------+--------+


mysql> create or replace view view_employee as select id,name from employee where salary>15000;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from view_employee;
+------+--------+
| id   | name   |
+------+--------+
|  101 | 麦当   |
|  104 | 米龙   |
|  108 | 皇帝   |
+------+--------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

注：视图的字段列表写不写都无所谓

方式2

mysql> alter view 视图名[(字段列表)] as select语句 [with [cascaded/local] check option];
1

mysql> select * from stu;
+-----+--------+-----+---------+
| id  | name   | age | classid |
+-----+--------+-----+---------+
| 101 | 小天   |  18 |    1001 |
| 102 | 小明   |  20 |    1003 |
| 103 | 小红   |  13 |    1002 |
+-----+--------+-----+---------+


mysql> select * from view_stu;
+-----+--------+
| id  | name   |
+-----+--------+
| 101 | 小天   |
| 102 | 小明   |
+-----+--------+



mysql> alter view view_stu as select id,name,age from stu where age>15;
Query OK, 0 rows affected (0.03 sec)


mysql> select * from view_stu;
+-----+--------+-----+
| id  | name   | age |
+-----+--------+-----+
| 101 | 小天   |  18 |
| 102 | 小明   |  20 |
+-----+--------+-----+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

注：视图的字段列表写不写都无所谓

删除视图

mysql> drop view [if exists] 视图名;
1

mysql> show tables;
+-----------------+
| Tables_in_huazi |
+-----------------+
| account         |
| class           |
| department      |
| employee        |
| mytable         |
| stu             |
| view_employee   |
| view_stu        |
+-----------------+


mysql> drop view if exists view_stu;
Query OK, 0 rows affected (0.00 sec)


mysql> show tables;
+-----------------+
| Tables_in_huazi |
+-----------------+
| account         |
| class           |
| department      |
| employee        |
| mytable         |
| stu             |
| view_employee   |
+-----------------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

在视图中插入数据

注：在视图中插入数据，都是基于基表的插入条件

mysql> create view view_account as select id,name from account where money>=2000;
Query OK, 0 rows affected (0.01 sec)


mysql> show tables;
+-----------------+
| Tables_in_huazi |
+-----------------+
| account         |
| class           |
| department      |
| employee        |
| mytable         |
| stu             |
| view_account    |
| view_employee   |
+-----------------+


mysql> select * from view_account;
+----+--------+
| id | name   |
+----+--------+
|  1 | 张三   |
|  2 | 李四   |
+----+--------+



mysql> insert into view_account values
    -> (3,'小明');
Query OK, 1 row affected (0.01 sec)


mysql> select * from view_account;
+----+--------+
| id | name   |
+----+--------+
|  1 | 张三   |
|  2 | 李四   |
+----+--------+


mysql> select * from account;
+----+--------+-------+
| id | name   | money |
+----+--------+-------+
|  1 | 张三   |  2000 |
|  2 | 李四   |  2000 |
|  3 | 小明   |  NULL |
+----+--------+-------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51

mysql> select * from account;
+----+--------+-------+
| id | name   | money |
+----+--------+-------+
|  1 | 张三   |  2000 |
|  2 | 李四   |  2000 |
+----+--------+-------+


#将主表中的money字段修改约束条件为not null
mysql> alter table account modify money int not null;
Query OK, 0 rows affected (0.06 sec)


mysql> show create table account\G;
*************************** 1. row ***************************
       Table: account
Create Table: CREATE TABLE `account` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(10) NOT NULL,
  `money` int NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='账户表'



mysql> select * from view_account;
+----+--------+
| id | name   |
+----+--------+
|  1 | 张三   |
|  2 | 李四   |
+----+--------+


#插入失败的原因：主表中的money字段是not null
mysql> insert int view_account values
    -> (3,'小明');
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'int view_account values
(3,'小明')' at line 1
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40

视图的with检查选项

视图的更新

• 要使视图可更新，视图数据的行与基础表中的行之间必须存在一对一的关系。如果视图包含以下任何一项，则该视图不可更新:
  

视图的作用和优点

作用：

• 控制安全
• 保存查询数据

优点：

• 提供了灵活一致级别安全性。
• 隐藏了数据的复杂性
• 简化了用户的SQL指令
• 通过重命名列，从另一个角度提供数据

视图的使用规则

1. 视图必须有唯一命名
2. 在mysql中视图的数量没有限制
3. 创建视图必须从管理员那里获得必要的权限
4. 视图支持嵌套，也就是说可以利用其他视图检索出来的数据创建新的视图
5. 在视图中可以使用order by，但是如果视图内已经使用该排序子句，则视图的ORDER BY将覆盖前面的ORDER BY。
6. 视图不能索引，也不能关联触发器或默认值
7. 视图可以和表同时使用

案例