SQLServer常用函数对比.docx
- 文档编号:8302533
- 上传时间:2023-01-30
- 格式:DOCX
- 页数:40
- 大小:49.10KB
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:
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- SQLServer 常用 函数 对比