近距离接触：oracle sql复杂查询（第1部分）

大家好，关于近距离接触：oracle sql复杂查询（第1部分）很多朋友都还不太明白，今天小编就来为大家分享关于的知识，希望对各位有所帮助！

全表扫描：指针从第一条记录开始，逐行处理，直到最后一条记录结束；

水平选择+垂直投影=结果集

7.2 多表连接

7.2.1 多表连接的优缺点

优势：

1）减少冗余数据意味着优化存储空间，减少IO负担。

2）根据查询需要决定是否需要连接表。

3）灵活添加字段。每个表中的字段相对独立（无主外键约束），可以灵活添加或删除。

缺点：

1）多表join语句可能较长、复杂，可读性较差。

2）可能需要更多的CPU资源，一些复杂的连接算法会消耗CPU和Memory。

3）多表连接查询只能在一个数据库中完成。

7.2.2 多表连接中表之间的对应关系

1）一对一的关系

将表一分为二，最简单的对应关系

2）一对多关系

通过定义主外键约束，使两个表符合第三范式标准的对应关系。

3）多对多关系

多对多关系

非标准对应关系：当两个表存在多对多关系时，通常需要建立中间表。中间表必须至少具有两个表的主键。这样中间表就可以和各个表建立一对多的关系。

7.2.3 多表连接的类型和语法

交叉连接（笛卡尔积）

非等值连接

等价连接（内连接）

外连接（内连接的扩展、左外连接、右外连接、全连接）

自连接

自然连接（内部连接、隐式连接条件、连接字段自动匹配）

复合连接（多个结果集的合并、交集、差异）

7.2.1 交叉连接（笛卡尔积）

连接条件无效或被省略。两个表的所有行都连接起来，所有行的组合将返回（n*m）

SQL99的写法：

SCOTT@ prodselect * 来自员工e 交叉加入部门d；

Oracle的写法：

SCOTT@ prodselect * 来自emp e，dept d；

7.2.2 非等价连接：（连接条件没有“=”符号）

SQL99的写法：

SCOTT@ prod从emp 中选择empno、ename、sal、grade、losal、hisal 在losal 和hisal 之间加入salgrade on sal；

Oracle的写法：

SCOTT@ prodselect empno、ename、sal、grade、losal、hisal from emp、salgrade，其中sal 位于losal 和hisal 之间；

7.2.3 等值连接，典型的内连接

SQL99的写法：

SCOTT@ prodselect e.ename, d.loc from emp e 内连接dept d on e.deptno=d.deptno;

Oracle的写法：

SCOTT@ prodselect e.ename, d.loc from emp e,dept d where e.deptno=d.deptno;

7.2.4 等值连接的using子句（常用）

等值连接的连接字段可以相同，

例如e.deptno=d.deptno;

也可以不一样

例如e.empno=e.mgr

如果连接字段相同，可以使用using子句来简化书写

例如，在e.deptno=d.detpno.替换为using(deptno)

示例：

SCOTT@ prodselect deptno, e.ename, d.loc from emp e 内连接部门d using(deptno);您还可以使用多个列。使用using关键字时需要注意的事项：

1、如果select结果列表项包含using关键字指定的关键字，则不要指定该关键字属于哪个表。

2、using中可以指定多个列名。

3. on和using关键字是互斥的，即它们不能同时出现。

7.2.5 外连接（包括左外连接、右外连接、全外连接）

1) 左外连接语法

SQL99 语法：

SCOTT@ prodselect * from emp e left external join dept d on e.deptno=d.deptno;

Oracle 语法：

SCOTT@ prodselect * from emp e, dept d where e.deptno=d.deptno(+);

2）了解左连接有两个关键点

1.如何确定左表和右表

SQL99写法：由from后面的表的顺序决定，第一个表是左表

SCOTT@ prodselect e.ename, d.loc from emp e left join dept d on e.deptno=d.deptno;

from之后的第一个表是emp表，也就是左表。 “=”的左右位置无关紧要。

Oracle写法：由where后面“=”的位置决定，“=”号的左表为左表

SCOTT@ prodselect e.ename, d.loc from emp e, dept d where e.deptno=d.deptno(+);

“=”左边是emp表，也就是左表。 “from”之后的表格位置并不重要。

2.左侧链接主要根据左侧表格

左连接由左表驱动。每行都参与右表行的匹配。如果匹配则连接成一行。如果没有匹配，则左表行不会缺少连接的行。此时只需将右表内容填入空格即可。

左连接后，左表中的行不缺失，即左连接后结果集中的行数=左表的行数。这可能是因为左表中的一行可能与右表中的多行匹配。

左表和右表也可以是同一张表，即“从左自联”。

3. 哪款手表做主力手表比较好？

没有固定的规则，根据业务需求来决定。

