MySQL 入门到精通全攻略

MySQL 入门详细指南

一、MySQL 简介

1.1 什么是 MySQL？

• 关系型数据库管理系统（RDBMS）
• 开源免费（社区版）
• 使用 SQL（Structured Query Language） 进行数据管理
• 支持多种操作系统（Windows、Linux、macOS）

1.2 主要特性

• 数据以表格形式存储
• 支持事务处理（ACID 特性）
• 提供数据完整性约束
• 支持多种存储引擎（InnoDB、MyISAM 等）
• 良好的安全性和权限管理

二、安装 MySQL

2.1 Windows 安装

1. 下载 MySQL Installer
2. 选择安装类型：
   • Developer Default：开发者默认
   • Server only：仅安装服务器
   • Client only：仅安装客户端
3. 配置 root 用户密码
4. 设置 Windows 服务

2.2 Linux 安装（Ubuntu为例）

1. # 更新包列表
2. sudo apt update
4. # 安装 MySQL 服务器
5. sudo apt install mysql-server
7. # 启动 MySQL 服务
8. sudo systemctl start mysql
10. # 设置开机启动
11. sudo systemctl enable mysql
13. # 运行安全脚本
14. sudo mysql_secure_installation

复制代码

2.3 macOS 安装

1. # 使用 Homebrew 安装
2. brew install mysql
4. # 启动 MySQL 服务
5. brew services start mysql
7. # 设置 root 密码
8. mysql_secure_installation

复制代码

三、MySQL 基本操作

3.1 连接 MySQL

1. -- 命令行连接
2. mysql -u root -p
4. -- 指定主机和端口连接
5. mysql -h localhost -P 3306 -u root -p

复制代码

3.2 数据库操作

1. -- 查看所有数据库
2. SHOW DATABASES;
4. -- 创建数据库
5. CREATE DATABASE mydatabase;
6. CREATE DATABASE IF NOT EXISTS mydatabase;
8. -- 选择数据库
9. USE mydatabase;
11. -- 删除数据库
12. DROP DATABASE mydatabase;
13. DROP DATABASE IF EXISTS mydatabase;
15. -- 查看当前使用的数据库
16. SELECT DATABASE();

复制代码

3.3 数据表操作

创建表

1. CREATE TABLE users (
2. id INT PRIMARY KEY AUTO_INCREMENT, -- 主键，自动增长
3. username VARCHAR(50) NOT NULL UNIQUE, -- 用户名，非空且唯一
4. password VARCHAR(100) NOT NULL, -- 密码
5. email VARCHAR(100) NOT NULL UNIQUE, -- 邮箱，唯一
6. age INT CHECK (age >= 0 AND age <= 150), -- 年龄，范围检查
7. created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- 创建时间
8. updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP -- 更新时间
9. );
11. -- 创建带索引的表
12. CREATE TABLE products (
13. id INT PRIMARY KEY AUTO_INCREMENT,
14. name VARCHAR(100) NOT NULL,
15. category_id INT,
16. price DECIMAL(10, 2),
17. stock INT DEFAULT 0,
18. INDEX idx_category (category_id), -- 普通索引
19. INDEX idx_name (name(20)), -- 前缀索引
20. FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL -- 外键
21. );

复制代码

修改表结构

1. -- 添加列
2. ALTER TABLE users ADD COLUMN phone VARCHAR(15);
4. -- 修改列
5. ALTER TABLE users MODIFY COLUMN phone VARCHAR(20);
7. -- 重命名列
8. ALTER TABLE users CHANGE COLUMN phone telephone VARCHAR(20);
10. -- 删除列
11. ALTER TABLE users DROP COLUMN telephone;
13. -- 添加主键
14. ALTER TABLE users ADD PRIMARY KEY (id);
16. -- 添加外键
17. ALTER TABLE orders ADD FOREIGN KEY (user_id) REFERENCES users(id);
19. -- 添加索引
20. ALTER TABLE users ADD INDEX idx_email (email);
21. CREATE INDEX idx_username ON users(username);
23. -- 删除索引
24. ALTER TABLE users DROP INDEX idx_email;
25. DROP INDEX idx_username ON users;

复制代码

查看表信息

1. -- 查看所有表
2. SHOW TABLES;
4. -- 查看表结构
5. DESCRIBE users;
6. DESC users;
7. SHOW COLUMNS FROM users;
9. -- 查看建表语句
10. SHOW CREATE TABLE users;
12. -- 查看表状态
13. SHOW TABLE STATUS LIKE 'users';

复制代码

删除表

