MySQL之视图详解

华子目录

什么是视图？
为什么需要视图
视图的作用和优点
创建视图
查询
- 查看创建视图语句
- 查看视图数据
修改`视图结构`
- 方式1
- 方式2
删除视图
修改视图中的数据
视图的`with`选项
- `with cascaded check option`
- `with local check option`
- 示例
- - - 创建视图
更新视图
- 注意
视图的使用规则
`不加with`
`加with`

什么是视图？

视图（view）是一种虚拟存在的表，视图中的数据并不在数据库中实际存在，视图中的数据来自定义视图时select查询的表，并且视图是动态生成的。类似对常用的复杂多表连接查询的结果截图拍照，之后需要用到时只需看一下视图即可无需重新查询，以节省资源
通俗的讲：视图只保存了select的SQL逻辑，不保存查询结果。所以我们在创建视图时，主要工作就落在创建select查询语句上。
视图的定义保存在数据字典内，创建视图所基于的表称为“基表”
视图是一种数据库对象，其内没有存储任何数据，它只是对表的一个查询

为什么需要视图

例如经常要对student和score表进行连接查询，每次都要做表的连接，写同样的一串语句，同时由于成绩数据比较敏感，对外要求不可见。对这样的问题就可以通过视图来解决

视图的作用和优点

作用：

控制安全
保存查询数据

优点：

简化操作：通过视图可以使用户将注意力集中在他所关心的数据上。使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件
提高数据的安全性：在设计数据库时可以针对不同的用户定义不同的视图，使用视图的用户只能访问他们被允许查询的结果集
数据独立：视图的结构定义好之后，如果增加新的关系或对原有的关系增加新的字段对用户访问的数据都不会造成影响

创建视图

mysql> create [or replace] [algorithm = {undefined | merge | temptable}] 
    -> view 视图名[(字段列表)] 
    -> as select语句 
    -> [with [cascaded/local] check option];

# 说明：
	1、or replace：如果要创建的视图名称已存在，则替换已有视图。
	2、algorithm：可选参数，表示视图选择的算法，默认算法是 undefined
		(1)undefined：未定义指定算法
		(2)merge:更新视图表数据的同时会更新真实表的数据
		(3)temptable:只能查询不能更新
	4、字段列表：可选，表示视图的字段列表。若省略，则使用 select 语句中的字段列表。
	5、as select语句：创建视图的 select 语句。

#当前库是view库
mysql> select database();
+------------+
| database() |
+------------+
| view       |
+------------+

#查看student表
mysql> select * from student;
+----+------+-----+--------+--------------------+---------+
| id | name | age | gender | address            | classid |
+----+------+-----+--------+--------------------+---------+
|  1 | 文   |  21 | M      | 陕西省西安市       |    1003 |
|  2 | rong |  18 | F      | 陕西省榆林市       |    1001 |
|  3 | yuan |  19 | M      | 陕西省西安市       |    1002 |
|  4 | kai  |  20 | M      | 陕西省汉中市       |    1003 |
+----+------+-----+--------+--------------------+---------+

#查看class表
mysql> select * from class;
+----------+------------+
| class_id | class_name |
+----------+------------+
|     1001 | 卓越班     |
|     1002 | 958班      |
|     1003 | 211班      |
+----------+------------+

#多表联合查询
mysql> select name,class_name from student inner join class on student.classid=class.class_id;
+------+------------+
| name | class_name |
+------+------------+
| 文   | 211班      |
| rong | 卓越班     |
| yuan | 958班      |
| kai  | 211班      |
+------+------------+

#创建视图
mysql> create view viewInfo_stu as select name,class_name from student inner join class on student.classid=class.class_id;


mysql> show tables;
+----------------+
| Tables_in_view |
+----------------+
| class          |
| student        |
| viewInfo_stu   |   #发现了创建的视图，但是在磁盘上并没有存储
+----------------+

#我们发现磁盘上并没有存储视图
[root@mysql ~]# cd /var/lib/mysql/view/
[root@mysql view]# ll
总用量 224
-rw-r----- 1 mysql mysql 114688 11月 25 04:32 
-rw-r----- 1 mysql mysql 114688 11月 25 04:35 

#查看视图内容
mysql> select * from viewInfo_stu;
+------+------------+
| name | class_name |
+------+------------+
| 文   | 211班      |
| rong | 卓越班     |
| yuan | 958班      |
| kai  | 211班      |
+------+------------+

