mysql练习题1

在罗马种地的太阳花

于 2021-12-08 14:45:50 发布

阅读量374

点赞数

文章标签： mysql 数据库 database

本文链接：https://blog.csdn.net/qq_41623932/article/details/121786822

版权

学生表 Student

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' , '女');
insert into Student values('09' , '高凡' , '1991-04-10' , 'null');

教师表 Teacher

create table Teacher(Tid varchar(10),Tname varchar(10));
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');

课程表 Course

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');
insert into Course values('04' , '政治' , '01');

成绩表 SC

create table SC(Sid varchar(10), Cid varchar(10), score decimal(18,1));
insert into SC values('01' , '01' , 40);
insert into SC values('01' , '02' , 90);
insert into SC values('01' , '03' , 99);
insert into SC values('01' , '04' , 80);
insert into SC values('02' , '01' , 70);
insert into SC values('02' , '02' , 60);
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('05' , '03' , 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);
insert into SC values('09' , '01' , 72);
insert into SC values('09' , '03' , 59);
insert into SC values('09' , '04' , 59);

分析：

四张表的关联关系

1、查询'01'课程比'02'课程分数高的学生名字

思考思路：

（1）把01课程的成绩表和02课程的成绩表分别查询出来

SELECT *from SC where Cid = '01';
SELECT *from SC where Cid = '02';

（2）把这两张表join，然后找出01>02的学生Sid

select  A.Sid  from (SELECT *from SC where Cid = '01') A 
inner join (SELECT *from SC where Cid = '02') B on A.Sid=B.Sid 
where A.score>B.score;

（3）在学生表中找到对应的学生

SELECT S.Sname from Student S where S.Sid 
in (select  A.Sid  from (SELECT *from SC where Cid = '01') A 
inner join (SELECT *from SC where Cid = '02') B on A.Sid=B.Sid 
where A.score>B.score);

优化结果：

做三表联结

Select S.Sname,A.score,B.score from Student S ,SC A ,SC B 
where A.Sid=B.Sid and A.Cid='01' and B.Cid='02' 
and A.score>B.score and S.Sid=A.Sid;

2、查询平均成绩大于等于60分的学生姓名

select S.Sid,Sname,avg(score) as avg_score from Student S,SC 
where S.Sid=SC.Sid group by S.sid having avg_score>=60;

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

select S.* from (select distinct(Sid) from SC) A 
left join Student S on A.Sid=S.Sid;

4、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null）

select S.Sid,S.Sname,A.count_C,A.sum_sc from Student S 
left join (select Sid,count(Cid) count_C,sum(score) sum_sc  
from SC group by Sid) A on S.Sid=A.Sid;

select S.Sid,S.Sname,count(Cid) count_C,sum(score) sum_sc  from Student S 
left join SC on S.Sid=SC.Sid group by S.Sid;

