MySQL 训练三十题 （附答案）

最新推荐文章于 2024-06-22 11:14:00 发布
最新推荐文章于 2024-06-22 11:14:00 发布
阅读量3k 收藏 5
点赞数 2
文章标签： mysql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Fucker_about/article/details/132996879
版权

NO.1 查询 - 全表查询

教程:

​ 在 SQL 学习的起点，我们需要了解一些基本概念，包括数据库、数据表、SQL、select 查询以及全表查询。

​ 数据库是存放专门存放和管理数据的库。数据库就好比是一座大型的图书馆，而这个图书馆可以存储大量的信息。我们可以在图书馆中建立各种各样的书架，每个书架代表一个数据表。

​ 书架上会有很多本书，数据表中的每一行就相当于一本书，每一列就相当于这本书的属性，比如书的名称、书的出版日期等等。

​ SQL（Structured Query Language）是一种用于管理、操作和查询数据库的标准化语言，被广泛应用于各种类型的数据库，如 MySQL、PostgreSQL、Oracle、Microsoft SQL Server 等，本系列教程中，我们将以通用的 SQL 语法带大家入门 SQL 查询的学习。

​ SQL 的语法是简单易学的，它使用类似自然语言的结构，方便开发人员和数据库管理员进行数据库操作和管理。无论是网站应用、企业软件还是大型数据系统，SQL 都是数据库操作的基础和核心。

如何使用 SQL 从数据库中查询数据呢？

​ 首先要了解 select 查询，就好比是我们要从图书馆中找到我们感兴趣的书籍。我们可以使用 select 查询从数据表中检索所需的信息，就像是通过图书馆目录找到了我们想读的书。

​ select 查询语句有非常多的语法，本节我们学习的是最简单直接的 全表查询 。

​ 当我们使用 select * from 表名 这样的 SQL 语句时，就是在进行全表查询，它会返回数据表中的所有行，让我们可以全面了解表中的数据。

示例:

​ 让我们假设有一张名为 student 的数据表，它存储了学生的信息，包括学号、姓名、年龄等。

​ 现在，我们来使用 select 全表查询语句，查看整个学生表的内容。

数据表 student：

学号姓名年龄
101小明20
102小红22
103小刚21
104小丽19

SQL 查询语句：

select * from student;

查询结果：

学号姓名年龄
101小明20
102小红22
103小刚21
104小丽19

题目:

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

答案:

select * from student;

运行结果

idnameageclass_idscoreexam_num
1鸡哥2512.51
2鱼皮1814004
3热dog4026004
4摸FISH23604
5李阿巴1931202
6老李5635004
7李变量2443903
8王加瓦23404
9赵派森8046004
10孙加加605100.51

NO.2 查询 - 选择查询

教程:

​ 选择查询是 SQL 中最基本的查询类型，它用于从数据表中选择 特定的列或字段 ，并只返回这些列的数据值。

​ 选择查询的 SQL 语法是 SELECT {列名...}，它告诉数据库我们想要查看哪些列的数据。

​ 让我们用一个形象的比喻来理解选择查询：假设你是一名班级管理者，而你的班级有一张学生名单表，上面列有每个学生的姓名（name）、年龄（age）、性别（gender）、分数（score）等信息。

​ 现在，你想了解你班级里的学生的姓名和年龄，因为这些信息能帮助你更好地了解每个学生的基本情况。所以，你只需从学生名单表中挑选出姓名（name）和年龄（age）这两列信息。

示例:

​ 假设有一张名为 students 的学生数据表，它存储了班级里学生的信息，包括姓名（name）、年龄（age）、性别（gender）、分数（score）等。

数据表 students：

nameagegenderscore
John18Male90
Alice17Female88
Bob19Male78
Kate18Female95

​ 现在，我们使用"选择查询"来获取所有学生的姓名（name）和性别（gender）信息，SQL 语句如下：

select name, gender from students;

查询结果：

namegender
JohnMale
AliceFemale
BobMale
KateFemale

​ 通过上述 SQL 查询语句，我们得到了学生名单表中所有学生的姓名和性别信息。

题目:

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

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

答案:

select name, age from student;

运行结果

nameage
鸡哥25
鱼皮18
热dog40
摸FISH
李阿巴19
老李56
李变量24
王加瓦23
赵派森80
孙加加60

NO.3 查询 - 别名

教程:

​ 别名是为数据表的列或查询结果中的字段取一个临时的、可读性更高的名字，使得查询结果更加直观和易读。

​ 让我们用一个形象的比喻来理解 “别名”：假设你是一名团队领导，你的团队有很多成员，每个成员都有自己的名字。为了更好地管理团队，你决定给每个成员都取一个昵称，让团队的交流更加便捷。这个昵称就是 “别名”。它并不改变成员的真实信息，只是提供了一个便于团队内部交流的称呼。

​ 在 SQL 查询中，我们可以使用别名语法 {原始字段名} as {别名} 来为查询结果的列名取一个便于理解的名称。通过使用别名，我们可以更直观地知道查询结果中每一列的含义，方便阅读和使用。

示例:

​ 假设有一张名为 employees 的数据表，它存储了团队成员的信息，包括姓名（name）、年龄（age）、职位（position）等：

数据表 employees：

nameageposition
John30Project Manager
Alice28Software Engineer
Bob32Product Designer
Kate26Marketing Specialist

​ 现在，我们使用 “别名” 来获取所有团队成员的姓名（name）和职位（position）信息，并为它们取别名为 员工姓名 和 职位名称：

-- SQL查询语句
select name as 员工姓名, position as 职位名称 from employees;

上述代码中的 as 也可以省略，比如 name 员工姓名 也是 ok 的。

查询结果，注意表格头的列名从英文变为了中文：

