SQL必知必会(SQL函数)

常用的 SQL 函数有哪些

一、算术函数

算术函数，顾名思义就是对数值类型的字段进行算术运算。常用的算术函数及含义如下表所示：

这里我举一些简单的例子，你来体会下：

SELECT ABS(-2)，运行结果为 2。

SELECT MOD(101,3)，运行结果 2。

SELECT ROUND(37.25,1)，运行结果 37.3

二、字符串函数

常用的字符串函数操作包括了字符串拼接，大小写转换，求长度以及字符串替换和截取等。具体的函数名称及含义如下表所示：

这里同样有一些简单的例子，你可以自己运行下：

SELECT CONCAT('abc', 123)，运行结果为 abc123。

SELECT LENGTH('你好')，运行结果为 6。

SELECT CHAR_LENGTH('你好')，运行结果为 2。

SELECT LOWER('ABC')，运行结果为 abc。

SELECT UPPER('abc')，运行结果 ABC。

SELECT REPLACE('fabcd', 'abc', 123)，运行结果为 f123d。

SELECT SUBSTRING('fabcd', 1,3)，运行结果为 fab。

三、日期函数

日期函数是对数据表中的日期进行处理，常用的函数包括：

下面是一些简单的例子，你可自己运行下：

SELECT CURRENT_DATE()，运行结果为 2019-04-03。

SELECT CURRENT_TIME()，运行结果为 21:26:34。

SELECT CURRENT_TIMESTAMP()，运行结果为 2019-04-03 21:26:34。

SELECT EXTRACT(YEAR FROM '2019-04-03')，运行结果为 2019。

SELECT DATE('2019-04-01 12:00:05')，运行结果为 2019-04-01。

这里需要注意的是，DATE 日期格式必须是yyyy-mm-dd的形式。如果要进行日期比较，就要使用 DATE 函数，不要直接使用日期与字符串进行比较，我会在后面的例子中讲具体的原因。

四、转换函数

转换函数可以转换数据之间的类型，常用的函数如下表所示：

这两个函数不像其他函数，看一眼函数名就知道代表什么、如何使用。下面举了这两个函数的例子，你需要自己运行下：

SELECT CAST(123.123 AS INT)，运行结果会报错。

SELECT CAST(123.123 AS DECIMAL(8,2))，运行结果为 123.12。

SELECT COALESCE(null,1,2)，运行结果为 1。

CAST 函数在转换数据类型的时候，不会四舍五入，如果原数值有小数，那么转换为整数类型的时候就会报错。不过你可以指定转化的小数类型，在 MySQL 和 SQL Server 中，你可以用DECIMAL(a,b)来指定，其中 a 代表整数部分和小数部分加起来最大的位数，b 代表小数位数，比如DECIMAL(8,2)代表的是精度为 8 位（整数加小数位数最多为 8 位），小数位数为 2 位的数据类型。所以SELECT CAST(123.123 AS DECIMAL(8,2))的转换结果为 123.12。

五、练习

显示英雄以及他的物攻成长，对应字段为attack_growth。我们让这个字段精确到小数点后一位，需要使用的是算术函数里的 ROUND 函数

SELECT name, ROUND(attack_growth,1) FROM heros;

代码中，ROUND(attack_growth,1)中的attack_growth代表想要处理的数据，“1”代表四舍五入的位数，也就是我们这里需要精确到的位数

假设我们想显示英雄最大生命值的最大值，就需要用到 MAX 函数。在数据中，“最大生命值”对应的列数为hp_max，在代码中的格式为MAX(hp_max)

SELECT MAX(hp_max) FROM heros;

假如我们想要知道最大生命值最大的是哪个英雄，以及对应的数值，就需要分成两个步骤来处理：首先找到英雄的最大生命值的最大值，即SELECT MAX(hp_max) FROM heros，然后再筛选最大生命值等于这个最大值的英雄

SELECT name, hp_max FROM heros WHERE hp_max = (SELECT MAX(hp_max) FROM heros)

假如我们想显示英雄的名字，以及他们的名字字数，需要用到CHAR_LENGTH函数

SELECT CHAR_LENGTH(name), name FROM heros;

假如想要提取英雄上线日期（对应字段 birthdate）的年份，只显示有上线日期的英雄即可（有些英雄没有上线日期的数据，不需要显示），这里我们需要使用 EXTRACT 函数，提取某一个时间元素。所以我们需要筛选上线日期不为空的英雄，即WHERE birthdate is not null，然后再显示他们的名字和上线日期的年份

SELECT name, EXTRACT(YEAR FROM birthdate) AS birthdate FROM heros WHERE birthdate is NOT NULL;

或

SELECT name, YEAR(birthdate) AS birthdate FROM heros WHERE birthdate is NOT NULL;

假设我们需要找出在 2016 年 10 月 1 日之后上线的所有英雄。这里我们可以采用 DATE 函数来判断 birthdate 的日期是否大于 2016-10-01，即WHERE DATE(birthdate)>'2016-10-01'，然后再显示符合要求的全部字段信息

SELECT * FROM heros WHERE DATE(birthdate)>'2016-10-01'

假设我们需要知道在 2016 年 10 月 1 日之后上线英雄的平均最大生命值、平均最大法力和最高物攻最大值。同样我们需要先筛选日期条件，即WHERE DATE(birthdate)>'2016-10-01'，然后再选择AVG(hp_max), AVG(mp_max), MAX(attack_max)字段进行显示

SELECT AVG(hp_max), AVG(mp_max), MAX(attack_max) FROM heros WHERE DATE(birthdate)>'2016-10-01';