两个表之间的一对多关系一般由主外键确定。外键表是明细表，比如emp和dept的关系。父子关系由deptno决定。 emp 是外键表。

您想要检查每个员工的工作地点。这时候自然就制作出外键表的左表（emp明细表）。

SCOTT@ prodselect e.ename, d.loc from emp e left external join dept d on e.deptno=d.deptno;

你想查看每个部门有多少员工，想根据每个部门编号进行统计（即使没有员工，40号部门也会统计）。这种情况下，使用主键表（dept）来建表是比较合理的。

SCOTT@ prodselect d.deptno, count(e.ename) from emp e, dept d where d.deptno=e.deptno(+) group by d.deptno;

我们来推论一下：

SCOTT@ prodselect d.deptno, e.ename from dept d left external join emp e on e.deptno=d.deptno;

以上三点是个人理解，这套规则也适用于友联。

2) 右外连接

SQL99 语法：

SCOTT@ prodselect * from emp e right join dept d on e.deptno=d.deptno;

Oracle 语法：

SCOTT@ prod select * from emp e, dept d where e.deptno(+)=d.deptno;

3) 全外部连接

SQL99 语法：

SCOTT@ prod select * from emp e full join dept d on e.deptno=d.deptno;

Oracle语法：（无，相当于union连接）

斯科特@产品

select * from emp e, dept d where e.deptno=d.deptno(+)

联盟

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

7.2.6 自连接

SQL99语法：

SCOTT@ prodselect e1.empno,e2.mgr from emp e1 cross join emp e2;

Oracle 语法：

SCOTT@ prod 从emp e1、emp e2 中选择e1.empno、e2.mgr；

注意：必须使用别名来区分不同的表

7.2.6 自然连接（属于内连接和等值连接）

使用关键字natural join，即自然连接。

SCOTT@ prodselect e.ename, d.loc from emp e 自然加入部门d；

如果复合匹配条件有多列，则会发生自动多列匹配。

7.3 复合查询（使用集合运算符）

Union，对两个select结果集进行并集操作，重复的行只取一次，同时按默认规则排序；

Union All，对两个select结果集执行并集操作，包括所有重复行，不排序；

Intersect，对两个select结果集进行交集操作，重复行只取一次，同时按默认规则排序；

减，对两个select结果集进行差异操作，不提取重复行，同时按默认规则排序。

复合查询操作包括并集、交集和差集运算符。

例子：

SQL 创建表dept1 as select * from dept where rownum=1;

SQL插入dept1值(80,’MARKTING’,’BEIJING’);

SQL 从部门中选择*；

部门号DNAME 位置

———- ————– ————-

10 纽约会计

20 达拉斯研究中心

30 销售芝加哥

40 个行动波士顿

SQL 从部门1选择*；

部门名称定位

———- ————– ————-

10 纽约会计

80 营销北京

1）工会

SQL

从部门中选择*

联盟

从部门1中选择*；

部门名称定位

———- ————– ————-

10 纽约会计

20 达拉斯研究中心

30 销售芝加哥

40 个行动波士顿

80 营销北京

2) 联合所有

SQL

从部门中选择*

联合所有

从部门1中选择*；

部门名称定位

———- ————– ————-

10 纽约会计

20 达拉斯研究中心

30 销售芝加哥

40 个行动波士顿

10 纽约会计

80 营销北京

特别说明：可以看到只有union all的结果集没有排序。

3）相交

SQL

从部门中选择*

相交

从部门1中选择*；

部门名称定位

———- ————– ————-

10 纽约会计

4）减去（注意谁减去谁）

SQL

从部门中选择*

减

从部门1中选择*；

部门名称定位

———- ————– ————-

20 达拉斯研究中心

30 销售芝加哥

40 个行动波士顿

SQL

从部门1中选择*

减

从部门中选择*；

部门名称定位

———- ————– ————-

80 营销北京

7.4 关于复合查询的一些注意事项

1）列名不必相同，但类型必须匹配，顺序必须对应。可以匹配大类型，例如char 与varchar2、date 与timestamp。字段数量必须相同。如果它们不相等，则需要完成它们。

创建表a(id_a int,name_a char(10));

创建表b(id_b int,name_b char(10),sal number(10,2));

插入一个values(1,’sohu’);

插入一个values(2, ‘sina’);

插入b值(1,’sohu’,1000);

插入b值(2, ‘yahoo’, 2000);

犯罪；

SQL从a中选择*；

ID_A NAME_A

———- ———-

1 搜狐

2 新浪

SQL从b中选择*；

ID_B NAME_B SAL

———- ———- ———-

1 搜狐1000

2 雅虎2000

SQL

从a 中选择id_a,name_a

联盟

从b 中选择id_b,name_b;

2）设置的四个操作符优先级按照出现的顺序执行。如果有特殊要求，可以使用（）。

