SQL 函数

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

SQL 函数是什么

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

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

SQL 内置函数分类

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

  1. 算术函数
  2. 字符串函数
  3. 日期函数
  4. 转换函数

这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函数的代码可移植性是很差的,因此在使用函数的时候需要特别注意。

SQL 函数大小写的规范

在写SELECT语句的时候用的是大写,而你在网上很多地方,包括你自己写的时候可能用的是小写。实际上在SQL中,关键字和函数名是不用区分字母大小写的,比如SELECT、WHERE、ORDER、GROUP BY等关键字,以及ABS、MOD、ROUND、MAX等函数名。

不过在SQL中,你还是要确定大小写的规范,因为在Linux和Windows环境下,你可能会遇到不同的大小写问题。

比如MySQL在Linux的环境下,数据库名、表名、变量名是严格区分大小写的,而字段名是忽略大小写的。

而MySQL在Windows的环境下全部不区分大小写。

这就意味着如果你的变量名命名规范没有统一,就可能产生错误。这里有一个有关命名规范的建议:

  1. 关键字和函数名称全部大写;
  2. 数据库名、表名、字段名称全部小写;
  3. SQL语句必须以分号结尾。

虽然关键字和函数名称在SQL中不区分大小写,也就是如果小写的话同样可以执行,但是数据库名、表名和字段名在Linux MySQL环境下是区分大小写的,因此建议你统一这些字段的命名规则,比如全部采用小写的方式。同时将关键词和函数名称全部大写,以便于区分数据库名、表名、字段名。

如果你对SQL感兴趣,请访问极客教程为大家提供的SQL 教程

Python教程

Java教程

Web教程

数据库教程

图形图像教程

大数据教程

开发工具教程

计算机教程