select补充

范围查询

# 查询考试分数在95到100分之间的学生学号和成绩
# 方法1
select stuno,stugrade from result where stugrade>=95 and stugrade<=100;
# 方法2
select stuno,stugrade from result where stugrade>=95 && stugrade<=100;
# 方法3
select stuno,stugrade from result where stugrade between 95 and 100;
# 方法4
select stuno,stugrade from result where stugrade in(95,96,97,98,99,100);

# 查询学号为7以外的学生的学号和成绩
# 方法1
select stuno,stugrade from result where stuno !=7;
# 方法2
select stuno,stugrade from result where not stuno =7;

模糊查询

# 查询姓林的同学
select stuname from student where name like '林%';#会查询到首个字为林的所有同学，不管是两个字林萍还是三个字林东恩还是几个字的琳琳琳琳..
# 查询姓林的名字为2个字的同学
select stuname from student where name like '林_';#会查询到首个字为林且后面跟着一个字的同学
# 查询中间带星字的同学
select stuname from student where name like '%星%';

多表查询

select A.name,B.teamname from A,B

不推荐，更推荐用联表查询

联表查询

有a，b两张表

内连接

内连接即查询两表的交集

只显示能一一匹配的信息

  • 三表内连接
    ts表存储了学生和老师的id
    使用两次内连接实现多对多关系的查询

左外连接left outer join（outer可省略）

查询主表以及从表中主从表交集的部分

a左连接b，即a表（主表）的项全部显示出来，b表（从表）中如果没有对应的值用NULL表示。

右外连接right outer join（outer可省略）

查询从表以及主表中主从表交集的部分

a右连接b，即b表（主表）的项全部显示出来，a表（从表）中如果没有对应的值用NULL表示。

自连接

同一张表中进行比较

  • 查找收入超过各自经理的员工的姓名

子查询

补充：https://blog.csdn.net/weixin_30410999/article/details/99574758?utm_medium=distribute.pc_relevant.none-task-blog-2defaultbaidujs_baidulandingword~default-1.pc_relevant_antiscanv2&spm=1001.2101.3001.4242.2&utm_relevant_index=2

exists

子查询中的一种特殊类型是 "exists" 子查询，用于检查主查询的结果集是否存在满足条件的记录，它返回布尔值（True 或 False），而不返回实际的数据。

现在，我们希望查询出存在订单的客户姓名和订单金额。

SELECT name, total_amount
FROM customers
WHERE EXISTS (
    -- 子查询
    SELECT 1
    FROM orders
    WHERE orders.customer_id = customers.customer_id
);

先遍历客户信息表的每一行，获取到客户编号；然后执行子查询，从订单表中查找该客户编号是否存在，如果存在则返回结果。

not exists

假设有一个学生表 student，包含以下字段：id（学号）、name（姓名）、age（年龄）、score（分数）、class_id（班级编号）。还有一个班级表 class，包含以下字段：id（班级编号）、name（班级名称）。

请你编写一个 SQL 查询，使用 exists 子查询的方式来获取 不存在对应班级的 学生的所有数据，返回学生姓名（name）、年龄（age）、班级编号（class_id）字段。

select name, age, class_id from student 
where not exists
(select class_id from class where class.id = student.class_id);

组合查询 UNION & UNION ALL

UNION 操作：它用于将两个或多个查询的结果集合并， 并去除重复的行 。即如果两个查询的结果有相同的行，则只保留一行。

UNION ALL 操作：它也用于将两个或多个查询的结果集合并， 但不去除重复的行 。即如果两个查询的结果有相同的行，则全部保留。

假设我们有以下两个数据表：table1 和 table2，分别包含不同部门的员工信息。

合并这两张表的数据

UNION

SELECT name, age, department
FROM table1
UNION
SELECT name, age, department
FROM table2;

UNION ALL

SELECT name, age, department
FROM table1
UNION ALL
SELECT name, age, department
FROM table2;

开窗函数

在 SQL 中，开窗函数是一种强大的查询工具，它允许我们在查询中进行对分组数据进行计算、 同时保留原始行的详细信息 。

开窗函数可以与聚合函数（如 SUM、AVG、COUNT 等）结合使用，但与普通聚合函数不同，开窗函数不会导致结果集的行数减少。

打个比方，可以将开窗函数想象成一种 "透视镜"，它能够将我们聚焦在某个特定的分组，同时还能看到整体的全景。

sum over

我们先讲第一个开窗函数：sum over

SUM(计算字段名) OVER (PARTITION BY 分组字段名)


SELECT 
    order_id, 
    customer_id, 
    order_date, 
    total_amount,
    SUM(total_amount) OVER (PARTITION BY customer_id) AS customer_total_amount
FROM
    orders;


