MySQL综合练习（50道）

drop table if exists student;
create table student(
s_id int,
s_name varchar(8),
s_birth date,
s_sex char(4)
);

insert into student values
(1,'赵雷','1990-01-01','男'),
(2,'钱电','1990-12-21','男'),
(3,'孙风','1990-05-20','男'),
(4,'李云','1990-08-06','男'),
(5,'周梅','1991-12-01','女'),
(6,'吴兰','1992-03-01','女'),
(7,'郑竹','1989-07-01','女'),
(8,'王菊','1990-01-20','女');


drop table if exists score;
create table score(
s_id int,
c_id int,
s_score int
);

insert into score values
(1,1,80),
(1,2,90),
(1,3,99),
(2,1,70),
(2,2,60),
(2,3,65),
(3,1,80),
(3,2,80),
(3,3,80),
(4,1,50),
(4,2,30),
(4,3,40),
(5,1,76),
(5,2,87),
(6,1,31),
(6,3,34),
(7,2,89),
(7,3,98);


drop table if exists course;
create table course(
c_id int,
c_name varchar(8),
t_id int
);

insert into course values
(1,'语文',2),
(2,'数学',1),
(3,'英语',3);

drop table if exists teacher;
create table teacher(
t_id int,
t_name varchar(8)
);

insert into teacher values
(1,'张三'),
(2,'李四'),
(3,'王五');

二、SQL练习（50道）

1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数

-- 方法1
select s.*, sc1.s_score as score_01, sc2.s_score as score_02 
from student s, 
     (select s_id, s_score from score where c_id = 1) sc1,
		 (select s_id, s_score from score where c_id = 2) sc2
 where sc1.s_score > sc2.s_score
 and sc1.s_id = sc2.s_id
 and sc1.s_id = s.s_id
 
-- 方法2
select s.*, sc1.s_score score_01, sc2.s_score score_02
from score sc1, score sc2, student s
where sc1.s_id = sc2.s_id 
and sc1.c_id = 1 
and sc2.c_id = 2
and sc1.s_score > sc2.s_score 
and s.s_id = sc1.s_id;

-- 方法3
select s.*,sc1.s_score score_01,sc2.s_score score02 
from student s
inner join (select * from score where c_id = 1) sc1
on s.s_id = sc1.s_id
inner join (select * from score where c_id = 2) sc2
on s.s_id = sc2.s_id
where sc1.s_score > sc2.s_score;

2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数

同(1)

3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩

-- 方法1
select s.s_id, s_name, round(avg(s_score),2) avg_score
from student s, score
where s.s_id = score.s_id
group by s.s_id,s_name
having avg_score >= 60;

-- 方法2
select s.s_id, s_name, avg_score
from student s, 
     (select s_id, round(avg(s_score),2) avg_score 
		  from score
			group by s_id
			having avg_score >= 60) as temp
where s.s_id = temp.s_id;

4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩(包括有成绩的和无成绩的)

-- 方法1 （先找到平均成绩小于60的学生信息，在用union连接，加上没有选课的学生信息）
select s.s_id, s_name, avg_score
from student s, 
    (select s_id, round(avg(s_score),2) avg_score
		 from score
		 group by s_id
		 having avg_score < 60) temp
where s.s_id = temp.s_id
union
select s_id, s_name, 0
from student 
where s_id not in (select s_id from score)


-- 方法2  没有成绩即该学生没有选课， 查询结果需要包含成绩为空的学生信息， 使用外连接
select s.s_id, s_name,
      (case when avg_score is null then 0 else avg_score end) avg_score 
from student s
left join 
    (select s_id ,round(avg(s_score),2) avg_score
		 from score
		 group by s_id) temp 
on temp.s_id = s.s_id
where avg_score < 60 or avg_score is null

5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

-- 方法1
select s.s_id, s_name, count(c_id) cnt_course,
       (case when sum(s_score) is null then 0 else sum(s_score) end) sum_score
from student s 
left join score 
on score.s_id = s.s_id
group by s.s_id, s_name	

-- 方法2
select s.s_id, s_name,
       (case when cnt_course is null then 0 else cnt_course end) cnt_course,
			 (case when sum_score is null then 0 else sum_score end) sum_score
from student s 
left join 
     (select s_id, count(c_id) cnt_course, sum(s_score) sum_score
		  from score
			group by s_id) temp
on temp.s_id = s.s_id

6、查询"李"姓老师的数量

select count(t_name)  from teacher where t_name like '%李%';

