--此文章可以作为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 的函数
转播
