數(shù)據(jù)庫應(yīng)用與開發(fā)課件第5章

第5章使用SQL語言管理和設(shè)計(jì)數(shù)據(jù)庫

主要學(xué)習(xí)內(nèi)容SQLServer數(shù)據(jù)管理基本數(shù)據(jù)查詢復(fù)雜數(shù)據(jù)查詢使用SQL語句設(shè)計(jì)和管理數(shù)據(jù)庫

學(xué)習(xí)目標(biāo)了解SQLServer的數(shù)據(jù)管理功能掌握基本數(shù)據(jù)查詢方法熟練掌握使用SQL語句設(shè)計(jì)和管理數(shù)據(jù)庫

導(dǎo)入案例使用軟件產(chǎn)品提供的菜單服務(wù)仿佛是到餐廳用餐，即便你第一次光臨，也能在餐桌上擺放的菜單幫助下，找到你想要的一切。而軟件語言如同你光顧久了的餐廳的外賣服務(wù)，只需撥動一個電話號碼，你也能吃到你熟悉的餐點(diǎn)。如果與餐廳上下相處的十分融洽，或許你還可以在特殊時候訂制菜譜，然后由外賣朋友送上門。當(dāng)然不同的餐館提供的服務(wù)不同，人性化服務(wù)的程度也各異。幸運(yùn)的是，SQLServer不僅提供了全面的菜單命令，幫助我們建設(shè)數(shù)據(jù)庫、保護(hù)數(shù)據(jù)庫、編輯數(shù)據(jù)庫，還提供了更加靈活全面的Transact-SQL(T-SQL)語言，幫助用戶實(shí)現(xiàn)數(shù)據(jù)庫數(shù)據(jù)的編輯和各種效果的查詢統(tǒng)計(jì)，以及數(shù)據(jù)庫的建設(shè)和安全保衛(wèi)。本章主要介紹符合SQL標(biāo)準(zhǔn)部分的T-SQL語言。

5.1SQLServer數(shù)據(jù)管理T-SQL與SQL插入數(shù)據(jù)更新數(shù)據(jù)行刪除數(shù)據(jù)行Merge語法

5.1.1T-SQL與SQLSQL(StructuredQueryLanguage)，即結(jié)構(gòu)化查詢語言。它包括數(shù)據(jù)定義語言DDL、數(shù)據(jù)管理語言DML和數(shù)據(jù)控制語言DCL。DDL用來建立數(shù)據(jù)庫、數(shù)據(jù)庫對象和定義其列，語句有CREATETABLE、DROPTABLE等。DML語言用來查詢、插入、刪除和修改數(shù)據(jù)庫中的數(shù)據(jù)；語句有SELECT、INSERT、UPDATE、DELETE等；DCL語言用來控制存取許可、存取權(quán)限等，語句有：GRANT、REVOKE等。

為什么使用SQL一方面可以幫助關(guān)系數(shù)據(jù)庫自身靈活操作數(shù)據(jù)庫；一方面可以幫助應(yīng)用程序方便管理數(shù)據(jù)。

T-SQLT-SQL（Transact-SQL），是SQL的加強(qiáng)版，包括：1）DML，有SELECT、INSERT、UPDATE、DELETE等。2）DCL，有：GRANT、REVOKE等。3）DDL，有CREATETABLE、DROPTABLE等。4）變量說明、流程控制、功能函數(shù)：定義變量、判斷、分支、循環(huán)結(jié)構(gòu)等；函數(shù)包括日期函數(shù)、數(shù)學(xué)函數(shù)、字符函數(shù)、系統(tǒng)函數(shù)等。

運(yùn)算符運(yùn)算符含義運(yùn)算符含義運(yùn)算符含義=等于>=大于或等于!非>大于<=小于或等于<小于<>不等于

通配符

通配符解釋示例‘_’一個字符ALike'C_'%任意長度的字符串BLike'CO_%'[]括號中所指定范圍內(nèi)的一個字符CLike'9W0[1-2]'[^]不在括號中所指定范圍內(nèi)的一個字符DLike‘%[A-D][^1-2]'

邏輯表達(dá)式邏輯運(yùn)算符說明示例AND邏輯與1AND1=1;1AND0=0;0AND0=0;OR邏輯或1OR1=1;1OR0=1;0OR0=0;NOT邏輯非NOT1=0;NOT0=1;

5.1.2插入數(shù)據(jù)1.插入一條數(shù)據(jù)行

INSERT[INTO]<表名>[列名]VALUES<值列表>例：有表Student(sNo,sName,sAddress,sGrade,sEmail,sSex)insertintoStudent(sNo,sName,sAddress,sGrade,sEmail,sSex)

values('020110001','張黎','上海','2011','ZQC@S',0)

插入語句的注意事項(xiàng)

1）每次插入一行數(shù)據(jù)，不可能只插入半行或者幾列數(shù)據(jù)，因此，插入的數(shù)據(jù)是否有效將按照整行的完整性的要求來檢驗(yàn)；2）每個數(shù)據(jù)值的數(shù)據(jù)類型、精度和小數(shù)位數(shù)必須與相應(yīng)的列匹配；3）不能為標(biāo)識列指定值，因?yàn)樗臄?shù)字是自動增長的；4）如果在設(shè)計(jì)表的時候就指定了某列不允許為空，則必須插入數(shù)據(jù)；5）插入的數(shù)據(jù)項(xiàng)，要求符合檢查約束的要求。6）具有缺省值的列，可以使用DEFAULT（缺?。╆P(guān)鍵字來代替插入的數(shù)值插入一條示例

插入多行數(shù)據(jù)

