mysql数据库查询练习
创建四张数据表
学生表student：学号，姓名，性别，出生年月日，所在班级
教师表teacher：教师编号，教师名，教师性别，出生年月日，职称，所在部门
课程表course：课程号，课程名，教师编号
成绩表score：学号，课程号，成绩
添加信息：
学生表：
insert into student values ('1001','老大','男','1999-08-01','2000');
insert into student values ('1002','老二','女','1999-08-02','2000');
insert into student values ('1003','老三','男','1999-08-03','2001');
insert into student values ('1004','老四','女','1999-08-04','2001');
insert into student values ('1005','老五','男','1999-08-05','2002');
insert into student values ('1006','老六','男','1999-08-06','2002');
insert into student values ('1007','老七','男','1999-08-07','2003');
insert into student values ('1008','老八','女','1999-08-08','2004');
教师表：
insert into teacher values('201','王菊','女','1890-10-11','副教授','计算机系');
insert into teacher values('202','李平','男','1890-10-11','教授','电子工程系');
insert into teacher values('203','张利','男','1890-10-11','副教授','自动化系');
insert into teacher values('204','王霞','女','1890-10-11','讲师','电子工程系');
insert into teacher values('205','刘杰','男','1890-10-11','助教','计算机系');
课程表：
insert into course values ('3-101','计算机基础','201');
insert into course values ('3-102','高等数学','202');
insert into course values ('3-103','操作系统','203');
insert into course values ('3-104','概率论','204');
insert into course values ('3-105','概率论','205');
成绩表：
insert into score values ('1001','3-101','98');
insert into score values ('1001','3-103','97');
insert into score values ('1002','3-101','88');
insert into score values ('1003','3-104','78');
insert into score values ('1004','3-103','98');
insert into score values ('1006','3-101','77');
insert into score values ('1008','3-104','55');
insert into score values ('1001','3-102','98');
insert into score values ('1007','3-105','67')
查询练习：
1.查询student表的所有记录；
select * from student
2.查询student表的sname，ssex,class列；
select sname,ssex,class from student
3.查询教师所有的部门，不重复显示
select distinct depart from teacher
4.查询成绩表中成绩在77-88之间的所有记录
select * from score where degree between '77' and '88'
由查询的数据可知，between...and查询的结果包括两端的值
select * from score where degree>77 and degree<88
由查询数据可知，直接使用运算符比较不包含两端数据
5.查询成绩为98，88，或77的记录
select * from score where degree = '77' or degree = '88' or degree = '98'
select * from score where degree in (77,88,98) （in表示或者的关系）
6.查询学生表中1001班或性别为女的学生记录
select * from student where class='2000' or ssex = '女'
7.以class降序查询学生表的所有记录
select * from student order by class desc
8.以cno升序，degree降序查询成绩表的所有记录
select * from score order by cno asc , degree desc
9.查询2000班的所有学生人数
select count(*) from student where class = 2000
10.查询成绩表中的最高分的学生号和课程号（子查询）
select sno,cno from score where degree=(select max(degree) from score)
11.查询每门课的平均成绩（分组查询group by）
select cno,avg(degree)from score group by cno
12.查询score 表中至少有2名学生选修的并以3开头的课程的平均成绩（具有条件的分组查询group by....having，模糊查询）
select cno,count(cno),avg(degree) from score group by cno having count(cno)>=2 and cno like'3%'
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
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.查询“2000”班学生每门课的平均成绩
（1）2000班的学生的学号 select * from student where class='2000' select sno from student where class='2000' (2)根据（1）学生号查询2000班的学生成绩表 select * from score where sno in (select sno from student where class='2000') select cno,degree from score where sno in (select sno from student where class='2000') （3）根据（2）中的cno对学科进行分组求平均分 select cno,avg(degree) from score where sno in (select sno from student where class='2000') group by cno
18.查询选修“3-101”课程的成绩高于“1006”号同学选修“3-101”成绩的所有同学的记录
（1）查选“1006”号同学选修3-101课的成绩 select * from score where cno='3-101' and sno='1006' （2）查询选修“3-101”课程的成绩高于“1006”号同学选修“3-101”成绩的所有同学的记录 select * from score where degree>(select degree from score where cno='3-101' and sno='1006')and cno='3-101'
19.查询成绩高于学号“1006”、课程号为“3-101”的成绩的所有记录
（1）查选“1006”号同学选修3-101课的成绩 select * from score where cno='3-101' and sno='1006' （2）查询成绩高于学号“1006”、课程号为“3-101”的成绩的所有记录 select * from score where degree>(select degree from score where cno='3-101' and sno='1006')
20.查询学号为1008、1001的同学的同年出生的所有学生的sno\sname\sbrithday列
（1）查询学号为1008、1001的同学的同年出生 select year(sbrithday) from student where sno in ('1001','1008') （2）查询学号为1008、1001的同学的同年出生的所有学生的sno\sname\sbrithday列 select sno,sname,sbrithday from student where year(sbrithday) =(select year(sbrithday) from student where sno in ('1001','1008'))
21.查询王菊老师任课的学生成绩
（1）查询王菊老师的课程号 select cno from teacher as t,course as c where t.tno=c.tno （2）查询王菊老师任课的学生成绩 select sno,degree from score where cno=(select cno from teacher as t,course as c where t.tno=c.tno and tname='王菊')
22.查询选修某课程的学生人数多于2人的教师姓名
（1）根据课程号查询选修人数大于2人的课程号（group by的条件分组查询） select cno from score group by cno having count(cno)>2 （2）根据（1）查询出课程表中的教师号 select tno from course where cno =(select cno from score group by cno having count(cno)>2) （3）根据教师号查询教师名字 select tname from teacher where tno=(select tno from course where cno =(select cno from score group by cno having count(cno)>2))
23.查询2000班和2001班的学生记录（或的使用，In，or）
select * from student where class in('2000','2001')
select * from student where class ='2000' or class='2001'
24.查询计算机系老师所教课程的成绩
（1）查询计算机系老师的教师号 select tno from teacher where depart='计算机系' （2）根据教师号查询老师对应的课程号 select cno from course where tno in (select tno from teacher where depart='计算机系') （3）根据课程号查询成绩 select cno,degree from score where cno in (select cno from course where tno in (select tno from teacher where depart='计算机系'))
25.查询计算机系和电子工程系不同职称的教师姓名和职称prof（这两个系中职称相同的舍去）
（1）查询电子工程系的所有老师的职称 select prof from teacher where depart = '计算机系' 查询计算机系的所有老师的职称 select prof from teacher where depart = '电子工程系' （2）查询计算机系中与电子工程系中不同的职称 select prof from teacher where depart = '计算机系' and prof not in (select prof from teacher where depart = '电子工程系') 查询电子工程系中与计算机系中不同的职称 select prof from teacher where depart = '电子工程系' and prof not in (select prof from teacher where depart = '计算机系') （3）使用union将（2）中的语句进行联合（求并集），得出两个系中不同的职称 select prof from teacher where depart = '电子工程系' and prof not in (select prof from teacher where depart = '计算机系') union select prof from teacher where depart = '计算机系' and prof not in (select prof from teacher where depart = '电子工程系')
26.查询选修编号为“3-101”课程且成绩至少高于选修编号为“3-102”的同学的cno,sno,degree,并按成绩由高到底排列
（1）查询出选修“3-102”学生的成绩 select degree from score where cno='3-102' （2）查询出选修3-101课程的成绩至少大于3-102的学生cno,sno,degree的信息（至少any：只需要满足3-101中的成绩大于3-102中的最低成绩即可） select cno,sno,degree from score where degree>any(select degree from score where cno='3-102') and cno ='3-101' （3）根据（2）的成绩降序排列（order by ... desc） select cno,sno,degree from score where degree>any(select degree from score where cno='3-102') and cno ='3-101' order by degree desc
27.查询选修编号为“3-101”课程且成绩高于选修编号为“3-102”的同学的cno,sno,degree
（1）查询出选修“3-102”学生的成绩 select degree from score where cno='3-102' （2）查询出选修3-101课程的成绩大于3-102的学生cno,sno,degree的信息（且all:高于3-102班的最高成绩才满足） select cno,sno,degree from score where degree>all(select degree from score where cno='3-102') and cno ='3-101'
28.查询所有教师和学生的name,sex,brithday(求并集union)
select sname as name,ssex as sex,sbrithday as brithday from student
union
select tname as name,tsex as sex,tbrithday as brithday from teacher
29.查询成绩比该成绩平均分低的同学的成绩表
（1）查询每一门课的平均成绩 select avg(degree) from score b group by cno （2）通过采用复制表结构的方法做条件查询（保证使用的cno是相同的） select * from score a where degree<(select avg(degree) from score b where a.cno=b.cno)
30.查询至少有两名男生的班级
select count(ssex),class from student where ssex='男' group by class having count(ssex)>=2
31.查询student表中不含“老”字的同学记录
select * from student where sname not like'老%'
32.查询student表中每个学生的姓名和年龄
年龄=当前年份-出生年份 （1）当前年份 select year(now()) （2）学生出生年份 select year(sbrithday) from student （3）每个学生的姓名和年龄 select sname,year(now())-year(sbrithday) as age from student
33.以班号和年龄从大到小查询student表中所有同学的信息
select * from student order by class desc,sbrithday
34.按等级查询
创建一个等级表 create table grade(low int(3),upp int(3),grade char(4)) 插入数据 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 score.degree between grade.low and grade.upp
35.sql的四种连接查询
内连接：inner join 或 join
外连接：
左外连接：left join ...on或 left outer join ...on
右外连接：right join...on 或 right outer join...on
完全外连接：full join...on 或 full outer join...on
创建两张表：
person 表：id,name,cardId
card 表 ：id，name
create table person (id int (10) primary key ,name varchar(20),cardid int(10) );
create table card (id int(10) primary key ,name varchar (20) );
向两张表中分别添加数据
insert into card values(1,'饭卡');
insert into card values(2,'地铁卡');
insert into card values(3,'工商卡');
insert into card values(4,'邮政卡');
insert into card values(5,'农行卡');
insert into person values(1,'老大',1);
insert into person values(2,'老二',4);
insert into person values(3,'老三',5);
insert into person values(4,'老四',2);
insert into person values(5,'老五',2);
insert into person values(6,'老六',6);
注意到person表中并没有创建外键
（1）内连接（jion ...on）,查询出两张表有关联的信息（即两表共有的部分）
select * from person join card on person.cardId=card.id
select * from person inner join card on person.cardId=card.id
（2）左外连接：会把左边表里的所有数据取出来，而右边表中的数据，如果有相等的就显示出来，若没有就显示为空
select * from person left join card on person.cardId=card.id
（3）右外连接：会把右边表里的所有数据取出来，而左边表中的数据，如果有相等的就显示出来，若没有就显示为空
select * from person right join card on person.cardId=card.id
（4）全外连接（full join ）
,注:Mysql 默认不支持此种写法 Oracle支持(select * from person full join card on person.cardId=card.id) ,可以使用将左连接与右连接结合起来作为全连接
select * from person right join card on person.cardId=card.id
union
select * from person left join card on person.cardId=card.id
