常用SQL數(shù)據(jù)庫知識(shí)點(diǎn)匯總

一、語句:

1、說明:創(chuàng)建數(shù)據(jù)庫

CREATEDATABASEdatabase-name

2、說明:刪除數(shù)據(jù)庫

dropdatabasedbnamc

3、說明：備份sqlserver

—?jiǎng)?chuàng)建備份數(shù)據(jù)的device

USEmaster

EXECspaddumpdevice'disk',,testBack,,'c:\mssql7backup\MyNwindl.dat'

—開始備份

BACKUPDATABASEpubsTOtestBack

4、說明:創(chuàng)建新表

createtabletabname(col1typel[notnull][primarykey],col2type2[notnull],..)

5、說明:刪除新表

droptabletabname

6、說明:增加一個(gè)列

Altertabletabnameaddcolumncoltype

7、說明:創(chuàng)建索引：

create[unique]indexidxnameontabname(cob**.)

刪除索引:dropindexidxname

注:索引是不可更改的,想更改必須刪除重新建.

8、說明:創(chuàng)建視圖:

createviewviewnameasselectstatement

刪除視圖：dropviewviewname

看視圖的源碼:sphelptextviewname

10、說明：幾個(gè)簡單的基本的sql語句

選擇:select*fromtablelwhere條件范圍

插入：insertintotablel(fieldl,field2)values(valuel,value2)

刪除:deletefromtablelwhere條件范圍

更新:updatetablelsetfieldl=valuelwhere條件范圍

查找:select*fromtablelwherefieldllike'%valuel%'

排序:select*fromtablelorderbyfieldl,field2[desc]

總數(shù):selectcountastotalcountfromtablel

求和:selectsum(fieldl)assumvaluefromtablel

平均:selectavg(fieldl)asavgvaluefromtablet

最大:selectmax(fieldl)asmaxvaluefromtablel

最小:selectmin(fieldl)asminvaluefromtablel

11、說明:幾個(gè)高級(jí)查詢運(yùn)算詞

A:UNION運(yùn)算符

UNION運(yùn)算符通過組合其他兩個(gè)結(jié)果表(例如TABLEI和TABLE2)并消去表中任何重復(fù)行

而派生出一個(gè)結(jié)果表.當(dāng)ALL隨UNION一起使用時(shí)(即UNIONALL),不消除重復(fù)行.兩

種情況下，派生表的每一行不是來自TABLE1就是來自TABLE2.

B:EXCEPT運(yùn)算符

EXCEPT運(yùn)算符通過包括所有在TABLE1中但不在TABLE2中的行并消除所有重復(fù)行而

派生出一個(gè)結(jié)果表.當(dāng)ALL隨EXCEPT一起使用時(shí)(EXCEPTALL),不消除重復(fù)行.

C:INTERSECT運(yùn)算符

INTERSECT運(yùn)算符通過只包括TABLE1和TABLE2中都有的行并消除所有重復(fù)行而派生

出一個(gè)結(jié)果表.當(dāng)AI.I.隨INTERSECT一起使用時(shí)(INTERSECTALL),不消除重復(fù)行.

注:使用運(yùn)算詞的幾個(gè)查詢結(jié)果行必須是一致的.

12、說明:使用外連接

A、left(outer)join:左外連接(左連接)：結(jié)果集幾包括連接表的匹配行，也包括左連接表

的所有行.SQL:selecta.a,a.b,a.c,b.c,b.d,b.ffromaLEFTOUTJOINbONa.a=b.c

B:right(outer)join:右外連接(右連接)：結(jié)果集既包括連接表的匹配連接行，也包括右

連接表的所有行.

C:full/cross(outer)join:全外連接:不僅包括符號(hào)連接表的匹配行,還包括兩個(gè)連接表

中的所有記錄.

12、說明：分組:Groupby:一張表，一旦分組完成后，查詢后只能得到組相關(guān)的信息.

