mysql 练习题
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了mysql 练习题,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含17458字,纯文字阅读大概需要25分钟。
内容图文
![mysql 练习题](/upload/InfoBanner/zyjiaocheng/485/412f811d91584e6abcc71c09652a3449.jpg)
C:\Users\Administrator>mysqldump -u root db1>D:\agon\db1.sql -p 转储命令 select * from course; SELECT teacher_id,count(cname) from course group by teacher_id; select * from course left join teacher on course.teacher_id=teacher.tid; SELECT * from student left join class on student.class_id=class.cid; 2、查询“生物”课程比“物理”课程成绩高的所有学生的学号; 思路:获取所有生物课程的人(学号,成绩)-临时表 获取所有生物课程的人(学号,成绩)-临时表 根据 学号 连接两个临时表: 学号 物理成绩 生物成绩 然后在进行筛选 select A.student_id from (select student_id,num from score LEFT JOIN course on score.course_id=course.cid WHERE cname=‘生物‘) as A left join (select student_id,num from score LEFT JOIN course on score.course_id=course.cid WHERE cname=‘物理‘) as B on A.student_id=B.student_id where A.num>B.num; 3、查询平均成绩大于60分的同学的学号和平均成绩; select B.student_id,student.sname,b.avv from (SELECT student_id,avg(num) as avv from score GROUP BY student_id having avg(num)>60) as B left join student on B.student_id=student.sid; 4、查询所有同学的学号、姓名、选课数、总成绩;student.sid,count(student.sid) select student_id,count(student_id),sum(num),student.sname from score left join student on score.student_id=student.sid group by student_id; 5、查询姓“李”的老师的个数; select count(tname) from teacher where tname like‘李%‘; 6、查询没学过“叶平”老师课的同学的学号、姓名; 获取李平老师课程ID select cid from course left join teacher on course.teacher_id=teacher.tid where tname=‘李平老师‘; 查询所有学过老师课的学生的id, select student_id from score where course_id in (2,4) group by student_id; select * from tb12 where id in (select id from tb11) 这个是格式 select student.sid,student.sname from student where sid not in (select student_id from (select student_id from score where course_id in (select cid from course left join teacher on course.teacher_id=teacher.tid where tname=‘李平老师‘) group by student_id)as B ); 7、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名; 找到所有选了001课程的同学的id和所有选了002课程的同学的id 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); 不加having也行 8、查询学过“叶平”老师所教的所有课的同学的学号、姓名; 先找李平老师教过的课的id select cid from course LEFT JOIN teacher on course.teacher_id=teacher.tid where tname like ‘李平%‘; 通过cid找到对应的sid select student_id,sname from student INNER JOIN (SELECT student_id from score where course_id in (select cid from course LEFT JOIN teacher on course.teacher_id=teacher.tid where tname like ‘李平%‘))as B on B.student_id=student.sid GROUP BY student_id HAVING COUNT(student_id)>1; 9、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名; 先找到002课程的所有成绩和student_id在找到001的num和student_id select student_id,course_id,num from score where course_id=2; select student_id,course_id,num from score where course_id=1; 这里有个坑,我操操操,第57行如果有A.num和B.num的话会报重复列的错, 所以够用就行,不要多谢闲的蛋疼 SELECT student.sid,student.sname from ( select * from (select student_id,course_id,num from score where course_id=2)as A INNER JOIN (select student_id,course_id,num from score where course_id=1)as B on A.student_id=B.student_id where A.num<B.num )as C INNER JOIN student on C.student_id=student.sid; 10、查询有课程成绩小于60分的同学的学号、姓名; SELECT student.sid,sname,num FROM score INNER JOIN student on score.student_id=student.sid where num<60; 11、查询没有学全所有课的同学的学号、姓名; SELECT A.student_id,count(A.student_id),sname from (SELECT student_id,course_id from score)as A LEFT JOIN student on A.student_id=student.sid group by A.student_id HAVING count(A.student_id)<(select count(1) from course); 12、查询至少有一门课与学号为“001”的同学所学相同的同学的学号和姓名; 先找到学好为001的学生的课程ID 124 限制死了,就算你有三门也是124里面的三门,注意一下 select sid,sname from (SELECT student_id,course_id from score where student_id!=1 and course_id in (1,2,4))as A LEFT JOIN student on A.student_id=student.sid GROUP BY sid; 13、查询至少学过学号为“001”同学所选课程中任意一门课的其他同学学号和姓名; 也是限制死了,从select * from score WHERE course_id in (1,2,4) 分组后计数最多为3 但是可能人家是选了四门课,这是注意点 SELECT student.sid,student.sname from student INNER JOIN ( select student_id,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(1)=(SELECT count(course_id) from score WHERE student_id=1) )as A on student.sid=A.student_id; 14、查询和“001”号的同学学习的课程完全相同的其他同学学号和姓名; 先找到和001一样都学过3门的学生id 在通过course_id将表约束为只有1,2,4三门的学生成绩表,通过这个表分组筛选出个数为3的student_id明显没有符合条件的 select student_id from score where student_id in (select student_id from score where student_id!=1 GROUP BY student_id having count(course_id)=3) and course_id in(1,2,4) GROUP BY student_id having count(1)=3; select student_id from score where student_id in ( select student_id from score where student_id !=1 GROUP BY student_id HAVING count(1) = (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(1) = (select count(1) from score where student_id = 1) 15、删除学习“叶平”老师课的SC表记录; DELETE FROM score WHERE course_id in ( SELECT cid from course LEFT JOIN teacher on course.teacher_id=teacher.tid WHERE tname=‘李平老师‘ ); 16、向SC表中插入一些记录,这些记录要求符合以下条件:①没有上过编号“002”课程的同学学号;②插入“002”号课程的平均成绩; 用到insert into 表名(a,b,c) select a,b,c from 表名 // 如果bc不是表的列明则bc必须是单值 先找到选了002课程的所有同学的id 主要是讲新的插入姿势 insert into score(student_id,course_id,num) select student_id,2,(select avg(num) from score where course_id=2) from score WHERE course_id!=2; 17、按平均成绩从低到高显示所有学生的“语文”、“数学”、“英语”三门的课程成绩,按如下形式显示: 学生ID,语文,数学,英语,有效课程数,有效平均分; 知识点把列的内容横着放,涉及到子查询,每个学生ID对应三门成绩,还需把所有的null设置为0 select student_id, (select num from score as s2 where s2.student_id=s1.student_id and course_id=1)as 语文, (select num from score as s2 where s2.student_id=s1.student_id and course_id=3)as 数学, (select num from score as s2 where s2.student_id=s1.student_id and course_id=4)as 英语, avg(if(isnull(s1.num),0,s1.num)), avg(num) from score as s1 GROUP BY student_id ORDER BY avg(if(isnull(s1.num),0,s1.num)) desc; 18、查询各科成绩最高和最低的分:以如下形式显示:课程ID,最高分,最低分; select course_id,max(num),min(num) from score GROUP BY course_id; 19、按各科平均成绩从低到高和及格率的百分数从高到低顺序; 知识点:select case when num<60 then 0 else num end from score; case when..THEN..ELSE..END 可直接用于查 大于60分的=1,小于60分的=0,二者求和后就是及格人数,用及格人数/选该科目的总人数 就是及格率 select course_id,avg(num), sum(case when score.num>60 then 1 else 0 end)/count(1)*100 as percent from score GROUP BY course_id ORDER BY avg(num) asc,percent desc; 20、课程平均分从高到低显示(现实任课老师); 知识点:avg(if(isnull(score.num),0,score.num)) 如果为null则为0否则为null.num select course_id,teacher.tname,avg(if(isnull(score.num),0,score.num)) from score LEFT JOIN course on score.course_id=course.cid LEFT JOIN teacher on course.teacher_id=teacher.tid GROUP BY course_id ORDER BY avg(if(isnull(score.num),0,score.num)) desc; 21、查询各科成绩前三名的记录:(不考虑成绩并列情况) 考虑成绩并列的话,应该先按成绩分组,这样如果有3个100分则他们都是第一名 select course_id, (select num from score as s1 WHERE s1.course_id=s2.course_id GROUP BY num ORDER BY num desc LIMIT 0,1)as A, (select num from score as s1 WHERE s1.course_id=s2.course_id GROUP BY num ORDER BY num desc LIMIT 1,1)as B, (select num from score as s1 WHERE s1.course_id=s2.course_id GROUP BY num ORDER BY num desc LIMIT 2,1)as C from score as s2 GROUP BY course_id; 22、查询每门课程被选修的学生数; select course_id,count(1) from score GROUP BY course_id; 23、查询出只选修了一门课程的全部学生的学号和姓名; select student_id,student.sname from score LEFT JOIN student on score.student_id=student.sid GROUP BY student_id HAVING COUNT(course_id)=1 24、查询男生、女生的人数; select gender,COUNT(1) from student GROUP BY gender; 25、查询姓“张”的学生名单; SELECT sname from student where sname LIKE ‘张%‘; 26、查询同名同姓学生名单,并统计同名人数; SELECT sname,count(1) from student GROUP BY sname HAVING count(1)>1; 27、查询每门课程的平均成绩,结果按平均成绩升序排列,平均成绩相同时,按课程号降序排列; select course_id,avg(if(isnull(num),0,num)) from score GROUP BY course_id ORDER BY avg(if(isnull(num),0,num)) asc,course_id desc; 28、查询平均成绩大于85的所有学生的学号、姓名和平均成绩; SELECT student_id,sname,avg(if(ISNULL(num),0,num)) from score left join student on score.student_id=student.sid GROUP BY student_id HAVING avg(if(isnull(num),0,num))>85 29、查询课程名称为“数学”,且分数低于60的学生姓名和分数; select student.sid,sname from score LEFT JOIN course on score.course_id=course.cid LEFT JOIN student on score.student_id=student.sid where cname=‘生物‘ and num<60 30、查询课程编号为003且课程成绩在80分以上的学生的学号和姓名; select student_id,sname from score LEFT JOIN student on score.student_id=student.sid where course_id=3 and num>80 31、求选了课程的学生人数 select count(distinct student_id) from score; select count(1) from (select student_id from score GROUP BY student_id) as A; 32、查询选修“杨艳”老师所授课程的学生中,成绩最高的学生姓名及其成绩; select sname,num from score LEFT JOIN course on score.course_id=course.cid LEFT JOIN student on score.student_id=student.sid LEFT JOIN teacher on course.teacher_id=teacher.tid where tname=‘张磊老师‘ ORDER BY num desc LIMIT 1; 33、查询各个课程及相应的选修人数; select course.cname,count(1) from score LEFT JOIN course on course.cid=score.course_id GROUP BY course_id; 34、查询不同课程但成绩相同的学生的学号、课程号、学生成绩; select * from teacher as s1,teacher as s2; 5*5=25 笛卡尔机原理 select s1.student_id,s1.course_id,s1.num from score as s1,score as s2 where s1.sid!=s2.sid and s1.course_id!=s2.course_id and s1.num=s2.num; 35、查询每门课程成绩最好的前两名; SELECT course_id, (select num from score as s1 where s1.course_id=s2.course_id GROUP BY num ORDER BY num desc LIMIT 0,1)as A, (select num from score as s1 where s1.course_id=s2.course_id GROUP BY num ORDER BY num desc LIMIT 1,1)as B from score as s2 GROUP BY course_id; 36、检索至少选修两门课程的学生学号; SELECT student_id from score GROUP BY student_id HAVING count(course_id)>1; 37、查询全部学生都选修的课程的课程号和课程名; 把score以课程号分组加个条件:count(1)=student表中的行数===》这样拿到课程id,连表course拿到课程名 select course_id,count(1) from score GROUP BY course_id HAVING COUNT(1)=(SELECT count(1) from student) 38、查询没学过“叶平”老师讲授的任一门课程的学生姓名; 先找到李平老师教过的所有课的学生的id SELECT sid,sname from student where sid not in ( select student_id from score WHERE course_id in (select cid from course LEFT JOIN teacher on course.teacher_id=teacher.tid where tname=‘李平老师‘) GROUP BY student_id ); 39、查询两门以上不及格课程的同学的学号及其平均成绩; select student_id,count(1),avg(if(ISNULL(num),0,num)) from score where num<60 GROUP BY student_id HAVING COUNT(1)>2; 40、检索“004”课程分数小于60,按分数降序排列的同学学号; select student_id FROM score WHERE num<60 and course_id = 4 ORDER BY num desc; 41、删除“002”同学的“001”课程的成绩; DELETE from score where student_id=2 and course_id=1;
mysql 练习题
标签:isp from encoding rem data 生物 insert 数据 cat
本文系统来源:http://www.cnblogs.com/wuyongqiang/p/6979201.html
内容总结
以上是互联网集市为您收集整理的mysql 练习题全部内容,希望文章能够帮你解决mysql 练习题所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。