任務(wù)4從學(xué)生成績(jī)管理數(shù)據(jù)庫(kù)中獲取所感興趣的信息

1、基于工作任務(wù)的基于工作任務(wù)的SQL Server 2008SQL Server 2008數(shù)據(jù)庫(kù)應(yīng)用數(shù)據(jù)庫(kù)應(yīng)用學(xué)生成績(jī)管理系統(tǒng)學(xué)生成績(jī)管理系統(tǒng) 任務(wù)任務(wù)4 從學(xué)生成績(jī)管理數(shù)據(jù)庫(kù)中獲取所感興趣的信息從學(xué)生成績(jī)管理數(shù)據(jù)庫(kù)中獲取所感興趣的信息任務(wù)目標(biāo)p會(huì)使用簡(jiǎn)單查詢語(yǔ)句p會(huì)使用TOP、ORDER BY 子句實(shí)現(xiàn)查詢排序p會(huì)使用各種函數(shù)p會(huì)使用模糊查詢（Like、Between、In）p會(huì)使用聚集函數(shù)（Sum、Avg、Count、Min、Max）實(shí)現(xiàn)對(duì)數(shù)據(jù)集合的運(yùn)算p會(huì)使用GROUP BY 關(guān)鍵字實(shí)現(xiàn)分組查詢p會(huì)使用連接查詢（內(nèi)連接，外連接，全連接）p查詢所有的學(xué)號(hào)、姓名以及班級(jí)p查詢某個(gè)學(xué)生的基本信息

2、p查詢出成績(jī)?yōu)榍叭膶W(xué)生信息p查詢出成績(jī)?yōu)榍鞍俜种膶W(xué)生信息p查詢出成績(jī)?cè)?0分到90分之間的學(xué)生信息p查詢出所有學(xué)生的平均成績(jī)p查詢每一門課的平均成績(jī)或者某個(gè)人的所有課的總成績(jī)p查詢參加考試的學(xué)生姓名，課程名以及相應(yīng)的成績(jī)p查詢?nèi)繉W(xué)生的考試信息客戶需求客戶的要求要用到數(shù)據(jù)查詢功能，要使用SELECT語(yǔ)句p使用簡(jiǎn)單SELECT語(yǔ)句查詢學(xué)生基本信息p使用 TOP、ORDER BY 子句實(shí)現(xiàn)學(xué)生信息查詢排序p使用模糊查詢（Like、Between、In）限制查詢范圍p使用聚集函數(shù)（Sum、Avg、Count、Min、Max）統(tǒng)計(jì)分析學(xué)生成績(jī)p使用GROUP BY子句實(shí)現(xiàn)對(duì)學(xué)生信息的分組查詢

3、p使用連接查詢分布在多個(gè)表中的學(xué)生信息任務(wù)分析簡(jiǎn)單的簡(jiǎn)單的SELECT SELECT 語(yǔ)句語(yǔ)句的查詢的查詢SELECT * FROM Students使用使用WHEREWHERE子句查詢子句查詢SELECT * from students where Sname=張三任務(wù)實(shí)施-【信息收集】限制固定行數(shù)的查詢限制固定行數(shù)的查詢SELECT TOP 5 SName, SAddressFROM Students WHERE SSex = 0返回百分之多少行返回百分之多少行SELECT TOP 20 PERCENT SName, SAddressFROM Students WHERE SSex = 0

4、任務(wù)實(shí)施-【信息收集】按多列排序查詢按多列排序查詢SELECT StudentID As 學(xué)員編號(hào), Score As 成績(jī)FROM ScoreWHERE Score60ORDER BY Score,CourseID任務(wù)實(shí)施-【信息收集】排序中的字段，可以使用表達(dá)式嗎？p如果不可以，請(qǐng)說(shuō)明原因；p如果可以，請(qǐng)舉例說(shuō)明；字符串函數(shù)字符串函數(shù)任務(wù)實(shí)施-【信息收集】函數(shù)名描述舉例返回值CHARINDEX用來(lái)尋找一個(gè)指定的字符串在另一個(gè)字符串中的起始位置SELECT CHARINDEX(BDDY,MyBDDY Course, 1 )3LEN返回傳遞給它的字符串長(zhǎng)度SELECT LEN(SQL Serv

5、er課程) 12LOWER把傳遞給它的字符串轉(zhuǎn)換為小寫SELECT LOWER(SQL Server課程) sql server課程UPPER把傳遞給它的字符串轉(zhuǎn)換為大寫SELECT UPPER(sql server課程)SQL SERVER課程字符串函數(shù)字符串函數(shù)任務(wù)實(shí)施-【信息收集】函數(shù)名描述舉例返回值LTRIM清除字符左邊的空格SELECT LTRIM ( 劉德華 )劉德華 （后面的空格保留）RTRIM清除字符右邊的空格SELECT RTRIM ( 劉德華 )劉德華（前面的空格保留）RIGHT從字符串右邊返回指定數(shù)量的字符SELECT RIGHT(買賣提.吐?tīng)査?3)吐?tīng)査蒖EPLACE