1）從已知表向已知表插入若干條滿足條件記錄INSERTINTO<表名>(列名)SELECT<列名>FROM<源表名>例：INSERTINTOTongXunLu(姓名,地址,電子郵件)SELECT SName,SAddress,SEmailFROM Student

插入多行數(shù)據(jù)2）從已知表向未知表插入多行數(shù)據(jù)SELECT(列名)INTO<表名>FROM<源表名>注：該語句只能執(zhí)行一次。例：SELECTStudent.SName,Student.SAddress,Student.SEmailINTOTongXunLu1FROMStudent

插入多行數(shù)據(jù)3）SELECTIDENTITY(數(shù)據(jù)類型,標(biāo)識種子,標(biāo)識增長量)AS列名INTO新表FROM原始表例：SELECTStudent.SName,Student.SAddress,Student.SEmail,IDENTITY(int,1,1)AsStudentIDINTOTongXunLu2FROMStudent從已知表向其他表一次插入多條記錄

插入多行數(shù)據(jù)4）插入多行常值記錄，語法如下：INSERTINTO<表名>(列名)SELECT<列名>UNIONSELECT<列名>UNION……SELECT<列名>

插入多行數(shù)據(jù)例：INSERTSTUDENTS(SName,SGrade,SSex)SELECT'測試女生1',7,0UNIONSELECT'測試女生2',7,0UNIONSELECT'測試女生3',7,0UNIONSELECT'測試女生4',7,0UNIONSELECT'測試女生1',7,0UNIONSELECT'測試男生2',7,1UNIONSELECT'測試男生3',7,1UNIONSELECT'測試男生4',7,1UNIONSELECT'測試男生5',7,1

插入多行數(shù)據(jù)5）簡化的插入多條記錄的sql語句向已知表中插入多條常值

5.1.3更新數(shù)據(jù)行1.更新表數(shù)據(jù)語法

UPDATE<表名>SET<列名1=更新值1>,……SET<列名n=更新值n>[WHERE<更新條件>]例：用常量作更新值。UPDATEStudentSETSSEX=0例：有條件更新UPDATEStudentSETSAddress='北京'WHERESAddress='上海'例：用表達(dá)式更新UPDATEstudentSETsSex=sSex+1WHEREsSex=1無條件和有條件更新數(shù)據(jù)

更新數(shù)據(jù)行2.用其他表中數(shù)據(jù)更新表數(shù)據(jù)語法

UPDATE<表名1>SET<表名1．列名=表名2．列名>from<表名1>,<表名2>[WHERE<更新條件>]

更新數(shù)據(jù)行3.允許使用復(fù)合賦值操作符

更新前

更新后

5.1.4刪除數(shù)據(jù)行1.刪除數(shù)據(jù)行語法

DELETEFROM<表名>[WHERE<刪除條件>]例：DELETEFROMStudentWHERESName='張青'

刪除數(shù)據(jù)行2.清空表中數(shù)據(jù)中數(shù)據(jù)TRUNCATETABLE<表名>

5.1.5Merge語法Merge語法是在一條語句中同時執(zhí)行插入，更新，刪除這三個操作。操作原理是根據(jù)與源表聯(lián)接的結(jié)果，對目標(biāo)表執(zhí)行插入、更新或刪除操作。例如，根據(jù)在另一個表中找到的差異在一個表中插入、更新或刪除行，可以將兩個表進(jìn)行同步。

Merge語法Merge語法包含4個主要的子句：Merge子句用于指定進(jìn)行insert、delete和update操作的目標(biāo)表或視圖，Using子句用于指定要與目標(biāo)數(shù)據(jù)連接的數(shù)據(jù)源，ON子句用于指定目標(biāo)數(shù)據(jù)與數(shù)據(jù)源聯(lián)接位置的匹配條件，When子句用于根據(jù)ON子句的結(jié)果指定的操作。

Merge語法例：usetestDB--創(chuàng)建一個訂單表CREATETABLEOrders(orderIDint,customerIDchar(10))GO

Merge語法--往訂單表中添加兩行記錄INSERTINTOOrdersVALUES(1,'2012010101'),(2,'2012010102')--復(fù)制訂單表中第一條數(shù)據(jù)到新建表Order2SELECT*INTOOrders2FROMOrdersWHEREOrderID=1--顯示兩個表初值select*fromordersselect*fromorders2--將Order2表的數(shù)據(jù)進(jìn)行更新UPDATEorders2SETCustomerID='2012010103'

Merge語法--合并兩個表MERGEOrdersASo1USINGOrders2ASo2ONo2.OrderID=o1.OrderIDWHENMATCHEDTHENUPDATESETo1.CustomerID=o2.CustomerID--如--果匹配到了，就更新掉目標(biāo)表WHENNOTMATCHEDTHENINSERTVALUES(o2.OrderID,o2.CustomerID)--如--果匹配不到，就插入WHENNOTMATCHEDBYSOURCETHENDELETE;-----如果來源表無法匹配到，就刪除--顯示修改后表select*fromordersselect*fromorders2go

Merge例題

小結(jié)SQL(結(jié)構(gòu)化查詢語言)是數(shù)據(jù)庫能夠識別的通用指令集。SQLServer中的通配符經(jīng)常和LIKE結(jié)合使用來進(jìn)行不精確的限制。WHERE用來限制條件，其后緊跟條件表達(dá)式。一次插入多行數(shù)據(jù)，可以使用INSERT…SELECT…、SELECT…INTO…或者UNION關(guān)鍵字來實(shí)現(xiàn)。使用UPDATE更新數(shù)據(jù)，一般都有限制條件。使用DELETE刪除數(shù)據(jù)時，不能刪除被外鍵值所引用的數(shù)據(jù)行。

5.2基本數(shù)據(jù)查詢基本查詢基本函數(shù)綜合應(yīng)用

