mysql题目练习的答案
rianley 博客 盗版必究！
基于上一个sql练习的答案；该答案是我所写！如果有错误或者用法不当之处；请下方评论指出；感激不尽！谢谢各位码友！
<!--1、查询所有的课程的名称以及对应的任课老师姓名 -->
SELECT
course.cname,
teacher.tname
FROM
course
INNER JOIN teacher ON course.teacher_id = teacher.tid;
<!--2、查询学生表中男女生各有多少人-->
SELECT
gender
count(1)
FROM
student
GROUP BY
gender;
<!--3、查询物理成绩等于100的学生的姓名-->
SELECT
student.sname
FROM
student
WHERE
sid IN (
SELECT
student_id
FROM
score
INNER JOIN course ON score.course_id = course.cid
WHERE
course.cname = '物理'
AND score.num = 100
);
<!--4、查询平均成绩大于八十分的同学的姓名和平均成绩-->
SELECT
student.sname,
t1.avg_num
FROM
student
INNER JOIN (
SELECT
student_id,
avg(num) AS avg_num
FROM
score
GROUP BY
student_id
HAVING
avg(num) > 80
) AS t1 ON student.sid = t1.student_id;
<!--5、查询所有学生的学号，姓名，选课数，总成绩 -->
SELECT
student.sid,
student.sname,
t1.course_num,
t1.total_num
FROM
student
LEFT JOIN (
SELECT
student_id,
COUNT(course_id) course_num,
sum(num) total_num
FROM
score
GROUP BY
student_id
) AS t1 ON student.sid = t1.student_id;
<!--6、 查询姓李老师的个数-->
SELECT
count(tid)
FROM
teacher
WHERE
tname LIKE '李%';
<!--7、 查询没有报李平老师课的学生姓名 -->
SELECT
student.sname
FROM
student
WHERE
sid NOT IN (
SELECT DISTINCT
student_id
FROM
score
WHERE
course_id IN (
SELECT
course.cid
FROM
course
INNER JOIN teacher ON course.teacher_id = teacher.tid
WHERE
teacher.tname = '李平老师'
)
);
<!--8、 查询物理课程比生物课程高的学生的学号 -->
SELECT
t1.student_id
FROM
(
SELECT
student_id,
num
FROM
score
WHERE
course_id = (
SELECT
cid
FROM
course
WHERE
cname = '物理'
)
) AS t1
INNER JOIN (
SELECT
student_id,
num
FROM
score
WHERE
course_id = (
SELECT
cid
FROM
course
WHERE
cname = '生物'
)
) AS t2 ON t1.student_id = t2.student_id
WHERE
t1.num > t2.num;
<!--方法二-->
SELECT x.sname from
(SELECT c.sid,c.sname,b.num from course as a INNER JOIN score as b on a.cid=b.course_id
and a.cname ='物理'
LEFT JOIN student as c on b.student_id=c.sid)
as x INNER JOIN
(SELECT c.sid,c.sname,b.num from course as a INNER JOIN score as b on a.cid=b.course_id
and a.cname ='生物'
LEFT JOIN student as c on b.student_id=c.sid)
as y on x.sid=y.sid WHERE x.num>y.num
<!--9、 查询没有同时选修物理课程和体育课程的学生姓名 -->
SELECT
student.sname
FROM
student
WHERE
sid IN (
SELECT
student_id
FROM
score
WHERE
course_id IN (
SELECT
cid
FROM
course
WHERE
cname = '物理'
OR cname = '体育'
)
GROUP BY
student_id
HAVING
COUNT(course_id) = 1
);
<!--10、查询挂科超过两门(包括两门)的学生姓名和班级 -->
SELECT
student.sname,
class.caption
FROM
student
INNER JOIN (
SELECT
student_id
FROM
score
WHERE
num < 60
GROUP BY
student_id
HAVING
count(course_id) >= 2
) AS t1
INNER JOIN class ON student.sid = t1.student_id
AND student.class_id = class.cid;
<!--11、查询选修了所有课程的学生姓名 -->
SELECT
student.sname
FROM
student
WHERE
sid IN (
SELECT
student_id
FROM
score
GROUP BY
student_id
HAVING
COUNT(course_id) = (SELECT count(cid) FROM course)
);
<!--12、查询李平老师教的课程的所有成绩记录-->
<!-- 方法一 -->
select
score.num
from
score
join
(select cid from course inner join teacher on course.teacher_id = teacher.tid where tid =2) as t1
on
score.course_id=t1.cid;
<!-- 方法二 -->
select
score.num
from
score
where
course_id
in (select cid from course inner join teacher on course.teacher_id = teacher.tid where tid =2)
<!--13、查询全部学生都选修了的课程号和课程名-->
<!-- 方式一 -->
SELECT
cid,
cname
FROM
course
WHERE
cid IN ( select course_id from score group by course_id having COUNT(student_id) = (select count(sid) from student) ) <!-- 注解：--先将所有的课程分组 然后 过滤学生没有全选择的课程，在通过cid 找出 对应的课程号与课程名-->
);
<!--方式二-->
SELECT course.cname,count(course.cid) as count_id from course INNER JOIN score on course.cid=score.course_id
group by course.cid HAVING count_id=(SELECT count(sid) from student);
<!--方式三-->
SELECT a.cid,a.cname from course as a INNER JOIN score as b on a.cid=b.course_id
GROUP BY a.cid HAVING count(a.cid)=(SELECT count(sid) from student);
<!--14、查询每门课程被选修的次数-->
select course_id,count(student_id) from score group by student_id;
<!--15、查询之选修了一门课程的学生姓名和学号-->
SELECT
sid,
sname
FROM
student
WHERE
sid IN (
SELECT
student_id
FROM
score
GROUP BY
student_id
HAVING
COUNT(course_id) = 1
);
<!--16、查询所有学生考出的成绩并按从高到低排序（成绩去重）-->
SELECT DISTINCT sum(if(b.num is null,0,b.num)) as all_num from
student as a LEFT JOIN score as b on a.sid=b.student_id
GROUP BY a.sid ORDER BY all_num DESC
<!--17、查询平均成绩大于85的学生姓名和平均成绩-->
SELECT
sname,
t1.avg_num
FROM
student
INNER JOIN (
SELECT
student_id,
avg(num) avg_num
FROM
score
GROUP BY
student_id
HAVING
AVG(num) > 85
) t1 ON student.sid = t1.student_id;
<!--18、查询生物成绩不及格的学生姓名和对应生物分数-->
SELECT
sname 姓名,
num 生物成绩
FROM
score
LEFT JOIN course ON score.course_id = course.cid
LEFT JOIN student ON score.student_id = student.sid
WHERE
course.cname = '生物'
AND score.num < 60;
<!--19、查询在所有选修了李平老师课程的学生中，这些课程(李平老师的课程，不是所有课程)平均成绩最高的学生姓名-->
SELECT
sname
FROM
student
WHERE
sid = (
SELECT
student_id
FROM
score
WHERE
course_id IN (
SELECT
course.cid
FROM
course
INNER JOIN teacher ON course.teacher_id = teacher.tid
WHERE
teacher.tname = '李平老师'
)
GROUP BY
student_id
ORDER BY
AVG(num) DESC
LIMIT 1
);
<!--20、查询每门课程成绩最好的前两名学生姓名-->
SELECT c.course_id,c.student_id,c.num
from
score as c LEFT JOIN
(SELECT b.course_id,max(num) as second_num from (SELECT course_id,max(num) first_num from score GROUP BY course_id) as a LEFT JOIN score as b
on a.course_id=b.course_id WHERE a.first_num>b.num GROUP BY b.course_id) as b on c.course_id=b.course_id and b.second_num=c.num
LEFT JOIN
(SELECT b.course_id,b.student_id,a.first_num from (SELECT course_id,max(num) first_num from score GROUP BY course_id) as a LEFT JOIN score as b on a.course_id=b.course_id and a.first_num=b.num) as a
on c.course_id=a.course_id and a.first_num=c.num
WHERE a.first_num is NOT NULL or b.second_num is NOT NULL
GROUP BY c.student_id,c.num
ORDER BY c.course_id,c.num desc
<!--21、查询不同课程但成绩相同的学号，课程号，成绩-->
SELECT a.student_id,a.course_id,a.num from (SELECT num,course_id,student_id FROM score
GROUP BY course_id,num) as a
GROUP BY a.num
HAVING count(a.num)>1
<!--22、查询没学过“叶平”老师课程的学生姓名以及选修的课程名称；-->
SELECT DISTINCT d.sname,b.cname from teacher as a INNER JOIN course as b
on a.tid=b.teacher_id and a.tname!='叶平老师'
LEFT JOIN score as c on c.course_id=b.cid
LEFT JOIN student as d on d.sid=c.student_id
<!--23、查询所有选修了学号为1的同学选修过的一门或者多门课程的同学学号和姓名；-->
SELECT DISTINCT b.sid,b.sname FROM score as a LEFT JOIN student as b
on a.student_id=b.sid
WHERE a.course_id in (SELECT course_id FROM score WHERE student_id=1)
<!--24、任课最多的老师中学生单科成绩最高的学生姓名-->
SELECT b.cname,b.num,c.sname from <!-- 注：这里没有考虑2个老师任课数目一样，考虑了单科成绩可能会一样-->
(SELECT a.cname,max(a.num) as max_num from (
SELECT a.cname,b.num,b.student_id from course as a LEFT JOIN score as b on a.cid=b.course_id
WHERE a.teacher_id =(SELECT COUNT(teacher_id) as teacher_idx FROM course GROUP BY teacher_id
ORDER BY teacher_idx desc LIMIT 1)
) as a
GROUP BY a.cname) as a LEFT JOIN (
SELECT a.cname,b.num,b.student_id from course as a LEFT JOIN score as b on a.cid=b.course_id
WHERE a.teacher_id =(SELECT COUNT(teacher_id) as teacher_idx FROM course GROUP BY teacher_id
ORDER BY teacher_idx desc LIMIT 1)
) as b on a.cname=b.cname and a.max_num=b.num
LEFT JOIN student as c on c.sid=b.student_id
<!------------------------------------------------------------->
<!--扩展题-->
<!-- 问题来了，有个上班打卡表，记录公司多个user的打开记录（每天至少打卡2次），表里字段：id,user_id,sign_time，要求统计出2018-6月份user迟到情况,9点以后算迟到 -->
CREATE TABLE sign_in(id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
`user_id` int not null ,
sign_time datetime not null )ENGINE=INNODB CHARSET=UTF8
<!--迟到情况 大于9点算是迟到--用substr 获取>
select a.sign_time,a.user_id from sign_in as a inner join (select id,sign_time from sign_in where sign_time >='2018-6-1 00:00:00' and sign_time < '2018-7-1 00:00:00'
GROUP BY user_id,DATE(sign_time) having substr(sign_time,12,2) > '09') as t1
on a.id= t1.id
<!--迟到情况 大于9点算是迟到-- 用 time 获取>
SELECT user_id, MIN(sign_time) as min_sign_time from sign_in WHERE sign_time like '2018-06%' GROUP BY user_id,DATE(sign_time) HAVING TIME(min_sign_time)>'09:00:00'
<!--统计出上个月上班不足8小时的user的情况-->
SELECT user_id, MIN(sign_time) as min_sign_time,MAX(sign_time) as max_sign_time from sign_in WHERE sign_time like '2018-06%' GROUP BY user_id,DATE(sign_time) HAVING UNIX_TIMESTAMP(max_sign_time)-UNIX_TIMESTAMP(min_sign_time)<8*3600
作者邮箱：rianleycheng@gmail.com
作者QQ：2855132411
