MySQL 数据库从入门到精通教程

3.2 Linux（CentOS7）安装

# 1. 安装依赖
yum install -y wget
# 2. 添加 MySQL 源
wget https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm
rpm -ivh mysql80-community-release-el7-3.noarch.rpm
# 3. 安装 MySQL 服务
yum install -y mysql-community-server
# 4. 启动服务
systemctl start mysqld
# 5. 查看初始密码
grep 'temporary password' /var/log/mysqld.log
# 6. 登录并修改密码（初始密码替换为上面查到的）
mysql -u root -p
ALTER USER 'root'@'localhost' IDENTIFIED BY '新密码（需包含大小写 + 数字 + 特殊字符）';
# 7. 设置开机自启
systemctl enable mysqld

3.3 基础配置（my.cnf）

修改配置文件（Windows：my.ini；Linux：/etc/my.cnf），添加以下常用配置：

[mysqld]
# 数据库默认编码
character-set-server=utf8mb4
# 排序规则
collation-server=utf8mb4_general_ci
# 默认存储引擎
default-storage-engine=InnoDB
# 端口（默认 3306，可修改避免冲突）
port=3306
# 最大连接数
max_connections=1000
# 慢查询阈值（超过 1 秒记录）
long_query_time=1

[mysql]
# 客户端编码
default-character-set=utf8mb4

修改后重启 MySQL 服务生效。

MySQL 核心语法

4.1 数据库操作（DDL）

示例：

-- 创建名为 test_db 的数据库，避免重复创建，编码为 utf8mb4
CREATE DATABASE IF NOT EXISTS test_db DEFAULT CHARSET utf8mb4;
-- 使用该数据库
USE test_db;
-- 查看数据库列表
SHOW DATABASES;
-- 删除数据库（谨慎操作！）
DROP DATABASE IF EXISTS test_db;

4.2 数据表操作（DDL）

4.2.1 创建表

语法：

