零点起飞学Oracle之查询数据表

1、第3章 查询数据表查询数据表对数据表来说是最频繁的一种操作。Oracle中的SQL查询语法，一方面遵循了SQL标准，另一方面又有自己的独特之处。本章将详细讲述Oralce中的SQL查询语句，其主要内容包括，基本查询、子查询、联合语句、关联语句、层次化查询。13.1 基本查询方式所谓查询就是让数据库服务器根据客户端的要求搜寻出用户所需要的信息资料，并按用户规定的格式进行整理后返回给客户端。使用select子句和from子句的查询是简单查询。select子句和from子句是查询语句的必选项。复杂查询语句，都是由最简单的基本查询组成。本节将重点讲述示例数据库脚本的下载及安装，Oracle中的基本查询

2、语句。23.1.1 安装示例数据库脚本在安装Oracle 11g时，我们没有安装数据库示例。如果我们在创建实例时没有选择安装示例，我们可以通过官网下载并安装Oracle 11g R2数据库示例用户。在浏览器地址栏中输入Oracle官方网站网址，进入Oracle网站。可供选择的下载地址，。33.1.2 查询关键词selectselect语句主要用于查询语句。在select关键字后面放置的是要在查询结果中显示的列名，这些列名源于from后面的表。如果想查询表中的全部数据，可以在select后面使用“*”代表对应表的所有列。无论查询语句多么复杂，最外层的select命令总是最后执行。数据查询sele

3、ct的基本语法如下。select 列名 from 表名;【示例3-1】查询一个简单的查询员工表employees中所有员工姓名。43.1.3 带条件的查询where子句一个数据表中存放着大量相关的记录数据。在实际应用中，往往只需要其中满足要求的部分记录。这时，就需要用到where条件子句。where子句用于限定from子句所指定的数据源或者各数据源进行运算之后形成的结果集合。【示例3-2】对于职员信息表employees，查询DEPARTMENT_ID是“90”的职员信息。【示例3-3】本例是from子句的复杂情况，查询每位员工所在部门的具体信息。53.1.4 去除查询结果中的重复记录dist

4、inct在查询的过程中，有时候从表中搜索到的所有行的数据，会有数据重复的情况，这是我们不希望看到的。使用distinct关键字 ，就可以从返回的结果中删除重复的行，使返回的结果更简洁。【示例3-4】查询jobs表中，员工的最低工资分布情况，查询时我们会发现重复记录。为了剔除其中的重复记录，可以利用distinct关键字。63.1.5 分组group by子句查询结果中，我们可以使用group by子句对指定的列进行分组，即是将相同的列聚集在一起，该子句一般是放在where子句的后面。【示例3-5】从表employees与表departmens中查询各个部门的员工总数，分别显示部门号、部门名字、

5、以及总员工数的信息。73.1.6 过滤分组having子句having子句通常与group by子句是一起使用的，在完成对分组结果统计后，可以使用having子句对分组的结果做进一步的筛选。having子句与where子句的相似之处都是定义搜索条件。为了将分组按照一定条件进行过滤，应该使用having子句。【示例3-6】having子句是依附于group by子句存在而存在。在示例3-5中，查询部门人数多余10个人的部门信息记录。83.1.7 排序order by子句order by子句是可以对查询结果进行排序，可以规定升序（从低到高）或降序（从高到低），方法是使用ASC（升序）或DESC（降

6、序）。如果省略ASC或DESC，系统则默认是升序。可以在order by组合中指定多个列，查询结果首先按第一列进行排序，对第一列列值相同的那些数据行，再按照第2列进行排序，依次类推。order by子句一般写在where子句的后面。【示例3-7】在jobs表中查询员工的工资分布情况，查询结果中可以看到，没有指定排序规则的情况下，搜寻结果是杂乱无章的。可以对该搜寻结果按照工资水平由低到高的顺序排列。93.1.8 order by与group by子句在数据库查询中，同时存在order by与group by子句时，Oracle首先执行group by子句，然后才进行排序操作。分组是指将数据表中所

