第六章數(shù)據(jù)查詢

SQL是關(guān)系數(shù)據(jù)庫標準語言,該語言是基于關(guān)系運算的,其中最常用的是SELECT語句，因為使用數(shù)據(jù)庫的最終目的是為了利用數(shù)據(jù)庫中的數(shù)據(jù)，而SELECT語句的功能就是從數(shù)據(jù)庫中檢索出符合用戶需求的數(shù)據(jù)，任何從數(shù)據(jù)庫中取得數(shù)據(jù)的操作最終都將體現(xiàn)為SELECT語句。本章主要講述了SELECT語句結(jié)構(gòu)和使用方法。6.1SQL語言的數(shù)據(jù)理論基礎(chǔ)

SQLServer是一個關(guān)系數(shù)據(jù)庫管理系統(tǒng)。關(guān)系數(shù)據(jù)庫建立在關(guān)系模型基礎(chǔ)之上，具有嚴格的數(shù)學理論基礎(chǔ)。關(guān)系數(shù)據(jù)庫對數(shù)據(jù)的操作除了包括集合代數(shù)的并、差、交等運算之外，還定義了一組專門的關(guān)系運算：選擇、投影和聯(lián)接。關(guān)系運算的特點是運算的對象和結(jié)果都是表。6.1SQL語言的數(shù)據(jù)理論基礎(chǔ)6.1.1選擇選擇(Selection)是單目運算，其運算對象是一個表。該運算按給定的條件對關(guān)系作水平分割，即選擇符合條件的行。條件可用命題公式F表示，F(xiàn)中的運算對象是常數(shù)(用引號括起來)或元組分量(屬性名或列的序號)，運算符有算術(shù)比較運算符(<，≤，>，≥，≠)和邏輯運算符(∩，∪，！)。關(guān)系R關(guān)于公式F的選擇操作用σF(R)表示。形式定義如下:σF(R)＝{t|t∈R∧F(t)＝true}其中，F(xiàn)表示選擇條件，它是一個邏輯表達式，取邏輯值‘真’或‘假’。R是被操作的表。6.1SQL語言的數(shù)據(jù)理論基礎(chǔ)【例6-1】有一個學生基本情況表student如表6-1所示,若要在表中找出政治面貌是黨員的女同學的行并形成一個新表，則選擇運算表達式是：σ政治面貌=’黨員’∧性別=‘女’(student)運行結(jié)果如圖6-2所示.表6-1學生基本情況表表6-2選擇運算結(jié)果學號姓名出生日期性別政治面貌010548周長貴1983-11-10男黨員010389劉娜1985-11-14女黨員039484張穎1985-12-18女團員930032張博聞1984-2-22男黨員學號姓名出生日期性別政治面貌010389劉娜1985-11-14女黨員6.1SQL語言的數(shù)據(jù)理論基礎(chǔ)6.1.2投影

關(guān)系R上的投影是從R中選擇出若干屬性列組成新的關(guān)系。即對一個關(guān)系進行垂直分割，消去某些列，并重新安排列的順序，再刪去重復(fù)行。記作πA(R)＝{t[A]|t∈R}其中，A為R中的屬性列。投影操作是從列的角度進行的運算。投影之后不僅取消了原關(guān)系中的某些列，而且還可能取消某些元組，因為取消了某些屬性列后，就可能出現(xiàn)重復(fù)行，應(yīng)取消這些完全相同的行。6.1SQL語言的數(shù)據(jù)理論基礎(chǔ)【例6-2】利用投影運算查詢學生的學號和姓名。投影運算表達式為：Π學號,姓名(student)或Π1,2(S)6.1SQL語言的數(shù)據(jù)理論基礎(chǔ)【例6-3】查詢姓名為張穎的學生的姓名、性別、政治面貌?？梢酝ㄟ^兩個運算的組合，查詢出所需要的列和行,其表達式為：Π2,4,5(σ姓名=‘張穎’(student))結(jié)果如表6-4所示

