mysql 题目 --1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数
select s_id,c_id,score as score_01 from stu_sco where c_id='01';
select s_id,c_id,score as score_02 from stu_sco where c_id='02';
-- 然后我们就可以通过多表查询得到那些"01"课程比"02"课程成绩高的学生s_id
select a.s_id from (select s_id,c_id,score as score_01 from stu_sco where c_id='01') a
inner join
(select s_id,c_id,score as score_02 from stu_sco where c_id='02')b
on a.s_id=b.s_id and a.score_01>b.score_02;
-- 得到"01"课程比"02"课程成绩高的学生s_id后,利用这些s_id
select a.*,c.c_id,c.score from student a left join
(select a.s_id from (select s_id,c_id,score as score_01 from stu_sco where c_id='01') a
inner join
(select s_id,c_id,score as score_02 from stu_sco where c_id='02')b
on a.s_id=b.s_id and a.score_01>b.score_02) b
on a.s_id=b.s_id
inner join stu_sco c on b.s_id=c.s_id;
-- 上面的方法的逻辑理解简单,便于根据类似的题型作出灵活修改,但是写起来繁琐。
-- 如果题目不要求查询出目标对象的所有课程,只要求查询出‘01’和‘02’课程,我们可以做两次内连接,学生表内连接到分数表a,再内连接到分数表b,再作筛选,是可以实现相同的查询目的的。
select a.*,b.score as '语文',c.score as '数学' from student a
inner join stu_sco b on a.s_id=b.s_id and c_id ='01'
inner join stu_sco c on b.s_id=c.s_id and c.c_id ='02'
where b.score>c.score;
--2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数
select a.*,c.c_id,c.score from student a left join
(select a.s_id from (select s_id,c_id,score as score_01 from stu_sco where c_id='01') a
inner join
(select s_id,c_id,score as score_02 from stu_sco where c_id='02')b
on a.s_id=b.s_id and a.score_01<b.score_02) b
on a.s_id=b.s_id
inner join stu_sco c on b.s_id=c.s_id;
--3、查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
SELECT student.s_id,student.s_name,stu_sco.score FROM student RIGHT JOIN
(SELECT stu_sco.AVG(score) FROM stu_sco GROUP BY s_id)stu_sco on student.s_id=stu_sco.s_id WHERE stu_sco.score >= 60 ORDER BY student.s_id;
--4、查询平均成绩小于 60 分的同学的学生编号和学生姓名和平均成绩
SELECT student.s_id,student.s_name,stu_sco.score FROM student RIGHT JOIN
(SELECT stu_sco.AVG(score) FROM stu_sco GROUP BY s_id)stu_sco on student.s_id=stu_sco.s_id WHERE stu_sco.score =< 60 ORDER BY student.s_id;
--5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
SELECT student.s_id,student.s_name,course.c_name,stu_sco.score FROM
student RIGHT JOIN stu_sco ON student.s_id=stu_sco.s_id RIGHT JOIN course
ON stu_sco.c_id=course.c_id
--6、查询"李"姓老师的数量
SELECT * FROM teacher WHERE t_name LIKE '李%';
--7、查询学过"张三"老师授课的同学的信息
SELECT teacher.t_name,course.c_name,student.s_id,student.s_name,student.s_age FROM teacher RIGHT JOIN course
on teacher.t_id=course.t_id RIGHT JOIN stu_sco ON course.c_id=stu_sco.c_id RIGHT JOIN student
ON stu_sco.s_id=student.s_id WHERE t_name = '张三'
--8、查询没学过"张三"老师授课的同学的信息
select a.*,b.c_id from student a inner join stu_sco b
on a.s_id=b.s_id and b.c_id in (select b.c_id from teacher a , course b where a.t_id=b.t_id and a.t_name!='张三');
--9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
SELECT student.s_id,student.s_name,student.s_age,student.s