1. MySQL 数据库操作基础MySQL作为最流行的开源关系型数据库之一掌握其核心SQL语句是每位开发者的必备技能。我在实际项目中使用MySQL已有8年时间从简单的增删改查到复杂的分析查询积累了不少实战经验。本文将系统梳理MySQL中最常用、最实用的SQL语句包含基础操作、高级查询、性能优化等多个维度。对于刚接触MySQL的新手建议先掌握基础CRUD操作再逐步学习索引优化、事务控制等进阶内容。而对于有经验的开发者本文中的一些优化技巧和特殊语法或许能带来新的启发。无论你是准备面试还是解决实际问题这份SQL语句大全都能作为快速参考手册。2. 数据库与表操作2.1 数据库管理创建数据库是最基础的操作但有几个关键参数需要注意CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;这里我推荐使用utf8mb4字符集而非老旧的utf8因为前者支持完整的Unicode字符包括emoji。COLLATE指定了排序规则utf8mb4_unicode_ci表示不区分大小写的Unicode排序。查看所有数据库SHOW DATABASES;删除数据库慎用DROP DATABASE mydb;切换当前数据库USE mydb;2.2 表结构操作创建表时需要仔细设计字段类型和约束CREATE TABLE users ( id INT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(50) NOT NULL UNIQUE, email VARCHAR(100) NOT NULL, age TINYINT UNSIGNED, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, INDEX idx_email (email) ) ENGINEInnoDB;几个关键点主键使用自增INT是常见做法但大数据量时考虑BIGINTVARCHAR长度根据实际需要设置过大会浪费内存时间戳字段默认值设为当前时间很实用为常用查询字段(email)添加索引提升性能修改表结构ALTER TABLE users ADD COLUMN phone VARCHAR(20) AFTER email;查看表结构DESCRIBE users;3. 数据增删改查(CRUD)3.1 插入数据基础插入语法INSERT INTO users (username, email, age) VALUES (john_doe, johnexample.com, 30);批量插入能显著提高性能INSERT INTO users (username, email, age) VALUES (user1, user1example.com, 25), (user2, user2example.com, 28), (user3, user3example.com, 32);从其他表导入数据INSERT INTO user_backup SELECT * FROM users WHERE created_at 2023-01-01;3.2 查询数据基础查询SELECT id, username, email FROM users WHERE age 25;分页查询注意性能SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 20; -- 获取第3页每页10条模糊查询SELECT * FROM users WHERE username LIKE j% AND email LIKE %example.com;3.3 更新数据单表更新UPDATE users SET age 31, email new_emailexample.com WHERE id 1;多表关联更新UPDATE orders o JOIN users u ON o.user_id u.id SET o.status completed WHERE u.username john_doe;3.4 删除数据条件删除DELETE FROM users WHERE id 1;清空表不可回滚TRUNCATE TABLE logs;4. 高级查询技巧4.1 多表连接查询内连接只返回匹配行SELECT u.username, o.order_date, o.amount FROM users u INNER JOIN orders o ON u.id o.user_id;左连接返回左表所有行SELECT u.username, COUNT(o.id) AS order_count FROM users u LEFT JOIN orders o ON u.id o.user_id GROUP BY u.id;4.2 聚合函数与分组常用聚合函数SELECT COUNT(*) AS total_users, AVG(age) AS avg_age, MAX(created_at) AS latest_user FROM users;分组统计SELECT DATE(created_at) AS signup_date, COUNT(*) AS daily_signups FROM users GROUP BY signup_date HAVING COUNT(*) 5 ORDER BY signup_date DESC;4.3 子查询WHERE子句中的子查询SELECT * FROM products WHERE price (SELECT AVG(price) FROM products);FROM子句中的子查询SELECT u.username, o.order_count FROM users u JOIN ( SELECT user_id, COUNT(*) AS order_count FROM orders GROUP BY user_id ) o ON u.id o.user_id;5. 索引与性能优化5.1 索引管理添加索引ALTER TABLE users ADD INDEX idx_username (username);创建复合索引ALTER TABLE orders ADD INDEX idx_user_status (user_id, status);查看表索引SHOW INDEX FROM users;5.2 查询优化使用EXPLAIN分析查询EXPLAIN SELECT * FROM users WHERE username john_doe;强制使用索引SELECT * FROM users FORCE INDEX (idx_username) WHERE username john_doe;避免全表扫描的技巧-- 不好的写法 SELECT * FROM users WHERE YEAR(created_at) 2023; -- 优化写法 SELECT * FROM users WHERE created_at BETWEEN 2023-01-01 AND 2023-12-31;6. 事务与锁机制6.1 事务控制基本事务START TRANSACTION; UPDATE accounts SET balance balance - 100 WHERE id 1; UPDATE accounts SET balance balance 100 WHERE id 2; COMMIT; -- 出错时使用 ROLLBACK;设置隔离级别SET TRANSACTION ISOLATION LEVEL READ COMMITTED;6.2 锁机制行锁示例SELECT * FROM orders WHERE id 123 FOR UPDATE; -- 获取排他锁表锁慎用LOCK TABLES orders WRITE; -- 执行操作... UNLOCK TABLES;7. 存储过程与触发器7.1 存储过程创建存储过程DELIMITER // CREATE PROCEDURE update_user_age(IN user_id INT, IN age_diff INT) BEGIN UPDATE users SET age age age_diff WHERE id user_id; SELECT ROW_COUNT() AS affected_rows; END // DELIMITER ;调用存储过程CALL update_user_age(1, 1);7.2 触发器创建触发器CREATE TRIGGER before_user_update BEFORE UPDATE ON users FOR EACH ROW BEGIN SET NEW.updated_at NOW(); END;8. 实用技巧与注意事项备份重要数据-- 导出表结构和数据 mysqldump -u username -p mydb backup.sql批量更新时注意-- 每1000条提交一次事务 SET autocommit0; UPDATE large_table SET col1 value WHERE condition; COMMIT; SET autocommit1;处理大数据量表-- 分批次处理 DELETE FROM large_table WHERE id IN ( SELECT id FROM large_table WHERE condition LIMIT 10000 );监控慢查询-- 在my.cnf中配置 slow_query_log 1 slow_query_log_file /var/log/mysql/mysql-slow.log long_query_time 2字符集问题排查SHOW VARIABLES LIKE character_set%; SHOW VARIABLES LIKE collation%;在实际项目中我发现很多性能问题都源于不当的索引设计和低效的查询语句。特别是在处理百万级数据时一个简单的JOIN操作如果没有正确使用索引可能导致整个系统响应变慢。建议定期使用EXPLAIN分析关键查询并建立适当的复合索引。另一个常见问题是事务使用不当。长时间运行的事务会持有锁资源导致其他操作阻塞。在编写事务代码时应尽量缩短事务持续时间避免在事务中进行耗时操作如网络请求、文件IO等。
1. MySQL 数据库操作基础MySQL作为最流行的开源关系型数据库之一掌握其核心SQL语句是每位开发者的必备技能。我在实际项目中使用MySQL已有8年时间从简单的增删改查到复杂的分析查询积累了不少实战经验。本文将系统梳理MySQL中最常用、最实用的SQL语句包含基础操作、高级查询、性能优化等多个维度。对于刚接触MySQL的新手建议先掌握基础CRUD操作再逐步学习索引优化、事务控制等进阶内容。而对于有经验的开发者本文中的一些优化技巧和特殊语法或许能带来新的启发。无论你是准备面试还是解决实际问题这份SQL语句大全都能作为快速参考手册。2. 数据库与表操作2.1 数据库管理创建数据库是最基础的操作但有几个关键参数需要注意CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;这里我推荐使用utf8mb4字符集而非老旧的utf8因为前者支持完整的Unicode字符包括emoji。COLLATE指定了排序规则utf8mb4_unicode_ci表示不区分大小写的Unicode排序。查看所有数据库SHOW DATABASES;删除数据库慎用DROP DATABASE mydb;切换当前数据库USE mydb;2.2 表结构操作创建表时需要仔细设计字段类型和约束CREATE TABLE users ( id INT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(50) NOT NULL UNIQUE, email VARCHAR(100) NOT NULL, age TINYINT UNSIGNED, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, INDEX idx_email (email) ) ENGINEInnoDB;几个关键点主键使用自增INT是常见做法但大数据量时考虑BIGINTVARCHAR长度根据实际需要设置过大会浪费内存时间戳字段默认值设为当前时间很实用为常用查询字段(email)添加索引提升性能修改表结构ALTER TABLE users ADD COLUMN phone VARCHAR(20) AFTER email;查看表结构DESCRIBE users;3. 数据增删改查(CRUD)3.1 插入数据基础插入语法INSERT INTO users (username, email, age) VALUES (john_doe, johnexample.com, 30);批量插入能显著提高性能INSERT INTO users (username, email, age) VALUES (user1, user1example.com, 25), (user2, user2example.com, 28), (user3, user3example.com, 32);从其他表导入数据INSERT INTO user_backup SELECT * FROM users WHERE created_at 2023-01-01;3.2 查询数据基础查询SELECT id, username, email FROM users WHERE age 25;分页查询注意性能SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 20; -- 获取第3页每页10条模糊查询SELECT * FROM users WHERE username LIKE j% AND email LIKE %example.com;3.3 更新数据单表更新UPDATE users SET age 31, email new_emailexample.com WHERE id 1;多表关联更新UPDATE orders o JOIN users u ON o.user_id u.id SET o.status completed WHERE u.username john_doe;3.4 删除数据条件删除DELETE FROM users WHERE id 1;清空表不可回滚TRUNCATE TABLE logs;4. 高级查询技巧4.1 多表连接查询内连接只返回匹配行SELECT u.username, o.order_date, o.amount FROM users u INNER JOIN orders o ON u.id o.user_id;左连接返回左表所有行SELECT u.username, COUNT(o.id) AS order_count FROM users u LEFT JOIN orders o ON u.id o.user_id GROUP BY u.id;4.2 聚合函数与分组常用聚合函数SELECT COUNT(*) AS total_users, AVG(age) AS avg_age, MAX(created_at) AS latest_user FROM users;分组统计SELECT DATE(created_at) AS signup_date, COUNT(*) AS daily_signups FROM users GROUP BY signup_date HAVING COUNT(*) 5 ORDER BY signup_date DESC;4.3 子查询WHERE子句中的子查询SELECT * FROM products WHERE price (SELECT AVG(price) FROM products);FROM子句中的子查询SELECT u.username, o.order_count FROM users u JOIN ( SELECT user_id, COUNT(*) AS order_count FROM orders GROUP BY user_id ) o ON u.id o.user_id;5. 索引与性能优化5.1 索引管理添加索引ALTER TABLE users ADD INDEX idx_username (username);创建复合索引ALTER TABLE orders ADD INDEX idx_user_status (user_id, status);查看表索引SHOW INDEX FROM users;5.2 查询优化使用EXPLAIN分析查询EXPLAIN SELECT * FROM users WHERE username john_doe;强制使用索引SELECT * FROM users FORCE INDEX (idx_username) WHERE username john_doe;避免全表扫描的技巧-- 不好的写法 SELECT * FROM users WHERE YEAR(created_at) 2023; -- 优化写法 SELECT * FROM users WHERE created_at BETWEEN 2023-01-01 AND 2023-12-31;6. 事务与锁机制6.1 事务控制基本事务START TRANSACTION; UPDATE accounts SET balance balance - 100 WHERE id 1; UPDATE accounts SET balance balance 100 WHERE id 2; COMMIT; -- 出错时使用 ROLLBACK;设置隔离级别SET TRANSACTION ISOLATION LEVEL READ COMMITTED;6.2 锁机制行锁示例SELECT * FROM orders WHERE id 123 FOR UPDATE; -- 获取排他锁表锁慎用LOCK TABLES orders WRITE; -- 执行操作... UNLOCK TABLES;7. 存储过程与触发器7.1 存储过程创建存储过程DELIMITER // CREATE PROCEDURE update_user_age(IN user_id INT, IN age_diff INT) BEGIN UPDATE users SET age age age_diff WHERE id user_id; SELECT ROW_COUNT() AS affected_rows; END // DELIMITER ;调用存储过程CALL update_user_age(1, 1);7.2 触发器创建触发器CREATE TRIGGER before_user_update BEFORE UPDATE ON users FOR EACH ROW BEGIN SET NEW.updated_at NOW(); END;8. 实用技巧与注意事项备份重要数据-- 导出表结构和数据 mysqldump -u username -p mydb backup.sql批量更新时注意-- 每1000条提交一次事务 SET autocommit0; UPDATE large_table SET col1 value WHERE condition; COMMIT; SET autocommit1;处理大数据量表-- 分批次处理 DELETE FROM large_table WHERE id IN ( SELECT id FROM large_table WHERE condition LIMIT 10000 );监控慢查询-- 在my.cnf中配置 slow_query_log 1 slow_query_log_file /var/log/mysql/mysql-slow.log long_query_time 2字符集问题排查SHOW VARIABLES LIKE character_set%; SHOW VARIABLES LIKE collation%;在实际项目中我发现很多性能问题都源于不当的索引设计和低效的查询语句。特别是在处理百万级数据时一个简单的JOIN操作如果没有正确使用索引可能导致整个系统响应变慢。建议定期使用EXPLAIN分析关键查询并建立适当的复合索引。另一个常见问题是事务使用不当。长时间运行的事务会持有锁资源导致其他操作阻塞。在编写事务代码时应尽量缩短事务持续时间避免在事务中进行耗时操作如网络请求、文件IO等。