表6-4選擇、投影組合運算結(jié)果姓名性別政治面貌張穎女團員6.1SQL語言的數(shù)據(jù)理論基礎(chǔ)6.1.3聯(lián)接

聯(lián)接也稱為θ聯(lián)接。它是從兩個關(guān)系的笛卡爾積中選取屬性間滿足一定條件的元組。記作：。。。。。其中A和B分別為R和S上度數(shù)相等且可比的屬性組。θ是比較運算符。聯(lián)接運算從R和S的笛卡爾積R×S中選?。≧關(guān)系）在A屬性組上的值與（S關(guān)系）在B屬性組上的值滿足比較關(guān)系θ的元組。θ為“＝”的聯(lián)接運算稱為等值聯(lián)接。它是從關(guān)系R與S的笛卡爾積中選取A、B屬性值相等的那些元組。即等值聯(lián)接為。。。。。6.1SQL語言的數(shù)據(jù)理論基礎(chǔ)

若A、B是相同的屬性組，就可以在結(jié)果中把重復(fù)的屬性去掉。這種去掉了重復(fù)屬性的等值聯(lián)接稱為自然聯(lián)接。自然聯(lián)接可記作R。。。。。

6.2SELECT語句結(jié)構(gòu)

數(shù)據(jù)查詢是從數(shù)據(jù)庫中獲得所需要的數(shù)據(jù)。查詢是數(shù)據(jù)庫的主要操作之一，也是SQL語言最主要的功能。由于用戶對數(shù)據(jù)庫查詢的要求多種多樣，因此SQL的查詢功能非常強大，但SQL只有一條SELECT查詢語句，該語句的語法很復(fù)雜，以滿足各種不同的需求。SELECT語句是SQL最基本和最重要的語句。用戶使用SELECT語句不但可以查看普通數(shù)據(jù)庫中的表格和視圖的信息，還可以查看SQLServer的系統(tǒng)信息。

6.2SELECT語句結(jié)構(gòu)

SELECT語句的語法格式如下:SELECTselect_list[INTOnew_table_name]FROMsource_table[WHEREsearch_condition1][GROUPBYexpression1][HAVINGsearch_condition2][ORDERBYexpression2[ASC|DESC]]其中：●Select_list：指明要查詢的選擇列表。列表可以包括若干個列名或表達式，列名或表達式之間用逗號隔開，用來指明應(yīng)該返回哪些數(shù)據(jù)。表達式可以是列名、函數(shù)或常數(shù)的列表。6.2SELECT語句結(jié)構(gòu)

