## 概述 MySQL是最流行的开源关系型数据库管理系统之一。本教程涵盖MySQL的基础操作、查询技巧、表结构管理等常见使用场景，适合初学者和进阶用户学习。 ## 1. MySQL基础 ### 1.1 连接和基本操作 ```sql -- 连接MySQL服务器 mysql -u username -p mysql -u username -p -h hostname -P port -- 查看数据库 SHOW DATABASES; -- 选择数据库 USE database_name; -- 查看当前数据库 SELECT DATABASE(); -- 查看数据库中的表 SHOW TABLES; -- 查看表结构 DESCRIBE table_name; DESC table_name; SHOW COLUMNS FROM table_name; -- 查看创建表的SQL语句 SHOW CREATE TABLE table_name; -- 退出MySQL EXIT; QUIT; ``` ### 1.2 数据库和表的基本操作 ```sql -- 创建数据库 CREATE DATABASE database_name; CREATE DATABASE IF NOT EXISTS database_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; -- 删除数据库 DROP DATABASE database_name; DROP DATABASE IF EXISTS database_name; -- 创建表 CREATE TABLE users ( id INT PRIMARY KEY AUTO_INCREMENT, username VARCHAR(50) NOT NULL UNIQUE, email VARCHAR(100) NOT NULL, age INT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ); -- 删除表 DROP TABLE table_name; DROP TABLE IF EXISTS table_name; -- 重命名表 RENAME TABLE old_name TO new_name; ``` ## 2. 数据类型 ### 2.1 数值类型 ```sql -- 整数类型 TINYINT -- 1字节，-128到127 SMALLINT -- 2字节，-32768到32767 MEDIUMINT -- 3字节 INT -- 4字节 BIGINT -- 8字节 -- 浮点类型 FLOAT -- 单精度浮点数 DOUBLE -- 双精度浮点数 DECIMAL -- 定点数，适合财务计算 -- 示例 CREATE TABLE products ( id INT PRIMARY KEY AUTO_INCREMENT, price DECIMAL(10, 2), -- 总共10位，小数点后2位 weight FLOAT, quantity INT ); ``` ### 2.2 字符串类型 ```sql -- 固定长度字符串 CHAR(n) -- 固定长度，不足用空格填充 -- 可变长度字符串 VARCHAR(n) -- 可变长度，最大65535字节 -- 文本类型 TEXT -- 最大65535字节 MEDIUMTEXT -- 最大16MB LONGTEXT -- 最大4GB -- 示例 CREATE TABLE articles ( id INT PRIMARY KEY AUTO_INCREMENT, title VARCHAR(200) NOT NULL, content TEXT, author CHAR(50), created_at DATETIME ); ``` ### 2.3 日期时间类型 ```sql -- 日期时间类型 DATE -- 日期 'YYYY-MM-DD' TIME -- 时间 'HH:MM:SS' DATETIME -- 日期时间 'YYYY-MM-DD HH:MM:SS' TIMESTAMP -- 时间戳，范围较小 YEAR -- 年份 -- 示例 CREATE TABLE events ( id INT PRIMARY KEY AUTO_INCREMENT, event_name VARCHAR(100), event_date DATE, start_time TIME, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ); ``` ## 3. 基础增删改查 (CRUD) ### 3.1 插入数据 (CREATE) ```sql -- 插入单条记录 INSERT INTO users (username, email, age) VALUES ('john_doe', 'john@example.com', 25); -- 插入多条记录 INSERT INTO users (username, email, age) VALUES ('alice', 'alice@example.com', 23), ('bob', 'bob@example.com', 30), ('charlie', 'charlie@example.com', 28); -- 插入所有字段 INSERT INTO users VALUES (NULL, 'david', 'david@example.com', 35, NOW(), NOW()); -- 从其他表插入数据 INSERT INTO users_backup SELECT * FROM users; ``` ### 3.2 查询数据 (READ) ```sql -- 查询所有数据 SELECT * FROM users; -- 查询特定字段 SELECT username, email FROM users; -- 使用别名 SELECT username AS '用户名', email AS '邮箱' FROM users; -- 去重查询 SELECT DISTINCT age FROM users; -- 限制查询结果数量 SELECT * FROM users LIMIT 5; SELECT * FROM users LIMIT 10, 5; -- 跳过前10条，取5条（偏移量，数量） SELECT * FROM users LIMIT 5 OFFSET 10; -- 同上，更清晰的语法 ``` ### 3.3 更新数据 (UPDATE) ```sql -- 更新单条记录 UPDATE users SET age = 26 WHERE username = 'john_doe'; -- 更新多个字段 UPDATE users SET age = 27, email = 'john_new@example.com' WHERE id = 1; -- 批量更新 UPDATE users SET age = age + 1 WHERE age < 30; -- 基于其他表更新 UPDATE users u SET age = ( SELECT AVG(age) FROM users WHERE age > 0 ) WHERE u.id = 1; ``` ### 3.4 删除数据 (DELETE) ```sql -- 删除单条记录 DELETE FROM users WHERE id = 1; -- 根据条件删除 DELETE FROM users WHERE age < 20; -- 删除所有数据（保留表结构） DELETE FROM users; TRUNCATE TABLE users; -- 更快，重置自增ID -- 基于其他表删除 DELETE FROM users WHERE age < ( SELECT AVG(age) FROM users WHERE age > 0 ); ``` ## 4. 条件查询 ### 4.1 WHERE子句 ```sql -- 基本条件 SELECT * FROM users WHERE age > 25; SELECT * FROM users WHERE age >= 25 AND age <= 35; SELECT * FROM users WHERE age BETWEEN 25 AND 35; -- 字符串条件 SELECT * FROM users WHERE username = 'john_doe'; SELECT * FROM users WHERE username != 'john_doe'; SELECT * FROM users WHERE username <> 'john_doe'; -- 列表条件 SELECT * FROM users WHERE age IN (25, 30, 35); SELECT * FROM users WHERE username IN ('alice', 'bob', 'charlie'); SELECT * FROM users WHERE age NOT IN (20, 21, 22); -- 空值条件 SELECT * FROM users WHERE email IS NULL; SELECT * FROM users WHERE email IS NOT NULL; -- 模糊查询 SELECT * FROM users WHERE username LIKE 'j%'; -- 以j开头 SELECT * FROM users WHERE username LIKE '%n'; -- 以n结尾 SELECT * FROM users WHERE username LIKE '%oh%'; -- 包含oh SELECT * FROM users WHERE username LIKE 'j___'; -- j开头，后跟3个字符 SELECT * FROM users WHERE username NOT LIKE 'admin%'; ``` ### 4.2 逻辑运算符 ```sql -- AND运算符 SELECT * FROM users WHERE age > 25 AND age < 35; -- OR运算符 SELECT * FROM users WHERE age < 25 OR age > 35; -- 组合逻辑运算符 SELECT * FROM users WHERE (age > 25 AND age < 35) OR username LIKE 'admin%'; -- NOT运算符 SELECT * FROM users WHERE NOT (age >= 25 AND age <= 35); ``` ## 5. 排序和分组 ### 5.1 排序 (ORDER BY) ```sql -- 升序排序（默认） SELECT * FROM users ORDER BY age; SELECT * FROM users ORDER BY age ASC; -- 降序排序 SELECT * FROM users ORDER BY age DESC; -- 多字段排序 SELECT * FROM users ORDER BY age DESC, username ASC; -- 按表达式排序 SELECT *, age + 5 AS age_plus_5 FROM users ORDER BY age_plus_5 DESC; ``` ### 5.2 分组 (GROUP BY) ```sql -- 基本分组 SELECT age, COUNT(*) FROM users GROUP BY age; SELECT age, COUNT(*) AS user_count FROM users GROUP BY age; -- 多字段分组 SELECT age, username, COUNT(*) FROM users GROUP BY age, username; -- 分组后过滤 SELECT age, COUNT(*) FROM users GROUP BY age HAVING COUNT(*) > 1; SELECT age, AVG(age) FROM users GROUP BY age HAVING AVG(age) > 25; ``` ### 5.3 聚合函数 ```sql -- COUNT函数 SELECT COUNT(*) FROM users; -- 总行数 SELECT COUNT(age) FROM users; -- 非NULL值的数量 SELECT COUNT(DISTINCT age) FROM users; -- 去重后的数量 -- SUM函数 SELECT SUM(age) FROM users; -- 年龄总和 SELECT AVG(age) FROM users; -- 平均年龄 -- MAX和MIN函数 SELECT MAX(age) FROM users; -- 最大年龄 SELECT MIN(age) FROM users; -- 最小年龄 -- 组合使用 SELECT COUNT(*) AS total_users, AVG(age) AS avg_age, MAX(age) AS max_age, MIN(age) AS min_age FROM users; ``` ## 6. 连接查询 ### 6.1 内连接 (INNER JOIN) ```sql -- 创建示例表 CREATE TABLE departments ( id INT PRIMARY KEY AUTO_INCREMENT, dept_name VARCHAR(100) NOT NULL, manager_id INT ); CREATE TABLE employees ( id INT PRIMARY KEY AUTO_INCREMENT, emp_name VARCHAR(100) NOT NULL, dept_id INT, salary DECIMAL(10, 2), FOREIGN KEY (dept_id) REFERENCES departments(id) ); -- 内连接（只返回匹配的行） SELECT e.emp_name, d.dept_name FROM employees e INNER JOIN departments d ON e.dept_id = d.id; -- 使用WHERE实现内连接（旧语法） SELECT e.emp_name, d.dept_name FROM employees e, departments d WHERE e.dept_id = d.id; -- 多表内连接 SELECT e.emp_name, d.dept_name, m.emp_name AS manager_name FROM employees e INNER JOIN departments d ON e.dept_id = d.id INNER JOIN employees m ON d.manager_id = m.id; ``` ### 6.2 左连接 (LEFT JOIN) ```sql -- 左连接（返回左表所有行，右表匹配不到的为NULL） SELECT e.emp_name, d.dept_name FROM employees e LEFT JOIN departments d ON e.dept_id = d.id; -- 查找没有部门的员工 SELECT e.emp_name FROM employees e LEFT JOIN departments d ON e.dept_id = d.id WHERE d.id IS NULL; -- 右连接（RIGHT JOIN） SELECT e.emp_name, d.dept_name FROM employees e RIGHT JOIN departments d ON e.dept_id = d.id; ``` ### 6.3 全连接和自连接 ```sql -- MySQL不支持FULL OUTER JOIN，可以用UNION模拟 SELECT e.emp_name, d.dept_name FROM employees e LEFT JOIN departments d ON e.dept_id = d.id UNION SELECT e.emp_name, d.dept_name FROM employees e RIGHT JOIN departments d ON e.dept_id = d.id; -- 自连接（表与自身连接） SELECT e1.emp_name AS employee, e2.emp_name AS manager FROM employees e1 LEFT JOIN employees e2 ON e1.manager_id = e2.id; ``` ## 7. 子查询 ### 7.1 标量子查询 ```sql -- 返回单个值的子查询 SELECT * FROM users WHERE age > (SELECT AVG(age) FROM users); -- 在WHERE子句中使用 SELECT * FROM users WHERE age = (SELECT MAX(age) FROM users); -- 在SELECT子句中使用 SELECT username, age, (SELECT AVG(age) FROM users) AS avg_age FROM users; ``` ### 7.2 列表子查询 ```sql -- 返回多行单列的子查询 SELECT * FROM users WHERE age IN (SELECT age FROM employees WHERE salary > 50000); -- 使用ANY、SOME、ALL SELECT * FROM users WHERE age > ANY (SELECT age FROM employees WHERE dept_id = 1); SELECT * FROM users WHERE age > ALL (SELECT age FROM employees WHERE dept_id = 1); SELECT * FROM users WHERE age = SOME (SELECT age FROM employees WHERE salary > 50000); ``` ### 7.3 表子查询 ```sql -- 返回多行多列的子查询 SELECT * FROM (SELECT username, age FROM users WHERE age > 25) AS older_users; -- 在FROM子句中使用 SELECT u.username, u.age, e.salary FROM (SELECT * FROM users WHERE age > 25) AS u INNER JOIN employees e ON u.username = e.emp_name; ``` ### 7.4 相关子查询 ```sql -- 子查询引用外部查询的列 SELECT u1.username, u1.age FROM users u1 WHERE u1.age > (SELECT AVG(u2.age) FROM users u2 WHERE u2.age < u1.age); -- EXISTS子查询 SELECT d.dept_name FROM departments d WHERE EXISTS (SELECT 1 FROM employees e WHERE e.dept_id = d.id); -- NOT EXISTS子查询 SELECT d.dept_name FROM departments d WHERE NOT EXISTS (SELECT 1 FROM employees e WHERE e.dept_id = d.id); ``` ## 8. 表结构修改 ### 8.1 添加字段 ```sql -- 添加单个字段 ALTER TABLE users ADD COLUMN phone VARCHAR(20); ALTER TABLE users ADD COLUMN address TEXT AFTER email; -- 添加多个字段 ALTER TABLE users ADD COLUMN phone VARCHAR(20), ADD COLUMN address TEXT, ADD COLUMN birthday DATE; -- 添加带默认值的字段 ALTER TABLE users ADD COLUMN status VARCHAR(10) DEFAULT 'active'; ALTER TABLE users ADD COLUMN created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP; ``` ### 8.2 修改字段 ```sql -- 修改字段类型 ALTER TABLE users MODIFY COLUMN phone VARCHAR(30); ALTER TABLE users MODIFY COLUMN age INT UNSIGNED; -- 修改字段名称和类型 ALTER TABLE users CHANGE COLUMN phone telephone VARCHAR(30); ALTER TABLE users CHANGE COLUMN birth birthday DATE; -- 修改字段位置 ALTER TABLE users MODIFY COLUMN email VARCHAR(100) AFTER username; ALTER TABLE users MODIFY COLUMN id INT FIRST; ``` ### 8.3 删除字段 ```sql -- 删除单个字段 ALTER TABLE users DROP COLUMN phone; -- 删除多个字段 ALTER TABLE users DROP COLUMN phone, DROP COLUMN address; ``` ### 8.4 添加和删除索引 ```sql -- 添加主键 ALTER TABLE users ADD PRIMARY KEY (id); -- 添加唯一索引 ALTER TABLE users ADD UNIQUE INDEX uk_username (username); ALTER TABLE users ADD UNIQUE INDEX uk_email (email); -- 添加普通索引 ALTER TABLE users ADD INDEX idx_age (age); ALTER TABLE users ADD INDEX idx_name_age (username, age); -- 添加全文索引 ALTER TABLE articles ADD FULLTEXT INDEX ft_content (content); -- 删除索引 ALTER TABLE users DROP INDEX uk_username; ALTER TABLE users DROP PRIMARY KEY; ``` ### 8.5 添加和删除约束 ```sql -- 添加外键约束 ALTER TABLE employees ADD CONSTRAINT fk_dept FOREIGN KEY (dept_id) REFERENCES departments(id); -- 添加检查约束（MySQL 8.0.16+） ALTER TABLE users ADD CONSTRAINT chk_age CHECK (age >= 0 AND age <= 150); -- 删除外键约束 ALTER TABLE employees DROP FOREIGN KEY fk_dept; ``` ## 9. 视图 ### 9.1 创建和使用视图 ```sql -- 创建简单视图 CREATE VIEW user_overview AS SELECT id, username, email, age FROM users; -- 创建复杂视图 CREATE VIEW employee_details AS SELECT e.id, e.emp_name, e.salary, d.dept_name, m.emp_name AS manager_name FROM employees e LEFT JOIN departments d ON e.dept_id = d.id LEFT JOIN employees m ON d.manager_id = m.id; -- 使用视图 SELECT * FROM user_overview WHERE age > 25; SELECT * FROM employee_details WHERE dept_name = 'IT'; ``` ### 9.2 管理视图 ```sql -- 查看视图定义 SHOW CREATE VIEW employee_details; -- 修改视图 CREATE OR REPLACE VIEW employee_details AS SELECT e.id, e.emp_name, e.salary, d.dept_name FROM employees e LEFT JOIN departments d ON e.dept_id = d.id; -- 删除视图 DROP VIEW employee_details; DROP VIEW IF EXISTS employee_details; ``` ## 10. 事务处理 ### 10.1 事务基本操作 ```sql -- 开始事务 START TRANSACTION; BEGIN; -- 执行SQL语句 INSERT INTO users (username, email, age) VALUES ('test_user', 'test@example.com', 25); UPDATE users SET age = age + 1 WHERE username = 'john_doe'; -- 提交事务 COMMIT; -- 回滚事务 ROLLBACK; ``` ### 10.2 事务隔离级别 ```sql -- 查看当前隔离级别 SELECT @@transaction_isolation; -- 设置隔离级别 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SET TRANSACTION ISOLATION LEVEL READ COMMITTED; SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; -- 设置全局隔离级别 SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED; ``` ## 11. 存储过程和函数 ### 11.1 存储过程 ```sql -- 创建简单存储过程 DELIMITER // CREATE PROCEDURE GetUserCount() BEGIN SELECT COUNT(*) FROM users; END // DELIMITER ; -- 调用存储过程 CALL GetUserCount(); -- 带参数的存储过程 DELIMITER // CREATE PROCEDURE GetUsersByAge(IN min_age INT, IN max_age INT) BEGIN SELECT * FROM users WHERE age BETWEEN min_age AND max_age; END // DELIMITER ; -- 调用带参数的存储过程 CALL GetUsersByAge(20, 30); -- 带输出参数的存储过程 DELIMITER // CREATE PROCEDURE GetUserStats( IN min_age INT, OUT user_count INT, OUT avg_age DECIMAL(10,2) ) BEGIN SELECT COUNT(*) INTO user_count FROM users WHERE age >= min_age; SELECT AVG(age) INTO avg_age FROM users WHERE age >= min_age; END // DELIMITER ; -- 调用带输出参数的存储过程 CALL GetUserStats(25, @count, @avg_age); SELECT @count, @avg_age; ``` ### 11.2 函数 ```sql -- 创建函数 DELIMITER // CREATE FUNCTION CalculateAge(birth_date DATE) RETURNS INT DETERMINISTIC BEGIN DECLARE age INT; SET age = TIMESTAMPDIFF(YEAR, birth_date, CURDATE()); RETURN age; END // DELIMITER ; -- 使用函数 SELECT username, CalculateAge(birthday) AS age FROM users; -- 删除存储过程和函数 DROP PROCEDURE IF EXISTS GetUserCount; DROP FUNCTION IF EXISTS CalculateAge; ``` ## 12. 触发器 ### 12.1 创建触发器 ```sql -- 创建BEFORE INSERT触发器 DELIMITER // CREATE TRIGGER before_user_insert BEFORE INSERT ON users FOR EACH ROW BEGIN IF NEW.age < 0 THEN SET NEW.age = 0; END IF; IF NEW.created_at IS NULL THEN SET NEW.created_at = CURRENT_TIMESTAMP; END IF; END // DELIMITER ; -- 创建AFTER UPDATE触发器 DELIMITER // CREATE TRIGGER after_user_update AFTER UPDATE ON users FOR EACH ROW BEGIN IF OLD.age != NEW.age THEN INSERT INTO user_logs (user_id, action, old_value, new_value, created_at) VALUES (NEW.id, 'age_updated', OLD.age, NEW.age, CURRENT_TIMESTAMP); END IF; END // DELIMITER ; ``` ### 12.2 管理触发器 ```sql -- 查看触发器 SHOW TRIGGERS; SHOW CREATE TRIGGER before_user_insert; -- 删除触发器 DROP TRIGGER IF EXISTS before_user_insert; ``` ## 13. 索引优化 ### 13.1 索引类型 ```sql -- 普通索引 CREATE INDEX idx_username ON users(username); -- 唯一索引 CREATE UNIQUE INDEX uk_email ON users(email); -- 复合索引 CREATE INDEX idx_name_age ON users(username, age); -- 前缀索引 CREATE INDEX idx_email_prefix ON users(email(20)); -- 全文索引 CREATE FULLTEXT INDEX ft_content ON articles(content); ``` ### 13.2 索引分析 ```sql -- 查看索引使用情况 SHOW INDEX FROM users; -- 分析查询执行计划 EXPLAIN SELECT * FROM users WHERE username = 'john_doe'; EXPLAIN SELECT * FROM users WHERE age > 25; -- 分析表 ANALYZE TABLE users; ``` ## 14. 性能优化 ### 14.1 查询优化 ```sql -- 使用LIMIT限制结果 SELECT * FROM users ORDER BY id LIMIT 100; -- 避免SELECT * SELECT id, username, email FROM users; -- 使用合适的数据类型 -- 使用索引列进行条件过滤 SELECT * FROM users WHERE age > 25 AND username LIKE 'j%'; -- 避免在WHERE子句中使用函数 SELECT * FROM users WHERE DATE(created_at) = '2023-01-01'; -- 不推荐 SELECT * FROM users WHERE created_at >= '2023-01-01' AND created_at < '2023-01-02'; -- 推荐 ``` ### 14.2 配置优化 ```sql -- 查看配置变量 SHOW VARIABLES LIKE 'query_cache%'; SHOW VARIABLES LIKE 'innodb_buffer_pool%'; -- 查看状态 SHOW STATUS LIKE 'Connections'; SHOW STATUS LIKE 'Slow_queries'; ``` ## 15. 备份和恢复 ### 15.1 数据备份 ```bash # 命令行备份（在系统终端执行） mysqldump -u username -p database_name > backup.sql mysqldump -u username -p database_name table_name > table_backup.sql mysqldump -u username -p --all-databases > all_backup.sql # 压缩备份 mysqldump -u username -p database_name | gzip > backup.sql.gz ``` ### 15.2 数据恢复 ```bash # 命令行恢复（在系统终端执行） mysql -u username -p database_name < backup.sql mysql -u username -p database_name < table_backup.sql # 恢复压缩备份 gunzip < backup.sql.gz | mysql -u username -p database_name ``` ### 15.3 SQL方式备份和恢复 ```sql -- 创建备份表 CREATE TABLE users_backup AS SELECT * FROM users; -- 恢复数据 INSERT INTO users SELECT * FROM users_backup WHERE id > 100; ``` ## 16. 用户权限管理 ### 16.1 用户管理 ```sql -- 创建用户 CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password'; CREATE USER 'webuser'@'%' IDENTIFIED BY 'password'; -- 修改用户密码 ALTER USER 'username'@'localhost' IDENTIFIED BY 'new_password'; SET PASSWORD FOR 'username'@'localhost' = PASSWORD('new_password'); -- 删除用户 DROP USER 'username'@'localhost'; ``` ### 16.2 权限管理 ```sql -- 授予权限 GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'localhost'; GRANT SELECT, INSERT, UPDATE ON database_name.table_name TO 'username'@'localhost'; GRANT SELECT ON *.* TO 'readonly'@'%'; -- 刷新权限 FLUSH PRIVILEGES; -- 查看权限 SHOW GRANTS FOR 'username'@'localhost'; -- 撤销权限 REVOKE ALL PRIVILEGES ON database_name.* FROM 'username'@'localhost'; REVOKE INSERT ON database_name.table_name FROM 'username'@'localhost'; ``` ## 17. 常用函数 ### 17.1 字符串函数 ```sql -- 字符串连接 SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM employees; -- 字符串长度 SELECT LENGTH(username) FROM users; -- 字符串截取 SELECT SUBSTRING(username, 1, 3) FROM users; SELECT LEFT(username, 3) FROM users; SELECT RIGHT(username, 3) FROM users; -- 字符串替换 SELECT REPLACE(email, '@example.com', '@newdomain.com') FROM users; -- 大小写转换 SELECT UPPER(username), LOWER(email) FROM users; -- 去除空格 SELECT TRIM(' hello '); -- 'hello' SELECT LTRIM(' hello'); -- 'hello' SELECT RTRIM('hello '); -- 'hello' ``` ### 17.2 数值函数 ```sql -- 四舍五入 SELECT ROUND(3.14159, 2); -- 3.14 -- 向上取整和向下取整 SELECT CEIL(3.14); -- 4 SELECT FLOOR(3.14); -- 3 -- 绝对值 SELECT ABS(-10); -- 10 -- 取余 SELECT MOD(10, 3); -- 1 -- 随机数 SELECT RAND(); -- 0到1之间的随机数 SELECT RAND() * 100; -- 0到100之间的随机数 ``` ### 17.3 日期时间函数 ```sql -- 当前日期时间 SELECT NOW(); -- 2023-01-01 12:34:56 SELECT CURDATE(); -- 2023-01-01 SELECT CURTIME(); -- 12:34:56 -- 日期格式化 SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s'); SELECT DATE_FORMAT(birthday, '%Y年%m月%d日') FROM users; -- 日期计算 SELECT DATE_ADD(NOW(), INTERVAL 1 DAY); -- 加1天 SELECT DATE_SUB(NOW(), INTERVAL 1 MONTH); -- 减1个月 SELECT DATEDIFF('2023-12-31', '2023-01-01'); -- 日期差（天） -- 提取日期部分 SELECT YEAR(NOW()); SELECT MONTH(NOW()); SELECT DAY(NOW()); SELECT HOUR(NOW()); SELECT MINUTE(NOW()); SELECT SECOND(NOW()); ``` ## 18. 实用技巧 ### 18.1 数据导入导出 ```sql -- 导入CSV文件 LOAD DATA INFILE '/path/to/file.csv' INTO TABLE users FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 ROWS; -- 导出数据到CSV SELECT * FROM users INTO OUTFILE '/path/to/export.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n'; ``` ### 18.2 条件插入和更新 ```sql -- INSERT ... ON DUPLICATE KEY UPDATE INSERT INTO users (username, email, age) VALUES ('john_doe', 'john@example.com', 25) ON DUPLICATE KEY UPDATE age = VALUES(age), updated_at = CURRENT_TIMESTAMP; -- REPLACE INTO（先删除后插入） REPLACE INTO users (id, username, email, age) VALUES (1, 'john_doe', 'john_new@example.com', 26); ``` ### 18.3 窗口函数（MySQL 8.0+） ```sql -- ROW_NUMBER() SELECT username, age, ROW_NUMBER() OVER (ORDER BY age DESC) AS age_rank FROM users; -- RANK()和DENSE_RANK() SELECT username, age, RANK() OVER (ORDER BY age DESC) AS rank, DENSE_RANK() OVER (ORDER BY age DESC) AS dense_rank FROM users; -- LAG()和LEAD() SELECT username, age, LAG(age, 1) OVER (ORDER BY age) AS prev_age, LEAD(age, 1) OVER (ORDER BY age) AS next_age FROM users; ``` ## 19. 常见问题和解决方案 ### 19.1 字符编码问题 ```sql -- 查看字符编码 SHOW VARIABLES LIKE 'character_set%'; -- 设置字符编码 SET NAMES 'utf8mb4'; ALTER DATABASE database_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; ``` ### 19.2 连接问题 ```sql -- 查看最大连接数 SHOW VARIABLES LIKE 'max_connections'; -- 查看当前连接数 SHOW STATUS LIKE 'Threads_connected'; -- 查看正在执行的查询 SHOW PROCESSLIST; SHOW FULL PROCESSLIST; -- 终止查询 KILL connection_id; ``` ### 19.3 性能问题 ```sql -- 查看慢查询日志 SHOW VARIABLES LIKE 'slow_query_log%'; -- 启用慢查询日志 SET GLOBAL slow_query_log = 'ON'; SET GLOBAL long_query_time = 2; -- 分析表 ANALYZE TABLE table_name; OPTIMIZE TABLE table_name; CHECK TABLE table_name; ``` ## 总结 MySQL是一个功能强大的关系型数据库管理系统，掌握以下关键点对于高效使用MySQL至关重要： ### 核心技能 - **基础CRUD操作**：数据的增删改查是数据库操作的基础 - **条件查询**：WHERE子句和各种条件运算符的使用 - **连接查询**：内连接、外连接等多表关联查询 - **索引优化**：合理创建和使用索引提高查询性能 - **事务处理**：保证数据的一致性和完整性 ### 进阶技能 - **存储过程和函数**：封装业务逻辑，提高代码复用性 - **触发器**：自动化数据处理和业务规则 enforcement - **视图**：简化复杂查询，提供数据安全性 - **性能优化**：查询优化、索引设计、配置调优 ### 最佳实践 - 合理设计数据库结构 - 选择合适的数据类型 - 创建必要的索引 - 使用事务保证数据一致性 - 定期备份数据 - 监控和优化性能通过系统学习和实践这些知识点，您将能够熟练使用MySQL进行各种数据库操作，从简单的数据查询到复杂的业务逻辑处理都能得心应手。

文章列表

MySQL从基础到高级