Oracle的高级查询
在实际应用中,我们经常需要在多张表中查询数据或者需要对表中的数据进行分类、汇总等。这就需要较为复杂的高级查询
前提掌握部分{
多个表之间关系:一对多|(多对一) 多对多 一对一 3种
关系的完整性约束:实体完整性、参照完整性、用于定义的完整性。 必须满足实体完整性和参照完整性.
实体完整性:规定了字段|属性的约束
参照完整性:关系与关系之间的引用 某个字段的约束 外键
备注:实体完整性及参照完整性是任何关系数据库必须满足的条件。
用户定义完整性:举例:在学生表中 学生的年龄不能够大于60(用户自定义的条件)
}
以员工表 与部门表为例:(一对多的关联)
部门表:
|
员工表:
|
-- Create table
create table DEPT
(
DEPTNO NUMBER(2) not null,
DNAME VARCHAR2(14),
LOC VARCHAR2(13)
)
tablespace SYSTEM
pctfree 10
pctused 40
initrans 1
maxtrans 255
storage
(
initial 64
minextents 1
maxextents unlimited
);
-- Create/Recreate primary, unique and foreign key constraints
alter table DEPT
add constraint DEPT_PRIMARY_KEY primary key (DEPTNO)
using index
tablespace SYSTEM
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
|
-- Create table
create table EMP
(
EMPNO NUMBER(4) not null,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2) not null
)
tablespace SYSTEM
pctfree 10
pctused 40
initrans 1
maxtrans 255
storage
(
initial 64
minextents 1
maxextents unlimited
);
-- Create/Recreate primary, unique and foreign key constraints
alter table EMP
add constraint EMP_PRIMARY_KEY primary key (EMPNO)
using index
tablespace SYSTEM
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
alter table EMP
add constraint EMP_FOREIGN_KEY foreign key (DEPTNO)
references DEPT (DEPTNO);
alter table EMP
add constraint EMP_SELF_KEY foreign key (MGR)
references EMP (EMPNO)
disable;
|
连接查询
在emp表与dept表之间存在着多对一的关联关系(现实中还有其他的关联),往往我们希望查询出更多信息,这时候我们就要用到连接查询。
//查询员工及部门的详细信息 但是会产生一个笛卡尔积的效果
SQL> select * from emp,dept;
//怎么避免笛卡尔积呢?加入where查询条件 引用关系的比较
SQL> select * from emp ,dept where emp.deptno = dept.deptno;
//别名查询 为表起别名 采用别名查询
SQL> select * from emp e,dept d where e.deptno=d.deptno;
//注意 以下写法是有问题的:ORA-00918: 未明确定义列
SQL> select e.empno,e.ename,deptno,d.dname from emp e,dept d where e.deptno=d.deptno;
备注说明:deptno在两个表中都存在,所以一定要使用前缀区分。
SQL> select e.empno,e.ename,e.deptno,d.dname from emp e,dept d where e.deptno=d.deptno;
综上所述 创建连接查询时应遵循如下规则:
1、 from子句应当包括所有的表名
2、 where子句应定义连接条件 两个表1一个等值条件 三个表 2个等值条件…依次类推。
l 备注:连接 n个表,至少需要 n-1个连接条件。 例如:连接三个表,至少需要两个连接条件。
3、 当列名为多个表共有时,列名必须被限制。
使用join连接查询
语法:
From join_table join_type join_table on join_condition
备注: Join_table连接的表名 join_type连接的类型
Join_type连接类型分类如下:
1、 内连接:
内连接根据所使用的比较方式不同,把内连接分为了:
1) 等值连接:在连接条件中使用等于号(=)运算符比较被连接列的列值,其查询结果中列出被连接表中的所有列,包括其中的重复列。
SQL> select * from emp e inner join dept d on e.deptno = d.deptno;
2) 不等连接:在连接条件使用除等于运算符以外的其它比较运算符比较被连接的列的列值。这些运算符包括>、>=、<=、<、!>、!<和<>。
SQL> select * from emp e inner join dept d on e.deptno>d.deptno;
3) 自然连接:在连接条件中使用等于(=)运算符比较被连接列的列值,但它使用选择列表指出查询结果集合中所包括的列,并删除连接表中的重复列。
SQL> select * from emp natural join dept;
SQL> select e.*,d.dname,d.loc from emp e inner join dept d on e.deptno = d.deptno;
SQL> select d.*,e.ename,e.empno,e.job,e.mgr,e.hiredate,e.sal,e.comm from emp e inner join dept d on e.deptno=d.deptno;
备注:Distinct是去掉重复的行,而自然连接是去掉重复的列。
2、 外连接
内连接的查询结果都是满足连接条件的记录。但是,有时我们也希望输出那些不满足连接条件的记录的信息。比如,我们想知道这个部门中所有员工的情况,也包括没有员工的部门,这时就需要使用外连接。外连接是只限制一张表中的数据必须满足连接条件,而另一张表中的数据可以不满足连接条件的连接方式。3种外连接:
1) 左外连接(LEFT OUTER JOIN)
如果在连接查询中,连接表左端的表中所有的记录都列出来,并且能在右端的表中找到匹配的记录,那么连接成功。如果在右端的表中,没能找到匹配的记录,那么对应的记录是空值(NULL)。这时,查询语句使用关键字 LEFT OUTER JOIN,也就是说,左外连接的含义是限制连接关键字右端的表中的数据必须满足连接条件,而不关左端的表中的数据是否满足连接条件,均输出左端表中的内容。
例如:要查询所有部门的员工信息查询语句为
SQL> select * from dept d left outer join emp e on e.deptno=d.deptno order by d.deptno; 左外连接查询中左端表中的所有记录的信息都得到了保留。
备注:部门表中记录保留,如果部门中没有员工,部门显示 员工记录用null补充。
2)右外连接(RIGHT OUTER JOIN)
右外连接与左外连接类似,只是右端表中的所有元组都列出,限制左端表的数据必须满足连接条件,而不管右端表中的数据是否满足连接条件,均输出表中的内容。
例如:同上例内容,查询语句为
SQL> select * from emp e right outer join dept d on e.deptno=d.deptno order by d.deptno;
右外连接查询中右端表中的所有元组的信息都得到了保留。
3)全外连接(FULL OUTER JOIN)
全外连接查询的特点是左、右两端表中的记录都输出,如果没能找到匹配的记录,就使用NULL来代替。
例如:同左外连接例子内容,查询语句为
SQL> select * from emp e full outer join dept d on e.deptno=d.deptno order by d.deptno;
全外连接查询中所有表中的元组信息都得到了保留。
备注:一定分清:左与右 在join后边的是右
3、 交叉联接
交叉连接即笛卡儿乘积,是指两个关系中所有记录的任意组合。一般情况下,交叉查询是没有实际意义的。
SQL> select * from emp e cross join dept d;
备注:可以添加where子句筛选出有意义的数据。建议不使用。
4、 自连接查询
如果在一个连接查询中,涉及到的两个表都是同一个表,这种查询称为自连接查询。同一张表在FROM字句中多次出现,为了区别该表的每一次出现,需要为表定义一个别名。自连接是一种特殊的内连接,它是指相互连接的表在物理上为同一张表,但可以在逻辑上分为两张表。
例如:要求检索出员工编号为7369的上司的详细信息,查询语句为
SQL> select e1.* from emp e inner join emp e1 on e.mgr=e1.empno where e.empno=7369;
注意:对于连接查询中使用到的 inner outer是可以省略的。但为规范最好不要省略。
提高部分:
SQL> select * from emp e inner join dept d on e.deptno(+)=d.deptno;
SQL> select * from emp e join dept d using(deptno);
l 使用外连接可以查询不满足连接条件的数据。
l 外连接的符号是 (+)。
l 在NATURAL JOIN 子句创建等值连接时,可以使用 USING 子句指定等值连接中需要用到的列。
l 使用 USING 可以在有多个列满足条件时进行选择。
l 不要给选中的列中加上表名前缀或别名。
l NATURAL JOIN 和 USING 子句经常同时使用。
分享到:
相关推荐
总之,Oracle高级查询涵盖了多列子查询、EXISTS和NOT EXISTS运算符以及WITH子句等高级技术,这些都是数据库管理员和开发人员在处理复杂查询时的重要工具。通过熟练掌握这些技能,你可以更有效地管理和操作Oracle...
数据库 增、删、改、查练习题目,多表联查练习及答案详解
以下是对"Oracle高级查询技巧"的详细阐述。 一、子查询 子查询是嵌套在其他SQL语句中的查询,用于获取主查询所需的值。它可以作为SELECT、FROM或WHERE子句的一部分。例如,你可以使用子查询来找到某个部门薪水最高...
2. 高级查询技术:使用Oracle SQL的高级特性,如分析函数、子查询、连接以及数据聚合等。 3. 事务处理:深入理解如何使用Oracle的事务控制语句,如COMMIT、ROLLBACK以及SAVEPOINT,进行有效的事务管理。 4. 锁机制:...
调优是Oracle高级编程的重要部分,涵盖SQL调优、存储调优、内存调优和并发控制等多个方面。SQL调优主要通过优化查询结构、减少全表扫描、使用索引等方式实现。存储调优涉及到表空间、数据块、段和分区等概念,调整...
Day06-Oracle高级查询一.pdf Day07-Oracle高级查询二.pdf Day08-Oracle分页查询和视图.pdf Day09-存储过程.pdf Day10-变量定义和循环控制.pdf Day11-plsql游标和函数.pdf Day12-触发器.pdf Day13-索引.pdf
### Oracle高级复制技术详解 #### 一、Oracle高级复制技术概述 Oracle高级复制技术作为Oracle早期推出的高可用性(HA)解决方案之一,自Oracle8i版本以来就已存在。尽管随着时间的发展,它逐渐被诸如DataGuard、...
oracle笔记高级子查询,oracle中的高级子查询有点难度,不太好理解,有具体的案例代码!
Oracle 高级语法详解 Oracle 高级语法是指在 Oracle 数据库管理系统中使用的高级语法结构,包括事务、函数、存储过程、触发器、异常等。这些语法结构是 Oracle 数据库开发和管理的核心内容,对于数据库开发者和管理...
Oracle高级SQL学习与练习涵盖了数据库编程中的一系列高级主题,旨在帮助数据库开发者和管理员提高解决复杂问题的能力。在Oracle数据库系统中,高级SQL技能是进行高效数据管理、查询优化和复杂数据处理的基础。 1. ...
本教程将详细介绍如何进行Oracle高级复制的配置。 首先,我们需要准备两个运行Oracle数据库的服务器,分别命名为beijing和hebei,每个数据库都有一个名为scott的用户,该用户下有一个包含id和name字段的test表,...
oracle 高级教程-深入学习集合 oracle 高级教程-深入学习集合
Oracle 高级应用列表 Oracle 高级应用列表是 Oracle 数据库管理系统中高级应用的集合,涵盖了 PL/SQL、存储过程、函数、触发器等高级内容。下面是对每个知识点的详细解释: 1. PL/SQL PL/SQL 是 Oracle 数据库...
Oracle高级教程是针对已经对数据库基础有一定了解,希望深入学习Oracle特性和高级功能的IT专业人士设计的。这个教程涵盖了从Oracle的基本概念到实际操作的各个方面,旨在帮助学习者提升数据库管理、开发和优化的能力...
本文将基于"Oracle高级详细资料"的标题和描述,深入探讨Oracle物理内存的分配、数据库碎片整理、回滚段的管理、系统使用经验和性能优化技术。 首先,Oracle物理内存的分配是数据库高效运行的基础。在Oracle 9i版本...
"Oracle高级用法"主要涵盖了数据库的优化、安全管理、备份恢复、性能监控和复杂SQL操作等核心内容。下面我们将深入探讨这些高级主题。 一、优化 Oracle数据库优化主要包括SQL优化和索引优化。SQL优化涉及到编写高效...
oracle_高级查询,使用oracle数据库最终上升到高级用法
精通Oracle SQL【第2版】ORACLE SQL高级编程【第二版】学习笔记
这个名为"Oracle高级数据库大全"的压缩包文件显然包含了丰富的Oracle数据库学习资源和实践案例,旨在帮助用户深入理解并掌握Oracle数据库的高级特性与管理技巧。 首先,Oracle数据库的核心特性包括ACID(原子性、...