●INTOnew_table_name：指定用查詢的結(jié)果創(chuàng)建成一個新表。●FROMsource_table：指定要查詢的表或視圖的名稱?！馱HEREsearch_condition：指明查詢所要滿足的條件?！馟ROUPBYexpressin1：根據(jù)指定列中的值對結(jié)果集進行分組，分組的條件是由expressin1指定?！馠AVINGsearch_conditin2：對用FROMWHERE或GROUPBY子句創(chuàng)建的中間結(jié)果集進行行的篩選。它通常與GROUPBY子句一起使用。●ORDERBYexpression2[ASC|DESC]：對查詢結(jié)果集中的行重新排序。ASC和DESC關(guān)鍵字分別用于指定按升序或降序排序。如果省略ASC或DESC，則系統(tǒng)默認為升序。6.3簡單查詢6.3.1使用SELECT子句SELECT子句的語法格式為:SELECT[ALL|DISTINCT][TOPn[PERCENT][WITHTIES]]<select_list>其中：●ALL|DISTINCT選項：指定查詢結(jié)果集的所有行，其中，ALL指明結(jié)果集中包括重復(fù)行，DISTINCT是用于刪除結(jié)果集中重復(fù)的行。ALL是該選項的默認設(shè)置；●TOPn[PERCENT]:指定只返回查詢結(jié)果集中的前n行。如果加了PERCENT，則表示只返回查詢結(jié)果集中的前n%行。6.3簡單查詢●WITHTIES:指定從基本結(jié)果集中返回附加的行?！駍electlist—指明要查詢的選擇列表。列表可以包括若干個列名或表達式，列名或表達式之間用逗號隔開，用來指示應(yīng)該返回哪些數(shù)據(jù)。如果使用星號*則表示返回FROM子句中指定的表或視圖中的所的列。表達式可以是列名、函數(shù)或常數(shù)的列表?！纠?-5】查詢學生所有的基本信息。由于學生的基本信息都保存在表student中，所以在查詢分析器執(zhí)行下列語句。usestudent_msgoselect*fromstudentgo6.3簡單查詢運行結(jié)果如圖所示，他將student表中的所有信息均顯示出來。6.3簡單查詢【例6-6】查詢學生的政治面貌情況由于只需要查看學生的政治面貌，而不需要性別、出生日期等其他信息，所以可以在查詢分析器中執(zhí)行下列語句：usestudent_msgoselect姓名,性別,政治面貌fromstudentgo6.3簡單查詢結(jié)果如下圖所示6.3簡單查詢

【例6-7】查詢前10%記錄中的學生的學號、姓名、出生日期情況在查詢分析器中執(zhí)行下列語句：usestudent_msgoselecttop10percent學號，姓名,出生日期fromstudentgo分析：表中原有記錄17條，其中的10%約有2條數(shù)據(jù)，所以使用top10percent語句可以顯示出前2條記錄。6.3簡單查詢

6.3.2使用INTO子句使用INTO子句可以創(chuàng)建一個新表，新表中所包含的內(nèi)容就是查詢的結(jié)果，通常使用這種方法可以創(chuàng)建一個臨時表，以便在以后的開發(fā)過程中使用，但是查詢的用戶必須在該數(shù)據(jù)庫中擁有CREATETABLE權(quán)限，而且INTO子句不能和COMPUTE子句一起使用?！纠?-8】將學生的學號、姓名、性別、出生日期的查詢結(jié)果作為一個新建的表student_info_1保存起來。在查詢分析器中運行下列語句：USEstudent_msGOSELECT學號，姓名,性別，出生日期INTOstudent_info_1FROMstudentGO6.3簡單查詢

由于該查詢建立了一個新表，這時，在企業(yè)管理器中stument_ms數(shù)據(jù)庫中增加了一個student_info_1，可以打開并看到表中的字段和記錄。6.3.3使用WHERE子句

WHERE子句是用于指明查詢所需要滿足的條件。一般情況下，在數(shù)據(jù)的查詢過程中必須定義一個或多個條件限制查詢選擇的數(shù)據(jù)行。該條件是由WHERE子句中的邏輯表達式所確定，結(jié)果集將返回表達式為真的數(shù)據(jù)行。其中邏輯表達式主要由比較運算符、邏輯運算符構(gòu)成。6.3簡單查詢【例6-9】在學生基本信息表student中查找政治面貌是“黨員”的學生的姓名、性別。在查詢分析器中運行下列命令語句：USEstudent_msGOSELECT姓名,性別FROMstudentWhere政治面貌='黨員'GO運行結(jié)果如下圖所示。6.3簡單查詢6.3簡單查詢【例6-10】在學生基本信息表中查詢1985年以后出生的女生的基本信息在查詢分析器中運行下列命令語句：USEstudent_msGOSELECT學號,姓名,出生日期FROMstudentWHERE性別='女'AND出生日期>1985-1-1G0運行結(jié)果如下圖所示6.3簡單查詢6.3簡單查詢6.3.4使用GROUPBY子句

