MySQL知识

其他要点和函数

select distinct去重
找到值为null 的列要用is而不是=
round(m, n)四舍五入，m表留n位小数，n为负数时就是保留到整数位
ifnull(m, n)如果m为null就替换为n
SUM(CASE WHEN c.action = 'confirmed' THEN 1 ELSE 0 END) / COUNT(*)
case when 语句 then v1 else v2 end 类似三目运算，然后sum对v1和v2求和
mod(id, 2) = 1取余，也能用id % 2 = 1
on u.purchase_date between p.start_date and p.end_date筛选日期在有效期内的
YEAR(mr.created_at) = 2020 and MONTH(mr.created_at) = 2提取日期中的年月为数字
(子查询) union all (子查询)合并两个查询结果集，不带all时还会去重
DATE_FORMAT(trans_date, '%Y-%m') AS month日期格式化
CONCAT(YEAR(trans_date), '-', LPAD(MONTH(trans_date), 2, '0')) AS month拼接字符串
date_sub(a.event_date, interval 1 day)日期减法
select count(distinct user_id) from Activity查询所有用户数量
WITH a AS (子集查询)之后就可以直接用这个a作为表，如果还有表就直接, b ()不用子查询
列名如果有特殊字符，要用反引号包裹
substring(name,1,1)截取子串，从第一个字符开始截取一个
upper(str) lower(str)大写小写
where conditions regexp '^DIAB1|\\sDIAB1'正则表达式

join 组合两个表

SELECT * FROM A JOIN B a ON A.id = B.user_id;

inner join 内连接，返回两个表中都存在的行（只写join默认就是内连接）
left join 左连接，返回 A 表中所有的行，即使 B 表中没有对应行
right join 右连接，返回 B 表中所有的行，即使 A 表中没有对应行
cross join 交叉连接，返回两个表中所有行的组合（from 表1, 表2 一样的效果）

【力扣第1280题，学生们参加各科测试的次数】

select
    s.student_id, s.student_name, sub.subject_name,
    -- 如果为null就替换为0
    ifnull(grouped.attended_exams, 0) as attended_exams
from
    Students s
-- 每个学生都交叉连接学科表
cross join
    Subjects sub
-- 左连接一个子表
left join (
    select student_id, subject_name, count(*) as attended_exams
    from Examinations
    -- 将考试中相同学生和学科聚合
    group by student_id, subject_name
-- 定义子查询的表名
) grouped
-- 根据学科名称和学生id，关联子查询查到的次数（这个注解必须要空一格，不然会报错）
on s.student_id = grouped.student_id and sub.subject_name = grouped.subject_name
-- 最后根据id和学科名排序
order by student_id, subject_name

limit 限制返回的行数

SELECT * FROM user LIMIT M, 10;跳过 M 行后返回十条数据
SELECT * FROM user LIMIT 10 OFFSET M

聚合函数

聚合函数的作用就是就是对有多个名字相同的结果聚合
聚合函数要搭配 group by （字段名）使用
比如这个按商品名聚合，然后把同名商品的销售额全加在一起作为总的销售额

简单来说就是，根据列名分组，把列里面属性相同的值分成组然后压成一条数据，此时保留或计算的这个属性是根据聚合函数指定的，可以是求和 sum，保留最小值 min，保留最大值 max 统计行数 count 求平均值等等

1
2
3

SELECT product, SUM(amount) AS total_sales
FROM sales
GROUP BY product;

假设有个学生表，保留每个学科的最高分

1
2
3

SELECT subject, MAX(score) AS max_score
FROM students
GROUP BY subject;

【聚合函数】

count() 统计行数。count(列名)统计非空的行数，count(*)统计所有行，常用在子查询做分母
sum() 求和
avg() 平均值
max() 最大值
min() 最小值
只写聚合函数不写group by就是按整个表作为一组

-- sum和count都是聚合函数，所以这里的confirm计算只会算对应聚合的用户
SUM(CASE WHEN c.action = 'confirmed' THEN 1 ELSE 0 END) / COUNT(*)
GROUP BY s.user_id;
-- 感觉上面case这个用if写更习惯
if(c.action = 'confirmed', 1, 0)