3）关于复合查询中使用别名排序的顺序问题：

默认情况下，复合查询后的结果集按所有字段的组合隐式排序（union all除外）

如果您不想使用默认排序，也可以使用order by 显式排序。

SQL select id_a, name_a name from a

联盟

从b 中选择id_b, name_b 名称

按姓名排序；

SQL

从a 中选择id_a, name_a

联盟

从b 中选择id_b、name_b

按2 排序；

显式order by指的是第一个select语句的列元素。因此order by后的列名只能是第一次select使用的列名、别名和列号。如果补全的空值需要order by，则需要使用别名。

SQL

从a 中选择id_a, name_a 名称，to_number(null)

联盟

从b 中选择id_b、name_b 名称、sal

按萨尔订购；

错误：ORA-00904: ‘SAL’: 标识符无效

以下三种写法都是正确的：

SQL

从a 中选择id_a, name_a 名称，to_number(null)

联盟

从b 中选择id_b、name_b 名称、sal

按3 排序；

SQL

从b 中选择id_b、name_b 名称、sal

联盟

从a 中选择id_a, name_a 名称，to_number(null)

按萨尔订购；

SQL

从a 中选择id_a, name_a 名称，to_number(null) aa

联盟

从b 中选择id_b、name_b 名称、sal aa

按aa 订购；

排序是对复合查询结果集进行排序。各个表不能单独排序。 order by 只能使用一次，出现在最后一行；

SQL

从id_a 的订单中选择id_a、name_a

联盟

从b order by id_b 中选择id_b、name_b；

错误：ORA-00933: SQL 命令未正确结束

用户评论

站上冰箱当高冷

这个标题吸引我啊！我一直对SQL复杂的查询感到头疼，希望这篇文章能给我一些实用的技巧和方法。

    有12位网友表示赞同！

哭花了素颜

Oracle SQL 的查询确实让人摸不着头脑，感觉官方文档也是乱七八糟的。期待看到作者带来的直观讲解和案例分析。

    有8位网友表示赞同！

哥帅但不是蟋蟀

终于有人来解决这个难题了！很久以前就听说过复杂的SQL查询在面试的时候会考察，我一直觉得这门技术好难啊。

    有16位网友表示赞同！

各自安好ぃ

亲密接触？听起来有点像“深度挖掘”一样，我很期待作者能深入浅出地讲解这些复杂查询的原理和使用方法。也许这篇博客能揭开我心中的这个谜团！

    有13位网友表示赞同！

素婉纤尘

我对Oracle SQL不太了解，希望这篇文章能够从基础开始解释，这样对于初学者更加友好。

    有6位网友表示赞同！

微信名字

我只想知道如何快速的写出一个高效的SQL查询语句！ 复杂查询听起来就很吓人，如果能带来一些实用的捷径，那真是太棒了!

    有19位网友表示赞同！

铁树不曾开花

我觉得这篇博客应该多用些图片和图表来辅助讲解，这样更直观易懂，不会像阅读官方文档那么枯燥乏味。

    有14位网友表示赞同！

断秋风

希望这篇文章不仅仅停留在理论层面，还可以提供一些实际的案例分析，以及如何针对不同场景优化查询语句的技巧。

    有16位网友表示赞同！

風景綫つ

我最看重的是效率问题，复杂查询会导致数据库性能下降吗？希望能有关于如何提高查询效率的探讨。

    有16位网友表示赞同！

七夏i

oracle SQL 确实很强大，但也有它的局限性，像一些非常复杂的查询，还是需要借助其他工具辅助进行编写吧。

    有12位网友表示赞同！

晨与橙与城

我比较感兴趣的是文章中提到的“亲密接触”，这究竟指的是什么？作者能否详细说明一下呢？

    有18位网友表示赞同！

花花世界总是那么虚伪﹌

看标题感觉很有深度，希望作者能将复杂的概念解释得清晰易懂，而不是像很多博客那样深奥难以理解。

    有16位网友表示赞同！

十言i

这篇博文应该是在讲如何通过一些特殊的语法和技巧来实现复杂的SQL查询吧。我很期待看到这些“高级技巧”！

    有14位网友表示赞同！

男神大妈

说实话，我对Oracle SQL的配置不太了解，只能说这个标题听起来很有科技感，但具体内容还没法判断。

    有17位网友表示赞同！

拥菢过后只剰凄凉

我已经看完了这篇文章（上）了，感觉不错，希望能继续更新到（下），让我能更深入地了解Oracle SQL 复杂查询的技巧！

    有12位网友表示赞同！

回到你身边

我觉得这篇文章对于想要进一步学习 Oracle SQL 的读者来说非常实用，尤其针对那些需要编写复杂查询语句的人来说，确实受益良多。

    有19位网友表示赞同！