多表查询

目录

统计出一张数据表中的数据量

查询 dept 表中的数据量

查询 emp 表中的数据量

实现 emp 与 dept 的多表查询

笛卡尔积

消除笛卡尔积

把数据表 emp 的别名定为 e，数据表 dept 的别名定为 d，然后在查询中分别使用 e 和 d 代替这两个表

---

Oracle从入门到总裁:https://blog.csdn.net/weixin_67859959/article/details/135209645

所谓的多表查询指的就是同时从多张数据表中取出数据并且显示的一种操作

select [distinct] * | 列 [ 别名 ] , 列 [ 别名 ] ...
from 表名称 [ 别名 ], 表名称 [ 别名 ],...
[where 限定条件 (s)] 
[order by  排序字段 [asc | desc], 排序字段 [asc | desc],...]

上面语法中，

第一行表示确定要显示的数据列；

第二行确定数据来源；

第三行对数据行进行筛选；

第四行对选定数据的行与列排序。

和前面唯一的不同之处在于 FROM 后面增加了更多的表

下面按照这样的语法结构实现多表查询。本次将利用 emp 与 dept 两张表进行多表查询操作。在查询之前，先介绍一个函数

统计出一张数据表中的数据量

关键词是count()

查询 dept 表中的数据量

SQL> select count(*)2  from dept;COUNT(*)
----------4

可以看出这个数据表有 4 行记录

查询 emp 表中的数据量

SQL> select count(*)2  from emp;COUNT(*)
----------13

运行后可以看出这个数据表有 13 行记录

有些同学会发现记录数不一样,这个没有关系的

也就是说，这两张表加起来，总共有 17 行记录

实现 emp 与 dept 的多表查询

