SQL浅复习

增(Create)

注意INTO和VALUES关键字

-- 指定字段插入多条数据
INSERT INTO `user` (username, age, email) 
VALUES 
('李四', 30, 'lisi@example.com'),
('王五', 28, 'wangwu@example.com');
-- 不指定字段插入单条数据
INSERT INTO `user` 
VALUES (NULL, '赵六', 22, 'zhaoliu@example.com', NULL);

删(Delete)

注意FROM关键字

1	DELETE FROM `user` WHERE username = '赵六';

改(Update)

注意SET关键字

1	UPDATE `user` SET age = 31, email = 'lisi_new@example.com' WHERE id = 2;

查(Read)

查的方法方式很多，下面列举常见的：

排序可以多字段，并且每个字段的升降序可以不同，如下：

SELECT * FROM products
ORDER BY prod_price DESC, prod_name ASC;

-- 汇总字段也可以进行排序
SELECT cust_name, COUNT(cust_address) AS addr_num
FROM Customers GROUP BY cust_name
ORDER BY addr_num DESC;

having在group by之后，where在group by之前；having只能针对group by或汇总(聚合)字段进行过滤

SELECT cust_name, COUNT(*) AS NumberOfOrders
FROM Customers
WHERE cust_email IS NOT NULL
GROUP BY cust_name
HAVING COUNT(*) > 1; -- 汇总字段，也可以用别名NumberOfOrders

判断是否null用is null，where的等号是单个的=，字符串是单引号''包裹的
LIKE操作符在WHERE子句中使用，作用是确定字符串是否匹配模式，支持两个通配符匹配选项：%表示任何字符出现任意次数，_表示任何字符出现一次；不要滥用通配符，通配符位于开头处匹配会非常慢。
IN操作符在WHERE子句中使用，作用是在指定的几个特定值中任选一个值；BETWEEN操作符在WHERE子句中使用，作用是选取介于某个范围内的值。
1
2
3
4
5
6
7
SELECT *
FROM products
WHERE vend_id IN ('DLL01', 'BRS01');

SELECT *
FROM products
WHERE prod_price BETWEEN 3 AND 5;

子查询

子查询可以嵌入 SELECT、INSERT、UPDATE 和 DELETE 语句中，也可以和 =、<、>、IN、BETWEEN、EXISTS 等运算符一起使用。

-- 放在where后边
select column_name [, column_name ]
from   table1 [, table2 ]
where  column_name operator
    (select column_name [, column_name ]
    from table1 [, table2 ]
    [where])
-- 放在from后边
select column_name [, column_name ]
from (select column_name [, column_name ]
      from table1 [, table2 ]
      [where]) as temp_table_name -- 必须指定别名
where  condition

连接(JOIN)和组合(UNION)

JOIN是将两个表水平放置，UNION是垂直放置；

JOIN
JOIN主要分为INNER, LEAF, RIGHT, FULL；默认是INNER JOIN
INNER JOIN可以用WHERE替代；
还有个交叉连接CROSS JOIN，是纯做笛卡尔积，不能有ON条件
UNION
所有查询的列数和列顺序必须相同，每个查询中涉及表的列的数据类型必须相同或兼容

常见函数，以MySQL为例

文本处理

LENGTH()：以字节为单位的长度；CHAR_LENGTH()：以字符为单位的长度，中文特殊符号都算1个
LOWER()、UPPER()：小写或大写
CONCAT(str1, str2, …)：字符串拼接，如果存在NULL则返回NULL
SUBSTRING(‘MySQL函数’, 1, 5)：子串，输入MySQL，索引开始是1，而非0
TRIM()：去掉首尾的空格

数值函数

ABS()
ROUND(n, decimals)：四舍五入到decimals位，省略则取整数
MOD(n, m)：n % m
RAND([seed])：0~1之间的随机浮点数
POWER(base, exponent)或POW()
SQRT()：平方根

日期和时间

可以参考JavaGuide的这一块儿

