编程马·菜鸟教程

Appearance

附录：MySQL 核心知识点汇总 ​

本附录汇总了 MySQL 的核心知识点，包括核心 SQL 语法、常用数据类型、函数、多表连接模板、备份与恢复步骤、新手易错点和 SQL 注入防范技巧，方便你快速查阅和使用。

1. 核心 SQL 语法速记 ​

1.1 DDL（数据定义语言） ​

操作语法示例
创建数据库CREATE DATABASE 数据库名;CREATE DATABASE test;
删除数据库DROP DATABASE 数据库名;DROP DATABASE test;
使用数据库USE 数据库名;USE test;
创建表CREATE TABLE 表名 (字段定义);CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(50));
修改表ALTER TABLE 表名 操作;ALTER TABLE users ADD COLUMN age INT;
删除表DROP TABLE 表名;DROP TABLE users;
清空表TRUNCATE TABLE 表名;TRUNCATE TABLE users;
创建索引CREATE INDEX 索引名 ON 表名(字段);CREATE INDEX idx_name ON users(name);
删除索引DROP INDEX 索引名 ON 表名;DROP INDEX idx_name ON users;

1.2 DML（数据操纵语言） ​

操作语法示例
插入数据INSERT INTO 表名 (字段) VALUES (值);INSERT INTO users (name, age) VALUES ('张三', 18);
更新数据UPDATE 表名 SET 字段=值 WHERE 条件;UPDATE users SET age=19 WHERE id=1;
删除数据DELETE FROM 表名 WHERE 条件;DELETE FROM users WHERE id=1;

1.3 DQL（数据查询语言） ​

操作语法示例
基本查询SELECT 字段 FROM 表名;SELECT * FROM users;
条件查询SELECT 字段 FROM 表名 WHERE 条件;SELECT * FROM users WHERE age > 18;
排序SELECT 字段 FROM 表名 ORDER BY 字段;SELECT * FROM users ORDER BY age DESC;
分页SELECT 字段 FROM 表名 LIMIT 数量 OFFSET 偏移;SELECT * FROM users LIMIT 10 OFFSET 20;
分组SELECT 字段 FROM 表名 GROUP BY 字段;SELECT age, COUNT(*) FROM users GROUP BY age;
连接查询SELECT 字段 FROM 表1 JOIN 表2 ON 条件;SELECT u.name, o.order_no FROM users u JOIN orders o ON u.id = o.user_id;
子查询SELECT 字段 FROM 表名 WHERE 字段 IN (子查询);SELECT * FROM users WHERE id IN (SELECT user_id FROM orders);

1.4 事务操作 ​

操作语法示例
开始事务START TRANSACTION;START TRANSACTION;
提交事务COMMIT;COMMIT;
回滚事务ROLLBACK;ROLLBACK;

2. 常用数据类型、约束汇总 ​

2.1 数值类型 ​

类型大小范围适用场景
TINYINT1字节-128~127小整数，如状态
SMALLINT2字节-32768~32767小整数，如年龄
INT4字节-2147483648~2147483647常用整数
BIGINT8字节很大范围大整数，如ID
DECIMAL(M,D)可变精确小数金额、价格
FLOAT4字节单精度浮点数科学计算
DOUBLE8字节双精度浮点数科学计算

2.2 字符串类型 ​

类型大小特点适用场景
CHAR(N)N字节固定长度身份证号、手机号
VARCHAR(N)0~65535字节可变长度姓名、地址
TEXT0~65535字节大文本文章内容
MEDIUMTEXT0~16777215字节中等文本长文章
LONGTEXT0~4294967295字节超长文本非常长的内容
BLOB0~65535字节二进制数据图片、文件

2.3 日期时间类型 ​

类型大小范围适用场景
DATE3字节1000-01-01~9999-12-31日期
TIME3字节-838:59:59~838:59:59时间
DATETIME8字节1000-01-01 00:00:00~9999-12-31 23:59:59日期时间
TIMESTAMP4字节1970-01-01 00:00:00~2038-01-19 03:14:07时间戳
YEAR1字节1901~2155年份

