項目5學生成績管理數(shù)據(jù)庫表數(shù)據(jù)查詢

1、項目項目5 學生成績管理數(shù)據(jù)庫表數(shù)據(jù)學生成績管理數(shù)據(jù)庫表數(shù)據(jù)的查詢的查詢1. 能利用查詢語句進行簡單查詢能利用查詢語句進行簡單查詢 ；2. 能利用查詢語句進行能利用查詢語句進行多表查詢、嵌套查詢多表查詢、嵌套查詢 ；3. 能利用能利用聚合函數(shù)進行簡單匯總；聚合函數(shù)進行簡單匯總；4. 能結(jié)合查詢進行數(shù)據(jù)記錄的添加、修改、刪除等操作。能結(jié)合查詢進行數(shù)據(jù)記錄的添加、修改、刪除等操作。能力目標能力目標項項目目描描述述數(shù)據(jù)庫的最大功能之一是查詢，在學生成績管理系統(tǒng)中用到的最多也是查詢。例如，要查詢姓名為“張大山”的信息；或者要查詢姓名為“張大山”所修課程及對應成績的信息等等。所以本項目首先通過學生表、成

2、績表、課程表等記錄的查詢，學會數(shù)據(jù)表記錄的基本查詢方法；其次通過學生表、成績表等幾個表的連接查詢、子查詢等學會高級查詢的方法；然后再通過對學生表、成績表等表的查詢結(jié)果更新相應的表數(shù)據(jù)，學會利用查詢結(jié)果更新表數(shù)據(jù)的方法；最后通過課堂實訓、課外實訓來加強對數(shù)據(jù)庫表數(shù)據(jù)的靈活查詢的能力。學學習習任任務務任務5.1：學生成績管理數(shù)據(jù)庫單表查詢?nèi)蝿?.2：學生成績管理數(shù)據(jù)庫多表查詢?nèi)蝿?.3：用學生成績管理數(shù)據(jù)庫數(shù)據(jù)查詢結(jié)果更新表數(shù)據(jù)項目描述項目描述及及學習任務學習任務5.1 5.1 學生成績管理數(shù)據(jù)庫單表查詢學生成績管理數(shù)據(jù)庫單表查詢數(shù)據(jù)查詢是對數(shù)據(jù)庫中的數(shù)據(jù)按指定內(nèi)容和順序進行檢索輸出。它可以對數(shù)據(jù)

3、數(shù)據(jù)查詢是對數(shù)據(jù)庫中的數(shù)據(jù)按指定內(nèi)容和順序進行檢索輸出。它可以對數(shù)據(jù)源進行各種組合，有效地篩選記錄、管理數(shù)據(jù)，并對結(jié)果進行排序；它可以讓源進行各種組合，有效地篩選記錄、管理數(shù)據(jù)，并對結(jié)果進行排序；它可以讓用戶以需要的方式查詢數(shù)據(jù)表中的數(shù)據(jù)，可以控制查詢數(shù)據(jù)表中的字段、記錄用戶以需要的方式查詢數(shù)據(jù)表中的數(shù)據(jù)，可以控制查詢數(shù)據(jù)表中的字段、記錄以及顯示記錄的順序等。數(shù)據(jù)查詢是數(shù)據(jù)庫的核心操作。本學習任務主要介紹以及顯示記錄的順序等。數(shù)據(jù)查詢是數(shù)據(jù)庫的核心操作。本學習任務主要介紹數(shù)據(jù)庫單表查詢語句。數(shù)據(jù)庫單表查詢語句。任務描述任務描述 5.1.1 簡單查詢簡單查詢數(shù)據(jù)庫查詢數(shù)據(jù)庫查詢是數(shù)據(jù)庫的核心操作

4、。數(shù)據(jù)查詢是用來描述怎樣從數(shù)據(jù)庫中獲取所是數(shù)據(jù)庫的核心操作。數(shù)據(jù)查詢是用來描述怎樣從數(shù)據(jù)庫中獲取所需要的數(shù)據(jù)。查詢會產(chǎn)生一個虛擬表，即看到的是表形式顯示的結(jié)果，但結(jié)果需要的數(shù)據(jù)。查詢會產(chǎn)生一個虛擬表，即看到的是表形式顯示的結(jié)果，但結(jié)果并不真正存儲，每次執(zhí)行查詢只是從數(shù)據(jù)表中提取數(shù)據(jù)，并按照表的形式顯示并不真正存儲，每次執(zhí)行查詢只是從數(shù)據(jù)表中提取數(shù)據(jù)，并按照表的形式顯示出來。如圖出來。如圖5-15-1所示。所示。Application Logic客戶程序查詢請求查詢結(jié)果集SQL SERVERA B C D E F G 圖圖5-1 查詢過程查詢過程簡單查詢指的是在一個數(shù)據(jù)表中查詢所需的數(shù)據(jù)。簡單查

5、詢指的是在一個數(shù)據(jù)表中查詢所需的數(shù)據(jù)。1 1格式格式：SELECTSELECT All | Distinct Top n Percent All | Distinct Top n Percent FROMFROM WHERE WHERE ORDER BY ORDER BY ASCASC或或DESCDESC帶有方括號的子句是可以選擇的。帶有方括號的子句是可以選擇的。 指所查詢列，它可以由一組列名列表、星號、表達式等構(gòu)成。指所查詢列，它可以由一組列名列表、星號、表達式等構(gòu)成。All | Distinct All All | Distinct All 表示所有行，表示所有行，Distinct Dis

6、tinct 表示過濾重復行，默認為所有表示過濾重復行，默認為所有行。行。 Top n Percent Top n Percent 表示返回的行數(shù)，表示返回的行數(shù)，Top n Top n 表示顯示前表示顯示前n n行，行，Top n Top n PercentPercent表示顯示前百分比行。表示顯示前百分比行。From From ：對單表查詢，只需給出一個表名。：對單表查詢，只需給出一個表名。Where Where 是篩選條件是篩選條件ORDER BY ORDER BY 是對指定的列進行排序，是對指定的列進行排序，ASCASC表示升序，表示升序，DESCDESC表示降序。表示降序。5.1.1

