mysql经典练习题廖

转载

AI智行者 2024-11-27 08:25:54

文章标签 mysql经典练习题廖 mysql SQL 数据升序 文章分类 MySQL 数据库

SQL经典50题这里只贴个人解答，思路详细分析日后再说

第一部分建表


数据表介绍

--1.学生表
studentinfo(StuIdId,StuName,StuAge,StuSex)
--StuIdId 学生编号,StuName 学生姓名,StuAge 出生年月,StuSex 学生性别

--2.教师表
teacherinfo(TeacherId,TeacherName)
--TeacherId 教师编号,TeacherName 教师姓名

--3.课程表
courseinfo(CourseId,CourseName,fkTeacherId)
--CourseId 课程编号,CourseName 课程名称,fkTeacherId 教师编号

--4.成绩表
scoreinfo(ScoreId,fkStuId,fkCourseId,score)
--ScoreId 成绩编号，fkStuId 学生编号,fkCourseId 课程编号,score 分数

-- 请分两次执行代码 1、先创建表结构 2、insert数据

drop database if exists school;

create database school;

use school;

create table studentinfo(
	StuId int auto_increment primary key,
	StuName varchar(10) not null,
	StuAge datetime not null,
	StuSex char(1) not null
);

create table teacherinfo(
	TeacherId int auto_increment primary key,
	TeacherName varchar(10) not null
);

create table courseinfo(
	CourseId int auto_increment primary key,
	CourseName varchar(10) not null,
	fkTeacherId int not null
);

create table scoreinfo(
	ScoreId int auto_increment primary key,
	fkStuId int not null,
	fkCourseId int not null,
	score decimal(4,1) not null
);

alter table courseinfo
add constraint fk_course_teacher
foreign key (fkTeacherId)
references teacherinfo(TeacherId);

alter table scoreinfo
add constraint fk_score_stu
foreign key(fkStuId)
references studentinfo(StuId);

alter table scoreinfo
add constraint fk_score_course
foreign key(fkCourseId)
references courseinfo(CourseId);

--------------------- 模拟数据
insert into studentinfo 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','女');



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


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


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

代码片

第二部分练习题+解

练习题目
1.查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数

select 
    *
from
	scoreinfo S 
inner join
	scoreinfo T
on 
    S.fkStuId = T.fkStuId
where
	S.score>T.score and T.fkCourseId = 2 and S.fkCourseId = 1 ;

1.1 查询同时存在" 01 "课程和" 02 "课程的情况

1.2 查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )
略
1.3 查询不存在" 01 "课程但存在" 02 "课程的情况
略
2.查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩

select 
 U.stuId,U.stuName,avg(score)
from
	scoreinfo S
inner join 
	studentinfo U
on 
	S.fkStuId = U.StuId
group by 
	S.fkStuId
	having
	avg(score) >= 60;

3.查询在 scoreinfo 表存在成绩的学生信息
select 
	S.*
from
	studentinfo S
inner join
	scoreinfo F
on
	S.StuId = F.fkStuId
group by
	S.StuId
	;

4.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
select 
	U.StuId,U.StuName,count(S.score) cn,sum(S.score)
from
	studentinfo U
left join 
	scoreinfo S
on
	U.StuId = S.fkStuId
left join
	courseinfo C
on 
	S.fkCourseId = C.CourseId

group by 
	U.StuId;
	

4.1 查有成绩的学生信息
select 
	U.*
from
	studentinfo U
inner join 
	scoreinfo S
on
	U.StuId = S.fkStuId

group by 
	U.StuId;
	
5.查询「李」姓老师的数量
	select 
		count(*)
	from
		teacherinfo
	where
		TeacherName like '李%';

6.查询学过「张三」老师授课的同学的信息
select 
	U.*
from 
	studentinfo U
inner join
	scoreinfo S
on 
	U.StuId = S.fkStuId
inner join
	courseinfo C
on
	S.fkCourseId = C.CourseId
inner join
	teacherinfo T
on
	C.fkTeacherId = T.TeacherId and T.TeacherName = '张三'

7.查询没有学全所有课程的同学的信息
select 
	*
from
(
	select 
		U.StuId,U.StuName,count(S.score) cn
	from 
		studentinfo U
	left join
		scoreinfo S
	on
		U.StuId = S.fkStuId
	group by
		U.StuId 
) T
	where 
		T.cn < (select count(CourseId) from courseinfo );

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

select
    distinct studentinfo.*
from
	studentinfo
inner join 
	scoreinfo 
on
	studentinfo.StuId = scoreinfo.fkStuId
where fkCourseId  in
	(select 
		fkCourseId
	from 
		scoreinfo
	where	
		fkStuId = 1) 
	
	and
	fkStuId != 1
	;
