#创建学生表-----------------------------createtable students(
sid char(4)primarykey,#学号
sname varchar(4),#姓名
ssex enum('男','女'),#性别
sage smallint,#年龄
sdept varchar(10)#学院);#创建课程表-------------------------------createtable course(
cno char(4)primarykey,#课程号
cname varchar(4),#课程名
cpno char(4)#所选课程);#创建学生所选课程成绩表-----------------------createtable students_course(
scid intprimarykey,#学生课程id
scsid char(4),#学号constraint fk_sc_scsid foreignkey(scsid)references students(sid),
sccno char(4),#课程号constraint fk_sc_sccno foreignkey(sccno)references course(cno),
scscore float(3,1)#课程成绩);#插入对应数据---------------------------------------insertinto students values('S001','李易峰','男',34,'物理系'),('S002','开司米','男',34,'物理系'),('S007','单是开','女',23,'外语系'),('S003','霍得华','女',17,'计算机系'),('S004','刘恺威','男',12,'数学系'),('S005','金科是','女',23,'计算机系'),('S006','费时经','男',16,'计算机系');insertinto course values('A001','数学','P001'),('A002','英语','P002'),('A003','物理','P003'),('A004','化学','P004'),('A005','动力学','P005'),('A006','环保学','P006');insertinto students_course values(1,'S001','A001',78.0),(2,'S002','A002',60.0),(7,'S007','A002',23.0),(3,'S003','A003',90.0),(8,'S001','A003',79.0),(4,'S004','A004',67.0),(5,'S005','A005',45.0),(6,'S006','A006',80.0)----------**多表查询练习及解析**----------------#1、 查询Student表中的所有记录的Sname、Ssex和Class列。select sname,ssex,class from student;#2、 查询教师所有的单位即不重复的Depart列。select depart from teacher GROUPBY depart
#3、 查询Student表的所有记录。select*from student;#4、 查询Score表中成绩在60到80之间的所有记录。select*from score where degree BETWEEN60and80;#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 ORDERBY class desc;#8、 以Cno升序、Degree降序查询Score表的所有记录。select*from score ORDERBY cno,degree desc;#9、 查询“95031”班的学生人数。selectcount(sno)from student where class='95031';#10、查询Score表中的最高分的学生学号和课程号。select sno,cno,MAX(degree)from score;#11、查询‘3-105’号课程的平均分。selectavg(degree)from score where cno='3-105'#12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。select cno,avg(degree) cou from score where cno like'3%'groupby#cno having count(sno)>1select cno,count(sno) cou from score where cno like'3%'GROUPBY#cno having cou > 5selectavg(degree)from score s,(select cno,count(sno) cou from score where cno like'3%'GROUPBY cno having cou >1) a where s.cno=a.cno
#13、查询最低分大于70,最高分小于90的Sno列。select*,min(degree) mi,max(degree) ma from score GROUPBY sno having mi>70and ma<90#14、查询所有学生的Sname、Cno和Degree列。select s.sname,sco.cno,sco.degree from student s LEFTJOIN score sco on s.sno=sco.sno
#15、查询所有学生的Sno、Cname和Degree列。select c.cname,s.sno,s.degree from score s LEFTJOIN course c on s.cno=c.cno
#16、查询所有学生的Sname、Cname和Degree列。select s.sname,c.cname,sco.degree from student s LEFTJOIN score sco on s.sno=sco.sno LEFTJOIN course c on sco.cno=c.cno
#17、查询“95033”班所选课程的平均分。selectavg(degree)from score s,student stu where s.sno=stu.sno and stu.class='95033'#19、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。select degree from score s where s.cno='3-105'and s.sno='109'select*from score s,(select degree from score s where s.cno='3-105'and s.sno='109') a where s.cno='3-105'and s.degree>a.degree
#20、查询score中选学一门以上课程的同学中分数为非最高分成绩的记录。select cno,max(degree) degree from score GROUPBY cno
select sno,count(cno) cou from score GROUPBY sno having cou>1select s.*from score s,(select cno,max(degree) degree from score GROUPBY cno) a,(select sno,count(cno) cou from score GROUPBY sno having cou>1) b where b.sno=s.sno and a.cno=s.cno and s.degree<a.degree
#21、查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。select degree from score where sno='109'and cno='3-105'select*from score s,(select degree from score where sno='109'and cno='3-105') a where s.cno='3-105'and s.degree>a.degree
22、查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。
select EXTRACT(yearFROM sbirthday)yearfrom student where sno=108select sno,sname,sbirthday from student s,(select EXTRACT(yearFROM sbirthday)yearfrom student where sno=108) a where EXTRACT(yearFROM sbirthday)=a.year23、查询“张旭“教师任课的学生成绩。
select s.sname,sco.degree from student s,score sco,teacher t,course c where s.sno=sco.sno and c.cno = sco.cno and c.tno=t.tno and t.tname='张旭'24、查询选修某课程的同学人数多于5人的教师姓名。
select t.tname from score s,course c,teacher t where s.cno=c.cno and c.tno=t.tno GROUPBY s.cno havingcount(s.cno)>525、查询95033班和95031班全体学生的记录。
select*from student s where s.class ='95033'or s.class='95031'26、查询存在有85分以上成绩的课程Cno.select cno,max(degree) max from score GROUPBY cno having max>8527、查询出“计算机系“教师所教课程的成绩表。
select s.*from teacher t,course c,score s where t.tno=c.tno and s.cno=c.cno and t.depart='计算机系'28、查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof。
#all some anyselect tname,prof from teacher GROUPBY prof havingcount(prof)=1select tname,prof from teacher where depart='计算机系'select tname,prof from teacher where depart='电子工程系'select tname,prof from() a,() b where a.prof = b.select tname,prof from teacher where depart="电子工程系"and prof !=all(select prof from teacher where depart='计算机系')select tname,prof from teacher t where prof notin(select prof from teacher t1 where t1.depart!=t.depart)select t.prof from teacher t,teacher t1 where t.prof=t1.prof and t.depart ="计算机系"and t1.depart ="电子工程系"groupby prof
select t2.tname,t2.prof from teacher t2 where t2.prof !=all(select t.prof from teacher t,teacher t1 where t.prof=t1.prof and t.depart ="计算机系"and t1.depart ="电子工程系"groupby prof)29、查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的Cno、Sno和Degree,并按Degree从高到低次序排序。
select sno,cno,degree from score where cno='3-245'select sno,cno,degree from score where cno='3-105'SELECT a.cno,a.sno,a.degree from(select sno,cno,degree from score where cno='3-245') a,(select sno,cno,degree from score where cno='3-105') b where a.sno=b.sno and b.degree>a.degree ORDERBY degree desc;30、查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的Cno、Sno和Degree.31、查询所有教师和同学的name、sex和birthday.select sname name,ssex sex,sbirthday birthday from student UNIONselect tname name,tsex sex,tbirthday birthday from teacher
32、查询所有“女”教师和“女”同学的name、sex和birthday.select sname name,ssex sex,sbirthday birthday from student where ssex='女'UNIONselect tname name,tsex sex,tbirthday birthday from teacher where tsex='女'33、查询成绩比该课程平均成绩低的同学的成绩表。
select cno,avg(degree) avgree from score GROUPBY cno
select s.*from score s,(select cno,avg(degree) avgree from score GROUPBY cno) a where s.cno=a.cno and s.degree<a.avgree
34、查询所有任课教师的Tname和Depart.select tname,depart from teacher t,course c where t.tno=c.tno
35、 查询所有未讲课的教师的Tname和Depart.select tname,depart,c.cname from teacher t LEFTJOIN course c on t.tno=c.tno where c.cname isnull36、查询至少有2名男生的班号。
select class from student where ssex='男'GROUPBY class havingcount(sno)>137、查询Student表中不姓“王”的同学记录。
select*from student where sname notlike'王%'38、查询Student表中每个学生的姓名和年龄。
select sname,EXTRACT(yearFROM SYSDATE())-EXTRACT(yearFROM sbirthday)from student;39、查询Student表中最大和最小的Sbirthday日期值。
selectmax(sbirthday),min(sbirthday)from student
40、以班号和年龄从大到小的顺序查询Student表中的全部记录。
select*from student ORDERBY class,sbirthday
41、查询“男”教师及其所上的课程。
select t.*,c.cname from teacher t,course c where t.tno=c.tno and t.tsex='男'42、查询最高分同学的Sno、Cno和Degree列。
select sno,cno,degree from score GROUPBY cno having degree=max(degree)43、查询和“李军”同性别的所有同学的Sname.select ssex from student where sname='李军'select s.sname from student s,(select ssex from student where sname='李军') a where s.ssex=a.ssex
44、查询和“李军”同性别并同班的同学Sname
select s.sname from student s,(select ssex,class from student where sname='李军') a where s.ssex=a.ssex and s.class=a.class
45、查询所有选修“计算机导论”课程的“男”同学的成绩表
select s.sname,so.*from student s,score so,course c where s.sno=so.sno and so.cno=c.cno and c.cname='计算机导论'and s.ssex='男'