mysql查询平均成绩_SQL经典45题 _ 基础篇 _ 用MySQL查询霍格沃茨的学生成绩单

一想到有人会关注我就忍不住紧张

SQL又来啦！！

如果想从事数据分析

SQL都不能说是优势

因为那是默认必备的

MySQL经典45题早有耳闻

据说这是基础中的基础

你得熟练到可以闭着眼睛查询它

今天我做了 一遍

这是针对表连接和子查询的练习

题目比较简单

适合初学者

刷完后保你信心满满！！

不管题目多简单，不管提问多复杂

认真

认真

认真

细节

细节

细节

如有疏漏

欢迎指正

准备工作

我使用的MySQL版本是8.0，我建立了一个刷题的数据库，以后刷题的数据可以都放这～

create database 刷题;show create database 刷题;use 刷题;

以下代码可直接复制

创建四个表格

学生表、课程表、成绩表、教师表

# 创建学生表 create table students(    sno varchar(3) not null,    sname varchar(4) not null,    ssex varchar(2) not null,    sbirthday datetime,    class varchar(5));# 创建课程表create table courses(    cno varchar(5) not null,    cname varchar(10) not null,    tno varchar(10) not null);# 创建成绩表create table scores(    sno varchar(3) not null,    cno varchar(5) not null,    degree numeric(10,1) not null);  # 创建教师表create table teachers(    tno varchar(3) not null,    tname varchar(4) not null,    tsex varchar(2) not null,    tbirthday datetime not null,    prof varchar(6),    depart varchar(10) not null);

TIPS

float类型是可以存浮点数(即小数类型),但是当你给定的数据是整数的时候，那么它就以整数给你处理。

decimal和numeric类型，会更加强硬，即使给的是整数，也会按照小数型处理，他们是定点型，用于保存对准确精度有重要要求的值。

插入数据

# 插入数据 -- 学生表insert into students(sno, sname, ssex, sbirthday, class) values    (108 ,'哈利' ,'男' ,'1980-07-31',95033),    (107 ,'赫敏' ,'女' ,'1979-09-19',95033),    (101 ,'罗恩' ,'男' ,'1980-03-01',95033),    (105 ,'马尔福' ,'男' ,'1980-10-02',95031),    (109 ,'潘西' ,'女' ,'1980-02-10',95031),    (103 ,'高尔' ,'男' ,'1980-06-03',95031);    # 插入数据 -- 课程表 insert into courses(cno, cname, tno) values    ('3-105' ,'魔药课',825),    ('3-245' ,'变形术' ,804),    ('6-166' ,'黑魔法防御术' ,856),    ('9-888' ,'保护神奇生物课' ,831);    # 插入数据 -- 成绩表insert into scores(sno, cno, degree) values    (103,'3-245',86),    (105,'3-245',75),    (109,'3-245',68),    (103,'3-105',92),    (105,'3-105',88),    (109,'3-105',76),    (101,'3-105',74),    (107,'3-105',91),    (108,'3-105',88),    (101,'6-166',85),    (107,'6-106',99),    (108,'6-166',81);  # 插入数据 -- 教师表insert into teachers(tno, tname, tsex, tbirthday, prof, depart) values    (804,'麦格','女','1948-12-02','教授','格兰芬多'),    (856,'卢平','男','1950-03-12','教授','格兰芬多'),    (825,'斯内普','男','1950-05-05','教授','斯莱特林'),    (831,'海格','男','1967-08-14','钥匙管理员','格兰芬多');

也许你已经注意到了，这些数据中的人物怎么这么眼熟呢，没错！数据中人物被我改成了《哈利波特》中的角色，这样做练习时我竟然真的会期待查询结果，他们的成绩如何呢？哈哈哈！

我们就用MySQL来查询一下霍格沃茨的魔法师学生们的成绩吧！！不知道是不是比他们的魔杖好用些~

十年前的藏图，已经无法追踪作者了/侵删

查看数据

数据准备已完毕，我们来看看数据是怎样的呢？

