MySQL笔记整理.md

MySQL笔记整理 > 在线练习平台：牛客 SQL 快速入门 ## MySQL 执行顺序 >视频：数据库面试——SQL执行顺序_哔哩哔哩_bilibili MySQL执行顺序与编写顺序不同，编写顺序： ``sql select from where group by having order by limit ` 编写顺序从上到下依次编写，但执行顺序却不同：中间开始，返回头部，最后执行尾部 `sql 1. FROM 确定数据来源，从指定表中获取数据 2. WHERE 对原始数据进行筛选，只保留满足条件的数据行 3. GROUP BY 对筛选后的数据进行分组 4. HAVING 对分组后的结果再次进行筛选，通常用于筛选聚合结果 5. SELECT 决定最终需要返回哪些字段、表达式或聚合结果 6. ORDER BY 对查询结果进行排序 7. LIMIT 限制最终返回的数据条数 ` 因此，默认情况下，select 语句设置的别名，只有尾部 order by 才能使用（limit 只是限制数量，不做筛选），但 MySQL 对 having、order by 做了优化，having 与 order by 也可以使用 select 设置的别名，示例： `sql SELECT class_name, AVG(score) AS avg_score FROM score GROUP BY class_name HAVING avg_score > 83; ` 执行顺序是： - from - group by - having - select 此时 select 设置的别名，having 等语句访问应该是无效的，但由于 MySQL 做了优化，上面的代码也可以正确运行 ## MySQL基础 Mysql 执行顺序应该是 from, where, group by, having, select, order by, limit | 一级分类 | 二级分类 | 操作 | SQL 示例 | 说明 | | ---------------- | -------- | -------------- | ------------------------------------------------------------ | ------------------------------------------------------------ | | 数据库 | 查看 | 查看所有数据库 | SHOW DATABASES; | 查看 MySQL 中已有数据库 | | | 创建 | 创建数据库 | CREATE DATABASE school; | 创建 school 数据库 | | | 删除 | 删除数据库 | DROP DATABASE school; | 删除整个数据库及其中所有表 | | | 选择 | 选择数据库 | USE school; | 后续操作默认针对该数据库 | | | 查看 | 查看当前数据库 | SELECT DATABASE(); | 查看当前正在使用的数据库 | | 表 | 查看 | 查看所有表 | SHOW TABLES; | 查看当前数据库中的所有表 | | | 创建 | 创建表 | CREATE TABLE user (id INT, name VARCHAR(50)); | 创建表 | | | 查看 | 查看表结构 | DESC user; | 查看字段名、类型、NULL 等信息 | | | 删除 | 删除表 | DROP TABLE user; | 删除表结构和其中的数据 | | | 修改 | 添加字段 | ALTER TABLE user ADD age INT; | 为已有表添加字段 | | | 修改 | 删除字段 | ALTER TABLE user DROP COLUMN age; | 删除指定字段 | | | 修改 | 修改字段 | ALTER TABLE user MODIFY name VARCHAR(100); | 修改字段类型等定义 | | | 修改 | 重命名表 | RENAME TABLE user TO users; | 修改表名 | | 行数据 | 新增 | 新增一行 | INSERT INTO user (id, name) VALUES (1, 'Tom'); | 插入一条数据 | | | 新增 | 新增多行 | INSERT INTO user (id, name) VALUES (1, 'Tom'), (2, 'Jack'); | 一次插入多条数据 | | | 修改 | 修改数据 | UPDATE user SET name = 'Jerry' WHERE id = 1; | 修改符合条件的数据 | | | 删除 | 删除指定数据 | DELETE FROM user WHERE id = 1; | 删除符合条件的行 | | | 删除 | 删除全部数据 | DELETE FROM user; | 删除所有数据，但保留表结构 | | 查询 | 基础查询 | 查询全部字段 | SELECT FROM user; | 查询所有字段 | | | 基础查询 | 查询指定字段 | SELECT id, name FROM user; | 查询指定字段 | | | 别名 | 字段别名 | SELECT name AS username FROM user; | AS 可以省略 | | | 去重 | 查询不同值 | SELECT DISTINCT age FROM user; | 去除重复结果 | | | 排序 | 升序 | SELECT FROM user ORDER BY age ASC; | ASC 为升序，也是默认值 | | | 排序 | 降序 | SELECT FROM user ORDER BY age DESC; | DESC 为降序 | | | 排序 | 多列排序 | select device_id, gpa, age from user_profile order by gpa asc, age asc; | 依次放置即可，首先对 gap 进行升序排序，然后再对 age 进行升序排序 | | | 限制结果 | 限制数量 | SELECT FROM user LIMIT 10; | 获取前 10 条 | | | 分页 | 偏移查询 | SELECT FROM user LIMIT 10 OFFSET 20; | 跳过 20 条，再获取 10 条 | | WHERE 条件 | 比较 | 等于 | WHERE age = 18 | SQL 中判断相等使用 = | | | 比较 | 不等于 | WHERE age != 18 | 也可以使用 <> | | | 比较 | 大小比较 | WHERE age >= 18 | 支持 >、<、>=、<= | | | 逻辑 | AND | WHERE age >= 18 AND age <= 25 | 多个条件同时成立 | | | 逻辑 | OR | WHERE age = 18 OR age = 20 | 任一条件成立 | | | 逻辑 | NOT | WHERE NOT age = 18 | 对条件取反 | | | 范围 | BETWEEN | WHERE age BETWEEN 18 AND 25 | 判断是否位于指定范围，包含边界 | | | 集合 | IN | WHERE id IN (1, 2, 3) | 判断值是否属于指定集合 | | | 模糊查询 | LIKE | WHERE name LIKE 'Tom%' | % 表示任意数量字符 | | | 模糊查询 | LIKE | WHERE name LIKE 'To_' | _ 表示任意一个字符 | | | NULL | 判断 NULL | WHERE age IS NULL | 不能使用 age = NULL | | | NULL | 判断非 NULL | WHERE age IS NOT NULL | 判断字段不为 NULL | | 删除方式对比 | DELETE | 删除数据 | DELETE FROM user WHERE id = 1; | 删除行，保留表结构，支持 WHERE，AUTO_INCREMENT 的计数状态通常不会重置 | | | TRUNCATE | 清空表 | TRUNCATE TABLE user; | 删除全部数据，保留表结构，不支持 WHERE，AUTO_INCREMENT 的计数状态重置 | | | DROP | 删除表 | DROP TABLE user; | 删除表结构以及全部数据 | ## 正则 > REGEXP 是 Regular Expression（正则表达式）的缩写 REGEXP 是用于进行正则表达式匹配的运算符，用于检查一个字符串是否匹配指定的正则表达式模式，
示例： 查找 name 字段中以 'st' 为开头的所有数据： `sql SELECT name FROM person_tbl WHERE name REGEXP '^st'; ` | 分类 | 语法 / 符号 | 示例 | 说明 | | -------- | --------------- | ------------------------------------ | ------------------------------------- | | 正则查询 | REGEXP | WHERE name REGEXP 'Tom' | 判断字段是否匹配正则表达式 | | | RLIKE | WHERE name RLIKE 'Tom' | REGEXP 的同义写法 | | | REGEXP_LIKE() | WHERE REGEXP_LIKE(name, 'Tom') | MySQL 8.0 推荐的正则函数写法 | | 位置匹配 | ^ | WHERE name REGEXP '^Tom' | 匹配以 Tom 开头的字符串 | | | $ | WHERE name REGEXP 'Tom | 匹配以 Tom 结尾的字符串 | | 任意字符 | . | WHERE name REGEXP 'T.m' | . 表示任意单个字符，如 Tom、Tim | | 重复匹配 | | WHERE name REGEXP 'abc' | 前一个字符出现 0 次或多次 | | | + | WHERE name REGEXP 'ab+c' | 前一个字符出现 1 次或多次 | | | ? | WHERE name REGEXP 'ab?c' | 前一个字符出现 0 次或 1 次 | | 次数匹配 | {n} | WHERE code REGEXP '[0-9]{4}' | 前一个模式恰好出现 n 次 | | | {n,} | WHERE code REGEXP '[0-9]{4,}' | 至少出现 n 次 | | | {n,m} | WHERE code REGEXP '[0-9]{2,4}' | 出现 n 到 m 次 | | 字符集合 | [abc] | WHERE name REGEXP '[abc]' | 匹配 a、b、c 中任意一个字符 | | | [a-z] | WHERE name REGEXP '[a-z]' | 匹配指定字符范围 | | | [0-9] | WHERE code REGEXP '[0-9]' | 匹配数字 | | 排除字符 | [^abc] | WHERE name REGEXP '[^abc]' | 匹配不是 a、b、c 的字符 | | 多条件 | | | WHERE name REGEXP 'Tom|Jack' | 匹配 Tom 或 Jack | | 分组 | () | WHERE name REGEXP '^(Tom|Jack) | 将多个正则模式组合 | | 数字 | [[:digit:]] | WHERE code REGEXP '[[:digit:]]' | 匹配数字字符 | | 字母 | [[:alpha:]] | WHERE name REGEXP '[[:alpha:]]' | 匹配字母 | | 字母数字 | [[:alnum:]] | WHERE code REGEXP '[[:alnum:]]' | 匹配字母或数字 | | 空白字符 | [[:space:]] | WHERE content REGEXP '[[:space:]]' | 匹配空格、换行等空白字符 | ### 常见示例 `SQL -- 查询以 Tom 开头的用户名 SELECT FROM user WHERE name REGEXP '^Tom'; ` `sql -- 查询以 3 个数字结尾的数据 SELECT FROM user WHERE code REGEXP '[0-9]{3} ; ` ## 聚合 | 聚合函数 | 作用 | 示例 | | --------- | -------- | ------------ | | COUNT() | 统计数量 | COUNT() | | SUM() | 求和 | SUM(score) | | AVG() | 求平均值 | AVG(score) | | MAX() | 求最大值 | MAX(score) | | MIN() | 求最小值 | MIN(score) | ## 分组 | 聚合函数 | 作用 | 示例 | | ---------- | ------------------------------------------------------------ | ------------------------------------------------------------ | | GROUP BY | 分组 | SELECT class_name FROM score GROUP BY class_name; | | HAVING | 如果要过滤的不是原始数据，而是分组之后的结果，就要使用 HAVING | SELECT class_name, AVG(score) AS avg_score FROM score GROUP BY class_name HAVING AVG(score) > 83; | HAVING 示例：查询平均分大于 83 的班级 | id | student_name | class_name | gender | score | | ---- | ------------ | ---------- | ------ | ----- | | 1 | Tom | A | 男 | 80 | | 2 | Jack | A | 男 | 90 | | 3 | Lucy | A | 女 | 85 | | 4 | Jerry | B | 男 | 70 | | 5 | Alice | B | 女 | 95 | > 如果要过滤的不是原始数据，而是分组之后的结果，就要使用 HAVING 思路： 1. 对班级进行分组，分为A班和B班 2. 计算每个班级的平均分，A班的平均分，B班的平均分 3. 筛选平均分大于83的班级 由于是对分组后的结果进行处理，需使用 having `sql SELECT class_name, AVG(score) AS avg_score FROM score GROUP BY class_name HAVING avg_score > 83; ` ## 多表查询 >笛卡尔积：《离散数学》知识点详解：
集合的运算（笛卡尔积）_哔哩哔哩_bilibili > >SQL关系代数：【关系代数】并、
交、差与广义笛卡尔积_哔哩哔哩_bilibili 当一个查询需要同时使用多张表中的数据时，就属于多表查询，理解多表查询，可以先从 笛卡尔积 开始： image-20260930173655859 假设有两张表： ### S 学生表 | Sno | Sname | | ---- | ----- | | 001 | Tom | | 002 | Jack | ### SC 成绩表 | Sno | Course | Score | | ---- | ------ | ----- | | 001 | MySQL | 90 | | 001 | Java | 85 | | 002 | MySQL | 80 | 如果直接查询两张表： `sql SELECT S., SC. FROM S, SC; ` 没有设置任何关联条件，此时会得到两张表的 笛卡尔积 如果： `text S 表有 m 行 SC 表有 n 行 ` 那么理论上会得到： `text m × n 行 ` 因为 S 表中的每一行都会分别与 SC 表中的每一行进行组合 例如： `text S.001 × SC.001 MySQL S.001 × SC.001 Java S.001 × SC.002 MySQL S.002 × SC.001 MySQL S.002 × SC.001 Java S.002 × SC.002 MySQL ` 总共： `text 2 × 3 = 6 行 ` image-20260930173553140 将两张表进行笛卡尔积，得到的是第一张表每行数据与另一张表所有数据合并的结果，
如上图所示，S表的006数据与SC表的每行数据进行合并，得到了5行数据，
总共20行数据，后续的多表查询便是再次基础上进行处理，而且实际业务中，我们通常并不需要所有组合，
而只需要存在关系的数据，如学生编号相同时才认为两条数据有关联： 1. 多表进行笛卡尔积 `sql select S., SC. from S, SC; ` 便能得到上图中的20行大表，后续对这张大表进行业务处理即可（看作一张表） 2. 后续业务处理，如：学生编号相同 `sql select S., SC. from S, SC where S.Sno = SC.Sno; ` 这种写法属于较早的隐式连接写法，实际开发中更推荐明确使用 JOIN `sql SELECT S., SC. FROM S INNER JOIN SC ON S.Sno = SC.Sno; ` 这两种写法在这里表达的查询含义基本相同： `sql FROM S, SC WHERE S.Sno = SC.Sno ` 等价理解为： `sql FROM S INNER JOIN SC ON S.Sno = SC.Sno ` 需要注意： “先生成完整笛卡尔积，再使用 WHERE 筛选”适合作为理解模型 但 MySQL 实际执行查询时，优化器通常不会真的先生成一张巨大的笛卡尔积临时表，再进行筛选，
而会选择更高效的连接算法和执行计划 ## 连接 JOIN > MYSQL面试——left join 、inner join 、right join区别_哔哩哔哩_bilibili JOIN 用于：根据连接条件，将多张表中满足条件的行组合成新的结果集 例如： `sql S.Sno = SC.Sno ` 就是一个连接条件 常见 JOIN 包括： | 类型 | 含义 | | ------------ | ---------------------------------------- | | CROSS JOIN | 所有行互相组合，即笛卡尔积 | | INNER JOIN | 只保留满足连接条件的数据 | | LEFT JOIN | 保留左表全部数据，同时连接右表匹配的数据 | | RIGHT JOIN | 保留右表全部数据，同时连接左表匹配的数据 | 维恩图： image-20260930193639960 ### CROSS JOIN CROSS JOIN 就是笛卡尔积 `sql SELECT FROM S CROSS JOIN SC; ` 如果： `text S：4 行 SC：5 行 ` 结果： `text 4 × 5 = 20 行 ` 因此： `sql FROM S, SC ` 可以理解为一种隐式的 CROSS JOIN ### 左连接 左连接的核心规则是： > 左表全部保留，右表能匹配就连接，左表不能匹配项填 NULL image-20260930193836186 image-20260930194059506 将 from Student 的 Student 作为左表，left join SC 的 SC 作为右表，两者进行左连接 `sql from A（左表） LEFT JOIN B（右表） ` ### 内连接 INNER JOIN 内连接：左表公共部分与右表公共部分做笛卡尔积，核心规则是： > 满足连接条件的行组合 image-20260930194720120 示例： `sql SELECT S.Sno, S.Sname, SC.Course, SC.Score FROM S INNER JOIN SC ON S.Sno = SC.Sno; ` 执行逻辑可以理解为： `text S 表 │ │ S.Sno = SC.Sno ▼ 寻找 SC 表中满足条件的数据 │ ▼ 组合匹配成功的数据 ` 例如： `text S 001 Tom 002 Jack 003 Lucy SC 001 MySQL 90 001 Java 85 002 MySQL 80 004 Java 90 ` 执行： `sql SELECT FROM S INNER JOIN SC ON S.Sno = SC.Sno; ` 得到： `text 001 Tom 001 MySQL 90 001 Tom 001 Java 85 002 Jack 002 MySQL 80 ` 其中： `text 003 Lucy 004 Java ` 不会出现在最终结果中，因为它们没有找到满足连接条件的数据 ## ON 与 WHERE 学习 JOIN 时，还需要区分： `sql ON ` 与： `sql WHERE ` ON 主要用于描述： > 两张表之间如何进行匹配 例如： `sql ON S.Sno = SC.Sno ` WHERE 主要用于描述： > 对连接完成后的结果进一步进行筛选 例如查询 MySQL 成绩大于 80 分的学生： `sql SELECT S.Sname, SC.Score FROM S INNER JOIN SC ON S.Sno = SC.Sno WHERE SC.Course = 'MySQL' AND SC.Score > 80; ` 可以理解为： `text S \ \ S.Sno = SC.Sno JOIN / SC ↓ 得到关联数据 ↓ WHERE Course = 'MySQL' AND Score > 80 ↓ 得到最终结果 ` 因此可以简单记忆： `text ON → 表与表怎么关联 WHERE → 结果还需要满足什么条件 ` 需要注意，对于外连接 LEFT JOIN / RIGHT JOIN，将条件写在 ON 还是 WHERE 中可能直接改变查询结果，因此两者不能认为完全等价 ## 多表查询整体理解 整个知识点可以整理成一条主线： `text 多表查询 │ ├── CROSS JOIN │ └── 所有数据组合：笛卡尔积 │ ├── INNER JOIN │ └── 只保留匹配成功的数据 │ ├── LEFT JOIN │ └── 左表全部保留 │ ├── 匹配成功 → 合并右表数据 │ └── 匹配失败 → 右表字段 NULL │ └── RIGHT JOIN └── 右表全部保留 ├── 匹配成功 → 合并左表数据 └── 匹配失败 → 左表字段 NULL ` 最核心的理解是： `text JOIN ≠ 单纯寻找两个集合的交集 JOIN = 根据 ON 条件寻找行与行之间的匹配关系，并将匹配的数据组合起来 ` 其中： `text INNER JOIN → 只要匹配成功的数据 LEFT JOIN → 左边全部要，右边能匹配多少就匹配多少 RIGHT JOIN → 右边全部要，左边能匹配多少就匹配多少 CROSS JOIN → 不考虑匹配关系，所有数据互相组合 ``