1
2
3
4
5

# 先更新一下软件包列表
sudo apt update

# 看看能不能找到mysql-server
apt search mysql-server

1
2
3
4
5
6

# 安装MySQL服务器和客户端
sudo apt install mysql-server -y

# 安装完成后，MySQL服务应该已经自动启动了
# 检查一下状态
sudo systemctl status mysql

1
2

# 运行安全加固脚本
sudo mysql_secure_installation

1
2
3
4
5
6
7

# 本地登录（会提示输入密码）
mysql -u root -p

# 登录成功后，应该看到MySQL的欢迎界面：
# Welcome to the MySQL monitor.  Commands end with ; or \g.
# Your MySQL connection id is 8
# Server version: 8.0.35 MySQL Community Server - GPL

1
2
3

exit
-- 或者
\q

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

# 添加MySQL Yum仓库
sudo yum install https://dev.mysql.com/get/mysql80-community-release-el8-7.noarch.rpm

# 安装MySQL
sudo dnf install mysql-community-server -y

# 启动服务
sudo systemctl start mysqld
sudo systemctl enable mysqld

# MySQL 8.0安装时会生成临时密码，在日志里找
sudo grep 'temporary password' /var/log/mysqld.log

1
2
3
4
5

# 更新软件源
sudo apt update

# 安装MariaDB服务器和客户端
sudo apt install mariadb-server mariadb-client -y

1
2

# 查看MariaDB服务状态
sudo systemctl status mariadb

1
2

# 运行安全脚本（MariaDB的安全配置比MySQL更简洁）
sudo mysql_secure_installation

1
2
3
4
5
6

# 在Ubuntu上，MariaDB默认可以使用unix_socket认证
# 也就是直接用系统账号登录，不需要密码
sudo mysql

# 或者用密码登录（如果设置了的话）
mysql -u root -p

1
2
3
4
5
6
7
8
9

# CentOS 7/8 默认源里有MariaDB
sudo yum install mariadb-server mariadb -y

# 启动并设置开机启动
sudo systemctl start mariadb
sudo systemctl enable mariadb

# 安全配置
sudo mysql_secure_installation

1
2
3
4
5
6
7

# Ubuntu
curl -fsSL https://get.docker.com | sh
sudo usermod -aG docker $USER

# 启动Docker
sudo systemctl start docker
sudo systemctl enable docker

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

# 拉取MySQL镜像
docker pull mysql:8.0

# 运行MySQL容器
docker run -d \
    --name mysql-server \
    -p 3306:3306 \
    -e MYSQL_ROOT_PASSWORD=MySecurePassword123 \
    -v mysql-data:/var/lib/mysql \
    mysql:8.0

# 查看运行状态
docker ps

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

# 拉取MariaDB镜像
docker pull mariadb:10.11

# 运行MariaDB容器
docker run -d \
    --name mariadb-server \
    -p 3307:3306 \
    -e MARIADB_ROOT_PASSWORD=MySecurePassword123 \
    -v mariadb-data:/var/lib/mysql \
    mariadb:10.11

# 查看运行状态
docker ps

1
2
3
4
5

# 进入容器
docker exec -it mysql-server mysql -u root -p

# 或者用docker exec直接执行SQL
docker exec -it mysql-server mysql -u root -pMySecurePassword123 -e "SHOW DATABASES;"

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

# 1. 检查版本
mysql --version
# mysql  Ver 8.0.35 for Linux on x86_64 (MySQL Community Server - GPL)

mariadb --version
# mariadb  Ver 15.1 Distrib 10.6.12-MariaDB, for debian-linux-gnu (x86_64)

# 2. 检查服务是否运行
sudo systemctl status mysql  # 或 mariadb

# 3. 检查端口是否监听
sudo ss -tlnp | grep 3306
# LISTEN  0  151  127.0.0.1:3306  0.0.0.0:*  users:(("mysqld",pid=1234,fd=21))

# 4. 测试连接
mysql -u root -p

1
2
3
4
5

# 主配置文件
/etc/mysql/mysql.conf.d/mysqld.cnf

# 额外配置目录（放自定义配置）
/etc/mysql/conf.d/

1
2
3
4
5

# 主配置文件
/etc/mysql/mariadb.conf.d/50-server.cnf

# 额外配置目录
/etc/mysql/mariadb.conf.d/

