- 在 MySQL 中，复合查询是指将多个查询组合在一起以实现更复杂的查询需求的操作
- 在MySQL中，一切皆表
1. 连接方式
笛卡尔乘积
- 笛卡尔乘积是指在数学中，两个集合 X 和 Y 的笛卡尔积（Cartesian product），又称直积，表示为 X × Y
- 假设有两个表 table1 和 table2，table1 中有 3 行数据，table2 中有 4 行数据，那么它们的笛卡尔乘积结果集将包含 3 * 4 = 12 行数据。
- 笛卡尔乘积特点：两张表进行穷举组合，不加过滤条件
- 注意事项：笛卡尔乘积通常会产生大量不必要的数据，因为其中很多组合可能在实际业务中并无意义。在实际查询中，一般需要通过有效的连接条件来避免产生笛卡尔乘积，以获取有意义的查询结果。
SELECT * FROM table1, table2;
- 上述查询没有指定连接条件，因此会返回 table1 和 table2 的笛卡尔乘积结果集，包含了所有可能的行组合。
内连接 - join 或 inner join
- 功能：内连接是最常用的连接方式之一，它返回两个表中满足连接条件的行的组合。只有当两个表中的连接列的值相匹配时，对应的行才会被包含在结果集中。
SELECT s.name, c.course_name
FROM students s
INNER JOIN courses c ON s.student_id = c.id; # 只有在 students 表中的 student_id 与 courses 表中的 id 相等的行才会被显示
结果：返回学生姓名和他们所选课程的名称
左外连接 - left join
- 功能：左连接返回左表中的所有行以及右表中满足连接条件的行。如果右表中没有匹配的行，则对应的列将显示为 NULL。
SELECT s.name, c.course_name
FROM students s
LEFT JOIN courses c ON s.student_id = c.id;
students 表中的所有学生都会被列出，即使他们没有对应的课程记录。
右外连接 - right join
- 功能：右连接与左连接类似，但它返回右表中的所有行以及左表中满足连接条件的行。如果左表中没有匹配的行，则对应的列将显示为 NULL。
SELECT s.name, c.course_name
FROM students s
RIGHT JOIN courses c ON s.student_id = c.id;
此查询会列出所有课程，即使某些课程没有学生选修。
全外连接 - full join
- 功能：全连接返回两个表中的所有行，无论是否有匹配的行。如果某一行在另一个表中没有匹配的行，则对应的列将显示为 NULL。
SELECT s.name, c.course_name
FROM students s
FULL JOIN courses c ON s.student_id = c.id;
结果集中将包含所有学生和所有课程的组合，对于没有匹配的情况，相应的列将填充 NULL 值。
交叉连接 - cross join
- 功能：交叉连接返回两个表的笛卡尔积，即两个表中所有行的组合，不使用任何连接条件。
SELECT * FROM students
CROSS JOIN courses;
等同于：SELECT * FROM student,courses;
自连接
- 自连接是指在同一张表上进行连接操作，即将表与自身进行连接。
- 注意：在使用自连接时，需要为表指定不同的别名，以便在查询中区分不同的表实例。
SELECT st1.name FROM student st1;
取别名时，直接在表后面空格 + 别名即可。
示例：假设有一个名为 employees 的表，其中包含 id、name、department_id 和 manager_id 等列，要查找与每个员工同部门的其他员工，可以使用自连接来实现。
SELECT e1.name AS employee_name, e2.name AS colleague_name
FROM employees e1
JOIN employees e2 ON e1.department_id = e2.department_id AND e1.id!= e2.id;
- 将 employees 表别名为 e1 和 e2，通过 department_id 列进行连接，并排除自身连接的情况（即 e1.id!= e2.id），从而获取到每个员工同部门的其他员工的姓名组合。
2. 子查询
- 一个查询的结果做另一个查询的条件
SELECT * FROM students WHERE age > (SELECT AVG(age) FROM students);
- 内部子查询 (SELECT AVG(age) FROM students) 计算出学生的平均年龄，然后外部查询根据这个平均年龄筛选出年龄大于平均年龄的学生记录。
- 即：select 出平均年龄 做 另一个 select 的条件
比较关键字 - all
- ALL：当使用 ALL 时，主查询中的条件将与子查询结果集中的所有值进行比较，只有当条件对于子查询结果集中的所有值都满足时，主查询才会返回相应的行。
SELECT * FROM students
WHERE score > ALL (SELECT min_score FROM grades WHERE grade_level = 'A');
子查询获取了 'A' 等级的最低分，主查询则筛选出成绩高于该最低分的所有学生记录。
比较关键字 - any
- ANY：与 ALL 不同，当使用 ANY 时，主查询中的条件只需与子查询结果集中的至少一个值满足比较条件，主查询就会返回相应的行。
SELECT * FROM students
WHERE score > ANY (SELECT min_score FROM grades WHERE grade_level = 'B');
只要学生的成绩高于子查询返回的 'B' 等级最低分中的任意一个值，该学生的记录就会被查询出来。
与空集的比较
- 当子查询返回的结果集为空时，使用 ALL 的条件始终为真，而使用 ANY 的条件始终为假。这是因为不存在任何值可以与主查询中的条件进行比较，对于 ALL 来说，由于没有不满足条件的值，所以条件为真；对于 ANY 来说，由于没有满足条件的值，所以条件为假。
3. 联合查询
- 联合查询用于将多个 SELECT 语句的结果集合并成一个结果集。要求这些 SELECT 语句所查询的列数必须相同，并且对应的列的数据类型要兼容。联合查询可以使用 UNION、UNION ALL 等关键字来实现。
union - 取两个结果的并集，去重
SELECT name FROM students
UNION
SELECT name FROM teachers;
查询所有学生的姓名以及所有教师的姓名，并将结果合并在一起，去除重复的行。
union all - 取两个结果的并集，不去重
SELECT name FROM students
UNION ALL
SELECT name FROM teachers;
查询所有学生的姓名以及所有教师的姓名，并将结果合并在一起。
4. 排名分析函数
并列同名间断 - rank()
语法
- RANK() OVER (ORDER BY column_name [ASC|DESC])
- 其中，column_name 是用于确定排名顺序的列，ASC 表示升序排列，DESC 表示降序排列，默认为升序。
- 根据指定列的值对结果集进行排名。但当遇到相同值时，RANK() 会分配相同的排名，且下一个不同值的行的排名会跳过相应的数量，导致排名不连续。
示例
- 假设存在一个名为 students 的表，包含 id、name 和 score 列，要根据学生的成绩对学生进行排名
SELECT id, name, score, RANK() OVER(ORDER BY score DESC) AS ranking
FROM students;
- 若有两个学生并列第一名，则下一个学生的排名为第三名。
严格顺序排名 - row_number()
语法
- ROW_NUMBER() OVER (ORDER BY column_name [ASC|DESC])
- 按照指定列的值对结果集中的行进行严格顺序的排名，会为每一行分配一个连续的排名，即使在排序列上的值相同，也会按照行的顺序依次分配不同的排名，不会出现排名相同的情况。
示例
- 假设存在一个名为 students 的表，包含 id、name 和 score 列，要根据学生的成绩对学生进行排名
SELECT id, name, score, ROW_NUMBER() OVER(ORDER BY score DESC) AS ranking
FROM students;
- 所有学生排名不会相同
并列同名不间断 - dense_rank()
语法
- DENSE_RANK() OVER(ORDER BY column_name [ASC|DESC])
其中，column_name 是用于确定排名顺序的列，ASC 表示升序排列，DESC 表示降序排列，默认为升序。- 该函数会根据指定列的值对结果集中的行进行排名。如果有多个行在排序列上的值相同，它们将共享相同的排名，并且下一个不同值的行的排名将是连续的，不会跳过任何数字。
示例
- 假设存在一个名为 students 的表，包含 id、name 和 score 列，要根据学生的成绩对学生进行排名
SELECT id, name, score, DENSE_RANK() OVER(ORDER BY score DESC) AS ranking
FROM students;
- DENSE_RANK() OVER(ORDER BY score DESC) 会根据 score 列的值对学生进行降序排名，并将排名结果作为 ranking 列添加到结果集中。
与 rank 和 row_number 区别
- 与 RANK() 函数相比，RANK() 函数在遇到相同值时会分配相同的排名，但下一个不同值的行的排名会跳过相应的数量，导致排名不连续。例如，如果有两个学生并列第一名，那么下一个学生的排名将是第三名。
- 与 ROW_NUMBER() 函数相比，ROW_NUMBER() 函数会严格按照顺序为每一行分配一个连续的排名，不会出现排名相同的情况，即使在排序列上的值相同，也会按照行的顺序依次分配不同的排名。