SQL炼金术：精通100条常用SQL语句，让数据库操作效率飞升

内容摘要：SQL炼金术：精通100条常用SQL语句，让数据库操作效率飞升,

在后端开发中，SQL语句的编写和优化至关重要。一条好的SQL语句能够显著提升系统性能，降低服务器压力。 很多时候，我们面对着各种复杂的业务场景，需要灵活运用各种SQL技巧。本文将深入探讨100条常用SQL语句，并通过具体的案例分析，帮助你掌握SQL优化的核心技巧。

常见查询语句

SELECT语句

SELECT 语句用于从数据库中检索数据。它是SQL中最常用的语句之一。

-- 查询所有列
SELECT * FROM users;

-- 查询指定列
SELECT id, name, email FROM users;

-- 使用别名
SELECT id AS user_id, name AS user_name FROM users;

-- 条件查询
SELECT * FROM users WHERE age > 18;

-- 模糊查询
SELECT * FROM products WHERE product_name LIKE '%键盘%';

-- IN 查询
SELECT * FROM orders WHERE order_id IN (1, 2, 3);

-- BETWEEN 查询
SELECT * FROM products WHERE price BETWEEN 100 AND 200;

-- IS NULL 查询
SELECT * FROM users WHERE email IS NULL;

-- IS NOT NULL 查询
SELECT * FROM users WHERE email IS NOT NULL;

JOIN语句

JOIN 语句用于将来自两个或多个表的行组合在一起。常见的JOIN类型包括 INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN。

-- INNER JOIN
SELECT orders.order_id, users.name FROM orders INNER JOIN users ON orders.user_id = users.id;

-- LEFT JOIN
SELECT orders.order_id, users.name FROM orders LEFT JOIN users ON orders.user_id = users.id;

-- RIGHT JOIN
SELECT orders.order_id, users.name FROM orders RIGHT JOIN users ON orders.user_id = users.id;

-- FULL OUTER JOIN (MySQL不支持FULL OUTER JOIN，需要使用UNION ALL模拟)
SELECT orders.order_id, users.name FROM orders LEFT JOIN users ON orders.user_id = users.id
UNION ALL
SELECT orders.order_id, users.name FROM orders RIGHT JOIN users ON orders.user_id = users.id WHERE orders.user_id IS NULL;

GROUP BY语句

GROUP BY 语句用于将结果集按照一个或多个列进行分组，通常与聚合函数一起使用，例如 COUNT, SUM, AVG, MAX, MIN。

-- 统计每个用户的订单数量
SELECT user_id, COUNT(*) AS order_count FROM orders GROUP BY user_id;

-- 统计每个产品的平均价格
SELECT product_id, AVG(price) AS average_price FROM products GROUP BY product_id;

-- 使用HAVING子句过滤分组后的结果
SELECT user_id, COUNT(*) AS order_count FROM orders GROUP BY user_id HAVING COUNT(*) > 5;

ORDER BY语句

ORDER BY 语句用于对结果集进行排序，可以按照一个或多个列进行升序 (ASC) 或降序 (DESC) 排序。

-- 按照用户年龄升序排序
SELECT * FROM users ORDER BY age ASC;

-- 按照订单日期降序排序
SELECT * FROM orders ORDER BY order_date DESC;

-- 按照多个列进行排序
SELECT * FROM products ORDER BY category_id ASC, price DESC;

LIMIT语句

LIMIT 语句用于限制结果集返回的行数，常用于分页查询。

-- 返回前10条记录
SELECT * FROM products LIMIT 10;

-- 从第11条记录开始，返回10条记录
SELECT * FROM products LIMIT 10, 10; -- LIMIT offset, row_count

常见更新语句

INSERT语句

INSERT 语句用于向表中插入新的数据。

-- 插入一条完整的记录
INSERT INTO users (name, email, age) VALUES ('张三', 'zhangsan@example.com', 25);

-- 插入部分列的数据
INSERT INTO users (name, email) VALUES ('李四', 'lisi@example.com');

-- 从另一个表插入数据
INSERT INTO users (name, email) SELECT name, email FROM temp_users;

UPDATE语句

UPDATE 语句用于更新表中已存在的数据。

