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

MySQL 8.4 SQL 全攻略：所有知识点与实战场景

article 2025/2/28 20:11:45

一、引言

MySQL 作为一款广泛使用的开源关系型数据库管理系统，在数据存储和管理领域占据着重要地位。MySQL 8.4 版本在性能、功能和安全性等方面都有了显著的提升。本文将全面介绍 MySQL 8.4 中 SQL 的各种知识点，并结合实战场景进行详细讲解，帮助读者更好地掌握和应用 MySQL。

二、数据库操作

2.1 创建数据库

创建数据库时，可以指定字符集和排序规则以满足不同的需求。

-- 创建名为 mydb 的数据库，指定字符集为 utf8mb4，排序规则为 utf8mb4_unicode_ci
CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

实战场景：在开发一个多语言的网站时，需要存储不同语言的文本信息，使用 utf8mb4 字符集可以支持更广泛的字符编码。

2.2 查看数据库

使用 SHOW DATABASES 语句可以查看当前 MySQL 服务器中所有的数据库。

SHOW DATABASES;

2.3 选择数据库

使用 USE 语句选择要操作的数据库。

USE mydb;

2.4 删除数据库

删除数据库会永久删除数据库及其包含的所有数据，操作需谨慎。

DROP DATABASE mydb;

实战场景：当项目不再需要某个数据库时，可以使用该语句删除数据库以释放磁盘空间。

2.5 修改数据库属性

可以使用 ALTER DATABASE 语句修改数据库的字符集和排序规则。

ALTER DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

三、表操作

3.1 创建表

创建表时需要定义列名、数据类型和约束条件。

-- 创建名为 users 的表
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

实战场景：在开发一个用户管理系统时，使用该表存储用户的基本信息。

3.2 查看表结构

使用 DESCRIBE 或 SHOW COLUMNS FROM 语句查看表的结构。

DESCRIBE users;
-- 或者
SHOW COLUMNS FROM users;

3.3 修改表

可以使用 ALTER TABLE 语句对表进行各种修改操作，如添加列、修改列的数据类型、删除列等。

-- 添加列
ALTER TABLE users ADD COLUMN last_login TIMESTAMP;

-- 修改列的数据类型
ALTER TABLE users MODIFY COLUMN password VARCHAR(512);

-- 删除列
ALTER TABLE users DROP COLUMN last_login;

实战场景：随着业务的发展，需要在用户表中添加一个新的字段来记录用户的最后登录时间，使用 ALTER TABLE 语句可以方便地实现。

3.4 删除表

删除表会删除表及其包含的所有数据，操作需谨慎。

DROP TABLE users;

3.5 重命名表

使用 RENAME TABLE 语句可以重命名表。

RENAME TABLE users TO new_users;

四、数据操作

4.1 插入数据

可以使用 INSERT INTO 语句向表中插入单条或多条数据。

-- 插入单条数据
INSERT INTO users (username, email, password) VALUES ('john_doe', 'john@example.com', 'hashed_password');

-- 插入多条数据
INSERT INTO users (username, email, password) VALUES 
('jane_smith', 'jane@example.com', 'hashed_password2'),
('bob_johnson', 'bob@example.com', 'hashed_password3');

实战场景：在用户注册时，将用户的信息插入到用户表中。

4.2 更新数据

使用 UPDATE 语句更新表中的数据。

UPDATE users SET password = 'new_hashed_password' WHERE username = 'john_doe';

实战场景：当用户修改密码时，使用该语句更新用户表中的密码字段。

4.3 删除数据

使用 DELETE FROM 语句删除表中的数据。

DELETE FROM users WHERE username = 'bob_johnson';

实战场景：当用户注销账户时，使用该语句从用户表中删除该用户的信息。

4.4 查询数据

使用 SELECT 语句查询表中的数据，可以使用 WHERE 子句进行条件过滤，ORDER BY 进行排序，LIMIT 进行分页等。

-- 查询所有用户的信息
SELECT * FROM users;

-- 查询用户名以 'j' 开头的用户信息
SELECT * FROM users WHERE username LIKE 'j%';

