單元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 教學目標教學目標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ù)學習型工作任務(wù)學習型工作任務(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)合查詢知識知識鏈接鏈接【L1.2.1】Select 語句的語句的語法格式語法格式 1. SELE

2、CT 語句的語法格式2. SELECT語句說明3. SELECT語句的執(zhí)行過程4. 使用使用SELECT語句的注意語句的注意事項事項單元訓練單元訓練單元訓練單元訓練SELECT子句中謂詞的使用方法WHERE子句的使用方法分組查詢GROUP BY、HAVING子句的使用方法集合函數(shù)的使用方法多表連接查詢的使用方法靈活使用SELECT子句進行數(shù)據(jù)處理單元單元1 知識結(jié)構(gòu)框架圖知識結(jié)構(gòu)框架圖 多表查詢學習型工作任務(wù)學習型工作任務(wù)查詢數(shù)據(jù)表中數(shù)據(jù)4.1 創(chuàng)建基本查詢4.2 創(chuàng)建連接查詢4.3 創(chuàng)建子查詢4.4 創(chuàng)建聯(lián)合查詢 多多表表查查詢詢單表單表查詢查詢4.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)計查詢【任務(wù)4.1.5 】使用HAVING子句限定分組統(tǒng)計查詢【任務(wù)4.1.6 】使用ORDER BY子句排序【任務(wù)4.1.7 】使用COMPUTE BY子句匯總查詢【任務(wù)4.1.8 】使用INTO子句保存查詢【操作準備】【操作準備】uSELECT語句的語法格式及功能；語句的語法格式及功能；學生成績管理系統(tǒng)數(shù)據(jù)庫學生成績管理系統(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 編輯器查詢“班級表”中所有信息。（2）查詢班級表ClassInfo中班級號ClassID，班級名ClassNamer的信息。（3）查詢班級表ClassInfo中ClassID和ClassName，為便于用戶閱讀，列名顯示為中文“班級號”和“班級名”。（4）查詢成績表ScorInfo中學號StudID,課程號CourseID,成績Score及成績Score加5分后結(jié)果。（5）查詢學生表StudentInfo中前5條記錄。（6）查詢

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

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

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

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

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

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

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

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

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

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

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

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

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

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

19、命令：運行結(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)計查詢子句分組統(tǒng)計查詢GROUP BY 與列名或聚合函數(shù)配合實現(xiàn)分組統(tǒng)計。聚合函數(shù)是對整個表中的數(shù)據(jù)進行計算統(tǒng)計，但實際應(yīng)用中往往需要根據(jù)某列的值進行分組統(tǒng)計與匯總，如從成績表中統(tǒng)計每位學生所選課程門數(shù)、總分等，統(tǒng)計時需要用“GROUP BY StudID”,即

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

21、，并用相應(yīng)的中文名稱表示相應(yīng)的值。在查詢分析器中運行如下命令：select StudID , count(*) 課程門數(shù),max(score) 最高分 ,MIN(score) 最低分, sum(score) 總分, avg (score) 平均分 from ScoreInfo group by StudID【任務(wù)4-19】從“成績表”中查詢選修每門課程的學生人數(shù)、最高分、最低分、總分、平均分，并用相應(yīng)的中文名稱表示相應(yīng)的值。在查詢分析器中運行如下命令：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)計查詢子句限定分組統(tǒng)計查詢HAVING子名與GROUP BY參數(shù)配合篩選統(tǒng)計結(jié)果。HAVING子句的邏輯表達式通常包含聚合函數(shù)。注意聚合函數(shù)不能放在WHERE子句的邏輯表達式中。語法格式：HAVING邏輯表達式【任務(wù)描述】【任務(wù)描述】（20）從“成績表”中查詢選修課程門數(shù)大于2門的每位學生所選的課程門數(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個字節(jié)）對查詢結(jié)果進行升序（個字節(jié)）對查詢結(jié)果進行升序（ASC，默認）或降序，默認）或降序(DESC)排序。如果排序。如果ORDER BY子句后是一個列名表達式表，則系統(tǒng)將根據(jù)各列的次子句后是一個列名表達式表，則系統(tǒng)將根據(jù)各列的次序決定排序的優(yōu)先級，然后排序。序決定排序的優(yōu)先級，然后排序。ORDER BY對數(shù)據(jù)類型為對數(shù)據(jù)類型為varchar(max)、nvarchar(max)、varbinary(max)或或xml的的 列不使用，并且只能在外查詢中使用。列不使用，并且只能在外查詢中使用。語法格式：語法格式：ORDER BY 列名表達式表列名表達式表 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 語句既查看明細行，又查看分類匯總行；而COMPUTE子句使用戶可以用同一SELECT語句既可查看明細行，又查看總計行?？梢杂嬎銠C整個結(jié)果集匯總值，也可以計算子組的匯總值。COMPUTE子句需要下列信息：（1）可選的BY關(guān)鍵字，

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

27、的匯總值。SELECT StudID,CourseID,Score FROM ScoreInfo ORDER BY StudidCOMPUTE SUM(score),AVG(Score) BY StudID【任務(wù)實施任務(wù)實施】【任務(wù)【任務(wù)4.1.8 】使用】使用INTO子句保存查詢子句保存查詢INTO子句指定使用結(jié)果集創(chuàng)建新表。這種方式常用于創(chuàng)建表的副本。INTO 子名位于FROM子句前。語法格式：INTO 新表名【任務(wù)描述】【任務(wù)描述】（24）從“成績表”中查詢選修課程門數(shù)大于2門的每位學生所選的課程門數(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)建基本連接查詢前面主要介紹了在一個數(shù)據(jù)表中查詢數(shù)據(jù)的方法。在實際應(yīng)用中，往往需要在多個數(shù)據(jù)表之間查詢，才能滿足用戶查詢需求。 實現(xiàn)從兩個或兩個以上數(shù)據(jù)表中查詢數(shù)據(jù)且結(jié)果集中出現(xiàn)的更來自于兩個或兩個以上數(shù)據(jù)表的檢索操作稱為連接查詢。其實質(zhì)是通過各個數(shù)據(jù)表之間的共同列的相關(guān)性來查詢數(shù)據(jù)，首先在數(shù)據(jù)表之間建立連接，然后再從中查詢數(shù)據(jù)。 連接的類型分為內(nèi)連接、外連接和交叉連接。其中外連接包括左外連接、右外連接和全外連接。 連接查詢根據(jù)語法分類分為：基本連接查詢和ANSI（American National Standards Institute，美國標準學會）連接

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

31、接查詢ANSI連接查詢的語法形式，在FROM子句中使用JOINON關(guān)鍵字，連接條件寫在ON后面，從而實現(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個數(shù)據(jù)表的記錄數(shù)乘以第2個表的記錄數(shù)。例如：學生信息表StudentInfo中有10條記錄，成績表ScoreInfo中有8條記錄，其結(jié)果有(10*8=80)條記錄，交叉

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

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

34、tInfo)、成績表（ScoreInfo），這兩個表通過學號(StudID)關(guān)聯(lián)，故其連接條件為：StudentInfo.StudID=ScoreInfo.StudID，該連接條件位于WHERE子句中，且對于兩個表中的共同字段要注明哪個表中的字段，如：StudentInfo.StudID。為書寫方便，SQL 支持使用別名，用于簡化。1兩個數(shù)據(jù)表之間的連接查詢兩個數(shù)據(jù)表之間的連接查詢【任務(wù)【任務(wù)4-25】在學生成績管理數(shù)據(jù)庫StudentScore_DB中，查詢學生的成績，要求有學生學號、姓名、所在班級號，課程號，成績等。在【SQL編輯器】中運行如下命令：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ù)實施任務(wù)實施】【任務(wù)任務(wù)4.2.1】創(chuàng)建基本連接查詢創(chuàng)建基本連接查詢2多個數(shù)據(jù)表之間的連接查詢多個數(shù)據(jù)表之間的連接查詢【任務(wù)4-

