單元4查詢數(shù)據(jù)表中的數(shù)據(jù)

1、單元單元4 查詢數(shù)據(jù)表中的數(shù)據(jù)查詢數(shù)據(jù)表中的數(shù)據(jù)SQL Server 2008數(shù)據(jù)庫技術(shù)及應(yīng)用數(shù)據(jù)庫技術(shù)及應(yīng)用 單元單元4 教學(xué)目標(biāo)教學(xué)目標(biāo)SQL Server 2008數(shù)據(jù)庫技術(shù)及應(yīng)用數(shù)據(jù)庫技術(shù)及應(yīng)用單元單元4 在本書中位置在本書中位置單元單元4 結(jié)構(gòu)圖結(jié)構(gòu)圖單元單元4 查詢數(shù)據(jù)表中的數(shù)據(jù)查詢數(shù)據(jù)表中的數(shù)據(jù)學(xué)習(xí)型工作任務(wù)學(xué)習(xí)型工作任務(wù)【任務(wù)任務(wù)4.1 】創(chuàng)建基本查詢創(chuàng)建基本查詢【任務(wù)任務(wù)4.2 】創(chuàng)建連接查詢創(chuàng)建連接查詢【任務(wù)任務(wù)4.3 】創(chuàng)建子查詢創(chuàng)建子查詢【任務(wù)任務(wù)4.4 】創(chuàng)建聯(lián)合查詢創(chuàng)建聯(lián)合查詢知識(shí)知識(shí)鏈接鏈接【L1.2.1】Select 語句的語句的語法格式語法格式 1. SELE

2、CT 語句的語法格式2. SELECT語句說明3. SELECT語句的執(zhí)行過程4. 使用使用SELECT語句的注意語句的注意事項(xiàng)事項(xiàng)單元訓(xùn)練單元訓(xùn)練單元訓(xùn)練單元訓(xùn)練SELECT子句中謂詞的使用方法WHERE子句的使用方法分組查詢GROUP BY、HAVING子句的使用方法集合函數(shù)的使用方法多表連接查詢的使用方法靈活使用SELECT子句進(jìn)行數(shù)據(jù)處理單元單元1 知識(shí)結(jié)構(gòu)框架圖知識(shí)結(jié)構(gòu)框架圖 多表查詢學(xué)習(xí)型工作任務(wù)學(xué)習(xí)型工作任務(wù)查詢數(shù)據(jù)表中數(shù)據(jù)4.1 創(chuàng)建基本查詢4.2 創(chuàng)建連接查詢4.3 創(chuàng)建子查詢4.4 創(chuàng)建聯(lián)合查詢 多多表表查查詢?cè)儐伪韱伪聿樵儾樵?.1 創(chuàng)建基本查詢創(chuàng)建基本查詢-單表查詢單表

3、查詢【任務(wù)4.1.1】SELECT 子句查詢【任務(wù)4.1.2 】WHERE 子句查詢【任務(wù)4.1.3 】使用聚合函數(shù)查詢【任務(wù)4.1.4 】使用GROUP BY子句分組統(tǒng)計(jì)查詢【任務(wù)4.1.5 】使用HAVING子句限定分組統(tǒng)計(jì)查詢【任務(wù)4.1.6 】使用ORDER BY子句排序【任務(wù)4.1.7 】使用COMPUTE BY子句匯總查詢【任務(wù)4.1.8 】使用INTO子句保存查詢【操作準(zhǔn)備】【操作準(zhǔn)備】uSELECT語句的語法格式及功能；語句的語法格式及功能；學(xué)生成績管理系統(tǒng)數(shù)據(jù)庫學(xué)生成績管理系統(tǒng)數(shù)據(jù)庫StudentScore_DBu根據(jù)成績管理根據(jù)成績管理E-R圖，了解成績管理數(shù)據(jù)庫中表之間的

4、關(guān)系。圖，了解成績管理數(shù)據(jù)庫中表之間的關(guān)系。圖圖4-4 成績管理數(shù)據(jù)庫關(guān)系圖成績管理數(shù)據(jù)庫關(guān)系圖【任務(wù)【任務(wù)4.1.1】SELECT 子句查詢子句查詢（1）利用SQL 編輯器查詢“班級(jí)表”中所有信息。（2）查詢班級(jí)表ClassInfo中班級(jí)號(hào)ClassID，班級(jí)名ClassNamer的信息。（3）查詢班級(jí)表ClassInfo中ClassID和ClassName，為便于用戶閱讀，列名顯示為中文“班級(jí)號(hào)”和“班級(jí)名”。（4）查詢成績表ScorInfo中學(xué)號(hào)StudID,課程號(hào)CourseID,成績Score及成績Score加5分后結(jié)果。（5）查詢學(xué)生表StudentInfo中前5條記錄。（6）查詢

5、學(xué)生表中班級(jí)編號(hào)ClassID，要求相同的班級(jí)編號(hào)只顯示一次。【任務(wù)描述任務(wù)描述】【任務(wù)【任務(wù)4.1.1】SELECT 子句查詢子句查詢1.啟動(dòng)SSMS，連接數(shù)據(jù)庫服務(wù)器，打開SQL編輯器。打開【對(duì)象資源管理器】窗口，單擊工具欄中按鈕，打開【SQL 編輯器】。2. 設(shè)置當(dāng)前數(shù)據(jù)庫為StudentSocre_DB在【SQL編輯器】工具欄中的數(shù)據(jù)庫下拉列表中選擇“StudentScore_DB”為當(dāng)前數(shù)據(jù)庫，或使用“use StudScore_DB”命令打開數(shù)據(jù)庫。如圖4-5所示。3. 根據(jù)任務(wù)描述，依次輸入SELECT 語句，并執(zhí)行。單擊【SQL編輯器】工具欄中的【執(zhí)行】按鈕 或者選擇菜單命令【

6、查詢】【執(zhí)行】或者直接按F5鍵，在當(dāng)前數(shù)據(jù)庫中執(zhí)行SQL查詢語句?！救蝿?wù)實(shí)施任務(wù)實(shí)施】【任務(wù)【任務(wù)4.1.1】SELECT 子句查詢子句查詢（1）使用（*）查詢表的所有列。使用*表示選取表的全部列，from 子名表示要查詢的表?！救蝿?wù)4-1】 利用SQL 編輯器查詢“班級(jí)表”中所有信息。select * from classInfo（2）查詢指定的列【任務(wù)4-2】查詢班級(jí)表ClassInfo中班級(jí)號(hào)ClassID，班級(jí)名ClassNamer的信息。Select classID,className from ClassInfo（3）改變查詢結(jié)果的列標(biāo)題。格式1：指定的列標(biāo)題=列名表達(dá)式格式2：列