組相關(guān)的信息：(統(tǒng)計(jì)信息)count,sum,max,min,avg

13、說明:復(fù)制表(只復(fù)制結(jié)構(gòu)，源表名:a新表名:b)

法一:select*intobfromawhere1<>1

法二：selecttop0*intobfroma

14、說明:拷貝表(拷貝數(shù)據(jù)，源表名:a目標(biāo)表名:b)

insertintob(a,b,c)selectd,e,ffroma;

15、說明:跨數(shù)據(jù)庫之間表的拷貝(具體數(shù)據(jù)使用絕對(duì)路徑)

插入不存在的表:select*intoanywell2013.dbo.bfromanywe11.dbo.a;

插入己存在的表：insertintoanywel12013.dbo.b(a,b,c)selectd,e,ffrom

anywell.dbo.a;

16、說明:子查詢(表名l:a表名2:b)

selecta,b,cfromawhereaIN(selectdfromb)或selecta,b,cfromawhereaIN

(1,2,3)

17、說明:顯示文章、提交人和最后回復(fù)時(shí)間

selecta.title,a.username,b.adddatefromtablea,(selectmax(adddate)adddatefrom

tablewheretable.title=a.title)b

18、說明:外連接查詢(表名l:a表名2:b)

selecta.a,a.b,a.c,b.c,b.d,b.ffromaLEFTOUTJOINbONa.a=b.c

19、說明：between的用法,between限制查詢數(shù)據(jù)范圍時(shí)包括了邊界值,notbetween不包括

select*fromtablelwheretimebetweentimelandtime2

se.ecta,b,c,fromtablelwhereanotbetween數(shù)值1and數(shù)值2

20、說明：in的使用方法

select*fromtablelwherea[not]in(，值1'值2'J值4'J值6')

21、說明:兩張關(guān)聯(lián)表，刪除主表中已經(jīng)在副表中沒有的信息

deletefromtablelwherenotexists(select*fromtable2where

tablel.fieldl=table2.fieldl)

22、說明：四表聯(lián)查問題：

SP'pct*fromainnprjoinhona.a=b.hinnprjoincona.a=c.c.innprjoindona.a=d.d

where....

23、說明：日程安排提前五分鐘提醒

SQL:select*fromreapwheredatediff(*minute，,kssj,getdate())>5

24、說明：前10條記錄

selecttop10*formtablelwhere條件范圍

25、說明:選擇在每一組b值相同的數(shù)據(jù)中對(duì)應(yīng)的a最大的記錄的所有信息(類似這樣的用法

可以用于論壇每月排行榜,每月熱銷產(chǎn)品分析,按科目成績排名,等等.)

selecta,b,cfromtablcnametawherea=(selectmax(a)fromtablcnametbwhere

tb.b=ta.b)

26、說明:包括所有在TableA中但不在TableB和TableC中的行并消除所有重復(fù)行而

派生出一個(gè)結(jié)果表

(selectafromtableA)except(selectafromtableB)except(selectafromtableC)

27、說明：隨機(jī)取出10條數(shù)據(jù)

selecttop10*fromtablenameorderbynewid；)

28、說明：隨機(jī)選擇記錄

selectnewid()

29、說明:刪除重復(fù)記錄

1),deletefromtablenamewhereidnotin(selectmax(id)fromtablenamegroupby

coll,col2,...)

2),selectdistinct*intotempfromtablename

deletefromtablename

insertintotablenameselect*fromtemp

評(píng)價(jià)：這種操作牽連大量的數(shù)據(jù)的移動(dòng),這種做法不適合大容量但數(shù)據(jù)操作

3),例如:在一個(gè)外部表中導(dǎo)入數(shù)據(jù)，由于某些原因第一次只導(dǎo)入了一部分，但很難判斷具體位