GROUPBY子句用于對表或視圖中的數(shù)據(jù)按字段分組，語法格式為：GROUPBY[ALL|分組表達式[，…n][WITH{CUBE|ROLLUP}]]其中分組表達式通常包含字段名。指定ALL將顯示所有組。使用GROUPBY子句后，SELECT子句中的列表中只能包含在GROUPBY中指出的列或在聚合函數(shù)中指定的列。WITH指定CUBE或ROLLUP操作符，CUBE或ROLLUP與聚合函數(shù)一起使用，在查詢結(jié)果中增加附加記錄。6.3簡單查詢【例6-11】查詢每位同學的課程門數(shù)、總成績、平均成績。查詢每位同學的課程成績情況，實際上就是按照“學號”列進行分類統(tǒng)計，在GROUPBY子句中可以使用聚合函數(shù)COUNT（）、SUN（）、AVG（）分別統(tǒng)計課程的門數(shù)、總成績、平均成績。在查詢分析器中運行下列命令語句：SELECT學號,COUNT(課程號)課程門數(shù),SUM(成績)AS總成績,平均成績=AVG(成績)FROMscoreGROUPBY學號GO運行結(jié)果如圖所示。6.3簡單查詢6.3簡單查詢說明：在上例中使用了列的別名的三種定義方式，分別為：

●列名表達式列別名●列名表達式AS列別名●列別名=列名表達式6.3.5HAVING子句

使用GROUPBY子句和聚合函數(shù)對數(shù)據(jù)進行分組后，還可以使用HAVING子句對分組數(shù)據(jù)進一步的篩選，以限制SELECT語句返回的行數(shù)。HAVING子句應(yīng)該處在GROUPBY子句之后，并且不能在HAVING子句中使用text、image和ntext數(shù)據(jù)類型。

6.3簡單查詢語法格式為：HAVING<search_condition>其中：search_condition為條件表達式【例6-12】查詢參加“計算機基礎(chǔ)”課程（課程號為“01005”）考試的學生人數(shù)。在查詢分析器中運行下列命令語句：SELECT課程號,COUNT(課程號)人數(shù)FROMscoreGROUPBY課程號HAVING課程號='01005'Go運行結(jié)果下圖所示。6.3簡單查詢含有HAVING子句查詢結(jié)果

6.3簡單查詢6.3.6ORDERBY子句

在查詢過程中，如果需要對查詢結(jié)果集重新排序輸出，則可以使用ORDERBY子句，其語法格式為：ORDERBYcolum_nmae[ASC|DESC][，column_name[ASC|DESC][,…]]其中，ASC和DESC關(guān)鍵字分別用于指定按升序或降序排序。如果省略ASC或DESC，則系統(tǒng)默認為升序。可以在ORDERBY子句中指定多個排序列，即嵌套排序，檢索結(jié)果首先按第1列進行排序，對第1列值相同的那些數(shù)據(jù)行，再按照第2列排序……依此類推。注意在ORDERBY子句中不能使用ntext,text和image列。6.3簡單查詢【例6-13】查詢所有男生的學號、姓名和年齡，并按照年齡從小到大的順序進行排列。由于按照年齡從小到大的順序排列，所以，應(yīng)該按照出生日期的降序排序。在查詢分析器中運行下列命令語句：SELECT學號,姓名,YEAR(GETDATE())-YEAR(出生日期)年齡FROMstudentWHERE性別='男'ORDERBY出生日期DESCgo運行結(jié)果如圖所示6.3簡單查詢含有ORDERBY子句查詢結(jié)果

6.4聯(lián)接查詢

