ToB企服应用市场:ToB评测及商务社交产业平台

标题: MySQL之视图详解 [打印本页]

---

作者: 篮之新喜    时间: 2024-6-11 13:21
标题: MySQL之视图详解
什么是视图？

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

创建视图

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

复制代码

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

1. mysql> select * from stu;
2. +-----+--------+-----+---------+
3. | id  | name   | age | classid |
4. +-----+--------+-----+---------+
5. | 101 | 小天   |  18 |    1001 |
6. | 102 | 小明   |  20 |    1003 |
7. | 103 | 小红   |  13 |    1002 |
8. +-----+--------+-----+---------+
11. mysql> create or replace view view_stu as select id,name from stu where age>15;
12. Query OK, 0 rows affected (0.02 sec)
15. mysql> show tables;
16. +-----------------+
17. | Tables_in_huazi |
18. +-----------------+
19. | account         |
20. | class           |
21. | department      |
22. | employee        |
23. | mytable         |
24. | stu             |
25. | view_employee   |
26. | view_stu        |
27. +-----------------+
30. mysql> select * from view_stu;
31. +-----+--------+
32. | id  | name   |
33. +-----+--------+
34. | 101 | 小天   |
35. | 102 | 小明   |
36. +-----+--------+

复制代码

1. mysql> select * from employee;
2. +------+-----------+-----------------+--------+-------+--------+
3. | id   | name      | job             | salary | bonus | job_id |
4. +------+-----------+-----------------+--------+-------+--------+
5. |  101 | 麦当      | 后端研发        |  25000 |  5000 |   1003 |
6. |  102 | 咕咚      | 网络运维        |  15000 |  3000 |   1003 |
7. |  103 | 迪亚      | 测试工程师      |  12000 |  2000 |   1003 |
8. |  104 | 米龙      | 后端开发        |  20000 |  3500 |   1003 |
9. |  105 | 极光      | 前端开发        |  15000 |  2500 |   1003 |
10. |  106 | 村长      | 人力资源        |  10000 |   500 |   1001 |
11. |  107 | 五条人    | 销售工程师      |  14000 |  7000 |   1002 |
12. |  108 | 皇帝      | 董事长          |  30000 | 10000 |   1004 |
13. +------+-----------+-----------------+--------+-------+--------+
16. mysql> create or replace view view_employee(id,name,salary) as select * from employee where salary>15000;
17. ERROR 1353 (HY000): In definition of view, derived table or common table expression, SELECT list and column names list have different column counts
18. #报错原因：视图的字段列表和select的字段列表必须一致，否则就会报错
21. mysql> create or replace view view_employee(id,name,salary) as select id,name,salary from employee where salary>15000;
22. Query OK, 0 rows affected (0.00 sec)
24. mysql> show tables;
25. +-----------------+
26. | Tables_in_huazi |
27. +-----------------+
28. | account         |
29. | class           |
30. | department      |
31. | employee        |
32. | mytable         |
33. | stu             |
34. | view_employee   |
35. +-----------------+
38. mysql> select * from view_employee;
39. +------+--------+--------+
40. | id   | name   | salary |
41. +------+--------+--------+
42. |  101 | 麦当   |  25000 |
43. |  104 | 米龙   |  20000 |
44. |  108 | 皇帝   |  30000 |
45. +------+--------+--------+

复制代码

查询

查看创建视图语句

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

复制代码

1. mysql> show create view view_employee;
2. +---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
3. | View          | Create View
5.                                          | character_set_client | collation_connection |
6. +---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
7. | 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   |
8. +---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
12. mysql> show create view view_employee\G;
13. *************************** 1. row ***************************
14.                 View: view_employee
15.          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)
16. character_set_client: utf8mb4
17. collation_connection: utf8mb4_0900_ai_ci

复制代码

查看视图数据

1. mysql> select * from 视图名;

复制代码

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

复制代码

修改视图布局

方式1

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

复制代码

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

复制代码

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

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

复制代码