2.4 约束类型 ​

约束作用示例
PRIMARY KEY主键，唯一且非空id INT PRIMARY KEY
FOREIGN KEY外键，关联其他表user_id INT REFERENCES users(id)
UNIQUE唯一约束email VARCHAR(100) UNIQUE
NOT NULL非空约束name VARCHAR(50) NOT NULL
DEFAULT默认值created_at DATETIME DEFAULT CURRENT_TIMESTAMP
AUTO_INCREMENT自增id INT PRIMARY KEY AUTO_INCREMENT
CHECK检查约束age INT CHECK (age > 0)

3. 聚合函数、字符串函数、日期函数汇总 ​

3.1 聚合函数 ​

函数作用示例
COUNT()计数SELECT COUNT(*) FROM users;
SUM()求和SELECT SUM(amount) FROM orders;
AVG()平均值SELECT AVG(age) FROM users;
MAX()最大值SELECT MAX(price) FROM products;
MIN()最小值SELECT MIN(price) FROM products;
GROUP_CONCAT()连接字符串SELECT GROUP_CONCAT(name) FROM users;

3.2 字符串函数 ​

函数作用示例
CONCAT()拼接字符串SELECT CONCAT(first_name, ' ', last_name) FROM users;
LENGTH()字符串长度SELECT LENGTH(name) FROM users;
SUBSTR()截取字符串SELECT SUBSTR(name, 1, 3) FROM users;
UPPER()转为大写SELECT UPPER(name) FROM users;
LOWER()转为小写SELECT LOWER(name) FROM users;
TRIM()去除首尾空格SELECT TRIM(name) FROM users;
REPLACE()替换字符串SELECT REPLACE(name, 'a', 'b') FROM users;
LEFT()左截取SELECT LEFT(name, 3) FROM users;
RIGHT()右截取SELECT RIGHT(name, 3) FROM users;

3.3 日期函数 ​

函数作用示例
NOW()当前日期时间SELECT NOW();
CURDATE()当前日期SELECT CURDATE();
CURTIME()当前时间SELECT CURTIME();
DATE()提取日期部分SELECT DATE(created_at) FROM users;
TIME()提取时间部分SELECT TIME(created_at) FROM users;
YEAR()提取年份SELECT YEAR(created_at) FROM users;
MONTH()提取月份SELECT MONTH(created_at) FROM users;
DAY()提取日SELECT DAY(created_at) FROM users;
HOUR()提取小时SELECT HOUR(created_at) FROM users;
MINUTE()提取分钟SELECT MINUTE(created_at) FROM users;
SECOND()提取秒SELECT SECOND(created_at) FROM users;
DATEDIFF()日期差值SELECT DATEDIFF(NOW(), created_at) FROM users;
DATE_ADD()日期增加SELECT DATE_ADD(created_at, INTERVAL 1 DAY) FROM users;
DATE_SUB()日期减少SELECT DATE_SUB(created_at, INTERVAL 1 DAY) FROM users;

3.4 其他函数 ​

函数作用示例
IF()条件判断SELECT IF(age > 18, '成年', '未成年') FROM users;
IFNULL()空值处理SELECT IFNULL(email, '无邮箱') FROM users;
NULLIF()比较两个值SELECT NULLIF(a, b) FROM table;
CASE多条件判断SELECT CASE WHEN age < 18 THEN '未成年' WHEN age < 60 THEN '成年' ELSE '老年' END FROM users;
ROUND()四舍五入SELECT ROUND(price, 2) FROM products;
FLOOR()向下取整SELECT FLOOR(price) FROM products;
CEIL()向上取整SELECT CEIL(price) FROM products;
RAND()随机数SELECT RAND() FROM users;

4. 多表连接、子查询常用模板 ​

4.1 内连接（INNER JOIN） ​

sql
-- 基本内连接
SELECT
    t1.column1,
    t1.column2,
    t2.column1,
    t2.column2
FROM
    table1 t1
INNER JOIN
    table2 t2 ON t1.id = t2.table1_id