员工姓名职位名称
JohnProject Manager
AliceSoftware Engineer
BobProduct Designer
KateMarketing Specialist

​ 通过上述 SQL 查询语句，我们得到了团队成员名单表中所有成员的姓名和职位信息，并通过别名让查询结果更加易读和直观。

题目:

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

答案:

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

运行结果

学生姓名学生年龄
鸡哥25
鱼皮18
热dog40
摸FISH
李阿巴19
老李56
李变量24
王加瓦23
赵派森80
孙加加60

NO.4 查询 - 常量和运算

教程:

​ “常量和运算” 是 SQL 中用于对数据进行计算和处理的重要概念。在 SQL 查询中，常量指的是固定的数值或文本（比如 “1”），而运算则是对这些常量进行数学运算或字符串操作。

​ 通过常量和运算，我们可以在查询语句中对数据进行加减乘除、取平均值、连接文本等操作，从而得到我们想要的查询结果。

示例:

​ 让我们来看一个具体的 SQL 代码和结果示例，假设有一张名为 orders 的数据表，它存储了订单信息，包括订单编号（order_id）、商品单价（unit_price）、购买数量（quantity）等：

数据表orders：

order_idunit_pricequantity
100110.005
100220.003
100315.002
100425.004

现在，我们需要计算每个订单的总金额（total_amount），即商品单价（unit_price）乘以购买数量（quantity）。

SQL 查询语句如下：

select order_id, unit_price, quantity, unit_price * quantity as total_amount from orders;

​ 查询结果如下，其中 total_amount 是计算出来的新列：

order_idunit_pricequantitytotal_amount
100110.00550.00
100220.00360.00
100315.00230.00
100425.004100.00

​ 此外，SQL 可以直接把常量作为列名，比如执行下列 SQL 语句：

select 200, '篮球' as hobby;

查询结果如下：

200hobby
200篮球

题目:

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

答案:

select name, score, score * 2 as double_score from student;

运行结果

namescoredouble_score
鸡哥2.55
鱼皮400800
热dog6001200
摸FISH360720
李阿巴120240
老李5001000
李变量390780
王加瓦00
赵派森6001200
孙加加100.5201

NO.5 条件查询 - where

教程:

​ 在前面的教程中，我们每次都是对整个表的所有数据行进行查询。但有的时候，我们只想取表中的部分数据，怎么办呢？

​ 这时，我们就可以使用条件查询语法。通过条件查询，你可以从数据库中筛选出 满足特定条件 的数据行，而不是返回表中的所有数据。

​ 主要使用 where 子句在查询中设置过滤条件，只有满足这些条件的数据行才会被返回。

where 子句的语法如下：

SELECT 列1, 列2, ...
FROM 表名
WHERE 条件;

​ 其中，列1, 列2, ...是你要选择的列，可以是具体的列名，也可以是*表示选择所有列。表名是你要从中查询数据的表名。条件是指定的查询条件，可以使用比较运算符（如=、<、>等）、逻辑运算符（如AND、OR等）、IN 操作符、LIKE 操作符等来设置条件。

示例:

​ 让我们来看一个具体的SQL代码和结果示例，假设有一张名为products的数据表，它存储了产品信息，包括产品名称（name）、单价（price）、库存（stock）等：

数据表products：

namepricestock
A10.0050
B20.0030
C15.0020
D25.0010

​ 现在，我们使用 “WHERE” 来筛选出库存小于等于 20 的产品：

-- SQL查询语句
select name, price, stock from products where stock <= 20;

查询结果：

namepricestock
C15.0020
D25.0010

​ 通过上述 SQL 查询语句，我们筛选出了库存小于等于 20 的产品，从而得到了符合条件的产品信息。

题目:

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

答案:

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

运行结果

namescore
鱼皮400

NO.6 条件查询 - 运算符

教程:

​ 运算符是 SQL 中用于在条件查询中进行条件判断的特殊符号，比如 =、 !=、<、> 等。通过使用不同的运算符，我们可以在查询语句中设定多样化的条件，从而根据数据的不同属性进行灵活的筛选和过滤。

​ 假设你是一名招聘官，而数据表中的数据就像是你面试的候选人。不同的运算符就像是你设定的不同面试条件，例如你可以使用 “!=” 运算符来筛选出不符合某个条件的候选人，使用 “>” 运算符来筛选出成绩优秀的候选人，使用 “BETWEEN” 运算符来筛选出年龄在某个范围内的候选人。

示例:

​ 让我们来看一个具体的SQL代码和结果示例，假设有一张名为 employees 的数据表，它存储了员工信息，包括员工姓名（name）、年龄（age）、工资（salary）等：

数据表 employees：

nameagesalary
小明255000
小红306000
小李285500
小张224500

​ 现在，我们使用不同的运算符来进行条件查询：

1）使用 “!=” 运算符筛选出 name 不是 ‘小张’ 的员工：

-- SQL查询语句
select name, age, salary from employees where name != '小张';

查询结果：

nameagesalary
小明255000
小红306000
小李285500

2）使用 “>” 运算符筛选出工资高于 5500 的员工：

-- SQL查询语句
select name, age, salary from employees where salary > 5500;

查询结果：

nameagesalary
小红306000

3）使用 “BETWEEN” 运算符筛选出年龄在 25 到 30 之间的员工：

-- SQL查询语句
select name, age, salary from employees where age between 25 and 30;

查询结果：

nameagesalary
小明255000
小红306000
小李285500

​ 通过上述 SQL 查询语句，我们可以根据不同的运算符进行条件查询，得到了符合不同条件的员工信息。

题目:

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

答案:

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

运行结果

nameage
鸡哥25
鱼皮18
摸FISH
李阿巴19
老李56
李变量24
王加瓦23
赵派森80
孙加加60

