数据库MySQL-视图

数据库MySQL-视图

#视图

含义：虚拟表，和普通表一样使用，行和列的数据来自定义视图的查询中使用的表，
并且实在使用视图时动态生成的，只保存了sql逻辑，不保存查询结果。
mysql 5.1版本出现的新特性，是通过表动态生成的数据

好处：
重用sql语句
简化复杂的sql操作，不必知道它的查询细节
保护数据，提高安全性
	创建关键字      是否实际占用物理空间   使用
视图    create view      只是保存了sql逻辑     增删改查，只是一般不能增删改
表      create table     保存了数据            增删改查
 
*/
#案例：查询姓张的学生名和专业名
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`;

一、创建视图

语法：
create view 视图名
as
查询语句;
*/
USE `myemployees`;
#1、查询邮箱中包含a字符的员工名、部门名和工种信息

创建：
CREATE VIEW myv1
AS

SELECT `last_name`,`department_name`,`job_title`
FROM `employees` e INNER JOIN `departments` d
ON e.`department_id` = d.`department_id`
INNER 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 * FROM myv2;

#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
INNER JOIN `departments` d
ON m.`department_id` = d.`department_id`;

二、视图的修改
#方式一：
/*
create or replace view 视图名
as
查询语句;
*/

#方式二：
/*
语法：
alter view 视图名
as
查询语句;

*/

三、删除视图
/*
语法：
drop view 视图名，视图名，...;
*/
DROP VIEW myv1,myv2,myv3;

四、查看视图
DESC myv3;

SHOW CREATE VIEW myv3;

五、视图的更新
CREATE OR REPLACE VIEW myv1
AS
SELECT last_name,email
FROM employees;

#1、插入
INSERT INTO myv1 VALUES('张飞','zf8qq.com');

#2、修改
UPDATE myv1 SET last_name='张无忌' WHERE last_name='张飞';

#3、删除
DELETE FROM myv1 WHERE last_name='张无忌';

具备一下特点的视图不允许更新
1、包含以下关键字的sql语句：分组函数、distinct、group by、having、union或者union all
2、常量视图
3、select中包含子查询
4、join
5、from一个不能更新的视图
6、where子句的子查询引用了from子句中的表
*/