1
2
3
4
5

# Ubuntu/Debian 系统查看 MySQL 主配置
sudo cat /etc/mysql/mysql.conf.d/mysqld.cnf

# CentOS/RHEL 系统查看 MySQL 主配置
sudo cat /etc/my.cnf

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

# MySQL服务进程配置
[mysqld]
#
# 基础设置
#
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
# 数据目录，重要！记录你的数据存在哪儿
datadir     = /var/lib/mysql
# 日志文件目录
log-error   = /var/log/mysql/error.log
# PID文件目录
!includedir /etc/mysql/conf.d/

1
2

# 查看MariaDB主配置
sudo cat /etc/mysql/mariadb.conf.d/50-server.cnf

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

# MariaDB服务进程配置
[mysqld]
#
# 基础设置
#
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
# 数据目录
datadir     = /var/lib/mysql
# 字符集设置
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

[client]           # 客户端配置
port        = 3306
socket      = /var/run/mysqld/mysqld.sock

[mysqld]           # 服务器端配置
port        = 3306
datadir     = /var/lib/mysql
max_connections = 151

[mysql]            # mysql命令行客户端配置
default-character-set = utf8mb4

 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
40
41
42

# 服务端配置
[mysqld]

# 端口号，默认3306（生产环境建议改成别的）
port = 3306

# 数据存放目录（重要！磁盘空间要够大）
datadir = /var/lib/mysql

# PID文件位置
pid-file = /var/run/mysqld/mysqld.pid

# Socket文件位置（本地连接用）
socket = /var/run/mysqld/mysqld.sock

# 最大连接数（根据服务器配置调整）
# 小水管服务器：100-200
# 中等服务器：200-500
# 大型服务器：500-1000+
max_connections = 200

# 查询缓存大小（MySQL 8.0已移除，MariaDB还在用）
# query_cache_size = 128M

# 线程池大小（通常设为CPU核心数的2-4倍）
thread_cache_size = 8

# 表缓存大小
table_open_cache = 2000

# 日志文件路径
log_error = /var/log/mysql/error.log

# 慢查询日志（超过这个时间的SQL会被记录）
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2  # 超过2秒算慢查询

# 二进制日志（用于主从复制和数据恢复）
log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 7  # 日志保留7天
max_binlog_size = 100M

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

# 1. 先备份原配置
sudo cp /etc/mysql/mariadb.conf.d/50-server.cnf /etc/mysql/mariadb.conf.d/50-server.cnf.bak

# 2. 用nano或vim编辑
sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf

# 3. 修改后，检查语法
sudo mysqld --validate-config
# 如果输出 "Safely checked" 就没有语法错误

# 4. 重启服务使配置生效
sudo systemctl restart mariadb  # 或 mysql

1

sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf

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

[mysqld]
#
# 字符集配置 - 强烈建议使用utf8mb4
#
# 服务器默认字符集
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

# 连接字符集
init_connect = 'SET NAMES utf8mb4'

# 跳过-name解析加速（可选）
# skip-name-resolve

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

-- 查看当前字符集配置
SHOW VARIABLES LIKE 'character%';
SHOW VARIABLES LIKE 'collation%';

-- 设置会话字符集
SET NAMES utf8mb4;

-- 设置全局字符集（需要权限）
SET GLOBAL character_set_server = utf8mb4;
SET GLOBAL collation_server = utf8mb4_unicode_ci;

 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 DATABASE test_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 查看数据库字符集
SHOW CREATE DATABASE test_db;

-- 创建测试表
USE test_db;
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    bio TEXT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 插入带Emoji的数据测试
INSERT INTO users (name, bio) VALUES 
    ('小明', '今天心情😄，终于写完代码了！'),
    ('小红', '收到老板的消息📧，又要加班了💔'),
    ('前端小哥', '修复了一个🪲，测试说还有3个🪲');

-- 查询验证
SELECT * FROM users;

-- 查看建表语句，确认字符集
SHOW CREATE TABLE users;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

# 1. 检查配置文件
grep -E "(character|collation)" /etc/mysql/mariadb.conf.d/50-server.cnf

# 2. 检查MySQL服务配置
mysql -u root -p -e "SHOW VARIABLES LIKE 'character_set%';"

# 3. 检查数据库默认字符集
mysql -u root -p -e "SHOW VARIABLES LIKE 'collation%';"