NOW()：返回当前时间，格式YYYY-MM-DD HH:MM:SS；CURDATE()/CURTIME()：仅返回当前日期或时间
DATE_FORMAT(date, format)：date是now()或原始日期字符串，支持多种分隔符；format是格式化字符串，特殊符号如下：
%Y：4位数字年份；%y：2位数字年份；
%m：2位月份，01-12；%M：月份英文全称，January；
%d：月份中的天数，01-31；%e：月份中的天数，1-31；
%H：小时，00-23；%i：分钟，00-59；%s：秒，00-59；
%W：星期英文全称，如Sunday；
1
SELECT DATE_FORMAT(NOW(), '%Y年%m月%d日 %H时%i分%s秒') AS cn_time;
Year()、Month()、Day()、Hour()、Minute()、Second()：返回一个时间的对应部分
DATE_ADD(date, INTERVAL expr unit)：给日期增加指定时间间隔

DATE_SUB(date, INTERVAL expr unit)：给日期减少指定时间间隔

1
2
3

SELECT DATE_ADD(NOW(), INTERVAL 7 DAY); → 7 天后的时间
SELECT DATE_SUB(NOW(), INTERVAL 6 MONTH); → 7 天后的时间
-- unit: DAY/MONTH/YEAR/HOUR/MINUTE/SECOND

DATEDIFF(date1, date2)：返回天数差，半开半闭区间，注意是前-后
1
SELECT DATEDIFF('2026-02-01', '2026-01-26'); -- 结果为6

TIMESTAMPDIFF(unit, start_date, end_date)：闭区间，注意是后-前

1 2	SELECT TIMESTAMPDIFF(MONTH, '2026-01-10', '2026-03-15'); -- 结果为2 -- unit: DAY/MONTH/YEAR/HOUR/MINUTE/SECOND

聚合

使用DISTINCT可以让聚合函数针对不同的值进行聚合

AVG()：返回某列的平均值，会忽略NULL
SUM()：计算某列之和

开窗函数/窗口函数

基于当前行，在一个 “窗口”（指定范围的结果集）内对数据进行计算，不会像 GROUP BY 那样合并行，而是保留每行的独立结果

LAG()和LEAD()：取前面或后面的offset列，如果不设置default参数，会返回NULL

LAG(col, offset, default) OVER (
  [PARTITION BY 分组列]  -- 可选，按指定的列进行分组（例如相同班级），在组内独立计算前一个或后一个
  ORDER BY 排序列 [ASC/DESC]  -- 必选，定义行的顺序，否则偏移无意义，不能和分组列相同
)

LEAD(col, offset, default) OVER (
  [PARTITION BY 分组列]
  ORDER BY 排序列 [ASC/DESC]
)

-- 计算当前员工与上一个同部门的员工的薪资差值
SELECT
  dept,
  name,
  salary,
  prev_salary,
  salary - prev_salary AS salary_diff_pre
  next_salary - salary AS salary_diff_nxt
FROM (
  SELECT
    dept,
    name,
    salary,
    LAG(salary, 1, salary) OVER (PARTITION BY dept ORDER BY id) AS prev_salary
    LEAD(salary, 1, salary) OVER (PARTITION BY dept ORDER BY id) AS next_salary
  FROM employee
) AS t;

聚合函数

包括：SUM/AVG/COUNT/MAX/MIN；
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW一般用于SUM函数，表示从分区的第一行到当前行的累计和

聚合函数(col) OVER (
  [PARTITION BY 分组列]  -- 可选，按列分组，如按部门
  [ORDER BY 排序列]     -- 可选，定义行顺序，用于累计聚合
  [ROWS BETWEEN 边界1 AND 边界2]  -- 可选，定义窗口范围
)

