MySQL 学习指南

# 学习目标

本指南旨在为 MySQL 初学者提供系统性的学习路径，从基础概念到高级应用，逐步掌握 MySQL 数据库管理和 SQL 编程技能。

# 1. MySQL 基础入门

# 1.1 什么是 MySQL？

MySQL 是一种开源的关系型数据库管理系统（RDBMS），它支持 SQL 语言和 ACID 事务，广泛应用于 Web 应用程序、企业应用程序、分布式应用程序等领域。

MySQL 的优点：

数据安全：支持 ACID 事务，保证数据的一致性和安全性
大规模数据存储：可以存储大量结构化数据，适应企业应用需求
可扩展性：支持水平扩展和垂直扩展，灵活调整数据库性能
开源免费：社区版免费，拥有庞大的社区支持
跨平台：支持 Windows、Linux、macOS 等多种操作系统

# 1.2 环境搭建

# macOS 安装

# 使用 Homebrew 安装
brew install mysql

# 启动 MySQL 服务
brew services start mysql

# 停止 MySQL 服务
brew services stop mysql

# 重启 MySQL 服务
brew services restart mysql

1
2
3
4
5
6
7
8
9
10
11

# Linux 安装

# Ubuntu/Debian
sudo apt update
sudo apt install mysql-server

# CentOS/RHEL
sudo yum install mysql-server

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

1
2
3
4
5
6
7
8
9
10

# Windows 安装

访问 MySQL 官网 (opens new window)
下载 MySQL Installer
运行安装程序，选择 Developer Default
完成安装向导

# 客户端工具

MySQL Workbench：官方图形化管理工具
TablePlus：现代化的数据库管理工具
DBeaver：开源的通用数据库工具
phpMyAdmin：基于 Web 的管理工具

# 1.3 连接 MySQL

# 连接到本地 MySQL
mysql -u root -p

# 连接到远程 MySQL
mysql -h hostname -u username -p

# 指定数据库连接
mysql -u root -p database_name

# 指定端口连接
mysql -h hostname -P 3306 -u username -p

1
2
3
4
5
6
7
8
9
10
11

# 1.4 基本概念

# 数据库（Database）

数据库是存储数据的容器，一个 MySQL 服务器可以包含多个数据库。

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

-- 创建数据库
CREATE DATABASE mydb;

-- 创建数据库（指定字符集）
CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 选择数据库
USE mydb;

-- 删除数据库
DROP DATABASE mydb;

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

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

# 数据表（Table）

数据表是数据库中存储数据的基本单位，由行和列组成。

-- 查看所有表
SHOW TABLES;

-- 查看表结构
DESC users;
DESCRIBE users;
SHOW COLUMNS FROM users;

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

1
2
3
4
5
6
7
8
9
10

# 字段（Column）

字段是表中的列，定义了数据的类型和属性。

# 记录（Row）

记录是表中的行，代表一条完整的数据。

# 2. SQL 基础语法

# 2.1 数据类型

# 数值类型

-- 整数类型
TINYINT     -- 1字节，范围：-128 到 127
SMALLINT    -- 2字节，范围：-32768 到 32767
MEDIUMINT   -- 3字节
INT         -- 4字节，范围：-2147483648 到 2147483647
BIGINT      -- 8字节

-- 浮点数类型
FLOAT       -- 4字节，单精度浮点数
DOUBLE      -- 8字节，双精度浮点数
DECIMAL(M,D) -- 定点数，M是总位数，D是小数位数

-- 示例
CREATE TABLE products (
    id INT,
    price DECIMAL(10, 2),  -- 最多10位数字，2位小数
    quantity INT UNSIGNED,  -- 无符号整数
    rating FLOAT
);

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

# 字符串类型

-- 字符串类型
CHAR(n)      -- 定长字符串，最多255字符
VARCHAR(n)   -- 变长字符串，最多65535字符
TEXT         -- 长文本，最多65535字符
MEDIUMTEXT   -- 中等长度文本，最多16MB
LONGTEXT     -- 超长文本，最多4GB

-- 二进制类型
BLOB         -- 二进制大对象
MEDIUMBLOB   -- 中等二进制对象
LONGBLOB     -- 超长二进制对象

-- 示例
CREATE TABLE articles (
    id INT,
    title VARCHAR(200),
    content TEXT,
    author CHAR(50)
);

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

# 日期时间类型

-- 日期时间类型
DATE         -- 日期，格式：YYYY-MM-DD
TIME         -- 时间，格式：HH:MM:SS
DATETIME     -- 日期时间，格式：YYYY-MM-DD HH:MM:SS
TIMESTAMP    -- 时间戳，范围：1970-2038
YEAR         -- 年份

