MySQL 进阶知识

A_cot

于 2024-09-04 09:13:41 发布

阅读量618

点赞数 15

文章标签： mysql 数据库前端 big data database

本文链接：https://blog.csdn.net/A_cot/article/details/141883810

版权

当你掌握了基本的 SQL 语句和数据库操作后，进一步深入了解 MySQL 的进阶内容，将为你打开更广阔的数据管理和应用之门。

一、存储过程与函数

1. 存储过程

存储过程是一组为了完成特定功能的 SQL 语句集，经编译后存储在数据库中。存储过程可以接收参数、返回结果，并且可以被重复调用。

优点：

提高性能：存储过程在数据库服务器上执行，减少了网络传输和客户端处理的开销。
增强安全性：可以限制对数据库的直接访问，通过存储过程进行数据操作，提高数据的安全性。
易于维护：将复杂的业务逻辑封装在存储过程中，便于维护和修改。

示例代码：

-- 创建存储过程
DELIMITER //
CREATE PROCEDURE GetCustomerOrders(IN customerId INT)
BEGIN
    SELECT * FROM orders WHERE customer_id = customerId;
END //
DELIMITER ;

-- 调用存储过程
CALL GetCustomerOrders(1);

2. 函数

MySQL 函数允许你定义自己的函数，以实现特定的计算或操作。函数可以接收参数并返回一个值。

示例代码：

-- 创建函数
DELIMITER //
CREATE FUNCTION CalculateTotalPrice(orderId INT) RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
    DECLARE total DECIMAL(10,2);
    SELECT SUM(price * quantity) INTO total FROM order_items WHERE order_id = orderId;
    RETURN total;
END //
DELIMITER ;

-- 调用函数
SELECT CalculateTotalPrice(1);

二、视图

视图是一种虚拟的表，它是基于一个或多个表的查询结果集。视图可以像表一样进行查询，但不实际存储数据。

优点：

简化查询：可以将复杂的查询封装在视图中，使查询更加简洁和易于理解。
数据安全：可以限制用户对敏感数据的访问，只提供用户需要的部分数据。
逻辑独立性：当底层表的结构发生变化时，只要视图的定义不变，查询视图的代码无需修改。

示例代码：

-- 创建视图
CREATE VIEW CustomerOrdersView AS
SELECT c.name AS customer_name, o.order_date, oi.product_name, oi.price, oi.quantity
FROM customers c
JOIN orders o ON c.id = o.customer_id
JOIN order_items oi ON o.id = oi.order_id;

-- 查询视图
SELECT * FROM CustomerOrdersView WHERE customer_name = 'John Doe';

三、索引

索引是一种数据结构，用于提高数据库查询的性能。索引可以快速定位表中的数据，减少查询的时间。

类型：

主键索引：唯一标识表中的每一行，通常在表的主键上自动创建。
唯一索引：确保表中某一列的值是唯一的。
普通索引：可以提高对特定列的查询性能。

示例代码：

-- 创建普通索引
CREATE INDEX idx_product_name ON products(name);

-- 创建唯一索引
CREATE UNIQUE INDEX idx_email ON users(email);

四、触发器

触发器是一种特殊的存储过程，它在特定的表上自动执行，以响应特定的数据库事件，如插入、更新或删除操作。

示例代码：

-- 创建触发器
DELIMITER //
CREATE TRIGGER UpdateStockAfterInsertOrderItem
AFTER INSERT ON order_items
FOR EACH ROW
BEGIN
    UPDATE products SET stock = stock - NEW.quantity WHERE id = NEW.product_id;
END //
DELIMITER ;