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中显示的是破折号。