7、有记录中，以某个或者某些列为标准，划分为一组。进行分组查询应该使用group by子句。group by子句指定分组标准，并将数据源按照该标准进行划分，然后循环处理每组数据。再使用order by子句用于将分组的结果进行排序。【示例3-8】在示例3-5中，从表employees与表departmens中查询时我们可以获得各个部门的员工总数，以及部门号、部门名字、以及总员工数的信息。查询之后，可以按照各个部门的员工总数进行升序排序。103.1.9 order by与distinctorder by子句与distinct关键字同时使用时，需要注意的是，如果指定了SELECT DISTINCT，那么

8、ORDER BY 子句中的项就必须出现在选择列表中。【示例3-9】我们可以通过表employees获得员工的工资状况。查询时显示员工姓名，以及员工的工资，最后并按工资进行升序排列。113.2 子查询子查询是一般嵌套在select、insert、update、delete语句中，或者还可以嵌套在其他子查询中。任何允许使用表达式的地方都可以使用子查询。子查询也叫做内部查询或内部选择，而包含子查询的语句可以称为外部查询或者外部选择。子查询本质上是一种嵌入的select语句，由于子查询是在外部查询返回的结果集上执行的，因此它的效率肯定是有所下降的。一般情况下，子查询可用于查询条件。123.2 子查询【

9、示例3-10】例如，departments表中列出了所有部门信息，但是目前有的部门有员工，有的部门目前还没有员工。利用表employees、departments查询部门中有员工的部门信息中存储了员工信息。133.3 联合语句如果有不同的查询结果数据集，但又希望将它们按照一定的关系连接在一起，组成一组数据，这就可以使用集合运算来实现。在Oracle数据库中，PL/SQL提供的运算符有，union、union all、intersect和minus。这些集合运算都是二元运算，运算结果仍然是一个记录集合。最基本的运算是并、交和差运算。143.3.1 求并集在PL/SQL中，可以使用union运算符

10、实现集合并的运算。使用union运算符可以将两个或更多查询的结果合并为单个结果集，该结果集包含联合查询中的所有查询的全部行。union运算不同于使用连接合并两个表中的列的计算。153.3.1 求并集其基本的语法如下所示。select commandunion | union allselect command .其中，select command表示查询表达式，用于返回与另一个查询规范或查询表达式所返回的数据合并的数据；union指定合并多个结果集并将其作为单个结果集返回；ALL将全部行并入结果中，其中包括重复行，如果未指定该参数，则删除重复行。163.3.1 求并集使用union合并两个查询

11、结果集，必须满足的条件如下所示。在用union运算符组合的语句中，所有选择列表中的表达式数目必须相同。用union组合的结果集中的对应列，或者各个查询中所使用的列都必须具有相同的数据类型。用union运算组合的各语句中对应结果集中列的顺序必须相同，因为union运算符按照各个查询中给定的顺序一对一地比较各列。【示例10-13】数据库中存在着两个表employees和employeenew，分别存储了参加了公司已有员工和新加入的员工信息。173.3.2 求并集union all运算与union运算都是并集运算。但是union all只是将两个运算结果集进行简单整合，并不剔除其中的重复数据。这是与

12、union运算的最大区别。【示例3-14】数据库中存在着两个表employees和employeenew，查询表中的的员工的名字、以及上级编号。可以利用union all代替union来执行SQL语句，并不去掉重复记录。183.3.3 求交集使用intersect运算可以实现集合交集运算，返回intersect操作符左右两边的两个查询都返回的非重复值。【示例3-15】查询两个员工表employees和employeenew属于同一上级的并且同名的的员工姓名和上级编号信息。193.3.4 求差集minus运算符可以实现集合差的运算，即是从左查询中返回右查询没有找到的所有非重复值。【示例3-16】

13、使用minus运算符实现集合差运算。203.3.5 混合运算对于这四种集合运算union运算、uion all运算、intersect运算和minus运算，Oracle允许进行混合运算。在混合运算时，这四种运算的优先级是相同的，也就是说，他们将按照自左至右的顺序依次进行。【示例3-17】下面演示intersect和union all的混合运算。【示例3-18】我们可以首先将表employees与表employeenew进行一次并集运算，然后获得employees表与该并集的差集。213.4 连接查询在实际的操作中，往往需要同时从两个或两个以上的表中想查询相关数据，连接就是满足这些需求的技术。通