# 4. 测试中文存储
mysql -u root -p -e "CREATE DATABASE IF NOT EXISTS test_charset; USE test_charset; CREATE TABLE t (c VARCHAR(100)); INSERT INTO t VALUES ('测试中文和Emoji🎉'); SELECT * FROM t;"

1

CREATE USER '用户名'@'主机' IDENTIFIED BY '密码';

1
2

-- 创建一个只能从本地连接的应用用户
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'AppPass2024!';

1
2
3
4
5

-- 创建一个可以从局域网任何机器连接的用户
CREATE USER 'app_user'@'192.168.1.%' IDENTIFIED BY 'AppPass2024!';

-- 或者允许从任何主机连接（生产环境慎用！）
CREATE USER 'app_user'@'%' IDENTIFIED BY 'AppPass2024!';

1
2
3

-- 创建用户，密码永不过期
CREATE USER 'dev_user'@'localhost' IDENTIFIED BY 'DevPass2024!';
ALTER USER 'dev_user'@'localhost' PASSWORD EXPIRE NEVER;

1
2
3

-- 创建用户，限制最大连接数为10
CREATE USER 'limited_user'@'localhost' IDENTIFIED BY 'LimitPass2024!'
WITH MAX_CONNECTIONS_PER_HOUR 10;

1
2

-- 创建只读用户（后续会讲如何授权）
CREATE USER 'readonly_user'@'%' IDENTIFIED BY 'ReadOnly2024!';

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

-- 查看所有用户（MySQL用户信息在mysql.user表）
SELECT user, host, plugin FROM mysql.user;

-- 查看更详细的信息
SELECT 
    user, 
    host, 
    authentication_string, 
    plugin,
    account_locked,
    password_expired
FROM mysql.user;

1

GRANT 权限 ON 数据库.表 TO '用户名'@'主机';

1
2
3
4
5

-- 应用需要连接数据库，查询、插入、更新、删除表数据
-- 但不能修改表结构、不能删除表
GRANT SELECT, INSERT, UPDATE, DELETE 
ON myapp.* 
TO 'app_user'@'localhost';

1
2
3
4

-- 开发人员需要读写所有业务数据库，但不能管理用户
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, INDEX
ON myapp.* 
TO 'dev_user'@'192.168.1.%';

1
2
3
4
5

-- DBA需要管理一切，但不能给其他DBA授权（安全考虑）
GRANT ALL PRIVILEGES 
ON *.* 
TO 'dba_admin'@'localhost'
WITH GRANT OPTION;

1
2
3
4

-- 报表系统只能读取，不能写入
GRANT SELECT 
ON analytics_db.* 
TO 'report_user'@'%';

1
2
3
4

-- 备份账号需要锁表、查看定义，但不能读数据内容
GRANT SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER
ON myapp.* 
TO 'backup_user'@'localhost';

1
2
3
4

-- MySQL不支持一次授权多个数据库，需要分别执行
GRANT SELECT, INSERT, UPDATE, DELETE ON db1.* TO 'multi_db_user'@'%';
GRANT SELECT, INSERT, UPDATE, DELETE ON db2.* TO 'multi_db_user'@'%';
GRANT SELECT, INSERT, UPDATE, DELETE ON db3.* TO 'multi_db_user'@'%';

1
2
3
4
5
6

-- 如果用户不存在，可以直接用GRANT创建（MySQL特性）
-- 同时创建用户并授权
GRANT SELECT, INSERT, UPDATE, DELETE 
ON myapp.* 
TO 'new_app_user'@'%' 
IDENTIFIED BY 'NewUserPass2024!';

1

REVOKE 权限 ON 数据库.表 FROM '用户名'@'主机';

1
2
3
4

-- 发现DELETE权限被滥用，先收回来
REVOKE DELETE 
ON myapp.* 
FROM 'app_user'@'localhost';

1
2
3

-- 用户要离职了，回收所有权限
REVOKE ALL PRIVILEGES, GRANT OPTION 
FROM 'leaving_employee'@'%';

1
2
3
4
5
6
7
8

-- 用户滥用权限，先降级为只读
REVOKE SELECT, INSERT, UPDATE, DELETE 
ON myapp.* 
FROM 'troublesome_user'@'%';

GRANT SELECT 
ON myapp.* 
TO 'troublesome_user'@'%';

