sql语句基础练习

目录

1.全表查询

2.选择查询

3.别名

4.常量和运算

5.条件查询 - where

6.条件查询 - 运算符

7.条件查询 - 空值

8.条件查询 - 模糊查询

9.条件查询 - 逻辑运算

10.去重

11.排序

12.截断和偏移

13.条件分支

14.时间函数

15.字符串处理

16.聚合函数

17.单字段分组

18.多字段分组

19.分组聚合 - having 子句

20.关联查询 - cross join

21.关联查询 - inner join

22.关联查询 - outer join

23.子查询

24.子查询 - exists

25.组合查询

26.开窗函数 - sum over

27.开窗函数 - sum over order by

28.开窗函数 - rank

29.开窗函数 - row_number

30.开窗函数 - lag / lead

---

1.全表查询

        请编写 SQL 查询语句，从名为 student 的数据表中查询出所有学生的信息。

select * from student

2.选择查询

        请编写一条 SQL 查询语句，从名为 student 的数据表中选择出所有学生的姓名（name）和年龄（age）信息。

        注意，所有题目的 数据列输出顺序必须和题目的要求保持一致 ！比如本题必须学生姓名（name）在前，年龄（age）在后。

select name,age from student

3.别名

        请编写一条 SQL 查询语句，从名为 student 的数据表中选择出所有学生的姓名（name）和年龄（age）信息，并为它们取别名为 学生姓名 和 学生年龄。

select name '学生姓名',age '学生年龄' from student

4.常量和运算

        请编写一条 SQL 查询语句，从名为student的数据表中选择出所有学生的姓名（name）和分数（score），并且额外计算出分数的 2 倍（double_score）。

select name,score,score*2 'double_score' from student

5.条件查询 - where

        请编写一条 SQL 查询语句，从名为student 的数据表中选择出所有学生的姓名（name）和成绩（score），要求学生姓名为 '鱼皮'。

select name,score from student where name='鱼皮'

6.条件查询 - 运算符

        请编写一条 SQL 查询语句，从名为 student 的数据表中选择出所有学生的姓名（name）和年龄（age），要求学生姓名不等于 '热dog' 。

select name,age from student where name !='热dog'

7.条件查询 - 空值

        请编写一条 SQL 查询语句，从名为 student 的数据表中选择出所有学生的姓名（name）、年龄（age）和成绩（score），要求学生年龄不为空值。

select name,age,score from student where age is not null

8.条件查询 - 模糊查询

        请编写一条 SQL 查询语句，从名为 student 的数据表中选择出所有学生的姓名（name）和成绩（score），要求姓名（name）不包含 "李" 这个字。

select name,score from student where name not like '%李%'

9.条件查询 - 逻辑运算

        请编写一条 SQL 查询语句，从名为 student 的数据表中选择出所有学生的姓名（name）、成绩（score），要求学生的姓名包含 "李"，或者成绩（score）大于 500。

select name,score from student where name like '%李%' or score>500

10.去重

        在数据表中，可能存在重复的数据记录，但如果我们想要过滤掉重复的记录，只保留不同的记录，就要使用 SQL 的去重功能。

        在 SQL 中，我们可以使用 DISTINCT 关键字来实现去重操作。

        举个应用场景：假设你是班长，要统计班级中有哪些不同的学生，而不关心他们重复出现的次数，就可以使用去重。

        除了按照单字段去重外，DISTINCT 关键字还支持根据多个字段的组合来进行去重操作，确保多个字段的组合是唯一的。

distinct 字段1, 字段2, 字段3, ...

select distinct class_id,exam_num from student

11.排序

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

select name,age,score from student order by score Desc,age Asc

12.截断和偏移

        我们先用一个比喻来引出截断和偏移的概念。

        假设你有一张待办事项清单，上面有很多任务。当你每次只想查看其中的几个任务时，会怎么办呢？

        1）你可以使用手指挡住不需要看的部分（即截断）

        2）根据任务的编号，直接翻到需要查看的位置（即偏移）

        在 SQL 中，我们使用 LIMIT 关键字来实现数据的截断和偏移。

        截断和偏移的一个典型的应用场景是分页，即网站内容很多时，用户可以根据页号每次只看部分数据。

        题目：

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

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

