当前位置： 首页 > news >正文

SQL基础⑫ | 视图篇

news 2025/7/25 19:46:14

0 序言

本文将系统讲解数据库中视图的相关知识，包括视图的定义、作用、创建（单表、多表、基于视图创建）、查看、更新、修改与删除操作，以及视图的优缺点。

通过学习，你能够掌握视图的基本概念，理解何时及如何合理使用视图，提升数据库操作的灵活性与安全性。

1 常见的数据库对象

数据库包含多种对象，各自承担不同功能，具体如下：

1.1 表（TABLE）

是存储数据的逻辑单元，以行和列的形式存在，列即字段，行即记录。

1.2 数据字典

是系统表，存放数据库相关信息，数据通常由数据库系统维护，程序员一般只可查看，不应修改。

1.3 约束（CONSTRAINT）

用于执行数据校验的规则，目的是保证数据的完整性。

1.4 视图（VIEW）

是一个或多个数据表里数据的逻辑显示，本身不存储数据。

1.5 索引（INDEX）

用于提高查询性能，类似书的目录。

1.6 存储过程（PROCEDURE）

用于完成一次完整的业务处理，无返回值，但可通过传出参数传递多个值给调用环境。

1.7 存储函数（FUNCTION）

用于完成一次特定的计算，具有一个返回值。

1.8 触发器（TRIGGER）

相当于事件监听器，当数据库发生特定事件后被触发，完成相应处理。

2. 视图概述

2.1 为什么使用视图

数据访问控制：可针对不同用户提供不同查询视图，限制数据可见范围，如对销售人员隐藏采购价格、对普通员工隐藏薪酬敏感字段。
简化查询：将常用查询结果集封装为视图，减少重复编写复杂查询语句的工作量。
数据格式化：可对数据进行格式化处理，方便按特定格式展示，如拼接员工姓名与部门名。

2.2 视图的理解

视图是一种虚拟表，本身不存储数据，仅占用少量内存空间，其数据来源于基表（赖以建立的已有表）。
视图的创建和删除仅影响自身，不影响基表；但对视图数据的增删改会同步影响基表，反之亦然。

视图的数据由SELECT语句提供，自身不保存数据，仅作为基表数据的另一种表现形式。

3. 创建视图

3.1 基本语法

CREATE [OR REPLACE] 
[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] 
VIEW 视图名称 [(字段列表)]
AS 查询语句
[WITH [CASCADED|LOCAL] CHECK OPTION]

精简版：CREATE VIEW 视图名称 AS 查询语句

3.2 创建单表视图

3.2.1 示例1：直接使用SELECT字段作为视图字段

-- 创建视图empvu80，包含80号部门员工的ID、姓氏、薪资
CREATE VIEW empvu80
AS 
SELECT employee_id, last_name, salary
FROM employees
WHERE department_id = 80;-- 查询视图
SELECT * FROM empvu80;

在这里插入图片描述

3.2.2 示例2：指定视图字段别名

-- 创建视图emp_year_salary，包含员工姓名和年薪（含佣金）
CREATE VIEW emp_year_salary (ename, year_salary)
AS 
SELECT ename, salary*12*(1+IFNULL(commission_pct,0))
FROM t_employee;

在这里插入图片描述

这里要注意一点：

若未指定视图字段列表，默认与SELECT语句字段列表一致；
若SELECT字段有别名，视图字段名与别名相同。

3.3 创建多表联合视图

3.3.1 示例1：两表内连接

-- 创建视图empview，关联员工表和部门表，包含员工ID、姓名、部门名
CREATE VIEW empview 
AS 
SELECT employee_id emp_id, last_name NAME, department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id;

在这里插入图片描述

3.3.2 示例2：带聚合函数的联合视图

-- 创建视图dept_sum_vu，统计各部门薪资的最小、最大、平均值
CREATE VIEW dept_sum_vu (name, minsal, maxsal, avgsal)
AS 
SELECT d.department_name, MIN(e.salary), MAX(e.salary), AVG(e.salary)
FROM employees e, departments d
WHERE e.department_id = d.department_id 
GROUP BY d.department_name;

