实验三 数据查询1.docx
《实验三 数据查询1.docx》由会员分享，可在线阅读，更多相关《实验三 数据查询1.docx（13页珍藏版）》请在冰豆网上搜索。
实验三数据查询1
实验三“教学管理系统”数据查询
（1）
一、实验内容
1、在“教学管理系统”中，所有学生的基本信息都保存在学生信息表TB_Student中，请用SQL查询语句实现以下的查询要求：
①查看学生表中所有学生的所有字段的信息；
②查看学生表中所有学生的部分字段【StuID,StuName,Sex,ClassID】信息；
③按班级查看某个班学生的部分字段【StuID,StuName,Sex,ClassID】信息；
④按班级查看某个班学生的部分字段【StuID,StuName,Sex,ClassID】信息，而且先按字段【Sex】降序，再按字段【StuName】进行降序排列。
参考知识及代码：
●简单SELECT查询
首先，让我们学习最简单的查询语句，它的基本结构是：
SELECT字段1,字段2…FROM数据表
--
WHERE查询条件
--
ORDERBY字段a,字段b,…[ASC︱DESC]
--
●试验参考代码:
USEDB_TeachingMS
--当前数据库
GO
--
SELECT*FROMTB_Student
--学生表所有行列
USEDB_TeachingMS
--当前数据库
GO
--
SELECTStuID,StuName,Sex,ClassId
--所有行部分列
FROMTB_Student
--从学生表
USEDB_TeachingMS
--当前数据库
GO
--
SELECTStuID,StuName,Sex,ClassId
--所有行部分列
FROMTB_Student
--从学生表
WHEREClassId=‘040801’
--从04网络
（1）班
USEDB_TeachingMS
--当前数据库
GO
--
SELECTStuID,StuName,Sex,ClassId
--所有行部分列
FROMTB_Student
--从学生表
WHEREClassId='040801'
--从04网络
（1）班
ORDERBYSexDESC,StuNameDESC
--按两字段降序排列
另，两个关键字的学习：
●关键字DISTINCT:
USEDB_TeachingMS
--当前数据库
GO
--
SELECTDISTINCTClassId
--选择【班级编码】字段
FROMTB_Student
--从学生表
●关键字TOP:
USEDB_TeachingMS
--当前数据库
GO
--
SELECTTOP5StuID,StuName,Sex,ClassId
--所有行部分列
FROMTB_Student
--从学生表
WHEREClassId='040801'
--04网络
（1）班
2、用计算列显示学生和学生选课信息
①用“列别名”显示的【学生姓名】和【性别】字段的数据合成为一个【学生姓名（性别）】字段显示，如下图；
②查看“学生信息表”中自己班（如“04网络
（1）班”）学生的部分字段【StuID,StuName,Sex】信息，同时显示一个计算列【年龄】；
③查看“课程班信息表”中的部分字段【CourseClassID,ClassID,TeacherID,MaxNumber,SelectedNumber】信息
参考知识及代码：
●相关函数介绍:
函数名
函数描述
LTRIM（字符串）
删除指定字符串的左边空格，返回处理后的字符串
RTRIM（字符串）
删除指定字符串的右边空格，返回处理后的字符串
LEFT（字符串,长度）
左子串函数，返回从左边开始的指定长度个字符的字符串
RIGHT（字符串,长度）
右子串函数，返回从右边开始的指定长度个字符的字符串
SUBSTRING（字符串,位置,长度）
子串函数，返回从指定位置开始的指定长度个字符串
LEN（字符串）
返回指定字符串的字符长度数，不包含字符串右边的空格
LOWRE（字符串）
将指定字符串中的大写字母转换成小写字母，返回字符串
UPPER（字符串）
将指定字符串中的小写字母转换成大写字母，返回字符串
STR（数字）
将指定数字转换成字符的转换函数
函数名
函数描述
GETDATE（）
以DATATIME类型的标准格式返回当前系统的日期和时间
YEAR（日期）
返回指定日期的年份整数
MONTH（日期）
返回指定日期的月份整数
DAY（日期）
返回指定日期的天的整数
DATEPART（返回部分,日期）
返回指定日期的指定返回部分的整数
DATEDIFF（返回部分,起始日期,结束日期）
返回两个指定日期的指定返回部分差值
●试验参考代码:
①用“列别名”显示的【学生姓名】和【性别】字段的数据合成为一个【学生姓名（性别）】字段显示（请考虑消除结果中多余的空格）
USEDB_TeachingMS
GO
SELECTStuID学号,StuName+'（'+Sex+'）'AS'学生姓名（性别）',ClassIdAS班级编码
FROMTB_Student
WHEREClassId='040801'
ORDERBYSex,StuNameDESC
②查看“学生信息表”中自己班（如“04网络
（1）班”）学生的部分字段【StuID,StuName,Sex】信息，同时显示一个计算列【年龄】；
USEDB_TeachingMS
GO
SELECTStuID,StuName,Sex,YEAR（GETDATE（））-YEAR（Birthday）
AS年龄
FROMTB_Student
WHEREClassId='040801'
③查看“课程班信息表”中的部分字段【CourseClassID,ClassID,TeacherID,MaxNumber,SelectedNumber】信息
USEDB_TeachingMS
GO
SELECTCourseClassID,CourseID,TeacherID,MaxNumber,
SelectedNumber,MaxNumber-SelectedNumberAS可选数
FROMTB_CourseClass
3、SUBSTRING函数和LEN函数
1）、请写出查询语句，显示“课程班信息表”中上课教师的系部信息和【TeachingTime】字段的字符长度信息，如下图所示：
2）、请写出查询语句，显示“课程班信息表”中上课教师的系部信息和【TeachingTime】字段的字符长度信息，如下图所示：
4、运用运算符查询学生相关信息
①查询自己班级（如04网络
（2）班）性别为“男”（或为“女”）的所有学生信息，只显示“学号、姓名、性别和出生年份”字段；
②查询自己班级（如04网络
（2）班）在某个出生年份段（如19-21岁）的所有学生信息，只显示“学号、姓名、性别和年龄”字段；
③要查询一个学生的所有信息。
但是只知道这个学生的班级（如04网络
（2）班），还有该学生的姓（如“刘”）；或者只知道这个学生的班级（如04网络
（2）班），还有该学生的名字中的一个字（如“金”）。
参考代码：
①查询自己班级（如04网络
（2）班）性别为“男”（或为“女”）的所有学生信息，只显示“学号、姓名、性别和出生年份”字段；
USEDB_TeachingMS
GO
SELECTStuID学号,StuName姓名,Sex性别,
YEAR（Birthday）出生年份
FROMTB_Student
WHEREClassId='040802'ANDSex='M'
另：
通配符[]的学习
如果要查询“学生信息表”中04网络
（2）班中邮政编码为“23”或“24”开头的学生信息，结果如下图所示：
可以用下述T-SQL语句实现：
USEDB_TeachingMS
GO
SELECT*FROMTB_Student
WHEREClassId='040802'ANDZipCodeLIKE'2[3-4]%'
5、运用分类统计功能查询学生成绩
①按照学号统计班内每个学生的平均成绩，从高到低排序；如下右图所示：
②筛选出班内平均成绩在不同分数段的学生：
85分以上（含85分），80-85分（含80分），75-80分（含75分）；例如在75-80分的查询结果：
③根据两个班的统计情况，按班级分组汇总75分以上（含75分）的学生个数。
以“04网络
（1）班和04网络
（2）班”为例，请用T-SQL语句实现上述查询功能，要求显示学生的【StuID、ClassID、CourseID】字段和计算列【平均成绩】。
参考代码：
①按照学号统计班内每个学生的平均成绩，从高到低排序；（没有控制小数位数）
USEDB_TeachingMS
GO
SELECTStuID,ClassID,AVG（TotalScore）ASAvgScore
FROMTB_Grade
WHEREClassID='040801'
GROUPBYStuID,ClassID
ORDERBYAvgScoreDESC
另，函数学习：
1、MAX和MIN函数
学期结束后，班主任要查询自己班级（如04网络
（1）班）每门课程的平均成绩、最高分和最低分，结果如下图所示：
可以用下述T-SQL语句实现：
USEDB_TeachingMS
GO
SELECTCourseID,AVG（TotalScore）AvgScore,
MAX（TotalScore）MaxScore,MIN（TotalScore）MinScore
FROMTB_Grade
WHEREClassID='040801'
GROUPBYCourseID
2、COUNT函数
如果班主任要查询自己班级（如04网络
（1）班）每个学生已经选修的课程门数，结果如下图所示：
可以用下述T-SQL语句实现：
USEDB_TeachingMS
GO
SELECTStuID,Count（CourseID）ASCourseCnt
FROMTB_Grade
WHEREClassID='040801'
GROUPBYStuID
6、多表联合查询班级信息和课程成绩
①查询“TB_Class”表中的班级基本情况，要求显示的字段为【DeptName、ClassName】；
参考代码：
USEDB_TeachingMS
GO
SELECTDeptName,ClassName
FROMTB_Dept,TB_Class
WHERETB_Dept.DeptID=TB_Class.DeptID
②查询各个系的班级情况，要求所有系的情况都列出来，要求显示的字段为【DeptName、ClassName】；然后统计各个系的班级数，没有班级的系班级数显示为null；结果如下图所示：
参考代码：
USEDB_TeachingMS
GO
SELECTDeptName,ClassName
FROMTB_DeptLEFTOUTERJOINTB_Class
ONTB_Dept.DeptID=TB_Class.DeptID
USEDB_TeachingMS
GO
SELECTDeptName系部名称,COUNT（ClassName）班级名称
FROMTb_DeptLEFTOUTERJOINTb_Class
ONTb_Class.DeptId=Tb_Dept.DeptId
GROUPBYDeptName
③查询“TB_Grade”表中单个课程班的成绩，要求显示的字段为【StuID、StuName、ClassName、CourseName、TotalScore】，查询的表用相应的别名，按班级编码排序；结果如下图所示：
参考代码：
USEDB_TeachingMS
GO
SELECTTG.StuID,StuName,ClassName,CourseName,TotalScore
FROMTB_GradeTG,TB_StudentTS,TB_ClassTCL,TB_CourseTC
WHERETG.StuID=TS.StuIDANDTG.ClassID=TCL.ClassIDAND
TG.CourseID=TC.CourseIDANDCourseClassID='T080040401'
ORDERBYTG.CourseID
④查询“TB_Grade”表中所有课程班的平均成绩，要求显示的字段为【CourseClassID、CourseName、TeacherName】和计算列【平均成绩】。
参考代码：
USEDB_TeachingMS
GO
SELECTTG.CourseClassID,CourseName,TeacherName,
ROUND（AVG（TotalScore）,2）ASAvgScore
FROMTB_GradeTG,TB_CourseTC,TB_TeacherTT,
TB_CourseClassTCC
WHERETG.CourseID=TC.CourseIDAND
TCC.TeacherID=TT.TeacherIDAND
TG.CourseClassID=TCC.CourseClassID
GROUPBYTG.CourseClassID,CourseName,TeacherName
升级会员 