置，這樣只有在下一次全部導(dǎo)入,這樣也就產(chǎn)生好多重復(fù)的字段,怎樣刪除重復(fù)字段

altertabletablename

一添加一個(gè)自增列

addcolumn_bintidentity(1,1)

deletefromtablenamewherecolumnbnotin(

se.ectmax(column_b)fromtablenamegroupbycolumnl,column2,...}

altertabietabienamedropcolumncolumnb

30、說明:列出數(shù)據(jù)庫里所有的表名

selectnamefromsysobjectswheretype='U'//U代表用戶

31、說明:列出表里的所有的列名

selectnamefromsyscolumnswhereid=object_id(*TableName*)

32、說明：列示type、vender、pcs字段，以type字段排列,case可以方便地實(shí)現(xiàn)多重選擇，

類似seiect.中的case.

selecttype,sum(casevenderwhen'A'thenpcselse0end),sum(casevenderwhen'C'

thenpcselse0end),sum(casevenderwhen'B'thenpcselse0end)FROMtablenamegroup

bytype

顯示結(jié)果：

typevenderpcs

電腦A1

電腦A1

光盤B2

光盤A2

手機(jī)B3

手機(jī)C3

33、說明:初始化表a(若有自增量ID,初始化后則會(huì)從1開始)

truncatetablea(不能加WHERE條件范圍)

34、說明：選擇從10到15的記錄

selecttop5*from(selecttop15*fromtableorderbyidasc)table_另J名order

byiddesc

35、1=1,1=2的使用，在SQL語句組合時(shí)用的較多

"where1=1”是表示選擇全部“where1=2”全部不選，

36、收縮數(shù)據(jù)庫

一重建索引

DBCCREINDEX

DBCCINDEXDEFRAG

一收縮數(shù)據(jù)和日志

DBCCSHRINKDB

DBCCSHRINKFILE

37、創(chuàng)建觸發(fā)器

CREATETRIGGER[dbo].[atri]

ON[dbo].[a]

FORUPDATE,INSERT,DELETEAS

GO

更新觸發(fā)器：ALTERTRIGGER[dbo].[atri]

ON[dbo].[a]

FORUPDATE,INSERT,DELETEAS

GO

刪除觸發(fā)器：DROPTRIGGER[dbo].[atri]

38、創(chuàng)建存儲(chǔ)過程

CREATEPROCEDURE[dbo].[P_XSBB](@USERIDASVARCHAR(80),@RQCASDATETIME,@RQDAS

DATETIME)

AS

GO

更新存儲(chǔ)過程：ALTERPROCEDURE[dbo].[PXSBB](0USERIDASVARCHAR(80)z(?RQCAS

DATETIME,@RQDASDATETIME)

AS

GO

刪除存儲(chǔ)過程：DROPPROCEDURE[dbo].[PXSBB)

執(zhí)行存儲(chǔ)過程：EXECP_XSBB

二、函數(shù)：

1、聚合函數(shù)

報(bào)表的典型用途是從全部數(shù)據(jù)中提取出代表一種趨勢(shì)的值或者匯總值，這就是聚合的意義.

聚合函數(shù)應(yīng)用特定的聚合操作并返回一個(gè)標(biāo)量值(單一值).返回的數(shù)據(jù)類型對(duì)應(yīng)于該列或者傳遞到函數(shù)中的

值.聚合經(jīng)常和分組、累積以及透視等表運(yùn)算一起使用，生成數(shù)據(jù)分析結(jié)果.

AVG()函數(shù)

AVG。函數(shù)用于返P1一組數(shù)值中所有非空數(shù)值的平均值.

SELECTAVG(Score)

COUNT。函數(shù)

COUNT。函數(shù)用于返回一個(gè)列內(nèi)所有非空值的個(gè)數(shù)，這是一個(gè)整型值.

SELECTCOUNT(rs)

MIN()與MAX。函數(shù)

MIN()函數(shù)用于返回一個(gè)列范圍內(nèi)的最小非空值;MAX。函數(shù)用于返回最大值.這兩個(gè)函數(shù)可以用于大多數(shù)

的數(shù)據(jù)類型，返回的值根據(jù)對(duì)不同數(shù)據(jù)類型的排序規(guī)則而定.

SELECTMIN(RQ)

SELECTMAX(RQ)

SUM()函數(shù)

SUM()函數(shù)是最常用的聚合函數(shù)之一，它的功能很容易理解:和AVG()函數(shù)一樣，它用于數(shù)值數(shù)據(jù)類型,返回

一八列范圍內(nèi)所有非空值的總和.

SELECTSUM(JE)

2、轉(zhuǎn)換函數(shù)

數(shù)據(jù)類型轉(zhuǎn)換可以通過CAST()和CONVERT。函數(shù)來實(shí)現(xiàn).大多數(shù)情況下,這兩個(gè)函數(shù)是重疊的,它們反映

了SQL語言的演化歷史.這兩個(gè)函數(shù)的功能相似,不過它們的語法不同.雖然并非所有類型的值都能轉(zhuǎn)變?yōu)槠渌?/p>