-- 示例
CREATE TABLE events (
    id INT,
    event_date DATE,
    event_time TIME,
    created_at DATETIME 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

# 2.2 创建表（CREATE TABLE）

-- 基本语法
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL,
    password VARCHAR(255) NOT NULL,
    age INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 完整示例
CREATE TABLE IF NOT EXISTS employees (
    emp_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '员工ID',
    emp_name VARCHAR(50) NOT NULL COMMENT '员工姓名',
    emp_email VARCHAR(100) NOT NULL UNIQUE COMMENT '邮箱',
    emp_phone CHAR(11) COMMENT '电话',
    dept_id INT COMMENT '部门ID',
    salary DECIMAL(10, 2) DEFAULT 0.00 COMMENT '薪资',
    hire_date DATE NOT NULL COMMENT '入职日期',
    status ENUM('active', 'inactive', 'suspended') DEFAULT 'active' COMMENT '状态',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    INDEX idx_dept_id (dept_id),
    INDEX idx_hire_date (hire_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='员工表';

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

# 2.3 插入数据（INSERT）

-- 插入单条记录
INSERT INTO users (username, email, password) 
VALUES ('alice', '[email protected]', 'password123');

-- 插入多条记录
INSERT INTO users (username, email, password) VALUES
    ('bob', '[email protected]', 'pass456'),
    ('charlie', '[email protected]', 'pass789'),
    ('david', '[email protected]', 'pass000');

-- 插入部分字段（其他字段使用默认值）
INSERT INTO users (username, email) 
VALUES ('eve', '[email protected]');

-- 从另一个表插入数据
INSERT INTO users_backup 
SELECT * FROM users WHERE created_at < '2023-01-01';

-- 插入或更新（如果主键冲突则更新）
INSERT INTO users (id, username, email) 
VALUES (1, 'alice', '[email protected]')
ON DUPLICATE KEY UPDATE email = VALUES(email);

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

# 2.4 查询数据（SELECT）

-- 基本查询
SELECT * FROM users;

-- 查询指定列
SELECT username, email FROM users;

-- 使用别名
SELECT username AS name, email AS mail FROM users;

-- 去重查询
SELECT DISTINCT age FROM users;

-- 条件查询
SELECT * FROM users WHERE age > 18;
SELECT * FROM users WHERE age >= 18 AND age <= 30;
SELECT * FROM users WHERE username IN ('alice', 'bob', 'charlie');
SELECT * FROM users WHERE email LIKE '%@gmail.com';
SELECT * FROM users WHERE age BETWEEN 20 AND 30;
SELECT * FROM users WHERE created_at IS NOT NULL;

-- 排序
SELECT * FROM users ORDER BY age ASC;   -- 升序
SELECT * FROM users ORDER BY age DESC;  -- 降序
SELECT * FROM users ORDER BY age DESC, username ASC;  -- 多字段排序

-- 限制结果数量
SELECT * FROM users LIMIT 10;           -- 前10条
SELECT * FROM users LIMIT 10, 20;       -- 跳过10条，取20条
SELECT * FROM users LIMIT 20 OFFSET 10; -- 同上

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

# 2.5 更新数据（UPDATE）

-- 更新单个字段
UPDATE users SET email = '[email protected]' WHERE id = 1;

-- 更新多个字段
UPDATE users 
SET email = '[email protected]', 
    age = 26 
WHERE username = 'alice';

-- 批量更新
UPDATE users SET status = 'inactive' WHERE age < 18;

-- 使用表达式更新
UPDATE products SET price = price * 1.1;  -- 价格上涨10%

-- 更新并限制影响行数
UPDATE users SET status = 'active' WHERE age > 18 LIMIT 100;

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

# 2.6 删除数据（DELETE）

-- 删除指定记录
DELETE FROM users WHERE id = 1;

-- 批量删除
DELETE FROM users WHERE age < 18;

-- 删除所有记录（保留表结构）
DELETE FROM users;

-- 清空表（更快，重置自增ID）
TRUNCATE TABLE users;

-- 删除并限制影响行数
DELETE FROM users WHERE status = 'inactive' LIMIT 100;

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

# 3. 高级查询

# 3.1 聚合函数

-- COUNT：计数
SELECT COUNT(*) FROM users;
SELECT COUNT(DISTINCT age) FROM users;

-- SUM：求和
SELECT SUM(salary) FROM employees;

-- AVG：平均值
SELECT AVG(age) FROM users;

-- MAX/MIN：最大值/最小值
SELECT MAX(salary), MIN(salary) FROM employees;

-- 组合使用
SELECT 
    COUNT(*) AS total_users,
    AVG(age) AS avg_age,
    MAX(age) AS max_age,
    MIN(age) AS min_age
FROM users;

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

# 3.2 分组查询（GROUP BY）

-- 基本分组
SELECT age, COUNT(*) AS count 
FROM users 
GROUP BY age;

-- 多字段分组
SELECT dept_id, status, COUNT(*) AS count
FROM employees
GROUP BY dept_id, status;

-- 分组后筛选（HAVING）
SELECT age, COUNT(*) AS count
FROM users
GROUP BY age
HAVING COUNT(*) > 5;

-- 分组后排序
SELECT dept_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY dept_id
ORDER BY avg_salary DESC;

-- WITH ROLLUP：添加汇总行
SELECT dept_id, SUM(salary) AS total_salary
FROM employees
GROUP BY dept_id WITH ROLLUP;

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

# 3.3 连接查询（JOIN）

-- 内连接（INNER JOIN）
SELECT u.username, o.order_id, o.amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- 左连接（LEFT JOIN）
SELECT u.username, o.order_id
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

-- 右连接（RIGHT JOIN）
SELECT u.username, o.order_id
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

-- 全外连接（FULL OUTER JOIN）- MySQL不直接支持，需要用UNION
SELECT u.username, o.order_id
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
UNION
SELECT u.username, o.order_id
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

-- 自连接
SELECT e1.emp_name AS employee, e2.emp_name AS manager
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.emp_id;

-- 多表连接
SELECT u.username, o.order_id, p.product_name, oi.quantity
FROM users u
INNER JOIN orders o ON u.id = o.user_id
INNER JOIN order_items oi ON o.order_id = oi.order_id
INNER JOIN products p ON oi.product_id = p.product_id;

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

# 3.4 子查询

-- WHERE子句中的子查询
SELECT * FROM users 
WHERE age > (SELECT AVG(age) FROM users);

-- IN子查询
SELECT * FROM users 
WHERE id IN (SELECT user_id FROM orders WHERE amount > 1000);

-- EXISTS子查询
SELECT * FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

-- FROM子句中的子查询
SELECT dept_name, avg_salary
FROM (
    SELECT dept_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY dept_id
) AS dept_stats
JOIN departments d ON dept_stats.dept_id = d.dept_id;

-- SELECT子句中的子查询
SELECT 
    username,
    (SELECT COUNT(*) FROM orders WHERE user_id = users.id) AS order_count
FROM users;

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

# 3.5 联合查询（UNION）

-- UNION：合并结果集并去重
SELECT username FROM users WHERE age < 20
UNION
SELECT username FROM users WHERE age > 60;

-- UNION ALL：合并结果集不去重
SELECT username FROM users WHERE age < 20
UNION ALL
SELECT username FROM users WHERE age > 60;

-- 多个UNION
SELECT 'young' AS category, COUNT(*) AS count FROM users WHERE age < 20
UNION
SELECT 'middle' AS category, COUNT(*) AS count FROM users WHERE age BETWEEN 20 AND 40
UNION
SELECT 'old' AS category, COUNT(*) AS count FROM users WHERE age > 40;

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

# 4. 表约束和索引

# 4.1 主键约束（PRIMARY KEY）

-- 单字段主键
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50)
);

-- 联合主键
CREATE TABLE user_roles (
    user_id INT,
    role_id INT,
    PRIMARY KEY (user_id, role_id)
);

-- 添加主键约束
ALTER TABLE users ADD PRIMARY KEY (id);

-- 删除主键
ALTER TABLE users DROP PRIMARY KEY;

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

# 4.2 唯一约束（UNIQUE）

-- 建表时创建唯一约束
CREATE TABLE users (
    id INT PRIMARY KEY,
    email VARCHAR(100) UNIQUE,
    username VARCHAR(50),
    UNIQUE KEY uk_username (username)
);

-- 添加唯一约束
ALTER TABLE users ADD UNIQUE (email);
ALTER TABLE users ADD CONSTRAINT uk_username UNIQUE (username);

-- 删除唯一约束
ALTER TABLE users DROP INDEX email;

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

# 4.3 非空约束（NOT NULL）

-- 建表时添加非空约束
CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL
);

