SQL算数函数和时间函数
1. 使用 AVG() 函数求数值列的平均值
但是当参数 column_name
列中的数据均为空时,结果会返回 NULL。
语法:
SELECT AVG(`column_name`)
FROM `table_name`;
👇我们可以通过下面的实例来感受一下 AVG() 函数 的用法。
假如我们想要计算课程表 courses
中学生上课人数 student_count
的平均值,我们可以使用下面的SQL语句:
SELECT AVG(`student_count`) AS `average_student_count`
FROM `courses`;
查询教师表 teachers 中教师邮箱为 '@qq.com' 结尾的年龄的平均值,最后返回结果列名显示为 'average_teacher_age'
teachers
2.使用 MAX() 函数返回指定列中的最大值
最大值函数 MAX() 用于返回指定列中的最大值。它只有一个参数 column_name
,表示指定的列名。但是当参数 column_name
列中的数据均为空时,结果会返回 NULL。
语法:
SELECT MAX(`column_name`) AS
`column_name_max`
FROM `table_name`;
3. 使用 MIN() 函数返回指定列中的最小值
MIN() 函数的功能与 MAX() 正好相反,它用于返回指定列中的最小值。但与 MAX() 相同的是,它也只有一个参数 column_name
,表示指定的列名,且当参数 column_name
列中的数据均为空时,结果会返回 NULL。
语法
SELECT MIN(`column_name`)
FROM `table_name`;
4. 使用 SUM() 函数统计数值列的总数
SUM() 函数用于统计数值列的总数并返回其值。它只有一个参数 column_name
,表示指定的列名,但是当参数 column_name
列中的数据均为空时,结果会返回 NULL。
语法
SELECT SUM(`column_name`)
FROM `table_name`;
👇我们可以通过下面的实例来感受一下 SUM() 函数 的用法。
假如我们想查询课程表 courses
所有课程上课学生人数的总和。
我们可以使用下面的 SQL 语句:
SELECT SUM(`student_count`) AS `all_student_count`
FROM `courses`;
使用 ROUND() 函数将数值四舍五入
ROUND()
函数用于把数值字段舍入为指定的小数位数。
语法
SELECT ROUND(`column_name`, `decimals`)
FROM `table_name`;
其中:
- column_name 为要舍入的字段
- decimals 规定要返回的小数位数
- ROUND() 函数始终返回一个值。当 decimals 为正数时,column_name 四舍五入为 decimals 所指定的小数位数。当 decimals 为负数时,column_name 则按 decimals 所指定的在小数点的左边四舍五入。
- 特别的,如果 length 是负数且大于小数点前的数字个数,ROUND() 函数将返回 0
3.1.1 ROUND(X)
ROUND( X ):返回参数 X 四舍五入后的一个整数。
3.1.2 ROUND(X, D)
ROUND(X, D): 返回参数 X 四舍五入且保留 D 位小数后的一个数字。如果 D 为 0,结果将没有小数点或小数部分。
2. 使用 NULL() 函数判断空值
在介绍判断空值函数 NULL() 之前,我们先来了解一下在之前的学习中遇到了多次的 NULL ,即我们常说的空值。但是这种叫法并不准确,因为 NULL 并不是值,它表示数值未知或者不确定。因此,NULL 无法和 0 或空格字符串 "" 进行比较,甚至 NULL 与 NULL 之间也无法比较。默认地,表的列可以存放 NULL 。
在本小节中,我们主要介绍 SQL 中的 ISNULL()
函数和 IFNULL()
函数,他们的用途都是判断字段是否为空,但是具体的用法有些差别,让我们在接下来的学习中体会一下他们的差别吧。
2.1 ISNULL()
ISNULL()
函数用于判断字段是否为 NULL,它只有一个参数 column_name
为列名,根据column_name
列中的字段是否为 NULL 值返回 0 或 1。
语法
SELECT ISNULL(`column_name`)
FROM `table_name`;
其中:
- 如果
column_name
列中的某个字段是 NULL 则返回 1,不是则返回 0
2.2 IFNULL()
IFNULL()
函数也用于判断字段是否为NULL,但是与 ISNULL()
不同的是它接收两个参数,第一个参数 column_name
为列名,第二个参数 value
相当于备用值。
语法
SELECT IFNULL(`column_name`, `value`)
FROM `table_name`;
其中:
- 如果
column_name
列中的某个字段是 NULL 则返回 value 值,不是则返回对应内容。 COALESCE(column_name, value)
函数也用于判断字段是否为NULL,其用法和IFNULL()
相同。
teachers
表中找出没有邮箱并且年龄大于20岁的教师信息。3. 使用 COUNT() 函数计数
COUNT() 函数用于计数,可利用其确定表中行的数目或者符合特定条件的行的数目。当COUNT() 中的参数不同时,其的用途也是有明显的不同的,主要可分为以下三种情况:COUNT(column_name) 、COUNT( * ) 和 COUNT(DISTINCT column_name) 。
3.1 COUNT( column_name )
COUNT(column_name) 函数会对指定列具有的行数进行计数,但是会除去值为 NULL 的行。该函数主要用于查看各列数据的数量情况,便于统计数据的缺失值。
假如出现某一列的数据全为 NULL 值的情况,
使用COUNT( column_name ) 函数对该列进行计数,会返回 0。
语法:
SELECT COUNT(`column_name`)
FROM `table_name`;
3.2 COUNT(*)
COUNT(*) 函数会对表中行的数目进行计数,包括值为 NULL 所在行和重复项所在行。该函数主要用于查看表中的记录数。
语法:
SELECT COUNT(*)
FROM `table_name`;
❗ 注意: COUNT(column_name) 与 COUNT(*) 的区别
-
COUNT(column_name) 中,如果
column_name
字段中的值为 NULL,则计数不会增加,而如果字段值为空字符串""
,则字段值会加 1 -
COUNT(*) 中,除非整个记录全为 NULL,则计数不会增加,如果存在某一个记录不为 NULL,或者为空字符串
""
,计数值都会加 1。正常来说,表都会有主键,而主键不为空,所以 COUNT(*) 在有主键的表中等同于 COUNT(PRIMARY_KEY),即查询有多少条记录。
统计教师表中年龄在 20 到 28 岁之间,且国籍为中国或英国的教师人数,最后返回统计值,结果列名显示为 teacher_count
1. 使用 NOW() 、 CURDATE()、CURTIME() 获取当前时间
在 SQL 中,我们可以通过使用 NOW()、CURDATE()、CURTIME() 来获取当前的时间
NOW()
可以用来返回当前日期和时间 格式:YYYY-MM-DD hh:mm:ssCURDATE()
可以用来返回当前日期 格式:YYYY-MM-DDCURTIME()
可以用来返回当前时间 格式:hh:mm:ss
在使用
NOW()
和CURTIME()
时,如果要精确的秒以后的时间的话,可以在()中加数字,加多少,就表示精确到秒后多少位比如
NOW(3)
就是精确到毫秒,表示为:2021-03-31 15:27:20.645
使用 NOW()
向记录表 records
中插入当前的时间(精确到毫秒)
2. 使用 DATE()、TIME() 函数提取日期和时间
使用 DATE()
、TIME()
函数分别将 '2021-03-25 16:16:30'
这组数据中的日期于时间提取出来,并用 date
、time
作为结果集列名。
SELECT DATE('2021-03-25 16:16:30') AS `date`,TIME('2021-03-25 16:16:30') AS `time`;
+------------+----------+
| date | time |
+------------+----------+
| 2021-03-25 | 16:16:30 |
+------------+----------+
3.2 分别查询出课程表的课程创建时间中的日期与时间
使用 DATE()
、 TIME()
函数从课程表 courses
中查询课程的名字 name
和课程创建时间 created_at
,从课程创建时间 created_at
中提取出创建课程的日期与时间,用 created_date
和 created_time
作为结果集列名。
mysql> SELECT `name`, `created_at`,
-> DATE_FORMAT(DATE(`created_at`),"%Y-%m-%d") AS `created_date`,
-> DATE_FORMAT(TIME(`created_at`),"%H:%i:%s") AS `created_time`
-> FROM `courses`;
+-------------------------+---------------------+--------------+--------------+
| name | created_at | created_date | created_time |
+-------------------------+---------------------+--------------+--------------+
| Advanced Algorithms | 2020-06-01 09:10:12 | 2020-06-01 | 09:10:12 |
| System Design | 2020-07-18 10:11:12 | 2020-07-18 | 10:11:12 |
| Django | 2020-02-29 12:10:12 | 2020-02-29 | 12:10:12 |
| Web | 2020-04-22 13:01:12 | 2020-04-22 | 13:01:12 |
| Big Data | 2020-09-11 16:01:12 | 2020-09-11 | 16:01:12 |
| Artificial Intelligence | 2018-05-13 18:12:30 | 2018-05-13 | 18:12:30 |
| Java P6+ | 2019-01-19 13:31:12 | 2019-01-19 | 13:31:12 |
| Data Analysis | 2019-07-12 13:01:12 | 2019-07-12 | 13:01:12 |
| Object Oriented Design | 2020-08-08 13:01:12 | 2020-08-08 | 13:01:12 |
| Dynamic Programming | 2018-08-18 20:01:12 | 2018-08-18 | 20:01:12 |
+-------------------------+---------------------+--------------+--------------+
10 rows in set
courses
中查询 2020 年 8 月前的课程名和创建日期,并为创建日期的列名起别名为 created_date
(日期指 created_at
中不包括具体时间的部分)3. 使用 EXTRACT() 函数提取指定的时间信息
前面我们已经学习了 DATE 函数和 TIME 函数,明白 DATE 返回日期, TIME 返回时间,如果我只想知道年份的信息或者小时的信息,那么该怎么解决呢?这时,我们就可以使用 EXTRACT() 函数来解决问题。
EXTRACT() 函数用于返回日期/时间的单独部分,如 YEAR
(年)、MONTH
(月)、DAY
(日)、HOUR
(小时)、MINUTE
(分钟)、 SECOND
(秒)。
语法
SELECT EXTRACT(unit FROM date)
FROM `table`
其中:
table 是表格名
date 参数是合法的日期表达式。
unit 参数是需要返回的时间部分,如 YEAR
、MONTH
、 DAY
、 HOUR
、MINUTE
、SECOND
等。
在一般情况下,
EXTRACT(unit FROM date)
与unit()
的结果相同。
4.1 DATE_FORMAT() 用法
我们在 SQL 中使用 DATE_FORMAT()
方法来格式化输出 date/time。
需要注意的是 DATE_FORMAT()
函数返回的是字符串格式。
语法
SELECT DATE_FORMAT(date,format);
其中
date
一个有效日期。
format
是 date/time 的输出格式。
DATE_FORMAT(pay_time, '%Y-%m-%d %H:%i:%S')
1. 使用 DATE_ADD() 增加时间
1.1 DATE_ADD() 函数
DATE_ADD()
函数是常用的时间函数之一,用于向日期添加指定的时间间隔。
语法
SELECT DATE_ADD(date, INTERVAL expr type)
FROM table_name
其中:date
指代希望被操作的有效日期,为起始日期
expr
是希望添加的时间间隔的数值(expr 是一个字符串,对于负值的间隔,可以以 ”-“ 开头)
type
是具体的数据类型,表示加上时间间隔的单位(可以是 MICROSECOND , SECOND , MINUTE , HOUR , DAY , WEEK , MONTH , QUARTER , YEAR 等)
2. 使用 DATE_SUB() 减少时间
上一节了解过能够令时间加上几天、几小时的函数 DATE_ADD()
,对应的,当我们需要修改数据表中的时间,让时间减少几天、几年时,又该如何做呢?
我们同样可以使用相关的时间函数对时间进行修改。
2.1 DATE_SUB() 函数
DATE_SUB() 函数是常用的时间函数之一,用于从日期减去指定的时间间隔。它与 DATE_ADD()
函数具有相似的用法。
语法
SELECT DATE_SUB(date, INTERVAL expr type)
FROM table_name
其中:date
指代希望被操作的有效日期
expr
是希望添加的时间间隔
type
是具体的数据类型(可以是 MICROSECOND , SECOND , MINUTE , HOUR , DAY , WEEK , MONTH , QUARTER , YEAR 等)
3. 使用时间函数 DATEDIFF() 和 TIMESTAMPDIFF() 计算日期差
3.1 DATEDIFF ()
DATEDIFF() 常用的日期差,在 MySQL 中默认只能计算天数差。
示例代码
DATEDIFF() 用法:
DATEDIFF(时间1,时间2)
SELECT DATEDIFF(时间1,时间2) AS date_diff FROM courses;
DATEDIFF() 差值计算规则:时间 1 - 时间 2
date_diff
为返回结果列名称
3.2 TIMESTAMPDIFF()
查询功能多的 MySQL 自带的日期函数,可以计算两个日期相差的年(YEAR,时间1,时间2),月(MONTH,时间1,时间2),周(WEEK,时间1,时间2),日(DAY,时间1,时间2),小时(HOUR,时间1,时间2)。
示例代码
TIMESTAMPDIFF() 用法:
TIMESTAMPDIFF (类型,时间1,时间2)
SELECT TIMESTAMPDIFF (类型,时间1,时间2) AS year_diff;
TIMESTAMPDIFF() 差值计算规则:时间 2 - 时间 1
year_diff
为返回结果列名称
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· 百万级群聊的设计实践
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战
· 永远不要相信用户的输入:从 SQL 注入攻防看输入验证的重要性