143-视图练习，视图和表的区别

一、创建视图emp_v1，要求查询电话号码以011开头的员工姓名和工资、邮箱；

CREATE OR REPLACE VIEW myv1
AS
SELECT `last_name`,`salary`,`email`,`phone_number`
FROM employees
WHERE `phone_number` LIKE '011%';

SELECT * FROM myv1;

二、创建视图emp_v2，要求查询部门的最高工资高于12000的部门信息

1\不用视图

select d.*,m.mx_dep
from departments d
join(
	select max(salary) mx_dep,department_id
	from employees
	group by department_id
	having max(salary)>12000
) m
on m.department_id=d.department_id;

2、用视图

CREATE OR REPLACE myv2
AS
SELECT MAX(`salary`) mx_dep,department_id
FROM `employees`
GROUP BY `department_id`
HAVING mx_dep>12000;

select d.*,myv2.mx_dep
from departments d
join myv2
on myv2.department_id=d.department_id;