#更新视图中的内容
mysql> update viewInfo_stu set name="荣" where name="rong";

#发现实际更新的是基表中的内容
mysql> select * from student;
+----+------+-----+--------+--------------------+---------+
| id | name | age | gender | address            | classid |
+----+------+-----+--------+--------------------+---------+
|  1 | 文   |  21 | M      | 陕西省西安市       |    1003 |
|  2 | 荣   |  18 | F      | 陕西省榆林市       |    1001 |
|  3 | yuan |  19 | M      | 陕西省西安市       |    1002 |
|  4 | kai  |  20 | M      | 陕西省汉中市       |    1003 |
+----+------+-----+--------+--------------------+---------+

#修改基表中的内容
mysql> update student set name="rong" where id=2;

mysql> select * from student;
+----+------+-----+--------+--------------------+---------+
| id | name | age | gender | address            | classid |
+----+------+-----+--------+--------------------+---------+
|  1 | 文   |  21 | M      | 陕西省西安市       |    1003 |
|  2 | rong |  18 | F      | 陕西省榆林市       |    1001 |
|  3 | yuan |  19 | M      | 陕西省西安市       |    1002 |
|  4 | kai  |  20 | M      | 陕西省汉中市       |    1003 |
+----+------+-----+--------+--------------------+---------+

#发现视图中的内容也被更新了
mysql> select * from viewInfo_stu;
+------+------------+
| name | class_name |
+------+------------+
| 文   | 211班      |
| rong | 卓越班     |
| yuan | 958班      |
| kai  | 211班      |
+------+------------+

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\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 视图名;

修改`视图结构`

方式1

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

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


