MySQL基础语句全解析：从CRUD到高级查询

1. MySQL基础语句概述MySQL作为最流行的开源关系型数据库管理系统其基础语句是每个开发者必须掌握的技能。无论是数据查询、表结构管理还是用户权限控制90个基础语句涵盖了日常开发中的绝大多数场景。这些语句按照功能可以分为以下几大类数据库操作语句CREATE/DROP/ALTER DATABASE表操作语句CREATE/DROP/ALTER TABLE数据操作语句INSERT/UPDATE/DELETE/SELECT索引操作语句CREATE/DROP INDEX视图操作语句CREATE/DROP VIEW用户权限语句GRANT/REVOKE事务控制语句COMMIT/ROLLBACK/SAVEPOINT提示MySQL语句对大小写不敏感但按照行业惯例关键字通常使用大写而数据库名、表名和列名使用小写以提高可读性。2. 数据库与表操作语句2.1 数据库管理创建数据库是最基础的操作之一以下是常用语句-- 创建数据库 CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; -- 查看所有数据库 SHOW DATABASES; -- 使用特定数据库 USE mydb; -- 删除数据库 DROP DATABASE mydb;在实际项目中字符集设置尤为重要。utf8mb4支持完整的Unicode字符集包括emoji表情符号而早期的utf8只能支持部分Unicode字符。2.2 表结构操作表是存储数据的核心结构管理表的语句包括-- 创建表 CREATE TABLE users ( id INT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(50) NOT NULL UNIQUE, email VARCHAR(100) NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINEInnoDB; -- 查看表结构 DESCRIBE users; -- 修改表结构 ALTER TABLE users ADD COLUMN age TINYINT UNSIGNED AFTER email; -- 重命名表 RENAME TABLE users TO members; -- 删除表 DROP TABLE users;在表设计中有几个关键点需要注意主键通常使用自增整数AUTO_INCREMENT字符串类型根据长度选择CHAR定长或VARCHAR变长时间戳字段可以自动维护创建和更新时间存储引擎推荐使用InnoDB支持事务和外键3. 数据增删改查CRUD语句3.1 数据插入插入数据有多种方式各有适用场景-- 基本插入 INSERT INTO users (username, email) VALUES (john, johnexample.com); -- 批量插入效率更高 INSERT INTO users (username, email) VALUES (alice, aliceexample.com), (bob, bobexample.com), (charlie, charlieexample.com); -- 插入时忽略错误 INSERT IGNORE INTO users (username, email) VALUES (john, johnexample.com); -- 替换已有记录 REPLACE INTO users (id, username, email) VALUES (1, john, new_emailexample.com);3.2 数据查询SELECT语句是最复杂也最常用的语句-- 基本查询 SELECT * FROM users; -- 条件查询 SELECT username, email FROM users WHERE age 18 AND email LIKE %example.com; -- 排序 SELECT * FROM users ORDER BY created_at DESC; -- 分页 SELECT * FROM users LIMIT 10 OFFSET 20; -- 等价于 LIMIT 20, 10 -- 分组聚合 SELECT COUNT(*) as count, age FROM users GROUP BY age HAVING count 5; -- 连接查询 SELECT u.username, p.title FROM users u JOIN posts p ON u.id p.user_id WHERE u.created_at 2023-01-01;3.3 数据更新与删除-- 更新数据 UPDATE users SET email newexample.com, age 30 WHERE id 1; -- 删除数据 DELETE FROM users WHERE id 1; -- 清空表不可回滚 TRUNCATE TABLE users;注意生产环境中执行UPDATE和DELETE语句时务必先使用SELECT确认WHERE条件匹配的记录避免误操作。4. 高级查询与优化4.1 子查询与联合查询-- 子查询 SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE amount 100); -- EXISTS子查询 SELECT * FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id u.id); -- 联合查询 SELECT username FROM users WHERE age 18 UNION ALL SELECT username FROM admins WHERE status active;4.2 索引优化索引是提高查询性能的关键-- 创建索引 CREATE INDEX idx_email ON users(email); CREATE UNIQUE INDEX idx_username ON users(username); -- 多列索引 CREATE INDEX idx_name_age ON users(last_name, first_name, age); -- 查看索引 SHOW INDEX FROM users; -- 删除索引 DROP INDEX idx_email ON users;索引使用的最佳实践为WHERE、JOIN和ORDER BY子句中的列创建索引遵循最左前缀原则设计复合索引避免在索引列上使用函数或计算定期分析慢查询并优化索引4.3 事务与锁-- 事务基本用法 START TRANSACTION; INSERT INTO orders (user_id, amount) VALUES (1, 100); UPDATE accounts SET balance balance - 100 WHERE user_id 1; COMMIT; -- 设置隔离级别 SET TRANSACTION ISOLATION LEVEL READ COMMITTED; -- 行级锁 SELECT * FROM users WHERE id 1 FOR UPDATE;5. 视图与存储过程5.1 视图管理视图可以简化复杂查询-- 创建视图 CREATE VIEW active_users AS SELECT * FROM users WHERE last_login DATE_SUB(NOW(), INTERVAL 30 DAY); -- 使用视图 SELECT * FROM active_users WHERE age 18; -- 修改视图 ALTER VIEW active_users AS SELECT * FROM users WHERE status active; -- 删除视图 DROP VIEW active_users;5.2 存储过程与函数-- 创建存储过程 DELIMITER // CREATE PROCEDURE update_user_age(IN user_id INT, IN years INT) BEGIN UPDATE users SET age age years WHERE id user_id; SELECT ROW_COUNT() AS affected_rows; END // DELIMITER ; -- 调用存储过程 CALL update_user_age(1, 1); -- 创建函数 DELIMITER // CREATE FUNCTION get_user_email(p_user_id INT) RETURNS VARCHAR(100) READS SQL DATA BEGIN DECLARE v_email VARCHAR(100); SELECT email INTO v_email FROM users WHERE id p_user_id; RETURN v_email; END // DELIMITER ; -- 使用函数 SELECT get_user_email(1);6. 用户权限管理6.1 用户账户操作-- 创建用户 CREATE USER app_userlocalhost IDENTIFIED BY secure_password; -- 修改密码 ALTER USER app_userlocalhost IDENTIFIED BY new_password; -- 删除用户 DROP USER app_userlocalhost;6.2 权限控制-- 授予权限 GRANT SELECT, INSERT, UPDATE ON mydb.* TO app_userlocalhost; -- 授予所有权限 GRANT ALL PRIVILEGES ON mydb.* TO admin%; -- 撤销权限 REVOKE DELETE ON mydb.* FROM app_userlocalhost; -- 刷新权限 FLUSH PRIVILEGES; -- 查看权限 SHOW GRANTS FOR app_userlocalhost;7. 数据库维护语句7.1 备份与恢复-- 导出数据在命令行执行 mysqldump -u root -p mydb mydb_backup.sql -- 导入数据 mysql -u root -p mydb mydb_backup.sql7.2 状态检查与优化-- 查看数据库状态 SHOW STATUS; -- 查看进程列表 SHOW PROCESSLIST; -- 分析表 ANALYZE TABLE users; -- 优化表 OPTIMIZE TABLE large_table; -- 修复表 REPAIR TABLE corrupted_table;8. 实用技巧与常见问题8.1 日期时间处理-- 当前日期时间 SELECT NOW(), CURDATE(), CURTIME(); -- 日期计算 SELECT DATE_ADD(NOW(), INTERVAL 1 DAY); SELECT DATEDIFF(2023-12-31, 2023-01-01); -- 格式化日期 SELECT DATE_FORMAT(NOW(), %Y-%m-%d %H:%i:%s);8.2 字符串操作-- 字符串连接 SELECT CONCAT(first_name, , last_name) AS full_name FROM users; -- 子字符串 SELECT SUBSTRING(email, 1, 5) FROM users; -- 替换 SELECT REPLACE(description, old, new) FROM products; -- 正则匹配 SELECT * FROM users WHERE username REGEXP ^[a-z]{5}$;8.3 常见问题解决方案忘记root密码# 停止MySQL服务 sudo systemctl stop mysql # 以安全模式启动 sudo mysqld_safe --skip-grant-tables # 登录并修改密码 mysql -u root FLUSH PRIVILEGES; ALTER USER rootlocalhost IDENTIFIED BY new_password;处理大表分批更新UPDATE large_table SET col val WHERE id BETWEEN 1 AND 10000;使用临时表处理复杂操作性能优化使用EXPLAIN分析查询执行计划避免SELECT *只查询需要的列合理使用连接JOIN替代子查询掌握这90个基础语句你已经能够应对MySQL日常开发中的绝大多数需求。在实际工作中建议将这些语句分类整理成自己的速查手册并不断积累特定场景下的优化经验。