MySQL之视图详解
什么是视图？[*]视图（view）是一种虚拟存在的表，视图中的数据并不在数据库中实际存在，视图中的数据来自定义视图时select查询的表，并且视图是动态生成的。
[*]平凡的讲：视图只保存了select的SQL逻辑，不保存查询效果。所以我们在创建视图时，主要工作就落在创建select查询语句上。
[*]MySQL 视图是一个虚拟的表，它由一个 SQL 查询定义，并且不存储实际的数据。视图的数据来自于查询实验的效果，并且可以像表一样被查询、更新和删除。视图提供了一种简化复杂查询的方法，并且可以用来限制用户对数据库中特定数据的访问。
[*]视图的定义保存在数据字典内，创建视图所基于的表称为“基表”
创建视图
mysql> create view 视图名[(字段列表)] as select语句 check option];
注：视图的字段列表写不写都无所谓
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 | 小明 |
+-----+--------+
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 |
+------+--------+--------+
查询
查看创建视图语句
mysql> show create view 视图名\G;
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
查看视图数据
mysql> select * from 视图名;
mysql> select * from view_employee;
+------+--------+--------+
| id | name | salary |
+------+--------+--------+
|101 | 麦当 |25000 |
|104 | 米龙 |20000 |
|108 | 皇帝 |30000 |
+------+--------+--------+
修改视图布局
方式1
mysql> create view 视图名[(字段列表)] as select语句 check option];
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 | 皇帝 |+------+--------+ 注：视图的字段列表写不写都无所谓
方式2
mysql> alter view 视图名[(字段列表)] as select语句 check option];
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 |
+-----+--------+-----+
注：视图的字段列表写不写都无所谓
删除视图
mysql> drop view 视图名;
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 |
+-----------------+
在视图中插入数据
https://img-blog.csdnimg.cn/direct/5b016b59d2db459181ece85f47152731.png
注：在视图中插入数据，都是基于基表的插入条件
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 |
+----+--------+-------+
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
视图的with查抄选项
https://img-blog.csdnimg.cn/direct/7e1d7102f58648c486ef24cd70c50648.png
https://img-blog.csdnimg.cn/direct/91c2ce608e2c46d596abd780dc1d2cd6.png
https://img-blog.csdnimg.cn/direct/66c6f312f4cc4c18840b4abe3ee841e0.png
视图的更新
[*]要使视图可更新，视图数据的行与根本表中的行之间必须存在一对一的关系。假如视图包含以下任何一项，则该视图不可更新:
https://img-blog.csdnimg.cn/direct/86b8481a200a41dfb08920598ba63339.png
视图的作用和优点
https://img-blog.csdnimg.cn/direct/9aea37d7c0844072adfcd96ecce55f7c.png
作用：
[*]控制安全
[*]保存查询数据
优点：
[*]提供了灵活一致级别安全性。
[*]隐藏了数据的复杂性
[*]简化了用户的SQL指令
[*]通过重命名列，从另一个角度提供数据
视图的使用规则
[*]视图必须有唯一命名
[*]在mysql中视图的数目没有限制
[*]创建视图必须从管理员那边得到须要的权限
[*]视图支持嵌套，也就是说可以利用其他视图检索出来的数据创建新的视图
[*]在视图中可以使用order by，但是假如视图内已经使用该排序子句，则视图的ORDER BY将覆盖前面的ORDER BY。
[*]视图不能索引，也不能关联触发器或默认值
[*]视图可以和表同时使用
案例
https://img-blog.csdnimg.cn/direct/94e64c05ef0b445fb40ddc564482b63d.png
https://img-blog.csdnimg.cn/direct/de675b192d7941c1bedd2a9075796bcc.png
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
页:
[1]