SQL 之 函数（一）

关键字：LEFT, RIGHT, SUBSTRING, LTRIM, RTRIM, CONTACT, UPPER, LOWER, GETDATE/NOW/CURRENT_DATE, DATEPART/DATE_FORMAT, DATEDIFF, ROUND, RAND, PI, CAST, ISNULL / IFNULL/ NVL

SQL

SQL函数：字符函数、日期/时间函数、数值函数、转换函数

复合函数：把多种函数组成一个表达式的方法

 

标量函数、聚合函数

标量函数：只针对单行中的数据执行该函数。eg. LTRIM函数用于删除一个特定值中的起始空格

聚合函数：对一个较大的数据集合（或数据组）进行操作，eg. SUM函数可以用来计算一个特定的列中所有值的总和。

 

1. 字符函数

LEFT, RIGHT, SUBSTRING, LTRIM, RTRIM, CONTACT, UPPER, LOWER

比如

 

（1）LEFT  RIGHT

LEFT(CharacterValue, NumberOfCharacters)

从指定的CharacterValue中查看NumberOfCharacters个字符，返回结果。

LEFT(‘Hello world’, 3)

返回 Hel

注意的是字符数据经常在其右边包含空格，比如在一个只有一行的表中国包含了一个名为President的列，把它定义为20个字符的长度。

President

--------------------------------

George Washington

 

针对这个表

SELECT

RIGHT (President, 10) AS ‘Last Name’

FROM table1

得到的返回数据：

Last Name

---------------------------------

hington

为什么不是Washington？因为整个列的长度是20个字符，SQL会先用空格填充不够20个字符的字段（会认为即使这一行的President字段值不足20个字符，也需要与其他行保持一致）所以“George Washington”的右边会有3个空格，当像这样要求时就会得到右边的3个空格加上“George Washington”的另外7个字符。

从中间选择：

SUBSTRING (CharacterValue, StartingPosition, NumberOfCharacters)

 

注意MySQL有时要求函数名称和放参数的左括号之间不能有空格：

SUBSTRING(CharacterValue, StartingPosition, NumberOfCharacters)

Oracle中允许SUBSTR（与SQL和MySQL的SUBSTRING等效的）函数的第二个参数允许负数，为负数时，需要从列的右边开始计数。

Oracle不允许编写没有FROM子句的SELECT语句：

SELECT SUBSTRING (‘thewhtitegoat’, 4, -5) AS ‘The answer’

是不可以的；

但是提供了一个叫DUAL的虚拟表：

SELECT SUBSTRING (‘thewhtitegoat’, 4, -5) AS ‘The answer’ FROM DUAL;

 

（2）LTRIM RTRIM

修剪左边的空格：

SELECT

LTRIM (‘the apple’) AS‘The answer’

返回数据

The answer

----------------

the apple

 

LTRIM会足够聪明不会删除字符串“the apple’”里面的空格，只会删除字符串左边的空格。

RTRIM也是同理。

（3）CONTACT

在SQL Server里面可以用“+”来连接两个字符串。在MySQL里面则要用CONTACT（Oracle使用连接符‘ ||‘）

MySQL里面用CONTACT来连接字符串：

SELECT

OrderID,

FirstName,

LastName,

CONTACT (FirstName, ‘ ‘, LastName) AS‘Whole Name’

FROM Orders

 

得到

OrderID    FirstName    LastName    Whole Name

----------------------------------------------------------------

1             Bob          Smith       Bob Smith

 

还记得列的别名要用单引号吧。本例用CONTACT连接了三个值，FirstName、空格和LastName。

Oracle版的CONTACT函数只允许两个参数：

SELECT

OrderID,

FirstName,

LastName,

CONTACT (CONTACT (FirstName,‘ ‘), LastName) AS‘Whole Name’

FROM Orders

 

（4）UPPER LOWER

把任何字符串转换为全部大写或全部小写。

SELECT

UPPER (‘Abraham Lincoln’ ) AS‘Convert to Uppercase’

 

2. 复合函数

比如之前的右子串带空格情况，解决办法：复合函数组合RIGHT和RTRIM

SELECT

RIGHT (RTRIM (President), 10) AS‘Last Name’

FROM table1

正确得到Washington

 

在Oracle中，不像SQL Server和MySQL使用RIGHT，而只能用SUBSTR：

SELECT

SUBSTR (RTRIM (President), -10, 10) AS‘Last Name’

FROM table1

开始位置参数为负数，从右边开始计算，右起第10个...

3. 日期/时间函数

SQL Server ： GETDATE 、DATEPART、DATEDIFF

（1）最简单的日期/时间函数返回当前的日期和时间：GETDATE，无参数

SELECT GETDATE()

MySQL中等价的是NOW()函数，Oracle的是CURRENT_DATE()函数

 

（2）能够分析日期和时间的：

DATEPART(DatePart, DateValue)

