#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;
)

mySQL__View课堂笔记和练习