MySQL 练习题

数据库
字数统计: 2.2k   |   阅读时长≈ 10 分钟

创建表结构

  • 学生表 Student
1
2
3
4
5
6
7
8
9
create table Student(Sid varchar(6), Sname varchar(10), Sage datetime, Ssex varchar(10));
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' , '女')
  • 成绩表 SC
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
create table SC(Sid varchar(10), Cid varchar(10), score decimal(18,1));
insert into SC values('01' , '01' , 80);
insert into SC values('01' , '02' , 90);
insert into SC values('01' , '03' , 99);
insert into SC values('02' , '01' , 70);
insert into SC values('02' , '02' , 60);
insert into SC values('02' , '03' , 80);
insert into SC values('03' , '01' , 80);
insert into SC values('03' , '02' , 80);
insert into SC values('03' , '03' , 80);
insert into SC values('04' , '01' , 50);
insert into SC values('04' , '02' , 30);
insert into SC values('04' , '03' , 20);
insert into SC values('05' , '01' , 76);
insert into SC values('05' , '02' , 87);
insert into SC values('06' , '01' , 31);
insert into SC values('06' , '03' , 34);
insert into SC values('07' , '02' , 89);
insert into SC values('07' , '03' , 98)
  • 课程表 Course
1
2
3
4
create table Course(Cid varchar(10),Cname varchar(10),Tid varchar(10));
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03')
  • 教师表 Teacher
1
2
3
4
create table Teacher(Tid varchar(10),Tname varchar(10));
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五')

练习题

① 查询” 01 “课程比” 02 “课程成绩高的学生的信息及课程分数
② 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
③ 查询在 SC 表存在成绩的学生信息
④ 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
⑤ 查询姓李的老师数量
⑥ 学过”张三”老师授课的同学的信息
⑦ 没有学过”张三”老师授课的同学的信息
⑧ 查询学过编号为”01”并且也学过编号为”02”的课程的同学的信息
⑨ 查询学过编号为”01”但是没有学过编号为”02”的课程的同学的信息
⑩ 查询没有学全所有课程的同学的信息
⑪ 查询至少有一门课与学号为 01 同学所学相同的同学的信息
⑫ 查询和 01 号的同学学习的课程,完全相同的其他同学的信息
⑬ 查询所有同学最高分对应的学科名称

查询”01“课程比”02”课程成绩高的学生的信息及课程分数
1
2
3
4
5
6
7
8
9
10
11
12
13
select Sname as 姓名,t1.score as "语文" , t2.score as '数学'
from
  (select SId ,score from SC as sc1 where sc1.CId='01') as t1,
  (select SId ,score from SC as sc2 where sc2.CId='02') as t2,
  (Select * from Student) as t3
where t1.SId=t2.SId and  t2.SId =t3.SId and t1.score>t2.score;


select st.*, sc1.score as '语文',sc2.score as '数学'
from Student as st
left join SC as sc1 on st.SId  = sc1.SId and sc1.CId ='01'
left join SC as sc2 on st.SId  = sc2.SId and sc2.CId ='02'
where sc1.score > sc2.score;
查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
1
2
3
4
5
select s.SId ,s.Sname,AVG(s2.score) avg_score
from Student s
left join SC s2 on s2.SId  = s.SId
group by s.SId
having avg_score >= 60;

需要注意 where 和 having 的区别, where 是分组前筛选,所以一定写在 group by 的前面, having 是分组后筛选,这道题是想查找求完平均分数之后的结果

查询在 SC 表存在成绩的学生信息

第一种方法:将学生表和成绩表关联,过滤出没有成绩的条目,再用学生 id 分组

1
2
3
4
5
select s.*
from Student s
left join SC s2 on s2.SId = s.SId
where s2.score is not NULL
group by s.SId ;

第二种方法: 先将成绩表按学生 id 分组,再查询学生 id 在分组后的临时表中的学生信息

1
2
3
4
5
6
7
select s.*
from Student s
where s.SId in (
	SELECT s.SId
	from SC s
	group by s.SId
);
查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
1
2
3
4
select s.SId ,s.Sname, COUNT(s2.CId),sum(s2.score)
from Student s
left join SC s2 on s2.SId  = s.SId
group by s.SId ;
查询姓李的老师数量
1
2
3
select COUNT(*) as 数量
from Teacher t
where t.Tname LIKE "李%";
学过”张三”老师授课的同学的信息
1
2
3
4
5
select s.*,t.Tname  from Student s
inner join SC s2 on s.SId = s2.SId
inner join Course c on s2.CId  = c.CId
inner join Teacher t on t.TId  = c.TId
where t.Tname = "张三";
没有学过”张三”老师授课的同学的信息
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

