數(shù)據(jù)庫教案 項(xiàng)目二 數(shù)據(jù)庫的查詢

項(xiàng)目二數(shù)據(jù)庫的查詢一、教學(xué)目標(biāo)1.掌握學(xué)習(xí)SQL基本查詢。2.辨析排序orderby與分組groupby。3.學(xué)會聚合數(shù)據(jù)查詢與分組統(tǒng)計數(shù)據(jù)。4.掌握連接查詢、組合查詢。5.了解插入、更新。二、課時分配本項(xiàng)目共6個任務(wù)，安排6課時。三、教學(xué)重點(diǎn)通過本項(xiàng)目的學(xué)習(xí)，讓學(xué)生了解學(xué)習(xí)SQL基本查詢，掌握通配符“*”的使用，學(xué)習(xí)多個字段拼接，掌握WHERE子句使用方法；辨析排序orderby與分組groupby，學(xué)習(xí)INTO子句和TOP子句；掌握并學(xué)會在數(shù)據(jù)庫information09da2中，利用排序ORDERBY和分組GROUPBY進(jìn)行SQL查詢操作。按學(xué)生的姓名拼音排序，顯示學(xué)生的序號、姓名、性別、戶口所在地。分別統(tǒng)計男女在校住宿生人數(shù)和住宿生總數(shù)。統(tǒng)計戶口在同一城市的學(xué)生人數(shù)，并按統(tǒng)計的人數(shù)從少到多排序；學(xué)會聚合數(shù)據(jù)查詢以及分組統(tǒng)計數(shù)據(jù)；學(xué)會對學(xué)生成績的聚合數(shù)據(jù)查詢，利用數(shù)據(jù)庫information10da5da6，根據(jù)數(shù)據(jù)表“信息10大5”，對表中的數(shù)據(jù)進(jìn)行聚合數(shù)據(jù)查詢；利用數(shù)據(jù)庫information09da2中的數(shù)據(jù)表“戶口和住宿表”，進(jìn)行分組統(tǒng)計查詢；掌握什么是連接查詢，分清等值連接與非等值連接、自身連接、外連接、嵌套查詢；掌握在SQLServer2005中，將多個查詢的結(jié)果放在一起，以一個查詢結(jié)果的形式顯示出來，則可以使用UNION關(guān)鍵字把多個SELECT連接起來。每個SELECT查詢語句應(yīng)當(dāng)有相同數(shù)量的字段，若字段個數(shù)不等，可以使用NULL來代替；每個查詢語句中相應(yīng)的字段的類型必須相互兼容，若不兼容，可使用類型轉(zhuǎn)換函數(shù)、強(qiáng)制轉(zhuǎn)換字段類型；學(xué)會INSERT語句、批量插入數(shù)據(jù)，將成績不及格的學(xué)生信息批量插入到新建的數(shù)據(jù)表中。四、教學(xué)難點(diǎn)1.掌握SQL基本查詢。2.辨析排序orderby與分組groupby的區(qū)別。3.掌握聚合數(shù)據(jù)查詢與分組統(tǒng)計數(shù)據(jù)。4.掌握連接查詢、組合查詢、插入與更新。五、教學(xué)內(nèi)容任務(wù)七SQL基本查詢在SQLServer2005中，master數(shù)據(jù)庫是默認(rèn)的當(dāng)前數(shù)據(jù)庫，所以在編寫操作SQL語句時，先使用USE語句打開要使用的表所在的數(shù)據(jù)庫，否則就會出現(xiàn)“對象名無效”提示。一、單表查詢1.通配符“*”的使用在SELECT語句中，可以使用通配符“*”來顯示所有的字段?！纠?】查詢學(xué)生信息表中所有學(xué)生的信息。USEinformation09da2SELECT*FROM學(xué)生信息表SELECT后面列出要查詢的字段名，字段之間要用英文逗號“,”隔開。如果要去掉重復(fù)的查詢結(jié)果，要加上DISTINCT。用關(guān)鍵字AS可以命名新列，也可以給現(xiàn)有字段取別名。2.用AS重新指定返回字段的名字【例2】查詢學(xué)生信息表中所有學(xué)生的姓名和性別，其中sname用“姓名”顯示，sex用“性別”顯示。SELECTsnameAS姓名,sexAS性別FROM學(xué)生信息表3.多個字段拼接如果需要將多個字段拼接成一個字段，可以使用“+”連接，使用函數(shù)rtrim去掉字段右側(cè)的空格?！纠?】查詢學(xué)生信息表中所有學(xué)生的姓名和系別，并生成拼接成一個字段。SELECTRTRIM(sname)+‘(’+RTRIM(depart)+‘)’AS姓名和系別FROM學(xué)生信息表運(yùn)行的結(jié)果如：張三(信息工程系)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?！纠?】查詢學(xué)生信息表中所有學(xué)生的年齡不小于18歲的女生的姓名和性別。SELECT姓名,性別FROM學(xué)生信息表WHERE性別=‘女’AND年齡>=185.查詢常用的通配符常用的通配符見表7-1。在sqlsever中nchar，nvarchar使用的是unicode字符集。而char，varchar使用的是ASCII字符集。規(guī)定：當(dāng)unicode數(shù)據(jù)（nchar或nvarchar）與like一起使用時，尾隨空格有意義。但對非unicode數(shù)據(jù)，尾隨空格無意義。而ASCII中char字符串后面的空格沒有意義，即char和varchar尾部空格無意義。數(shù)據(jù)庫中“姓名”列的數(shù)據(jù)類型，若數(shù)據(jù)類型為nchar(8)，所以要查詢姓“王”且單名的學(xué)生例如“王石”情況，則查詢語句應(yīng)該為：SELECT*FROM學(xué)生信息表WHERE姓名LIKE王_——王下劃線后面是6個空格，要與nchar的長度一致。任務(wù)八、排序orderby與分組groupby一、排序ORDERBY在SQLServer2005中，為了方便查看查詢結(jié)果，按某種規(guī)律排序，可使用ORDERBY子句來排序數(shù)據(jù)，還可以進(jìn)行多字段排序。【例1】從學(xué)生表student中按照成績grade升序排序。SELECT*FROMstudentORDERBYgrade也就是按照成績grade升序排序，默認(rèn)排序方式是升序，功能等于在字段名后加上升序關(guān)鍵字ASC，ORDERBY后的字段名可以不出現(xiàn)在SELECT字段列表中，如果要降序排列，在字段名后加上DESC。例如：ORDERBYgradeDESC二、分組GROUPBY數(shù)據(jù)分組經(jīng)常運(yùn)用在統(tǒng)計匯總中，運(yùn)用GROUPBY可以進(jìn)行分組，如果要將滿足條件的分組查詢出來，還需要使用HAVING子句。需要注意的是，使用GROUPBY時，不要使用SELECT*語句，否則有“列表中的列無效，因?yàn)樵摿袥]有包含在聚合函數(shù)或GROUPBY子句中”錯誤提示。如果SELECT子句后是字段名列表，而這些字段名又不在聚合函數(shù)中，則應(yīng)當(dāng)在GROUPBY子句中列出所有這些字段名，這時分組就是這些字段的組合，而并非單個字段分組?！纠?】從學(xué)生表student中按照性別sex分組，統(tǒng)計男女生人數(shù)。SELECTsex,COUNT(*)As人數(shù)FROMstudentGROUPBYsex三、INTO子句和TOP子句對已經(jīng)存在的表，運(yùn)用INTO子句可以創(chuàng)建一張新表，此新表存儲在數(shù)據(jù)庫中，不會顯示在屏幕上，因此，建立后可以用SELECT顯示全部記錄。若INTO后的表名前有“?！碧?，表示該新表為臨時表，退出SQLServer后，不保存該新表?！纠?】從學(xué)生表student中按照系別，把信息工程系的學(xué)生記錄單獨(dú)生成一張新表“信息工程系學(xué)生表”。SELECT*INTO信息工程系學(xué)生表FROMstudentWHERE系別=‘信息工程系’SELECT*FROM信息工程系學(xué)生表TOP子句可以限定顯示前n條記錄?！纠?】顯示學(xué)生表student中前10條記錄。SELECTTOP3*FROMstudent任務(wù)九、聚合數(shù)據(jù)查詢與分組統(tǒng)計數(shù)據(jù)一、聚合數(shù)據(jù)查詢GROUPBY子句可以將查詢結(jié)果按屬性列或?qū)傩粤薪M合在行的方向上進(jìn)行分組，每組在屬性列或?qū)傩粤薪M合上具有相同的聚合值。常用的聚合函數(shù)如表9-1所示?！纠?】查詢求學(xué)號為001學(xué)生的總分和平均分。程序清單如下：SELECTSUM(score)AS總分,AVG(score)AS平均分FROMSCWHEREsno=001注意：函數(shù)SUM和AVG只能對數(shù)值型字段進(jìn)行計算。【例2】查詢求信息工程系學(xué)生的總數(shù)。SELECTCOUNT(sno)FROMSWHEREdept=信息工程系【例3】查詢求學(xué)校中共有多少個系。程序清單如下：SELECTCOUNT(DISTINCTdept)AS系部個數(shù)FROMS注意：加入關(guān)鍵字DISTINCT后表示消去重復(fù)行，可計算字段“dept”不同值的數(shù)目。COUNT函數(shù)對空值不計算，但對零進(jìn)行計算。二、分組統(tǒng)計數(shù)據(jù)在分組查詢中，只要表達(dá)式中不包括聚合函數(shù)，就可以按該表達(dá)式分組?！纠?】查詢每位學(xué)生的學(xué)號及其選課的門數(shù)。程序清單如下：SELECTcno,COUNT(*)AS選課門數(shù)FROMSCGROUPBYcnoGROUPBY子句按cno的值分組，所有具有相同cno的元組為一組，對每一組使用函數(shù)COUNT進(jìn)行計算，統(tǒng)計出各位學(xué)生選課的門數(shù)。GROUPBY子句還可以與WHERE子句配合使用，WHERE子句先于GROUPBY子句執(zhí)行，將滿足條件的記錄保留下來，然后，再按照GROUPBY子句分成小組。若在分組后還要按照一定的條件進(jìn)行篩選，則需要使用HAVING子句。【例5】在分組查詢中使用WHERE條件，查詢計算機(jī)系的學(xué)生學(xué)號及平均成績。程序清單如下：SELECTsno,AVG(score)ASaveragescoreFROMSCWHEREsno=(SELECTsnoFROMSWHEREdept=’計算機(jī)’)GROUPBYsnoORDERBYsno【例6】在分組查詢中使用HAVING條件，查詢平均成績大于85分的學(xué)生學(xué)號及平均成績。程序清單如下：SELECTsno,AVG(score)ASaveragescoreFROMSCGROUPBYsnoHAVINGAVG(score)>85【例7】查詢選課在三門以上且各門課程均及格的學(xué)生的學(xué)號及其總成績，查詢結(jié)果按總成績降序列出。程序清單如下：SELECTsno,SUM(score)AStotalscoreFROMSCWHEREscore>=60GROUPBYsnoHAVINGCOUNT(*)>=3ORDERBYSUM(score)DESC三、反轉(zhuǎn)查詢結(jié)果當(dāng)查詢的結(jié)果不是理想的行列顯示時，可以使用反轉(zhuǎn)查詢轉(zhuǎn)換?？梢岳肅OUNT函數(shù)忽略NULL值的規(guī)則，借助于CASEEND表達(dá)式實(shí)現(xiàn)?！纠?】查詢每系學(xué)生的男生和女生人數(shù)得到結(jié)果如表9-2所示。要想得到如表9-3所示的表格。查詢語句如下：SELECTdept,COUNT(CASEWHENsex=’男’THEN1ELSENULLEND)AS男生人數(shù),COUNT(CASEWHENsex=’女’THEN1ELSENULLEND)AS女生人數(shù)FROMstudentGROUPBYdept四、HAVING子句在SQL中增加HAVING子句原因是，WHERE關(guān)鍵字無法與統(tǒng)計函數(shù)一起使用。如果只想要看到分組的統(tǒng)計結(jié)果，不需要全部的統(tǒng)計信息?！纠?】查詢信息工程系和機(jī)電工程系的學(xué)生數(shù)。SELECTdept,COUNT(*)AS人數(shù)FORMstudentGROUPBYdeptHAVINGdeptIN(‘信息工程’,’機(jī)電工程’)【例10】訂單表如表9-4所示，有商品編號Id、訂單日期OderDate、訂單價格OrderPrice和訂單的顧客姓名。若希望查找訂單總金額少于2000的客戶。則使用如下SQL語句：SELECTCustomer,SUM(OrderPrice)FROMOrdersGROUPBYCustomerHAVINGSUM(OrderPrice)<2000若希望查找客戶“李其?！被颉翱灼娇　睋碛谐^2500的訂單總金額。則在SQL語句中增加了WHERE子句：SELECTCustomer,SUM(OrderPrice)FROMOrdersWHERECustomer=李其福ORCustomer=孔平俊GROUPBYCustomerHAVINGSUM(OrderPrice)>2500對于WHERE和HAVING，HAVING子句主要用于篩選組，而WHERE子句用于篩選記錄；HAVING子句中可以運(yùn)用聚合函數(shù)，而WHERE子句不能運(yùn)用聚合函數(shù)；HAVING子句不能出現(xiàn)既不被GROUPBY子句包含的字段，又不被聚合函數(shù)包含的字段，而WHERE子句中可出現(xiàn)所需的任意字段。通過上例可以知道使用的順序一般為先WHRER，再GROUPBY分組，最后是HAVING子句分組條件。任務(wù)十、連接查詢一、等值連接與非等值連接連接條件的一般格式為：［<表名1>.］<列名1><比較運(yùn)算符>［<表名2>.］<列名2>其中，比較運(yùn)算符主要有：＝、>、<、>＝、<＝、?。?。當(dāng)比較運(yùn)算符為“＝”時，稱為等值連接，其他情況為非等值連接?！纠?】現(xiàn)有學(xué)生表student和選課表sc，分別如表10-1和表10-2所示。查詢每個學(xué)生及其選修課程的情況。SELECTstudent.*,sc.*FROMstudent,scWHEREstudent.sno=sc.sno查詢結(jié)果如表10-3所示。自然連接是一種特殊的等值連接，它要求兩個關(guān)系中進(jìn)行比較的分量必須是相同的屬性組，并且在結(jié)果中把重復(fù)的屬性列去掉，而等值連接并不去掉重復(fù)的屬性列?！纠?】對上例用自然連接完成查詢。SELECTstudent.sno,sname,ssex,sage,sdept,cno,scoreFROMstudent,scWHEREstudent.sno=sc.sno查詢結(jié)果如表10-4所示。【例3】查詢張亮同學(xué)所選修的課程號為2的課程成績。SELECTstudent.sno,sname,o,scoreFROMstudent,scWHERE(student.sno=sc.sno)AND(student.sname=張亮)AND(o=2)查詢結(jié)果為表10-5所示?！纠?】現(xiàn)有學(xué)生表student和選課表sc，如表10-1和10-2所示，課程表c如表10-6所示。查詢所有選課學(xué)生的學(xué)號、姓名、選課名稱及成績。SELECTstudent.sno,sname,cname,scoreFROMstudent,c,scWHEREstudent.sno=sc.snoANDo=o查詢結(jié)果為表10-7所示。本例涉及三個表，WHERE子句中有兩個連接條件。當(dāng)有兩個以上的表進(jìn)行連接時，稱為多表連接。二、自身連接當(dāng)一個表與其自已進(jìn)行連接操作時，稱為表的自身連接。要查詢的內(nèi)容均在同一表中，可以將表分別取兩個別名，一個是x，一個是y。將x，y中滿足查詢條件的行連接起來。這實(shí)際上是同一表的自身連接?！纠?】現(xiàn)有教師工資表TeacherSalary如表10-8所示，查詢所有比教師“李強(qiáng)”工資高的教師姓名、性別、工資和李四的工資。程序清單如下：SELECTAS姓名,x.salaryAS工資,y.salaryAS李強(qiáng)工資FROMTeacherSalaryasx，TeacherSalaryasyWHEREx.salary>y.salaryAND=李強(qiáng)需要給表起別名以示區(qū)別，由于所有屬性都是同名屬性，因此必須使用別名前綴。查詢結(jié)要如表10-9所示。三、外連接普通連接操作只輸出滿足連接條件的元組；外連接操作以指定表為連接主體，將主體表中不滿足連接條件的元組一起輸出?？梢苑譃樽笸膺B接和右外連接，左外連接的結(jié)果集包括LEFTOUTER子句或LEFTOUTERJOIN中指定的左表的所有行，而不僅僅是連接列所匹配的行。如果左表的某行在右表中沒有匹配行，則在相關(guān)聯(lián)的結(jié)果集行中右表的所有選擇列表列均為空值。右外連接RIGHTJOIN或RIGHTOUTERJOIN將返回右表的所有行。如果右表的某行在左表中沒有匹配行，則將為左表返回空值?！纠?】在學(xué)生表10-1中增加兩個學(xué)生，分別為“劉艷”和“劉希妍”，如表10-10所示。選課表sc如表10-2所示。左外連接查詢學(xué)生表student各選課表sc。SELECTstudent.sno,sname,ssex,sage,sdept,cno,scoreFROMstudentLEFTOUTERJIONscON(student.sno=sc.sno)執(zhí)行結(jié)果如表10-11所示。四、嵌套查詢一個SELECTFROMWHERE語句稱為一個查詢塊，將一個查詢塊嵌套在另一個查詢塊的WHERE子句或HAVING短語的條件中的查詢?yōu)榍短撞樵儭?.帶有IN的子查詢子查詢在上一級查詢處理之前求解，子查詢的結(jié)果用于建立父查詢的查找條件?！纠?】運(yùn)用表10-1和表10-2，查詢選修課程號為2的學(xué)生名單。SELECTsnameFROMstudentWHEREsnoIN（SELECTsnoFROMscWHEREcno=2)【例8】查詢與“陳路”同系的學(xué)生名單若用分步完成：（1）確定“陳路”所在的系名：SELECTsdeptFROMstudentWHEREsname=陳路結(jié)果為“計算機(jī)”（2）查找所有在“計算機(jī)”學(xué)生名單：SELECTsno,sname,sdeptFROMstudentWHEREsdept=計算機(jī)結(jié)果如表10-12所示。把前面兩步合并在一起的查詢?yōu)椋篠ELECTsno,sname,sdeptFROMstudentWHEREsdeptIN(SELECTsdeptFROMstudentWHEREsname=陳路)本例的IN可以用“=”代替。2.帶有比較運(yùn)算符的子查詢【例9】查詢超出課程平均成績的學(xué)生號和課程號。SELECTsno,cnoFROMSCxWHEREscore>=(SELECTavg(score)FROMSCyWHEREy.sno=x.sno)3.帶有ANY、SOME或ALL的子查詢【例10】查詢其他系中比計算機(jī)系所有學(xué)生年齡都要小的學(xué)生姓名和年齡。SELECTsname,sageFROMstudentWHEREsage<ALL(SELECTsageFROMstudentWHEREsdept=計算機(jī))ANDsdept<>計算機(jī)運(yùn)用上述的表101，可以查詢出兩位“張?jiān)隆焙汀皠⑾ｅ钡姆蠗l件，她們的年齡都比計算機(jī)系所有學(xué)生的年齡都要小。用聚合函數(shù)改寫為：SELECTsname,sageFROMstudentWHEREsage<ALL(SELECTMIN(sage)FROMstudentWHEREsdept=計算機(jī))ANDsdept<>計算機(jī)4.帶EXISTS的子查詢不需要返回具體的查詢數(shù)據(jù)，而只關(guān)心是否有返回值，即返回True或False?！纠?1】運(yùn)用表10-1和10-2，查詢沒有選修1號課程的學(xué)生姓名。SELECTsnameFROMstudentWHERENOTEXISTS(SELECT*FROMscWHEREsno=student.snoANDcno=1)任務(wù)十一、組合查詢在SQLServer2005中，將多個查詢的結(jié)果放在一起，以一個查詢結(jié)果的形式顯示出來，則可以使用UNION關(guān)鍵字把多個SELECT連接起來。每個SELECT查詢語句應(yīng)當(dāng)有相同數(shù)量的字段，若字段個數(shù)不等，可以使用NULL來代替；每個查詢語句中相應(yīng)的字段的類型必須相互兼容，若不兼容，可使用類型轉(zhuǎn)換函數(shù)強(qiáng)制轉(zhuǎn)換字段類型。UNION缺省在合并結(jié)果集后消除重復(fù)項(xiàng)，UNIONALL指定在合并結(jié)果集后保留重復(fù)項(xiàng)。UNION結(jié)果集中的列名總是等于UNION中第一個SELECT語句中的列名?！纠?】網(wǎng)絡(luò)售書情況表InternetInfo如表11-1所示，實(shí)體店面售書表StoreInfo如表11-2所示，現(xiàn)在需要查詢所有售書的訂單日期。SELECTOrderDateFROMInternetInfoUNIONSELECTOrderDateFROMStoreInfo操作結(jié)果如表11-3所示。SELECTOrderDateFROMInternetInfoUNIONALLSELECTOrderDateFROMStoreInfo操作結(jié)果如表11-4所示。任務(wù)十二、插入、更新在SQLServer2005中，有時為了方便，需要將查詢結(jié)果保存起來。在SELECT子句的后面，F(xiàn)ROM子句的前面加上了一個INTO關(guān)鍵字，關(guān)鍵字的后面緊跟用于保存查詢結(jié)果的新表的名字。SELECT*(或字段列表)INTO新表名FROM表名一、INSERT語句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ù)的具體值。列名的排列順序不一定要和表定義時的順序一致。但當(dāng)指定列名表時，VALUES子句值的排列順序必須和列名表中的列名排列順序一致，個數(shù)相等，數(shù)據(jù)類型一一對應(yīng)。在進(jìn)行數(shù)據(jù)插入操作時須注意以下2點(diǎn)：（1）在VALUES子句中，必須用逗號將各個數(shù)據(jù)分開，字符型數(shù)據(jù)要用單引號括起來。（2）有時并不需要向表中插入完整的行，而需要將數(shù)據(jù)只插入到幾個指定的字段內(nèi)，則在表名后加上字段列表，且VALUES子句中值的排列順序要和表中各屬性