項目五查詢數據庫182183任務1基本查詢任務2條件查詢任務3查詢結果排序任務4分組查詢任務5連接查詢任務6集合查詢任務7子查詢基本查詢任務11841.?能根據任務要求，使用SELECT語句進行數據庫查詢。2.?能使用關鍵字DISTINCT、AS和TOP子句進行數據庫查詢。185本任務要求根據導入數據庫ssts中的學生成績表stuScore.xls，完成以下查詢。1.查詢stuScore表中的所有記錄。2.查詢總成績排名前10的學生，其中，總成績=平時成績

×40%+考試成績

×60%，保留兩位小數。186一、SELECT語法查詢語句SELECT是從數據庫中檢索行，并允許從SQLServer中的一個或多個表中選擇一個或多個行或列。雖然SELECT語句的完整語法較為復雜，但其主要子句可歸納如下。187可在查詢之間使用UNION、EXCEPT和INTERSECT運算符，以便將各個查詢的結果合并到一個結果集中或進行比較。SELECT后面列出要查詢的字段名（列名），多個字段名之間要用英文逗號隔開。FROM后面一般為表名（table_name）

或視圖名(view_name)；WHERE后面為搜索條件表達式。GROUPBY表示分組；HAVING表示對結果過濾，通常與GROUPBY一起使用。ORDERBY表示對結果集排序時，ASC為升序，DESC為降序。188二、單列和多列查詢1.?單列查詢一般情況下，在數據庫中，每個表包含若干個列信息。如果只需要查詢表中的某一列數據時，可直接使用以下代碼。1892.?多列查詢多列查詢和單列查詢方法基本相同，只需要在多個字段名之間加上逗號分隔即可。3.?查詢所有的列若要查詢的表中有很多列，一一列舉比較麻煩，則可以使用“*”符號代表所有的列，語法格式如下。190三、使用DISTINCT去除結果的重復信息如果要去掉重復的查詢結果，要加上DISTINCT關鍵字。這時DISTINCT對所有的列都起作用，若列1內容相同，列2內容不同，則結果集中會出現列1相同而列2不同，也會出現列1不同而列2相同的記錄。191四、使用AS取別名用關鍵字AS可以命名新列，也可以給現有字段取別名。用AS重新指定返回字段或表的名字，稱為別名（aliasname）。1.?列的別名數據表中的列名有的是英文，為了方便查看查詢結果，可以使用別名來代替，以便增強可讀性；另外，在多個表查詢中，出現相同的列，通常也可以使用別名，其語法格式如下。1922.?表的別名在進行多表查詢時，為了方便查詢也經常給表取別名，其語法格式如下。此時，AS可以省略，可以在原表名后加上空格，再加上別名。1933.?結果集的別名結果集的別名也稱臨時表T，語法格式如下。1944.?別名的使用場合（1）常用在有兩個名字重復的表，需要為其中一個起一個別名，如自連接查詢。（2）當兩個表有重復的列名，也可以給表取別名，加以區分。（3）表名或列名較長時，可以為其取別名。（4）把一個查詢結果當作另一個表來查詢，可以理解查詢結果為一個臨時表T。195五、使用TOP查詢前若干行TOP子句用于規定要返回的記錄的數目，TOPnPERCENT則按照前n%返回記錄，語法格式如下。196條件查詢任務21971.?根據任務要求，能使用條件表達式進行查詢。2.?能描述在查詢中AND、OR、NOT、BETWEEN、IN、LIKE的作用。3.?能描述通配符的作用，并會使用通配符查詢數據，驗證查詢結果。198現要求在數據庫ssts中，使用學生成績表stuScore，完成以下查詢任務。1.查詢考試成績exam在95分及以上的所有記錄。2.根據公式“總成績total=平時成績normal×40%+考試成績exam×60%”，總成績保留兩位小數。查詢總成績在95分及以上學生的學號、姓名、平時成績、考試成績、總成績、系部。3.查詢平時成績和考試成績都不及格（小于60分）的學生的姓名、平時成績、考試成績。4.查詢信息工程系姓“李”的所有學生信息。199一、條件查詢用WHERE子句來說明查詢條件，緊跟在FROM子句的后面。WHERE后面的條件表達式包括由關系運算符、邏輯運算符、其他運算符構成的表達式。其他運算符主要有IN、NOTIN、BETWEEN、NOTBETWEEN、LIKE、NOTLIKE、ISNULL、ISNOTNULL、EXISTS、ANY、SOME。簡單的條件查詢可以在WHERE后加上一個搜索條件。200二、使用關系運算符表達式查詢在WHERE子句中，條件表達式中使用的關系運算符主要包括:=、<、>、<=、>=、<>、!=。三、使用邏輯表達式查詢在WHERE子句中，條件表達式中使用的邏輯運算符主要包括NOT、AND、OR。201四、使用BETWEEN限定數據查詢范圍在WHERE子句中，使用BETWEEN可以限制查詢數據的范圍。五、使用IN限定檢索數據查詢范圍IN操作符允許在WHERE子句中規定多個值，其語法格式如下。202六、使用通配符模糊查詢在搜索數據庫中的數據時，通配符可以替代一個或多個字符。1.?通配符“*”在SELECT語句中，可以使用通配符“*”來顯示所有字段，SELECT與*之間要有空格。2.?查詢常用的通配符查詢常用的通配符見下表，其中下劃線符號“_”是在英文狀態下，按Shift鍵和減號鍵（數字0右側的鍵）得到的。203在SQL?Server中nchar、nvarchar使用的是Unicode字符集。

