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

SQL刷题

news 2025/8/12 5:37:27

0 MySQL数据库

0.1 from>join>where>group by>聚合函数>having>select>order by>limit>

MySQL 在执行 GROUP BY 之前，会先计算所有分组表达式（如 DATE_FORMAT(trans_date, '%Y-%m')），并将结果存储在临时表中。

这些表达式的计算发生在 GROUP BY 实际分组操作之前。

0.2 子查询（派生表）必须指定别名

让数据库明确如何引用子查询中的列。
避免与外层查询的命名空间冲突。
符合 SQL 标准，确保查询可移植性。

在你的查询中，别名 a 用于标识子查询生成的临时表，使外层查询能正确访问 order_date 和 customer_pref_delivery_date 列。

1 一些函数

1.1 某字段内容长度函数 char_length(content)

1.2 在某个区间 between and

1.3 round(x,2) 保留两位小数

2 JOIN

2.1 隐式内连接与left join区别(其实就是内连接与外连接的区别)

在 SQL 中，隐式内连接（Implicit Inner Join）和 LEFT JOIN（左外连接）是两种不同的表连接方式，主要区别在于如何处理未匹配的记录。以下是核心差异和示例说明：

1. 语法差异

隐式内连接：使用逗号 , 分隔表，并在 WHERE 子句中指定连接条件。

SELECT *
FROM TableA, TableB
WHERE TableA.id = TableB.id;  -- 隐式内连接条件

LEFT JOIN：使用 LEFT JOIN 关键字明确指定连接类型，并在 ON 子句中定义条件。

SELECT *
FROM TableA
LEFT JOIN TableB ON TableA.id = TableB.id;  -- 左连接条件

2. 核心区别：处理未匹配记录的方式

连接类型	未匹配记录的处理	结果集特点
隐式内连接	丢弃左表或右表中无法匹配的记录。	仅包含两表中能匹配的记录。
LEFT JOIN	保留左表的所有记录，右表未匹配的字段用 `NULL`填充。	包含左表全部记录，右表可能有 `NULL`。

3. 示例对比

假设表结构和数据如下：

TableA (Users)

id	name
1	Alice
2	Bob
3	Carol

TableB (Orders)

order_id	user_id	product
O1	1	iPhone
O2	1	Macbook
O3	3	iPad

隐式内连接结果（与显示内连接的结果是一样的）

SELECT Users.name, Orders.product
FROM Users, Orders
WHERE Users.id = Orders.user_id;

显式内连接（INNER JOIN）

SELECT Users.name, Orders.product
FROM Users
INNER JOIN Orders ON Users.id = Orders.user_id;

name	product
Alice	iPhone
Alice	Macbook
Carol	iPad

说明：Bob 因无订单记录被丢弃。

LEFT JOIN 结果

SELECT Users.name, Orders.product
FROM Users
LEFT JOIN Orders ON Users.id = Orders.user_id;

name	product
Alice	iPhone
Alice	Macbook
Carol	iPad
Bob	NULL

说明：Bob 保留在结果中，但其订单信息为 NULL。

4. 常见应用场景

隐式内连接：
- 只需要两表交集数据时（如查询有订单的用户）。
- 历史遗留 SQL 代码中常见。
LEFT JOIN：
- 查询左表的全部记录及其关联数据（如统计所有用户的订单数，包括无订单用户）。
- 筛选未匹配记录（通过 WHERE 右表字段 IS NULL）。

5. 性能差异

语法层面：隐式内连接和 INNER JOIN 关键字在逻辑上等价，性能无差异。
数据量层面：
- 内连接可能因过滤掉大量数据而更快。
- LEFT JOIN 需保留左表全部记录，可能生成更大的临时结果集。

总结

用内连接：当你只关心两表匹配的交集时，不会有null, join 就是内联等于cross join。
用 LEFT JOIN：当你需要确保左表的所有记录都被保留，无论右表是否有匹配，会有null。

2.2 CROSS JOIN与INNER

-- INNER JOIN（需要 ON 子句）
SELECT *
FROM table1
INNER JOIN table2 ON table1.id = table2.id;-- CROSS JOIN（显式语法）
SELECT *
FROM table1
CROSS JOIN table2;-- 不带 ON 子句的 JOIN（在某些数据库中会报错，在其他数据库中会执行 CROSS JOIN）
SELECT *
FROM table1
JOIN table2; -- 这种写法不规范，应该避免

3 聚合函数 – 聚合函数不可以嵌套使用

3.1 AVG(action = ‘confirmed’)

select project_id  , round(sum(e.experience_years) / count(experience_years ), 2) as average_years 
from Project p
left join Employee e on p.employee_id = e.employee_id 
group by p.project_id sum(e.experience_years) / count(experience_years )
就等价于avg(e.experience_years)