在这里插入图片描述

3.3.3 示例3：数据格式化视图

-- 创建视图emp_depart，格式化员工姓名与部门名（格式：姓名(部门名)）
CREATE VIEW emp_depart
AS
SELECT CONCAT(last_name,'(',department_name,')') AS emp_dept
FROM employees e JOIN departments d
WHERE e.department_id = d.department_id;

在这里插入图片描述

3.4 基于视图创建视图

基于emp_dept和emp_year_salary视图创建新视图

-- 创建视图emp_dept_ysalary，包含员工姓名、部门名、年薪
CREATE VIEW emp_dept_ysalary
AS 
SELECT emp_dept.ename, dname, year_salary
FROM emp_dept INNER JOIN emp_year_salary
ON emp_dept.ename = emp_year_salary.ename;

4. 查看视图

4.1 查看数据库中的视图对象

-- 显示数据库中所有表和视图
SHOW TABLES;

在这里插入图片描述

4.2 查看视图结构

-- 查看视图empvu80的结构
DESC empvu80;
-- 或
DESCRIBE empvu80;

在这里插入图片描述

4.3 查看视图属性信息

-- 查看视图empvu80的存储引擎、版本等信息（Comment为VIEW表示是视图）
SHOW TABLE STATUS LIKE 'empvu80'\G

在这里插入图片描述

4.4 查看视图详细定义

-- 查看视图empvu80的创建语句
SHOW CREATE VIEW empvu80;

在这里插入图片描述

5. 更新视图的数据

5.1 一般情况

MySQL支持通过INSERT、UPDATE、DELETE操作视图数据，且会同步影响基表，反之亦然。
示例1：UPDATE操作

-- 修改emp_tel视图中"孙洪亮"的电话
UPDATE emp_tel SET tel = '13789091234' WHERE ename = '孙洪亮';
-- 基表t_employee中对应数据同步更新
SELECT ename,tel FROM t_employee WHERE ename = '孙洪亮'; -- 结果：孙洪亮 13789091234

这里跟上文约束那一篇里面写到的主表跟从表，

有个示例也是效果如此。

一个表的信息变动另一个表相关联的信息也会随着更新。

示例2：DELETE操作

-- 删除emp_tel视图中"孙洪亮"的记录
DELETE FROM emp_tel WHERE ename = '孙洪亮';
-- 基表t_employee中对应记录同步删除
SELECT ename,tel FROM t_employee WHERE ename = '孙洪亮'; -- 结果：空集

5.2 不可更新的视图

当视图满足以下情况时，不支持INSERT、UPDATE、DELETE操作：

  • 定义时指定ALGORITHM = TEMPTABLE（不支持INSERT、DELETE）；
  • 不包含基表中所有非空且无默认值的列（不支持INSERT）；
  • SELECT语句使用JOIN联合查询（不支持INSERT、DELETE）；
  • 字段列表包含数学表达式或子查询（不支持INSERT，且无法UPDATE该字段）；
  • 使用DISTINCT、聚合函数、GROUP BY、HAVING、UNION等（不支持增删改）；
  • SELECT包含子查询且引用FROM后的表（不支持增删改）；
  • 基于不可更新的视图创建；
  • 常量视图。

比如说：

-- 创建基于两表连接的视图emp_dept
CREATE VIEW emp_dept
AS SELECT ename,salary,birthday,tel,email,hiredate,dname
FROM t_employee INNER JOIN t_department
ON t_employee.did = t_department.did;-- 插入数据会失败（JOIN视图不支持INSERT）
INSERT INTO emp_dept(ename,salary,birthday,tel,email,hiredate,dname)
VALUES('张三',15000,'1995-01-08','18201587896','zs@atguigu.com','2022-02-14','新部门');

这里会报错，错误内容：Can not modify more than one base table through a join view

意思就是说，你现在这个JOIN视图不支持用INSERT语法进行插入数据。

