MySQL从基础到高级

概述

MySQL是最流行的开源关系型数据库管理系统之一。本教程涵盖MySQL的基础操作、查询技巧、表结构管理等常见使用场景，适合初学者和进阶用户学习。

1. MySQL基础

1.1 连接和基本操作

-- 连接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 数据库和表的基本操作

-- 创建数据库
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 数值类型

-- 整数类型
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 字符串类型

-- 固定长度字符串
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 日期时间类型

-- 日期时间类型
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)

-- 插入单条记录
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)

-- 查询所有数据
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)

-- 更新单条记录
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)

-- 删除单条记录
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子句

-- 基本条件
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 逻辑运算符

-- 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)

-- 升序排序（默认）
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)

-- 基本分组
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 聚合函数

-- 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)

-- 创建示例表
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)

-- 左连接（返回左表所有行，右表匹配不到的为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 全连接和自连接

-- 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 标量子查询

-- 返回单个值的子查询
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 列表子查询

-- 返回多行单列的子查询
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 表子查询

-- 返回多行多列的子查询
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 相关子查询

-- 子查询引用外部查询的列
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 添加字段

-- 添加单个字段
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 修改字段

-- 修改字段类型
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 删除字段

-- 删除单个字段
ALTER TABLE users DROP COLUMN phone;

-- 删除多个字段
ALTER TABLE users 
DROP COLUMN phone,
DROP COLUMN address;

8.4 添加和删除索引

-- 添加主键
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 添加和删除约束

-- 添加外键约束
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 创建和使用视图

-- 创建简单视图
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 管理视图

-- 查看视图定义
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 事务基本操作

-- 开始事务
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 事务隔离级别

-- 查看当前隔离级别
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 存储过程

-- 创建简单存储过程
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 函数

-- 创建函数
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 创建触发器

-- 创建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 管理触发器

-- 查看触发器
SHOW TRIGGERS;
SHOW CREATE TRIGGER before_user_insert;

-- 删除触发器
DROP TRIGGER IF EXISTS before_user_insert;

13. 索引优化

13.1 索引类型

-- 普通索引
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 索引分析

-- 查看索引使用情况
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 查询优化

-- 使用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 配置优化

-- 查看配置变量
SHOW VARIABLES LIKE 'query_cache%';
SHOW VARIABLES LIKE 'innodb_buffer_pool%';

-- 查看状态
SHOW STATUS LIKE 'Connections';
SHOW STATUS LIKE 'Slow_queries';

15. 备份和恢复

15.1 数据备份

# 命令行备份（在系统终端执行）
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 数据恢复

# 命令行恢复（在系统终端执行）
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方式备份和恢复

-- 创建备份表
CREATE TABLE users_backup AS SELECT * FROM users;

-- 恢复数据
INSERT INTO users SELECT * FROM users_backup WHERE id > 100;

16. 用户权限管理

16.1 用户管理

-- 创建用户
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 权限管理

-- 授予权限
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 字符串函数

-- 字符串连接
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 数值函数

-- 四舍五入
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 日期时间函数

-- 当前日期时间
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 数据导入导出

-- 导入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 条件插入和更新

-- 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+）

-- 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 字符编码问题

-- 查看字符编码
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 连接问题

-- 查看最大连接数
SHOW VARIABLES LIKE 'max_connections';

-- 查看当前连接数
SHOW STATUS LIKE 'Threads_connected';

-- 查看正在执行的查询
SHOW PROCESSLIST;
SHOW FULL PROCESSLIST;

-- 终止查询
KILL connection_id;

19.3 性能问题

-- 查看慢查询日志
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进行各种数据库操作，从简单的数据查询到复杂的业务逻辑处理都能得心应手。