NO.7 条件查询 - 空值

教程:

​ 在数据库中，有时候数据表的某些字段可能没有值，即为空值（NULL）。

​ 空值表示该字段的值是未知的、不存在的或者没有被填写的。在SQL查询中，我们可以使用 “IS NULL” 和 “IS NOT NULL” 来判断字段是否为空值或非空值。

​ 空值的应用场景：假设你是一名考试老师，而数据表中的数据就像是你学生们的考试成绩。当某个学生没有参加考试或者成绩尚未出来时，他的考试成绩就是空值。你可以使用 “IS NULL” 来找出没有参加考试的学生，使用 “IS NOT NULL” 来找出已经有成绩的学生。

示例:

​ 假设有一张名为 employees 的数据表，它存储了员工信息，包括员工姓名（name）、年龄（age）、入职日期（hire_date）等：

数据表 employees ：

nameagehire_date
小明252020-01-01
小红302020-02-15
小李28NULL
小张NULL2020-03-20

​ 现在，我们使用 “IS NULL” 来查询出入职日期未填写的员工：

-- SQL查询语句
select name, age from employees where hire_date is null;

查询结果：

nameage
小李28

题目:

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

答案:

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

运行结果

nameagescore
鸡哥252.5
鱼皮18400
热dog40600
李阿巴19120
老李56500
李变量24390
王加瓦230
赵派森80600
孙加加60100.5

NO.8 条件查询 - 模糊查询

教程:

​ 模糊查询是一种特殊的条件查询，它允许我们根据模式匹配来查找符合特定条件的数据，可以使用 LIKE 关键字实现模糊查询。

​ 在 LIKE 模糊查询中，我们使用通配符来代表零个或多个字符，从而能够快速地找到匹配的数据。

有如下 2 种通配符：

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

​ 模糊查询的应用场景：假设你是一名侦探，你需要根据目标人物的一部分线索信息来找到匹配的目标，比如你可以根据目标的名字中包含的关键字或字符来查找。

示例:

​ 假设有一张名为employees的数据表，它存储了员工信息，包括员工姓名（name）、年龄（age）、职位（position）等：

数据表employees：

nameageposition
张三25软件工程师
李四30数据分析师
王五28产品经理
小明22软件测试工程师

​ 现在，我们使用 LIKE 模糊查询来找出姓名（name）中包含关键字 “张” 的员工信息：

-- SQL查询语句
select name, age, position from employees where name like '%张%';

查询结果：

nameageposition
张三25软件工程师

还可以使用模糊查询匹配开头和结尾：

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

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

​ 同理，可以使用 not like 来查询不包含某关键字的信息。

题目:

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

答案:

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

运行结果

namescore
鸡哥2.5
鱼皮400
热dog600
摸FISH360
王加瓦0
赵派森600
孙加加100.5

NO.9 条件查询 - 逻辑运算

教程:

​ 逻辑运算是一种在条件查询中使用的运算符，它允许我们结合多个条件来过滤出符合特定条件的数据。

在逻辑运算中，常用的运算符有：

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

示例:

​ 假设有一张名为employees的数据表，它存储了员工信息，包括员工姓名（name）、年龄（age）、工资（salary）等：

数据表employees：

nameagesalary
张三2510000
李四3012000
李五2815000
小明228000

​ 现在，我们使用逻辑运算来找出姓名中包含关键字 “李” 且 年龄小于 30 岁的员工信息：

-- SQL查询语句
select name, age, salary from employees where name like '%李%' and age < 30;

查询结果：

nameagesalary
李五2815000

​ 上述查询语句中，我们使用 and 逻辑与运算得到了符合要求的员工信息。

题目:

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

答案:

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

运行结果

namescore
热dog600
李阿巴120
老李500
李变量390
赵派森600

NO.10 去重

教程:

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

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

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

示例:

​ 假设有一张名为students的数据表，它存储了学生信息，包括学生姓名（name）、班级ID（class_id）、考试编号（exam_num）、成绩（score）等：

数据表students：

nameclass_idexam_numscore
张三1190
李四2285
王五1192
李四2388

​ 现在，我们使用DISTINCT关键字来找出不同的班级 ID：

-- SQL 查询语句
select distinct class_id from students;

查询结果：

class_id
1
2

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

示例语法如下：

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

题目:

​ 请编写一条 SQL 查询语句，从名为 student 的数据表中选择出所有不重复的班级 ID（class_id）和考试编号（exam_num）的组合。

答案:

select distinct class_id, exam_num from student;

运行结果

class_idexam_num
11
14
24
32
34
43
44
51

NO.11 排序

教程:

​ 在查询数据时，我们有时希望对结果按照某个字段的值进行排序，以便更好地查看数据。

​ 在 SQL 中，我们可以使用 ORDER BY 关键字来实现排序操作。ORDER BY 后面跟上需要排序的字段，可以选择升序（ASC）或降序（DESC）排列。

示例:

​ 假设有一张名为 students 的数据表，它存储了学生信息，包括学生姓名（name）、年龄（age）和成绩（score）等：

数据表 students ：

nameagescore
张三1890
李四2085
王五1992
赵六2088

现在，我们使用ORDER BY关键字来对学生表进行排序：

-- SQL 查询语句 1
select name, age from students order by age asc;

-- SQL 查询语句 2
select name, score from students order by score desc;

查询语句 1 结果，按照年龄升序（从小到大）：

nameage
张三18
王五19
李四20
赵六20

查询语句 2 结果，按照分数降序（从大到小）：

namescore
王五92
张三90
赵六88
李四85

​ 在排序的基础上，我们还可以根据多个字段的值进行排序。当第一个字段的值相同时，再按照第二个字段的值进行排序，以此类推。

示例语法如下：