-- 查询所有用户信息，按创建时间降序排列
SELECT * FROM users ORDER BY created_at DESC;

-- 查询前 10 条用户信息
SELECT * FROM users LIMIT 10;

实战场景：在用户列表页面，需要显示所有用户的信息，并且可以根据用户输入的关键字进行筛选和排序。

五、条件查询

5.1 比较运算符

使用比较运算符（如 =, >, <, >=, <=, <>）进行条件过滤。

-- 查询年龄大于 20 的用户信息
SELECT * FROM users WHERE age > 20;

5.2 逻辑运算符

使用逻辑运算符（如 AND, OR, NOT）组合多个条件。

-- 查询年龄大于 20 且性别为男的用户信息
SELECT * FROM users WHERE age > 20 AND gender = 'male';

5.3 范围运算符

使用范围运算符（如 BETWEEN, IN）进行范围查询。

-- 查询年龄在 20 到 30 之间的用户信息
SELECT * FROM users WHERE age BETWEEN 20 AND 30;

-- 查询用户名是 'john_doe' 或 'jane_smith' 的用户信息
SELECT * FROM users WHERE username IN ('john_doe', 'jane_smith');

5.4 模糊查询运算符

使用模糊查询运算符（如 LIKE）进行模糊查询，% 表示任意多个字符，_ 表示任意单个字符。

-- 查询邮箱以 'example.com' 结尾的用户信息
SELECT * FROM users WHERE email LIKE '%example.com';

六、排序和分页

6.1 排序

使用 ORDER BY 语句对查询结果进行排序，可以按单个列或多个列进行排序，默认是升序（ASC），也可以指定降序（DESC）。

-- 按用户的创建时间降序排列
SELECT * FROM users ORDER BY created_at DESC;

-- 先按年龄升序排列，年龄相同的再按用户名升序排列
SELECT * FROM users ORDER BY age ASC, username ASC;

实战场景：在商品列表页面，需要按商品的价格降序排列，以便用户快速找到价格较高的商品。

6.2 分页

使用 LIMIT 语句进行分页，LIMIT offset, count 中，offset 表示从第几行开始取数据（从 0 开始计数），count 表示取多少行数据。

-- 显示第 2 页（每页显示 10 条记录）的用户信息
SELECT * FROM users LIMIT 10, 10;

实战场景：在新闻列表页面，为了提高页面加载速度，通常会采用分页的方式显示新闻，每次只加载 10 条新闻。

七、聚合函数

7.1 常用聚合函数

SUM()：计算列的总和。
AVG()：计算列的平均值。
COUNT()：计算行数。
MAX()：获取列的最大值。
MIN()：获取列的最小值。

-- 计算所有用户的年龄总和
SELECT SUM(age) FROM users;

-- 计算所有用户的平均年龄
SELECT AVG(age) FROM users;

-- 统计用户的数量
SELECT COUNT(*) FROM users;

-- 获取用户的最大年龄
SELECT MAX(age) FROM users;

-- 获取用户的最小年龄
SELECT MIN(age) FROM users;

实战场景：在统计报表中，需要计算某一时间段内的销售总额、平均销售额等数据。

7.2 GROUP BY 子句

使用 GROUP BY 子句将查询结果按指定的列进行分组，通常与聚合函数一起使用。

-- 按性别分组，统计每个性别的用户数量
SELECT gender, COUNT(*) FROM users GROUP BY gender;

7.3 HAVING 子句

使用 HAVING 子句过滤分组后的结果，与 WHERE 子句类似，但 WHERE 用于过滤行，HAVING 用于过滤分组。

-- 按性别分组，统计每个性别的用户数量，只显示用户数量大于 10 的分组
SELECT gender, COUNT(*) FROM users GROUP BY gender HAVING COUNT(*) > 10;

八、连接查询

8.1 内连接（INNER JOIN）

内连接只返回两个表中匹配的记录。

-- 创建订单表
CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    order_amount DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- 查询用户及其订单信息
SELECT users.username, orders.order_amount
FROM users
INNER JOIN orders ON users.id = orders.user_id;