7、名 as 指定的列標(biāo)題?！救蝿?wù)實(shí)施任務(wù)實(shí)施】【任務(wù)【任務(wù)4.1.1】SELECT 子句查詢子句查詢【任務(wù)4-3】查詢班級(jí)表ClassInfo中ClassID和ClassName，為便于用戶閱讀，列名顯示為中文“班級(jí)號(hào)”和“班級(jí)名”。Select classID 班級(jí)號(hào),className 班級(jí)名 from ClassInfo也可寫為：Select 班級(jí)號(hào)=ClassID,班級(jí)名=ClassName from ClassInfo（4）查詢時(shí)除使用字段名稱外，還可使用常量、函數(shù)或它們的組合。【任務(wù)4-4】查詢成績表ScoreInfo中學(xué)號(hào)StudID,課程號(hào)CourseID,成績Score及成績S

8、core加5分后結(jié)果。查詢結(jié)果如圖4-8所示。Select studID 學(xué)號(hào),courseID 課程號(hào),Score 成績,Score+5 加分后 from ScoreInfo（5）使用top關(guān)鍵字限制返回的行數(shù)。格式：top n Percent如果未指定關(guān)鍵字percent，則返回查詢結(jié)果集的前n條記錄。如果指定了關(guān)鍵字percent，n返回結(jié)果集行的百分?jǐn)?shù)?！救蝿?wù)4-5】查詢學(xué)生表StudentInfo中前5條記錄。Select top 5 * from StudentInfo【任務(wù)實(shí)施任務(wù)實(shí)施】【任務(wù)【任務(wù)4.1.1】SELECT 子句查詢子句查詢（6）使用 distinct 關(guān)鍵字可以

9、消除查詢結(jié)果中重復(fù)的數(shù)據(jù)行?！救蝿?wù)4-6】查詢學(xué)生表中班級(jí)編號(hào)ClassID，要求相同的班級(jí)編號(hào)只顯示一次。select distinct ClassID from studentinfo4. 保存SQL語句。單擊工具欄中的【保存】按鈕 或者選擇菜單命令【文件】【保存】，打開【另存文件為】對(duì)話框，在該對(duì)話框中定位到保存SQL語句的文件夾，輸入文件名“sele_class.sql”，然后單擊【保存】按鈕即可?！救蝿?wù)實(shí)施任務(wù)實(shí)施】【任務(wù)【任務(wù)4.1.2 】WHERE 子句查詢子句查詢格式 ：SELECT ALL|DISTINCT|TOP n| 表達(dá)式1，表達(dá)式2，。表達(dá)式n FROM 表名 WHE

10、RE 。WHERE 子句稱為選擇操作，其作用是查詢符合WHERE條件的記錄。此外，WHERE子句還用在DELETE和UPDATE語句中，定義表中被刪除和修改的行。T-SQL 語言通常用邏輯表達(dá)式描述條件。當(dāng)查詢的表中記錄使得WHERE子句邏輯表達(dá)式為真時(shí)，才向結(jié)果集提供數(shù)據(jù)。 邏輯表達(dá)式中可以使用比較運(yùn)算符（=、 =、=）、邏輯運(yùn)算符（and、or、not）、范圍運(yùn)算符(betweenand)、模糊匹配(Like、not Like )、空值(IS NULL、IS NOT NULL)等?！竟δ苊枋龉δ苊枋觥俊救蝿?wù)【任務(wù)4.1.2 】WHERE 子句查詢子句查詢（7）利用SQL 編輯器查詢學(xué)生表S

11、tudInfo中女同學(xué)的信息。（8）查詢1993年1月1日以前出生的女同學(xué)的信息。（9）查詢成績表中成績?cè)?0至85之間信息。（10）查詢名子中有“麗”的同學(xué)的信息。（11）查詢姓“張”，名子中僅有一個(gè)字的，備注為空的學(xué)生信息。（12）查詢學(xué)號(hào)為“kj1331001、wl1331003、wl1332008”的學(xué)生信息。【任務(wù)描述任務(wù)描述】【任務(wù)【任務(wù)4.1.2 】WHERE 子句查詢子句查詢（1）關(guān)系運(yùn)算：）關(guān)系運(yùn)算：關(guān)系運(yùn)算符： （等于）、（不等于）、（大于）、=（大于等于）、=（小于等于） 關(guān)系表達(dá)式：常量、列名（變量）、函數(shù)、關(guān)系運(yùn)算符?！救蝿?wù)4-7】利用SQL 編輯器查詢學(xué)生表Stud

12、Info中女同學(xué)的信息。在查詢分析器中運(yùn)行如下命令：Select * from StudentInfo where studsex=女（2）邏輯運(yùn)算符：）邏輯運(yùn)算符： NOT（非）、AND（與）、OR（或）邏輯表達(dá)式：常量、列名（變量）、函數(shù)、邏輯運(yùn)算符?！救蝿?wù)4-8】查詢1993年1月1日以前出生的女同學(xué)的信息。在查詢分析器中運(yùn)行如下命令：Select * from StudentInfo where StudBirthday=開始值 AND 列名=結(jié)束值）NOT BETWEEN 開始值 AND 結(jié)束值 。等價(jià)于（列名結(jié)束值）【任務(wù)4-9】查詢成績表中成績?cè)?0至85之間信息在查詢分析器中運(yùn)

13、行如下命令：Select * from ScoreInfo where Score between 70 and 85（4）模式匹配運(yùn)算符）模式匹配運(yùn)算符模糊查詢就是查找數(shù)據(jù)表中與用戶輸入關(guān)鍵字相近或相似的記錄信息。模糊匹配通常與通配符一起使用，且必須將字符串和通配符都用單引號(hào)引起來。語法：列名 NOT LIKE字符串（含通配符）通配符“_”代表一個(gè)任意字符，通配符“%”代表任意多個(gè)字符。模式匹配運(yùn)算符LIKE可以實(shí)現(xiàn)對(duì)表的模糊查詢。 【任務(wù)4-10】查詢名子中有“麗”的同學(xué)的信息。Select * from StudentInfo where StudName like %麗%【任務(wù)實(shí)施任務(wù)