9.查询和" 01 "号的同学学习的课程 完全相同的其他同学的信息
SELECT
	*
FROM
	(
		SELECT
			fkStuId,
			group_concat(
				fkCourseId
				ORDER BY
					fkCourseId
			) rst
		FROM
			scoreinfo
		WHERE
			fkStuId != 1
		GROUP BY
			fkStuId
	) A
WHERE
	rst = (
		SELECT
			group_concat(
				fkCourseId
				ORDER BY
					fkCourseId
			) rst2
		FROM
			scoreinfo
		WHERE
			fkStuId = 1
		GROUP BY
			fkStuId
	)


10.查询没学过"张三"老师讲授的任一门课程的学生姓名
	select if (find_in_set(C.CourseId,A),null,StuName) 
	from(
		
		select 
			StuName,group_concat(fkCourseId order by fkCourseId) A
		from 
			studentinfo U
		inner join
			scoreinfo S
		on
			U.StuId = S.fkStuId
		inner join
			courseinfo C
		on 
			S.fkCourseId = C.CourseId
		inner join
			teacherinfo T
		on	
			C.fkTeacherId = T.TeacherId
		group by 
			StuName
		)B
			
		inner join (
			select 
				CourseId 
			from
				courseinfo 
			inner join
				teacherinfo
			on 
				courseinfo.fkTeacherId = teacherinfo.TeacherId
			where 
				TeacherName = '张三'
		)C
		--  on  1=1
		 

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

select 
	StuId,StuName, avg(score)
from
	studentinfo U
inner join 
	scoreinfo S
on
	U.StuId = S.fkStuId
inner join 
	(
	select 
		fkStuId,count(1) cnt
	from
		scoreinfo
	where 
		score<60 
	group by 
		fkStuId
	)A
on S.fkStuId = A.fkStuId
where A.cnt>=2

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

select 
	U.*
from 
	studentinfo U
inner join (
	select 
		fkStuId,score
	from 
		scoreinfo 
	where 
		fkCourseId=1 and score<60
		) A
	on U.StuId = A.fkStuId
	order by
		score desc



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

select 
	fkStuId,StuName,avg(score) avg,group_concat(concat(CourseName,'-',score)) allscore
from 
	scoreinfo S
inner join
	studentinfo U
on
	S.fkStuId = U.StuId
inner join
	courseinfo C
on
	S.fkCourseId = C.CourseId
group by
	fkStuId
order by
	avg desc

14.查询各科成绩最高分、最低分和平均分：

以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率

及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90

要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
	
select fkCourseId,
count(ScoreId) 选修人数,
max(score),
min(score),
avg(score),
sum(D)/count(ScoreId) as 及格率, 
sum(C)/count(ScoreId) as 中等率,
sum(B)/count(ScoreId) as 优良率,
sum(A)/count(ScoreId) as 优秀率
from (select *,
case when  score>=60  then 1 else 0  end as D,
case when  score>=70  and score<80 then 1 else 0  end as C , 
case when  score>=80  and score<90 then 1 else 0  end as B ,
case when  score>=90  then 1 else 0  end as A 
from scoreinfo)a 
group by fkCourseId

order by count(ScoreId) desc,fkCourseId

-- 这里还可以简化，from后的条件也并到select后的子查询里
15.按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺

select 
	S1.fkCourseId, S1.fkStuId,S1.score, count(S2.score) +1 as rank
from 
	scoreinfo S1
left join
	scoreinfo S2
on 
	S1.fkCourseId=S2.fkCourseId and S1.score<s2.score
group by 
	S1.fkCourseId, S1.fkStuId, S1.score
order by
	S1.fkCourseId,rank asc

15.1 按各科成绩进行排序，并显示排名， Score 重复时合并名次
select 
	S1.fkCourseId, S1.fkStuId,S1.score, count(distinct(S2.score)) +1 as rank
from 
	scoreinfo S1
left join
	scoreinfo S2
on 
	S1.fkCourseId=S2.fkCourseId and S1.score<s2.score
group by 
	S1.fkCourseId, S1.fkStuId, S1.score
order by
	S1.fkCourseId,rank asc


16.查询学生的总成绩，并进行排名，总分重复时保留名次空缺

	SELECT
			A.fkStuId,
			总分1,
			count(总分2) as 名次
		FROM
			(
				select 
					fkStuId,sum(score) 总分1
				from
					scoreinfo
				GROUP BY
					fkStuId
				ORDER BY
					总分1 DESC
			)A
			inner join 
			(
				select 
					fkStuId,sum(score) 总分2
				from
					scoreinfo
				GROUP BY
					fkStuId
				ORDER BY
					总分2 DESC
			)B
			on
			
			总分1<=总分2
	GROUP BY
	fkStuId
