MYSQL初阶学习笔记——视图

#视图
/*
含义：虚拟表，和普通表一样使用，mysql5.0.1版本开始的特性
      行和列的数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的，只保存sql逻辑，不保存查询结果。

应用场景：
    -多个地方用到同样的查询结果
    -该查询结果使用的sql语句较复杂
特点：
     1.重用sql语句
     2.简化复杂的sql操作
     3.保护数据，提高安全性
    
    
    创建语法的关键字    是否实际占用物理空间    使用

视图    create view        只是保存了sql逻辑    增删改查，只是一般不能增删改

表    create table        保存了数据        增删改查
*/

#案例1：查询job_id为AD_VP的员工名和job_title
USE myemployees;

SELECT last_name,job_title,salary
FROM employees e
INNER JOIN jobs j ON e.`job_id`=j.`job_id`
WHERE e.`job_id`='AD_VP';

##用视图方式完成

CREATE VIEW temp1
AS
SELECT last_name,job_title,salary
FROM employees e
INNER JOIN jobs j ON e.`job_id`=j.`job_id`
WHERE e.`job_id`='AD_VP';

SELECT * FROM temp1;

#一、创建视图
/*
语法：
create view 视图名
as
查询语句;

*/
#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 e.job_id=j.job_id;

##选择筛选
SELECT * FROM myv1 WHERE last_name LIKE '%a%';

#2.查询各部门的平均工资级别

##创建各部门平均工资视图
CREATE VIEW myv2
AS
SELECT AVG(salary) ag,department_id
FROM employees e
GROUP BY e.department_id;

##非等值连接
SELECT myv2.*,g.`grade_level`
FROM myv2
INNER JOIN job_grades g 
ON myv2.ag BETWEEN g.`lowest_sal` AND g.`highest_sal`;

#3.查询平均工资最低的部门id

SELECT * FROM myv2 ORDER BY ag LIMIT 1;

#4.查询平均工资最低的部门名和工资


SELECT department_name,myv2.ag
FROM myv2
JOIN departments d
ON myv2.`department_id`=d.`department_id`
ORDER BY ag
LIMIT 1;


#二、视图的修改

/*
方式一：
语法：
create or replace view 视图名
as
查询语句;
###存在则修改，不存在则创建

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

*/

SELECT * FROM myv3;

#方式一
CREATE OR REPLACE VIEW myv3
AS
SELECT AVG(salary) ag,job_id
FROM employees
GROUP BY job_id;

#方式二
ALTER VIEW myv3
AS
SELECT * FROM employees;

#三、删除视图
/*
语法：（可多个一次删除）
drop view 视图名,视图名,……;

*/
DROP VIEW temp1;

#四、查看视图
/*
desc 视图名
show create view 视图名

*/
DESC temp1;
SHOW CREATE VIEW myv3;

#=======================Test=================

#1、创建视图emp_v1,要求查询电话号码以‘011’开头的员工姓名和工资、邮箱

##1

CREATE OR REPLACE VIEW emp_v1
AS
SELECT last_name,salary,email,phone_number
FROM employees
WHERE phone_number LIKE '011%';

SELECT * FROM emp_v1;

#2、创建视图emp_v2，要求查询部门的最高工资高于12000的部门信息
USE myemployees;
##1部门最高工资
CREATE OR REPLACE VIEW emp_v2
AS
SELECT MAX(salary) maxsa,department_name
FROM employees e
JOIN departments d ON e.`department_id`=d.`department_id`
GROUP BY e.department_id;

##2选高于12000的信息
SELECT emp_v2.maxsa,d.*
FROM emp_v2
JOIN departments d ON emp_v2.`department_name`=d.`department_name`
WHERE emp_v2.maxsa>12000;
#===========================end Test==========================

#五、视图的更新
DROP VIEW myv1,myv2,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 VALUE("张飞","zhangfei1");
 
#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子句中的表

*/