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

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

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

分析:两张表在进行查询的时候产生了笛卡尔积,就是两张表数据的乘积,
例如下面这个产生的笛卡尔积

列数相加,行数相乘就得到了下面这个表

我们会发现有的记录中部门号不同,就说明这一条数据是错误的,不应该出现在结果中.
多表查询就是从笛卡尔积中选出正确的记录
当满足这个连接条件: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条

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

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

这里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

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

猜你在找的Oracle相关文章