MySQL 视图入门

news/2025/2/21 11:37:24

一、什么是 MySQL 视图

1.1 视图的基本概念

在 MySQL 中，视图是一种虚拟表，它本身并不存储实际的数据，而是基于一个或多个真实表（基表）的查询结果集。可以把视图想象成是一个预定义好的查询语句的快捷方式。当你查询视图时，MySQL 会动态地执行定义视图时的查询语句，并返回结果，就好像你直接查询了一个真实的表一样。

举个简单的生活例子，假设你有一个装满各种文件的大文件夹，里面的文件按照不同的主题、日期等分类存放。有时候你只需要查看某一类特定的文件，比如最近一周内关于项目 X 的文件。每次都手动去大文件夹里筛选这些文件会很麻烦，于是你可以创建一个“快捷方式”，这个快捷方式会自动帮你找到并显示符合条件的文件。在 MySQL 里，视图就类似于这个“快捷方式”。

1.2 视图与表的区别

  • 数据存储：表是实际存储数据的容器，数据会被持久化保存在磁盘上；而视图只是一个逻辑上的概念，它不存储数据，数据仍然存储在基表中。
  • 操作方式：对表可以进行插入、更新、删除等操作，这些操作会直接影响表中的实际数据；对视图也可以进行部分操作，但这些操作最终还是会映射到基表上，并且有些视图可能不允许进行某些操作（后面会详细介绍）。

二、MySQL 视图的作用

2.1 简化复杂查询

在实际的数据库应用中，查询语句可能会非常复杂，涉及多个表的连接、子查询、复杂的条件过滤等。使用视图可以将这些复杂的查询封装起来，用户只需要查询视图即可，无需关心底层的查询逻辑。

例如，假设我们有两个表：employees 表存储员工的基本信息，departments 表存储部门信息，并且通过 department_id 字段关联。如果我们经常需要查询每个员工所在的部门名称，每次都编写连接查询会很繁琐。这时可以创建一个视图来简化这个查询。

-- 创建 employees 表
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100),
    department_id INT
);

-- 创建 departments 表
CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(100)
);

-- 插入一些示例数据
INSERT INTO employees (employee_id, employee_name, department_id) VALUES
(1, '张三', 1),
(2, '李四', 2);

INSERT INTO departments (department_id, department_name) VALUES
(1, '技术部'),
(2, '市场部');

-- 创建视图
CREATE VIEW employee_department_view AS
SELECT e.employee_id, e.employee_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

-- 查询视图
SELECT * FROM employee_department_view;

2.2 提高数据安全性

视图可以限制用户对数据的访问权限。通过创建视图，只向用户暴露他们需要的数据，而隐藏敏感信息。

比如，在一个包含员工工资信息的 employees 表中，工资信息是敏感数据，只有管理人员可以查看。我们可以创建一个不包含工资字段的视图，只允许普通员工查询基本信息。

-- 创建包含工资信息的 employees 表
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100),
    salary DECIMAL(10, 2)
);

-- 插入示例数据
INSERT INTO employees (employee_id, employee_name, salary) VALUES
(1, '张三', 5000),
(2, '李四', 6000);

-- 创建不包含工资字段的视图
CREATE VIEW employee_basic_info_view AS
SELECT employee_id, employee_name
FROM employees;

-- 普通员工只能查询视图
SELECT * FROM employee_basic_info_view;

2.3 数据独立性

视图可以为用户提供一种逻辑上的数据独立性。当底层表的结构发生变化时，只要视图的查询结果保持不变，用户的查询代码就不需要修改。

例如，假设原来的 employees 表中有 first_name 和 last_name 两个字段，我们创建了一个视图将这两个字段合并显示为 full_name。后来，表结构发生了变化，将 first_name 和 last_name 合并为一个 full_name 字段。这时，只要我们修改视图的定义，用户查询视图的代码仍然可以正常使用。

-- 原始表结构
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50)
);