5.2.1基本查詢1.查詢基本語法：SELECT<列名>FROM<表名>[WHERE<查詢條件表達(dá)式>][ORDERBY<排序的列名>[ASC或DESC]]use數(shù)據(jù)庫名--打開數(shù)據(jù)庫

SELECTsNo,SName,SAddressFROMStudentWHERESSEX=1ORDERBYsNo

基本查詢2.查詢?nèi)康男泻土欣篠ELECT*FROMStudent

基本查詢3.查詢部分行例：SELECTsNo,SName,SAddressFROMStudentWHERESAddress='北京‘例：SELECTsNo,SName,SAddressFROMStudentWHERESAddress<>'上海'基本查詢示例

基本查詢4.數(shù)據(jù)查詢-列名1)使用AS來命名列例：SELECTsNoAS學(xué)生編號,SNameAS學(xué)生姓名,SAddressAS學(xué)生地址FROMStudentWHERESAddress<>'北京'例：SELECTsAddress+'and'+sEmailAS'地址及郵箱'FROMstudent

基本查詢4.數(shù)據(jù)查詢-列名

2） 使用=來命名列例：SELECT'地址及郵箱'=sAddress+'and'+sEmailFROMstudent3）使用常量列例：SELECT姓名=SName,地址=SAddress,‘河北新龍’AS學(xué)校名稱

FROMStudents命名列例題

基本查詢4.數(shù)據(jù)查詢-列名4）判斷一行中的數(shù)據(jù)項(xiàng)是否為空例：SELECTSNameFromStudentwheresEmailISNULL

基本查詢5.數(shù)據(jù)查詢-限制行數(shù)1）限制固定行數(shù)

例：SELECTTOP5SName,SAddressFROMStudentWHERESSex=1

2）返回百分之多少行

例：SELECTTOP20PERCENTSName,SAddressFROMStudentWHERESSex=1限制行數(shù)查詢

基本查詢6.數(shù)據(jù)查詢-排序1） 升序排列ASC是默認(rèn)升序排序命令，可以缺省。例：select*fromstuInfoorderbystuNoASC

基本查詢6.數(shù)據(jù)查詢-排序2）降序排列DESC是降序排序命令。例：select*fromstuInfoorderbystuNoDESC

基本查詢6.數(shù)據(jù)查詢-排序3）按照表達(dá)式排序例：SELECTstuNoAs學(xué)員編號,(Score*0.9+5)As綜合成績FROMScoresWHERE(Score*0.9+5)>60ORDERBYScore基本查詢6.數(shù)據(jù)查詢-排序4）按照新命名列排序例：SELECTstuNo+'.'+cNoAS[學(xué)號.科目號]FromScoresUnionSELECTstuNo+'.'+cNoAS[學(xué)號.科目號]FromScores2ORDERBY[學(xué)號.科目號]DESC

基本查詢6.數(shù)據(jù)查詢-排序5）按多列排序例：SELECTstuNoAs學(xué)員編號,ScoreAs成績FROMScoresWHEREScore>60ORDERBYScore,cNo

排序查詢示例

基本查詢7.distinct-去掉重復(fù)記錄行例：在成績表scores(stuNo,cNo,score)中查詢參加考試的學(xué)生考號SELECTdistinctstuNofromscoresdistinct查詢結(jié)果