36、26】在學生成績管理數(shù)據(jù)庫StudentScore_DB中，查詢學生的成績，要求有學生學號、姓名、所在班級名稱，課程號，課程名、成績等。分析任務(wù)26可以發(fā)現(xiàn)，其查詢涉及四個數(shù)據(jù)表：班級名、學生表、課程表和成績表的連接。連接條件為：班級表（ClassInfo）與學生表（StudentInfo）之間共同字段為班級號(ClassID)，課程表(CourseInfo)與成績表（ScoreInfo）共字段為課程號（CourseID），學生表（StudentInfo）與成績表（ScoreInfo）之間共同字段為學號（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ù)實施任務(wù)實施】【任務(wù)任務(wù)4.2.1】創(chuàng)建基本連接查詢創(chuàng)建基本連接查詢【任務(wù)4-27】在學生成績管理數(shù)據(jù)庫StudentScore_DB中，查詢男同學的成績，要求有學生學號、姓名、性別、班級號、班級名稱，課程號，課程名、成績等。在【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ù)實施任務(wù)實施】【任務(wù)【任務(wù)4.2.2】創(chuàng)建內(nèi)連接查詢】創(chuàng)建內(nèi)連接查詢 內(nèi)連接是組合兩個表的常用方法。內(nèi)連接使用比較運算符進行多個源表之間數(shù)據(jù)的比較，并返回這些源表中與連接條件相匹配的數(shù)據(jù)行。使用JOIN或INNER JOIN關(guān)鍵字實現(xiàn)內(nèi)連接。其執(zhí)行連接查詢后，結(jié)果僅顯示匹配行的所有記錄，其它不匹配的記錄將被過濾掉。內(nèi)連接分為等值連接、非等值連接

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

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