在設(shè)計關(guān)系數(shù)據(jù)庫管理系統(tǒng)時，為了保證數(shù)據(jù)的完整性，一致性，而且在建立數(shù)據(jù)表初期時各數(shù)據(jù)之間的關(guān)系不確定，因此，常把一個實體的所有信息存放在一個表中。而在實際查詢過程中，為了得到滿足要求的查詢結(jié)果，需要從多個表中選取不同的數(shù)據(jù)，這時，就要求有聯(lián)接查詢操作，從而可以查詢出存放在多個表中的不同實體的信息。聯(lián)接操作給用戶帶來很大的靈活性，通過聯(lián)接可以為不同實體創(chuàng)建新的數(shù)據(jù)表。聯(lián)接的基本語法格式如下:FROM<left_table>[<join_type>]JOIN<right>ON<join_condition>6.4聯(lián)接查詢

其中，join_type(聯(lián)接類型)有如下5種:1．INNER:內(nèi)聯(lián)接，這是默認類型。2．LEFT或LEFTOUTER左聯(lián)接。當左表中的記錄根據(jù)<聯(lián)接條件>在右表中沒有匹配的記錄時，仍在結(jié)果集中產(chǎn)生一條記錄，3．RIGHT或RIGHTOUTER右聯(lián)接。4．FULL或FULLOUTER完整聯(lián)接。5．CROSS交叉聯(lián)接。6.4.1內(nèi)聯(lián)接內(nèi)聯(lián)接(INNERJOIN)是用比較運算符比較表中列值，返回符合聯(lián)接條件的數(shù)據(jù)行，從而將兩個表聯(lián)接成一個新表。內(nèi)聯(lián)接通常有三種聯(lián)接方式。6.4聯(lián)接查詢

1.等值聯(lián)接

等值內(nèi)聯(lián)接在聯(lián)接條件中使用等號（=）運算符比較被聯(lián)接列的列值，因為聯(lián)接列值是相等的列，聯(lián)接的列要顯示兩次，所以會產(chǎn)生冗余行?！纠?-14】運用等值聯(lián)接方法，通過學號把student表和score表進行等值聯(lián)接。在查詢分析器中運行下列命令語句：SELECT*FROMstudentINNERJOINscoreONstudent.學號=score.學號Go6.4聯(lián)接查詢等值聯(lián)接查詢結(jié)果如圖6.4聯(lián)接查詢2．不等值聯(lián)接

在聯(lián)接條件中使用除等于運算符以外的其他比較運算符（>、>=、<、<=、!>、!<、<>）來比較被聯(lián)接的列的列值?！纠?-15】將學生表中的學號與成績表中的學號相比較,在兩個表聯(lián)接所產(chǎn)生的結(jié)果中選擇非團員學生的成績。在查詢分析器中運行下列命令語句：SELECT姓名,政治面貌,成績FROMstudentINNERJOINscoreONstudent.學號>score.學號WHERE政治面貌<>'團員'Go6.4聯(lián)接查詢不等值查詢結(jié)果如下：6.4聯(lián)接查詢3.自然聯(lián)接

自然聯(lián)接是將要聯(lián)接的列作相等比較的聯(lián)接，由于該聯(lián)接使用選擇列表指定查詢結(jié)果結(jié)合中所包含的列，并刪除聯(lián)接表中的重復(fù)列，從而消除了等值聯(lián)接產(chǎn)生的冗余?！纠?-16】查詢學生的學號、課程名稱和成績SELECT學號,課程名稱,成績FROMcourseINNERJOINscoreONcourse.課程號=score.課程號ORDERBY學號Go6.4聯(lián)接查詢自然聯(lián)接查詢結(jié)果如圖：6.4聯(lián)接查詢6.4.2外聯(lián)接

外聯(lián)接只限制一個表，而對另外一個表不加限制（所有的行都出現(xiàn)在結(jié)果集中）。外聯(lián)接分為左外聯(lián)接（LEFT）、右外聯(lián)接（RIGHT）和全外聯(lián)接（FULL）。1．左外聯(lián)接左外聯(lián)接對聯(lián)接條件中左邊的表不加限制?！纠?-17】查詢所有課程的成績信息。在查詢分析器中運行下列命令語句：SELECT學號,course.課程號,course.課程名稱,成績FROMcourseLEFTJOINscoreONcourse.課程號=score.課程號GO6.4聯(lián)接查詢

