#案例:查询姓张的学生名和专业名#没用视图SELECT stuname,majorname
FROM stuinfo s
INNERJOIN major m ON s.`majorid`= m.`id`WHERE s.`stuname`LIKE'张%';#用了视图CREATEVIEW v1
ASSELECT stuname,majorname
FROM stuinfo s
INNERJOIN major m ON s.`majorid`= m.`id`;SELECT*FROM v1 WHERE stuname LIKE'张%';
一、创建视图
/*
语法:
create view 视图名
as
查询语句;
*/
USE myemployees;
1.查询姓名中包含a字符的员工名、部门名和工种信息
#①创建CREATEVIEW myv1
ASSELECT 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.查询各部门的平均工资级别
#①创建视图查看每个部门的平均工资CREATEVIEW myv2
ASSELECTAVG(salary) ag,department_id
FROM employees
GROUPBY department_id;#②使用SELECT*FROM myv2;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 ORDERBY ag LIMIT1;
4.查询平均工资最低的部门名和工资
CREATEVIEW myv3
ASSELECT*FROM myv2 ORDERBY ag LIMIT1;SELECT d.*,m.ag
FROM myv3 m
JOIN departments d
ON m.`department_id`=d.`department_id`;
二、视图的修改
方式一:
/*
语法:
create or replace view 视图名
as
查询语句;
*/
SELECT*FROM myv3;CREATEORREPLACEVIEW myv3
ASSELECTAVG(salary),job_id
FROM employees
GROUPBY job_id;
UPDATE myv1 SET last_name ='张无忌'WHERE last_name='张飞';
3.删除
DELETEFROM myv1 WHERE last_name ='张无忌';
具备以下特点的视图不允许更新
①包含以下关键字的sql语句:分组函数、distinct、group by、having、union或者union all
CREATEORREPLACEVIEW myv1
ASSELECTMAX(salary) m,department_id
FROM employees
GROUPBY department_id;SELECT*FROM myv1;#更新UPDATE myv1 SET m=9000WHERE department_id=10;#失败
②常量视图
CREATEORREPLACEVIEW myv2
ASSELECT'john' NAME;SELECT*FROM myv2;#更新UPDATE myv2 SET NAME='lucy';#失败
③Select中包含子查询
CREATEORREPLACEVIEW myv3
ASSELECT department_id,(SELECTMAX(salary)FROM employees) 最高工资
FROM departments;SELECT*FROM myv3;#更新UPDATE myv3 SET 最高工资=100000;#失败
④join
CREATEORREPLACEVIEW myv4
ASSELECT last_name,department_name
FROM employees e
JOIN departments d
ON e.department_id = d.department_id;SELECT*FROM myv4;#更新UPDATE myv4 SET last_name ='张飞'WHERE last_name='Whalen';#成功INSERTINTO myv4 VALUES('陈真','xxxx');#失败
⑤from一个不能更新的视图
CREATEORREPLACEVIEW myv5
ASSELECT*FROM myv3;SELECT*FROM myv5;#更新UPDATE myv5 SET 最高工资=10000WHERE department_id=60;#失败
⑥where子句的子查询引用了from子句中的表
CREATEORREPLACEVIEW myv6
ASSELECT last_name,email,salary
FROM employees
WHERE employee_id IN(SELECT manager_id
FROM employees
WHERE manager_id ISNOTNULL);SELECT*FROM myv6;#更新UPDATE myv6 SET salary=10000WHERE last_name ='k_ing';#失败