MySQL 视图

数据库对象

为何使用视图

1. 视图可以使指定用户 仅能管理查看 指定的字段 指定的表 保证了数据的安全 , 同时简化了查询
2. 可以将视图理解为存储起来的SELECT 语句,视图建立在已有表的基础上,视图赖以建立的这些表称为基表 及 SELECT 涉及的表称 基表
3. 语法: CREATE VIEW 视图名 [字段,字段…] AS 查询语句
4. 视图是一和种·虚拟表·，本身是不存有数据的，占用很少的内存空间，它是SQL中一个重要概念。
5. 视图的创建和删除只影响视图本身，不影响对应的基表。但是当对视图中的数据进行增加、删除和修改时，数据表中的数据会相应地发生变化，反之亦然。
6. 向视图提供数据内容的语句为 SELECT 语句
7. 在数据库中，视图不会保存数据，数据真正保存在数据表中。当对视图中的数据进行增删改操作时，数据表中的数据会相应地发生变化；反之亦然。
8. 视图的删除并不会影响到基表数据

   使用

   准备工作:
   创建并使用数据库: ```sql CREATE DATABASE t_view_db CHARACTER SET ‘utf8mb4’;

USE t_view_db;

创建表(使用了基于原有表拷贝新表的方法 同时复制了数据 约束 [外键约束无法复制]):
```sql
CREATE TABLE employees
SELECT *
FROM atguigudb.employees;
CREATE TABLE dept
SELECT *
FROM atguigudb.departments;
# 查看复制表的数据
SELECT *
FROM employees;
SELECT *
FROM dept;

创建视图

###### 创建单表视图
CREATE VIEW vu_emp1
AS
SELECT employee_id, first_name, last_name
FROM employees;
### 查询视图
SELECT *
FROM vu_emp1;
###### 创建视图可使用别名 使用别名后视图中的字段名将使用别名
CREATE VIEW vu_emp2
AS
SELECT employee_id e_id, first_name f_name, last_name l_name
FROM employees;
### 查询
SELECT *
FROM vu_emp2;
##### 创建视图可以使用视图字段列表
CREATE VIEW vu_emp3 (emp_id, first, last) # 与 SELECT 的字段列表顺序、数量 一致 但视图中的字段名将使用此处的字段名
AS
SELECT employee_id, first_name, last_name
FROM employees;
### 查询
SELECT *
FROM vu_emp3;
#### 可以使用查询查询原表中不存在的数据 如平均工资
CREATE VIEW vu_emp4(dept_id, avg_salary)
AS
SELECT department_id, avg(salary)
FROM employees
GROUP BY department_id;
### 查询
SELECT *
FROM vu_emp4;
########## 创建多表视图(即 使用了多张表的查询)
CREATE VIEW vu_emp_dept1(e_id, l_name, f_name, salary, d_id, d_name)
AS
SELECT e.employee_id, e.last_name, e.first_name, e.salary,d.department_id,d.department_name
FROM employees e
LEFT JOIN dept d ON e.department_id = d.department_id;
#### 查询
SELECT *
FROM vu_emp_dept1;
######## 使用视图 格式化数据
CREATE VIEW vu_emp_dept2(e_id, all_name, salary, d_id, d_name)
AS
SELECT e.employee_id, concat(e.last_name, '-', e.first_name), e.salary,d.department_id,d.department_name
FROM employees e
LEFT JOIN dept d ON e.department_id = d.department_id;
#### 查询
SELECT *
FROM vu_emp_dept2;
####### 基于 表 视图 联合创建视图
CREATE VIEW vu_emp_dept3(eId, allNameDept, sal, dId, efNme, hire_date)
AS
SELECT vu_emp_dept2.e_id, concat(vu_emp_dept2.all_name, '[' , vu_emp_dept2.d_name , ']'),
       vu_emp_dept2.salary, vu_emp_dept2.d_id, e.f_name, ep.hire_date
FROM vu_emp_dept2
LEFT JOIN vu_emp2 e ON e.e_id = vu_emp_dept2.e_id
LEFT JOIN employees ep ON ep.employee_id = e.e_id;
### 查询
SELECT *
FROM vu_emp_dept3;

查看视图信息

#### 查看数据库表 视图对象
SHOW TABLES ;
#### 查看视图的结构
DESC vu_emp2;
#### 查看视图的属性信息
SHOW TABLE STATUS LIKE 'vu_emp2';
/* 执行结果显示, Comment值为VIEW 说明此表为视图 其他信息为NULL 说明 此表是个虚表 */
#### 查询视图定义信息
SHOW CREATE VIEW vu_emp2;

视图、基表数据的修改及影响

