MySQL 入门教程——从建库到查询优化

, , ,

MySQL 入门教程——从建库到查询优化

作者： CaoZH
日期： 2023-12-15
本文为原创教程

MySQL 是 2023 年全球使用最广泛的开源关系型数据库（DB-Engines 排名第二）。无论你是后端开发者还是全栈工程师，数据库操作都是必须掌握的核心技能。本文从零开始，带你掌握 MySQL 的安装、建表、CRUD 和基础优化。

一、安装 MySQL

Ubuntu/Debian

1
2
3
4
5
6
7
8
9
10
11
12
13
# 安装 MySQL 8.0
sudo apt update
sudo apt install -y mysql-server-8.0

# 检查状态
sudo systemctl status mysql

# 安全配置
sudo mysql_secure_installation
# 按提示设置：root 密码、移除匿名用户、禁止远程 root 登录

# 登录 MySQL
sudo mysql -u root -p

CentOS

1
2
3
4
5
6
7
8
9
10
11
12
13
14
# 安装 MySQL 8.0
sudo yum install -y https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm
sudo yum install -y mysql-community-server

# 启动
sudo systemctl start mysqld
sudo systemctl enable mysqld

# 获取临时密码
sudo grep 'temporary password' /var/log/mysqld.log

# 登录并修改密码
mysql -u root -p
ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewPassword123!';

⚠️ 注意： MySQL 8.0 的密码策略要求：至少 8 位，包含大小写字母、数字和特殊字符。

二、数据库与表管理

数据库操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 查看所有数据库
SHOW DATABASES;

-- 创建数据库
CREATE DATABASE myapp DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 使用数据库
USE myapp;

-- 查看当前数据库
SELECT DATABASE();

-- 删除数据库
DROP DATABASE myapp;

