③MySQL刷题马拉松：坚持刷题，MySQL技能大提升！

最新推荐文章于 2024-01-03 09:15:08 发布

以山河作礼。

最新推荐文章于 2024-01-03 09:15:08 发布

阅读量7.8k

点赞数 17

分类专栏： Mysql基础入门文章标签： mysql 数据库

本文链接：https://blog.csdn.net/weixin_50804299/article/details/131718943

版权

Mysql基础入门专栏收录该内容

4 篇文章 1 订阅

订阅专栏

③MySQL刷题马拉松：坚持刷题，MySQL技能大提升！

环境
mysql经典50习题

🏘️🏘️个人简介：以山河作礼。
🎖️🎖️:Python领域新星创作者，CSDN实力新星认证，阿里云社区专家博主,CSDN内容合伙人
🎁🎁:文章末尾扫描二维码可以加入粉丝交流群，不定期免费送书。

环境

MySQL版本： 8.0.28
数据库管理工具：MySQL Workbench 8.0 CE

MySQL Workbench 8.0 CE是一款开源的、跨平台的、图形化的MySQL数据库管理工具，由MySQL AB公司开发。它提供了一个集成的开发环境，包括数据库设计、SQL开发、数据库管理和数据库维护等功能

mysql经典50习题

数据库数据：

1.学生表

-- Student(SID,Sname,Sage,Ssex)
-- --SID 学生编号,Sname 学生姓名,Sage 年龄,Ssex 学生性别
-- CREATE TABLE Student(
-- Sid VARCHAR(20),
-- Sname VARCHAR(20) NOT NULL DEFAULT '',
-- Sage VARCHAR(20) NOT NULL DEFAULT '',
-- Ssex VARCHAR(10) NOT NULL DEFAULT '',
--  PRIMARY KEY(Sid)
--  );

-- 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' , ' 女 ');

2.课程表

-- create table Course(Cid int primary key auto_increment,Cname char(20),Tid int);
-- insert into Course(Cid,Cname,Tid) values(1,'语文',2),(2,'数学',1),(3,'英语',3);

3.教师表

-- Teacher(Tid,Tname)
-- Tid 教师编号,Tname 教师姓名
-- create table Teacher(Tid int primary key auto_increment,Tname char(20));
-- insert into Teacher(Tid,Tname) values(1,'张三'),(2,'李四'),(3,'王五');

4.成绩表

-- Score(Sid,Cid,score)
-- --Sid 学生编号,Cid 课程编号,score 分数
-- CREATE TABLE Score (
--   Sid INT NOT NULL,
--   Cid INT NOT NULL,
--   score INT,
--   PRIMARY KEY (Sid, Cid)
-- );

-- 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);

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

select s.* 
from student s 
join (select s_id from score where c_id=1) sc1 on s.s_id=sc1.s_id
left join (select s_id from score where c_id=2) sc2 on s.s_id=sc2.s_id
where sc2.s_id is null;

select s.*：表示查询学生表（student）中所有列的数据。
2. join (select s_id from score where c_id=1) sc1 on s.s_id=sc1.s_id：表示将学生成绩表（score）中选了课程1的学生ID（s_id）与学生表中的ID（s_id）进行连接，生成一个名为sc1的虚拟表，包含选了课程1的学生ID。
left join (select s_id from score where c_id=2) sc2 on s.s_id=sc2.s_id：表示将学生成绩表中选了课程2的学生ID与学生表中的ID进行连接，生成一个名为sc2的虚拟表，包含选了课程2的学生ID。这里使用左连接（left join）是因为有些学生可能只选了课程1，没有选课程2，这样可以保留这些学生的信息。
where sc2.s_id is null：表示筛选出sc2表中学生ID为空的记录，即只选了课程1而没有选课程2的学生信息。

在这里插入图片描述

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

select s.* 
from student s
left join (
    select s_id
    from score
    group by s_id
    having count(c_id)=5
    ) tmp on s.s_id=tmp.s_id
where tmp.s_id is null;

select * from Student：表示查询学生表（Student）中所有列的数据。
where Sid in (select distinct(Sid) from (select Sid from Score where Sid not in (select Sid from Score where Cid = 1 ) or Sid not in (select Sid from Score where Cid = 2 ) or Sid not in (select Sid from Score where Cid = 3)) as a)：表示筛选出选修了除指定课程（Cid=1、2、3）之外的所有课程的学生信息。
select Sid from Score where Sid not in (select Sid from Score where Cid = 1 ) or Sid not in (select Sid from Score where Cid = 2 ) or Sid not in (select Sid from Score where Cid = 3)：表示查询选修了除指定课程之外的所有课程的学生ID。
- distinct(Sid)：表示去重，只保留不同的学生ID。
- as a：表示将上述查询结果命名为“a”。
- where Sid in (...)：表示筛选出学生表中学生ID在上述查询结果中的学生信息。

在这里插入图片描述

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

select s.s_id, s.s_name, s.s_birth, s.s_sex
from score sc1
join score sc2 on sc2.s_id=1 and sc2.c_id=sc1.c_id
left join student s on s.s_id=sc1.s_id
where sc1.s_id not in (1)
group by s.s_id, s.s_name, s.s_birth, s.s_sex;