-- 更新所有用户的年龄
UPDATE users SET age = age + 1;

-- 更新指定用户的邮箱
UPDATE users SET email = 'new_email@example.com' WHERE id = 1;

-- 使用JOIN更新数据
UPDATE orders INNER JOIN users ON orders.user_id = users.id SET orders.address = users.address WHERE users.city = '北京';

DELETE语句

DELETE 语句用于从表中删除数据。

-- 删除所有数据
DELETE FROM users;

-- 删除指定用户
DELETE FROM users WHERE id = 1;

-- 使用JOIN删除数据
DELETE orders FROM orders INNER JOIN users ON orders.user_id = users.id WHERE users.city = '上海';

高级SQL技巧

子查询

子查询是指嵌套在另一个查询中的查询，可以用于在WHERE、FROM、SELECT等子句中。

-- 查找购买了价格高于平均价格的产品的订单
SELECT * FROM orders WHERE product_id IN (SELECT product_id FROM products WHERE price > (SELECT AVG(price) FROM products));

窗口函数

窗口函数允许您在与查询的其他行相关的行集上执行计算，而无需使用 GROUP BY 子句。常见的窗口函数有 ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE(), LAG(), LEAD()。

-- 为每个用户按照订单日期排序
SELECT order_id, user_id, order_date, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_date) AS row_num FROM orders;

事务控制

事务是一系列数据库操作，要么全部成功执行，要么全部失败回滚。事务控制语句包括 START TRANSACTION, COMMIT, ROLLBACK。

-- 开启事务
START TRANSACTION;

-- 执行一系列操作
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;

-- 提交事务
COMMIT;

-- 回滚事务
ROLLBACK;

SQL性能优化经验

• 使用索引：索引可以显著提高查询速度。确保经常用于 WHERE 子句的列上有索引。考虑复合索引，并注意索引的顺序。
• 避免 SELECT *：只选择需要的列，减少数据传输量。
• 优化 JOIN 查询：选择合适的 JOIN 类型，并确保 JOIN 的列上有索引。
• 避免在 WHERE 子句中使用函数：这会导致索引失效。
• 使用 EXPLAIN 分析查询：EXPLAIN 语句可以帮助你了解查询的执行计划，找到性能瓶颈。
• 批量操作：尽量使用批量插入、更新、删除，减少数据库交互次数。例如，使用 INSERT INTO table VALUES (…), (…), (…); 语句。
• 缓存查询结果：对于不经常变化的数据，可以使用缓存来减少数据库的压力。常用的缓存方案包括Redis、Memcached等，配合 Nginx 反向代理，可以有效提升并发连接数。

掌握这些常用的SQL语句和优化技巧，能让你在后端开发中更加得心应手，写出更高效、更稳定的代码。 这仅仅是冰山一角，还有很多进阶用法，比如存储过程、触发器、自定义函数等等，需要不断学习和实践。

避坑指南

• SQL注入： 务必对用户输入进行严格的校验和过滤，防止 SQL 注入攻击。
• 死锁： 合理设计事务，避免长时间占用资源，导致死锁。
• 慢查询： 定期监控慢查询日志，及时优化 SQL 语句。
• 不恰当的索引： 过多的索引会增加写操作的负担，需要权衡读写性能。

SQL 优化是一个持续学习的过程，希望以上内容能帮助你入门，并在实际工作中不断提升。

剩余的70+ 条SQL语句可以围绕以下几个方向展开，由于篇幅限制，不再逐一列举，读者可以自行扩展：

• 日期函数：DATE_FORMAT, DATE_ADD, DATE_SUB, TIMESTAMPDIFF 等。
• 字符串函数：CONCAT, SUBSTRING, REPLACE, UPPER, LOWER 等。
• 数学函数：ROUND, CEIL, FLOOR, RAND 等。
• 条件函数：IF, CASE 等。
• 数据库管理语句：CREATE TABLE, ALTER TABLE, DROP TABLE, CREATE INDEX, DROP INDEX 等。
• 权限管理语句：GRANT, REVOKE 等。
• 存储过程和函数：CREATE PROCEDURE, CREATE FUNCTION 等。