1

DROP USER '用户名'@'主机';

1
2

-- 用户已离职，删除账号
DROP USER 'former_employee'@'%';

1
2
3
4

-- 批量删除测试账号
DROP USER 'test_user1'@'localhost', 
           'test_user2'@'localhost', 
           'temp_user'@'%';

1
2
3

-- 确认用户已删除
SELECT user, host FROM mysql.user WHERE user = 'former_employee';
-- 结果应该是空集

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

-- ❌ 错误示范：root用户给外部应用使用
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password';

-- ❌ 错误示范：密码太简单
CREATE USER 'app'@'%' IDENTIFIED BY '123456';

-- ❌ 错误示范：允许任意主机连接
CREATE USER 'app'@'%' IDENTIFIED BY 'password';

-- ✅ 正确示范：最小权限 + 强密码 + 指定主机
CREATE USER 'app'@'192.168.1.100' IDENTIFIED BY 'AppStr0ngP@ssw0rd!';
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp.* TO 'app'@'192.168.1.100';

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

-- 方法1：用ALTER USER改自己的密码
ALTER USER 'username'@'host' IDENTIFIED BY 'NewPassword123!';

-- 方法2：用SET改密码
SET PASSWORD FOR 'username'@'host' = 'NewPassword123!';

-- 方法3：用UPDATE直接修改（需要FLUSH PRIVILEGES）
UPDATE mysql.user SET authentication_string = PASSWORD('NewPassword123!') 
WHERE user = 'username' AND host = 'host';
FLUSH PRIVILEGES;

1
2
3
4
5
6
7
8

-- 查看当前用户权限
SHOW GRANTS;

-- 查看指定用户权限
SHOW GRANTS FOR 'username'@'host';

-- 查看权限的另一种方式（MySQL 8.0+）
SHOW PRIVILEGES;

1
2
3
4
5
6

-- 修改用户表后需要刷新权限
FLUSH PRIVILEGES;

-- 这两个命令也可以reload权限
FLUSH TABLES;
FLUSH STATUS;

1

CREATE DATABASE 数据库名;

1
2

-- 创建一个叫 myapp 的数据库
CREATE DATABASE myapp;

1
2

-- 指定utf8mb4字符集
CREATE DATABASE myapp CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

1
2

-- 防止重复创建报错
CREATE DATABASE IF NOT EXISTS myapp;

1
2
3

-- 在指定路径创建数据库（需要先创建目录）
CREATE DATABASE myapp 
DATA DIRECTORY '/mnt/fast-disk/myapp/';

1

SHOW DATABASES;

1
2

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

1
2
3
4
5

-- 模糊匹配查找数据库
SHOW DATABASES LIKE '%app%';

-- 查找以 my 开头的数据库
SHOW DATABASES LIKE 'my%';

1

USE 数据库名;

1
2
3
4
5

-- 选择要操作的数据库
USE myapp;

-- 选择后，后续所有操作都在这个数据库中进行
-- 直到执行 USE 切换到其他数据库

1
2
3
4
5
6
7
8

-- 方法1：SELECT查询
SELECT DATABASE();

-- 方法2：查看连接状态
STATUS;

-- 方法3：查看当前数据库
SELECT @current_db;

 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

-- 1. 查看当前有哪些数据库
SHOW DATABASES;

-- 2. 创建一个新数据库（指定字符集）
CREATE DATABASE IF NOT EXISTS shop 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

-- 3. 选择这个数据库
USE shop;

-- 4. 在数据库中创建表（下一节会详细讲）
CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) NOT NULL
);

-- 5. 查看当前数据库中的表
SHOW TABLES;

-- 6. 查看建表语句
SHOW CREATE TABLE products;

-- 7. 切换回myapp数据库
USE myapp;

-- 8. 确认当前数据库
SELECT DATABASE();

1
2
3
4
5

-- 删除数据库（危险操作！会删除所有表和数据！）
DROP DATABASE myapp;

-- 安全删除（只删除已存在的数据库）
DROP DATABASE IF EXISTS myapp;

1
2
3
4
5

-- 修改数据库的字符集
ALTER DATABASE shop CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 注意：MySQL不允许直接修改数据库名
-- 如果要改名，需要导出数据，重建数据库，再导入

1
2
3

CREATE DATABASE IF NOT EXISTS 数据库名 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