实战场景：在电商系统中，需要查询每个用户的订单信息，使用内连接可以将用户表和订单表进行关联。

8.2 左连接（LEFT JOIN）

左连接返回左表中的所有记录，以及右表中匹配的记录。如果右表中没有匹配的记录，对应列的值为 NULL。

-- 查询所有用户及其订单信息（包括没有订单的用户）
SELECT users.username, orders.order_amount
FROM users
LEFT JOIN orders ON users.id = orders.user_id;

实战场景：在用户管理系统中，需要查询所有用户的订单信息，包括没有订单的用户，使用左连接可以满足需求。

8.3 右连接（RIGHT JOIN）

右连接返回右表中的所有记录，以及左表中匹配的记录。如果左表中没有匹配的记录，对应列的值为 NULL。

-- 查询所有订单及其对应的用户信息（包括没有关联用户的订单）
SELECT users.username, orders.order_amount
FROM users
RIGHT JOIN orders ON users.id = orders.user_id;

8.4 全连接（FULL JOIN）

MySQL 8.4 中没有直接的 FULL JOIN 语法，可以通过 UNION 组合左连接和右连接来实现。

-- 实现全连接
SELECT users.username, orders.order_amount
FROM users
LEFT JOIN orders ON users.id = orders.user_id
UNION
SELECT users.username, orders.order_amount
FROM users
RIGHT JOIN orders ON users.id = orders.user_id;

8.5 自连接

自连接是表与自身进行连接，常用于处理具有层次结构的数据。

-- 假设用户表中有 parent_id 字段表示上级用户
SELECT u1.username AS user, u2.username AS parent_user
FROM users u1
LEFT JOIN users u2 ON u1.parent_id = u2.id;

实战场景：在组织架构管理系统中，需要查询每个员工的上级领导信息，使用自连接可以实现。

九、子查询

9.1 标量子查询

标量子查询返回单个值。

-- 查询年龄大于平均年龄的用户信息
SELECT * FROM users WHERE age > (SELECT AVG(age) FROM users);

9.2 行子查询

行子查询返回一行数据。

-- 查询与指定用户年龄和性别相同的用户信息
SELECT * FROM users WHERE (age, gender) = (SELECT age, gender FROM users WHERE id = 1);

9.3 列子查询

列子查询返回一列数据。

-- 查询所有订单金额大于平均订单金额的订单所属的用户信息
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE order_amount > (SELECT AVG(order_amount) FROM orders));

9.4 表子查询

表子查询返回一个表。

-- 查询每个月的订单数量和总金额
SELECT month, COUNT(*) AS order_count, SUM(order_amount) AS total_amount
FROM (
    SELECT MONTH(order_date) AS month, order_amount
    FROM orders
) subquery
GROUP BY month;

十、事务处理

10.1 事务的特性

事务遵循 ACID 原则，即原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability）。

10.2 事务控制语句

11.2 创建索引

START TRANSACTION：开始一个事务。
COMMIT：提交事务，将事务中的所有操作永久保存到数据库。
ROLLBACK：回滚事务，撤销事务中的所有操作。
SAVEPOINT：在事务中设置保存点。
ROLLBACK TO SAVEPOINT：回滚到指定的保存点。
```
-- 开始事务
START TRANSACTION;

-- 设置保存点
SAVEPOINT sp1;

-- 执行一系列操作
UPDATE users SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET amount = amount + 100 WHERE user_id = 1;

-- 如果出现错误，回滚到保存点
IF (出现错误条件) THEN
    ROLLBACK TO SAVEPOINT sp1;
ELSE
    COMMIT;
END IF;
```
实战场景：在银行转账系统中，需要保证转账操作的原子性，使用事务可以确保转账过程中不会出现数据不一致的情况。

10.3 事务隔离级别

MySQL 支持四种事务隔离级别，分别是 READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ（默认）和 SERIALIZABLE。不同的隔离级别在并发性能和数据一致性上有不同的表现。
```
-- 设置事务隔离级别为 READ COMMITTED
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
```
十一、索引操作

11.1 索引类型
普通索引：用于提高查询效率。
唯一索引：保证列中的值唯一。
主键索引：是一种特殊的唯一索引，用于唯一标识表中的每一行。