SQL> select *2  from emp,dept;EMPNO ENAME                JOB                       MGR HIREDATE              SAL       COMM     DEPTNO     DEPTNO DNAMELOC
---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ---------- ---------- ---------------------------- --------------------------7369 SMITH                CLERK                    7902 17-12月-80            800                    20         10 ACCOUNTINGNEW YORK7499 ALLEN                SALESMAN                 7698 20-2月 -81           1600        300         30         10 ACCOUNTINGNEW YORK7521 WARD                 SALESMAN                 7698 22-2月 -81           1250        500         30         10 ACCOUNTINGNEW YORK7566 JONES                MANAGER                  7839 02-4月 -81           2975                    20         10 ACCOUNTINGNEW YORK7654 MARTIN               SALESMAN                 7698 28-9月 -81           1250       1400         30         10 ACCOUNTINGNEW YORK7698 BLAKE                MANAGER                  7839 01-5月 -81           2850                    30         10 ACCOUNTINGNEW YORK7782 CLARK                MANAGER                  7839 09-6月 -81           2450                    10         10 ACCOUNTINGNEW YORK7839 KING                 PRESIDENT                     17-11月-81           5000                    10         10 ACCOUNTINGNEW YORK7844 TURNER               SALESMAN                 7698 08-9月 -81           1500          0         30         10 ACCOUNTINGNEW YORK7900 JAMES                CLERK                    7698 03-12月-81            950                    30         10 ACCOUNTINGNEW YORK7902 FORD                 ANALYST                  7566 03-12月-81           3000                    20         10 ACCOUNTINGNEW YORK7934 MILLER               CLERK                    7782 23-1月 -82           1300                    10         10 ACCOUNTINGNEW YORK8989 HELLO                                                                                                      10 ACCOUNTINGNEW YORK7369 SMITH                CLERK                    7902 17-12月-80            800                    20         20 RESEARCHDALLAS7499 ALLEN                SALESMAN                 7698 20-2月 -81           1600        300         30         20 RESEARCHDALLAS7521 WARD                 SALESMAN                 7698 22-2月 -81           1250        500         30         20 RESEARCHDALLAS7566 JONES                MANAGER                  7839 02-4月 -81           2975                    20         20 RESEARCHDALLAS7654 MARTIN               SALESMAN                 7698 28-9月 -81           1250       1400         30         20 RESEARCHDALLAS7698 BLAKE                MANAGER                  7839 01-5月 -81           2850                    30         20 RESEARCHDALLAS7782 CLARK                MANAGER                  7839 09-6月 -81           2450                    10         20 RESEARCHDALLAS7839 KING                 PRESIDENT                     17-11月-81           5000                    10         20 RESEARCHDALLAS7844 TURNER               SALESMAN                 7698 08-9月 -81           1500          0         30         20 RESEARCHDALLAS7900 JAMES                CLERK                    7698 03-12月-81            950                    30         20 RESEARCHDALLAS7902 FORD                 ANALYST                  7566 03-12月-81           3000                    20         20 RESEARCHDALLAS7934 MILLER               CLERK                    7782 23-1月 -82           1300                    10         20 RESEARCHDALLAS8989 HELLO                                                                                                      20 RESEARCHDALLAS7369 SMITH                CLERK                    7902 17-12月-80            800                    20         30 SALESCHICAGOEMPNO ENAME                JOB                       MGR HIREDATE              SAL       COMM     DEPTNO     DEPTNO DNAMELOC
---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ---------- ---------- ---------------------------- --------------------------7499 ALLEN                SALESMAN                 7698 20-2月 -81           1600        300         30         30 SALESCHICAGO7521 WARD                 SALESMAN                 7698 22-2月 -81           1250        500         30         30 SALESCHICAGO7566 JONES                MANAGER                  7839 02-4月 -81           2975                    20         30 SALESCHICAGO7654 MARTIN               SALESMAN                 7698 28-9月 -81           1250       1400         30         30 SALESCHICAGO7698 BLAKE                MANAGER                  7839 01-5月 -81           2850                    30         30 SALESCHICAGO7782 CLARK                MANAGER                  7839 09-6月 -81           2450                    10         30 SALESCHICAGO7839 KING                 PRESIDENT                     17-11月-81           5000                    10         30 SALESCHICAGO7844 TURNER               SALESMAN                 7698 08-9月 -81           1500          0         30         30 SALESCHICAGO7900 JAMES                CLERK                    7698 03-12月-81            950                    30         30 SALESCHICAGO7902 FORD                 ANALYST                  7566 03-12月-81           3000                    20         30 SALESCHICAGO7934 MILLER               CLERK                    7782 23-1月 -82           1300                    10         30 SALESCHICAGO8989 HELLO                                                                                                      30 SALESCHICAGO7369 SMITH                CLERK                    7902 17-12月-80            800                    20         40 OPERATIONSBOSTON7499 ALLEN                SALESMAN                 7698 20-2月 -81           1600        300         30         40 OPERATIONSBOSTON7521 WARD                 SALESMAN                 7698 22-2月 -81           1250        500         30         40 OPERATIONSBOSTON7566 JONES                MANAGER                  7839 02-4月 -81           2975                    20         40 OPERATIONSBOSTON7654 MARTIN               SALESMAN                 7698 28-9月 -81           1250       1400         30         40 OPERATIONSBOSTON7698 BLAKE                MANAGER                  7839 01-5月 -81           2850                    30         40 OPERATIONSBOSTON7782 CLARK                MANAGER                  7839 09-6月 -81           2450                    10         40 OPERATIONSBOSTON7839 KING                 PRESIDENT                     17-11月-81           5000                    10         40 OPERATIONSBOSTON7844 TURNER               SALESMAN                 7698 08-9月 -81           1500          0         30         40 OPERATIONSBOSTON7900 JAMES                CLERK                    7698 03-12月-81            950                    30         40 OPERATIONSBOSTON7902 FORD                 ANALYST                  7566 03-12月-81           3000                    20         40 OPERATIONSBOSTON7934 MILLER               CLERK                    7782 23-1月 -82           1300                    10         40 OPERATIONSBOSTON8989 HELLO                                                                                                      40 OPERATIONSBOSTON已选择 52 行。

发现每一行 emp 表中的记录出现了 4 次，而 4 次是 dept 表中的数据量，所以最终产生了 emp 表 13 行 *dept 表 4 行 =52 行记录

在查询显示的时候，emp 表每行记录同时显示了 dept 表的 4 行记录，而实际上只有 1 行记录是对应的，即数据表 emp 中字段 deptno 与数据表 dept 中 字段 deptno 相等

笛卡尔积

之所以会出现这样的情况，主要与数据库的产生原理有关数学的集合。这样的集合操作，会 将两个集合（数据表）的统一查询，作为乘法的形式出现。结果一定会产生积笛卡尔积

在任何情况下，进行多表查询都会存在笛卡尔积的问题。但是事实上这些积的产生对用户而言是没有任何实质上的用处的，所以需要想办法进行消除。如果要想消除积，那么必须有关联字段 