數(shù)據(jù)類型,但總的來說,任何可以轉(zhuǎn)換的值都可以用簡單的函數(shù)實(shí)現(xiàn)轉(zhuǎn)換.

CAST()函數(shù)

CAST。函數(shù)的參數(shù)是一個(gè)表達(dá)式，它包括用AS關(guān)鍵字分隔的源值和目標(biāo)數(shù)據(jù)類型.

SELECTCAST(1123'ASint)

CAST。函數(shù)和CONVERT。函數(shù)都不能執(zhí)行四舍五入或截?cái)嗖僮?

SELECTCAST('123.41ASdecimal(9,2))

CONVERT。函數(shù)

對(duì)于簡單類型轉(zhuǎn)換,CONVERT。函數(shù)和CAST。函數(shù)的功能相同，只是語法不同.CAST。函數(shù)?般更容易使

用，其功能也更簡單.CONVERT。函數(shù)的優(yōu)點(diǎn)是可以格式化日期和數(shù)值,它需要兩個(gè)參數(shù)：第1個(gè)是目標(biāo)數(shù)據(jù)類型,

第2個(gè)是源數(shù)據(jù).

SELECT'DefaultDate:'+CONVERT(Varchar(50)zGETDATE()z100)

STR()函數(shù)

這是一個(gè)將數(shù)字轉(zhuǎn)換為字符串的快捷函數(shù).這個(gè)函數(shù)有3個(gè)參數(shù):數(shù)值、總長度和小數(shù)位數(shù).如果數(shù)字的整數(shù)

位數(shù)和小數(shù)位數(shù)(要加上小數(shù)點(diǎn)占用的一個(gè)字符)的總和小于總長度,對(duì)結(jié)果中左邊的字符將用空格填充.在下面

第1個(gè)例子中,包括小數(shù)點(diǎn)在內(nèi)一共是5個(gè)字符.結(jié)果顯示在網(wǎng)格中,顯然左邊的空格被填充了.這個(gè)調(diào)用指定，總

長度為8個(gè)字符.小數(shù)位為4位：

SELECTSTR(123.4,8,4)

3、日期函數(shù)

這些函數(shù)可以操作DateTime與SmallDateTime類型的值.有些函數(shù)可用于解析日期值的日期與時(shí)間部分,

有些函數(shù)可用于比較、操縱日期/時(shí)間值.

DATEADD。函數(shù)

DATEADD。函數(shù)用于在日期/時(shí)間值上加上日期單位間隔.比如，要得到2007年4月29日起90天后的日

期,可以使用下列語句：

SELECTDATEADD(DAY,90,'4-29-2007')

DATEDIFF。函數(shù)

DATEADD。和DATEDIFF。函數(shù)可以看作一對(duì)表兄弟,有點(diǎn)像乘法與除法.在等式的兩端有4個(gè)元素:起始

日期、時(shí)間間隔(datepart)、差值和最終日期.如果已知其中的三個(gè)值,就可以求出第4個(gè)值.如果在DATEADD。

函數(shù)中使用起始日期、?個(gè)整型值和?個(gè)時(shí)間間隔，就可返P1與起始日期相關(guān)的最終日期值.如果提供了起始日期、

時(shí)間間隔和最終日期，DATEDIFF。函數(shù)就可以返回差值.

SELECTDATEDIFF(MONTH,‘9-8-19891,10-17-1991')

DATEPART()與DATENAME()函數(shù)

這兩個(gè)函數(shù)用于返回datetime或者shortdatetime值的日期部分.DATEPART。函數(shù)返回一個(gè)整型

值;DATENAME。函數(shù)返網(wǎng)一個(gè)包含描述性文字的字符串.比如，將日期4-29-1988傳遞給DATEPART0函數(shù)，

如指定返回月份值，則返回?cái)?shù)字4:

SELECTDATEPART(MONTH,‘4-29-1988’)

GETDATE。與GETUTCDATE。函數(shù)

這兩個(gè)函數(shù)都用于返回datetime類型的當(dāng)前日期與時(shí)間.GETUTCDATE。函數(shù)使用服務(wù)器上的時(shí)區(qū)設(shè)置

來求出UTC時(shí)間,這和格林威治標(biāo)準(zhǔn)時(shí)間或飛行員所說的“祖魯時(shí)”(ZuluTime)是一樣的.兩個(gè)函數(shù)都能精確到

3.33亳秒.

SELECTGETDATE()

SELECTGETUTCDATE()

SELECTDATEDIFF(HOUR,GETDATE(),GETUTCDATE(：1)

SYSDATETIME。和SYSUTCDATETIME。函數(shù)

這兩個(gè)SQLServer2008函數(shù)等價(jià)于GETDATE。和GETUTCDATE。函數(shù)，但不是返回datetime數(shù)據(jù)

類瑪?shù)慕Y(jié)果,而是返回SQLServer2008新的datetime2數(shù)據(jù)類型的結(jié)果，該數(shù)據(jù)類型可以精確到100納秒，

當(dāng)然這取決于服務(wù)器安裝的硬件.

SELECTSYSDATETIMEO

SELECTSYSUTCDATETIMEO

DAY。、MONTH。和YEAR。函數(shù)

這三個(gè)函數(shù)分別返回以整數(shù)表示的datetime或者smalldatetime類型值的日、月、年.它們的用途很廣泛，

如可以創(chuàng)建獨(dú)特的個(gè)性化日期格式.假設(shè)需要?jiǎng)?chuàng)建一個(gè)自定義的日期值作為字符串，通過將這三個(gè)函數(shù)的輸出結(jié)

果轉(zhuǎn)換成字符類型,然后進(jìn)行連接操作,就可以對(duì)輸出結(jié)果以任何形式進(jìn)行組合了：

SELECT'Year:'+CONVERT(varchar(4),YEAR(GETDATE()))

+IMonth:'+CONVERT(varchar(2)zMONTH(GETDATE()))

+Day:'+CONVERT(varchar(2),DAY(GETDATE()))

ASCII。、CHAR。、UNICODE。和NCHAR()函數(shù)

這四個(gè)函數(shù)是相似的，它們都可以在字符和字符的標(biāo)準(zhǔn)數(shù)字表示之間轉(zhuǎn)換.美國標(biāo)準(zhǔn)信息交換碼(American

StandardCodeforInformationInterchange,ASCH)標(biāo)準(zhǔn)字符集包含128個(gè)字母、數(shù)字和標(biāo)點(diǎn)符號(hào).這個(gè)