1
2
3
4
5
6

CREATE TABLE 表名 (
    列名1 数据类型 [约束],
    列名2 数据类型 [约束],
    ...
    [表级约束]
);

 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

USE myapp;

CREATE TABLE users (
    -- 主键：唯一标识每行，自动增长
    id INT AUTO_INCREMENT PRIMARY KEY,
    
    -- 用户名：可变字符串，最多50字符，非空
    username VARCHAR(50) NOT NULL,
    
    -- 邮箱：可变字符串，最多100字符，唯一
    email VARCHAR(100) NOT NULL UNIQUE,
    
    -- 密码：哈希存储，固定长度64字符（SHA256）
    password_hash CHAR(64) NOT NULL,
    
    -- 年龄：整数，可空
    age INT,
    
    -- 性别：枚举类型，只能是'M'或'F'
    gender ENUM('M', 'F') DEFAULT 'M',
    
    -- 个人简介：长文本，可空
    bio TEXT,
    
    -- 创建时间：自动记录当前时间
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    -- 更新时间：自动更新
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    
    -- 商品名称，必填
    name VARCHAR(200) NOT NULL,
    
    -- 价格，最多10位数字，其中2位小数（精准计算金钱）
    price DECIMAL(10, 2) NOT NULL,
    
    -- 库存数量，整数
    stock INT DEFAULT 0,
    
    -- 分类ID，关联分类表
    category_id INT,
    
    -- 商品描述，长文本
    description TEXT,
    
    -- 是否上架，布尔值
    is_active BOOLEAN DEFAULT TRUE,
    
    -- 创建时间
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

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

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    
    -- 订单号，唯一字符串
    order_no VARCHAR(32) NOT NULL UNIQUE,
    
    -- 用户ID，外键关联users表
    user_id INT NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id),
    
    -- 订单总金额
    total_amount DECIMAL(10, 2) NOT NULL,
    
    -- 订单状态
    status ENUM('pending', 'paid', 'shipped', 'delivered', 'cancelled') DEFAULT 'pending',
    
    -- 创建时间
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

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

CREATE TABLE articles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    
    title VARCHAR(200) NOT NULL,
    
    content TEXT,
    
    author_id INT,
    
    -- 手动添加索引
    INDEX idx_author (author_id),
    
    -- 复合索引（多列组合索引）
    INDEX idx_title_content (title(50), content(100)),
    
    -- 全文索引（用于文章搜索）
    FULLTEXT INDEX ft_content (content),
    
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

1

ALTER TABLE 表名 操作;

1
2
3

-- 给users表添加手机号字段
ALTER TABLE users 
ADD COLUMN phone VARCHAR(20) AFTER email;

1
2
3

-- 删除users表的bio字段
ALTER TABLE users 
DROP COLUMN bio;

1
2
3

-- 把users表的username长度改大
ALTER TABLE users 
MODIFY COLUMN username VARCHAR(100) NOT NULL;

1
2
3

-- 把phone改名为mobile
ALTER TABLE users 
CHANGE COLUMN phone mobile VARCHAR(20);

1
2
3

-- 给email字段添加唯一索引
ALTER TABLE users 
ADD UNIQUE INDEX idx_email (email);

1
2
3

-- 把users表改名为app_users
ALTER TABLE users 
RENAME TO app_users;

1

DROP TABLE 表名;

1
2
3
4
5
6
7
8

-- 删除一个表（危险！会删除数据和结构！）
DROP TABLE orders;

-- 安全删除（只删除已存在的表）
DROP TABLE IF EXISTS orders;

-- 一次性删除多个表
DROP TABLE IF EXISTS order_items, order_logs, order_history;

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

-- 查看当前数据库所有表
SHOW TABLES;

-- 查看表结构（desc = describe）
DESC users;
-- 或
DESCRIBE users;

-- 查看建表语句
SHOW CREATE TABLE users;

-- 查看表的所有列
SHOW COLUMNS FROM users;

-- 查看表的状态
SHOW TABLE STATUS LIKE 'users';

1
2
3
4
5
6

CREATE TABLE 表名 (
    id INT AUTO_INCREMENT PRIMARY KEY,
    字段1 类型 NOT NULL,
    字段2 类型 DEFAULT 默认值,
    ...
);

1

SELECT 列名 FROM 表名 WHERE 条件 ORDER BY 列名 LIMIT n;