order by 字段1 [升序/降序], 字段2 [升序/降序], ...

题目:

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

答案:

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

运行结果

nameagescore
热dog40600
赵派森80600
老李56500
鱼皮18400
李变量24390
摸FISH360
李阿巴19120
孙加加60100.5
鸡哥252.5
王加瓦230

NO.12 截断和偏移

教程:

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

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

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

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

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

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

示例:

​ 假设有一张名为 tasks 的数据表，它存储了待办事项信息，包括任务名称（task_name）和截止日期（due_date）等。

数据表tasks：

task_namedue_date
完成报告2023-08-05
预约医生2023-08-08
购买礼物2023-08-10
安排旅行2023-08-15

现在，我们使用LIMIT关键字来进行分页查询：

-- LIMIT 后只跟一个整数，表示要截断的数据条数（一次获取几条）
select task_name, due_date from tasks limit 2;

-- LIMIT 后跟 2 个整数，依次表示从第几条数据开始、一次获取几条
select task_name, due_date from tasks limit 2, 2;

查询语句 1 结果，只获取了 2 条数据：

task_namedue_date
完成报告2023-08-05
预约医生2023-08-08

查询语句 2 结果，从下标为 2（第 3 条）数据的位置开始获取 2 条数据：

task_namedue_date
购买礼物2023-08-10
安排旅行2023-08-15

​ 通过上述 SQL 查询语句，我们分别选取了待办事项表中的前两个任务和从第三个任务开始的两个任务，实现了数据的截断和偏移。

题目:

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

答案:

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

运行结果

nameage
鱼皮18
李阿巴19
王加瓦23

NO.13 条件分支

教程:

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

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

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

示例:

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

nameage
小明18
鸡哥25
李华30
王五40

使用条件分支 case when ，根据 name 来判断学生是否会说 RAP，并起别名为 can_rap。

示例 SQL 如下：

SELECT
  name,
  CASE WHEN (name = '鸡哥') THEN '会' ELSE '不会' END AS can_rap
FROM
  student;

查询结果：

namecan_rap
小明不会
鸡哥会
李华不会
王五不会

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
      and age <= 60
    ) then  '年轻'
    else  '小同学'
    end as age_level
    from
  student
order by
  name asc ;

运行结果

nameage_level
孙加加年轻
摸FISH小同学
李变量年轻
李阿巴小同学
热dog年轻
王加瓦年轻
老李年轻
赵派森老同学
鱼皮小同学
鸡哥年轻

NO.14 时间函数

教程:

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

常用的时间函数有：

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

示例:

​ 假设有一个订单表 orders，包含以下字段：order_id（订单号）、order_date（下单日期）、order_time（下单时间）。数据如下：

order_idorder_dateorder_time
12023-08-0112:30:45
22023-08-0114:20:10
32023-08-0209:15:00
42023-08-0218:05:30

使用时间函数获取当前日期、当前日期时间和当前时间：

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

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

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

查询结果：

为了方便对比，放到同一个表格

current_datecurrent_datetimecurrent_time
2023-08-012023-08-01 14:30:0014:30:00

注意，这里的日期、日期时间和时间将根据当前的系统时间来生成，实际运行结果可能会因为当前时间而不同。

​ 还有很多时间函数，比如计算两个日期的相差天数、获取当前日期对应的毫秒数等，实际运用时自行查阅即可，此处不做赘述。

题目:

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

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

答案:

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

运行结果

name当前日期
鸡哥2023-09-18
鱼皮2023-09-18
热dog2023-09-18
摸FISH2023-09-18
李阿巴2023-09-18
老李2023-09-18
李变量2023-09-18
王加瓦2023-09-18
赵派森2023-09-18
孙加加2023-09-18

NO.15 字符串处理

教程:

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

示例:

​ 假设有一个员工表 employees，包含以下字段：id（员工编号）、name（员工姓名）。数据如下：

idname
1小明
2热dog
3Fish摸摸
4鸡哥

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

-- 将姓名转换为大写
SELECT name, UPPER(name) AS upper_name
FROM employees;

查询结果：

nameupper_name
小明小明
热dog热DOG
Fish摸摸FISH摸摸
鸡哥鸡哥

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

-- 计算姓名长度
SELECT name, LENGTH(name) AS name_length
FROM employees;

查询结果：

namename_length
小明2
热dog4
Fish摸摸6
鸡哥2

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

-- 将姓名转换为小写并进行条件筛选
SELECT name, LOWER(name) AS lower_name
FROM employees;

查询结果：

idname
1小明
2热dog
3fish摸摸
4鸡哥

题目:

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

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

答案:

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

运行结果

idnameupper_name
3热dog热DOG

NO.16 聚合函数

教程:

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

常见的聚合函数包括：

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

示例:

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

order_idcustomer_idamount
1A001100
2A002200
3A001150
4A00350

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

SELECT COUNT(*) AS order_num
FROM orders;

查询结果：

order_num
4

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

SELECT COUNT(DISTINCT customer_id) AS customer_num
FROM orders;

查询结果：

customer_num
3

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

SELECT SUM(amount) AS total_amount
FROM orders;

查询结果：

total_amount
500

题目:

​ 假设有一个学生表 student，包含以下字段：id（学号）、name（姓名）、class_id（班级编号）、score（成绩）。请你编写一个 SQL 查询，汇总学生表中所有学生的总成绩（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;

运行结果

total_scoreavg_scoremax_scoremin_score
3073307.36000

NO.17 单字段分组

教程:

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

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

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

示例:

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

order_idcustomer_idamount
1A001100
2A002200
3A001150
4A00350

1）使用分组聚合查询中每个客户的编号：

SELECT customer_id
FROM orders
GROUP BY customer_id;

查询结果：

customer_id
A001
A002
A003