全文索引：用于全文搜索。

-- 创建普通索引
CREATE INDEX idx_username ON users (username);

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

-- 创建全文索引
CREATE FULLTEXT INDEX idx_fulltext_content ON articles (content);

实战场景：在用户表中，经常根据用户名进行查询，为用户名列创建普通索引可以提高查询效率。

11.3 删除索引

DROP INDEX idx_username ON users;

11.4 查看索引

SHOW INDEX FROM users;

十二、视图

12.1 创建视图

视图是虚拟的表，基于一个或多个表的查询结果。

-- 创建一个视图，显示用户的用户名和订单总金额
CREATE VIEW user_order_summary AS
SELECT users.username, SUM(orders.order_amount) AS total_amount
FROM users
LEFT JOIN orders ON users.id = orders.user_id
GROUP BY users.id;

实战场景：在数据分析系统中，经常需要查询用户的订单总金额，创建视图可以简化查询操作。

12.2 查询视图

可以像查询普通表一样查询视图。

SELECT * FROM user_order_summary;

12.3 修改视图

ALTER VIEW user_order_summary AS
SELECT users.username, SUM(orders.order_amount) AS total_amount
FROM users
INNER JOIN orders ON users.id = orders.user_id
GROUP BY users.id;

12.4 删除视图

DROP VIEW user_order_summary;

十三、存储过程和函数

13.1 存储过程

存储过程是一组预编译的 SQL 语句，存储在数据库中，可以通过名称调用。

-- 创建一个存储过程，用于更新用户的余额
DELIMITER //
CREATE PROCEDURE UpdateUserBalance(IN user_id INT, IN amount DECIMAL(10, 2))
BEGIN
    UPDATE users SET balance = balance + amount WHERE id = user_id;
    SELECT balance FROM users WHERE id = user_id;
END //
DELIMITER ;

-- 调用存储过程
CALL UpdateUserBalance(1, 100);

实战场景：在金融系统中，经常需要进行账户余额的更新操作，使用存储过程可以提高代码的复用性和安全性。

13.2 函数

函数与存储过程类似，但函数必须有返回值，并且可以在 SQL 语句中像普通函数一样调用。

-- 创建一个函数，用于计算两个数的和
DELIMITER //
CREATE FUNCTION AddNumbers(num1 INT, num2 INT)
RETURNS INT
DETERMINISTIC
BEGIN
    DECLARE result INT;
    SET result = num1 + num2;
    RETURN result;
END //
DELIMITER ;

-- 调用函数
SELECT AddNumbers(5, 3);

实战场景

在一个电商系统中，需要根据商品的单价和数量计算商品的总价。可以创建一个函数来实现这个功能。

-- 创建计算商品总价的函数
DELIMITER //
CREATE FUNCTION CalculateTotalPrice(unit_price DECIMAL(10, 2), quantity INT)
RETURNS DECIMAL(10, 2)
DETERMINISTIC
BEGIN
    DECLARE total_price DECIMAL(10, 2);
    SET total_price = unit_price * quantity;
    RETURN total_price;
END //
DELIMITER ;

-- 假设存在 products 表，包含 unit_price 和 quantity 列
SELECT product_id, CalculateTotalPrice(unit_price, quantity) AS total_price
FROM products;

13.3 存储过程和函数的管理

查看存储过程和函数：可以使用 SHOW PROCEDURE STATUS 和 SHOW FUNCTION STATUS 语句查看数据库中所有的存储过程和函数。

SHOW PROCEDURE STATUS WHERE Db = 'your_database_name';
SHOW FUNCTION STATUS WHERE Db = 'your_database_name';

查看存储过程和函数的定义：使用 SHOW CREATE PROCEDURE 和 SHOW CREATE FUNCTION 语句查看存储过程和函数的具体定义。
```
SHOW CREATE PROCEDURE UpdateUserBalance;
SHOW CREATE FUNCTION AddNumbers;
```
删除存储过程和函数：使用 DROP PROCEDURE 和 DROP FUNCTION 语句删除存储过程和函数。
```
DROP PROCEDURE IF EXISTS UpdateUserBalance;
DROP FUNCTION IF EXISTS AddNumbers;
```
十四、触发器

