以问题驱动的方式来讲解
create table stu
(
sno char(10) not null,
sname char(20) not null,
cname char(20) not null,
score decimal(3,1) not null
);
insert into stu values('001','王军','计网',70.0);
insert into stu values('001','王军','算法',70.0);
insert into stu values('002','李伟','计网',80.9);
insert into stu values('002','李伟','算法',59.0);
insert into stu values('003','刘辉','计网',56.0);
insert into stu values('003','刘辉','算法',56.0);
查询成绩表中存在不及格课程的学生姓名,所有课程名和成绩信息
刚开始上来就敲了这个
select sname,cname,score
from stu
where score<60;
只显示成绩小于60的课程,不是所有课程,这时就用到表的自身连接了
select *
from stu as s1,stu as s2
where s1.sno=s2.sno;
表的自身连接就是产生2个表的笛卡尔积
select s1.sname,s1.cname,s1.score
from stu as s1,stu as s2
where s1.sno=s2.sno
and s2.score<60
order by s1.sname;
就是上图最后6行的数据
有重复的语句所以要去重
select distinct s1.sname,s1.cname,s1.score
from stu as s1,stu as s2
where s1.sno=s2.sno
and s2.score<60
order by s1.sname;
完成
再来个例子,具体的原理就不讲了
查询每一门课的先修课的先修课
course表有2列con(课程号),cpno(先修课程号)
select first.cno,second.cpno
from course first,course second
where first.cpno=second.cno