第14章_视图

1. 常见的数据库对象

2. 视图概述

2.1 为什么使用视图？

视图一方面可以帮我们使用表的一部分而不是所有的表，另一方面也可以针对不同的用户制定不同的查询视图。 

2.2 视图的理解

• 1.视图的理解
    1.视图，就是一个虚拟表，本身不存储数据，
    视图的本质就是看作储存起来的select语句 
     
    2。视图中select语句涉及到的表，称作基表
    
    3.针对视图DML操作（增删改），会直接影响到对应的基表
    4.视图本身的删除，不会影响基表数据的删除
    
    5. 视图的应用场景：小型项目，不推荐使用视图。大型项目，可以推荐使用
    
    6.视图的优点:简化查询：控制访问   

 3. 创建视图

CREATE [OR REPLACE]
[ALGORITHM = {UNDEFINED | MERGE|TEMPTABLE}]
VIEW 视图名称 [(字段列表)]
AS 查询语句
[WITH [CASCADED|LOCAL] CHECK OPTIO

CREATE VIEW 视图名称
AS 查询语句

3.1 创建单表视图

#情况1：视图中的字段与原表中的字段有对应的关系
CREATE  VIEW vu_emp1
AS 
SELECT employee_id,last_name,salary
FROM  emps;
 
SELECT *FROM vu_emp1;

#确定视图字段名方式1：
CREATE  VIEW vu_emp2
AS 
SELECT employee_id emp_id,last_name lname,salary #查询语句字段的别名会作为视图字段名称出现
FROM  emps
WHERE salary >8000;
 
SELECT *FROM vu_emp2;
 
#确定视图字段名方式2：
CREATE  VIEW vu_emp3(emp_id,NAME,monthal_sal)#括号里的字段和查询的字段一一匹配
AS 
SELECT employee_id,last_name,salary
FROM  emps
WHERE salary >8000;
 
SELECT *FROM vu_emp3;

 
#情况2：视图中的字段与原表没有对应关系
CREATE VIEW vu_sal
AS
SELECT department_id,AVG(salary) avg_sal
FROM emps
WHERE department_id IS NOT NULL
GROUP BY department_id
 
SELECT *FROM  vu_sal;

3.2 创建多表联合视图

#2.2 针对多表
 
CREATE VIEW  vu_emp_dept
AS
SELECT e.employee_id,e.department_id,d.department_name
FROM emps e  JOIN depts d
ON e.department_id =d.department_id;
 
SELECT *FROM vu_emp_dept;
 

#利用视图对数据进行格式化 

 
#利用视图对数据进行格式化
CREATE VIEW  vu_emp_depart
AS
SELECT   CONCAT(last_name,'(',d.department_name,')')   emp_info
FROM emps e  JOIN depts d
ON e.department_id =d.department_id;
 
 
SELECT *FROM  vu_emp_depart;

3.3 基于视图创建视图

2.3 基于视图来创建视图
 
CREATE VIEW vu_emp4
AS
SELECT employee_id,last_name
FROM vu_emp1;
 
SELECT *FROM vu_emp4;

4. 查看视图

语法1：查看数据库的表对象，视图对象
SHOW TABLES ;
 
#语法2：查看视图的结构
DESC vu_emp4;
 
#语法3；查看视图的属性信息
SHOW TABLE STATUS LIKE 'vu_emp4';
 
#语法4：查看视图的详细定义信息
SHOW CREATE VIEW vu_emp4;

5. 更新视图的数据

4.1 一般情况下
SELECT *FROM  vu_emp1;
 
SELECT employee_id,last_name,salary
FROM   emps;
#更新视图的数据，同步更改表中的数据
UPDATE vu_emp1
SET salary=20000
WHERE employee_id=101;
#更新表中的数据，会同步更改视图中的数据
UPDATE emps
SET salary=10000
WHERE employee_id=101;
 
#删除视图中的数据
DELETE FROM vu_emp1
WHERE employee_id=101;

5.2 不可更新的视图

#4.2不能更新视图的情况
 
SELECT *FROM vu_sal;
#更新失败
UPDATE vu_sal
SET avg_sal =5000
WHERE department_id=30;
#删除失败
DELETE FROM vu_sal 
WHERE department_id=30;

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

6. 修改、删除视图

6.1 修改视图

5.修改视图
SELECT *FROM vu_emp1;
#方式1：
CREATE OR REPLACE  VIEW vu_emp1
AS 
SELECT employee_id,last_name,salary,email
FROM emps
WHERE salary>7000;
 
#方式2：
ALTER VIEW vu_emp1
AS 
SELECT employee_id,last_name,salary
FROM emps

6.2 删除视图

#6.删除视图
SHOW TABLES;
 
DROP VIEW vu_emp4;
 
DROP VIEW  IF EXISTS vu_emp2;

7. 总结

1. 操作简单
2. 减少数据冗余
3. 数据安全
4. 适应灵活多变的需求
5. 能够分解复杂的查询逻辑

7.2 视图不足 

如果我们在实际数据表的基础上创建了视图，那么，如果实际数据表的结构变更了，我们就需要及时对相关的视图进行相应的维护。特别是嵌套的视图（就是在视图的基础上创建视图），维护会变得比较复杂， 可读性不好 ，容易变成系统的潜在隐患。因为创建视图的 SQL 查询可能会对字段重命名，也可能包含复杂的逻辑，这些都会增加维护的成本

课后练习

#第14章_视图练习题
练习1 
题目：  
#1. 使用表employees创建视图employee_vu，其中包括姓名（LAST_NAME）
#      ，员工号（EMPLOYEE_ID），部门号(DEPARTMENT_ID)
 CREATE VIEW employee_vu
 AS
 SELECT last_name,employee_id,department_id
 FROM atguigudb.`employees`;
 
 
 #2. 显示视图的结构
  DESC employee_vu;
#3. 查询视图中的全部内容
   SELECT *FROM employee_vu;
#4. 将视图中的数据限定在部门号是80的范围内
    ALTER VIEW employee_vu
    AS
   SELECT last_name,employee_id,department_id
    FROM atguigudb.`employees`
    WHERE  department_id=80;
 
 
练习2 
题目：
CREATE TABLE emps1
 AS
 SELECT * FROM atguigudb.employees;
 
 SELECT *FROM emps1;
 #1. 创建视图emp_v1,要求查询电话号码以‘011’开头的员工姓名和工资、邮箱
   CREATE VIEW emp_v1 
   AS 
   SELECT last_name,salary,email
   FROM emps1 
   WHERE phone_number LIKE '011%';
   
   SELECT *FROM emp_v1;
#2. 要求将视图 emp_v1 修改为查询电话号码以‘011’开头的并且
#    邮箱中包含 e 字符的员工姓名和邮箱、电话号码
    ALTER VIEW emp_v1 
    AS 
    SELECT  last_name,salary,email,phone_number
    FROM emps1 
    WHERE phone_number LIKE '011%' AND email LIKE '%e%' ;
   
#3. 向 emp_v1 插入一条记录，是否可以？
    DESC emps;
 DESC emp_v1;
 # 实测：失败了
 INSERT INTO emp_v1(last_name,salary,email,phone_number)
 VALUES('Tom',2300,'tom@126.com','1322321312');
#4. 修改emp_v1中员工的工资，每人涨薪1000 
     UPDATE  emp_v1 
     SET salary =salary+1000;
    
 #5. 删除emp_v1中姓名为Olsen的员工
     DELETE FROM emp_v1
     WHERE  last_name='Olsen';
#6. 创建视图emp_v2，要求查询部门的最高工资高于 12000 的部门id和其最高工资
      CREATE VIEW emp_v2  
      AS 
      SELECT  department_id,MAX(salary)
      FROM  emps
      GROUP BY department_id 
      HAVING  MAX(salary) >12000;
#7. 向 emp_v2 中插入一条记录，是否可以？
     SELECT *
     FROM emp_v2;
     #不可以
     INSERT INTO  emp_v2
     VALUE (400,18000)
#8. 删除刚才的emp_v2 和 emp_v1
     DROP VIEW  emp_v2,emp_v1;