#一、创建视图
/
语法：
create view 视图名
as
查询语句;
/

案例1：查询姓名中包含a字符的员工名、部门名和工种信息
#①创建视图
CREATE VIEW myv1
AS
SELECT last_name,department_name,job_title
FROM employees AS e
JOIN departments AS d
ON e.department_id = d.department_id
JOIN jobs AS j
ON j.job_id = e.job_id;

#②使用视图
SELECT *
FROM myv1
WHERE last_name LIKE ‘%a%’;

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

#②使用视图
SELECT myv2.ag ,g.grade_level
FROM myv2
JOIN job_grades AS g
ON myv2.ag BETWEEN g.lowest_sal AND g.highest_sal;

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

#4.查询平均工资最低的部门名和工资
#①创建视图
CREATE VIEW myv3
AS
SELECT
FROM myv2
ORDER BY ag ASC
LIMIT 0,1;
#②使用视图
SELECT d.,m.ag
FROM myv3 AS m
JOIN departments AS d
ON m.department_id = d.department_id;