5.2.2基本函數(shù)函數(shù)名描述舉例CHARINDEX用來尋找一個指定的字符串在另一個字符串中的起始位置SELECTCHARINDEX('ACCP','MyAccpCourse',1)返回：4LEN返回傳遞給它的字符串長度SELECTLEN('SQLServer課程')返回：12LOWER把傳遞給它的字符串轉(zhuǎn)換為小寫SELECTLOWER('SQLServer課程')返回：sqlserver課程UPPER把傳遞給它的字符串轉(zhuǎn)換為大寫SELECTUPPER('sqlserver課程')返回：SQLSERVER課程LTRIM清除字符左邊的空格SELECTLTRIM('周智宇

')返回：周智宇

（后面的空格保留）RTRIM清除字符右邊的空格SELECTRTRIM('周智宇

')返回：

周智宇（前面的空格保留）RIGHT從字符串右邊返回指定數(shù)目的字符SELECTRIGHT('買賣提.吐爾松',3)返回：吐爾松REPLACE替換一個字符串中的字符SELECTREPLACE('莫樂可切.楊可','可','蘭')返回：莫樂蘭切.楊蘭STUFF在一個字符串中，刪除指定長度的字符，并在該位置插入一個新的字符串SELECTSTUFF('ABCDEFG',2,3,'我的音樂我的世界')返回：A我的音樂我的世界EFG

字符串函數(shù)表

日期函數(shù)函數(shù)名描述舉例GETDATE取得當(dāng)前的系統(tǒng)日期SELECTGETDATE()返回：今天的日期DATEADD將指定的數(shù)值添加到指定的日期部分后的日期SELECTDATEADD(mm,4,’01/01/99’)返回：以當(dāng)前的日期格式返回05/01/99DATEDIFF兩個日期之間的指定日期部分的區(qū)別SELECTDATEDIFF(mm,’01/01/99’,’05/01/99’)返回：4DATENAME日期中指定日期部分的字符串形式SELECTDATENAME(dw,’01/01/2000’)返回：SaturdayDATEPART日期中指定日期部分的整數(shù)形式SELECTDATEPART(day,’01/15/2000’)返回：15

數(shù)學(xué)函數(shù)表函數(shù)名描述舉例ABS取數(shù)值表達(dá)式的絕對值SELECTABS(-43)返回：43CEILING返回大于或等于所給數(shù)字表達(dá)式的最小整數(shù)SELECTCEILING(43.5)返回：44FLOOR取小于或等于指定表達(dá)式的最大整數(shù)SELECTFLOOR(43.5)返回：43POWER取數(shù)值表達(dá)式的冪值SELECTPOWER(5,2)返回：25ROUND將數(shù)值表達(dá)式四舍五入為指定精度SELECTROUND(43.543,1)返回：43.5Sign對于正數(shù)返回+1，對于負(fù)數(shù)返回-1，對于0則返回0SELECTSIGN(-43)返回：-1Sqrt取浮點(diǎn)表達(dá)式的平方根SELECTSQRT(9)返回：3

系統(tǒng)函數(shù)表函數(shù)名描述舉例CONVERT用來轉(zhuǎn)變數(shù)據(jù)類型SELECTCONVERT(VARCHAR(5)，12345)返回：字符串12345CURRENT_USER返回當(dāng)前用戶的名字SELECTCURRENT_USER返回：你登錄的用戶名DATALENGTH返回用于指定表達(dá)式的字節(jié)數(shù)SELECTDATALENGTH('中國A盟')返回：7HOST_NAME返回當(dāng)前用戶所登錄的計(jì)算機(jī)名字SELECTHOST_NAME()返回：你所登錄的計(jì)算機(jī)的名字SYSTEM_USER返回當(dāng)前所登錄的用戶名稱SELECTSYSTEM_USER返回：你當(dāng)前所登錄的用戶名USER_NAME從給定的用戶ID返回用戶名SELECTUSER_NAME(1)返回：從任意數(shù)據(jù)庫中返回“dbo”

5.2.3綜合應(yīng)用【例5.1】：某公司做了一批手機(jī)充值卡，充值密碼是隨機(jī)生成的，現(xiàn)在出現(xiàn)這個問題：充值密碼里面的“o和0”（哦和零）“l(fā)和1”（哎和一），用戶反映說看不清楚，公司決定，把存儲在數(shù)據(jù)庫中的密碼中所有的“哦”都改成“零”，把所有的“l(fā)”都改成“1”；請編寫SQL語句實(shí)現(xiàn)以上要求；數(shù)據(jù)庫表名：Card；密碼字段名：PassWord；

綜合應(yīng)用-I分析：這是更新語句，需要使用UPDATE語句；因?yàn)闋可娴阶址奶鎿Q，需要使用到SQLServer中的函數(shù)Replace；解決：UpdateCardSetPassWord=Replace(密碼,'o','0')UpdateCardSetPassWord=Replace(密碼,'l','1')或者寫成一條語句Update CardSet PassWord=Replace(Replace(密碼,'o','0'),'l','1')

綜合應(yīng)用-II【例5.2】：在數(shù)據(jù)庫表中有以下字符數(shù)據(jù)，如：

1-1、1-2、1-3、1-10、1-11、1-108、1-18、1-31、1-15、2-1、2-2

現(xiàn)在希望通過SQL語句進(jìn)行排序，并且首先要按照前半部分的數(shù)字進(jìn)行排序，然后再按照后半部分的數(shù)字進(jìn)行排序，輸出要排成這樣：

1-1、1-2、1-3、1-10、1-11、1-15、1-18、1-31、1-108、2-1、2-2數(shù)據(jù)庫表名：ArticleNo；字段名：ListNumber；

綜合應(yīng)用-II分析：這是查詢語句，需要使用SELECT語句需要使用到ORDERBY進(jìn)行排序，并且在ORDERBY的排序列中，也需要重新計(jì)算出排序的數(shù)字前半部分的數(shù)字，可以從先找到“-”符號的位置，然后，取其左半部分，最后再使用Convert函數(shù)將其轉(zhuǎn)換為數(shù)字：Convert(int,Left(ListNumber,CharIndex('-',ListNumber)-1))后半部分的數(shù)字，可以先找到“-”符號的位置，然后把從第一個位置到該位置的全部字符替換為空格，最后再使用Convert函數(shù)將其轉(zhuǎn)換為數(shù)字：Convert(int,Stuff(ListNumber,1,Charindex('-',ListNumber),''))

綜合應(yīng)用-II解決：SELECTListNumberFROMArticleNoORDERBYConvert(int,Left(ListNumber,CharIndex('-',ListNumber)-1)),Convert(int,Stuff(ListNumber,1,Charindex('-',ListNumber),''))思考：還有其他的辦法嗎？

小結(jié)查詢將逐行篩選表中的數(shù)據(jù)，最后符合要求的記錄重新組合成“記錄集”，記錄集的結(jié)構(gòu)類似于表結(jié)構(gòu)；判斷一行中的數(shù)據(jù)項(xiàng)是否為空，使用ISNULL；使用ORDERBY進(jìn)行查詢記錄集的排序，并且可以按照多個列進(jìn)行排序；在查詢中，可以使用常量、表達(dá)式、運(yùn)算符；在查詢中使用函數(shù)，能夠像在程序中那樣處理查詢得到的數(shù)據(jù)項(xiàng)。

5.3復(fù)雜數(shù)據(jù)查詢模糊查詢聚合函數(shù)分組匯總GroupingSets多表聯(lián)結(jié)查詢綜合應(yīng)用

5.3.1模糊查詢1.LIKE：查詢時，字段中的內(nèi)容并不一定與查詢內(nèi)容完全匹配，使用LIKE和通配符。例：SELECTstuNameAS姓名FROMstuInfoWHEREstuNameLIKE'x%'

模糊查詢2.ISNULL：把某一字段中內(nèi)容為空的記錄查詢出來。例：SELECTstuNameAs姓名,stuAddressAS地址FROMstuInfoWHEREstuAddressISNULL

模糊查詢3.BETWEENAND：把某一字段中內(nèi)容在特定范圍內(nèi)的記錄查詢出來。例：SELECTstuNo,ScoreFROMSCORESWHEREScoreBETWEEN60AND80

模糊查詢4.IN：把某一字段中內(nèi)容與所列出的查詢內(nèi)容列表匹配的記錄查詢出來例：SELECTstuNameAS學(xué)員姓名,stuAddressAs地址FROMstuInfoWHEREstuAddressIN('北京','廣州','上海')模糊查詢

5.3.2聚合函數(shù)1.SUM：例：SELECTSUM(score)FROMscoresWHEREcNo='0001'例：錯誤寫法！SELECTSUM(score),stuNoFROMscoresWHEREcNo='0001'

聚合函數(shù)2.AVG：例：SELECTAVG(score)AS平均成績FromScoresWHEREScore>=60

聚合函數(shù)3.MAX、MIN：例：SELECTAVG(score)AS平均成績,MAX(Score)AS最高分,MIN(Score)AS最低分FromScoresWHEREScore>=60

聚合函數(shù)4.COUNT：例：SELECTCOUNT(*)AS及格人數(shù)FromScoresWHEREScore>=60

聚合函數(shù)5.注意事項(xiàng)：聚合函數(shù)不單獨(dú)出現(xiàn)在條件語句中。只與返回結(jié)果值數(shù)目一致的列一起查詢。聚合函數(shù)

5.3.3分組匯總基本語法：SELECT[<列名x>]，[聚合函數(shù)]FROM<表名>WHERE條件GROUPBY<列名x>HAVING條件

分組匯總注：分組查詢—GROUPBYWHERE子句從數(shù)據(jù)源中去掉不符合其搜索條件的數(shù)據(jù)GROUPBY子句搜集數(shù)據(jù)行到各個組中，統(tǒng)計(jì)函數(shù)為各個組計(jì)算統(tǒng)計(jì)值HAVING子句去掉不符合其組搜索條件的各組數(shù)據(jù)行

分組匯總例：SELECT 部門編號,COUNT(*)FROM 員工信息表WHERE 工資>=2000GROUPBY 部門編號HAVING COUNT(*)>1

5.3.4GroupingSets例：有數(shù)據(jù)庫Sale，表sales(productID,productName,saleAmout,saleMonth)。按照銷售量升序顯示表sales中數(shù)據(jù)

GroupingSets使用SQL語句分組顯示各產(chǎn)品的銷售總量。SELECTproductNameas產(chǎn)品名,sum(saleAmout)as銷售總量fromsalesGroupbyGROUPINGSETS((productName));

GroupingSets效果等同于selectproductNameas產(chǎn)品名,sum(saleAmout)as銷售總量fromsalesGroupbyproductName顯示每個產(chǎn)品的銷售總量

GroupingSets但是如果使用兩個GROUPINGSETS分組就可以實(shí)現(xiàn)分別分組匯總了：SELECTproductNameas產(chǎn)品名,sum(saleAmout)as銷售總量fromsalesGroupbyGROUPINGSETS((productName),());顯示每個產(chǎn)品的銷售總量和所有產(chǎn)品的銷售總和

GroupingSets當(dāng)然，可以給分組匯總結(jié)果起個名字“匯總”，而不必用NULL顯示：

select[sales.Rep]=casewhenproductNameisnullThen'匯總'elseproductNameend,sum(saleAmout)as銷售總量fromsalesGroupbyGROUPINGSETS((productName),());顯示每個產(chǎn)品的銷售總量和帶名字的所有產(chǎn)品的銷售總和

GroupingSets我們還可以進(jìn)行三級甚至四級分類匯總，例如將產(chǎn)品按月份分類匯總顯示：

SELECTproductNameas產(chǎn)品名,sum(saleAmout)as銷售總量,saleMonthas銷售月份FromsalesGroupbyGROUPINGSETS((saleMonth,productName),(saleMonth),());三級甚至四級分類匯總

GroupingSets值得一提的是，CUBE和ROLLUP是在SQLServer2005中新增的groupby擴(kuò)展，用來創(chuàng)建分組匯總。例如上面的實(shí)現(xiàn)效果也可以由以下SQL語句實(shí)現(xiàn)：

selectproductNameas產(chǎn)品名,sum(saleAmout)as銷售總量,saleMonthas銷售月份fromsalesGroupbysaleMonth,productNamewithROLLUP使用ROLLUP分組匯總

5.3.5多表聯(lián)結(jié)查詢1.內(nèi)聯(lián)結(jié)(INNERJOIN)語法：1）兩表連接：SELECT <表名。列名>From左表[INNER]JOIN 右表ON 左表。列=右表。列例：查詢參加考試的同學(xué)的姓名、考試科目號碼、考試成績：SELECT S.stuName,C.cNo,C.scoreFrom ScoresASCINNERJOIN stuInfoASSON C.stuNo=S.stuNo

多表聯(lián)結(jié)查詢1.內(nèi)聯(lián)結(jié)(INNERJOIN)語法：2）三表內(nèi)連接：例：查參加考試的學(xué)生姓名、考試科目名稱、考試成績：SELECTS.stuNameAS姓名,CS.cNameAS課程,C.ScoreAS成績FROMstuInfoASSINNERJOINScoresASCON(S.stuNo=C.stuNo)INNERJOINcourseInfoASCSON(CS.cNo=C.cNo)

多表聯(lián)結(jié)查詢2.普通多表查詢：SELECT <表名.列名>From表1，表2WHERE左表.列=右表.列

例：SELECT stuInfo.stuName,scores.cNo,scores.scoreFromstuInfo,scoresWHEREstuInfo.stuNo=scores.stuNo多表連接查詢示例

多表聯(lián)結(jié)查詢3.外聯(lián)結(jié)：1)左外聯(lián)結(jié)(LEFTJOIN)：例：查詢所有學(xué)生考試情況SELECTS.stuName，C.cNo，C.ScoreFromstuInfoASSLEFTJOIN ScoresASCON C.stuNo=S.stuNo