WHERE
    t1.condition = 'value'
ORDER BY
    t1.column1;

-- 三表连接
SELECT
    u.name,
    o.order_no,
    p.name as product_name
FROM
    users u
INNER JOIN
    orders o ON u.id = o.user_id
INNER JOIN
    order_items oi ON o.id = oi.order_id
INNER JOIN
    products p ON oi.product_id = p.id
WHERE
    o.status = 'completed';

4.2 左连接（LEFT JOIN） ​

sql
-- 基本左连接
SELECT
    t1.column1,
    t1.column2,
    t2.column1
FROM
    table1 t1
LEFT JOIN
    table2 t2 ON t1.id = t2.table1_id
WHERE
    t1.condition = 'value';

-- 左连接查询所有用户及其订单
SELECT
    u.id,
    u.name,
    o.order_no
FROM
    users u
LEFT JOIN
    orders o ON u.id = o.user_id
ORDER BY
    u.id;

4.3 右连接（RIGHT JOIN） ​

sql
-- 基本右连接
SELECT
    t1.column1,
    t2.column1,
    t2.column2
FROM
    table1 t1
RIGHT JOIN
    table2 t2 ON t1.id = t2.table1_id
WHERE
    t2.condition = 'value';

4.4 子查询 ​

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

-- FROM 子句中的子查询
SELECT
    t.user_id,
    t.order_count,
    u.name
FROM
    (SELECT user_id, COUNT(*) as order_count FROM orders GROUP BY user_id) t
INNER JOIN
    users u ON t.user_id = u.id
WHERE
    t.order_count > 5;

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

-- IN 子查询
SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders WHERE status = 'completed');

5. 数据库备份与恢复步骤 ​

5.1 备份步骤 ​

1. 使用 mysqldump 备份

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

# 备份单个表
mysqldump -u root -p database_name table1 table2 > backup.sql

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

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

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

2. 使用可视化工具备份

  • Navicat：选择数据库 → 右键 → 转储 SQL 文件
  • phpMyAdmin：选择数据库 → 导出 → 选择 SQL 格式
  • MySQL Workbench：服务器 → 数据导出

3. 自动备份脚本

bash
#!/bin/bash

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

# 创建备份目录
mkdir -p $BACKUP_DIR

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

# 压缩备份
gzip $BACKUP_DIR/${DB_NAME}_${DATE}.sql

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

echo "Backup completed: $BACKUP_DIR/${DB_NAME}_${DATE}.sql.gz"

5.2 恢复步骤 ​

1. 使用 SOURCE 命令恢复

sql
-- 登录 MySQL
mysql -u root -p

-- 创建数据库（如果不存在）
CREATE DATABASE IF NOT EXISTS database_name;

-- 使用数据库
USE database_name;

-- 恢复数据
SOURCE /path/to/backup.sql;

2. 使用 mysql 命令恢复

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

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

3. 使用可视化工具恢复

  • Navicat：选择数据库 → 右键 → 运行 SQL 文件
  • phpMyAdmin：选择数据库 → 导入 → 选择 SQL 文件
  • MySQL Workbench：服务器 → 数据导入

5.3 备份策略 ​

备份类型频率保留时间适用场景
完整备份每天7-30天所有数据库
增量备份每小时24-48小时大型数据库
差异备份每周1-2个月中等大小数据库

6. 新手易错点对照表 ​

错误类型错误表现原因解决方案
登录失败无法连接 MySQL密码错误、服务未启动检查密码、启动服务
SQL 语法错误语法报错拼写错误、缺少分号检查语法、使用格式化工具
中文乱码中文显示为乱码编码未设置为 UTF-8修改编码为 utf8mb4
误操作数据误删或误改数据未加 WHERE 条件使用事务、添加 WHERE 条件
连接查询无结果连接查询返回空连接条件错误检查连接条件、数据类型
索引未生效查询速度慢索引使用不当优化查询、合理设计索引
约束冲突插入数据失败主键重复、外键错误检查约束条件、数据完整性
性能问题查询执行慢缺少索引、SQL 复杂添加索引、优化 SQL

