SQLServer常用函数对比.docx

SQLServer常用函数对比.docx

《SQLServer常用函数对比.docx》由会员分享，可在线阅读，更多相关《SQLServer常用函数对比.docx（40页珍藏版）》请在冰豆网上搜索。

SQLServer常用函数对比

SQLServer和Oracle的常用函数对比

1.绝对值

S:

selectabs（-1）value

O:

selectabs（-1）valuefromdual

2.取整（大）

S:

selectceiling（-1.001）value

O:

selectceil（-1.001）valuefromdual

3.取整（小）

S:

selectfloor（-1.001）value

O:

selectfloor（-1.001）valuefromdual

4.取整（截取）

S:

selectcast（-1.002asint）value

O:

selecttrunc（-1.002）valuefromdual

5.四舍五入

S:

selectround（1.23456,4）value1.23460

O:

selectround（1.23456,4）valuefromdual1.2346

6.e为底的幂

S:

selectExp

（1）value2.7182818284590451

O:

selectExp

（1）valuefromdual2.71828182

7.取e为底的对数

S:

selectlog（2.7182818284590451）value1

O:

selectln（2.7182818284590451）valuefromdual;

1

8.取10为底对数

S:

selectlog10（10）value1

O:

selectlog（10,10）valuefromdual;1

9.取平方

S:

selectSQUARE（4）value16

O:

selectpower（4,2）valuefromdual16

10.取平方根

S:

selectSQRT（4）value2

O:

selectSQRT（4）valuefromdual2

11.求任意数为底的幂

S:

selectpower（3,4）value81

O:

selectpower（3,4）valuefromdual81

12.取随机数

S:

selectrand（）value

O:

selectsys.dbms_random.value（0,1）valuefromdual;

13.取符号

S:

selectsign（-8）value-1

O:

selectsign（-8）valuefromdual-1数学函数

14.圆周率

S:

SelectPI（）value3.1415926535897931

O:

不知道

15.sin,cos,tan参数都以弧度为单位例如：

selectsin（PI（）/2）value得到1（SQLServer）

16.Asin,Acos,Atan,Atan2返回弧度

17.弧度角度互换（SQLServer，Oracle不知道）

DEGREES:

弧度-〉角度

RADIANS：

角度-〉弧度

数值间比较

18.求集合最大值S:

selectmax（value）valuefrom（select1valueunionselect-2valueunionselect4valueunionselect3value）a

O:

selectgreatest（1,-2,4,3）valuefromdual

19.求集合最小值S:

selectmin（value）valuefrom（select1value

union

select-2valueunionselect4valueunionselect3value）a

O:

selectleast（1,-2,4,3）valuefromdual

20.如何处理null值（F2中的null以10代替）

S:

selectF1,IsNull（F2,10）valuefromTblO:

selectF1,nvl（F2,10）valuefromTbl数值间比较

21.求字符序号S:

selectascii（'a'）value

O:

selectascii（'a'）valuefromdual

22.从序号求字符S:

selectchar（97）value

O:

selectchr（97）valuefromdual

23.连接

S:

select'11'+'22'+'33'value

O:

selectCONCAT（'11','22'）||33valuefromdual

23子串位置--返回3

S:

selectCHARINDEX（'s','sdsq',2）value

O:

selectINSTR（'sdsq','s',2）valuefromdual

23■模糊子串的位置--返回2,参数去掉中间%则返回7

S:

selectpatindex（'%d%q%','sdsfasdqe'）value

O:

oracle没发现，但是instr可以通过第四霾问證刂瞥鱿执问？

BR>selectINSTR（'sdsfasdqe','sd',1,2）

valuefromdual返回6

24.求子串

S:

selectsubstring（'abcd',2,2）value

O:

selectsubstr（'abcd',2,2）valuefromdual

25子串代替返回aijklmnef

S:

SelectSTUFF（'abcdef',2,3,'ijklmn'）value

O:

SelectReplace（'abcdef','bed','ijklmn'）valuefrom

dual

