#引入#案例:查询姓张的学生名和专业名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%';SELECT*FROM myv1 WHERE last_name LIKE'%b%';#2.查询各部门的平均工资级别#①创建视图查看每个部门的平均工资CREATEVIEW myv2
ASSELECTAVG(salary) ag,department_id
FROM employees
GROUPBY 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 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;#方式二:/*
语法:
alter view 视图名
as
查询语句;
*/ALTERVIEW myv3
ASSELECT*FROM employees;#三、删除视图/*
语法:drop view 视图名,视图名,...;
*/DROPVIEW emp_v1,emp_v2,myv3;#四、查看视图DESC myv3;SHOWCREATEVIEW myv3;#五、视图的更新
引入
CREATEORREPLACEVIEW myv1
ASSELECT last_name,email,salary*12*(1+IFNULL(commission_pct,0))"annual salary"FROM employees;CREATEORREPLACEVIEW myv1
ASSELECT last_name,email
FROM employees;SELECT*FROM myv1;SELECT*FROM employees;#1.插入INSERTINTO myv1 VALUES('张飞','zf@qq.com');#2.修改UPDATE myv1 SET last_name ='张无忌'WHERE last_name='张飞';#3.删除DELETEFROM myv1 WHERE last_name ='张无忌';#具备以下特点的视图不允许更新#①包含以下关键字的sql语句:分组函数、distinct、group by、having、union或者union allCREATEORREPLACEVIEW 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;#④joinCREATEORREPLACEVIEW 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';