MySQL查询练习（对上一条博客建的表）

查询练习

1.查询student表的所有记录。

select  * from student;

2.查询student表中的所有sname、ssex和class列。

select sname,ssex,class from student;

3.查询教师所有的单位即不重复的depart列。

select distinct depart from teacher;

distinct排重

4.查询score表中成绩在60~80的所有记录(区间查询)。

select * from score where degree between 60 and 80;

between…and…区间

或者

select * from score where degree > 60 and degree < 80;

5.查询score表中成绩为78、88、89的记录（或关系查询）。

select * from score where degree in (78,88,89); 

in在同一字段中的或查询

6.查询student表中“95001”班或女生的记录。

select * from student where class = '95001' or ssex = '女';

or不同字段的或查询

7.以class降序查询student表的所有记录。

​ 升序(默认)：asc;

​ 降序：desc;

select * from student order by class desc;

8.以cno升序、degree降序查询score表的所有记录。

select * from score order by cno asc,degree desc;

先将cno升序排列，cno相同的以degree降序排列

9.查询“95002”班的学生人数。

select count(*) from student where class = "95002";

10.查询score表中的最高分的学生学号和课程号。（子查询或排序）

select sno,cno from score where degree = (select max(degree)from score); 

​ 实际步骤：

​ a.找到最高分select max(degree)from score；

​ b.找最高分的sno和cno。

11.查询每门课的平均成绩。

​ 计算一门课的平均成绩（avg()计算平均值）：

 select avg(degree) from score where cno = '3-105'; 

​ 计算每门课的平均成绩（group by 分组）：

select cno,avg(degree) from score group by cno;

12.查询score表中至少有2名学生选修的，并以3开头的课程的平均分数。

​ like模糊查询

select cno,avg(degree),count(*) from score group by cno having count(cno)>=2 and cno like '3%';

having和where的区别：

​ having是在分组后对数据进行过滤，where是在分组前；

​ having后面可以使用聚合函数，where后面不可以。

13.查询分数大于75，小于90的sno列。

select sno,degree from score where degree>75 and degree<90;

14.查询所有学生的sname、cno、degree列。(不同表的字段，多表查询)

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

15.查询所有学生的sno、cname、degree列。

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

16.查询所有学生的sname、cname、degree列。

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

17.查询“95001”班学生每门课的平均分

为了好理解，按步骤写一遍：

a.先查出95001班学生

select sno from student where class = '95001';

b.再查出95001班学生所选的课程及分数

select * from score where sno in (select sno from student where class = '95001');

c.查出每门课的平均分

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

18.查询选修3-105课程的成绩高于204号同学3-105成绩的所有同学记录。

a.先找出学号为204，选修3-105课程的学生

select degree from score where sno = '204' and cno = '3-105';

b.查出分数高于他的人

select * from score where degree >(select degree from score where sno = '204' and cno = '3-105') and cno = '3-105';

19.查询成绩高于学号204选课为3-105的同学，成绩的所有记录。（不分课程）

select * from score where degree >(select degree from score where sno = '204' and cno = '3-105');

20.查询和学号为101、102的同学同年出生的所有学生的sno、sname和sbirthday列。

a.找出两位同学的出生年份

select year(sbirthday) from student where sno in (101,102);

b.找出同年的同学（因为是多个值，不能用=，用in）

select * from student where year(sbirthday) in (select year(sbirthday) from student where sno in (101,102));

21.查询“张旭”教师任课的学生成绩。

a.找出老师的编号

select tno from teacher where tname = '张旭'; 

b.找出老师的课程编号

select cno from course where tno = (select tno from teacher where tname = '张旭'); 

c.查询出老师所带学生的成绩

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

22.查询选修某课程的同学人数多于2门的教师姓名。

a.查询人数大于2的课程编号

select cno from score group by cno having count(*)>2;

b.查询课程任课老师的编号

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

c.查询课程任课老师的姓名

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

23.查询95001班和95003班的全体学生记录。

select * from student where class in('95001','95003');

24.查询存在有85分以上成绩的课程cno。

select cno,degree from score where degree>85;

25.查询“计算机系”和“电子工程系”不同职称的教师的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 = '计算机系');

union将两条记录连接（合并操作）

26.查询选修编号为3-105课程且成绩至少高于选修编号为4-256的同学的cno、sno、degree，并按degree从高到低的次序排序。

select * from score where cno = '3-105' and degree > any(select degree from score where cno = '4-256') order by degree desc;

any表示至少一个

27.查询选修编号为3-105课程且成绩高于选修编号为4-256的同学的cno、sno、degree。

select * from score where cno = '3-105' and degree > all(select degree from score where cno = '4-256');

–all表示全部

28.查询所有教师和学生的name、sex、birthday。

a.查询教师

select tname,tsex,tbirthday from teacher;

a.查询学生

select sname,ssex,sbirthday from student;

c.合并

select tname as name,tsex as sex,tbirthday as birthday from teacher
union
select sname,ssex,sbirthday from student;

as xxx表示别名

29.查询所有女教师和女学生的name、sex、birthday。

select tname as name,tsex as sex,tbirthday as birthday from teacher where tsex = '女'
union
select sname,ssex,sbirthday from student where ssex = '女';

30.查询成绩比该课程平均成绩低的同学的成绩表。

select * from score a where degree < (select avg(degree) from score b where a.cno = b.cno);

相当于把score表复制了一份，此时分为a，b两份。

在b表中算出平均成绩，与a表中的成绩比较。

31.查询至少有2名男生的班级号。

select class from student where ssex = '男' group by class having count(*)>1;

32.查询student中不姓王的同学记录。

select * from student where sname not like '王%';

33.查询student中每个学生的姓名和年龄。

年龄 = 当前年份-出生年份

select sname,year(now()) - year(sbirthday) as '年龄' from student; 

34.以班号和年龄从大到小的顺序查询student表中的全部记录。

select * from student order by class desc,sbirthday;

35.按等级查询

使用如下命令建立一张grade表：

create table grade(
	low int(3),
    upp int(3),
    grade char(2)
);

insert into grade values(90,100,'A');
insert into grade values(80,89,'B');
insert into grade values(70,79,'C');
insert into grade values(60,69,'D');
insert into grade values(0,59,'E');

查询所有同学的sno、cno、grade列

select sno,cno,degree,grade from score,grade where degree between low and upp order by grade asc;