1. -- 删除表
2. DROP TABLE users;
4. -- 安全删除（如果存在）
5. DROP TABLE IF EXISTS users;
7. -- 清空表数据（保留结构）
8. TRUNCATE TABLE users;

复制代码

四、数据类型

4.1 数值类型

1. -- 整数类型
2. TINYINT -- 1字节，-128~127
3. SMALLINT -- 2字节
4. MEDIUMINT -- 3字节
5. INT/INTEGER -- 4字节
6. BIGINT -- 8字节
8. -- 浮点数类型
9. FLOAT(m,d) -- 单精度浮点数
10. DOUBLE(m,d) -- 双精度浮点数
11. DECIMAL(m,d) -- 精确小数，m总位数，d小数位数

复制代码

4.2 字符串类型

1. CHAR(n) -- 定长字符串，0-255字符
2. VARCHAR(n) -- 变长字符串，0-65535字符
3. TEXT -- 长文本数据
4. LONGTEXT -- 超长文本数据
5. BLOB -- 二进制数据
6. LONGBLOB -- 超长二进制数据
7. ENUM -- 枚举类型
8. SET -- 集合类型

复制代码

4.3 日期时间类型

1. DATE -- 日期，YYYY-MM-DD
2. TIME -- 时间，HH:MM:SS
3. DATETIME -- 日期时间，YYYY-MM-DD HH:MM:SS
4. TIMESTAMP -- 时间戳，自动更新
5. YEAR -- 年份

复制代码

五、数据操作（CRUD）

5.1 插入数据（INSERT）

1. -- 插入单行数据
2. INSERT INTO users (username, password, email, age)
3. VALUES ('john_doe', 'password123', 'john@example.com', 25);
5. -- 插入多行数据
6. INSERT INTO users (username, password, email, age)
7. VALUES
8. ('jane_doe', 'pass123', 'jane@example.com', 28),
9. ('bob_smith', 'bobpass', 'bob@example.com', 32);
11. -- 插入查询结果
12. INSERT INTO user_backup (username, email)
13. SELECT username, email FROM users WHERE age > 20;
15. -- 使用 SET 语法
16. INSERT INTO users
17. SET username = 'alice', password = 'alicepass', email = 'alice@example.com';

复制代码

5.2 查询数据（SELECT）

1. -- 查询所有列
2. SELECT * FROM users;
4. -- 查询指定列
5. SELECT id, username, email FROM users;
7. -- 去重查询
8. SELECT DISTINCT age FROM users;
10. -- 条件查询
11. SELECT * FROM users WHERE age > 25;
12. SELECT * FROM users WHERE age BETWEEN 20 AND 30;
13. SELECT * FROM users WHERE email LIKE '%@gmail.com';
14. SELECT * FROM users WHERE username IN ('john', 'jane', 'bob');
15. SELECT * FROM users WHERE age > 25 AND email LIKE '%@example.com';
17. -- 空值检查
18. SELECT * FROM users WHERE phone IS NULL;
19. SELECT * FROM users WHERE phone IS NOT NULL;
21. -- 排序
22. SELECT * FROM users ORDER BY age DESC;
23. SELECT * FROM users ORDER BY created_at ASC, username DESC;
25. -- 限制结果
26. SELECT * FROM users LIMIT 10;
27. SELECT * FROM users LIMIT 5 OFFSET 10; -- 跳过10条，取5条
28. SELECT * FROM users LIMIT 10, 5; -- 同上
30. -- 分组统计
31. SELECT age, COUNT(*) as count FROM users GROUP BY age;
32. SELECT age, COUNT(*) FROM users GROUP BY age HAVING COUNT(*) > 1;
34. -- 别名
35. SELECT username AS 用户名, email AS 邮箱 FROM users;

复制代码

5.3 更新数据（UPDATE）

1. -- 更新单行
2. UPDATE users SET age = 26 WHERE id = 1;
4. -- 更新多行
5. UPDATE users SET status = 'active' WHERE age >= 18;
7. -- 更新多列
8. UPDATE users SET age = age + 1, updated_at = NOW() WHERE id = 1;
10. -- 使用子查询更新
11. UPDATE orders
12. SET total_price = (SELECT SUM(price * quantity) FROM order_items WHERE order_id = orders.id)
13. WHERE id = 100;

复制代码

5.4 删除数据（DELETE）

1. -- 删除特定行
2. DELETE FROM users WHERE id = 1;
4. -- 删除所有行
5. DELETE FROM users;
7. -- 使用子查询删除
8. DELETE FROM users
9. WHERE id IN (SELECT user_id FROM inactive_users WHERE last_login < '2023-01-01');

复制代码

六、高级查询

6.1 连接查询（JOIN）