7、簡單查詢簡單查詢5.1.1 5.1.1 簡單查詢簡單查詢?nèi)粝氩樵內(nèi)粝氩樵儭皩W生表學生表”中專業(yè)為應用電子的前中專業(yè)為應用電子的前5 5條信息（按性別排序），將它按格條信息（按性別排序），將它按格式中分類，則如表式中分類，則如表5-15-1所示。所示。選擇列限制固定行數(shù)表名過濾條件排序條件Select學號,姓名Top 5From學生表Where專業(yè)=應用電子Order by性別表表5-1“學生表學生表”中專業(yè)為應用電子的前中專業(yè)為應用電子的前5條信息查詢格式分類表條信息查詢格式分類表即：即：Select Top 5 學號學號,姓名姓名 From 學生表學生表 Where 專業(yè)專業(yè)=應用電應用電子

8、子 order by 性別性別2. 2. 應用應用（1）查詢選擇部分列，并指定它們的顯示次序）查詢選擇部分列，并指定它們的顯示次序【例例5.1】查詢查詢“學生表學生表”中所有學生學號中所有學生學號,姓名姓名,性別性別,專業(yè)的信息。專業(yè)的信息。在查詢編輯器中輸入：在查詢編輯器中輸入：select 學號學號, 姓名姓名, 性別性別, 專業(yè)專業(yè) from 學生表學生表或者或者: select all 學號學號, 姓名姓名, 性別性別, 專業(yè)專業(yè) from 學生表學生表點擊點擊“執(zhí)行執(zhí)行”，則會出現(xiàn)如圖所示結(jié)果。，則會出現(xiàn)如圖所示結(jié)果。注意：列名之間是用逗號隔開的。注意：列名之間是用逗號隔開的。2.

9、2. 應用應用任務描述任務描述 （2）查詢所有列）查詢所有列用用“ * ”表示所有列或?qū)⒈碇械乃辛幸灰涣谐?。表示所有列或?qū)⒈碇械乃辛幸灰涣谐觥！纠?.2】查詢查詢“學生表學生表”所有學生的詳細信息。所有學生的詳細信息。select * from 學生表學生表或或 select 學號學號, 姓名姓名, 性別性別, 專業(yè)專業(yè), 出生年月出生年月, 家庭地址家庭地址, 聯(lián)系電話聯(lián)系電話, 總學分總學分 from 學生表學生表執(zhí)行，則出現(xiàn)如圖的結(jié)果。執(zhí)行，則出現(xiàn)如圖的結(jié)果。2. 2. 應用應用任務描述任務描述 （3 3）查詢經(jīng)過計算的值）查詢經(jīng)過計算的值【例例5.3】查詢查詢“學生表學生表”所有

