任务4从学生成绩管理数据库中获取所感兴趣的信息

1、基于工作任务的基于工作任务的SQL Server 2008SQL Server 2008数据库应用数据库应用学生成绩管理系统学生成绩管理系统 任务任务4 从学生成绩管理数据库中获取所感兴趣的信息从学生成绩管理数据库中获取所感兴趣的信息任务目标p会使用简单查询语句p会使用TOP、ORDER BY 子句实现查询排序p会使用各种函数p会使用模糊查询（Like、Between、In）p会使用聚集函数（Sum、Avg、Count、Min、Max）实现对数据集合的运算p会使用GROUP BY 关键字实现分组查询p会使用连接查询（内连接，外连接，全连接）p查询所有的学号、姓名以及班级p查询某个学生的基本信息

2、p查询出成绩为前三名的学生信息p查询出成绩为前百分之二十的学生信息p查询出成绩在70分到90分之间的学生信息p查询出所有学生的平均成绩p查询每一门课的平均成绩或者某个人的所有课的总成绩p查询参加考试的学生姓名，课程名以及相应的成绩p查询全部学生的考试信息客户需求客户的要求要用到数据查询功能，要使用SELECT语句p使用简单SELECT语句查询学生基本信息p使用 TOP、ORDER BY 子句实现学生信息查询排序p使用模糊查询（Like、Between、In）限制查询范围p使用聚集函数（Sum、Avg、Count、Min、Max）统计分析学生成绩p使用GROUP BY子句实现对学生信息的分组查询

3、p使用连接查询分布在多个表中的学生信息任务分析简单的简单的SELECT SELECT 语句语句的查询的查询SELECT * FROM Students使用使用WHEREWHERE子句查询子句查询SELECT * from students where Sname=张三任务实施-【信息收集】限制固定行数的查询限制固定行数的查询SELECT TOP 5 SName, SAddressFROM Students WHERE SSex = 0返回百分之多少行返回百分之多少行SELECT TOP 20 PERCENT SName, SAddressFROM Students WHERE SSex = 0

4、任务实施-【信息收集】按多列排序查询按多列排序查询SELECT StudentID As 学员编号, Score As 成绩FROM ScoreWHERE Score60ORDER BY Score,CourseID任务实施-【信息收集】排序中的字段，可以使用表达式吗？p如果不可以，请说明原因；p如果可以，请举例说明；字符串函数字符串函数任务实施-【信息收集】函数名描述举例返回值CHARINDEX用来寻找一个指定的字符串在另一个字符串中的起始位置SELECT CHARINDEX(BDDY,MyBDDY Course, 1 )3LEN返回传递给它的字符串长度SELECT LEN(SQL Serv

5、er课程) 12LOWER把传递给它的字符串转换为小写SELECT LOWER(SQL Server课程) sql server课程UPPER把传递给它的字符串转换为大写SELECT UPPER(sql server课程)SQL SERVER课程字符串函数字符串函数任务实施-【信息收集】函数名描述举例返回值LTRIM清除字符左边的空格SELECT LTRIM ( 刘德华 )刘德华 （后面的空格保留）RTRIM清除字符右边的空格SELECT RTRIM ( 刘德华 )刘德华（前面的空格保留）RIGHT从字符串右边返回指定数量的字符SELECT RIGHT(买卖提.吐尔松,3)吐尔松REPLACE