6、替換一個(gè)字符串中的字符SELECT REPLACE(莫樂(lè)可切.楊可,可,蘭) 莫樂(lè)蘭切.楊蘭STUFF在一個(gè)字符串中，刪除指定長(zhǎng)度的字符，并在該位置插入一個(gè)新的字符串SELECT STUFF(ABCDEFG, 2, 3, 我的音樂(lè)我的世界)A我的音樂(lè)我的世界EFG日期函數(shù)日期函數(shù)任務(wù)實(shí)施-【信息收集】函數(shù)名描述 舉例返回值GETDATE取得當(dāng)前的系統(tǒng)日期SELECT GETDATE()今天的日期DATEADD將指定的數(shù)值添加到指定日期部分后的日期SELECT DATEADD(m,3,7-22-2008)2008-10-22 00:00:00.000DATEDIFF兩個(gè)日期之間的指定日期部分的差

7、值SELECT DATEDIFF(m,1-01-2011, 5-01-2011)4DATENAME日期中指定日期部分的字符串形式SELECT DATENAME(dw,01/01/2011)星期六DATEPART日期中指定日期部分的整數(shù)形式SELECT DATEPART(day, 01/15/2011)15數(shù)學(xué)函數(shù)數(shù)學(xué)函數(shù)任務(wù)實(shí)施-【信息收集】函數(shù)名描述舉例返回值A(chǔ)BS返回?cái)?shù)值表達(dá)式的絕對(duì)值SELECT ABS(-43) 43CEILING返回大于或等于所給數(shù)字表達(dá)式的最小整數(shù)SELECT CEILING(43.5) 44FLOOR返回小于或等于指定表達(dá)式的最大整數(shù)SELECT FLOOR(43

8、.5) 43POWER返回?cái)?shù)值表達(dá)式的冪值SELECT POWER(5,2) 25ROUND將數(shù)值表達(dá)式四舍五入為指定精度SELECT ROUND(43.543,1) 43.5Sign正數(shù)返回+1，負(fù)數(shù)返回-1，0則返回0SELECT SIGN(-43) -1Sqrt返回浮點(diǎn)表達(dá)式的平方根SELECT SQRT(9) 3系統(tǒng)函數(shù)系統(tǒng)函數(shù)任務(wù)實(shí)施-【信息收集】函數(shù)名描述舉例返回值CONVERT用來(lái)轉(zhuǎn)變數(shù)據(jù)類型S E L E C T C O N V E R T (VARCHAR (5),12345)字符串12345CURRENT_USER 返回當(dāng)前用戶的名字SELECT CURRENT_USER你

9、登錄的用戶名DATALENGTH返回用于指定表達(dá)式的字節(jié)數(shù)SELECT DATALENGTH (中國(guó)A盟)7HOST_NAME返回當(dāng)前用戶所登錄的計(jì)算機(jī)名SELECT HOST_NAME()你所登錄的計(jì)算機(jī)的名SYSTEM_USER 返回當(dāng)前所登錄的用戶名SELECT SYSTEM_USER你當(dāng)前所登錄的用戶USER_NAME從給定的用戶I D返回用戶名SELECT USER_NAME(1)從任意數(shù)據(jù)庫(kù) 中 返 回“dbo”SELECT SName As 姓名 SAddress AS 地址 FROM Students WHERE SAddress IS NULL姓名地址張果老NULL李尋歡NU

10、LL令狐沖NULL程靈素NULL猜一猜：把Students表中某些行的SAddress字段值刪掉后：p 使用IS NULL能查詢出來(lái)這些數(shù)據(jù)行嗎？p 怎么查詢出這些行來(lái)？模糊查詢模糊查詢IS NULLIS NULL任務(wù)實(shí)施-【信息收集】SELECT StudentID, Score FROM SCore WHERE Score BETWEEN 60 AND 80StudentIDScore77786111761764模糊查詢模糊查詢BETWEENBETWEEN任務(wù)實(shí)施-【信息收集】p 把某一字段中內(nèi)容在特定范圍內(nèi)的記錄查詢出來(lái)SELECT SName AS 學(xué)員姓名,SAddress As 地

11、址 FROM Students WHERE SAddress IN (北京,廣州,上海)學(xué)員姓名地址李 揚(yáng)廣州于紫電上海李青霜北京司馬弓上海模糊查詢模糊查詢ININ任務(wù)實(shí)施-【信息收集】SUMSUM函數(shù)函數(shù)SELECT SUM(score) as 總成績(jī) FROM ScoreAVGAVG函數(shù)函數(shù)SELECT AVG(SCore) AS 平均成績(jī) From Score WHERE Score =60聚合函數(shù)聚合函數(shù)任務(wù)實(shí)施-【信息收集】MAXMAX、MINMIN函數(shù)函數(shù)SELECT AVG(SCore) AS 平均成績(jī), MAX (Score) AS 最高分, MIN (Score) AS 最低