ORDER BY
	名次 


16.1 查询学生的总成绩，并进行排名，总分重复时不保留名次空缺
SELECT
			A.fkStuId,
			总分1,
			count(DISTINCT 总分2) as 名次
		FROM
			(
				select 
					fkStuId,sum(score) 总分1
				from
					scoreinfo
				GROUP BY
					fkStuId
				ORDER BY
					总分1 DESC
			)A
			inner join 
			(
				select 
					fkStuId,sum(score) 总分2
				from
					scoreinfo
				GROUP BY
					fkStuId
				ORDER BY
					总分2 DESC
			)B
			on
			
			总分1<=总分2
	GROUP BY
	fkStuId
ORDER BY
	名次 

17.统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比
select 
	S.fkCourseId,
	C.CourseName,
  (IFNULL(A,0)) as '[100-85] 人数',
	CONCAT_WS('',ROUND(((IFNULL(A,0))/COUNT(fkStuId)*100),2),'%') AS '[100-85]',
(IFNULL(B,0)) as '[85-70] 人数',
	CONCAT_WS('',ROUND(((IFNULL(B,0))/COUNT(fkStuId)*100),2),'%') AS '[85-70]',
(IFNULL(U,0)) as '[70-60] 人数',
  CONCAT_WS('',ROUND(((IFNULL(U,0))/COUNT(fkStuId)*100),2),'%') AS '[70-60]',
(IFNULL(D,0)) as '[60-0] 人数',
  CONCAT_WS('',ROUND(((IFNULL(D,0))/COUNT(fkStuId)*100),2),'%') AS '[60-0]'
	
from 
	scoreinfo S
LEFT JOIN  
(
	select 
		fkCourseId,count(1) A
	from 
		scoreinfo
	where
		score>=85
	group by fkCourseId
)Z
on 
	S.fkCourseId = Z.fkCourseId
LEFT JOIN
(
	select 
		fkCourseId,count(1) B
	from 
		scoreinfo
	where
		score<85 and score>=70
	group by fkCourseId
)X
on 
	S.fkCourseId = X.fkCourseId
LEFT JOIN
(
	select 
		fkCourseId,count(1) U
	from 
		scoreinfo
	where
		score<70 and score>=60
	group by fkCourseId
)M
on 
	S.fkCourseId = M.fkCourseId
LEFT JOIN
(
	select 
		fkCourseId,count(1) D
	from 
		scoreinfo
	where
		score<60 
	group by fkCourseId
)V
on 
	S.fkCourseId = V.fkCourseId
inner JOIN
	courseinfo C
ON
  S.fkCourseId = C.CourseId
GROUP BY
	S.fkCourseId




18.查询各科成绩前三名的记录
select 
	fkCourseId,substring_index(group_concat(score order by score desc),',',3 )grade
from
	scoreinfo
group by
	fkCourseId


19.查询每门课程被选修的学生数
select 
	fkCourseId,count(0)
from 
	scoreinfo
group by
	fkCourseId

20.查询出只选修两门课程的学生学号和姓名
select 
	fkStuId,StuName
from	
	scoreinfo C
inner JOIN
	studentinfo S
ON
	C.fkStuId=S.StuId
group by 
	fkStuId
having 
	count(1) =2

21.查询男生、女生人数
select count(if (StuSex='男',StuSex,null) )as 男,count(if(StuSex='女',StuSex,null)) as 女

from studentinfo

=========================

select 
	count(1)
from
studentinfo
group by 
StuSex

22.查询名字中含有「风」
select
  *
  from studentinfo
  where StuName like '%风%'

23.查询同姓同性学生名单，并统计同名人数

select 
	concat(A.StuName,'和',B.StuName) as 同姓同性的是,count(A.StuId) as 人数
from 
	studentinfo A
inner join 
	studentinfo B
	
where
	left(A.StuName,1)=left(B.StuName,1) and A.StuId != B.StuId and A.StuSex = B.StuSex

24.查询 1990 年出生的学生名单

select 
    s.stuName
from
	studentinfo s
where
	year(StuAge)=1990




25.查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
select 
	fkCourseId,avg(score) grade
from
	scoreinfo
group by
	fkCourseId
order by
	grade desc ,fkCourseId

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

select
	stuId,stuName,grade
from
	studentinfo s 
inner join
	(
	select 
		fkStuId,avg(score) grade
	from
		scoreinfo c
	group by
		fkStuId
	)c
on
	s.stuId = c.fkStuId
where grade>=85
	

27.查询课程名称为「数学」，且分数低于 60 的学生姓名和分数
select 
   stuName,CourseName,score
from
	studentinfo s