10、學生的學號，姓名，性別，專業(yè)，年齡，家所有學生的學號，姓名，性別，專業(yè)，年齡，家庭地址，聯(lián)系電話。庭地址，聯(lián)系電話。select 學號學號, 姓名姓名, 性別性別, year(getdate()-year(出生年月出生年月), 家庭地址家庭地址, 聯(lián)系聯(lián)系電話電話 from 學生表學生表執(zhí)行結(jié)果如圖所示。執(zhí)行結(jié)果如圖所示。注意：由于注意：由于“學生表學生表”中只有中只有“出出生年月生年月”的字段，而無的字段，而無“年齡年齡”字字段，但是通過計算可以得到年齡。段，但是通過計算可以得到年齡。由于是計算得到的列，在原先的表由于是計算得到的列，在原先的表中無此列，所以顯示中無此列，所以顯示“無列名無列

11、名”。2. 2. 應用應用SQL ServerSQL Server中的函數(shù)分：字符串函數(shù)、日期函數(shù)、數(shù)學函數(shù)、系統(tǒng)函數(shù)。表中的函數(shù)分：字符串函數(shù)、日期函數(shù)、數(shù)學函數(shù)、系統(tǒng)函數(shù)。表5-25-2至表至表5-55-5是部分常用的是部分常用的SQL ServerSQL Server函數(shù)。函數(shù)。表5-2 日期函數(shù)函數(shù)名函數(shù)名描述描述舉例舉例getdate取得當前的系統(tǒng)日期Select getdate( )返回：今天的日期dateadd在日期中添加或減去指定的時間間隔Select dateadd(mm, 4, 03/01/1999)返回：以當前的日期格式返回07/01/1999year返回日期表達式中的年

12、份Select year(01/10/2014)返回：2014month返回日期表達式中的月份Select month(2014-11-13)返回：11datename日期中指定日期部分的字符串形式Select datename(dw, 12/03/2013)返回：星期二2. 2. 應用應用任務描述任務描述 表表5-3 字符串函數(shù)字符串函數(shù)函數(shù)名函數(shù)名描述描述舉例舉例LEN返回傳遞給它的字符串長度SELECT LEN(SQL Server課程)返回：12LOWER把傳遞給它的字符串轉(zhuǎn)換為小寫SELECT LOWER(SQL Server課程)返回： SQL Server課程UPPER把傳遞給它

13、的字符串轉(zhuǎn)換為大寫SELECT UPPER(sql server課程)返回：SQL SERVER課程LTRIM清除字符左邊的空格SELECT LTRIM ( 周智宇 )返回：周智宇 （后面的空格保留）RTRIM清除字符右邊的空格SELECT RTRIM ( 周智宇 )返回： 周智宇（前面的空格保留）RIGHT從字符串右邊返回指定數(shù)目的字符SELECT RIGHT(買賣提.吐爾松,3)返回：吐爾松REPLACE替換一個字符串中的字符SELECT REPLACE(莫樂可切.楊可,可,蘭)返回：莫樂蘭切.楊蘭STUFF在一個字符串中，刪除指定長度的字符，并在該位置插入一個新的字符串SELECT ST

14、UFF(ABCDEFG, 2, 3, 我的音樂我的世界)返回：A我的音樂我的世界EFG2. 2. 應用應用任務描述任務描述 表表5-4 數(shù)學函數(shù)數(shù)學函數(shù)函數(shù)名函數(shù)名描述描述舉例舉例ABS取數(shù)值表達式的絕對值SELECT ABS(-43)返回：43CEILING返回大于或等于所給數(shù)字表達式的最小整數(shù)SELECT CEILING(43.3)返回：44FLOOR取小于或等于指定表達式的最大整數(shù)SELECT FLOOR(43.5)返回：43POWER取數(shù)值表達式的冪值SELECT POWER(5,2)返回：25ROUND將數(shù)值表達式四舍五入為指定精度SELECT ROUND(43.543,1)返回：4

15、3.5SIGN對于正數(shù)返回+1，對于負數(shù)返回-1，對于0 則返回0SELECT SIGN(-43)返回：-1SQRT取浮點表達式的平方根SELECT SQRT(9)返回：32. 2. 應用應用任務描述任務描述 表表5-5 系統(tǒng)函數(shù)系統(tǒng)函數(shù)函數(shù)名函數(shù)名描述描述舉例舉例CONVERT用來轉(zhuǎn)變數(shù)據(jù)類型SELECT CONVERT (VARCHAR (5)，12345)返回：字符型12345CURRENT_USER返回當前用戶的名字SELECT CURRENT_USER返回：登錄的用戶名DATALENGTH返回用于指定表達式的字節(jié)數(shù)SELECT DATALENGTH (中國A盟)返回：7HOST_NA

16、ME返回當前用戶所登錄的計算機名字SELECT HOST_NAME()返回：所登錄的計算機的名字SYSTEM_USER 返回當前所登錄的用戶名稱SELECT SYSTEM_USER返回：當前所登錄的用戶名USER_NAME從給定的用戶I D返回用戶名SELECT USER_NAME(1)返回：從任意數(shù)據(jù)庫中返回“dbo”2. 2. 應用應用任務描述任務描述 （4 4）更改列標題）更改列標題【例例5.45.4】將將【例例5.35.3】中的年齡要求顯示列標題。中的年齡要求顯示列標題。select 學號學號, 姓名姓名, 性別性別, 專業(yè)專業(yè), 年齡年齡=year(getdate()-year(出生

17、年月出生年月), 家庭家庭地址地址, 聯(lián)系電話聯(lián)系電話 from 學生表學生表執(zhí)行結(jié)果如圖所示。執(zhí)行結(jié)果如圖所示。2. 2. 應用應用任務描述任務描述 （4 4）更改列標題）更改列標題【例例5.45.4】將將【例例5.35.3】中的年齡要求顯示列標題。中的年齡要求顯示列標題。select 學號學號, 姓名姓名, 性別性別, 專業(yè)專業(yè), 年齡年齡=year(getdate()-year(出生年月出生年月), 家庭家庭地址地址, 聯(lián)系電話聯(lián)系電話 from 學生表學生表執(zhí)行結(jié)果如圖所示。執(zhí)行結(jié)果如圖所示。也可以寫成：也可以寫成：select 學號學號, 姓名姓名, 性別性別,專業(yè)專業(yè), year(

18、getdate()-year(出生年月出生年月) as 年齡年齡, 家庭家庭地址地址, 聯(lián)系電話聯(lián)系電話 from 學生表學生表或或select 學號學號, 姓名姓名, 性別性別,專業(yè)專業(yè), year(getdate()-year(出生年月出生年月) 年齡年齡, 家庭地家庭地址址, 聯(lián)系電話聯(lián)系電話 from 學生表學生表即可以寫成：即可以寫成：“列別名列別名=表達式表達式”，或，或“表達式表達式 as 列別名列別名”，或，或 “表達式表達式 列別名列別名”。2. 2. 應用應用任務描述任務描述 （5 5）過濾重復行）過濾重復行Select 語句中使語句中使用用all或或distinct選項來

19、顯示表中符合條件的所有行或過濾其選項來顯示表中符合條件的所有行或過濾其中重復的數(shù)據(jù)行，默認為中重復的數(shù)據(jù)行，默認為all 。使用。使用distinct選項時，對于所有重復的數(shù)據(jù)選項時，對于所有重復的數(shù)據(jù)行在行在select返回的結(jié)果集合中只保留一行。返回的結(jié)果集合中只保留一行。【例例5.55.5】顯示顯示“學生表學生表”中所有的專業(yè)（不要出現(xiàn)重復專業(yè)）。中所有的專業(yè)（不要出現(xiàn)重復專業(yè)）。select distinct 專業(yè)專業(yè) from 學生學生表表執(zhí)行結(jié)果如圖所示。執(zhí)行結(jié)果如圖所示。說明：說明：distinct是過濾掉重復的列。即是過濾掉重復的列。即若執(zhí)行：若執(zhí)行：select 專業(yè)專業(yè) f

20、rom 學生表，學生表，則顯示的結(jié)果中多次重復出現(xiàn)相同的專則顯示的結(jié)果中多次重復出現(xiàn)相同的專業(yè)。業(yè)。2. 2. 應用應用任務描述任務描述 （6 6）限制返回的行數(shù)）限制返回的行數(shù)使使用用top n percent選項限制返回的數(shù)據(jù)行數(shù)，選項限制返回的數(shù)據(jù)行數(shù)， top n 說明返回說明返回n行，而行，而top n percent 時，時，n表示一百分數(shù)，指定返回的行數(shù)等于總行數(shù)的百分之幾。表示一百分數(shù)，指定返回的行數(shù)等于總行數(shù)的百分之幾。【例例5.6】顯示顯示“學生表學生表”中前三位的專業(yè)（不要出現(xiàn)重復專業(yè)）。中前三位的專業(yè)（不要出現(xiàn)重復專業(yè)）。select distinct top 3 專業(yè)

21、專業(yè) from 學生表學生表執(zhí)行結(jié)果如圖所示。執(zhí)行結(jié)果如圖所示。說明：說明：Distinct是過濾掉重復是過濾掉重復的列，的列，top 3是前三行。是前三行。2. 2. 應用應用任務描述任務描述 【例例5.75.7】顯示顯示“學生表學生表”中前中前60%60%的專業(yè)（不要重復出現(xiàn)專業(yè)）。的專業(yè)（不要重復出現(xiàn)專業(yè)）。select distinct top 60 percent 專業(yè)專業(yè) from 學生表學生表執(zhí)行結(jié)果如圖所示。執(zhí)行結(jié)果如圖所示。說明：說明：Distinct是過濾掉重復是過濾掉重復的列，的列，top 60 Percent是前是前60%行。行。2. 2. 應用應用任務描述任務描述 （

22、7 7）查詢滿足條件的記錄）查詢滿足條件的記錄查詢滿足條件的記錄用查詢滿足條件的記錄用where 子句，子句， where 子句常用查詢條件中的運算符子句常用查詢條件中的運算符見表見表5-6表表5-6 常用的查詢運算符常用的查詢運算符運算符運算符含義含義運算符運算符含義含義運算符運算符含義含義大于大于 不大于不大于!模糊查詢模糊查詢LikeLikeNot LikeNot Like大于等于大于等于=不小于不小于!空值空值Is NullIs Null等于等于= =在某一范圍在某一范圍Between andBetween and非空值非空值IS Not NullIS Not Null小于小于 不在某

23、一范圍不在某一范圍Not Between andNot Between and非非Not Not 小于等于小于等于=指定集合指定集合InIn并且并且AndAnd不等于不等于!=!=；不屬于指定集合不屬于指定集合Not InNot In或或OrOr2. 2. 應用應用任務描述任務描述 比較運算符比較運算符【例【例5.85.8】顯示】顯示“學生表學生表”中計算機系學生的中計算機系學生的信息。信息。select * from 學生表學生表 where 專業(yè)專業(yè) = 計算機應用計算機應用執(zhí)行結(jié)果如圖所示。執(zhí)行結(jié)果如圖所示。2. 2. 應用應用任務描述任務描述 【例例5.95.9】查詢查詢“選課表選課表

24、”中考試成績小于中考試成績小于7070分的選課信息。分的選課信息。select * from 選課表選課表 where 成績成績 = 70說明：顯示結(jié)果中將成績說明：顯示結(jié)果中將成績小于等于小于等于70分的記錄全都分的記錄全都顯示，可能會出現(xiàn)一個同顯示，可能會出現(xiàn)一個同學有幾門課成績都在學有幾門課成績都在70分分以下，像圖中的第以下，像圖中的第4行、行、第第5行。行。有時，即使?jié)M足條件的記有時，即使?jié)M足條件的記錄有多條，也只要顯示一錄有多條，也只要顯示一條就行，這樣就要用到條就行，這樣就要用到Distinct。2. 2. 應用應用任務描述任務描述 【例【例5.105.10】查詢】查詢“選課表選

25、課表”中考試成績小于中考試成績小于7070分的同學。分的同學。select distinct 學號學號 from 選課表選課表 where 成績成績 =80 and 成績成績=90說明：二者之間可以用說明：二者之間可以用betweenand，也可以用，也可以用and2. 2. 應用應用任務描述任務描述 Not Between Not Between and and 運算符（二者之外）運算符（二者之外）【例【例5.155.15】查詢】查詢“選課表選課表”中課號為中課號為10011001，學習成績不在，學習成績不在8080到到9090分之間的同學信息。分之間的同學信息。select * from

26、選課表選課表 where 課程號課程號=1001 and 成績成績 not between 80 and 90或或select * from 選課表選課表 where 課程號課程號=1001 and (成績成績 90)說明：不在二者之間，可以用說明：不在二者之間，可以用not between and 也可以用也可以用or 2. 2. 應用應用任務描述任務描述 is null is null，is not nullis not null運算符運算符 - -空或非空查詢空或非空查詢【例【例5.165.16】查詢】查詢“課程表課程表”中備注為中備注為nullnull的課程信息。的課程信息。selec

27、t * from 課程表課程表 where 備注備注 is null執(zhí)行命令，則結(jié)果如圖所示。執(zhí)行命令，則結(jié)果如圖所示。2. 2. 應用應用任務描述任務描述 【例【例5.175.17】查詢】查詢“課程表課程表”中備注為非中備注為非nullnull的課程信息。的課程信息。select * from 課程表課程表 where 備注備注 is not null執(zhí)行命令，則結(jié)果如圖所示。執(zhí)行命令，則結(jié)果如圖所示。注意：觀察注意：觀察is null及及is not null的用的用法，這里法，這里is運算符運算符不能用不能用“=”來代替，來代替，is not也不能用也不能用“”或或“!=”來代來代替。替

28、。2. 2. 應用應用任務描述任務描述 Li Lik ke,Not Likee,Not Like運算符運算符有時查詢數(shù)據(jù)時，不知道查詢的范圍，只知道查詢的模式。這個時候，經(jīng)常有時查詢數(shù)據(jù)時，不知道查詢的范圍，只知道查詢的模式。這個時候，經(jīng)常用到用到LikeLike，Not LikeNot Like，其格式為：，其格式為：列名列名 Not Like Not Like 表表5-7 Like 匹配通配符及說明匹配通配符及說明通配符通配符說說 明明_表示任意單個字符。例如表示任意單個字符。例如a_b表示以表示以a開頭，以開頭，以b結(jié)尾的結(jié)尾的長度為長度為3的任意字符串。的任意字符串。例如：例如：adb

29、，afb等。等。%表示任意長度的字符（長度可以是表示任意長度的字符（長度可以是0的字符串）。例如的字符串）。例如a%b表表示示以以a開頭，以開頭，以b結(jié)屬的任意長度的字符串結(jié)屬的任意長度的字符串。例如：。例如：ab，acb，asdfb等。等。 表示方括號中列出的任意一個字符。例如：表示方括號中列出的任意一個字符。例如：asdfg，表示，表示a，s，d，f，g中的任意一個中的任意一個。也可以是字符范圍，例如。也可以是字符范圍，例如abcdef同同a-f的含義一樣。的含義一樣。表示不在方括號中列出的任意一個字符。例如：表示不在方括號中列出的任意一個字符。例如：asdfg之外之外的任意字符。的任意字

30、符。2. 2. 應用應用任務描述任務描述 【例【例5.185.18】查詢】查詢“學生表學生表”中姓中姓“李李”的同學的信息。的同學的信息。select * from 學生表學生表 where 姓名姓名 like 李李%執(zhí)行命令，則結(jié)果如圖所示。執(zhí)行命令，則結(jié)果如圖所示。說明：通配符說明：通配符字符串字符串李李%表示是第一個表示是第一個漢字是漢字是“李李”的字符串。的字符串。2. 2. 應用應用任務描述任務描述 【例【例5.195.19】查詢】查詢“學生表學生表”中姓名中間一個是中姓名中間一個是“明明”的同學的信息。的同學的信息。select * from 學生表學生表 where 姓名姓名 l

31、ike _明明_執(zhí)行命令，則結(jié)果如圖執(zhí)行命令，則結(jié)果如圖5-205-20所示。所示。說明：通配符字說明：通配符字符串符串 _明明_表示表示是中間漢字是是中間漢字是“明明”的字符串。的字符串。2. 2. 應用應用任務描述任務描述 【例【例5.205.20】查詢】查詢“學生表學生表”中姓名中間一個是中姓名中間一個是“海?！被蚧颉敖ńā钡耐瑢W的信息。的同學的信息。select * from 學生表學生表 where 姓名姓名 like _海建海建_執(zhí)行命令，則結(jié)果如圖所示。執(zhí)行命令，則結(jié)果如圖所示。說明：通配符字說明：通配符字符串符串_海建海建_表表示中間漢字是示中間漢字是“海?！被蚧颉敖ńā钡淖址?/p>

32、串。的字符串。2. 2. 應用應用任務描述任務描述 【例【例5.215.21】查詢】查詢“學生表學生表”中不姓中不姓“李李”、“張張”、“王王”、“陳陳”的同學的信息。的同學的信息。select * from 學生表學生表 where 姓名姓名 like 李張王陳李張王陳%或：或：select * from 學生表學生表 where 姓名姓名 not like 李張王陳李張王陳%執(zhí)行命令，則結(jié)果如圖所示。執(zhí)行命令，則結(jié)果如圖所示。說明：通配符字符串說明：通配符字符串李張王陳李張王陳%表示第一個漢字不是表示第一個漢字不是“李李”或或“張張”或或“王王”或或“陳陳”的字符串。的字符串。2. 2.

33、應用應用（8 8）對查詢的結(jié)果進行排序）對查詢的結(jié)果進行排序可以使用可以使用Order byOrder by子句對查詢結(jié)果按照一個或多個屬性列的升序子句對查詢結(jié)果按照一個或多個屬性列的升序(ASC)(ASC)或降序或降序(DESC)(DESC)排序，默認為升序。如果不使用排序，默認為升序。如果不使用order byorder by子句，則結(jié)果集按照記錄在表中子句，則結(jié)果集按照記錄在表中順序排序。順序排序。Order byOrder by子句的語法格式如下：子句的語法格式如下：Order by Order by 列名列名 ASC|DESC,nASC|DESC,n【例例5.22】查詢查詢“學生表學

34、生表”中應用中應用電子專業(yè)同學的信息，按出生年月電子專業(yè)同學的信息，按出生年月降序排序。降序排序。select * from 學生表學生表 where 專業(yè)專業(yè)=應用電子應用電子 order by 出生年月出生年月 desc執(zhí)行命令，則結(jié)果如圖所示。執(zhí)行命令，則結(jié)果如圖所示。說明：說明：order by 出生年月出生年月 desc 是按是按出生年月的降序排序。出生年月的降序排序。2. 2. 應用應用任務描述任務描述 【例【例5.235.23】查詢】查詢“學生表學生表”應用電子專業(yè)和應用英語專業(yè)同學的信息，要求先應用電子專業(yè)和應用英語專業(yè)同學的信息，要求先按性別排序（升序），再按出生年月降序排序

35、。按性別排序（升序），再按出生年月降序排序。select * from 學生表學生表 where 專業(yè)專業(yè) in(應用電子應用電子,應用英語應用英語) order by 性別性別 asc,出生年月出生年月 desc其中：其中：order by 性別性別 asc,出生年月出生年月 desc表示表示是先按性別升序，再按出是先按性別升序，再按出生年月降序排序生年月降序排序5.1.2 5.1.2 對數(shù)據(jù)進行統(tǒng)計對數(shù)據(jù)進行統(tǒng)計任務描述任務描述 【例【例5.245.24】計算】計算“選課表選課表”中課中課程號為程號為10011001課程的最低分、最高分同學的信息。課程的最低分、最高分同學的信息。selec

36、t min(select min(成績成績),max(),max(成績成績) from ) from 選課表選課表 where where 課程號課程號=1001=1001執(zhí)行命令，則結(jié)果如圖所示。執(zhí)行命令，則結(jié)果如圖所示。1使用聚合函數(shù)進行查詢使用聚合函數(shù)進行查詢用戶經(jīng)常需要對結(jié)果集進行統(tǒng)計，例如求和、平均、最大值、最小值、個數(shù)等，這用戶經(jīng)常需要對結(jié)果集進行統(tǒng)計，例如求和、平均、最大值、最小值、個數(shù)等，這些統(tǒng)計可能通過聚合函數(shù)來實現(xiàn)。些統(tǒng)計可能通過聚合函數(shù)來實現(xiàn)。這里這里min()、max()就是聚合函數(shù)，常用的聚合函就是聚合函數(shù)，常用的聚合函數(shù)如表數(shù)如表5-8所示。因為所示。因為min(成

37、績成績)、max(成績成績)是是計算得到的列，所以顯示是計算得到的列，所以顯示是“無列名無列名”可以給定一個別名，如：可以給定一個別名，如：select min(成績成績)as 最低分最低分,max(成績成績)as 最高分最高分 from 選選課表課表 where 課程號課程號=10012. 2. 應用應用任務描述任務描述 【例【例5.255.25】查詢】查詢“選課表選課表”中課程號為中課程號為10011001課程的平均成績。課程的平均成績。select AVG(成績成績) as 平均成績平均成績 from 選課表選課表 where 課程號課程號=1001執(zhí)行命令，則結(jié)果如圖執(zhí)行命令，則結(jié)果如

38、圖所示。所示。2. 2. 應用應用任務描述任務描述 【例例5.265.26】查詢查詢“教師表教師表”中教師總?cè)藬?shù)及有聯(lián)系電話的教師人數(shù)。中教師總?cè)藬?shù)及有聯(lián)系電話的教師人數(shù)。select count(*) as 總?cè)藬?shù)總?cè)藬?shù),count(聯(lián)系電話聯(lián)系電話) as 有聯(lián)系電話人數(shù)有聯(lián)系電話人數(shù) from 教師表教師表執(zhí)行命令，則結(jié)果如圖所示。執(zhí)行命令，則結(jié)果如圖所示。說明：說明：count(*) as 總?cè)藬?shù)：表示總記總?cè)藬?shù)：表示總記錄數(shù)；錄數(shù)；count(聯(lián)系電話聯(lián)系電話) as 有聯(lián)系電有聯(lián)系電話人數(shù)：表示聯(lián)系電話不為話人數(shù)：表示聯(lián)系電話不為null的總的總記錄數(shù)。記錄數(shù)。5.1.2 5.1.