1. mysql> select * from stu;
2. +-----+--------+-----+---------+
3. | id  | name   | age | classid |
4. +-----+--------+-----+---------+
5. | 101 | 小天   |  18 |    1001 |
6. | 102 | 小明   |  20 |    1003 |
7. | 103 | 小红   |  13 |    1002 |
8. +-----+--------+-----+---------+
11. mysql> select * from view_stu;
12. +-----+--------+
13. | id  | name   |
14. +-----+--------+
15. | 101 | 小天   |
16. | 102 | 小明   |
17. +-----+--------+
21. mysql> alter view view_stu as select id,name,age from stu where age>15;
22. Query OK, 0 rows affected (0.03 sec)
25. mysql> select * from view_stu;
26. +-----+--------+-----+
27. | id  | name   | age |
28. +-----+--------+-----+
29. | 101 | 小天   |  18 |
30. | 102 | 小明   |  20 |
31. +-----+--------+-----+

复制代码

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

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

复制代码

1. mysql> show tables;
2. +-----------------+
3. | Tables_in_huazi |
4. +-----------------+
5. | account         |
6. | class           |
7. | department      |
8. | employee        |
9. | mytable         |
10. | stu             |
11. | view_employee   |
12. | view_stu        |
13. +-----------------+
16. mysql> drop view if exists view_stu;
17. Query OK, 0 rows affected (0.00 sec)
20. mysql> show tables;
21. +-----------------+
22. | Tables_in_huazi |
23. +-----------------+
24. | account         |
25. | class           |
26. | department      |
27. | employee        |
28. | mytable         |
29. | stu             |
30. | view_employee   |
31. +-----------------+

复制代码

在视图中插入数据

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

1. mysql> create view view_account as select id,name from account where money>=2000;
2. Query OK, 0 rows affected (0.01 sec)
5. mysql> show tables;
6. +-----------------+
7. | Tables_in_huazi |
8. +-----------------+
9. | account         |
10. | class           |
11. | department      |
12. | employee        |
13. | mytable         |
14. | stu             |
15. | view_account    |
16. | view_employee   |
17. +-----------------+
20. mysql> select * from view_account;
21. +----+--------+
22. | id | name   |
23. +----+--------+
24. |  1 | 张三   |
25. |  2 | 李四   |
26. +----+--------+
30. mysql> insert into view_account values
31.     -> (3,'小明');
32. Query OK, 1 row affected (0.01 sec)
35. mysql> select * from view_account;
36. +----+--------+
37. | id | name   |
38. +----+--------+
39. |  1 | 张三   |
40. |  2 | 李四   |
41. +----+--------+
44. mysql> select * from account;
45. +----+--------+-------+
46. | id | name   | money |
47. +----+--------+-------+
48. |  1 | 张三   |  2000 |
49. |  2 | 李四   |  2000 |
50. |  3 | 小明   |  NULL |
51. +----+--------+-------+

复制代码

1. mysql> select * from account;
2. +----+--------+-------+
3. | id | name   | money |
4. +----+--------+-------+
5. |  1 | 张三   |  2000 |
6. |  2 | 李四   |  2000 |
7. +----+--------+-------+
10. #将主表中的money字段修改约束条件为not null
11. mysql> alter table account modify money int not null;
12. Query OK, 0 rows affected (0.06 sec)
15. mysql> show create table account\G;
16. *************************** 1. row ***************************
17.        Table: account
18. Create Table: CREATE TABLE `account` (
19.   `id` int NOT NULL AUTO_INCREMENT,
20.   `name` varchar(10) NOT NULL,
21.   `money` int NOT NULL,
22.   PRIMARY KEY (`id`)
23. ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='账户表'
27. mysql> select * from view_account;
28. +----+--------+
29. | id | name   |
30. +----+--------+
31. |  1 | 张三   |
32. |  2 | 李四   |
33. +----+--------+
36. #插入失败的原因：主表中的money字段是not null
37. mysql> insert int view_account values
38.     -> (3,'小明');
39. 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
40. (3,'小明')' at line 1

复制代码

视图的with查抄选项

视图的更新

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

视图的作用和优点

作用：

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

优点：

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

视图的使用规则

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

案例

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。

---

欢迎光临 ToB企服应用市场:ToB评测及商务社交产业平台 (https://dis.qidao123.com/)

Powered by Discuz! X3.4