14、實(shí)施】【任務(wù)【任務(wù)4.1.2 】WHERE 子句查詢子句查詢（5）空值查詢）空值查詢空值判斷查詢條件常用來查詢某一字段為空值的記錄，使用 IS NULL 或 IS NOT NULL指定查詢條件。NULL值表示字段的數(shù)據(jù)值未知或不可用，它并用表示零、零長度的字符串或空白字符串。格式：列名 IS NOT NULL【任務(wù)4-11】查詢姓“張”，名子中僅有一個(gè)字的，備注為空的學(xué)生信息。Select * from studentInfo where studName like 張_ and remarks is null（6）IN關(guān)鍵字關(guān)鍵字包含列表查詢條件的查詢將返回所有與列表中的任意一個(gè)值匹配的記錄

15、，通常用IN關(guān)鍵字來指定查詢條件。IN關(guān)鍵字格式：IN（列表值1，列表值2，）【任務(wù)4-12】查詢學(xué)號(hào)為“kj1331001、wl1331003、wl1332008”的學(xué)生信息Select * from studentinfo where StudID in(kj1331001,wl1331001,wl1332008)【任務(wù)實(shí)施任務(wù)實(shí)施】【任務(wù)【任務(wù)4.1.3 】使用聚合函數(shù)查詢】使用聚合函數(shù)查詢聚合函數(shù)功能COUNT ( ALL|DISTINCT expression |* )計(jì)算符合條件的記錄數(shù)，COUNT(*)返回記錄數(shù)，包括含有空值的記錄，不能與DISTINCT一起使用SUM( ALL

16、|DISTINCT expression )計(jì)算一組數(shù)據(jù)的和AVG( ALL|DISTINCT expression )計(jì)算一組數(shù)據(jù)的平均值MAX ( ALL|DISTINCT expression )計(jì)算一組數(shù)據(jù)的最大值MIN( ALL|DISTINCT expression )計(jì)算一組數(shù)據(jù)的最小值聚合函數(shù)也稱統(tǒng)計(jì)函數(shù)，其對(duì)一組值進(jìn)行計(jì)算并返回一個(gè)數(shù)值。表4-1 SQL Server聚合函數(shù)及其功能ALL為默認(rèn)選項(xiàng)，指計(jì)算所有的值；DISTINCT則去掉重復(fù)值。表4-1 常用聚合函數(shù)?！救蝿?wù)【任務(wù)4.1.3 】使用聚合函數(shù)查詢】使用聚合函數(shù)查詢【任務(wù)描述】【任務(wù)描述】（13）從“成績表”中統(tǒng)

17、計(jì)成績?cè)凇?0至80（包含）”之間的記錄數(shù)。（14）從“成績表”中計(jì)算所有成績的平均值。（15）從“成績表”中計(jì)算所有成績的總分。（16）從“成績表”查詢所有成績的最高分、最低分。（17） 查詢學(xué)號(hào)為“wl1133001”學(xué)生所選課程門數(shù)、最高分、最低分、總分、平均分，并用相應(yīng)的中文名稱表示相應(yīng)的值 。【任務(wù)【任務(wù)4.1.3 】使用聚合函數(shù)查詢】使用聚合函數(shù)查詢【任務(wù)4-13】從“成績表”中統(tǒng)計(jì)成績?cè)凇?0至80（包含）”之間的記錄數(shù)。在查詢分析器中運(yùn)行如下命令：select COUNT(*) from ScoreInfo where Score between 70 and 80【任務(wù)4-1

18、4】從“成績表”中計(jì)算所有成績的平均值。在查詢分析器中運(yùn)行如下命令：select AVG(score) from ScoreInfo【任務(wù)4-15】從“成績表”中計(jì)算所有成績的總分。在查詢分析器中運(yùn)行如下命令：select sum(score) from ScoreInfo【任務(wù)4-16】從“成績表”查詢所有成績的最高分、最低分。在查詢分析器中運(yùn)行如下命令：select max(score),MIN(score) from ScoreInfo【任務(wù)4-17】 查詢學(xué)號(hào)為“wl1133001”學(xué)生所選課程門數(shù)、最高分、最低分、總分、平均分，并用相應(yīng)的中文名稱表示相應(yīng)的值 。在查詢分析器中運(yùn)行如下

19、命令：運(yùn)行結(jié)果如圖4-10所示。select count(*) 課程門數(shù),max(score) 最高分 ,MIN(score) 最低分, sum(score) 總分, avg (score) 平均分 from ScoreInfo where StudID =wl1331001【任務(wù)【任務(wù)4.1.4 】使用】使用GROUP BY子句分組統(tǒng)計(jì)查詢子句分組統(tǒng)計(jì)查詢GROUP BY 與列名或聚合函數(shù)配合實(shí)現(xiàn)分組統(tǒng)計(jì)。聚合函數(shù)是對(duì)整個(gè)表中的數(shù)據(jù)進(jìn)行計(jì)算統(tǒng)計(jì)，但實(shí)際應(yīng)用中往往需要根據(jù)某列的值進(jìn)行分組統(tǒng)計(jì)與匯總，如從成績表中統(tǒng)計(jì)每位學(xué)生所選課程門數(shù)、總分等，統(tǒng)計(jì)時(shí)需要用“GROUP BY StudID”,即

20、按“學(xué)號(hào)”進(jìn)行分組統(tǒng)計(jì)，然后再計(jì)算機(jī)聚合函數(shù)的值。在SELECT 子句中投影的列，必須出現(xiàn)在GROUP BY 列名中。語法格式：GROUP BY 列名表【任務(wù)描述】【任務(wù)描述】（18）從“成績表”中查詢每位學(xué)生所選的課程門數(shù)、最高分、最低分、總分、平均分，并用相應(yīng)的中文名稱表示相應(yīng)的值。（19）從“成績表”中查詢選修每門課的學(xué)生人數(shù)、最高分、最低分、總分、平均分。并用相應(yīng)的中文名稱表示相應(yīng)的值，并用相應(yīng)的中文名稱表示相應(yīng)的值?！救蝿?wù)【任務(wù)4.1.4 】使用】使用GROUP BY子句分組統(tǒng)計(jì)查詢子句分組統(tǒng)計(jì)查詢【任務(wù)4-18】從“成績表”中查詢每位學(xué)生所選的課程門數(shù)、最高分、最低分、總分、平均分