13.条件分支

        条件分支 case when 是 SQL 中用于根据条件进行分支处理的语法。它类似于其他编程语言中的 if else 条件判断语句，允许我们根据不同的条件选择不同的结果返回。

        使用 case when 可以在查询结果中根据特定的条件动态生成新的列或对现有的列进行转换。

        举个例子：假设你是一位餐厅的服务员，客人点了不同的菜品，而你需要根据客人点的菜来确定服务的方式。如果客人点了鱼，你会用餐具和服务方式适合吃鱼的方式来招待他们；如果客人点了牛排，你会用适合牛排的餐具和服务方式。case when 就像你根据客人点的菜品来选择不同服务方式的过程。

        题目：

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

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

 -- 请在此处输入 SQL
select
  name,
  case
    when (age > 60) then '老同学'
    when (age > 20) then '年轻'
    else '小同学'
  end as 'age_level'
from
  student
order by
  name

14.时间函数

        在 SQL 中，时间函数是用于处理日期和时间的特殊函数。它们允许我们在查询中操作和处理日期、时间、日期时间数据，从而使得在数据库中进行时间相关的操作变得更加方便和灵活。

        常用的时间函数有：

• DATE：获取当前日期
• DATETIME：获取当前日期时间
• TIME：获取当前时间

        题目：

        假设有一个学生表 student，包含以下字段：name（姓名）、age（年龄）。

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

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

15.字符串处理

        在 SQL 中，字符串处理是一类用于处理文本数据的函数。它们允许我们对字符串进行各种操作，如转换大小写、计算字符串长度以及搜索和替换子字符串等。字符串处理函数可以帮助我们在数据库中对字符串进行加工和转换，从而满足不同的需求。

        1）使用字符串处理函数 UPPER 将姓名转换为大写：

        2）使用字符串处理函数 LENGTH 计算姓名长度：

        3）使用字符串处理函数 LOWER 将姓名转换为小写：

        题目：

        假设有一个学生表 student，包含以下字段：id（学号）、name（姓名）。请你编写一个 SQL 查询，筛选出姓名为 '热dog' 的学生，展示其学号（id）、姓名（name）及其大写姓名（upper_name）。

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

16.聚合函数

        在 SQL 中，聚合函数是一类用于对数据集进行 汇总计算 的特殊函数。它们可以对一组数据执行诸如计数、求和、平均值、最大值和最小值等操作。聚合函数通常在 SELECT 语句中配合 GROUP BY 子句使用，用于对分组后的数据进行汇总分析。

        常见的聚合函数包括：

• COUNT：计算指定列的行数或非空值的数量。
• SUM：计算指定列的数值之和。
• AVG：计算指定列的数值平均值。
• MAX：找出指定列的最大值。
• MIN：找出指定列的最小值。

        假设有一个学生表 student，包含以下字段：id（学号）、name（姓名）、class_id（班级编号）、score（成绩）。请你编写一个 SQL 查询，汇总学生表中所有学生的总成绩（total_score）、平均成绩（avg_score）、最高成绩（max_score）和最低成绩（min_score）。

-- 请在此处输入 SQL
select SUM(score) 'total_score',AVG(score) 'avg_score',MAX(score) 'max_score',MIN(score) 'min_score' from student

17.单字段分组

        在 SQL 中，分组聚合是一种对数据进行分类并对每个分类进行聚合计算的操作。它允许我们按照指定的列或字段对数据进行分组，然后对每个分组应用聚合函数，如 COUNT、SUM、AVG 等，以获得分组后的汇总结果。

        举个例子：某个学校可以按照班级将学生分组，并对每个班级进行统计。查看每个班级有多少学生、每个班级的平均成绩。这样我们就能够对学校各班的学生情况有一个整体的了解，而不是单纯看个别学生的信息。

        在 SQL 中，通常使用 GROUP BY 关键字对数据进行分组。

        题目：

        假设有一个学生表 student，包含以下字段：id（学号）、name（姓名）、class_id（班级编号）、score（成绩）。请你编写一个 SQL 查询，统计学生表中的班级编号（class_id）和每个班级的平均成绩（avg_score）。