-- 添加非空约束
ALTER TABLE users MODIFY username VARCHAR(50) NOT NULL;

-- 移除非空约束
ALTER TABLE users MODIFY username VARCHAR(50);

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

# 4.4 默认值约束（DEFAULT）

-- 建表时添加默认值
CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(50),
    status VARCHAR(20) DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 添加默认值
ALTER TABLE users ALTER COLUMN status SET DEFAULT 'active';

-- 删除默认值
ALTER TABLE users ALTER COLUMN status DROP DEFAULT;

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

# 4.5 外键约束（FOREIGN KEY）

-- 建表时创建外键
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    user_id INT,
    amount DECIMAL(10, 2),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- 带级联操作的外键
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    user_id INT,
    amount DECIMAL(10, 2),
    FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE CASCADE      -- 删除用户时同时删除订单
        ON UPDATE CASCADE      -- 更新用户ID时同时更新订单
);

-- 添加外键约束
ALTER TABLE orders 
ADD CONSTRAINT fk_user_id 
FOREIGN KEY (user_id) REFERENCES users(id);

-- 删除外键约束
ALTER TABLE orders DROP FOREIGN KEY fk_user_id;

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

# 4.6 索引（INDEX）

-- 创建普通索引
CREATE INDEX idx_username ON users(username);

