MySQL 经典练习 50 题

最新推荐文章于 2024-07-15 17:39:27 发布

有智青年i

最新推荐文章于 2024-07-15 17:39:27 发布

阅读量88

点赞数

分类专栏： MySQL 文章标签：数据库 mysql

本文链接：https://blog.csdn.net/m0_64622390/article/details/132191724

版权

MySQL 专栏收录该内容

15 篇文章 1 订阅

订阅专栏

--1 查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数
-- 2 查询同时存在" 01 "课程和" 02 "课程的学生信息
-- 3 查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )
-- 4 查询不存在" 01 "课程但存在" 02 "课程的情况
-- 5. 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩

-- 6. 查询在 Score 表存在成绩的学生信息
-- 7 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
-- 8 查没有成绩的学生信息
-- 9. 查询「李」姓学生的数量
-- 10. 查询学过「张三」老师授课的同学的信息-- 空间复杂度：对内存空间的占用情况-- 时间复杂度：对CPU的占用情况（逻辑判断的次数）
-- 11. 查询没有学全所有课程的同学的信息（没学过的也算）
-- 12 查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
-- 13 查询和" 01 "号的同学学习的课程 完全相同的其他同学的信息
-- 14 查询没学过"张三"老师讲授的任一门课程的学生姓名
-- 15 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
-- 16 检索" 01 "课程分数小于 60，按分数降序排列的学生信息
-- 17 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
-- 18.查询各科成绩最高分、最低分和平均分：
以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，
优秀率 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
-- 19 按各科成绩进行排序，并各科成绩从高到底显示排名， Score 重复时保留名次空缺 RANK()
-- 20各科成绩进行排序，并显示排名， Score 重复时合并名次
-- 21查询学生的总成绩，总成绩并进行排名，总分重复时保留名次空缺
-- 22统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比
-- 23查询各科成绩前三名的记录
-- 24查询每门课程被选修的学生数
-- 25 查询出只选修两门课程的学生学号和姓名
-- 26 查询男生、女生人数
-- 27 查询名字中含有「小」字的学生信息
-- 28 查询同名同性学生名单，并统计同名人数
-- 29查询 1990 年出生的学生名单
-- 30查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
-- 31查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
-- 32查询课程名称为「数学」，且分数低于 60 的学生姓名和分数
-- 33查询所有学生的课程名称及分数情况（存在学生没成绩，没选课的情况）
-- 34查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
-- 35 查询不及格的课程
-- 36 查询课程编号为 01 且课程成绩在 60 分以上的学生的学号和姓名
-- 37 求每门课程的学生人数
-- 38成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩 ROW_NUMBER()
-- 39成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
-- 40查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
-- 41查询每门功成绩最好的前两名 RANK
-- 42统计每门课程的学生选修人数（超过 5 人的课程才统计）。
-- 43检索至少选修两门课程的学生学号
-- 44查询选修了全部课程的学生信息
-- 45 查询各学生的年龄，只按年份来算
-- 46 按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一;
-- 47 查询本周过生日的学生
-- 48 查询下周过生日的学生 -
-- 49 查询本月过生日的学生
-- 50询下月过生日的学生

CREATE TABLE `Student` (
	`s_id` VARCHAR ( 20 ),
	`s_name` VARCHAR ( 20 ) NOT NULL DEFAULT '',
	`s_birth` VARCHAR ( 20 ) NOT NULL DEFAULT '',
	`s_sex` VARCHAR ( 10 ) NOT NULL DEFAULT '',
	PRIMARY KEY ( `s_id` ) 
);-- 课程表
CREATE TABLE `Course` (
	`c_id` VARCHAR ( 20 ),
	`c_name` VARCHAR ( 20 ) NOT NULL DEFAULT '',
	`t_id` VARCHAR ( 20 ) NOT NULL,
	PRIMARY KEY ( `c_id` ) 
);-- 教师表
CREATE TABLE `Teacher` ( `t_id` VARCHAR ( 20 ), `t_name` VARCHAR ( 20 ) NOT NULL DEFAULT '', PRIMARY KEY ( `t_id` ) );-- 成绩表
CREATE TABLE `Score` ( `s_id` VARCHAR ( 20 ), `c_id` VARCHAR ( 20 ), `s_score` INT ( 3 ), PRIMARY KEY ( `s_id`, `c_id` ) );-- 插入学生表测试数据
INSERT INTO Student
VALUES
	( '01', '赵雷', '1990-01-01', '男' );
INSERT INTO Student
VALUES
	( '02', '钱电', '1990-12-21', '男' );