7、询学过"张三"老师授课的同学的信息

-- 方法1（嵌套查询）
select * 
from student
where s_id IN(
     select s_id 
		 from score 
		 where c_id IN(
		     select c_id
				 from course
				 where t_id IN(
				     select t_id
						 from teacher
						 where t_name = '张三'))); 

-- 方法2（自然连接）
select student.* 
from student natural join score
						 natural join course
						 natural join teacher
where t_name = '张三';

8、查询没学过"张三"老师授课的同学的信息

select * from student
where s_id NOT IN (
    select s_id from score
		where c_id IN(
		    select c_id from course
				where t_id IN(
				    select t_id from teacher
						where t_name='张三')));

9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息

-- 方法1
select * from student
where s_id IN (select s_id from score where c_id = 1)
and s_id IN (select s_id from score where c_id = 2)


-- 方法2
select s.* 
from student s,
		 (select s_id from score where c_id = 1) sc1,
		 (select s_id from score where c_id = 2) sc2
where s.s_id = sc1.s_id
and s.s_id = sc2.s_id

10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息

-- 方法1
select * from student
where s_id IN (select s_id from score where c_id = 1)
and s_id NOT IN (select s_id from score where c_id = 2)

-- 方法2（使用except，做减法, 包含条件1但不包含条件2的学生）
select s.* 
from student s natural join score where c_id = 1
except 
select s.* 
from student s natural join score where c_id = 2

11、查询没有学全所有课程的同学的信息

-- 方法1 （用union连接 没选修完课程的学生id 和 没有选修课程的学生id）
select s.* from student s
where s_id IN (
               select s_id 
							 from score
							 group by s_id
							 having count(*) < (select count(*) from course)
							 )
union 
select s.* from student s
where s_id NOT IN (
							 select s_id
							 from score
              )		

-- 方法2  查询选修全部课程的学生—————即不存在这样一个学生，该生没有
select * from student
where s_id IN (
               select s_id from student s1
							 where NOT EXISTS (
							       select * from course
										 where NOT EXISTS (
										      select * from score
													where score.s_id = s1.s_id
													and score.c_id = course.c_id)))

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

-- 方法1
select * from student s
where s_id <> 1
and s_id IN (
             select s_id from score
						 where c_id IN(
						              select c_id from score
													where s_id = 1
													)
						 )

-- 方法2
select distinct s.*
from student s, score sc1, score sc2
where s.s_id = sc1.s_id
and sc1.c_id = sc2.c_id
and sc2.s_id = 1
and sc1.s_id <> 1

13、查询和"01"号的同学学习的课程完全相同的其他同学的信息

-- (即 没有一门课程是01同学选了，而其他同学没有选的)
select s.* from student s
where s.s_id <> 1
and NOT EXISTS (
                select * from score sc1
								where sc1.s_id = 1
								and NOT EXISTS (
								                select * from score sc2
																where sc2.s_id = s.s_id
																and sc2.c_id = sc1.c_id
																)
								)

14、查询没学过"张三"老师讲授的任一门课程的学生姓名

select * from student
where s_id NOT IN (
    select s_id from score
		where c_id IN(
		    select c_id from course
				where t_id IN(
				    select t_id from teacher
						where t_name='张三')));

15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

-- 方法1
select s.s_id, s_name, avg_score
from student s join(
								 select s_id , round(avg(s_score),2) avg_score
								 from score
								 where s_score < 60
								 group by s_id
								 having count(*) >= 2
								 )temp
on temp.s_id = s.s_id
)


-- 方法2
select s.s_id, s_name, round(avg(s_score),2) avg_score
from student s, score sc
where s.s_id = sc.s_id
and s_score < 60
group by s.s_id, s_name
having count(*) >= 2;

16、检索"01"课程分数小于60，按分数降序排列的学生信息

-- 方法1
select s.* 
from student s join (
                    select s_id, s_score
										from score
										where c_id = 1
										and s_score < 60
)temp on temp.s_id = s.s_id
and s_score < 60
order by s_score desc


-- 方法2
select s.*
from student s
join score sc on sc.s_id = s.s_id
where c_id = 1
and s_score < 60
order by s_score desc

17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

select s.s_id, s_name,
			 sum(case c_id when 1 then s_score else 0 end) 语文,
			 sum(case c_id when 2 then s_score else 0 end) 数学,
			 sum(case c_id when 3 then s_score else 0 end) 英语,
			 ifnull(round(avg(s_score),2),0) 平均成绩 
 from student s 
 left join score sc 
 on sc.s_id = s.s_id
 group by s.s_id,s_name
 order by 平均成绩 desc;