1. -- 内连接
2. SELECT u.username, o.order_date, o.total_amount
3. FROM users u
4. INNER JOIN orders o ON u.id = o.user_id;
6. -- 左连接
7. SELECT u.username, o.order_date
8. FROM users u
9. LEFT JOIN orders o ON u.id = o.user_id;
11. -- 右连接
12. SELECT u.username, o.order_date
13. FROM users u
14. RIGHT JOIN orders o ON u.id = o.user_id;
16. -- 全外连接（MySQL 不支持，使用 UNION 模拟）
17. SELECT u.username, o.order_date
18. FROM users u LEFT JOIN orders o ON u.id = o.user_id
19. UNION
20. SELECT u.username, o.order_date
21. FROM users u RIGHT JOIN orders o ON u.id = o.user_id;
23. -- 自连接
24. SELECT e1.name AS employee, e2.name AS manager
25. FROM employees e1
26. LEFT JOIN employees e2 ON e1.manager_id = e2.id;
28. -- 多表连接
29. SELECT u.username, o.order_date, p.product_name
30. FROM users u
31. JOIN orders o ON u.id = o.user_id
32. JOIN order_items oi ON o.id = oi.order_id
33. JOIN products p ON oi.product_id = p.id;

复制代码

6.2 子查询

1. -- 标量子查询（返回单个值）
2. SELECT username FROM users
3. WHERE age = (SELECT MAX(age) FROM users);
5. -- 列子查询（返回一列）
6. SELECT * FROM users
7. WHERE id IN (SELECT user_id FROM orders WHERE total_amount > 1000);
9. -- 行子查询（返回一行）
10. SELECT * FROM users
11. WHERE (age, salary) = (SELECT MAX(age), AVG(salary) FROM users);
13. -- 表子查询
14. SELECT u.username, o.order_count
15. FROM users u
16. JOIN (SELECT user_id, COUNT(*) as order_count FROM orders GROUP BY user_id) o
17. ON u.id = o.user_id;
19. -- EXISTS 子查询
20. SELECT * FROM users u
21. WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

复制代码

6.3 聚合函数

1. -- 常用聚合函数
2. SELECT
3. COUNT(*) as total_users, -- 计数
4. COUNT(DISTINCT age) as unique_ages, -- 去重计数
5. AVG(age) as average_age, -- 平均值
6. SUM(age) as total_age, -- 求和
7. MAX(age) as max_age, -- 最大值
8. MIN(age) as min_age, -- 最小值
9. GROUP_CONCAT(username) as user_list -- 连接字符串
10. FROM users;

复制代码

6.4 窗口函数（MySQL 8.0+）

1. -- 排名函数
2. SELECT
3. username,
4. age,
5. ROW_NUMBER() OVER (ORDER BY age DESC) as row_num,
6. RANK() OVER (ORDER BY age DESC) as rank_num,
7. DENSE_RANK() OVER (ORDER BY age DESC) as dense_rank_num,
8. NTILE(4) OVER (ORDER BY age DESC) as quartile
9. FROM users;
11. -- 聚合窗口函数
12. SELECT
13. username,
14. age,
15. AVG(age) OVER () as avg_all,
16. AVG(age) OVER (PARTITION BY department) as avg_dept,
17. SUM(age) OVER (ORDER BY created_at) as running_total
18. FROM users;
20. -- 前后行访问
21. SELECT
22. username,
23. age,
24. LAG(age) OVER (ORDER BY created_at) as prev_age,
25. LEAD(age) OVER (ORDER BY created_at) as next_age
26. FROM users;

复制代码

七、索引优化

7.1 创建索引

1. -- 创建普通索引
2. CREATE INDEX idx_email ON users(email);
3. CREATE INDEX idx_name_age ON users(username, age);
5. -- 创建唯一索引
6. CREATE UNIQUE INDEX uniq_email ON users(email);
8. -- 创建全文索引
9. CREATE FULLTEXT INDEX ft_content ON articles(content);
11. -- 创建空间索引
12. CREATE SPATIAL INDEX sp_location ON locations(coordinates);
14. -- 创建前缀索引
15. CREATE INDEX idx_name_prefix ON users(username(10));

复制代码

7.2 查看索引

1. -- 查看表的所有索引
2. SHOW INDEX FROM users;
4. -- 查看索引使用情况
5. EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

复制代码

八、事务管理

8.1 事务基本操作

1. -- 开始事务
2. START TRANSACTION;
3. BEGIN;
5. -- 提交事务
6. COMMIT;
8. -- 回滚事务
9. ROLLBACK;
11. -- 设置保存点
12. SAVEPOINT point1;
14. -- 回滚到保存点
15. ROLLBACK TO SAVEPOINT point1;
17. -- 释放保存点
18. RELEASE SAVEPOINT point1;

