学习SQL写法 && 数据库一些用法 的笔记

JOIN

  • 数据分散到多个表时，需要用表连接来关联数据
  • 每个join操作都会生成一个中间表，根据需要去进一步操作

结构

1
2
3
SELECT 表1.字段, 表2.字段
FROM 表1
[连接类型] JOIN 表2 ON 表1.共同字段 = 表2.共同字段;

内连接

只返回两表之中满足条件的记录（交集）

1
2
3
SELECT *
FROM 表A
INNER JOIN 表B ON 表A.id = 表B.user_id; -- 可简写为 JOIN

左连接

返回左表（表 A）的 所有记录，右表（表 B）中匹配的记录，若右表无匹配则用 NULL 填充

1
2
3
SELECT *
FROM 表A
LEFT JOIN 表B ON 表A.id = 表B.user_id;

右链接

返回右表（表 B）的 所有记录，左表（表 A）中匹配的记录，若左表无匹配则用 NULL 填充

1
2
3
SELECT *
FROM 表A
RIGHT JOIN 表B ON 表A.id = 表B.user_id;

全外链接

返回两表的 所有记录，匹配的记录合并，不匹配的字段用 NULL 填充（并集）

1
2
3
SELECT *
FROM 表A
FULL OUTER JOIN 表B ON 表A.id = 表B.user_id;

MySQl不支持上面的写法，下面是替代方案

1
2
3
4
5
6
7
8
SELECT *
FROM 表A
LEFT JOIN 表B ON 条件
UNION
SELECT *
FROM 表A
RIGHT JOIN 表B ON 条件
WHERE 表A.id IS NULL; -- 去重左连接已包含的记录

多表联查

1
2
3
4
SELECT *
FROM 表A
JOIN 表B ON 表A.id = 表B.a_id
JOIN 表C ON 表B.id = 表C.b_id;

笛卡尔积

CROSS JOIN是一种用于组合两个表中所有可能的行组合的链接方式

  • 生成全部组合：如果表 A 有 m 行，表 B 有 n 行，A CROSS JOIN B 会生成 m×n 行的结果集
  • 无关联条件：不同于 INNER JOIN 或 LEFT JOIN，CROSS JOIN 不使用 ON 子句指定连接条件
  • 应用场景：通常用于生成所有可能的组合，如学生与科目的全排列、日期与产品的组合等

示例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- 学生表 Students
+------------+--------------+
| student_id | student_name |
+------------+--------------+
| 1          | Alice        |
| 2          | Bob          |
+------------+--------------+

-- 科目表 Subjects
+--------------+
| subject_name |
+--------------+
| Math         |
| Science      |
+--------------+

SELECT * 
FROM Students 
CROSS JOIN Subjects;

结果

1
2
3
4
5
6
7
8
+------------+--------------+--------------+
| student_id | student_name | subject_name |
+------------+--------------+--------------+
| 1          | Alice        | Math         |
| 1          | Alice        | Science      |
| 2          | Bob          | Math         |
| 2          | Bob          | Science      |
+------------+--------------+--------------+

例题：学生们参加各科考试的次数

聚合函数

  • 对一组数据进行计算返回单一结果值，只能在SELECT和HAVING中使用
  • 除了COUNT(*)，自动忽略NULL值

COUNT()

  • COUNT(*)：统计所有行（包括含 NULL 的行）
  • COUNT(列名)：统计该列中非 NULL 值的数量
  • COUNT(DISTINCT 列名)：统计该列中不同值的非 NULL 数量

SUM()

  • 获取数值列的总和，忽略NULL

AVG()

  • 数值列的平均值，忽略NULL

MAX()/MIN()

  • 数值列最大最小值，忽略NULL

分组

  • GROUP BY 子句用于将数据按一个或多个列分成不同的组，以便对每个组分别应用聚合函数，得到每组的汇总结果
1
2
3
SELECT 分组列, 聚合函数(其他列)
FROM 表名
GROUP BY 分组列;

日期函数

日期计算函数

  • DATE_ADD(date, INTERVAL value unit)：日期加法
  • DATE_SUB(date, INTERVAL value unit)：日期减法
  • DATEDIFF(end_date, start_date)：返回两个日期之间的天数差（end_date - start_date）

筛选数据的子句

子句执行时机作用对象是否支持聚合函数典型用法示例
WHERE在 FROM 之后、GROUP BY 之前原始表中的行❌ 不支持过滤原始数据中的行SELECT * FROM Employees WHERE salary > 50000;
ON在表连接（JOIN）时执行表连接的匹配条件❌ 不支持定义表之间的关联规则LEFT JOIN Managers ON Employees.managerId = Managers.id;
HAVING在 GROUP BY 之后、SELECT 之前分组后的聚合结果✅ 支持筛选分组后的统计结果SELECT dept_id, COUNT(*) AS cnt FROM Employees GROUP BY dept_id HAVING cnt > 5;

关键差异总结：

  1. 执行顺序：
    FROM → ON（连接时）→ WHERE → GROUP BY → HAVING → SELECT → ORDER BY

  2. 核心区别：

    • WHERE：先过滤再处理，用于原始数据的行级筛
    • ON：边连接边过滤，用于控制表连接的匹配规则（尤其在 LEFT JOIN 中保留主表行）
    • HAVING：先分组聚合再过滤，必须与 GROUP BY 配合使用，用于筛选聚合后的结果（如 COUNT、SUM 等）

  3. 特殊场景：

    • 在 LEFT JOIN 中，ON 条件不满足时会保留主表行（对应字段为 NULL），而 WHERE 会直接排除这些行
    • HAVING 可以引用 SELECT 中的别名（需注意数据库兼容性），而 WHERE 不行

数据库知识补遗

外键

  • 强制数据完整性，简化挂你先维护

学生表（Students）：student_id（主键）, name, class_id
班级表（Classes）：class_id（主键）, class_name

这里，Students.class_id 就是外键，它引用了 Classes.class_id，确保学生所属的班级必须存在于 Classes 表中

  • 不能引用不存在的数据：例如，添加一个学生时，如果指定的 class_id 在 Classes 表中不存在，数据库会拒绝这个操作
  • 不能随意删除关联数据：如果 Classes 表中的某个班级被学生引用，直接删除该班级会导致引用无效，数据库通常会阻止这种删除（或级联删除关联数据）
  • 当更新 Classes.class_id 时，外键可以自动级联更新 Students.class_id（如果配置了级联更新）
  • 当删除 Classes 中的班级时，外键可以级联删除所有关联的学生（如果配置了级联删除）