更多查询模式--mysql学习笔记

分组查询

分组计算平均成绩

select avg(degree) from score where cno='1001';
每门课程的平均成绩
select cno ,avg(degree) from score group by cno;

分组查询与模糊查询

查询至少有两名学生修并且1开头的课程，平均成绩，选修人数

select cno,avg(degree),count(*) from score group by cno having count(cno)>=2 and cno like '1%';
//group by 后面having表示查询条件
//like表示模糊查询

范围查询

查询分数在91和93之间的学生名单

1.select sno,degree from score where degree>91 and degree<93;
2.select sno,degree from score where degree between 91 and 93;

查询1001班和1003班的全体学生记录——in表示或者关系

select * from student where class in (1001,1003);

多表查询

1.查询所有学生的sname,cno,degree列（来自不同的表）
至少要用到student,score表，寻找两个表的共同点sno

select sname,cno,degree from student,score where student.sno=score.sno;

2.查询所有学生的cname,sno和degree
course,score
(1)select cno,cname from course;
(2)select cno,sno,degree from score;

select cname,sno,degree from course,score where course.cno=score.cno;

三表查询

1.查询所有学生的sname,cname,degree
student,course,score表

select sname,cname,degree from student,course,score where student.sno=score.sno and course.cno=score.cno;

2.查询选修学生数>2的课程的任课教师姓名

select tname from teacher where tno in (select tno from course where cno in (select cno from score group by cno having count(*)>2));

子查询

1.查询1002班学生每门课程的平均分

select cno,avg(degree) from score where sno in (select sno from student where class='1002') group by cno;

2.查询选修“1004”课程的成绩高于"1"号同学"1001"成绩的所有同学的记录
select * from score where cno=‘1004’ and degree>(select degree from score where sno=‘1’ and cno=‘1001’);

3.查询成绩高于学号为“0”，课程号为“1001”的成绩的所有记录

select * from score where degree>(select degree from score where sno='0' and cno='1001');

多层嵌套子查询——套娃

查询"li"任课的课的学生成绩

select tno from teacher where tname=‘li’;

select cno from course where tno=(select tno from teacher where
tname=‘li’);

select * from score where cno=(select cno from course where tno=(select tno from teacher where tname='li'));

2.查询计算机系教师所教课程的成绩表

select * from score where cno in (select cno from course where tno in (select tno from teacher where depart='计算机系'));

union和notin的使用

查询“计算机系”与“建筑系”不同职称教师的tname和prof

select * from teacher where depart=‘计算机系’ and prof not in (select prof
from teacher where depart=‘建筑系’) union select * from teacher where
depart=‘建筑系’ and prof not in (select prof from teacher where
depart=‘计算机系’);

any表示至少一个

查询选修编号为1003课程且成绩至少高于编号为1001同学的cno,sno和degree,并按degree从高到低次序排序

select * from score where cno=‘1003’ and degree>any(select degree from
score where cno=‘1001’) order by degree desc;

all表示所有

select * from score where cno=‘1003’ and degree>all(select degree from
score where cno=‘1001’);

as取别名union求并集

别名：tname—name tsex—sex tbirth—birthday