多表聯(lián)結(jié)查詢3.外聯(lián)結(jié)：2)右外聯(lián)結(jié)(RIGHTJOIN)：例：SELECTS.stuName，C.cNo，C.ScoreFromstuInfoASSRightJOIN ScoresASCON C.stuNo=S.stuNo

多表聯(lián)結(jié)查詢4.UNION

兩個結(jié)構(gòu)相同的表的連接查詢，相同列合并、記錄行做或運(yùn)算。語法：

Select<列1>,<…>,<列n>from表1UNIONSelect<列1>,<…>,<列n>from表2例：在數(shù)據(jù)庫jsj2019中有結(jié)構(gòu)相同的兩張表：scores(stuNo,cNo,score)和表scores2(stuNo,cNo,score)，求selectstuNo,cNofromsocresUNIONselectstuNo,cNofromscores2？

5.3.6綜合應(yīng)用【例5.3】：在給發(fā)掘出來的遠(yuǎn)古時代的谷物種子做育種實(shí)驗(yàn)的過程中，種子每3粒一組，組內(nèi)每顆種子的培育方法不同?，F(xiàn)要求查看所有組內(nèi)培育方法相同的種子的發(fā)育值的平均值。數(shù)據(jù)庫表名：TABLE1

字段名：A

主鍵字段：IDKEY（標(biāo)識列，種子：1；增長量：1）

