一、查询进阶 - 关联查询 - cross join将左表的每一行与右表的每一行进行组合返回的结果是两个表的笛卡尔积学生表student包含以下字段id学号、name姓名、age年龄、class_id班级编号还有一个班级表class包含以下字段id班级编号、name班级名称。将学生表和班级表的所有行组合在一起并返回学生姓名student_name、学生年龄student_age、班级编号class_id以及班级名称class_name。select s.name as student_name, s.age as student_age, s.class_id as class_id, c.name as class_name from student s cross join class c;二、关联查询 - inner join根据两个表之间的关联条件将满足条件的行组合在一起。注意INNER JOIN 只返回两个表中满足关联条件的交集部分即在两个表中都存在的匹配行。只返回两边匹配成功的行A 表有、B 表没有 →不出现B 表有、A 表没有 →不出现没有匹配的数据直接丢弃简写JOIN就等于INNER JOIN学生表student包含以下字段id学号、name姓名、age年龄、class_id班级编号。还有一个班级表class包含以下字段id班级编号、name班级名称、level班级级别。根据学生表和班级表之间的班级编号进行匹配返回学生姓名student_name、学生年龄student_age、班级编号class_id、班级名称class_name、班级级别class_level。select s.name as student_name, s.age as student_age, c.id as class_id,--!!!这里值得注意一下这里有可能会多写一个s.class_id c.name as class_name, c.level as class_level from student s join class c on s.class_id c.id三、关联查询 - outer joinOUTER JOIN 是一种关联查询方式它根据指定的关联条件将两个表中满足条件的行组合在一起并包含没有匹配的行。在 OUTER JOIN 中包括 LEFT OUTER JOIN 和 RIGHT OUTER JOIN 两种类型它们分别表示查询左表和右表的所有行即使没有被匹配再加上满足条件的交集部分LEFT [OUTER] JOIN 左外连接A LEFT JOIN B ON 条件保留左表 A 全部记录匹配上正常显示 B 表字段匹配不上B 表字段全部填 NULL学生LEFT JOIN班级所有学生都出来没有班级的学生班级相关字段为 null。RIGHT [OUTER] JOIN 右外连接保留右表 B 全部记录学生表student包含以下字段id学号、name姓名、age年龄、class_id班级编号。还有一个班级表class包含以下字段id班级编号、name班级名称、level班级级别。根据学生表和班级表之间的班级编号进行匹配返回学生姓名student_name、学生年龄student_age、班级编号class_id、班级名称class_name、班级级别class_level要求必须返回所有学生的信息即使对应的班级编号不存在select s.name as student_name, s.age as student_age, s.class_id, c.name as class_name, c.level as class_level from student s left join class c on s.class_id c.id四、子查询子查询是指在一个查询语句内部嵌套另一个完整的查询语句学生表student包含以下字段id学号、name姓名、age年龄、score分数、class_id班级编号。还有一个班级表class包含以下字段id班级编号、name班级名称。使用子查询的方式来获取存在对应班级的学生的所有数据返回学生姓名name、分数score、班级编号class_id字段select name,score,class_id from student where class_id in ( select distinct id from class )子查询 - existsexists 子查询用于检查主查询的结果集是否存在满足条件的记录它返回布尔值True 或 False而不返回实际的数据和 exists 相对的是 not exists用于查找不满足存在条件的记录。学生表student包含以下字段id学号、name姓名、age年龄、score分数、class_id班级编号。还有一个班级表class包含以下字段id班级编号、name班级名称。使用 exists 子查询的方式来获取不存在对应班级的学生的所有数据返回学生姓名name、年龄age、班级编号class_id字段。select name,age,class_id from student where not exists( select id from class where student.class_id class.id )五、组合查询包括两种常见的组合查询操作UNION 和 UNION ALL。UNION 操作它用于将两个或多个查询的结果集合并并去除重复的行。即如果两个查询的结果有相同的行则只保留一行。UNION ALL 操作它也用于将两个或多个查询的结果集合并但不去除重复的行。即如果两个查询的结果有相同的行则全部保留。学生表student包含以下字段id学号、name姓名、age年龄、score分数、class_id班级编号。还有一个新学生表student_new包含的字段和学生表完全一致。获取所有学生表和新学生表的学生姓名name、年龄age、分数score、班级编号class_id字段要求保留重复的学生记录。select name,age, score,class_id from student union all select name,age,score,class_id from student_new六、开窗函数开窗函数是一种强大的查询工具它允许我们在查询中进行对分组数据进行计算、同时保留原始行的详细信息1. sum overSUM(计算字段名) OVER (PARTITION BY 分组字段名)学生表student包含以下字段id学号、name姓名、age年龄、class_id班级编号、score分数、exam_num考试编号。返回每个学生的详细信息字段顺序和原始表的字段顺序一致并计算每个班级的学生平均分class_avg_score。select id, name, age, class_id, score, exam_num, AVG(score) OVER ( PARTITION BY class_id ) as class_avg_score from student2.sum over order by可以实现同组内数据的累加求和学生表student包含以下字段id学号、name姓名、age年龄、score分数、class_id班级编号。返回每个学生的详细信息字段顺序和原始表的字段顺序一致并且按照分数升序的方式累加计算每个班级的学生总分class_sum_scoreselect id,name,age,score,class_id, SUM(score) OVER ( PARTITION BY class_id ORDER BY score ASC ) AS class_sum_score from student3.rankRank 开窗函数是 SQL 中一种用于对查询结果集中的行进行排名的开窗函数。它可以根据指定的列或表达式对结果集中的行进行排序并为每一行分配一个排名。在排名过程中相同的值将被赋予相同的排名而不同的值将被赋予不同的排名RANK() OVER ( PARTITION BY 列名1, 列名2, ... -- 可选用于指定分组列 ORDER BY 列名3 [ASC|DESC], 列名4 [ASC|DESC], ... -- 用于指定排序列及排序方式 ) AS rank_column 其中PARTITION BY 子句可选用于指定分组列 将结果集按照指定列进行分组ORDER BY 子句用于指定排序列及排序方式 决定了计算 Rank 时的排序规则。AS rank_column 用于指定生成的 Rank 排名列的别名。学生表student包含以下字段id学号、name姓名、age年龄、score分数、class_id班级编号。返回每个学生的详细信息字段顺序和原始表的字段顺序一致并且按照分数降序的方式计算每个班级内的学生的分数排名ranking,select id,name,age,score,class_id, RANK()OVER (PARTITION BY class_id ORDER BY score DESC) AS ranking from student4.row_number用于为查询结果集中的每一行分配唯一连续排名的开窗函数。Row_Number 函数为每一行都分配一个唯一的整数值不管是否存在并列相同排序值的情况。每一行都有一个唯一的行号从 1 开始连续递增ROW_NUMBER() OVER ( PARTITION BY column1, column2, ... -- 可选用于指定分组列 ORDER BY column3 [ASC|DESC], column4 [ASC|DESC], ... -- 用于指定排序列及排序方式 ) AS row_number_column学生表student包含以下字段id学号、name姓名、age年龄、score分数、class_id班级编号。返回每个学生的详细信息字段顺序和原始表的字段顺序一致并且按照分数降序的方式给每个班级内的学生分配一个编号row_number.select id, name, age, score, class_id, row_number() over ( partition by class_id order by score desc ) as row_number from student5.lag / lead1Lag 函数Lag 函数用于获取当前行之前的某一列的值。它可以帮助我们查看上一行的数据。Lag 函数的语法如下LAG( column_name, --column_name要获取值的列名 offset --表示要向上偏移的行数。例如offset为1表示获取上一行的值 , default_value --可选参数用于指定当没有前一行时的默认值 ) OVER ( PARTITION BY partition_column ORDER BY sort_column )2Lead 函数Lead 函数用于获取当前行之后的某一列的值。它可以帮助我们查看下一行的数据。Lead 函数的语法如下LEAD( column_name, --要获取值的列名 offset --表示要向下偏移的行数 , default_value --用于指定当没有后一行时的默认值 ) OVER ( PARTITION BY partition_column ORDER BY sort_column )学生表student包含以下字段id学号、name姓名、age年龄、score分数、class_id班级编号。返回每个学生的详细信息字段顺序和原始表的字段顺序一致并且按照分数降序的方式获取每个班级内的学生的前一名学生姓名prev_name、后一名学生姓名next_name。select id,name,age,score,class_id, lag(name, 1, null) over ( partition by class_id order by score desc ) as prev_name, lead(name, 1, null) over ( partition by class_id order by score desc ) as next_name from student