一、前言
本文主要是总结做题过程中碰到的一些难题以及容易做错的题目
二、题目所用表
CourseInfo
CID | CNam3 | Ccredit | Cproperty |
00000001 | 高等数学 | 3 | 基础课 |
00000002 | 英语阅读 | 2 | NULL |
00000004 | 大学计算机基础 | 4 | 基础课 |
00000005 | 大学体育 | 2 | 基础课 |
00000006 | 企业管理 | 3 | 专业基础课 |
00100001 | 数据库原理及应用 | 3 | 专业基础课 |
CID | CNam3 | Ccredit | Cproperty |
00000001 | 高等数学 | 3 | 基础课 |
00000002 | 英语阅读 | 2 | NULL |
00000004 | 大学计算机基础 | 4 | 基础课 |
00000005 | 大学体育 | 2 | 基础课 |
00000006 | 企业管理 | 3 | 专业基础课 |
00100001 | 数据库原理及应用 | 3 | 专业基础课 |
ScoreInfo
CID | SID | TID | SCore | Schear | Term |
00000001 | 05000001 | 00000001 | 92 | 2005-2006 | 1 |
00000001 | 05000002 | 00000001 | 61 | 2005-2006 | 1 |
00000001 | 04000002 | 00000001 | 99 | 2004-2005 | 1 |
00000001 | 05000003 | 00000001 | 76 | 2004-2005 | 1 |
00000001 | 06010001 | 00000001 | 80 | 2005-2006 | 1 |
00100002 | 05000001 | 01000001 | 70 | 2005-2006 | 2 |
00100002 | 05000002 | 01000001 | 35 | 2005-2006 | 2 |
00100002 | 05000003 | 01000001 | 60 | 2005-2006 | 2 |
00100002 | 05000004 | 01000001 | 65 | 2005-2006 | 2 |
00100002 | 04000002 | 01000002 | 40 | 2004-2005 | 2 |
00100001 | 04000002 | 01000003 | 77 | 2005-2006 | 1 |
00100001 | 05000001 | 01000003 | 99 | 2005-2006 | 2 |
StuInfo
SID | SName | Sex | Birthday | Dept | Major | Class | Grade |
05000002 | 孙雯 | 女 | 1983-08-05 00:00:00 | 计算机系 | 计算机科学 | 计科1班 | 05级 |
05000004 | 苏小明 | 男 | 1984-12-21 00:00:00 | 计算机系 | 计算机科学 | 计科1班 | 05级 |
05000005 | 周小杰 | 男 | 1985-06-01 00:00:00 | 计算机系 | 计算机科学 | 计科1班 | 05级 |
05000006 | 李建国 | 男 | 1985-05-01 00:00:00 | 管理系 | 计算机科学 | 计科1班 | 05级 |
05010002 | 徐贺菁 | 女 | 1985-03-15 00:00:00 | 管理科学与工程系 | 信息管理 | 信管2班 | 05级 |
06010001 | 陈平 | 男 | 1986-05-10 00:00:00 | 管理科学与工程系 | 信息管理 | 信管1班 | 06级 |
07011103 | 林敏 | 女 | 1985-05-04 00:00:00 | 管理科学与工程系 | 多媒体 | NULL | NULL |
TchInfo
TID | TName | Sex | Birthday | Title | Dep |
00000001 | 黄贺贺 | 男 | 1977-01-15 00:00:00 | 讲师 | 基础部 |
01000001 | 王晓红 | 女 | 1958-01-01 00:00:00 | 副教授 | 计算机系 |
01000002 | 李小波 | 男 | 1959-08-11 00:00:00 | 教授 | 计算机系 |
01000003 | 谈华 | 男 | 1962-05-01 00:00:00 | 教授 | 计算机系 |
01000004 | 黄利敏 | 女 | 1976-03-21 00:00:00 | 讲师 | 计算机系 |
01000005 | 曹珊珊 | 女 | 1982-12-12 00:00:00 | 助讲 | 计算机系 |
01000909 | 王小小 | 女 | 1999-01-04 00:00:00 | 副教授 | 计算机系 |
01000991 | 王红 | 女 | 1956-01-01 00:00:00 | 教授 | 基础部 |
01000999 | 王多多 | 女 | 1979-01-04 00:00:00 | 副教授 | 基础部 |
02000002 | 李丽丽 | 女 | 1972-11-12 00:00:00 | 副教授 | 管理科学与工程系 |
三、基础查询
1.题目
查询StuInfo表中的学生姓名和年龄,要求列名显示姓名和年龄
2.思路
- 所要查询属性:
SName
、年龄
- 所需要表:
StuInfo
- 起别名:
··· AS ···
- 将生日转化为年龄:
YEAR(GETDATE())-YEAR(Birthday)
3.完整语句
SELECT SName AS '姓名',YEAR(GETDATE())-YEAR(Birthday)AS '年龄' FROM StuInfo;
4.结果
四、条件查询
1.题目
查询StuInfo中出生年月介于1980年1月1日到1985年8月31日的学生信息
2.思路
- 所要查询属性:*
- 所需要表:StuInfo
- 条件语句:
BETWEE···AND···
- 易错点:日期外面要打单引号,否则查询结果错误
3.完整语句
SELECT * FROM StuInfo WHERE Birthday Between '1980-1-1' AND '1985-8-31';
4.结果
五、连接查询和分组查询
1.题目
通过ScoreInfo表查询在各门课程平均成绩高于80分的课程名称和平均成绩,要求显示对应的“课程名称”和“平均成绩”。注意保留1位小数
2.思路
- 所要查询属性:CName、平均成绩
- 需要查询的表格:CourseInfo 、ScoreInfo
- 注意点:平均值保留一位小数,用 decimal(18,1),AVG(SCore),不能使用 ROUND 函数
- 连接条件:ScoreInfo.CID = CourseInfo.CID
- 分组条件:CourseInfo.CName
- 筛选条件:平均值CONVERT(decimal(18,1),AVG(SCore)) > 80
- 数据排序方式:ORDER BY ··· DESC
- 注意点:防止数据重复,使用 CONVERT 去重
3.完整语句
SELECT CourseInfo.CName '课程名称', CONVERT(decimal(18,1),AVG(SCore)) '平均成绩' FROM CourseInfo,ScoreInfo WHERE ScoreInfo.CID = CourseInfo.CID GROUP BY CourseInfo.CName HAVING CONVERT(decimal(18,1),AVG(SCore)) > 80 ORDER BY CONVERT(decimal(18,1),AVG(SCore)) DESC;
4.结果
六、嵌套查询
1.题目
查询在所有选修了电子商务课程的学生中,分数高于本课程平均成绩的学生学号、姓名、性别
2.思路
- 所要查询属性:学号、姓名、性别
- 所要查询表:StuInfo、ScoreInfo
- 连接条件:StuInfo.SID = ScoreInfo.SID
- 筛选条件:ScoreInfo.CID = CourseInfo中电子商务课程的CID且ScoreInfo.SCore大于CourseInfo中电子商务课程的平均分
- 子查询+连接查询
- 子查询1:电子商务课程的CID
- 子查询1的筛选条件:CName = ‘电子商务’
- 子查询2:CourseInfo中电子商务课程的平均分(需要连接ScoreInfo来查询成绩)
- 子查询2的连接条件:ScoreInfo.CID = CourseInfo.CID
- 子查询2的筛选条件:CourseInfo.CName = ‘电子商务’
3.完整语句
SELECT StuInfo.SID,SName,Sex FROM StuInfo JOIN ScoreInfo ON StuInfo.SID = ScoreInfo.SID WHERE ScoreInfo.CID IN (SELECT CID FROM CourseInfo WHERE CName = '电子商务') AND ScoreInfo.SCore > (SELECT AVG(Score) FROM ScoreInfo JOIN CourseInfo ON ScoreInfo.CID = CourseInfo.CID WHERE CourseInfo.CName = '电子商务');
4.结果
七、结语
如果有任何疑问或者文中问题可以留言评论