select class_id,AVG(score) 'avg_score' from student group by class_id

18.多字段分组

        有时，单字段分组并不能满足我们的需求，比如想统计学校里每个班级每次考试的学生情况，这时就可以使用多字段分组。

        多字段分组和单字段分组的实现方式几乎一致，使用 GROUP BY 语法即可。

        题目：

        假设有一个学生表 student，包含以下字段：id（学号）、name（姓名）、class_id（班级编号）、exam_num（考试次数）、score（成绩）。请你编写一个 SQL 查询，统计学生表中的班级编号（class_id），考试次数（exam_num）和每个班级每次考试的总学生人数（total_num）。

 -- 请在此处输入 SQL
select
  class_id,
  exam_num,
  COUNT(id) 'total_num'
from
  student
group by
  class_id,
  exam_num

19.分组聚合 - having 子句

        在 SQL 中，HAVING 子句用于在分组聚合后对分组进行过滤。它允许我们对分组后的结果进行条件筛选，只保留满足特定条件的分组。

        HAVING 子句与条件查询 WHERE 子句的区别在于，WHERE 子句用于在 分组之前 进行过滤，而 HAVING 子句用于在 分组之后 进行过滤。

        题目：

        假设有一个学生表 student，包含以下字段：id（学号）、name（姓名）、class_id（班级编号）、score（成绩）。请你编写一个 SQL 查询，统计学生表中班级的总成绩超过 150 分的班级编号（class_id）和总成绩（total_score）。

 -- 请在此处输入 SQL
select
  class_id,
  SUM(score) 'total_score'
from
  student
group by
  class_id
having
  SUM(score) >= 150

20.关联查询 - cross join

        在之前的教程中，我们所有的查询操作都是在单个数据表中进行的。但有时，我们可能希望在单张表的基础上，获取更多额外数据，比如获取学生表中学生所属的班级信息等。这时，就需要使用关联查询。

        在 SQL 中，关联查询是一种用于联合多个数据表中的数据的查询方式。

        其中，CROSS JOIN 是一种简单的关联查询，不需要任何条件来匹配行，它直接将左表的 每一行 与右表的 每一行 进行组合，返回的结果是两个表的笛卡尔积。

SELECT
  student.name AS student_name,
  student.age AS student_age,
  student.class_id,
  class.name AS class_name
FROM
  student
  CROSS JOIN class;

21.关联查询 - inner join

        在 SQL 中，INNER JOIN 是一种常见的关联查询方式，它根据两个表之间的关联条件，将满足条件的行组合在一起。

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

        题目：

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

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

 -- 请在此处输入 SQL
select
  student.name 'student_name',
  student.age 'student_age',
  class_id,
  class.name 'class_name',
  level 'class_level'
from
  student
  join class on student.class_id = class.id

22.关联查询 - outer join

        在 SQL 中，OUTER JOIN 是一种关联查询方式，它根据指定的关联条件，将两个表中满足条件的行组合在一起，并 包含没有匹配的行 。

        在 OUTER JOIN 中，包括 LEFT OUTER JOIN 和 RIGHT OUTER JOIN 两种类型，它们分别表示查询左表和右表的所有行（即使没有被匹配），再加上满足条件的交集部分。

        题目：

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

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

 -- 请在此处输入 SQL
select
  s.name 'student_name',
  s.age 'student_age',
  class_id,
  c.name 'class_name',
  level 'class_level'
from
  student s
  left join class c on s.class_id = c.id

23.子查询

        子查询是指在一个查询语句内部 嵌套 另一个完整的查询语句，内层查询被称为子查询。子查询可以用于获取更复杂的查询结果或者用于过滤数据。

        当执行包含子查询的查询语句时，数据库引擎会首先执行子查询，然后将其结果作为条件或数据源来执行外层查询。

        打个比方，子查询就像是在一个盒子中的盒子，外层查询是大盒子，内层查询是小盒子。执行查询时，我们首先打开小盒子获取结果，然后将小盒子的结果放到大盒子中继续处理。

        题目：

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

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