綜合應(yīng)用-I分析：可以依靠標(biāo)識列的值來進(jìn)行判斷和選取，但是因?yàn)椴僮鬟^程中，數(shù)據(jù)行可能存在增加、修改和刪除，因此標(biāo)識列的數(shù)據(jù)值未必完全有序，也就不“完全可靠”。例如標(biāo)識列值為3，但并不一定是第三行，因?yàn)槿绻诙斜粍h除了，它就是第二行。根據(jù)我們前面使用過的SELECT…INTO，可以創(chuàng)建一張新表，順便創(chuàng)建新的標(biāo)識列，再在新的標(biāo)識列上執(zhí)行除3取余判斷。判斷依據(jù)：標(biāo)識列值%3等于0、標(biāo)識列值%3等于1和標(biāo)識列值%3等于2。

綜合應(yīng)用-ISELECT A,IDENTITY(int,1,1)ASID

INTO TABLE2FROM TABLE1SELECT AVG(A)AS1號種子發(fā)育平均值FROM TABLE2WHERE ID%3=1SELECT AVG(A)AS2號種子發(fā)育平均值FROM TABLE2WHERE ID%3=2SELECT AVG(A)AS3號種子發(fā)育平均值FROM TABLE2WHERE ID%3=0

綜合應(yīng)用-II【例5.4】：有學(xué)生基本信息表stuInfo（stuNo,stuName）和學(xué)生成績表Scores(stuNo,cNo,score)?，F(xiàn)在要求建立新表stuAllInfo(stuNo,stuName,cNo,score)，存儲所有學(xué)生的考試信息。

綜合應(yīng)用-II分析：這是數(shù)據(jù)插入的操作，因此要使用INSERT語句來進(jìn)行。參加考試的同學(xué)的考試信息在Scores表里，所以可以使用INSERTINTO…SELECT結(jié)構(gòu)。但是還要插入stuName數(shù)據(jù)項(xiàng)。所以要用多表連接查詢。但是還有同學(xué)可能沒有參加任何一科的考試，所以根本不在scores成績表中。

綜合應(yīng)用-II等值的多表連接和不等值的多表連接都不能找到這些同學(xué)。在前面的聯(lián)結(jié)查詢中，使用INNERJOIN…ON可以找出所有參加考試的學(xué)生信息項(xiàng)，編寫以下T-SQL：SELECTstuInfo.stuNo,stuName,cNo,scorefromstuInfoINNERJOINscoresONstuInfo.stuNo=scores.stuNo但是如何把未參加任何一科考試的同學(xué)也顯示其中？如下可以嗎？SELECTstuInfo.stuNo,stuName,cNo,scorefromstuInfoINNERJOINscoresONstuInfo.stuNo<>scores.stuNo以上把“=”簡單地改為“<>”，不僅不能找出未參加考試的同學(xué)，而且所找到的項(xiàng)很多，也沒有意義，因此，這種方法不可行。這也說明一點(diǎn)：內(nèi)聯(lián)結(jié)查詢的基礎(chǔ)是on后面的等值比較，非等值比較就不是內(nèi)聯(lián)查詢了。

綜合應(yīng)用-II考慮我們在學(xué)習(xí)過的左外連接查詢，能夠查詢出左表中存在而相關(guān)表不存在的數(shù)據(jù)項(xiàng)。所以可以使用如下語句查詢出所有考生。SELECTstuInfo.stuNo,stuName,cNo,scorefromstuInfoLeftOUTJOINscoresONstuInfo.stuNo=scores.stuNo最后，使用插入同時建立新表語句子查詢插入新建表stuAllInfo

綜合應(yīng)用-II解決：SELECTstuInfo.stuNo,stuName,cNo,scoreINTOstuAllInfofromstuInfoLeftJOINscoresONstuInfo.stuNo=scores.stuNoSELECT*FROMstuAllInfo在查詢分析器中選擇工具->選項(xiàng)->結(jié)果->默認(rèn)結(jié)果目標(biāo)修改為“顯示為文本”。

