mysql练习(待补充)
2、查询‘生物’课程比‘物理’课程成绩高的所有学生的学号
思路:
获取所有生物课程的人(学号,成绩)-临时表
获取所有物理课程的人(学号,成绩)-临时表
根据学号连接两个临时表:
学号 生物成绩 物理成绩
SELECT A.student_id,sw,wl from
(SELECT student_id,num as sw from score LEFT JOIN course on score.course_id=course.cid where course.cname='生物')as A
LEFT JOIN
(SELECT student_id,num as wl from score LEFT JOIN course on score.course_id=course.cid where course.cname='物理')as B
on A.student_id =B.student_id where sw > if (ISNULL(wl),0,wl);
自己写了一段
SELECT A.student_id,sw,wl from
(SELECT student_id,num as sw from score where course_id=(SELECT cid from course WHERE cname='生物')) as A
LEFT JOIN
(SELECT student_id,num as wl from score where course_id=(SELECT cid from course WHERE cname='物理')) as B
on A.student_id=B.student_id where sw > if (ISNULL(wl),0,wl)
3、查询平均成绩大于60分的同学的学号和平均成绩
思路:
根据学生分组,使用avg获取平均值,通过having对avg进行筛选
select student_id,avg(num) from score group by student_id having avg(num) > 60
4、查询所有同学的学号、姓名、选课数、总成绩
select score.student_id,student.sname,count(score.student_id),sum(score.num)
from
score left join student on score.student_id=student.sid group by score.student_id
5、查询姓‘李’的老师的个数
select count(tid) from teacher where tname like '李%'
6、查询没学过‘叶平’老师课的同学的学号、姓名
思路:
先查询‘李平老师’老师教的所有课的ID
然后获取选了李平老师课的学生ID
最后从学生表中筛选
select sid,sname from student where sid not in (
select DISTINCT student_id from score where score.course_id in (
select cid from course left join teacher on course.teacher_id = teacher.tid where tname = '李平老师'
)
)
7、查询学过‘001’并且也学过编号‘002’课程的同学的学号、姓名
思路:
先查到既选择001又选择002课程的所有同学
根据学生进行分组,如果学生数量等于2表示,两门均已选择
select student_id,sname FROM
(SELECT student_id,course_id from score where course_id =1 or course_id =2)as B
LEFT JOIN
student on B.student_id=student.sid GROUP BY student_id having count(student_id)>1
8、查询学过‘叶平’老师所教的所有课的同学的学号、姓名
select sid,sname from student where sid in (
select DISTINCT student_id from score where score.course_id in (
select cid from course left join teacher on course.teacher_id = teacher.tid where tname = '李平老师'
)
)
9、查询课程编号‘002’的成绩比课程编号‘001’课程低的所有同学的学号、姓名
同第一题,把大于号改成小于号
SELECT A.student_id,sw,wl from
(SELECT student_id,num as sw from score LEFT JOIN course on score.course_id=course.cid where course.cname='生物')as A
LEFT JOIN
(SELECT student_id,num as wl from score LEFT JOIN course on score.course_id=course.cid where course.cname='物理')as B
on A.student_id =B.student_id where sw < if (ISNULL(wl),0,wl)
我写了个更简单的
SELECT A.student_id,one,two from
(SELECT student_id,num as one FROM score where course_id=1)as A
LEFT JOIN
(SELECT student_id,num as two FROM score where course_id=2)as B
on A.student_id =B.student_id where one < if (ISNULL(two),0,two);
10、查询有课程成绩小于60分的同学的学号、姓名
SELECT sid,sname from student where sid in (SELECT DISTINCT student_id from score where num < 60 )
11、查询没学全所有课的同学的学号、姓名
思路:
在分数表中根据学生进行分组,获取每一个学生选课数量
如果数量==总课程数量,表示已经选择了所有课程
select student_id,sname from score left join student on score.student_id =student.sid group by student_id having count(course_id) =(select count(1) from course)
12、查询至少有一门课与学号为‘001’的同学所学相同的同学的学号和姓名(没有理解)
思路:
获取001同学选择的所有课程
获取课程在其中的所有人以及所有课程
根据学生筛选,获取所有学生信息
再与学生表连接,获取姓名
SELECT student_id,sname,count(course_id)
from score LEFT JOIN student on score.student_id=student.sid
where student_id!=1 and course_id in (SELECT course_id from score where student_id=1)GROUP BY student_id
13、查询至少学过学号为‘001’同学的所有课的其他同学学号和姓名(没有理解)
思路:
先找到和001的学过的所有人
然后个数=001所有学科 ==》》其他人可能选择的更多
select student_id,sname, count(course_id)
from score left join student on score.student_id = student.sid
where student_id != 1 and course_id in (select course_id from score where student_id = 1) group by student_id having count(course_id) = (select count(course_id) from score where student_id = 1)
14、查询和“002”号的同学学习的课程完全相同的其他同学学号和姓名;
思路:
个数相同
002学过的也学过
select student_id,sname from score left join student on score.student_id = student.sid where student_id in (
select student_id from score where student_id != 1 group by student_id HAVING count(course_id) = (select count(1) from score where student_id = 1)
) and course_id in (select course_id from score where student_id = 1) group by student_id HAVING count(course_id) = (select count(1) from score where student_id = 1)
15、删除学习‘李平’老师课的score表记录
delete from score where course_id in(select cid from course left join teacher on course.teacher_id = teacher.tid where teacher.tname = '李平老师')
不用连表写了一次
DELETE from score where course_id in (SELECT cid from course where teacher_id=(SELECT tid from teacher WHERE tname='李平老师'))
16、向sc表中插入一些记录,这些记录要求符合以下条件:没有上过编号‘002’课程的同学学号;插入'002'号课程的平均成绩
思路:
由于insert支持
insert into tb1(xx,xx) select x1,x2 from tb2;
所以,获取所有没上过002课的所有人,获取002的平均成绩
insert into score(student_id,course_id,num)
SELECT sid,2,(SELECT avg(num) from score where course_id=2)
from student WHERE sid not in (SELECT student_id from score where course_id=2)
17、按平均成绩从低到高,显示所有学生的‘生物’、‘物理’、‘体育’三门的课程成绩,按如下形式显示:学生ID,生物,物理,体育,有效课程数,有效平均分(没有理解)
select sc.student_id,
(select num from score left join course on score.course_id = course.cid where course.cname = "生物" and score.student_id=sc.student_id) as sy,
(select num from score left join course on score.course_id = course.cid where course.cname = "物理" and score.student_id=sc.student_id) as wl,
(select num from score left join course on score.course_id = course.cid where course.cname = "体育" and score.student_id=sc.student_id) as ty,
count(sc.course_id),
avg(sc.num)
from score as sc
group by student_id desc
18、查询各科成绩最高和最低分:以如下形式显示:课程ID,最高分,最低分
select course_id,MAX(num) as max_num,min(num) as min_num from score GROUP BY course_id;
19、按各科平均成绩从低到高和及格率的百分数从高到低顺序(没有理解)
思路:
case when .. then
select course_id, avg(num) as avgnum,sum(case when score.num > 60 then 1 else 0 END)/count(1)*100 as percent from score group by course_id order by avgnum asc,percent desc;
20、课程平均分从高到低显示(现实任课老师)没有理解并且sql运行失败
select avg(if(isnull(score.num),0,score.num)),teacher.tname from course
left join score on course.cid = score.course_id
left join teacher on course.teacher_id = teacher.tid
group by score.course_id
最新文章
- T-sql语句查询执行顺序
- Java内部类详解
- HTML+CSS学习笔记
- 数据库连接池之Proxool使用
- picasso总结
- hdu1115(计算多边形几何重心)
- JavaScipt 源码解析 数据缓存
- [转载]java NIO详解
- android 自定义控件中获取属性的三种方式(转)
- C#完整的通信代码(点对点,点对多,同步,异步,UDP,TCP)
- windows 7 64bit 下apache php mysql 环境配置
- 命令行运行android模拟器
- Flask学习记录之Flask-WTF
- Varint code
- Android中使用http协议访问网络
- POI/Excel/HTML单元格公式问题
- 第29月第18天 mac evpp环境
- JavaScript异步并发请求问题
- 54. Spiral Matrix(剑指offer--19)
- iOS-UI篇—简单的浏览器查看程序和Tomcat简单实现