INSERT INTO Student
VALUES
	( '03', '孙风', '1990-05-20', '男' );
INSERT INTO Student
VALUES
	( '04', '李云', '1990-08-06', '男' );
INSERT INTO Student
VALUES
	( '05', '周梅', '1991-12-01', '女' );
INSERT INTO Student
VALUES
	( '06', '吴兰', '1992-03-01', '女' );
INSERT INTO Student
VALUES
	( '07', '郑竹', '1989-07-01', '女' );
INSERT INTO Student
VALUES
	( '08', '王菊', '1990-01-20', '女' );-- 课程表测试数据
INSERT INTO Course
VALUES
	( '01', '语文', '02' );
INSERT INTO Course
VALUES
	( '02', '数学', '01' );
INSERT INTO Course
VALUES
	( '03', '英语', '03' );-- 教师表测试数据
INSERT INTO Teacher
VALUES
	( '01', '张三' );
INSERT INTO Teacher
VALUES
	( '02', '李四' );
INSERT INTO Teacher
VALUES
	( '03', '王五' );-- 成绩表测试数据
INSERT INTO Score
VALUES
	( '01', '01', 80 );
INSERT INTO Score
VALUES
	( '01', '02', 90 );
INSERT INTO Score
VALUES
	( '01', '03', 99 );
INSERT INTO Score
VALUES
	( '02', '01', 70 );
INSERT INTO Score
VALUES
	( '02', '02', 60 );
INSERT INTO Score
VALUES
	( '02', '03', 80 );
INSERT INTO Score
VALUES
	( '03', '01', 80 );
INSERT INTO Score
VALUES
	( '03', '02', 80 );
INSERT INTO Score
VALUES
	( '03', '03', 80 );
INSERT INTO Score
VALUES
	( '04', '01', 50 );
INSERT INTO Score
VALUES
	( '04', '02', 30 );
INSERT INTO Score
VALUES
	( '04', '03', 20 );
INSERT INTO Score
VALUES
	( '05', '01', 76 );
INSERT INTO Score
VALUES
	( '05', '02', 87 );
INSERT INTO Score
VALUES
	( '06', '01', 31 );
INSERT INTO Score
VALUES
	( '06', '03', 34 );
INSERT INTO Score
VALUES
	( '07', '02', 89 );
INSERT INTO Score
VALUES
	( '07', '03', 98 );
	-- 1 查询" 01 "课程比" 02"课程成绩高的学生的信息及课程分数
SELECT
	* 
FROM
	Score a
	JOIN Student d ON a.s_id = d.s_id
	JOIN Score b ON a.s_id = b.s_id 
	AND a.c_id = '01' 
	AND b.c_id = '02' 
WHERE
	a.s_score > b.s_score;
-- 2 查询同时存在" 01 "课程和" 02 "课程的学生信息
SELECT
	c.* 
FROM
	Score a
	JOIN Score b ON a.s_id = b.s_id 
	AND a.c_id = '01' 
	AND b.c_id = '02'
	JOIN Student c ON a.s_id = c.s_id;
	
-- 3 查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )
SELECT
	* 
FROM
	Score a
	JOIN Score b ON a.s_id = b.s_id 
	AND b.c_id != '02' 
WHERE
	a.c_id = '01';

-- 4 查询不存在" 01 "课程但存在" 02 "课程的情况
SELECT
	* 
FROM
	Score a
	JOIN Score b ON a.s_id = b.s_id 
	AND b.c_id = '02' 
WHERE
	a.c_id != '01';
-- 5. 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩    
SELECT
	a.s_id,
	s_name,
	AVG( s_score ) c 
FROM
	Student a
	JOIN Score b ON a.s_id = b.s_id 
GROUP BY
	a.s_id 
HAVING
	c > 60;
-- 6. 查询在 Score 表存在成绩的学生信息
SELECT DISTINCT
	b.* 
FROM
	Score a
	INNER JOIN Student b ON a.s_id = b.s_id;

-- 7 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
SELECT
	a.s_id,
	s_name,
	COUNT( c_id ),
	SUM( s_score ) 
FROM
	Student a
	JOIN Score b ON a.s_id = b.s_id 
GROUP BY
	a.s_id;

-- 8 查没有成绩的学生信息
SELECT
	* 
FROM
	Student a
	LEFT JOIN Score b ON a.s_id = b.s_id 
WHERE
	s_score IS NULL;
-- 9. 查询「李」姓学生的数量
SELECT
	COUNT( s_name ) 
