MariaDB教程FG034-MariaDB视图使用与业务查询简化实战

内容简介：本文主要介绍MariaDB视图的使用方法和在业务查询中的应用，包括视图的基本概念、特性与优势、创建与管理等核心内容。通过实际案例讲解销售报表视图、用户信息视图和库存管理视图的创建和使用，以及如何通过视图简化业务查询。风哥教程参考MariaDB官方文档Views、SQL Syntax等相关内容。

Part01-基础概念与理论知识

1.1 视图的基本概念

视图是一个虚拟表，其内容由查询定义。视图并不在数据库中存储数据，而是在查询时动态生成结果。视图可以看作是对一个或多个表的查询结果的命名封装。

视图的主要作用：

简化复杂查询：将复杂的查询逻辑封装在视图中
数据安全性：限制用户对数据的访问范围
数据一致性：确保多个应用使用相同的查询逻辑
简化应用开发：应用可以直接使用视图，而不需要了解底层表结构

1.2 视图的特性与优势

视图的特性：

虚拟性：视图不存储数据，只存储查询定义
动态性：视图的数据会随着基表数据的变化而变化
可操作性：可以像表一样查询、更新（在一定条件下）视图
灵活性：可以基于多个表创建复杂的视图

视图的优势：

简化查询语句：将复杂的JOIN、聚合等操作封装在视图中
提高数据安全性：可以限制用户只能访问视图中的特定列
统一数据访问接口：多个应用可以使用相同的视图
隐藏数据结构：应用不需要了解底层表结构的变化

1.3 视图的类型

MariaDB中的视图类型：

普通视图：最常见的视图类型，基于查询定义
物化视图：存储查询结果的视图，需要定期刷新
临时视图：只在当前会话中存在的视图
updatable views：可以更新的视图，需要满足一定条件

更多视频教程www.fgedu.net.cn

Part02-生产环境规划与建议

2.1 视图设计原则

生产环境中视图设计的原则：

明确视图的目的：确定视图的使用场景和需求
保持视图简单：避免创建过于复杂的视图
命名规范：使用清晰、描述性的名称
避免嵌套视图：过多的视图嵌套会影响性能
考虑更新需求：如果需要更新视图，确保满足更新条件

2.2 性能考虑

视图的性能考虑：

视图本身不存储数据，每次查询都会执行底层查询
复杂视图可能会影响查询性能
适当使用索引：确保视图底层的表有适当的索引
考虑使用物化视图：对于频繁查询的复杂视图
避免在视图中使用ORDER BY：除非必要

2.3 安全建议

视图的安全建议：

使用视图限制数据访问：只暴露必要的列
避免在视图中包含敏感信息
使用权限控制：限制用户对视图的访问权限
定期审查视图：确保视图定义符合安全要求

学习交流加群风哥微信: itpux-com

Part03-生产环境项目实施方案

3.1 视图创建与管理

视图的创建语法：

更多学习教程公众号风哥教程itpux_com

CREATE [OR REPLACE] VIEW view_name [(column_list)]
AS query_expression
[WITH [CASCADED | LOCAL] CHECK OPTION];

视图的管理操作：

查看视图：SHOW CREATE VIEW view_name
修改视图：ALTER VIEW view_name AS query_expression
删除视图：DROP VIEW [IF EXISTS] view_name
查看所有视图：SHOW FULL TABLES WHERE TABLE_TYPE LIKE ‘VIEW’

3.2 视图在业务中的应用

视图在业务中的常见应用场景：

报表查询：创建报表专用视图
数据集成：整合多个表的数据
权限控制：限制用户访问特定数据
简化查询：封装复杂的查询逻辑
数据转换：对数据进行格式化和转换

3.3 视图维护与优化

视图的维护与优化建议：

定期审查视图：确保视图定义仍然有效
优化视图底层查询：提高视图查询性能
使用适当的索引：加速视图查询
避免过度使用视图：合理使用视图，避免性能问题
考虑使用物化视图：对于频繁查询的复杂视图

学习交流加群风哥QQ113257174