1
2
3

-- sum计算商品的真实平均价格
ifnull(round((sum(price * units) / sum(units)), 2),0)
group by p.product_id

【配套语法】

group by() 分组
having() 过滤分组，类似where，只能引用GROUP BY 中出现的列或聚合函数的结果（如 COUNT(), SUM(), MAX() 等）。

【1581.进店却未进行交易的顾客】

# Write your MySQL query statement below
select customer_id, count(customer_id) as count_no_trans
from Visits v
-- 上面的表是参观的客人，下面是参观时消费的客人
-- 左连接保留所有客人，没有交易的那transaction_id就为null
left join Transactions t on v.visit_id = t.visit_id
-- 选择null不能用=而是用is
where transaction_id is null
group by customer_id

【至少有5名下属的经理】（having例子）

select e1.name
from Employee e1
left join Employee e2
on e1.id = e2.managerId
group by e1.name
having count(e1.name) >= 5

【查询每个学科最高分的学生】

SELECT
    st.student_name,
    sc.subject,
    sc.score
FROM
    score sc
JOIN student st ON sc.student_id = st.student_id
WHERE
    sc.score = (
        -- 查出该学科下的最高分
        SELECT MAX(s2.score)
        FROM score s2
        WHERE s2.subject = sc.subject
    );

-- 先查询出成绩表中所有最高分的记录，再补全学生信息
SELECT
    s.student_name,  -- 学生姓名
    t.subject,       -- 学科
    t.score          -- 最高分
FROM
    score t
INNER JOIN
    student s
    ON t.student_id = s.student_id
WHERE
    (t.subject, t.score) IN (
        -- 子查询：获取每个学科的最高分
        SELECT
            subject,
            MAX(score)
        FROM
            score
        GROUP BY
            subject
    );

【即时食物配送比率】
先查询出所有用户的首次订单
用min函数筛选，然后外部连接这个子查询后用where条件筛选id和日期
然后用sum来统计所有即时订单的数量，计算比率

select
-- 在所有首次订单中计算即时订单完成率
    round(
        sum(if(d.order_date = d.customer_pref_delivery_date, 1, 0))/count(*) * 100
    ,2)
    as immediate_percentage
from Delivery d
join
(
    -- 获取所有首次订单
    select customer_id, min(order_date) as first_order_date
    from Delivery
    group by customer_id
) as d2
on d.customer_id = d2.customer_id and d.order_date = d2. first_order_date

【误区】

-- 查询用户数量
SELECT COUNT(DISTINCT player_id) FROM Activity;
-- 错误写法，这是在获取每个用户记录有几条
select count(*) from Activity group by player_id

## 日期计算函数

- `DATE_ADD()` 添加日期
- `DATE_SUB()` 减去日期

加用 DATE_ADD，减用 DATE_SUB；
单位写在 INTERVAL 后，(间隔)
动态计算别写死，
自连比较很实用。

【197.上升的温度】

