mysql复杂查询练习题_mysql复杂查询，优质题⽬表结构
1.⾃⾏添加测试数据
2.查询平均成绩⼤于60分的同学的学号和平均成绩；
SELECT t1.sid,AVG(t2.number)
FROM student t1 LEFT JOIN score t2 ON t1.sid=t2.student_id
GROUP BY t1.sid HAVING AVG(t2.number)>60;
3.查询所有同学的学号、姓名、选课数、总成绩；
SELECT t1.sid,t1.p2
FROM student t1,(SELECT student_id,COUNT(sid) temp1 FROM score GROUP BY student_id) t2,
(SELECT student_id,SUM(number) temp2 FROM score GROUP BY student_id) t3
WHERE t1.sid=t2.student_id AND t1.sid=t3.student_id;
#较复杂，有难度
4.查询姓“李”的⽼师的个数；
SELECT COUNT(tid)
FROM teacher
WHERE tname LIKE '李%';
5.查询没学过“叶平”⽼师课的同学的学号、姓名；
SELECT t1.`sid`,t1.`sname`
FROM student t1
WHERE t1.`sid` NOT IN(SELECT student_id
FROM score
WHERE course_id IN(SELECT t2.cid
FROM teacher t1 LEFT JOIN course t2 ON t1.`tid`=t2.`teacher_id`
ame='叶平' ORDER BY t2.`cid`));
#⼦句
SELECT student_id
FROM scoremysql面试题库
WHERE course_id IN(SELECT t2.cid
FROM teacher t1 LEFT JOIN course t2 ON t1.`tid`=t2.`teacher_id` ame='叶平' ORDER BY t2.`cid`);
#较复杂
6.查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；#⽅法1
SELECT t1.`student_id`,t2.`sname`
FROM score t1 LEFT JOIN student t2 ON t1.`student_id`=t2.`sid` WHERE course_id=1
UNION ALL
SELECT t1.`student_id`,t2.`sname`
FROM score t1 LEFT JOIN student t2 ON t1.`student_id`=t2.`sid` WHERE course_id=2;
#⽅法2
SELECT t1.sid,t1.sname
FROM (SELECT t1.`student_id` sid,t2.`sname` sname
FROM score t1 LEFT JOIN student t2 ON t1.`student_id`=t2.`sid` WHERE course_id=1
外挂软件免费
UNION ALL
SELECT t1.`student_id`,t2.`sname`
FROM score t1 LEFT JOIN student t2 ON t1.`student_id`=t2.`sid` WHERE course_id=2) t1
GROUP BY t1.sid HAVING COUNT(*)>1;
7.查询学过“叶平”⽼师所教的所有课的同学的学号、姓名；
SELECT t1.`student_id`,t2.`sname`
FROM score t1 LEFT JOIN student t2 ON t1.`student_id`=t2.`sid` WHERE t1.`course_id` IN(SELECT t2.cid
FROM teacher t1 LEFT JOIN course t2 ON t1.`tid`=t2.`teacher_id` ame='叶平' ORDER BY t2.`cid`);
#⼦句
SELECT t2.cid
FROM teacher t1 LEFT JOIN course t2 ON t1.`tid`=t2.`teacher_id`
ame='叶平' ORDER BY t2.`cid`;
8.查询课程编号“002”的成绩⽐课程编号“001”课程低的所有同学的学号、姓名；
#⼦句1
SELECT number
FROM score
WHERE course_id=2;
#⼦句2
SELECT number
FROM score t1
WHERE t1.`course_id`=1;
#总语句
SELECT t3.`sid`,t3.`sname`
FROM (SELECT student_id,number
FROM score
WHERE course_id=2) t1,(SELECT student_id,number
modify和reviseFROM score
WHERE course_id=1) t2,student t3
WHERE t1.student_id=t3.sid AND t2.student_id=t3.sid AND t1.number
9.查询有课程成绩⼩于60分的同学的学号、姓名；
#⽅法1
SELECT DISTINCT t1.student_id,t2.`sname`
FROM score t1 LEFT JOIN student t2 ON t1.`student_id`=t2.`sid`java批处理
WHERE t1.number<60;
#⽅法2
SELECT t1.sid,t1.sname
FROM student t1,(SELECT student_id FROM score WHERE number<60 GROUP BY student_id) t2 WHERE t1.sid=t2.student_id;
10.查询没有学全所有课的同学的学号、姓名；
#总语句
SELECT sid,sname
FROM student
WHERE sid NOT IN(SELECT student_id
FROM score
舍曲林能改善躯体症状GROUP BY student_id HAVING COUNT(course_id)=(SELECT COUNT(cid) FROM course));
#⼦句
SELECT sid,sname
FROM student
WHERE sid NOT IN();
导轨的种类#⼦句：学全了的学⽣编号
SELECT student_id
FROM score
GROUP BY student_id HAVING COUNT(course_id)=(SELECT COUNT(cid) FROM course); #⼦句：课程的总数
SELECT COUNT(cid) FROM course;
11.查询⾄少有⼀门课与学号为“001”的同学所学相同的同学的学号和姓名；
#总语句
SELECT DISTINCT t1.student_id,t2.`sname`
FROM score t1 LEFT JOIN student t2 ON t1.`student_id`=t2.`sid`
WHERE course_id IN(SELECT course_id
FROM score
WHERE student_id=1);
#⼦句：学号1所选所有课程
SELECT course_id
FROM score
WHERE student_id=1
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论