42、級號、班級名稱，課程號，課程名、成績等?！救蝿?wù)描述任務(wù)描述】【任務(wù)【任務(wù)4.2.2】創(chuàng)建內(nèi)連接查詢】創(chuàng)建內(nèi)連接查詢1兩個數(shù)據(jù)表之間的連接查詢兩個數(shù)據(jù)表之間的連接查詢【任務(wù)【任務(wù)4-25】在學生成績管理數(shù)據(jù)庫StudentScore_DB中，查詢學生的成績，要求有學生學號、姓名、所在班級號，課程號，成績等。Select S1.StudID ,StudName,ClassID,CourseID,ScoreFrom StudentInfo S1 INNER JOIN ScoreInfo S2 ON S1.StudID =S2.StudID【任務(wù)4-26】使用內(nèi)連接查詢時，命令如下： 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】在學生成績管理數(shù)據(jù)庫StudentScore_DB中，查詢男同學的成績，要求有學生學號、姓名、性別、班級號、班級名稱，課程號，課程名、成績等。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子句中指定的至少一個表或視圖中的所有行，只要這些行符合任何WHERE選擇（不包括ON之后的連接條件）或HAVING限定條件。外連接又分為左外連接、右外連接和全外連接。FROM子句表稱為左表，JOIN子句表稱為右表。左外連接對連接中左邊的表不加限制；右外連接中對連接中右邊的表不加限制；全外連接對兩個表都不加限制，兩個表的所有行都包括在結(jié)果集中。語法格式： FROM 表1

46、 LEFT|RIGHT|FULL JOIN 表2 ON 表1與表2的連接表達式?！竟δ苊枋龉δ苊枋觥俊救蝿?wù)【任務(wù)4.2.3】創(chuàng)建外連接查詢】創(chuàng)建外連接查詢（28）在學生成績管理數(shù)據(jù)庫StudentScore_DB中，查詢學生的信息，要求有學生學號、姓名、性別、所在班級號、班級名，目前暫無學生的班級也要顯示出來。1. 使用左外連接查詢使用左外連接查詢?nèi)缜八觯笸膺B接對連接中左邊的表不加限制，即左邊的數(shù)據(jù)表記錄會全部顯示在結(jié)果集中。語法格式 ：FROM 表1 LEFT JOIN 表2 ON 表1與表2的連接表達式【任務(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ù)實施任務(wù)實施】【任務(wù)【任務(wù)4.2.3】創(chuàng)建外連接查詢】創(chuàng)建外連接查詢（29）在學生成績管理數(shù)據(jù)庫StudentScore_DB中，查詢學生的成績，要求顯示：學生表中的學號、成績表中的學號、學生姓名、所在班級號、課程號、成績。2. 使用右外連接查詢使用右外連接查詢右外連接中對連接中右邊的表不加限制， RIGHT JOIN子句表稱為右表。語法格式 ：FROM 表1 RIGHT JOIN 表2 ON 表1與表2的連接

48、表達式有時當參照完整性約束時不完善時，會出現(xiàn)在成績表中有某學號學生成績，而在學生表中不存在該學號信息，即出現(xiàn)異常成績。【任務(wù)4-29】命令如下：Select S1.StudID 學生表中學號 ,S2.StudID 成績表中學號 ,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）在學生成績管理數(shù)據(jù)庫StudentScore_DB中，查詢學生的成績，要求顯示：學生表中的學號

49、、成績表中的學號、學生姓名、所在班級號、課程表中的課程號、課程名、成績。兩個數(shù)據(jù)表的記錄都要顯示出來。3. 全連接查詢?nèi)B接查詢?nèi)膺B接對兩個表都不加限制，兩個表的所有行都包括在結(jié)果集中。語法格式 ：FROM 表1 FULL JOIN 表2 ON 表1與表2的連接表達式【任務(wù)4-30】命令如下：Select S1.StudID 學生表中學號 ,S2.StudID 成績表中學號 ,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. 自連接查詢自連接查詢表可以通過自連接實現(xiàn)自身的連接運算。自連接可以看作是一張表的兩個副本之間進行的連接，在自連接中，必須為表指定兩個不同的別名，使之在邏輯上成為兩張表。語法格式： FROM 表1 別名1 JOIN 表1 別名2 ON 連接表達式【任務(wù)4-31】在學生成績管理數(shù)據(jù)庫StudentScore_DB中，查詢選修了“A01001”課程的學生的成績排名，要求查詢結(jié)果中包含學號，名次字段。Select S1.Studid,count(*)

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

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

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

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