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替代
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)：给日期增加指定时间间隔

1 2	SELECT DATE_ADD(NOW(), INTERVAL 7 DAY); → 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;

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;