18、查询各科成绩最高分、最低分、平均分、及格率（60分以上）、中等率（70-80）、优良率（80-90）、优秀率（90以上）

select c.c_id 课程ID, c_name 课程名称, 
       max(s_score) 最高分, min(s_score) 最低分, round(avg(s_score),2) 平均分,
			 concat(round(sum(case when s_score >=60 then 1 else 0 end)/count(*) * 100, 2), '%') 及格率,
			 concat(round(sum(case when s_score between 70 and 80 then 1 else 0 end)/count(*) * 100, 2), '%') 中等率,
			 concat(round(sum(case when s_score between 80 and 90 then 1 else 0 end)/count(*) * 100, 2), '%') 优良率,
			 concat(round(sum(case when s_score >= 90 then 1 else 0 end)/count(*) * 100, 2), '%') 优秀率
from course c join score sc on sc.c_id = c.c_id
group by c.c_id, c_name

19、按各科成绩进行排序，并显示排名

-- 先查询各科的排名，再将查询结果命名为temp, 和student,course连接
select c.c_id, c_name, s.s_id, s_name, s_score, srank
from student s, course c,
		(select s_id, c_id, s_score,
       1+(select count(*)
			    from score sc1
					where sc1.c_id = sc2.c_id
					and sc1.s_score > sc2.s_score
					) srank
     from score sc2
		 )temp
where temp.s_id = s.s_id
and temp.c_id = c.c_id
order by temp.c_id, s_score desc

20、查询学生的总成绩并进行排名

select s.s_id, s_name, sum_score,
			1+(select count(*) 
			   from
				   	(select s_id, sum(s_score) sum_score from score group by s_id) t1
				 where t1.sum_score > t2.sum_score	
				 ) srank	
from (select s_id, sum(s_score) sum_score
      from score 
			group by s_id) t2,
			student s
where s.s_id = t2.s_id
order by srank asc			

-- 方法2
WITH t as
  (select s_id, sum(s_score) sum_score
	 from score 
	 group by s_id
	 )
select s_name, t2.s_id, sum_score,
       1+(select count(*) 
			    from t t1
					where t1.sum_score > t2.sum_score
					) srank
from t t2, student s
where t2.s_id = s.s_id
order by srank

21、查询不同老师所教不同课程平均分从高到低显示

-- 方法1
select t.t_id, t_name, c.c_id, c_name, round(avg(s_score),2) avg_score
from score sc
join course c on c.c_id = sc.c_id
join teacher t on t.t_id = c.t_id
group by t.t_id, t_name, c.c_id, c_name
order by t.t_id, avg_score desc

-- 方法2
select t.t_id, t_name, c.c_id, c_name, avg_score
from (
      select c_id, round(avg(s_score),2) avg_score
			from score
			group by c_id
			) sc
join course c on c.c_id = sc.c_id
join teacher t on t.t_id = c.t_id
order by t.t_id, avg_score desc

22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩

-- 22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
select c.c_id, c_name, s.s_id, s_name, s_score, srank
from student s, course c,
		 (select s_id, c_id, s_score,
			1+(select count(*)
			   from score sc1
				 where sc1.s_score > sc2.s_score
				 )srank
			from score sc2	
			) temp
where s.s_id = temp.s_id
and c.c_id = temp.c_id
and srank in (2,3)
order by temp.c_id, s_score desc

23、统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比

select c.c_id 课程ID, c_name 课程名称, 
			 concat(round(sum(case when s_score between 0 and 60 then 1 else 0 end)/count(*) * 100, 2), '%') 及格率,
			 sum(case when s_score between 0 and 60 then 1 else 0 end) 及格人数,
			 concat(round(sum(case when s_score between 60 and 70 then 1 else 0 end)/count(*) * 100, 2), '%') 中等率,
			 sum(case when s_score between 60 and 70 then 1 else 0 end) 中等人数,
			 concat(round(sum(case when s_score between 70 and 85 then 1 else 0 end)/count(*) * 100, 2), '%') 优良率,
			 sum(case when s_score between 70 and 85 then 1 else 0 end) 优良人数,
			 concat(round(sum(case when s_score between 85 and 100 then 1 else 0 end)/count(*) * 100, 2), '%') 优秀率,
			 sum(case when s_score between 85 and 100 then 1 else 0 end) 优秀人数
