视图

视图

定义

/*
虚拟的表，使用方法同表
行和列的数据来自 定义视图的查询中使用的表，
并且是再使用视图时动态生成的，
只保存了sql逻辑，不保存查询结果
相当于把查询语句包装好，方便下次直接使用
*/

与表的区别

/*
	    创建语法的关键字	  是否实际占用物理空间	   使用

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

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

*/

创建

# 创建视图
/*
语法：
create view
as 查询语句
*/
USE myemployees;

# 案例一： 查询工资>4000的员工名，工资和其所对应的部门名
-- 直接查询
SELECT e.last_name, e.salary, d.department_name 
FROM employees e
JOIN departments d
ON e.department_id = d.department_id
WHERE e.salary > 4000

-- 使用视图
# 封装语句
CREATE VIEW v1
AS
SELECT e.last_name, e.salary, d.department_name 
FROM employees e
JOIN departments d
ON e.department_id = d.department_id;

# 执行
SELECT * FROM v1 WHERE salary > 4000;



# 案例二：查询各部门的平均工资级别

# 1. 创建视图查看每个部门的平均工资
CREATE VIEW myv2
AS
SELECT AVG(salary) ag,department_id
FROM employees
GROUP BY department_id;

# 2. 使用
SELECT myv2.`ag`,g.grade_level
FROM myv2
JOIN job_grades g
ON myv2.`ag` BETWEEN g.`lowest_sal` AND g.`highest_sal`;

修改

二。视图修改

/*
方式一
create or replace view 视图名
as 
查询语句;
*/

# 以案例一为例，增加一条job_id小于100的条件
CREATE OR REPLACE VIEW v1
AS 
SELECT e.last_name, e.salary, d.department_name 
FROM employees e
JOIN departments d
ON e.department_id = d.department_id
WHERE e.job_id < 100;


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

# 以案例一为例，增加一条job_id小于100的条件
ALTER VIEW v1
AS 
SELECT e.last_name, e.salary, d.department_name 
FROM employees e
JOIN departments d
ON e.department_id = d.department_id
WHERE e.job_id < 100;

删除

/*
语法：drop view 视图名,视图名.....
*/

DROP VIEW v1;

查看

-- 方式1
DESC v1;

-- 方式2
SHOW CREATE VIEW v1;

更新

# 与修改不通，指更新 视图中内容 ，包括 插入数据，修改数据，删除数据
# 一般来说视图不允许更新
# 创建一个v1视图
CREATE OR REPLACE VIEW v1 
AS 
SELECT last_name, email
FROM employees;

SELECT * FROM v1;

# 1. 插入

INSERT INTO v1 VALUES('侠奢', 2333);

# 2. 修改

UPDATE v1 SET last_name = '猪猪' WHERE last_name = '侠奢';

# 3. 删除

DELETE FROM v1 WHERE last_name= '猪猪';







-----------------------具备以下特点的视图不允许更新-------------------------


-- 1. 包含以下关键字的sql语句：分组函数、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;


# 更新 -- 无法更新
UPDATE myv1 SET m=9000 WHERE department_id=10;


-- 2. 常量视图

# 创建常量视图
CREATE OR REPLACE VIEW myv2
AS

SELECT 'john' NAME;


# 更新 -- 无法更新
UPDATE myv2 SET NAME='lucy';


-- 3. Select中包含子查询

CREATE OR REPLACE VIEW myv3
AS

SELECT department_id,(SELECT MAX(salary) FROM employees) 最高工资
FROM departments;


#更新 -- 无法更新
UPDATE myv3 SET 最高工资=100000;


-- 4. join
CREATE OR REPLACE VIEW myv4
AS

SELECT last_name,department_name
FROM employees e
JOIN departments d
ON e.department_id  = d.department_id;

# 更新 -- 无法更新
UPDATE myv4 SET last_name  = '张飞' WHERE last_name='Whalen';
INSERT INTO myv4 VALUES('陈真','xxxx');



-- 5. from一个不能更新的视图
CREATE OR REPLACE VIEW myv5
AS

SELECT * FROM myv3;

# 更新 -- 无法更新
UPDATE myv5 SET 最高工资=10000 WHERE department_id=60;



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

# 更新 -- 无法更新
UPDATE myv6 SET salary=10000 WHERE last_name = 'k_ing';

/