在上面的示例中，我们使用开窗函数 SUM 来计算每个客户的订单总金额（customer_total_amount），并使用 PARTITION BY 子句按照customer_id 进行分组。从前两行可以看到，开窗函数保留了原始订单的详细信息，同时计算了每个客户的订单总金额。
就是在原表的基础上新增一列显示每个用户的消费总额

sum over order by

用于实现同组内数据的累加求和
有订单表orders，表格数据如下：

现在，我们希望计算每个客户的历史订单累计金额，并显示每个订单的详细信息。

SELECT 
    order_id, 
    customer_id, 
    order_date, 
    total_amount,
    SUM(total_amount) OVER (PARTITION BY customer_id ORDER BY order_date ASC) AS cumulative_total_amount
FROM
    orders;


我们使用开窗函数 SUM 来计算每个客户的历史订单累计金额（cumulative_total_amount），并使用 PARTITION BY 子句按照 customer_id 进行分组，并使用 ORDER BY 子句按照 order_date 进行排序。从结果的前两行可以看到，开窗函数保留了原始订单的详细信息，同时计算了每个客户的历史订单累计金额；相比于只用 sum over，同组内的累加列名称

排名常用函数

按各科成绩排序，并显示排名
如图是三个常用的排序类窗口函数，row_number(),rank(),dense_rank()
row_number()是连续的，即使同分排名也会有先后。
dense_rank()在同分的情况下排名相同，下一个不同分的排名+1
rank()在同分的情况下排名相同，但下一个不同分的排名+同分的数量（是第几个人就排几个）

-- 按各科成绩排序，并显示排名
SELECT sc.s_id '学号',s_name '姓名',c_name '科目',s_score '成绩',
RANK() OVER(PARTITION BY c_name ORDER BY s_score DESC) '排名' FROM score sc -- 按rank()的方式以科目为分组，根据成绩降序排列
INNER JOIN course c ON sc.c_id = c.c_id
INNER JOIN student st ON sc.s_id = st.s_id

lag/lead

开窗函数 Lag 和 Lead 的作用是获取在当前行之前或之后的行的值，这两个函数通常在需要比较相邻行数据或进行时间序列分析时非常有用。
1）Lag 函数
Lag 函数用于获取 当前行之前 的某一列的值。它可以帮助我们查看上一行的数据。
Lag 函数的语法如下：

LAG(column_name, offset, default_value) OVER (PARTITION BY partition_column ORDER BY sort_column)

参数解释：

  • column_name：要获取值的列名。
  • offset：表示要向上偏移的行数。例如，offset为1表示获取上一行的值，offset为2表示获取上两行的值，以此类推。
  • default_value：可选参数，用于指定当没有前一行时的默认值。
  • PARTITION BY和ORDER BY子句可选，用于分组和排序数据。

2）Lead 函数
Lead 函数用于获取 当前行之后 的某一列的值。它可以帮助我们查看下一行的数据。
Lead 函数的语法如下：

LEAD(column_name, offset, default_value) OVER (PARTITION BY partition_column ORDER BY sort_column)

参数解释：

  • column_name：要获取值的列名。
  • offset：表示要向下偏移的行数。例如，offset为1表示获取下一行的值，offset为2表示获取下两行的值，以此类推。
  • default_value：可选参数，用于指定当没有后一行时的默认值。
  • PARTITION BY和ORDER BY子句可选，用于分组和排序数据。

有一个学生成绩表scores，其中包含学生的成绩和考试日期：

现在我们想要查询每个学生的考试日期和上一次考试的成绩，以及下一次考试的成绩，示例 SQL 如下：

SELECT 
    student_id,
    exam_date,
    score,
    LAG(score, 1, NULL) OVER (PARTITION BY student_id ORDER BY exam_date) AS previous_score,
    LEAD(score, 1, NULL) OVER (PARTITION BY student_id ORDER BY exam_date) AS next_score
FROM
    scores;


在上面的示例中，我们使用 Lag 函数获取每个学生的上一次考试成绩（previous_score），使用 Lead 函数获取每个学生的下一次考试成绩（next_score）。如果没有上一次或下一次考试，对应的列将显示为 NULL。

有一个学生表 student，包含以下字段：id（学号）、name（姓名）、age（年龄）、score（分数）、class_id（班级编号）。
编写一个 SQL 查询，返回每个学生的详细信息（字段顺序和原始表的字段顺序一致），并且按照分数降序的方式获取每个班级内的学生的前一名学生姓名（prev_name）、后一名学生姓名（next_name）。

SELECT id, name, age, score, class_id,
LAG(name) over (PARTITION BY class_id ORDER BY score DESC) as prev_name,
LEAD(name) OVER (PARTITION BY class_id ORDER BY score DESC) AS next_name
FROM student;

posted @ 2022-04-12 17:48  ben10044  阅读(55)  评论(0)  编辑  收藏  举报