21、，并用相應(yīng)的中文名稱表示相應(yīng)的值。在查詢分析器中運(yùn)行如下命令：select StudID , count(*) 課程門數(shù),max(score) 最高分 ,MIN(score) 最低分, sum(score) 總分, avg (score) 平均分 from ScoreInfo group by StudID【任務(wù)4-19】從“成績表”中查詢選修每門課程的學(xué)生人數(shù)、最高分、最低分、總分、平均分，并用相應(yīng)的中文名稱表示相應(yīng)的值。在查詢分析器中運(yùn)行如下命令：select CourseID , count(*) 課程門數(shù),max(score) 最高分 ,MIN(score) 最低分, sum(scor

22、e) 總分, avg (score) 平均分 from ScoreInfo group by CourseID 【任務(wù)【任務(wù)4.1.5 】使用】使用HAVING子句限定分組統(tǒng)計(jì)查詢子句限定分組統(tǒng)計(jì)查詢HAVING子名與GROUP BY參數(shù)配合篩選統(tǒng)計(jì)結(jié)果。HAVING子句的邏輯表達(dá)式通常包含聚合函數(shù)。注意聚合函數(shù)不能放在WHERE子句的邏輯表達(dá)式中。語法格式：HAVING邏輯表達(dá)式【任務(wù)描述】【任務(wù)描述】（20）從“成績表”中查詢選修課程門數(shù)大于2門的每位學(xué)生所選的課程門數(shù)、最高分、最低分、總分、平均分，并用相應(yīng)的中文名稱表示相應(yīng)的值select StudID , count(*) 課程門數(shù),

23、max(score) 最高分 ,MIN(score) 最低分, sum(score) 總分, avg (score) 平均分 from ScoreInfo group by StudID having COUNT(*)2SQL Server 2008 數(shù)據(jù)庫技術(shù)及應(yīng)用數(shù)據(jù)庫技術(shù)及應(yīng)用【任務(wù)【任務(wù)4.1.6 】使用】使用ORDER BY子句排序查詢子句排序查詢【任務(wù)【任務(wù)4-21】從】從“成績表成績表”中查詢信息，將結(jié)果按成績由高到低排序。中查詢信息，將結(jié)果按成績由高到低排序。 SELECT * FROM ScoreInfo ORDER BY Score DESC【任務(wù)描述任務(wù)描述】 按一列名多

24、列（最多按一列名多列（最多8060個(gè)字節(jié)）對(duì)查詢結(jié)果進(jìn)行升序（個(gè)字節(jié)）對(duì)查詢結(jié)果進(jìn)行升序（ASC，默認(rèn)）或降序，默認(rèn)）或降序(DESC)排序。如果排序。如果ORDER BY子句后是一個(gè)列名表達(dá)式表，則系統(tǒng)將根據(jù)各列的次子句后是一個(gè)列名表達(dá)式表，則系統(tǒng)將根據(jù)各列的次序決定排序的優(yōu)先級(jí)，然后排序。序決定排序的優(yōu)先級(jí)，然后排序。ORDER BY對(duì)數(shù)據(jù)類型為對(duì)數(shù)據(jù)類型為varchar(max)、nvarchar(max)、varbinary(max)或或xml的的 列不使用，并且只能在外查詢中使用。列不使用，并且只能在外查詢中使用。語法格式：語法格式：ORDER BY 列名表達(dá)式表列名表達(dá)式表 ASC

25、|DESC如果指定了如果指定了SELECT DISTINCT ，那么，那么ORDER BY 子句中的列名就必須出現(xiàn)在子句中的列名就必須出現(xiàn)在SELECT子句【功能介紹功能介紹】【任務(wù)【任務(wù)4.1.7 】使用】使用COMPUTE BY子句匯總查詢子句匯總查詢COMPUTE子句包括COMPUTE和COMPUTE BY兩種。COMPUTE BY子句使用戶可以用同一SELECT 語句既查看明細(xì)行，又查看分類匯總行；而COMPUTE子句使用戶可以用同一SELECT語句既可查看明細(xì)行，又查看總計(jì)行?？梢杂?jì)算機(jī)整個(gè)結(jié)果集匯總值，也可以計(jì)算子組的匯總值。COMPUTE子句需要下列信息：（1）可選的BY關(guān)鍵字，

26、該關(guān)鍵字可對(duì)一列計(jì)算指定的行統(tǒng)計(jì)；（2）行統(tǒng)計(jì)函數(shù)名稱；例如，SUM、AVG、MIN、MAX、COUNT；（3）對(duì)其執(zhí)行行統(tǒng)計(jì)函數(shù)的列。語法格式：（1）COMPUTE 聚合函數(shù)（數(shù)值型列名） （2）ORDER BY 分組字段名 COMPUTE 聚合函數(shù)（數(shù)值型列名） BY 分組字段名【功能描述功能描述】【任務(wù)【任務(wù)4.1.7 】使用】使用COMPUTE BY子句匯總查詢子句匯總查詢（22）從“成績表”中計(jì)算機(jī)結(jié)果集中成績的匯總值。 SELECT StudID,CourseID,Score FROM ScoreInfo COMPUTE SUM(Score)（23）按學(xué)號(hào)進(jìn)行分組，計(jì)算結(jié)果集中成績

27、的匯總值。SELECT StudID,CourseID,Score FROM ScoreInfo ORDER BY StudidCOMPUTE SUM(score),AVG(Score) BY StudID【任務(wù)實(shí)施任務(wù)實(shí)施】【任務(wù)【任務(wù)4.1.8 】使用】使用INTO子句保存查詢子句保存查詢INTO子句指定使用結(jié)果集創(chuàng)建新表。這種方式常用于創(chuàng)建表的副本。INTO 子名位于FROM子句前。語法格式：INTO 新表名【任務(wù)描述】【任務(wù)描述】（24）從“成績表”中查詢選修課程門數(shù)大于2門的每位學(xué)生所選的課程門數(shù)、最高分、最低分、總分、平均分，用相應(yīng)的中文名稱表示相應(yīng)的值，并將查詢結(jié)果保存至表Sco

