概述
本题目的表结构大致如下图所示,具体的建表及测试数据可以参考原博主的博客。
我已经连通练习过一次并记录在了为知笔记中,也借此得到了很大的锻炼。这一次进行第二次练习熟悉,也顺便录入到Github中来。
另,本篇中的题目,前半部分难度稍大,后半部分难度减小。
练习
1)查询“001”课程比“002”课程成绩高的所有学生的学号
select
s1.StudentNo
from
score s1,
score s2
where
s1.CourseNo = 001
and
s2.CourseNo = 002
and
s1.StudentNo = s2.StudentNo
and
s1.score > s2.score
2) 查询平均成绩大于60分的同学的学号和平均成绩
select
s1.StudentNo,
AVG(s1.score)
from
score s1
group by s1.StudentNo
having AVG(s1.score) > 60
3)查询所有同学的学号、姓名、选课数、总成绩
select
stu1.studentNo,
stu1.name,
count(s1.CourseNo),
sum(s1.score)
from
score s1,
student stu1
where
s1.StudentNo = stu1.studentNo
group by s1.StudentNo
4)查询姓“李”的老师的个数
select
count(*)
from
teacher t1
where
t1.name like '李%'
5)查询没学过“叶平”老师课的同学的学号、姓名
select
stu1.studentNo,
stu1.name
from
student stu1
where
stu1.studentNo notin
(
--学过叶平老师课的学生学号
selectdistinct
s1.StudentNo
from
score s1,
course c1,
teacher t1
where
s1.CourseNo = c1.courseNo
and
c1.teacherNo = t1.teacherNo
and
t1.name = '叶平'
)
6)查询学过“001”并且也学过编号“002”课程的同学的学号、姓名
select
stu1.studentNo,
stu1.name
from
score s1,
student stu1
where
s1.StudentNo = stu1.studentNo
and
s1.CourseNo = 001
and
s1.StudentNoin
(
selectdistinct
s2.StudentNo
from
score s2
where
s2.CourseNo = 002
)
7)查询学过“叶平”老师所教的所有课的同学的学号、姓名
select
s1.StudentNo,
stu1.name
from
score s1,
student stu1,
course c1,
teacher t1
where
s1.StudentNo = stu1.studentNo
and
s1.CourseNo = c1.courseNo
and
c1.teacherNo = t1.teacherNo
and
t1.name = '叶平'
group by s1.StudentNo
having count(c1.courseNo)=
(
select
count(c2.courseNo)
from
course c2,
teacher t2
where
c2.teacherNo = t2.teacherNo
and
t2.name = '叶平'
)
8)查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名(和第1题是重复的)
select
s1.StudentNo,
stu1.name
from
score s1,
score s2,
student stu1
where
s1.StudentNo = stu1.studentNo
and
s1.CourseNo = 001
and
s2.CourseNo = 002
and
s1.StudentNo = s2.StudentNo
and
s1.score > s2.score
9)查询有课程成绩小于60分的同学的学号、姓名
selectdistinct
s1.StudentNo,
stu1.name
from
score s1,
student stu1
where
s1.StudentNo = stu1.studentNo
and
s1.score < 60
10)查询没有学全所有课的同学的学号、姓名
select
s1.StudentNo,
stu1.name
from
score s1,
student stu1
where
s1.StudentNo = stu1.studentNo
group by s1.StudentNo
having count(s1.CourseNo) <
(
select
count(*)
from
course c1
)
11)查询至少有一门课与学号为“001”的同学所学相同的同学的学号和姓名
selectdistinct
s1.StudentNo,
stu1.name
from
score s1,
student stu1
where
s1.StudentNo = stu1.studentNo
and
s1.CourseNoin
(
select
s2.CourseNo
from
score s2
where
s2.StudentNo = 001
)
12)查询至少学过学号为“001”同学所有一门课的其他同学学号和姓名(和11题几乎一样)
selectdistinct
s1.StudentNo,
stu1.name
from
score s1,
student stu1
where
s1.StudentNo = stu1.studentNo
and
s1.StudentNo != 001
and
s1.CourseNoin
(
select
s2.CourseNo
from
score s2
where
s2.StudentNo = 001
)
13)把“SC”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩
这道题有两个点需要注意:
MySQL中不支持先select同一表中的某值,再update这个表,所以需要采用再select的方式来提取值;
个人理解的该题的题意是,假设叶平老师教授了A课和B课,那么课程A改为课程A的平均成绩,课程B改为课程B的平均成绩。实际上我写出的是课程A和B的成绩都改为A和B成绩的平均成绩。所以实际上我认为以下的写法是有违题意的,但是按照理解的题意,暂未想到合适的sql语句,故此搁浅,隔日再审。
update
score s1
set
s1.score =
(
select
table1.avgScore
from
(
select
avg(s2.score) as avgScore
from
score s2,
course c2,
teacher t2
where
s2.CourseNo = c2.courseNo
and
c2.teacherNo = t2.teacherNo
and
t2.name = '叶平'
)table1
)
where
s1.CourseNoin
(
select
c1.courseNo
from
course c1,
teacher t1
where
c1.teacherNo = t1.teacherNo
and
t1.name = '叶平'
)
以上为当初不符题意的sql写法,后来知道原来update可以采取类似多表联合的方式,于是得到了解决,具体代码如下(另,相关文章参考链接:MySQL:把一个表中的数据按键值更新(update)到另一个表),读书少害人啊!
UPDATE
score s,
(
SELECT
s1.CourseNo as courseNo,
AVG(s1.score) as avgScore
FROM
score s1,
course c1,
teacher t1
WHERE
s1.CourseNo = c1.CourseNo
AND
c1.teacherNo = t1.teacherNo
AND
t1.name = '叶平'
GROUP BY s1.CourseNo
) as t
SET
s.score = t.avgScore
WHERE
s.CourseNo = t.courseNo
14)查询和“002”号的同学学习的课程完全相同的其他同学学号和姓名
这里利用了CourseNo作为主键唯一的特性,不同人的各课程相加的值也不同,如果相同,那么所学课程必定相同。
select
s1.StudentNo,
stu1.name
from
score s1,
student stu1
where
s1.StudentNo = stu1.studentNo
and
s1.StudentNo != 2
group by s1.StudentNo
having sum(s1.CourseNo) =
(
select
sum(s2.CourseNo)
from
score s2
where
s2.StudentNo = 2
)
15)删除学习“叶平”老师课的SC表记录
delete from
score s1
where
s1.CourseNoin
(
select
c1.courseNo
from
course c1,
teacher t1
where
c1.teacherNo = t1.teacherNo
and
t1.name = '叶平'
)
16)向SC表中插入一些记录,这些记录要求符合以下条件:1、没有上过编号“002”课程的同学学号;2、插入“002”号课程的平均成绩
(本题采用插入子查询的方式,三个字段中后两个字段为常量)
insert into score(StudentNo, courseNo, score)
(
select
stu1.StudentNo,
"002",
(
select
avg(s1.score)
from
score s1
where
s1.CourseNo = 2
)
from
student stu1
where
stu1.studentNo notin
(
select
s2.StudentNo
from
score s2
where
s2.CourseNo = 2
)
)
17)按平均成绩从低到高显示所有学生的“语文”、“数学”、“英语”三门的课程成绩,按如下形式显示: 学生ID,语文,数学,英语,有效课程数,有效平均分
(在自然连接和等值连接中,不匹配的元组信息会丢失,如果在这道题中使用,就会不完整。这里采用了相关子查询的方式。)
select
s.StudentNo,
(select s1.score from score s1 where s1.CourseNo=1 and s1.StudentNo = s.StudentNo) as "语文",
(select s2.score from score s2 where s2.CourseNo=2 and s2.StudentNo = s.StudentNo) as "数学",
(select s3.score from score s3 where s3.CourseNo=3 and s3.StudentNo = s.StudentNo) as "英语",
COUNT(s.CourseNo) as "有效课程数",
AVG(s.score) as "有效平均分"
from
score s
group by s.StudentNo
order by AVG(s.score)
先将score s按s.StudentNo分类,则StudentNo不重复,分别提出和子查询中参与运算,如[ select s1.score from score s1 where s1.CourseNo=1 and s1.StudentNo = s.StudentNo ]中,提取s中StudentNo如1到子查询中,在子查询中找到s1.StudentNo = s.StudentNo = 1的,然后继续条件and s1.CourseNo = 1,如果没有,填为null。
18)查询各科成绩最高和最低的分:以如下形式显示:课程ID,最高分,最低分;
select
s1.CourseNo as '课程ID',
max(s1.score) as '最高分',
min(s1.score) as '最低分'
from
score s1
group by s1.CourseNo
19)按各科平均成绩从低到高和及格率的百分数从高到低顺序;
select
s1.CourseNo,
avg(s1.score) as 'avgScore',
t1.passNum / count(s1.StudentNo) as 'passRate'
from
score s1,
(
select
s2.CourseNo,
count(s2.StudentNo) as 'passNum'
from
score s2
where
s2.score > 60
group by s2.CourseNo
)t1
where
s1.CourseNo = t1.CourseNo
group by s1.CourseNo
order by avgScore, passRate desc
(这种解法有误,忽略了某学科全部成绩低于60的情况,信息丢失)
所以采取外连接的方式,重写如下:
select
t0.CourseNo,
t0.avgScore,
t1.passNum,
t0.amount,
t1.passNum / t0.amount * 100 as 'passRate'
from
(
select
s1.CourseNo,
avg(s1.score) as 'avgScore',
count(s1.score) as 'amount'
from
score s1
group by s1.CourseNo
)t0
natural left outer join
(
select
s2.CourseNo,
count(s2.StudentNo) as 'passNum'
from
score s2
where
s2.score > 60
group by s2.CourseNo
)t1
order by t0.avgScore, passRate desc
科目1所有人都没及格,你会发现这个查询结果及格数为null,但是我们想要的是,如果没有就为0,怎么办?那么我们在使用ifNull函数:
select
t0.CourseNo,
t0.avgScore,
ifnull(t1.passNum, 0),
t0.amount,
ifnull(t1.passNum, 0) / t0.amount * 100 as 'passRate'
from
(
select
s1.CourseNo,
avg(s1.score) as 'avgScore',
count(s1.score) as 'amount'
from
score s1
group by s1.CourseNo
)t0
natural left outer join
(
select
s2.CourseNo,
count(s2.StudentNo) as 'passNum'
from
score s2
where
s2.score > 60
group by s2.CourseNo
)t1
order by t0.avgScore, passRate desc
这里,提一下MYSQL中ISNULL和IFNULL的区别:
ISNULL(expr):if expr is NULL, returns 1, else returns 0;
IFNULL(expr1, expr2):if expr1 is not NULL, returns expr1, else returns expr2.
20)查询不同老师所教不同课程平均分从高到低显示
select
t1.name,
c1.courseNo,
c1.name,
avg(s1.score)
from
score s1,
course c1,
teacher t1
where
s1.CourseNo = c1.courseNo
and
c1.teacherNo = t1.teacherNo
group by s1.CourseNo
order by avg(s1.score) desc
21)统计列印各科成绩,各分数段人数: 课程ID,课程名称,(100-85),(85-70),(70-60),( 低于60)
这里用到了MySQL中 case when then else end 用法,命令如其名,详见题目SQL语句。
select
s1.CourseNo,
c1.name,
sum(case when s1.score between 85 and 100 then 1 else 0 end) as "85-100",
sum(case when s1.score between 70 and 85 then 1 else 0 end) as "70-85",
sum(case when s1.score between 60 and 70 then 1 else 0 end) as "60-70",
sum(case when s1.score < 60 then 1 else 0 end) as "<60"
from
score s1,
course c1
where
s1.CourseNo = c1.courseNo
group by s1.CourseNo
22)查询各科成绩前三名的记录:(不考虑成绩并列情况)
这里会巧妙利用到相关子查询。
select
*
from
score s1
where
s1.scorein
(
select
s2.score
from
score s2
where
s2.CourseNo = s1.CourseNo
order by s2.score desc
limit 0, 3
)
如果每次提出的该行中的分数,在该分数对应学科的前三名分数当中,那么符合条件,提取出来。
但是,在MySQL4.1中子查询是不能使用LIMIT的,手册中也明确指明 This version of MySQL doesn’t yet support ‘LIMIT & IN/ALL/ANY/SOME subquery’。
所以这个方法暂时待定,换另一个方法:
select
*
from
score s
where
(
select
count(*)
from
score s1
where
s1.CourseNo = s.CourseNo
and
s1.score > s.score
) < 3
order by s.CourseNo
这个方法的思路是,需要提取的行,该行对应的分数对应的学科中,比它高的分数必须小于3个
(即假如自己是第3,那么在该学科所有分数中,比自己高的只有第一和第二共2个)
但是,这个方法对于并列的成绩,会全部输出。
23)查询每门课程被选修的学生数
select
s1.CourseNo,
count(s1.StudentNo)
from
score s1
group by s1.CourseNo
24)查询出只选修了一门课程的全部学生的学号和姓名
select
s1.StudentNo,
stu1.name
from
score s1,
student stu1
where
s1.StudentNo = stu1.studentNo
group by s1.StudentNo
having count(s1.CourseNo) = 1
25)查询男生、女生的人数
select
stu1.sex,
count(stu1.studentNo)
from
student stu1
group by stu1.sex
26)查询同名同姓学生名单,并统计同名人数
这道题告诉我,凡是不要想得太复杂...
select
stu1.name,
count(stu1.name)
from
student stu1
group by stu1.name
27)查询1991年出生的学生名单
不同数据库的时间函数不一样,这里使用的是mysql;
CURDATE() 或CURRENT_DATE() 获取当前日期,按格式"YYYY-MM-DD"返回;
YEAR(date) 返回date对应的年份,范围是1970-2069
select
stu1.name,
stu1.age
from
student stu1
where
YEAR(CURRENT_DATE())-stu1.age = 1991
28)查询每门课程的平均成绩,结果按平均成绩升序排列
select
s1.CourseNo,
avg(s1.score)
from
score s1
group by s1.CourseNo
order by avg(s1.score)
29)查询平均成绩大于85的所有学生的学号、姓名和平均成绩
select
stu1.studentNo,
stu1.name,
avg(s1.score)
from
score s1,
student stu1
where
s1.StudentNo = stu1.studentNo
group by s1.StudentNo
having avg(s1.score)>85
30)查询课程名称为“数学”,且分数低于60的学生姓名和分数
select
stu1.name,
s1.score
from
score s1,
course c1,
student stu1
where
s1.StudentNo = stu1.studentNo
and
s1.CourseNo = c1.courseNo
and
c1.name = "数学"
and
s1.score < 60
31)查询所有学生的选课情况
select
stu1.name,
c1.name
from
score s1,
student stu1,
course c1
where
s1.StudentNo = stu1.studentNo
and
s1.CourseNo = c1.courseNo
order by stu1.studentNo
32)查询任何一门课程成绩在70分以上的姓名、课程名称和分数
select
stu1.name,
c1.name,
s1.score
from
score s1,
course c1,
student stu1
where
s1.CourseNo = c1.courseNo
and
s1.StudentNo = stu1.studentNo
and
s1.score > 70
33)查询不及格的课程,并按课程号从大到小排列
selectdistinct
s1.CourseNo
from
score s1
where
s1.score < 60
order by s1.CourseNo desc
34)查询课程编号为003且课程成绩在80分以上的学生的学号和姓名
select
s1.StudentNo,
stu1.name,
s1.score
from
score s1,
student stu1
where
s1.StudentNo = stu1.studentNo
and
s1.CourseNo = 3
and
s1.score > 80
35)求选了课程的学生人数
select
count(*)
from
(
select
*
from
score s1
group by s1.StudentNo
) table1
不够简洁,优化如下:
select
count(distinct s1.StudentNo)
from
score s1
36)查询选修“杨艳”老师所授课程的学生中,成绩最高的学生姓名及其成绩
使用MySQL的limit函数:
select
stu1.name,
s1.score
from
score s1,
course c1,
teacher t1,
student stu1
where
s1.CourseNo = c1.courseNo
and
s1.StudentNo = stu1.studentNo
and
c1.teacherNo = t1.teacherNo
and
t1.name = '杨艳'
order by s1.score desc
limit 0, 1
37)查询各个课程及相应的选修人数
select
c1.courseNo,
c1.name,
count(*)
from
score s1,
course c1
where
s1.CourseNo = c1.courseNo
group by s1.CourseNo
38)查询不同课程但成绩相同的学生的学号、课程号、学生成绩
selectdistinct
s1.StudentNo,
s1.CourseNo,
s1.score
from
score s1,
score s2
where
s1.score = s2.score
and
s1.CourseNo <> s2.CourseNo
order by s1.score
39)查询每门课程成绩最好的前两名
这里同第22题:查询各科成绩前三名的记录
select
stu1.name,
s.CourseNo,
s.score
from
score s,
student stu1
where
(
select
count(*)
from
score s1
where
s.CourseNo = s1.CourseNo
and
s.score< s1.score
) < 2
and
s.StudentNo = stu1.studentNo
order by s.CourseNo
40)统计每门课程的学生选修人数(超过5人的课程才统计)要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列
select
s1.CourseNo,
count(s1.StudentNo)
from
score s1
group by s1.CourseNo
having count(s1.StudentNo)>5
order by count(s1.StudentNo) desc, s1.CourseNo
41)检索至少选修两门课程的学生学号
select
s1.StudentNo
from
score s1
group by s1.StudentNo
having count(s1.CourseNo) >= 2
42)查询全部学生都选修的课程的课程号和课程名
思路:全部学生都选修了的课程,即:score表按课程分组,课程中的学生数量和总学生数量相同的元组,即为全部学生选修的课程
select
s1.CourseNo,
c1.name
from
score s1,
course c1
where
s1.CourseNo = c1.courseNo
group by s1.CourseNo
having count(s1.StudentNo)=
(
select
count(*)
from
student stu1
)
43)查询没学过“叶平”老师讲授的任一门课程的学生姓名
select
stu1.name
from
student stu1
where
stu1.StudentNo notin
(
selectdistinct
s1.StudentNo
from
score s1,
course c1,
teacher t1
where
s1.CourseNo = c1.courseNo
and
c1.teacherNo = t1.teacherNo
and
t1.name = '叶平'
)
44)查询两门以上不及格课程的同学的学号及其平均成绩
select
t1.StudentNo,
t1.avgScore
from
(
select
s2.StudentNo,
avg(s2.score) as avgScore
from
score s2
group by s2.StudentNo
) t1,
(
select
s1.StudentNo
from
score s1
where
s1.score < 60
group by s1.StudentNo
having count(s1.CourseNo)>=2
) t2
where
t1.StudentNo = t2.StudentNo
45)检索“004”课程分数小于60,按分数降序排列的同学学号
select
s1.StudentNo,
s1.score
from
score s1
where
s1.CourseNo = 4
and
s1.score < 60
order by s1.score desc
46)删除“002”同学的“001”课程的成绩
enter code heredelete from
score s1
where
s1.StudentNo = 2
and
s1.CourseNo = 1
END