#step16_View.sql
#视图
/*
含义：本身是一个虚拟表，它的数据来源于表，通过执行时动态生成
好处：
简化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 '张%'
#将一部分语句封装成v1，那么这就是一个视图了
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 myv2.department_id
FROM myv2
ORDER BY myv2.ag ASC
LIMIT 1;
#查询平均工资最低的部门名和工资
CREATE VIEW myv3
AS
SELECT * FROM myv2 ORDER BY ag LIMIT 1;
SELECT department_name,ag
FROM myv3
INNER JOIN departments d
ON myv3.department_id=d.`department_id`
#二、视图的修改
#方式1：
/*
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;
#方式2：
/*
语法：
alter view 视图名
as
查询语句;
*/
ALTER VIEW myv3
AS
SELECT * FROM employees;
#三、删除视图
/*
语法：drop view 视图名，视图名,...;
(root用户是有这个权限的)
*/
DROP VIEW myv1,myv2,myv3;
#四、查看视图的结构(字段、字段类型、默认值、是否为null等)
CREATE OR REPLACE VIEW myv3
AS
SELECT AVG(salary),job_id
FROM employees
GROUP BY job_id;
#查看
DESC myv3;
#查看(信息不全，但是在cmd中运行会特别全)
SHOW CREATE VIEW myv3;
#【案例】
#1.创建视图emp_v1,要求查询电话号码以011开头的员工姓名、工资、邮箱
CREATE VIEW emp_v1
AS
SELECT last_name,salary,email
FROM employees e
WHERE e.phone_number LIKE '011%';
SELECT * FROM emp_v1;
#2.创建视图emp_v2,要求查询部门最高工资高于12000的部门信息
CREATE VIEW emp_v2
AS
SELECT MAX(salary),department_id
FROM employees
GROUP BY department_id
HAVING MAX(salary)>12000
SELECT d.*
FROM emp_v2
INNER JOIN departments d
WHERE emp_v2.department_id=d.department_id
DROP VIEW emp_v1,emp_v2,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;
#1.插入
INSERT INTO myv1 VALUES('张飞','zf@qq.com');
SELECT * FROM myv1;
SELECT * FROM employees;#原始表中也插入了
#2.修改
UPDATE myv1 SET last_name='张无忌' WHERE last_name='张飞';
SELECT * FROM myv1;
SELECT * FROM employees;#原始表中也修改了
#3.删除
DELETE FROM myv1 WHERE last_name='张无忌';
SELECT * FROM myv1;
SELECT * FROM employees;#原始表中也删除了
/*
上述这样往往不安全，所以一般会为视图添加只读的权限，即便不设置权限，只要视图中包含
一下语句则也是无效的。
（1）包含以下关键字的sql语句：分组函数、distinct、group by、having、union或者union all
（2）常量视图
（3）Select中包含子查询
（4）join
（5）from一个不能更新的视图
（6）where子句的子查询引用了from子句中的表
*/
#（1）分组函数、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;#报错
#（2）常量视图
CREATE OR REPLACE VIEW myv2
AS
SELECT 'join' NAME;#select后面就是一个常量
SELECT * FROM myv2;
UPDATE myv2 SET NAME='licy';#报错
#（3）略
#（4）略
#（5）不仅是join，只要是涉及到连接的都不可以，包括sql92语法中的用逗号连接
CREATE OR REPLACE VIEW myv4
AS
SELECT last_name,department_name
FROM employees e
JOIN departmnets d
ON e.department_id=d.department_id
SELECT * FROM myv4;
UPDATE myv4 SET last_name='张飞' WHERE last_name='what';#不报错
INSERT INTO myv4 VALUES('xxx','yyy');#报错
#（5）from一个不能更新的视图
CREATE OR REPLACE VIEW myv5
AS
SELECT * FROM myv1#from后面有一个不能更新的myv1
#（6）where子句的子查询引用了from子句中的表
CREATE OR REPLACE VIEW myv5
AS
SELECT last_name,email,salary
FROM employees
WHERE employee_id IN(
SELECT manager_id
FROM employees
WHERE manager_id IS NOT NULL;
)