而char、varchar使用的是ASCII字符集。當Unicode數據（nchar或nvarchar）與like?一起使用時，尾隨空格有意義。但對非Unicode數據，尾隨空格無意義。而ASCII中char、varchar字符串后面的空格沒有意義。204查詢常用的通配符查詢結果排序任務32051.?根據任務要求，查詢時能使用ORDER?BY進行排序。2.?能敘述多個字段排序時應遵循的順序關系。206本任務要求使用ORDERBY完成以下任務。1.在數據庫ssts的book表中，按照price價格從大到小排序。2.在數據庫ssts的教師表teacher中，查詢工齡最長的前5名員工的工號、姓名、工齡。3.在數據庫ssts中，查詢數據表competition。刪除ID不同，其他字段的值都相同的冗余信息。4.將數據表competition重命名為newCompetition，查詢Name中帶有“林”字的所有學生的信息。207一、排序ORDERBY在SQLServer中，為了方便查看查詢結果，按某種規律排序，可使用ORDERBY子句進行數據排序，還可以進行多字段排序。默認的排序方式是升序ASC，如果要降序排列，可在列名后加上DESC。ORDERBY語法格式如下。對查詢結果集按“列或表達式1”排序，再按“列或表達式2”排序，以此類推。208二、排序原則1.?對于數值型數據，按其數值大小進行比較。2.?對于日期型數據，按年月日的數值大小進行比較。3.?對于邏輯型數據，false小于true。4.?對于中英文字符，按其ASCII碼大小進行比較。5.?對于NULL值，若按升序排列，則含NULL的記錄將最先顯示。6.?不能按ntext、text、image類型排序。209分組查詢任務4