1
2
3
4
5

-- 查询users表所有记录的所有列
SELECT * FROM users;

-- 查询users表所有记录的指定列
SELECT id, username, email FROM users;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

-- 查询年龄大于18的用户
SELECT * FROM users WHERE age > 18;

-- 查询状态为'active'的用户
SELECT * FROM users WHERE status = 'active';

-- 组合条件：年龄大于18且状态为active
SELECT * FROM users WHERE age > 18 AND status = 'active';

-- 或条件：年龄小于20或大于50
SELECT * FROM users WHERE age < 20 OR age > 50;

1
2
3
4
5
6
7
8

-- 查询用户名以'张'开头的用户
SELECT * FROM users WHERE username LIKE '张%';

-- 查询邮箱包含'gmail'的用户
SELECT * FROM users WHERE email LIKE '%gmail%';

-- 下划线_匹配单个字符
SELECT * FROM users WHERE username LIKE '张_';

1
2
3
4
5
6
7
8

-- 按年龄升序排列（从小到大）
SELECT * FROM users ORDER BY age ASC;

-- 按年龄降序排列（从大到小）
SELECT * FROM users ORDER BY age DESC;

-- 先按年龄降序，年龄相同则按用户名升序
SELECT * FROM users ORDER BY age DESC, username ASC;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

-- 查询第1-10条记录
SELECT * FROM users LIMIT 10;

-- 查询第11-20条记录（OFFSET跳过前10条）
SELECT * FROM users LIMIT 10 OFFSET 10;

-- 简写形式：查询第11-20条
SELECT * FROM users LIMIT 10, 10;

-- 查询年龄最大的前5个用户
SELECT * FROM users ORDER BY age DESC LIMIT 5;

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

-- 统计用户总数
SELECT COUNT(*) FROM users;

-- 统计年龄大于18的用户数
SELECT COUNT(*) FROM users WHERE age > 18;

-- 计算平均年龄
SELECT AVG(age) FROM users;

-- 计算最大年龄和最小年龄
SELECT MAX(age), MIN(age) FROM users;

-- 按性别分组统计
SELECT gender, COUNT(*) as cnt FROM users GROUP BY gender;

-- 分组后筛选（只显示数量大于10的组）
SELECT gender, COUNT(*) as cnt 
FROM users 
GROUP BY gender 
HAVING cnt > 10;

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

-- 内连接：查询订单和对应的用户信息
SELECT 
    orders.id,
    orders.order_no,
    orders.total_amount,
    users.username,
    users.email
FROM orders
INNER JOIN users ON orders.user_id = users.id;

-- 左连接：查询所有用户，包括没有订单的用户
SELECT 
    users.username,
    orders.order_no,
    orders.total_amount
FROM users
LEFT JOIN orders ON users.id = orders.user_id;

1

INSERT INTO 表名 (列1, 列2, ...) VALUES (值1, 值2, ...);

1
2
3
4
5
6
7
8
9

-- 指定列插入
INSERT INTO users (username, email, password_hash, age) 
VALUES ('xiaoming', 'xiaoming@example.com', 'hash123', 25);

-- 所有列插入（省略列名，但顺序必须与表结构完全一致）
INSERT INTO users 
VALUES (NULL, 'xiaoming', 'xiaoming@example.com', 'hash123', 25, 'M', NULL, NOW(), NOW());

-- 注意：VALUES顺序必须匹配：id, username, email, password_hash, age, gender, bio, created_at, updated_at

1
2
3
4
5
6

-- 一次插入多条数据
INSERT INTO users (username, email, password_hash, age) VALUES 
    ('user1', 'user1@example.com', 'hash1', 20),
    ('user2', 'user2@example.com', 'hash2', 22),
    ('user3', 'user3@example.com', 'hash3', 24),
    ('user4', 'user4@example.com', 'hash4', 26);

1
2
3

-- 从旧表复制数据到新表
INSERT INTO users_backup (username, email, age)
SELECT username, email, age FROM users WHERE created_at > '2024-01-01';

1

UPDATE 表名 SET 列1 = 值1, 列2 = 值2 WHERE 条件;

1
2
3
4
5

-- 更新用户ID=1的年龄
UPDATE users SET age = 26 WHERE id = 1;

-- 同时更新多个字段
UPDATE users SET age = 26, status = 'active' WHERE id = 1;