### 查询视图 vu_emp1 查询基表
SELECT *
FROM vu_emp1;
SELECT *
FROM employees;
### 增删改基表数据 会影响到视图中的数据
UPDATE employees SET first_name = '测试' WHERE employee_id = 100;
INSERT INTO employees(employee_id, first_name, last_name) VALUES (1, '添加数据', '添加数据');
DELETE FROM employees WHERE employee_id = 101;
### 再次查询视图 发现数据已经更新
SELECT *
FROM vu_emp1;
### 增删改图中数据 会影响到基表中的数据
UPDATE vu_emp1 SET first_name = '修改了视图' WHERE employee_id = 100;
INSERT INTO vu_emp1(employee_id, first_name, last_name) VALUES (99, '添加数据99', '添加数据99');
DELETE FROM vu_emp1 WHERE employee_id = 102;
### 再次查询基表 发现数据已经更新
SELECT *
FROM employees;
###### 不能增删改基表中不存在的数据 The target table vu_emp4 of the UPDATE is not updatable
UPDATE vu_emp4
SET vu_emp4.avg_salary = 6666
WHERE vu_emp4.dept_id = 101;
##  The target table vu_emp4 of the DELETE is not updatable
DELETE
FROM vu_emp4
WHERE vu_emp4.dept_id = 101;
#### 向视图添加数据有可能失败 因为视图的字段可能不完整 若某个字段没在视图显示 但非空 这时添加会失败

总结:
要使视图可更新，视图中的行和底层基本表中的行之间必须存在 一对一 的关系 , 当视图定义出现以下情况时，视图不支持更新操作:

1. 在定义视图的时候指定了“ALGORITHM = TEMPTABLE”，视图将不支持INSERT和DELETE操作；
2. 视图中不包含基表中所有被定义为非空又未指定默认值的列，视图将不支持INSERT操作；
3. 在定义视图的SELECT语句中使用了JOIN联合查询，视图将不支持INSERT和DELETE操作;
4. 在定义视图的SELECT语句后的字段列表中使用了数学表达式或子查询，视图将不支持INSERT，也不支持UPDATE使用了数学表达式、子查询的字段值；
5. 在定义视图的SELECT语句后的字段列表中使用DISTINCT. 聚合函数 . GROUP BY 、HAVING、UNION等,视图将不支持INSERT、UPDATE、DELETE；
6. 在定义视图的SELECT语句中包含了子查询，FROM后面的表,视图将不支持INSERT,UPDATE、DELETE;
7. 视图定义基于一个不可史新视图；
8. 常量视图。

虽然可以更新视图数据，但总的来说，视图作为 虚拟表，主要用于 方便查询，不建议更新视图的数
图数据的更改，都是通过对实际数据表里数据的操作来完成的。

更新、删除视图

### 先查询原视图
SELECT *
FROM vu_emp1;
### 修改 方式1
CREATE OR REPLACE VIEW vu_emp1
AS
SELECT employee_id, first_name, last_name, email
FROM employees;
### 再次查询视图 发现多了列 email
### 修改 方式2
ALTER VIEW vu_emp1
AS
SELECT employee_id, first_name, last_name, email, hire_date
FROM employees;
### 再次查询视图 发现多了列 hire_date
#### 删除视图
DROP VIEW IF EXISTS vu_emp2;
## 查看视图列表 删除成功
SHOW TABLES ;

注意:
基于视图a、b创建了新的视图c，如果将视图a或者视图b删除，会导致视图c的查询失败。这样现图c需要手动删除或修改，否则影响使用。

视图的优劣

优势:

1.操作简单
将经常使用的查询操作定义为视图，可以使开发人员不需要关心视图对应的数据表的结构、表与表之间的关联关
系，也不需要关心数据表之间的业务逻辑和查询条件，而只需要简单地操作视图即可，极大简化了开发人员对数
据库的操作。

2.减少数据冗余
视图跟实际数据表不一样，它存储的是查询语句。所以，在使用的时候，我们要通过定义视图的查询语句来获取
结果集。而视图本身不存储数据，不占用数据存储的资源，减少了数据冗余。

3.数据安全
MySQL将用户对数据的 访问限制 在某些数据的结果集上，而这些数据的结果集可以使用视图来实现。用户不必直接查询或操作数据表。这也可以理解为视图具有 隔离性，视图相当于在用户和实际的数据表之间加了一层虚拟表。

同时，MySQL可以根据权限将用户对数据的访问限制在某些视图上，用户不需要查询数据表，可以直接通过视图
获取数据表中的信息。这在一定程度上保障了数据表中数据的安全性。

4.适应灵活多变的需求
当业务系统的需求发生变化后，如果需要改动数据表的结构，则工作呈相对较大，可以使用视图来减少改动的工
作量。这种方式在实际工作中使用得比较多。

5.能够分解复杂的查询逻辑
数据库中如果存在复杂的查询逻辑，则可以将问题进行分解，创建多个视图获取数据，再将创建的多个视图结合
起来，完成复杂的查询逻辑。

劣势:

如果我们在实际数据表的基础上创建了视图，那么，如果实际数据表的结构变更了，我们就需要及时对相关的视
图进行相应的维护。特别是嵌套的视图（就是在视图的基础上创建视图），维护会变得比较复杂，可读性不好，
容易变成系统的潜在隐患，因为创建视图的SQL查询可能会对字段重命名，也可能包含复杂的逻辑，这些都会增
加维护的成本。
实际项目中，如果视图过多，会导致数据库维护成本的问题。
所以，在创建视图的时候，你要结合实际项目需求，综合考虑视图的优点和不足，这样才能正确使用视图，使系
统整体达到最优。