SQL命令详解之常用函数

目录

1 简介

2 字符串函数

2.1 字符串函数语法

2.2 字符串函数练习

3 数学函数

3.1 数学函数语法

3.2 数学函数练习

4 日期时间函数

4.1 日期时间函数语法

4.2 日期时间函数练习

5 条件函数

5.1 条件函数语法

5.2 条件函数练习

 6 总结

---

1 简介

在SQL中我们经常会用到一些字符串、数学、日期时间和条件函数，下面通过多个实际案例演示了它们的应用。例如，如何截取字符串、转换大小写、计算平均值、处理日期差值等。此外，还包含SQL练习题，如筛选无效推文、修复用户名格式、计算学生平均成绩、查找温度上升的日期、统计活跃用户数等，帮助读者加深对SQL查询和数据处理的理解。

2 字符串函数

2.1 字符串函数语法

2.2 字符串函数练习

练习一：无效的推文

表：Tweets
+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| tweet_id       | int     |
| content        | varchar |
+----------------+---------+
在 SQL 中，tweet_id 是这个表的主键。
content 只包含美式键盘上的字符，不包含其它特殊字符。
这个表包含某社交媒体 App 中所有的推文。查询所有无效推文的编号（ID）。当推文内容中的字符数严格大于 15 时，该推文是无效的。
以任意顺序返回结果表。
查询结果格式如下所示：示例 1：
输入：
Tweets 表：
+----------+----------------------------------+
| tweet_id | content                          |
+----------+----------------------------------+
| 1        | Vote for Biden                   |
| 2        | Let us make America great again! |
+----------+----------------------------------+输出：
+----------+
| tweet_id |
+----------+
| 2        |
+----------+

SQL:

SELECTtweet_id
FROMTweets
WHEREchar_length(content) > 15;

练习二：修复表中的名字

表： Users
+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| user_id        | int     |
| name           | varchar |
+----------------+---------+
user_id 是该表的主键(具有唯一值的列)。
该表包含用户的 ID 和名字。名字仅由小写和大写字符组成。编写解决方案，修复名字，使得只有第一个字符是大写的，其余都是小写的。
返回按 user_id 排序的结果表。
返回结果格式示例如下。示例 1：
输入：
Users table:
+---------+-------+
| user_id | name  |
+---------+-------+
| 1       | aLice |
| 2       | bOB   |
+---------+-------+
输出：
+---------+-------+
| user_id | name  |
+---------+-------+
| 1       | Alice |
| 2       | Bob   |
+---------+-------+

SQL:

SELECTuser_id,concat(upper(substr(name, 1, 1)), lower(substr(name, 2))) AS name
FROMUsers;

3 数学函数

3.1 数学函数语法

3.2 数学函数练习

练习一：查询学生各科的平均成绩，四舍五入保留两位小数：

score 表：
+--------+--------+---------+
| s_name | c_name | s_score |
+--------+--------+---------+
| 赵雷   | 语文   |      80 |
| 赵雷   | 数学   |      90 |
| 赵雷   | 英语   |      99 |
| 钱电   | 语文   |      70 |
| 钱电   | 数学   |      60 |
| 钱电   | 英语   |      80 |
| 孙风   | 语文   |      80 |
| 孙风   | 数学   |      80 |
| 孙风   | 英语   |      80 |
| 李云   | 语文   |      50 |
| 李云   | 数学   |      30 |
| 李云   | 英语   |      20 |
| 周梅   | 语文   |      76 |
| 周梅   | 数学   |      87 |
| 吴兰   | 语文   |      31 |
| 吴兰   | 英语   |      34 |
| 郑竹   | 数学   |      89 |
| 郑竹   | 英语   |      98 |
+--------+--------+---------+

SQL:

mysql> select->     t.s_name,->     round(avg(t.s_score),2) as avg_score-> from (->     SELECT->         t2.s_name,->         t3.c_name,->         t1.s_score->     FROM->         score t1->         JOIN student t2 ON t1.s_id = t2.s_id->         JOIN Course t3 ON t1.c_id = t3.c_id-> ) t groupby t.s_name;
+--------+-----------+
| s_name | avg_score |
+--------+-----------+
| 赵雷   |     89.67 |
| 钱电   |     70.00 |
| 孙风   |     80.00 |
| 李云   |     33.33 |
| 周梅   |     81.50 |
| 吴兰   |     32.50 |
| 郑竹   |     93.50 |
+--------+-----------+
7 rows in set (0.01 sec)

4 日期时间函数

4.1 日期时间函数语法

4.2 日期时间函数练习

练习一：上升的温度

表： Weather
+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| recordDate    | date    |
| temperature   | int     |
+---------------+---------+
id 是该表具有唯一值的列。
没有具有相同 recordDate 的不同行。
该表包含特定日期的温度信息编写解决方案，找出与之前（昨天的）日期相比温度更高的所有日期的 id 。
返回结果 无顺序要求 。
结果格式如下例子所示。示例 1：
输入：
Weather 表：
+----+------------+-------------+
| id | recordDate | Temperature |
+----+------------+-------------+
| 1  | 2015-01-01 | 10          |
| 2  | 2015-01-02 | 25          |
| 3  | 2015-01-03 | 20          |
| 4  | 2015-01-04 | 30          |
+----+------------+-------------+
输出：
+----+
| id |
+----+
| 2  |
| 4  |
+----+
解释：
2015-01-02 的温度比前一天高（10 -> 25）
2015-01-04 的温度比前一天高（20 -> 30）