39、2 對數(shù)據(jù)進行統(tǒng)計對數(shù)據(jù)進行統(tǒng)計任務描述任務描述 （1 1）格式：）格式：SELECT SELECT FROM FROM WHERE WHERE Group By Group By 列名列名 Having Having 2對結(jié)果進行分組對結(jié)果進行分組有時，要統(tǒng)計不同類別的數(shù)據(jù)，例如統(tǒng)計選課表中每門課程的最低分，最高分，平有時，要統(tǒng)計不同類別的數(shù)據(jù)，例如統(tǒng)計選課表中每門課程的最低分，最高分，平均分，或每個同學的平均分、總分等等，這時就要用到均分，或每個同學的平均分、總分等等，這時就要用到Group By子句。子句。Group By子句是將查詢結(jié)果集按某一列或多列值分組，并對每一組進行統(tǒng)計。子句是

40、將查詢結(jié)果集按某一列或多列值分組，并對每一組進行統(tǒng)計。其中：其中：Group By 列名：是按列名指定的字段進行分組，將該字段值相同的記錄組成一列名：是按列名指定的字段進行分組，將該字段值相同的記錄組成一組，對每一組記錄進行匯總統(tǒng)計并生成一列記錄。組，對每一組記錄進行匯總統(tǒng)計并生成一列記錄。注意：注意：SELECT 的列名必須是的列名必須是Group By 列名已有的列名或計算列。列名已有的列名或計算列。（2 2） 應用應用任務描述任務描述 【例例5.275.27】查詢查詢“選課表選課表”中每個同學的平均分。中每個同學的平均分。select 學號學號,AVG(成績成績) as 平均分平均分 f