小結(jié)使用LIKE、BETWEEN、IN關(guān)鍵字，能夠進(jìn)行模糊查詢——條件不明確的查詢。聚合函數(shù)能夠?qū)α猩梢粋€單一的值，對于分析和統(tǒng)計(jì)通常非常有用。分組查詢是針對表中不同的組，分類統(tǒng)計(jì)和輸出，GROUPBY子句通常會結(jié)合聚合函數(shù)一起來使用。HAVING子句能夠在分組的基礎(chǔ)上，再次進(jìn)行篩選。多個表之間通常使用聯(lián)結(jié)查詢。最常見的聯(lián)結(jié)查詢是內(nèi)聯(lián)結(jié)（INNERJOIN），通常會在相關(guān)表之間提取引用列的數(shù)據(jù)項(xiàng)。

5.4使用SQL語句設(shè)計(jì)和管理數(shù)據(jù)庫創(chuàng)建數(shù)據(jù)庫刪除數(shù)據(jù)庫建表刪除表為表加約束刪除約束安全管理

5.4.1創(chuàng)建數(shù)據(jù)庫語法：CREATEDATABASE數(shù)據(jù)庫名

ON[PRIMARY](<數(shù)據(jù)文件參數(shù)>[，…n][<文件組參數(shù)>])[LOGON](<日志文件參數(shù)>[，…n])注：PRIMARY：主文件組，可選參數(shù)，默認(rèn)；[]表示可選參數(shù)。

【例5.5】創(chuàng)建數(shù)據(jù)庫studentDB，保存在D：\project目錄，數(shù)據(jù)文件增長率：15％，初始大小5MB，日志文件初始大小2MB，文件增長率按1MB自動增長。

CREATEDATABASEstudentDBONPRIMARY--默認(rèn)就屬于PRIMARY主文件組，可省略

(NAME='studentDB',--主數(shù)據(jù)文件的邏輯名

FILENAME='D:\project\studentDB.mdf',--主數(shù)據(jù)文件的物理名

SIZE=5mb,--主數(shù)據(jù)文件初始大小

MAXSIZE=100mb,--主數(shù)據(jù)文件增長的最大值

FILEGROWTH=15%--主數(shù)據(jù)文件的增長率)LOGON(NAME='studentDB_log',FILENAME='D:\project\studentDB_log.ldf',SIZE=2mb,FILEGROWTH=1MB)GO

【例5.6】在D:\下創(chuàng)建數(shù)據(jù)庫DB，包含一個主數(shù)據(jù)文件和一個從數(shù)據(jù)文件，數(shù)據(jù)文件增長率都按10%自動增長，初始大小為1MB，日志文件增長率都按1MB自動增長，初始大小都為1MB。

createdatabaseDBon(name='db1',filename='d:\db1.mdf',size=1,filegrowth=10%),(name='db2',filename='d:\db2.ndf',size=1,filegrowth=10%)

logon(name='db1_log',filename='d:\db1_log.ldf',size=1,filegrowth=1),(name='db2_log',filename='d:\db2_log.ldf',size=1,filegrowth=1)go

多個數(shù)據(jù)文件的好處：如硬盤滿了，希望買個硬盤再繼續(xù)存放數(shù)據(jù)，這時就可以將一個數(shù)據(jù)文件放在的D盤，另一個可能在另一個硬盤的H盤等。

5.4.2刪除數(shù)據(jù)庫刪除數(shù)據(jù)庫語法：DROPDATABASE數(shù)據(jù)庫名例：是否已經(jīng)存在數(shù)據(jù)庫stuDB，存在刪除重建。提示：新建的數(shù)據(jù)庫信息在數(shù)據(jù)庫的視圖sys.databases中可以找到，所以我們只需要查看master數(shù)據(jù)庫的sys.databases視圖的name列即可。

解決方案：USEmaster--設(shè)置當(dāng)前數(shù)據(jù)庫為master，以便訪問sys.databases系統(tǒng)視圖GOIFEXISTS（SELECT*FROMsys.databasesWHEREname='stuDB'）

DROPDATABASEstuDBCREATEDATABASEstuDBON(…..)LOGON(…)GO注：EXISTS(查詢語句)檢測語句的用法，如果查詢語句返回1條以上的記錄，即表示存在滿足條件的記錄，則返回為true，否則為false

5.4.3建表創(chuàng)建表的語法:CREATETABLE表名

(

字段1數(shù)據(jù)類型列的特征，字段2數(shù)據(jù)類型列的特征，

...)

注意：1．?dāng)?shù)據(jù)類型：數(shù)據(jù)表的字段，一般都要求在數(shù)據(jù)類型后加“()”，并在其中聲明長度。比如，char，varchar等。但int，smallint，float，datetime，image，bit和money類型不需要聲明字段的長度。2．列的特征：包括該列是是否為空（NULL）、是否是標(biāo)識列（自動編號）、是否有默認(rèn)值、是否為主鍵等。

【例5.7】創(chuàng)建學(xué)員信息表stuInfo，具體要求如下表所示。字段類型描述stuNoCHAR(6)非空stuNameVARCHAR(20)非空stuAgeINT非空stuIDNUMERIC(18,0),身份證號stuSeatSMALLINT標(biāo)識列stuAddressTEXT

實(shí)現(xiàn)代碼如下：實(shí)現(xiàn)代碼如下：USEstudentDB--將當(dāng)前數(shù)據(jù)庫設(shè)置為studentDBGOCREATETABLEstuInfo/*-創(chuàng)建學(xué)員信息表-*/(stuNameVARCHAR(20)NOTNULL,--姓名，非空（必填）

stuNoCHAR(6)NOTNULL,--學(xué)號，非空（必填）

stuAgeINTNOTNULL,--年齡，INT類型默認(rèn)為4個字節(jié)

stuIDNUMERIC(18,0),--身份證號

stuSeatSMALLINTIDENTITY(1,1),--座位號，自動編號

stuAddressTEXT--住址，允許為空，即可選輸入)GO

