MySQL视图

# 视图概述

视图（View）是一个虚拟的表，它是基于一个或多个表的查询结果构建的。视图可以被视为存储在数据库中的预定义查询。通过创建视图，可以简化复杂的查询操作，并提供一种安全性和简洁性的方式来访问和操作数据。

# 基本用法

# 创建视图

# 1. 单表创建视图
CREATE VIEW 视图名称 [(列名,...)] AS SELECT 列名,... FROM 表名 WHERE 条件;
# 创建学生视图
CREATE VIEW v_student AS
SELECT id, sno, `name`
FROM tb_student
    
# 2. 多表创建视图
CREATE VIEW 视图名称 [(列名,...)] AS SELECT 列名,... FROM 表名1 JOIN 表名2 ON 连接条件  WHERE 条件; 
# 创建学生成绩视图
CREATE VIEW v_student_score (id, sno, name, score) AS 
SELECT t1.id, t1.sno, t1.name, t2.score 
FROM tb_student t1 
LEFT JOIN tb_score t2 ON t1.id = t2.s_id

1
2
3
4
5
6
7
8
9
10
11
12
13
14

其它参数

OR REPLACE: 替换已有视图
CREATE OR REPLACE VIEW 视图名 AS SELECT 列名,... FROM 表名 WHERE 条件;
ALGORITHM: 选择算法，默认是UNDEFINED(未定义，自动选择)， MERGE(合并，将查询视图时的语句与视图定义的查询语句合并起来进行查询)、
TEMPTABLE(临时表，将视图的结果存入临时表，使用临时表执行语句)。
CREATE ALGORITHM = MERGE VIEW 视图名 AS SELECT 列名,... FROM 表名 WHERE 条件;
DEFINER: 视图定义者、创建者，默认为当前用户。
CREATE DEFINER = 'user2'@'%' VIEW 视图名 AS SELECT 列名,... FROM 表名 WHERE 条件;
SQL SECURITY: 视图的安全控制，DEFINER(默认值)，验证是否有视图本身的权限；INVOKER，由调用视图的用户的权限来执行，需要有视图的权限，也需要有视图涉及的表的权限。
CREATE SQL SECURITY INVOKER VIEW 视图名 AS SELECT 列名,... FROM 表名 WHERE 条件;
WITH [CASCADED | LOCAL] CHECK OPTION: 视图检查，如插入、更新、删除数据，使其符合视图的定义(不能违反WHERE条件的限制)。
CASCADED(级联): 默认值，基于视图创建的视图，会级联检查依赖的视图(即使依赖的视图没有设置检查选项,更新本视图时也需要满足依赖视图的限制条件)。
LOCAL(本地): 只检查当前视图的定义，不检查依赖的视图。
CREATE VIEW 视图名 AS SELECT 列名,... FROM 表名 WHERE 条件 WITH CASCADED CHECK OPTION;

# 使用视图

# 查询
SELECT 列名,... FROM 视图名 WHERE 条件;

# 查询学生成绩90分以上的学生列表
SELECT id, sno, name, score FROM v_student_score WHERE score >= 90; 

# 查看视图信息
DESC 视图名;
DESC v_student_score; # 查看学生成绩视图信息

# 查看视图详细信息
SHOW CREATE VIEW 视图名;
SHOW CREATE VIEW v_student_score; # 查看学生成绩视图详细信息

1
2
3
4
5
6
7
8
9
10
11
12
13

# 更新视图数据

# 更新
UPDATE 视图名 SET 列名1=值1, 列名2=值2 WHERE 条件;
# 修改张三的成绩为92分
UPDATE v_student_score SET score = 92 WHERE name = '张三';

# 新增
INSERT INTO 视图名 VALUES(值,...); 
# 新增学生
INSERT INTO v_student(id,sno,`name`) VALUES('2132dfqe32','2021006','test');

# 删除
DELETE  FROM 视图名 WHERE 条件;
# 删除张三的数据
DELETE FROM v_student_score WHERE name = '张三';

1
2
3
4
5
6
7
8
9
10
11
12
13
14

视图更新限制

视图定义中包含聚合函数(SUM()、AVG()等)、GROUP BY、DISTINCT、ORDER BY、UNION、UNION ALL关键字，子查询等；
未满足基本表对字段的约束条件。

# 修改视图定义

# 1. 通过ALTER语句修改
ALTER VIEW 视图名 [(列名,...)]
AS
SELECT 列名,... FROM 表名 WHERE 筛选条件;

# 2. 通过CREATE OR REPLACE 语句修改
CREATE OR REPLACE VIEW 视图名 [(列名,...)]
AS SELECT 列名,... FROM 表名 WHERE 筛选条件;

# 修改学生成绩视图
ALTER VIEW v_student_score (id, name, score) AS
SELECT t1.id, t1.name, t2.score
FROM tb_student t1
LEFT JOIN tb_score t2 ON t1.id = t2.s_id

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

# 删除视图

DROP VIEW [IF EXISTS] 视图名 [,视图名,...];

# 删除学生成绩视图
DROP VIEW v_student_score;

1
2
3
4

# 视图的优点

简化查询：当需要执行复杂的查询时，使用视图可以将查询逻辑封装在一个视图中，从而简化查询语句。可以提高开发效率。
提高数据安全性：通过视图，可以授予用户对特定列或行的访问权限，而隐藏其他敏感数据。可以防止未经授权的访问和保护敏感信息。
重用查询逻辑：如果数据库中存在常用的查询逻辑，可以将其定义为视图，以便在需要时重复使用。这样可以避免重复编写相同的查询语句，提高代码的可读性和维护性，并减少错误的可能性。

#SQL教程

上次更新: 2023/11/23, 14:00:19

← MySQL索引 MySQL常用查询→