26.子串全部替换

S:

没发现

O:

selectTranslate（'fasdbfasegas','fa'我'）valuefromdual

27.长度

S:

len,datalength

O:

length

28.大小写转换lower,upper

29.单词首字母大写

s：

没发现

O:

selectINITCAP（'abcddsafdf'）valuefromdual

30.左补空格（LPAD的第一个参数为空格则同space函数）

S:

selectspace（10）+'abcd'value

O:

selectLPAD（'abcd',14）valuefromdual

31.右补空格（RPAD的第一个参数为空格则同space函数）

S:

select'abcd'+space（10）value

O:

selectRPAD（'abcd',14）valuefromdual

32.删除空格

S:

ltrim,rtrim

O:

ltrim,rtrim,trim

33.重复字符串

S:

selectREPLICATE（'abcd',2）value

O:

没发现

34.发音相似性比较（这两个单词返回值一样，发音相同）

S:

SelectSOUNDEX（'Smith'）,SOUNDEX（'Smythe'）

O:

SelectSOUNDEX（'Smith'）,SOUNDEX（'Smythe'）fromdual

SQLServer中用SelectDIFFERENCE（'Smithers','Smythers'）比较soundex的差

返回0-4，4为同音，1最高

dual

dual

日期函数

35.系统时间

S:

selectgetdate（）value

O:

selectsysdatevaluefromdual

36.前后几日直接与整数相加减

37.求日期

S:

selectconvert（char（10）,getdate（）,20）value

O:

selecttrunc（sysdate）valuefromdual

selectto_char（sysdate,'yyyy-mm-dd'）valuefrom

38.求时间

S:

selectconvert（char（8）,getdate（）,108）value

O:

selectto_char（sysdate,'hh24:

mm:

ss'）valuefrom

39.取日期时间的其他部分

S:

DATEPART和DATENAME函数（第一个参数决定）

O:

to_char函数第二个参数决定参数下表需要补充

yearyy,yyyyquarterqq,q（季度）monthmm,m（mO无效）

dayofyeardy,y（O表星期）daydd,d（dO无效）weekwk,ww（wkO无效）weekdaydw（O不清楚）

Hourhh,hh12,hh24（hh12,hh24S无效）minutemi,n（nO无效）secondss,s（sO无效）millisecondms（O无效）

40.当月最后一天

S:

不知道

O:

selectLAST_DAY（sysdate）valuefromdual

41.本星期的某一天（比如星期日）

S:

不知道

O:

SelectNext_day（sysdate,7）vauleFROMDUAL;

42.字符串转时间

S:

可以直接转或者selectcast（'2004-09-08'asdatetime）value

O:

SelectTo_date（'2004-01-05

22:

09:

38','yyyy-mm-ddhh24-mi-ss'）vauleFROMDUAL;

43.求两日期某一部分的差（比如秒）

S:

selectdatediff（ss,getdate（）,getdate（）+12.3）value

O:

直接用两个日期相减（比如d1-d2=12.3）

Select（d1-d2）*24*60*60vauleFROMDUAL;

44.根据差值求新的日期（比如分钟）

S:

selectdateadd（mi,8,getdate（））value

O:

Selectsysdate+8/60/24vauleFROMDUAL;

45.求不同时区时间

S:

不知道

O:

SelectNew_time（sysdate,'ydt','gmt'）vaule

FROMDUAL;

时区参数,北京在东8区应该是Ydt

ASTADT大西洋标准时间

BSTBDT白令海标准时间

CSTCDT中部标准时间

ESTEDT东部标准时间

GMT格林尼治标准时间

HSTHDT阿拉斯加—夏威夷标准时间

MSTMDT山区标准时间

NST纽芬兰标准时间

PSTPDT太平洋标准时间

YSTYDTYUKON标准时间

配置SQLServer数据库

EXECsp_dboption'pubs','readonly','True'此选项把数据库“pubs”设置为只读。

EXECsp_dboption'pubs'autoshrinktrue此选项把符合条件的“pubs”数据库文件设置为自动周期性收缩。