DateValue是任意日期

DatePart：year, quarter, month, dayofyear, day, week, weekday（星期几）, hour, minute, second

 

对于数据“1/10/2015”:

DATEPART 函数表达式	结果值
DATEPART(month, ‘1/10/2015’)	10
DATEPART(day, ‘1/10/2015’)	1
DATEPART(week, ‘1/10/2015’)	40
DATEPART(weekday, ‘1/10/2015’)	7

“1/10/2015”的month是10，day是1，week是40（2016年10月1日是这一年的第40周），weekday是7（这天是周六，是一周中的第7天）

 

MySQL的等价函数：DATE_FORMAT，并且使用不同的值作为DateValue参数。

MySQL查询日期是该年的第几天：

SELECT DATE_FORMAT(‘2016-10-1’,‘%d’);

Oracle中没有与之等价的函数

 

（3）计算两个日期之间相差的天数（或周数、月数）

SQL: DATE_DIIFF

DATEDIFF (DatePart, StartDate, EndDate)

DatePart参数：year, quarter(季度), month, dayofyear, day, week, month, hour, minute, second

 

示例：7/8/2009和8/14/2009

DATEDIFF 函数表达式	结果值
DATEDIFF (day,‘7/8/2009’,‘8/14/2009’)	37
DATEDIFF (week,‘7/8/2009’,‘8/14/2009’)	5
DATEDIFF (month,‘7/8/2009’,‘8/14/2009’)	1
DATEDIFF (year,‘7/8/2009’,‘8/14/2009’)	0

两个日期相差37天，5周，1个月，0年

 

MySQL：DATEDIFF只计算两个日期之间的天数

Oracle中没有与之等价的函数

 

4. 数值函数

（1）ROUND

ROUND (NumericValue, DecimalPlaces)

正负数、有无小数点都可

DecimalPlaces：为正整数，表示将数字四舍五入到指定的那么多个小数点；为0，表示没有小数部分（四舍五入到整数）；为负整数，表示对小数点左侧前几位进行四舍五入

表达式	结果值
ROUND (712.863, 3)	712.863
ROUND (712.863, 2)	712.86
ROUND (712.863, 1)	712.9
ROUND (712.863, 0)	713
ROUND (712.863, -1)	710
ROUND (712.863, -2)	700

 

（2）RAND

产生随机数。RAND ([seed]) seed是可选的参数。不使用时产生0到1之间的一个随机数。

SELECT

RAND () AS ‘Random Value’

改变seed可能返回不同的值

 

（3）PI

返回pi值：SELECT PI()   返回3.14159265358979

SELECT ROUND (PI(), 2)   返回3.14

 

5. 转换函数

类型转换或把NULL转换成有意义的内容

（1）CAST

CAST (Expression AS Datatype)

很多情况下并不需要用到它。

SELECT

2 * Quantity

FROM table1

这样的语句SQL能够自动把Quantity列（没有定义为数字列，比如说是字符）转换成数字列以乘2

 

示例：

比如我们把一列日期保存到一个字符列中，想要把这些日期转换成真正的日期/时间列：

SELECT

‘2009-04-11’ AS‘Original Date’

CAST (‘2009-04-11’ AS DATETIME) AS‘Converted Date’

 

输出

Original Date         Converted Date

----------------------------------------------

2009-04-11        2009-04-11 00:00:00

Original Date是字符数据，Converted Date是真正的日期/时间列

 

Oracle等价表达：

SELECT

‘2009-04-11’ AS‘Original Date’

CAST (‘11-APR-20090’ AS DATE) AS‘Converted Date’

FROM DUAL

 

 

（2）ISNULL

SQL:  将NULL值转换成一个有意义的值

NULL值是没有数据的值，和零值与空格不同。

 

示例表单：

Product      Description       Color

-----------------------------------------------

1                  Chair A              Red

2                  Chair B              NULL

3                  Chair C              Green

Chair B的Color列为NULL值说明还没有为这种产品提供Color值

SELECT

Description,

Color

FROM  Products

 

显示：

Description       Color

-----------------------------------------------

Chair A              Red

Chair B              NULL

Chair C             Green

 

NULL对用户来说可能是困惑的结果，更想看到或者容易理解的是“unknown”之类的：

SELECT

Description,

ISNULL (Color, ‘Unknown’) AS‘Color’

FROM  Products

这只会把NULL值改为Unknown

显示：

Description       Color

-----------------------------------------------

Chair A              Red

Chair B              Unknown

Chair C              Green

 

MySQL等价函数：IFNULL

SELECT

Description,

IFNULL (Color, ‘Unknown’) AS‘Color’

FROM  Products;

 

Oracle等价函数：NVL

SELECT

Description,

NVL (Color, ‘Unknown’) AS‘Color’

FROM  Products;

 

PS: Oracle遇到NULL值用破折号显示