28、re_gr_ha中。select StudID , count(*) 課程門數(shù),max(score) 最高分 ,MIN(score) 最低分, sum(score) 總分, avg (score) 平均分 into Score_gr_ha from ScoreInfo group by StudID having COUNT(*)2【任務(wù)描述任務(wù)描述】4.2創(chuàng)建連接查詢創(chuàng)建連接查詢【任務(wù)任務(wù)4.2.1】創(chuàng)建基本連接查詢創(chuàng)建基本連接查詢【任務(wù)任務(wù)4.2.2 】創(chuàng)建內(nèi)連接查詢創(chuàng)建內(nèi)連接查詢【任務(wù)任務(wù)4.2.3 】創(chuàng)建外連接查詢創(chuàng)建外連接查詢【任務(wù)任務(wù)4.2.4 】創(chuàng)建交叉連接查詢創(chuàng)建交叉連接查詢【

29、任務(wù)任務(wù)4.2.1】創(chuàng)建基本連接查詢創(chuàng)建基本連接查詢前面主要介紹了在一個(gè)數(shù)據(jù)表中查詢數(shù)據(jù)的方法。在實(shí)際應(yīng)用中，往往需要在多個(gè)數(shù)據(jù)表之間查詢，才能滿足用戶查詢需求。 實(shí)現(xiàn)從兩個(gè)或兩個(gè)以上數(shù)據(jù)表中查詢數(shù)據(jù)且結(jié)果集中出現(xiàn)的更來自于兩個(gè)或兩個(gè)以上數(shù)據(jù)表的檢索操作稱為連接查詢。其實(shí)質(zhì)是通過各個(gè)數(shù)據(jù)表之間的共同列的相關(guān)性來查詢數(shù)據(jù)，首先在數(shù)據(jù)表之間建立連接，然后再從中查詢數(shù)據(jù)。 連接的類型分為內(nèi)連接、外連接和交叉連接。其中外連接包括左外連接、右外連接和全外連接。 連接查詢根據(jù)語法分類分為：基本連接查詢和ANSI（American National Standards Institute，美國標(biāo)準(zhǔn)學(xué)會(huì)）連接

30、查詢兩種類型?！救蝿?wù)任務(wù)4.2.1】創(chuàng)建基本連接查詢創(chuàng)建基本連接查詢1. 基本連接查詢基本連接查詢是早期的SQL SERVER連接的語法形式。連接條件寫在WHERE子句的邏輯表達(dá)式中，實(shí)現(xiàn)連接。語法格式：語法格式：FROM ， WHERE 為書寫方便，也可給表1和表2起別名，因此上述格式還可寫為：FROM 別名1， 別名2 WHERE 連接操作符可以是：=、=、= 等，其中“=”表示等值連接，使用情況也最廣泛。對(duì)于Select 子句中，當(dāng)字段來源于多個(gè)數(shù)據(jù)表，且同名時(shí)，必須注明字段所在的數(shù)據(jù)表名稱，例如：表1.字段。【任務(wù)任務(wù)4.2.1】創(chuàng)建基本連接查詢創(chuàng)建基本連接查詢2. ANSI連接查詢連

31、接查詢ANSI連接查詢的語法形式，在FROM子句中使用JOINON關(guān)鍵字，連接條件寫在ON后面，從而實(shí)現(xiàn)連接。SQL SERVER 2008 R2 推薦使用ANSI連接查詢。語法格式：語法格式：FROM JOIN ON 連接類型包括：內(nèi)連接（INNER）、外連接（LEFT、RIGHT或FULL）、交叉連接（CROSS JOIN）。【任務(wù)任務(wù)4.2.1】創(chuàng)建基本連接查詢創(chuàng)建基本連接查詢交叉連接又稱笛卡爾積，返回的結(jié)果記錄數(shù)等于第1個(gè)數(shù)據(jù)表的記錄數(shù)乘以第2個(gè)表的記錄數(shù)。例如：學(xué)生信息表StudentInfo中有10條記錄，成績表ScoreInfo中有8條記錄，其結(jié)果有(10*8=80)條記錄，交叉

32、連接方式的關(guān)鍵字是：Cross Join，一般用于測(cè)試數(shù)據(jù)庫的執(zhí)行效率，實(shí)際應(yīng)用中使用較少。其語法格式： FROM 表1 CROSS JOIN 表2內(nèi)連接是用比較運(yùn)算符比較要連接列的值的連接，當(dāng)至少有一個(gè)同屬于兩個(gè)表的記錄符合連接條件時(shí)才返回記錄，其可消除與另一個(gè)表中的任何不匹配的記錄，連接條件通常采用“主鍵=外鍵”的形式。 【任務(wù)任務(wù)4.2.1】創(chuàng)建基本連接查詢創(chuàng)建基本連接查詢【任務(wù)描述任務(wù)描述】（25）在學(xué)生成績管理數(shù)據(jù)庫）在學(xué)生成績管理數(shù)據(jù)庫StudentScore_DB中，查詢學(xué)生的成績，要中，查詢學(xué)生的成績，要求有學(xué)生學(xué)號(hào)、姓名、所在班級(jí)號(hào)，課程號(hào)，成績等。求有學(xué)生學(xué)號(hào)、姓名、所在班

33、級(jí)號(hào)，課程號(hào)，成績等。（26）在學(xué)生成績管理數(shù)據(jù)庫）在學(xué)生成績管理數(shù)據(jù)庫StudentScore_DB中，查詢學(xué)生的成績，要中，查詢學(xué)生的成績，要求有學(xué)生學(xué)號(hào)、姓名、班級(jí)號(hào)、班級(jí)名稱，課程號(hào)，課程名、成績等。求有學(xué)生學(xué)號(hào)、姓名、班級(jí)號(hào)、班級(jí)名稱，課程號(hào)，課程名、成績等。（27）在學(xué)生成績管理數(shù)據(jù)庫）在學(xué)生成績管理數(shù)據(jù)庫StudentScore_DB中，查詢男同學(xué)的成績，中，查詢男同學(xué)的成績，要求有學(xué)生學(xué)號(hào)、姓名、性別、班級(jí)號(hào)、班級(jí)名稱，課程號(hào)，課程名、成要求有學(xué)生學(xué)號(hào)、姓名、性別、班級(jí)號(hào)、班級(jí)名稱，課程號(hào)，課程名、成績等。績等。分析任務(wù)25可以發(fā)現(xiàn)，其查詢涉及到兩個(gè)表：學(xué)生信息表(Studen

34、tInfo)、成績表（ScoreInfo），這兩個(gè)表通過學(xué)號(hào)(StudID)關(guān)聯(lián)，故其連接條件為：StudentInfo.StudID=ScoreInfo.StudID，該連接條件位于WHERE子句中，且對(duì)于兩個(gè)表中的共同字段要注明哪個(gè)表中的字段，如：StudentInfo.StudID。為書寫方便，SQL 支持使用別名，用于簡化。1兩個(gè)數(shù)據(jù)表之間的連接查詢兩個(gè)數(shù)據(jù)表之間的連接查詢【任務(wù)【任務(wù)4-25】在學(xué)生成績管理數(shù)據(jù)庫StudentScore_DB中，查詢學(xué)生的成績，要求有學(xué)生學(xué)號(hào)、姓名、所在班級(jí)號(hào)，課程號(hào)，成績等。在【SQL編輯器】中運(yùn)行如下命令：Select StudentInfo .

