使用SQL函数

2016.11.29

四．使用SQL函数

SQL函数可以大大增加创建动态结果的能力。为使用报表的人提供更好的服务。

常用SQL函数基本上可以分为四种类型：字符函数、日期/时间函数、数值函数和转换函数。

此外也可以将多种函数组合为一个表达式，成为复合函数。

4.1 函数的作用

函数就是使用特定的公式把一个（一些）值转换为另一个（些）值的一种规则。函数分为标量函数和聚合函数。前者只针对单个数字进行运算，后者对一个较大的数据集合进行操作。每种数据库都会提供几十种标量函数，当然，他们的名称甚至工作方式都各不一样。

4.2 字符（String）函数

操作字符数据的函数。

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

LEFT(CharacterValue,Number Of Characters):从指定的字符串CharacterValue中从左边开始查看Number Of Characters个字符并返回结果。

SELECT

LEFT (‘sunlight’,3) AS ‘The Answer’

结果：

The Answer

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

sun

在一条SELECT语句中，FROM不是必需的。当然，在ORACLE中要求所有的SELECT语句都必须有FROM子句。但是，在FROM字句中提供的表不一定需要一个真正的表，ORACLE提供了一个名为DUAL的虚拟表。

RIGHT(CharacterValue,Number Of Characters):从指定的字符串CharacterValue中从右边开始查看Number Of Characters个字符并返回结果。

注意：在ORACLE中没有提供这两个函数，而是用SUBSTR函数提供相同的功能。在指定了数据类型长度的情况下要考虑字符串右边的空格。这时，要得到想要的字符串就必须用到RTRIM函数。

RTRIM函数：删除字符串右边的空格。

LTRIM函数：删除字符串左边的空格。

这两个函数不会删除字符串中间的空格。

SUBSTRING(CharacterValue,StartingPosition,NumberOfCharacters):从指定的字符串CharacterValue中从指定位置StartingPosition开始取NumberOfCharacters个字符串。

SQLserver:

SELET

SUBSTRING (‘thewhitegoat’,4,5)  AS  ‘the answer’;

Mysql:函数名称和左括号间不能有空格

SELET

SUBSTRING(‘thewhitegoat’,4,5)  AS  ‘the answer’;

ORACLE：用SUBSTR函数代替，且第二个参数可以是负数，表示从右边开始计数。

SELECT

SUBSTR(‘thewhitegoat’,4,5)  AS  “the answer”

FROM DUAL;

结果：

The Answer

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

white

CONCAT函数：只用在MySql和ORACLE数据库中，表示连接字符串。在ORACLE中一次只能连接两个字符串。

Mysql：

SELECT

Columname1,

Columname2,

Columname3,

CONCAT(Columname1，’ ’，columname2)

FROM table;

ORACLE：

SELECT

Columname1,

Columname2,

Columname3,

CONCAT(CONCAT(Columname1，’ ’)，columname2)

FROM table;

UPPER和LOWER函数：将字符串转换成全部大写或者全部小写。

4.3 复合函数

在执行复合函数时，总是从里边开始，然后向外边执行。

SELECT

RIGHT(RTRIM(CharacterValue),NumberOfCharacters)

FROM table

先执行RTRIM再执行RIGHT。

ORACLE中：

SELECT

SUBSTR(RTRIM(CharacterValue),-NumberOfCharacters,NumberOfCharacters)

FROM table；

4.4 日期和时间函数

获取当前时间：

SQLServer：

SELECT GETDATE()

MySQL：

SELECT NOW();

ORACLE:

SELECT CURRENT_DATE;

分析时间：当前时间是第几天，第几个月，第几周等。

SQLserver：DATPART(Datapart,DateValue)

Mysql:DATE_FORMATE(‘DateValue’,’%d’)

时间间隔：

SQLserver：DATEDIFF(Datapart,StartDate,EndDate)

Mysql:DATEDIFF(EndDate,startDate)

Datapart可取：year/quarter/month/dayofyear/day/week/weekday/hour/minute/second

4.5 数值函数

ROUND(NumericValue,DecimalPlaces):对NumericValue进行四舍五入，DecimalPlaces为正数表示保留几位小数，为0表示没有小数部分，为负数表示对小数点左边前几位进行四舍五入。

RAND([seed]):随机数，若没有指定seed，默认返回0~1之间的数。

PI():返回π的值。

ORACLE中没有RAND和PI这两个函数。

4.6 转换函数

CAST(Expression AS DataType)

SQLserver:

SELECT

‘2009-04-110 AS ‘Original Date’,

CAST(‘2009-04-110 AS DATETIME) AS ‘Coverted Date’

ORACLE:

SELECT

‘2009-04-110 AS ‘Original Date’,

CAST(‘11-APR-20090 AS DATE) AS “Coverted Date”

FROM DUAL;

把NULL值转换成有意义的值：将COLOR列中NULL值转换成Unknow。

SQLserver:ISNULL(COLOR,’Unknow’)

Mysql:IFNULL(COLOR,’Unknow’)

ORACLE:NVL(COLOR,’Unknow’)

当遇到NULL值时，在ORACLE中显示的是破折号。