SQL 函数|极客教程

SQL 函数，在SQL中我们也可以使用函数对检索出来的数据进行函数操作，比如求某列数据的平均值，或者求字符串的长度等。从函数定义的角度出发，可以将函数分成内置函数和自定义函数。在SQL语言中，同样也包括了内置函数和自定义函数。内置函数是系统内置的通用函数，而自定义函数是我们根据自己的需要编写的，极客教程为大家讲解的是SQL的内置函数。

SQL 函数是什么

当我们学习编程语言的时候，也会遇到函数。函数的作用是什么呢？它可以把我们经常使用的代码封装起来，需要的时候直接调用即可。这样既提高了代码效率，又提高了可维护性。

SQL中的函数一般是在数据上执行的，可以很方便地转换和处理数据。一般来说，当我们从数据表中检索出数据之后，就可以进一步对这些数据进行操作，得到更有意义的结果，比如返回指定条件的函数，或者求某个字段的平均值等。

SQL 内置函数分类

SQL提供了一些常用的内置函数，当然你也可以自己定义SQL函数。SQL的内置函数对于不同的数据库软件来说具有一定的通用性，我们可以把内置函数分成四类：

算术函数
字符串函数
日期函数
转换函数

这4类函数分别代表了算术处理、字符串处理、日期处理、数据类型转换，它们是SQL函数常用的划分形式。

SQL 函数为什么是这4个维度

函数是对提取出来的数据进行操作，那么数据表中字段类型的定义有哪几种呢？

我们经常会保存一些数值，不论是整数类型，还是浮点类型，实际上对应的就是数值类型。
我们也会保存一些文本内容，可能是人名，也可能是某个说明，对应的就是字符串类型。
我们还需要保存时间，也就是日期类型
针对数值、字符串和日期类型的数据，可以对它们分别进行算术函数、字符串函数以及日期函数的操作
如果想要完成不同类型数据之间的转换，就可以使用转换函数

SQL 算术函数

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

SQL 函数

这里我举一些简单的例子，你来体会下，针对每一个SQL 算术函数极客教程都有详细介绍他的使用。

SELECT ABS(-2)，运行结果为2。
SELECT MOD(101,3)，运行结果2。
SELECT ROUND(37.25,1)，运行结果37.3。

SQL 算术函数的详细用法请参考下表

SQL 算术函数	描述
ABS()	此SQL ABS()返回作为参数传递的数字的绝对值。
CEIL()	这个SQL CEIL()将向上舍入函数中任何正或负的小数值。
FLOOR()	SQL FLOOR()将任何正或负的十进制值舍入为下一个最小的整数值。
EXP()	SQL EXP()返回e的n次幂(n是数值表达式)，其中e是自然算法的基础，e的值大约是2.71828183。
LN()	SQL LN()函数的作用是:返回n的自然对数，其中n大于0，其底数约等于2.71828183。
MOD()	这个SQL MOD()函数返回除法的余数。
POWER()	POWER函数进行幂运算
SQRT()	SQL SQRT()返回参数中给定值的平方根。

SQL 字符串函数

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

SQL 函数

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

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。

SQL 日期函数

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

SQL 函数

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

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函数，不要直接使用日期与字符串进行比较

SQL 转换函数

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

SQL 函数

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

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。

SQL 函数使用案例

我们来看看如何使用SQL 函数对王者荣耀英雄数据做处理，极客教程SQL 查询 SELECT这篇文章中提到王者荣耀英雄数据库，一共有69个英雄，23个属性值。

SQL 函数

我们现在把这个文件导入到MySQL中，你可以使用Navicat可视化数据库管理工具将.sql文件导入到数据库中。数据表为heros，然后使用今天学习的SQL函数，对这个英雄数据表进行处理。

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

SELECT name, ROUND(attack_growth,1) FROM heros

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

SQL 函数

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

SELECT MAX(hp_max) FROM heros

运行结果为9328。

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

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

运行结果：

SQL 函数

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

SELECT CHAR_LENGTH(name), name FROM heros

运行结果为：

SQL 函数

假如想要提取英雄上线日期（对应字段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

运行结果为：

SQL 函数

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

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

需要注意的是下面这种写法是不安全的：

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

因为很多时候你无法确认birthdate的数据类型是字符串，还是datetime类型，如果你想对日期部分进行比较，那么使用DATE(birthdate)来进行比较是更安全的。
运行结果为：

SQL 函数

假设我们需要知道在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'

运行结果为：

SQL 函数

使用SQL函数会带来什么问题

尽管SQL函数使用起来会很方便，但我们使用的时候还是要谨慎，因为你使用的函数很可能在运行环境中无法工作，这是为什么呢？

如果你学习过编程语言，就会知道语言是有不同版本的，比如Python会有2.7版本和3.x版本，不过它们之间的函数差异不大，也就在10%左右。但我们在使用SQL语言的时候，不是直接和这门语言打交道，而是通过它使用不同的数据库软件，即DBMS。DBMS之间的差异性很大，远大于同一个语言不同版本之间的差异。实际上，只有很少的函数是被DBMS同时支持的。比如，大多数DBMS使用（||）或者（+）来做拼接符，而在MySQL中的字符串拼接函数为Concat()。大部分DBMS会有自己特定的函数，这就意味着采用SQL函数的代码可移植性是很差的，因此在使用函数的时候需要特别注意。