inner join 
    scoreinfo c
	on
	s.stuId = c.fkStuId
inner join
	courseinfo i
	on
	c.fkCourseId = i.CourseId
where 
	CourseName ='数学' and score<60
28.查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）
select 
	stuId,stuName,fkCourseId,score
from
	studentinfo s
left join
	scoreinfo c
on 
	s.stuId = c.fkStuId
	



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

select 
	stuName , CourseName,score
from
	studentinfo s 
inner join
	scoreinfo c
on 
	s.stuId = c.fkStuId
inner join
	courseinfo i 
on 
	s.fkCourseId = i.CourseId
where
	score >70

30.查询不及格的课程

select 
	fkCourseId,CourseName,fkStuId,score
from
	scoreinfo
inner join
	courseinfo 
on
	scoreinfo.fkCourseId = courseinfo.CourseId
where 
	score<60

31.查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名
select 
	stuId,stuName,fkCourseId,score
from 
	studentinfo
inner join
	scoreinfo c 
on 
	studentinfo.stuId = c.fkStuId
where
	fkCourseId=1 and score>80

32.求每门课程的学生人数

select 
	fkCourseId,CourseName,count(1)
from
	scoreinfo S 
inner join
	courseinfo C 
on 
	S.fkCourseId = C.CourseId
group by
	fkCourseId

33.成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩

	select 
		 fkStuId,stuName,max(score)
	from
		studentinfo u
	inner join 
		scoreinfo s
	on 
		u.stuId = s.fkStuId
	inner join 
		courseinfo c
	on 
		s.fkCourseId = c.CourseId
	inner join 
		teacherinfo t
	on
		c.fkTeacherId = t.TeacherId
	where 
		TeacherName = '张三'
		
	
34.成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩

select 
	stuId,stuName,s.score,t.TeacherName
	from
		studentinfo u
	inner join 
		scoreinfo s
	on 
		u.stuId = s.fkStuId
	inner join 
		courseinfo c
	on 
		s.fkCourseId = c.CourseId
	inner join 
		teacherinfo t
	on
		c.fkTeacherId = t.TeacherId
	where 
		TeacherName='张三' and 
		(select count(1) from scoreinfo c1 inner join  courseinfo info on c1.fkCourseId = info.CourseId
		where c1.score>s.score and info.CourseId =2
					)=0

35.查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
	select 
		s.fkStuId, s.fkCourseId,s.score,s1.fkStuId, s1.fkCourseId,s1.score
	from
		scoreinfo s
	inner join
		scoreinfo s1
	where 
		s.fkCourseId < s1.fkCourseId and  s.score = s1.score 

36.查询每门功成绩最好的前两名
	select 
	   fkCourseId,SUBSTRING_INDEX(group_concat(info order by  score desc ), ',' ,2)
	from	
		(
			select 
				fkCourseId,CONCAT_WS('-',fkStuId,stuName,score) as info,score
			from 
				scoreinfo s
			inner JOIN	
				studentinfo u
			ON
				s.fkStuId = u.stuId
			)c
	group by 
		fkCourseId

37.统计每门课程的学生选修人数（超过 5 人的课程才统计）。
select 
	fkCourseId,count(1) cnt
from 
	scoreinfo 
group by
	fkCourseId
having cnt >5


38.检索至少选修两门课程的学生学号
select 
	fkStuId,count(fkCourseId) cnt
from 
	scoreinfo
group by
	fkStuId
having 
	cnt >=2
39.查询选修了全部课程的学生信息
select 
	fkStuId,stuName
from
	scoreinfo c 
inner join 
	studentinfo s 
on
	c.fkStuId = s.stuId
group by 
	fkStuId
having 
	count(fkCourseId) = (SELECT COUNT(DISTINCT fkCourseId) from scoreinfo)

40.查询各学生的年龄，只按年份来算
select 
    StuId,StuName,(year(now())-year(StuAge)) as age
FROM
	studentinfo

41.按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一
select 
	StuId,StuName,ceil(datediff(now(),StuAge)/365)
FROM
	studentinfo

42.查询本周过生日的学生
select StuId,StuName

	from  studentinfo

WHERE
 week(concat(year(now()),'-',DATE_FORMAT(StuAge,'%m-%d')))  = week(now())
43.查询下周过生日的学生
select StuId,StuName

	from  studentinfo

WHERE
 week(concat(year(now()),'-',DATE_FORMAT(StuAge,'%m-%d')))  = week(now())+1

44.查询本月过生日的学生
select 
	StuId,StuName
from 
	studentinfo
where
	month(StuAge)=month(now())

45.查询下月过生日的学生
select 
	StuId,StuName
from 
	studentinfo
where
	month(StuAge)=month(now())+1

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。