6. 修改与删除视图

6.1 修改视图

方式1：使用CREATE OR REPLACE VIEW

-- 修改empvu80视图，包含员工ID、全名（名+姓）、薪资、部门ID
CREATE OR REPLACE VIEW empvu80
(id_number, name, sal, department_id)
AS 
SELECT employee_id, first_name || ' ' || last_name, salary, department_id
FROM employees
WHERE department_id = 80;

在这里插入图片描述

这里的图片还不够直观，

补充上姓名数据会看起来更加直观，

就好比本来是张 三，一个放在lastname，一个在firstname，

然后这里的作用就是变成一个name里面存放的就是张三。

方式2：使用ALTER VIEW

-- 修改视图salvu50的查询条件
ALTER VIEW salvu50
AS 
SELECT employee_id ID_NUMBER, last_name NAME, salary*12 ANN_SALARY
FROM employees
WHERE department_id = 60; -- 原条件为department_id = 50

同理，这种方法也能实现相同的效果。

6.2 删除视图

-- 删除视图empvu80（IF EXISTS避免视图不存在时报错）
DROP VIEW IF EXISTS empvu80;

说明：删除基视图会导致依赖它的视图查询失败，需手动维护。

7 小结

7.1 视图的优点

操作简单：封装复杂查询，简化开发人员对数据库的操作，无需关注基表结构与关联关系。
减少数据冗余：仅存储查询语句，不占用数据存储资源。
数据安全：限制用户访问范围，通过视图隔离用户与基表，保障数据安全。
适应需求变化：减少因基表结构变更带来的工作量。
分解复杂逻辑：将复杂查询拆分为多个视图，组合完成复杂逻辑。

7.2 视图的不足

维护成本高：基表结构变更时需同步维护视图，嵌套视图维护更复杂。
潜在隐患：视图定义可能包含重命名、复杂逻辑，降低可读性，增加系统风险。
过多视图问题：实际项目中视图过多会增加数据库维护难度。

通过本文学习，我们要掌握视图的创建、操作及应用场景，合理使用视图提升数据库操作效率与安全性。

http://www.xdnf.cn/news/1181269.html

相关文章：

  • C++ - 仿 RabbitMQ 实现消息队列--服务端核心模块实现（三）
  • 基于深度学习的图像分类：使用MobileNet实现高效分类
  • Python进阶第三方库之Matplotlib
  • 深度学习（鱼书）day01--感知机
  • LeetCode 23：合并 K 个升序链表
  • 【C++】使用中值滤波算法过滤数据样本中的尖刺噪声
  • rust-方法语法
  • C++STL系列之set和map系列
  • 基于python django的农业可视化系统，以奶牛牧场为例
  • 用 Function Call 让 AI 主动调用函数（超入门级示例）｜保姆级大模型应用开发实战
  • SpringBoot航空订票系统的设计与实现
  • 进阶系统策略
  • 技术赋能多元探索：我的技术成长与行业洞察
  • Linux应用开发基础知识——进程学习2(exec函数、system函数、popen函数)（三）
  • 斐波那契数列策略
  • 人形机器人_双足行走动力学：Maxwell模型及在拟合肌腱特性中的应用
  • Java学习----原型模式
  • 使用Claude Code从零到一打造一个现代化的GitHub Star项目管理器
  • day46day47 通道注意力
  • 无源域自适应综合研究【2】
  • C++ 性能优化
  • 力扣 hot100 Day54
  • pytest中使用skip跳过某个函数
  • 无人机速度模块技术要点分析
  • 第三章：掌握 Redis 存储与获取数据的核心命令
  • MNIST 手写数字识别模型分析
  • 秋叶sd-webui频繁出现生成后无反应的问题
  • 【Web APIs】JavaScript 节点操作 ⑧ ( 删除节点 - removeChild 函数 | 删除节点 - 代码示例 | 删除网页评论案例 )
  • 算法竞赛阶段二-数据结构（34）数据结构链表STL vector
  • 【PyTorch】图像二分类项目-部署