MySQL经典50题

/*
创建表
--1.学生表
Student(SID,Sname,Sage,Ssex) --SID 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别
--2.课程表
Course(CID,Cname,TID) --CID --课程编号,Cname 课程名称,TID 教师编号
--3.教师表
Teacher(TID,Tname) --TID 教师编号,Tname 教师姓名
--4.成绩表
SC(SID,CID,score) --SID 学生编号,CID 课程编号,score 分数
*/


CREATE TABLE student 
(SID VARCHAR(10),Sname VARCHAR(20),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' , '女');

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

CREATE TABLE Teacher(TID VARCHAR(10),Tname VARCHAR(10));
INSERT INTO Teacher VALUES('01' , '张三');
INSERT INTO Teacher VALUES('02' , '李四');
INSERT INTO Teacher VALUES('03' , '王五');

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

表结构分析图

题目

1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数

#方法一
select student.*, s1.score, s2.score
from student
         join sc s1 on s1.sid = student.sid and s1.cid = '01'
         join sc s2 on s2.sid = student.sid and s2.cid = '02'
where s1.score > s2.score;

#方法二
select s.*, sc1.score, sc2.score
from sc sc1
         join sc sc2 on sc1.SID = sc2.SID and sc1.CID = '01' and sc2.CID = '02'
         left join student s on sc1.SID = s.SID
having sc1.score > sc2.score;

输出结果：

2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数

# 方法一
select student.*, s1.score, s2.score
from student
         join sc s1 on s1.sid = student.sid and s1.cid = '01'
         join sc s2 on s2.sid = student.sid and s2.cid = '02'
where s1.score < s2.score;
# 方法二
select s.*, sc1.score, sc2.score
from sc sc1
         join sc sc2 on sc1.SID = sc2.SID and sc1.CID = '01' and sc2.CID = '02'
         left join student s on sc1.SID = s.SID
having sc1.score < sc2.score;

输出结果：

3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩

select a.SID, a.Sname, avg(b.score) 平均成绩
from Student a,
     sc b
where a.SID = b.SID
group by a.SID, a.Sname
having 平均成绩 >= 60;

输出结果：

4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩

select a.SID, a.Sname, avg(b.score) 平均成绩
from Student a
         left join sc b on a.SID = b.SID
group by a.SID, a.Sname
having 平均成绩 < 60;

输出结果：

5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

select a.SID 学生编号, a.Sname 学生姓名, count(b.CID) 选课总数, sum(score) 所有课程的总成绩
from Student a
         left join SC b
                   on a.SID = b.SID
group by a.SID, a.Sname;

输出结果：

6、查询"李"姓老师的数量

select count(Tname) 李姓老师数量
from teacher
where Tname like '李%';

输出结果：

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

# 方法一
select a.*
from student a
         left join SC S on a.SID = S.SID
         left join Course C on S.CID = C.CID
         left join teacher t on C.TID = t.TID
where t.Tname = '张三';

# 方法二
select *
from student
where SID in (select sid
              from sc
              where cid in (select distinct CID from course where TID = (select tid from teacher where Tname = '张三')));

输出结果：

8、查询没学过"张三"老师授课的同学的信息

select s.*
from student s
where s.SID not in (select s.SID
                    from student
                             left join SC S on s.SID = S.SID
                             left join Course C on S.CID = C.CID
                             left join teacher t on C.TID = t.TID
                    where t.Tname = '张三');


#方法二
select s.*
from student s
where s.SID not in (select sid
                    from sc
                    where cid in
                          (select distinct CID from course where TID = (select tid from teacher where Tname = '张三')));

输出结果：

9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息

# 方法一
select s.*
from sc sc1
         join sc sc2 on sc1.sid = sc2.sid and sc1.CID = 01 and sc2.CID = 02
         left join student s on sc1.SID = s.SID;
# 方法二
select a.*
from student a
where a.SID in (select SID from SC where CID = '01')
  and a.SID in (select SID from SC where CID = '02');

输出结果：

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

# 方法一
select a.*
from student a
where a.SID in (select SID from SC where CID = '01')
  and a.SID not in (select SID from SC where CID = '02');

# 方法二
select sc2.SID,sc1.CID,sc2.CID,s.*
from sc sc1
          left join sc sc2 on sc1.sid = sc2.sid and sc1.CID = 01 and sc2.CID = 02
         left join student s on s.SID = sc1.SID
having sc1.CID = 01 and sc2.CID is null;

输出结果：

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

#方法一
select *
from student
where SID not in (
    select SID
    from SC
    group by SID
    having count(*) = (select count(*) from course)
);

#方法二
select s.*, count(sc.CID) snum
from sc
         right join student s on SC.SID = s.SID
group by s.Sname,s.SID,s.Sage,s.Ssex
having snum < (select COUNT(CID) num from course);

输出结果：

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

select distinct s.*
from student s
         join SC a on s.SID = a.SID
where s.SID != 01
  and a.CID in (
    select CID
    from student s
             join SC a on s.SID = a.SID
    where s.SID = '01'
);

输出结果：

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

#方法一
select sc1.SID, s.*, count(sc1.CID) snum
from sc sc1
         left join sc sc2 on sc1.CID = sc2.CID and sc1.SID != 01 and sc2.SID = 01
         left join student s on sc1.SID = s.SID
where sc1.SID != 01
  and sc2.SID is not null
group by sc1.SID, s.SID, s.Sname, s.Sage, s.Ssex
having snum != (select count(CID) from sc where sid = '01');

#方法二
select s2.sid, student.*
from sc s1
         join sc s2
              on s1.cid = s2.cid and s1.sid = '01' and s2.sid != '01'
         join student on student.sid = s2.sid
group by s2.sid, student.sname, student.Sage, student.Ssex
having count(s2.sid) = (select count(*) from sc where sid = '01');

输出结果：

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

select s.*
from student s
where s.SID not in (select s.SID
                    from student
                             left join SC S on s.SID = S.SID
                             left join Course C on S.CID = C.CID
                             left join teacher t on C.TID = t.TID
                    where t.Tname = '张三');

输出结果：

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

select stu.sid, stu.sname, round(avg(sc.score), 2) as 平均分
from student as stu
         join sc on stu.sid = sc.sid
where sc.score < 60
group by stu.sid, stu.sname
having count(sc.cid) >= 2;

输出结果：

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

select distinct student.*, sc.score
from student
         left join
     sc on student.SID = sc.SID
where score < 60
  and sc.CID = '01'
order by score desc;

输出结果：

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

select s.Sname,s.SID,
       ifnull(t.avgscpre,0) 平均分,
       sum(case when sc.CID = '01' then sc.score else 0 end) as 语文,
       sum(case when sc.CID = '02' then sc.score else 0 end) as 数学,
       sum(case when sc.CID = '03' then sc.score else 0 end) as 英语
from (select sc.sid, avg(score) avgscpre from sc group by sc.sid order by avgscpre desc) t
         left join sc on t.SID = sc.SID
         right join student s on SC.SID = s.SID
group by s.SID, t.avgscpre, s.Sname;

输出结果：

18、查询各科成绩最高分、最低分和平均分，及格率，中等率，优良率，优秀率

以如下形式显示：

课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率

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

select a.Cname,
       a.CID,
       max(b.score)                                                                   最高分,
       min(b.score)                                                                   最低分,
       avg(b.score)                                                                   平均分,
       sum(case when b.score >= 60 then 1 else 0 end) / count(b.cid)                  及格率,
       sum(case when b.score >= 70 and b.score < 80 then 1 else 0 end) / count(b.cid) 中等率,
       sum(case when b.score >= 80 and b.score < 90 then 1 else 0 end) / count(b.cid) 优良率,
       sum(case when b.score >= 90 then 1 else 0 end) / count(b.cid)                  优秀率
from course a
   , sc b
where a.CID = b.CID
group by a.Cname, b.CID;

输出结果：

19、按各科成绩进行排序，并显示排名

select s.Sname, c.Cname, sc.score, row_number() over (partition by sc.cid order by score desc,sc.SID) as '排名'
from sc
         left join student s on SC.SID = s.SID
         left join course c on SC.CID = c.CID;

输出结果：

20、查询学生的总成绩并进行排名

select a.SID, a.Sname, sum(b.score) as zf
from student a
         left join sc b on a.SID = b.SID
group by a.SID, a.Sname
order by zf;

输出结果：

21、查询不同老师所教不同课程平均分从高到低显示

select t.Tname, c.Cname, avg(score) 平均分
from sc
         left join course c on SC.CID = c.CID
         left join teacher t on c.TID = t.TID
group by sc.CID, c.Cname, t.Tname
order by 平均分 desc;

输出结果：

22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩

select stu.*, cou.cname, score
from student stu
         inner join
     ((select sid, cid, score from sc where cid = 01 order by score desc limit 1,2)
      UNION
      (select sid, cid, score from sc where cid = 02 order by score desc limit 1,2)
      UNION
      (select sid, cid, score from sc where cid = 03 order by score desc limit 1,2)) p on stu.sid = p.sid
         inner join course cou on p.cid = cou.cid
order by cname, score desc;

输出结果：

23、统计各科成绩各分数段人数：课程编号,课程名称, [100-85] , [85-70] , [70-60] , [0-60] 及所占百分比

select a.CID,
       a.Cname,
       sum(case when b.score <= 60 then 1 else 0 end)                                       '60↓人数',
       sum(case when b.score >= 60 and b.score < 70 then 1 else 0 end)                      '70-60人数',
       sum(case when b.score >= 70 and b.score < 85 then 1 else 0 end)                      '85-70人数',
       sum(case when b.score >= 85 then 1 else 0 end)                                       '85↑人数',
       sum(case when b.score <= 60 then 1 else 0 end) / count(b.cid) * 100                  '60↓占比',
       sum(case when b.score >= 60 and b.score < 70 then 1 else 0 end) / count(b.cid) * 100 '70-60占比',
       sum(case when b.score >= 70 and b.score < 85 then 1 else 0 end) / count(b.cid) * 100 '85-70占比',
       sum(case when b.score >= 85 then 1 else 0 end) / count(b.cid) * 100                  '85↑占比'
from course a
   , sc b
where a.CID = b.CID
group by a.Cname, b.CID;

输出结果：

24、查询学生平均成绩及其名次

select a.SID,
       a.Sname,
       round(ifnull(avg(b.score), 0), 2)               平均分,
       ROW_NUMBER() over (order by avg(b.score) desc ) 排名
from student a
         left join sc b on a.SID = b.SID
group by a.SID, a.Sname
order by 平均分 desc;

输出结果：

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

select stu.*, cou.cname, score
from student stu
         inner join
     ((select sid, cid, score from sc where cid = 01 order by score desc limit 3)
      UNION
      (select sid, cid, score from sc where cid = 02 order by score desc limit 3)
      UNION
      (select sid, cid, score from sc where cid = 03 order by score desc limit 3)) p on stu.sid = p.sid
         inner join course cou on p.cid = cou.cid
order by cname, score desc;

输出结果：

26、查询每门课程被选修的学生数

select sc.cid, c.Cname, count(sc.cid) '选修人数'
from sc
         left join course c on SC.CID = c.CID
group by sc.cid, c.Cname;

输出结果：

27、查询出只有两门课程的全部学生的学号和姓名

select student.SID, student.Sname, count(s.SID) count
from student
         left join SC S on student.SID = S.SID
group by student.SID, student.Sname
having count = 2;

输出结果：

28、查询男生、女生人数

select Ssex, count(*) 数量
from student
group by Ssex;

输出结果：

29、查询名字中含有"风"字的学生信息

select  student.*
from student 
where Sname like '%风%';

输出结果：

30、查询同名同性学生名单，并统计同名人数

select count(*) 同名同性人数
from student s1
         join student s2
where s1.Sname = s2.Sname
  and s1.Ssex = s2.Ssex
  and s1.SID != s2.SID;

输出结果：

没有同名同性学生

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

(注：Student表中Sage列的类型是datetime)

select *
from student
where Sage like '1990%';

输出结果：

32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列

select c.CID, c.Cname, avg(score) 平均分
from sc
         left join course c on SC.CID = c.CID
group by sc.CID, c.Cname
order by 平均分 desc ,sc.CID asc ;

输出结果：

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

select a.SID, a.Sname, avg(b.score) as 平均分
from student a
         left join sc b on a.SID = b.SID
group by a.SID, a.Sname
having 平均分>=85
order by 平均分;

输出结果：

34、查询课程名称为"数学"，且分数低于60的学生姓名和分数

select c.Sname,b.score
from course a
         left join sc b on a.CID = b.CID
         left join student c on b.SID = c.SID
where a.Cname = '数学' and b.score<60;

输出结果：

35、查询所有学生的课程及分数情况

select a.Sname,c.Cname,b.score
from student a
         left join SC b on a.SID = b.SID
         left join Course c on b.CID = c.CID;

输出结果：

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

select s.Sname,
       s.SID,
       sum(case when sc.CID = '01' then sc.score else 0 end) as 语文,
       sum(case when sc.CID = '02' then sc.score else 0 end) as 数学,
       sum(case when sc.CID = '03' then sc.score else 0 end) as 英语
from (select sc.sid from sc group by sc.sid) t
         left join sc on t.SID = sc.SID
         right join student s on SC.SID = s.SID
group by s.SID, s.Sname
having 语文 >= 70
   and 数学 >= 70
   and 英语 >= 70;

输出结果：

37、查询不及格的课程

select a.*,c.Cname,b.score
from student a
         left join SC b on a.SID = b.SID
         left join Course c on b.CID = c.CID
where b.score<60;

输出结果：

38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名

select a.SID, a.Sname, s.score
from student a
         join SC S on a.SID = S.SID
    where s.CID = '01'
    and s.score >= 80;

输出结果：

39、求每门课程的学生人数

select c.Cname, count(sc.CID) 学生人数
from sc
         left join course c on SC.CID = c.CID
group by sc.CID, c.Cname;

输出结果：

40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩

select student.*, s.score
from student
         inner join sc s on student.SID = s.SID and s.score = (
    select max(score)
    from teacher
             left join Course C on Teacher.TID = C.TID
             left join sc s on C.CID = s.CID
             left join student s2 on s.SID = s2.SID
    where teacher.Tname = '张三');

输出结果：

41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

select sc1.*, sc2.*
from sc sc1
         join sc sc2

where sc1.CID != sc2.CID
  and sc1.score = sc2.score;

输出结果：

42、查询每门功课成绩最好的前两名

select stu.*, cou.cname, score
from student stu
         inner join
     ((select sid, cid, score from sc where cid = 01 order by score desc limit 2)
      UNION
      (select sid, cid, score from sc where cid = 02 order by score desc limit 2)
      UNION
      (select sid, cid, score from sc where cid = 03 order by score desc limit 2)) p on stu.sid = p.sid
         inner join course cou on p.cid = cou.cid
order by cname, score desc;

输出结果：

43、统计每门课程的学生选修人数（超过5人的课程才统计）。

要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

select c.CID, count(sc.CID) 选修人数
from sc
         left join course c on SC.CID = c.CID
group by c.CID
having 选修人数>5
order by 选修人数 desc, c.CID asc;

输出结果：

44、检索至少选修两门课程的学生学号

select SID
from student
where SID in (
    select SID
    from SC
    group by SID
    having count(*) >= 2
);

输出结果：

45、查询选修了全部课程的学生信息

select *
from student
where SID in (
    select SID
    from SC
    group by SID
    having count(*) = (select count(*) from course)
);

输出结果：

46、查询各学生的年龄

#1.只按年份来算

select student.*, year(now()) - year(Sage) '年龄'
from student;

#2.按出生日期来算，过了生日那一天肯定就是大一岁了

select student.*, round(datediff(curdate(), Sage) / 365.2422) as '年龄'
FROM student;

输出结果：

47、查询本周过生日的学生

select *
from student
where week(Sage) = week(now());

输出结果：

数据中没有本周（12.5---12.11）过生日的学生

48、查询下周过生日的学生

select *
from student
where week(Sage) = week(date_add(now(),interval 1 week ));

输出结果：

49、查询本月过生日的学生

select *
from student
where month(Sage) = month(now());

输出结果：

50、查询下月过生日的学生

select *
from student
where month(Sage) = month(date_add(now(),interval 1 month ));

输出结果：

快乐画饼

关注

16
点赞
踩
42

收藏

觉得还不错? 一键收藏
0
评论
MySQL经典50题

mysql经典50题
复制链接

扫一扫

专栏目录