Part04-生产案例与实战讲解

4.1 销售报表视图实战

场景描述：创建销售报表视图，整合销售数据和产品信息。

# 创建基础表
CREATE TABLE fgedu_products (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
price DECIMAL(10,2) NOT NULL,
category VARCHAR(50)
);
CREATE TABLE fgedu_sales (
id INT PRIMARY KEY AUTO_INCREMENT,
product_id INT NOT NULL,
quantity INT NOT NULL,
amount DECIMAL(10,2) NOT NULL,
sale_date DATE NOT NULL,
FOREIGN KEY (product_id) REFERENCES fgedu_products(id)
);

执行结果：

Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.01 sec)

# 插入测试数据
INSERT INTO fgedu_products (name, price, category) VALUES
(‘产品A’, 100.00, ‘电子产品’),
(‘产品B’, 200.00, ‘电子产品’),
(‘产品C’, 300.00, ‘家居用品’),
(‘产品D’, 400.00, ‘家居用品’),
(‘产品E’, 500.00, ‘办公用品’);
INSERT INTO fgedu_sales (product_id, quantity, amount, sale_date) VALUES
(1, 10, 1000.00, ‘2023-01-01’),
(1, 5, 500.00, ‘2023-01-02’),
(2, 8, 1600.00, ‘2023-01-03’),
(3, 3, 900.00, ‘2023-01-04’),
(4, 2, 800.00, ‘2023-01-05’),
(5, 1, 500.00, ‘2023-01-06’);

执行结果：

Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0
Query OK, 6 rows affected (0.01 sec)
Records: 6 Duplicates: 0 Warnings: 0

# 创建销售报表视图
CREATE VIEW v_fgedu_sales_report AS
SELECT
s.id AS sale_id,
p.name AS product_name,
p.category AS product_category,
p.price AS unit_price,
s.quantity,
s.amount,
s.sale_date
FROM
fgedu_sales s
JOIN
fgedu_products p ON s.product_id = p.id;

执行结果：

Query OK, 0 rows affected (0.01 sec)

# 使用视图查询销售数据
SELECT * FROM v_fgedu_sales_report;

执行结果：

+———+————–+——————+————+———-+———+————+
| sale_id | product_name | product_category | unit_price | quantity | amount | sale_date |
+———+————–+——————+————+———-+———+————+
| 1 | 产品A | 电子产品 | 100.00 | 10 | 1000.00 | 2023-01-01 |
| 2 | 产品A | 电子产品 | 100.00 | 5 | 500.00 | 2023-01-02 |
| 3 | 产品B | 电子产品 | 200.00 | 8 | 1600.00 | 2023-01-03 |
| 4 | 产品C | 家居用品 | 300.00 | 3 | 900.00 | 2023-01-04 |
| 5 | 产品D | 家居用品 | 400.00 | 2 | 800.00 | 2023-01-05 |
| 6 | 产品E | 办公用品 | 500.00 | 1 | 500.00 | 2023-01-06 |
+———+————–+——————+————+———-+———+————+

# 使用视图进行统计分析
SELECT
product_category,
COUNT(*) AS sale_count,
SUM(quantity) AS total_quantity,
SUM(amount) AS total_amount
FROM v_fgedu_sales_report
GROUP BY product_category
ORDER BY total_amount DESC;

执行结果：

+——————+————+—————-+————-+
| product_category | sale_count | total_quantity | total_amount |
+——————+————+—————-+————-+
| 电子产品 | 3 | 23 | 3100.00 |
| 家居用品 | 2 | 5 | 1700.00 |
| 办公用品 | 1 | 1 | 500.00 |
+——————+————+—————-+————-+

4.2 用户信息视图实战

场景描述：创建用户信息视图，整合用户基本信息和订单信息。

# 创建基础表
CREATE TABLE fgedu_users (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) UNIQUE,
phone VARCHAR(20),
register_date DATE NOT NULL
);
CREATE TABLE fgedu_orders (
id INT PRIMARY KEY AUTO_INCREMENT,
user_id INT NOT NULL,
total_amount DECIMAL(10,2) NOT NULL,
order_date DATE NOT NULL,
status VARCHAR(20) NOT NULL,
FOREIGN KEY (user_id) REFERENCES fgedu_users(id)
);

