4、mysql查询练习
1、创建四个表供查询
（1）学生表—Student
学号
姓名
性别
出生年月日
所在班级
【语句】
> create table student(
-> sno varchar(20) primary key,
-> sname varchar(20) not null,
-> ssex varchar(20) not null,
-> sbirthday datetime,
-> class varchar(20));
（2）教师表—Teacher
教师编号
教师姓名
教师性别
出生年月日
职称
所在部门
【语句】
> create table teacher(
-> tno varchar(20) primary key,
-> tname varchar(20) not null,
-> tsex varchar(10) not null,
-> tbirthday datetime,
-> prof varchar(20) not null,
-> depart varchar(20) not null);
（3）课程表—Course
课程号
课程名称
教师编号
【语句】
>create table course(
-> cno varchar(20) primary key,
-> cname varchar(20) not null,
-> tno varchar(20) not null,
-> foreign key(tno) references teacher(tno));
（4）成绩表—Score
学号
课程号
成绩
【语句】
>create table score(
-> sno varchar(20) not null,
-> cno varchar(20) not null,
-> degree decimal,
-> foreign key(sno) references student(sno),
-> foreign key(cno) references course(cno),
->primary key(sno,cno));
2、往表中添加数据
//添加学生信息
insert into student values('101','曾华','男','1977-09-01','95033');
insert into student values('102','匡明','男','1975-10-02','95031');
insert into student values('103','王丽','女','1976-01-23','95033');
insert into student values('104','李军','男','1976-02-20','95033');
insert into student values('105','王芳','女','1975-02-10','95031');
insert into student values('106','陆君','男','1974-06-03','95031');
insert into student values('107','王尼玛','男','1976-02-20','95033');
insert into student values('108','张全蛋','男','1975-02-10','95031');
insert into student values('109','赵铁柱','男','1974-06-03','95031');
//添加教师表
insert into teacher values(804,'李成','男','1958-12-02','副教授','计算机系');
insert into teacher values(856,'张旭','男','1969-03-12','讲师','电子工程系');
insert into teacher values(825,'王萍','女','1972-05-05','助教','计算机系');
insert into teacher values(831,'刘冰','女','1977-08-14',' 教','电子 工程系');
//添加课程表
insert into course values('3-105','计算机导论','825');
insert into course values('3-245','操作系统','804');
insert into course values('6-166','数字电路','856');
insert into course values('9-888','高等数学','831');
//添加成绩表
insert into score values('103','3-105','92');
insert into score values('103','3-245','86');
insert into score values('103','6-166','85');
insert into score values('105','3-105','88');
insert into score values('105','3-245','75');
insert into score values('105','6-166','79');
insert into score values('109','3-105','76');
insert into score values('109','3-245','68');
insert into score values('109','6-166','81');
3、查询练习
（1）查询student 表中所有的记录
> select * from student;
（2）查询部分记录（student表中的sno、sname、ssex）
> select sno,sname,ssex from student;
（3）查询教师depart中不重复的记录 distinct
>select distinct depart from teacher;
（4）查询区间（查询score中成绩在60到90的记录）
——用between……and……
>select * from score where degree between 60 and 90;
——直接用运算符进行比较
> select * from score where degree > 60 and degree < 90;
（5）表示或者关系（查询同一字段中指定记录：查询score表中成绩为85，86，88的记录）
>select * from score where degree in(85,86,88);
（6）表示或者关系（查询不同字段中指定记录：查询student表中“95031”班或者性别为“女” 的记录）
>select * from student where class='95031' or ssex='女';
（7）升序、降序
——以class降序查询student表中所有记录
>select * from student order by class desc;
——以class升序查询student表中所有记录
> select * from student order by class;（默认为升序）
> select * from student order by class asc;（或加asc）
（8）以cno升序、degree降序查询score表
>select * from score order by cno asc,degree desc;（先以cno升序，相同的再以degree降序）
（9）查询“95031”班的学生人数
>select count(*) from student where class='95031';
（10）查询score表中最高分的学生的学生号和课程号（子查询）
> select sno,cno from score where degree=(select max(degree) from score);
【注】1、找到最高分
>select max(degree) from score
2、找到最高分学生的学生号和课程号
>select sno,cno from score where degree=();
——通过排序取出记录
1、将成绩降序
>select sno,cno,degree from score order by degree desc;
2、取记录（用limit，这里取出了一条，当然可以改变取值范围）
> select sno,cno,degree from score order by degree desc limit 0,1;
（11）查询每门课的平均成绩
—1—分步查询
>select cno,avg(degree) from score where cno='3-105';
> select cno,avg(degree) from score where cno ='3-245';
>select cno,avg(degree) from score where cno ='6-166';
>select cno,avg(degree) from score where cno ='9-888';
—2—分组实现 group by
>select cno,avg(degree) from score group by cno;
（12）查询score表中至少有两名学生选修的并以3开头的课程的平均分数
> select cno,avg(degree),count(*) from score group by cno having count(cno)>=2 and cno like'3%';
—分析—：
> select cno from score group by cno; //分组
>having count(cno)>=2 //选修>=2
> cno like'3%' //以3开头
>avg(degree) //求平均值
>count(*) //因为现在已经进行了分组，直接记录每个字段所有的人数
（13）查询分数大于70小于90的sno列
>select sno,degree from score where degree between 70 and 90;
或者
> select sno,degree from score where degree > 70 and degree < 90;
（14）查询所有学生的sname、cno、degree列（多表查询）
>select sname,cno,degree from student,score where student.sno=score.sno;
【注】这里因为两个表共同有sno的字段，所以可以对应起来将sname替换过来
（15）查询所有学生的sno、cname、degree
> select sno,cname,degree from score,course where score.cno=course.cno;
（16）查询所有学生的sname、cname、degree（3表查询）
>select sname,cname,degree from score,course,student where student.sno=score.sno and score.cno=course.cno;
【拓展】as可以进行重命名
>select sname,cname,degree,student.sno as stu_sno,score.sno,course.cno as cou_cno,score.cno from score,course,student where student.sno=score.sno and score.cno=course.cno;
通过上述多表查询的练习我们知道，就是通过表与表之间外键联系起来，实现不同表之间的拼接展示
（17）查询“95031” 班学生每门课的平均分
—分析—
<a>查询95031班
> select * from student where class='95031';
<b>利用子查询通过sto将score中95031班的学生筛选出来
>select * from score where sno in(select sno from student where class='95031');
<c>将课程分组，求出每门课平均分，并查询
select cno,avg(degree) from score where sno in(select sno from student where class='95031') group by cno;
（18）查询选修“3-105”课程同学的成绩高于“109”号同学“3-105”成绩的其他所有同学的记录
>select * from score where cno = '3-105' and degree > (select degree from score where cno='3-105'and sno='109');
—分析—
<a> 先找到选修3-105并且学号为109的同学的成绩
>select degree from score where cno='3-105'and sno='109'
<b> 再筛选选修3-105的所有同学
>select * from score where cno = '3-105'
<c>二者结合在一起
>select * from score where cno = '3-105' and degree > (select degree from score where cno='3-105'and sno='109');
（19）查询成绩高于学号为“109”、课程号为“3-105”的同学的成绩的其他同学的记录情况
> select * from score where degree > (select degree from score where cno='3-105'and sno='109');
只需要满足（18）中的一个条件即可
（20）查询和学号为108、109的同学同年出生的所有学生的sno、sname和sbirthday字段
> select sno,sname,sbirthday from student where year(sbirthday) in(select year(sbirthday) from student where sno in(108,109));
—分析—
<a>先将学号为108、109的同学的出生年份查询出来
>select year(sbirthday) from student where sno in(108,109);
<b> 再通过子查询查到与上述两位同学同年出生的学生的记录
> select sno,sname,sbirthday from student where year(sbirthday) in(select year(sbirthday) from student where sno in(108,109));
（21）查询张旭教师任课的学生的成绩（嵌套子查询）
>>select sname,degree from score,student where score.sno = student.sno and cno = (select cno from course where tno = (select tno from teacher where tname='张旭'));
—分析—
<a>在teacher中先将张旭的tno找到
>select tno from teacher where tname='张旭'
<b>通过tno在表course中找到所对应的cno
> select cno from course where tno = (select tno from teacher where tname='张旭');
<c>通过表sname与表student中的sno，将成绩与学生姓名对应起来在满足cno等于上述取出的cno情况下查询出来
>select sname,degree from score,student where score.sno = student.sno and cno = (select cno from course where tno = (select tno from teacher where tname='张旭'));
（22）查询选修某课程的同学人数多于5人的教师的姓名
【注】从score表中我们已知满足上述问题的没有，现在对score添加几条数据（注意前面的操作用的都是之前的score表）
——新添加的数据——
insert into score values('101','3-105','90');
insert into score values('102','3-105','91');
insert into score values('104','3-105','89');
——之前的数据如下——
insert into score values('103','3-105','92');
insert into score values('103','3-245','86');
insert into score values('103','6-166','85');
insert into score values('105','3-105','88');
insert into score values('105','3-245','75');
insert into score values('105','6-166','79');
insert into score values('109','3-105','76');
insert into score values('109','3-245','68');
insert into score values('109','6-166','81');
继续……
语句： select tname from teacher where tno =( select tno from course where cno =(select cno from score group by cno having count(cno) >5))
—分析—
<a>在表score中查询选修某课程的同学人数多于5人的课程号cno
>select cno from score group by cno having count(cno) >5;
<b>在表course中查询满足上述cno的教师的tno
> select tno from course where cno =(select cno from score group by cno having count(cno) >5);
<c>最后在teacher表中查询满足上述tno的老师的tname
> select tname from teacher where tno =( select tno from course where cno =(select cno from score group by cno having count(cno) >5))
（23）查询95033班和95031班全体学生的记录
【注】我们在这里又新加了一条数据
> insert into student values('110','张飞','男','1974-06-03','95038');
语句： select * from student where class in('95033','95031');
（24）查询存在有85分以上成绩的课程cno
>select cno,degree from score where degree >85;
（25）查出“计算机系”教师所教课程的成绩表
> select * from score where cno in (select cno from course where tno in(select tno from teacher where depart ="计算机系"));
—分析—
<a>先从teacher表中查询计算机系老师的tno
>select tno from teacher where depart ="计算机系";
<b>从表course中查询到tno满足上述要求的con
>select cno from course where tno in(select tno from teacher where depart ="计算机系")
<c>从score表中查询满徐上述con的记录
> select * from score where cno in (select cno from course where tno in(select tno from teacher where depart ="计算机系"));
（26）查询“计算机系”与“电子工程系”不同职称的教师的tname和prof
<a>理解它的意思是，查询计算机系中电子工程系没有的职称+查询电子工程系中计算机系没有的职称
<b>查询计算机系中的职称
>select prof from teacher where depart ="计算机系"
<c>查询电子工程系中的职称在计算机系没有的记录
>select tname,prof from teacher where depart ="电子工程系" and prof not in(select prof from teacher where depart ="计算机系");
<d>同理查询计算机系中的职称在电子工程系中没有的记录
> select tname,prof from teacher where depart ="计算机系" and prof not in(select prof from teacher where depart ="电子工程系");
<e>两者相加 union
>select tname,prof from teacher where depart ="电子工程系" and prof not in(select prof from teacher where depart ="计算机系") union select tname,prof from teacher where depart ="计算机系" and prof not in(select prof from teacher where depart ="电子工程系");
（27）查询（选修编号为“3-105”且成绩至少高于选修编号为“3-245”的同学任意一位同学的成绩）的cno、sno、degree、将degree按从高到低的次序排列
>select degree from score where cno ='3-105'and degree > any( select degree from score where cno ='3-245') order by degree desc;
—分析—
<a>选修3-105同学的成绩
>select degree from score where cno ='3-105'
<b>选修3-245同学的成绩
>select degree from score where cno ='3-245'
<c>至少高与（高于选修3-245任何一位同学的成绩 >any ）
>select degree from score where cno ='3-105'and degree > any( select degree from score where cno ='3-245')
<d>从高到底的次序排列 order by
>select degree from score where cno ='3-105'and degree > any( select degree from score where cno ='3-245') order by degree desc;
（28）查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的cno、sno、degree
> select degree from score where cno ='3-105'and degree > all( select degree from score where cno ='3-245') order by degree desc;
—分析—
<a>选修3-105同学的成绩
>select degree from score where cno ='3-105'
<b>选修3-245同学的成绩
>select degree from score where cno ='3-245'
<c>高于（是高于选修3-245的所有同学的成绩）>all
> select degree from score where cno ='3-105'and degree > all( select degree from score where cno ='3-245') order by degree desc;
（29）查询所有教师和同学的name、sex、和birthday
>select tname as name,tsex as sex,tbirthday as birthday from teacher union select sname,ssex,sbirthday from student;
—分析—
<a>求教师
>select tname,tsex,tbirthday from teacher
<b>求学生
>select sname,ssex,sbirthday from student;
<c>加一块 union
> select tname,tsex,tbirthday from teacher union select sname,ssex,sbirthday from student;
<d>取别名（因加两部分加一起字段还是只是一方的，所以整体给字段起个别名）
>select tname as name,tsex as sex,tbirthday as birthday from teacher union select sname,ssex,sbirthday from student;
（30）查询所有“女”教师和‘女"同学的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 ='女';
—分析—
<a>查询所有“女”教师的name、sex、和birthday
>select tname,tsex,tbirthday from teacher where tsex ='女';
<b>查询所有“女”同学的name、sex、和birthday
>select sname,ssex,sbirthday from student where ssex ='女';
<c>加在一起 union
>select tname,tsex,tbirthday from teacher where tsex ='女' union select sname,ssex,sbirthday from student where ssex ='女';
<d>取别名
> select tname as name,tsex as sex,tbirthday as birthday from teacher where tsex ='女' union select sname,ssex,sbirthday from student where ssex ='女';
（31）查询成绩比该课程平均成绩低的同学的成绩表
>select * from score a where degree < (select avg(degree) from score b where a.cno = b.cno );
—分析—
<a>查看score表
<b>将表分为a、b两个表，如下：（脑子里琢磨）
<c>b表用于计算平均成绩再与a表进行比较查询满足条件的
> select * from score a where degree < (select avg(degree) from score b where a.cno = b.cno );
（32）查询所有任课教师的tname、depart
>select tname,depart from teacher where tno in (select tno from course);
—分析—
任课 （查询教师的tno必须在course中有才满足）
>select tname,depart from teacher where tno in (select tno from course);
（33）查询至少有两名男生的班号
>select class,count(ssex) from student where ssex = '男' group by class having count(ssex) >=2;
<a>查询班号必须要分组（group by）
<b>男生 where……
<c>至少有两名 （后接条件：having ）数量的话需要用到count
（34）查询student表中，不姓王“王”的同学记录
> select * from student where sname not like'王%';
（35）查询student表中每个同学的姓名和年龄
>select sname,year(now()) - year(sbirthday) as age from student;
—分析—
<a>当前年份
>select year(now());
<b>查询每个同学的出生年份
>select year(sbirthday) from student;
<c>用当前年份 - 出生年份=年龄（as取别名）
>select sname,year(now()) - year(sbirthday) as age from student;
（36）查询student表中最大和最小的sbirthday值
max、min
>select max(sbirthday) as '最大生日',min(sbirthday) as '最小生日' from student;
（37）以班号和年龄从大到小的顺序查询student表中的全部记录
> select * from student order by class desc,sbirthday asc;
（38）查询“男”教师及其所上的课程
> select * from course where tno in(select tno from teacher where tsex = '男');
—分析—
<a>先取出男教师的tno
>select tno from teacher where tsex = '男';
<b>再从表course中取出满足上述tno的记录
> select * from course where tno in(select tno from teacher where tsex = '男');
（39）查询最高分同学的sno、cno和degree
> select * from score where degree=( select max(degree) from score);
—分析—
<a>先求出最高分
> select max(degree) from score;
<b>求等于最高分的记录
> select * from score where degree=( select max(degree) from score);
（40）查询和“李军”同性别的所有同学的sname
>select sname from student where ssex =( select ssex from student where sname = '李军');
—分析—
<a>先查出李军的性别
> select ssex from student where sname = '李军';
<b>查询与满足上述性别的同学的名字
>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 class=(select class from student where sname = '李军');
—分析—
<a>先查出李军的性别
> select ssex from student where sname = '李军';
<b>查出李军的班级
>select class from student where sname = '李军';
<c>将上述两个条件连起来
>select sname from student where ssex =( select ssex from student where sname = '李军') and class=(select class from student where sname = '李军');
（42）查询所有选修“计算机导论”课程的“男”同学的成绩表
>select * from score where cno in(select cno from course where cname ="计算机导论") and sno in(select sno from student where ssex ='男');
—分析—
<a>从course中查询计算机导论的cno
>select cno from course where cname ="计算机导论";
<b>从student表中选出男同学的sno
>select sno from student where ssex ='男';
<c>从score中查询满足上述两个条件的同学的记录
>select * from score where cno in(select cno from course where cname ="计算机导论") and sno in(select sno from student where ssex ='男');
（43）、假设创建一个grade表，如下：
>create table grade(
-> low int(3),
-> high int(3),
-> grade char(1));
——添加数据
>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,grade from score,grade where degree between low and high;
—分析—
用between…and…将score表与grade表连接起来
>select sno,cno,grade from score,grade where degree between low and high;
【总结，比较生疏的】
> select cno,avg(degree),count(*) from score group by cno having count(cno)>=2 and cno like'3%';
having count()>/</= 与group by连用，满足数字条件的
con like 以……开头的
关注个人公众号，有福利哦……
