#【例6.6】查询student表中专业为计算机或性别为女的学生。
use stusys;
select * from student
where speciality ='计算机' or ssex ='女';
#【例6.7】查询score表成绩为92、95的记录。
use stusys;
select * from score
where grade =92 or grade=95;
#【例6.8】查询student表中不在1998年出生的学生情况。
use stusys;
select * from student
where sbirthday not between '1998-01-01' and '1998-12-31';
#【例6.9】查询已选课但未参加考试的学生情况。
use stusys;
select * from score
where grade is null;
#【例6.10】查询student表中姓董的学生情况。
use stusys;
select * from student
where sname like '董%';
#【例6.11】查询含有“系统”或“数字”的所有课程名称。
use stusys;
select cname as 课程名称 from course
where cname regexp '系统|数字';
#【例6.15】查询8001课程的最高分、最低分、平均成绩。
use stusys;
select max(grade)as 最高分,min(grade) as 最低分, avg(grade) as 平均成绩, count(sno) as 人数,sum(grade) as 总分
from score
where cno='8001';
#【例6.16】查询各门课程的最高分、最低分、平均成绩。
use stusys;
select max(grade)as 最高分,min(grade) as 最低分, avg(grade) as 平均成绩, count(sno) as 人数,sum(grade) as 总分
from score
where grade is not null
group by cno;
#【例6.17】查询平均成绩在90分以上的学生的学号和平均成绩。
use stusys;
select max(grade)as 最高分,min(grade) as 最低分, avg(grade) as 平均成绩, count(sno) as 人数,sum(grade) as 总分
from score
where grade is not null
group by cno
having avg(grade)>90;
#【例6.18】查询至少有5名学生选修且以8开头的课程号和平均分数。
use stusys;
select cno as 课程号,avg(grade) as 平均分数 from score
where cno like'8%'
group by cno
having count(*)>5;
#【例6.19】将计算机专业的学生按出生时间降序排序。
#【例6.20】查询成绩表中成绩前3位学生的学号、课程号和成绩。
use stusys;
select * from score
order by grade desc
limit 0,3;
#多表查询
#【例6.21】采用交叉连接查询教师和和讲课地点所有可能组合。
use stusys;
select a.* , b.*
from teacher a,lecture b
where a.tno=b.tno;
#或者:
select a.*,b.*
from teacher a join lecture b on a.tno=b.tno;
#【例6.24】对例6.22进行自然连接查询。
use stusys;
select * from teacher natural join lecture;
#【例6.25】查询选修了“1201”课程的成绩高于学号为“191002”的成绩的学生姓名。
use stusys;
select a.sno as 学号,a.cno as a的课程号,a.grade as 成绩
from score a,score b
where a.cno='1201' and a.grade>b.grade and b.sno='191002' and a.cno=b.cno;
#【例6.28】查询选修了课程号为8001的课程的学生情况。
use stusys;
select a.*,b.* from student a,score b
where b.cno='8001' and a.sno=b.sno;
select * from student a natural join score b
where b.cno='8001';
select * from student
where sno in
(select sno from score
where cno='8001'
);