视图的更新

#五、视图的更新
DROP VIEW myv3,emp_v2;
CREATE   OR REPLACE VIEW myv1
AS
SELECT  last_name,email,salary*12*(1+IFNULL(commission_pct,0))  "annual salary"
FROM employees;

SELECT * FROM myv1;
SELECT * FROM employees;
CREATE   OR REPLACE VIEW myv1
AS
SELECT  last_name,email
FROM employees;

#1.插入
INSERT INTO myv1 VALUES('张艺兴','lay@qq.com');
#2.修改
UPDATE myv1 SET last_name='Lay' WHERE last_name='张艺兴';
#3.删除
DELETE FROM myv1 WHERE last_name='Lay';

#1.演示包含group by 关键字
CREATE OR REPLACE VIEW  myv2
AS
SELECT MAX(salary) m,department_id
FROM employees
GROUP BY department_id;
SELECT * FROM myv2;

#更新
UPDATE myv2 SET m=9000 WHERE department_id=10;
#2.常量视图
CREATE OR REPLACE VIEW myv3
AS
SELECT 'john'  NAME;
SELECT * FROM myv3;
#更新
UPDATE myv3 SET 
NAME='lay';
#select中包含子查询
CREATE OR REPLACE VIEW myv4
AS
SELECT (
         SELECT MAX(salary) FROM employees
) 最高工资;
SELECT * FROM myv4;
#更新
UPDATE myv4 SET 最高工资=10000;
#4.join
CREATE OR REPLACE VIEW myv5
AS
SELECT last_name,department_name
FROM employees e
JOIN departments d
ON e.department_id=d.department_id;
SELECT * FROM myv5;
#更新
UPDATE  myv5 SET last_name='张艺兴' WHERE last_name='Fay';
INSERT INTO myv5 VALUES('兴行','ssss');
#5.from一个不能更新的视图
DROP VIEW myv6;
CREATE OR REPLACE VIEW myv6
AS
SELECT * FROM myv4;

#更新
SELECT * FROM myv6;
UPDATE myv6 SET 最高工资=200000 ;

#6.where子句的子查询引用了from子句中的表
CREATE OR REPLACE VIEW myv7
AS
SELECT last_name,email,salary
FROM employees
WHERE employee_id IN(
      SELECT manager_id
      FROM employees
      WHERE manager_id IS NOT NULL

);
SELECT * FROM myv7;
#更新
UPDATE myv7 SET salary=100000 WHERE last_name='De Haan';