12、分 From Score WHERE Score =60COUNTCOUNT函數(shù)函數(shù)SELECT COUNT (*) AS 及格人數(shù) From Score WHERE Score=60聚合函數(shù)聚合函數(shù)任務(wù)實(shí)施-【信息收集】第一門課6個(gè)成績(jī)?nèi)∑骄档诙T課6個(gè)成績(jī)?nèi)∑骄档谌T課5個(gè)成績(jī)?nèi)∑骄捣纸M匯總分組匯總?cè)蝿?wù)實(shí)施-【信息收集】SELECT CourseID, AVG(Score) AS 課程平均成績(jī)FROM ScoreGROUP BY CourseID分組匯總分組匯總?cè)蝿?wù)實(shí)施-【信息收集】怎么樣來(lái)統(tǒng)計(jì)每次的內(nèi)部測(cè)試不同學(xué)員的成績(jī)？第一次內(nèi)部測(cè)試不及格第二次內(nèi)部測(cè)試不及格第三次內(nèi)部測(cè)試不及格

13、補(bǔ)考成績(jī)多列分組匯總多列分組匯總?cè)蝿?wù)實(shí)施-【信息收集】?jī)?nèi)部測(cè)試成績(jī)還要把這個(gè)加上取平均StudentID和CourseID的組合存在重復(fù)需要按照這兩個(gè)來(lái)進(jìn)行分組，避免StudentID和CourseID 同時(shí)一樣多列分組匯總多列分組匯總?cè)蝿?wù)實(shí)施-【信息收集】SELECT StudentID AS 學(xué)員編號(hào),CourseID AS 內(nèi)部測(cè)試, AVG(Score) AS 內(nèi)部測(cè)試平均成績(jī)FROM ScoreGROUP BY StudentID,CourseID多列分組匯總多列分組匯總?cè)蝿?wù)實(shí)施-【信息收集】SELECT StudentID AS 學(xué)員編號(hào),CourseID AS 內(nèi)部測(cè)試, AVG

14、(Score) AS 內(nèi)部測(cè)試平均成績(jī)FROM ScoreGROUP BY StudentID,CourseIDHAVING COUNT(Score)10分組查詢分組查詢HAVINGHAVING任務(wù)實(shí)施-【信息收集】SELECT S.SName,C.CourseID,C.Score From Score AS CINNER JOIN Students AS SON C.StudentID = S.SCodeScore122300100100200297896776300381猜一猜：這樣寫，返回的查詢結(jié)果是一樣的嗎？SELECT S.SName,C.CourseID,C.Score From

15、Students AS SINNER JOIN Score AS CON C.StudentID = S.SCode再猜一猜：以下返回多少行？SELECT S.SName,C.CourseID,C.Score From Students AS SINNER JOIN Score AS CON C.StudentID S.SCodeStundents梅超風(fēng)陳玄風(fēng)陸乘風(fēng)曲靈風(fēng)1234查詢結(jié)果梅超風(fēng)陳玄風(fēng)陳玄風(fēng)陸乘風(fēng)00100100200297896776陸乘風(fēng)00381多表聯(lián)查多表聯(lián)查內(nèi)連接內(nèi)連接任務(wù)實(shí)施-【信息收集】SELECT Students.SName, Score.CourseID, S

16、core.ScoreFROM Students,ScoreWHERE Students.SCode = Score.StudentID多表聯(lián)查多表聯(lián)查內(nèi)連接內(nèi)連接任務(wù)實(shí)施-【信息收集】SELECT S.SName AS 姓名, CS.CourseName AS 課程, C.Score AS 成績(jī)FROM Students AS S INNER JOIN Score AS C ON (S.SCode = C.StudentID)INNER JOIN Course AS CS ON (CS.CourseID = C.CourseID)多表聯(lián)查多表聯(lián)查三表連接三表連接任務(wù)實(shí)施-【信息收集】Score

17、122300100100200297896776300381Stundents梅超風(fēng)陳玄風(fēng)陸乘風(fēng)曲靈風(fēng)1234查詢結(jié)果梅超風(fēng)陳玄風(fēng)陳玄風(fēng)陸乘風(fēng)00100100200297896776陸乘風(fēng)00381曲靈風(fēng)NULL NULL猜一猜：這樣寫，返回的查詢結(jié)果是一樣的嗎？SELECT S.SName,C.CourseID,C.Score From Score AS CLEFT JOIN Students AS SON C.StudentID = S.SCodeSELECT S.SName,C.CourseID,C.Score From Students AS SLEFT JOIN Score AS C

