首页 > 代码库 > oracle02--多表关联查询

oracle02--多表关联查询

1. 多表(关联)查询

多表查询也称之为关联查询、多表关联查询等,主要是指通过多个表的关联来获取数据的一种方式。

1.1. 多表映射关系

技术分享

一对多:A表的一行数据,对应B表中的多条。:一个部门可以对应多个员工.

多对一:B表中的多条对应A表的一行数据.:多个员工对应一个部门.

多对多:学生和选修课表----学生和课程对应表

技术分享

一对一:人员基本信息和人员信息扩展表。

技术分享

1.2. 笛卡尔集

技术分享

笛卡尔集对于我们数据库的数据查询结果的影响:

1. 数据冗余。---笛卡尔集并不是我们所需要的数据.

2. 效率问题:导致数量级的增长100w *100w====1w亿。如果你在查询大量数据的时候,不注意这个笛卡尔集的话,会导致你的查询结果时间非常非常非常长,还会导致数据库故障。

因此,在实际运行环境下,应避免使用全笛卡尔集

笛卡尔集产生的条件:

l 省略连接条件

l 连接条件无效

如下示例:

技术分享

语句一结果3,语句二结果2

语句三,无论是加无效连接条件,还是不加条件,结果都是2*3=6,这是由于笛卡尔积的问题,所以要使用有效的连接条件;

如何避免笛卡尔集:

WHERE 加入有效的连接条件。

这时候就需要学习表关联的几种方式了。

1.3. 多表连接的类型

根据连接方式的不同,Oracle的多表关联的类型分为:

内连接、外连接、自连接。

内连接分为:等值内连接、不等值内连接

外连接分为:左外连接、右外连接、全外连接

自连接是一种特殊的关联,可以包含内连接和外连接的连接方式。

1.4. 关于sql99-了解

Oracle是关系型数据库,它遵的规范(sql规范)。

但是,mysqlOracle有些地方不一样,原因:各个厂商的实现可能会有差别。

技术分享

Sql99 是为了 统一规范多个关系型数据库的通用语法的

 

1.5. 多表连接的基本语法

Sql99的语法:

技术分享

Oracle的语法:

技术分享

技术分享

sql语句 优化:

加上前缀:效率高!

 

1.6. 内连接

技术分享

1.6.1. 等值内连接

等值内连接也称之为等值连接。

--需求:查询一下员工信息,并且显示其部门名称
SELECT * FROM emp t1,dept t2 WHERE t1.deptno=t2.deptno;
--等值内连接,数据库的私有扩展语法:隐式内连接(mysql,oracle都支持)

SELECT * FROM emp t1 INNER JOIN dept t2 ON t1.deptno=t2.deptno;
--sql99语法,显示内连接(所有符合sql99规范的都支持)
注意:INNER 可以省略

1.6.2. 不等值内连接

不等值内连接也称之为不等值连接。

--分析:要完成这个需求,需要使用到下面两张表:
--需求:查询员工信息,要求显示员工的编号、姓名、月薪、工资级别。
SELECT * FROM emp t1,salgrade t2 WHERE t1.sal >=t2.losal AND t1.sal<=t2.hisal; --隐式语法

SELECT * FROM emp INNER JOIN salgrade ON emp.sal >=salgrade.losal AND emp.sal <=salgrade.hisal --sql99

1.6.3. 表的别名

为什么要使用表的别名?

l 使用别名可以简化查询。

使用表名前缀可以提高执行效率。--SQL性能优化方案

在不同表中具有相同列名的列,可以用表的别名作为前缀来加以区分。

技术分享

需要注意的是,如果一旦使用了表的别名,则不能再使用表的真名。

 技术分享

1.6.4. 更多表的连接

技术分享

1.7. 外连接

分为左外连接,外连接,全外连接(oracle特有 mysql没有)

技术分享

注意:单表查询与多表查询的区别:单表查询只显示一个表中的数据,而多表查询一般会显示多个表中的数据

至于显示是全部字段还是部分字段,决定于select后面查询的内容;

