《數(shù)據(jù)庫基礎(chǔ)》項(xiàng)目二 數(shù)據(jù)庫的查詢

數(shù)據(jù)庫基礎(chǔ)1任務(wù)七、SQL基本查詢2任務(wù)八、排序orderby與分組groupby項(xiàng)目二3任務(wù)九、聚合數(shù)據(jù)查詢與分組統(tǒng)計(jì)數(shù)據(jù)4任務(wù)十、連接查詢5任務(wù)十一、組合查詢6任務(wù)十二、插入、更新知識(shí)儲(chǔ)備任務(wù)七SQL查詢?cè)赟QLServer2005中，master數(shù)據(jù)庫是默認(rèn)的當(dāng)前數(shù)據(jù)庫，所以在編寫操作SQL語句時(shí)，先使用USE語句打開要使用的表所在的數(shù)據(jù)庫，否則就會(huì)出現(xiàn)“對(duì)象名無效”提示。任務(wù)七、SQL查詢知識(shí)儲(chǔ)備一、單表查詢?nèi)蝿?wù)七SQL查詢1.通配符“*”的使用在SELECT語句中，可以使用通配符“*”來顯示所有的字段。2.用AS重新指定返回字段的名字。3.多個(gè)字段拼接如果需要將多個(gè)字段拼接成一個(gè)字段，可以使用“+”連接，使用函數(shù)rtrim去掉字段右側(cè)的空格。知識(shí)儲(chǔ)備任務(wù)七SQL查詢4.WHERE子句用關(guān)鍵字WHERE子句來說明查詢條件，緊跟在FROM子句的后面。條件表達(dá)式中有如下條件運(yùn)算符，關(guān)系運(yùn)算符：=,<,>,<=,>=,<>,!=；邏輯運(yùn)算符NOT,AND,OR；其他運(yùn)算符：IN,NOTIN,BETWEEN,NOTBETWEEN,LIKE,NOTLIKE,ISNULL,ISNOTNULL,EXISTS,ANY,SOME。5.查詢常用的通配符常用的通配符見表7-1。知識(shí)儲(chǔ)備任務(wù)七SQL查詢知識(shí)儲(chǔ)備任務(wù)七SQL查詢?cè)趕qlsever中nchar，nvarchar使用的是unicode字符集。而char，varchar使用的是ASCII字符集。規(guī)定：當(dāng)unicode數(shù)據(jù)（nchar或nvarchar）與like一起使用時(shí)，尾隨空格有意義。但對(duì)非unicode數(shù)據(jù)，尾隨空格無意義。而ASCII中char字符串后面的空格沒有意義，即char和varchar尾部空格無意義。數(shù)據(jù)庫中“姓名”列的數(shù)據(jù)類型，若數(shù)據(jù)類型為nchar(8)，所以要查詢姓“王”且單名的學(xué)生例如“王石”情況，則查詢語句應(yīng)該為：SELECT*FROM學(xué)生信息表WHERE姓名LIKE王_——王下劃線后面是6個(gè)空格，要與nchar的長度一致。任務(wù)描述任務(wù)二創(chuàng)建學(xué)生班級(jí)管理數(shù)據(jù)表SQL語句基本查詢查詢數(shù)據(jù)庫information10da5da6中的數(shù)據(jù)表“考工成績”的所有內(nèi)容；查詢考工應(yīng)知成績大于90分的學(xué)生名單，并顯示出該名學(xué)生的應(yīng)會(huì)成績；查詢姓王的學(xué)生的考工成績；計(jì)算學(xué)生的總成績＝應(yīng)知*50%＋應(yīng)會(huì)*50%。實(shí)訓(xùn)操作任務(wù)七SQL查詢（1）連接數(shù)據(jù)庫information10da5da6，在工具欄中單擊按鈕，在右邊的查詢窗口中，輸入查詢的語句，如圖7-1所示。（2）在查詢的窗口中輸入“select*from考工成績表”，單擊按鈕，可以看出考工成績表的所有學(xué)生記錄信息，如圖7-2所示。（3）查詢考工應(yīng)知成績大于90分的學(xué)生名單，在新建查詢執(zhí)窗口中輸入“select序號(hào),姓名,應(yīng)知,應(yīng)會(huì)from考工成績表where應(yīng)知>90”，執(zhí)行查詢，結(jié)果如圖7-3所示。（4）查詢姓王的學(xué)生的考工成績。在新建查詢執(zhí)窗口中輸入“select*from考工成績表where姓名like王%”，執(zhí)行查詢，如圖7-4所示。實(shí)訓(xùn)操作任務(wù)七SQL查詢（5）計(jì)算學(xué)生的總成績＝應(yīng)知*50%＋應(yīng)會(huì)*50%。在新建查詢執(zhí)窗口中輸入“select姓名，準(zhǔn)考證號(hào)，應(yīng)知，應(yīng)會(huì)，應(yīng)知*0.5+應(yīng)會(huì)*0.5as總成績from考工成績表”。執(zhí)行查詢，如圖7-5所示。實(shí)訓(xùn)操作任務(wù)七SQL查詢實(shí)訓(xùn)操作任務(wù)七SQL查詢實(shí)訓(xùn)操作任務(wù)七SQL查詢實(shí)訓(xùn)操作任務(wù)七SQL查詢實(shí)訓(xùn)操作任務(wù)七SQL查詢1任務(wù)七、SQL基本查詢2任務(wù)八、排序orderby與分組groupby