41、rom 選課表選課表 group by 學號學號執(zhí)行命令，則結(jié)果如圖所示。執(zhí)行命令，則結(jié)果如圖所示。說明：因為是求每個同學的平均分，說明：因為是求每個同學的平均分，所以是按學號分組的，即所以是按學號分組的，即Group by 學號，因而學號，因而Select 子句中的出子句中的出現(xiàn)的列名必須是現(xiàn)的列名必須是Group By中出現(xiàn)中出現(xiàn)的學號及平均分。的學號及平均分。（2 2） 應用應用任務描述任務描述 【例【例5.285.28】查詢】查詢“選課表選課表”中每個同學的最高分、最低分、平均分。中每個同學的最高分、最低分、平均分。select 學號學號,MAX(成績成績) as 最高分最高分,MIN

42、(成績成績) as 最低分最低分,AVG(成績成績) as 平均平均分分 from 選課表選課表 group by 學號學號執(zhí)行命令，則結(jié)果如圖所示。執(zhí)行命令，則結(jié)果如圖所示。（2 2） 應用應用任務描述任務描述 【例【例5.305.30】查詢】查詢“選課表選課表”中平均分高于中平均分高于7575分的每分的每門課程門課程的最高分、最低分、總的最高分、最低分、總分及平均分，并按平均分的高低進行排序分及平均分，并按平均分的高低進行排序 ( (升序升序) )。select 課程號課程號, MAX(成績成績) as 最高分最高分, MIN(成績成績) as 最低分最低分, 總分總分=sum(成績成績)