复制代码

8.2 事务示例

1. -- 转账示例
2. START TRANSACTION;
4. UPDATE accounts SET balance = balance - 500 WHERE id = 1;
5. UPDATE accounts SET balance = balance + 500 WHERE id = 2;
7. -- 检查余额是否充足
8. SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
10. COMMIT; -- 或 ROLLBACK 如果出现错误

复制代码

8.3 事务隔离级别

1. -- 查看当前隔离级别
2. SELECT @@transaction_isolation;
4. -- 设置隔离级别
5. SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -- 读未提交
6. SET TRANSACTION ISOLATION LEVEL READ COMMITTED; -- 读已提交
7. SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; -- 可重复读（MySQL默认）
8. SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; -- 串行化
10. -- 设置会话级别隔离
11. SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

复制代码

九、存储过程和函数

9.1 存储过程

1. -- 创建存储过程
2. DELIMITER //
3. CREATE PROCEDURE GetUserCount(IN minAge INT, OUT userCount INT)
4. BEGIN
5. SELECT COUNT(*) INTO userCount FROM users WHERE age >= minAge;
6. END //
7. DELIMITER ;
9. -- 调用存储过程
10. CALL GetUserCount(18, @count);
11. SELECT @count;
13. -- 删除存储过程
14. DROP PROCEDURE IF EXISTS GetUserCount;

复制代码

9.2 函数

1. -- 创建函数
2. DELIMITER //
3. CREATE FUNCTION CalculateDiscount(price DECIMAL(10,2), discount_rate DECIMAL(3,2))
4. RETURNS DECIMAL(10,2) DETERMINISTIC
5. BEGIN
6. DECLARE final_price DECIMAL(10,2);
7. SET final_price = price * (1 - discount_rate);
8. RETURN final_price;
9. END //
10. DELIMITER ;
12. -- 使用函数
13. SELECT product_name, price, CalculateDiscount(price, 0.1) as discounted_price
14. FROM products;
16. -- 删除函数
17. DROP FUNCTION IF EXISTS CalculateDiscount;

复制代码

十、触发器和事件

10.1 触发器

1. -- 创建 BEFORE INSERT 触发器
2. DELIMITER //
3. CREATE TRIGGER before_user_insert
4. BEFORE INSERT ON users
5. FOR EACH ROW
6. BEGIN
7. SET NEW.created_at = NOW();
8. SET NEW.updated_at = NOW();
9. END //
10. DELIMITER ;
12. -- 创建 AFTER UPDATE 触发器
13. DELIMITER //
14. CREATE TRIGGER after_user_update
15. AFTER UPDATE ON users
16. FOR EACH ROW
17. BEGIN
18. INSERT INTO user_logs (user_id, action, action_time)
19. VALUES (OLD.id, 'UPDATE', NOW());
20. END //
21. DELIMITER ;
23. -- 删除触发器
24. DROP TRIGGER IF EXISTS before_user_insert;

复制代码

10.2 事件调度器

1. -- 启用事件调度器
2. SET GLOBAL event_scheduler = ON;
4. -- 创建定时事件
5. DELIMITER //
6. CREATE EVENT clean_old_logs
7. ON SCHEDULE EVERY 1 DAY STARTS '2024-01-01 02:00:00'
8. DO
9. BEGIN
10. DELETE FROM user_logs WHERE action_time < DATE_SUB(NOW(), INTERVAL 30 DAY);
11. END //
12. DELIMITER ;
14. -- 查看事件
15. SHOW EVENTS;
17. -- 删除事件
18. DROP EVENT IF EXISTS clean_old_logs;

复制代码

十一、备份与恢复

11.1 备份数据库

1. # 备份单个数据库
2. mysqldump -u root -p mydatabase > backup.sql
4. # 备份所有数据库
5. mysqldump -u root -p --all-databases > all_backup.sql
7. # 备份特定表
8. mysqldump -u root -p mydatabase users products > tables_backup.sql
10. # 压缩备份
11. mysqldump -u root -p mydatabase | gzip > backup.sql.gz

复制代码

11.2 恢复数据库

1. # 恢复数据库
2. mysql -u root -p mydatabase < backup.sql
4. # 恢复压缩的备份
5. gunzip < backup.sql.gz | mysql -u root -p mydatabase
7. # 在 MySQL 命令行中恢复
8. mysql> USE mydatabase;
9. mysql> SOURCE backup.sql;

复制代码

十二、性能优化

12.1 查询优化

