MySQL之视图详解

篮之新喜  金牌会员 | 2024-6-11 13:21:49 | 显示全部楼层 | 阅读模式
打印 上一主题 下一主题
楼主

主题 651|帖子 651|积分 1957

什么是视图？



  • 视图（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企服之家，中国第一个企服评测及商务社交产业平台。

本帖子中包含更多资源

您需要 登录 才可以下载或查看，没有账号？立即注册

x
回复

使用道具 举报

0 个回复

倒序浏览
返回列表

快速回复

高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 or 立即注册

本版积分规则

篮之新喜

金牌会员
这个人很懒什么都没写!

楼主热帖

标签云

挺好的 服务器
微信订阅号
微信服务号
微信客服
小程序
H5
关于我们 商务合作 网站地图
©Copyright 2022-2024 杭州祥升科技有限公司 版权所有 浙ICP备20004199号
快速回复 返回顶部 返回列表