数据库 — Oracle_DQL语句（多表关联查询、子查询、分页查询）

一、多表关联查询

1.1 笛卡尔积

多表查询的基础是笛卡尔积，而笛卡尔积是两张表完整的无差别的乘，会产生大量无效数据。

select * from emp, dept; -- 无等值连接的笛卡尔积

而想要通过笛卡尔积获得有效的数据，需要通过等值连接。

1.2 等值连接（内连接）

• 等值连接：通过两张表中的共同字段，来进行匹配筛选。
• 若想展示公共字段，需要在字段前加上表名前缀来区分，唯一字段就不需要（因为不会有重复）

select * 
from emp, dept
where emp.deptno = dept.deptno; -- 等值连接的字段

-- 获得员工和他所在的部门名称
select 
    -- 公共字段必须添加表名前缀，唯一字段可以不加前缀
    emp.deptno, ename, dname
from emp, dept
where emp.deptno = dept.deptno;

• 内连接的展示效果与等值连接一致。

-- 内连接，等同于等值连接
-- 等值连接问题：只能显示两边能匹配上的数据
select 
    emp.deptno, ename, dname
from emp
    inner join dept  -- inner可以省略
    on emp.deptno = dept.deptno;

1.3 左外连接、右外连接、全外连接(MySQL不支持)

• 驱动表：想要展示全部数据的表（null也展示）
• join + 想要连接的表
• on + 连接的条件，永远是多表中共同的字段
• 左外连接，表示在语句左边left的表是全展示的表
• 右外连接，表示在语句右边right的表是全展示的表。（左右外连接都是相对的）

select
    e.empno, e.ename, m.ename manager, e.mgr
from 
    emp e
    left outer join emp m --outer可以省略
    on e.mgr = m.empno
;

练习：

-- 1)查询员工的姓名及其所在部门的名字和城市
select 
    ename, dname, loc
from 
    emp e
    join dept d
    on e.deptno = d.deptno ;-- 两张表之间的共同字段
-- 2)查询员工的姓名和他的管理者的姓名
select  
    e.ename, m.ename
from 
    emp e
    join emp m
    on e.mgr = m.empno
;

-- 3)查询员工的编号、姓名、部门编码、部门名称以及部门所在城市。要求：把没有部门的员工也查出来
select
    empno, ename, e.deptno, dname, loc
from 
    emp e
    left join dept d
    on e.deptno = d.deptno
    ;

-- 4)查询员工的信息及其所在部门的信息。要求：把没有员工的部门也查出来
select
    empno, ename, d.deptno, dname, loc
from 
    emp e
    right join dept d
    on e.deptno = d.deptno
    ;

-- 5)查询员工的信息及其所在部门的信息。要求：只查询没有员工的部门
select
    empno, ename, d.deptno, dname, loc
from 
    emp e
    right join dept d
    on e.deptno = d.deptno
where 
    ename is null
    ;
-- 6)查询并显示SALES部门的职位
select 
    distinct job, dname
from
    emp e
    join dept d
    on e.deptno = d.deptno
where 
    d.dname = 'SALES';
-- 7)查询所有部门的名称、所在地、员工数量以及平均工资
select
    dname, loc, count(e.ename), avg(e.sal)
from 
    emp e
    right join dept d
    on e.deptno = d.deptno
group by  dname, loc
;

二、子查询

• 子查询的核心就是分步操作，然后整合。
• 子查询结果
  • 单记录：一行一列，可用where直接进行比较，相当于返回一个值。
  • 一列：多行记录，可用any、in、all，相当于返回一个集合。
  • 多行多列：相当于返回一张表。

-- 查询所有薪资比ALLEN高的员工姓名和薪资

-- 第一步：查询ALLEN的工资
select sal from emp where ename='ALLEN'; -- 1600

-- 第二步：查询比ALLEN工资高的员工
select 
    ename, sal 
from 
    emp 
where 
    sal > (1600); --'ALLEN'的工资

-- 第三步：将第一步能返回结果（值）的语句，作为参数传入
select 
    ename, sal 
from 
    emp 
where 
    sal > (
        select sal 
        from emp 
        where ename='ALLEN'
    );

• 执行顺序
  • from
  • where
  • group by
  • having
  • select
  • order by，可用别名

三、分页查询

• 分页查询是每个数据库必须存在的功能。
• （page-1）*pageSize + 1 ～ page * pageSize
• rownum是表中原始内容的排序编号，且只能从1开始，因此不能直接使用

select sal, ename, rownum 
from emp
where rownum <= 4; -- 从1开始,可行

select sal, ename, rownum 
from emp
where rownum between 5 and 8; -- rownum从5开始，不可行，报错

• 解决思路，将rownum从不可更改的表属性用别名转换为可变的表字段属性，实现分页查询。

-- 查询工资由高到低，5~8名
-- 1.先排总序
select
    sal, ename
from 
    emp_hh
order by sal desc;

-- 2. 将排完序的表，得出rownum
select
    sal, ename, rownum
from
    (select
            sal, ename
        from 
            emp_hh
        order by sal desc);
        
-- 3. 再将上面的表，作为子表，将子表的rownum转换为新表字段（可从5开始，不从1开始）
select 
    sal,ename, e.n -- rownum属性
from
    (select
        sal, ename, rownum n -- rownum别名
    from
        (select
                sal, ename
            from 
                emp_hh
            order by sal desc)) e -- 子表字段
where e.n BETWEEN 5 and 8; -- 转换完成后，就可已使用该条件