-- 排除学过张三课的同学,剩下的就是没学过张三课的同学
select s.* from Student s
WHERE  s.SId not in (
	select s.SId  from Student s
	inner join SC s2 on s.SId = s2.SId
	inner join Course c on s2.CId  = c.CId
	inner join Teacher t on t.TId  = c.TId
	where t.Tname = "张三"
);

-- 查找每条成绩对应的课程信息,查找这些信息中是张三老师课的信息
-- 在查找这些信息对应的学生信息,并排除

SELECT s.* FROM Student s
WHERE s.SId NOT IN (
	SELECT s2.SId  from SC s2
	inner JOIN Course c2 on c2.CId = s2.CId
	WHERE c2.TId  = (
		SELECT t.TId  from  Teacher t
		WHERE t.Tname = "张三"
	)
);

查询学过编号为”01”并且也学过编号为”02”的课程的同学的信息
1
2
3
4
5
6
7
8
9
10
-- 利用inner join 过滤没有匹配结果的条目
SELECT  * from Student s
inner join  SC s2 on s2.SId =s.SId and s2.CId = "01"
inner join  SC s3 on s3.SId =s.SId and s3.CId = "02";

-- 先分组在查询个数
select s.* from Student s
inner join SC s2 on s2.SId = s.SId
GROUP BY s.SId
HAVING sum(IF(s2.CId="01" or s2.CId="02",1,0)) >1;
查询学过编号为”01”但是没有学过编号为”02”的课程的同学的信息
1
2
3
4
5
6
SELECT  * from Student s
inner join  SC s2 on s2.SId =s.SId and s2.CId = "01"
WHERE s.SId not in (
	SELECT  s.SId  from Student s
	inner join  SC s2 on s2.SId =s.SId and s2.CId = "02"
);
查询没有学全所有课程的同学的信息
1
2
3
4
5
6
select s.* from Student s
left join SC s2 on s2.SId  = s.SId
GROUP by s.SId
HAVING COUNT(*) < (
	SELECT  COUNT(*) from Course c
);
查询至少有一门课与学号为 01 同学所学相同的同学的信息
  1. 查找学号 01 同学学过的科目
  2. 查找每个同学学过的科目,排除 01 同学自己
  3. 查找每个同学学过的科目在 01 同学学过的科目中的同学
  4. 去除重复的同学信息
1
2
3
4
5
6
7
8
9
10
11
12
13
14
select s.* from Student s
left join SC s2 on s2.SId  = s.SId
where s2.CId in (
	select s2.CId  from Student s
	inner  join SC s2 on s2.SId  = s.SId and s2.SId ='01'
)  and s.SId <> '01'
group by s.SId;

-- 优化查询01同学学过的科目,直接从成绩表中查
-- 使用distinct去重
Select distinct sc.SId,	 st.* from SC as sc
Join Student   st
On sc.SId  = st.SId and st.SId <> '01'
Where sc.CId  in (Select CId  from SC where SId  = '01')
查询和 01 号的同学学习的课程,完全相同的其他同学的信息
  1. 查找所有同学学过的科目是在 01 同学学过的科目中的同学
  2. 学过和 01 同学相同课程的科目数时候和 01 同学的科目数相同
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
select s.* from Student s
join SC s2 on s2.SId  = s.SId and s.SId <> '01' and s2.CId in (select s3.CId  from SC s3 where s3.SId = '01')
group by s.SId
HAVING count(s.SId) = (
	select count(*) from SC s3 where s3.SId = '01'
)


-- 也可以使用group concat 对比课程id
-- join的时候无需确认是否课程是01学过的课程


select s.* from Student s
join SC s2 on s2.SId  = s.SId and s.SId <> '01'
group by s.SId
HAVING GROUP_CONCAT(s2.CId order by s2.CId desc) = (
	select GROUP_CONCAT(s3.CId order by s3.CId desc) from SC s3 where s3.SId ='01'
)
查询所有同学最高分对应的学科名称
1
2
3
4
5
6
7
8
9
select * from (
	select s.SId ,s.Sname ,t.CId,t.score,t.row from Student s
	left join (
	   select s2.*,
		row_number () over (partition by s2.SId order by s2.score desc) as row
		from SC s2
	) as t  on s.SId = t.SId
	left join Course c on c.CId  = t.CId
) as t where t.row = '1'
打赏
  • 版权声明： 本博客所有文章除特别声明外，著作权归作者所有。转载请注明出处！
  • Copyrights © 2015-2025 SunZhiqi

此时无声胜有声！

支付宝
微信