1. -- 使用 EXPLAIN 分析查询
2. EXPLAIN SELECT * FROM users WHERE age > 25;
4. -- 避免 SELECT *
5. SELECT id, username, email FROM users; -- 优于 SELECT *
7. -- 使用 LIMIT 限制结果
8. SELECT * FROM users LIMIT 100;
10. -- 合理使用索引
11. SELECT * FROM users USE INDEX (idx_age) WHERE age > 25;
13. -- 避免在 WHERE 子句中对字段进行运算
14. SELECT * FROM users WHERE YEAR(created_at) = 2023; -- 不好
15. SELECT * FROM users WHERE created_at >= '2023-01-01' AND created_at < '2024-01-01'; -- 好

复制代码

12.2 配置优化

1. # my.cnf 配置文件示例
2. [mysqld]
3. # 内存设置
4. innodb_buffer_pool_size = 1G
5. key_buffer_size = 256M
7. # 连接设置
8. max_connections = 1000
9. thread_cache_size = 100
11. # 查询缓存（MySQL 8.0 已移除）
12. query_cache_type = 0
14. # 日志设置
15. slow_query_log = 1
16. slow_query_log_file = /var/log/mysql/slow.log
17. long_query_time = 2

复制代码

十三、安全最佳实践

13.1 用户权限管理

1. -- 创建用户
2. CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'StrongPassword123!';
4. -- 授予权限
5. GRANT SELECT, INSERT, UPDATE, DELETE ON mydatabase.* TO 'app_user'@'localhost';
7. -- 更细粒度的权限控制
8. GRANT SELECT (id, username, email) ON mydatabase.users TO 'app_user'@'localhost';
10. -- 撤销权限
11. REVOKE DELETE ON mydatabase.* FROM 'app_user'@'localhost';
13. -- 查看用户权限
14. SHOW GRANTS FOR 'app_user'@'localhost';
16. -- 删除用户
17. DROP USER 'app_user'@'localhost';

复制代码

13.2 防止 SQL 注入

1. -- 不安全的方式（不要这样用！）
2. SET @sql = CONCAT('SELECT * FROM users WHERE username = "', @input, '"');
3. PREPARE stmt FROM @sql;
4. EXECUTE stmt;
6. -- 安全的方式：使用参数化查询
7. PREPARE stmt FROM 'SELECT * FROM users WHERE username = ?';
8. SET @username = 'john_doe';
9. EXECUTE stmt USING @username;
11. -- 在编程语言中使用预处理语句
12. # Python 示例
13. cursor.execute("SELECT * FROM users WHERE username = %s", (username,))

复制代码

十四、常用命令速查

1. -- 查看版本
2. SELECT VERSION();
4. -- 查看当前用户
5. SELECT USER();
6. SELECT CURRENT_USER();
8. -- 查看系统变量
9. SHOW VARIABLES LIKE '%timeout%';
10. SELECT @@global.max_connections;
12. -- 查看进程
13. SHOW PROCESSLIST;
15. -- 终止查询
16. KILL [CONNECTION | QUERY] process_id;
18. -- 查看状态
19. SHOW STATUS LIKE 'Threads_connected';
21. -- 刷新权限
22. FLUSH PRIVILEGES;
24. -- 清除查询缓存
25. RESET QUERY CACHE;
27. -- 优化表
28. OPTIMIZE TABLE users;

复制代码

十五、学习路径建议

初级阶段（1-2周）

1. 安装和配置 MySQL
2. 学习基本 SQL 语句（SELECT, INSERT, UPDATE, DELETE）
3. 理解数据类型和表结构设计
4. 练习简单的查询操作

中级阶段（2-4周）

1. 掌握高级查询（JOIN, 子查询，聚合函数）
2. 学习索引优化
3. 理解事务管理
4. 学习基本的存储过程和函数

高级阶段（1-2个月）

1. 数据库设计与规范化
2. 性能调优和监控
3. 备份恢复策略
4. 高可用和复制配置
5. 安全最佳实践

十六、学习资源推荐

官方文档

• MySQL 官方文档
• MySQL Tutorial

在线教程

• W3Schools MySQL
• 菜鸟教程 MySQL
• MySQLTutorial.org

书籍推荐

• 《MySQL 必知必会》
• 《高性能 MySQL》
• 《MySQL 技术内幕》

练习平台

• LeetCode 数据库题库
• HackerRank SQL 练习
• SQLZoo

这个指南涵盖了 MySQL 从入门到进阶的主要内容。建议按照顺序学习，并通过实际项目练习来巩固知识。MySQL 的学习需要理论与实践相结合，多动手操作才能真正掌握。