43、, AVG(成績成績) as 平均分平均分 from 選課表選課表 group by 課程號課程號 having AVG(成績成績)=75 order by AVG(成績成績)說明：因為是對計算列的結(jié)果進行說明：因為是對計算列的結(jié)果進行篩選，所以要用到篩選，所以要用到Having AVG(成成績績)=75，不能用，不能用where AVG(成成績績)=75。同時要對平均分進行排。同時要對平均分進行排序，所以用到序，所以用到Order by avg(成績成績)。（2 2） 應用應用任務描述任務描述 【例例5.31】查詢查詢“選課表選課表”中每門課選課的人數(shù)及每門課已有成績的人數(shù)。中每門課選課的人

44、數(shù)及每門課已有成績的人數(shù)。select 課程號課程號, COUNT(成績成績) 有分數(shù)的人數(shù)有分數(shù)的人數(shù), COUNT(*) 選課的人數(shù)選課的人數(shù) from 選課表選課表 group by 課程號課程號說明：因為是統(tǒng)計每門課選課的人說明：因為是統(tǒng)計每門課選課的人數(shù)及每門課已有成績的人數(shù)，所以數(shù)及每門課已有成績的人數(shù)，所以是按課程號分組的，即是按課程號分組的，即Group by 課程號，選課的人數(shù)中可能有成績，課程號，選課的人數(shù)中可能有成績，也有可能是無成績，所以要用也有可能是無成績，所以要用Count(*)，而選課中已有成績的人，而選課中已有成績的人數(shù)，則要用到數(shù)，則要用到Count(成績成績

45、)。（2 2） 應用應用任務描述任務描述 【例【例5.32】查詢】查詢“選課表選課表”中每個學生選課門數(shù)及已有成績課程門數(shù)。中每個學生選課門數(shù)及已有成績課程門數(shù)。select 學號學號, COUNT(*) 選課門數(shù)選課門數(shù), COUNT(成績成績) 已有成績選課門數(shù)已有成績選課門數(shù) from 選課表選課表 group by 學號學號說明：因為是統(tǒng)計選課表中每個學說明：因為是統(tǒng)計選課表中每個學生選課門數(shù)及已有成績課程門數(shù)，生選課門數(shù)及已有成績課程門數(shù)，所以是按學號分組的，即所以是按學號分組的，即Group by 學號。選課門數(shù)僅指學生所選的課，學號。選課門數(shù)僅指學生所選的課，可能有成績，可能無成

