MySQL教程

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

# 1. 卸载系统自带的mariadb，避免冲突
yum remove -y mariadb*
# 2. 配置MySQL官方源
wget https://dev.mysql.com/get/mysql80-community-release-el8-3.noarch.rpm
rpm -ivh mysql80-community-release-el8-3.noarch.rpm
# 3. 安装MySQL
yum install -y mysql-community-server
# 4. 启动服务，设置开机自启
systemctl start mysqld
systemctl enable mysqld
# 5. 获取临时密码
grep 'temporary password' /var/log/mysqld.log
# 6. 登录修改密码
mysql -u root -p
ALTER USER 'root'@'localhost' IDENTIFIED BY 'Mysql@Linux2026!';

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

# 1. 更新源，清理旧版本
apt update -y && apt upgrade -y
apt remove -y mysql* mariadb*
# 2. 安装MySQL
apt install -y mysql-server
# 3. 启动服务
systemctl start mysql
systemctl enable mysql
# 4. 执行安全加固，设置密码
mysql_secure_installation

1
2
3
4
5
6
7
8
9

# 1. 如果你没装Homebrew，先装它
/bin/bash -c "$(curl -fsSL https://mirrors.ustc.edu.cn/misc/brew-install.sh)"
# 2. 安装MySQL
brew update
brew install mysql
# 3. 启动服务
brew services start mysql
# 4. 安全加固，设置密码
mysql_secure_installation

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

-- 1. 创建数据库，不存在才创建，指定编码为utf8mb4（支持emoji）
CREATE DATABASE IF NOT EXISTS test_db DEFAULT CHARSET utf8mb4;

-- 2. 查看所有数据库
SHOW DATABASES;

-- 3. 使用/切换到这个数据库
USE test_db;

-- 4. 删除数据库（谨慎！删了就没了）
DROP DATABASE IF EXISTS test_db;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