from course c join score sc on sc.c_id = c.c_id
group by c.c_id, c_name

24、查询学生平均成绩及其名次

-- 先计算每个学生的平均成绩， 再用排名的sql语句
WITH t as(
 select s_id, round(avg(s_score),2) avg_score
 from score
 group by s_id
)
select t2.s_id, s_name, avg_score,
       1+ (
					  select count(*)
					  from t t1
						where t1.avg_score> t2.avg_score
			  ) srank
from t t2 natural join student
order by srank asc

25、查询各科成绩前三名的记录

select c_id, c_name, s_id, s_name, s_score
from student natural join score
						 natural join course
						 natural join (
	                         select s_id, c_id, s_score
													 from score s1
													 where (
																		 select count(*)
																		 from score s2
																		 where s2.c_id = s1.c_id
																		 and s2.s_score > s1.s_score 
													         ) < 3				 
  						              ) temp
order by c_id, s_score desc

26、查询每门课程被选修的学生数

-- 方法1
select c_id, c_name, count(*) cnt_studnet
from student s natural join score sc 
               natural join course c
group by c_id, c_name

-- 方法2
select c.c_id, c_name, cnt_student
from course c join ( 
                    select c_id, count(*) cnt_student
										from score
										group by c_id
										) temp
on temp.c_id = c.c_id

27、查询出只有两门课程的全部学生的学号和姓名

-- 方法1
select s_id, s_name
from student
where s_id IN (
               select s_id
							 from score
							 group by s_id
							 having count(*)=2	
              )

-- 方法2
select s_id, s_name
from student natural join score
group by s_id, s_name
having count(*) = 2

28、查询男生、女生人数

-- 方法1
select s_sex, count(*) cnt_sex
from student
group by s_sex;

-- 方法2
select  sum(case s_sex when '男' then 1 else 0 end) 男生人数,
        sum(case s_sex when '女' then 1 else 0 end) 女生人数
from student

29、查询名字中含有"风"字的学生信息

select * from student
where s_name like '%风%'

30、查询同名同性学生名单，并统计同名人数

select s_name, s_sex, count(*) cnt_student
from student
group by s_name, s_sex
having cnt_student > 1

31、查询1990年出生的学生名单

select * from student
where year(s_birth) = '1990'

32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列

-- 方法1
select c_id, c_name, round(avg(s_score) ,2) avg_score
from course natural join score 
group by c_id, c_name
order by avg_score desc, c_id asc

-- 方法2 
select c.c_id, c_name, round(avg(s_score) ,2) avg_score
from course c 
join score sc on sc.c_id = c.c_id
group by c.c_id, c_name 
order by avg_score desc, c_id asc

-- 方法3
select c.c_id, c_name, avg_score
from course c join (
                     select c_id, round(avg(s_score) ,2) avg_score
										 from score
										 group by c_id
										) temp
on temp.c_id = c.c_id
order by avg_score desc, c_id asc

33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩

-- 方法1
select s_id, s_name, round(avg(s_score) ,2) avg_score
from student natural join score 
group by s_id, s_name
having avg_score >= 85;

-- 方法2
select s.s_id, s_name, avg_score
from student s join (
										select s_id, round(avg(s_score) ,2) avg_score
										from score
										group by s_id
										) temp
on temp.s_id = s.s_id
where avg_score >= 85

34、查询课程名称为"数学"，且分数低于60的学生姓名和分数

-- 方法1
select s_name, s_score
from student natural join score
             natural join course
where c_name = '数学'
and s_score < 60


-- 方法2
select s_name, s_score
from student s join (
                      select s_id, c_id, s_score
											from score
											where s_score < 60
											) temp
on temp.s_id = s.s_id
join course c on c.c_id = temp.c_id
where c_name = '数学'

35、查询所有学生的课程及分数情况

select s.s_id, s_name,
			sum(case c_id when 1 then s_score else 0 end) 语文,
			sum(case c_id when 2 then s_score else 0 end) 数学,
			sum(case c_id when 3 then s_score else 0 end) 英语
from student s natural join score
group by s.s_id, s_name

36、查询任何一门课程成绩在70分以上的学生姓名、课程名称和分数

-- 任何一门成绩都要大于70， 则表示最低成绩也要大于70
select s_name, c_name, s_score
from student s natural join score
               natural join course
where s_id IN (
                select s_id
								from score
								group by s_id
								having min(s_score) > 70
								)

37、查询课程不及格的学生