項(xiàng)目二3任務(wù)九、聚合數(shù)據(jù)查詢與分組統(tǒng)計(jì)數(shù)據(jù)4任務(wù)十、連接查詢5任務(wù)十一、組合查詢6任務(wù)十二、插入、更新知識(shí)儲(chǔ)備一、排序ORDERBY任務(wù)八排序orderby與分組groupby在SQLServer2005中，為了方便查看查詢結(jié)果，按某種規(guī)律排序，可使用ORDERBY子句來排序數(shù)據(jù)，還可以進(jìn)行多字段排序。任務(wù)八、排序orderby與分組groupby知識(shí)儲(chǔ)備任務(wù)八排序orderby與分組groupby【例1】從學(xué)生表student中按照成績grade升序排序。SELECT*FROMstudentORDERBYgrade也就是按照成績grade升序排序，默認(rèn)排序方式是升序，功能等于在字段名后加上升序關(guān)鍵字ASC，ORDERBY后的字段名可以不出現(xiàn)在SELECT字段列表中，如果要降序排列，在字段名后加上DESC。例如：ORDERBYgradeDESC知識(shí)儲(chǔ)備任務(wù)八排序orderby與分組groupby數(shù)據(jù)分組經(jīng)常運(yùn)用在統(tǒng)計(jì)匯總中，運(yùn)用GROUPBY可以進(jìn)行分組，如果要將滿足條件的分組查詢出來，還需要使用HAVING子句。需要注意的是，使用GROUPBY時(shí)，不要使用SELECT*語句，否則有“列表中的列無效，因?yàn)樵摿袥]有包含在聚合函數(shù)或GROUPBY子句中”錯(cuò)誤提示。如果SELECT子句后是字段名列表，而這些字段名又不在聚合函數(shù)中，則應(yīng)當(dāng)在GROUPBY子句中列出所有這些字段名，這時(shí)分組就是這些字段的組合，而并非單個(gè)字段分組。二、分組GROUPBY知識(shí)儲(chǔ)備任務(wù)八排序orderby與分組groupby【例2】從學(xué)生表student中按照性別sex分組，統(tǒng)計(jì)男女生人數(shù)。SELECTsex,COUNT(*)As人數(shù)FROMstudentGROUPBYsex對(duì)已經(jīng)存在的表，運(yùn)用INTO子句可以創(chuàng)建一張新表，此新表存儲(chǔ)在數(shù)據(jù)庫中，不會(huì)顯示在屏幕上，因此，建立后可以用SELECT顯示全部記錄。若INTO后的表名前有“?！碧?hào)，表示該新表為臨時(shí)表，退出SQLServer后，不保存該新表。三、INTO字句和TOP字句知識(shí)儲(chǔ)備任務(wù)八排序orderby與分組groupby【例3】從學(xué)生表student中按照系別，把信息工程系的學(xué)生記錄單獨(dú)生成一張新表“信息工程系學(xué)生表”。SELECT*INTO信息工程系學(xué)生表FROMstudentWHERE系別=‘信息工程系’SELECT*FROM信息工程系學(xué)生表TOP子句可以限定顯示前n條記錄。知識(shí)儲(chǔ)備任務(wù)八排序orderby與分組groupby【例4】顯示學(xué)生表student中前10條記錄。SELECTTOP3*FROMstudent任務(wù)描述任務(wù)八排序orderby與分組groupby在數(shù)據(jù)庫information09da2中，利用排序ORDERBY和分組GROUPBY進(jìn)行SQL查詢操作。按學(xué)生的姓名拼音排序，顯示學(xué)生的序號(hào)、姓名、性別、戶口所在地。分別統(tǒng)計(jì)男女在校住宿生人數(shù)和住宿生總數(shù)。統(tǒng)計(jì)戶口在同一城市的學(xué)生人數(shù)，并按統(tǒng)計(jì)的人數(shù)從少到多排序。實(shí)訓(xùn)操作任務(wù)八排序orderby與分組groupby（1）排序orderby操作，在數(shù)據(jù)庫information09da2，按學(xué)生的姓名拼音排序查詢，顯示學(xué)生的序號(hào)、姓名、郵編、性別、戶口所在地。在新建查詢執(zhí)窗口中輸入“select序號(hào),姓名,郵編,性別,戶口所在地from戶口和住宿表orderby姓名”，如圖8-1所示。（2）統(tǒng)計(jì)在校住宿生人數(shù)，在新建查詢執(zhí)窗口中輸入“selectcount(是否住宿)as住宿生總數(shù)from戶口和住宿表where是否住宿=是”，如圖8-2所示。實(shí)訓(xùn)操作任務(wù)八排序orderby與分組groupby（3）統(tǒng)計(jì)住宿學(xué)生總數(shù)和不住宿學(xué)生總數(shù)，總學(xué)生數(shù)。在新建查詢執(zhí)窗口中輸入：selectcount(casewhen是否住宿=是then1end)as住宿生總數(shù),count(casewhen是否住宿=否then1end)as不住宿生總數(shù),count(是否住宿)as學(xué)生總數(shù)from戶口和住宿表執(zhí)行結(jié)果如圖8-3所示。實(shí)訓(xùn)操作任務(wù)八排序orderby與分組groupby（4）分別統(tǒng)計(jì)男生住宿情況和女學(xué)生住宿情況，統(tǒng)計(jì)學(xué)生總數(shù)。在新建查詢執(zhí)窗口中輸入：selectcount(casewhen性別=男and是否住宿=是then1end)as男生住宿生總數(shù),count(casewhen性別=男and是否住宿=否then1end)as男生不住宿生總數(shù),count(casewhen性別=女a(chǎn)nd是否住宿=是then1end)as女生住宿生總數(shù),count(casewhen性別=女a(chǎn)nd是否住宿=否then1end)as女生不住宿生總數(shù),count(是否住宿)as學(xué)生總數(shù)from戶口和住宿表。執(zhí)行結(jié)果如圖8-4所示。實(shí)訓(xùn)操作任務(wù)八排序orderby與分組groupby實(shí)訓(xùn)操作任務(wù)八排序orderby與分組groupby實(shí)訓(xùn)操作任務(wù)八排序orderby與分組groupby實(shí)訓(xùn)操作任務(wù)八排序orderby與分組groupby實(shí)訓(xùn)操作任務(wù)八排序orderby與分組groupby1任務(wù)七、SQL基本查詢2任務(wù)八、排序orderby與分組groupby