FROM
	Student 
WHERE
	s_name LIKE '李%';
-- 10. 查询学过「张三」老师授课的同学的信息
SELECT
	* 
FROM
	Student a
	JOIN Score b ON a.s_id = b.s_id
	JOIN Course c ON b.c_id = c.c_id
	JOIN Teacher d ON c.t_id = d.t_id 
WHERE
	t_name = '张三';
-- 11. 查询没有学全所有课程的同学的信息（没学过的也算）
SELECT
	a.* 
FROM
	Student a
	LEFT JOIN Score b ON a.s_id = b.s_id
	LEFT JOIN Course c ON b.c_id = c.c_id 
GROUP BY
	a.s_id 
HAVING
	COUNT(*)< 3;
-- 12 查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
-- 查询01同学学的课程
SELECT
	c_id 
FROM
	Score 
WHERE
	s_id = '01';
SELECT DISTINCT
	a.* 
FROM
	Student a
	JOIN Score b ON a.s_id = b.s_id 
WHERE
	c_id IN ( SELECT c_id FROM Score WHERE s_id = '01' );
-- 13 查询和" 01 "号的同学学习的课程 完全相同的其他同学的信息
-- 1.查询01同学学的课程
SELECT c_id from Score where s_id = '01';
-- 2.查询除了01同学外其他同学学的课程
SELECT s_id,c_id from Score where s_id != '01';
-- 3.查询01同学学的课程数
SELECT COUNT(c_id) from Score where s_id = '01';
-- 4.查询其他同学学的课程总数
SELECT s_id,COUNT(c_id) from Score where s_id != '01' GROUP BY s_id;
-- 5.总数进行对比，相等则为完全一样
SELECT
	b.* 
FROM
	Score a
	INNER JOIN Student b ON a.s_id = b.s_id 
WHERE
	a.s_id NOT IN (
	SELECT
		s_id 
	FROM
		Score 
	WHERE
	c_id NOT IN ( SELECT c_id FROM Score WHERE s_id = '01' )) 
GROUP BY
	a.s_id 
HAVING
	count( 1 ) = ( SELECT count(*) FROM Score WHERE s_id = '01' );
-- 14 查询没学过"张三"老师讲授的任一门课程的学生姓名
-- 1.查询哪些同学学习了张三老师的课程
-- select c_id FROM Teacher a join Course b on a.t_id = b.t_id where t_name = '张三'; -- 张三老师课程的编号
-- SELECT b.s_id  from Score a join Student b on a.s_id = b.s_id where c_id =(select c_id FROM Teacher a join Course b on a.t_id = b.t_id where t_name = '张三');-- 学习张三老师课程的学生id
-- 2.除了这些学生
SELECT
	* 
FROM
	Student 
WHERE
	s_id NOT IN (
	SELECT
		b.s_id 
	FROM
		Score a
		JOIN Student b ON a.s_id = b.s_id 
	WHERE
		c_id =(
		SELECT
			c_id 
		FROM
			Teacher a
			JOIN Course b ON a.t_id = b.t_id 
		WHERE
			t_name = '张三' 
		));

-- 15 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
-- SELECT a.s_id,s_name,AVG(s_score)
-- from Student a 
-- join Score b on a.s_id = b.s_id 
-- where s_score  < 60 -- and SELECT s_id,COUNT(c_id) from Score  GROUP BY s_id having COUNT(c_id)>=2
-- GROUP BY a.s_id;

SELECT
	a.s_id,
	b.s_name,
	avg( s_score ) AS avg_score 
FROM
	Score a
	LEFT JOIN Student b ON a.s_id = b.s_id
	INNER JOIN (
	SELECT
		s_id,
		s_name 
	FROM
		Student 
	WHERE
	s_id IN ( SELECT s_id FROM Score WHERE s_score < 60 GROUP BY s_id HAVING count( 1 )> 1 )) AS c ON a.s_id = c.s_id 
GROUP BY
	a.s_id;

-- 16 检索" 01 "课程分数小于 60，按分数降序排列的学生信息
SELECT
	b.*,
	s_score 
FROM
	Score a
	RIGHT JOIN Student b ON a.s_id = b.s_id 
WHERE
	c_id = '01' 
	AND s_score < 60 
ORDER BY
	s_score DESC;

-- 17 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
SELECT
	a.*,
	GROUP_CONCAT( s_score ),
	AVG( s_score ) 
FROM
	Student a
	JOIN Score b ON a.s_id = b.s_id 
GROUP BY
	a.s_id 