mysql> create 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语句 
    -> [with [cascaded/local] 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 [if exists] 视图名;

修改视图中的数据

注：对视图的一切修改操作(insert delete update)，都是基于基表的，即对视图的修改操作，就是对基表的修改操作

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`选项

with check option子句是视图定义中的一个可选部分，用于限制通过视图进行的插入、更新和删除操作，以确保这些操作不会违反视图的定义条件。

with check option有两种形式：cascaded和local。（默认为local）。

`with cascaded check option`

当使用cascaded时，不仅视图本身的条件会被检查，任何嵌套的视图（即视图所依赖的其他视图）的条件也会被检查。如果视图是基于另一个视图创建的，那么所有嵌套层次的视图条件都将被检查

`with local check option`

当使用LOCAL时，只有当前视图本身的条件会被检查。这意味着，通过视图进行的插入、更新和删除操作必须满足视图定义中的where条件

示例

假设有一个名为employees的表，包含以下列：id、name、department和salary。

创建视图

没有with check option的视图：

CREATE VIEW high_salary_employees AS
SELECT * FROM employees
WHERE salary > 50000;

这个视图包含所有工资高于50000的员工。通过此视图插入或更新数据时，不会检查工资是否高于50000

有with check option的视图：

CREATE VIEW high_salary_employees_with_check AS
SELECT * FROM employees
WHERE salary > 50000
WITH CHECK OPTION;

通过此视图插入或更新数据时，MySQL会检查工资是否仍然高于50000。如果不是，操作会失败。

更新视图

更新(insert update delete)视图中的数据，实际上是更新创建视图时用到的基本表中的数据

注意

以下视图不可更新

包含以下关键字的SQL语句：聚合函数、distinct、group by、having、union 或 uinon all
select中包含子查询
from一个不可更新的视图
where子句的子查询引用了from子句中的表

视图的使用规则

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

`不加with`

不加with参数，在insert插入数据时，视图中select的where不起作用

mysql> select * from student;
+----+------+-----+--------+--------------------+---------+
| id | name | age | gender | address            | classid |
+----+------+-----+--------+--------------------+---------+
|  1 | 文   |  21 | M      | 陕西省西安市       |    1003 |
|  2 | rong |  18 | F      | 陕西省榆林市       |    1001 |
|  3 | yuan |  19 | M      | 陕西省西安市       |    1002 |
|  4 | kai  |  20 | M      | 陕西省汉中市       |    1003 |
+----+------+-----+--------+--------------------+---------+

#创建age>=20的student信息
mysql> create view view_stu as select * from student where age>=20;

#查看视图
mysql> select * from view_stu;
+----+------+-----+--------+--------------------+---------+
| id | name | age | gender | address            | classid |
+----+------+-----+--------+--------------------+---------+
|  1 | 文   |  21 | M      | 陕西省西安市       |    1003 |
|  4 | kai  |  20 | M      | 陕西省汉中市       |    1003 |
+----+------+-----+--------+--------------------+---------+

#修改视图，发现可以插入成功，说明where条件不起作用
mysql> insert into view_stu values
    -> (5,"hua",10,"M","山西省",1001);
Query OK, 1 row affected (0.01 sec)   


mysql> select * from student;
+----+------+-----+--------+--------------------+---------+
| id | name | age | gender | address            | classid |
+----+------+-----+--------+--------------------+---------+
|  1 | 文   |  21 | M      | 陕西省西安市       |    1003 |
|  2 | rong |  18 | F      | 陕西省榆林市       |    1001 |
|  3 | yuan |  19 | M      | 陕西省西安市       |    1002 |
|  4 | kai  |  20 | M      | 陕西省汉中市       |    1003 |
|  5 | hua  |  10 | M      | 山西省             |    1001 |
+----+------+-----+--------+--------------------+---------+

#删除视图中的数据
mysql> delete from view_stu where name="kai";

#查看基表，发现基表中的数据也被删了
mysql> select * from student;
+----+-------+-----+--------+--------------------+---------+
| id | name  | age | gender | address            | classid |
+----+-------+-----+--------+--------------------+---------+
|  1 | 文    |  21 | M      | 陕西省西安市       |    1003 |
|  2 | rong  |  18 | F      | 陕西省榆林市       |    1001 |
|  3 | yuan  |  19 | M      | 陕西省西安市       |    1002 |
|  5 | hua   |  10 | M      | 山西省             |    1001 |

`加with`

加with参数，在insert插入数据到视图中，视图中select的where起作用，即会进行where条件判断

mysql> select * from student;
+----+------+-----+--------+--------------------+---------+
| id | name | age | gender | address            | classid |
+----+------+-----+--------+--------------------+---------+
|  1 | 文   |  21 | M      | 陕西省西安市       |    1003 |
|  2 | rong |  18 | F      | 陕西省榆林市       |    1001 |
|  3 | yuan |  19 | M      | 陕西省西安市       |    1002 |
|  5 | hua  |  10 | M      | 山西省             |    1001 |
+----+------+-----+--------+--------------------+---------+

#创建视图
mysql> create view view_stu1 as select * from student where age>=20 with check option;
#with check option默认为local


#修改视图，发现可以插入失败，说明where条件起作用
mysql> insert into view_stu1 values
    -> (6,"huazi",11,"M","山西省",1002);
ERROR 1369 (HY000): CHECK OPTION failed 'view.view_stu1'

#当age在20岁以上时，修改成功
mysql> insert into view_stu1 values
    -> (6,"huazi",26,"M","山西省",1002);
Query OK, 1 row affected (0.00 sec)

#基表插入成功
mysql> select * from student;
+----+-------+-----+--------+--------------------+---------+
| id | name  | age | gender | address            | classid |
+----+-------+-----+--------+--------------------+---------+
|  1 | 文    |  21 | M      | 陕西省西安市       |    1003 |
|  2 | rong  |  18 | F      | 陕西省榆林市       |    1001 |
|  3 | yuan  |  19 | M      | 陕西省西安市       |    1002 |
|  5 | hua   |  10 | M      | 山西省             |    1001 |
|  6 | huazi |  26 | M      | 山西省             |    1002 |
+----+-------+-----+--------+--------------------+---------+

秒客网

MySQL之视图详解

华子目录

什么是视图？

为什么需要视图

视图的作用和优点

创建视图

查询

查看创建视图语句

查看视图数据

修改`视图结构`

方式1

方式2

删除视图

修改视图中的数据

视图的`with`选项

`with cascaded check option`

`with local check option`

示例

创建视图

更新视图

注意

视图的使用规则

`不加with`

`加with`

相关文章

MySQL之视图详解

华子目录

什么是视图？

为什么需要视图

视图的作用和优点

创建视图

查询

查看创建视图语句

查看视图数据

修改视图结构

方式1

方式2

删除视图

修改视图中的数据

视图的with选项

with cascaded check option

with local check option

示例

创建视图

更新视图

注意

视图的使用规则

不加with

加with

相关文章

修改`视图结构`

视图的`with`选项

`with cascaded check option`

`with local check option`

`不加with`

`加with`