SQL 查询技巧与应用

最新推荐文章于 2024-08-25 14:40:36 发布

一日三餐384

最新推荐文章于 2024-08-25 14:40:36 发布

阅读量999

点赞数 16

文章标签：数据库

本文链接：https://blog.csdn.net/weixin_62647396/article/details/137938381

版权

本文详细讲解了SQL中的模糊查询、条件判断、日期和字符串函数，以及JOIN、LEFTJOIN、EXISTS、UNION和UNIONALL等操作，同时涉及窗口函数（Rank、Lag和Lead）在数据分组和前后邻居查找中的应用。

摘要由CSDN通过智能技术生成

模糊查询

百分号（%）：表示任意长度的任意字符序列。

下划线（_）：表示任意单个字符。

排序：order by desc：从大到小

从名为 student 的数据表中选择出学生姓名（name）、年龄（age）和成绩（score），首先按照成绩从大到小排序，如果成绩相同，则按照年龄从小到大排序。

select name,age,score from student order by score desc , age asc

截断和偏移：limit

从名为 student 的数据表中选择学生姓名（name）和年龄（age），按照年龄从小到大排序，从第 2 条数据开始、截取 3 个学生的信息。

select name,age from student order by age limit 1,3

条件分支：case when

CASE WHEN (条件1) THEN 结果1
	   WHEN (条件2) THEN 结果2
	   ...
	   ELSE 其他结果 END

假设有一个学生表 student，包含以下字段：name（姓名）、age（年龄）。请你编写一个 SQL 查询，将学生按照年龄划分为三个年龄等级（age_level）：60 岁以上为 "老同学"，20 岁以上（不包括 60 岁以上）为 "年轻"，20 岁及以下、以及没有年龄信息为 "小同学"。

返回结果应包含学生的姓名（name）和年龄等级（age_level），并按姓名升序排序。

select name ,

case

when(age>60) then '老同学'

when( age>20) then '年轻'

else '小同学'

end as age_level

from student

order by name

日期函数

-- 获取当前日期
SELECT DATE() AS current_date;

-- 获取当前日期时间
SELECT DATETIME() AS current_datetime;

-- 获取当前时间
SELECT TIME() AS current_time;

包含以下字段：name（姓名）、age（年龄）。

请你编写一个 SQL 查询，展示所有学生的姓名（name）和当前日期（列名为 "当前日期"）。

select name,DATE() as '当前日期' from student

字符串处理

-- 将姓名转换为大写
SELECT name, UPPER(name) AS upper_name
FROM employees;
-- 计算姓名长度
SELECT name, LENGTH(name) AS name_length
FROM employees;
-- 将姓名转换为小写并进行条件筛选
SELECT name, LOWER(name) AS lower_name
FROM employees;

筛选出姓名为 '热dog' 的学生，展示其学号（id）、姓名（name）及其大写姓名（upper_name）。

select id,name,UPPER(name) as upper_name from student where name='热dog'

函数：

COUNT：计算指定列的行数或非空值的数量。

SUM：计算指定列的数值之和。

AVG：计算指定列的数值平均值。

MAX：找出指定列的最大值。

MIN：找出指定列的最小值。

汇总学生表中所有学生的总成绩（total_score）、平均成绩（avg_score）、最高成绩（max_score）和最低成绩（min_score）。

select

sum(score) as total_score,

avg(score) as avg_score,

max(score) as max_score,

min(score) as min_score from student

group by 分组

统计学生表中的班级编号（class_id）和每个班级的平均成绩（avg_score）。

select class_id,avg(score) as avg_score from student group by class_id

having 分组聚合

WHERE 子句用于在 分组之前 进行过滤，而 HAVING 子句用于在 分组之后 进行过滤。

统计学生表中班级的总成绩超过 150 分的班级编号（class_id）和总成绩（total_score）。

select class_id,sum(score) as total_score

from student group by class_id

having total_score>150

笛卡尔积连接 cross join连接

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

请你编写一个 SQL 查询，将学生表和班级表的所有行组合在一起，并返回学生姓名（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

JOIN ON 只返回两个表中满足关联条件的交集部分，即在两个表中都存在的匹配行

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

请你编写一个 SQL 查询，根据学生表和班级表之间的班级编号进行匹配，返回学生姓名（student_name）、学生年龄（student_age）、班级编号（class_id）、班级名称（class_name）、班级级别（class_level）。

select s.name student_name,

s.age student_age,

s.class_id class_id,

c.name class_name,

c.level class_level

from student s

join class c on s.class_id=c.id

LEFT JOIN

RIGHT JOIN 它们分别表示查询左表和右表的所有行（即使没有被匹配），再加上满足条件的交集部分。

请你编写一个 SQL 查询，根据学生表和班级表之间的班级编号进行匹配，返回学生姓名（student_name）、学生年龄（student_age）、班级编号（class_id）、班级名称（class_name）、班级级别（class_level），要求必须返回所有学生的信息（即使对应的班级编号不存在）。

select

s.name student_name,

s.age student_age,

s.class_id class_id,

c.name class_name,

c.level class_level

from

student s

left join class c on s.class_id = c.id;

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

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

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

请编写一条 SQL 语句，获取所有学生表和新学生表的学生姓名（name）、年龄（age）、分数（score）、班级编号（class_id）字段，要求保留重复的学生记录。

SELECT name, age, score,class_id

FROM student

UNION ALL

SELECT name, age, score,class_id

FROM student_new

计算但保留原始数据

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

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

请你编写一个 SQL 查询，返回每个学生的详细信息（字段顺序和原始表的字段顺序一致），并计算每个班级的学生平均分（class_avg_score）。

select id,name,age,score,class_id,AVG(score) OVER (PARTITION BY class_id) as class_avg_score from student

开窗函数

SUM(计算字段名) OVER (PARTITION BY 分组字段名 ORDER BY 排序字段 排序规则)

每生成一份订单，都会算一下累计的价钱总和

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

请你编写一个 SQL 查询，返回每个学生的详细信息（字段顺序和原始表的字段顺序一致），并且按照分数升序的方式累加计算每个班级的学生总分（class_sum_score）。

select id,

name,age,

score,

class_id,

SUM(score) OVER (PARTITION BY class_id ORDER BY score asc) as class_sum_score

from student

rank:排名，可以并列

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（班级编号）。

请你编写一个 SQL 查询，返回每个学生的详细信息（字段顺序和原始表的字段顺序一致），并且按照分数降序的方式计算每个班级内的学生的分数排名（ranking）。

select id,

name,age,

score,

class_id,

RANK() OVER (

PARTITION BY class_id

ORDER BY score desc

) AS ranking

from student

row_number不可以并列：

它与之前讲到的 Rank 函数，Row_Number 函数为每一行都分配一个唯一的整数值，不管是否存在并列（相同排序值）的情况。每一行都有一个唯一的行号，从 1 开始连续递增。

ROW_NUMBER() OVER (
  PARTITION BY column1, column2, ... -- 可选，用于指定分组列
  ORDER BY column3 [ASC|DESC], column4 [ASC|DESC], ... -- 用于指定排序列及排序方式
) AS row_number_column

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子句可选，用于分组和排序数据。

假设有一个学生表 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;