《数据库基础语法》5. 什么是函数？如何利用函数提高效率

楔子

SQL 语句主要的功能就是对数据进行处理和分析。为了避免重复造轮子，提高数据处理的效率，SQL 为我们提供了许多标准的功能模块：函数（Function）。

SQL 函数是一种具有某种功能的模块，可以接收零个或多个输入值，并且返回一个输出值。

在 SQL 中，函数主要分为两种类型：

• 标量函数（scalar function）。标量函数针对每个输入参数，返回一个输出结果。例如，ABS(x) 可以计算 x 的绝对值。
• 聚合函数（aggregate function）。聚合函数基于一组数据进行计算，返回一个输出结果。例如，AVG 函数可以计算一组数据的平均值。

我们先介绍标量函数，聚合函数将会在后续进行介绍。为了方便学习，我们将常见的 SQL 标量函数分为以下几类：数值函数、字符函数、日期函数 以及 类型转换函数。这里先来介绍前两个。

数值函数

数值函数，很好理解，就是用来对数值进行操作的。那么在写 SQL 的时候，都可以使用哪些数值函数呢？我们来看一下。

这个比较简单，应该不用演示了，我们来随便挑几个吧。

--我们没有使用from，这是数据库自己支持的。
--所以我们说这种方式很适合学习一些函数
SELECT ABS(-1), CEIL(3.5), FLOOR(3.5),
       SQRT(4);
-- 1    4    3    2

最后我们来看一下 RANDOM / RAND 函数，我们这个函数会返回一个 0 ~ 1 的随机数。

SELECT RAND();  -- 0.60232236478694

那么这个函数可以用来干什么呢？显然可以随机筛选指定条数的记录。

SELECT *, RAND() FROM staff limit 5;
/*
01010011868    25    高级工    生产人员    0.4342025690099248
01010010306    32    中级工    生产人员    0.3640457814224491
01010001867    44    高级工    生产人员    0.5176212308161159
01010007780    39    中级工    生产人员    0.49596884054687695
01010002647    33    高级工    生产人员    0.9269805410196823
 */
 
--我们看到，给每一个记录都加上了一个随机数
--那么它可以干什么呢？显然可以进行随机的样本选择
SELECT id FROM staff ORDER BY RAND() LIMIT 5;
/*
01010002428
01010003399
01010005965
01010009792
01010001795
 */
--我们按照 RAND()进行排序，当然，即使 RAND()不在字段里面也是可以的
--然后选择前5个幸运员工，由于 RAND()是随机的，所以选择的员工也是随机的

函数之文本数据处理

上面我们介绍了 SQL 中常见的数值函数。接下来我们继续学习用于处理文本数据的字符函数。

字符函数用于字符数据的处理，例如字符串的拼接、大小写转换、子串的查找和替换等。下表列出了 SQL 中常见的字符函数：

我们通过示例说明一下这些函数的作用，当然不会全部举例，会介绍一些常用的。

-- 即便里面出现了数字也无所谓，甚至全部是数字都行，会把里面的内容统统解释成字符串
SELECT CONCAT('你好', 123, '啊'); -- 你好123啊

-- 字符串变小写 变大写
SELECT LOWER('SaTori'), UPPER('SaTori'); -- satori SATORI

-- 返回字符的数量 返回字节的数量，一个汉字三个字节
SELECT CHAR_LENGTH('古明地觉'), OCTET_LENGTH('古明地觉'); -- 4	12

-- 返回字符串的指定位置的字符，索引从1开始
-- 表示从第2个字符开始选，选6个
SELECT SUBSTRING('where are you', 2, 6);  -- here a

-- 查找某个子串在字符串中首次出现的位置, PostgreSQL 支持 POSITION, 但不支持 INSTR
-- 而 MySQL 两种都支持, 注意: 索引从1开始
SELECT POSITION('to' in 'satori'), INSTR('satori', 'to'); -- 3 3


-- 将字符串中的指定字符替换成其它的字符
-- 下面将空格全部换成空字符
SELECT REPLACE('I Love Satori', ' ', ''); -- ILoveSatori

-- 删除字符串开头和结尾位于子串的部分，直接说比较难理解，我们距离说明
SELECT TRIM('~ab' from '~~abXXXXb'); -- XXXX
-- 从两端开始，如果字符在'~ab'当中，那么就删掉，直到出现一个不在 '~ab' 中的字符

小结

掌握常见的 SQL 函数将会方便我们进行数据的处理和分析，避免重复实现已有的功能。我们介绍了常见的数值函数，大多数函数都可以在不同的数据库之间通用，但是也存在一些数据库专有的函数实现。还介绍了常见的字符函数，掌握这些函数可以方便我们对文本数据进行清洗和转换等处理。除了这些函数之外，各种数据库还提供了大量的字符处理函数。当我们需要实现某种操作时，可以先查找数据库的文档，避免重复实现已有的功能。