oracle数据库之多表查询

• 两个表之间内连接

select s.stuid,s.stuname,s.stuage,s.gender,cl.classesname
from student s,classes cl
where s.classesid = cl.classesid;

select s.stuid,s.stuname,s.stuage,s.gender,cl.classesname
from student s inner join classes cl on(s.stuid = cl.classesid)
where gender='男';

• 三个表之间

create table Student(
  stuid number(6) primary key,
  stuname nvarchar2(50) not null,
  stugender nvarchar2(4) not null,
  stuage number(4) default 18,
  address number(6),
  phone number(11),
  classesid number(6)
);

create table classes(
  classesid number(6) primary key,
  classesname nvarchar2(50) not null
);

create table address(
       addid number(6),
       addstreet nvarchar2(20),
       addnum nvarchar2(20),
       constraint address_pk primary key (addid)
);

alter table student add constraint student_classes_fk
foreign key (classesid) references classes(classesid);

alter table student add constraint student_address_fk
foreign key (address) references address(addid);
commit;

drop sequence stu_seq;
create sequence stu_seq
start with 1
increment by 1
nomaxvalue
nocycle
cache 10;
drop sequence class_seq;
create sequence class_seq
start with 1
increment by 1
nomaxvalue
nocycle
cache 10;

create sequence add_seq
start with 1
increment by 1
nomaxvalue
nocycle
cache 10;

insert into classes values(class_seq.nextval,'书法班');
insert into classes values(class_seq.nextval,'钢琴班');
insert into classes values(class_seq.nextval,'跆拳道班');
insert into classes values(class_seq.nextval,'舞蹈班');
insert into classes values(class_seq.nextval,'小主持人');
commit;

select * from classes;

insert into address
values(add_seq.nextval,'莲花街',null);
insert into address
values(add_seq.nextval,'科学大道',null);
insert into address
values(add_seq.nextval,'雪松路',null);
insert into address
values(add_seq.nextval,'银屏路',null);
insert into address
values(add_seq.nextval,'石南路',null);
insert into address
values(add_seq.nextval,'瑞达路',null);
commit;

select * from address;

insert into student values(stu_seq.nextval,'Tom','男',22,3,'123456',1);
insert into student values(stu_seq.nextval,'Jerry','男',23,6,'123456',3);
insert into student values(stu_seq.nextval,'sanmao','男',18,1,'123456',1);
insert into student values(stu_seq.nextval,'ruhua','女',19,5,'123456',2);
insert into student values(stu_seq.nextval,'Mray','女',24,3,'123456',3);
commit;
select * from student;

select st.stuid stuid,st.stuname sname,st.stugender sgen,st.stuage age,
ad.addstreet adds,st.phone ph,cl.classesname cname
from student st,classes cl,address ad
where st.classesid = cl.classesid and st.address = ad.addid;

select st.stuid stuid,st.stuname sname,st.stugender sgen,st.stuage age,
ad.addstreet adds,st.phone ph,cl.classesname cname
from classes cl inner join student st
on(cl.classesid=st.classesid) 
inner join address ad
on(ad.addid=st.address);

--左外链接--
select st.stuid stuid,st.stuname sname,ad.addstreet adds
from address ad left outer join student st
on(ad.addid=st.address);

select st.stuid stuid,st.stuname sname,ad.addstreet adds
from address ad,student st
where ad.addid = st.address(+);

--右外连接--
select st.stuid stuid,st.stuname sname,ad.addstreet adds
from student st right outer join address ad
on(ad.addid=st.address);

select st.stuid stuid,st.stuname sname,ad.addstreet adds
from student st,address ad
where st.address(+)=ad.addid;

select cid,cname from(
  select cl.classesid cid,cl.classesname cname,st.stuid stuid
  from classes cl left outer join student st
  on(cl.classesid = st.classesid)
) where stuid is null;

--行号和行标示--
select rowid from student;
select rownum from student;