ORDER BY
	AVG( s_score ) DESC;
-- 18.查询各科成绩最高分、最低分和平均分：以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
SELECT 
		a.c_id,
		c_name,
		MAX(s_score) '最高分',
		MIN(s_score) '最低分',
		AVG(s_score) '平均分',
		sum(case when s_score >= 60 then 1 else 0 end )/COUNT(s_id) '及格率',
		sum(case when s_score >= 70 and s_score < 80 then 1 else 0 end )/COUNT(s_id) '中等率',
		sum(case when s_score >= 80 and s_score < 90 then 1 else 0 end )/COUNT(s_id) '优良率',
		sum(case when s_score >= 90 then 1 else 0 end )/COUNT(s_id) '优秀率'
FROM Score a 
join Course b on a.c_id = b.c_id
group by c_id;

-- 19 按各科成绩进行排序，并各科成绩从高到底显示排名， Score 重复时保留名次空缺 RANK()
select 
			c_id,
			s_score,
			RANK() over (PARTITION by s_id ORDER BY s_score DESC)
from Score ;
-- 20各科成绩进行排序，并显示排名， Score 重复时合并名次
select 
			c_id,
			s_score,
			rank() over (partition by s_id ORDER BY s_score)
from Score;

-- 21查询学生的总成绩，总成绩并进行排名，总分重复时保留名次空缺
select 
			s_id,
			sum_score ,
			RANK() over (order by sum_score )
from (select s_id,
			sum(s_score) as sum_score
			from Score
			group by s_id) a;

SELECT 
s_id,
sum_score,
rank() over (ORDER BY sum_score DESC) AS '排名'
FROM (
SELECT 
s_id,
SUM(s_score) AS sum_score
FROM Score
GROUP BY s_id) a;

-- 22统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比
-- 同14题
SELECT 
			a.c_id,
			c_name,
			count(case when s_score <= 100 and s_score >=85 then 1 end ) '[100-85]',
			count(case when s_score < 85 and s_score >=70 then 1 end ) '[85-70]',
			count(case when s_score < 70 and s_score >=60 then 1 end ) '[70-60]',
			count(case when s_score < 60 and s_score >=0 then 1 end ) '[60-0]',
			count(case when s_score <= 100 and s_score >=85 then 1 end )/count(s_id) '[100-85]百分比',
			count(case when s_score < 85 and s_score >=70 then 1 end ) /count(s_id)'[85-70]百分比',
			count(case when s_score < 70 and s_score >=60 then 1 end )/count(s_id) '[70-60]百分比',
			count(case when s_score < 60 and s_score >=0 then 1 end )/count(s_id) '[60-0]百分比'
from Score a 
join Course b on a.c_id = b.c_id
group by c_id;

-- 23查询各科成绩前三名的记录
select c_name,
			GROUP_CONCAT(s_score ORDER BY s_score desc ) 
from Score a
join Course b on a.c_id = b.c_id
group by c_name;

select a.* 
from Score a
where (select count(1) from Score b where b.c_id =a.c_id and b.s_score >a.s_score) < 3
order by c_id desc,s_score desc;
-- limit 
-- 24查询每门课程被选修的学生数
SELECT c_id,COUNT(s_id)
FROM Score
GROUP BY c_id;

-- 25 查询出只选修两门课程的学生学号和姓名
select b.s_id,
			s_name,
			count(c_id)
from Student a
join Score b on a.s_id = b.s_id
group by s_id
HAVING count(c_id) = 2;

-- 26 查询男生、女生人数
-- select 
-- 			COUNT(case when s_sex = '男' then 1 else 0 END) '男生人数',
-- 			count(case when s_sex = '女' then 1 else 0 END) '女生人数'
-- from Student;
select 
			s_sex,
			count(s_sex)
from Student
group by s_sex;
-- 27 查询名字中含有「风」字的学生信息
select *
from Student
where s_name like '%风%';
-- 28 查询同名同性学生名单，并统计同名人数
select a.s_name,
			a.s_sex,
			count(*)
from Student a 
join Student b on a.s_name = b.s_name and a.s_sex = b.s_sex and a.s_id != b.s_id
group by a.s_sex,a.s_name;

-- 29查询 1990 年出生的学生名单
select *
from Student
where year(s_birth) = '1990';
-- 30查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
select  c_id ,AVG(s_score)
from Score
GROUP BY c_id
ORDER BY AVG(s_score) DESC,c_id ASC;
-- 31查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
SELECT b.s_id,
			s_name,
			AVG(s_score)