6、替换一个字符串中的字符SELECT REPLACE(莫乐可切.杨可,可,兰) 莫乐兰切.杨兰STUFF在一个字符串中，删除指定长度的字符，并在该位置插入一个新的字符串SELECT STUFF(ABCDEFG, 2, 3, 我的音乐我的世界)A我的音乐我的世界EFG日期函数日期函数任务实施-【信息收集】函数名描述 举例返回值GETDATE取得当前的系统日期SELECT GETDATE()今天的日期DATEADD将指定的数值添加到指定日期部分后的日期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日期中指定日期部分的整数形式SELECT DATEPART(day, 01/15/2011)15数学函数数学函数任务实施-【信息收集】函数名描述举例返回值ABS返回数值表达式的绝对值SELECT ABS(-43) 43CEILING返回大于或等于所给数字表达式的最小整数SELECT CEILING(43.5) 44FLOOR返回小于或等于指定表达式的最大整数SELECT FLOOR(43

8、.5) 43POWER返回数值表达式的幂值SELECT POWER(5,2) 25ROUND将数值表达式四舍五入为指定精度SELECT ROUND(43.543,1) 43.5Sign正数返回+1，负数返回-1，0则返回0SELECT SIGN(-43) -1Sqrt返回浮点表达式的平方根SELECT SQRT(9) 3系统函数系统函数任务实施-【信息收集】函数名描述举例返回值CONVERT用来转变数据类型S E L E C T C O N V E R T (VARCHAR (5),12345)字符串12345CURRENT_USER 返回当前用户的名字SELECT CURRENT_USER你

9、登录的用户名DATALENGTH返回用于指定表达式的字节数SELECT DATALENGTH (中国A盟)7HOST_NAME返回当前用户所登录的计算机名SELECT HOST_NAME()你所登录的计算机的名SYSTEM_USER 返回当前所登录的用户名SELECT SYSTEM_USER你当前所登录的用户USER_NAME从给定的用户I D返回用户名SELECT USER_NAME(1)从任意数据库 中 返 回“dbo”SELECT SName As 姓名 SAddress AS 地址 FROM Students WHERE SAddress IS NULL姓名地址张果老NULL李寻欢NU

10、LL令狐冲NULL程灵素NULL猜一猜：把Students表中某些行的SAddress字段值删掉后：p 使用IS NULL能查询出来这些数据行吗？p 怎么查询出这些行来？模糊查询模糊查询IS NULLIS NULL任务实施-【信息收集】SELECT StudentID, Score FROM SCore WHERE Score BETWEEN 60 AND 80StudentIDScore77786111761764模糊查询模糊查询BETWEENBETWEEN任务实施-【信息收集】p 把某一字段中内容在特定范围内的记录查询出来SELECT SName AS 学员姓名,SAddress As 地

11、址 FROM Students WHERE SAddress IN (北京,广州,上海)学员姓名地址李 扬广州于紫电上海李青霜北京司马弓上海模糊查询模糊查询ININ任务实施-【信息收集】SUMSUM函数函数SELECT SUM(score) as 总成绩 FROM ScoreAVGAVG函数函数SELECT AVG(SCore) AS 平均成绩 From Score WHERE Score =60聚合函数聚合函数任务实施-【信息收集】MAXMAX、MINMIN函数函数SELECT AVG(SCore) AS 平均成绩, MAX (Score) AS 最高分, MIN (Score) AS 最低

12、分 From Score WHERE Score =60COUNTCOUNT函数函数SELECT COUNT (*) AS 及格人数 From Score WHERE Score=60聚合函数聚合函数任务实施-【信息收集】第一门课6个成绩取平均值第二门课6个成绩取平均值第三门课5个成绩取平均值分组汇总分组汇总任务实施-【信息收集】SELECT CourseID, AVG(Score) AS 课程平均成绩FROM ScoreGROUP BY CourseID分组汇总分组汇总任务实施-【信息收集】怎么样来统计每次的内部测试不同学员的成绩？第一次内部测试不及格第二次内部测试不及格第三次内部测试不及格

13、补考成绩多列分组汇总多列分组汇总任务实施-【信息收集】内部测试成绩还要把这个加上取平均StudentID和CourseID的组合存在重复需要按照这两个来进行分组，避免StudentID和CourseID 同时一样多列分组汇总多列分组汇总任务实施-【信息收集】SELECT StudentID AS 学员编号,CourseID AS 内部测试, AVG(Score) AS 内部测试平均成绩FROM ScoreGROUP BY StudentID,CourseID多列分组汇总多列分组汇总任务实施-【信息收集】SELECT StudentID AS 学员编号,CourseID AS 内部测试, AVG