-- 方法1
select s.* , c_name, s_score
from student s natural join score
               natural join course
where s_score < 60

-- 方法2
select s_name, c_name, s_score
from score sc 
join student s on s.s_id = sc.s_id
join course c on c.c_id = sc.c_id
where s_score < 60

38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名

-- 方法1
select s_id, s_name, s_score
from student s natural join score sc 
               natural join course c
where c_id = 1
and s_score >= 80

-- 方法2
select s.s_id, s_name
from student s
where s_id IN (
               select s_id 
							 from score
							 where c_id = 1
							 and s_score >= 80
							 )

39、求每门课程的学生人数

-- 方法1
select c.c_id, c_name, count(*) cnt_student
from course c natural join score 
group by c.c_id, c_name

-- 方法2
select c.c_id, c_name, cnt_student
from course c join (
                    select c_id, count(*) cnt_student
										from score
										group by c_id
										) temp
on temp.c_id = c.c_id

40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩


-- 方法1 张三老师所授课程中学生的最高分数，  再求张三老师所授课程中学生的成绩等于最高分数的学生
select s.*, s_score
from student s natural join score sc 
               natural join course c
							 natural join teacher t
where t_name = '张三'
and s_score = (
							  select max(s_score)
								from student s natural join score sc 
								               natural join course c
							                 natural join teacher t
                where t_name = '张三'
              )

-- 方法2 
select s.*, s_score
from student s natural join score sc 
							natural join course c
							natural join teacher t
where t_name = '张三'
order by s_score desc limit 1

41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩


-- 按成绩分组，选择count(*)>1 ，再根据s_score 选择score表中的元组
select * from score
where s_score IN (
                   select s_score from score
									 group by s_score
									 having count(*) > 1
									 )

42、查询每门课程成绩最好的前三名

select c.c_id, c_name, s.s_id, s_name, s_score
from (
      select s_id, c_id, s_score
			from score s1
			where (
			        select count(*) 
							from score s2
							where s2.s_score > s1.s_score
							and s2.c_id = s1.c_id
							) < 3
			) temp
join student s on s.s_id = temp.s_id
join course c on c.c_id = temp.c_id
order by temp.c_id, temp.s_score desc

43、统计每门课程的学生选修人数（超过5人的课程才统计）

select c_id, c_name , count(*) cnt_student
from score natural join course
group by c_id, c_name
having count(*)>=5

44、检索至少选修两门课程的学生学号

select * from student
where s_id IN (
               select s_id from score
							 group by s_id
							 having count(*) >=2
							 )

45、查询选修了全部课程的学生信息

-- 方法1
select * from student s
where s_id IN (
							 select s_id from score 
							 group by s_id
							 having count(*) = (
							                      select count(*) from course
												          )
						   )

-- 方法2
select * from student 
where NOT EXISTS (
                   select * from course
									 where NOT EXISTS (
									                    select * from score
																			where score.c_id = course.c_id
																			and student.s_id = score.s_id
																			)
							    )

46、查询各学生的年龄(周岁)

select *,
			 (
			      date_format(now(), '%Y') - date_format(s_birth, '%Y') -
			       (case when date_format(now(), '%M%D')>date_format(s_birth, '%M%D') then 0 else 1 end)
			 ) as age
from student

47、查询本周过生日的学生

# 1、选取原生日里的月日成分（按照字符串进行处理）
#    >>> MID(sage,6,5)
# 2、将选取的月日成分与今年的年份相接
#    >>> CONCAT(YEAR(CURDATE()),"-" ,MID(sage,6,5))
# 3、使拼接后的日期的周数和 now/curdate 的周数相等即可确定本周过生日
select * from student
where week(concat(year(curdate()),"-", mid(s_birth,6,5))) = week(curdate())

48、查询下周过生日的学生

select * from student
where week(concat(year(curdate()),"-", mid(s_birth,6,5))) = week(curdate())+1

49、查询本月过生日的学生

select * from student where month(s_birth) = month(now())

select * from student
where month(concat(year(curdate()), "-", mid(s_birth,6,5))) = month(curdate())

50、查询12月份过生日的学生

select * from student where month(s_birth)=12

select * from student
where month(concat(year(curdate()), "-", mid(s_birth,6,5))) = 12

最后，博主把整合版本的sql练习放在下面，需要的宝子们自取。

student-test.sql资源-CSDN文库

柿子镭

关注

3
点赞
踩
38

收藏

觉得还不错? 一键收藏
4
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录