select s.s_id, s.s_name, s.s_birth, s.s_sex：表示查询学生表（student）中学生ID、姓名、出生日期和性别这四列数据。
from score sc1：表示从成绩表（score）中选取数据，将其命名为sc1。
join score sc2 on sc2.s_id=1 and sc2.c_id=sc1.c_id：表示将成绩表（score）中和学生1选了相同课程的成绩记录连接起来，将其命名为sc2。
left join student s on s.s_id=sc1.s_id：表示将学生表（student）中和sc1中学生ID相同的学生信息连接起来，生成一个左连接的结果。
where sc1.s_id not in (1)：表示筛选出学生ID不为1的成绩记录，即排除掉学生1自己的成绩记录。
group by s.s_id, s.s_name, s.s_birth, s.s_sex：表示按照学生ID、姓名、出生日期和性别进行分组，确保查询结果中每个学生只出现一次。

在这里插入图片描述

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

select s.s_id, s.s_name, s.s_birth, s.s_sex, count(sc1.c_id) as cnt
from score sc1
join score sc2 on sc2.s_id=1 and sc2.c_id=sc1.c_id
left join student s on s.s_id=sc1.s_id
where sc1.s_id not in (1)
group by s.s_id, s.s_name, s.s_birth, s.s_sex
having count(sc1.c_id) in (select count(c_id) from score where s_id=1);

select s.s_id, s.s_name, s.s_birth, s.s_sex, count(sc1.c_id) as cnt：表示查询学生表（student）中学生ID、姓名、出生日期、性别和选修课程数量这五列数据。
from score sc1：表示从成绩表（score）中选取数据，将其命名为sc1。
join score sc2 on sc2.s_id=1 and sc2.c_id=sc1.c_id：表示将成绩表（score）中和学生1选了相同课程的成绩记录连接起来，将其命名为sc2。
left join student s on s.s_id=sc1.s_id：表示将学生表（student）中和sc1中学生ID相同的学生信息连接起来，生成一个左连接的结果。
where sc1.s_id not in (1)：表示筛选出学生ID不为1的成绩记录，即排除掉学生1自己的成绩记录。
-group by s.s_id, s.s_name, s.s_birth,s.s_sex：表示按照学生ID、姓名、出生日期和性别进行分组，确保查询结果中每个学生只出现一次。
having count(sc1.c_id) in (select count(c_id) from score where s_id=1)：表示筛选出选修课程数量和学生1相同的学生信息，即只保留选修课程数量和学生1相同的学生信息。这里使用having子句进行筛选，因为需要对分组后的结果进行筛选，而where子句只能对原始数据进行筛选。

在这里插入图片描述

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

select s.s_id, s.s_name 
from student s
left join score sc on s.s_id=sc.s_id
left join course c on sc.c_id=c.c_id
left join teacher t on t.t_id=c.t_id and t.t_name='张三'
group by s.s_id, s.s_name
having count(t.t_id)=0;

select s.s_id, s.s_name：表示查询学生表（student）中学生ID和姓名这两列数据。
from student s：表示从学生表（student）中选取数据，将其命名为s。
left join score sc on s.s_id=sc.s_id：表示将成绩表（score）中和学生表（student）中学生ID相同的成绩记录连接起来，生成一个左连接的结果，将其命名为sc。
left join course c on sc.c_id=c.c_id：表示将课程表（course）中和sc中课程ID相同的课程信息连接起来，生成一个左连接的结果，将其命名为c。
left join teacher t on t.t_id=c.t_id and t.t_name='张三'：表示将教师表（teacher）中和c中教师ID相同且教师姓名为“张三”的教师信息连接起来，生成一个左连接的结果，将其命名为t。
group by s.s_id, s.s_name：表示按照学生ID和姓名进行分组，确保查询结果中每个学生只出现一次。
having count(t.t_id)=0：表示筛选出没有选修过张三老师所教授的课程的学生信息，即排除掉选修过张三老师所教授的课程的学生信息。这里使用having子句进行筛选，因为需要对分组后的结果进行筛选，而where子句只能对原始数据进行筛选。

在这里插入图片描述

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

select s.*, tmp.avg_score 
from student s 
join (
    select s_id, count(c_id) as c_cnt, avg(s_score) as avg_score 
    from score 
    where s_score < 60 
    group by s_id 
    having c_cnt >= 2
) tmp on tmp.s_id=s.s_id;

select s.*, tmp.avg_score：表示查询学生表（student）中所有列数据以及平均成绩这一列数据。
from student s：表示从学生表（student）中选取数据，将其命名为s。
join (select s_id, count(c_id) as c_cnt, avg(s_score) as avg_score from score where s_score < 60 group by s_id having c_cnt >= 2) tmp on tmp.s_id=s.s_id：表示将成绩表（score）中选修了至少两门不及格课程的学生信息连接起来，生成一个内连接的结果，将其命名为tmp。这里使用了一个子查询，首先筛选出不及格课程数量大于等于2门的学生，并计算他们的平均成绩，然后将结果和学生表（student）连接起来，得到最终的查询结果。

在这里插入图片描述

以山河作礼。

关注

17
点赞
踩
19

收藏

觉得还不错? 一键收藏
打赏
13
评论
③MySQL刷题马拉松：坚持刷题，MySQL技能大提升！

🎖️🎖️:Python领域新星创作者，CSDN实力新星认证，阿里云社区专家博主,CSDN内容合伙人🎁🎁:文章末尾扫描二维码可以加入粉丝交流群，不定期免费送书。
复制链接

扫一扫