Oracle 数据库基于SCOTT模式的多表检索

阳光穿透心脏的1/2处 2022-07-03 13:25 149阅读 0赞

Oracle提供的SCOTT模式可以示例表和数据来展示数据库的一些特性。下面我们连接该模式，并对该模式下的表进行操作。

1.查询该模式下有哪些示例表

select table_name from user_tables;

![Center][]

2.在多表检索前，我们先试着进行单表查询

select ename as "姓名",job as "职位"，hiredate as "工作日期",sal as "工资"
    from emp;

![Center 1][]

\---->查询并排序数据

select ename,job,sal from emp where deptno=30 order by 3;

![Center 2][]

3.表的别名

SQL语言提供了一种机制----表的别名。表的别名是在SELECT语句中为表定义的临时性名称，以简化对表的引用。下面运用表的别名知识点，使用SELECT语句检索EMP和DEPT表，查询属于某一个部门的职工信息。

select e.ename 姓名,e.job 职位,e.sal 工资,d.dname 部门 from emp e,dept d wh
    ere e.deptno=d.deptno and d.dname='SALES';

![Center 3][]

4.内连接

内连接是指满足连接条件的连接操作，也就是通常所说的连接操作。也就是说，在内连接的检索结果中，都是满足连接条件的数据。因此，内连接的检索结果是笛卡尔积中满足连接条件的子集。(注意：这也是区别于以下几种连接之处，不满足两个表连接条件的数据，坚决不显示在查询的结果中)

select e.ename ,e.job ,e.sal ,d.deptno ,d.dname from emp e join dept d on
    e.deptno=d.deptno;

![Center 4][]

5.外连接

如果某个表中的数据不满足连接条件，而又想出现在检索结果中，那么可以使用外连接。外连接的特点是某些不满足连接条件的数据也可以出现在检索结果中，可能会以空值null的形式出现。（区别于内连接之处）

根据外连接检索结果中包含的数据，外连接可以分为左外连接，右外连接和全外连接。左外连接表示在结果中不仅包含了满足条件的数据，而且还包含了连接左边的左表。在右外连接中，结果则包含了满足条件的数据和不满足条件的右表中的数据。如果左表和右表中不满足连接条件的数据都出现在结果中，那么这种连接是全外连接。

外连接的语法和内连接的语法规则相似，区别在于外连接中用LEFT OUTER JOIN,RIGHT OUTER JOIN 或 FULL OUTER JOIN关键字，而不使用INNER JOIN关键字。其中OUTER是可选的，例如，左外连接可以使用LEFT JOIN代替LEFT OUTER JOIN。

SQL> insert into emp(empno,ename,job,sal) values(8000,'Haige','Green',950);
    
    已创建 1 行。
    
    SQL> select e.ename ,e.job,e.sal,d.deptno,d.dname from emp e left join dept d
    on e.deptno=d.deptno;

![Center 5][]

\----->左外连接显示出满足连接条件的数据+左表独有的数据（如红线所示）

SQL> select e.ename,e.job,e.sal,d.deptno,d.dname from emp e right join dept d
      2  on e.deptno=d.deptno;

![Center 6][]

\----->右外连接

SQL> select e.ename,e.job,e.sal,d.deptno,d.dname from emp e full join dept d on
    e.deptno=d.deptno;

![Center 7][]

\------->全外连接

6.自然连接

与内连接的功能相似，在使用自然连接检索多个表时，Oracle会将第一个表中的列与第二个表中具有相同名称的列进行连接。在自然连接中，用户不需要明确指定进行连接的列，系统会自动完成这一任务。（最省时省力的连接查询方式）

SQL> select empno,ename,job,sal,deptno,dname from emp natural join dept where
    dname='SALES';

![Center 8][]

\----->但需要指出的是，自然连接的实际应用较差，因为它需要连接的各个表之间必须具有相同名称的列。这将会强制设计者将要连接的表设计为具有相同名称的列，并且不能够让表中的其他列具有相同的名称。

6.交叉连接

交叉连接实际上就是没有连接条件的连接。这种连接的结果就是笛卡尔积。但是我们一般可以通过where子句从中过滤掉所需要的数据。

SQL> select count(*) from emp e cross join dept d where e.deptno=d.deptno;

![Center 9][]

\------>红线处的CROSS JOIN关键字表示执行交叉连接

[Center]: /images/20220618/af373323c7ff489a896ba8d16e0d6958.png
[Center 1]: /images/20220618/64725ea226744e35b292879980cabc71.png
[Center 2]: /images/20220618/6a37b80823184ff39aec67c1cd25b65e.png
[Center 3]: /images/20220618/251da788fb5f470c8c8a9d94097e54de.png
[Center 4]: /images/20220618/44b99bb2af32421baf5c44305d2337b0.png
[Center 5]: /images/20220618/1c9d722bfec74f818de55d10863e35f8.png
[Center 6]: /images/20220618/e006faccde8f476ba07674177584fd22.png
[Center 7]: /images/20220618/d8dc004e7726480ea067a30f4fc0ea1a.png
[Center 8]: /images/20220618/ad09ef2a669349c19ba7040040b3df67.png
[Center 9]: /images/20220618/6eaf606e16604c71a8a569c47a0e97c6.png