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

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

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

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

4、任務(wù)實施-【信息收集】按多列排序查詢按多列排序查詢SELECT StudentID As 學(xué)員編號, Score As 成績FROM ScoreWHERE Score60ORDER BY Score,CourseID任務(wù)實施-【信息收集】排序中的字段，可以使用表達式嗎？p如果不可以，請說明原因；p如果可以，請舉例說明；字符串函數(shù)字符串函數(shù)任務(wù)實施-【信息收集】函數(shù)名描述舉例返回值CHARINDEX用來尋找一個指定的字符串在另一個字符串中的起始位置SELECT CHARINDEX(BDDY,MyBDDY Course, 1 )3LEN返回傳遞給它的字符串長度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ù)名描述舉例返回值LTRIM清除字符左邊的空格SELECT LTRIM ( 劉德華 )劉德華 （后面的空格保留）RTRIM清除字符右邊的空格SELECT RTRIM ( 劉德華 )劉德華（前面的空格保留）RIGHT從字符串右邊返回指定數(shù)量的字符SELECT RIGHT(買賣提.吐爾松,3)吐爾松REPLACE

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

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ù)名描述舉例返回值A(chǔ)BS返回數(shù)值表達式的絕對值SELECT ABS(-43) 43CEILING返回大于或等于所給數(shù)字表達式的最小整數(shù)SELECT CEILING(43.5) 44FLOOR返回小于或等于指定表達式的最大整數(shù)SELECT FLOOR(43

8、.5) 43POWER返回數(shù)值表達式的冪值SELECT POWER(5,2) 25ROUND將數(shù)值表達式四舍五入為指定精度SELECT ROUND(43.543,1) 43.5Sign正數(shù)返回+1，負數(shù)返回-1，0則返回0SELECT SIGN(-43) -1Sqrt返回浮點表達式的平方根SELECT SQRT(9) 3系統(tǒng)函數(shù)系統(tǒng)函數(shù)任務(wù)實施-【信息收集】函數(shù)名描述舉例返回值CONVERT用來轉(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返回用于指定表達式的字節(jié)數(shù)SELECT DATALENGTH (中國A盟)7HOST_NAME返回當(dāng)前用戶所登錄的計算機名SELECT HOST_NAME()你所登錄的計算機的名SYSTEM_USER 返回當(dāng)前所登錄的用戶名SELECT SYSTEM_USER你當(dāng)前所登錄的用戶USER_NAME從給定的用戶I D返回用戶名SELECT USER_NAME(1)從任意數(shù)據(jù)庫 中 返 回“dbo”SELECT SName As 姓名 SAddress AS 地址 FROM Students WHERE SAddress IS NULL姓名地址張果老NULL李尋歡NU

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

11、址 FROM Students WHERE SAddress IN (北京,廣州,上海)學(xué)員姓名地址李 揚廣州于紫電上海李青霜北京司馬弓上海模糊查詢模糊查詢ININ任務(wù)實施-【信息收集】SUMSUM函數(shù)函數(shù)SELECT SUM(score) as 總成績 FROM ScoreAVGAVG函數(shù)函數(shù)SELECT AVG(SCore) AS 平均成績 From Score WHERE Score =60聚合函數(shù)聚合函數(shù)任務(wù)實施-【信息收集】MAXMAX、MINMIN函數(shù)函數(shù)SELECT AVG(SCore) AS 平均成績, 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ù)實施-【信息收集】第一門課6個成績?nèi)∑骄档诙T課6個成績?nèi)∑骄档谌T課5個成績?nèi)∑骄捣纸M匯總分組匯總?cè)蝿?wù)實施-【信息收集】SELECT CourseID, AVG(Score) AS 課程平均成績FROM ScoreGROUP BY CourseID分組匯總分組匯總?cè)蝿?wù)實施-【信息收集】怎么樣來統(tǒng)計每次的內(nèi)部測試不同學(xué)員的成績？第一次內(nèi)部測試不及格第二次內(nèi)部測試不及格第三次內(nèi)部測試不及格

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

14、(Score) AS 內(nèi)部測試平均成績FROM ScoreGROUP BY StudentID,CourseIDHAVING COUNT(Score)10分組查詢分組查詢HAVINGHAVING任務(wù)實施-【信息收集】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ù)實施-【信息收集】SELECT Students.SName, Score.CourseID, S

16、core.ScoreFROM Students,ScoreWHERE Students.SCode = Score.StudentID多表聯(lián)查多表聯(lián)查內(nèi)連接內(nèi)連接任務(wù)實施-【信息收集】SELECT S.SName AS 姓名, CS.CourseName AS 課程, C.Score AS 成績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ù)實施-【信息收集】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ù)實施-【信息收集】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ù)實施-【信息收集】p分組（每組5人）p小組成員討論并制定具體實施步驟，明確任務(wù)分工p確定完成時間（90分鐘）p每組提交一份實施方案，教師進行審核與指導(dǎo)p教師提出自己的方案p教師點評，學(xué)生互評p開始

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

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

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

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