項(xiàng)目二3任務(wù)九、聚合數(shù)據(jù)查詢與分組統(tǒng)計(jì)數(shù)據(jù)4任務(wù)十、連接查詢5任務(wù)十一、組合查詢6任務(wù)十二、插入、更新知識(shí)儲(chǔ)備任務(wù)九聚合數(shù)據(jù)查詢與分組統(tǒng)計(jì)數(shù)據(jù)分別使用SQLServer2005數(shù)據(jù)庫管理系統(tǒng)和SQL語句創(chuàng)建數(shù)據(jù)庫和表。下面簡單介紹SQLServer中一些常用的對(duì)象，如表、視圖、索引、存儲(chǔ)過程、觸發(fā)器等。任務(wù)九、聚合數(shù)據(jù)查詢與分組統(tǒng)計(jì)數(shù)據(jù)知識(shí)儲(chǔ)備一、聚合數(shù)據(jù)查詢?nèi)蝿?wù)九聚合數(shù)據(jù)查詢與分組統(tǒng)計(jì)數(shù)據(jù)GROUPBY子句可以將查詢結(jié)果按屬性列或?qū)傩粤薪M合在行的方向上進(jìn)行分組，每組在屬性列或?qū)傩粤薪M合上具有相同的聚合值。常用的聚合函數(shù)如表9-1所示。知識(shí)儲(chǔ)備任務(wù)九聚合數(shù)據(jù)查詢與分組統(tǒng)計(jì)數(shù)據(jù)【例1】查詢求學(xué)號(hào)為001學(xué)生的總分和平均分。程序清單如下：SELECTSUM(score)AS總分,AVG(score)AS平均分FROMSCWHEREsno=001【例2】查詢求信息工程系學(xué)生的總數(shù)。SELECTCOUNT(sno)FROMSWHEREdept=信息工程系知識(shí)儲(chǔ)備任務(wù)九聚合數(shù)據(jù)查詢與分組統(tǒng)計(jì)數(shù)據(jù)【例3】查詢求學(xué)校中共有多少個(gè)系。程序清單如下：SELECTCOUNT(DISTINCTdept)AS系部個(gè)數(shù)FROMS注意：加入關(guān)鍵字DISTINCT后表示消去重復(fù)行，可計(jì)算字段“dept”不同值的數(shù)目。COUNT函數(shù)對(duì)空值不計(jì)算，但對(duì)零進(jìn)行計(jì)算。知識(shí)儲(chǔ)備二、分組統(tǒng)計(jì)數(shù)據(jù)任務(wù)九聚合數(shù)據(jù)查詢與分組統(tǒng)計(jì)數(shù)據(jù)在分組查詢中，只要表達(dá)式中不包括聚合函數(shù)，就可以按該表達(dá)式分組GROUPBY子句還可以與WHERE子句配合使用，WHERE子句先于GROUPBY子句執(zhí)行，將滿足條件的記錄保留下來，然后，再按照GROUPBY子句分成小組。若在分組后還要按照一定的條件進(jìn)行篩選，則需要使用HAVING子句。知識(shí)儲(chǔ)備任務(wù)九聚合數(shù)據(jù)查詢與分組統(tǒng)計(jì)數(shù)據(jù)【例4】查詢每位學(xué)生的學(xué)號(hào)及其選課的門數(shù)。程序清單如下：SELECTcno,COUNT(*)AS選課門數(shù)FROMSCGROUPBYcnoGROUPBY子句按cno的值分組，所有具有相同cno的元組為一組，對(duì)每一組使用函數(shù)COUNT進(jìn)行計(jì)算，統(tǒng)計(jì)出各位學(xué)生選課的門數(shù)。知識(shí)儲(chǔ)備任務(wù)九聚合數(shù)據(jù)查詢與分組統(tǒng)計(jì)數(shù)據(jù)【例5】在分組查詢中使用WHERE條件，查詢計(jì)算機(jī)系的學(xué)生學(xué)號(hào)及平均成績。程序清單如下：SELECTsno,AVG(score)ASaveragescoreFROMSCWHEREsno=(SELECTsnoFROMSWHEREdept=’計(jì)算機(jī)’)GROUPBYsnoORDERBYsno知識(shí)儲(chǔ)備任務(wù)九聚合數(shù)據(jù)查詢與分組統(tǒng)計(jì)數(shù)據(jù)【例6】在分組查詢中使用HAVING條件，查詢平均成績大于85分的學(xué)生學(xué)號(hào)及平均成績。程序清單如下：SELECTsno,AVG(score)ASaveragescoreFROMSCGROUPBYsnoHAVINGAVG(score)>85知識(shí)儲(chǔ)備任務(wù)九聚合數(shù)據(jù)查詢與分組統(tǒng)計(jì)數(shù)據(jù)【例7】查詢選課在三門以上且各門課程均及格的學(xué)生的學(xué)號(hào)及其總成績，查詢結(jié)果按總成績降序列出。SELECTsno,SUM(score)AStotalscoreFROMSCWHEREscore>=60GROUPBYsnoHAVINGCOUNT(*)>=3ORDERBYSUM(score)DESC知識(shí)儲(chǔ)備三、反轉(zhuǎn)查詢結(jié)果任務(wù)九聚合數(shù)據(jù)查詢與分組統(tǒng)計(jì)數(shù)據(jù)當(dāng)查詢的結(jié)果不是理想的行列顯示時(shí)，可以使用反轉(zhuǎn)查詢轉(zhuǎn)換?？梢岳肅OUNT函數(shù)忽略NULL值的規(guī)則，借助于CASEEND表達(dá)式實(shí)現(xiàn)?！纠?】查詢每系學(xué)生的男生和女生人數(shù)得到結(jié)果如表9-2所示。知識(shí)儲(chǔ)備任務(wù)九聚合數(shù)據(jù)查詢與分組統(tǒng)計(jì)數(shù)據(jù)知識(shí)儲(chǔ)備任務(wù)九聚合數(shù)據(jù)查詢與分組統(tǒng)計(jì)數(shù)據(jù)要想得到如表9-3所示的表格。知識(shí)儲(chǔ)備任務(wù)九聚合數(shù)據(jù)查詢與分組統(tǒng)計(jì)數(shù)據(jù)查詢語句如下：SELECTdept,COUNT(CASEWHENsex=’男’THEN1ELSENULLEND)AS男生人數(shù),COUNT(CASEWHENsex=’女’THEN1ELSENULLEND)AS女生人數(shù)FROMstudentGROUPBYdept知識(shí)儲(chǔ)備四、HAVING子句任務(wù)九聚合數(shù)據(jù)查詢與分組統(tǒng)計(jì)數(shù)據(jù)在SQL中增加HAVING子句原因是，WHERE關(guān)鍵字無法與統(tǒng)計(jì)函數(shù)一起使用。如果只想要看到分組的統(tǒng)計(jì)結(jié)果，不需要全部的統(tǒng)計(jì)信息。【例9】查詢信息工程系和機(jī)電工程系的學(xué)生數(shù)。SELECTdept,COUNT(*)AS人數(shù)FORMstudentGROUPBYdeptHAVINGdeptIN(‘信息工程’,’機(jī)電工程’)知識(shí)儲(chǔ)備任務(wù)九聚合數(shù)據(jù)查詢與分組統(tǒng)計(jì)數(shù)據(jù)【例10】訂單表如表9-4所示，有商品編號(hào)Id、訂單日期OderDate、訂單價(jià)格OrderPrice和訂單的顧客姓名。知識(shí)儲(chǔ)備任務(wù)九聚合數(shù)據(jù)查詢與分組統(tǒng)計(jì)數(shù)據(jù)若希望查找訂單總金額少于2000的客戶。則使用如下SQL語句：SELECTCustomer,SUM(OrderPrice)FROMOrdersGROUPBYCustomerHAVINGSUM(OrderPrice)<2000若希望查找客戶“李其?！被颉翱灼娇　睋碛谐^2500的訂單總金額。則在SQL語句中增加了WHERE子句：SELECTCustomer,SUM(OrderPrice)FROMOrdersWHERECustomer=李其福ORCustomer=孔平俊GROUPBYCustomerHAVINGSUM(OrderPrice)>2500知識(shí)儲(chǔ)備任務(wù)九聚合數(shù)據(jù)查詢與分組統(tǒng)計(jì)數(shù)據(jù)對(duì)于WHERE和HAVING，HAVING子句主要用于篩選組，而WHERE子句用于篩選記錄；HAVING子句中可以運(yùn)用聚合函數(shù)，而WHERE子句不能運(yùn)用聚合函數(shù)；HAVING子句不能出現(xiàn)既不被GROUPBY子句包含的字段，又不被聚合函數(shù)包含的字段，而WHERE子句中可出現(xiàn)所需的任意字段。通過上例可以知道使用的順序一般為先WHRER，再GROUPBY分組，最后是HAVING子句分組條件。任務(wù)描述任務(wù)九聚合數(shù)據(jù)查詢與分組統(tǒng)計(jì)數(shù)據(jù)對(duì)學(xué)生成績的聚合數(shù)據(jù)查詢，利用數(shù)據(jù)庫information10da5da6，根據(jù)數(shù)據(jù)表“信息10大5”，對(duì)表中的數(shù)據(jù)進(jìn)行聚合數(shù)據(jù)查詢；利用數(shù)據(jù)庫information09da2中的數(shù)據(jù)表“戶口和住宿表”，進(jìn)行分組統(tǒng)計(jì)查詢。實(shí)訓(xùn)操作任務(wù)九聚合數(shù)據(jù)查詢與分組統(tǒng)計(jì)數(shù)據(jù)（1）聚合數(shù)據(jù)查詢，利用數(shù)據(jù)庫information10da5da6中的數(shù)據(jù)表“信息10大5”，表中有課程為“數(shù)據(jù)庫”的學(xué)生成績，查詢?cè)摪鄬W(xué)生中該門成績大于班級(jí)平均成績的學(xué)生的序號(hào)，姓名和該門課的成績。在新建查詢執(zhí)窗口中輸入：select序號(hào),姓名,數(shù)據(jù)庫from信息10大5where數(shù)據(jù)庫>(selectavg(數(shù)據(jù)庫)from信息10大5)。結(jié)果如圖9-1所示。實(shí)訓(xùn)操作任務(wù)九聚合數(shù)據(jù)查詢與分組統(tǒng)計(jì)數(shù)據(jù)實(shí)訓(xùn)操作任務(wù)九聚合數(shù)據(jù)查詢與分組統(tǒng)計(jì)數(shù)據(jù)（2）利用數(shù)據(jù)表“信息10大5”，有三門課程：數(shù)據(jù)庫，文檔編輯，電子表格。查詢?nèi)T課程的最高分的學(xué)生序號(hào)，姓名和該門課的成績。在新建查詢執(zhí)窗口中輸入：——查詢數(shù)據(jù)庫課程的最高分的學(xué)生姓名select序號(hào),姓名,數(shù)據(jù)庫from信息10大5where數(shù)據(jù)庫=(selectmax(數(shù)據(jù)庫)from信息10大5)實(shí)訓(xùn)操作任務(wù)九聚合數(shù)據(jù)查詢與分組統(tǒng)計(jì)數(shù)據(jù)——查詢文檔編輯課程的最高分的學(xué)生姓名select序號(hào),姓名,文檔編輯from信息10大5where文檔編輯=(selectmax(文檔編輯)from信息10大5)——查詢電子表格課程的最高分的學(xué)生姓名select序號(hào),姓名,電子表格from信息10大5where電子表格=(selectmax(電子表格)from信息10大5)結(jié)果如圖9-2所示。實(shí)訓(xùn)操作任務(wù)九聚合數(shù)據(jù)查詢與分組統(tǒng)計(jì)數(shù)據(jù)實(shí)訓(xùn)操作任務(wù)九聚合數(shù)據(jù)查詢與分組統(tǒng)計(jì)數(shù)據(jù)（3）分組統(tǒng)計(jì)數(shù)據(jù)，利用數(shù)據(jù)庫information09da2中的數(shù)據(jù)表“戶口和住宿表”，查詢每個(gè)宿舍的學(xué)生數(shù)，要考慮到有部分學(xué)生不住宿，該字段值為null，住宿的學(xué)生有宿舍號(hào)和床箱號(hào)，例如：202＃1表示202宿舍1號(hào)床鋪1號(hào)箱子。利用substring(宿舍#床箱號(hào),1,3)進(jìn)行分組。在新建查詢執(zhí)窗口中輸入：——查詢宿舍的學(xué)生數(shù)selectsubstring(宿舍#床箱號(hào),1,3)as宿舍號(hào),count(宿舍#床箱號(hào))as宿舍人數(shù)from戶口和住宿表where宿舍#床箱號(hào)isnotnullgroupbysubstring(宿舍#床箱號(hào),1,3)結(jié)果如圖9-3所示。實(shí)訓(xùn)操作任務(wù)九聚合數(shù)據(jù)查詢與分組統(tǒng)計(jì)數(shù)據(jù)實(shí)訓(xùn)操作任務(wù)九聚合數(shù)據(jù)查詢與分組統(tǒng)計(jì)數(shù)據(jù)（4）having子句的用法，學(xué)校男生宿舍共有4張床鋪，女生宿舍共有6張床鋪?，F(xiàn)查詢男女生宿舍已經(jīng)住滿的宿舍號(hào)和人數(shù)。在新建查詢執(zhí)窗口中輸入：——查詢男女生宿舍學(xué)生已經(jīng)住滿的宿舍號(hào)和人數(shù)selectsubstring(宿舍#床箱號(hào),1,3)as宿舍號(hào),count(宿舍#床箱號(hào))as宿舍人數(shù)from戶口和住宿表where宿舍#床箱號(hào)isnotnullgroupbysubstring(宿舍#床箱號(hào),1,3),性別having((count(宿舍#床箱號(hào))>=4and性別=男)or(count(宿舍#床箱號(hào))>=6and性別=女))結(jié)果如圖9-4所示。實(shí)訓(xùn)操作任務(wù)九聚合數(shù)據(jù)查詢與分組統(tǒng)計(jì)數(shù)據(jù)1任務(wù)七、SQL基本查詢2任務(wù)八、排序orderby與分組groupby

項(xiàng)目二3任務(wù)九、聚合數(shù)據(jù)查詢與分組統(tǒng)計(jì)數(shù)據(jù)4任務(wù)十、連接查詢5任務(wù)十一、組合查詢6任務(wù)十二、插入、更新知識(shí)儲(chǔ)備任務(wù)十連接查詢?cè)赟QLServer2005中，數(shù)據(jù)表之間的聯(lián)系是通過表的字段值來體現(xiàn)的，這種字段稱為連接字段。連接操作的目的就是通過加在連接字段的條件將多個(gè)表連接起來，以便從多個(gè)表中查詢數(shù)據(jù)。當(dāng)查詢同時(shí)涉及兩個(gè)以上的表時(shí)，稱為連接查詢。任務(wù)十、連接查詢知識(shí)儲(chǔ)備一、等值連接與非等值連接任務(wù)十連接查詢連接條件的一般格式為：［<表名1>.］<列名1><比較運(yùn)算符>［<表名2>.］<列名2>其中，比較運(yùn)算符主要有：＝、>、<、>＝、<＝、?。健．?dāng)比較運(yùn)算符為“＝”時(shí)，稱為等值連接，其他情況為非等值連接?！纠?】現(xiàn)有學(xué)生表student和選課表sc，分別如表10-1和表10-2所示。知識(shí)儲(chǔ)備任務(wù)十連接查詢知識(shí)儲(chǔ)備任務(wù)十連接查詢查詢每個(gè)學(xué)生及其選修課程的情況。SELECTstudent.*,sc.*FROMstudent,scWHEREstudent.sno=sc.sno查詢結(jié)果如表10-3所示。自然連接是一種特殊的等值連接，它要求兩個(gè)關(guān)系中進(jìn)行比較的分量必須是相同的屬性組，并且在結(jié)果中把重復(fù)的屬性列去掉，而等值連接并不去掉重復(fù)的屬性列。知識(shí)儲(chǔ)備任務(wù)十連接查詢知識(shí)儲(chǔ)備任務(wù)十連接查詢【例2】對(duì)上例用自然連接完成查詢。SELECTstudent.sno,sname,ssex,sage,sdept,cno,scoreFROMstudent,scWHEREstudent.sno=sc.sno查詢結(jié)果如表10-4所示。知識(shí)儲(chǔ)備任務(wù)十連接查詢知識(shí)儲(chǔ)備任務(wù)十連接查詢【例3】查詢張亮同學(xué)所選修的課程號(hào)為2的課程成績。SELECTstudent.sno,sname,o,scoreFROMstudent,scWHERE(student.sno=sc.sno)AND(student.sname=張亮)AND(o=2)查詢結(jié)果為表10-5所示。知識(shí)儲(chǔ)備任務(wù)十連接查詢知識(shí)儲(chǔ)備任務(wù)十連接查詢【例4】現(xiàn)有學(xué)生表student和選課表sc，如表101和102所示，課程表c如表10-6所示。知識(shí)儲(chǔ)備任務(wù)十連接查詢查詢所有選課學(xué)生的學(xué)號(hào)、姓名、選課名稱及成績。SELECTstudent.sno,sname,cname,scoreFROMstudent,c,scWHEREstudent.sno=sc.snoANDo=o查詢結(jié)果為表10-7所示。本例涉及三個(gè)表，WHERE子句中有兩個(gè)連接條件。當(dāng)有兩個(gè)以上的表進(jìn)行連接時(shí)，稱為多表連接。知識(shí)儲(chǔ)備任務(wù)十連接查詢知識(shí)儲(chǔ)備二、自身連接任務(wù)十連接查詢當(dāng)一個(gè)表與其自已進(jìn)行連接操作時(shí)，稱為表的自身連接。要查詢的內(nèi)容均在同一表中，可以將表分別取兩個(gè)別名，一個(gè)是x，一個(gè)是y。將x，y中滿足查詢條件的行連接起來。這實(shí)際上是同一表的自身連接。【例5】現(xiàn)有教師工資表TeacherSalary如表10-8所示，查詢所有比教師“李強(qiáng)”工資高的教師姓名、性別、工資和李四的工資。知識(shí)儲(chǔ)備任務(wù)十連接查詢知識(shí)儲(chǔ)備任務(wù)十連接查詢程序清單如下：SELECTAS姓名,x.salaryAS工資,y.salaryAS李強(qiáng)工資FROMTeacherSalaryasx，TeacherSalaryasyWHEREx.salary>y.salaryAND=李強(qiáng)知識(shí)儲(chǔ)備任務(wù)十連接查詢需要給表起別名以示區(qū)別，由于所有屬性都是同名屬性，因此必須使用別名前綴。查詢結(jié)要如表10-9所示。知識(shí)儲(chǔ)備三、外連接任務(wù)十連接查詢普通連接操作只輸出滿足連接條件的元組；外連接操作以指定表為連接主體，將主體表中不滿足連接條件的元組一起輸出?？梢苑譃樽笸膺B接和右外連接，左外連接的結(jié)果集包括LEFTOUTER子句或LEFTOUTERJOIN中指定的左表的所有行，而不僅僅是連接列所匹配的行。如果左表的某行在右表中沒有匹配行，則在相關(guān)聯(lián)的結(jié)果集行中右表的所有選擇列表列均為空值。右外連接RIGHTJOIN或RIGHTOUTERJOIN將返回右表的所有行。如果右表的某行在左表中沒有匹配行，則將為左表返回空值。知識(shí)儲(chǔ)備任務(wù)十連接查詢【例6】在學(xué)生表10-1中增加兩個(gè)學(xué)生，分別為“劉艷”和“劉希妍”，如表10-10所示。選課表sc如表10-2所示。知識(shí)儲(chǔ)備任務(wù)十連接查詢左外連接查詢學(xué)生表student各選課表sc。SELECTstudent.sno,sname,ssex,sage,sdept,cno,scoreFROMstudentLEFTOUTERJIONscON(student.sno=sc.sno)執(zhí)行結(jié)果如表10-11所示。知識(shí)儲(chǔ)備任務(wù)十連接查詢知識(shí)儲(chǔ)備四、嵌套查詢?nèi)蝿?wù)十連接查詢一個(gè)SELECTFROMWHERE語句稱為一個(gè)查詢塊，將一個(gè)查詢塊嵌套在另一個(gè)查詢塊的WHERE子句或HAVING短語的條件中的查詢?yōu)榍短撞樵儭?.帶有IN的子查詢子查詢?cè)谏弦患?jí)查詢處理之前求解，子查詢的結(jié)果用于建立父查詢的查找條件。知識(shí)儲(chǔ)備任務(wù)十連接查詢【例7】運(yùn)用表101和表102，查詢選修課程號(hào)為2的學(xué)生名單。SELECTsnameFROMstudentWHEREsnoIN（SELECTsnoFROMscWHEREcno=2)知識(shí)儲(chǔ)備任務(wù)十連接查詢【例8】查詢與“陳路”同系的學(xué)生名單若用分步完成：（1）確定“陳路”所在的系名：SELECTsdeptFROMstudentWHEREsname=陳路結(jié)果為“計(jì)算機(jī)”（2）查找所有在“計(jì)算機(jī)”學(xué)生名單：SELECTsno,sname,sdeptFROMstudentWHEREsdept=計(jì)算機(jī)結(jié)果如表10-12所示。知識(shí)儲(chǔ)備任務(wù)十連接查詢知識(shí)儲(chǔ)備任務(wù)十連接查詢把前面兩步合并在一起的查詢?yōu)椋篠ELECTsno,sname,sdeptFROMstudentWHEREsdeptIN(SELECTsdeptFROMstudentWHEREsname=陳路)本例的IN可以用“=”代替。知識(shí)儲(chǔ)備任務(wù)十連接查詢2.帶有比較運(yùn)算符的子查詢【例9】查詢超出課程平均成績的學(xué)生號(hào)和課程號(hào)。SELECTsno,cnoFROMSCxWHEREscore>=(SELECTavg(score)FROMSCyWHEREy.sno=x.sno)知識(shí)儲(chǔ)備任務(wù)十連接查詢

