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
SELECT AVG(`age`) AS `average_teacher_age`
FROM teachers
WHERE email LIKE "%@qq.com"
 

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,结果将没有小数点或小数部分。

SELECT ROUND(AVG(`student_count`), 2) AS `avg_student_count`
FROM `courses`;

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岁的教师信息。
SELECT *
FROM teachers
WHERE age>20 AND ISNULL(`email`)

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

SELECT COUNT(*) AS 'teacher_count'
FROM `teachers`
WHERE age BETWEEN 20 AND 28 AND `country` IN ('CN', 'UK')

 

1. 使用 NOW() 、 CURDATE()、CURTIME() 获取当前时间

 

在 SQL 中,我们可以通过使用 NOW()、CURDATE()、CURTIME() 来获取当前的时间

  • NOW() 可以用来返回当前日期和时间 格式:YYYY-MM-DD hh:mm:ss
  • CURDATE() 可以用来返回当前日期 格式:YYYY-MM-DD
  • CURTIME() 可以用来返回当前时间 格式:hh:mm:ss

在使用 NOW() 和 CURTIME() 时,如果要精确的秒以后的时间的话,可以在()中加数字,加多少,就表示精确到秒后多少位

比如 NOW(3) 就是精确到毫秒,表示为: 2021-03-31 15:27:20.645

 

使用 NOW() 向记录表 records 中插入当前的时间(精确到毫秒)

INSERT INTO `records`
VALUES (NOW(3));
 

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 中不包括具体时间的部分)
 
SELECT `name`,
DATE_FORMAT(DATE(`created_at`),"%Y-%m-%d") AS `created_date`
FROM courses
WHERE DATE(created_at) < '2020-08-01';
 
 

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 为返回结果列名称

 
posted @   君逸堂  阅读(230)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· 百万级群聊的设计实践
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战
· 永远不要相信用户的输入:从 SQL 注入攻防看输入验证的重要性
点击右上角即可分享
微信分享提示