关键字：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值用破折号显示
扫一扫
1571
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