5、所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩，各科成绩(没成绩的显示为 null）

select
  S.Sid,
  S.Sname,
  count(Cid) count_C,
  sum(score) sum_sc,
  sum(case when Cid=1 then score else null end) as score_1,
  sum(case when Cid=2 then score else null end) as score_2,
  sum(case when Cid=3 then score else null end) as score_3
from
  Student S
  left join SC on S.Sid = SC.Sid
group by
  S.Sid;

6、查询【李】姓老师的数量

select count(1) from Teacher where Tname like '李%'

7、查询学过【张三】老师授课的同学的信息

select distinct S.* from Student S,SC where S.Sid=SC.Sid and SC.Cid in 
(select Cid from Course C,Teacher T where T.Tid=C.Tid and Tname='张三');

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

select *from Student where Sid not in 
(select Sid from SC GROUP BY Sid having count(*)=(select count(1) from Course));

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

思考思路：

（1）先查询‘03’同学学习的课程

select GROUP_CONCAT(DISTINCT Cid order by Cid) from SC where Sid='03';

（2）再查询SC表中和‘03’同学的学习课程一样的同学

select Sid from SC where Sid !='03' group by Sid 
having GROUP_CONCAT(DISTINCT Cid order by Cid) = 
(select GROUP_CONCAT(DISTINCT Cid order by Cid) from SC where Sid='03');

（3）最后查询Student表中其他学生的信息

SELECT * from Student  where Sid in (select Sid from SC where Sid !='03' 
group by Sid having GROUP_CONCAT(DISTINCT Cid order by Cid) 
= (select GROUP_CONCAT(DISTINCT Cid order by Cid) from SC where Sid='03'));

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

SELECT * from Student where Sid in (SELECT distinct Sid from SC where Cid in 
(SELECT Cid from SC where Sid = '07'));

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

SELECT Sname from Student where Sname not in 
(SELECT S.Sname from Student S,SC,Course C,Teacher T where S.Sid=SC.Sid 
and SC.Cid=C.Cid and C.Tid=T.Tid and T.Tname = '张三');

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

select S.Sid,S.Sname,avg(score) from Student S,SC WHERE S.Sid=SC.Sid and 
S.Sid in(select Sid from SC where score<60 GROUP BY Sid having count(*)>=2) 
GROUP BY S.Sid

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

SELECT S.*,score from Student S,SC where S.Sid=SC.Sid and score<60 
and Cid = '01' order by score desc;

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

SELECT Sid,avg(score) avg_score,
sum(CASE WHEN cid='01' THEN  score ELSE null END) as cid_01,
sum(CASE WHEN cid='02' THEN  score ELSE null END) as cid_02,
sum(CASE WHEN cid='03' THEN  score ELSE null END) as cid_03,
sum(CASE WHEN cid='04' THEN  score ELSE null END) as cid_04
from SC  group by Sid ORDER BY avg_score desc;

15、查询各科成绩最高分、最低分和平均分，以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率(及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90）。
要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

SELECT C.Cid,Cname,max(score),min(score),avg(score),count(Sid) count_sid, 
concat(round(sum(case when score>=60 then 1 else 0 end)/count(*)*100,2),'%') as '及格率',
concat(round(sum(case when score>=70 and score<80  then 1 else 0 end)/count(*)*100,2),'%') as '中等率',
concat(round(sum(case when score>=80 and score<90 then 1 else 0 end)/count(*)*100,2),'%') as '优良率',
concat(round(sum(case when score>=90 then 1 else 0 end)/count(*)*100,2),'%') as '优秀率'
from Course C,SC where C.Cid=SC.Cid group by SC.Cid order by count_sid desc,SC.cid asc;

16、按平均成绩进行排序，显示总排名和各科排名，Score 重复

时保留名次空缺

SELECT S.Sid,S.Sname,rank_01,rank_02,rank_03,rank_04,rank_total from Student  S 
left join(SELECT  Sid,rank() over(PARTITION by Cid ORDER BY score desc) as rank_01 FROM  SC  where Cid='01') A on S.Sid=A.Sid
left join(SELECT  Sid,rank() over(PARTITION by Cid ORDER BY score desc) as rank_02 FROM  SC  where Cid='02') B on S.Sid=B.Sid
left join(SELECT  Sid,rank() over(PARTITION by Cid ORDER BY score desc) as rank_03 FROM  SC  where Cid='03') C on S.Sid=C.Sid
left join(SELECT  Sid,rank() over(PARTITION by Cid ORDER BY score desc) as rank_04 FROM  SC  where Cid='04') D on S.Sid=D.Sid
left join(SELECT  Sid,rank() over(ORDER BY avg(score) desc) as rank_total from SC GROUP by Sid) E on S.Sid=E.Sid 
order by rank_total

16、按平均成绩进行排序，显示总排名和各科排名，Score 重复

时合并名次

SELECT S.Sid,S.Sname,rank_01,rank_02,rank_03,rank_04,rank_total from Student  S 
left join(SELECT  Sid,dense_rank() over(PARTITION by Cid ORDER BY score desc) as rank_01 FROM  SC  where Cid='01') A on S.Sid=A.Sid
left join(SELECT  Sid,dense_rank() over(PARTITION by Cid ORDER BY score desc) as rank_02 FROM  SC  where Cid='02') B on S.Sid=B.Sid
left join(SELECT  Sid,dense_rank() over(PARTITION by Cid ORDER BY score desc) as rank_03 FROM  SC  where Cid='03') C on S.Sid=C.Sid
left join(SELECT  Sid,dense_rank() over(PARTITION by Cid ORDER BY score desc) as rank_04 FROM  SC  where Cid='04') D on S.Sid=D.Sid
left join(SELECT  Sid,dense_rank() over(ORDER BY avg(score) desc) as rank_total from SC GROUP by Sid) E on S.Sid=E.Sid 
order by rank_total

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

SELECT *from 
(SELECT  Sid,Cid,score,rank() over(PARTITION by Cid ORDER BY score desc) as rank_total FROM  SC ) A 
where A.rank_total<= 3

18、查询出只选修两门课程的学生学号和姓名

select S.Sid,Sname from Student as S,SC where S.Sid=SC.Sid
group by S.Sid,Sname having count(Cid)=2;

19、查询名字中含有「风」字的学生信息

SELECT *from Student where Sname like '%风%';

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

SELECT *from Student where year(Sage) = '1990';

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

SELECT S.*,Cid,score from Student S,
( SELECT *from 
(select SC.*,dense_rank() over(partition by Cid ORDER BY score desc) as rank_total from SC) A where A.rank_total='1' ) B
where S.Sid=B.Sid and B.Cid in 
(select Cid from Course where Tid=(select Tid from Teacher where Tname='张三'));

在罗马种地的太阳花

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
mysql练习题1

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' , '孙风' ...
复制链接

扫一扫