【SQL系列学习（四）】

学习目标

学习数据库中最常用的函数

关键字：LEFT、RIGHT、SUBSTRING、LTRIM、RTRIM、UPPER、LOWER、GETDATE、DATEPART、DATEDIFF、GOUND、PI、POWER和ISNULL

内容结构

四、使用函数

SQL中常用的函数分为4种类型：字符函数、日期/时间函数、数值函数和转换函数。此外，还有复合函数（composite function），它是把多种函数组成一个表达式的一种方式。

4.1 什么是函数

函数可以把多个数值的数据包含进来，但是函数的最终的结果总是一个数值。

什么是函数？函数只是把任意数目的输入值转换成一个输出值的一种规则。规则在函数中定义，并且无法更改。

有两种类型的函数：标量函数（scalar function）和聚合函数（aggregate function）。标量（scalar）这个词来源于数学，指的是只针对单个数字进行运算。在计算机领域中，它表示针对单行中的数据执行该函数。例如，LTRIM函数用于删除一行数据中的一个特定值的起始空格。

相反，聚合函数应用于较大的数据集合或数据组。例如，SUM函数可以用来计算一个特定的列中的所有值的总和。

最常用的标量函数可以分为3种类型：字符类型、日期/时间函数和数值函数。

4…2 字符函数

字符函数是指能够操作字符数据的函数，也称作字符串函数。接下来介绍如下的7个字符函数的示例：LEFT、RIGHT、SUBSTRING、LTRIM、RTRIM、UPPER和LOWER。

在本节中，所直接使用的SELECT语句将会在columnlist中带有直接量，而不是从指定的表中检索数据。

我们从LEFT函数开始第一个示例。

当执行如下的SQL命令时：

SELECT
LEFT('sunlight', 3) AS 'The Answer'

它会返回如下的数据：

The Answer
sun

注：
（1）这条SQL语句包含了列的别名，所以列标题的输出显示为’The Answer’。
（2）这条SQL语句中，没有FROM子句，从一个直接量（即’sunlight’）中查询数据，而不是从一个表中检索数据。

LEFT函数的一般格式如下：

LEFT(CharacterValue, NumberOfCharacters)

LEFT函数的计算公式的含义是：从指定的CharacterValue中，从左边开始查看NumberOfCharacters个字符，并且返回结果。

1.函数可以有任意多个的参数，并且要把参数放在圆括号中。
2.对任何想要使用的函数，需要在数据库的参考指南中查看该函数，以确定它需要多少个参数以及各个参数的含义是什么。

第2个要介绍的字符函数是RIGHT函数。

它和LEFT函数类似，只不过它从输入值右边开始指定字符。

RIGHT函数的一般格式如下：

RIGHT(CharacterValue, NumberOfCharacters)

3.使用SUBSTRING函数来从一个表达式的中间选择数据。

SUBSTRING函数的一般格式如下：

SUBSTRING(CharacterValue, StartingPosition, NumberOfCharacters)

例如：

SUBSTRING('Thewhitegoat', 4, 5)

返回如下数据：

The Answer
white

这个函数从第4个字符开始，选取了5个字符。显示的结果是单词"white"。

数据库的差异：MySQL和Oracle
在Oracle中，和SUBSTRING函数等价的是SUBSTR。区别在于，Oracle版的SUBSTR第2个参数（StartingPosition）可以是负数，当这个参数是负数的时候，表示需要从该列的右边开始计数。

4.LTRIM函数从左边开始，删除字符左边的空格

示例如下：

SELECT
LTRIM('       The apple') AS 'The Answer'

返回的数据如下：

The Answer
the apple

LTRIM不会删除短语中间的空格，只会删除字符左边的空格。

5.RTRIM函数从右边开始，删除字符右边的空格

6.最后介绍UPPER和LOWER

这两个函数把任意的单词或短语转换成全部大写或全部小写。如下是这两个函数的示例：

SELECT
UPPER(' Abraham Lincoln') AS 'Convert to Uppercase'
LOWER(' ABRAHAM LINCOIN ') AS 'Convert to Lowercase'

输出如下：

Convert to Uppercase	Convert to Lowercase
ABRAHAM LINCOIN	abraham Lincoln

4.3 复合函数

函数的特性是可以把两个或两个以上的函数组合成一个复合函数。
由两个函数组成的复合函数，可以称为函数的函数。
以George Washington来举例说明，使用如下数据：

President
George Washington

其中，President列的长度是20个字符，在“George Washington”这个值的右边有3个空格。

SELECT
RIGHT(RTRIM (President),10) AS 'Last Name'
FROM tablel

返回数据如下：

Last Name
George Washington

当执行复合函数时，总是从里面开始，向外执行。

RTRIM函数接受President列的值作为参数，并且把右侧的空格全部删除，然后对该结果应用RIGHT函数，以返回预期的值。

4.4 日期/时间函数

最简单的日期/时间函数是返回当前的日期和时间。

在Microsoft SQL Server中，该函数名为GETDATE。这个函数没有参数，只是返回当前的日期和时间。
在MySQL中，这个函数是NOW。例如：SELECT NOW()
在Oracle中，这个函数是CURRENT_DATE。

接下来要介绍的日期/时间函数，能够分析任意具体的日期，并且返回诸如该日期是该月中的第几天或者是该年份的第几个周等信息。
在Microsoft SQL Server中，该函数名为DATEPART，其一般格式如下所示：

DATEPART (DatePart, DateValue)

参数DataValue是任意的日期。参数DataPart可以是许多不同的值。如下都是有效的值：year、quarter、month、dayofyear、day、week、weekday、hour、minute和second。

数据库的差异：MySQL和Oracle

在MySQL中，和DATEPART等价的函数名为DATE_FORMAT，并且它使用不同的值作为DateValue参数。
在Oracle中，没有和DATEPART等价的函数

最后要介绍的是DATEDIFF，它可以得到任意两个日期之间相差的天数。其一般的格式如下：

DATEDIFF (DatePart, StartDate, EndDate)

这个函数的DataPart参数的有效值包括：year、quarter、month、dayofyear、day、hour、minute和second。

数据库的差异：MySQL和Oracle

在MySQL中，DATEDIFF函数只允许计算两个日期之间的天数。其一般格式如下：

DATEDIFF (EndDate, StartDate)

其返回值为一个正数。

在Oracle中，没有和DATEDIFF等价的函数

4.5 数值函数

本节将介绍的数值函数有：ROUND、RAND、PI和POWER。

ROUND函数允许对任意数值进行四舍五入。其一般格式如下：

ROUND (NumericValue, DecimalPlaces)

参数NumericValue可正可负可以是小数，当参数DecimalPlaces为正时，代表四舍五入保留几位小数，当参数DecimalPlaces为负时，代表四舍五入保留几位整数。

PI函数只会返回数学运算中的pi值（近似于3.14的无理数）。

POWER函数用于指定包含幂的一个数字，POWER函数的一般格式如下所示：

POWER (NumericValue, Exponent)

来看一个例子，即如何显示5的2次方

SELECT POWER(5, 2) AS '5 Squared'

返回如下的数据：

5 Squared
25

4.6 转换函数

有CAST函数和ISNULL函数，略。

4.7 小结

要讨论每个可用函数的细节，是不太现实的。当需要使用函数时，在数据库的参考指南中查找它们。

下一节我将给大家介绍如何对数据进行排序，将开始对所展示的信息进行整体性的考虑，而不是只考虑单个的数据项。