新建库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;