14、过连接，可以根据各个表之间的逻辑关系从两个或多个表中检索数据。Oracle中提供的表的连接有自然连接、内部连接、外部连接、交叉连接。223.4.1 自然连接查询自然连接是一种比较特殊的等值连接，它将表中具有相同名称的列自动进行记录匹配。自然连接自动判断相同名称的列，而后形成匹配。连接后相同的列只保留一个。【示例3-19】表employees和表jobs都包含了job_id列。二者可以进行自然连接操作。233.4.1 自然连接查询自然连接实际自动指定了搜寻条件。这里包括两方面的内容：首先，自然连接列必须同名，另外，所有同名列都将作为搜寻条件。自然连接与笛卡尔积的区别在于，笛卡尔积无论何时，都将获

15、得两个数据表中所有记录的两两组合。而当两个数据表中存在同名列，Oracle将同名列作为搜寻条件。相当于强制添加了条件where t1.column = t2.column。但是，当两个表不存在同名列时，Oracle不会添加任何搜寻条件，其效果相当于笛卡尔积。例如，表departments与表jobs不存在同名列，二者进行自然连接获得的结果集与笛卡尔积相同。243.4.2 内连接查询内连接是一种常用的多表查询，一般用关键字inner join。其中，inner可以省略，而只使用join关键字表示内连接。使用内连接查询多个表时，在from子句中需要定义一个on子句。【示例3-20】使用内连接查询员

16、工信息和员工所在的部门名称,并要求job_id的值是“ST_CLERK”。253.4.3 外连接查询使用内连接进行多表查询时，返回的查询结果集中仅包含符合查询条件和连接条件的行。内连接消除了与另一个表中任何行不匹配的行，而外连接扩展了内连接的结果集，除了返回所有匹配的行外，还会返回一部分或全部不匹配的行。这主要取决于外连接的种类。外连接分为左（外）连接和右（外）连接。因为内连接没有左右之分，所以习惯上，我们将左外连接和右外连接简称为左连接和右连接。263.4.3 外连接查询1左连接左连接的运算使用left join。左连接的连接条件中左边的表不加限制。【示例3-21】在employees表中，

17、新插入一条记录，再使用左连接查询员工信息和员工所在的部门名称,并要求job_id的值是“IT_PROG”。273.4.3 外连接查询2. 右连接右连接的运算使用right join。右连接中对右边的表不加限制。【示例3-22】与左连接相反，右连接是以运算符右侧的表作为基础表来实现关联。右连接的运算符为right join，使用右连接来改写示例3-21。283.4.3 外连接查询3. 外连接的简略写法其实，外连接，使用left join和right join无非是为了判断到底以数据源作为基础。因此，Oracle提供了外连接的简略写法在where条件中将附属数据源的列使用(+)进行标识，从而省略l

18、eft join/right join以及on关键字。【示例3-23】我们可以利用外连接的简略写法来改写示例3-21。293.4.3 外连接查询4简略写法的优劣很多时候，使用简略写法会缩小代码长度，并且易于调整基础表。例如，我们利用where e.department_id(+)=d.department_id来指定表d为基础表，当需要转换基础表时，只需调整(+)的位置即可，如下所示。where e.department_id=d.department_id(+)但是，当from子句指定了多个数据源时，使用left join/right join的写法更易维护。303.4.3 外连接查询5完全

19、连接完全连接实际是一个左连接和右连接的组合。也就是，如果两个数据源使用了完全连接，那么将首先进行一次左连接，然后进行一次右连接，最后再删除其中的重复记录，即得到完全连接。完全连接应该使用full join关键字，并使用on关键字指定连接条件。【示例3-24】例如，可以利用full join来获得表employees与表departments的完全连接。313.5 层次化查询关系型数据库中，同一个数据表中不同的记录之间存在着平行关系。但是，有时候，各记录之间也可能存在着“父子”关系。当这些“父子”关系较为复杂时，我们可以将整个表中的数据看做树状结构，而基于树状结构数据的查询，称为层次化查询。层次查询使用树的遍历，走遍含树形结构的数据集合，来获取树的层次关系报表的方法，对于树形结构的父子关系，你可以控制，遍历树的方向，是自上而下，还是自下而上；确定层次的开始点(root)的位