3.帶有ANY、SOME或ALL的子查詢【例10】查詢其他系中比計(jì)算機(jī)系所有學(xué)生年齡都要小的學(xué)生姓名和年齡。SELECTsname,sageFROMstudentWHEREsage<ALL(SELECTsageFROMstudentWHEREsdept=計(jì)算機(jī))ADsdept<>計(jì)算機(jī)知識(shí)儲(chǔ)備任務(wù)十連接查詢運(yùn)用上述的表101，可以查詢出兩位“張?jiān)隆焙汀皠⑾ｅ钡姆蠗l件，她們的年齡都比計(jì)算機(jī)系所有學(xué)生的年齡都要小。用聚合函數(shù)改寫為：SELECTsname,sageFROMstudentWHEREsage<ALL(SELECTMIN(sage)FROMstudentWHEREsdept=計(jì)算機(jī))ANDsdept<>計(jì)算機(jī)知識(shí)儲(chǔ)備任務(wù)十連接查詢4.帶EXISTS的子查詢不需要返回具體的查詢數(shù)據(jù)，而只關(guān)心是否有返回值，即返回True或False?！纠?1】運(yùn)用表101和102，查詢沒有選修1號(hào)課程的學(xué)生姓名。SELECTsnameFROMstudentWHERENOTEXISTS(SELECT*FROMscWHEREsno=student.snoANDcno=1)任務(wù)描述任務(wù)十連接查詢數(shù)據(jù)庫ssts中有三個(gè)數(shù)據(jù)表“學(xué)生表”“課程表”“選課表”，三表連接查詢。查詢信息工程系所有學(xué)生的成績，要求輸出學(xué)號(hào)、姓名、課程名、成績，并按成績降序排序；查詢所有成績小于60分的學(xué)生，要求輸出學(xué)號(hào)、姓名、課程名、成績，并按學(xué)號(hào)和課程號(hào)升序、成績降序排序；查詢各課程均分，并按均分降序排序。實(shí)訓(xùn)操作任務(wù)十連接查詢（1）連接數(shù)據(jù)庫ssts，打開數(shù)據(jù)庫的三張表“學(xué)生表”“課程表”“選課表”，了解各個(gè)表的字段名稱，如圖10-1所示。（2）根據(jù)“學(xué)生表”“課程表”“選課表”三張表，查詢信息工程系所有學(xué)生的成績，要求輸出學(xué)號(hào)、姓名、課程名、成績，并按成績降序排序。在新建查詢執(zhí)窗口中輸入：——查詢信息工程系所有學(xué)生的成績，要求輸出學(xué)號(hào)、姓名、課程名、成績，并按成績降序排序select學(xué)生表，學(xué)號(hào),姓名,課程名,成績from學(xué)生表,課程表,選課表where系名=信息工程系and學(xué)生表.學(xué)號(hào)=選課表.學(xué)號(hào)and課程表.課程號(hào)=選課表.課程號(hào)orderby成績desc。運(yùn)行結(jié)果如圖10-2所示。實(shí)訓(xùn)操作任務(wù)十連接查詢實(shí)訓(xùn)操作任務(wù)十連接查詢實(shí)訓(xùn)操作任務(wù)十連接查詢（3）上面使用的是where語句，如果使用連接查詢，則使用innerjoin…on…語句。根據(jù)“學(xué)生表”“課程表”“選課表”三張表，查詢機(jī)電工程系所有學(xué)生的成績，要求輸出學(xué)號(hào)、姓名、課程名、成績，并按成績降序排序。在新建查詢執(zhí)窗口中輸入：——查詢機(jī)電工程系所有學(xué)生的成績，要求輸出學(xué)號(hào)、姓名、課程名、成績，并學(xué)號(hào)升序排序select學(xué)生表.學(xué)號(hào),姓名,課程名,成績from(選課表innerjoin學(xué)生表on選課表.學(xué)號(hào)=學(xué)生表.學(xué)號(hào))innerjoin課程表on選課表.課程號(hào)=課程表.課程號(hào)where系名=機(jī)電工程系orderby學(xué)號(hào)運(yùn)行結(jié)果如圖10-3所示。實(shí)訓(xùn)操作任務(wù)十連接查詢實(shí)訓(xùn)操作任務(wù)十連接查詢（4）根據(jù)“學(xué)生表”“課程表”“選課表”三張表，查詢所有成績小于60分的學(xué)生，要求輸出學(xué)號(hào)、姓名、課程名、成績，并按學(xué)號(hào)和課程號(hào)升序、成績降序排序。在新建查詢執(zhí)窗口中輸入：——查詢所有成績小于60分的學(xué)生，要求輸出學(xué)號(hào)、姓名、課程名、成績，并按學(xué)號(hào)和課程號(hào)升序、成績降序排序select學(xué)生表.學(xué)號(hào),姓名,課程名,成績from(選課表innerjoin學(xué)生表on選課表.學(xué)號(hào)=學(xué)生表.學(xué)號(hào))innerjoin課程表on選課表.課程號(hào)=課程表.課程號(hào)where成績<60orderby學(xué)生表.學(xué)號(hào),課程表.課程號(hào)asc,成績desc運(yùn)行結(jié)果如圖10-4所示。實(shí)訓(xùn)操作任務(wù)十連接查詢（5）查詢學(xué)生所學(xué)習(xí)的各個(gè)課程均分，并按均分降序排序。在新建查詢執(zhí)窗口中輸入：——查詢各課程均分，并按均分降序排序。select課程表.課程號(hào),課程表.課程名,avg(成績)as平均成績from課程表,選課表where課程表.課程號(hào)=選課表.課程號(hào)groupby課程表.課程號(hào),課程表.課程名orderby平均成績desc運(yùn)行結(jié)果如圖10-5所示。實(shí)訓(xùn)操作任務(wù)十連接查詢實(shí)訓(xùn)操作任務(wù)十連接查詢1任務(wù)七、SQL基本查詢2任務(wù)八、排序orderby與分組groupby