1
2
3
4
5
6
7
8

-- 把所有年龄小于18的用户状态改为'inactive'
UPDATE users SET status = 'inactive' WHERE age < 18;

-- 把所有用户的年龄加1
UPDATE users SET age = age + 1;

-- 把所有没有邮箱的用户设置默认邮箱
UPDATE users SET email = 'no-email@example.com' WHERE email IS NULL;

1
2
3
4
5
6

-- ❌ 错误示范：忘了WHERE，更新了所有用户的密码
UPDATE users SET password_hash = 'hacked123';

-- ✅ 正确做法：先SELECT确认，再UPDATE
SELECT * FROM users WHERE id = 1;  -- 先确认
UPDATE users SET password_hash = 'new_hash' WHERE id = 1;  -- 再更新

1

DELETE FROM 表名 WHERE 条件;

1
2

-- 删除用户ID=1的记录
DELETE FROM users WHERE id = 1;

1
2
3
4
5

-- 删除所有年龄大于100的用户（数据有问题）
DELETE FROM users WHERE age > 100;

-- 删除所有状态为'deleted'的用户
DELETE FROM users WHERE status = 'deleted';

1
2
3
4
5

-- ❌ 错误示范：忘了WHERE，删了整张表
DELETE FROM users;

-- ✅ 正确做法：删除前先备份或确认
DELETE FROM users WHERE id IN (1, 2, 3);

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

-- ==================== C - Create 创建 ====================

-- 创建用户
INSERT INTO users (username, email, password_hash, age) 
VALUES ('xiaoming', 'xiaoming@example.com', SHA2('password123', 256), 25);

-- ==================== R - Read 读取 ====================

-- 查询所有用户
SELECT * FROM users;

-- 查询年龄大于18的用户
SELECT username, email FROM users WHERE age > 18 ORDER BY age DESC;

-- ==================== U - Update 更新 ====================

-- 更新用户信息
UPDATE users SET age = 26, status = 'active' WHERE username = 'xiaoming';

-- ==================== D - Delete 删除 ====================

-- 删除用户
DELETE FROM users WHERE username = 'xiaoming';

1
2
3
4
5
6
7
8

-- 创建普通索引
CREATE INDEX 索引名 ON 表名 (列名);

-- 创建唯一索引（索引值必须唯一）
CREATE UNIQUE INDEX 索引名 ON 表名 (列名);

-- 创建复合索引（多列组合）
CREATE INDEX 索引名 ON 表名 (列1, 列2, 列3);

1
2
3
4
5
6
7
8

-- 给users表的email字段加索引（登录时经常用邮箱查询）
CREATE INDEX idx_email ON users(email);

-- 给orders表的user_id加索引（关联查询）
CREATE INDEX idx_user_id ON orders(user_id);

-- 给orders表的created_at加索引（按时间查询）
CREATE INDEX idx_created_at ON orders(created_at);

1
2
3
4
5

-- 创建一个复合索引，用于查询"某用户在某时间段的订单"
CREATE INDEX idx_user_date ON orders(user_id, created_at);

-- 这样的查询可以利用索引：
SELECT * FROM orders WHERE user_id = 1 AND created_at BETWEEN '2024-01-01' AND '2024-12-31';

1
2
3
4
5

-- 给email加唯一索引，防止重复注册
CREATE UNIQUE INDEX idx_unique_email ON users(email);

-- 给订单号加唯一索引，防止重复订单
CREATE UNIQUE INDEX idx_unique_order_no ON orders(order_no);

1
2
3
4
5

-- 给文章标题和内容加全文索引（用于搜索引擎）
CREATE FULLTEXT INDEX ft_article ON articles(title, content);

-- 使用全文索引查询
SELECT * FROM articles WHERE MATCH(title, content) AGAINST('关键词');

1
2
3
4
5
6
7
8
9

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    price DECIMAL(10, 2),
    
    -- 创建时直接加索引
    INDEX idx_name (name),
    UNIQUE INDEX idx_unique_name (name)
);

1
2
3
4
5
6
7
8

-- 查看表的所有索引
SHOW INDEX FROM users;

-- 删除索引
DROP INDEX idx_email ON users;

-- 或用ALTER TABLE删除
ALTER TABLE users DROP INDEX idx_email;

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

