#建学生信息表studentcreate table student
(
sno varchar(20) not null primary key,
sname varchar(20) not null,
ssex varchar(20) not null,
sbirthday datetime,
class varchar(20)
);
#建立教师表create table teacher
(
tno varchar(20) not null primary key,
tname varchar(20) not null,
tsex varchar(20) not null,
tbirthday datetime,
prof varchar(20),
depart varchar(20) not null
);
#建立课程表coursecreate table course
(
cno varchar(20) not null primary key,
cname varchar(20) not null,
tno varchar(20) not null,foreign key(tno) references teacher(tno)
);
#建立成绩表create table score
(
sno varchar(20) not null primary key,foreign key(sno) references student(sno),
cno varchar(20) not null,foreign key(cno) references course(cno),
degree decimal
);
#添加学生信息insert into student values('108','曾华','男','1977-09-01','95033');insert into student values('105','匡明','男','1975-10-02','95031');insert into student values('107','王丽','女','1976-01-23','95033');insert into student values('101','李军','男','1976-02-20','95033');insert into student values('109','王芳','女','1975-02-10','95031');insert into student values('103','陆君','男','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-245','86');insert into score values('105','3-245','75');insert into score values('109','3-245','68');insert into score values('103','3-105','92');insert into score values('105','3-105','88');insert into score values('109','3-105','76');insert into score values('103','3-105','64');insert into score values('105','3-105','91');insert into score values('109','3-105','78');insert into score values('103','6-166','85');insert into score values('105','6-166','79');insert into score values('109','6-166','81');
题目:
1、 查询Student表中的所有记录的Sname、Ssex和Class列。
select sname,ssex,class from student;
2、 查询教师所有的单位即不重复的Depart列。
select distinct depart from teacher;
3、 查询Student表的所有记录。
select * from student;
4、 查询Score表中成绩在60到80之间的所有记录。
select * from score where degree between 60 and 80;
5、查询Score表中成绩为85,86或88的记录
select * from score where degree=85 or degree=86 or degree=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;
8、 以Cno升序、Degree降序查询Score表的所有记录。
select * from score order by cno asc,degree desc;
9、 查询“95031”班的学生人数。
select count(*) from student where class='95031';
10、 查询Score表中的最高分的学生学号和课程号。(子查询或者排序)
select sno,cno from score where degree=(select max(degree) from score);
11、 查询每门课的平均成绩。
select cno,avg(degree) from score group by cno;
详解--查询每门课的平均成绩--分组和聚合结合的时候,先分组,然后对每一组分别进行聚合Select AVG(Degree) from Score group by Cno;--先按Cno分组,在对Cno聚合
12、 查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。
解法1:select * from score where cno in(select cno from score group by cno having count(*)>=5) and cno like '3%';
解法2:select cno,avg(degree) from score where cno like'3%'group by cno having count(*) >=5;
详解--select Cno,AVG(Degree) from Score where Cno like '3%' group by Cno having COUNT(*)>=5--筛选分组完了再对每一组进行AVG聚合,分完组之后相当于只剩下分完组的列
13、 查询分数大于70,小于90的Sno列。
select sno from score group by sno having min(degree)>70 and max(degree)<90
14、 查询所有学生的Sname、Cno和Degree列。
select student.sname,score.cno,score.degree from student,score where student.sno=score.sno;
详解--表连接,一定有主外键关系:都好隔开两个表形成笛卡尔积,再进行where筛选,通过表主外键关系筛选,where只是个筛选条件
15、查询所有学生的Sno、Cname和Degree列。
select score.sno,score.degree,course.cname from score,course where score.cno=course.cno;
16、 查询所有学生的Sname、Cname和Degree列。
解法1:select student.sname,score.degree,course.cname from student,score,course where score.cno=course.cno and score.sno=student.sno;
解法2:select Sname,Cname,Degree from student join Score on Student.Sno=Score.Sno join Course on Score.Cno=Course.Cno
17、 查询“95033”班学生的平均分。
解法1:select avg(degree) from student,score where student.sno=score.sno and class='95033';
解法2:select avg(degree) from score where sno in(select sno from student where class='95033');
18、假设使用如下命令建立了一个grade表:
create table grade(low int(3),upp int(3),rank 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和rank列。
解法1:select sno,cno,rank from score,grade where degree between low and upp order by rank;
解法2:select sno,cno,degree,[rank] from Score join grade on degree between low and upp order by [rank]--on后面加筛选条件解法3:select Sno,Cno,(select [rank] from grade where Score.Degree between low and upp) as LV from Score order by Degree desc--子查询得到的结果必须唯一
19查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。
select * from score where cno='3-105' and degree>(select degree from score where sno=109 and cno='3-105');
20、查询score中选学多门课程的同学中分数为非最高分成绩的记录。
理解1:选学多门课程的同学中,分数小于所有学生中最高分:
1.select * from score where sno in(2)and degree<(3)
2.select sno from score group by sno having count(*)>1
3.select max(degree) from score where sno in(4)
4.select sno from score group by sno having count(*)>1
结合起来:select * from score where sno in(select sno from score group by sno having count(*)>1) and degree<(select max(degree) from score where sno in(select sno from score group by sno having count(*)>1));
理解2:选多门课程中,分数小于每门课程最高分的学生信息
select * from score a where sno in(select sno from score group by sno having count(*)>1) and degree<(select max(degree) from score b where b.cno = a.cno )
21、 查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。
select *from score where degree>(select degree from score where sno='109'and Cno='3-105')
22、查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列
select sno,sname,sbirthday from student where year(sbirthday)=(select year(sbirthday) from student where sno='108');
23、查询“张旭“教师任课的学生成绩。
1.select * from score where cno in ()2.select cno from course where tno=()3.select tno from teacher where tname='张旭'
结合起来:
select * from score where cno in (select cno from course where tno=(select tno from teacher where tname='张旭'))
解法1:select teacher.tname,course.cno,score.degree from teacher,course,score where teacher.tno=course.tno and course.cno=score.cno and tname='张旭';
解法2:select degree from Score join Course on Score.Cno=Course.Cno join Teacher on Course.Tno=Teacher.Tno where Tname = '张旭'
24、查询选修某课程的同学人数多于3人的教师姓名。
1.select tname from teacher where tno=()2.select tno from course where cno=()3.select cno from score group by cno having count(*)>3
联合起来:
select tname from teacher where tno=(select tno from course where cno=(select cno from score group by cno having count(*)>3))
解法1;select teacher.tname from teacher,score,course where teacher.tno=course.tno and course.cno=score.cno group by tname having count(*)>3 ;
解法2:select Tname from Teacher where Tno=(select Tno from Course where Cno=(select Cno from Score group by Cno having COUNT(Cno)>=3))
25、查询95033班和95031班全体学生的记录。
解法1:select * from student where class='95033' or class='95031';
解法2:select*from student inner join Score on Student.Sno=Score.Sno where Class in(‘95033’,‘95031’)
解法3:select *from Student,Score where Class in(95033,95031) and Student.Sno=Score.Sno
select * from(score left join course on score.cno=course.cno)left join student on score.sno=student.sno where class="95033" or class="95031";
26、 查询存在有85分以上成绩的课程Cno.
select score.cno from score,course where score.cno=course.cno and degree>85;
select cno from score group by cno having max(degree)>85;
27、查询出“计算机系“教师所教课程的成绩表
解法1:select degree from teacher,score,course where teacher.tno=course.tno and course.cno=score.cno and teacher.depart='计算机系';
解法2:select *from Score where Cno in( select Cno from Course where Tno in (select Tno from Teacher where Depart='计算机系'))解法3:select Sno,Score.Cno,Degree from Score join Course on Score.Cno=Course.Cno join Teacher on Course.Tno=Teacher.Tno where Depart='计算机系'
28、查询“计算 机系”与“电子工程系“不同职称的教师的Tname和Prof。
解法1:select tname,prof from teacher where depart='计算机系' and prof not in (select prof from teacher where depart='电子工程系');
解法2:select tname,prof from teacher where prof not in
(select prof from teacher where depart='电子工程系' and prof in (select prof from teacher where depart='计算机系'))and depart in ('计算机系','电子工程系')--查询两个系中教师相同职称名称,不在这里面的就是除去两个系都有的剩下的,见上面select prof from teacher where depart='电子工程系' and prof in (select prof from teacher where depart='计算机系')
29、查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的Cno、Sno和Degree,并按Degree从高到低次序排序。(any的用法)
解法1;select * from score where cno='3-105' and degree > (select min(degree) from score where cno='3-245')order by degree desc;
解法2:select * from score where cno='3-105' and degree > any(select degree from score where cno='3-245')order by degree desc;
30、查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的Cno、Sno和Degree.(all的用法)
select * from score where cno='3-105' and degree > (select max(degree) from score where cno='3-245');
select * from score where cno='3-105' and degree > all(select degree from score where cno='3-245');
31、 查询所有教师和同学的name、sex和birthday.
select tname,tsex,tbirthday from teacherunionselect sname,ssex,sbirthday from student
32、查询所有“女”教师和“女”同学的name、sex和birthday.
select tname,tsex,tbirthday from teacher where tsex='女'union select sname,ssex,sbirthday from student where ssex='女'
33、 查询成绩比该课程平均成绩低的同学的成绩表。
select * from score as aa where degree<(select avg(degree) from score as bb where aa.cno=bb.cno);
解法2:详解--相关子查询--同一门学科的平均分,,每门学科低于自身平均分的select * from score as a where a.degree<
(select AVG(degree) from score as b where a.cno = b.cno group by cno)--相当于foreach列出每一门课的平均分select * from score as a where a.degree<
(select AVG(degree) from score as b group by cno having a.cno = b.cno)
34、 查询所有任课教师的Tname和Depart
解法1:select tname,depart from teacher where exists (select * from course where teacher.tno=course.tno);
解法2:select Tname,Depart from Teacher where Tno in(select distinct Tno from Course)
35 、 查询所有未讲课的教师的Tname和Depart.
解法1:select tname,depart from teacher where not exists (select * from course where teacher.tno=course.tno);
36、查询至少有2名男生的班号。
select class from student where ssex='男'group by class having count(*)>=2;
37、查询Student表中不姓“王”的同学记录。
select * from student where sname not like'王%';
38、查询Student表中每个学生的姓名和年龄。
select sname,year(now())-year(sbirthday) from student;
39、查询Student表中最大和最小的Sbirthday日期值。
select max( date(student.sbirthday)) ,min( date(student.sbirthday)) from student;
40、以班号和年龄从大到小的顺序查询Student表中的全部记录。
select * from student order by class desc,date(sbirthday) asc;
41、查询“男”教师及其所上的课程。
解法1:select teacher.tname,teacher.tsex,course.cname from teacher,course where teacher.tno=course.tno and tsex='男';
解法2:select teacher.tno,tname,tsex,cname,cno from teacher join course on teacher.tno=course.tno where tsex='男'
42、查询最高分同学的Sno、Cno和Degree列。
select sno,cno,degree from score where degree=(select max(degree) from score);
43、查询和“李军”同性别的所有同学的Sname.
select sname from student where ssex=(select ssex from student where sname='李军');
44、查询和“李军”同性别并同班的同学Sname.
select sname from student where ssex=(select ssex from student where sname='李军') and class=(select class from student where sname='李军');
45、查询所有选修“计算机导论”课程的“男”同学的成绩表。
select student.sname,score.degree from student,score,course where student.sno=score.sno and course.cno=score.cno and course.cname='计算机导论' and student.ssex='男';
select * from score where sno in(select sno from student where ssex='男') and cno=(select cno from course where cname='计算机导论')