14.1 触发器的概念

触发器是与表相关联的特殊存储过程，当表上发生特定事件（如 INSERT、UPDATE、DELETE）时自动执行。

14.2 创建触发器

-- 创建一个触发器，在插入新用户时记录日志
DELIMITER //
CREATE TRIGGER LogNewUserInsert
AFTER INSERT ON users
FOR EACH ROW
BEGIN
    INSERT INTO user_logs (action, user_id, log_time)
    VALUES ('INSERT', NEW.id, NOW());
END //
DELIMITER ;

代码解释

AFTER INSERT ON users 表示在 users 表插入新记录后触发。
FOR EACH ROW 表示对每一行受影响的记录都执行触发器中的 SQL 语句。
NEW 关键字用于引用插入的新记录，这里通过 NEW.id 获取新用户的 ID。

14.3 触发器的类型

BEFORE 触发器：在事件执行之前执行，可用于数据验证和修改。

-- 创建一个 BEFORE INSERT 触发器，验证用户年龄是否合法
DELIMITER //
CREATE TRIGGER ValidateUserAge
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
    IF NEW.age < 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '年龄不能为负数';
    END IF;
END //
DELIMITER ;

AFTER 触发器：在事件执行之后执行，可用于记录日志、更新关联数据等。前面的 LogNewUserInsert 就是一个 AFTER 触发器的例子。

14.4 删除触发器

DROP TRIGGER IF EXISTS LogNewUserInsert;

实战场景

在一个库存管理系统中，当有商品出库时，需要自动更新库存数量。可以创建一个触发器来实现这个功能。

-- 创建商品表和出库记录表
CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(100),
    stock INT
);