46、績，所以要可能有成績，可能無成績，所以要用用Count(*)，而已有成績的選課門，而已有成績的選課門數(shù)，則要用到數(shù)，則要用到Count(成績成績)。（2 2） 應用應用任務描述任務描述 【例例5.33】查詢查詢“選課表選課表”中每個學生選課門數(shù)已超過中每個學生選課門數(shù)已超過4門（含門（含4門）的學生門）的學生的學號與課程數(shù)。的學號與課程數(shù)。select 學號學號,COUNT(*) 選課門數(shù)選課門數(shù) from 選課表選課表 group by 學號學號 having COUNT(*)=4說明：選課門數(shù)是統(tǒng)計值，所以已說明：選課門數(shù)是統(tǒng)計值，所以已超過超過4門的條件該用：門的條件該用：having

47、COUNT(*)=45.1.2 5.1.2 對數(shù)據(jù)進行統(tǒng)計對數(shù)據(jù)進行統(tǒng)計任務描述任務描述 （1 1）格式）格式COMPUTECOMPUTE聚集函數(shù)聚集函數(shù) By By 列名列名 3顯示詳細清單的查詢（顯示詳細清單的查詢（COMPUTE 子句）子句）COMPUTE子句對查詢結(jié)果的所有記錄進行匯總統(tǒng)計，并顯示所有參加匯總記錄的子句對查詢結(jié)果的所有記錄進行匯總統(tǒng)計，并顯示所有參加匯總記錄的詳細信息。其語法格式為：詳細信息。其語法格式為：其中：其中：聚集函數(shù)指的是聚集函數(shù)指的是sum()、avg()、Max()、Min()、Count()等等By 列名按指定列名按指定“列名列名”字段進行分組計算，并顯

48、示被統(tǒng)計記錄的詳細信息。字段進行分組計算，并顯示被統(tǒng)計記錄的詳細信息。By選項必須與選項必須與order By子句一起使用。子句一起使用。（2 2） 應用應用任務描述任務描述 【例【例5.345.34】查詢】查詢“選課表選課表”中所有成績的最高分、最低分、平均分。并顯示詳中所有成績的最高分、最低分、平均分。并顯示詳細清單。細清單。select select * * from from 選課表選課表 compute MAX( compute MAX(成績成績),min(),min(成績成績),avg(),avg(成績成績) )說明：因為是所有記錄中的最說明：因為是所有記錄中的最高分、最低分、平均

49、分，所以高分、最低分、平均分，所以不用進行排序，故不用加不用進行排序，故不用加Order by子句，后面的子句，后面的By選項自然也選項自然也沒有。沒有。（2 2） 應用應用任務描述任務描述 【例【例5.355.35】查詢】查詢“選課表選課表”中每門課程的最高分、最低分、平均分。并顯示中每門課程的最高分、最低分、平均分。并顯示詳細清單。詳細清單。select select * * from from 選課表選課表 order by order by 課程號課程號 compute MAX( compute MAX(成績成績),min(),min(成成績績),avg(),avg(成績成績) by

50、) by 課程號課程號說明：說明： compute by與與group by的區(qū)別的區(qū)別在于前者既顯示統(tǒng)計記錄又顯示詳在于前者既顯示統(tǒng)計記錄又顯示詳細記錄，后者僅顯示分組統(tǒng)計的匯細記錄，后者僅顯示分組統(tǒng)計的匯總記錄?？傆涗?。 compute by之前要使用之前要使用order by子句，原因是必須先按分類字段子句，原因是必須先按分類字段排序之后才能使用排序之后才能使用compute by子句子句進行分類匯總。本題是按課程號排進行分類匯總。本題是按課程號排序。序。 compute by與與group by使用的使用的語法格式不一樣。語法格式不一樣。（2 2） 應用應用任務描述任務描述 【例【例5

51、.365.36】查詢】查詢“選課表選課表”中每個同學所參加的課程成績中的最高分、最低中每個同學所參加的課程成績中的最高分、最低分、平均分。并顯示詳細清單。分、平均分。并顯示詳細清單。select select * * from from 選課表選課表 order by order by 學號學號 compute MAX( compute MAX(成績成績),min(),min(成成績績),avg(),avg(成績成績) by ) by 學號學號因為是要查詢每個同學幾門課因為是要查詢每個同學幾門課中的統(tǒng)計值，所以要按學號進中的統(tǒng)計值，所以要按學號進行排序。行排序。5.2 5.2 學生成績管理數(shù)據(jù)

52、庫多表查詢學生成績管理數(shù)據(jù)庫多表查詢在實際的應用系統(tǒng)中，多數(shù)情況下用戶需要查詢的信息來自多表。例如查詢姓在實際的應用系統(tǒng)中，多數(shù)情況下用戶需要查詢的信息來自多表。例如查詢姓名為名為“李小明李小明”同學的成績單。因為選課表中只有同學的成績單。因為選課表中只有“學號學號”、“課程號課程號”及及“成績成績”這三個字段，而無這三個字段，而無“姓名姓名”這個字段，因而，查詢的步驟是先從這個字段，因而，查詢的步驟是先從“學學生表生表”中查到姓名為中查到姓名為“李小明李小明”同學的學號，然后根據(jù)所查到的學號，從同學的學號，然后根據(jù)所查到的學號，從“選選課表課表”中再查相應的數(shù)據(jù)，這樣就涉及兩個表的查詢。下面

