oracle06_oracle的多表查询(跟着宝哥学java:oracle系列：全网最全)：多表查询、连接查询、子查询、合并结果集

/*
多表查询：一次查多张表
1：合并结果集： 把两个结果集通过union和union all 合并为一个结果集
               对合并的两个结果集有要求：列类型和列个数相同
               select sname,sex,sage from stu1 union select tname,tsex,tage from tea1; -- 重复行只显示一次
               select sname,sex,sage from stu1 union all select tname,tsex,tage from tea1; -- 显示所有行
2：连接查询：form后面两张表
     2.1 笛卡尔积：让两张表的所有记录互相见面
         select * from stu1,tea1;
     2.2 内连接：只获取满足条件的笛卡尔积行
         select * from stu1,tea1 where stu1.stid=tea1.tid; 
     2.3 左外连接：保证左边表的完整性
         select * from stu1 left join tea1 on stu1.stid=tea1.tid;     
     2.4 右外连接：保证右边表的完整性
         select * from stu1 right join tea1 on stu1.stid=tea1.tid; 
         
     注意： 在oracle中可以通过内连接的方式实现外连接：+在哪里 就保证对方的完整性
            
            select * from stu1,tea1 where stu1.stid(+)=tea1.tid; 
            等价于： select * from stu1 right join tea1 on stu1.stid=tea1.tid; 
            select * from stu1,tea1 where stu1.stid=tea1.tid(+);
            等价于：select * from stu1 left join tea1 on stu1.stid=tea1.tid;
3：子查询：两个select嵌套
     3.1 子select出现在where后面作为条件： 可以使用的运算符、关键字：
           > >= < <= != = in,not in,between and, >any, <any, >all ,<all
         如：select * from stu1 where sage=(select max(sage) from stu1);  
             select * from stu1 where (sage,sex) in (select sage,sex from stu1 where sname in('张三','李四'));
     3.2 子select出现在from后面作为表：
        如：select * from (select * from stu1 where sex='男') s1,(select * from stu1 where sex='女') s2 where s1.sage=s2.sage;
     3.3 子select出现在select后面：作为结果集的列
        如：select s1.*,(select avg(sage) from stu1)-s1.sage 差值,(select avg(sage) from stu1) 平均年龄 from stu1 s1;
                 
        
*/
create table stu1(
   sid int primary key,
   sname varchar(100),
   sage int,
   sex char(3)
);
insert into stu1 values(
    trunc(dbms_random.value(1,100),0),
    dbms_random.string('l',6),
    trunc(dbms_random.value(15,20),0),
    case when dbms_random.value>0.5 then '男' else '女' end,
      null
    );
truncate table tea1;

create table tea1(
   tid int primary key,
   tname varchar(100),
   tage int,
   tsex char(3)
);
alter table stu1 add  stid int references tea1(tid);
update stu1 set stid= mod(sid,6)+1;
select * from stu1;
insert into tea1 values(1,'张三',30,'女');
insert into tea1 values(2,'李四',40,'男');
insert into tea1 values(3,'王五',31,'女');
insert into tea1 values(4,'赵六',42,'男');
insert into tea1 values(5,'田七',33,'女');
insert into tea1 values(6,'刘八',44,'男');
insert into tea1 values(7,'tom',44,'男');
insert into tea1 values(8,'rose',44,'男');
insert into tea1 values(
    trunc(dbms_random.value(1000,10000),0),
    dbms_random.string('l',6),
    trunc(dbms_random.value(15,20),0),
    case when dbms_random.value>0.5 then '男' else '女' end
    );
select * from stu1;
select * from tea1;

select sname,sex,sage from stu1 union select tname,tsex,tage from tea1; -- 重复行只显示一次
select sname,sex,sage from stu1 union all select tname,tsex,tage from tea1; -- 显示所有行

-- 让所有学生和所有老师互相见面:求笛卡尔积
select * from stu1,tea1;
-- 获取学生及其老师的信息：去除笛卡尔积中的垃圾行：老师和学生无关的行：：通过外键=主键
select * from stu1,tea1 where stu1.stid=tea1.tid;
-- 获取所有学生及其老师的信息：去除笛卡尔积中的垃圾行：保证有所有学生的信息
select * from stu1 left join tea1 on stu1.stid=tea1.tid;
select * from tea1 right join stu1 on stu1.stid=tea1.tid;
-- oracle的外连接方言
select * from stu1,tea1 where stu1.stid(+)=tea1.tid;
select * from stu1,tea1 where stu1.stid=tea1.tid(+);
-- 获取最大年龄学生的信息
select * from stu1 where sage=(select max(sage) from stu1);
-- 获取和张三，李四年龄相同并且性别相同的所有学生的信息
select sage,sex from stu1 where sname in('张三','李四');
select * from stu1 where (sage,sex) in (select sage,sex from stu1 where sname in('张三','李四'));

-- 获取年龄相同的男女生组合
select * from stu1 s1,stu1 s2 where s1.sex='男' and s2.sex='女' and s1.sage=s2.sage;
select * from (select * from stu1 where sex='男') s1,(select * from stu1 where sex='女') s2 where s1.sage=s2.sage;

--- 获取所有学生及其与平均年龄之间的差距
select s1.*,(select avg(sage) from stu1)-s1.sage 差值,(select avg(sage) from stu1) 平均年龄 from stu1 s1;