一、SQL 基础入门 核心规则与分类
1.1 通用语法(硬性规则)
- 语句可单行 / 多行书写,必须以分号结尾;
- 可通过空格 / 缩进优化格式,不影响执行;
- MySQL 中 SQL 关键字不区分大小写(推荐:关键字大写,表 / 字段名小写);
- 注释方式:单行:
-- 注释内容(需加空格)、# 注释内容(MySQL 特有,无空格);多行:/* 注释内容 */。
1.2 SQL 四大分类(核心框架)
日常开发 99% 操作集中在DDL/DML/DQL,DCL 仅做权限管理基础了解。
本文系统梳理 MySQL 核心知识体系,涵盖 SQL 基础语法规则、DDL/DML/DCL/DQL 四大分类操作、数据类型与约束选择、多表关系设计及索引优化策略。重点讲解事务 ACID 特性及隔离级别,提供常用查询函数与分页方案,旨在帮助读者建立扎实的数据库基础并规避常见开发陷阱。
-- 注释内容(需加空格)、# 注释内容(MySQL 特有,无空格);多行:/* 注释内容 */。日常开发 99% 操作集中在DDL/DML/DQL,DCL 仅做权限管理基础了解。
| 分类 | 全称 | 核心作用 | 核心操作 |
|---|
| DDL | 数据定义语言 | 定义数据库 / 表 / 字段结构 | 建 / 删 / 改库、表、字段 |
| DML | 数据操作语言 | 操作表中数据 | 增(insert)、删(delete)、改(update) |
| DQL | 数据查询语言 | 查询表中记录(开发核心) | 基础查询 / 聚合 / 分组 / 多表查询等 |
| DCL | 数据控制语言 | 管理用户 / 数据库权限 | 建用户、授权 / 撤销权限 |
核心用于定义数据结构,表结构设计是关键(选对字段类型 + 加对约束,决定数据规范性)。
关键注意:最后一个字段后无逗号,约束 / 注释紧跟字段定义。
分为数值、字符串、日期三大类,无需死记所有类型,掌握开发常用款即可。
| 类型 | 大小 (byte) | 有符号范围 | 无符号范围 | 核心场景 | 关键备注 |
|---|---|---|---|---|---|
| tinyint | 1 | (-128,127) | (0,255) | 性别 / 状态(0/1) | 常用 UNSIGNED |
| int | 4 | (-2¹⁰,2¹⁰-1) | (0,2³²-1) | 年龄 / 普通 ID | 覆盖大部分业务场景 |
| bigint | 8 | (-2⁶³,2⁶³-1) | (0,2⁶⁴-1) | 订单 ID / 用户 ID | 防海量数据溢出 |
| float | 4 | 浮点型 | 浮点型 | 非精准小数 | 有精度丢失 |
| double | 8 | 浮点型 | 浮点型 | 非精准小数 | 精度高于 float |
| decimal(M,D) | 可变 | 定点型 | 定点型 | 金额 / 薪资 | M 总长度,D 小数位,金融场景必用 |
| 类型 | 大小 | 特性 | 核心场景 | 关键备注 |
|---|---|---|---|---|
| char(n) | 0-255 | 定长,性能高,浪费空间 | 手机号 / 身份证 | 不足 n 补空格,占用 n 个字符 |
| varchar(n) | 0-65535 | 变长,性能略低,省空间 | 姓名 / 地址 / 描述 | 按实际长度存储 |
| text | 0-65535 | 长文本 | 文章 / 详情 | 超出 varchar 时使用 |
| 类型 | 大小 (byte) | 格式 | 范围 | 核心场景 |
|---|---|---|---|---|
| date | 3 | YYYY-MM-DD | 1000-01-01 至 9999-12-31 | 生日 / 入职日期 |
| datetime | 8 | YYYY-MM-DD HH:MM:SS | 1000-01-01 至 9999-12-31 | 订单创建时间 |
| timestamp | 4 | YYYY-MM-DD HH:MM:SS | 1970-01-01 至 2038-01-19 | 最后修改时间(支持自动更新) |
timestamp 自动更新语法:
约束是作用于字段上的规则,核心目的:保证数据的正确性、有效性、完整性。
核心坑点:主键可搭配 auto_increment 自增,唯一约束不能自增;主键自带非空 + 唯一特性。
| 约束类型 | 关键字 | 核心作用 | 实战场景 |
|---|---|---|---|
| 非空约束 | not null | 字段值不能为 null | 姓名 / 手机号 / 用户名 |
| 唯一约束 | unique | 字段值唯一,允许多个 null | 邮箱 / 手机号 |
| 主键约束 | primary key | 一行数据的唯一标识,非空 + 唯一 | 表 ID(每张表仅一个) |
| 默认约束 | default | 未指定值时,使用默认值 | 性别 / 状态 |
| 外键约束 | foreign key | 关联两张表,保证数据一致性 | 员工表 dept_id 关联部门表 id |
外键约束核心语法(多表关系基础)
常用级联操作:
on delete set null:删除主表数据,子表外键字段置为 null;
on update cascade:更新主表主键,子表外键字段同步更新。
语法简单但坑点多,核心原则:精准过滤条件,禁止无 where 操作全表(生产环境致命错误)。
推荐指定字段插入,表结构变更时不易报错;批量插入比单条插入效率高 N 倍。
字符串 / 日期类型值必须用单引号包裹,字段与值的顺序一一对应。
强制注意:必须加 where 条件,建议先执行 select 验证过滤结果,再执行 update。
必须加 where 条件,无条件则删除全表数据;
仅能删除整行数据,若需清空单个字段值,用 UPDATE 表名 SET 字段=null WHERE 条件;
删除后自增主键不重置,数据可通过事务回滚恢复。
| 特性 | delete | truncate |
|---|---|---|
| 操作类型 | DML | DDL |
| where 条件 | 支持(精准删除) | 不支持(仅清全表) |
| 自增主键 | 不重置 | 重置为 1 |
| 事务回滚 | 可回滚 | 不可回滚 |
| 执行效率 | 低(逐行删除) | 高(重建表结构) |
MySQL 最核心的操作,覆盖基础查询→复杂多表查询,核心原则:杜绝 SELECT *(性能低 + 表结构变更易报错),掌握执行顺序是关键。
书写顺序:SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT
实际执行顺序:FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT
执行顺序决定:where 不能使用聚合函数,having 可以。
通过运算符实现精准过滤,重点掌握 like/between...and/in/is null。
常用运算符
| 类型 | 运算符 | 示例 |
|---|---|---|
| 比较 | >、>=、<、<=、=、<>(不等于) | age <> 20 |
| 范围 | between ... and ... | age BETWEEN 20 AND 30 |
| 集合 | in(...) | dept_id IN(1,2,3) |
| 模糊 | like | name LIKE '张%'(姓张)、name LIKE '__'(两个字) |
| 空值 | is null、is not null | dept_id IS NOT NULL |
| 逻辑 | and、or、not | age>20 AND gender='男' |
将一列数据作为整体进行计算,null 值不参与所有聚合函数运算;统计数量优先使用 COUNT (*)。
| 函数 | 功能 | 示例 |
|---|---|---|
| count | 统计记录数 | COUNT (*) 员工总数 |
| max | 求最大值 | MAX (salary) 最高工资 |
| min | 求最小值 | MIN (age) 最小年龄 |
| avg | 求平均值 | AVG (salary) 平均工资 |
| sum | 求和 | SUM (salary) 工资总和 |
支持单 / 多字段排序,默认ASC 升序,降序用DESC。
多字段排序:第一个字段相同,才按第二个排。
前端分页核心,起始索引从 0 开始,公式:起始索引 = (页码 -1) * 每页显示记录数。
无需代码层处理,数据库直接返回业务所需值,提升效率
实际业务无单表场景,通过外键约束关联多张表,遵循三大关系设计原则,核心:相同信息仅存储一次,杜绝数据冗余。
| 关系类型 | 定义 | 实现方式 | 实战案例 |
|---|---|---|---|
| 一对多 | 一方多条数据对应另一方一条数据 | 多的一方添加外键,关联一的一方主键 | 部门 - 员工、分类 - 商品 |
| 一对一 | 双方数据一一对应 | 任意一方添加外键 +唯一约束,关联另一方主键 | 用户 - 用户详情、学生 - 学生档案 |
| 多对多 | 双方数据互相一对多 | 建中间表,含两个外键,分别关联两张主表主键 | 学生 - 课程、商品 - 订单 |
多对多实战示例(学生 - 课程)
基于表关系实现,分为连接查询和子查询,核心:找到关联字段(外键),避免笛卡尔积。
仅查两张表有关联的数据,显式内连接更规范,推荐使用。
保留一张表全量数据,无关联的补 null;左 / 右外连接可通过交换表顺序互相转换。
将一个查询结果作为外层查询的条件、字段或临时表,需用 () 包裹,执行顺序:先子查询,后外层查询。按结果类型分 4 类,标量 / 列子查询为开发常用。
| 子查询类型 | 结果类型 | 常用操作符 |
|---|---|---|
| 标量 | 单个值(数字 / 字符串 / 日期) | =、>、<、>=、<= |
| 列 | 一列多行 | in、not in |
| 行 | 一行多列 | =、in |
| 表 | 多行多列 | 作为临时表关联 |
常用子查询示例
适用于多 DML 操作原子性场景(如转账、删部门同步删员工等),核心:一组操作要么全成,要么全败。
MySQL 默认自动提交事务(单条 DML 执行后立即提交),需手动开启事务实现多操作的原子性。
关闭自动提交:SET AUTOCOMMIT=0;(仅当前会话有效),执行 COMMIT 后才会提交。
多事务并发执行会出现脏读、不可重复读、幻读,MySQL 默认REPEATABLE READ(可重复读),兼顾隔离性和并发性能。
| 隔离级别 | 脏读 | 不可重复读 | 幻读 | 核心解决 |
|---|---|---|---|---|
| 读未提交(READ UNCOMMITTED) | ✔️ | ✔️ | ✔️ | 无 |
| 读已提交(READ COMMITTED) | ❌ | ✔️ | ✔️ | 脏读 |
| 可重复读(REPEATABLE READ,默认) | ❌ | ❌ | ✔️ | 脏读、不可重复读 |
| 串行化(SERIALIZABLE) | ❌ | ❌ | ❌ | 所有并发问题(并发性能极低) |
查询 / 设置隔离级别:
索引是帮助数据库高效获取数据的 B+Tree 数据结构,核心:提升查询效率,降低增删改效率,并非越多越好。
索引是帮助数据库高效获取数据的 B+Tree 数据结构,核心:提升查询效率,降低增删改效率,并非越多越好。
| 优点 | 缺点 |
|---|---|
| 提升查询效率,减少数据库 IO 成本 | 占用磁盘存储空间 |
| 加速排序 / 分组,降低 CPU 消耗 | 增删改需维护索引,效率降低 |
主键字段 / 唯一约束字段会自动创建索引,无需手动操作。
age+1=25/SUBSTR(name,1,1)='张';name LIKE '%张';phone='123456'(phone 为 int 类型);
微信公众号「极客日志」,在微信中扫描左侧二维码关注。展示文案:极客日志 zeeklog
在线格式化和美化您的 SQL 查询(它支持各种 SQL 方言)。 在线工具,SQL 美化和格式化在线工具,online
解析 INSERT 等受限 SQL,导出为 CSV、JSON、XML、YAML、HTML 表格(见页内语法说明)。 在线工具,SQL 转 CSV/JSON/XML在线工具,online
CSV 与 JSON/XML/HTML/TSV/SQL 等互转,单页多 Tab。 在线工具,CSV 工具包在线工具,online
将字符串编码和解码为其 Base64 格式表示形式即可。 在线工具,Base64 字符串编码/解码在线工具,online
将字符串、文件或图像转换为其 Base64 表示形式。 在线工具,Base64 文件转换器在线工具,online
将 Markdown(GFM)转为 HTML 片段,浏览器内 marked 解析;与 HTML 转 Markdown 互为补充。 在线工具,Markdown 转 HTML在线工具,online