SQL实战闯关练习

前沿：SQL（Structured Query Language）是一种用于管理、操作和查询数据库的标准化语言，被广泛应用于各种类型的数据库，如 MySQL、PostgreSQL、Oracle、Microsoft SQL Server 等，是程序员必备的基础技能之一。

纯前端实现的闯关式 SQL 自学网(https://github.com/liyupi/sql-mother)

By 程序员鱼皮 ，一人全役

主线关卡

1-基础语法-查询-全表查询

SQL 查询语句：

1

select * from student;

*表示全表查询，即获取表内所有数据并打印

2-基础语法-查询-选择查询

例如要查询name和age这两列数据

SQL 查询语句：

1

select name,age from student;

3-基础语法-查询-别名

我们可以用as创建别名，即打印表格显示的列名

SQL 查询语句：

1

select name as 学生姓名 from student;

4-基础语法-查询-常量和运算

在SQL中，常量可以之=直接作为列名，但要记得加上单引号'常量'，同时可以进行基础运算如+、-、*、/

5-基础语法-条件查询-where

选择查询时得到的时整列的数据，那么当我们希望只截取其中部分数据时，就可以用where语句进行条件查询，只有满足我们条件的才会被返回输出

SQL 查询语句：

1

select name from student where age >20;

6-基础语法-条件查询-运算符

同样是用where语句进行筛选过滤，只不过可以用运算符如 =、 !=、<、> 等

SQL 查询语句：

1

select name from student where name!='小李';

7-基础语法-条件查询-空值

• 灵活运用is null和is not null来构造条件

SQL 查询语句：

1

select name,age from student where score is null;

8-基础语法-条件查询-模糊查询

模糊查询可以用来检索符合条件的数据，可以用like关键字来实现

在like模糊查询中，用通配符来表示零个或多个字符：

• 百分号（%）：表示任意长度的任意字符序列。
• 下划线（_）：表示任意单个字符。

1. 例如，我们用like模糊查询来查找name中含有关键字“张”的学生信息:

SQL 查询语句：

1

select name,age from student where name like '%张%';

2. 还可以查询符合关键字开头或结尾的数据：

SQL 查询语句：

1
2
3
4
5

-- 只查询以 "张" 开头的数据行
select name,age from student where name like '张%';

-- 只查询以 "张" 结尾的数据行
select name,age from student where name like '%张';

3. 同样的，也可以用not like来查询不包含关键字的数据

SQL 查询语句：

1

select name,age from student where name not like '%张%';

9-基础语法-条件查询-逻辑查询

逻辑运算允许我们使用多个条件来筛选数据，常用的运算符有：

• AND：表示逻辑与，要求同时满足多个条件，才返回 true。
• OR：表示逻辑或，要求满足其中任意一个条件，就返回 true。
• NOT：表示逻辑非，用于否定一个条件（本来是 true，用了 not 后转为 false）

例如用and逻辑与运算，SQL 查询语句如下：

1

select name,age from student where name not like '%张%' and age >20;

10-基础语法-去重-distinct

有时候数据表中可能存在重复的记录，这时候就可以用distinct关键字来过滤重复项，只保留不同的记录，即去重

举个例子，用distinct找出不同的班级id

SQL查询语句：

1

select distinct class_id from students;

• 除了按单字段去重，distinct关键字还支持多个字段的组合去重，确保多个字段的组合是唯一的，语法:distinct 字段1, 字段2, 字段3, ...

例如，选择出所有不重复的class_id和exam_num的组合，SQL查询语句：

1

select distinct class_id,exam_num from student;

11-基础语法-排序

一般和order by搭配使用

• 升序：asc
• 降序：desc

SQL查询语句：

1

select name, age from students order by age asc;

则按照年龄升序（从小到大）：

12-基础语法-截断和偏移

• 用limit关键字进行截断，只获取我们需要的长度

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

SQL查询语句：

1

select name, age from students order by age asc limit 1,3;

注：limit后第一个数字表示起始下标2（第3条），第二个数字表示一次截取多少行

13-基础语法-条件分支

条件分支case when类似于if else，对某个列进行条件对比，并把结果存储到新的列中，如法如下

1
2
3
4

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

举个例子吧，比较形象一点

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

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

SQL查询语句：

1
2
3
4

select name,case when ( age >60 ) then '老同学' 
 when (age >20) then '年轻'
else '小同学' end as age_level 
from student order by name asc;

14-函数-时间函数

几个常用的时间函数：

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

1
2
3
4
5
6
7
8

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

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

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

SQL查询语句：

1

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

15-函数-字符串处理

我们可以用特定的函数对字符串进行处理，常用的有：

• upper：转大写
• lower：转小写
• length：求字符串长度

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

SQL查询语句：

1

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

16-函数-聚合函数

在 SQL 中，聚合函数是对数据集进行 汇总计算 的特殊函数。可以进行如计数、求和、平均值、最大值和最小值等操作，通常在 SELECT 语句中配合 group by使用

常见的聚合函数包括：

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

示例

假设有一个订单表 orders，包含以下字段：order_id（订单号）、customer_id（客户编号）、amount（订单金额）。数据如下：

1）使用聚合函数 COUNT 计算订单表中的总订单数：

1
2

SELECT COUNT(*) AS order_num
FROM orders;

查询结果：

2）使用聚合函数 COUNT(DISTINCT 列名) 计算订单表中不同客户的数量：

1
2

SELECT COUNT(DISTINCT customer_id) AS customer_num
FROM orders;

查询结果：

3）使用聚合函数 SUM 计算总订单金额：

1
2

SELECT SUM(amount) AS total_amount
FROM orders;

查询结果：

题目

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

SQL查询语句：

1
2
3
4
5
6
7

SELECT
  SUM(score) AS total_score,
  AVG(score) AS avg_score,
  MAX(score) AS max_score,
  MIN(score) AS min_score
FROM
  student;

17-分组聚合-单子段分组

通常用group by关键字对数据分组，相当于是以什么为单位进行筛选

SQL查询语句：

1

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

18-分组聚合-多字段分组

还是用group by关键字，只不过改成了2个变量（或两个以上）的排列组合

假设有一个订单表 orders，包含以下字段：order_id（订单号）、product_id（商品编号）、customer_id（客户编号）、amount（订单金额）。

数据如下：

要查询使用多字段分组查询表中 每个客户 购买的 每种商品 的总金额，相当于按照客户编号和商品编号分组：

1
2
3
4

-- 查询每个用户购买的每种商品的总金额，按照客户编号和商品编号分组
SELECT customer_id, product_id, SUM(amount) AS total_amount
FROM orders
GROUP BY customer_id, product_id;

查询结果：

19-分组聚合-having子句

having子句用于在分组聚合之后对分组进行条件过滤

• having和where的区别在于，where是在分组前过滤，而having用于在分组后过滤

SQL查询语句：

1

select class_id,sum(score) as sum_score from student group by class_id having sum(score)>150;

20-查询进阶-关联查询-cross join

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

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

请你编写一个 SQL 查询，将学生表和班级表的所有行组合在一起，并返回学生姓名（student_name）、学生年龄（student_age）、班级编号（class_id）以及班级名称（class_name）。

SQL查询语句：

1

select s.name student_name, s.age student_age, s.class_id class_id, c.name class_name from student s, class c;

21-查询进阶-关联查询-inner join

• 与cross join不同，inner join只返回两个表中满足关联条件的交集部分

使用 INNER JOIN 进行关联查询，根据员工表和部门表之间的公共字段 部门名称（department） 进行匹配，将员工的姓名、工资以及所属部门和部门经理组合在一起：

1
2
3

SELECT e.emp_name, e.salary, e.department, d.manager
FROM employees e
JOIN departments d ON e.department = d.departmen

22-查询进阶-关联查询-outer join

outer join分为 left outer join和 right outer join,允许将两个表中满足条件的行组合在一起，并 包含没有匹配的行 ，赋值为Null

23-查询进阶-子查询

子查询就是嵌套查询，示例如下：

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

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

1

select name, score, class_id from student where class_id in (select distinct id from class);

24-查询进阶-子查询-exists

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

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

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

1

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

25-查询进阶-组合查询

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

包括两种常见的组合查询操作：union 和 union all

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

1
2
3
4
5
6

-- UNION ALL操作
SELECT name, age, department
FROM table1
UNION ALL
SELECT name, age, department
FROM table2;