执行结果：

Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.01 sec)

# 插入测试数据
INSERT INTO fgedu_users (name, email, phone, register_date) VALUES
(‘张三’, ‘zhangsan@fgedu.net.cn’, ‘13800138001’, ‘2023-01-01’),
(‘李四’, ‘lisi@fgedu.net.cn’, ‘13900139001’, ‘2023-01-02’),
(‘王五’, ‘wangwu@fgedu.net.cn’, ‘13700137001’, ‘2023-01-03’);
INSERT INTO fgedu_orders (user_id, total_amount, order_date, status) VALUES
(1, 1000.00, ‘2023-01-04’, ‘已完成’),
(1, 500.00, ‘2023-01-05’, ‘已完成’),
(2, 1500.00, ‘2023-01-06’, ‘已完成’),
(3, 800.00, ‘2023-01-07’, ‘待处理’),
(3, 1200.00, ‘2023-01-08’, ‘已完成’);

执行结果：

Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0

# 创建用户信息视图
CREATE VIEW v_fgedu_user_info AS
SELECT
u.id AS user_id,
u.name,
u.email,
u.phone,
u.register_date,
COUNT(o.id) AS order_count,
SUM(o.total_amount) AS total_spent,
MAX(o.order_date) AS last_order_date
FROM
fgedu_users u
LEFT JOIN
fgedu_orders o ON u.id = o.user_id
GROUP BY
u.id, u.name, u.email, u.phone, u.register_date;

执行结果：

Query OK, 0 rows affected (0.01 sec)

# 使用视图查询用户信息
SELECT * FROM v_fgedu_user_info;

执行结果：

+———+——–+———————-+————-+—————+————+————-+—————–+
| user_id | name | email | phone | register_date | order_count | total_spent | last_order_date |
+———+——–+———————-+————-+—————+————+————-+—————–+
| 1 | 张三 | zhangsan@fgedu.net.cn | 13800138001 | 2023-01-01 | 2 | 1500.00 | 2023-01-05 |
| 2 | 李四 | lisi@fgedu.net.cn | 13900139001 | 2023-01-02 | 1 | 1500.00 | 2023-01-06 |
| 3 | 王五 | wangwu@fgedu.net.cn | 13700137001 | 2023-01-03 | 2 | 2000.00 | 2023-01-08 |
+———+——–+———————-+————-+—————+————+————-+—————–+

4.3 库存管理视图实战

场景描述：创建库存管理视图，整合产品信息和库存数据。

# 创建基础表
CREATE TABLE fgedu_inventory (
id INT PRIMARY KEY AUTO_INCREMENT,
product_id INT NOT NULL,
quantity INT NOT NULL,
last_update_date DATE NOT NULL,
FOREIGN KEY (product_id) REFERENCES fgedu_products(id)
);

执行结果：

Query OK, 0 rows affected (0.01 sec)

# 插入测试数据
INSERT INTO fgedu_inventory (product_id, quantity, last_update_date) VALUES
(1, 100, ‘2023-01-01’),
(2, 50, ‘2023-01-01’),
(3, 30, ‘2023-01-01’),
(4, 20, ‘2023-01-01’),
(5, 10, ‘2023-01-01’);

执行结果：

Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0

# 创建库存管理视图
CREATE VIEW v_fgedu_inventory_status AS
SELECT
p.id AS product_id,
p.name AS product_name,
p.category,
p.price,
i.quantity,
(p.price * i.quantity) AS inventory_value,
i.last_update_date,
CASE
WHEN i.quantity < 20 THEN '库存不足'
WHEN i.quantity < 50 THEN '库存偏低'
ELSE ‘库存充足’
END AS inventory_status
FROM
fgedu_products p
JOIN
fgedu_inventory i ON p.id = i.product_id;

执行结果：

Query OK, 0 rows affected (0.01 sec)

