mysql 视图

/*
 视图：一种虚拟存在的表
     行和列的数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的，
     只保存了sql逻辑，不保存查询结果
 
 应用场景：
     多个地方用到同样的查询结果
     该查询结果使用的sql语句较复杂 
 优点：
    重用sql语句
    简化了sql操作
    保护数据，提高了安全性
 视图和表的区别：
    创建语法的关键字    是否实际占用空间    使用
 视图 create view     只是保存了sql逻辑  增删改查，一般不能增删改
 表  create table        保存了数据       增删改查
*/

CREATE VIEW my_v1
AS
SELECT studentname,majorname
FROM student s
INNER JOIN major m
ON s.majorid = m.majorid;

SELECT * FROM vc WHERE studentname LIKE '张%';

#一、创建视图
/*
  create view 视图名
  as
  查询语句;
  
*/
#1. 查询姓名中包含a字符的员工名、部门名和工种信息
CREATE VIEW myv1
AS

SELECT last_name,department_name,job_title
FROM departments
INNER JOIN employees
ON employees.`department_id` = departments.`department_id`
INNER JOIN jobs
ON employees.`job_id` = jobs.`job_id`;

SELECT * FROM myv1
WHERE myv1.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.*
 FROM myv3 m
 INNER JOIN departments d
 ON m.department_id = d.department_id;

#2. 视图的修改
/*
  方式一：
  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
  查询语句
*/
CREATE OR REPLACE VIEW myv3
AS
SELECT *
FROM employees;

# 三 删除视图
/*
 drop view 视图名，视图名，..;
 
*/
DROP VIEW myv1;

# 四 查看视图
DESC myv3;
SHOW CREATE VIEW myv3; #命令行信息较全

#练习

#创建视图emp_v1, 要求查询电话号码以011开头的员工姓名和工资 邮箱
CREATE OR REPLACE VIEW emp_v1
AS
SELECT e.last_name, e.salary,e.email, e.phone_number
FROM employees e;

SELECT * FROM emp_v1;

SELECT * 
FROM emp_v1 e
WHERE e.phone_number LIKE '011%';

# 创建视图emp_v2,要求查询部门的最高工资高于12000的部门信息
DROP VIEW emp_v2;
CREATE VIEW emp_v2
AS
SELECT MAX(salary) s ,department_id d
FROM employees
GROUP BY department_id;
HAVING s>12000;

SELECT * FROM emp_v2;

SELECT e.*,departments.*
FROM emp_v2 e
INNER JOIN departments
ON departments.`department_id` = e.`d`
WHERE e.s>12000;

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

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

SELECT * FROM myv1;

# 插入视图
INSERT INTO myv1 VALUES('张飞','123@132.com');

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

SELECT * FROM myv1 WHERE last_name='张无忌';

# 删除
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子句中的表
*/

#1. 包含以下关键字的sql语句：distinct  group by having union union all
   CREATE OR REPLACE VIEW myv1
   AS
   SELECT MAX(salary) s, department_id
   FROM employees
   GROUP BY department_id;  
    
   SELECT * FROM myv1;
   
   UPDATE myv1 SET  s = 1 WHERE department_id=10; #The target table myv1 of the UPDATE is not updatable
# 2. 常量视图
   CREATE OR REPLACE VIEW myv2
   AS
   SELECT 'john' NAME;
   SELECT * FROM myv2;
   UPDATE myv2 SET NAME='lucy';
   
 #3. select中包含子查询 
   CREATE OR REPLACE VIEW myv3
   AS 
   SELECT (SELECT MAX(salary) FROM employees);
   SELECT * FROM myv3;

#4. join
   CREATE OR REPLACE VIEW myv4
   AS 
   SELECT last_name,department_name
   FROM  employees e
   JOIN departments d
   ON d.department_id = e.department_id;
    SELECT * FROM myv4;
    
 #5. from一个不可更新的视图
   CREATE OR REPLACE VIEW myv5
   AS 
   SELECT * FROM myv5;
   
 #6.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;