mysql查询练习2

目录

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

2.查询每门课的平均成绩

3.查询分数大于70，小于90的sno列

4.查询所有学生的sname、cno和degree列

5.三表关联查询

6.查询“96031”班学生每门课的平均分

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

8.查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录

9.查询和学号为108、101的同学同年出生的所有学生的sno,sname和sbirthday列

10.多层嵌套

11.多表查询

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

13.查询存在有85分以上成绩的课程Cno

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

15.查询“计算机系”与“电子工程系”不同职称的教师的tname和prof

16.查询选修编号为“3-105”课程且成绩至少高于选修编号为“3-245”的同学的cno,sno和degree,并按degree从高到低次序排序

17.查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的cno,sno和degree

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

19.查询所有“女”教师和“女”同学的name、sex和birthday.

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

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

---

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

  -- like’ 3% ‘ 是以3开头，having是过滤的意思

   select cno from score group by cno

 having count(cno)>=2 and cno like ‘3%’;

2.查询每门课的平均成绩

select * from course;

--avg()

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

select degree from score where cno=’3-105’

--我能不能在一个sql语句中写呢?

--group by 分组

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

3.查询分数大于70，小于90的sno列

   select sno,degree from score

where degree>70 and degree<90/between 70 and 90

4.查询所有学生的sname、cno和degree列

   select sno,sname from student;

select sno,cno,degree from score;

将score中的sno替换为student中的sname

select sname,cno,degree from student,score

where student.sno=score.sno  student中的sno=sore中的sno

5.三表关联查询

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

   sname--->student

   cname--->course

   degree--->score

select sname,cname,degree from student,course,score

where student.sno=score.sno

and course.cno=score.cno

6.查询“96031”班学生每门课的平均分

   select *from student where class=’95031’;

select sno from student where class=’95031’;

--->select * from score where sno in (select sno from student where class=’95031’);

--->select cno,avg(degree)

from score

where sno in (select sno from student where class=’95031’)

 group by cno;

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

select * from score

where cno=’3-105’ and degree>(select degree from score where sno=’109’ and cno=’3-105’);

8.查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录

select * from score

where degree>(select degree from score where sno=’109’ and cno=’3-105’);

9.查询和学号为108、101的同学同年出生的所有学生的sno,sname和sbirthday列

查学号，包含两个用in:select * from student where sno in (108,101);

查生日：select year(sbirthday) from student where sno in (108,101);

---> select * from student where year(sbirthday) in (select year(sbirthday) from student where sno in (108,101));

10.多层嵌套

查询“张旭”老师任课的学生成绩

查询老师：select * from teacher where tname=”张旭”;

查询课程：select cno from course where tno=(select * from teacher where tname=”张旭”);

查询成绩：select * from score where cno=(select cno from course where tno=(select * from teacher where tname=”张旭”));

11.多表查询

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

查人数多于5人的课程：select cno from score group by cno having count(*)>5;

select * from teacher;

查课程中的老师：select tno from course where cno=(select cno from score group by cno having count(*)>5);

查老师的姓名：select tname from teacher where tno=( select tno from course where cno=(select cno from score group by cno having count(*)>5));

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

select * from student;

select * from student where class in (‘95031’,’95033’);

13.查询存在有85分以上成绩的课程Cno

select cno,degree from score where degree>85;

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

select * from teacher where depart=”计算机系”;

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

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

15.查询“计算机系”与“电子工程系”不同职称的教师的tname和prof

   关键在不同职称

union 求并集

select prof from teacher where depart=’电子工程系’;

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=’计算机系’);

16.查询选修编号为“3-105”课程且成绩至少高于选修编号为“3-245”的同学的cno,sno和degree,并按degree从高到低次序排序

select * from score where cno=’3-245’;

select * from score where cno=’3-105’;

至少？大于其中至少一个，any

select * from score

where cno=’3-105’

and degree>any(select * from score where cno=’3-245’)

order by degree desc;

17.查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的cno,sno和degree

all 表示所有的关系

select * from score

where cno=’3-105’

and degree>all(select * from score where cno=’3-245’)

order by degree desc;

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

别名 ？ as

select tname as name,tsex as sex,tbirthday as birthday from teacher

union

select sname,ssex,sbirthday from student;(第二排不用取别名，默认和第一排一样）

19.查询所有“女”教师和“女”同学的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=’女’;

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

求平均值：select cno,avg(degree) from score group by cno;

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

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

select tname,depart from teacher where tno in (select tno from course);