Oracle数据库的查询之多表查询(五)

分手后的思念是犯贱 2022-09-30 12:54 225阅读 0赞

一.多表连接的基本查询  
使用一张以上的表做查询就是多表查询  
语法：  
SELECT \{DISTINCT\} \*|列名.. FROM 表名 别名，表名1 别名  
\{WHERE 限制条件 ORDER BY 排序字段 ASC|DESC…\}

SELECT * FROM emp; --14条记录
    SELECT * FROM dept;--4条记录
    SELECT * FROM emp,dept;--显示56条数据？？为什么

分析:两张表在进行查询的时候产生了笛卡尔积,就是两张表数据的乘积,  
例如下面这个产生的笛卡尔积  
![部门表][SouthEast]  
![员工表][SouthEast 1]  
列数相加,行数相乘 就得到了下面这个表  
![笛卡尔积表][SouthEast 2]  
我们会发现有的记录中部门号不同,就说明这一条数据是错误的,不应该出现在结果中.  
多表查询就是从笛卡尔积中选出正确的记录  
当满足这个连接条件:e.deptno = d.deptno的时候这条记录是正确的  
连接条件为:N-1(N是表的个数)  
dept表的结构:  
名称 是否为空? 类型  
DEPTNO NOT NULL NUMBER(2)  
DNAME VARCHAR2(14)  
LOC VARCHAR2(13)  
二.Oracle连接的类型  
1.等值连接

例如:查询员工信息：员工号  姓名  月薪  部门名称
    select e.empno,e.ename,e.sal,d.dname
    from emp e,dept d
    where e.deptno = d.deptno;

2.不等值连接

select * from salgrade;查询出工资的等级
    
         GRADE      LOSAL      HISAL                                     
             1        700       1200                                               
             2       1201       1400                                       
             3       1401       2000                                              
             4       2001       3000                                            
             5       3001       9999

例如:查询员工信息：员工号 姓名 月薪  工资级别
    select e.empno 员工号,e.ename 姓名,e.sal 月薪,s.grade 工资级别
     from emp e,salgrade s
      where e.sal between s.losal and s.hisal;

3.外连接

例如:按部门统计员工信息：部门号 部门名称  人数
    select d.deptno 部门号,d.dname 部门名称,count(e.empno) 人数 from emp e,dept d where e.deptno=d.deptno group by d.deptno,d.dname;

查询的结果为:  
部门号 部门名称 人数  
10 ACCOUNTING 3  
20 RESEARCH 5  
30 SALES 6  
也就是40号部门的人数为0没有显示出来,原因: where e.deptno=d.deptno这个条件直接过滤掉了40号部门的员工.  
我们希望：对于某些不成立的记录（比如40号部门），仍然希望包含在最后的结果中.  
这个时候就需要使用左外连接或者右外连接  
左外连接：当where e.deptno=d.deptno不成立的时候，等号左边的表仍然被包含在最后的结果中  
写法：where e.deptno=d.deptno(+)  
右外连接：当where e.deptno=d.deptno不成立的时候，等号右边的表仍然被包含在最后的结果中  
写法: where e.deptno(+)=d.deptno

所以最后的语句为:
    select d.deptno,d.dname,count(e.ename) from emp e,dept d where e.deptno(+) = d.deptno group by d.deptno, d.dname;

部门号 部门名称             人数                                 
      10 ACCOUNTING              3                                       
        40 OPERATIONS              0                                       
        20 RESEARCH                5                                      
        30 SALES                   6

4.自连接  
通过表的别名，将同一张表视为多张表

例如:查询员工信息： 员工姓名 老板姓名
     select e.ename 员工姓名,b.ename 老板姓名 from emp e,emp b where e.mgr=b.empno;
      select count(*) from emp e,emp b;这样查询出的结果为196条

结论:自连接不适合操作数据较多的表

补充:使用层次查询上面自连接查询操作.  
层次查询不属于多表查询.

![这里写图片描述][SouthEast 3]

这里level列是伪列
    select level,empno,ename,mgr from emp connect by prior empno=mgr start with mgr is null order by 1;

LEVEL EMPNO ENAME MGR  
1 7839 KING  
2 7566 JONES 7839  
2 7698 BLAKE 7839  
2 7782 CLARK 7839  
3 7902 FORD 7566  
3 7521 WARD 7698  
3 7900 JAMES 7698  
3 7934 MILLER 7782  
3 7499 ALLEN 7698  
3 7788 SCOTT 7566  
3 7654 MARTIN 7698  
3 7844 TURNER 7698  
4 7876 ADAMS 7788  
4 7369 SMITH 7902

[SouthEast]: /images/20220705/4a0969b64b6649708da191bfc97005e8.png
[SouthEast 1]: /images/20220705/c02ac91eaef1479b964a65b65242bdaf.png
[SouthEast 2]: /images/20220705/1ccdbdfce91a48aa8fc85a5ca7abcd55.png
[SouthEast 3]: /images/20220705/6d857c00de2c4000b22b5b589f79e1d1.png