黑猴子的家：mysql 视图实操练习

1、创建视图emp_v1,要求查询电话号码以‘011’开头的员工姓名和工资、邮箱

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

2、要求将视图emp_v1修改为查询电话号码以‘011’开头的并且邮箱中包含e字符的员工姓名和邮箱、电话号码

CREATE OR REPLACE  VIEW emp_v1
AS
SELECT last_name,email,phone_number
FROM employees
WHERE phone_number LIKE '011%' AND email LIKE '%e%';
SELECT * FROM employees;

3、向emp_v1插入一条记录，是否可以？

可以

INSERT INTO emp_v1 VALUES('john','sdfe','0112345');

4、修改刚才记录中的电话号码为‘119’

UPDATE emp_v1 SET phone_number ='119' WHERE last_name='john';

5、删除刚才记录

DELETE FROM emp_v1 WHERE last_name='';

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

SELECT *
FROM departments 
WHERE department_id IN(
    SELECT department_id
    FROM employees
    GROUP BY department_id
    HAVING MAX(salary)>12000
) 

CREATE OR REPLACE VIEW emp_v2
AS
SELECT *
FROM departments
WHERE department_id IN(
    SELECT department_id
    FROM employees
    GROUP BY department_id
    HAVING MAX(salary)>12000
) 

SELECT * FROM emp_v2;

7、向emp_v2中插入一条记录，是否可以？

INSERT INTO emp_v2 VALUES(999,'dd',201,1700);

8、删除刚才的emp_v2和emp_v1

DROP VIEW emp_v2,emp_v1;