select * from students;desc students;# sno为主键，字段：sno, sname, ssex, sbirthday, class  select * from courses;desc courses;# cno为主键，字段：cno, cname, tnoselect * from scores;desc scores;# sno和cno 为联合主键 ，字段：sno, cno, degreeselect * from teachers;desc teachers;# tno为主键，字段：tno, tname, tsex, tbirthday, prof, depart

scores表可以和students表连接

scores表可以和courses表连接

courses可以和teachers表连接

刷刷刷刷 题

1查询students表中的sname、ssex和class列的所有记录

select sname, ssex, class from students;

2 查询teachers表中教师的单位有哪些(不重复的depart列)

select distinct depart from teachers;

3 查询students表的所有记录

select * from students;

4 查询scores表中成绩在60到80之间的所有记录

select * from scoreswhere degree between 60 and 80;

5 查询scores表中成绩为85、86、88的记录

select * from scoreswhere degree in (85, 86, 88);

6 查询students表中班级为95031或性别为女的同学记录

select * from studentswhere class='95031' or ssex='女';

7 查询students表的所有记录，以class降序显示

select * from studentsorder by class desc;

8 查询scores表的所有记录，以cno升序、degree降序显示

select * from scoresorder by cno asc, degree desc;

9 查询95031班的学生总数

select class, count(1) 学生总数 from studentswhere class='95031';

TIPS

MySQL查询中count(*)、count(1)、count(field)的区别：

count(*)对行的数目进行计算，包含NULL；

count(column)对特定的列的值具有的行数进行计算，不包含NULL值；

count()还有一种使用方式，count(1)这个用法和count(*)的结果是一样的。

任何情况下的最优选择：

select count(*) from tablename；

尽量减少这种查询：

select count(*) from tablename where COL = ‘value’；

杜绝出现：

select count(COL) from tablename where COL2 = ‘value’;

如果表没有主键，那么count(1)比count(*)快。

如果有主键，那么count(主键，联合主键)比count(*)快。

如果表只有一个字段，count(*)最快。

count(1)跟count(主键)一样，只扫描主键。

count(*)跟count(非主键)一样，扫描整个表。

10 查询scores表中分数最高的学生学号和课程号

# 方法一

select sno, cno, max(degree) 最高分 from scores;

# 方法一

select sno, cno, degree from scoresorder by degree desclimit 1;

11 查询课程号为3-105号的平均分

select cno, avg(degree) 平均分 from scoreswhere cno='3-105';

12 查询scores表中至少有5名学生选修的并以3开头的课程的平均分数

select cno, count(1) 选修人数, avg(degree) 平均分from scoreswhere cno like '3%'group by cno having 选修人数 >= 5;

13 查询scores表中最低分大于70，最高分小于90的学生(sno)

select sno, group_concat(degree)from scoresgroup by sno having min(degree)>70   and max(degree)<90;

TIPS

having 针对分组之后的进行条件筛选，对数据透视表进行筛选，是汇总值，汇总维度下的筛选

14 查询所有学生的sname、cno和degree

# sname来自students表，cno和degree来自scores表。

# 想要查询学生的信息，students为主表，通过sno链接(左链接和内连接都行)

select sname, cno, degree from students left join scores on students.sno=scores.snoorder by sname;

15 查询所有学生的sno、cname和degree

# sno和degree来自scores表，cname来自courses表。

# 想要查询学生的信息，原则上应该是students为主表，因为有的学生可能没有成绩，也要查询，但是因为这个数据构成很简单，不存在学生成绩为空的情况，所以用scores表作为主表，通过cno链接。 

select sno, cname, degreefrom scores left join courses on scores.cno=courses.cnoorder by sno;

16 查询所有学生的sname、cname和degree

# sname来自students表，cname来自courses表，degree来自scores表

# 学生各科目成绩查询，学生表为主表，链接成绩表，链接课程表

select sname, cname, degree from students left join scores on students.sno=scores.snoleft join courses on scores.cno=courses.cno;

17 查询95033班所选课程的平均分

# class来自students表，cname来自courses表、degree来自scores表

# 方法一 用表连接的方法，直接连接所有表查询，更简单

select cname, avg(degree) 平均分from students left join scores on students.sno=scores.snoleft join courses on scores.cno=courses.cnowhere class='95033'group by courses.cnoorder by cname;