2101.?能在學習常用聚合函數的基礎上，使用GROUP?BY進行分組查詢。2.?能使用HAVING對分組查詢結果進行篩選。211本任務要求使用分組查詢，完成以下查詢任務。1.在數據庫ssts的book表中，按書的種類分類，求出各種類型type書籍的價格總和、平均價格及各類書籍的數量。2.在數據庫ssts的book表中，查詢所有價格超過35元的書籍的種類和平均價格。3.在數據庫ssts的book表中，在所有價格超過35元的書籍中，查詢所有平均價格超過40元的書籍的種類和平均價格。212一、聚合函數聚合函數對一組值執行計算并返回單一的值，常用的聚合函數見下表。213常用的聚合函數二、分組GROUPBYGROUPBY子句可以將查詢結果按屬性列或屬性列組合在行的方向上進行分組，每組在屬性列或屬性列組合上具有相同的聚合值。1.?使用GROUP?BY進行簡單分組使用GROUPBY子句對單個字段進行簡單分組，通常與聚合函數配合使用。【例】從學生表student中按照性別sex分組，統計男女生人數。214統計男女生人數的執行結果如圖所示。（2）反轉查詢結果當查詢的結果不是理想的行列顯示時，可以使用反轉查詢進行轉換。可以利用COUNT函數忽略NULL值的規則，借助于CASE…END表達式實現。215統計男女生人數的執行結果2.?使用HAVING子句設置統計條件GROUPBY子句還可以與WHERE子句配合使用，WHERE子句先于GROUPBY子句執行，將滿足條件的記錄保留下來，然后，再按照GROUPBY子句分成小組。若在分組后還要按照一定的條件進行篩選，則需使用HAVING子句。在SQL中增加HAVING子句的原因是，WHERE關鍵字無法與統計函數一起使用。如果只想要得到分組的統計結果，那么不需要全部的統計信息。216連接查詢任務52171.?能根據公式計算笛卡爾積。2.?能獨立對表做交叉連接查詢、內連接查詢和外連接查詢。218本任務要求在數據庫ssts中，使用連接查詢，完成以下3個查詢操作。1.根據teacher、teach、course三張表，查詢陳老師所任教的課程名稱。2.根據book表和author表，查詢出版了程序設計類的圖書作者，而且作者編號authorID是包含2022字符的，輸出作者的authorID、姓名authorName、所在城市city。3.根據author表，查找居住在“北京”的手機號碼前三位相同的作者。這里的teacher是教師表、teach是教學表、course是課程表，三表之間有主外鍵關系；author是作者表，book是圖書表，兩者之間也有主外鍵關系。219一、交叉連接交叉連接（crossjoin）又稱笛卡爾連接，是指兩個表之間做笛卡爾積操作，返回結果表的行數為兩個表行數的乘積。笛卡爾積：設A和B為集合，用A中元素為第一元素，B中元素為第二元素構成的有序對，所有這樣的有序對組成的集合叫做A與B的笛卡爾積，記作A×B。笛卡爾積的符號化表示為：A×B={(x,y)|x∈A,y∈B}。220二、內連接內連接（innerjoin）也稱為等同連接，返回的結果集是兩個表中所有相匹配的數據，而舍棄不匹配的數據。內連接使用比較運算符來完成，其可分為等值連接與非等值連接。連接條件的一般格式如下。[<表名1.>]<列名><比較運算符>[<表名2.>]<列名>其中，比較運算符主要包括=、>、<、>=、<=、！=。當比較運算符為“=”時，稱為等值連接，其他情況稱為非等值連接。若定義了表的別名，則在T-SQL語句中必須用別名代替表名。221三、外連接內連接（INNERJOIN）操作只輸出滿足連接條件的元組；外連接（OUTERJOIN）操作以指定表為連接主體，將主體表中不滿足連接條件的元組一起輸出，可分為左外連接（LEFTJOIN）、右外連接(RIGHTJOIN)和全外連接(FULLJOIN)。1.?左外連接左外連接的語法格式如下。222左外連接的結果集包括LEFTOUTER子句或LEFTOUTERJOIN中指定的左表的所有行，而不僅僅是連接列所匹配的行。如果左表的某行在右表中沒有匹配行，那么在相關聯的結果集行中右表的所有選擇列表列均為空值。左連接是將左表中的所有記錄與右表中的每條記錄進行組合，結果集中除返回內部連接的記錄外，還在查詢結果中返回左表中不符合條件的記錄，并在右表的相應列上加上NULL，bit類型以0填充。2232.?右外連接右外連接的語法格式如下。右外連接RIGHTJOIN或RIGHTOUTERJOIN將返回右表的所有行。右外連接是將左表中的所有記錄與右表中的每條記錄進行組合，結果集中除返回內部連接的記錄外，還在查詢結果中返回右表中不符合條件的記錄，并在左表的相應列上加上NULL，bit類型以0填充。2243.?全外連接全外連接的語法格式如下。全外連接FULLJOIN是將左表中的所有記錄與右表中的每條記錄進行組合，結果集中除返回內部連接的記錄外，還在查詢結果中返回兩個表中不符合條件的記錄，并在左表或右表的相應列上加上NULL，bit類型以0填充。225集合查詢任務62261.?能使用UNION聯合查詢，將多個查詢結果合并為一個結果集。2.?能敘述UNION?ALL關鍵字的用法，用于合并查詢結果時保留所有的行，包括重復行。3.?能敘述UNION、EXCEPT、INTERSECT關鍵字的用法。4.?能使用SELECT?INTO語句把聯合查詢的結果生成新表。227本任務要求使用數據庫ssts中的數據表classOne和classTwo進行組合查詢，具體查詢任務如下。1.查詢兩個班級總分高于320分的學生的學號、姓名和成績。2.查詢兩個班級網絡安全課程成績小于60分的學生名單。要求輸出學號、姓名和課程的成績。3.對于網絡安全課程成績不及格的學生，單獨生成一張新表，表名為“網絡安全補考名單”。228一、UNION聯合查詢在SQLServer中，將多個查詢的結果放在一起，以一個查詢結果的形式顯示出來，可以使用UNION關鍵字把多個SELECT連接起來。每個SELECT查詢語句應有相同數量的字段，若字段個數不等，可以使用NULL來代替；每個查詢語句中相應的字段的類型必須相互兼容，若不兼容，可使用類型轉換函數強制轉換字段類型。UNION缺省在合并結果集后消除重復項，UNIONALL指定在合并結果集后保留重復項。UNION結果集中的列名總是等于UNION中第一個SELECT語句中的列名。229二、INTERSECT交集查詢UNION將查詢結果合并到一個結果集中，而INTERSECT運算符從最終結果集中刪除重復的行，取兩個查詢的交集。三、EXCEPT集合差查詢EXCEPT用于從第一個查詢的結果中去除第二個查詢結果中也出現的行，即找出在第一個查詢結果中獨有的行，而不在第二個查詢結果中出現的行。230子查詢任務72311.?能使用單值子查詢，將單值子查詢轉化為連接查詢。2.?能使用帶有ANY、SOME或ALL子句的子查詢，并驗證查詢結果。3.?能使用帶有IN、EXISTS和FROM子句的子查詢，并驗證查詢結果。232本任務要求在數據庫ssts中完成以下3個查詢任務。1.根據課程表course，查詢cno號碼是c01、c02或c03的所有信息。2.根據學生表student，查詢dept不是信息工程系，也不是創意服務系的所有的學生信息。3.根據newCompetition表的出生日期Date，查詢年齡最小的學生，數據表NewCompetition如圖所示。233234數據表NewCompetition一、子查詢子查詢也稱內部查詢或嵌套查詢，是指將一個SELECT查詢（子查詢）的結果作為另一個T-SQL語句（主查詢）的數據來源或判斷條件。當一個查詢構成另一個查詢的條件時，這個查詢稱為子查詢。子查詢是一個嵌套在SELECT、INSERT、DELETE語句或其他子查詢中的查詢。任何允許使用表達式的地方都可以使用子查詢。子查詢可以從任何表中提取數據，只要對該表有適當的訪問權限即可。通過子查詢可以在主查詢的條件中使用子查詢的結果，以便根據特定的需求進行過濾和排序。235一般而言，子查詢的組件包括選擇列表組件的常規SELECT子句、一個表或多個表或視圖名稱的常規FROM子句、可選的WHERE子句、可選的GROUPBY子句、可選的HAVING子句等。子查詢在上一級查詢處理之前求解，子查詢的結果用于建立父查詢的查找條件。1.?單值子查詢單值子查詢可以由一個比較運算符引入，由比較運算符引入的子查詢必須返回單個值而不是值列表。2362.?帶有比較運算符的子查詢【例】根據數據庫ssts的選課表sc，查詢超出課程平均成績學生的學號和課程號。一個SELECTFROMWHERE語句稱為一個查詢塊，將一個查詢塊嵌套在另一個查詢塊的WHERE子句或HAVING子句的條件中的查詢稱為嵌套查詢。237二、帶有ANY、SOME或ALL的子查詢1.?使用ALL返回一組值的普通子查詢ALL運算符用于比較子查詢返回列表中的每個值。“<ALL”表示小于最小的；“>ALL”表示大于最大的；“=ALL”表示沒有返回值，因為在等于子查詢的情況下，返回列表中的所有值是不符合邏輯的。2.?使用ANY返回一組值的普通子查詢ANY運算符用于比較子查詢返回列表中的某個值。“<ANY”表示小于最大的；“>ANY”表示大于最小的；“=ANY”表示等于IN。2383.?使用SOME返回一組值的普通子查詢SOME運算符與ANY運算符是同義的，它們都允許比較運算符前面的單值與后面的子查詢返回值集合中的某個值進行比較。如果比較運算符前面的單值與比較運算符后面的子查詢返回值集合中的某個值之間任何比較結果為TRUE，那么比較表達式求值的結果為TRUE。239三、帶有IN子查詢通過IN(或NOTIN)運算符