Mysql 语句练习

本文转自：https://blog.csdn.net/fashion2014/article/details/78826299

#1.查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
select a.s_id as id,a.s_name as name,ROUND(AVG(b.s_score),2) as avg_score from student a,score b where a.s_id=b.s_id GROUP BY a.s_id HAVING avg_score>=60 

select b.s_id,b.s_name,ROUND(AVG(a.s_score),2) as avg_score from student b join score a on b.s_id = a.s_id GROUP BY b.s_id,b.s_name HAVING avg_score >=60;

#2.查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
#(包括有成绩的和无成绩的)
select a.s_id as id,a.s_name as name,ROUND(AVG(b.s_score),2) as avg_score from student a LEFT JOIN score b on (a.s_id=b.s_id) GROUP BY a.s_id HAVING avg_score<60 
UNION
select a.s_id,a.s_name,0 as avg_score from student a where a.s_id not in (select distinct s_id from score);

#3.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
#不包含没选课的人
select a.s_id as id,a.s_name as name,count(b.c_id) as course_count,sum(b.s_score) as total_score from student a,score b where a.s_id=b.s_id GROUP BY a.s_id 

#4.包含没有选课的人
select a.s_id as id,a.s_name as name,count(b.c_id) as course_count,sum(b.s_score) as total_score from student a left JOIN score b on a.s_id=b.s_id GROUP BY a.s_id

#5.查询学过"张三"老师授课的同学的信息
select student.* from  student JOIN score on student.s_id=score.s_id where score.c_id in
(SELECT course.c_id from course where course.t_id=
(select teacher.t_id  FROM teacher where teacher.t_name='张三'))

#6.查询没学过"张三"老师授课的同学的信息
select * from student where student.s_id not in(select student.s_id from student JOIN score on student.s_id=score.s_id where score.c_id  in
(SELECT course.c_id from course where course.t_id=
(select teacher.t_id  FROM teacher where teacher.t_name='张三')))

#7.查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
select  student.* from student JOIN score on student.s_id=score.s_id WHERE score.c_id in ('01','02') GROUP BY student.s_id HAVING count(student.s_id)=2

select a.* from 
	student a,score b,score c 
	where a.s_id = b.s_id  and a.s_id = c.s_id and b.c_id='01' and c.c_id='02';
	
select a.* from 
	student a 
	where a.s_id in (select s_id from score where c_id='01' ) and a.s_id in(select s_id from score where c_id='02')

#8查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
select a.* from 
	student a 
	where a.s_id in (select s_id from score where c_id='01' ) and a.s_id not in(select s_id from score where c_id='02')
	
#9查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩 
select a.s_id as id,a.s_name as name,ROUND(AVG(b.s_score),2) as avg_score from student a,score b where a.s_id=b.s_id GROUP BY a.s_id HAVING avg_score>=85

#10查询没有学全所有课程的同学的信息
select student.* from student where s_id not in 
(
select s_id from score GROUP BY s_id HAVING count(score.c_id)=(SELECT COUNT(*) from course)
)

#11查询至少有一门课与学号为"01"的同学所学相同的同学的信息

select DISTINCT a.* from student a JOIN score b on a.s_id=b.s_id and a.s_id!='01'
where c_id in (select c_id from score where s_id='01')