-- 创建用户表，包含各种约束和注释
CREATE TABLE IF NOT EXISTS `user` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '用户ID，自增主键',
    `username` VARCHAR(50) NOT NULL UNIQUE COMMENT '用户名，唯一',
    `name` VARCHAR(10) NOT NULL COMMENT '姓名',
    `age` TINYINT UNSIGNED DEFAULT 0 COMMENT '年龄',
    `gender` ENUM('男','女','未知') DEFAULT '未知' COMMENT '性别',
    `phone` CHAR(11) NOT NULL UNIQUE COMMENT '手机号',
    `create_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间，自动填充',
    PRIMARY KEY (`id`) -- 主键，唯一标识一条记录
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户信息表';

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

-- 查看表结构
DESC `user`;

-- 查看建表的SQL语句
SHOW CREATE TABLE `user`;

-- 添加字段：给user表加个email字段
ALTER TABLE `user` ADD `email` VARCHAR(50) AFTER `phone`;

-- 修改字段：把phone的长度改成20
ALTER TABLE `user` MODIFY `phone` VARCHAR(20);

-- 删除字段
ALTER TABLE `user` DROP `email`;

-- 删除表（谨慎！）
DROP TABLE IF EXISTS `user`;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

-- 1. 插入单条数据，指定字段
INSERT INTO `user` (`username`, `name`, `age`, `gender`, `phone`) 
VALUES ('zhangsan', '张三', 20, '男', '13800138001');

-- 2. 批量插入多条数据，效率更高
INSERT INTO `user` (`username`, `name`, `age`, `gender`, `phone`) 
VALUES 
('lisi', '李四', 22, '女', '13800138002'),
('wangwu', '王五', 25, '男', '13800138003'),
('zhaoliu', '赵六', 18, '未知', '13800138004');

-- 3. 插入所有字段，按表的顺序来，id自增可以传NULL
INSERT INTO `user` VALUES (NULL, 'sunqi', '孙七', 21, '男', '13800138005', NOW());

SELECT * FROM `user`

1
2
3
4
5
6
7
8

-- 1. 更新单条数据：把id=1的张三的年龄改成21
UPDATE `user` SET `age` = 21 WHERE `id` = 1;

-- 2. 更新多个字段：把李四的年龄和性别都改了
UPDATE `user` SET `age` = 23, `gender` = '女' WHERE `id` = 2;

-- 3. 批量更新：给所有20岁以下的用户年龄加1（谨慎使用！）
UPDATE `user` SET `age` = `age` + 1 WHERE `age` < 20;

1
2
3
4
5
6
7
8

-- 1. 删除单条数据：删除id=4的赵六
DELETE FROM `user` WHERE `id` = 4;

-- 2. 批量删除：删除所有年龄大于100的无效数据
DELETE FROM `user` WHERE `age` > 100;

-- 3. 清空整个表，重置自增ID（比DELETE快，但是不可回滚）
TRUNCATE TABLE `user`;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

-- 1. 查询所有字段（不推荐，效率低，要啥查啥）
SELECT * FROM `user`;

-- 2. 查询指定字段
SELECT `name`, `age`, `gender` FROM `user`;

-- 3. 给字段起别名，方便看
SELECT `name` AS 姓名, `age` AS 年龄, `gender` AS 性别 FROM `user`;

-- 4. 去重：查询所有不重复的性别
SELECT DISTINCT `gender` FROM `user`;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

-- 1. 查询年龄大于20的男性
SELECT * FROM `user` WHERE `age` > 20 AND `gender` = '男';

-- 2. 查询年龄在18到25之间的用户
SELECT * FROM `user` WHERE `age` BETWEEN 18 AND 25;

-- 3. 查询姓名是张三或者李四的
SELECT * FROM `user` WHERE `name` IN ('张三', '李四');

-- 4. 查询姓张的用户（%匹配任意字符）
SELECT * FROM `user` WHERE `name` LIKE '张%';

-- 5. 查询姓名是两个字的用户（_匹配单个字符）
SELECT * FROM `user` WHERE `name` LIKE '__';

1
2
3
4
5
6
7
8

-- 1. 按年龄升序（从小到大，ASC是默认）
SELECT * FROM `user` ORDER BY `age` ASC;

-- 2. 按年龄降序（从大到小）
SELECT * FROM `user` ORDER BY `age` DESC;

-- 3. 多字段排序：先按年龄排，年龄相同按创建时间排
SELECT * FROM `user` ORDER BY `age` DESC, `create_time` DESC;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

-- 语法：LIMIT 起始索引, 每页条数
-- 起始索引 = (页码 - 1) * 每页条数

-- 第1页，每页2条
SELECT * FROM `user` LIMIT 0, 2;

-- 第2页，每页2条
SELECT * FROM `user` LIMIT 2, 2;

-- 第3页，每页2条
SELECT * FROM `user` LIMIT 4, 2;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

-- 聚合函数：用来统计的函数
-- COUNT(*)：统计行数，SUM()：求和，AVG()：平均值，MAX()：最大值，MIN()：最小值

-- 1. 按性别分组，统计每个性别的人数和平均年龄
SELECT 
    `gender`, 
    COUNT(*) AS 人数, 
    AVG(`age`) AS 平均年龄 
FROM `user` 
GROUP BY `gender`;

-- 2. 先过滤，再分组：查询20岁以上的用户，按性别分组
SELECT `gender`, COUNT(*) FROM `user` WHERE `age` > 21 GROUP BY `gender`;

-- 3. 分组后再过滤：筛选人数大于1的分组（HAVING是分组后的过滤，WHERE是分组前的）
SELECT `gender`, COUNT(*) AS cnt FROM `user` GROUP BY `gender` HAVING cnt > 1;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

-- 订单表
CREATE TABLE `order` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `order_no` VARCHAR(30) NOT NULL UNIQUE,
    `user_id` INT UNSIGNED NOT NULL,
    `amount` DECIMAL(10,2) NOT NULL,
    `create_time` DATETIME DEFAULT NOW(),
    FOREIGN KEY (`user_id`) REFERENCES `user`(`id`)
) ENGINE=InnoDB;

-- 插入测试数据
INSERT INTO `order` (`order_no`, `user_id`, `amount`) VALUES
('ORD001', 1, 99.9),
('ORD002', 1, 199.9),
('ORD003', 2, 299.9);

1
2
3
4
5

-- 查询用户的姓名和他的订单
SELECT 
    u.`name`, o.`order_no`, o.`amount`
FROM `user` u
INNER JOIN `order` o ON u.`id` = o.`user_id`;

1
2
3
4
5

-- 查询所有用户，以及他们的订单，没有订单的也显示
SELECT 
    u.`name`, o.`order_no`, o.`amount`
FROM `user` u
LEFT JOIN `order` o ON u.`id` = o.`user_id`;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

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

-- 2. 表子查询：查询有订单的用户
SELECT * FROM `user` 
WHERE `id` IN (SELECT DISTINCT `user_id` FROM `order`);

-- 3. exists子查询：查询有订单的用户，效率比IN高
SELECT * FROM `user` u
WHERE EXISTS (SELECT 1 FROM `order` o WHERE o.`user_id` = u.`id`);

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

-- 创建表的时候，给name加非空约束
CREATE TABLE `user` (
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(50) NOT NULL, -- 姓名不能为空
    `age` INT
);

-- 测试：插入name为空的数据，会直接报错！
INSERT INTO `user`(`name`, `age`) VALUES(NULL, 20);
-- 报错：Column 'name' cannot be null

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

-- 给username和phone加唯一约束
CREATE TABLE `user` (
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `username` VARCHAR(50) NOT NULL UNIQUE, -- 用户名唯一
    `phone` CHAR(11) NOT NULL UNIQUE, -- 手机号唯一
    `name` VARCHAR(50) NOT NULL
);

-- 测试：插入重复的用户名，会报错！
INSERT INTO `user`(`username`, `phone`, `name`) VALUES('zhangsan', '13800138001', '张三');
INSERT INTO `user`(`username`, `phone`, `name`) VALUES('zhangsan', '13800138002', '张三2');
-- 报错：Duplicate entry 'zhangsan' for key 'user.username'

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

CREATE TABLE `user` (
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(50) NOT NULL,
    `age` INT DEFAULT 0, -- 年龄默认0
    `gender` ENUM('男','女','未知') DEFAULT '未知', -- 性别默认未知
    `status` TINYINT DEFAULT 1, -- 状态默认1：启用
    `create_time` DATETIME DEFAULT CURRENT_TIMESTAMP -- 创建时间默认当前时间
);

-- 插入数据的时候，不用填这些字段，自动用默认值
INSERT INTO `user`(`name`) VALUES('张三');
-- 结果：age=0, gender=未知, status=1, create_time=现在的时间

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

CREATE TABLE `account` (
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(50) NOT NULL,
    `age` INT CHECK (age > 0), -- 年龄必须大于0
    `balance` DECIMAL(10,2) CHECK (balance >= 0) -- 余额不能小于0
);

-- 测试：插入年龄为-1的，会报错！
INSERT INTO `account`(`name`, `age`, `balance`) VALUES('张三', -1, 100);
-- 报错：Check constraint 'account_chk_1' is violated.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

-- 先建主表：用户表
CREATE TABLE `user` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(50) NOT NULL
);

-- 再建从表：订单表，user_id关联用户表的id
CREATE TABLE `order` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `order_no` VARCHAR(30) NOT NULL,
    `user_id` INT UNSIGNED NOT NULL,
    `amount` DECIMAL(10,2) NOT NULL,
    -- 定义外键：user_id关联users的id
    FOREIGN KEY (user_id) REFERENCES `user`(id)
);

-- 测试：插入一个不存在的user_id的订单，会报错！
INSERT INTO `order`(`order_no`, `user_id`, `amount`) VALUES('ORD001', 999, 100);
-- 报错：Cannot add or update a child row: a foreign key constraint fails

 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

-- 先建主表：用户表
CREATE TABLE `user` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(50) NOT NULL
);

-- 建表的时候，指定级联删除
CREATE TABLE `order` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `order_no` VARCHAR(30) NOT NULL,
    `user_id` INT UNSIGNED NOT NULL,
    `amount` DECIMAL(10,2) NOT NULL,
    -- 删用户的时候，级联删除他的订单
    FOREIGN KEY (user_id) REFERENCES `user`(id) ON DELETE CASCADE
);

INSERT INTO `user` VALUES(NULL, '张三');

INSERT INTO `order` VALUES(NULL, 'a0001', 1, 100);

-- 现在删用户，他的订单会自动被删掉
DELETE FROM `user` WHERE id = 1;

SELECT * FROM `order`
-- 关联的order里的user_id=1的订单，也一起没了

1
2
3
4
5
6
7
8
9

-- 先建账户表
CREATE TABLE `account` (
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(50) NOT NULL,
    `balance` DECIMAL(10,2) NOT NULL DEFAULT 0
);

-- 插入测试数据：A有1000，B有1000
INSERT INTO `account`(`name`, `balance`) VALUES('A', 1000), ('B', 1000);

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

-- 1. 开启事务，关闭自动提交
-- 开启事务之后，你做的所有操作都是临时的，没提交之前别人看不到
START TRANSACTION;

-- 2. 执行操作：A减100
UPDATE account SET balance = balance - 100 WHERE id = 1;

-- 3. 执行操作：B加100
UPDATE account SET balance = balance + 100 WHERE id = 2;

-- 4. 检查一下，没问题的话，提交事务，生效
COMMIT;

-- 5. 如果中间出错了，比如网络断了，就回滚，回到事务之前的状态
ROLLBACK;

1
2
3
4
5
6

-- 我们有复合索引(name, age)
-- 这个查询，只查name和age，都在索引里，不用回表，覆盖索引
SELECT `name`, `age` FROM `user` WHERE `name` = '张三';

-- 这个查询，要查phone，phone不在索引里，所以要回表
SELECT `name`, `age`, `phone` FROM `user` WHERE `name` = '张三';

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

-- 1. 创建普通索引：给name字段加索引
CREATE INDEX idx_user_name ON `user`(`name`);

-- 2. 创建唯一索引：给phone加唯一索引
CREATE UNIQUE INDEX idx_user_phone ON `user`(`phone`);

-- 3. 创建复合索引：给name、age、gender加复合索引
CREATE INDEX idx_user_name_age_gender ON `user`(`name`, `age`, `gender`);

-- 4. 查看表的所有索引
SHOW INDEX FROM `user`;

-- 5. 删除索引
DROP INDEX idx_user_name ON `user`;

1
2

-- 对create_time做了YEAR函数，索引失效
SELECT * FROM user WHERE YEAR(create_time) = 2026;

1
2

-- 用范围查询，不用函数，索引生效
SELECT * FROM user WHERE create_time >= '2026-01-01' AND create_time < '2027-01-01';

1
2

-- 开头的%，索引失效
SELECT * FROM user WHERE name LIKE '%张三';

1
2

-- 结尾的%，索引生效
SELECT * FROM user WHERE name LIKE '张三%';

1
2

-- name有索引，age没有，OR导致整个索引失效
SELECT * FROM user WHERE name = '张三' OR age = 20;

1
2

-- phone是字符串，你传了数字13800138001，隐式转换，索引失效
SELECT * FROM user WHERE phone = 13800138001;

1
2

-- 传字符串，索引生效
SELECT * FROM user WHERE phone = '13800138001';

1

EXPLAIN SELECT * FROM user WHERE name = '张三';

1
2

# 备份test_db库，生成sql文件
mysqldump -u backup_user -p --default-character-set=utf8mb4 --single-transaction test_db > D:mysql_backuptest_db_backup.sql

1
2

# 只备份结构，用来迁移表结构
mysqldump -u root -p --no-data test_db > D:mysql_backuptest_db_struct.sql

1
2

# 备份整个MySQL的所有库，用来迁移服务器
mysqldump -u root -p --all-databases > D:mysql_backupfull_backup.sql

1
2
3
4

# 先创建空库
mysql -u root -p -e "CREATE DATABASE IF NOT EXISTS test_db DEFAULT CHARSET utf8mb4"
# 恢复备份
mysql -u root -p test_db < D:mysql_backuptest_db_backup.sql

1
2
3

-- 也可以在MySQL客户端里用source命令
USE test_db;
source D:/mysql_backup/test_db_backup.sql; -- 注意路径用正斜杠

 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
27
28
29
30
31
32
33
34
35
36
37
38
39

-- 1. 创建数据库
CREATE DATABASE IF NOT EXISTS shop CHARACTER SET utf8mb4;
USE shop;

-- 2. 用户表
CREATE TABLE users (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '用户ID',
    username VARCHAR(50) NOT NULL UNIQUE COMMENT '用户名',
    phone VARCHAR(20) NOT NULL COMMENT '手机号',
    status TINYINT DEFAULT 1 COMMENT '状态:1启用0禁用',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '注册时间',
    PRIMARY KEY (id),
    INDEX idx_phone (phone)
) ENGINE=InnoDB COMMENT='用户表';

-- 3. 商品表
CREATE TABLE products (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '商品ID',
    name VARCHAR(200) NOT NULL COMMENT '商品名称',
    price DECIMAL(10,2) UNSIGNED NOT NULL COMMENT '价格',
    stock INT UNSIGNED DEFAULT 0 COMMENT '库存',
    status TINYINT DEFAULT 1 COMMENT '上架状态',
    PRIMARY KEY (id),
    INDEX idx_name (name)
) ENGINE=InnoDB COMMENT='商品表';

-- 4. 订单表
CREATE TABLE orders (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '订单ID',
    user_id INT UNSIGNED NOT NULL COMMENT '用户ID',
    total_amount DECIMAL(10,2) UNSIGNED NOT NULL COMMENT '订单金额',
    status ENUM('pending','paid','shipped','completed','cancelled') DEFAULT 'pending' COMMENT '订单状态',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    paid_at TIMESTAMP NULL COMMENT '支付时间',
    PRIMARY KEY (id),
    INDEX idx_user (user_id),
    INDEX idx_status (status),
    FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB COMMENT='订单表';

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

-- 插入用户
INSERT INTO users(username, phone) VALUES
('张三', '13800138001'),
('李四', '13800138002'),
('王五', '13800138003');

-- 插入商品
INSERT INTO products(name, price, stock) VALUES
('笔记本电脑', 5999.00, 10),
('无线鼠标', 89.90, 100),
('机械键盘', 299.00, 50);

-- 插入订单
INSERT INTO orders(user_id, total_amount, status) VALUES
(1, 5999.00, 'paid'),
(1, 388.90, 'completed'),
(2, 89.90, 'pending');

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

-- 1. 查询每个用户的订单数和消费总额
SELECT 
    u.username,
    COUNT(o.id) AS 订单数,
    IFNULL(SUM(o.total_amount), 0) AS 消费总额
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id
ORDER BY 消费总额 DESC;

-- 2. 查询已支付的订单，关联用户和商品
-- （这里我们可以扩展订单详情表，不过新手先理解这个）