SQL Server 初步学习

书接上回

#9.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩。

这里考察了group by 和having用法的知识点，还使用了average和count函数 。在写代码的过程中一定要注意相同列名的情况下要明确说明是哪张表中的列数据，踩坑3次了/(ㄒoㄒ)/

select Student.studentNo,studentName,avg(Score.score)
from Student inner join Score on Student.studentNo=Score.studentNo
where score<60
group by Student.studentNo,Student.studentName
having count(courseNo)>=2;

 结果如下显示。

#10.查找选修过课程名中包含“系统”的课程的同学学号、姓名和所在班级。

 这里考察了like字符串匹配的用法，还有通配符%的使用。

select studentNo,studentName,classNo
from Student
where studentNo in(select studentNo 
from Score inner join Course on Score.courseNo=Course.courseNo
where courseName like '%系统%' );

首先是通配符，有%和_两种：

• %表示任意长度的字符串
• _代表一个字符串；

关于like的用法，值得注意的是escape是用来转义，就像编程语言中的 \

LIKE ‘<匹配串>’ [ESCAPE ‘<换字符>’]，这里如果用 \%ab 则表示就匹配“%ab”的字符串，而不是“任意长度+ab”字符串。

查询结果如下：

#11.查找同时选修过“高等数学”和“大学英语”两门课程的同学学号、姓名以 及该同学所选修的所有课程的课程名和相应成绩，按学号(升序)、成绩 (降序)排序输出。

这里我踩了一个大坑，直接写courseName='高等数学' and courseName='大学英语'，在where语句中and不可以用于查询相同属性，and是用于连接不同列的。所以这时可以采用 In（'<>','<>'）的语句，在已经相连的course、student、score表中直接查询符合courseName2个值的行。

这里还涉及到order by的用法，asc表示升序，desc表示降序，按照如下格式:

order by <列1> asc/desc,<列2> asc/desc ,......

select Student.studentNo,studentName,courseName,score
from Score left join Student on Student.studentNo=Score.studentNo
left join Course on Score.courseNo=Course.courseNo
where courseName in('高等数学','大学英语')
order by Student.studentNo asc,score desc;

 结果如下：

#11.查询所有学生的选修信息，显示信息包括学号，选修门数，平均分和总分。

select studentNo as'学号',count(courseNo) as '选修门数',avg(score) as'平均分',sum(score) as '总分'
from Score
group by studentNo;

 这里有几个注意点：

1.最好命名count，sum，avg这种函数得出的结果列，用 函数（<列>）as ‘<新列名>’的格式

否则，就会变成这样，变成无列名。

2.在使用聚合函数后，不要忘记group by，不写系统会报错

#13.查询先修课是“高等数学”的课程。（分别用自连接和 IN 语句实现）

in语句实现，不算难

select courseName
from Course
where courseNo in
(select courseNo from Course where priorCourse in
 (select courseNo from Course where courseName='高等数学'));

自连接如下，一定要分清楚a，b：

a为课程表，b为先修课程表，所以where后面是b表，select是a表。

select a.courseName
from  Course as a join Course as b on a.priorCourse=b.courseNo
where b.courseName='高等数学';

#14.查询至少有三门课程成绩超过 85 分的所有学生的信息，显示信息包括学号、课程号和分数。

注意这里用了一个嵌套，把符合条件的学生学号先筛选出来，再得到courseNo和score的信息。

select studentNo,courseNo,score
from Score
where studentNo in(select studentNo from Score where score>=85
group by studentNo having count(courseNo)>=3)；

#15.查询至少有两门课程成绩超过 85 分的所有学生的学号。

就是把上面的代码更改一下下就行，这个还要简单一些

select studentNo
from Score
where studentNo in(select studentNo from Score where score>=85
group by studentNo having count(courseNo)>=2)
group by studentNo;

结果如下：

#16.查询平均成绩超过 80 分且选修 3 门及以上的所有学生的信息，显示信息包括学号、课程数目和分数。

where后面是不可以使用聚合函数，所以要分两步走：

1.select中先运行聚合函数

2.再在where中筛选符合聚合函数的行

select studentNo,count (courseNo) as '课程数目',score
from Score
where studentNo in (select studentNo from Score where (select avg(score) from Score)>=80
and studentNo in(select studentNo from Score where(select count(courseNo) from Score)>=3))
group by studentNo,score;

 或者这样也行，使用having语句

select studentNo,count (courseNo) as '课程数目',score
from Score
where exists(select studentNo from Score 
where (select avg(score) from Score)>=80 group by studentNo having count(courseNo)>=3)
group by studentNo,score;

#17.查询所选修课程的成绩大于所有“002”号课程成绩的同学学号及相应课程的课程号和成绩。

select studentNo,courseNo,score
from Score
where score>all(select score from Score where courseNo='002');

这里使用>all 即为大于所有002课程的成绩，即大于max(002课程的成绩)，但是在where从句中我不会使用max，保险起见，我使用>all

#18.查询选修了所有课程的学生姓名。

select studentName
from Score left join Student on Student.studentNo=Score.studentNo
left join Course on Score.courseNo=Course.courseNo
where Course.courseNo=all(select courseNo from Course);

 首先把三张表连接一下，使用left join，接着使用all，即选课表Score中含有,如此变便筛选出选秀了所有课程的学生姓名。

TO BE CONTINUED——