注意：NUMERIC(18,0)代表18位數(shù)字，小數(shù)位數(shù)為0有些類型不必規(guī)定長度，要記住，比如：int、smallint、datetime。

【例5.8】創(chuàng)建學(xué)員程序設(shè)計(jì)成績表stuMarks字段類型描述ExmaNoCHAR(7)考號，非空stuNoCHAR(6)學(xué)號，非空writtenExamINT筆試成績，非空LabExamINT機(jī)試成績，非空

CREATETABLEstuMarks(ExamNoCHAR(7)NOTNULL,--考號

stuNoCHAR(6)NOTNULL,--學(xué)號

writtenExamINTNOTNULL,--筆試成績

LabExamINTNOTNULL--機(jī)試成績)GO

5.4.4刪除表刪除表的語法：DROPTABLE表名【例5.9】如果當(dāng)前數(shù)據(jù)庫中已存在stuInfo表，此次創(chuàng)建時系統(tǒng)將提示出錯。如何解決呢？分析：當(dāng)表中存在stuInfo表時，在studentDB數(shù)據(jù)庫的系統(tǒng)視圖sys.objects中檢查name列即可。

解決方案：USEstudentDB--將當(dāng)前數(shù)據(jù)庫設(shè)置為studentDB，以便在studentDB數(shù)據(jù)庫中建表GOIFEXISTS（SELECT*FROMsys.objectsWHEREname=’stuInfo’）

DROPTABLEstuInfoCREATETABLEstuInfo/*-創(chuàng)建學(xué)員信息表-*/(…..)GO

5.4.5為表加約束SQLServer中常用的約束類型如下所示：主鍵約束（PrimaryKeyConstraint）：要求主鍵列數(shù)據(jù)唯一，并且不允許為空唯一約束（UniqueConstraint）：要求該列唯一，允許為空，但只能出現(xiàn)一個空值。檢查約束（CheckConstraint）：某列取值范圍限制、格式限制等，如有關(guān)年齡的約束默認(rèn)約束（DefaultConstraint）：某列的默認(rèn)值，如我們的男性學(xué)員較多，性別默認(rèn)為“男”外鍵約束（ForeignKeyConstraint）：用于兩表間建立關(guān)系，需要指定引用主表的那列

添加約束的語法：ALTERTABLE表名

ADDCONSTRAINT約束名約束類型具體的約束說明

約束名的取名規(guī)則推薦采用：約束類型_約束字段主鍵（PrimaryKey）約束：如PK_stuNo唯一（UniqueKey）約束：如UQ_stuID默認(rèn)（DefaultKey）約束：如DF_stuAddress檢查（CheckKey）約束：如CK_stuAge外鍵(ForeignKey)約束：如FK_stuNo

【例5.10】要求在stuInfo表（見表5-8）上添加約束：①添加主鍵約束（stuNo作為主鍵），②唯一約束（因?yàn)槊咳说纳矸葑C號全國唯一），③默認(rèn)約束（如果地址不填，默認(rèn)為“地址不詳”），④檢查check約束：要求年齡只能在15－40歲之間，⑤外鍵約束（主表stuInfo和從表stuMarks建立關(guān)系，關(guān)聯(lián)字段為stuNo）。

實(shí)現(xiàn)代碼如下：①加主鍵約束：ALTERTABLEstuInfoADDCONSTRAINTPK_stuNoPRIMARYKEY(stuNo)②加唯一約束ALTERTABLEstuInfoADDCONSTRAINTUQ_stuIDUNIQUE(stuID)③加默認(rèn)約束ALTERTABLEstuInfoADDCONSTRAINTDF_stuAddressDEFAULT('地址不詳')FORstuAddress④加檢查約束ALTERTABLEstuInfoADDCONSTRAINTCK_stuAgeCHECK(stuAgeBETWEEN15AND40)⑤加外鍵約束ALTERTABLEstuMarksADDCONSTRAINTFK_stuNoFOREIGNKEY(stuNo)REFERENCESstuInfo(stuNo)GO

各類表約束除了在數(shù)據(jù)表創(chuàng)建完畢之后添加，也可以在創(chuàng)建表時添加?！纠?.11】使用SQL語句在創(chuàng)建表stuInfo和stuMarks的同時，添加如【例5.10】中的約束。

實(shí)現(xiàn)代碼如下：USEstudentDB--將當(dāng)前數(shù)據(jù)庫設(shè)置為studentDBGOCREATETABLEstuInfo/*-創(chuàng)建學(xué)員信息表-*/(stuNameVARCHAR(20)NOTNULL,--姓名，非空（必填）

stuNoCHAR(6)PrimaryKey,--學(xué)號，非空（必填）

stuAgeINTCHECK(stuAgeBETWEEN15AND40),--年齡stuIDNUMERIC(18,0),--身份證號

stuSeatSMALLINTIDENTITY(1,1),--座位號，自動編號

stuAddressTEXTDEFAULT('地址不詳')--住址，允許為空，即可選輸入

UNIQUE(StuID))GO

CREATETABLEstuMarks/*創(chuàng)建學(xué)員成績表stuMarks*/(ExamNoCHAR(7),--考號

stuNoCHAR(6),--學(xué)號

writtenExamINT,--筆試成績

LabExamINT,--機(jī)試成績

Primarykey(ExamNo),FOREIGNKEY(stuNo)REFERENCESstuInfo(stuNo))GO

5.4.6刪除約束如果錯誤地添加了約束，我們還可以刪除約束，刪除約束的語法：ALTERTABLE表名

DROPCONSTRAINT約束名

例：刪除stuInfo表