很明显，现在 emp 与 dept 数据表中都存在关联字段（大部分情况下，都习惯将关联字段设置为同名） 。此时就可以利用关联字段消除笛卡尔积

消除笛卡尔积

SQL> select *2  from emp,dept3  where emp.deptno=dept.deptno;EMPNO ENAME                JOB                       MGR HIREDATE              SAL       COMM     DEPTNO     DEPTNO DNAMELOC
---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ---------- ---------- ---------------------------- --------------------------7839 KING                 PRESIDENT                     17-11月-81           5000                    10         10 ACCOUNTINGNEW YORK7782 CLARK                MANAGER                  7839 09-6月 -81           2450                    10         10 ACCOUNTINGNEW YORK7934 MILLER               CLERK                    7782 23-1月 -82           1300                    10         10 ACCOUNTINGNEW YORK7902 FORD                 ANALYST                  7566 03-12月-81           3000                    20         20 RESEARCHDALLAS7369 SMITH                CLERK                    7902 17-12月-80            800                    20         20 RESEARCHDALLAS7566 JONES                MANAGER                  7839 02-4月 -81           2975                    20         20 RESEARCHDALLAS7900 JAMES                CLERK                    7698 03-12月-81            950                    30         30 SALESCHICAGO7844 TURNER               SALESMAN                 7698 08-9月 -81           1500          0         30         30 SALESCHICAGO7654 MARTIN               SALESMAN                 7698 28-9月 -81           1250       1400         30         30 SALESCHICAGO7521 WARD                 SALESMAN                 7698 22-2月 -81           1250        500         30         30 SALESCHICAGO7499 ALLEN                SALESMAN                 7698 20-2月 -81           1600        300         30         30 SALESCHICAGO7698 BLAKE                MANAGER                  7839 01-5月 -81           2850                    30         30 SALESCHICAGO已选择 12 行。

对应显示的是 emp 数据表中 deptno 字段和 dept 数据表中 deptno 字段相等的数据行。显示的列是两个数据表的所有列。这时候只显示 emp 表中的 13 行记录，同时每个记录所 对应的 dept 中字段的信息也显示在后面

只要是多表查询，在多张表之间一定要存在关联关系，没有关联关系的表是不可能进行多表查询的

但是现在的代码还存在一个问题，此时进行字段访问的时候采用的是“表名称 . 字段名称” ，表名称短没什么问题，而如果表名称长了就比较麻烦，例如“yuzhou_yinhexi_diqiu_yazhou_beijing_zhongyang_ren” 。所以在进行多表查询的时候强烈建议使用别名 

把数据表 emp 的别名定为 e，数据表 dept 的别名定为 d，然后在查询中分别使用 e 和 d 代替这两个表

SQL> select e.*,d.dname2  from emp e,dept d3  where e.deptno=d.deptno;EMPNO ENAME                JOB                       MGR HIREDATE              SAL       COMM     DEPTNO DNAME
---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ---------- ----------------------------7839 KING                 PRESIDENT                     17-11月-81           5000                    10 ACCOUNTING7782 CLARK                MANAGER                  7839 09-6月 -81           2450                    10 ACCOUNTING7934 MILLER               CLERK                    7782 23-1月 -82           1300                    10 ACCOUNTING7902 FORD                 ANALYST                  7566 03-12月-81           3000                    20 RESEARCH7369 SMITH                CLERK                    7902 17-12月-80            800                    20 RESEARCH7566 JONES                MANAGER                  7839 02-4月 -81           2975                    20 RESEARCH7900 JAMES                CLERK                    7698 03-12月-81            950                    30 SALES7844 TURNER               SALESMAN                 7698 08-9月 -81           1500          0         30 SALES7654 MARTIN               SALESMAN                 7698 28-9月 -81           1250       1400         30 SALES7521 WARD                 SALESMAN                 7698 22-2月 -81           1250        500         30 SALES7499 ALLEN                SALESMAN                 7698 20-2月 -81           1600        300         30 SALES7698 BLAKE                MANAGER                  7839 01-5月 -81           2850                    30 SALES已选择 12 行。

这次显示的数据列和上一个查询不完全一样，因为查询的数据列是 e.* 和 d.name，所以显示的列应该是数据表 emp 的全部字段和 dept 的 dname 字段

实际上，笛卡尔积的存在对整个程序影响是相当巨大的，即便可以消除掉显示的笛卡尔积，但是从本质上来说，永远无法避免笛卡尔积