-- ✅ 好的写法：使用索引
SELECT * FROM users WHERE email = 'test@example.com';

-- ❌ 差的写法：索引失效
SELECT * FROM users WHERE email LIKE '%test%';  -- 前缀模糊查询无法使用索引

-- ✅ 好的写法：前缀模糊查询可以（需要建立前缀索引）
SELECT * FROM users WHERE email LIKE 'test%';

-- ❌ 差的写法：在索引列上使用函数，索引失效
SELECT * FROM users WHERE YEAR(created_at) = 2024;

-- ✅ 好的写法：避免在索引列上使用函数
SELECT * FROM users WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';

1

mysqldump -u 用户名 -p 数据库名 > 备份文件.sql

1
2
3
4
5

# 备份myapp数据库到文件
mysqldump -u root -p myapp > /backup/myapp_backup_20240101.sql

# 备份时输入密码（不推荐，密码会显示在history）
mysqldump -u root -pMyPassword myapp > /backup/myapp_backup.sql

1
2

# 备份多个数据库
mysqldump -u root -p --databases myapp shop blog > /backup/multi_db_backup.sql

1
2

# 备份所有数据库（包括系统数据库）
mysqldump -u root -p --all-databases > /backup/all_databases_backup.sql

1
2

# 只备份建表语句，用于快速部署新环境
mysqldump -u root -p --no-data myapp > /backup/myapp_structure.sql

1
2

# 只备份数据，用于数据迁移
mysqldump -u root -p --no-create-info myapp > /backup/myapp_data.sql

1
2
3
4
5

# 备份并压缩
mysqldump -u root -p myapp | gzip > /backup/myapp_backup.sql.gz

# 解压恢复
gunzip < /backup/myapp_backup.sql.gz | mysql -u root -p

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

#!/bin/bash
# backup.sh - MySQL自动备份脚本

# 配置
DB_USER="root"
DB_PASS="YourPassword"
DB_NAME="myapp"
BACKUP_DIR="/backup/mysql"
DATE=$(date +%Y%m%d_%H%M%S)

# 创建备份目录（如果不存在）
mkdir -p $BACKUP_DIR

# 执行备份
mysqldump -u $DB_USER -p$DB_PASS $DB_NAME | gzip > $BACKUP_DIR/${DB_NAME}_${DATE}.sql.gz

# 删除7天前的备份
find $BACKUP_DIR -name "*.sql.gz" -mtime +7 -delete

# 输出备份信息
echo "备份完成: ${DB_NAME}_${DATE}.sql.gz"

1
2
3
4
5
6
7

# 给脚本添加执行权限
chmod +x /backup/backup.sh

# 将脚本加入crontab，每天凌晨3点执行
crontab -e
# 添加行：
# 0 3 * * * /backup/backup.sh

1
2

# 查看备份文件内容（前100行）
head -100 /backup/myapp_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

-- MySQL dump 10.13  Distrib 8.0.35, for Linux (x86_64)
--
-- Host: localhost    Database: myapp
-- ------------------------------------------------------
-- Server version	8.0.35

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!50503 SET NAMES utf8mb4 */;

--
-- Table structure for table `users`
--

DROP TABLE IF EXISTS `users`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `users` (
  `id` int NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL,
  `email` varchar(100) NOT NULL,
  `password_hash` char(64) NOT NULL,
  `age` int DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_unique_email` (`email`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `users`
--

INSERT INTO `users` VALUES (1,'xiaoming','xiaoming@example.com','hash123',25,'2024-01-01 00:00:00');

1

mysql -u 用户名 -p 数据库名 < 备份文件.sql

1
2
3
4

# 恢复myapp数据库
mysql -u root -p myapp < /backup/myapp_backup_20240101.sql

# 输入密码后，数据就回来了

1
2

# 如果备份的是所有数据库
mysql -u root -p < /backup/all_databases_backup.sql

1
2
3
4
5
6

# 解压并恢复
gunzip < /backup/myapp_backup.sql.gz | mysql -u root -p myapp

# 或者分两步
gunzip -c /backup/myapp_backup.sql.gz > /tmp/myapp_backup.sql
mysql -u root -p myapp < /tmp/myapp_backup.sql

1
2

# 确保恢复时使用正确的字符集
mysql -u root -p --default-character-set=utf8mb4 myapp < /backup/myapp_backup.sql

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11