MySQL经典多表查询试题_mysql多表查询练习题（答案）1、查询所有的课程的名称以及对应的任课⽼师姓名
SELECT
courseame,
FROM
course
INNER JOIN teacher acher_id=teacher.tid;2、查询学⽣表中男⼥⽣各有多少⼈
SELECT
gender,
count(sid)
FROM
student
GROUP BY
gender;3、查询物理成绩等于100的学⽣的姓名
SELECT
sname
FROM
student
以下属于html5新标签的是WHERE
sid IN (
SELECT
score.student_id
FROM
score
LEFT JOIN course urse_id=course.cid
WHERE
score.num= 100AND courseame= "物理");4、查询平均成绩⼤于⼋⼗分的同学的姓名和平均成绩
SELECT
t1.sname,
t2.avg_num
FROM
女生做ui设计师有多累
student t1
INNER JOIN (
student_id,
avg(num) avg_num
FROM
score
GROUP BY
student_id
HAVING
avg(num)> 80) t2 ON t1.sid=t2.student_id;5、查询所有学⽣的学号，姓名，选课数，总成绩(注意：对于那些没有选修任何课程的学⽣也算在内)
SELECT
sid,
sname,
t1.sum_num
FROM
student
kerastase使用方法
LEFT JOIN (
SELECT
student_id,
count(course_id) count_course,
sum(num) sum_num
FROM
score
GROUP BY
phpinclude函数
student_id
) t1 ON student.sid=t1.student_id;6、 查询姓李⽼师的个数
SELECT
count(tid)
FROM
teacher
WHERE
tname LIKE'李%';7、 查询没有报李平⽼师课的学⽣姓名(出报名李平⽼师课程的学⽣，然后取反就可以)
SELECT
FROM
student
WHERE
sid NOT IN (
SELECT DISTINCT
student_id
FROM
score
WHERE
course_id IN (
SELECT
cid
FROM
course
WHERE
teacher_id=(
SELECT
tid
FROM
teacher
WHERE
tname= '李平')
)
)
;8、 查询物理课程的分数⽐⽣物课程的分数⾼的学⽣的学号SELECT
t1.student_id
FROM
(
SELECT
student_id,
num
FROM
score
LEFT JOIN course urse_id=course.cid
mysql语句多表查询WHERE
cname= '物理') t1
LEFT JOIN (
SELECT
student_id,
num
FROM
score
LEFT JOIN course urse_id=course.cid
WHERE
cname= '⽣物') t2 ON t1.student_id=t2.student_id
WHERE
t1.num>t2.num;9、 查询没有同时选修物理课程和体育课程的学⽣姓名(没有同时选修指的是选修了⼀门的，思路是得到物理+体育课程的学⽣信息表，然后基于学⽣分组，统计count(课程)=1)
SELECT
sname
FROM
student
WHEREpm安装教程
sid IN (
SELECT
student_id
FROM
score
WHERE
course_id IN (
SELECT
cid
FROM
course
WHERE
cname IN ('物理', '体育')
)
GROUP BY
student_id
HAVING
count(student_id)= 1);10、查询挂科超过两门(包括两门)的学⽣姓名和班级(求出<60的表，然后对学⽣进⾏分组，统计课程数⽬>=2)#⽅式⼀：
SELECT
t1.sname,
t2.caption
FROM
(
SELECT
class_id,
sname
FROM
student
WHERE
sid IN (
SELECT
student_id
FROM
score
WHERE
num< 60GROUP BY
student_id
HAVING
count(course_id)> 1)
) t1
INNER JOIN (SELECT cid, caption FROMclass) t2 ON t1.class_id =t2.cid;#⽅式⼆：
SELECT
student.sname,class.caption
FROM
student
INNER JOIN (
SELECT

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。