項(xiàng)目二3任務(wù)九、聚合數(shù)據(jù)查詢與分組統(tǒng)計(jì)數(shù)據(jù)4任務(wù)十、連接查詢5任務(wù)十一、組合查詢6任務(wù)十二、插入、更新知識(shí)儲(chǔ)備任務(wù)十一組合查詢?cè)赟QLServer2005中，將多個(gè)查詢的結(jié)果放在一起，以一個(gè)查詢結(jié)果的形式顯示出來，則可以使用UNION關(guān)鍵字把多個(gè)SELECT連接起來。每個(gè)SELECT查詢語句應(yīng)當(dāng)有相同數(shù)量的字段，若字段個(gè)數(shù)不等，可以使用NULL來代替；每個(gè)查詢語句中相應(yīng)的字段的類型必須相互兼容，若不兼容，可使用類型轉(zhuǎn)換函數(shù)強(qiáng)制轉(zhuǎn)換字段類型。UNION缺省在合并結(jié)果集后消除重復(fù)項(xiàng)，UNIONALL指定在合并結(jié)果集后保留重復(fù)項(xiàng)。UNION結(jié)果集中的列名總是等于UNION中第一個(gè)SELECT語句中的列名。任務(wù)十一、組合查詢知識(shí)儲(chǔ)備任務(wù)十一組合查詢【例1】網(wǎng)絡(luò)售書情況表InternetInfo如表11-1所示，實(shí)體店面售書表StoreInfo如表11-2所示，現(xiàn)在需要查詢所有售書的訂單日期。知識(shí)儲(chǔ)備任務(wù)十一組合查詢SELECTOrderDateFROMInternetInfoUNIONSELECTOrderDateFROMStoreInfo操作結(jié)果如表11-3所示。SELECTOrderDateFROMInternetInfoUNIONALLSELECTOrderDateFROMStoreInfo操作結(jié)果如表11-4所示。知識(shí)儲(chǔ)備任務(wù)十一組合查詢?nèi)蝿?wù)描述任務(wù)十一組合查詢利用數(shù)據(jù)庫ssts中數(shù)據(jù)表“信息0901班成績”和“信息0902班成績”進(jìn)行組合查詢。查詢兩個(gè)班級(jí)總分高于320分的學(xué)生的學(xué)號(hào)、姓名和成績。查詢兩個(gè)班級(jí)成績小于60分不及格的學(xué)生名單。實(shí)訓(xùn)操作任務(wù)十一組合查詢（1）連接數(shù)據(jù)庫ssts，利用數(shù)據(jù)表“信息0901班成績”和“信息0902班成績”，用union進(jìn)行組合查詢。查詢兩個(gè)班級(jí)總分高于320分的學(xué)生的學(xué)號(hào)、姓名和成績。在新建查詢執(zhí)窗口中輸入：select學(xué)號(hào),姓名,總分from信息0901班成績where總分>320unionselect學(xué)號(hào),姓名,總分from信息0902班成績where總分>320結(jié)果如圖11-1所示。實(shí)訓(xùn)操作任務(wù)十一組合查詢實(shí)訓(xùn)操作任務(wù)十一組合查詢（2）查詢兩個(gè)班級(jí)“網(wǎng)絡(luò)安全”課程成績小于60分的學(xué)生名單。要求輸出學(xué)號(hào)、姓名和課程的成績。在新建查詢執(zhí)窗口中輸入：select學(xué)號(hào),姓名,網(wǎng)絡(luò)安全from信息0901班成績where網(wǎng)絡(luò)安全<60unionselect學(xué)號(hào),姓名,網(wǎng)絡(luò)安全from信息0902班成績where網(wǎng)絡(luò)安全<60運(yùn)行結(jié)果如圖11-2所示。實(shí)訓(xùn)操作任務(wù)十一組合查詢1任務(wù)七、SQL基本查詢2任務(wù)八、排序orderby與分組groupby