2）使用分组聚合查询每个客户的下单数：

SELECT customer_id, COUNT(order_id) AS order_num
FROM orders
GROUP BY customer_id;

查询结果：

customer_idorder_num
A0012
A0021
A0031

题目:

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

答案:

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

运行结果

class_idavg_score
1201.25
2480
3310
4330
5100.5

NO.18 多字段分组

教程:

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

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

示例:

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

数据如下：

order_idproduct_idcustomer_idamount
11A001100
21A002200
31A001150
41A00350
52A00150

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

-- 查询每个班级每次考试的学生人数
SELECT customer_id, product_id, SUM(amount) AS total_amount
FROM orders
GROUP BY customer_id, product_id;

查询结果：

customer_idproduct_idtotal_amount
A0011250
A001250
A0021200
A003150

题目:

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

答案:

select class_id,exam_num,count(id) as total_num from student group by class_id,exam_num;

运行结果

class_idexam_numtotal_num
111
141
242
321
341
431
442
511

NO.19 having 子句

教程:

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

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

示例:

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

order_idcustomer_idamount
1A001100
2A002200
3A001150
4A00350

1）使用 HAVING 子句查询订单数超过 1 的客户：

SELECT customer_id, COUNT(order_id) AS order_num
FROM orders
GROUP BY customer_id
HAVING COUNT(order_id) > 1;

查询结果：

customer_idorder_num
A0012

2）使用 HAVING 子句查询订单总金额超过 100 的客户：

-- 查询总成绩超过200的班级
SELECT customer_id, SUM(amount) AS total_amount
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > 100;

查询结果：

customer_idtotal_amount
A001250
A002200

题目:

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

答案:

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

运行结果

class_idtotal_score
1402.5
2960
3620
4990

NO.20 关联查询 - cross join

教程:

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

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

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

示例:

​ 假设有一个员工表 employees，包含以下字段：emp_id（员工编号）、emp_name（员工姓名）、department（所属部门）、salary（工资）。数据如下：

emp_idemp_namedepartmentsalary
1小明技术部5000
2鸡哥财务部6000
3李华销售部4500

​ 假设还有一个部门表 departments，包含以下字段：department（部门名称）、manager（部门经理）、location（所在地）。数据如下：

departmentmanagerlocation
技术部张三上海
财务部李四北京
销售部王五广州

​ 使用 CROSS JOIN 进行关联查询，将员工表和部门表的所有行组合在一起，获取员工姓名、工资、部门名称和部门经理，示例 SQL 代码如下：

SELECT e.emp_name, e.salary, e.department, d.manager
FROM employees e
CROSS JOIN departments d;

​ 注意，在多表关联查询的 SQL 中，我们最好在选择字段时指定字段所属表的名称（比如 e.emp_name），还可以通过给表起别名（比如 employees e）来简化 SQL 语句。

查询结果：

emp_namesalarydepartmentmanager
小明5000技术部张三
小明5000财务部李四
小明5000销售部王五
鸡哥6000技术部张三
鸡哥6000财务部李四
鸡哥6000销售部王五
李华4500技术部张三
李华4500财务部李四
李华4500销售部王五

题目:

​ 假设有一个学生表 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,c.name as class_name from student s cross join class c;

运行结果

NO.21 关联查询 - inner join

教程:

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

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

示例:

​ 假设有一个员工表 employees，包含以下字段：emp_id（员工编号）、emp_name（员工姓名）、department（所属部门）、salary（工资）。数据如下：

emp_idemp_namedepartmentsalary
1小明技术部5000
2鸡哥财务部6000
3李华销售部4500

​ 假设还有一个部门表 departments，包含以下字段：department（部门名称）、manager（部门经理）、location（所在地）。数据如下：

departmentmanagerlocation
技术部张三上海
财务部李四北京
销售部王五广州
摸鱼部赵二吐鲁番

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

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

查询结果如下：

emp_namesalarydepartmentmanager
小明5000技术部张三
鸡哥6000财务部李四
李华4500销售部王五

​ 我们会发现，使用 INNER_JOIN 后，只有两个表之间存在对应关系的数据才会被放到查询结果中。

题目:

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

​ 请你编写一个 SQL 查询，根据学生表和班级表之间的班级编号进行匹配，返回学生姓名（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
join class c on s.class_id = c.id;

运行结果

student_namestudent_ageclass_idclass_nameclass_level
鸡哥251唱班优
鱼皮181唱班优
热dog402跳班良
摸FISH2跳班良
李阿巴193rap班普通
老李563rap班普通

NO.22 关联查询 - outer join

教程:

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

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

示例:

​ 假设有一个员工表 employees，包含以下字段：emp_id（员工编号）、emp_name（员工姓名）、department（所属部门）、salary（工资）。数据如下：

emp_idemp_namedepartmentsalary
1小明技术部5000
2鸡哥财务部6000
3李华销售部4500

​ 假设还有一个部门表 departments，包含以下字段：department（部门名称）、manager（部门经理）、location（所在地）。数据如下：

departmentmanagerlocation
技术部张三上海
财务部李四北京
人事部王五广州
摸鱼部赵二吐鲁番

​ 使用 LEFT JOIN 进行关联查询，根据员工表和部门表之间的部门名称进行匹配，将员工的姓名、工资以及所属部门和部门经理组合在一起，并包含所有员工的信息：

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

查询结果：

emp_namesalarydepartmentmanager
小明5000技术部张三
鸡哥6000财务部李四
李华4500销售部NULL

​ 关注下表格的最后一条数据，李华所属的销售部并没有在部门表中，但仍然返回在了结果集中，manager 为 NULL。

​ 有些数据库并不支持 RIGHT JOIN 语法，那么如何实现 RIGHT JOIN 呢？