18、ON C.StudentID = S.SCode多表聯(lián)查多表聯(lián)查左外連接左外連接任務(wù)實(shí)施-【信息收集】SELECT Titles.Title_id, Titles.Title, Publishers.Pub_nameFROM titles RIGHT OUTER JOIN Publishers ON Titles.Pub_id = Publishers.Pub_id多表聯(lián)查多表聯(lián)查右外連接右外連接任務(wù)實(shí)施-【信息收集】p分組（每組5人）p小組成員討論并制定具體實(shí)施步驟，明確任務(wù)分工p確定完成時(shí)間（90分鐘）p每組提交一份實(shí)施方案，教師進(jìn)行審核與指導(dǎo)p教師提出自己的方案p教師點(diǎn)評(píng)，學(xué)生互評(píng)p開(kāi)始

19、實(shí)施任務(wù)實(shí)施-【制定計(jì)劃】p 教師分析數(shù)據(jù)庫(kù)p 確定使用哪幾個(gè)表，以及各表存儲(chǔ)的數(shù)據(jù)信息p 教師講解并分析解題思路p 學(xué)生編寫數(shù)據(jù)查詢語(yǔ)句p 調(diào)試語(yǔ)法錯(cuò)誤和程序邏輯錯(cuò)誤p 運(yùn)行程序，查看查詢結(jié)果任務(wù)實(shí)施-【具體實(shí)現(xiàn)】p 查詢學(xué)生成績(jī)管理系統(tǒng)中學(xué)生的全部記錄 select * from studentsp 從學(xué)生成績(jī)管理系統(tǒng)中檢索所有的學(xué)號(hào)、姓名以及班級(jí)。select Snumber,Sname,SGrade from Studentsp 從學(xué)生成績(jī)管理系統(tǒng)中檢索學(xué)號(hào)為“100864”的學(xué)生信息。 select * from students where Snumber=100864p 從學(xué)生成

20、績(jī)管理系統(tǒng)中檢索出成績(jī)?yōu)榍叭膶W(xué)生信息。select TOP 3 * from Score order by score desc任務(wù)實(shí)施-【具體實(shí)現(xiàn)】p 從學(xué)生成績(jī)管理系統(tǒng)中檢索出成績(jī)?yōu)榍鞍俜种膶W(xué)生信息。SELECT TOP 20 PERCENT *FROM Score order by score desp 從學(xué)生成績(jī)管理系統(tǒng)中檢索出成績(jī)?cè)?0分到90分之間的學(xué)生信息。select * from Score where Score between 80 and 90p 從學(xué)生成績(jī)管理系統(tǒng)中檢索出成績(jī)是80分和50分的學(xué)生信息。 SELECT * FROM SCORE where s

21、core in (80,50)p 從學(xué)生成績(jī)管理系統(tǒng)中檢索姓”張”的學(xué)生信息。 SELECT * FROM Students where Sname like 張%任務(wù)實(shí)施-【具體實(shí)現(xiàn)】p 學(xué)生成績(jī)管理系統(tǒng)中檢索班級(jí)為空的學(xué)生信息。SELECT * FROM Students where SGrade is nullp 客戶要求從學(xué)生成績(jī)管理系統(tǒng)中檢索出學(xué)生的平均成績(jī)。 SELECT avg(score) as 平均成績(jī) FROM Scorep 客戶要求從學(xué)生成績(jī)管理系統(tǒng)中檢索出學(xué)生的最高成績(jī)。SELECT max(score) as 最高成績(jī)FROM Scorep 從學(xué)生成績(jī)管理系統(tǒng)中檢索每

22、一門課的平均成績(jī)SELECT CourseID, AVG(Score) AS 課程平均成績(jī)FROM ScoreGROUP BY CourseID任務(wù)實(shí)施-【具體實(shí)現(xiàn)】p 從學(xué)生成績(jī)管理系統(tǒng)中檢索平均成績(jī)大于70的課程平均成績(jī)SELECT CourseID, AVG(Score) AS 課程平均成績(jī)FROM ScoreGROUP BY CourseIDHAVING AVG(Score)70p 從學(xué)生成績(jī)管理系統(tǒng)中查詢課程編號(hào) SELECT DISTINCT CourseID FROM Scorep 從學(xué)生成績(jī)管理系統(tǒng)中查詢課程編號(hào) SELECT DISTINCT CourseID FROM Score任務(wù)實(shí)施-【具體實(shí)現(xiàn)】p查詢參加考試的學(xué)生姓名，課程號(hào)以及相應(yīng)的成績(jī)SELECT S.SName,C.CourseID,C.Score From Students AS SINNER JOIN Score AS CON C.SNumber =