EXECsp_dboption'pubs'singleuser此命令每次只允许一个用户访问数据库。

DBCCSHRINKDATABASE（PUBS,10）

会减小“pubs”数据库中文件的大小，并允许有10%

的可用空间。

使用T-SQL创建表

createtableMyTable

（stdIDint,

stdNamevarchar（50）

）

自定义数据类型

EXECsp_addtypeCity,'nvarchar（15）',NULL

EXECsp_addtypePostCode,'nvarchar（6）',NULL

EXECsp_addtypeNCode,'nvarchar（18）',NULL

删除自定义数据类型

EXECsp_droptypecity

使用T-SQL创建表

CREATETABLECategories

（CategoryIDintIDENTITY（1,1）,

NOT

CategoryNamenvarchar（15）

NULL,

DescriptionntextNULL,

PictureimageNULL）

实体完整性实现

l主键约束

CREATETABLEjobs（

Job_idsmallintPRIMARYKEY,job_descvarchar（50）NOTNULL）

l唯一约束

CREATETABLEjobs（

Job_idsmallintUNIQUE）

标识列

CREATETABLEjobs（

Job_idsmallintIDENTIRY（2,1）PRIMARYKEY）

lUniqueidentifier数据类型和NEWID函数CREATETABLECustomer

（CustIDuniqueidentifierNOTNULLDEFAULTNEWID（）,

CustNamechar（30）NOTNULL）

INSERTCustomerVALUES（NEWID（）,'ASB'）

l引用完整性实现

ALTERTABLEdbo.Orders

ADDCONSTRAINTFK_Orders_CustomersFOREIGNKEY（CustomerID）REFERENCESdbo.Customers（CustomerID）

l更新表结构

ALTERTABLEMyTableALTERCOLUMNNullCOlNVARCHAR（20）NOTNULL

ALTERTABLEjobsADDHIRE_DATEDATETIMEALTERTABLEDoc_EDDROPCOLUMNColumn_B

ALTERTABLEDoc_EDWITHNOCHECKADD

CONSTRAINTExd_CheckCHECK（Column_a>1）

l在已有字段中增加标识约束时，必须先删除该字段，再新建字段。

altertablemytabledropcolumnuserid

altertablemytableadduseridintidentity（1,1）

l删除表

DROPTABLEAirlines_Master

lDEFAULT约束

USENorthwind

CREATETABLEABC（ASDintDefault8,

ASDEvarchar（20）Default'UNKNOWN'）

USENorthwind

ALTERTABLEdbo.CustomersADDCONSTRAINTDF_contactnameDEFAULT'UNKNOWN'FORContactName

lCHECK约束

CREATETABLEABCD

（ASDintCHECK（ASD<100）,

ASDEvarchar（80）Default'UNKNOWN'）

USENorthwind

ALTERTABLEEmployeesADDCONSTRAINTCK_birthdate

CHECK（BirthDate>'01-01-1900'ANDBirthDate

<'0-011-2010'）

PRIMARYKEY约束

CREATETABLEAWC

（ASDintCHECK（ASD<100）PRIMARYKEYASDEvarchar（80）Default'UNKNOWN'）

USENorthwind

ALTERTABLECustomersADDCONSTRAINTPK_CustomersPRIMARYKEY（CustomerID）

lUNIQUE约束

CREATETABLEAAC

（ASDintCHECK（ASD<100）PRIMARYKEY,

ASDEintUNIQUE）

USENorthwind

ALTERTABLESuppliersADDCONSTRAINT

U_CompanyNameUNIQUE（CompanyName）

lFOREIGNKEY约束

CREATETABLEACC

（ASDintCHECK（ASD<100）PRIMARYKEY,

ASDEintFOREIGNKEYREFERENCESAAC（ASD））

USENorthwind

ALTERTABLEOrdersWITHNOCHECKADDCONSTRAINTFK_Or_Cu

FOREIGNKEY（CustomerID）

REFERENCESCustomers（CustomerID）