35、StudID ,StudName,ClassID,CourseID,Score From StudentInfo ,ScoreInfo Where StudentInfo .StudID =ScoreInfo .StudID 上面語句也可簡化為：Select S1.StudID ,StudName,ClassID,CourseID,ScoreFrom StudentInfo S1 ,ScoreInfo S2 Where S1.StudID =S2.StudID 【任務(wù)實(shí)施任務(wù)實(shí)施】【任務(wù)任務(wù)4.2.1】創(chuàng)建基本連接查詢創(chuàng)建基本連接查詢2多個(gè)數(shù)據(jù)表之間的連接查詢多個(gè)數(shù)據(jù)表之間的連接查詢【任務(wù)4-

36、26】在學(xué)生成績管理數(shù)據(jù)庫StudentScore_DB中，查詢學(xué)生的成績，要求有學(xué)生學(xué)號(hào)、姓名、所在班級(jí)名稱，課程號(hào)，課程名、成績等。分析任務(wù)26可以發(fā)現(xiàn)，其查詢涉及四個(gè)數(shù)據(jù)表：班級(jí)名、學(xué)生表、課程表和成績表的連接。連接條件為：班級(jí)表（ClassInfo）與學(xué)生表（StudentInfo）之間共同字段為班級(jí)號(hào)(ClassID)，課程表(CourseInfo)與成績表（ScoreInfo）共字段為課程號(hào)（CourseID），學(xué)生表（StudentInfo）與成績表（ScoreInfo）之間共同字段為學(xué)號(hào)（StudID），因此連接條件在WHERE子句中用AND 連接即可。若有其它篩選條件，在WH

37、ERE子句中使用AND連接，如任務(wù)27。語法格式：語法格式：FROM 表1，表2，表3 WHERE 表1.列名1=表2.列名1 AND 表2.列名2=表3.列名1Select S1.StudID ,S1.StudName,S1.StudSex ,S1.ClassID,C1.ClassName, C2.CourseID,C2.CourseName ,S2.Score From ClassInfo C1,StudentInfo S1 ,ScoreInfo S2,CourseInfo C2where S1.StudID =S2.StudID and C1.ClassID =S1.ClassID an

38、d C2.CourseID =S2.CourseID【任務(wù)實(shí)施任務(wù)實(shí)施】【任務(wù)任務(wù)4.2.1】創(chuàng)建基本連接查詢創(chuàng)建基本連接查詢【任務(wù)4-27】在學(xué)生成績管理數(shù)據(jù)庫StudentScore_DB中，查詢男同學(xué)的成績，要求有學(xué)生學(xué)號(hào)、姓名、性別、班級(jí)號(hào)、班級(jí)名稱，課程號(hào)，課程名、成績等。在【SQL編輯器】中執(zhí)行如下命令： Select s1.StudID ,s1.StudName,s1.StudSex ,s1.ClassID,c1.ClassName, c2.CourseID,c2.CourseName ,s2.Score From ClassInfo c1,StudentInfo s1 ,Sco

39、reInfo s2,CourseInfo c2 Where S1.StudID =S2.StudID and c1.ClassID =s1.ClassID and c2.CourseID =s2.CourseID and s1.StudSex =男【任務(wù)實(shí)施任務(wù)實(shí)施】【任務(wù)【任務(wù)4.2.2】創(chuàng)建內(nèi)連接查詢】創(chuàng)建內(nèi)連接查詢 內(nèi)連接是組合兩個(gè)表的常用方法。內(nèi)連接使用比較運(yùn)算符進(jìn)行多個(gè)源表之間數(shù)據(jù)的比較，并返回這些源表中與連接條件相匹配的數(shù)據(jù)行。使用JOIN或INNER JOIN關(guān)鍵字實(shí)現(xiàn)內(nèi)連接。其執(zhí)行連接查詢后，結(jié)果僅顯示匹配行的所有記錄，其它不匹配的記錄將被過濾掉。內(nèi)連接分為等值連接、非等值連接

40、和自然連接。在連接條件中使用“=”時(shí)，稱為等值連接。 連接字段相同，且在SELECT 語句中除去重復(fù)字段的等值連接稱為自然連接，其查詢結(jié)果與基本連接中使用“=”連接運(yùn)算符時(shí)相同。在連接條件中使用其它運(yùn)算符（如：、=、=、等）稱為非等值連接。 用比較運(yùn)算符比較要連接列的值的連接，當(dāng)至少有一個(gè)同屬于兩個(gè)表的記錄符合連接條件時(shí)才返回記錄，其可消除與另一個(gè)表中的任何不匹配的記錄，連接條件通常采用“主鍵=外鍵”的形式。 【任務(wù)【任務(wù)4.2.2】創(chuàng)建內(nèi)連接查詢】創(chuàng)建內(nèi)連接查詢兩個(gè)表內(nèi)連接時(shí)語法格式：兩個(gè)表內(nèi)連接時(shí)語法格式：FROM 表1 INNER JOIN 表2 ON 表1與表2 的連接表達(dá)式多表內(nèi)連接

41、時(shí)語法格式：多表內(nèi)連接時(shí)語法格式：FROM 表1 INNER JOIN 表2 ON 表1與表2的連接表達(dá)式 INNER JOIN 表3 ON 表2與表3的連接表達(dá)式 【任務(wù)【任務(wù)4.2.2】創(chuàng)建內(nèi)連接查詢】創(chuàng)建內(nèi)連接查詢（25）在學(xué)生成績管理數(shù)據(jù)庫StudentScore_DB中，查詢學(xué)生的成績，要求有學(xué)生學(xué)號(hào)、姓名、所在班級(jí)號(hào)，課程號(hào)，成績等。（26）在學(xué)生成績管理數(shù)據(jù)庫StudentScore_DB中，查詢學(xué)生的成績，要求有學(xué)生學(xué)號(hào)、姓名、班級(jí)號(hào)、班級(jí)名稱，課程號(hào)，課程名、成績等。（27）在學(xué)生成績管理數(shù)據(jù)庫StudentScore_DB中，查詢男同學(xué)的成績，要求有學(xué)生學(xué)號(hào)、姓名、性別、班