# 方法二 表连接结合子查询 

# 95033班有哪些学生？select * from students where class='95033';# 这些学生都选了那些课？select * from scores where sno in (select sno from students where class='95033');# 这些课程的平均分select cno, avg(degree) 平均分from scores where sno in (select sno from students where class='95033')group by cno;# 添加cname，整合select cname, avg(degree) 平均分from scores left join courses on scores.cno=courses.cnowhere sno in (select sno from students where class='95033')group by courses.cnoorder by cname;

TIPS

条件子查询返回的是一列时用 in，是否在这列里面，条件子查询是返回的是一行是用 =，是否和这一样相同。

18 假设使用如下命令建立了一个grade表，查询所有同学的sno、cno和ranking

create table grade(    low tinyint,       upp tinyint,     ranking char(1));insert into grade values    (90,100,'A'),    (80,89,'B'),    (70,79,'C'),    (60,69,'D'),    (0,59,'E');

# sno和cno来自scores表，ranking来自grade表

select sno, cno, rankingfrom scores left join grade on scores.degree between grade.low and grade.upporder by sno;

TIPS

tinyint类型：非常小的整数，(有符号)范围为(-128,127)。

19  查询选修3-105课程的成绩高于109号同学成绩的所有同学的记录

# cno、sno、degree来自scores表，所有同学的记录来自students表，students表为主表 

# 方法一

select students.sno, degreefrom students left join scores on students.sno=scores.snowhere cno='3-105' and degree > (select degree from scores where sno='109' and cno='3-105')order by students.sno;

# 方法二 

select s1.sno, s1.degreefrom scores s1 left join scores s2 on s1.cno=s2.cno and s1.degree>s2.degreewhere s1.cno='3-105' and s2.sno='109'order by s1.sno;# 课程相同时，s1的成绩高于s2的成绩

20 查询scores表中选学一门以上课程的同学中，分数为非最高分成绩的记录

select *, count(cno) 选修课程数量 from scoresgroup by snohaving 选修课程数量>1 and degree!=max(degree);

21 查询成绩高于学号为109，课程号为3-105的成绩的所有记录

# 方法一

select * from scores where degree>(select degree from scores where sno='109' and cno='3-105')  and cno='3-105'order by sno;

# 方法二

select s1.*from scores s1 left join scores s2 on s1.cno=s2.cno and s1.degree>s2.degreewhere s1.cno='3-105' and s2.sno='109'order by s1.sno;# 方法一逻辑上更好理解，但是方法二更好用， 多对多的连接

22 查询和学号为108的同学同年出生的学生的sno、sname和sbirthday

# 方法一

select sno, sname, sbirthday from students where year(sbirthday)=(select year(sbirthday) from students where sno='108');

# 方法二

select s1.sno, s1.sname, s1.sbirthdayfrom students s1 left join students s2 on year(s1.sbirthday)=year(s2.sbirthday)where s2.sno='108';# s2就像是专门做条件筛选的一样

23 查询斯内普教师任课的学生成绩

select scores.*, tnamefrom scores left join courses on scores.cno=courses.cnoleft join teachers on courses.tno=teachers.tnowhere tname='斯内普';

24 查询选修某课程的同学人数多于5人的教师姓名

# 先查每门课有多少学生选，筛选多余5人的课程 select * from scores group by cno having count(sno)>5; select distinct tnamefrom courses left join teachers on courses.tno=teachers.tnowhere cno in (select cno from scores group by cno having count(sno)>5);

25 查询95033班和95031班全体学生的记录

select * from students where class in ('95033', '95031');

26 查询存在有85分以上成绩的课程

select distinct cno from scores where degree>85 order by degree desc;

27 查询格兰芬多学院的教师所教课程的学生成绩表

# 教师表为主表 

select tname, cname, sname, degreefrom teachers left join courses on teachers.tno=courses.tno  left join scores on courses.cno=scores.cno  left join students on scores.sno=students.snowhere depart='格兰芬多'order by tname, cname, degree desc;

28 查询格兰芬多学院与斯莱特林学院职称不同的教师的tname和prof