CREATE TABLE product_outbound (
    outbound_id INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT,
    quantity INT,
    outbound_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

-- 创建触发器，在商品出库时更新库存数量
DELIMITER //
CREATE TRIGGER UpdateStockOnOutbound
AFTER INSERT ON product_outbound
FOR EACH ROW
BEGIN
    UPDATE products
    SET stock = stock - NEW.quantity
    WHERE product_id = NEW.product_id;
END //
DELIMITER ;

十五、窗口函数

15.1 窗口函数的概念

窗口函数是对一组行进行计算，并为每行返回一个结果。它不会像聚合函数那样将多行数据合并为一行，而是在每行数据旁边返回计算结果。

15.2 窗口函数的语法

function_name(expression) OVER (window_specification)

其中 function_name 是具体的窗口函数（如 SUM、AVG、RANK 等），expression 是要计算的表达式，window_specification 定义了窗口的范围和排序规则。

15.3 常见窗口函数及示例

RANK()：为每行数据分配一个排名，如果有相同的值，排名会重复，下一个排名会跳过相应的数量。

-- 假设存在 sales 表，包含 product_id 和 sales_amount 列
SELECT 
    product_id,
    sales_amount,
    RANK() OVER (ORDER BY sales_amount DESC) AS sales_rank
FROM 
    sales;

DENSE_RANK()：与 RANK() 类似，但排名不会跳过，即使有相同的值。

SELECT 
    product_id,
    sales_amount,
    DENSE_RANK() OVER (ORDER BY sales_amount DESC) AS sales_rank
FROM 
    sales;

ROW_NUMBER()：为每行数据分配一个唯一的行号，按照指定的排序规则依次递增。

SELECT 
    product_id,
    sales_amount,
    ROW_NUMBER() OVER (ORDER BY sales_amount DESC) AS row_num
FROM 
    sales;

SUM() 作为窗口函数：计算窗口内的累计总和。

SELECT 
    product_id,
    sales_amount,
    SUM(sales_amount) OVER (ORDER BY product_id) AS cumulative_sum
FROM 
    sales;

实战场景

在一个销售数据分析系统中，需要统计每个月的销售排名和累计销售额。

-- 创建 sales 表
CREATE TABLE sales (
    sale_id INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT,
    sale_date DATE,
    sale_amount DECIMAL(10, 2)
);

-- 插入示例数据
INSERT INTO sales (product_id, sale_date, sale_amount) VALUES
(1, '2024-01-01', 100),
(2, '2024-01-02', 200),
(1, '2024-02-01', 150),
(2, '2024-02-02', 250);

-- 查询每个月的销售排名和累计销售额
SELECT 
    sale_date,
    product_id,
    sale_amount,
    RANK() OVER (PARTITION BY MONTH(sale_date) ORDER BY sale_amount DESC) AS monthly_rank,
    SUM(sale_amount) OVER (PARTITION BY MONTH(sale_date) ORDER BY sale_date) AS cumulative_monthly_sales
FROM 
    sales;

十六、公用表表达式（CTE）

16.1 CTE 的概念

公用表表达式是一个临时命名的结果集，它只在当前查询的执行范围内有效。CTE 可以简化复杂查询，提高代码的可读性和可维护性。

16.2 创建和使用 CTE

-- 定义一个 CTE，计算每个部门的平均工资
WITH department_avg_salary AS (
    SELECT 
        department_id,
        AVG(salary) AS avg_salary
    FROM 
        employees
    GROUP BY 
        department_id
)
-- 主查询，查询工资高于所在部门平均工资的员工
SELECT 
    e.employee_id,
    e.department_id,
    e.salary
FROM 
    employees e
JOIN 
    department_avg_salary das ON e.department_id = das.department_id
WHERE 
    e.salary > das.avg_salary;

实战场景

在一个人力资源管理系统中，需要查询每个部门中工资最高的员工信息。可以使用 CTE 来实现这个功能。

-- 创建 employees 表
CREATE TABLE employees (
    employee_id INT AUTO_INCREMENT PRIMARY KEY,
    department_id INT,
    employee_name VARCHAR(100),
    salary DECIMAL(10, 2)
);

-- 插入示例数据
INSERT INTO employees (department_id, employee_name, salary) VALUES
(1, 'Alice', 5000),
(1, 'Bob', 6000),
(2, 'Charlie', 4500),
(2, 'David', 5500);

-- 使用 CTE 查询每个部门中工资最高的员工信息
WITH department_max_salary AS (
    SELECT 
        department_id,
        MAX(salary) AS max_salary
    FROM 
        employees
    GROUP BY 
        department_id
)
SELECT 
    e.employee_id,
    e.department_id,
    e.employee_name,
    e.salary
FROM 
    employees e
JOIN 
    department_max_salary dms ON e.department_id = dms.department_id AND e.salary = dms.max_salary;

十七、分区表

17.1 分区表的概念

分区表是将一个大表按照一定的规则（如范围、列表、哈希等）划分为多个小的分区，每个分区可以单独进行管理和维护。分区表可以提高查询性能、方便数据管理和维护。

17.2 分区类型及示例

范围分区（RANGE）：按照列值的范围进行分区。

-- 创建按订单日期范围分区的订单表
CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    order_date DATE,
    order_amount DECIMAL(10, 2)
)
PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p2025 VALUES LESS THAN MAXVALUE
);

列表分区（LIST）：按照列值的列表进行分区

-- 创建按地区列表分区的用户表
CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    user_name VARCHAR(100),
    region VARCHAR(50)
)
PARTITION BY LIST COLUMNS (region) (
    PARTITION p_north VALUES IN ('North', 'Northeast'),
    PARTITION p_south VALUES IN ('South', 'Southeast'),
    PARTITION p_west VALUES IN ('West', 'Northwest')
);

哈希分区（HASH）：根据列值的哈希值进行分区。

-- 创建按用户 ID 哈希分区的用户表
CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    user_name VARCHAR(100)
)
PARTITION BY HASH(user_id)
PARTITIONS 4;

键分区（KEY）：类似于哈希分区，但使用 MySQL 内部的哈希函数。

-- 创建按用户 ID 键分区的用户表
CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    user_name VARCHAR(100)
)
PARTITION BY KEY(user_id)
PARTITIONS 4;

实战场景

在一个电商系统中，订单表的数据量非常大。为了提高查询性能，可以按订单日期进行范围分区。