運行結(jié)果如圖所示，由于在course表中，專業(yè)英語、多媒體技術(shù)和軟件工程沒有學生成績，因此出現(xiàn)了null值。6.4聯(lián)接查詢

2．右外聯(lián)接

右外聯(lián)接對右邊的表不加限制?！纠?-18】查詢已輸入成績的課程的成績信息在查詢分析器中運行下列命令語句：SELECT學號,course.課程號,course.課程名稱,成績FROMcourseRIGHTJOINscoreONcourse.課程號=score.課程號GO運行結(jié)果如圖所示，從圖中可以看出，由于是不對score表不加限制，因此，結(jié)果中出現(xiàn)“網(wǎng)頁制作”這門課程的成績的兩個null值。6.4聯(lián)接查詢右外聯(lián)接查詢結(jié)果如圖：6.4聯(lián)接查詢3．全外聯(lián)接

全外聯(lián)接對兩個表都不加限制，兩個表中的所有行都包含在結(jié)果集中。【例6-19】查詢所有課程以及考試成績的信息在查詢分析器中運行下列命令語句：SELECT學號,course.課程號,course.課程名稱,成績FROMcourseFULLJOINscoreONcourse.課程號=score.課程號GO由于對兩個表都沒有限制，因此出現(xiàn)了四個null值。6.4聯(lián)接查詢

6.4.3交叉聯(lián)接

交叉聯(lián)接也叫非限制聯(lián)接，它將兩個表沒有任何約束限制地組合在一起，也就是對兩個表進行笛卡爾乘積。它不使用WHERE子句，它所產(chǎn)生的結(jié)果集包含了所聯(lián)接的兩個表中的所有行的組合，其數(shù)目為第一個表中符合聯(lián)接條件的行數(shù)與第二個表中符合聯(lián)接條件的行數(shù)的乘積?！纠?-20】查詢所有學生和課程信息在查詢分析器中運行下列命令語句：USEstudent_msSELECT*FROMstudentCROSSJOINcourseGO可以通過查詢結(jié)果窗口，計算查詢結(jié)果中的行數(shù)。6.4聯(lián)接查詢6.4.4嵌套查詢

嵌套查詢是在查詢中包含另一個查詢的查詢。在SQL語言中，一個完整的SELECT—FROM—WHERE語句被稱之為查詢塊，將一個查詢塊嵌套在另一個查詢塊的WHERE子句中，這就是嵌套查詢?！纠?-21】在score表中，查詢成績大于平均成績的成績信息。在查詢分析器中運行下列命令語句：SELECTAVG(成績)AS'平均成績'FROMscoreGOSELECT*FROMscoreWHERE成績>(SELECTAVG(成績)FROMscore)GO6.4聯(lián)接查詢嵌套查詢結(jié)果如圖：6.5使用其它關(guān)鍵字查詢數(shù)據(jù)

在檢索數(shù)據(jù)的過程中，需要使用WHERE子句限定查詢條件，這個條件通常是一個邏輯表達式。在表達式中除了可以使用比較運算符=、<、>、<>等外，還可使用范圍運算符BETWEEN,NOTBETWEEN,IN,LIKE,ISNULL以及邏輯運算符NOT(非)、OR(或)、AND(與)等來限定查詢條件。1.使用BETWEEN關(guān)健字BETWEEN關(guān)鍵字用來查詢在一個指定范圍內(nèi)的信息，與AND一起使用；NOTBETWEEN用于查詢不在某一范圍內(nèi)的信息。6.5使用其它關(guān)鍵字查詢數(shù)據(jù)【例6-22】從表score中查詢成績在75~90分之間的學生信息在查詢分析器中運行下列命令語句：SELECTstudent.學號,姓名,性別,課程名稱,成績F