select tname, proffrom teacherswhere depart="格兰芬多"   and prof not in (select distinct prof from teachers where depart="斯莱特林");

29   查询选修编号为3-105课程，且成绩至少高于任意选修编号为3-245的同学的成绩的cno、sno和degree,并按Degree从高到低次序排序

select * from scoreswhere cno='3-105' and degree > any (  select degree from scores where cno='3-245')order by degree desc;

30 查询选修编号为3-105课程且成绩高于所有选修编号为3-245课程的同学的cno、sno和degree

select * from scoreswhere cno='3-105' and degree > all (  select degree from scores where cno='3-245')order by degree desc;

31 查询所有教师和同学的name、sex和birthday

select sname name, ssex sex, sbirthday birthday from studentsunionselect tname, tsex, tbirthday from teachers;

32 查询所有女性教师和女性同学的name、sex和birthday

select sname name, ssex sex, sbirthday birthday from studentswhere ssex='女'unionselect tname, tsex, tbirthday from teacherswhere tsex='女';

33 查询成绩比该课程平均成绩低的同学的成绩表

select s1.* from scores s1 left join (select cno, avg(degree) 平均成绩 from scores group by cno) s2  on s1.cno=s2.cnowhere degree

34 查询所有任课教师的tname和depart

select tname, depart from teachers where tno in (select tno from courses);

# 这里有一个隐藏的陷阱，不是查询所有老师，是有任课的老师，是可需任课要从课程表里确定。

35 查询所有未讲课的教师的tname和depart

select tname, depart from teachers where tno not in (select tno from courses);

36 查询至少有2名男生的班级号

select class,  count(1) 男生人数from studentswhere ssex='男'group by class having 男生人数>=2;

37 查询students表中不以马开头的同学记录

select * from studentswhere sname not like "马%";

38  查询students表中每个学生的姓名和年龄

select sname, year(curdate())-year(sbirthday) agefrom students;

TIPS

datediff(expr1, expr2)返回的是expr1和expr2之间的天数。

39 查询students表中年龄最大和年龄最小的sbirthday日期值

select max(sbirthday), min(sbirthday) from students;

40 以班级号和年龄从大到小的顺序查询Student表中的全部记录

select * from students order by class desc, sbirthday asc;

# 当心年龄这个坑，sbirthday越小的年龄越大

41 查询男性教师及其所上的课程

select tname, tsex, cnamefrom teachers left join courses on teachers.tno=courses.tnowhere tsex="男";

42 查询各科分数最高的同学的sno、cno和degree

select * from scores group by cno having degree=max(degree);

43 查询和哈利同性别的所有同学的姓名

# 方法一

select sname from studentswhere ssex = (select ssex from students where sname='哈利');

# 方法二

select s1.snamefrom students s1 left join students s2  on s1.ssex=s2.ssexwhere s2.sname='哈利';

44 查询和哈利同性别并同班的同学sname

# 方法一

select sname from students where (ssex, class) = (select ssex, class from students where sname='哈利');

# 方法二

select s1.snamefrom students s1 left join students s2  on s1.ssex=s2.ssex and s1.class=s2.classwhere s2.sname='哈利';

45 查询所有选修黑魔法防御术课程的男同学的成绩表

# 方法一

select sname, ssex, cname, scores.* from scores left join students on scores.sno=students.sno  left join courses on scores.cno=courses.cnowhere ssex='男' and cname='黑魔法防御术';

# 方法二

select * from scoreswhere sno in (select sno from students where ssex='男')  and cno in (select cno from courses where cname='黑魔法防御术');

-- END --

初一那年，老姐大一，暑假时老姐带回来两本书，那时的我从来不会有耐心读完一本课外书，我更愿意出去和小伙伴们跑一圈~

老姐是天蝎座，她觉得好的东西，我也得喜欢，我不看，她就读给我听，我曾多次伴着《假如给我三天光明》、《世界上最伟大的推销员》入睡~ 哈哈！

然而这次带回来的两本书改变了我对课外书的态度，我趴在床上看，坐在楼梯上看，往后的每年都期待老姐带回来最新出版的一本，没错，就是《哈利波特》系列。

 欢迎关注 

 关注的人都好看 

# 只分享干货