MYSQL 练习
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了MYSQL 练习,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含11057字,纯文字阅读大概需要16分钟。
内容图文
![MYSQL 练习](/upload/InfoBanner/zyjiaocheng/473/fec1047f98244f3ea631a102a1f443bf.jpg)
2、查询“生物”课程比“物理”课程成绩高的所有学生的学号;
思路:
获取所有有生物课程的人(学号,成绩) - 临时表
获取所有有物理课程的人(学号,成绩) - 临时表
根据【学号】连接两个临时表:
学号 物理成绩 生物成绩
然后再进行筛选
select A.student_id,生物,物理 from (select student_id,number as 生物 from score left join course on score.corse_id = course.cid where course.cname = ‘生物‘) as A left join (select student_id,number as 物理 from score left join course on score.corse_id = course.cid where course.cname = ‘物理‘) as B on A.student_id = B.student_id where 物理 > 生物 ;
3、查询平均成绩大于60分的同学的学号和平均成绩;
思路:
根据学生分组,使用
avg
获取平均值,通过
having
对
avg
进行筛选
select student_id,avg(number) from score GROUP BY student_id having avg(number)>60;
4、查询所有同学的学号、姓名、选课数、总成绩;
SELECT
score.student_id,
COUNT(score.corse_id),
SUM(score.number),
student.sname
FROM
score
LEFT JOIN student ON student.sid = score.student_id
GROUP BY
student_id
5、查询姓“李”的老师的个数
select COUNT(tid) from teacher where tname LIKE"波%";
select count(1) from (select tid from teacher where tname like ‘波%‘) as B
6、查询没学过“叶平”老师课的同学的学号、姓名;
思路:
先查到“叶平老师”老师教的所有课ID
获取选过课的所有学生ID
学生表中筛选
select * from student where sid not in(
select student_id from score WHERE score.corse_id in(
select cid from course
LEFT JOIN teacher on teacher.tid = course.teache_id where teacher.tname = "波多"
)
)
7、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名;
思路:
先查到既选择001又选择002课程的所有同学
根据学生进行分组,如果学生数量等于2表示,两门均已选择
select student_id,sname from
(select student_id,corse_id from score where corse_id = 1 or corse_id = 2) as B
left join student on B.student_id = student.sid group by student_id HAVING count(student_id) = 2
8、查询学过“叶平”老师所教的所有课的同学的学号、姓名;
select * from student where sid in(
select student_id from score WHERE score.corse_id in(
select cid from course
LEFT JOIN teacher on teacher.tid = course.teache_id where teacher.tname = "波多"
)
)
9、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名;
select c.student_id,student.sname from
(select A.student_id,a,b from
(select student_id,number as a from score left join course on score.corse_id = course.cid where course.cid = 1) as A
left join
(select student_id,number as b from score left join course on score.corse_id = course.cid where course.cid = 2) as B
on A.student_id = B.student_id where b > a ) as c
LEFT JOIN
student on c.student_id = student.sid;
10、查询有课程成绩小于60分的同学的学号、姓名;
select sid,sname from student where sid in (
select distinct student_id from score where number < 60
)
select sid,sname from student where sid in (
select student_id from score where number < 60 GROUP BY student_id
)
11、查询没有学全所有课的同学的学号、姓名;
思路:
在分数表中根据学生进行分组,获取每一个学生选课数量
如果数量 == 总课程数量,表示已经选择了所有课程
select student_id,sname from score left join student on score.student_id = student.sid
group by student_id HAVING count(corse_id) = (select count(cid) from course)
12、查询至少有一门课与学号为“001”的同学所学相同的同学的学号和姓名;
思路:
获取 001 同学选择的所有课程
获取课程在其中的所有人以及所有课程
根据学生筛选,获取所有学生信息
再与学生表连接,获取姓名
select student_id,sname, count(corse_id)
from score left join student on score.student_id = student.sid
where student_id != 1 and corse_id in (select corse_id from score where student_id = 1) group by student_id
13、查询至少学过学号为“001”同学所有课的其他同学学号和姓名;
先找到和001的学过的所有人
然后个数 = 001所有学科 ==》 其他人可能选择的更多
select student_id,sname, count(corse_id)
from score left join student on score.student_id = student.sid
where student_id != 1 and corse_id in (select corse_id from score where student_id = 1)
group by student_id
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(corse_id) = (select count(1) from score where student_id = 1)
) and corse_id in (select corse_id from score where student_id = 1) group by student_id HAVING count(corse_id) = (select count(1) from score where student_id = 1)
15、删除学习“叶平”老师课的score表记录;
delete from score where corse_id in (
select cid from course left join teacher on course.teache_id = teacher.tid where teacher.tname = ‘饭岛‘
)
16、向SC表中插入一些记录,这些记录要求符合以下条件:①没有上过编号“002”课程的同学学号;②插入“002”号课程的平均成绩;
insert into score(student_id, corse_id, number) select sid,2,(select avg(number) from score where corse_id = 2)
from student where sid not in (
select student_id from score where corse_id = 2
)
17、按平均成绩从低到高 显示所有学生的“语文”、“数学”、“英语”三门的课程成绩,按如下形式显示: 学生ID,语文,数学,英语,有效课程数,有效平均分;
select sc.student_id,
(select number from score left join course on score.corse_id = course.cid where course.cname = "生物" and score.student_id=sc.student_id) as sy,
(select number from</
本文系统来源:http://www.cnblogs.com/lst1010/p/5994159.html
内容总结
以上是互联网集市为您收集整理的MYSQL 练习全部内容,希望文章能够帮你解决MYSQL 练习所遇到的程序开发问题。
如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。
来源:【匿名】