创建表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE TABLE sys_user (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '主键',
    username VARCHAR(50) NOT NULL COMMENT '用户名',
    password VARCHAR(100) NOT NULL COMMENT '密码',
    nickname VARCHAR(50) DEFAULT NULL COMMENT '昵称',
    email VARCHAR(100) DEFAULT NULL COMMENT '邮箱',
    phone VARCHAR(20) DEFAULT NULL COMMENT '手机号',
    gender CHAR(1) DEFAULT '0' COMMENT '性别（0未知 1男 2女）',
    status CHAR(1) DEFAULT '0' COMMENT '状态（0正常 1停用）',
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    INDEX idx_username (username),
    INDEX idx_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

常用表操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 查看表结构
DESC sys_user;
SHOW CREATE TABLE sys_user;

-- 修改表
ALTER TABLE sys_user ADD COLUMN avatar VARCHAR(500) AFTER email;
ALTER TABLE sys_user MODIFY COLUMN phone VARCHAR(30);
ALTER TABLE sys_user DROP COLUMN avatar;

-- 删除表
DROP TABLE sys_user;

-- 查看所有表
SHOW TABLES;

三、CRUD 操作

插入数据

1
2
3
4
5
6
7
8
9
-- 单条插入
INSERT INTO sys_user (username, password, nickname, email) 
VALUES ('admin', '123456', '管理员', 'admin@example.com');

-- 批量插入
INSERT INTO sys_user (username, password, nickname) VALUES 
('zhangsan', '123456', '张三'),
('lisi', '123456', '李四'),
('wangwu', '123456', '王五');

查询数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- 基本查询
SELECT * FROM sys_user;
SELECT id, username, nickname FROM sys_user;

-- WHERE 条件
SELECT * FROM sys_user WHERE status = '0';
SELECT * FROM sys_user WHERE nickname LIKE '%张%';
SELECT * FROM sys_user WHERE id IN (1, 2, 3);
SELECT * FROM sys_user WHERE create_time >= '2023-01-01';

-- 排序
SELECT * FROM sys_user ORDER BY create_time DESC;

-- 分页
SELECT * FROM sys_user LIMIT 10 OFFSET 0;    -- 第1页（每页10条）
SELECT * FROM sys_user LIMIT 10 OFFSET 10;   -- 第2页
-- 简写
SELECT * FROM sys_user LIMIT 0, 10;          -- 第1页
SELECT * FROM sys_user LIMIT 10, 10;         -- 第2页

-- 聚合函数
SELECT COUNT(*) FROM sys_user;
SELECT status, COUNT(*) FROM sys_user GROUP BY status;
SELECT MAX(create_time), MIN(create_time) FROM sys_user;

更新数据

1
2
3
4
5
UPDATE sys_user 
SET nickname = '超级管理员', email = 'super@admin.com' 
WHERE id = 1;

-- ⚠️ 不加 WHERE 会更新所有行！

删除数据

1
2
3
4
5
6
7
-- 删除单条
DELETE FROM sys_user WHERE id = 3;

-- 清空表（重置自增ID）
TRUNCATE TABLE sys_user;

-- ⚠️ DELETE 不加 WHERE 会删除所有数据！

四、关联查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
-- 建两张表举例
CREATE TABLE sys_order (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    order_no VARCHAR(50) NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- INNER JOIN
SELECT u.username, o.order_no, o.amount
FROM sys_user u
INNER JOIN sys_order o ON u.id = o.user_id;

-- LEFT JOIN（返回所有用户，即使没有订单）
SELECT u.username, o.order_no, o.amount
FROM sys_user u
LEFT JOIN sys_order o ON u.id = o.user_id;

-- 多表联查
SELECT u.username, o.order_no, o.amount
FROM sys_user u
LEFT JOIN sys_order o ON u.id = o.user_id
WHERE u.status = '0'
ORDER BY o.create_time DESC
LIMIT 20;

五、索引优化

查看索引

1
2
3
4
5
-- 查看表的索引
SHOW INDEX FROM sys_user;

-- 查看查询是否使用索引（EXPLAIN）
EXPLAIN SELECT * FROM sys_user WHERE username = 'admin';

EXPLAIN 结果解读

列名 说明 好 差
type 访问类型 const, ref, range ALL（全表扫描）
rows 扫描行数 越小越好 几十万行
Extra 附加信息 Using index Using filesort

创建索引

1
2
3
4
5
6
7
8
9
10
11
-- 单列索引
CREATE INDEX idx_phone ON sys_user(phone);

-- 联合索引（最左前缀原则）
CREATE INDEX idx_status_time ON sys_user(status, create_time);

-- 唯一索引
CREATE UNIQUE INDEX idx_username ON sys_user(username);

-- 删除索引
DROP INDEX idx_phone ON sys_user;

索引使用原则

1
2
3
4
5
6
7
8
9
10
✅ 适合加索引的字段：
  - WHERE 条件中的字段
  - JOIN 关联字段
  - ORDER BY 字段
  - 数据区分度高的字段

❌ 不适合加索引的字段：
  - 频繁更新的字段
  - 数据重复度高的字段（如 status 只有0/1）
  - 很少查询的字段

六、备份与恢复

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
# 备份整个数据库
mysqldump -u root -p myapp > myapp_backup.sql

# 备份多个数据库
mysqldump -u root -p --databases db1 db2 > dbs_backup.sql

# 备份所有数据库
mysqldump -u root -p --all-databases > all_dbs.sql

# 备份指定表
mysqldump -u root -p myapp sys_user sys_order > user_order_backup.sql

# 恢复数据库
mysql -u root -p myapp < myapp_backup.sql

# 压缩备份
mysqldump -u root -p myapp | gzip > myapp_$(date +%Y%m%d).sql.gz
gunzip < myapp_20231215.sql.gz | mysql -u root -p myapp

七、常用 SQL 速查

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- 查看数据库大小
SELECT table_schema, ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Size(MB)'
FROM information_schema.tables GROUP BY table_schema;

-- 查看当前连接
SHOW PROCESSLIST;

-- 查看数据库版本
SELECT VERSION();

-- 查看字符集
SHOW VARIABLES LIKE 'character_set_%';

-- 在线修改字符集
ALTER DATABASE myapp CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE sys_user CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

八、总结

通过本教程，你掌握了：

  • ✅ MySQL 安装与配置
  • ✅ 数据库和表的创建与管理
  • ✅ 完整的 CRUD 操作
  • ✅ 关联查询（JOIN）
  • ✅ 索引基础与 EXPLAIN 分析
  • ✅ 备份与恢复

MySQL 入门只是第一步，接下来可以深入学习： 事务与隔离级别、存储过程、主从复制、分库分表。

首发于 CaoZH 的笔记

CaoZHProgrammer

学习使我快乐。