數據庫教案 項目二 數據庫的查詢

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