SELECT
  dept, name, salary, hire_date,
  -- 部门内累计薪资，按入职日期排序，从部门第一行到当前行
  SUM(salary) OVER (PARTITION BY dept ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS dept_cum_salary,
  -- 部门内平均薪资，整个部门的平均值，所有行都显示
  AVG(salary) OVER (PARTITION BY dept) AS dept_avg_salary,
  -- 部门内总人数
  COUNT(*) OVER (PARTITION BY dept) AS dept_total_people,
  -- 部门内最高薪资
  MAX(salary) OVER (PARTITION BY dept) AS dept_max_salary
FROM employee;

排序函数

排序函数() OVER (
  PARTITION BY 分组列  -- 可选，按列分组排名（如按部门）
  ORDER BY 排序列 [ASC/DESC]  -- 必选，定义排名顺序（如薪资降序）
)

排序主要是针对并列值的不同处理逻辑，分为如下的排序函数，主要就前两个：

ROW_NUMBER()：连续排名，并列值也会分配不同排名
RANK()：跳跃排名，并列后跳过后续排名，例如 1,2,2,4
DENSE_RANK()：密集排名，并列后不跳过，例如 1,2,2,3

SELECT
  dept, name, salary,
  -- 连续排名（即使薪资相同，排名也不同）
  ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS row_num,
  -- 跳跃排名（并列后跳过）
  RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS rank_num,
  -- 密集排名（并列后不跳过）
  DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS dense_rank_num,
FROM employee;

其他函数

IF函数：IF(condition, expr1, expr2)

expr1和expr2可以是任意数据类型，condition要返回bool。

SELECT 
    name, score,
    IF(score >= 60, 1, 0) AS is_pass
    IF(score >= 60, '及格', '不及格') AS grade_level
FROM score;

CASE WHEN函数

CASE 字段名
    WHEN 值1 THEN 结果1
    WHEN 值2 THEN 结果2
    ELSE 默认结果  -- 可选，无匹配时返回NULL
END  -- 必须以END结尾，可加别名

空值处理函数：COALESCE(参数1, 参数2, 参数3, ..., 参数N)
coalesce

SQL刷题记录

需要刷一些leetcode的SQL题目巩固SQL能力，题单在此

1. 1581. 进店却未进行过交易的顾客

保留进店的记录，和交易表进行left join，最后统计交易id为null的进店记录的数量。

select v.customer_id, count(*) as count_no_trans
from Visits v left join Transactions t
on v.visit_id = t.visit_id
where t.transaction_id is null
group by v.customer_id

2. 197. 上升的温度

自己和自己连接

select t1.id from
Weather t1 inner join Weather t2
on DateDiff(t1.recordDate, t2.recordDate) = 1
-- where TIMESTAMPDIFF(DAY, w2.RecordDate, w1.RecordDate) = 1 -- 注意TIMESTAMPDIFF是`后-前`
where t1.Temperature > t2.Temperature;

使用LAG()获取按日期排序后上一条数据的日期和温度，如果日期和温度都满足，则作为结果数据

select t.id
from (
    select 
        id,
        recordDate as curDate,
        Temperature as curTemp,
        LAG(recordDate, 1) over(order by recordDate) as lastDate,
        LAG(Temperature, 1) over (order by recordDate asc) as lastTemp
    from Weather
) as t
where t.curTemp > t.lastTemp and DateDiff(t.curDate, t.lastDate) = 1;

3. 1661. 每台机器的进程平均运行时间

方法一：自连接

-- 自连接，字段相减
select 
    a1.machine_id,
    ROUND(AVG(a2.timestamp - a1.timestamp), 3) as processing_time
from Activity a1 inner join Activity a2
on
    a1.machine_id = a2.machine_id and a1.process_id = a2.process_id and
    a1.activity_type = 'start' and a2.activity_type = 'end'
group by a1.machine_id

方法二：通过IF分支，相减操作直接变成加上相反数

-- 直接聚合，根据start或end判断是否变成负数
select
    machine_id,
    ROUND(
        SUM(
            IF(activity_type='start', -timestamp, timestamp)
        ) / COUNT(distinct process_id)
    , 3) as processing_time
from Activity
group by machine_id

4.1280. 学生们参加各科测试的次数

此题注意cross join是纯没有条件的笛卡尔积，不能有on；并且count只针对非null的值统计，如果一堆null值仍然为0

-- 必须统计e.subject_name而不是su.subject_name，因为e.subject_name才有可能是null，才会正确返回0（因为count不统计null）
select s.student_id, s.student_name, su.subject_name, count(e.subject_name) as attended_exams
from Students s 
cross join Subjects su
-- 如果左表的一行在右表中对应多条记录，那么左表的该行会被复制多次以匹配右表的每一条记录
left join Examinations e on e.student_id = s.student_id and e.subject_name = su.subject_name
group by s.student_id, su.subject_name -- 此处必须都是su.subject_name，因为此处不应该是null
order by s.student_id, su.subject_name

SQL调优

1. 慢SQL锁定方法

慢查询日志(Slow Query Log)

基于时间阈值的传统记录方式。开启需在配置文件或会话中设置slow_query_log=1，并通过long_query_time=2定义什么是慢SQL；log_queries_not_using_indexes=1 可选开启，用于记录未使用索引的查询。

自带的Performance Schema数据库中的events_statements_*系列表格

通常默认开启，即 performance_schema=ON，但会消耗一定内存。每个线程执行完sql后会写入events_statements_history(保存每个线程最近N条记录)和events_statements_history_long(保存全局最近N条记录)
events_statements_summary_by_digest是对上述原始记录的聚合，将相同结构的SQL归类统计，提供平均延时、扫描行数、执行次数等宏观数据

2. 执行计划分析

用法：Explain [SQL]，下面两个图分别是构建索引前/后Explain结果，构建了好多索引，包含常用字段的索引，以及覆盖索引、组合索引

无索引Explain结果示例

比较重要的字段

(1) type

下面是type字段可能的值，效率依次变差

system：如果表使用的引擎对于表行数统计是精确的（如：MyISAM），且表中只有一行记录的情况下，访问方法是 system ，是 const 的一种特例。
const：表中最多只有一行匹配的记录，一次查询就可以找到，常用于使用主键或唯一索引的所有字段作为查询条件。
eq_ref：只会在联表查询时出现，前一张表的行在当前这张表中只有一行与之对应。是除了system 与 const 之外最好的 join 方式，常用于使用主键或唯一索引的所有字段作为连表条件。
ref：使用普通索引作为查询条件，查询结果可能找到多个符合条件的行。

注意：普通索引的where查询或联表操作是ref；唯一索引或主键索引的where查询，是const；唯一索引或主键索引的联表是eq_ref
index_merge：查询条件用AND/OR连接多个单列索引列，但没有合适的复合索引，优化器认为「组合多个单列索引扫描后合并」的代价，比单索引扫描 / 全表扫描更低时；此时执行计划中的 key 列列出了使用到的索引。
range：对索引列进行范围查询。此时执行计划中的key列表示使用哪个索引

index：遍历整棵索引树，与ALL类似，只不过扫描的是索引。如果是聚簇索引，那和All基本没区别，但如果是非聚簇索引，它就比全表扫描快不少了，因为索引文件肯定较小。
通常发生在只查被索引的列的数据，或者查COUNT(*)，注意下面两种情况的区分：

-- 假设 city 有索引
-- type: ref，但还需要回表
-- 如果符合条件的行数特别多，则需要做非常多的随机读取，甚至可能比全表扫描更慢；MySQL会评估两者的效益
SELECT * FROM users WHERE city = 'Shanghai'; 

-- type: index
SELECT city FROM users; 
SELECT count(*) FROM users;

ALL：全表扫描。

(2) possible_keys & key

possible_keys是可能用到的索引，key是实际用到的索引。InnoDB会选择更优秀的索引来查找数据。通过对比这两个字段，可以分析查询引擎的推断。

(3) Extra

extra包含额外的查询信息，性能依次降低：

Using index：表明查询使用了覆盖索引，不用回表，直接从索引树返回结果，查询效率非常高。
Using index condition：表示查询优化器选择使用了索引条件下推这个特性，虽然未能完全覆盖（通常涉及回表），但在存储引擎层就过滤掉了不符合条件的数据。
Using where：表明查询使用了WHERE子句进行条件过滤。一般在没有使用到索引的时候会出现。

分割线，上面性能还行，下面性能慢慢变差了。
Using join buffer(Block Nested Loop, BNL)：连表查询的方式，表示当被驱动表的没有使用索引的时候，MySQL会先将驱动表读出来放到join buffer中，再遍历被驱动表与驱动表进行查询。
Using filesort：查询中的ORDER BY无法利用索引顺序，数据库需要执行额外的排序操作。如果数据量小还在内存中进行，数据量大则会产生磁盘I/O，CPU消耗较高。
Using temporary：MySQL需要创建临时表来存储查询的结果，下面两种情况均可能出现：
1. 如果单表查询包含GROUP BY、DISTINCT或者某些特定的ORDER BY组合，且这些操作涉及的列没有合适的索引覆盖，就可能出现
2. 带子查询的SQL可能会被优化器给优化掉，这样就会出现了