Oracle多表查询，子查询，分页查询-阿里云开发者社区

一、多表查询

1、多表连接基本查询

定义：使用一张以上的表做查询就是多表查询，如果要查询的内容来自于2张或是N张表，这种情况下需要使用表连接，将2张或是N张表合并成一张大表。

合并后的结果：

1. 表结构(字段数)：2个表的字段之和；
2. 数据行数：取决于 表连接的类型。
3. 表连接后，有冲突的字段时，需要  表名.字段名  或是  表别名.字段名  加以区分

表连接的类型：内连接、外连接

语法：

SELECT {DISTINCT} *|列名.. 
FROM 表名 别名，表名 1 别名
{WHERE 限制条件 ORDER BY 排序字段 ASC|DESC...}

范例：查询员工表和部门表

1.   --范例：查询员工表和部门表
2.   select * from emp,dept;

1 (1).png

我们发现产生的记录数是 56 条，我们还会发现 emp 表是 14 条，dept 表是 4 条，56 正是 emp表和 dept 表的记录数的乘积，我们称其为笛卡尔积。如果多张表进行一起查询而且每张表的数据很大的话笛卡尔积就会变得非常大，对性能造成影响，想要去掉笛卡尔积我们需要关联查询。在两张表中我们发现有一个共同的字段是 depno，depno 就是两张表的关联的字段，我们可以使用这个字段来做限制条件，两张表的关联查询字段一般是其中一张表的主键，另一张表的外键。

  select * from emp,dept where emp.deptno=dept.deptno;

1 (2).png

关联之后我们发现数据条数是 14 条，不在是 56 条。

多表查询我们可以为每一张表起一个别名

  select * from emp e,dept d where e.deptno=d.deptno;

范例：查询出雇员的编号，姓名，部门的编号和名称，地址

  --范例：查询出雇员的编号，姓名，部门的编号和名称，地址
  select e.empno,e.ename,d.deptno,d.dname,d.lo
  c from emp e,dept d 
  where e.deptno=d.deptno;

1 (3).png

范例：查询出每个员工的上级领导

  --范例：查询出每个员工的上级领导
  --分析：emp 表中的 mgr字段是当前雇员的上级领导的编号，所以该字段对 emp表产生了自身关联，
  --可以使用 mgr 字段和 empno 来关联
  select e1.ename,e1.empno,e2.ename,e2.empno
   from emp e1 ,emp e2
    where e1.mgr=e2.empno;

1 (4).png

分析：emp 表中的 mgr字段是当前雇员的上级领导的编号，所以该字段对 emp表产生了自身关联，

可以使用 mgr 字段和 empno 来关联

范例:查询出每个员工的上级领导的基础上查询该员工的部门名称

 --查询出每个员工的上级领导的基础上查询该员工的部门名称
    select e1.empno,e1.ename,e2.ename as 领导 ,d.dname 
    from emp e1,emp e2,dept d
    where e1.mgr=e2.empno
    and e1.deptno=d.deptno;

1 (5).png

范例：查询出每个员工编号，姓名，部门名称，工资等级和他的上级领导的姓名，工资等级

1 (6).png

1 (7).png

  --工资等级
    select * from salgrade;
   --查询出每个员工编号，姓名，部门名称，工资等级和他的上级领导的姓名，工资等级
 select e.empno,
 e.ename,
 decode(s.grade,    
 1,'一级',
 2,'二级',
 3,'三级',
 4,'四级',
 5,'五级') grade,
 d.dname,
 e1.empno,
 e1.ename,
 decode(s1.grade,
 1,'一级',
 2,'二级',
 3,'三级',
 4,'四级',
 5,'五级') grade
 from emp e, emp e1, dept d, salgrade s, salgrade s1
where e.mgr = e1.empno
 and e.deptno = d.deptno
 and e.sal between s.losal and s.hisal
 and e1.sal between s1.losal and s1.hisal

2、外连接

外连接分类：

左外连接 left [outer]join 
右外连接 right [outer] 
全部外连接 full join

（1）左外连接

语法：

1. select...from 表名1 left [outer] join 表名2 on 连接条件
2.      where ... group by ... having...order by..

左外连接的特点：必须有连接条件以左表为主，左表和右表匹配上的数据+左表中没有匹配上的数据两个表有顺序要求：以左表为主，右表为辅左表中没有匹配上的数据在显示时，右表字段补空

查询员工的工号、姓、收入、所在部门编号、所在的部门名称 -- 左外连接

--查询员工的工号、姓、收入、所在部门编号、所在的部门名称  -- 左外连接
select e.empno,e.ename,e.sal,e.deptno,d.dname 
from emp e
 left join dept d 
 on e.deptno=d.deptno

1 (8).png

（2）右外连接

a. 语法：

select...from 表名1 right [outer] join 表名2 on 连接条件

b. 特点： I. 必须有连接条件 II. 最终的数据：左表+右表满足连接条件，同时右表没有符合连接条件的数据 III. 右外连接：以右表为主，只要右表中出现的数据，在结果中一定保留

（3）满连接

a. 语法：