```sql
-- 连接自己就行和上一天比较了
select w1.id
from Weather w1
-- 和上一天比较
left join Weather w2
-- 当前日期和上一天连接
on w1.recordDate = DATE_ADD(w2.recordDate, INTERVAL 1 DAY)
where w1.Temperature > w2.Temperature

执行顺序

先 from 再 where 最后 select

-- 所以这一段的逻辑是，先连接两个表把部门的值接进来
-- 然后按查的表，逐个员工对比
-- 如果他的工资与当前对比员工部门最高工资一致就返回
select d.name as Department, e.name as Employee, Salary
from Employee e left join Department d
on e.departmentId = d.id
where salary = (
    -- 子查询查询某个部门最高工资
    select max(salary)
    from Employee
    where departmentId = e.departmentId
)

子查询

用()包裹，可以写在where里面作为条件，或者左连接，然后用as name起一个别名

select employee_id
from Employees
where salary < 30000
    and manager_id not in (select employee_id from Employees)
order by employee_id

窗口函数

不需要像传统聚合函数那样将多行合并为单行输出。窗口函数让你可以在保留原始行数据的同时，对数据进行分组统计、排名、计算移动平均等操作。

窗口（Window）：指进行计算的一组行。这组行通常与当前行有某种关系（例如：同一部门的所有员工、当前行及其前后两行等）。
不折叠行：与普通聚合函数（如 SUM(), AVG() 配合 GROUP BY）不同，窗口函数不会减少结果集的行数。每一行输入都会对应一行输出。partition就类似原来的group by，假设统计总工资，group by就显示每个部门的工资，partition就会在每行员工后加上他部门对应的总工资
OVER 子句：这是窗口函数的标志。所有窗口函数都必须包含 OVER() 子句，用于定义窗口的范围、分区和排序规则。

窗口函数类型：

排名函数 (Ranking Functions)

用于生成排名序列。
- ROW_NUMBER(): 生成唯一的连续序号（1, 2, 3, 4…），即使值相同也不重复。
- RANK(): 生成排名，值相同时排名相同，但会跳过后续名次（1, 2, 2, 4…）。
- DENSE_RANK(): 生成排名，值相同时排名相同，不跳过后续名次（1, 2, 2, 3…）。
- NTILE(n): 将数据均匀分成 n 个桶。
聚合函数 (Aggregate Functions)

标准的聚合函数也可以作为窗口函数使用。
- SUM(), AVG(), COUNT(), MAX(), MIN()
- 用途：计算累计和、移动平均、组内总数等。
取值函数 (Value Functions)

用于访问窗口内其他行的数据。
- LAG(col, n): 获取当前行之前第 n 行的值。
- LEAD(col, n): 获取当前行之后第 n 行的值。
- FIRST_VALUE(col): 获取窗口内第一行的值。
- LAST_VALUE(col): 获取窗口内最后一行的值。
- NTH_VALUE(col, n): 获取窗口内第 n 行的值。

函数名(列) OVER (PARTITION BY 分组列 ORDER BY 排序列 [ROWS/RANGE 范围])

函数名(列) OVER (
    [PARTITION BY 列名]  -- 可选：将数据分组（类似 GROUP BY，但不折叠）
    [ORDER BY 列名]      -- 可选：在窗口内排序
    [ROWS/RANGE 子句]    -- 可选：定义窗口的物理或逻辑范围（如前几行、后几行）
)

-- 指定范围
-- range...preceding之前的行
-- range...following之后的行
AVG(sales) OVER (
    ORDER BY date
    ROWS BETWEEN 3 PRECEDING AND CURRENT ROW
)

【最后一个能进入巴士的人】

with a as(
    select person_name, weight, turn,
    -- 窗口函数，计算累计的重量，并作为一列
    sum(weight) over(order by turn) sum_weight
    from queue
)

select person_name
from a
where sum_weight <= 1000
order by turn desc
limit 1

if使用

MySQL中的if是一个函数
if(condition, true_value, false_value)第一个是条件，如果为真返回第一个值，否则返回第二个值

交换座位，奇数时最后一个人id不变

select
    -- 如果是偶数就将当前id-1
    if (id % 2 = 0, id - 1,
    -- 如果是计数要再进行判断，当前id是否等于总人数，等于就不变，不等于就加一
        if (id = (select count(distinct id) from seat), id ,id + 1 )) as id,student
from seat
order by id

变量声明

语句中只能用常量，不能用表达式

1 2	`declare M int; set M = N-1;`

模糊查询

CONCAT()是拼接字符串的函数，本意为连接
SELECT * FROM user WHERE name LIKE CONCAT('%', #{name}, '%')

#{}和${}

绝大部分都用#{}
#{}是预编译参数，可以防止 SQL 注入，MyBatis 会把 #{name} 转换成 JDBC 的 ? 占位符
${}直接字符串替换，MyBatis 直接把 ${name} 替换成你传入的值，原样插入 SQL

MySQL 的索引

索引就是记录按照一个字段的值根据字段类型进行排序，然后将排序的结果按照B+树保存方便查询
MySQL 的索引都是 B+树的结构，叶子结点每个都是一个页，然后页内的每一条记录就是真实存储的数据（按主键组织的 B+数存真实数据，其他索引需要回表，回到主键索引来查真实数据）
每个页的页号和页的最小记录主键构建 B+树的非叶子结点，也就是索引（主键索引、聚簇索引）
根据索引查找就是从根节点渐渐二分找，到达叶子结点存储的页，从页内再找对应的记录
对应 MySQL 来说，主键索引一定存在，不设置主键 MySQL 会优先选一个 Unique 键作为主键索引，这个也没有就自己生成一个默认主键DB_ROW_ID

普通索引（二级索引）

实际上是另一个 B+树，不过这个每个叶子结点存的不是数据而是索引列的值(下面的 name 就是) + 主键值
假设给 name 建立一个索引，这次不通过主键排序后二分，而是通过 name 排序（字典序）后二分查找，最后到叶子结点的时候拿到对应记录的主键来找真实数据，这样通过 name 找数据就变得非常快

联合索引

也是 B+树结构，假设为 name 列和 phone 列建立索引，叶子结点存储的就是 name 列，phone 列，和主键值，此时的排序就是先按 name 列排序，name 列相同再按 phone 列排序，直接搜索 phone 是乱序的，最后找到主键值后回表查真实数据
就假设联合索引是（a,b,c），在 a 查完之后，此时所有的相同的 a 的索引对应的 b 才是有序的，才能利用到联合索引去查 b，b 查完才能查 c，不查 a 直接查 b 是无效的，因为 b 整体无序，局部有序（这个局部就是相同的 a 的条件下，这个就是最左匹配原则）
如果只查询 a 的条件时，完全可以直接使用这个联合索引，不需要再单独为 a 建立一个索引，相当与 a 的二级索引（普通索引）
只有当 a 是等于时，b 才能利用到联合索引，只有当 a 和 b 都是等于时，c 才能利用到联合索引

减少或避免回表的操作

索引覆盖（覆盖索引）

索引中已经包含了所有查询的数据，不需要回表的查询方式叫做索引覆盖
假设是为 name 和 phone 建立联合索引，又碰巧查的数据就只有 name 和 phone，此时到叶子结点时就根本不需要回表

索引下推（索引条件下推、ICP）

正常索引的使用是在存储引擎，而条件的过滤是在 Server 层
索引下推就是把条件过滤下推到存储引擎，让其通过索引来完成
假设 name 和 phone 建立联合索引，查询语句是 select * from user where name = '张三' and phone = '%1566%'假设查到的叫张三的有一万个人，而手机号实际符合的只有 1 个人，拿就有 9999 条无关数据而且他们都是回表查出来的
索引下推后直接让存储引擎先从这一万条数据中选出合法的那一条，然后再进行回表，就只需要回表一次

索引失效

违反最左匹配原则
聚合函数不能用索引
表达式不能用索引 where id + 1 = 1100，但是修改一下where id = 1100 - 1就可以用索引了
索引类型为字符串时，用数字搜索会失效；但反过来，索引为数字，用字符串来搜索能用到。因为 MySQL 自动把字符串转换成数字
or 前是索引列但之后不是索引列，就不能用索引

但是具体用不用索引还是取决于 MySQL 的优化器，根据开销（cost）来判断，选开销最小的，如果都很大就直接全表扫描了

索引创建原则

不为离散度低的列创建索引，比如性别只有男女
只为搜索、排序或分组的列创建索引
用好联合索引
过长的字段创建前缀索引，该列的前几个字符，避免占用过大空间
频繁更新的列不作为主键或索引

知识

MySQL知识

http://www.981928.xyz/2025/11/17/MySql知识/

作者

981928

发布于

2025年11月17日

许可协议

知识点上一篇

苍穹外卖笔记下一篇