一、SQL 核心概述关系型数据库的 “操作语言”SQLStructured Query Language是关系型数据库的标准编程语言核心作用是存取、查询、更新和管理数据支持从简单的数据增删改查到复杂的多表关联、数据统计、事务控制等场景。本文将围绕 “基础操作→进阶特性→实战技巧” 三层结构展开所有示例基于MySQL 8.0兼容 PostgreSQL、SQL Server差异会标注沿用基础表user_info并新增关联表order_info用于进阶场景-- 基础表用户信息表沿用前文结构补充完整约束 CREATE TABLE user_info ( id INT PRIMARY KEY AUTO_INCREMENT, username VARCHAR(50) NOT NULL UNIQUE, age TINYINT CHECK (age 0 AND age gender ENUM(male, female, other), email VARCHAR(100) NOT NULL, create_time DATETIME DEFAULT CURRENT_TIMESTAMP, update_time DATETIME ON UPDATE CURRENT_TIMESTAMP ); -- 关联表订单信息表用于联表查询示例 CREATE TABLE order_info ( order_id INT PRIMARY KEY AUTO_INCREMENT, user_id INT NOT NULL, -- 关联 user_info.id order_amount DECIMAL(10,2) NOT NULL, order_status TINYINT NOT NULL COMMENT 0-待支付1-已支付2-已取消, create_time DATETIME DEFAULT CURRENT_TIMESTAMP, -- 外键约束确保 user_id 对应 user_info 中存在的用户 FOREIGN KEY (user_id) REFERENCES user_info(id) ON DELETE CASCADE );二、SQL 基础操作CRUD 核心用法回顾与补充CRUD 是 SQL 最基础的四大操作覆盖单表数据全生命周期前文已详细讲解核心用法此处补充高频场景扩展1. 新增Create插入与批量插入优化-- 扩展插入查询结果将查询到的数据直接插入表中 INSERT INTO user_info (username, age, gender, email) SELECT copy_user, age, gender, CONCAT(copy_, email) FROM user_info WHERE id 1; -- 复制 id1 的用户数据修改用户名和邮箱 -- 批量插入优化指定字段顺序避免因表结构变更报错 INSERT INTO order_info (user_id, order_amount, order_status) VALUES (1, 99.90, 1), (1, 199.50, 1), (2, 299.00, 0);2. 查询Read单表查询补充场景-- 扩展1条件查询 - 多值匹配优化IN 与 EXISTS 对比 -- 适合少量值匹配 SELECT username, email FROM user_info WHERE id IN (1,2,3); -- 适合大量值匹配性能更优 SELECT username, email FROM user_info u WHERE EXISTS (SELECT 1 FROM order_info o WHERE o.user_id u.id); -- 扩展2日期范围查询常用业务场景 SELECT username, create_time FROM user_info WHERE create_time BETWEEN 2026-01-01 00:00:00 AND 2026-12-31 23:59:59; -- 扩展3模糊查询优化前缀匹配可命中索引后缀/中间匹配无法命中 SELECT username FROM user_info WHERE username LIKE 张%; -- 推荐前缀匹配 SELECT username FROM user_info WHERE username LIKE %三; -- 不推荐后缀匹配3. 更新Update多表关联更新-- 扩展根据关联表数据更新目标表例如给有已支付订单的用户年龄1 UPDATE user_info u JOIN order_info o ON u.id o.user_id SET u.age u.age 1 WHERE o.order_status 1; -- 仅更新有已支付订单的用户4. 删除Delete关联删除与条件优化-- 扩展多表关联删除删除无订单的用户 DELETE u FROM user_info u LEFT JOIN order_info o ON u.id o.user_id WHERE o.user_id IS NULL; -- 左连接后筛选无关联订单的用户三、SQL 进阶特性从单表到复杂场景1. 联表查询JOIN多表关联核心用法联表查询是 SQL 进阶的核心用于从多个关联表中提取数据常用JOIN类型JOIN 类型说明示例场景INNER JOIN内连接只返回两表中匹配的记录查询有订单的用户及订单信息LEFT JOIN左连接返回左表所有记录右表匹配不到则为 NULL查询所有用户及关联订单无订单用户也显示RIGHT JOIN右连接返回右表所有记录左表匹配不到则为 NULL查询所有订单及关联用户无用户的订单也显示FULL JOIN全连接返回两表所有记录匹配不到则为 NULLMySQL 不直接支持需用UNION模拟实战示例-- 1. 内连接查询有已支付订单的用户信息及订单金额 SELECT u.username, u.email, o.order_id, o.order_amount, o.order_status FROM user_info u INNER JOIN order_info o ON u.id o.user_id WHERE o.order_status 1; -- 2. 左连接查询所有用户及关联订单无订单用户的订单字段为 NULL SELECT u.username, o.order_id, IFNULL(o.order_amount, 0) AS order_amount -- 用 IFNULL 处理 NULL 值 FROM user_info u LEFT JOIN order_info o ON u.id o.user_id; -- 3. 模拟全连接MySQL 特有查询所有用户和所有订单的关联关系 SELECT u.username, o.order_id FROM user_info u LEFT JOIN order_info o ON u.id o.user_id UNION SELECT u.username, o.order_id FROM user_info u RIGHT JOIN order_info o ON u.id o.user_id;2. 子查询嵌套查询逻辑子查询是将一个查询结果作为另一个查询的条件或数据源分为相关子查询依赖外部查询和非相关子查询独立执行-- 1. 非相关子查询查询订单金额大于平均订单金额的订单 SELECT order_id, order_amount FROM order_info WHERE order_amount (SELECT AVG(order_amount) FROM order_info); -- 子查询独立执行 -- 2. 相关子查询查询每个用户的最大金额订单 SELECT o.user_id, o.order_id, o.order_amount FROM order_info o WHERE o.order_amount ( SELECT MAX(order_amount) FROM order_info WHERE user_id o.user_id -- 子查询依赖外部查询的 user_id ); -- 3. 子查询用于 IN 条件查询有订单金额200的用户 SELECT username FROM user_info WHERE id IN (SELECT DISTINCT user_id FROM order_info WHERE order_amount 200);3. 事务Transaction保证数据一致性事务是一组不可分割的 SQL 操作遵循ACID 原则原子性、一致性、隔离性、持久性用于解决并发场景下的数据一致性问题如转账、下单支付-- 事务示例用户下单支付扣减余额→创建订单→更新订单状态 START TRANSACTION; -- 开启事务 -- 1. 假设存在余额表扣减用户余额示例语句 UPDATE user_wallet SET balance balance - 199.90 WHERE user_id 1; -- 2. 创建订单 INSERT INTO order_info (user_id, order_amount, order_status) VALUES (1, 199.90, 1); -- 3. 验证操作是否成功成功则提交失败则回滚 IF ROWCOUNT 0 THEN COMMIT; -- 提交事务所有操作生效 ELSE ROLLBACK; -- 回滚事务所有操作撤销 END IF; -- 简化写法使用 TRY...CATCHMySQL 8.0 支持 BEGIN TRY START TRANSACTION; UPDATE user_wallet SET balance balance - 199.90 WHERE user_id 1; INSERT INTO order_info (user_id, order_amount, order_status) VALUES (1, 199.90, 1); COMMIT; END TRY BEGIN CATCH ROLLBACK; SELECT 事务执行失败 ERROR_MESSAGE() AS error_msg; END CATCH;4. 索引提升查询性能索引是数据库优化的核心通过建立 “数据目录” 减少全表扫描常用索引类型主键索引、唯一索引、普通索引、联合索引-- 1. 创建普通索引用于查询条件列 CREATE INDEX idx_user_info_age ON user_info(age); -- 给 age 列建索引 CREATE INDEX idx_order_info_user_id ON order_info(user_id); -- 给关联列建索引 -- 2. 创建联合索引适合多列查询条件遵循“最左前缀原则” CREATE INDEX idx_user_info_gender_age ON user_info(gender, age); -- 先匹配 gender再匹配 age -- 3. 查看索引使用情况验证索引是否生效 EXPLAIN SELECT username FROM user_info WHERE gender female AND age 25; -- 会使用联合索引 -- 4. 删除无用索引避免占用空间 DROP INDEX idx_user_info_age ON user_info;索引最佳实践给查询频繁的列WHERE、JOIN、ORDER BY 后的列建索引避免给更新频繁、数据重复率高的列建索引如性别列重复率高索引效果差联合索引需按 “查询频率从高到低” 排列列顺序。5. 函数与表达式数据处理与格式化SQL 内置丰富函数用于数据计算、字符串处理、日期格式化等-- 1. 字符串函数 SELECT CONCAT(username, -, gender) AS user_label, -- 拼接字符串 LENGTH(username) AS name_length, -- 字符串长度 UPPER(email) AS email_upper -- 转大写 FROM user_info; -- 2. 日期函数常用业务场景 SELECT create_time, DATE_FORMAT(create_time, %Y-%m-%d) AS create_date, -- 格式化日期 DATEDIFF(NOW(), create_time) AS days_since_create -- 计算距今天数 FROM user_info; -- 3. 聚合函数进阶分组统计 SELECT DATE_FORMAT(o.create_time, %Y-%m) AS order_month, -- 按年月分组 COUNT(o.order_id) AS order_count, -- 订单数 SUM(o.order_amount) AS total_amount, -- 总金额 AVG(o.order_amount) AS avg_amount -- 平均金额 FROM order_info o GROUP BY order_month HAVING total_amount 1000; -- 筛选月总金额1000的记录四、SQL 实战场景综合案例需求统计 2026 年每个月的用户下单情况包括用户名、下单数、总金额、最大订单金额SELECT u.username, DATE_FORMAT(o.create_time, %Y-%m) AS order_month, COUNT(o.order_id) AS order_count, SUM(o.order_amount) AS total_amount, MAX(o.order_amount) AS max_amount FROM user_info u INNER JOIN order_info o ON u.id o.user_id WHERE o.create_time BETWEEN 2026-01-01 AND 2026-12-31 GROUP BY u.username, order_month ORDER BY order_month DESC, total_amount DESC;五、SQL 避坑指南与最佳实践1. 性能优化避免SELECT *只查询需要的列联表查询时小表在前、大表在后INNER JOIN 不影响LEFT JOIN 需注意避免在 WHERE 子句中使用函数如DATE(create_time) 2026-01-01会导致索引失效批量操作优先用INSERT ... VALUES (...)、UPDATE ... JOIN减少 SQL 执行次数。2. 兼容性处理分页语法MySQL 用LIMITSQL Server 用TOPPostgreSQL 用LIMIT/OFFSET日期函数MySQL 用DATE_FORMATSQL Server 用FORMATPostgreSQL 用TO_CHAR字符串拼接MySQL 用CONCATSQL Server 用PostgreSQL 用||。参考资料MySQL 8.0 官方文档https://dev.mysql.com/doc/refman/8.0/en/SQL 核心语法规范https://www.w3schools.com/sql/
SQL 用法详解：从基础操作到进阶实战的全场景指南
一、SQL 核心概述关系型数据库的 “操作语言”SQLStructured Query Language是关系型数据库的标准编程语言核心作用是存取、查询、更新和管理数据支持从简单的数据增删改查到复杂的多表关联、数据统计、事务控制等场景。本文将围绕 “基础操作→进阶特性→实战技巧” 三层结构展开所有示例基于MySQL 8.0兼容 PostgreSQL、SQL Server差异会标注沿用基础表user_info并新增关联表order_info用于进阶场景-- 基础表用户信息表沿用前文结构补充完整约束 CREATE TABLE user_info ( id INT PRIMARY KEY AUTO_INCREMENT, username VARCHAR(50) NOT NULL UNIQUE, age TINYINT CHECK (age 0 AND age gender ENUM(male, female, other), email VARCHAR(100) NOT NULL, create_time DATETIME DEFAULT CURRENT_TIMESTAMP, update_time DATETIME ON UPDATE CURRENT_TIMESTAMP ); -- 关联表订单信息表用于联表查询示例 CREATE TABLE order_info ( order_id INT PRIMARY KEY AUTO_INCREMENT, user_id INT NOT NULL, -- 关联 user_info.id order_amount DECIMAL(10,2) NOT NULL, order_status TINYINT NOT NULL COMMENT 0-待支付1-已支付2-已取消, create_time DATETIME DEFAULT CURRENT_TIMESTAMP, -- 外键约束确保 user_id 对应 user_info 中存在的用户 FOREIGN KEY (user_id) REFERENCES user_info(id) ON DELETE CASCADE );二、SQL 基础操作CRUD 核心用法回顾与补充CRUD 是 SQL 最基础的四大操作覆盖单表数据全生命周期前文已详细讲解核心用法此处补充高频场景扩展1. 新增Create插入与批量插入优化-- 扩展插入查询结果将查询到的数据直接插入表中 INSERT INTO user_info (username, age, gender, email) SELECT copy_user, age, gender, CONCAT(copy_, email) FROM user_info WHERE id 1; -- 复制 id1 的用户数据修改用户名和邮箱 -- 批量插入优化指定字段顺序避免因表结构变更报错 INSERT INTO order_info (user_id, order_amount, order_status) VALUES (1, 99.90, 1), (1, 199.50, 1), (2, 299.00, 0);2. 查询Read单表查询补充场景-- 扩展1条件查询 - 多值匹配优化IN 与 EXISTS 对比 -- 适合少量值匹配 SELECT username, email FROM user_info WHERE id IN (1,2,3); -- 适合大量值匹配性能更优 SELECT username, email FROM user_info u WHERE EXISTS (SELECT 1 FROM order_info o WHERE o.user_id u.id); -- 扩展2日期范围查询常用业务场景 SELECT username, create_time FROM user_info WHERE create_time BETWEEN 2026-01-01 00:00:00 AND 2026-12-31 23:59:59; -- 扩展3模糊查询优化前缀匹配可命中索引后缀/中间匹配无法命中 SELECT username FROM user_info WHERE username LIKE 张%; -- 推荐前缀匹配 SELECT username FROM user_info WHERE username LIKE %三; -- 不推荐后缀匹配3. 更新Update多表关联更新-- 扩展根据关联表数据更新目标表例如给有已支付订单的用户年龄1 UPDATE user_info u JOIN order_info o ON u.id o.user_id SET u.age u.age 1 WHERE o.order_status 1; -- 仅更新有已支付订单的用户4. 删除Delete关联删除与条件优化-- 扩展多表关联删除删除无订单的用户 DELETE u FROM user_info u LEFT JOIN order_info o ON u.id o.user_id WHERE o.user_id IS NULL; -- 左连接后筛选无关联订单的用户三、SQL 进阶特性从单表到复杂场景1. 联表查询JOIN多表关联核心用法联表查询是 SQL 进阶的核心用于从多个关联表中提取数据常用JOIN类型JOIN 类型说明示例场景INNER JOIN内连接只返回两表中匹配的记录查询有订单的用户及订单信息LEFT JOIN左连接返回左表所有记录右表匹配不到则为 NULL查询所有用户及关联订单无订单用户也显示RIGHT JOIN右连接返回右表所有记录左表匹配不到则为 NULL查询所有订单及关联用户无用户的订单也显示FULL JOIN全连接返回两表所有记录匹配不到则为 NULLMySQL 不直接支持需用UNION模拟实战示例-- 1. 内连接查询有已支付订单的用户信息及订单金额 SELECT u.username, u.email, o.order_id, o.order_amount, o.order_status FROM user_info u INNER JOIN order_info o ON u.id o.user_id WHERE o.order_status 1; -- 2. 左连接查询所有用户及关联订单无订单用户的订单字段为 NULL SELECT u.username, o.order_id, IFNULL(o.order_amount, 0) AS order_amount -- 用 IFNULL 处理 NULL 值 FROM user_info u LEFT JOIN order_info o ON u.id o.user_id; -- 3. 模拟全连接MySQL 特有查询所有用户和所有订单的关联关系 SELECT u.username, o.order_id FROM user_info u LEFT JOIN order_info o ON u.id o.user_id UNION SELECT u.username, o.order_id FROM user_info u RIGHT JOIN order_info o ON u.id o.user_id;2. 子查询嵌套查询逻辑子查询是将一个查询结果作为另一个查询的条件或数据源分为相关子查询依赖外部查询和非相关子查询独立执行-- 1. 非相关子查询查询订单金额大于平均订单金额的订单 SELECT order_id, order_amount FROM order_info WHERE order_amount (SELECT AVG(order_amount) FROM order_info); -- 子查询独立执行 -- 2. 相关子查询查询每个用户的最大金额订单 SELECT o.user_id, o.order_id, o.order_amount FROM order_info o WHERE o.order_amount ( SELECT MAX(order_amount) FROM order_info WHERE user_id o.user_id -- 子查询依赖外部查询的 user_id ); -- 3. 子查询用于 IN 条件查询有订单金额200的用户 SELECT username FROM user_info WHERE id IN (SELECT DISTINCT user_id FROM order_info WHERE order_amount 200);3. 事务Transaction保证数据一致性事务是一组不可分割的 SQL 操作遵循ACID 原则原子性、一致性、隔离性、持久性用于解决并发场景下的数据一致性问题如转账、下单支付-- 事务示例用户下单支付扣减余额→创建订单→更新订单状态 START TRANSACTION; -- 开启事务 -- 1. 假设存在余额表扣减用户余额示例语句 UPDATE user_wallet SET balance balance - 199.90 WHERE user_id 1; -- 2. 创建订单 INSERT INTO order_info (user_id, order_amount, order_status) VALUES (1, 199.90, 1); -- 3. 验证操作是否成功成功则提交失败则回滚 IF ROWCOUNT 0 THEN COMMIT; -- 提交事务所有操作生效 ELSE ROLLBACK; -- 回滚事务所有操作撤销 END IF; -- 简化写法使用 TRY...CATCHMySQL 8.0 支持 BEGIN TRY START TRANSACTION; UPDATE user_wallet SET balance balance - 199.90 WHERE user_id 1; INSERT INTO order_info (user_id, order_amount, order_status) VALUES (1, 199.90, 1); COMMIT; END TRY BEGIN CATCH ROLLBACK; SELECT 事务执行失败 ERROR_MESSAGE() AS error_msg; END CATCH;4. 索引提升查询性能索引是数据库优化的核心通过建立 “数据目录” 减少全表扫描常用索引类型主键索引、唯一索引、普通索引、联合索引-- 1. 创建普通索引用于查询条件列 CREATE INDEX idx_user_info_age ON user_info(age); -- 给 age 列建索引 CREATE INDEX idx_order_info_user_id ON order_info(user_id); -- 给关联列建索引 -- 2. 创建联合索引适合多列查询条件遵循“最左前缀原则” CREATE INDEX idx_user_info_gender_age ON user_info(gender, age); -- 先匹配 gender再匹配 age -- 3. 查看索引使用情况验证索引是否生效 EXPLAIN SELECT username FROM user_info WHERE gender female AND age 25; -- 会使用联合索引 -- 4. 删除无用索引避免占用空间 DROP INDEX idx_user_info_age ON user_info;索引最佳实践给查询频繁的列WHERE、JOIN、ORDER BY 后的列建索引避免给更新频繁、数据重复率高的列建索引如性别列重复率高索引效果差联合索引需按 “查询频率从高到低” 排列列顺序。5. 函数与表达式数据处理与格式化SQL 内置丰富函数用于数据计算、字符串处理、日期格式化等-- 1. 字符串函数 SELECT CONCAT(username, -, gender) AS user_label, -- 拼接字符串 LENGTH(username) AS name_length, -- 字符串长度 UPPER(email) AS email_upper -- 转大写 FROM user_info; -- 2. 日期函数常用业务场景 SELECT create_time, DATE_FORMAT(create_time, %Y-%m-%d) AS create_date, -- 格式化日期 DATEDIFF(NOW(), create_time) AS days_since_create -- 计算距今天数 FROM user_info; -- 3. 聚合函数进阶分组统计 SELECT DATE_FORMAT(o.create_time, %Y-%m) AS order_month, -- 按年月分组 COUNT(o.order_id) AS order_count, -- 订单数 SUM(o.order_amount) AS total_amount, -- 总金额 AVG(o.order_amount) AS avg_amount -- 平均金额 FROM order_info o GROUP BY order_month HAVING total_amount 1000; -- 筛选月总金额1000的记录四、SQL 实战场景综合案例需求统计 2026 年每个月的用户下单情况包括用户名、下单数、总金额、最大订单金额SELECT u.username, DATE_FORMAT(o.create_time, %Y-%m) AS order_month, COUNT(o.order_id) AS order_count, SUM(o.order_amount) AS total_amount, MAX(o.order_amount) AS max_amount FROM user_info u INNER JOIN order_info o ON u.id o.user_id WHERE o.create_time BETWEEN 2026-01-01 AND 2026-12-31 GROUP BY u.username, order_month ORDER BY order_month DESC, total_amount DESC;五、SQL 避坑指南与最佳实践1. 性能优化避免SELECT *只查询需要的列联表查询时小表在前、大表在后INNER JOIN 不影响LEFT JOIN 需注意避免在 WHERE 子句中使用函数如DATE(create_time) 2026-01-01会导致索引失效批量操作优先用INSERT ... VALUES (...)、UPDATE ... JOIN减少 SQL 执行次数。2. 兼容性处理分页语法MySQL 用LIMITSQL Server 用TOPPostgreSQL 用LIMIT/OFFSET日期函数MySQL 用DATE_FORMATSQL Server 用FORMATPostgreSQL 用TO_CHAR字符串拼接MySQL 用CONCATSQL Server 用PostgreSQL 用||。参考资料MySQL 8.0 官方文档https://dev.mysql.com/doc/refman/8.0/en/SQL 核心语法规范https://www.w3schools.com/sql/