CREATE TABLE [IF NOT EXISTS] 表名 (
    字段 1 数据类型 [约束],
    字段 2 数据类型 [约束],
    ...
    [PRIMARY KEY(主键字段)]
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

示例：创建用户表 user

CREATE TABLE IF NOT EXISTS `user` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增 ID',
    `name` VARCHAR(50) NOT NULL COMMENT '用户名',
    `age` TINYINT UNSIGNED DEFAULT 0 COMMENT '年龄',
    `gender` ENUM('男','女','未知') DEFAULT '未知' COMMENT '性别',
    `create_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    PRIMARY KEY (`id`) -- 主键
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户信息表';

4.2.2 常用表操作

-- 查看表结构
DESC `user`;
-- 查看创建表的 SQL
SHOW CREATE TABLE `user`;
-- 修改表：添加字段
ALTER TABLE `user` ADD `phone` VARCHAR(11) AFTER `name`;
-- 修改表：修改字段类型
ALTER TABLE `user` MODIFY `phone` VARCHAR(20);
-- 修改表：删除字段
ALTER TABLE `user` DROP `phone`;
-- 重命名表
ALTER TABLE `user` RENAME TO `user_info`;
-- 删除表（谨慎！）
DROP TABLE IF EXISTS `user`;

4.3 数据操作（DML）

4.3.1 插入数据（INSERT）

-- 插入单条数据
INSERT INTO `user`(`name`,`age`,`gender`) VALUES('张三',20,'男');
-- 插入多条数据
INSERT INTO `user`(`name`,`age`,`gender`) VALUES('李四',22,'女'),('王五',25,'男');
-- 插入所有字段（按表结构顺序）
INSERT INTO `user` VALUES(NULL,'赵六',18,'未知',NOW());
-- id 自增，传 NULL 即可

4.3.2 更新数据（UPDATE）

-- 修改单条数据（务必加 WHERE，否则修改全表！）
UPDATE `user` SET `age`=21 WHERE `id`=1;
-- 修改多条数据
UPDATE `user` SET `gender`='未知' WHERE `age`<20;

4.3.3 删除数据（DELETE）

-- 删除指定数据（务必加 WHERE！）
DELETE FROM `user` WHERE `id`=3;
-- 清空表（保留表结构，自增 ID 重置）
TRUNCATE TABLE `user`;

4.3.4 查询数据（DQL，核心）

基础语法：

SELECT [DISTINCT] 字段 1, 字段 2,...
FROM 表名
[WHERE 条件]
[ORDER BY 字段 [ASC/DESC]]
[LIMIT 偏移量，条数];

示例：

-- 查询所有字段
SELECT * FROM `user`;
-- 查询指定字段，去重
SELECT DISTINCT `gender` FROM `user`;
-- 条件查询：年龄大于 20 的男性
SELECT `name`,`age` FROM `user` WHERE `age`>20 AND `gender`='男';
-- 排序：按年龄降序
SELECT * FROM `user` ORDER BY `age` DESC;
-- 分页：第 1 页，每页 2 条（偏移量=0，条数=2）
SELECT * FROM `user` LIMIT 0,2;

高级查询技巧

5.1 条件查询进阶

5.2 分组查询（GROUP BY + HAVING）

用于统计数据（结合聚合函数）：

-- 按性别分组，统计每组人数和平均年龄
SELECT `gender`, COUNT(*) AS `total`, AVG(`age`) AS `avg_age`
FROM `user`
GROUP BY `gender`
HAVING `avg_age`>20;
-- 过滤分组后的结果（WHERE 过滤原始数据）

5.3 连接查询（多表关联）

假设有两张表：user（用户表）、order（订单表，含 user_id 关联 user.id）。

示例：

-- 查询每个用户的姓名和对应的订单数
SELECT u.`name`, COUNT(o.`id`) AS `order_count`
FROM `user` u
LEFT JOIN `order` o ON u.`id` = o.`user_id`
GROUP BY u.`id`;

5.4 子查询

嵌套在其他 SQL 中的查询（分「标量子查询」「行子查询」「表子查询」）：

-- 标量子查询：查询年龄大于平均年龄的用户
SELECT `name`,`age`
FROM `user`
WHERE `age`>(SELECT AVG(`age`) FROM `user`);

-- 表子查询：查询订单数大于 5 的用户
SELECT *
FROM `user`
WHERE `id` IN(
    SELECT `user_id`
    FROM `order`
    GROUP BY `user_id`
    HAVING COUNT(*)>5
);

MySQL 函数

6.1 字符串函数

6.2 数值函数

6.3 日期时间函数

6.4 聚合函数（重点）

数据约束

约束用于保证数据的完整性和一致性，核心约束如下：

外键示例：创建订单表关联用户表

CREATE TABLE `order` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `order_no` VARCHAR(30) NOT NULL UNIQUE COMMENT '订单号',
    `user_id` INT UNSIGNED NOT NULL COMMENT '关联用户 ID',
    `amount` DECIMAL(10,2) NOT NULL COMMENT '订单金额',
    PRIMARY KEY (`id`),
    -- 外键约束：user_id 关联 user 表的 id，删除用户时级联删除订单
    FOREIGN KEY (`user_id`) REFERENCES `user`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

注意：外键会降低性能，高并发场景可业务层保证数据一致性，不使用外键。

事务管理

8.1 事务的 ACID 特性

• A（原子性）：事务中的操作要么全执行，要么全不执行
• C（一致性）：事务执行前后数据状态一致（如转账：A 减 100，B 必加 100）
• I（隔离性）：多个事务并发执行时，互相不干扰
• D（持久性）：事务提交后，数据永久保存到磁盘

8.2 事务操作语法

-- 开启事务（关闭自动提交）
START TRANSACTION;
-- 执行 SQL 操作（如转账：用户 1 减 100，用户 2 加 100）
UPDATE `user` SET `balance`=`balance`-100 WHERE `id`=1;
UPDATE `user` SET `balance`=`balance`+100 WHERE `id`=2;
-- 提交事务（生效）
COMMIT;
-- 若出错，回滚事务（恢复到事务前状态）
-- ROLLBACK;

8.3 事务隔离级别

MySQL 默认隔离级别为 REPEATABLE READ（可重复读），避免脏读、不可重复读，允许幻读。

-- 查看当前隔离级别
SELECT @@transaction_isolation;
-- 设置隔离级别（会话级）
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

索引优化

9.1 索引的作用

索引是数据库的「目录」，用于快速定位数据，避免全表扫描，大幅提升查询效率；但会降低插入/更新/删除效率（需维护索引）。

9.2 索引类型

9.3 索引操作

-- 创建普通索引
CREATE INDEX idx_user_name ON `user`(`name`);
-- 创建复合索引
CREATE INDEX idx_user_name_age ON `user`(`name`,`age`);
-- 查看索引
SHOW INDEX FROM `user`;
-- 删除索引
DROP INDEX idx_user_name ON `user`;

9.4 索引使用原则（避坑！）

1. 适合建索引的字段：查询频繁、区分度高（如手机号）、排序/分组的字段
2. 不适合建索引的字段：频繁修改、区分度低（如性别）、数据量小的表
3. 复合索引遵循最左匹配原则：INDEX(a,b,c) 仅匹配 a、a+b、a+b+c，不匹配 b、b+c、c
4. 避免索引失效：
   • WHERE 子句中使用 OR（除非所有字段都有索引）
   • 对索引字段做函数/运算（如 YEAR(create_time) = 2026）
   • 使用 LIKE '%xxx'（模糊匹配开头通配符）
   • 使用 IS NULL/IS NOT NULL（除非是唯一索引）

存储过程与函数

存储过程是预编译的 SQL 集合，可封装复杂逻辑，减少网络交互。

10.1 存储过程创建与调用

-- 创建存储过程：根据性别查询用户
DELIMITER //
CREATE PROCEDURE get_user_by_gender(IN p_gender ENUM('男','女','未知'), OUT p_total INT)
BEGIN
    -- 查询符合条件的用户
    SELECT * FROM `user` WHERE `gender` = p_gender;
    -- 统计数量并赋值给输出参数
    SELECT COUNT(*) INTO p_total FROM `user` WHERE `gender` = p_gender;
END //
DELIMITER ;
-- 恢复结束符为;
-- 调用存储过程
CALL get_user_by_gender('男',@total);
-- 查看输出参数
SELECT @total;

10.2 存储函数

-- 创建存储函数：计算用户年龄（根据生日）
DELIMITER //
CREATE FUNCTION calc_age(p_birth DATE) RETURNS INT DETERMINISTIC
BEGIN
    RETURN TIMESTAMPDIFF(YEAR, p_birth,NOW());
END //
DELIMITER ;
-- 调用函数
SELECT `name`, calc_age(`birth`) AS `age` FROM `user`;

注意：存储过程/函数维护成本高，简单逻辑建议业务层实现，仅复杂统计场景使用。

用户与权限管理

MySQL 默认有 root 超级用户，生产环境需创建专用用户并分配最小权限。

11.1 用户操作

-- 创建用户：允许 192.168.1.%网段的 user_test 用户访问
CREATE USER 'user_test'@'192.168.1.%' IDENTIFIED BY'Test@123456';
-- 修改用户密码
ALTER USER 'user_test'@'192.168.1.%' IDENTIFIED BY'NewTest@123456';
-- 删除用户
DROP USER IF EXISTS 'user_test'@'192.168.1.%';
-- 查看所有用户
SELECT user, host FROM mysql.user;

11.2 权限操作

-- 授权：给 user_test 分配 test_db 数据库的所有权限
GRANT ALL PRIVILEGES ON test_db.* TO 'user_test'@'192.168.1.%';
-- 授权：仅允许查询 user 表
GRANT SELECT ON test_db.user TO 'user_test'@'192.168.1.%';
-- 回收权限
REVOKE UPDATE ON test_db.user FROM 'user_test'@'192.168.1.%';
-- 刷新权限（授权/回收后必须执行）
FLUSH PRIVILEGES;
-- 查看用户权限
SHOW GRANTS FOR 'user_test'@'192.168.1.%';

性能优化实战

12.1 慢查询日志

开启慢查询日志，定位耗时 SQL：

-- 查看慢查询配置
SHOW VARIABLES LIKE '%slow_query%';
-- 开启慢查询日志（会话级）
SET SESSION slow_query_log = ON;
-- 设置慢查询阈值（1 秒）
SET SESSION long_query_time = 1;
-- 查看慢查询日志路径
SHOW VARIABLES LIKE 'slow_query_log_file';

12.2 EXPLAIN 分析查询

EXPLAIN 用于分析 SQL 执行计划，判断是否使用索引、是否全表扫描：

-- 分析查询语句
EXPLAIN SELECT * FROM `user` WHERE `name`='张三';

EXPLAIN 结果核心字段：

• type：访问类型（ALL→全表扫描，最差；ref→索引扫描，较好；const→主键/唯一索引，最好）
• key：实际使用的索引（NULL 表示未使用索引）
• rows：预估扫描行数（越小越好）
• Extra：额外信息（Using filesort→文件排序，需优化；Using index→覆盖索引，最优）

12.3 优化原则

1. 索引优化：
   • 优先创建复合索引，遵循最左匹配
   • 避免在索引字段做函数/运算
2. 查询优化：
   • 只查需要的字段（避免 SELECT *）
   • 分页查询用 LIMIT，避免大偏移量（如 LIMIT 100000, 10 可优化为 WHERE id > 100000 LIMIT 10）
3. 表结构优化：
   • 用 INT 代替 VARCHAR 存储数字（如手机号可拆分为前缀 + 后缀，或用 BIGINT）
   • 大字段（如文章内容）拆分到单独表
   • 避免使用 NULL（可设置默认值）

常见问题与解决方案

总结

核心关键点回顾

1. 基础操作：掌握数据库/表的 CRUD，重点是 SELECT 查询（条件、分组、连接、子查询）。
2. 核心特性：事务（ACID）保证数据一致性，索引（合理创建 + 避免失效）提升查询性能，约束保证数据完整性。
3. 优化核心：用 EXPLAIN 分析 SQL，慢查询日志定位问题，遵循「最左匹配」「避免 SELECT *」等优化原则。
4. 生产规范：避免使用 root 用户，按最小权限分配账号；优先使用 InnoDB 引擎；外键、存储过程谨慎使用。