视图

一、视图

1.1.什么是视图？

视图：MySQL从5.0.1版本开始提供视图功能。是一张一种虚拟存在的表，行和列的数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的，只保存了sql逻辑，不保存查询结果

应用场景：

多个地方用到同样的查询结果
该查询结果使用的sql语句较复杂

# 创建视图实现
CREATE VIEW myvw1 
AS
SELECT e.last_name,d.department_name,j.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

1.2.常见题目

1.查询邮箱中包含a字符的员工名、部门名和工种信息

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

3.查询平均工资最低的部门信息

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

-- 1.查询邮箱中包含a字符的员工名、部门名和工种信息
# 连接查询实现
SELECT e.last_name,d.department_name,j.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
WHERE last_name LIKE '%a%'

# 创建视图实现
CREATE VIEW myvw1 
AS
SELECT e.last_name,d.department_name,j.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 myvw1 WHERE last_name LIKE '%a%';

-- 2.查询各部门的平均工资级别
SELECT d.department_name as 部门名,AVG(salary) 平均薪资 FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id
GROUP BY d.department_id


# 创建视图
CREATE VIEW myvw2
AS
SELECT d.department_name as 部门名,AVG(salary) 平均薪资 FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id
GROUP BY d.department_id

# 利用视图平均薪资确定级别
SELECT myvw2.`部门名`,myvw2.`平均薪资`,j.grade_level AS 等级 FROM myvw2 
INNER JOIN job_grades j 
ON myvw2.`平均薪资` BETWEEN j.lowest_sal AND j.highest_sal


-- 3.查询平均工资最低的部门信息
SELECT 部门名 FROM myvw2
ORDER BY 平均薪资 DESC
LIMIT 0,1;


-- 4.查询平均工资最低的部门名和工资
SELECT 部门名,`平均薪资` FROM myvw2
ORDER BY 平均薪资 DESC
LIMIT 0,1;

1.3.视图的好处

重用sql语句
简化复杂的sql操作，不必知道它的查询细节
保护数据，提高安全性

二、创建或修改视图

创建视图的语法：

create [or replace] view view_name
As select_statement
[with|cascaded|local|check option]

修改视图的语法：

方法一：

alter view view_name
As select_statement
[with|cascaded|local|check option]

方式二：

create or replace view  视图名
as
查询语句;

案例：

# 创建视图实现
CREATE VIEW myvw1 
AS
SELECT e.last_name,d.department_name,j.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


# 修改视图方式一：
ALTER VIEW myvw1
AS
SELECT e.last_name,d.department_name,j.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
WHERE last_name LIKE '%a%'

# 修改视图方式二：
CREATE OR REPLACE VIEW myvw1
AS
SELECT e.last_name,d.department_name,j.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
WHERE last_name LIKE '%a%'

三、删除视图

删除视图的语法：用户可以一次删除一个或者多个视图，前提是必须有该视图的drop权限

drop view [if exists]  view_name,view_name …[restrict|cascade]

删除myvw1如下：

DROP VIEW IF EXISTS myvw1

四、查看视图

查看视图的语法：

DESC myvw2;

如果需要查询某个视图的定义，可以使用show create view 命令进行查看：

SHOW CREATE VIEW myvw2 \G;

注意：\G在有的客户端工具不需要添加

五、更新视图

视图的数据更新是有局限性的，下面是可以跟新的：

# 创建视图
CREATE VIEW myvw9 
AS
SELECT last_name,email,salary FROM employees

SELECT * FROM myvw9

# 修改数据
UPDATE myvw9 SET salary = 1690 WHERE last_name='K_ing'

视图的可更新性和视图中查询的定义有关系，以下类型的视图是不能更新的。

包含以下关键字的sql语句：分组函数、distinct、group by 、having、union或者union all
常量视图
Select中包含子查询
join
from一个不能更新的视图
where子句的子查询引用了from子句中的表

5.1.包含以下关键字的sql语句：分组函数、distinct、group by、having、union或者union all

# 创建视图，求每个部门的最高薪资
CREATE VIEW myvw3 
AS
SELECT MAX(salary) m,department_id FROM employees
GROUP BY department_id

# 查看视图
SELECT * FROM myvw3;

# 更新视图数据
UPDATE myvw3 SET m=15000 WHERE department_id=10

上面更新视图中的数据会报错，如下：

5.2.常量视图

# 创建视图
CREATE VIEW myvw4
AS
SELECT 'augus' `name`

# 查看视图
SELECT * FROM myvw4;

# 更新数据
UPDATE myvw4 SET `name`='tom'

上面更新视图中的数据会报错，如下：

5.3.Select中包含子查询

# 创建视图：子查询
CREATE VIEW myvw5
AS
SELECT department_id,(SELECT MAX(salary) FROM employees) 最高薪资 FROM departments;

# 查询
SELECT * FROM myvw5

# 更新数据
UPDATE myvw5 SET 最高薪资=1100 WHERE department_id=60

提示不可以更新：

5.4.join

CREATE VIEW myvw6
AS
SELECT last_name,department_name FROM
employees INNER JOIN departments
ON employees.department_id = departments.department_id

# 查看数据
SELECT * FROM myvw6;

# 更新数据是可以的
UPDATE myvw6 SET last_name = '无忌' WHERE last_name='Whalen'
# 插入数据不可以
INSERT INTO myvw6 VALUES('敏敏特穆尔','Pur');

视图中多表连接用到了join语句，更新数据是可以，插入数据会报错：

5.5.from一个不能更新的视图

如果视图引用了其他不可更新的视图，那么它本身也将不可更新。

# 通过视图myvw6创建视图myvw7
CREATE VIEW myvw7
AS
SELECT * FROM myvw5

# 查看视图
SELECT * FROM myvw7

# 更新数据会报错
UPDATE myvw7 SET 最高薪资 = 1289 WHERE department_id=60

更新数据报错

5.6.where子句的子查询引用了from子句中的表

CREATE VIEW myvw8
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 myvw8;

UPDATE myvw8 SET salary=8888 WHERE last_name='K_ing'

更新数据报错：

posted @ 2024-05-16 17:35 酒剑仙* 阅读(42) 评论(0) 收藏举报

刷新页面返回顶部

Augus

视图