from Score a 
join Student b ON a.s_id = b.s_id
GROUP BY s_id,s_name
having AVG(s_score) >=85;
-- 32查询课程名称为「数学」，且分数低于 60 的学生姓名和分数
select a.s_id,s_name ,c.c_id,c_name, s_score
from Score a 
join Student b on a.s_id = b.s_id
join Course c on a.c_id =c.c_id
where c_name = '数学' and s_score <60;

-- 33查询所有学生的课程名称及分数情况（存在学生没成绩，没选课的情况）
select s_name,c_name , s_score
from Student a 
left join Score b on a.s_id = b.s_id
left join Course c on  b.c_id = c.c_id;
-- 34查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
select s_name , c_name ,s_score
from Student a
join (SELECT * from Score where s_score > 70) b on a.s_id = b.s_id 
join Course c on b.c_id = c.c_id ;
-- join Score b on a.s_id = b.s_id
-- join Course c on b.c_id = c.c_id
-- where (select count(1) from Score where s_score >70  group by s_id ) = 3;
-- 
select sname,cname,score
from student 
inner join (select * from sc where score > 70)as b on student.sid=b.sid 
inner join course on b.cid = course.cid;

-- 35 查询不及格的课程
SELECT *
from Score
where s_score < 60;
-- 36 查询课程编号为 01 且课程成绩在 60 分以上的学生的学号和姓名
select a.s_id,s_name
from Student a 
join Score b on a.s_id = b.s_id
where c_id = '01' and s_score > 60;
-- 37 求每门课程的学生人数
select c_id ,COUNT(s_id)
from Score 
GROUP BY c_id;
-- 38成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩 ROW_NUMBER()
select * ,
			ROW_NUMBER() over (partition by b.c_id ORDER BY s_score DESC) 
from Student a
join Score b on a.s_id = b.s_id
join Course c on b.c_id = c.c_id
join Teacher d on c.t_id = d.t_id
where t_name = '张三' 
limit 1;
-- 39成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
select *
from( 
SELECT a.s_id ,s_name,s_score,
RANK() over (order by s_score desc) as ranking
FROM Student a 
join Score b on a.s_id = b.s_id
join Course c on b.c_id = c.c_id
join Teacher d on d.t_id = d.t_id
where t_name = '张三'
)as st
where ranking = 1;

-- 40查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
select 
			a.s_id,
			a.c_id,
			a.s_score
from Score a 
join Score b on a.s_score = b.s_score and a.c_id != b.c_id and a.s_id != b.s_id;
-- 41查询每门课程成绩最好的前两名同学 RANK
SELECT *	
FROM(SELECT c_id,s_name,
			RANK() over (partition by c_id ORDER BY s_score DESC) as ranking
FROM Score a 
join Student b on a.s_id = b.s_id
) as st
GROUP BY c_id,s_name
HAVING ranking in (1,2);
-- 42统计每门课程的学生选修人数（超过 5 人的课程才统计）。
SELECT
			c_id,
			COUNT(s_id)
from Score
group by c_id
having count(s_id) >5;
-- 43检索至少选修两门课程的学生学号
SELECT
			s_id,
			COUNT(1)
from Score
group by s_id
having COUNT(1) >= 2;
-- 44查询选修了全部课程的学生信息
SELECT a.*
from Student a 
where  (SELECT COUNT(1) from Score b where b.s_id = a.s_id) = (SELECT COUNT(1) from Course);
-- 45 查询各学生的年龄，只按年份来算
select *,
			year(now())-YEAR(s_birth) age		
from Student;

-- 46 按照出生日期来算，当前月日 < 出生年月的月日则年龄减一;
SELECT *,
			TIMESTAMPDIFF(year,s_birth,NOW()) as age 
from Student;
-- 47 查询本周过生日的学生
SELECT * ,
			WEEK(s_birth),
			WEEK(NOW())
from Student
where WEEK(s_birth) = WEEK(NOW());
-- 48 查询下周过生日的学生    -
SELECT * ,
			WEEK(s_birth),
			WEEK(NOW())
from Student
where WEEK(s_birth) =( WEEK(NOW()) + 1);
-- 49 查询本月过生日的学生
SELECT *,
			MONTH(s_birth),
			MONTH(NOW())
FROM Student 
where MONTH(s_birth) = MONTH(NOW());
-- 50查询下月过生日的学生
SELECT *,
			MONTH(s_birth),
			MONTH(NOW())
FROM Student
where  MONTH(s_birth) = (MONTH(NOW())+1);