-- 创建唯一索引
CREATE UNIQUE INDEX idx_email ON users(email);

-- 创建复合索引
CREATE INDEX idx_name_age ON users(username, age);

-- 创建全文索引
CREATE FULLTEXT INDEX idx_content ON articles(content);

-- 查看索引
SHOW INDEX FROM users;

-- 删除索引
DROP INDEX idx_username ON users;
ALTER TABLE users DROP INDEX idx_username;

-- 使用索引优化查询
EXPLAIN SELECT * FROM users WHERE username = 'alice';

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

# 5. 数据库设计

# 5.1 数据库设计三大范式

# 第一范式（1NF）

目标：确保数据表中的每一列都是不可再分的数据单元，所有列都应该是原子的。

-- 不符合1NF（地址字段包含多个信息）
CREATE TABLE employees_bad (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    address VARCHAR(200)  -- "北京市朝阳区xxx街道xxx号"
);

-- 符合1NF（地址拆分为原子字段）
CREATE TABLE employees_good (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    province VARCHAR(50),
    city VARCHAR(50),
    district VARCHAR(50),
    street VARCHAR(100)
);

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

# 第二范式（2NF）

目标：在满足第一范式的基础上，确保非主键字段完全依赖于主键。

-- 不符合2NF（产品名称只依赖于product_id，不依赖于完整主键）
CREATE TABLE order_items_bad (
    order_id INT,
    product_id INT,
    product_name VARCHAR(100),  -- 部分依赖
    quantity INT,
    PRIMARY KEY (order_id, product_id)
);

-- 符合2NF（拆分表）
CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    quantity INT,
    PRIMARY KEY (order_id, product_id)
);

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100)
);

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

# 第三范式（3NF）

目标：在满足第二范式的基础上，确保非主键字段之间没有传递依赖关系。

-- 不符合3NF（dept_name依赖于dept_id，dept_id依赖于emp_id）
CREATE TABLE employees_bad (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(50),
    dept_id INT,
    dept_name VARCHAR(50)  -- 传递依赖
);

-- 符合3NF（拆分表）
CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(50),
    dept_id INT
);

CREATE TABLE departments (
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR(50)
);

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

# 5.2 数据库设计实战

# 电商系统数据库设计

-- 用户表
CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    phone CHAR(11),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_email (email),
    INDEX idx_phone (phone)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 商品分类表