字符集是IBMPC體系結(jié)構(gòu)的基礎(chǔ),雖然有些字符現(xiàn)在看來己經(jīng)很口.老了，但還是被保留了下來，且仍是現(xiàn)代計(jì)算

機(jī)技術(shù)的核心.如果在計(jì)算機(jī)上使用英語,則鍵盤上的每個(gè)字符都是用ASCII碼表示的.

SELECTASCII(*A')

SELECTCHAR(65)

SELECTUNICODE(108)

SELECTNCHAR(220)

CHARINDEX()和PATINDEX()函數(shù)

CHARINDEX。是原始的SQL函數(shù),用于尋找在一個(gè)字符串中某子字符串第一次出現(xiàn)的位置.如函數(shù)名所示,

這人函數(shù)返回一個(gè)整型值，表示某子字符串的第一個(gè)字符在整個(gè)字符串中的位置索引.

SELECTCHARINDEX('sh'zWashington*)

SELECTPATINDEX('%M_rs%*,*ThestarsnearMarsarefarfromours*)

LEN()函數(shù)

LEN()函數(shù)用于返回一個(gè)代表字符串長度的整型值.這是一個(gè)簡單、有用的函數(shù),經(jīng)常與其他函數(shù)一起使用，

來應(yīng)用業(yè)務(wù)規(guī)則.以下例子將月份和日期轉(zhuǎn)換為字符類型,然后測(cè)試它們的長度.

SELECTLEN('Washington')

LEFT。和RIGHT。函數(shù)

LEFT。與RIGHT。函數(shù)是相似的,它們都返網(wǎng)一定長度的子字符串.這兩個(gè)函數(shù)的區(qū)別是,它們返回的分別

是孑符串的不同部分.LEFT。函數(shù)返回字符串最左邊的字符,順序從左數(shù)到右.RIGHT。函數(shù)正好相反,它從最右

邊的字符開始，以從右到左的順序返回特定數(shù)量的字符.

SELECTLEFT(NAEE,5)

SELECTRIGHT(NAEE,3)

SUBSTRING。函數(shù)

SUBSTRING。函數(shù)能夠從字符串的一個(gè)位置開始,往右數(shù)若干字符,返回一個(gè)特定長度的子字符串.和

LEFT。函數(shù)不同之處是，該函數(shù)可以指定從哪個(gè)位置開始計(jì)數(shù)，這樣就可以在字符串的任何位置摘取子字符串了.

這人函數(shù)需要三個(gè)參數(shù):要解析的字符串、起始位置索引、要返回的子字符串長度.如果要返回到所諭入字符串尾

部的所有字符，可以使用比所需長度更大的長度值.SUBSTRING。函數(shù)將返回最大可能長度的字符數(shù),而不會(huì)將

多巴的長度以空格填充.

SELECTSUBSTRING(FullName,4,6)

LOWER。和UPPER()函數(shù)

它們用于將字符串中所有字符分別都轉(zhuǎn)換為小寫和大寫，這在比較用戶輸入或者存儲(chǔ)用于比較的字符串時(shí)是

非常有用的.字符串比較通常是區(qū)分大小寫的，這取決于SQLServer安裝時(shí)的設(shè)置.如果和其他的字符串操縱函

數(shù)一起使用，就可以將字符串轉(zhuǎn)換為合適的大小寫，以便存儲(chǔ)或顯示.

SELECTLOWER(GROUPSX_XH)

SELECTUPPER(GROUPSX_XH)

LTRIM()和RTRIM()函數(shù)

這兩個(gè)函數(shù)分別返回將字符串的左邊和右邊的空白修剪掉之后的字符串：

SELECTLTRIM(Value)

SELECTRTRIM(Value)

REPLACE。函數(shù)

REPLACE。函數(shù)可以把字符串中的某個(gè)字符或某個(gè)子字符串替換為另一個(gè)字符或者子字符串，該函數(shù)可以

用二全局查找和替換工具中.