MYSQL学生分数表

学生表

CREATE TABLE `student` (
  `stuId`      int(11) NOT NULL AUTO_INCREMENT COMMENT '学号',
  `name` varchar(10) not NULL COMMENT '名字',
  `sex`  char(1) not NULL COMMENT '性别',
  `birthday` DATE not NULL COMMENT '出生年月日',
  PRIMARY KEY (`stuId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into student(stuId,name,birthday,sex) values('0001' , '猴子' , '1989-01-01' , '男');
insert into student(stuId,name,birthday,sex) values('0002' , '猴子' , '1990-12-21' , '女');
insert into student(stuId,name,birthday,sex) values('0003' , '马云' , '1991-12-21' , '男');
insert into student(stuId,name,birthday,sex) values('0004' , '王思聪' , '1990-05-20' , '男');

课程表

CREATE TABLE `course` (
    `courseId`     int(11) not null AUTO_INCREMENT comment '课程号',
    `cname` varchar(10) not null comment '课程名',
    `teaId`   int(11) not null comment '教师号',
    PRIMARY KEY (`courseId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into course(courseId,cname,teaId)values('0001' , '语文' , '0002');
insert into course(courseId,cname,teaId)values('0002' , '数学' , '0001');
insert into course(courseId,cname,teaId)values('0003' , '英语' , '0003');

教师表

CREATE TABLE `teacher` (
    `teaId` int(11) not null AUTO_INCREMENT comment '教师号',
    `tname` varchar(10) not null comment '教师名',
     PRIMARY KEY (`teaId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into teacher(teaId,tname) values('0001' , '孟扎扎');
insert into teacher(teaId,tname) values('0002' , '马化腾');
insert into teacher(teaId,tname) values('0003' , null);
insert into teacher(teaId,tname) values('0004' , '');

成绩表

CREATE TABLE `score` (
    scoreId int(11) not null AUTO_INCREMENT comment '课程号',
    stuId int(11) not null comment '学号',
    score float(3) not null comment '成绩',
    PRIMARY KEY (`scoreId`,`stuId`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into score(stuId,scoreId,score) values('0001' , '0001' , 80);
insert into score(stuId,scoreId,score) values('0001' , '0002' , 90);
insert into score(stuId,scoreId,score) values('0001' , '0003' , 99);
insert into score(stuId,scoreId,score) values('0002' , '0002' , 60);
insert into score(stuId,scoreId,score) values('0002' , '0003' , 80);
insert into score(stuId,scoreId,score) values('0003' , '0001' , 80);
insert into score(stuId,scoreId,score) values('0003' , '0002' , 80);
insert into score(stuId,scoreId,score) values('0003' , '0003' , 80);

sql的顺序：

select 
from 
where 
group by 
having 
order by 
limit

1、查询性孟老师的个数

select count(teaId) from teacher where tname like '孟%';

2、查询学生0001的总成绩

select sum(score) from score where stuId = '0001';

3、查询各科成绩的最高分与最低分

select scoreId,MAX(score) as 最高分,MIN(score) as 最低分 from score GROUP BY scoreId; 

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

select scoreId,count(stuId) from score group by scoreId;

5、查询男生女生人数

SELECT sex,COUNT(*) from student GROUP BY sex;

6、查询平均成绩大于60分的学生并显示平均成绩

SELECT stuId,AVG(score) from score GROUP BY stuId HAVING AVG(score)>60;

7、查询至少选修两门课的学生学号

SELECT stuId,COUNT(scoreId) as 选修的课程数目 from score GROUP BY stuId HAVING count(scoreId)>=2;

8、查询姓名一样的学生个数

select `name` ,COUNT(*) as 人数 from student GROUP BY name HAVING COUNT(name)>=2;

9、查询学生0001的成绩并排序高到低

select scoreId ,score from score where stuId='0001'  order by score desc;

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

select scoreId,avg(score) as 平均成绩 from score group by scoreId order by avg(score) asc,scoreId desc;

11、检索课程编号为“0004”且分数小于60的学生学号，结果按按分数降序排列

select stuId from score where scoreId='0002' and score>60 order by score desc;

12、统计每门课程的学生选修人数(超过2人的课程才统计),要求输出课程号和选修人数，查询结果按人数降序排序，若人数相同，按课程号升序排序

select scoreId,count(stuId) as '选修人数' from score group by scoreId having count(stuId)>2 order by count(stuId) desc,scoreId asc;

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

select stuId,avg(score) as '平均成绩' from score where score>60 group by stuId having count(scoreId)>=2;

14、查询所有课程成绩小于60分学生的学号、姓名

select stuId,name from student WHERE stuId in (select stuId from score where score>=60 group by stuId having COUNT(scoreId)=3);

15、查询没有学全所有课的学生的学号、姓名

select stuId,name from student WHERE stuId in (select stuId from score GROUP BY stuId having count(scoreId)<select count(scoreId) from course);

16、查询出只选修了两门课程的全部学生的学号和姓名

select stuId,name from student WHERE stuId in (select stuId from score group by stuId having count(scoreId)=2);

17、查找1990年出生的学生名单

select stuId,name from student where year(birthday)=1990;

18、查询所有学生的学号、姓名、选课数、总成绩

左连接：左表a的记录会全部表示出来，而右表b只会显示符合搜索条件的记录,没有记录显示为null

select a.stuId,a.name,count(b.scoreId) as 选课数,sum(b.score) as 总成绩 from student a left join score b on a.stuId=b.stuId group by a.stuId;

右连接：右表b的记录会全部显示，而左表只会显示符合条件的记录，没有的记录显示为null

select a.stuId,a.name,count(b.scoreId) as 选课数,sum(b.score) as 总成绩 from student a right join score b on a.stuId=b.stuId group by a.stuId;

19、查询平均成绩大于70的所有学生的学号、姓名和平均成绩

select a.stuId,a.name,avg(b.score) as 平均成绩 from student a left join score b on a.stuId=b.stuId GROUP BY a.stuId HAVING avg(b.score)>70;

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

select a.stuId,a.name from student a inner join score b on a.stuId=b.stuId where b.scoreId='0003' and b.score>80;

21、查询出每门课程的及格人数和不及格人数

select scoreId,
sum(case when score>=60 then 1 
     else 0 
    end) as 及格人数,
sum(case when score <  60 then 1 
     else 0 
    end) as 不及格人数
from score
group by scoreId;

22、内连接：两表的交集，共有的id的数据