SELECT
  name,
  score,
  class_id
FROM
  student
WHERE
  class_id IN (
    SELECT
      id
    FROM
      class
  );

24.子查询 - exists

        子查询是一种强大的查询工具，它可以嵌套在主查询中，帮助我们进行更复杂的条件过滤和数据检索。

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

        题目：

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

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

 -- 请在此处输入 SQL
select
  name,
  age,
  class_id
from
  student
where
  not exists (
    SELECT
      1
    FROM
      class
    WHERE
      class.id = student.class_id
  )

25.组合查询

在 SQL 中，组合查询是一种将多个 SELECT 查询结果合并在一起的查询操作。

包括两种常见的组合查询操作：UNION 和 UNION ALL。

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

2. 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;

26.开窗函数 - sum over

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

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

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

        开窗函数：sum over。

        该函数用法为：

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

        题目：

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

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

 -- 请在此处输入 SQL
SELECT
  id,
  name,
  age,
  score,
  class_id,
  SUM(score) OVER (
    PARTITION BY
      class_id
  ) / COUNT(*) OVER (
    PARTITION BY
      class_id
  ) as class_avg_score
FROM
  student;

27.开窗函数 - sum over order by

        sum over order by，可以实现同组内数据的 累加求和 。

        示例用法如下：

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

        举一个应用场景：老师在每个班级里依次点名，每点到一个学生，老师都会记录当前已点到的学生们的分数总和。

        题目：

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

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

 -- 请在此处输入 SQL
SELECT
  id,
  name,
  age,
  score,
  class_id,
  SUM(score) OVER (
    PARTITION BY
      class_id
    ORDER BY
      score ASC
  ) as class_sum_score
FROM
  student;

28.开窗函数 - rank

        Rank 开窗函数是 SQL 中一种用于对查询结果集中的行进行 排名 的开窗函数。它可以根据指定的列或表达式对结果集中的行进行排序，并为每一行分配一个排名。在排名过程中，相同的值将被赋予相同的排名，而不同的值将被赋予不同的排名。

当存在并列（相同排序值）时，Rank 会跳过后续排名，并保留相同的排名。

        Rank 开窗函数的常见用法是在查询结果中查找前几名（Top N）或排名最高的行。

        Rank 开窗函数的语法如下：

RANK() OVER (
  PARTITION BY 列名1, 列名2, ... -- 可选，用于指定分组列
  ORDER BY 列名3 [ASC|DESC], 列名4 [ASC|DESC], ... -- 用于指定排序列及排序方式
) AS rank_column

        题目

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

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

 -- 请在此处输入 SQL
SELECT
  id,
  name,
  age,
  score,
  class_id,
  RANK() OVER (
    PARTITION BY
      class_id
    ORDER BY
      score DESC
  ) as ranking
FROM
  student;

29.开窗函数 - row_number

        Row_Number 开窗函数是 SQL 中的一种用于为查询结果集中的每一行 分配唯一连续排名 的开窗函数。

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

        Row_Number 开窗函数的语法如下（几乎和 Rank 函数一模一样）：

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

        其中，PARTITION BY子句可选，用于指定分组列，将结果集按照指定列进行分组。ORDER BY 子句用于指定排序列及排序方式，决定了计算 Row_Number 时的排序规则。AS row_number_column 用于指定生成的行号列的别名。

        题目

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

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

 -- 请在此处输入 SQL
SELECT
  id,
  name,
  age,
  score,
  class_id,
  ROW_NUMBER() OVER (
    PARTITION BY
      class_id
    ORDER BY
      score DESC
  ) as row_number
FROM
  student;

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

        题目

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

        请你编写一个 SQL 查询，返回每个学生的详细信息（字段顺序和原始表的字段顺序一致），并且按照分数降序的方式获取每个班级内的学生的前一名学生姓名（prev_name）、后一名学生姓名（next_name）。

 -- 请在此处输入 SQL
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;