-- 创建视图
CREATE VIEW employee_full_name_view AS
SELECT employee_id, CONCAT(first_name, ' ', last_name) AS full_name
FROM employees;

-- 修改表结构
ALTER TABLE employees
DROP COLUMN first_name,
DROP COLUMN last_name,
ADD COLUMN full_name VARCHAR(100);

-- 修改视图定义
CREATE OR REPLACE VIEW employee_full_name_view AS
SELECT employee_id, full_name
FROM employees;

-- 用户查询视图的代码不变
SELECT * FROM employee_full_name_view;

三、MySQL 视图的语法

3.1 创建视图

创建视图的基本语法如下：

CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    [DEFINER = {user | CURRENT_USER}]
    [SQL SECURITY {DEFINER | INVOKER}]
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION];
3.1.1 各部分参数详细解释

  • CREATE 和 OR REPLACE：

    • CREATE：用于创建一个新的视图。如果视图已经存在，再次使用 CREATE 会报错。
    • OR REPLACE：如果视图已经存在，则替换原有的视图；如果不存在，则创建新的视图。

  • ALGORITHM：指定视图的处理算法，有三种可选值：

    • UNDEFINED：MySQL 会根据具体情况选择合适的算法。这是默认值。
    • MERGE：将视图的查询与外层查询合并执行。也就是说，当你查询视图时，MySQL 会把视图的查询语句和外层查询语句合并成一个新的查询语句，然后一起执行。这种算法通常适用于简单的视图，效率较高。
    • TEMPTABLE：先将视图的查询结果存储在临时表中，再对外层查询进行处理。这种算法适用于复杂的视图，因为它可以避免重复计算视图的查询结果，但会占用额外的临时存储空间。

  • DEFINER：指定视图的定义者，默认为当前用户。可以指定为具体的用户，格式为 'username'@'hostname'。

  • SQL SECURITY：指定视图的安全模式，有两种可选值：

    • DEFINER：使用定义者的权限执行视图查询。也就是说，无论谁查询这个视图，都会以定义者的权限来执行视图中的查询语句。
    • INVOKER：使用调用者的权限执行视图查询。即查询视图时，会以执行查询操作的用户的权限来执行视图中的查询语句。

  • view_name：视图的名称，需要遵循 MySQL 标识符的命名规则。

  • column_list：可选参数，指定视图的列名。如果不指定，视图的列名会与查询语句中的列名相同。

  • select_statement：视图的查询语句，这是视图的核心部分，用于定义视图要显示的数据。

  • WITH CHECK OPTION：可选参数，用于确保通过视图插入、更新或删除的数据符合视图的定义条件。CASCADED 和 LOCAL 用于指定检查的范围：

    • CASCADED：会递归检查所有相关视图的条件。
    • LOCAL：只检查当前视图的条件。
3.1.2 创建视图示例
-- 创建一个简单的视图，使用默认参数
CREATE VIEW customer_view AS
SELECT customer_id, customer_name, email
FROM customers;

-- 创建一个使用 OR REPLACE 的视图
CREATE OR REPLACE VIEW customer_view AS
SELECT customer_id, customer_name, email, phone
FROM customers;

-- 创建一个指定 ALGORITHM 为 TEMPTABLE 的视图
CREATE ALGORITHM = TEMPTABLE VIEW customer_summary_view AS
SELECT COUNT(*) AS total_customers, AVG(age) AS average_age
FROM customers;

3.2 删除视图

删除视图的语法如下：

DROP VIEW [IF EXISTS] view_name;
  • IF EXISTS：可选参数，如果视图不存在，不会抛出错误。
  • view_name：要删除的视图名称。
3.2.1 删除视图示例
-- 删除视图
DROP VIEW customer_view;

-- 使用 IF EXISTS 删除视图
DROP VIEW IF EXISTS customer_summary_view;

3.3 修改视图

虽然 MySQL 没有专门的 MODIFY VIEW 语句，但可以使用 CREATE OR REPLACE VIEW 来修改视图的定义。

