当前位置： 首页 > news >正文

MySQL 全套 SQL 语句：语法规范、实战案例、易错点与避坑总结

news 2026/7/1 17:07:00

一、前言

本学期《MySQL 数据库技术》系统学习了 MySQL 各类 SQL 操作语句，覆盖库、表、数据、查询、事务、权限、函数等全场景。本文完整梳理全部 SQL 分类，每条语句包含标准语法、使用规范、适用场景、实操案例、高频错误，图文结合、原创完整，同时记录本人学习中遇到的难点与待深入研究的问题，适合 MySQL 初学者系统复习查阅。

二、SQL 整体分类总览

SQL 语句按功能分为 5 大类，思维导图如下（配图建议：分类架构图）：

  1. DDL 数据定义语言：库、表、字段结构创建 / 修改 / 删除
  2. DML 数据操作语言：表内数据增删改
  3. DQL 数据查询语言：SELECT 核心查询（使用频率最高）
  4. DCL 数据控制语言：用户创建、权限分配、账户管理
  5. TCL 事务控制语言：事务提交、回滚、保存点

三、DDL 数据定义语言（库 & 表操作）

3.1 数据库操作

1. 创建数据库
  • 标准语法

sql

CREATE DATABASE [IF NOT EXISTS] 库名 DEFAULT CHARACTER SET 字符集 DEFAULT COLLATE 排序规则;
  • 使用规范
    1. 必须加IF NOT EXISTS避免库已存在报错；
    2. 生产环境强制指定字符集utf8mb4（支持 emoji），排序规则utf8mb4_unicode_ci；
  • 实战案例

sql

CREATE DATABASE IF NOT EXISTS student_db DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_unicode_ci;
  • 易错点 ⚠️ 直接写CREATE DATABASE student_db;不指定字符集，默认 latin1，中文存储乱码； ⚠️ 数据库名称不能含空格、中文（不推荐）、MySQL 关键字。
2. 查询数据库

sql

-- 查看所有库 SHOW DATABASES; -- 查看库创建详情 SHOW CREATE DATABASE student_db; -- 切换数据库 USE student_db;
3. 修改数据库字符集

sql

ALTER DATABASE student_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
4. 删除数据库

sql

DROP DATABASE IF EXISTS student_db;

⚠️ 高危操作，删除后所有表、数据永久丢失，生产禁止随意执行。

3.2 数据表操作

1. 创建表
  • 基础语法

sql