7. SQL 注入防范技巧汇总 ​

7.1 防范措施 ​

1. 使用参数化查询

php
// PDO 示例
$stmt = $pdo->prepare("SELECT * FROM users WHERE username = ? AND password = ?");
$stmt->execute([$username, $password]);

// MySQLi 示例
$stmt = $mysqli->prepare("SELECT * FROM users WHERE username = ? AND password = ?");
$stmt->bind_param("ss", $username, $password);
$stmt->execute();

2. 输入验证

php
// 验证用户名
if (!preg_match('/^[a-zA-Z0-9_]+$/', $username)) {
    die("用户名格式错误");
}

// 验证邮箱
if (!filter_var($email, FILTER_VALIDATE_EMAIL)) {
    die("邮箱格式错误");
}

3. 最小权限原则

sql
-- 创建只读用户
CREATE USER 'readonly'@'localhost' IDENTIFIED BY 'password';
GRANT SELECT ON database.* TO 'readonly'@'localhost';

-- 创建应用用户
CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'password';
GRANT SELECT, INSERT, UPDATE, DELETE ON database.* TO 'appuser'@'localhost';

4. 使用 ORM 框架

  • PHP：Laravel Eloquent、Doctrine
  • Java：Hibernate、MyBatis
  • Python：SQLAlchemy、Django ORM

5. 转义特殊字符

php
// PHP 示例
$username = mysqli_real_escape_string($conn, $_POST['username']);
$password = mysqli_real_escape_string($conn, $_POST['password']);
$sql = "SELECT * FROM users WHERE username = '$username' AND password = '$password'";

7.2 常见攻击场景及防范 ​

1. 登录绕过

攻击：

sql
-- 输入用户名：admin' --
SELECT * FROM users WHERE username = 'admin' --' AND password = '任意值';

防范：

  • 使用参数化查询
  • 验证输入格式

2. 数据窃取

攻击：

sql
-- 输入 ID：1' UNION SELECT username, password FROM users --
SELECT * FROM products WHERE id = '1' UNION SELECT username, password FROM users --';

防范：

  • 限制查询结果数量
  • 使用参数化查询
  • 最小权限原则

3. 数据删除

攻击：

sql
-- 输入 ID：1'; DROP TABLE users; --
SELECT * FROM products WHERE id = '1'; DROP TABLE users; --';

防范：

  • 禁用多语句执行
  • 使用参数化查询
  • 最小权限原则

7.3 安全检查清单 ​

检查项状态说明
使用参数化查询✅防止 SQL 注入
输入验证✅检查用户输入
最小权限原则✅限制用户权限
使用 ORM 框架✅自动处理 SQL 注入
转义特殊字符✅处理用户输入
禁用多语句执行✅防止堆叠注入
定期安全审计✅检查安全配置
及时更新 MySQL✅修复安全漏洞

附录小结 ​

本附录汇总了 MySQL 的核心知识点，包括：

  • 核心 SQL 语法：DDL、DML、DQL、事务操作
  • 数据类型：数值、字符串、日期时间类型
  • 约束：主键、外键、唯一、非空等
  • 函数：聚合、字符串、日期函数
  • 多表连接：内连接、左连接、右连接
  • 子查询：WHERE 子句、FROM 子句、EXISTS、IN
  • 备份与恢复：mysqldump、可视化工具、自动脚本
  • 新手易错点：登录失败、语法错误、中文乱码等
  • SQL 注入防范：参数化查询、输入验证、最小权限

这些知识点是 MySQL 学习的核心内容，掌握它们可以帮助你更高效地使用 MySQL 数据库。在实际应用中，你可能会遇到更多复杂的场景，需要根据具体情况灵活运用这些知识。

学习建议：

  • 定期复习这些核心知识点
  • 通过实际项目巩固所学
  • 关注 MySQL 的最新特性和最佳实践
  • 加入 MySQL 社区，交流学习经验

祝你在 MySQL 学习之路上取得成功！

© 2026 编程马·菜鸟教程 版权所有

基于 VitePress 构建