-- 创建按订单日期范围分区的订单表
CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    order_date DATE,
    order_amount DECIMAL(10, 2)
)
PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p2025 VALUES LESS THAN MAXVALUE
);

-- 插入示例数据
INSERT INTO orders (order_date, order_amount) VALUES
('2023-01-01', 100),
('2024-02-02', 200),
('2025-03-03', 300);

-- 查询 2024 年的订单信息，MySQL 会只在 p2024 分区中查找
SELECT * FROM orders WHERE YEAR(order_date) = 2024;

十八、总结

MySQL 8.4 提供了丰富的 SQL 功能，涵盖了数据库操作、表操作、数据操作、查询优化、事务处理等多个方面。通过合理运用这些功能，可以构建高效、稳定、安全的数据库应用系统。在实际项目中，需要根据具体的业务需求选择合适的 SQL 语句和数据库特性，同时要注意数据的安全性和性能优化。希望本文能够帮助读者全面掌握 MySQL 8.4 的 SQL 知识，并在实际工作中灵活运用。

查看全文

http://www.kler.cn/a/564976.html

XXE漏洞：原理、危害与修复方法详解

angular登录页

编写第一个 C++ 程序 – Hello World 示例

Go 1.24版本在性能方面有哪些提升？

基于Spark的电商供应链系统的设计与实现

DeepSeek R1 训练策略4个阶段解析

PING命令TTL解析

React Router 完全指南：从基础到高级实践

每天一个Flutter开发小项目 (3) : 高效Flutter学习与产出 - 构建简易天气应用

【Azure 架构师学习笔记】- Azure Databricks (12) -- Medallion Architecture简介

Golang快速上手01/Golang基础

Ollama 简介及安装部署

网络安全高级职称网络安全高级认证

Spring Boot操作MaxComputer（保姆级教程）

深度学习-6.用于计算机视觉的深度学习

使用DeepSeek实现自动化编程：类的自动生成

Java文件操作和IO

若依框架实际国际化前后端统一解决方案

JavaWeb后端基础（1）

【算法与数据结构】Dijkstra算法求单源最短路径问题

一、引言

二、数据库操作

2.1 创建数据库

2.2 查看数据库

2.3 选择数据库

2.4 删除数据库

2.5 修改数据库属性

三、表操作

3.1 创建表

3.2 查看表结构

3.3 修改表

3.4 删除表

3.5 重命名表

四、数据操作

4.1 插入数据

4.2 更新数据

4.3 删除数据

4.4 查询数据

五、条件查询

5.1 比较运算符

5.2 逻辑运算符

5.3 范围运算符

5.4 模糊查询运算符

六、排序和分页

6.1 排序

6.2 分页

七、聚合函数

7.1 常用聚合函数

7.2 GROUP BY 子句

7.3 HAVING 子句

八、连接查询

8.1 内连接（INNER JOIN）

8.2 左连接（LEFT JOIN）

8.3 右连接（RIGHT JOIN）

8.4 全连接（FULL JOIN）

8.5 自连接

九、子查询

9.1 标量子查询

9.2 行子查询

9.3 列子查询

9.4 表子查询

十、事务处理

10.1 事务的特性

10.2 事务控制语句

11.2 创建索引

10.3 事务隔离级别

十一、索引操作

11.1 索引类型

11.3 删除索引

11.4 查看索引

十二、视图

12.1 创建视图

12.2 查询视图

12.3 修改视图

12.4 删除视图

十三、存储过程和函数

13.1 存储过程

13.2 函数

实战场景

13.3 存储过程和函数的管理

十四、触发器

14.1 触发器的概念

14.2 创建触发器

代码解释

14.3 触发器的类型

14.4 删除触发器

实战场景

十五、窗口函数

15.1 窗口函数的概念

15.2 窗口函数的语法

15.3 常见窗口函数及示例

实战场景

十六、公用表表达式（CTE）

16.1 CTE 的概念

16.2 创建和使用 CTE

实战场景

十七、分区表

17.1 分区表的概念

17.2 分区类型及示例

实战场景