select...from 表名1 full [outer] join 表名2 on 连接条件

b. 特点： i. 必须有连接条件 ii. 最终的数据：左表+右表符合连接条件的数据+右表没有符合条件+左表没有符合条件的数据 iii. 对表的位置没有左右要求

（4）多表连接

a.语法：

select...from 表名1 join 表名2 on 连接条件1 join 表名3 on 连接条件2

.....

b. 案例：

查询员工的工号、姓、部门名称、所在位置。

  select employee_id,last_name,department_name,city from 
      employees e join departments d on e.department_id=d.department_id
      join locations t on d.location_id = t.location_id

二、子查询

子查询：在一个查询的内部还包括另一个查询，则此查询称为子查询。Sql的任何位置都可以加入子查询，即：在一个select语句中嵌套了另一个select语句

写在外面的select通常被称为主查询
写在里面的select通常被称为子查询

1、子查询的第一种情况：子查询的结果为单个值(一行一列)

特点：直接将子查询的结果作为主查询的过滤条件

案例1：查询工资最高的员工信息(工号、名、工资)

-- 案例1：查询工资最高的员工信息(工号、名、工资)

select empno,ename,sal from emp where sal=(select max(sal) from emp);

1 (9).png

案例2：查询工资大于平均工资员工信息

1. select empno,ename,sal from emp where sal>(select avg(sal) from emp);
2.

1 (10).png

2、子查询的第二种情况：子查询的结果为多个值(n行1列)

特点：子查询多个数据，将多个数据看成一个结果集，直接可以参与主查询的

in(值1,值)枚举运算，注意：in中多个值来源于子查询的结果

案例：查询和姓'King' 同部门的员工信息

--案例：查询 和姓'King' 同部门的员工信息
select  empno,ename,sal
 from emp where
  deptno=(select deptno from emp where ename='KING');

1 (11).png

3、子查询的第三种情况：子查询结果为多行多列

特点：子查询的结果为多行多列(N行n列)，看做一张表，再次参与主查询的查询

案例：查询工资最高的前5名员工。

--案例：查询工资最高的前5名员工。
select * from  
(select  empno,ename,sal from emp  order by sal desc)
 where rownum<=5;

1 (12).png

案例：查询工资最高的前3~5名员工。

 select tab2.* from (
 select tab1.*,rownum r from (select  empno,ename,sal from emp  order by sal desc) tab1 where rownum<=5) tab2 
 where  r>=3;

1 (13).png

4、子查询解题思路

注意：子查询的多行多列情况通常用于分页查询

sql的思路：查询最高/低 m~m条数据

有排序，先排序，将排序的结果看作一个新的表进行二次查询 -->tab1

在tab1中获取查询数据上限：rownum<=n ，同时对rownum起别名rn --> tab2

在tab2中：rn>=m

---》合成sql

三、伪列

伪列：虚列，在表中不存在此列(定义时没有指定)，只有在查询时才显示。

1、rowid 【了解】

rowid：当前数据在硬盘中的物理地址计算的结果，唯一

应用：rowid通常应用在oracle的内部；在应用层通常使用id(主键)标识数据的唯

一性

2、rownum

rownum：对满足条件的查询结果进行自动编号，默认从1开始，1..2..3....

案例：查询薪资大于10000的员工信息：工号、名字、薪资、序号(rownum)

select rownum,empno,ename,sal from emp;

1 (14).png

查询员工表中，前10个员工的信息(工号，姓名，薪资，序号)

1.  --查询员工表中，前10个员工的信息(工号，姓名，薪资，序号)
2.  select rownum,empno,ename,sal from emp where rownum<=10;

1 (15).png

注意：rownum只能参与 < <= =1 >=1 <=1运算 rownum不能参与 >n >=n = n运算注意：n代表大于1的数据

案例：查询员工表中，6~10编号的员工信息

1.  --查询员工表中，前10个员工的信息(工号，姓名，薪资，序号)
2.  select rownum,empno,ename,sal from emp where rownum>=6 and rownum<=10;

1 (16).png

错误的结果：没有任何数据，分析原因：rownum不能参与>=运算

四、分页查询

案例1：查询工资排第6~10的员工信息。

--步骤1:先按工资降序排列。
select * from emp order by sal desc;
--步骤2：查询工资排前 10 的员工，同时对 rownum起别名
select tab1.*,rownum rn from (select * from emp order by sal desc) tab1 where rownum<=10;
--步骤3：查询 rn >= 6 的员工
select tab2.* from (select tab1.*,rownum rn from 
(select * from emp order by sal desc) tab1 where rownum<=10) tab2 
where rn>=6;
--步骤4：合成SQL
 select tab2.* from (select tab1.*,rownum rn from 
 (select * from emp order by sal desc) tab1 where rownum<=10) tab2 
 where rn>=6;

1 (17).png

分页查询的关键点：

分页时，有排序，排序必须先行，构成虚表后进行2次查询查询符合条件的前n行数据，并给rownum起别名 rn 查 rn >= m 的数据

Oracle多表查询，子查询，分页查询

一、多表查询

二、子查询

三、伪列

四、分页查询

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像