3.3.1 修改视图示例
-- 原视图定义
CREATE VIEW product_view AS
SELECT product_id, product_name
FROM products;

-- 修改视图定义
CREATE OR REPLACE VIEW product_view AS
SELECT product_id, product_name, price
FROM products;

四、MySQL 视图中的函数使用

在视图的查询语句中，可以使用各种 MySQL 函数来处理数据。下面介绍几个常用的函数。

4.1 CONCAT() 函数

CONCAT() 函数用于将多个字符串连接成一个字符串。

-- 创建一个包含 CONCAT() 函数的视图
CREATE VIEW employee_full_name_view AS
SELECT employee_id, CONCAT(first_name, ' ', last_name) AS full_name
FROM employees;

-- 查询视图
SELECT * FROM employee_full_name_view;

4.2 SUM() 函数

SUM() 函数用于计算指定列的总和。

-- 创建一个包含 SUM() 函数的视图，统计每个订单的总金额
CREATE VIEW order_total_view AS
SELECT order_id, SUM(quantity * price) AS total_amount
FROM order_items
GROUP BY order_id;

-- 查询视图
SELECT * FROM order_total_view;

4.3 AVG() 函数

AVG() 函数用于计算指定列的平均值。

-- 创建一个包含 AVG() 函数的视图，计算每个部门员工的平均工资
CREATE VIEW department_avg_salary_view AS
SELECT department_id, AVG(salary) AS average_salary
FROM employees
GROUP BY department_id;

-- 查询视图
SELECT * FROM department_avg_salary_view;

4.4 COUNT() 函数

COUNT() 函数用于统计记录的数量。

-- 创建一个包含 COUNT() 函数的视图，统计每个部门的员工数量
CREATE VIEW department_employee_count_view AS
SELECT department_id, COUNT(*) AS employee_count
FROM employees
GROUP BY department_id;

-- 查询视图
SELECT * FROM department_employee_count_view;

五、对视图进行数据操作

5.1 插入数据

并不是所有的视图都支持插入数据，只有满足一定条件的视图才可以进行插入操作。视图必须满足以下条件：

  • 视图的查询语句中不能包含 GROUP BY、HAVING、DISTINCT 等关键字。
  • 视图的查询语句中必须包含所有非空且没有默认值的列。
-- 创建一个可插入数据的视图
CREATE VIEW insertable_employee_view AS
SELECT employee_id, employee_name, department_id
FROM employees;

-- 向视图插入数据
INSERT INTO insertable_employee_view (employee_id, employee_name, department_id)
VALUES (3, '王五', 1);

5.2 更新数据

更新视图的数据和插入数据类似，也需要满足一定的条件。更新操作会直接影响基表中的数据。

-- 更新视图中的数据
UPDATE insertable_employee_view
SET employee_name = '赵六'
WHERE employee_id = 3;

5.3 删除数据

删除视图中的数据也会影响基表中的数据。同样，视图需要满足一定的条件才能进行删除操作。

-- 删除视图中的数据
DELETE FROM insertable_employee_view
WHERE employee_id = 3;

六、视图的性能考虑

6.1 索引的使用

虽然视图本身没有索引，但可以通过对基表创建索引来提高视图查询的性能。在创建视图时，如果视图的查询语句中涉及到条件过滤或排序操作，可以考虑在基表的相应列上创建索引。

-- 在 employees 表的 department_id 列上创建索引
CREATE INDEX idx_employees_department_id ON employees (department_id);

6.2 避免复杂的嵌套视图

过多的嵌套视图会增加查询的复杂度，降低查询性能。尽量避免创建过于复杂的嵌套视图，如果需要使用多个视图，可以考虑将它们合并或优化查询逻辑。

七、总结

视图是一个非常强大且实用的工具，它可以简化复杂查询、提高数据安全性和实现数据独立性。在实际应用中，我们需要根据具体的需求和数据特点，合理地使用视图，并注意视图的性能优化。


