时间：2021-07-01 10:21:17 帮助过：8人阅读
|| 或 CONCAT---并置运算符。
格式∶CONCAT(STRING1, STRING2)
例：’ABC’|| ’DE’=’ABCDE’
CONCAT(‘ABC’,’DE’) =’ABCDE’
ASCII---返回字符的ASCII码。
例：ASCII(‘A’) = 65
CHR---返回指定ASCII码的字符。
例：CHR(65) = ‘A’
INSTR---搜索子串位置
格式∶INSTR(STRING , SET[ , 开始位置[ , 出现次数]])
例∶ INSTR (‘this is a test’ , ‘i’ , 1,2)=6
INITCAP---将字符串每个单词首字母均变为大写
例: INITCAP(‘this is a test’)=’ This Is A Test’’
LENGTH----计算串长
格式∶ LENGTH(string)
RPAD,LPAD---右填充、左填充。默认为填充空格。
格式: RPAD(字符串 , 字符个数 , 填充字符)
例: RPAD(‘ABC’ , 6 , ’H’)=’ABCHHH’
LTRIM,RTRIM-----左右截断。默认为删除空格。
格式∶ LTRIM(STRING[,’SET’])
例∶ LTRIM(‘***tes*t***’ , ’*’)=’ tes*t***’
LOWER----将字符串转换为小写
格式∶LOWER(string)
UPPER---将字符串转换为大写
格式∶UPPER(string)
SUBSTR----提取子串。START为正数时从左开始、为负数时从右开始
格式∶ SUBSTR(STRING , START [ , COUNT])
例∶ SUBSTR(‘WORDSTAR’ , 2 , 3)=’ ORD’
REPLACE---搜索指定字符串并替换
格式∶REPLACE(string , substring , replace_string)
例∶ REPLACE(‘this is a test’ , ‘this’ , ‘that an’)=’that an is a test’
TRIM---删除字符串前缀或尾随字符
格式∶TRIM( [LEADING | TRAILING ｜BOTH] [ trimchar FROM ] string)
LEADING---删除前缀字符
TRAILING---删除后缀字符
BOTH---前后缀字符均删除(默认方式)
Trimchar---指定删除的字符
注：INSTR,LENGTH,SUBSTR加B时针对字节．
2)处理数字的函数
LEAST---返回参数列表中的最小值。返回参数类型以第一参数为准
格式∶LEAST(value,value,value,value,value,….)value为数字或字符串
例∶ LEAST(1,2,5,-10,9)= -10
SIGN---返回参数的符号位,负数--- -1,0----0,正数---1
格式∶SIGN(value)
CELL---返回大于等于特定值的最小整数
格式∶CELL(value)
例∶ CELL(-10,9)= -10
3）处理日期
SYSDATE---系统时间。精确至秒
ADD_MONTH—加减月份。numvalue为负数时减去相应月份
格式: ADD_MONTH (data1,numvalue)
MONTHS_BETWEEN---返回两日期之间的月数,当data1
格式: MONTHS_BETWEEN(data1,data2)
LAST_DAY---返回指定日期的最后一天。
格式∶LAST_DAY(date)
TRUNC---将日期按照format格式截短，缺省为DD(不是四舍五入)
格式：TRUNC（date, format）
例∶ TRUNC (to_date(‘2002-11-07’,’yyyy-mm-dd’),’MM’)= 2002-11-01
4）分组函数
返回基于多个行的单一结果．
常用函数：
AVG---求平均值
COUNT---返回查询的行数
MAX---返回查询列的最大值
MIN---返回查询列的最小值
SUM---返回查询列的总和
MAX, MIN常与GROUP BY配套使用
5）转换函数
TO_DATE---转换字符串为日期型
格式∶ TO_DATE(STRING[,’FORMAT’])
TO_CHAR---转换日期型或数值型为字符串。最重要的函数之一．其FORMAT格式多种多样
格式∶TO_CHAR(DATE [,’FORMAT’])
FORMAT---具体格式参考ORACLE8i DBA 宝典P835数字格式元素
P836 日期格式元素
TO_NUMBER---转换字符串为数字
格式∶TO_NUMBER(string [ , format])
FORMAT---具体格式参考ORACLE8i DBA 宝典P835数字格式元素
6)其他
DECODE---IF语句的另一形式。将输入数值与参数列表比较，返回对应值。应用于将表的行转换成列以及IF语句无法应用的场合。当与SIGN联合使用时功能扩展,可以判断大于小于的情况.
格式: DECODE(input_value , value1 , result1 , value2 , result2 , ….defult_result)
例∶ DECODE(VALUE,1,100,2,300,500)
当VALUE=1时返回100
当VALUE=2时返回300
否则返回500
DECODE(SIGN(VALUES-100), -1,-10,1,10,0)
当VALUE<100时返回-10
当VALUE>100时返回10
当VALUE=100时返回0
SELECT SUM(DECODE(EST_INT_KEY,77771,1,0)) A,
SUM(DECODE(EST_INT_KEY,77772,1,0)) B,
SUM(DECODE(EST_INT_KEY,77773,1,0)) C
FROM PMS_BLK
NVL---空值置换
格式: NVL(value,替换值)
例: NVL(value,’nullvalue’)当value为NULL值时返回nullvalue否则返回value的值
VSIZE---以字节为单位返回数据类型尺寸
格式∶VSIZE(数据类型)
例∶ VSIZE(SYSDATE)=8
USER---得到当前用户名
ROWNUM--- oracle系统顺序分配为从查询返回的行的编号，返回的第一行分配的是1，第二行是2，依此类推，这个伪字段可以用于限制查询返回的总行数。不能以任何基表的名称作为前缀。可以用在限制返回记录条数的地方不受ORDER BY的影响.
例∶ select rownum,month,sell from sale where rownum=1
或select rownum,month,sell from sale where rownum<2(返回第一条记录)
返回rownum在4—10之间的记录
select rownum,month,sell from sale where rownum<10
minus
select rownum,month,sell from sale where rownum<5;
以下均错误:
select rownum,month,sell from sale where rownum=2(1以上)
select rownum,month,sell from sale where rownum>2
ROWID---数据行在物理磁盘上的物理地址，Oracle通过ROWID来定位数据的具体位置，这是存取表中数据的最快的方法.
RETURN---用于函数和过程中.遇见RETURN程序立刻中断,返回,不再执行剩余部分.
SQLCODE---返回ORACLE错误号。
SQLERRM---返回ORACLE错误信息。
----------------------------------------------------------------------------------------------------------
1、Dateadd
在向指定日期加上一段时间的基础上，返回新的 datetime 值。
语法 ：DATEADD(datepart,number,date)
参数
datepart
是规定应向日期的哪一部分返回新值的参数。下表列出了识别的日期部分和缩写。
日期部分 缩写
Year yy, yyyy
quarter qq, q
Month mm, m
dayofyear dy, y
Day dd, d
Week wk, ww
Hour hh
minute mi, n
second ss, s
millisecond ms
number
是用来增加datepart的值。如果指定一个不是整数的值，则将废弃此值的小数部分。
例如，如果为datepart指定day，为number指定1.75，则date将增加1。
date
是返回datetime或smalldatetime值或日期格式字符串的表达式。有关指定日期的更多信息，请参见datetime和smalldatetime。
如果您只指定年份的最后两位数字，则小于或等于"两位数年份截止期"配置选项的值的最后两位数字的数字所在世纪与截止年所
在世纪相同。大于该选项的值的最后两位数字的数字所在世纪为截止年所在世纪的前一个世纪。
例如，如果two digit year cutoff 为 2049（默认），则 49 被解释为 2049，2050 被解释为 1950。
为避免模糊，请使用四位数的年份。
返回类型
返回 datetime，但如果 date 参数是 smalldatetime，返回 smalldatetime。
2、DATEDIFF
返回跨两个指定日期的日期和时间边界数。
一、 语法
DATEDIFF ( datepart , startdate , enddate )
二、参数
datepart
是规定了应在日期的哪一部分计算差额的参数。下表列出了 Microsoft? SQL Server? 识别的日期部分和缩写。
日期部分 缩写
year yy, yyyy
quarter qq, q
Month mm, m
dayofyear dy, y
Day dd, d
Week wk, ww
Hour hh
minute mi, n
second ss, s
millisecond ms
startdate
是计算的开始日期。startdate 是返回 datetime 或 smalldatetime 值或日期格式字符串的表达式。
因为 smalldatetime 只精确到分钟，所以当用 smalldatetime 值时，秒和毫秒总是 0。
如果您只指定年份的最后两位数字，则小于或等于"两位数年份截止期"配置选项的值的最后两位数字的数字所在世纪与截止年所在世纪相同。大于该选项的值的最后两位数字的数字所在世纪为截止年所在世纪的前一个世纪。例如，如果 two digit year cutoff 为 2049（默认），则 49 被解释为 2049，2050 被解释为 1950。为避免模糊，请使用四位数的年份。
enddate
是计算的终止日期。enddate 是返回 datetime 或 smalldatetime 值或日期格式字符串的表达式。
三、返回类型
integer
四、用法
此函数计算两个指定日期之间日期部分的数目。结果为日期部分中等于（date2 - date1）的有符号的整数值。
当结果不是日期部分的偶数倍时，DATEDIFF 将被截断而不是被舍入。
当使用 day 作为日期部分时，DATEDIFF 返回两个指定的时间之间（包括第二个日期但不包括第一个日期）的午夜数。
当使用 month 作为日期部分时，DATEDIFF 返回两个日期之间（包括第二个日期但不包括第一个日期）出现的月的第一天的数目。
当使用 week 作为日期部分时，DATEDIFF 返回两个日期（包括第二个日期但不包括第一个日期）之间星期日的数目。
对于更小的时间单位存在溢出值：
milliseconds 24 天
seconds 68 年
minutes 4083 年
others 没有溢出限制
如果超出这些限制，此函数将返回溢出错误。
五、标准和兼容性
SQL/92 Transact-SQL 扩展。
SQL/99 Transact-SQL 扩展。
Sybase 与 Adaptive Server Enterprise 兼容。
六、示例
下面的语句返回 1：
select datediff( hour, ‘‘4:00am‘‘, ‘‘5:50am‘‘ )
下面的语句返回 102：
select datediff( month, ‘‘1987/05/02‘‘, ‘‘1995/11/15‘‘ )
下面的语句返回 0：
select datediff( day, ‘‘00:00‘‘, ‘‘23:59‘‘ )
下面的语句返回 4：
select datediff( day, ‘‘1999/07/19 00:00‘‘,‘‘1999/07/23 23:59‘‘ )
下面的语句返回 0：
select datediff( month, ‘‘1999/07/19‘‘, ‘‘1999/07/23‘‘ )
下面的语句返回 1：
select datediff( month, ‘‘1999/07/19‘‘, ‘‘1999/08/23‘‘ )
3、decode函数
decode(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值)
该函数的含义如下：
IF 条件=值1 THEN
RETURN(翻译值1)
ELSIF 条件=值2 THEN
RETURN(翻译值2)
......
ELSIF 条件=值n THEN
RETURN(翻译值n)
ELSE
RETURN(缺省值)
END IF
·使用方法：
1、比较大小
select decode(sign(变量1-变量2),-1,变量1,变量2) from dual; --取较小值
sign()函数根据某个值是0、正数还是负数，分别返回0、1、-1
例如：
变量1=10，变量2=20
则sign(变量1-变量2)返回-1，decode解码结果为“变量1”，达到了取较小值的目的。
2、表、视图结构转化
现有一个商品销售表sale，表结构为：
month char(6) --月份
sell number(10,2) --月销售金额
现有数据为：
200001 1000
200002 1100
200003 1200
200004 1300
200005 1400
200006 1500
200007 1600
200101 1100
200202 1200
200301 1300
想要转化为以下结构的数据：
year char(4) --年份
month1 number(10,2) --1月销售金额
month2 number(10,2) --2月销售金额
month3 number(10,2) --3月销售金额
month4 number(10,2) --4月销售金额
month5 number(10,2) --5月销售金额
month6 number(10,2) --6月销售金额
month7 number(10,2) --7月销售金额
month8 number(10,2) --8月销售金额
month9 number(10,2) --9月销售金额
month10 number(10,2) --10月销售金额
month11 number(10,2) --11月销售金额
month12 number(10,2) --12月销售金额
结构转化的SQL语句为：
create or replace view
v_sale(year,month1,month2,month3,month4,month5,month6,
month7,month8,month9,month10,month11,month12)
as
select
substrb(month,1,4),
sum(decode(substrb(month,5,2),‘01‘,sell,0)),
sum(decode(substrb(month,5,2),‘02‘,sell,0)),
sum(decode(substrb(month,5,2),‘03‘,sell,0)),
sum(decode(substrb(month,5,2),‘04‘,sell,0)),
sum(decode(substrb(month,5,2),‘05‘,sell,0)),
sum(decode(substrb(month,5,2),‘06‘,sell,0)),
sum(decode(substrb(month,5,2),‘07‘,sell,0)),
sum(decode(substrb(month,5,2),‘08‘,sell,0)),
sum(decode(substrb(month,5,2),‘09‘,sell,0)),
sum(decode(substrb(month,5,2),‘10‘,sell,0)),
sum(decode(substrb(month,5,2),‘11‘,sell,0)),
sum(decode(substrb(month,5,2),‘12‘,sell,0))
from sale
group by substrb(month,1,4);
4、CAST和CONVERT
将某种数据类型的表达式显式转换为另一种数据类型。CAST 和 CONVERT 提供相似的功能。
语法
使用 CAST：
CAST ( expression AS data_type )
使用 CONVERT：
CONVERT (data_type[(length)], expression [, style])
参数
expression
是任何有效的 Microsoft? SQL Server? 表达式。有关更多信息，请参见表达式。
data_type
目标系统所提供的数据类型，包括 bigint 和 sql_variant。不能使用用户定义的数据类型。有关可用的数据类型的更多信息，请参见数据类型。
length
nchar、nvarchar、char、varchar、binary 或 varbinary 数据类型的可选参数。
style
日期格式样式，借以将 datetime 或 smalldatetime 数据转换为字符数据（nchar、nvarchar、char、varchar、nchar 或 nvarchar 数据类型）；或者字符串格式样式，借以将 float、real、money 或 smallmoney 数据转换为字符数据（nchar、nvarchar、char、varchar、nchar 或 nvarchar 数据类型）。
SQL Server 支持使用科威特算法的阿拉伯样式中的数据格式。
在表中，左侧的两列表示将 datetime 或 smalldatetime 转换为字符数据的 style 值。给 style 值加 100，可获得包括世纪数位的四位年份 (yyyy)。
不带世纪数位 (yy) 带世纪数位 (yyyy)
标准
输入/输出**
- 0 或 100 (*) 默认值 mon dd yyyy hh:miAM（或 PM）
1 101 美国 mm/dd/yyyy
2 102 ANSI yy.mm.dd
3 103 英国/法国 dd/mm/yy
4 104 德国 dd.mm.yy
5 105 意大利 dd-mm-yy
6 106 - dd mon yy
7 107 - mon dd, yy
8 108 - hh:mm:ss
- 9 或 109 (*) 默认值 + 毫秒 mon dd yyyy hh:mi:ss:mmmAM（或 PM）
10 110 美国 mm-dd-yy
11 111 日本 yy/mm/dd
12 112 ISO yymmdd
- 13 或 113 (*) 欧洲默认值 + 毫秒 dd mon yyyy hh:mm:ss:mmm(24h)
14 114 - hh:mi:ss:mmm(24h)
- 20 或 120 (*) ODBC 规范 yyyy-mm-dd hh:mm:ss[.fff]
- 21 或 121 (*) ODBC 规范（带毫秒） yyyy-mm-dd hh:mm:ss[.fff]
- 126(***) ISO8601 yyyy-mm-dd Thh:mm:ss:mmm（不含空格）
- 130* 科威特 dd mon yyyy hh:mi:ss:mmmAM
- 131* 科威特 dd/mm/yy hh:mi:ss:mmmAM
* 默认值（style 0 或 100、9 或 109、13 或 113、20 或 120、21 或 121）始终返回世纪数位 (yyyy)。
** 当转换为 datetime 时输入；当转换为字符数据时输出。
*** 专门用于 XML。对于从 datetime 或 smalldatetime 到 character 数据的转换，输出格式如表中所示。对于从 float、money 或 smallmoney 到 character 数据的转换，输出等同于 style 2。对于从 real 到 character 数据的转换，输出等同于 style 1。
重要 默认情况下，SQL Server 根据截止年份 2049 解释两位数字的年份。即，两位数字的年份 49 被解释为 2049，而两位数字的年份 50 被解释为 1950。许多客户端应用程序（例如那些基于 OLE 自动化对象的客户端应用程序）都使用 2030 作为截止年份。SQL Server 提供一个配置选项（"两位数字的截止年份"），借以更改 SQL Server 所使用的截止年份并对日期进行一致性处理。然而最安全的办法是指定四位数字年份。
当从 smalldatetime 转换为字符数据时，包含秒或毫秒的样式将在这些位置上显示零。当从 datetime 或 smalldatetime 值进行转换时，可以通过使用适当的 char 或 varchar 数据类型长度来截断不需要的日期部分。
下表显示了从 float 或 real 转换为字符数据时的 style 值。
值 输出
0（默认值） 最大为 6 位数。根据需要使用科学记数法。
1 始终为 8 位值。始终使用科学记数法。
2 始终为 16 位值。始终使用科学记数法。
在下表中，左列表示从 money 或 smallmoney 转换为字符数据时的 style 值。
值 输出
0（默认值） 小数点左侧每三位数字之间不以逗号分隔，小数点右侧取两位数，例如 4235.98。
1 小数点左侧每三位数字之间以逗号分隔，小数点右侧取两位数，例如 3,510.92。
2 小数点左侧每三位数字之间不以逗号分隔，小数点右侧取四位数，例如 4235.9819。
返回类型
返回与 data type 0 相同的值。
5、CHARINDEX函数
返回字符或者字符串在另一个字符串中的起始位置。CHARINDEX函数调用方法如下：
CHARINDEX ( expression1 , expression2 [ , start_location ] )
Expression1是要到expression2中寻找的字符中，start_location是CHARINDEX函数开始在expression2中找expression1的位置。
CHARINDEX函数返回一个整数，返回的整数是要找的字符串在被找的字符串中的位置。假如CHARINDEX没有找到要找的字符串，那么函数整数“0”。让我们看看下面的函数命令执行的结果：
CHARINDEX(‘SQL‘, ‘Microsoft SQL Server‘)
这个函数命令将返回在“Microsoft SQL Server”中“SQL”的起始位置，在这个例子中，CHARINDEX函数将返回“S”在“Microsoft SQL Server”中的位置11。
接下来，我们看这个CHARINDEX命令：
CHARINDEX(‘7.0‘, ‘Microsoft SQL Server 2000‘)
在这个例子中，CHARINDEX返回零，因为字符串“7.0” 不能在“Microsoft SQL Server”中被找到。接下来通过两个例子来看看如何使用CHARINDEX函数来解决实际的T-SQL问题。
6、DATEPART
返回代表指定日期的指定日期部分的整数。
语法
DATEPART ( datepart ,date )
参数
datepart
是指定应返回的日期部分的参数。下表列出了 Microsoft? SQL Server? 识别的日期部分和缩写。
日期部分 缩写
year yy, yyyy
quarter qq, q
month mm, m
dayofyear dy, y
day dd, d
week wk, ww
weekday dw
Hour hh
minute mi, n
second ss, s
millisecond ms
week (wk, ww) 日期部分反映对 SET DATEFIRST 作的更改。任何一年的 1 月 1 日定义了 week 日期部分的开始数字，例如：DATEPART(wk, ‘Jan 1, xxxx‘) = 1，此处 xxxx 代表任一年。
weekday (dw) 日期部分返回对应于星期中的某天的数，例如：Sunday = 1、Saturday = 7。weekday 日期部分产生的数取决于 SET DATEFIRST 设定的值，此命令设定星期中的第一天。
date
是返回 datetime 或 smalldatetime 值或日期格式字符串的表达式。对 1753 年 1 月 1 日之后的日期用datetime 数据类型。更早的日期存储为字符数据。当输入 datetime 值时，始终将其放入引号中。因为 smalldatetime 只精确到分钟，所以当用 smalldatetime 值时，秒和毫秒总是 0。
如果只指定年份的最后两位数字，则小于或等于"两位数年份截止期"配置选项的值的最后两位数字的数字所在世纪与截止年所在世纪相同。大于该选项的值的最后两位数字的数字所在世纪为截止年所在世纪的前一个世纪。例如，如果 two digit year cutoff 为 2049 （默认），则 49 被解释为 2049，2050 被解释为 1950。为避免模糊，请使用四位数的年份。
有关时间值指定的更多信息，请参见时间格式。有关日期指定的更多信息，请参见 datetime 和 smalldatetime。
返回类型
int
注释
DAY、MONTH、和 YEAR 函数分别是 DATEPART(dd, date)、DATEPART(mm, date)、和 DATEPART(yy, date) 的同义词。
示例
GETDATE 函数返回当前日期；然而，比较时并不总是需要完整的日期信息（通常只是对日期的一部分进行比较）。此示例显示 GETDATE 及 DATEPART 的输出。
SELECT GETDATE() AS ‘Current Date‘
GO
下面是结果集：
Current Date
---------------------------
Feb 18 1998 11:46PM
SELECT DATEPART(month, GETDATE()) AS ‘Month Number‘
GO
下面是结果集：
Month Number
------------
2
此示例假设日期是 5 月 29 日。
SELECT DATEPART(month, GETDATE())
GO
下面是结果集：
-----------
5
(1 row(s) affected)
在此示例中，以数字表示日期。注意：SQL Server 将 0 解释为 01/01/1900。
SELECT DATEPART(m, 0), DATEPART(d, 0), DATEPART(yy, 0)
下面是结果集：
----- ------ ------
1 1 1900
7、SUBSTRING
功能：返回字符、二进制、文本或图像表达式的一部分
语法：SUBSTRING ( expression, start, length )
SUBSTR(str,pos): 由<str>中，选出所有从第<pos>位置开始的字元。请注意，这个语法不适用于SQL Server上。
SUBSTR(str,pos,len): 由<str>中的第<pos>位置开始，选出接下去的<len>个字元。
SQL 中的 substring 函数是用来抓出一个栏位资料中的其中一部分。这个函数的名称在不同的资料库中不完全一样：
MySQL: SUBSTR(), SUBSTRING()
Oracle: SUBSTR()
SQL Server: SUBSTRING()
参数：
expression 字符串、二进制字符串、文本、图像、列或包含列的表达式。请勿使用包含聚合函数的表达式。
start 整数或可以隐式转换为 int 的表达式，指定子字符串的开始位置。
length 整数或可以隐式转换为 int 的表达式，指定子字符串的长度。
返回值：
如果 expression 是一种支持的字符数据类型，则返回字符数据。如果 expression 是一种支持的二进制数据类型，则返回二进制数据。如果 start = 1，则子字符串从表达式的第一个字符开始。
返回字符串的类型与给定表达式的类型相同（下表所示内容除外）。
给定表达式 返回类型
image varbinary
ntext nvarchar
8、stuff
stuff的功能：删除指定长度的字符串并在指定的起始点插入另一组字符
STUFF ( character_expression , start , length , character_expression )
character_expression ：操作的字符，
start:删除和插入的起始点，
length:删除的长度，
character_expression ：要插入的字符
注：sql的stuff()函数和编程中数组的下标起始位置不一样，数组是以0开始的，而 stuff()函数是以 1 开始的。
如果 stuff() 函数以 0 开始的话，它是从expression1_Str 字符串的最后一个字符开始截取。例子如下所示：
例1：stuff(‘00000000‘,0,3,‘123‘) --> 对应的结果是： (没有值输出)
例2：stuff(‘00000000‘,1,3,‘123‘) --> 对应的结果是：12300000
9、ceiling函数 floor函数
ceiling函数返回大于或等于所给数字表达式的最小整数。
floor函数返回小于或等于所给数字表达式的最大整数。
比如
celling(12.1) 结果为 13
floor(12.1)结果为 12
在介绍这个之前你必须明白什么是oracle中的空值null
1.NVL函数
NVL函数的格式如下：NVL(expr1,expr2)
含义是：如果oracle第一个参数为空那么显示第二个参数的值，如果第一个参数的值不为空，则显示第一个参数本来的值。
例如：
SQL> select ename,NVL(comm, -1) from emp;