14、(Score) AS 内部测试平均成绩FROM ScoreGROUP BY StudentID,CourseIDHAVING COUNT(Score)10分组查询分组查询HAVINGHAVING任务实施-【信息收集】SELECT S.SName,C.CourseID,C.Score From Score AS CINNER JOIN Students AS SON C.StudentID = S.SCodeScore122300100100200297896776300381猜一猜：这样写，返回的查询结果是一样的吗？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梅超风陈玄风陆乘风曲灵风1234查询结果梅超风陈玄风陈玄风陆乘风00100100200297896776陆乘风00381多表联查多表联查内连接内连接任务实施-【信息收集】SELECT Students.SName, Score.CourseID, S

16、core.ScoreFROM Students,ScoreWHERE Students.SCode = Score.StudentID多表联查多表联查内连接内连接任务实施-【信息收集】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)多表联查多表联查三表连接三表连接任务实施-【信息收集】Score

17、122300100100200297896776300381Stundents梅超风陈玄风陆乘风曲灵风1234查询结果梅超风陈玄风陈玄风陆乘风00100100200297896776陆乘风00381曲灵风NULL NULL猜一猜：这样写，返回的查询结果是一样的吗？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多表联查多表联查左外连接左外连接任务实施-【信息收集】SELECT Titles.Title_id, Titles.Title, Publishers.Pub_nameFROM titles RIGHT OUTER JOIN Publishers ON Titles.Pub_id = Publishers.Pub_id多表联查多表联查右外连接右外连接任务实施-【信息收集】p分组（每组5人）p小组成员讨论并制定具体实施步骤，明确任务分工p确定完成时间（90分钟）p每组提交一份实施方案，教师进行审核与指导p教师提出自己的方案p教师点评，学生互评p开始

19、实施任务实施-【制定计划】p 教师分析数据库p 确定使用哪几个表，以及各表存储的数据信息p 教师讲解并分析解题思路p 学生编写数据查询语句p 调试语法错误和程序逻辑错误p 运行程序，查看查询结果任务实施-【具体实现】p 查询学生成绩管理系统中学生的全部记录 select * from studentsp 从学生成绩管理系统中检索所有的学号、姓名以及班级。select Snumber,Sname,SGrade from Studentsp 从学生成绩管理系统中检索学号为“100864”的学生信息。 select * from students where Snumber=100864p 从学生成

20、绩管理系统中检索出成绩为前三名的学生信息。select TOP 3 * from Score order by score desc任务实施-【具体实现】p 从学生成绩管理系统中检索出成绩为前百分之二十的学生信息。SELECT TOP 20 PERCENT *FROM Score order by score desp 从学生成绩管理系统中检索出成绩在70分到90分之间的学生信息。select * from Score where Score between 80 and 90p 从学生成绩管理系统中检索出成绩是80分和50分的学生信息。 SELECT * FROM SCORE where s

21、core in (80,50)p 从学生成绩管理系统中检索姓”张”的学生信息。 SELECT * FROM Students where Sname like 张%任务实施-【具体实现】p 学生成绩管理系统中检索班级为空的学生信息。SELECT * FROM Students where SGrade is nullp 客户要求从学生成绩管理系统中检索出学生的平均成绩。 SELECT avg(score) as 平均成绩 FROM Scorep 客户要求从学生成绩管理系统中检索出学生的最高成绩。SELECT max(score) as 最高成绩FROM Scorep 从学生成绩管理系统中检索每

22、一门课的平均成绩SELECT CourseID, AVG(Score) AS 课程平均成绩FROM ScoreGROUP BY CourseID任务实施-【具体实现】p 从学生成绩管理系统中检索平均成绩大于70的课程平均成绩SELECT CourseID, AVG(Score) AS 课程平均成绩FROM ScoreGROUP BY CourseIDHAVING AVG(Score)70p 从学生成绩管理系统中查询课程编号 SELECT DISTINCT CourseID FROM Scorep 从学生成绩管理系统中查询课程编号 SELECT DISTINCT CourseID FROM Score任务实施-【具体实现】p查询参加考试的学生姓名，课程号以及相应的成绩SELECT S.SName,C.CourseID,C.Score From Students AS SINNER JOIN Score AS CON C.SNumber =