sql数据库第五次课

第五次课大纲
-3 同学们好！
-2 开始上课！
-1 上一次课我们学了什么？
●数据表记录查询（Select）。

●单表查询
●多表查询
●查询统计
●完成课堂任务单。

0 这次课我们主要学习什么？
●函数的使用（max，min，sum，avg）
●复合条件的使用（=，>，<，<>，like，in，between，and，or，not）
●数学表达式的使用（+，-，*，/）
●合并查询
●嵌套查询（子查询）
●查询记录集列别名设置
●完成课堂任务单。

1 数据准备
●使用UI界面工具SSMSE恢复数据库实例Class131
（1）恢复来源文件名：Class131_131106.bak
（2）恢复后文件组存储目录：D:\Class131.mdf和D:\Class131_log.ldf
（3）恢复后数据库实例管理权限归属于sa用户
（4）检查数据表：应有5个数据表，分别是T_Student（学籍信息表），T_CourseResult（课程成绩表），T_Sex（性别字典表），T_Photo（学籍图像表），T_Nationality（民族字典表）
2函数的使用
2.1max()函数
（1）查询T_CourseResult表中课程名称为“军事训练”的折算成绩最高分
select max(zscj) from T_CourseResult where kcmc='军事训练'
2.2min()函数
（1）查询T_CourseResult表中课程名称为“计算机应用基础”的折算成绩最低分
select min(zscj) from T_CourseResult where kcmc='计算机应用基础'
2.3sum()函数
（1）查询T_CourseResult表中学号为“1332324118”的课程总分（以折算成绩计）
select sum(zscj) from T_CourseResult where xh='1332324118'
（2）查询T_CourseResult表中学号为“1332324118”，学年为“2013-2014”，学期为“1”的课程总分（以折算成绩计）
select sum(zscj) from T_CourseResult where xh='1332324118' and xn='2013-2014' and xq='1'
2.4avg()函数
（1）查询T_CourseResult表中课程名称为“数据库应用技术”的平均分（以折算成绩计）
select avg(zscj) from T_CourseResult where kcmc='数据库应用技术'
3复合条件的使用
（1）查询T_Student表中学号等于“11111111”的记录，显示字段有XH,XM
select xh,xm from T_Student where xm='吴明'
（2）查询T_CourseResult表折算成绩大于“90”的全部记录
select * from T_CourseResult where zscj>90
（3）查询T_CourseResult表折算成绩小于“85”的全部记录
select * from T_CourseResult where zscj<85
（4）查询T_Student表中性别代码不等于“1”的全部记录
select * from T_Student where xb<>'1'
（5）查询T_Student表姓名含有“强”字记录，并按班级名称升序排序
select * from T_Student where xm like '%强' order by bjmc
（6）查询T_Student表班级名称包含“软件131”和“软件132”的记录，并按班级名称升序，学号降序排序
select * from T_Student where bjmc in ('软件131','软件132') order by bjmc asc,xh desc
（7）查询T_CourseResult表课程名称为“军事训练”，折算成绩在60和85之间的记录
select * from T_CourseResult where kcmc='计算机应用基础' and zscj between 60 and 80 （8）查询T_Student表性别代码为“1”并且民族代码为“01”的记录
select * from T_Student where xb='1' and mz='01'
（9）查询T_Student表姓名中姓氏为“张”或“李”的记录，并按姓名默认排序
select * from T_Student where xm like '张%' or xm like '李%' order by xm
（10）查询T_Student表姓名中姓氏不为“赵”的记录，并按姓名降序排序
select * from T_Student where xm not like '张%' order by xm desc
4数学表达式的使用
（1）查询T_CourseResult表折算成绩小于60的记录，显示记录列有：学号、课程名称、折算成绩、折算成绩和60分之间的差值
select xh,kcmc,zscj,60-zscj from T_CourseResult where zscj<60
（2）查询T_CourseResult表折算成绩小于等于45的记录，显示记录列有：学号、课程名称、折算成绩、折算成绩*1.5
select xh,kcmc,zscj,zscj*1.5 from T_CourseResult where zscj<=45
5合并查询
（1）查询T_Student表中前10个“张”姓和前5个“李”姓学生记录，显示列：学号，姓名select top 10 xh,xm from T_Student where xm like '张%'
union
select top 5 xh,xm from T_Student where xm like '李%'
（2）查询T_CourseResult表中课程名称为“军事训练”且成绩为”优秀“和课程名称为“入学教育”且成绩为“及格”的记录，显示列：学年，学期，课程名称，成绩select xn,xq,kcmc,cj from T_CourseResult where kcmc='军事训练' and cj='优秀'
union all
select xn,xq,kcmc,cj from T_CourseResult where kcmc='入学教育' and cj='及格'
6嵌套查询（子查询）
（1）以T_Student表为子表，查询T_CourseResult表中学号所属班级名称为“软件131”，课程名称为“入学教育”的记录
select * from T_CourseResult where kcmc='入学教育' and xh in (select xh from T_Student where bjmc='软件131')
（2）以T_Photo表为子表，查询T_Student表中无照片的记录
select * from T_Student where xh not in (select xh from T_Photo where zp is not null)
（3）★以T_CourseResult表为子表，查询T_Student表中姓名为“曹凯”的成绩总条数，显示列：学号，姓名，成绩总条数
select xh,xm,(select count(*) from T_CourseResult b where b.xh=a.xh) from T_Student a where xm='曹凯'
7查询记录集列别名设置
（1）查询T_Student表记录，显示列：学号，姓名，性别，民族（显示列以上述汉字提示）select xh 学号,xm 姓名,xb 性别,mz 民族from T_Student
（2）统计T_CourseResult表记录总数，显示列：全部成绩数（显示列以上述汉字提示）
select count(*) 全部成绩数from T_CourseResult
8本次课堂任务单
★本任务单基于恢复的数据库实例Class131，以其包含的5个数据表为基础运行本次任务单，要求保留全部T-SQL语句！
★完成任务单时间要求在60分钟以内。

★本次任务单完成结果计入个人平时成绩。

（1）统计并显示T_CourseResult表中折算成绩在60（含）和85（含）之间的折算成绩平均分（10分）
（2）使用子查询统计并显示学号为“1332324108”的2013学年第1学期全部课程的总分和平均分
（以折算成绩计算），显示列：姓名，总分，平均分（显示列以上述汉字提示）（30分）
（3）使用合并查询统计并显示T_CourseResult表中课程名称为“入学教育”且成绩为“良好”和课程名称为“计算机应用基础”且折算成绩大于等于85的记录，并按课程名称默认排序（30分）（4）使用子查询统计并显示T_CourseResult表中课程门数，课程名称为“计算机应用基础”的折算成绩最高分和课程名称为“数据库应用技术”的折算成绩最低分，显示字段：课程门数、计算机应用基础折算成绩最高分、数据库应用技术折算成绩最低分（显示列以上述汉字提示）（30分）。