SQL:

SELECTt1.id
FROMWeather t1,Weather t2
WHEREdatediff(t1.recordDate, t2.recordDate) = 1AND t1.Temperature > t2.Temperature;

练习二：查询近 30 天活跃用户数

表：Activity
+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| user_id       | int     |
| session_id    | int     |
| activity_date | date    |
| activity_type | enum    |
+---------------+---------+
该表没有包含重复数据。
activity_type 列是 ENUM(category) 类型， 从 ('open_session'， 'end_session'， 'scroll_down'， 'send_message') 取值。
该表记录社交媒体网站的用户活动。
注意，每个会话只属于一个用户。编写解决方案，统计截至 2019-07-27（包含2019-07-27），近 30 天的每日活跃用户数（当天只要有一条活动记录，即为活跃用户）。
以 任意顺序 返回结果表。
结果示例如下。示例 1:
输入：
Activity table:
+---------+------------+---------------+---------------+
| user_id | session_id | activity_date | activity_type |
+---------+------------+---------------+---------------+
| 1       | 1          | 2019-07-20    | open_session  |
| 1       | 1          | 2019-07-20    | scroll_down   |
| 1       | 1          | 2019-07-20    | end_session   |
| 2       | 4          | 2019-07-20    | open_session  |
| 2       | 4          | 2019-07-21    | send_message  |
| 2       | 4          | 2019-07-21    | end_session   |
| 3       | 2          | 2019-07-21    | open_session  |
| 3       | 2          | 2019-07-21    | send_message  |
| 3       | 2          | 2019-07-21    | end_session   |
| 4       | 3          | 2019-06-25    | open_session  |
| 4       | 3          | 2019-06-25    | end_session   |
+---------+------------+---------------+---------------+
输出：
+------------+--------------+ 
| day        | active_users |
+------------+--------------+ 
| 2019-07-20 | 2            |
| 2019-07-21 | 2            |
+------------+--------------+ 
解释：注意非活跃用户的记录不需要展示。

SQL:

SELECTt1.activity_date ASDAY,count(*) AS active_users
FROM(SELECT*FROMActivityWHEREdatediff(date('2019-07-27'), activity_date) < 30ANDdatediff(date('2019-07-27'), activity_date) >= 0GROUPBYactivity_date,user_id) t1
GROUPBYt1.activity_date
ORDERBYDAY;

5 条件函数

5.1 条件函数语法

5.2 条件函数练习

练习一：行转列，假设有一个成绩表scores，包含student_id（学生 ID）、subject（科目）和score（成绩）这 3 列，如下表所示：

student_id	subject	score
1	语文	80
1	数学	90
2	语文	75
2	数学	85

 现在要将每个学生的各科成绩显示在一行中：

SELECTstudent_id,MAX(CASEWHEN subject = '语文'THEN scoreELSENULLEND) AS'语文成绩',MAX(CASEWHEN subject = '数学'THEN scoreELSENULLEND) AS'数学成绩'
FROMscores
GROUPBYstudent_id;

student_id	语文成绩	数学成绩
1	80	90
2	75	85

练习二：判断三角形

表: Triangle
+-------------+------+
| Column Name | Type |
+-------------+------+
| x           | int  |
| y           | int  |
| z           | int  |
+-------------+------+
在 SQL 中，(x, y, z)是该表的主键列。
该表的每一行包含三个线段的长度。对每三个线段报告它们是否可以形成一个三角形。
以 任意顺序 返回结果表。
查询结果格式如下所示。示例 1:
输入: 
Triangle 表:
+----+----+----+
| x  | y  | z  |
+----+----+----+
| 13 | 15 | 30 |
| 10 | 20 | 15 |
+----+----+----+
输出: 
+----+----+----+----------+
| x  | y  | z  | triangle |
+----+----+----+----------+
| 13 | 15 | 30 | No       |
| 10 | 20 | 15 | Yes      |
+----+----+----+----------+

SQL:

SELECTt.*,casewhent.x + t.y > t.z andt.x + t.z > t.y andt.z + t.y > t.xthen'Yes'else'No'endas triangle
FROMTriangle t;

 6 总结

本文介绍了SQL中常用的字符串、数学、日期时间和条件函数，详细解释了它们的语法和使用方法。通过具体的示例，展示了如何操作字符串（如截取、转换大小写、去除空格、连接等）、进行数学运算（如四舍五入、计算平均值、取绝对值等）、处理日期时间（如计算日期差、日期加减、格式化日期等），以及如何使用条件函数进行逻辑判断（如CASE语句、IF语句等）。此外，文章还通过多个练习题帮助读者巩固所学知识，并提高SQL查询和数据处理的能力。这些函数和练习题不仅提升了SQL的应用技巧，还加深了对数据库操作的理解，适合各种数据分析和开发场景。