通配符

通配符

解释

例子

II

一个字符

BranchLike'L__'

%

任意长度

Route_CodeLike'AMS-%'

[]

指定范围内

AirbusnoLike'AB0[1-5]'

[A]

不在括号中

AirbusnoLike'AB0[A1-5]'

向表中插入数据

语法：

INSERT[INTO]表名（字段列表）

VALUES（值列表）

例：

1.插入指定值的一条记录

INSERTINTOMyTable（PriKey,Description）

VALUES（123,'Adescriptionofpart123.'）

2.从查询中获得数据（多条）

INSERTINTOMyTable（PriKey,Description）

SELECTForeignKey,

DescriptionFROMSomeView

更新数据行

语法：

UPDATE表名SET字段名=新值，…WHERE条件

例：

UPDATETITLESSETPrice=Price+0.25*PriceWhereTitle_ID=‘TC777'

联接：

根据表之间的逻辑关系搜索多表中的数据

1．内联接

SELECT…FROM表1INNERJOIN表2ON表1.字段1=表2.字段1WHERE条件

例：

UPDATETITLESSETPrice=Price+10FromTitlesINNERJOINTitleAuthor

ONTitles.Title_IS=TitleAuthor.Title_IDWHERETitles.Title='SuShi,AnyOne?

'

2．外联接左联接：

左表与右表相联接的数据和左表的其他数据：

SELECT…FROM表1LEFTJOIN表2ON表1.字段1=表2.字段1WHERE条件

例：

UPDATETITLESSETPrice=Price+10FromTitlesLEFTOUTERJOINTitleAuthor

ONTitles.Title_IS=TitleAuthor.Title_IDWHERETitles.Title='SuShi,AnyOne?

'

右联接：

右表与左表相联接的数据和右表的其他数据：

SELECT…FROM表1RIGHTJOIN表2

ON表1.字段1=表2.字段1WHERE条件

例：

UPDATETITLESSETPrice=Price+10FromTitlesRIGHTOUTERJOINTitleAuthor

ONTitles.Title_IS=TitleAuthor.Title_IDWHERETitles.Title='SuShi,AnyOne?

'

全联接：

右表与左表相联接的数据及右表和左表的其他数据：

SELECT…FROM表1FULLJOIN表2ON

表1.字段1=表2.字段1WHERE条件

3．自联接：

表与其自身联接

SELECT…FROM表1asAJOIN表1asBONA.字段1=B.字段2WHERE条件

删除数据

DELETEFROM表WHERE条件

TRUNCATETABLE表--删除表中所有的记录，但不记日志

查询数据

1.数据库和所有者限定

SELECT*FROMNorthwind.dbo.Shippers

2.查询中使用常量和运算符

SELECTtitle_id+':

'+title+'->'+typeAsMyTitleFROMtitles

3.聚合函数

SELECTproductid,SUM（quantity）AStotal_quantityFROMorderhistGROUPBYproductid

显示所有type的值，并按type分组求平均值SELECTtype,AVG（price）FROMtitlesWHEREroyalty=10GROUPBYALLtype

条件中带有聚合函数的，要用Having子句

SELECTproductid,SUM（quantity）AStotal_quantity

FROMorderhist

GROUPBYproductidHAVING

SUM（quantity）>=30

4.模糊查询

WHEREcompanynameLIKE'%Restaurant%'

WHEREfaxISNULL

WHEREcountryIN（'Japan','Italy'）

WHEREunitpriceBETWEEN10AND20

1.说明：

复制表（只复制结构,源表名：

a新表名：

b）

SQL:

select*intobfromawhere1<>12.说明：

拷贝表（拷贝数据,源表名：

a目标表名：

b）

SQL:

insertintob（a,b,c）selectd,e,ffromb;

3.说明：

显示文章、提交人和最后回复时间

SQL:

selecta.title,a.username,b.adddatefromtablea,（selectmax（adddate）adddatefromtablewheretable.title=a.title）b

4.说明：

外连接查询（表名1：