CREATE TABLE categories (
    category_id INT PRIMARY KEY AUTO_INCREMENT,
    category_name VARCHAR(50) NOT NULL,
    parent_id INT DEFAULT 0,
    sort_order INT DEFAULT 0,
    INDEX idx_parent_id (parent_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 商品表
CREATE TABLE products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(200) NOT NULL,
    category_id INT NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    stock INT DEFAULT 0,
    description TEXT,
    image_url VARCHAR(255),
    status ENUM('active', 'inactive') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES categories(category_id),
    INDEX idx_category_id (category_id),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 订单表
CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    total_amount DECIMAL(10, 2) NOT NULL,
    status ENUM('pending', 'paid', 'shipped', 'completed', 'cancelled') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(user_id),
    INDEX idx_user_id (user_id),
    INDEX idx_status (status),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 订单详情表
CREATE TABLE order_items (
    item_id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(product_id),
    INDEX idx_order_id (order_id),
    INDEX idx_product_id (product_id)
) ENGINE=InnoDB DEFAULT CHARSET=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
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64

# 6. 函数和运算符

# 6.1 字符串函数

-- CONCAT：连接字符串
SELECT CONCAT('Hello', ' ', 'World');  -- Hello World
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;

-- LENGTH：字符串长度
SELECT LENGTH('Hello');  -- 5
SELECT CHAR_LENGTH('你好');  -- 2（字符数）

-- SUBSTRING：截取字符串
SELECT SUBSTRING('Hello World', 1, 5);  -- Hello
SELECT SUBSTRING('Hello World', 7);     -- World

-- UPPER/LOWER：大小写转换
SELECT UPPER('hello');  -- HELLO
SELECT LOWER('WORLD');  -- world

-- TRIM：去除空格
SELECT TRIM('  hello  ');        -- hello
SELECT LTRIM('  hello  ');       -- 'hello  '
SELECT RTRIM('  hello  ');       -- '  hello'

-- REPLACE：替换字符串
SELECT REPLACE('Hello World', 'World', 'MySQL');  -- Hello MySQL

-- LIKE：模糊匹配
SELECT * FROM users WHERE email LIKE '%@gmail.com';
SELECT * FROM users WHERE username LIKE 'a%';  -- 以a开头
SELECT * FROM users WHERE username LIKE '%a%'; -- 包含a

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

# 6.2 数值函数

-- ABS：绝对值
SELECT ABS(-10);  -- 10

-- ROUND：四舍五入
SELECT ROUND(3.14159, 2);  -- 3.14

-- CEIL/FLOOR：向上/向下取整
SELECT CEIL(3.14);   -- 4
SELECT FLOOR(3.14);  -- 3

-- MOD：取余
SELECT MOD(10, 3);  -- 1

-- POWER：幂运算
SELECT POWER(2, 3);  -- 8

-- SQRT：平方根
SELECT SQRT(16);  -- 4

-- RAND：随机数
SELECT RAND();  -- 0到1之间的随机数
SELECT FLOOR(RAND() * 100);  -- 0到99的随机整数

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

# 6.3 日期时间函数

-- NOW：当前日期时间
SELECT NOW();  -- 2024-01-15 10:30:45

-- CURDATE：当前日期
SELECT CURDATE();  -- 2024-01-15

-- CURTIME：当前时间
SELECT CURTIME();  -- 10:30:45

-- DATE_FORMAT：格式化日期
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d');  -- 2024-01-15
SELECT DATE_FORMAT(NOW(), '%Y年%m月%d日');

-- DATE_ADD/DATE_SUB：日期加减
SELECT DATE_ADD(NOW(), INTERVAL 7 DAY);   -- 7天后
SELECT DATE_SUB(NOW(), INTERVAL 1 MONTH); -- 1个月前

-- DATEDIFF：日期差
SELECT DATEDIFF('2024-01-15', '2024-01-01');  -- 14

-- YEAR/MONTH/DAY：提取日期部分
SELECT YEAR(NOW());   -- 2024
SELECT MONTH(NOW());  -- 1
SELECT DAY(NOW());    -- 15

-- TIMESTAMPDIFF：时间戳差
SELECT TIMESTAMPDIFF(DAY, '2024-01-01', '2024-01-15');  -- 14

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

# 6.4 聚合函数

-- COUNT：计数
SELECT COUNT(*) FROM users;
SELECT COUNT(DISTINCT age) FROM users;

-- SUM：求和
SELECT SUM(amount) FROM orders;

-- AVG：平均值
SELECT AVG(price) FROM products;

-- MAX/MIN：最大值/最小值
SELECT MAX(salary), MIN(salary) FROM employees;

-- GROUP_CONCAT：分组连接
SELECT dept_id, GROUP_CONCAT(emp_name) AS employees
FROM employees
GROUP BY dept_id;

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

# 6.5 条件函数

-- IF：条件判断
SELECT IF(age >= 18, '成年', '未成年') AS status FROM users;

-- CASE：多条件判断
SELECT 
    username,
    CASE 
        WHEN age < 18 THEN '未成年'
        WHEN age BETWEEN 18 AND 60 THEN '成年'
        ELSE '老年'
    END AS age_group
FROM users;

-- IFNULL：空值处理
SELECT IFNULL(phone, '未提供') AS phone FROM users;

-- COALESCE：返回第一个非空值
SELECT COALESCE(phone, email, '无联系方式') AS contact FROM users;

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

# 7. 事务处理

# 7.1 事务基础

事务是一组SQL语句的集合，要么全部执行成功，要么全部失败回滚。

ACID特性：

原子性（Atomicity）：事务是不可分割的最小单位
一致性（Consistency）：事务执行前后数据保持一致
隔离性（Isolation）：多个事务并发执行时相互隔离
持久性（Durability）：事务提交后永久保存

-- 开启事务
START TRANSACTION;
-- 或
BEGIN;

-- 执行SQL语句
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;

-- 提交事务
COMMIT;

-- 回滚事务
ROLLBACK;

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

# 7.2 事务隔离级别

-- 查看当前隔离级别
SELECT @@transaction_isolation;

-- 设置隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;  -- MySQL默认
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

1
2
3
4
5
6
7
8

隔离级别说明：

隔离级别	脏读	不可重复读	幻读
READ UNCOMMITTED	可能	可能	可能
READ COMMITTED	不可能	可能	可能
REPEATABLE READ	不可能	不可能	可能
SERIALIZABLE	不可能	不可能	不可能

# 7.3 事务实战示例

-- 转账示例
START TRANSACTION;

-- 检查余额
SELECT balance FROM accounts WHERE user_id = 1 FOR UPDATE;

-- 扣款
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;

-- 加款
UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;

-- 记录转账日志
INSERT INTO transfer_logs (from_user, to_user, amount, created_at)
VALUES (1, 2, 100, NOW());

-- 提交事务
COMMIT;

-- 如果出错则回滚
-- ROLLBACK;

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

# 7.4 保存点（SAVEPOINT）

START TRANSACTION;

INSERT INTO users (username, email) VALUES ('user1', '[email protected]');

SAVEPOINT sp1;

INSERT INTO users (username, email) VALUES ('user2', '[email protected]');

SAVEPOINT sp2;

INSERT INTO users (username, email) VALUES ('user3', '[email protected]');

-- 回滚到sp2
ROLLBACK TO sp2;

-- 提交事务
COMMIT;

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

# 8. 视图和存储过程

# 8.1 视图（VIEW）

视图是虚拟表，基于SQL查询结果创建。

-- 创建视图
CREATE VIEW user_orders AS
SELECT 
    u.user_id,
    u.username,
    u.email,
    COUNT(o.order_id) AS order_count,
    SUM(o.total_amount) AS total_spent
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
GROUP BY u.user_id, u.username, u.email;

-- 使用视图
SELECT * FROM user_orders WHERE order_count > 5;

-- 更新视图定义
CREATE OR REPLACE VIEW user_orders AS
SELECT 
    u.user_id,
    u.username,
    COUNT(o.order_id) AS order_count
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
GROUP BY u.user_id, u.username;

-- 删除视图
DROP VIEW user_orders;

-- 查看所有视图
SHOW FULL TABLES WHERE TABLE_TYPE = 'VIEW';

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

# 8.2 存储过程（STORED PROCEDURE）

-- 创建存储过程
DELIMITER //

CREATE PROCEDURE GetUserOrders(IN userId INT)
BEGIN
    SELECT * FROM orders WHERE user_id = userId;
END //

DELIMITER ;

-- 调用存储过程
CALL GetUserOrders(1);

-- 带输出参数的存储过程
DELIMITER //

CREATE PROCEDURE GetUserStats(
    IN userId INT,
    OUT orderCount INT,
    OUT totalSpent DECIMAL(10,2)
)
BEGIN
    SELECT COUNT(*), IFNULL(SUM(total_amount), 0)
    INTO orderCount, totalSpent
    FROM orders
    WHERE user_id = userId;
END //

DELIMITER ;

-- 调用
CALL GetUserStats(1, @count, @total);
SELECT @count, @total;

-- 删除存储过程
DROP PROCEDURE IF EXISTS GetUserOrders;

-- 查看存储过程
SHOW PROCEDURE STATUS WHERE Db = 'your_database';
SHOW CREATE PROCEDURE GetUserOrders;

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

# 8.3 函数（FUNCTION）

-- 创建函数
DELIMITER //

CREATE FUNCTION CalculateDiscount(price DECIMAL(10,2), discount_rate DECIMAL(5,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
    RETURN price * (1 - discount_rate / 100);
END //

DELIMITER ;

-- 使用函数
SELECT product_name, price, CalculateDiscount(price, 10) AS discounted_price
FROM products;

-- 删除函数
DROP FUNCTION IF EXISTS CalculateDiscount;

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

# 8.4 触发器（TRIGGER）

-- 创建触发器（插入前）
DELIMITER //

CREATE TRIGGER before_user_insert
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
    SET NEW.created_at = NOW();
    SET NEW.username = LOWER(NEW.username);
END //

DELIMITER ;

-- 创建触发器（更新后）
DELIMITER //

CREATE TRIGGER after_order_update
AFTER UPDATE ON orders
FOR EACH ROW
BEGIN
    IF NEW.status != OLD.status THEN
        INSERT INTO order_status_log (order_id, old_status, new_status, changed_at)
        VALUES (NEW.order_id, OLD.status, NEW.status, NOW());
    END IF;
END //

DELIMITER ;

-- 删除触发器
DROP TRIGGER IF EXISTS before_user_insert;

-- 查看触发器
SHOW TRIGGERS;
SHOW CREATE TRIGGER before_user_insert;

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

# 9. 性能优化

# 9.1 索引优化

-- 查看查询执行计划
EXPLAIN SELECT * FROM users WHERE username = 'alice';

-- 分析表
ANALYZE TABLE users;

-- 优化表
OPTIMIZE TABLE users;

-- 创建合适的索引
CREATE INDEX idx_username ON users(username);
CREATE INDEX idx_email ON users(email);
CREATE INDEX idx_created_at ON users(created_at);

-- 复合索引（注意顺序）
CREATE INDEX idx_status_created ON orders(status, created_at);

-- 覆盖索引
CREATE INDEX idx_user_email_status ON users(username, email, status);

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

# 9.2 查询优化

-- 避免SELECT *
-- 不好
SELECT * FROM users;

-- 好
SELECT user_id, username, email FROM users;

-- 使用LIMIT限制结果
SELECT * FROM users ORDER BY created_at DESC LIMIT 100;

-- 避免在WHERE中使用函数
-- 不好
SELECT * FROM users WHERE YEAR(created_at) = 2024;

-- 好
SELECT * FROM users WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';

-- 使用EXISTS代替IN（大数据量时）
-- 不好
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders);

-- 好
SELECT * FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

-- 避免隐式类型转换
-- 不好（phone是VARCHAR）
SELECT * FROM users WHERE phone = 13800138000;

-- 好
SELECT * FROM users WHERE phone = '13800138000';

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

# 9.3 表设计优化

-- 选择合适的数据类型
-- 不好
CREATE TABLE users (
    id VARCHAR(50),
    age VARCHAR(10),
    is_active VARCHAR(10)
);

-- 好
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    age TINYINT UNSIGNED,
    is_active BOOLEAN
);

-- 垂直分表（字段过多时）
CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100)
);