53、，我們來介紹兩個中再查相應的數(shù)據(jù)，這樣就涉及兩個表的查詢。下面，我們來介紹兩個或兩個以上的表的查詢，即高級查詢?；騼蓚€以上的表的查詢，即高級查詢。任務描述任務描述 5.2.1 連接查詢連接查詢涉及二個或二個以上表的查詢稱作多表連接查詢，簡稱涉及二個或二個以上表的查詢稱作多表連接查詢，簡稱連接查詢連接查詢。連接查詢有：。連接查詢有：謂詞連接、內(nèi)連接、外連接等等。謂詞連接、內(nèi)連接、外連接等等。1謂詞連接謂詞連接在在select語句的語句的where子句中使用比較運算符給出連接條件，作為多表連接的表示子句中使用比較運算符給出連接條件，作為多表連接的表示形式，稱作謂詞連接。形式，稱作謂詞連接。（1）格

54、式）格式Select from 表表1，表，表2 ，表表n where 子句子句（2 2） 應用應用任務描述任務描述 【例【例5.375.37】查詢每個同學的選課情況。】查詢每個同學的選課情況。分析：因為沒有指定顯示哪些列，所以就認為顯示所有列，即用分析：因為沒有指定顯示哪些列，所以就認為顯示所有列，即用“* *”表示；表示；又因為有學生的信息，所以要用到又因為有學生的信息，所以要用到“學生表學生表”，同時還需要顯示選課的情，同時還需要顯示選課的情況，所以要用到?jīng)r，所以要用到“選課表選課表”，這二個表之間可以用，這二個表之間可以用“學號學號”進行聯(lián)系。在進行聯(lián)系。在查詢編輯器中輸入：查詢編輯器

55、中輸入：select * from 學生表學生表,選課表選課表 where 學生表學生表.學號學號=選課表選課表.學號學號說明：結(jié)果表包含說明：結(jié)果表包含“學生表學生表”和和“選課選課表表”中的所有列。這里的連接謂詞就是中的所有列。這里的連接謂詞就是where子句中的字段，即子句中的字段，即“學生表學生表”中中的學號和的學號和“選課表選課表”中的學號。中的學號。（2 2） 應用應用任務描述任務描述 【例例5.38】查詢每個同學的成績情況。結(jié)果表中顯示：學號、姓名、課程號、查詢每個同學的成績情況。結(jié)果表中顯示：學號、姓名、課程號、成績。成績。分析：要顯示分析：要顯示“學號學號”、“姓名姓名”字段

56、，要用到字段，要用到“學生表學生表”，而要顯示，而要顯示“課程課程號號”、“成績成績”，則要用到，則要用到“選課表選課表”，又因為結(jié)果表中顯示指定字段，所以，又因為結(jié)果表中顯示指定字段，所以要規(guī)定表列。要規(guī)定表列。在查詢編輯器中輸入：在查詢編輯器中輸入：select 學生表學生表.學號學號,學生表學生表.姓名姓名,選課表選課表.課程號課程號,選課表選課表.成績成績from 學生表學生表,選課選課表表 where 學生表學生表.學號學號=選課表選課表.學號學號注意：在注意：在select語句中將所要語句中將所要顯示的字段前注明了表名，例：顯示的字段前注明了表名，例：學生表學生表.學號等，但對二個

57、表中學號等，但對二個表中唯一出現(xiàn)的字段，則可以省略唯一出現(xiàn)的字段，則可以省略前綴表名，例如：前綴表名，例如：“學生表學生表”和和“選課表選課表”二個表中均有二個表中均有“學號學號”，所以需要加前綴表，所以需要加前綴表名，而姓名、課程號、成績是名，而姓名、課程號、成績是各表中唯一的，所以可以不加各表中唯一的，所以可以不加前綴，當然，加上前綴也行。前綴，當然，加上前綴也行。 （2 2） 應用應用任務描述任務描述 【例例5.39】查詢每個同學的成績情況。結(jié)果表中顯示：學號、姓名、課名、成績。查詢每個同學的成績情況。結(jié)果表中顯示：學號、姓名、課名、成績。分析：因為要顯示分析：因為要顯示“姓名姓名”，則

58、要用到，則要用到“學生表學生表”；要顯示；要顯示“課名課名”，則需要用到，則需要用到“課程表課程表”；而要顯示；而要顯示“成績成績”，則要用到，則要用到“選課表選課表”。“學生表學生表”和和“選課表選課表”通過通過“學號學號”聯(lián)系，聯(lián)系，“選課表選課表”與與“課程表課程表”通過通過“課程號課程號”聯(lián)系。聯(lián)系。所以在查詢編輯器中輸入代碼：所以在查詢編輯器中輸入代碼：select a.學號學號,姓名姓名,課名課名,成績成績 from 學生表學生表 a,選課表選課表 b,課程表課程表 cwhere a.學號學號=b.學號學號 and b.課程號課程號=c.課程號課程號注意：注意：“學生表學生表”和和

59、“選選課表課表”中根據(jù)中根據(jù)“學號學號”這這個謂詞連接，而個謂詞連接，而“選課表選課表”和和“課程表課程表”則是根據(jù)則是根據(jù)“課程號課程號”這個謂詞連接。這個謂詞連接。要顯示的列來自三個表，要顯示的列來自三個表，所以就要三個表兩兩連接。所以就要三個表兩兩連接。（2 2） 應用應用任務描述任務描述 【例例5.40】查詢選修查詢選修“大學英語大學英語”、“計算機基礎(chǔ)計算機基礎(chǔ)”課的同學的成績，要課的同學的成績，要求顯示：學號、姓名、課程名、成績。求顯示：學號、姓名、課程名、成績。select a.學號學號,姓名姓名,課名課名,成績成績 from 學學生表生表 a,選課表選課表 b,課程表課程表 c

60、 where a.學號學號=b.學號學號 and b.課程號課程號=c.課程號課程號 and (課名課名=大學英語大學英語 or 課名課名=計算機基礎(chǔ)計算機基礎(chǔ))【例例5.41】查詢老師的課表信息，要查詢老師的課表信息，要求顯示：教師號、教師姓名、課名、求顯示：教師號、教師姓名、課名、學時、學分。學時、學分。select a.教師號教師號,姓名姓名,課名課名,學時學時, 學分學分 from 教師表教師表 a,課程表課程表 b,授課表授課表 cwhere a.教師號教師號=c.教師號教師號 and b.課程課程號號 =c.課程號課程號（2 2） 應用應用任務描述任務描述 【例【例5.42】查詢講