oracle数据库多表联查并排序,oracle数据库之多表联接查询（一）

--此文章可以作为sql脚本直接运行

/*

建表以及插入相关数据，为后面的查询做准备工作

*/ --建学生信息表

create table studentinfo(

stuid  int  primary key,

sname  varchar(10) not null,

cid    int not null,

address varchar(20)

);

--建班级表

create table classes(

cid   int primary key ,

cname varchar2(10)

);

--建考试信息表

create table exam(

eid int  primary key,

stuid int not null,

coursename varchar(20) not null,

score float

);

--加上外键约束     外键约束在表内不好加，因为要可虑建立表的先后顺序，放在后面一起加就可以不考虑此问题

alter table studentinfo add  constraint fk_cid foreign key (cid) references  classes(cid);

alter table exam add  constraint fk_stuid foreign key(stuid) references  studentinfo(stuid);

--必须先插入classes表的数据，否则studentinfo表中的外键找不到参照的对象

insert into classes values (1,'0901');

insert into classes values (2,'0902');

insert into classes values (3,'0903');

create sequence seq_stuid ;

insert into studentinfo values(seq_stuid.Nextval,'zhangsan',1,'hnzz');

insert into studentinfo values(seq_stuid.Nextval,'lisi',1,'hncs');

insert into studentinfo values(seq_stuid.Nextval,'wangwu',2,'hncd');

insert into studentinfo values(seq_stuid.Nextval,'zhaoliu',2,'hndd');

insert into studentinfo values(seq_stuid.Nextval,'zhudao',3,'hnyy');

insert into studentinfo values(seq_stuid.Nextval,'david',3,'hnyy');

create sequence seq_eid ;

insert into exam values(seq_eid.Nextval,1,'java',80);

insert into exam values(seq_eid.Nextval,2,'java',59);

insert into exam values(seq_eid.Nextval,3,'java',67);

insert into exam values(seq_eid.Nextval,5,'oracle',99);

insert into exam values(seq_eid.Nextval,6,'oracle',100);

select * from classes ;

select * from studentinfo ;

select * from  exam ;

/*  开始今天的内容  */

--内联查询

----题目：请查询出所有参加了考试的同学的姓名，班级，考试科目和考试成绩

select sname as 姓名,cname as 班级,coursename as 科目,score as 分数    --要查询什么就select什么，最好取上别名，更容易理解查询结果

from studentinfo s inner join exam e     --从studentinfo表 内联接 exam表

on s.stuid = e.stuid                     --on   后面接条件(两张表联接的数据对应方式)    取表别名可以省写一些代码

inner join classes c

on c.cid=s.cid ;                         --这种联接可以一直联接下去    前提是表之间有数据的对应

--下面我们将进行联接的两个表交换下位置

select sname as 姓名,cname as 班级,coursename as 科目,score as 分数

from exam e inner join studentinfo s

on s.stuid = e.stuid

inner join classes c

on c.cid=s.cid ;

--->查询结果无变化     内联查询跟表的位置无关

--->分析：内联查询是   从左边的表往右边的表查找能匹配的记录，无匹配的记录行不显示

--外联查询  可以查询出某些找不到对应值的行    左外联和右外联

--左外联  左边的表中的行数全部保留  (从左边的行往右边找对应行)  无匹配记录的行也会显示，显示数据为空

select sname ,coursename ,score

from studentinfo s left join exam e on s.stuid =e.stuid ;

--右外联  右边的表中的行数全部保留  (从右边的行往左边找对应行)

select sname ,coursename ,score

from studentinfo s right join exam e on s.stuid =e.stuid ;

--可以找出没有去参加考试的人

select sname ,coursename ,score

from studentinfo s left join exam e on s.stuid =e.stuid

where score is null ;

--全外联   两边表的行数据都能够保存    会显示无对应记录的行

select sname ,coursename ,score

from studentinfo s full join exam e on s.stuid =e.stuid ;

--交叉联接     查询结果  左边表的每一行都对应一次右边表的行

----左边有5行   右边有3行     查询结果是  5*3=15行

select sname ,coursename ,score

from studentinfo s cross join exam e  ;

--下面的查询和交叉联接查询效果一样

select sname ,coursename ,score

from studentinfo s , exam e  ;

create table shu1(

num1 int

);

insert into shu1 values (0);

insert into shu1 values (1);

insert into shu1 values (2);

insert into shu1 values (3);

insert into shu1 values (4);

insert into shu1 values (5);

insert into shu1 values (6);

insert into shu1 values (7);

insert into shu1 values (8);

insert into shu1 values (9);

--交叉联接的应用

--生成彩票号码的表  还用到了自联接  这里模拟的是简单的，只有3位数的彩票，不过思路是这样的

create table caipiao   --oracle中一种特殊的建表方式----》create table 表名 as select ...  将查询到的数据直接插入到新生成的表中

as

select a.num1 as a,b.num1 as b,c.num1 as c   --新建的表的列名就是这里取的别名   这里新建表的列名就是 a，b，c

from shu1 a cross join shu1 b cross join shu1 c;

select * from caipiao ;

select count(*) as 彩票号码总数 from caipiao ;

--求奇数列的和

create table test

as

select shu1.*,rownum as hangshu

from shu1 ;

select sum(num1) as 奇数列和 from test

where mod(hangshu,2) = 1 ;   --mod(a,b)   求 a 模 b 的函数