就像左外链接查询和只查询左表的区别:左外链接显示的是两表内容,而查询左表值显示左表内容

1.7.1. 左外连接

--查询"所有"员工信息,要求显示员工号,姓名 ,和部门名称--要求使用左外连接
SELECT * FROM emp t1 LEFT OUTER JOIN dept t2 ON t1.deptno=t2.deptno; --sql99标准语法

SELECT * FROM emp t1,dept t2 WHERE t1.deptno=t2.deptno(+);
--oracle私有语法(mysql不支持),+放到右边是左外,你可以认为(+)是附加补充的意思。
--要求查询所有的信息的表,我们可以称之为主表,而补充信息的表,称之为从表

注意:外连接的outer可以省略(包括左,右,全连接);

1.7.2. 右外连接

技术分享

---查询所有部门及其下属的员工的信息。--右外连接
SELECT * FROM emp t1 RIGHT OUTER JOIN dept t2 ON t1.deptno=t2.deptno;
--sql99--右外连接--右边表(dept)数据全部显示。

SELECT  * FROM emp t1,dept t2 WHERE t1.deptno(+)=t2.deptno;--oracle语法,右外连接

1.7.3. 如何选择左外和右外

 技术分享

--1.到底是使用左外还是右外,主要是看两张表的在语句中的位置,
--两张表是有主从关系,一般把主表放在左边,----一般两张表的情况下,我们都使用左连接.
SELECT t1.*,t2.* FROM dept t1 ,emp t2 WHERE t1.deptno=t2.deptno(+);

--2.+到底是放在条件哪边?左外连接的+放在右边,右外连接的+放在左边.
----记忆的方法:(+)放在从表的一方,起到数据附加的作用.

简单的说:左外连接就是左边的表的数据全部显示,右外就是右边的表的数据全部显示。

注意:这种(+的写法,只能用在Oracle。不能用于mysql

一定要有主表和从表这个概念,分清那张是主表,哪张是从表。

把你想查询基础表当成左表。想把谁全部都查询出来就当成主表

到底哪张是主表哪张是从表?最终还看你的需求。

一般我们把主表放在左边,使用左外连接。

一般情况下,我们就用左连接就行了。

1.7.4. 全外连接

 技术分享

左表和右表的数据全部都显示,而且不是笛卡尔集。相当于左外+右外的数据。

【示例】

需求:要求将所有员工和所有部门都显示出来

--全外连接
SELECT * FROM emp t1 LEFT OUTER JOIN dept t2 on t1.deptno=t2.deptno
UNION   -- union 去重
SELECT * FROM emp t1 RIGHT JOIN dept t2 ON t1.deptno=t2.deptno;

SELECT empno, ename ,dname FROM emp LEFT JOIN dept ON emp.deptno= dept.deptno
UNION ALL  --union all 不去重
SELECT empno, ename ,dname FROM emp right JOIN dept ON emp.deptno= dept.deptno; --sql99

SELECT * FROM emp t1 FULL OUTER JOIN dept t2 ON t1.deptno=t2.deptno;  -- full 去重
--sql99语法,Oracle没有私有扩展的语法。而且,mysql没有全外(mysql只能使用前两种方式,没有第三种方式)

1.8. 自连接

自连接,就是将一张表当成两张表来查询。

1.8.1. 示例

技术分享

自连接的查询的原理:就是将一张表当成两张表来使用.

【示例】

1.查询员工信息,要求同时显示员工和员工的领导的姓名

2.查询“所有”员工信息,要求同时显示员工和员工的领导的姓名

--查询员工信息,要求同时显示员工和员工的领导的姓名
SELECT *  FROM emp t1,emp t2 WHERE t1.mgr=t2.empno;
--查询“所有”员工信息,要求同时显示员工和员工的领导的姓名
SELECT *  FROM emp t1,emp t2 WHERE t1.mgr=t2.empno(+);

实质:自连接实质上也是一种特殊的多表连接方式,可以使用内连接和外连接的操作.

注意问题:你也要注意笛卡尔集的产生.

技术分享

 

oracle02--多表关联查询