項(xiàng)目二3任務(wù)九、聚合數(shù)據(jù)查詢與分組統(tǒng)計(jì)數(shù)據(jù)4任務(wù)十、連接查詢5任務(wù)十一、組合查詢6任務(wù)十二、插入、更新知識(shí)儲(chǔ)備任務(wù)十二插入、更新在SQLServer2005中，有時(shí)為了方便，需要將查詢結(jié)果保存起來。在SELECT子句的后面，F(xiàn)ROM子句的前面加上了一個(gè)INTO關(guān)鍵字，關(guān)鍵字的后面緊跟用于保存查詢結(jié)果的新表的名字。SELECT*(或字段列表)INTO新表名FROM表名任務(wù)十二、插入、更新知識(shí)儲(chǔ)備一、INSERT語句任務(wù)十二插入、更新INSERT語句用于向數(shù)據(jù)庫表或者視圖中加入一行數(shù)據(jù)。INSERT語句的語法形式如下：INSERT［INTO］table_or_view［(column_list)］VALUES(data_values)其中，table_or_view是指要插入新記錄的表或視圖，column_list是可選項(xiàng)，指定待添加數(shù)據(jù)的列，VALUES子句指定待添加數(shù)據(jù)的具體值。列名的排列順序不一定要和表定義時(shí)的順序一致。但當(dāng)指定列名表時(shí)，VALUES子句值的排列順序必須和列名表中的列名排列順序一致，個(gè)數(shù)相等，數(shù)據(jù)類型一一對(duì)應(yīng)。知識(shí)儲(chǔ)備任務(wù)十二插入、更新在進(jìn)行數(shù)據(jù)插入操作時(shí)須注意以下2點(diǎn)：（1）在VALUES子句中，必須用逗號(hào)將各個(gè)數(shù)據(jù)分開，字符型數(shù)據(jù)要用單引號(hào)括起來。（2）有時(shí)并不需要向表中插入完整的行，而需要將數(shù)據(jù)只插入到幾個(gè)指定的字段內(nèi)，則在表名后加上字段列表，且VALUES子句中值的排列順序要和表中各屬性列的排列順序一致。知識(shí)儲(chǔ)備任務(wù)十二插入、更新【例1】將姓名為夏總的相關(guān)信息插入到表中。INSERTINTOstudent(sno,sname,sex,birth,depart)VALUES(0025,夏總,男,1958-10-01,信息工程系)（3）將VALUES子句中的值按照INTO子句中指定列名的順序插入到表中。（4）對(duì)于INTO子句中沒有出現(xiàn)的列，則插入的新記錄在這些列上將取空值。但在表定義時(shí)有NOTNULL約束的屬性列不能取空值，例如學(xué)號(hào)不能為空。知識(shí)儲(chǔ)備一、UPDATE語句任務(wù)十二插入、更新UPDATE語句用于修改數(shù)據(jù)庫表或視圖中特定記錄或者字段的數(shù)據(jù)，其語法形式如下：UPDATEtable_or_viewSET<column>=<expression>［,<column>=<expression>>］…［WHERE<search_condition>］其中：table_or_view是指要修改的表或視圖；SET子句給出要修改的列及其修改后的值，其中column為要修改的列名，expression為其修改后的值；WHERE子句指定待修改的記錄應(yīng)當(dāng)滿足的條件，WHERE子句省略時(shí)，則修改表中的所有記錄。知識(shí)儲(chǔ)備任務(wù)十二插入、更新【例2】帶有WHERE條件的修改語句。UPDATEstudent.addressSETcity=SuzhouWHEREstudent.address=1【例3】