CREATE TABLE IF NOT EXISTS 表名( 字段1 数据类型 [约束], 字段2 数据类型 [约束], ... 表级约束 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  • 约束分类：主键 PRIMARY KEY、唯一 UNIQUE、非空 NOT NULL、默认值 DEFAULT、外键 FOREIGN KEY
  • 实战案例

sql

CREATE TABLE IF NOT EXISTS student( id INT PRIMARY KEY AUTO_INCREMENT COMMENT '学生主键', name VARCHAR(20) NOT NULL COMMENT '学生姓名', age TINYINT DEFAULT 18 COMMENT '年龄', class_id INT COMMENT '班级id', create_time DATETIME DEFAULT NOW() COMMENT '创建时间', UNIQUE uk_name(name), FOREIGN KEY fk_class(class_id) REFERENCES class(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='学生信息表';
  • 使用规范
    1. 存储引擎统一使用 InnoDB（支持事务、外键）；
    2. 每个字段加COMMENT注释，便于后期维护；
    3. 主键统一自增 INT，避免字符串主键；
  • 常见错误
    1. 外键关联字段数据类型不一致，创建失败；
    2. VARCHAR 不指定长度；
    3. 未写IF NOT EXISTS重复建表抛出异常。
2. 查看表结构

sql

DESC student; SHOW COLUMNS FROM student; SHOW CREATE TABLE student; -- 查看完整建表语句
3. 修改表结构 ALTER TABLE
  1. 添加字段

sql

ALTER TABLE student ADD COLUMN email VARCHAR(50) AFTER name;
  1. 修改字段类型 / 名称

sql

ALTER TABLE student MODIFY COLUMN age SMALLINT; ALTER TABLE student CHANGE COLUMN email stu_email VARCHAR(60);
  1. 删除字段

sql

ALTER TABLE student DROP COLUMN email;
  1. 添加 / 删除主键、唯一索引

sql

ALTER TABLE student ADD PRIMARY KEY(id); ALTER TABLE student DROP INDEX uk_name;
4. 删除表

sql

DROP TABLE IF EXISTS student;
3.3 索引操作（DDL 补充）

sql

-- 创建普通索引 CREATE INDEX idx_stu_name ON student(name); -- 删除索引 DROP INDEX idx_stu_name ON student;

💡 应用场景：WHERE、JOIN 查询条件字段建立索引，大幅提升查询速度；索引过多会降低插入更新性能。

四、DML 数据操作语言（增删改）

4.1 INSERT 插入数据

语法 1：全字段插入

sql

INSERT INTO student VALUES(1,'张三',20,1,NOW());
语法 2：指定字段插入（推荐）

sql

INSERT INTO student(name,age,class_id) VALUES('李四',19,2);
语法 3：批量插入（性能最优）

sql

INSERT INTO student(name,age) VALUES('王五',18),('赵六',21),('钱七',20);
语法 4：查询结果插入新表

sql

CREATE TABLE student_back LIKE student; -- 复制表结构 INSERT INTO student_back SELECT * FROM student WHERE age>18;
  • 规范与易错点
    1. 批量插入比多条单 INSERT 效率高数十倍；
    2. 非空字段必须赋值，否则报错；
    3. 唯一索引字段不能插入重复值；
    4. 字符串、日期必须加单引号，数字不用。

4.2 UPDATE 更新数据

sql

-- 标准写法（必须带WHERE） UPDATE student SET age=22 WHERE id=3; -- 多字段更新 UPDATE student SET age=20,class_id=3 WHERE name='张三';

⚠️ 致命易错点：省略WHERE条件会更新表中全部数据，生产环境禁止！

4.3 DELETE 删除数据

sql

DELETE FROM student WHERE id=5;
拓展：TRUNCATE 清空表

sql

TRUNCATE TABLE student;

DELETE vs TRUNCATE 对比表格：

表格

特性DELETETRUNCATE
日志记录逐行记录，可回滚不记录单行日志，不可回滚
自增主键保留原有数值重置自增为 1
触发器触发 DELETE 触发器不触发
速度大数据量慢速度极快

五、DQL 数据查询语言（SELECT，核心重点）

5.1 基础完整语法结构（执行顺序标注）

sql

SELECT 字段列表 -- 5 FROM 表名/关联表 -- 1 WHERE 行过滤条件 -- 2 GROUP BY 分组字段 -- 3 HAVING 分组后过滤 -- 4 ORDER BY 排序字段 -- 6 LIMIT 分页偏移,条数; -- 7

5.2 基础查询

sql

-- 查询全部字段 SELECT * FROM student; -- 查询指定字段、别名 SELECT id AS 学号,name AS 姓名 FROM student; -- 去重 DISTINCT SELECT DISTINCT class_id FROM student;

5.3 WHERE 条件过滤

运算符：> < >= <= = != AND OR LIKE IN BETWEEN IS NULL

sql

-- 模糊查询：%任意字符，_单个字符 SELECT * FROM student WHERE name LIKE '张%'; -- 空值判断必须用IS NULL，不能用=NULL SELECT * FROM student WHERE email IS NULL; -- 区间查询 SELECT * FROM student WHERE age BETWEEN 18 AND 22;

⚠️ 易错：=NULL查询无结果，NULL 不能用等值匹配。

5.4 聚合函数（配合 GROUP BY）

常用聚合：COUNT() SUM() AVG() MAX() MIN()

sql

-- 每个班级人数、平均年龄 SELECT class_id,COUNT(*) 人数,AVG(age) 平均年龄 FROM student GROUP BY class_id HAVING COUNT(*)>=5; -- 分组后过滤不能用WHERE

💡 区分：WHERE 过滤原始数据，HAVING 过滤分组聚合结果。

5.5 多表连接查询

  1. 内连接 INNER JOIN（只返回匹配数据）

sql

SELECT s.name,c.class_name FROM student s INNER JOIN class c ON s.class_id = c.id;
  1. 左连接 LEFT JOIN（左表全部数据，右表无匹配补 NULL）

sql

SELECT s.name,c.class_name FROM student s LEFT JOIN class c ON s.class_id = c.id;
  1. 右连接 RIGHT JOIN
  2. 自连接：同一张表关联查询（树形结构、上下级场景）

5.6 子查询

  • 标量子查询（返回单个值）

sql

SELECT name FROM student WHERE class_id=(SELECT id FROM class WHERE class_name='一班');
  • IN 子查询、EXISTS 存在性子查询（大数据量性能优于 IN）

5.7 分页 LIMIT

sql

-- 第1页，10条数据 SELECT * FROM student LIMIT 0,10; -- 公式：LIMIT (页码-1)*每页条数,每页条数

⚠️ 深度分页LIMIT 100000,10性能极差，优化方案：主键过滤分页。

5.8 排序 ORDER BY

sql

-- 年龄降序，同年龄学号升序 SELECT * FROM student ORDER BY age DESC,id ASC;

六、TCL 事务控制语言（InnoDB 专属）

核心语法

sql

-- 开启事务 START TRANSACTION; -- 执行DML语句 UPDATE account SET money=money-100 WHERE id=1; UPDATE account SET money=money+100 WHERE id=2; -- 提交事务，永久生效 COMMIT; -- 回滚，撤销全部操作 ROLLBACK; -- 保存点（局部回滚） SAVEPOINT point1; ROLLBACK TO point1;

事务四大特性 ACID

  1. 原子性 Atomic：全部成功或全部失败
  2. 一致性 Consistent：执行前后数据合法
  3. 隔离性 Isolate：事务间互不干扰（4 种隔离级别）
  4. 持久性 Durable：提交后数据永久保存

易错点

  1. DDL 语句（CREATE/ALTER）执行会自动提交事务；
  2. MyISAM 引擎不支持事务；
  3. 忘记 COMMIT 会导致锁表，阻塞其他业务操作。

七、DCL 数据权限控制语句

7.1 创建用户

sql

CREATE USER 'user1'@'localhost' IDENTIFIED BY '123456';

7.2 分配权限 GRANT

sql

-- 给user1授予student_db全部表查询、插入权限 GRANT SELECT,INSERT ON student_db.* TO 'user1'@'localhost'; -- 授予全部权限 GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%'; -- 刷新权限生效 FLUSH PRIVILEGES;

7.3 回收权限 REVOKE

sql

REVOKE INSERT ON student_db.* FROM 'user1'@'localhost';

7.4 删除用户

sql

DROP USER IF EXISTS 'user1'@'localhost';

⚠️ 账号主机标识%代表允许任意 IP 访问，生产环境限制指定 IP 提升安全。

八、常用函数 SQL

8.1 字符串函数

CONCAT(str1,str2)拼接、SUBSTRING()截取、LENGTH()长度、REPLACE()替换

sql

SELECT CONCAT(name,'-',age) FROM student;

8.2 日期函数

NOW()当前时间、DATE_ADD()日期增减、DATEDIFF()日期差值

sql

SELECT DATE_FORMAT(create_time,'%Y-%m-%d') FROM student;

8.3 数学函数

ROUND()四舍五入、FLOOR 向下取整、CEIL 向上取整

8.4 流程控制函数

sql

-- IF判断 SELECT name,IF(age>=18,'成年','未成年') FROM student; -- CASE多分支 SELECT name, CASE WHEN class_id=1 THEN '一班' WHEN class_id=2 THEN '二班' ELSE '其他班级' END AS 班级 FROM student;

九、高频易错点汇总（学习经验总结）

  1. WHERE 与 HAVING 混淆：聚合结果过滤只能用 HAVING；
  2. UPDATE/DELETE 忘记 WHERE：全表修改 / 删除，线上重大事故；
  3. NULL 判断使用 = NULL：无查询结果，必须 IS NULL/IS NOT NULL；
  4. 字符集未指定 utf8mb4：emoji、生僻中文乱码；
  5. 批量操作不加事务：批量插入更新中途报错，数据不一致；
  6. 关联查询不建索引：多表联查全表扫描，查询超时；
  7. LIMIT 深度分页：偏移量过大，SQL 执行缓慢；
  8. 外键使用不规范：字段类型不一致、主表数据无法删除；
  9. 关键字不做转义：表名 / 字段名使用 name、order 等关键字，语法报错，需用`包裹；
  10. MyISAM 误用事务：引擎不支持回滚、崩溃丢数据。
  11. Mysql 的思维导图：MySQL全套SQL语句知识汇总 ┌───────────────┬───────────────┬───────────────┬───────────────┐ DDL结构语言 DML增删改数据 DQL查询核心 TCL事务控制 DCL权限管理 │ │ │ │ │ ┌──────┴──────┐ ┌─────┴─────┐ ┌────┴────┐ ┌────┴────┐ ┌────┴────┐ 数据库/表/索引 INSERT/UPDATE SELECT全套语法 事务提交回滚 用户创建授权 │ ┌───────┴───────┐ 分组聚合/多表联查/分页排序 ┌───────────────┬───────────────┐ 内置函数工具 高频易错避坑 学习复盘总结

十、学习遗留疑问与后续实践计划

  1. 疑问：MySQL 四种事务隔离级别在业务中如何选型？幻读场景该如何业务层面规避？
  2. 疑问：百万级数据表索引设计规则，联合索引最左匹配失效完整场景有哪些？
  3. 后续实践：
    • 搭建千万级测试数据表，对比不同查询语句执行效率，学习 EXPLAIN 执行计划分析；
    • 实操分库分表 SQL 适配，学习分页、联查分片改造方案；
    • 模拟线上慢 SQL，掌握索引优化、SQL 重写完整流程。

十一、结语

本文完整覆盖 MySQL 课程全部 SQL 语句，从库表结构定义、数据增删改查、事务、权限到内置函数全部包含，每条语句配套可直接运行的实战代码。整理过程中复盘了课堂实操踩过的各类坑，梳理出通用避坑规范，后续将通过大型项目实操深化 SQL 优化、高级特性的学习，进一步夯实数据库开发基础。

http://www.jsqmd.com/news/1103647/

相关文章：

  • 微信消息智能流转：告别手动复制的自动化解决方案
  • 遗传算法实战：N皇后问题的Python可运行实现
  • Python新手快速上手项目的五个关键步骤
  • 为什么你的 AI Coding 账单越涨越快？十个节约 Token 的工程办法。
  • 怎样高效使用猫抓Cat-Catch：3个实用技巧全面攻略
  • Windows DLL注入终极指南：三分钟掌握Xenos工具
  • 百德福参与起草：肿瘤患者术后营养新标准，科学干预有了权威指南
  • 想知道锅炉部件公司哪家好？这些优质之选不容错过！
  • 太阳能智能PID追光(S7-1200、高质量、PLC、组态设计)
  • 【成都理工大学工程技术学院本科毕业论文】基于Spring Boot的网上商城设计与实现
  • 凌晨2点Python数据服务突然告警，我靠这张排查流程图5分钟定位了内存泄漏根因
  • 如何在3小时内为你的应用添加网易云音乐播放功能？
  • Nmap与Metasploit实战：从rpcbind端口扫描到NFS漏洞利用的完整指南
  • 解锁AI图像超分辨率：waifu2x-caffe深度实战指南
  • 为什么专业开发者都选择Hack字体：提升代码可读性的终极解决方案
  • 2026年网络科技业务解析：重庆山三云企网络科技公司主要业务揭秘
  • 微信小程序逆向工程神器：5分钟掌握wxappUnpacker完全指南
  • Three.js 饱和度(自定义Pass)教程
  • 3种场景解锁Mac桌面歌词：LyricsX让你的音乐体验升级
  • 金相显微镜：精准聚焦半导体与PCB检测需求
  • 都有哪些AI数据可视化工具？2026年有7款值得收藏的AI数据可视化工具
  • NS-USBLoader完整指南：一站式Switch文件管理解决方案
  • AI 大模型热潮的第三年，这场直播给出了 4 个值得参考的判断
  • 从数据集到GUI界面，基于Python+YOLOv8+PyQt5的水果识别系统工程化落地完整指南
  • 用Agent如何提升LTV？实操教学来了
  • 你还在用截图导出SQL结果？，20年JetBrains生态专家吐血整理：7类业务场景下的最优导出策略矩阵（含安全审计合规模板）
  • 3步解锁千万曲库：网易云音乐API如何重构音乐应用开发范式
  • 如何一键自动化部署Microsoft Office：开源工具LKY Office Tools全面指南
  • 移动办公文档同步总断流？多终端无缝协同底层架构选型避坑
  • Mac Mouse Fix深度解析：让普通鼠标在macOS上超越苹果触控板的技术架构揭秘