http://www.niftyadmin.cn/n/5860786.html

相关文章

玩机日记 12 群晖部署AList并配置SSL，安装opkg，使用rclone挂载到本地

玩机日记 12 群晖部署AList并配置SSL，安装opkg，使用rclone挂载到本地

目录 1、部署alist，配置ssl 安装alist 参考官方文档添加网盘 配置ssl 开启webdav访问 2、安装opkg 修改环境变量 添加计划任务 3、安装配置rclone挂载alist 安装rclone，新建remote 安装fuse3 挂载AList 添加计划任务 1、部署alist&#xff0…
阅读更多...
【Blender】二、建模篇--05，阵列修改器与晶格形变

【Blender】二、建模篇--05，阵列修改器与晶格形变

阵列修改器是bender里面一个比较常用的修改器，所以我们单独开口来讲，我们会先从几片树叶出发，然后我们用阵列修改器把这几片树叶变成这样的造型和这样的造型。这两个造型分别就代表着阵列修改器最常用的两种偏移方法，我们现在就开始我们先来做几个树叶。 1.树叶建模 首先…
阅读更多...
npm、pnpm和yarn有什么区别

npm、pnpm和yarn有什么区别

1. 性能和速度 npm：在较早的版本中，速度较慢，尤其是在安装大型依赖集时。自npm 5以后的版本引入了缓存机制，性能有所提升。yarn：由Facebook开发，主要目标是提高安装速度。使用了缓存和并行安装（…
阅读更多...
Matplotlib 高级图表绘制与交互式可视化（ipywidgets）

Matplotlib 高级图表绘制与交互式可视化（ipywidgets）

目录： ipywidgets 介绍 1. 什么是 ipywidgets 直接开始： 动态调整正弦波频率 随机散点图 启用交互式模式 使用滑块和下拉菜单调整图表样式 使用布局管理器创建复杂界面 使用动画创建动态图表 最后： 综合示例：动态仪表盘 ipywidgets 介绍 1. 什么是 ipywidgets i…
阅读更多...
Vue 中组件通信的方式有哪些，如何实现父子组件和非父子组件之间的通信？

Vue 中组件通信的方式有哪些，如何实现父子组件和非父子组件之间的通信？

一、父子组件通信&#xff08;垂直通信&#xff09; 1. Props 传值&#xff08;父 → 子&#xff09; 实现方案&#xff1a; <!-- Parent.vue --> <template><Child :user"userData" /> </template><script setup> import { ref } …
阅读更多...
[kubelet-check] It seems like the kubelet isn‘t running or healthy.

[kubelet-check] It seems like the kubelet isn‘t running or healthy.

执行k8s时报错&#xff1a; [kubelet-check] It seems like the kubelet isn’t running or healthy. [kubelet-check] The HTTP call equal to ‘curl -sSL http://localhost:10248/healthz’ failed with error: Get "http://localhost:10248/heal ** 解决办法如下&a…
阅读更多...
Lineageos 22.1(Android 15)Launcer简单调整初始化配置

Lineageos 22.1(Android 15)Launcer简单调整初始化配置

一、前言 Launcer的初始化配置主要在如下的xml文件夹下&#xff0c;默认读取的5x5 这里我们把device_profiles调整一下&#xff0c;然后新建一个default_workspace_my.xml作为我们自己的配置就行。 二、配置 注意Lineageos 的Launcer是在lineageos/packages/apps/Trebuchet…
阅读更多...
Python数据结构实战：链表的构建与操作

Python数据结构实战：链表的构建与操作

Python数据结构实战&#xff1a;链表的构建与操作 一、链表&#xff08;LinkedList&#xff09;基础 1. 定义与特性 链表是一种动态数据结构&#xff0c;其特点如下&#xff1a; 元素&#xff08;节点&#xff09;通过指针连接&#xff0c;内存空间非连续插入/删除时间复杂…
阅读更多...
最新文章

Copyright @ 2022~2023