42、級(jí)號(hào)、班級(jí)名稱，課程號(hào)，課程名、成績等?！救蝿?wù)描述任務(wù)描述】【任務(wù)【任務(wù)4.2.2】創(chuàng)建內(nèi)連接查詢】創(chuàng)建內(nèi)連接查詢1兩個(gè)數(shù)據(jù)表之間的連接查詢兩個(gè)數(shù)據(jù)表之間的連接查詢【任務(wù)【任務(wù)4-25】在學(xué)生成績管理數(shù)據(jù)庫StudentScore_DB中，查詢學(xué)生的成績，要求有學(xué)生學(xué)號(hào)、姓名、所在班級(jí)號(hào)，課程號(hào)，成績等。Select S1.StudID ,StudName,ClassID,CourseID,ScoreFrom StudentInfo S1 INNER JOIN ScoreInfo S2 ON S1.StudID =S2.StudID【任務(wù)4-26】使用內(nèi)連接查詢時(shí)，命令如下： Select S

43、1.StudID ,S1.StudName,S1.StudSex ,S1.ClassID,C1.ClassName, C2.CourseID,C2.CourseName ,S2.Score From ClassInfo C1 INNER JOIN StudentInfo S1 ON C1.ClassID =S1.ClassID INNER JOIN ScoreInfo S2ON S1.StudID =S2.StudID INNER JOIN CourseInfo C2ON C2.CourseID =S2.CourseID【任務(wù)描述任務(wù)描述】【任務(wù)【任務(wù)4.2.2】創(chuàng)建內(nèi)連接查詢】創(chuàng)建內(nèi)連接查詢

44、【任務(wù)4-27】在學(xué)生成績管理數(shù)據(jù)庫StudentScore_DB中，查詢男同學(xué)的成績，要求有學(xué)生學(xué)號(hào)、姓名、性別、班級(jí)號(hào)、班級(jí)名稱，課程號(hào)，課程名、成績等。Select S1.StudID ,S1.StudName,S1.StudSex ,S1.ClassID,C1.ClassName, C2.CourseID,C2.CourseName ,S2.Score From ClassInfo C1 INNER JOIN StudentInfo S1 ON C1.ClassID =S1.ClassID INNER JOIN ScoreInfo S2ON S1.StudID =S2.StudID I

45、NNER JOIN CourseInfo C2ON C2.CourseID =S2.CourseIDWhere S1.StudSex =男【任務(wù)描述任務(wù)描述】【任務(wù)【任務(wù)4.2.3】創(chuàng)建外連接查詢】創(chuàng)建外連接查詢外連接返回FROM子句中指定的至少一個(gè)表或視圖中的所有行，只要這些行符合任何WHERE選擇（不包括ON之后的連接條件）或HAVING限定條件。外連接又分為左外連接、右外連接和全外連接。FROM子句表稱為左表，JOIN子句表稱為右表。左外連接對(duì)連接中左邊的表不加限制；右外連接中對(duì)連接中右邊的表不加限制；全外連接對(duì)兩個(gè)表都不加限制，兩個(gè)表的所有行都包括在結(jié)果集中。語法格式： FROM 表1

46、 LEFT|RIGHT|FULL JOIN 表2 ON 表1與表2的連接表達(dá)式?！竟δ苊枋龉δ苊枋觥俊救蝿?wù)【任務(wù)4.2.3】創(chuàng)建外連接查詢】創(chuàng)建外連接查詢（28）在學(xué)生成績管理數(shù)據(jù)庫StudentScore_DB中，查詢學(xué)生的信息，要求有學(xué)生學(xué)號(hào)、姓名、性別、所在班級(jí)號(hào)、班級(jí)名，目前暫無學(xué)生的班級(jí)也要顯示出來。1. 使用左外連接查詢使用左外連接查詢?nèi)缜八?，左外連接對(duì)連接中左邊的表不加限制，即左邊的數(shù)據(jù)表記錄會(huì)全部顯示在結(jié)果集中。語法格式 ：FROM 表1 LEFT JOIN 表2 ON 表1與表2的連接表達(dá)式【任務(wù)4-28】命令如下：Select S1.StudID ,StudName,C1

47、.ClassID,C1.ClassName From ClassInfo C1 LEFT JOIN StudentInfo S1 ON C1.ClassID =S1.ClassID【任務(wù)實(shí)施任務(wù)實(shí)施】【任務(wù)【任務(wù)4.2.3】創(chuàng)建外連接查詢】創(chuàng)建外連接查詢（29）在學(xué)生成績管理數(shù)據(jù)庫StudentScore_DB中，查詢學(xué)生的成績，要求顯示：學(xué)生表中的學(xué)號(hào)、成績表中的學(xué)號(hào)、學(xué)生姓名、所在班級(jí)號(hào)、課程號(hào)、成績。2. 使用右外連接查詢使用右外連接查詢右外連接中對(duì)連接中右邊的表不加限制， RIGHT JOIN子句表稱為右表。語法格式 ：FROM 表1 RIGHT JOIN 表2 ON 表1與表2的連接

48、表達(dá)式有時(shí)當(dāng)參照完整性約束時(shí)不完善時(shí)，會(huì)出現(xiàn)在成績表中有某學(xué)號(hào)學(xué)生成績，而在學(xué)生表中不存在該學(xué)號(hào)信息，即出現(xiàn)異常成績?！救蝿?wù)4-29】命令如下：Select S1.StudID 學(xué)生表中學(xué)號(hào) ,S2.StudID 成績表中學(xué)號(hào) ,StudName,ClassID,CourseID,ScoreFrom StudentInfo S1 right JOIN ScoreInfo S2ON S1.StudID =S2.StudID【任務(wù)描述任務(wù)描述】【任務(wù)【任務(wù)4.2.3】創(chuàng)建外連接查詢】創(chuàng)建外連接查詢（30）在學(xué)生成績管理數(shù)據(jù)庫StudentScore_DB中，查詢學(xué)生的成績，要求顯示：學(xué)生表中的學(xué)號(hào)

