查询练习

新建库test-库中建学生表、教师表、课程表、成绩表

一.建数据库test

create database test；

二.学生表-student  （学号sno、姓名sname、性别ssex、出生年月sbirthday、所在班级sclass）

create table student (
     sno varchar(10) primary key,
     sname varchar(10)  not null,
     ssex varchar(1) not null,
     sbirthday datetime  not null,
     sclass int
       
)  ;  

 

 三.教师表teacher-（教师编号tno、教师性别tsex、出生日期tbrithday、职称title、所在部门department）

create table teacher (
    tno varchar(10) primary key,
    tsex varchar(1) not null,
    tbrithday datetime not null,
    title varchar(10) not null,
    department varchar(10) not null
);

 

 四、课程表Course-(课程号cno、课程名称cname、教师编号tno）

create table course (
    cno varchar(10) primary key,
    cname varchar(10) not null,
    tno varchar(10)  not null,
    foreign key (tno) references teacher (tno)
);

 五、成绩表scroe-（学号sno、课程号cno、成绩score）

create table scroe (
    sno varchar(10) not null,
    cno varchar(10) not null,
    score decimal not null,
    foreign key (sno) references student (sno),
    foreign key (cno) references course (cno)
);

 

 

 

 

 查询练习：

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

select * from student;

2.查询student表中的所有记录的sname、ssex、class列

SELECT sname,ssex,sclass FROM `student`;

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

　　注：distinct  排除重复

SELECT distinct department FROM `teacher`;

4.查询score表中成绩在60~80之间的所有记录

　　注：between ....and...区间查询

SELECT * FROM `scroe` where score between 60 and 80;

　　注：用运算符进行筛选，and表示并且关系

SELECT * FROM `scroe` where score>=60 and score<=80;

5.查询score表中成绩为78、99、90的记录

　　注：in   表示或者关系的查询

SELECT * FROM `scroe` where score in (78,90,99);

6.查询student表中2班或性别为女的同学记录 

　　注：or 表示或者关系

SELECT * FROM `student` where sclass=2 or ssex='女' ;

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

　　注：describe 进行降序查询

SELECT * FROM `student`  order by sclass desc ;

8.查询1班的学生人数

　　注：count 进行计数查询

SELECT count(*) FROM `student` ;

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

SELECT * FROM `scroe` order by cno asc,score desc  ;

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

　　注：通过max 找最大值

SELECT sno,cno FROM `scroe`  where score=(select max(score) from scroe) ;

　　注：用排序的方法进行查询，

　　　　Limit 第一个数字表示从多少开始

　　　　第二个数字表示查多少条

　　　　此方法的弊端是不知道有几个最高分，一般不使用

SELECT sno,cno FROM `scroe` order by score desc limit 0,1;

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

　　注：用avg（）计算平均值

　　对每个班级分别进行查询

select avg(score) from scroe where cno=1;

　　用group by 进行分组

　　先根据课程号进行分组，再计算每一组的平均值

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

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

SELECT cno,avg(score) FROM `scroe` group by cno having count(cno)>=2 and cno like '3%';

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

SELECT sno FROM scroe where score  between 70 and 90  ;

14.查询所有学生的sname、cno 和score列

　　多表联查：利用两个表中相同的列进行查询

SELECT sname,cno,score FROM student,scroe where student.sno=scroe.sno;

15.查询所有学生的sno、cname、和score列

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

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

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

17.查询3班学生每门课的平均成绩

select cno,avg(score) from scroe where sno in (select sclass from student where sclass=3) group by cno;

18. 查询选修“3”课程的成绩高于学号“103”同学、课程号“3”成绩的所有记录

select cno,score from scroe where cno=3 and score>(select score from scroe where sno=103 and cno=3);

19.查询成绩高于学号为“103”、课程号为3的成绩的所有记录

select * from scroe where score>(select score from scroe where sno=103 and cno=3);

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

　　注：用year（）查询年份

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

 21.查询“张亮”老师任课的学生成绩

select * from scroe where cno=(select cno from course where tno=(select t
no from teacher where tname="张亮"));

22.查询选修某课程的同学人数多余三名的教师姓名

select tname from teacher where tno=(select tno from  course where cno=(select cno  from scroe group by cno having count(*)>3));

23.查询22班和33班全体学生的记录

 select * from student where sclass in (22,33);

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

select cno from scroe where score>85;

select cno from course where cno in (select cno from scroe where score>85
);

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

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

26.查询计算机系和环化系不同职称的教师的tname和title

　　用union求并集，单独使用有去重的作用，用union all一起使用可以筛选全部并集中的数据

select tname,title from teacher where department= '计算机系'and title not in (select title from teacher where department= '环化系') 
union 
select tname,title from teacher where department= '环化系' and title not in (select title from teacher where department='计算机系');

27.查询选修编号为2的课程且成绩至少高于选修课程为1的课程的同学的cno、sno、score,并按照score从高到低的顺序排列

　　用any表示任意一个，至少一个

 select cno,sno,score from  scroe where cno=2 and score>any(select score from scroe where cno=1) order by score desc;

 28.查询选修编号为2的课程且成绩且高于选修课程为1的课程的同学的cno、sno、score,并按照score从高到低的顺序排列

 　　用all表示所有

select cno,sno,score from  scroe where cno=2 and score>all(select score from scroe where cno=1) order by score desc;

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

　　用as取别名

select tname as name,tsex as sex,tbrithday as brithday from teacher union
( select sname,ssex,sbirthday from student);

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

select tname as name,tsex as sex,tbrithday as brithday from teacher where tsex='女'  union( select sname,ssex,sbirthday from student where ssex='女');

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

　　通过复制表的方式进行比较查询

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

 32.查询所有任课教师的tname和department

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

 33.查询至少有两名男生的班号

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

34. 查询学生表中不姓王的学生信息

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

35.查询student表中每个学生的姓名和年龄

　　查询当前年份用   year （now（））

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

 select sname,year(now())-year(sbirthday) from student;

 36.查询学生表中最大和最小的日期值

 select max(sbirthday) as '最大',min(sbirthday) as'最小' from student;

37.以班号和年龄从大到小的顺序查询学生表中的全部记录

select * from student order by sclass desc,year(now())-year(sbirthday) desc;

select * from student order by sclass desc,sbirthday;

 38.查询男教师及其所上的课程

select tname,cname from teacher,course where tsex='男' and teacher.tno=course.tno ;

select * from course where tno in (select tno from teacher where tsex='男');

 39.查询最高分同学的sno、cno、score列

 select * from scroe where score=(select max(score) from scroe);

40.查询和“张三”同学同性别的所有同学的sname

select sname from student where ssex=(select ssex from student  where sname='张三');

41.查询和“张三”同性别并同班的同学的sname

select sname from student where ssex=(select ssex from student  where sname='张三') and sclass=(select sclass from student where sname="张三");

42.查询所有选修“计算机科学”课程的男同学的成绩表

select * from scroe where cno=(select cno from course where cname='计算机科学') and sno in (select sno from student where ssex='男');

43.先创建一个grade表，然后查询sno、cno、grade列

 select sno,cno,grade from scroe,grade where score between low and upp;

44.新建一张mianshibiao，将num列中nun在[20,29]之间的数值改成20，将nun在[30,39]之间的数值改成30

　　将nun看成是一个变量，对num除以10取模然后在乘以10

　　用floor进行取整数

update mianshibiao set num=floor(num/10)*10  where num>=20 and num<=39;

 45.score表中（name，subject，score），查询出2门及两门以上不及格的同学的平均成绩

select name,avg(score),sum(score<60) as guakeshu from score group by name having guakeshu>1;

select name,avg(score) from score where name in 
(select name from (select name,count(1) as guakeshu from score where score<60 having guakeshu>=2 )as tmp)
 group by name;