CREATE TABLE user_profiles (
    user_id INT PRIMARY KEY,
    bio TEXT,
    avatar_url VARCHAR(255),
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

-- 水平分表（数据量过大时）
CREATE TABLE orders_2023 LIKE orders;
CREATE TABLE orders_2024 LIKE orders;

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

# 9.4 慢查询日志

-- 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;  -- 超过2秒的查询记录

-- 查看慢查询日志配置
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

-- 分析慢查询
-- 在命令行使用
-- mysqldumpslow /path/to/slow-query.log

1
2
3
4
5
6
7
8
9
10
11

# 10. 用户权限管理

# 10.1 用户管理

-- 创建用户
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password123';
CREATE USER 'remoteuser'@'%' IDENTIFIED BY 'password456';

-- 修改密码
ALTER USER 'newuser'@'localhost' IDENTIFIED BY 'newpassword';
SET PASSWORD FOR 'newuser'@'localhost' = 'newpassword';

-- 删除用户
DROP USER 'newuser'@'localhost';

-- 查看所有用户
SELECT user, host FROM mysql.user;

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

# 10.2 权限管理

-- 授予所有权限
GRANT ALL PRIVILEGES ON database_name.* TO 'user'@'localhost';

-- 授予特定权限
GRANT SELECT, INSERT, UPDATE, DELETE ON database_name.table_name TO 'user'@'localhost';

-- 授予所有数据库的权限
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' WITH GRANT OPTION;

-- 查看用户权限
SHOW GRANTS FOR 'user'@'localhost';

-- 撤销权限
REVOKE INSERT, UPDATE ON database_name.* FROM 'user'@'localhost';

-- 刷新权限
FLUSH PRIVILEGES;

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

# 10.3 权限类型

权限	说明
ALL PRIVILEGES	所有权限
SELECT	查询权限
INSERT	插入权限
UPDATE	更新权限
DELETE	删除权限
CREATE	创建数据库/表权限
DROP	删除数据库/表权限
ALTER	修改表结构权限
INDEX	创建/删除索引权限
EXECUTE	执行存储过程/函数权限
GRANT OPTION	授权权限

# 11. 备份与恢复

# 11.1 数据备份

# 备份单个数据库
mysqldump -u root -p database_name > backup.sql

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

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

# 备份单个表
mysqldump -u root -p database_name table_name > table_backup.sql

# 备份表结构（不含数据）
mysqldump -u root -p --no-data database_name > structure.sql

# 备份数据（不含结构）
mysqldump -u root -p --no-create-info database_name > data.sql

# 压缩备份
mysqldump -u root -p database_name | gzip > backup.sql.gz

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

# 11.2 数据恢复

# 恢复数据库
mysql -u root -p database_name < backup.sql

# 恢复压缩备份
gunzip < backup.sql.gz | mysql -u root -p database_name

# 在MySQL shell中恢复
mysql> USE database_name;
mysql> SOURCE /path/to/backup.sql;

1
2
3
4
5
6
7
8
9

# 11.3 导出导入数据

-- 导出为CSV
SELECT * FROM users
INTO OUTFILE '/tmp/users.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

-- 导入CSV
LOAD DATA INFILE '/tmp/users.csv'
INTO TABLE users
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

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

# 12. 常见问题和技巧

# 12.1 忘记root密码

# 1. 停止MySQL服务
sudo systemctl stop mysqld

# 2. 跳过权限验证启动
sudo mysqld_safe --skip-grant-tables &

# 3. 连接MySQL
mysql -u root

# 4. 修改密码
USE mysql;
UPDATE user SET authentication_string=PASSWORD('newpassword') WHERE User='root';
FLUSH PRIVILEGES;

# 5. 重启MySQL
sudo systemctl restart mysqld

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

# 12.2 查看表大小

-- 查看所有表大小
SELECT 
    table_schema AS 'Database',
    table_name AS 'Table',
    ROUND(((data_length + index_length) / 1024 / 1024), 2) AS 'Size (MB)'
FROM information_schema.TABLES
WHERE table_schema = 'your_database'
ORDER BY (data_length + index_length) DESC;

-- 查看数据库大小
SELECT 
    table_schema AS 'Database',
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Size (MB)'
FROM information_schema.TABLES
GROUP BY table_schema;

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

# 12.3 复制表

-- 复制表结构和数据
CREATE TABLE users_backup AS SELECT * FROM users;

-- 只复制表结构
CREATE TABLE users_copy LIKE users;

-- 复制数据到已存在的表
INSERT INTO users_backup SELECT * FROM users;

1
2
3
4
5
6
7
8

# 12.4 批量操作

-- 批量插入
INSERT INTO users (username, email) VALUES
    ('user1', '[email protected]'),
    ('user2', '[email protected]'),
    ('user3', '[email protected]');

-- 批量更新
UPDATE users 
SET status = 'active' 
WHERE user_id IN (1, 2, 3, 4, 5);

-- 批量删除
DELETE FROM users WHERE user_id IN (1, 2, 3);

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

# 12.5 Shell使用技巧

# Ctrl + U: 删除已输入的命令
# Ctrl + A: 光标跳转到行首
# Ctrl + E: 光标跳转到行尾
# Ctrl + L: 清屏
# Ctrl + C: 取消当前命令
# Ctrl + D: 退出MySQL shell

1
2
3
4
5
6

# 13. 学习路径建议

# 初级阶段（1-2个月）

掌握 MySQL 基础概念和安装配置
熟悉基本的 SQL 语句（SELECT、INSERT、UPDATE、DELETE）
理解数据类型和表约束
完成简单的数据库设计

# 中级阶段（2-3个月）

深入学习高级查询（JOIN、子查询、聚合）
掌握索引和性能优化
学习事务处理和锁机制
理解数据库设计范式

# 高级阶段（3-6个月）

学习存储过程、触发器、视图
掌握数据库备份和恢复
学习主从复制和高可用
理解 MySQL 架构和原理

# 专家阶段（6个月以上）

深入研究性能调优
学习分库分表和中间件
掌握监控和运维
参与实际项目和优化

# 14. 常见问题

# Q: MySQL 5.7 和 8.0 有什么区别？

A: MySQL 8.0 的主要改进：

更好的性能（InnoDB性能提升）
窗口函数支持
CTE（公用表表达式）支持
JSON 功能增强
默认字符集改为 utf8mb4
密码管理增强

# Q: InnoDB 和 MyISAM 的区别？

A: 主要区别：

InnoDB：支持事务、外键、行级锁，适合高并发写操作
MyISAM：不支持事务和外键，表级锁，适合读多写少场景

# Q: 如何选择数据类型？

A: 选择原则：

尽量使用小的数据类型（TINYINT vs INT）
定长字符串用 CHAR，变长用 VARCHAR
金额使用 DECIMAL 而不是 FLOAT
时间戳用 TIMESTAMP 或 DATETIME

# Q: 什么时候需要建索引？

A: 建议建索引的情况：

频繁作为查询条件的字段
频繁用于排序的字段
频繁用于连接的字段
唯一性约束的字段

不建议建索引的情况：

频繁更新的字段
区分度低的字段（如性别）
数据量很小的表

# 15. 学习资源

# 官方文档

# 在线教程

# 推荐书籍

《MySQL必知必会》- 适合初学者
《高性能MySQL》- 进阶必读
《MySQL技术内幕：InnoDB存储引擎》- 深入理解

# 实践项目

# 工具推荐

MySQL Workbench：官方图形化工具
TablePlus：现代化数据库管理工具
DBeaver：开源通用数据库工具
Navicat：功能强大的商业工具

# 总结

MySQL 是一个功能强大、应用广泛的关系型数据库管理系统。通过系统性的学习和持续的实践，您可以掌握 MySQL 的核心技能，并在实际项目中灵活运用。

学习要点：

扎实的基础：掌握 SQL 语法和数据库设计原则
实践为主：多动手写 SQL，多做项目实战
性能意识：始终关注查询性能和优化
持续学习：关注 MySQL 新特性和最佳实践

记住：数据库设计和优化是一门实践的艺术，多写代码、多分析、多思考，才能真正掌握 MySQL！

#MySQL #tutorial #SQL

上次更新: 2025/10/09, 23:53:03

← database mysql command→