49、、成績表中的學(xué)號(hào)、學(xué)生姓名、所在班級(jí)號(hào)、課程表中的課程號(hào)、課程名、成績。兩個(gè)數(shù)據(jù)表的記錄都要顯示出來。3. 全連接查詢?nèi)B接查詢?nèi)膺B接對(duì)兩個(gè)表都不加限制，兩個(gè)表的所有行都包括在結(jié)果集中。語法格式 ：FROM 表1 FULL JOIN 表2 ON 表1與表2的連接表達(dá)式【任務(wù)4-30】命令如下：Select S1.StudID 學(xué)生表中學(xué)號(hào) ,S2.StudID 成績表中學(xué)號(hào) ,StudName,ClassID,CourseID,ScoreFrom StudentInfo S1 FULL JOIN ScoreInfo S2ON S1.StudID =S2.StudID FULL JOIN Co

50、urseInfo C1ON S1.CourseID=C1.CourseID【任務(wù)描述任務(wù)描述】【任務(wù)【任務(wù)4.2.3】創(chuàng)建外連接查詢】創(chuàng)建外連接查詢4. 自連接查詢自連接查詢表可以通過自連接實(shí)現(xiàn)自身的連接運(yùn)算。自連接可以看作是一張表的兩個(gè)副本之間進(jìn)行的連接，在自連接中，必須為表指定兩個(gè)不同的別名，使之在邏輯上成為兩張表。語法格式： FROM 表1 別名1 JOIN 表1 別名2 ON 連接表達(dá)式【任務(wù)4-31】在學(xué)生成績管理數(shù)據(jù)庫StudentScore_DB中，查詢選修了“A01001”課程的學(xué)生的成績排名，要求查詢結(jié)果中包含學(xué)號(hào)，名次字段。Select S1.Studid,count(*)

51、as 名次FROM ScoreInfo S1 INNER JOIN ScoreInfo S2on S1.Score76)【任務(wù)實(shí)施任務(wù)實(shí)施】4.3.1創(chuàng)建子查詢創(chuàng)建子查詢（31）查詢每位學(xué)生的平均成績，包括：學(xué)號(hào)，姓名，平均成績。（32）查詢選修了課程的學(xué)生學(xué)號(hào)，姓名，班級(jí)號(hào)。（33）查詢平均分大于76分的學(xué)生信息?！救蝿?wù)描述任務(wù)描述】4.3.1 創(chuàng)建子查詢創(chuàng)建子查詢（31）查詢每位學(xué)生的平均成績，包括：學(xué)號(hào)，姓名，平均成績。（32）查詢選修了課程的學(xué)生學(xué)號(hào)，姓名，班級(jí)號(hào)。（33）查詢平均分大于76分的學(xué)生信息?！救蝿?wù)描述任務(wù)描述】【任務(wù)【任務(wù)4.3.2】子查詢?cè)跀?shù)據(jù)更新中的運(yùn)用】子查詢?cè)跀?shù)據(jù)

52、更新中的運(yùn)用子查詢除可以嵌套在外部SELECT以外，還可以嵌套在INSERT、UPDATE、DELETE語句的WHERE或HAVING子句內(nèi)，或者其他子查詢中。任何可以使用表達(dá)式的地方都可以使用子查詢，只要它返回的是單個(gè)值。1. 子查詢?cè)贗NSERT語句中的應(yīng)用在INSERT語句中使用SELECT子句可以將一個(gè)或多個(gè)表或視圖中的值添加到另一個(gè)表中。使用SELECT 子句可以同時(shí)插入多行。語法格式： INSERT INTO 表1 SELECT 字段列表 FROM 表2說明：表2結(jié)構(gòu)要與表1結(jié)構(gòu)相匹配。2. 子查詢?cè)赨PDATE語句中的應(yīng)用。UPDATE語句中SET、WHERE子句中均可使用SEL

53、ECT子查詢。3. 子查詢?cè)贒ELETE語句中的應(yīng)用。DELETE語句中，WHERE子句中可以使用SELECT子查詢?！竟δ苊枋龉δ苊枋觥俊救蝿?wù)【任務(wù)4.3.2】子查詢?cè)跀?shù)據(jù)更新中的運(yùn)用】子查詢?cè)跀?shù)據(jù)更新中的運(yùn)用（36）新建一個(gè)與ScoreInfo結(jié)構(gòu)相同的表，New_Score，然后將ScoreInfo表中成績小于等于60的記錄添加到表New_Score中。（37）將學(xué)生表StudentInfo備份，產(chǎn)生一個(gè)新表，表名為：New_Student；然后再根據(jù)成績表中分?jǐn)?shù)不及格的學(xué)生的學(xué)號(hào)，將新表New_Student中相應(yīng)學(xué)生的備注字段：Remarks的值修改為“成績不合格，請(qǐng)參加補(bǔ)考”?！救?/p>

54、務(wù)描述任務(wù)描述】【任務(wù)4-36】實(shí)施步驟（1）創(chuàng)建一個(gè)結(jié)構(gòu)與ScoreInfo相同的空表New_Score，請(qǐng)讀者自行完成。（2）將ScoreInfo表中成績小于等于70的記錄添加到表New_Score中。命令如下：insert into new_score select * from scoreinfo where score70【任務(wù)4-37】實(shí)施步驟（1）將學(xué)生表StudentInfo備份，產(chǎn)生一個(gè)新表，表名為：New_Student（2）將新表New_Student中相應(yīng)學(xué)生的備注字段：Remarks的值修改為“成績不合格，請(qǐng)參加補(bǔ)考”。命令如下：select * into New_student from studentinfogo【任務(wù)實(shí)施任務(wù)實(shí)施】【任務(wù)【任務(wù)4.3.2】子查詢?cè)跀?shù)據(jù)更新中的運(yùn)用】子查詢?cè)跀?shù)據(jù)更新中的運(yùn)用【任務(wù)【任務(wù)4.3.2】子查詢?cè)跀?shù)據(jù)更新中的運(yùn)用】子查詢?cè)跀?shù)據(jù)更新中的運(yùn)用update new_student set remarks=成績不合格，請(qǐng)參加補(bǔ)考 where exists(select studid from scoreinfo where score60 and studid=new_student.studid)goselec