13 视图

视图

含义：虚拟表，和普通表一样使用

并且是在使用视图时动态生成的，只保存了sql逻辑，不保存查询结果

#查询姓张的学生名和专业名
CREATE VIEW v1
AS
SELECT stuname,majorname
FROM stuinfo s
INNER JOIN major m ON s.majorid=m.id

SELECT * FROM v1 WHERE stuname LIKE '张%';

​ 创建语法的关键字 是否实际占用物理空间 使用

视图 create view 只是保存了sql逻辑 增删改查，一般不能增删改

表 create table 保存了数据 增删改查

一、创建视图

语法：

create view 视图名

as

查询语句

#1.查询姓名中包含a字符的员工名，部门名和工种信息
#①创建
CREATE VIEW myv1
AS
SELECT last_name,department_name,job_title
FROM employees e
JOIN departments d ON e.department_id=d.department_id
JOIN jobs j ON j.job_id=e.job_id;
#②使用
SELECT * FROM myv1 WHERE last_name LIKE '%a%';


#2.查询各部门的平均工资级别
#创建视图查看每个部门的平均工资
CREATE VIEW myv2
AS
SELECT AVG(salary) ag,department_id
FROM employees
GROUP BY department_id;

SELECT myv2.`ag`,g.grade_level
FROM myv2;
JOIN job_grades g
ON myv2.`ag` BETWEEN g.lowest_sal AND g.highest_sal;


#3.查询平均工资最低的部门信息
SELECT * FROM myv2 ORDER BY ag LIMIT 1;


#4.查询平均工资最低的部门名和工资
CREATE VIEW myv3
AS
SELECT * FROM myv2 ORDER BY ag LIMIT 1; 
 
SELECT d.*,m.ag
FROM myv3 m
JOIN departments d
ON m.department_id=d.`department_id`;

好处：

• 重用sql语句
• 简化复杂的sql操作，不必知道它的查询细节
• 保护数据，提高安全性

二、视图的修改

方式一：

create or replace view 视图名

as

查询语句;

CREATE OR REPLACE VIEW myv3
AS
SELECT AVG(salary),job_id
FROM employees
GROUP BY job_id;

方式二：

语法：

alter view 视图名

as

查询语句

ALTER VIEW myv3
AS
SELECT * FROM employees;

三、删除视图

语法：

drop view 视图名,视图名，....,

DROP VIEW myv1,myv2,myv3;

四、查看视图

DESC myv3;

SHOW CREATE VIEW myv3;

五、视图的更新

这样修改也会修改表中的数据

#1.插入
INSERT INTO  myv1 VALUES('张飞','zz@qq.com');

#2.修改
UPDATE myv1 SET last_name='张无忌' WHERE last_name = '张飞';

#3.删除
DELETE FROM myv1 WHERE last_name='张无忌';

具备以下特点的视图不允许更新

① 包含以下关键字的SQL语句：分组函数、DISTINCT、GROUP BY、HAVING、UNIQUE或者UNIQUE ALL

#会报错因为有max函数和分组
CREATE OR REPAIR VIEW myv1
AS
SELECT MAX(salary) m,department_id
FROM employees
GROUP BY department_id;

UPDATE myv1 SET m=900 WHERE department_id=10;

② 常量视图: select 后面是个常量

#举例
CREATE OR REPAIR myv2
AS
SELECT 'john' NAME;

UPDATE myv2 SET NAME='luccy';

③ SELECT 中包含子查询

④JOIN

#举例
CREATE OR REPAIR myv3
AS
SELECT (SELECT MAX(salary) FROM employees) 最高工资;

UPDATE myv3 SET 最高工资=1000;

⑤FROM后一个不能更新的视图

#报错 is not updatable
CREATE OR REPAIR myv4
AS
SELECT * FROM myv3;

UPDATE myv5 SET  最高工资=1000 WHERE department_id=60;

⑥ WHERE子句的子查询引用了FROM子句中的表

CREATE OR REPAIR myv5
AS
SELECT employee_id IN(
	SELECT manger_id
	FROM employees
	WHERE manager_id IS NOT NULL
);

UPDATE myv5 SET salary=10000;