十一、MySQL视图学习笔记（详解）数据库weixin44827418的博客-

视图

含义：理解成一张虚拟的表，和普通表一样使用
mysql5.1版本出现的新特性，是通过表动态生成的数据
比如：舞蹈班和普通班级的对比

视图和表的区别：

视图的好处：

1、sql语句提高重用性，效率高 2、和表实现了分离，提高了安全性 

视图的创建

语法： CREATE VIEW  视图名 AS 查询语句; 

视图的增删改查

1、查看视图的数据 ★  SELECT * FROM my_v4; SELECT * FROM my_v1 WHERE last_name='Partners';  2、插入视图的数据 INSERT INTO my_v4(last_name,department_id) VALUES('虚竹',90);  3、修改视图的数据  UPDATE my_v4 SET last_name ='梦姑' WHERE last_name='虚竹';   4、删除视图的数据 DELETE FROM my_v4; 

某些视图不能更新

包含以下关键字的sql语句：分组函数、distinct、group  by、having、union或者union all 常量视图 Select中包含子查询 join from一个不能更新的视图 where子句的子查询引用了from子句中的表 

视图逻辑的更新

#方式一： CREATE OR REPLACE VIEW test_v7 AS SELECT last_name FROM employees WHERE employee_id>100;  方式二: ALTER VIEW test_v7 AS SELECT employee_id FROM employees;  SELECT * FROM test_v7; 

视图的删除

DROP VIEW test_v1,test_v2,test_v3; 

视图结构的查看

DESC test_v7; SHOW CREATE VIEW test_v7; 

一、创建视图

语法：
create view 视图名
as
查询语句;

#案例：查询姓张的学生名和专业名 SELECT stuname,majorname FROM stuinfo s INNER JOIN major m ON s.`majorid`= m.`id` WHERE s.`stuname` LIKE '张%'; CREATE VIEW v1 AS SELECT stuname,majorname FROM stuinfo s INNER JOIN major m ON s.`majorid`= m.`id`; SELECT * FROM v1 WHERE stuname LIKE '张%'; #一、创建视图 /* 语法： create view 视图名 as 查询语句;  */ USE myemployees; #1.查询姓名中包含a字符的员工名、部门名和工种信息 #①创建 CREATE VIEW myv1 AS SELECT 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%'; #2.查询各部门的平均工资级别 #①创建视图查看每个部门的平均工资 CREATE VIEW myv2 AS SELECT AVG(salary) ag,department_id FROM employees GROUP BY 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 ORDER BY ag LIMIT 1; #4.查询平均工资最低的部门名和工资 CREATE VIEW myv3 AS SELECT * FROM myv2 ORDER BY ag LIMIT 1; SELECT d.*,m.ag FROM myv3 m JOIN departments d ON m.`department_id`=d.`department_id`; 

二、视图的修改

方式一：

语法：
create or replace view 视图名
as
查询语句;

方式二：

语法：
alter view 视图名
as
查询语句;

#二、视图的修改 #方式一： /* create or replace view  视图名 as 查询语句;  */ SELECT * FROM myv3   CREATE OR REPLACE VIEW myv3 AS SELECT AVG(salary),job_id FROM employees GROUP BY job_id; #方式二： /* 语法： alter view 视图名 as  查询语句;  */ ALTER VIEW myv3 AS SELECT * FROM employees; 

三、删除视图

语法：drop view 视图名,视图名,…;

#三、删除视图 /*  语法：drop view 视图名,视图名,...; */ DROP VIEW emp_v1,emp_v2,myv3; 

四、查看视图

#四、查看视图 DESC myv3; SHOW CREATE VIEW myv3; 

五、视图的更新

#五、视图的更新 CREATE OR REPLACE VIEW myv1 AS SELECT last_name,email,salary*12*(1+IFNULL(commission_pct,0)) "annual salary" FROM employees; CREATE OR REPLACE VIEW myv1 AS SELECT last_name,email FROM employees; SELECT * FROM myv1; SELECT * FROM employees; #1.插入 INSERT INTO myv1 VALUES('张飞','zf@qq.com'); #2.修改 UPDATE myv1 SET last_name = '张无忌' WHERE last_name='张飞'; #3.删除 DELETE FROM myv1 WHERE last_name = '张无忌'; #具备以下特点的视图不允许更新 #①包含以下关键字的sql语句：分组函数、distinct、group  by、having、union或者union all CREATE OR REPLACE VIEW myv1 AS SELECT MAX(salary) m,department_id FROM employees GROUP BY department_id; SELECT * FROM myv1; #更新 UPDATE myv1 SET m=9000 WHERE department_id=10; #②常量视图 CREATE OR REPLACE VIEW myv2 AS SELECT 'john' NAME; SELECT * FROM myv2; #更新 UPDATE myv2 SET NAME='lucy'; #③Select中包含子查询 CREATE OR REPLACE VIEW myv3 AS SELECT department_id,(SELECT MAX(salary) FROM employees) 最高工资 FROM departments; #更新 SELECT * FROM myv3; UPDATE myv3 SET 最高工资=100000; #④join CREATE OR REPLACE VIEW myv4 AS SELECT 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'; INSERT INTO myv4 VALUES('陈真','xxxx'); #⑤from一个不能更新的视图 CREATE OR REPLACE VIEW myv5 AS SELECT * FROM myv3; #更新 SELECT * FROM myv5; UPDATE myv5 SET 最高工资=10000 WHERE department_id=60; #⑥where子句的子查询引用了from子句中的表 CREATE OR REPLACE VIEW myv6 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 myv6; UPDATE myv6 SET salary=10000 WHERE last_name = 'k_ing';