目 录
19、查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。
20、查询和学号为108、101的同学,同年出生的所有学生的sno、sname和sbirthday列。
26、查询“计算机系”与“电子工程系“不同职称的教师的tname和prof。
27、查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的Cno、Sno和Degree,并按Degree从高到低次序排序。
28、查询选修编号为“3-105"且成绩高于选修编号为“3-245"课程的同学的Cno、Sno和Degree。
29、查询所有教师和同学的name、sex和birthday。
30、查询所有“女"教师和“女”同学的name、sex和birthday。
36、查询student表中最大和最小的sbirthday日期值。
37、以班号和年龄从大到小的顺序查询student表中的全部记录。
30-查询练习-子查询
19、查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。
select * from score
where degree > (select degree from score where sno = '109' and cno = '3-105');
select degree from score where sno = '109' and cno = '3-105';
31-查询练习year函数与带in关键字的子查询
20、查询和学号为108、101的同学,同年出生的所有学生的sno、sname和sbirthday列。
select * from student where sno in(108, 101);
select year(sbirthday) from student where sno in(108, 101);
select * from student where year(sbirthday)
in (select year(sbirthday) from student where sno in(108, 101));
32-查询练习-多层嵌套子查询
21、查询“张旭“教师任课的学生成绩。
/* select * from teacher where tname = '张旭'; */
select tno from teacher where tname = '张旭';
/* select * from course where tno = ( select tno from teacher where tname = '张旭' ); */
select cno from course where tno = ( select tno from teacher where tname = '张旭' );
select * from score where cno = ( select cno from course where tno = ( select tno from teacher where tname = '张旭' ) );
33-查询练习-多表查询
22、查询选修某课程的同学人数多于5人的教师姓名。
insert into score values('101', '3-105', '90');
insert into score values('102', '3-105', '91');
insert into score values('104', '3-105', '89');
where后面的语句用于筛选表内全部记录, having用于筛选分组后的记录。
select cno from score group by cno having count(*) > 5;
select * from teacher;
select tno from course where cno = ( select cno from score group by cno having count(*) > 5 ) ;
select tname from teacher where tno =
( select tno from course where cno = ( select cno from score group by cno having count(*) > 5 ) );
select tname from teacher where tno in
( select tno from course where cno = ( select cno from score group by cno having count(*) > 5 ) );
34-查询练习-in表示或者关系
23、查询95033班和95031班全体学生的记录。
insert into student values('111', '霸刀宋壹', '男', '2000-02-07', '95038' );
select * from student;
select * from student where class in ('95031', '95038');
35-查询练习-where条件查询
24、查询存在 有85分以上成绩的课程Cno。
36-查询练习-子查询
25、查询出“计算机系“教师所教课程的成绩表。
select * from teacher where depart = '计算机系';
select * from course where tno in ( select tno from teacher where depart = '计算机系' );
select * from score where cno in ( select cno from course where tno
in ( select tno from teacher where depart = '计算机系' ) );
37-查询练习-union和notin的使用
26、查询“计算机系”与“电子工程系“不同职称的教师的tname和prof。
union 求 并集
select prof from teacher where depart = '电子工程系' ;
select * from teacher where depart = '计算机系' and
prof not in (select prof from teacher where depart = '电子工程系');
select * from teacher where depart = '电子工程系' and
prof not in (select prof from teacher where depart = '计算机系');
select * from teacher where depart = '计算机系' and
prof not in (select prof from teacher where depart = '电子工程系')
union
select * from teacher where depart = '电子工程系' and
prof not in (select prof from teacher where depart = '计算机系');
38-查询练习-any表示至少一个-desc降序
27、查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的Cno、Sno和Degree,并按Degree从高到低次序排序。
至少?大于其中至少一个,any
select * from score where cno = '3-245';
select * from score where cno = '3-105';
select * from score where cno = '3-105' and
degree > any( select degree from score where cno = '3-245' );
select * from score where cno = '3-105' and
degree > any( select degree from score where cno = '3-245' )
order by degree desc;
39-查询练习-all表示所有
28、查询选修编号为“3-105"且成绩高于选修编号为“3-245"课程的同学的Cno、Sno和Degree。
select * from score where cno = '3-105' and
degree > all( select degree from score where cno = '3-245' )
order by degree desc;
select * from score where cno = '3-105' and
degree > all( select degree from score where cno = '3-245' );
40-查询练习-as取别名-union求并集
29、查询所有教师和同学的name、sex和birthday。
select tname, tsex, tbirthday from teacher;
select sname, ssex, sbirthday from student;
select tname, tsex, tbirthday from teacher
union
select sname, ssex, sbirthday from student;
select tname as 'name', tsex as sex, tbirthday as birthday from teacher
union
select sname, ssex, sbirthday from student;
41-查询练习-union求并集
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 = '女';
42-查询练习-复制表数据做条件查询
31、查询成绩比该课程平均成绩低的同学的成绩表。
同表同字段不能同时比较,需复制两张表来比较!
select cno, avg(degree) from score group by cno;
select * from score;
select * from score a where degree < ( select avg(degree) from score b where a.cno = b.cno );
43-查询练习-子查询
32、查询所有任课教师的Tname和Depart。
select * from teacher;
select * from course;
select tname, depart from teacher where tno in (select tno from course);
44-查询练习-条件加分组筛选
33、查询至少有2名男生的班号。
having在分完组后运行,可以过滤组,是高配版where。
select * from student;
select class from student where ssex = '男' group by class having count(*) > 1;
45-查询练习-notlike模糊查询取反
34、查询student表中不姓“王”的同学记录。
select * from student;
select * from student where sname not like '王%';
46-查询练习-year函数与now函数
35、查询student表中每个学生的姓名和年龄。
年龄 = 当前年份 - 出生年份
select year( now() );
select year(sbirthday) from student;
select sname, year( now() ) - year(sbirthday) as '年龄' from student;
47-查询练习-max与min函数
36、查询student表中最大和最小的sbirthday日期值。
select sbirthday from student;
select sbirthday from student order by sbirthday;
select max(sbirthday) as '最大', min(sbirthday) as 最小 from student;
48-查询练习-多字段排序
37、以班号和年龄从大到小的顺序查询student表中的全部记录。
select * from student order by class, sbirthday;
select * from student order by class desc, sbirthday;
49-查询练习-子查询
38、查询“男”教师及其所上的课程。
50-查询练习-max函数与子查询
39、查询最高分同学的sno、cno和degree列。
select max(degree) from score;
select * from score where degree = ( select max(degree) from score );
51-查询练习-子查询
40、查询和“李军”同性别的所有同学的Sname。
select ssex from student where sname = '李军';
select sname from student where ssex = (select ssex from student where sname = '李军');
52-查询练习-子查询
41、查询和“李军”同性别并同班的同学Sname。
select sname from student
where ssex = (select ssex from student where sname = '李军' )
and class = (select class from student where sname = '李军' ) ;
53-查询练习-子查询
42、查询所有选修“计算机导论"课程的“男”同学的成绩表。
select * from student where ssex = '男' ;
select * from course where cname = '计算机导论';
select * from score
where cno = ( select cno from course where cname = '计算机导论' )
and sno in ( select sno from student where ssex= '男' );
54-查询练习-按等级查询
43、现查询所有同学的Sno、Cno和grade列。
-- 43、假设使用如下命令建立了一个grade表:
create table grade(
low int(3),
upp 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 * from grade;
select sno, cno, grade from score, grade where degree between low and upp;