​ 其实只需要把主表（from 后面的表）和关联表（LEFT JOIN 后面的表）顺序进行调换即可！

题目:

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

​ 请你编写一个 SQL 查询，根据学生表和班级表之间的班级编号进行匹配，返回学生姓名（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_namestudent_ageclass_idclass_nameclass_level
鸡哥251唱班优
鱼皮181唱班优
热dog402跳班良
摸FISH2跳班良
李阿巴193rap班普通
老李563rap班普通
李变量244
王加瓦234
赵派森804
孙加加605

NO.23 子查询

教程:

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

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

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

示例:

假设我们有以下两个数据表：orders 和 customers，分别包含订单信息和客户信息。

orders 表：

order_idcustomer_idorder_datetotal_amount
11012023-01-01200
21022023-01-05350
31012023-01-10120
41032023-01-15500

customers 表：

customer_idnamecity
101AliceNew York
102BobLos Angeles
103CharlieChicago

现在，我们希望查询出订单总金额 > 200 的客户的姓名和他们的订单总金额，示例 SQL 如下：

-- 主查询
SELECT name, total_amount
FROM customers
WHERE customer_id IN (
    -- 子查询
    SELECT DISTINCT customer_id
    FROM orders
    WHERE total_amount > 200
);

在上述 SQL 中，先通过子查询从订单表中过滤查询出了符合条件的客户 id，然后再根据客户 id 到客户信息表中查询客户信息，这样可以少查询很多客户信息数据。

上述语句的查询结果：

nametotal_amount
Bob350
Charlie500

题目:

​ 假设有一个学生表 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 distinct id from class);

运行结果

NO.24 子查询 - exists

教程:

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

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

示例:

​ 假设我们有以下两个数据表：orders 和 customers，分别包含订单信息和客户信息。

orders 表：

order_idcustomer_idorder_datetotal_amount
11012023-01-01200
21022023-01-05350
31012023-01-10120
41032023-01-15500

customers 表：

customer_idnamecity
101AliceNew York
102BobLos Angeles
103CharlieChicago
104赵二China

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

使用 exists 子查询的方式，SQL 代码如下：

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

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

查询结果如下：

nametotal_amount
Alice200
Bob350
Charlie500

​ 和 exists 相对的是 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

);

运行结果

NO.25 组合查询

​

教程:

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

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

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

示例:

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

table1 表：

emp_idnameagedepartment
101Alice25HR
102Bob28Finance
103Charlie22IT

table2 表：

emp_idnameagedepartment
101Alice25HR
201David27Finance
202Eve24HR
203Frank26IT

现在，我们想要合并这两张表的数据，分别执行 UNION 操作和 UNION ALL 操作。

UNION 操作：

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

UNION 操作的结果，去除了重复的行（名称为 Alice）：

nameagedepartment
Alice25HR
Bob28Finance
Charlie22IT
David27Finance
Eve24HR
Frank26IT

UNION ALL 操作：

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

结果如下，保留了重复的行：

nameagedepartment
Alice25HR
Bob28Finance
Charlie22IT
Alice25HR
David27Finance
Eve24HR
Frank26IT

题目:

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

运行结果

nameagescoreclass_id
鸡哥252.51
鱼皮184001
热dog406002
摸FISH3602
李阿巴191203
老李565003
李变量243904
王加瓦2304
赵派森806004
孙加加60100.55
新学生1201201
新学生2211802
鸡哥252.51
鱼皮184001

NO.26 开窗函数 - sum over

教程:

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

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

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

​ 本节我们先讲第一个开窗函数：sum over。

该函数用法为：

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

示例:

​ 假设我们有订单表 orders，表格数据如下：

order_idcustomer_idorder_datetotal_amount
11012023-01-01200
21022023-01-05350
31012023-01-10120
41032023-01-15500

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

示例 SQL 如下：

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

查询结果：

order_idcustomer_idorder_datetotal_amountcustomer_total_amount
11012023-01-01200320
31012023-01-10120320
21022023-01-05350350
41032023-01-15500500

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

题目:

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

运行结果

idnameagescoreclass_idclass_avg_score
1鸡哥252.51201.25
2鱼皮184001201.25
3热dog406002480
4摸FISH3602480
5李阿巴191203310
6老李565003310
7李变量243904330
8王加瓦2304330
9赵派森806004330
10孙加加60100.55100.5

NO.27 开窗函数 - sum over order by

教程:

​ 之前的教程中，我们讲到了 sum over 开窗函数，并且用它实现了分组统计。

​ 本节教程我们将学习 sum over 函数的另一种用法：sum over order by，可以实现同组内数据的 累加求和 。

示例用法如下：

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

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

示例:

​ 假设我们有订单表 orders，表格数据如下：

order_idcustomer_idorder_datetotal_amount
11012023-01-01200
21022023-01-05350
31012023-01-10120
41032023-01-15500

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

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;

结果将是：

order_idcustomer_idorder_datetotal_amountcumulative_total_amount
11012023-01-01200200
31012023-01-10120320
21022023-01-05350350
41032023-01-15500500

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

题目:

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

运行结果

idnameagescoreclass_idclass_sum_score
1鸡哥252.512.5
2鱼皮184001402.5
4摸FISH3602360
3热dog406002960
5李阿巴191203120
6老李565003620
8王加瓦23040
7李变量243904390
9赵派森806004990
10孙加加60100.55100.5

NO.28 开窗函数 - rank

教程:

本节我们来学习一个新的开窗函数 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

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

示例:

假设我们有订单表 orders，表格数据如下：

order_idcustomer_idorder_datetotal_amount
11012023-01-01200
21022023-01-05350
31012023-01-10120
41032023-01-15500

​ 现在，我们希望为每个客户的订单按照订单金额降序排名，并显示每个订单的详细信息。

