学生表
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;