# 使用视图查询库存状态
SELECT * FROM v_fgedu_inventory_status;

执行结果：

+————+————–+———-+——-+———-+—————-+——————+—————-+
| product_id | product_name | category | price | quantity | inventory_value | last_update_date | inventory_status |
+————+————–+———-+——-+———-+—————-+——————+—————-+
| 1 | 产品A | 电子产品 | 100.00 | 100 | 10000.00 | 2023-01-01 | 库存充足 |
| 2 | 产品B | 电子产品 | 200.00 | 50 | 10000.00 | 2023-01-01 | 库存充足 |
| 3 | 产品C | 家居用品 | 300.00 | 30 | 9000.00 | 2023-01-01 | 库存偏低 |
| 4 | 产品D | 家居用品 | 400.00 | 20 | 8000.00 | 2023-01-01 | 库存偏低 |
| 5 | 产品E | 办公用品 | 500.00 | 10 | 5000.00 | 2023-01-01 | 库存不足 |
+————+————–+———-+——-+———-+—————-+——————+—————-+

风哥提示：安全开发是防止SQL注入的第一道防线

Part05-风哥经验总结与分享

5.1 视图使用最佳实践

风哥提示：在使用视图时，应根据实际业务需求合理设计视图，避免创建过于复杂的视图，以确保查询性能。

明确视图的用途：只创建必要的视图
保持视图简洁：避免在视图中使用复杂的逻辑
使用适当的命名规范：便于识别和管理
定期审查视图：确保视图定义仍然符合业务需求
考虑视图的可维护性：避免过度使用视图嵌套

5.2 性能优化技巧

优化底层查询：确保视图底层的查询性能良好
使用适当的索引：加速视图查询
避免在视图中使用ORDER BY：除非必要
考虑使用物化视图：对于频繁查询的复杂视图
限制视图返回的数据量：使用WHERE子句过滤数据

5.3 常见问题与解决方案

视图查询性能慢：优化底层查询，添加适当的索引
视图无法更新：检查视图是否满足更新条件
视图定义过时：定期更新视图定义以反映表结构变化
视图权限问题：确保用户有适当的权限访问视图
视图嵌套过深：减少视图嵌套层级，优化查询逻辑

# 查看视图定义
MariaDB [fgedudb]> SHOW CREATE VIEW v_fgedu_sales_report\G;
*************************** 1. row ***************************
View: v_fgedu_sales_report
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`fgedu.localhost` SQL SECURITY DEFINER VIEW `v_fgedu_sales_report` AS select `s`.`id` AS `sale_id`,`p`.`name` AS `product_name`,`p`.`category` AS `product_category`,`p`.`price` AS `unit_price`,`s`.`quantity` AS `quantity`,`s`.`amount` AS `amount`,`s`.`sale_date` AS `sale_date` from (`fgedu_sales` `s` join `fgedu_products` `p` on((`s`.`product_id` = `p`.`id`)))
character_set_client: utf8mb4
collation_connection: utf8mb4_unicode_ci

通过以上措施，可以有效利用MariaDB视图简化业务查询，提高数据访问的安全性和一致性，同时确保查询性能。

from MariaDB视频:www.itpux.com

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html

MariaDB优化 MariaDB性能 MariaDB管理数据库优化数据库管理

MariaDB教程FG034-MariaDB视图使用与业务查询简化实战

Part01-基础概念与理论知识

1.1 视图的基本概念

1.2 视图的特性与优势

1.3 视图的类型

Part02-生产环境规划与建议

2.1 视图设计原则

2.2 性能考虑

2.3 安全建议

Part03-生产环境项目实施方案

3.1 视图创建与管理

3.2 视图在业务中的应用

3.3 视图维护与优化

Part04-生产案例与实战讲解

4.1 销售报表视图实战

4.2 用户信息视图实战

4.3 库存管理视图实战

Part05-风哥经验总结与分享

5.1 视图使用最佳实践

5.2 性能优化技巧

5.3 常见问题与解决方案

相关推荐

联系我们