SELECT 
    order_id, 
    customer_id, 
    order_date, 
    total_amount,
    RANK() OVER (PARTITION BY customer_id ORDER BY total_amount DESC) AS customer_rank
FROM
    orders;

查询结果：

order_idcustomer_idorder_datetotal_amountcustomer_rank
11012023-01-012001
31012023-01-101202
21022023-01-053501
41032023-01-155001

​ 在上面的示例中，我们使用开窗函数 RANK 来为每个客户的订单按照订单金额降序排名（customer_rank），并使用 PARTITION BY 子句按照 customer_id 进行分组，并使用 ORDER BY 子句按照 total_amount 从大到小进行排序。

​ 可以看到，开窗函数保留了原始订单的详细信息，同时计算了每个客户的订单金额排名。

题目:

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

运行结果

idnameagescoreclass_idranking
2鱼皮1840011
1鸡哥252.512
3热dog4060021
4摸FISH36022
6老李5650031
5李阿巴1912032
9赵派森8060041
7李变量2439042
8王加瓦23043
10孙加加60100.551

NO.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 用于指定生成的行号列的别名。

示例:

假设我们有订单表 orders，表格数据如下：

order_idcustomer_idorder_datetotal_amount
11012023-01-01200
21022023-01-05350
31012023-01-10120
41032023-01-15500

​ 现在，我们希望为每个客户的订单按照订单金额降序排列，并且分配一个 row_number 编号，示例 SQL 语句如下：

SELECT 
    order_id, 
    customer_id, 
    order_date, 
    total_amount,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY total_amount DESC) AS row_number
FROM
    orders;

结果将是：

order_idcustomer_idorder_datetotal_amountrow_number
41032023-01-155001
21022023-01-053501
11012023-01-012001
31012023-01-101202

​ 在上面的示例中，我们使用开窗函数 ROW_NUMBER 为每个客户的订单按照订单金额降序排列，并为每个订单分配了一个编号（row_number），并使用 PARTITION BY 子句按照 customer_id 进行分组，并使用 ORDER BY 子句按照 total_amount 进行排序。

题目:

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

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

答案:

SELECT id, name, age, score, class_id, ROW_NUMBER() OVER (PARTITION BY class_id ORDER BY score DESC) AS row_number FROM student;

运行结果

idnameagescoreclass_idrow_number
2鱼皮1840011
1鸡哥252.512
3热dog4060021
4摸FISH36022
6老李5650031
5李阿巴1912032
9赵派森8060041
7李变量2439042
8王加瓦23043
10孙加加60100.551

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

示例:

​ 以下是一个示例，假设我们有一个学生成绩表scores，其中包含学生的成绩和考试日期：

student_idexam_datescore
1012023-01-0185
1012023-01-0578
1012023-01-1092
1012023-01-1580

​ 现在我们想要查询每个学生的考试日期和上一次考试的成绩，以及下一次考试的成绩，示例 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;

结果将是：

student_idexam_datescoreprevious_scorenext_score
1012023-01-0185NULL78
1012023-01-05788592
1012023-01-10927880
1012023-01-158092NULL

​ 在上面的示例中，我们使用 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;

运行结果

idnameagescoreclass_idprev_namenext_name
2鱼皮184001鸡哥
1鸡哥252.51鱼皮
3热dog406002摸FISH
4摸FISH3602热dog
6老李565003李阿巴
5李阿巴191203老李
9赵派森806004李变量
7李变量243904赵派森王加瓦
8王加瓦2304李变量
10孙加加60100.55

源网页 地址：

