sqlserver基础知识.pdf

该【sqlserver基础知识 】是由【cjc201601】上传分享，文档一共【28】页，该文档可以免费在线阅读，需要了解更多关于【sqlserver基础知识 】的内容，可以使用淘豆网的站内搜索功能，选择自己适合的文档，以下文字是截取该文章内的部分文字，如需要获得完整电子版，请下载此文档到您的设备，方便您编辑和打印。:.
SQLSERVER

1 oracle

!"!#$%&'!"!()
Oracle*#+,-./01234$SQLServer*'567Windows89,-.
34':;<12)
=>9?***@A*"B:CDE*F=>GHOracleFIJKLGH
MNOracle*O4PQRSTUV3WXYFZ[\]^_`abcdAefghi)X
YFZj4k+^lmhn4opqr)Ps"B:k#tu)$SQLServervwxWindows
MNyz{|}W ~€ySQLSeverWindowGH‚ƒ7Windows=>9?„
…†ao‡34hˆ‰7MicrosoftPGH„a‰Š Oracle‹k+)Windows=>9?
***@A‰ŒF‰#Ž:JT‘BMicrosoft’“RSTU:”XYŠ•A0WindowsGH–
—˜.wJ
Windows‰x™šSQLServer›xœ]ž()Ÿ=>GHz Oracle:¡¢
£¤¥SQLServery)
Ÿ¦§FZ¨0Oracle©¢ª«¬W_(ª«­®ISOU¯ª«$SQLServer°±J¬
W²³©¢ª«)zh´«µyOracle©¢‰:(¥SQLServer)
·¸]{¹º»¼½¾˜¿:¹º‰xy˜½¾‰xÀÁ˜¿ÂyÃÄÅ+ÂÆ
z¹º':{kÇÈÉÊ)ËÌ°MNÍ{*9?)ÎÏ_зÑÒÓÔÓÕ4Ö
ÎÏy×،ZÙÚÛÕ4)Orcale*ÜÝ:Þ SQLServer*(6²³Oracle
* SQLServer*ÜÝ(ßà³+]h´OracleÐáÅÕâ~(d®:½¾qrÒ
ÓÁãOraclek+qrÒÓeäå·¸æMicrosoftžçèÕSQLServerqrÒÓâ Oracle
é}ÜÝê)ë¥SQLServerwxWindowsìMNí5îïª6SQLServerÍ*F
‰: ~ð)Oracle‰x£ñ…ƒb+4Y$SQLServer‰x£ñ+4Y|}Wò
Ÿóy)
=>SQLServerµ} Orcaleôõö÷4øJava7DOTNETPGHù®ú|
:Oracle7SQLServerÀOracleû´ú:¥Java#üDqr:Dosû´ýþ
SQLPIus':SQLServer:ÿVB

DOS
SQLServer
!"#$Oracle%&'(
)*+,-./0123456SQLServer8+9:;<=>
Orcale?***@ABSQLServer?***@ACDEFGHIJ>KLMN<"QRNS
TUVWXY?***@Z[\]^_`abcd
SQLServer2000IefgWebhi?***@Ajk6lmnopqrsthi\]
InternetuBvwxyz{|}-~e&l€,Y‚,Ymƒ,„)*…†‡ˆd‰Š)Oracle
‹&Œl9,ŽSQLServerEu‘’8G…†.Hc“SQLServer+”•6–‘’
GH—{˜™š›Cœ?***@AI?***@A‰Š\]ž@'($Ÿ B`abc¡¢˜™!£R
:.
2Y¤sql
SQLZW¥
DDL?***@R¦qr(CREATE,ALTER,DROP,DECLARE)
DML?@§¨qr(SELECT,DELETE,UPDATE,INSERT)
DCL)?@qr(GRANT,REVOKE,COMMIT,ROLLBACK)
©ª,¬­®¯`aq°¥
1Y±²¥³´?***@A
CREATEDATABASEdatabase-name
2Y±²¥µ¶?***@A
dropdatabasedbname
3Y±²¥f·sqlserver
-³´f·?@device
USEmaster
EXECsp_addumpdevice'disk¥'testBack','c:\mssql7backup\'¹
-º»f·
BACKUPDATABASEpubsTOtestBack
4Y±²¥³´¼½
createtabletabname(col1typel[notnull][primarykey],col2type2[notnull],..)
SQLServer<SQL-92¾¿,6?***@WXÀ¦Á&ÂD"ÃÄÅÆ
?***@WXÀ¦Á
ÇÈ?É
Ê?
bigint
Ë-2Ì63(-9223372036854775808)2A63-1(9223372036854775807)ÊX?@(Î&?
Ï
int
Ë-2Ì31(-2,147,483,648)2Ì31-1(2,147,483,647)ÊX?@(Î&?É)
smallint:.
Ë-2Ì15(-32,768)2Ì15-1(32,767)Ê??@
tinyint
Ë0255Ê??@
bit
bit
1Ñ0Ê??@
decimalnumeric
decimal
Ë-10Ì38+110Ì38-1ÒRǏBÓ?Ô?É?@
numeric
Õ-udÀÖdecimalo
moneysmallmoney
money
×Ø?@Ù®Ö-2A63(-922,337,203,685,477,5808)Ú2A63-1(+922,337,203,685,)
ÛÜÇÈ×ØÝÔÞZÛß
smallmoney
×Ø?@Ù®Ö-214,Ú+214,ÛÜÇÈ×ØÝÔÞZÛß
àá?É
float
Ë-+308+308âŠÇ?É
real
Ë-+38+38âŠÇ?É
datetimesmalldatetime
datetime
Ë1753ã1ä1å9999ã12ä31ååæBçÜ?@ÇÈ‘ZÛèé(Ñ
êé)
smalldatetime
Ë1900ã1ä1å2079ã6ä6ååæBçÜ?@ÇÈZë
Éìí
char
ÒRîïUnicodeÉì?@ðñî<Éì:.
varchar
lòîïUnicode?@ðî<8,000Éì
text
lòîïUnicode?@ðñî<2Ì31-1(2,147,483,647)Éì
UnicodeÉìí
nchar
ÒRîUnicode?@ðñî<4,000Éì
nvarchar
lòîUnicode?@Gðñî<4,000ÉìsysnameIóôR¦?@
WX.Õ-udÀÖnvarchar(128),Öõ?***@Aö÷ø
ntext
lòîUnicode?@Gðñî<2Ì30-1(1,073,741,823)Éì
ùzÉìí
binary
ÒRîùz?@Gðñî<8,000Éú
varbinary
lòîùz?@Gðñî<8,000Éú
image
lòîùz?@Gðñî<2Ì31-1(2,147,483,647)Éú
G+?***@WX
cursor
ûoõ
sql_variant
JœOüSQLServerhi—œ?***@WX(textYntextYtimestampBsql_variant¶y)
Ù?***@WX
table
œ‰ý?***@WXOü\þVÿ

timestamp



uniqueidentifier

Acreatetabletab_newliketab_old()
Bcreatetabletab_newasselectcoll,col2-fromtab_olddefinitiononly
5!"#
$%:droptabletabname:.
6!"#:
()*+)+,-UAltertabletabnameaddcolumncoltype
$%
,.!/0
,.
7!"#
1)*23Altertabletabnameaddprimarykey(col)
"#
$%23Altertabletabnamedropprimarykey(col)
8!"#
45Icreate[unique]indexidxnameontabname(col--.)
6%45Idropindexidxname
7489:;0<0=>$%?
9!"#
***@Acreateviewviewnameasselectstatement
$%***@Adropviewviewname
10!"#B,CDEFsqlGH
IJ:select*fromtableiwhere
KLinsertintotablei(fieldl,field2)values(value1,value2)
$%:deletefromtablelwhere
updatetablelsetfieldl=value1where
MNselect*fromtablelwherefieldllike'%value1%,HlikeGOPQR,MTU!
WXselect*fromtablelorderbyfieldl,field2[desc]
Yselectcount*astotalcountfromtablel
Z[selectsum(fieldl)assumvaluefromtablel:.
\]selectavg(fieldl)asavgvaluefromtablei
^_selectmax(fieldl)asmaxvaluefromtable1
^`selectmin(fieldl)asminvaluefromtablei
11!"#B,abcdefg
AUNIONefh
UNIONefhijklmno,p(qrTABLE1[TABLE2)stuvwx?yz{|}
,p~ALLUNION
€(UNIONALL),:t%?yo‚ƒ„…{|
:9†‡TABLE1ˆ9†‡TABLE2
BEXCEPTefh
EXCEPTefhij‰Š‹ŒTABLE1v:ŒTABLE2vst%‹?yz{|}
,p
~ALLEXCEPT
€(EXCEPTALL),:t%?y
CINTERSECTefh
INTERSECTefhijŽ‰ŠTABLE1[TABLE2vst%‹?yz{|}
,p
~ALLINTERSECT
€(INTERSECTALL),:t%?y
7efgB,Mdp=>9

12!"#‘’“
A!leftouterjoin
”‘’“(”’“)p
B‰Š’“•–‰Š”’“‹
SQL:,,,,,=
Brightouterjoin:
—‘’“(—’“)p
˜‰Š’“•–’“‰Š—’“‹
Cfullouterjoin
™‘’“:š‰Šh›’“•–œ‰Šo,’“v‹ž
m_Ÿ† 
¡:¢sqlGH:.
1!"#y£(Žy£p¤¥¦a¦b)
O
select*intobfromawhere1<>1
O§:selecttop0*intobfroma
2!"#¨©(¨©¥¦aª«¦b)
insertintob(a,b,c)selectd,e,ffromb;
3!"#¬­®¨©(¯°±²³´)
insertintob(a,b,c)selectd,e,ffrombin'¯°'where¶·
q¸..frombin",&(',.',)&"\"&"'where..
4!"#¸Md(¦1a¦2b)
selecta,b,cfromawhereaIN(selectdfromb)¹º:selecta,b,cfromawhereaIN(1,2,3)
5!"#»¼½¾!¿ÀÁ[^ÂÃy®
,,,(selectmax(adddate)adddatefromtablewhere
=)b
6!"#‘’“Md(¦1a¦2b)
,,,,,=
7!"#ŒÄ***@AMd(¦1a)
select*from(SELECTa,b,cFROMa)>1;
8!"#betweenO,betweenÅ£MƉŠÇÈÉÊ,notbetween:‰Š
select*fromtableiwheretimebetweentimelandtime2
selecta,b,c,fromtablelwhereanotbetweenÊ1andÊ2
9!"#inËO
select*fromtablelwherea[not]in('{tT/Ê2'/Ê4'Ê6'):.
10!"#oÌÍÎ$%2vόÐvÑÒÓ
deletefromtablelwherenotexists(select*=)
3!SQLServer2000ÔÕÖSQLGH
 ×F
select@***@version
ÔØB‚SQLSERVERÙÚÛÂ×F›




-031

 ‹ŒÜÝÞßàáâ
execmaster..xp_msver
 ãäâ
sp_configure
 ãä®
selectconvert(varchar(30),login_time,120)frommaster..sysprocesseswherespid=1
M åæݦ[çq¦
print'ServerName..............:'+convert(varchar(30),@***@SERVERNAME)
print'Instance.................:'+convert(varchar(30),@***@SERVICENAME):.
 ‹¦èé_`
sp_helpdb
?ê¦SQL
sp_renamedb'old_dbname','new_dbname'
 ‹ëìžÒÓ

M ‹ë‹íîïÒÓ

/yðñåæÝòóë;ôfix_orphan_userõF¹ºLonellserjö
0÷,²øëí2
sp_changeobjectowner[***@objectname=]'object',[***@newowner=],owner'
7ù0²ø¦wúûü;ýþÿ



add_login_to_aserverW*
!"#$%&'()

7.*+

,-

8.!#!0%&12
sp.******@objname
3spjoptables41N56785009$!#!0%&:;:
sp_******@objname:.
3=>?:;***@A
SP_******@objname
clustered:;BCDEFGHIJ$:;KLMNOP
%QRDMLSTUVWX9YZ[\clustered:;]^$fillfactor_`67R
!#!0%&]^
sp_******@objname
9.abcd
******@database_name
sp_stored_procedures
defg
sp_helptext****@procedure_name'
hi!******@str%&mn
selectdistinctobject_name(id)fromsyscommentswheretextlike'%***@str%'
oZpqrdsAStupWITHENCRYPTION
,vqpqd
sp_decrypt
10.aw
sp_who
SQLServerxayzw
sp_who'active'
SQLServera{***@A
spjock
w|1--50BSQLServer}~€$w|150‚Bƒ+wspidBw„|,dbid
B„|,objidB%&„|w†s‡ˆSQL‰Š‹:.
dbccinputbuffer()
Œ1Žw‘sp_who3’+“w”ˆSQL‰Š
sp_who3
•–{sp_who_lock
sp_who_lock
11.—˜™š›œž
bc™š›œ12
dbccsqlperf(logspace)
Ÿ !¡™š›œO1$—˜¢£¤¥¦§™š$—˜‘***@database_name_log12£¨8M
******@database_namewithnoJog
dbccshrinkfile(***@database_name_log,5)
SQLServerSQL‰Šž‹
setstatisticstime{on|off}
setstatisticsio{on|off}
©ª§«¬­‡ˆ®¯$s­V°
$­$«¬±®²±®¯(D)-Ctrl-Lr³´µ¶·¸a
©ª$›
§«¬­‡ˆ®¯
setshowplan_all{on|off}
setshowplanjext{on|off}
setstatisticsprofile{on|off}:.
13.¹º»¼½¾¿$NTÀœÁ
a3624|½¾$Â¥ž
ÃÄÅÆÇHa;¹º»¼È½¾9$ɑsrÊËÌ
ä¥É‘ÍÂ¥ST
alterdatabase[***@error_database_name]setsingle_user
Â¥¹º»¼½¾9
dbccchecktable(,***@error_table_name',repair_allow_data_loss)
r³ÎÏÐÂ¥¹º»¼½¾2Ñm‹
dbcccheckdb(,***@error_database_name',repair_allow_data_loss)
alterdatabase[***@error_database_name]setmulti_user
CHECKDBc30_‹
repair_allow_data_losshÒ%ˆÓwˆVÔÕÖVԐ†VÔ½¾×ØوrÓ½¾$
ÚÛÜÝÞß›
%&$à¡Â¥áâã¼»¡äåÂ¥STsÀ#æçèé
êëbÍ=Ÿ êëÂ¥$ìíâic½¾$Çîïwˆ¤¥Ÿ ðbñòÂ
¥ó'ôõö÷!0½¾Â¥$ìøö÷ùúÕûî¥¥¥æç‘$ü
repairjastwˆ2׺ý¿Â¥ST$ŸÂ¥\þÿ




repair_rebuildrepairjast!
"#$%&'()
(+,-
)

(
4/0123ORACL45


S:selectabs(-1)value
O:selectabs(-1)valuefromdual:.
2.;<(=)
S:selectceiling(-)value
0:selectceil(-)valuefromdual
3;<(T)
S:selectfloor(-)value
0:selectfloor(-)valuefromdual
4.;<(V;)
S:selectcast(-)value
0:selecttrunc(-)valuefromdual
[
S:selectround(,4)
0:selectround(,4)
^_`
S:selectExp(l)**********
0:selectExp(l)
7.;e^_4
S:selectlog(**********)value1
0:selectln(**********)valuefromdual;1
8,;10^_4
S:selectloglO(10)value1
0:selectlog(10,10)valuefromdual;1
9.;ij
S:selectSQUARE(4)value16
0:selectpower(4,2)valuefromdual16
10.;ijp
S:selectSQRT(4)value2
0:selectSQRT(4)valuefromdual2:.
^_`
S:selectpower(3,4)value81
0:selectpower(3,4)valuefromdual81
12.;uv
S:selectrand()value
0:(0,1)valuefromdual;
13.;z{
S:selectsign(-8)value-1
0:selectsign(-8)valuefromdual-1
14.|}~
S:SELECTPI()
0:ƒ„
,cos,tan…†
‡ˆ^‰Š
‹+:selectsin(PI()/2)valueŽ1(SQLServer)
,Acos,Atan,Atan2‡ˆ
17.‡ˆ‘ˆ’“(SQLServer,Oracleƒ„)
DEGREES–‡ˆ-—‘ˆ
RADIANS–‘ˆ-—‡ˆ

™š›=7
S:selectmax(value)valuefrom
(select1value
union
select-2value
union
select4value:.
union
select3value)a
0:selectgreatest(1,-2,4,3)valuefromdual
™š›T7
S:selectmin(value)valuefrom
(select1value
union
select-2value
union
select4value
union
select3value)a
0:selectleast(1,-2,4,3)valuefromdual
20.+œžnull7(F2null
10 ¡)
S:selectFl,IsNull(F2,10)valuefromTbl
0:selectFl,nvl(F2,10)valuefromTbl
¢z£{
S:selectascii('a')value
0:selectascii('a')valuefromdual
22.¥£{r¢z
S:selectchar(97)value
0:selectchr(97)valuefromdual
23.§¨
S:select'll'+'22'+'33'value
0:selectCONCATC11,22)33valuefromdual:.
23.ª«Š¬­3
S:selectCHARINDEX('s'sdsq',2)value
O:selec