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

MySQL 数据库从入门到精通教程 | 极客日志

概念	解释
数据库（DB）	存储数据的容器，一个 MySQL 服务可以包含多个数据库（如 `test`、`mysql`）
数据表（Table）	数据库的基本存储单元，由行（记录）和列（字段）组成（如用户表 `user`）
字段（Column）	表的列，定义数据类型（如 `id` int、`name` varchar）
记录（Row）	表的行，是一条完整的数据（如 `1, "张三", 20`）
SQL	操作数据库的标准语言，分为 DQL（查询）、DML（增删改）、DDL（定义）、DCL（权限）

客户端（Navicat/命令行） → MySQL 服务端 → 存储引擎（InnoDB/MyISAM） → 磁盘文件

# 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

[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

操作	SQL 语句
创建数据库	`CREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHARSET 编码];`
查看所有数据库	`SHOW DATABASES;`
使用数据库	`USE 数据库名;`
修改数据库编码	`ALTER DATABASE 数据库名 CHARACTER SET utf8mb4;`
删除数据库	`DROP DATABASE [IF EXISTS] 数据库名;`

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

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

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='用户信息表';

-- 查看表结构
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`;

-- 插入单条数据
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 即可

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

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

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;

运算符/关键字	作用	示例
IN	匹配多个值	`WHERE age IN (20,22,25)`
LIKE	模糊匹配（%任意字符，_单个字符）	`WHERE name LIKE '张%'`
IS NULL	判断字段为空	`WHERE phone IS NULL`
BETWEEN … AND …	区间匹配	`WHERE age BETWEEN 18 AND 30`

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

连接类型	作用	语法
INNER JOIN	只查两表匹配的数据	`FROM user INNER JOIN order ON user.id = order.user_id`
LEFT JOIN	查左表所有数据，右表匹配不上则为 NULL	`FROM user LEFT JOIN order ON user.id = order.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`;

-- 标量子查询：查询年龄大于平均年龄的用户
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
);

函数	作用	示例
CONCAT(a,b)	拼接字符串	`CONCAT('姓名：', name)`
LENGTH(str)	字符串长度（字节）	`LENGTH(name)`
SUBSTR(str,start,len)	截取字符串	`SUBSTR(name,1,2)`
TRIM(str)	去除首尾空格	`TRIM(name)`

函数	作用	示例
ROUND(num,n)	四舍五入（保留 n 位小数）	`ROUND(3.1415,2)` → 3.14
ABS(num)	绝对值	`ABS(-5)` → 5
CEIL(num)	向上取整	`CEIL(3.2)` → 4
FLOOR(num)	向下取整	`FLOOR(3.9)` → 3

函数	作用	示例
NOW()	当前时间（日期 + 时间）	`NOW()` → 2026-01-10 10:00:00
DATE(NOW())	提取日期	`DATE(NOW())` → 2026-01-10
YEAR(NOW())	提取年份	`YEAR(NOW())` → 2026
DATEDIFF(d1,d2)	计算 d1-d2 的天数差	`DATEDIFF(NOW(), '2026-01-01')` → 9

函数	作用	示例
COUNT(*)	统计行数（包含 NULL）	`COUNT(*)`
SUM(字段)	求和	`SUM(age)`
AVG(字段)	平均值	`AVG(age)`
MAX(字段)	最大值	`MAX(age)`
MIN(字段)	最小值	`MIN(age)`

约束类型	关键字	作用	示例
非空约束	NOT NULL	字段不能为空	`name VARCHAR(50) NOT NULL`
唯一约束	UNIQUE	字段值唯一（可多个 NULL）	`phone VARCHAR(11) UNIQUE`
主键约束	PRIMARY KEY	非空 + 唯一，标识唯一记录	`id INT PRIMARY KEY AUTO_INCREMENT`
外键约束	FOREIGN KEY	关联另一张表的主键，保证数据关联完整性	`user_id INT FOREIGN KEY REFERENCES user(id)`
默认约束	DEFAULT	字段默认值	`gender ENUM('男','女') DEFAULT '男'`

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;

-- 开启事务（关闭自动提交）
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;

隔离级别	脏读	不可重复读	幻读	性能
READ UNCOMMITTED（读未提交）	✅	✅	✅	最高
READ COMMITTED（读已提交）	❌	✅	✅	中
REPEATABLE READ（可重复读）	❌	❌	✅	中
SERIALIZABLE（串行化）	❌	❌	❌	最低

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

索引类型	关键字/特点	适用场景
主键索引	PRIMARY KEY	主键字段（自动创建）
唯一索引	UNIQUE	唯一字段（如订单号、手机号）
普通索引	INDEX	普通查询字段（如姓名、年龄）
复合索引	INDEX (字段 1，字段 2)	多字段联合查询（如 name+age）
全文索引	FULLTEXT	文本内容模糊查询（如文章内容）

-- 创建普通索引
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`;

-- 创建存储过程：根据性别查询用户
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;

-- 创建存储函数：计算用户年龄（根据生日）
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`;

-- 创建用户：允许 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;

权限类型	关键字	说明
数据库权限	ALL PRIVILEGES	所有权限
表权限	SELECT, INSERT, UPDATE	查询、插入、更新
列权限	SELECT (name,age)	仅查询指定列

-- 授权：给 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.%';

-- 查看慢查询配置
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';

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

问题现象	原因	解决方案
连接 MySQL 报错 1045	密码错误/权限不足	重置密码/检查用户 host 授权
索引失效	违反最左匹配/函数操作索引字段	调整查询条件/避免索引字段运算
事务回滚失败	表引擎为 MyISAM	改为 InnoDB 引擎
插入中文乱码	编码不一致	统一设置为 utf8mb4（库/表/连接）
锁等待超时	事务长时间未提交	优化事务逻辑，缩短事务执行时间

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

前言

1.1 什么是 MySQL？

1.2 学习目标

1.3 环境准备

MySQL 基础认知

2.1 核心概念

2.2 MySQL 架构（简化版）

MySQL 安装与配置

3.1 Windows 安装

步骤 1：下载安装包

步骤 2：安装流程

3.2 Linux（CentOS7）安装

3.3 基础配置（my.cnf）

MySQL 核心语法

4.1 数据库操作（DDL）

4.2 数据表操作（DDL）

4.2.1 创建表

4.2.2 常用表操作

4.3 数据操作（DML）

4.3.1 插入数据（INSERT）

4.3.2 更新数据（UPDATE）

4.3.3 删除数据（DELETE）

4.3.4 查询数据（DQL，核心）

高级查询技巧

5.1 条件查询进阶

5.2 分组查询（GROUP BY + HAVING）

5.3 连接查询（多表关联）

5.4 子查询

MySQL 函数

6.1 字符串函数

6.2 数值函数

6.3 日期时间函数

6.4 聚合函数（重点）

数据约束

事务管理

8.1 事务的 ACID 特性

8.2 事务操作语法

8.3 事务隔离级别

索引优化

9.1 索引的作用

9.2 索引类型

9.3 索引操作

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

存储过程与函数

10.1 存储过程创建与调用

10.2 存储函数

用户与权限管理

11.1 用户操作

11.2 权限操作

性能优化实战

12.1 慢查询日志

12.2 EXPLAIN 分析查询

12.3 优化原则

常见问题与解决方案

总结

核心关键点回顾

微信扫一扫，关注极客日志

更多推荐文章

相关免费在线工具