提供作者：鱼皮
肖克利
关注
  • 2
    点赞
  • 踩
  • 5
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
43道MySQL面试题集合（附答案）
03-25
1. 请说下你对 MySQL 架构的了解？ 2. 一条 SQL 语句在数据库框架中的执行流程？ 3. 数据库的三范式是什么？ 4. char 和 varchar 的区别？ 5. varchar(10) 和 varchar(20) 的区别？ 6. 谈谈你对索引的理解？ 7. 索引的底层使用的是什么数据结构？ 8. 谈谈你对 B+ 树的理解？ 9. 为什么 InnoDB 存储引擎选用 B+ 树而不是 B 树呢？ 10. 谈谈你对聚簇索引的理解？ 11. 谈谈你对哈希索引的理解？ 12. 谈谈你对覆盖索引的认识？ 13. 索引的分类？ 14. 谈谈你对最左前缀原则的理解？ 15. 怎么知道创建的索引有没有被使用到？或者说怎么才可以知道这条语句运行很慢的原因? 16. 什么情况下索引会失效？即查询不走索引？ 17. 查询性能的优化方法？ 18. InnoDB 和 MyISAM 的比较？ 19. 谈谈你对水平切分和垂直切分的理解？ 20. 主从复制中涉及到哪三个线程？ 21. 主从同步的延迟原因及解决办法？ 22. 谈谈你对数据库读写分离的理解？ 23. 请你描述下事务的特性？ 24. 谈谈你对
MySQL经典50道练习题及全网最详细解析
m0_67401417的博客
08-02 1480
1.学表Student(SId,Sname,Sage,Ssex)SId学编号Sname学姓名Sage出年Ssex学性别2.课程表Course(CId,Cname,TId)CId课程编号Cname课程名称TId教师编号3.教师表Teacher(TId,Tname)TId教师编号Tname教师姓名4.成绩表SC(SId,CId,score)SId学编号CId课程编号score分数。...
MySQL操作语句练习【经典20题】
最新发布
m0_73940426的博客
06-22 830
5.请从表中查询工作是CLERK的所有人的姓名、工资、部门号、部门名称以及部门地址的信息。5.请从表中查询工作是CLERK的所有人的姓名、工资、部门号、部门名称以及部门地址的信息。4.请从表EMP中查找工资低于2000的雇员的姓名、工作、工资，并按工资降序排列。4.请从表EMP中查找工资低于2000的雇员的姓名、工作、工资，并按工资降序排列。2.请在EMP表中查找部门号在10－30之间的雇员的姓名、部门号、工资、工作。2.请在EMP表中查找部门号在10－30之间的雇员的姓名、部门号、工资、工作。
mysql中select查询强化50题（附解答）
OKkkbh的博客
11-27 2070
masql的查询操作练习题。题目多样，思路多变。嵌套查询，子查询，连接查询一篇博文一网打尽！真正强化了解数据库。
七、MySQL 多表查询详解(附练习题及答案----超详细)
Amo Xiang的博客
01-12 3124
MySQL多表查询详解及练习(附答案)
数据分析师 Mysql练习题（附答案）
AKlianwenlan的博客
09-08 7772
Mysql练习(一)（附答案） 练习题1： 现有投资表和业务员表两张表，表结构如下： #创建invest表 create table invest( created_at date, user_id varchar(20), invest_item varchar(100), investamount decimal(38,10)); #给invest插入数据 insert into invest values ("2017/11/1","A123","CFH",100000), ("2017/12
24道MySQL高频经典面试题（附答案）
10-31
yyds
sql语句练习50题(Mysql版)
Coder-Wf
09-10 3931
表名和字段 –1.学生表 Student(s_id,s_name,s_birth,s_sex) –学生编号,学生姓名, 出生年月,学生性别 –2.课程表 Course(c_id,c_name,t_id) – –课程编号, 课程名称, 教师编号 –3.教师表 Teacher(t_id,t_name) –教师编号,教师姓名 –4.成绩表 Score(s_id,c_id,s_scor...
MySQL学习：50道经典题目（含参考答案）(更新中）
weixin_42661635的博客
04-27 8929
首先根据course_id将成绩表分为两张表，筛选出" 1 "课程比" 2 "课程成绩高的学生，再与学生表关联。1. 查询" 1 "课程比" 2 "课程成绩高的学生的信息（学号、姓名、性别、出生日期）及课程分数。利用自连接，统计比当前分数高的分数有几个，排名就是几加1。使用case when语句，多个排序条件时用逗号分隔。利用课程数量，计算出所有课程数和每个学生的选课数量。先筛选与" 1 "号同学选课数量相同的同学，再剔除。10. 查询没有学全所有课程的同学的信息。选了" 1 "号同学没选的课的同学。
50道SQL练习题
皮卡丘的情绪
06-14 1万+
50道SQL练习题
Mysql精选大厂面试题附答案
02-04
Mysql精选大厂面试题附答案 MySQL数据库作发布系统的存储，一天五万条以上的增量，预计运维三年,怎么优化？ 对于大流量的网站,您采用什么样的方法来解决各页面访问量统计问题？ 如何设计一个高并发的系统 什么情况...
2023最新100道MySQL面试题（ 附答案解析）
02-07
2023最新100道MySQL面试题（ 附答案解析）2023最新100道MySQL面试题（ 附答案解析）2023最新100道MySQL面试题（ 附答案解析）2023最新100道MySQL面试题（ 附答案解析） 2023最新100道MySQL面试题（ 附答案解析） ...
比较全的MySQL面试题（ 附答案解析）
01-26
MySQL 面试题解析 MySQL 是一个关系型数据库管理系统，由瑞典 MySQL AB 公司开发，属于 Oracle 旗下产品。下面是 MySQL 面试题解析： 1. 数据保存的方式 数据可以保存在内存、文件或数据库中。保存在内存中的数据...
sql语句练习50题(含解析)
热门推荐
m0_45210394的博客
09-21 1万+
sql练习笔记
MySQL必学！！！（练习题50道精选）
weixin_52263599的博客
01-19 2685
数据表介绍–1.学生表--SId 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别–2.课程表--CId 课程编号,Cname 课程名称,TId 教师编号–3.教师表--TId 教师编号,Tname 教师姓名–4.成绩表--SId 学生编号,CId 课程编号,score 分数。
SQL语句练习（全）
weixin_45064346的博客
06-20 4988
查询全体学生的学号与姓名 查询全体学生的学号、姓名、所在系 查询全体学生的详细记录 查询全体学生的姓名及其出生年份 查询全体学生姓名、出生年份和所在院系，要求用小写字母表示系名 查询选修了课程的学生学号 查询计算机科学技术全体学生的名单 查询所有年龄在20岁以下的学生姓名及其年龄 查询考试成绩不及格的学生的学号 查询年龄在20~23（包含20、23）之间的学生姓名、系别和年龄 查询年龄不在20~23岁之间的学生姓名、系别和年龄 查询计算机科学技术系（cs）、数学系（......
MySQL练习整理（每日习题）
nppe6的博客
03-26 2135
# 1.查询员工12个月的工资总和，并起别名为ANNUAL SALARY #第一种理解方式,只计算了基本工资 SELECT employee_id,last_name,salary * 12 "ANNUAL SALARY" FROM employees; #第二种理解方式，基本工资加上了奖金 用到了IFNULL的语句 SELECT employee_id,last_name,salary * 12 * (1 + IFNULL (commission_pct,0))"ANNUAL SALARY" FR.
MySQL数据库习题
m0_59420288的博客
12-05 5128
大家好，分享一些MySQL练习题，都是自己整理的考试例题附有答案，可用作巩固知识点，也可以用来考前复习。 创建数据库： 创建teacher表： 创建class表： 创建course表 创建student表 创建score表 2.插入下图数据 有两种方式插入数据（演示两表）：例：插入class表数据 例：插入teacher表数据
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值