【数据库】SQL查询强化篇
查询是数据库的基本应用之一，oracle中的SQL查询语法，一方面遵循了SQL标准，另一方面又有自己的独特之处。
从而使得oracle中的SQL查询功能更加强大。接下来将会涉及oracle中的SQL查询语句，包括：
基本查询：主要讲述查询语句及各种子句的使用；
子查询：主要讲述如何在查询语句中添加子查询；
联合语句：主要讲述多个查询语句之间的集合运算；
关联语句：主要讲述多个表/视图之间的关联关系；
层次化查询：主要讲述树状结构的查询。
一、基本查询：
1. 查询命令select
执行查询的命令为select命令，该命令用于在数据源中捕获最终数据。无论查询语句多么复杂，最外层的select命令总是最后执行。
具体表现为：oracle首先根据from子句获得数据源的所有记录，接着，oracle扫描所有记录，并根据select命令所指定的列获取最终结果。
2. 指定过滤条件----where子句
where子句用于限定from子句所指定的数据源，或者各数据源进行运算之后形成的结果集合。因此，执行顺序处于from子句之后，select命令之前。即使from子句更加复杂，其执行顺序仍然如此。
注：笛卡尔积-->获得的是两个数据表的乘积，第一表的每条记录与第二表的每条记录组合所获得结果集。
3. 获取唯一记录----distinct
在查询时，会出现重复记录。为了剔除重复记录，可以利用distinct关键字。
select distinct a.xxx, b.yyy from table a, tables b where a.a_id = b.a_id
其中，distinct关键字用于获取结果集中列a.xxx，b.yyy的唯一性组合。
4. 分组----group by 子句
在数据库查询中，分组是一个非常重要的应用。分组是指将数据表中的所有记录，以某个或者某些列为标准，划分为一组。例如：在一个存储了地区学生的表中，以学校为标准，可以将所有学生信息划分为多个组。
进行分组查询应该使用group by子句。该子句指定分组标准，并将数据源按照该标准进行划分，然后循环处理每组数据。
select cd.channel_id, cd.channel_name
from intf.ecs_order eo , liuxin.channel_dianqu cd
where eo.org_id = cd.channel_id
group by cd.channel_id, cd.channel_name
其中，group by cd.channel_id, cd.channel_name用于对数据源按列 cd.channel_id, cd.channel_name 进行分组。详细步骤如下：
（1）from子句获得数据源；（2）利用where 子句筛选符合条件的记录；（3）利用group by子句进行分组；
（4）对每组进行循环处理，获得最终结果----每组的cd.channel_id, cd.channel_name。
5. 过滤分组----having子句
where子句可以过滤from子句所指定的数据源，但是对于group by子句所产生的分组无效。为了将分组按照一定条件进行过滤，应该使用having子句。
having子句是依附于group by子句存在而存在的。
select cd.channel_id, cd.channel_name
from intf.ecs_order eo , liuxin.channel_dianqu cd
where eo.org_id = cd.channel_id
group by cd.channel_id, cd.channel_name
having cd.channel_id >100000199
上面例子说明的是ID号大于100000199的记录。
（1）利用group by子句分组；（2）利用having子句筛选分组----组内所有ID号大于100000199。
（3）对每组进行循环处理，获得最终结果----每组的cd.channel_id, cd.channel_name。
6. 排序----order by子句
order by子句用于排序结果集。order by子句在使用时需要指定排序标准和排序方式。排序标准是指按照结果集中哪个或哪些列进行排序；order by有两种排序方式---升序（asc，也是默认的排序方式）和降序（desc）。
select cd.channel_id, cd.channel_name
from intf.ecs_order eo , liuxin.channel_dianqu cd
where eo.org_id = cd.channel_id
order by cd.channel_id desc;
7. order by与group by子句
order by与group by同时存在的时候，oracle是怎样执行的呢？
oracle首先执行group by子句，然后才进行排序操作。
8. order by与distinct
order by子句与distinct关键字同时使用时，也必须遵循这样的一个规则---order by子句所指定的排序列，必须出现在select表达式中。
二、子查询：
三、联合语句：
联合语句是指对于多个查询获得的结果集进行集合操作。这些集合操作包括union、union all、intersect 和 minus。
这些集合运算都是二元运算，运算结果仍然是一个记录集合。
本阶段讲述这几种联合运算的使用。
1、求并集（记录唯一）----union运算
union运算实际是合并两个结果集中的所有记录，并将其中重复记录剔除（保证结果集中的记录唯一）。
例：数据库中存在着两个表a_students和b_students，分别存储了参加了a培训班和b培训班的学生信息，其数据如下所示。现需要取得a班和b班共有多少学生，实际为获取表a_students与表b_students的并集，相应的SQL语句如下所示。
select student_id, student_name from a_students
union
select student_id, student_name from b_students;
union 用于对select student_id, student_name from a_students 和 select student_id, student_name from b_students所获得结果集进行并集运算。
但需要注意的是，union运算的两个结果集必须具有完全相同的列数，并且各列具有相同的数据类型。
select student_id, student_name, student_age from a_students
union
select student_id, student_name from b_students
上面这个就会报错！
oracle将抛出错误：ORA-01789：query block has incorrect number of result columns表面查询结果的列数非法，不能进行并集运算。
2、求并集----union all 运算
union all 运算与union运算都可看做并集运算。但是union all只是将两个运算结果集进行简单整合，并不剔除其中的重复数据。这是与union运算的最大区别。
为了统计
例如：为了统计A班级和B班级有多少人次参加了培训，则可以利用union all代替union来执行上面的SQL语句。
select student_id, student_name from a_students
union all
select student_id, student_name from b_students;
union all 并不删除重复记录，因此该SQL语句的执行结果记录较多。同时，由于union all运算不删除重复记录，因此在执行效率上要高于union操作。因此，当对两个结果集已经确定不会存在重复记录时，应该使用union all操作，以提升效率。
3、求交集---intersect运算
intersect运算是指交集运算。该运算可以获得两个结果集的交集----即同时存在于两个结果集中的记录。
例如：在表a_students和表b_students中均存在着学生信息，现需要获得既参加了a班，又参加了b班的学生姓名。即同时存在于两个表中的学生姓名，则可以使用intersect运算。相应的SQL如下：
select student_name from a_students
intersect
select student_name from b_students
4、求差集----minus运算
minus是集合间的减法运算。该运算将返回第一个集合中存在，而第二个集合中不存在的记录。
例如：现需要获得参加A培训班，但是未参加B培训班的学生。此时，可以利用minus运算获得存在于表a_students中，而不存在于表b_students中的学生姓名。
select student_name from a_students
minus
select student_name from a_students;
minus用于获得两个结果集的差集。注意：是返回得哪个表中的！
5、联合运算的混合运算
对于这4种集合运算----union运算、union all运算、intersect运算和minus运算，oracle允许进行混合运算。在混合运算时，这4种运算的优先级是相同的，也就是说，他们将按照自左至右的顺序依次进行。
例如：intersect和union all的混合运算
select student_name from a_students
intersect
select student_name from a_students
union all
select student_name from b_students
调整优先级，可以使用小括号！
四、连接：
在大多数查询中，所使用的数据源往往有多个。当多个数据源同时使用时，这些数据源如何进行组合便成为了一个至关重要的问题。连接即用来指定多个数据源之间的组合关系。默认情况下，多个数据源之间使用的是笛卡尔积方式进行组合。除此之外，oracle还提供了另外几种特殊的组合方式。这些特殊方式有效地补充了笛卡尔积的不足。
1、自然连接
自然连接，顾名思义，即无须用户指定任何连接条件，只需指定连接的两个数据源。至于两个数据源如何进行数据整合则无须用户操心，自然连接所使用的关键字为natural join。其连接原则：两个数据源的共有列，并且具有相同列值。
例如：表employees和表salary都包含了employee_id列。二者可以进行自然连接操作，相应的SQL语句如下所示。
select * from employees natural join salary;
natural join 用于两个表之间的自然连接。搜寻结果获得的结果集，将含有公共列employee_id。这里所说的公共列是指，不能为列employee_id指定限定词。例如，不能讲列employee_id标识为特定表employees或者salary中的列。详看如下SQL：
select e.employee_id from employees e natural join salary s;
上面语句将会抛出错误：ORA-25155：column used in NATURAL join cannot have qualifier
select e.employee_id尝试在自然连接的结果集中获得表employees中的列employee_id。此处的错误原因是：用于自然连接的列不能指定限定词。
当然，对于自然连接之外的其他列，可以使用限定词进行修饰。例如：
select employee_id, e.employee_name, s.month, s.salary from employees e natural join salary s
2、内连接
自然连接强制使用两个表之间的公共列表作为搜寻条件；而且要求公共列的值必须相等，这带来了极大的限制，因此，自然连接并不常用。而内连接突破了这两种约束，内连接可以自行指定连接列和连接条件。内连接运算的关键字为inner join.
例如：同样为了实现获得员工工资状况这一需求，利用内连接的SQL语句如下所示。
selecte.employee_id, e.employee_name, s.month, s.salary
from employees e
inner join salarys
on e.employee_id =s.employee_id;
其中，select e.employee_id, e.employee_name, s.month, s.salary 用于获取表employees中的employee_id、employee_name和表salary中的month、salary等列，与自然连接不同的是，此处的employee_id可以使用限定符e进行修饰；from employees e inner join salary s 用于将表employees与表salary进行内联操作；on e.employee_id = s.employee_id用于指定搜寻条件----表employees的列employee_id与表salary的列employee_id具有相同的列值。
另外，内连接运算inner join 中的inner关键字可以省略，如下所示。
selecte.employee_id, e.employee_name, s.month, s.salary
from employees e
join salary s
on e.employee_id =s.employee_id；
默认情况下，oracle的连接为内连接，因此，在这里，使用了join代替inner join可以实现相同的效果，而且写法更为简洁。
对于大多数开发者来说，并不习惯使用内连接方式，而更习惯于where 条件实现，利用where 条件改写本示例语句如下：
selecte.employee_id, e.employee_name, s.month, s.salary
from employees e,salary s
wheree.employee_id = s.employee_id;
虽然可以利用where 子句改写内连接的SQL查询，但需要注意的是，当实现多表关联，oracle在执行时还是有区别的，对于where子句方式，并且from子句中含有多个数据源，oracle在进行笛卡尔积运算时会自行优化。例如：
select * fromemployees, salary, company, sales wehre ...
from 子句中包含了多个数据表，而oracle执行的方式，并非按照如下顺序employees X salary X company X sales（其中X代表进行笛卡尔积运算）。而有可能被优化为salary X employees X sales X company，因此，有时我们对oracle的执行结果感到莫名其妙，使用肉眼即可分辨这种执行结果是错误的，尽管这种机会微乎其徽。
此时，不妨使用内连接来尝试解决该问题，改写后的代码为：
select * fromemployees
join salary on....
join company on
join sales on
在这种执行方式下，oracle所执行的连接顺序一定是遵循employees X salary X company X sales。
3、外链接
内连接所指定的两个数据源，处于平等的地位。而外链接不同，外链接总是以一个数据源为基础，将另外一个数据源与之进行条件匹配。即使条件不匹配，基础数据源中的数据总是出现在结果集中。那么，依据哪个数据源作为基础数据源，便出现了两种外链接的方式----左（外）连接和右（外）连接。因为内连接没有左右之分，所以，习惯上，将左外连接和右外连接简称为左连接和右连接。
3.1 左连接
例子：左连接和右连接的运算应该使用left join 和right join.利用内连接获得员工的工资信息，但是这并不包括所有员工的工资状况，因为有的员工并未出现在工资表salary中，为了获得所有员工的工资状况，可以利用外连接来实现。
selecte.employee_id, e.employee_name, s.month, s.salary
from employeese
left join salary s
on e.employee_id =s.employee_id;
其中，from employees e left join salary s 指定数据源为表employees与salry的左连接；如果将left join 视作运算符，那么左表为employees,右表为salary；左连接的意思即以左表----employees为基础表；on e.employee_id = s.employee_id指定连接条件----表employees的employee_id列与表salary的employee_id具有相同的值。
3.2 右连接
与左连接相反，右连接是以运算符右侧的表作为基础表来实现关联。右连接的运算符为right join.
3.3 外连接的简略写法
使用left join和right join无非是为了判断到底以数据源作为基础，因此，oracle提供了外连接的简略写法----在where条件中奖附属数据源的列使用（+）进行标识，从而省略left join right join 及on关键字。
wheree.employee_id = s.employee_id(+) --左连接
wheree.employee_id(+) = s.employee_id --右连接
3.4 完全连接
完全连接实际是一个左连接和右连接的组合，也就是说，如果两个数据源使用了完全连接，那么将首先进行一次左连接，然后进行一次右连接，最后再删除其中的重复记录，即得到完全连接。完全连接应该使用full join 关键字，并使用on关键字指定连接条件。
selecte.employee_id, e.employee_name, s.month, s.salary
from employees e
full join salarys
on e.employee_id =s.employee_id;
完全连接的执行过程：首先执行employees与salary的左连接，然后执行二者的右连接，最后将两个临时结果集进行union操作。
select e.employee_id, e.employee_name, s.month, s.salary
from employees e
left join salary s
on e.employee_id = s.employee_id
union
select e.employee_id, e.employee_name, s.month, s.salary
from employees e
right join salary s
on e.employee_id = s.employee_id;
五、层次化查询：
关系型数据库中，同一个数据表中的记录具有相同的列，因此，不同的记录之间存在着平行关系。但是，有时候，各记录之间也可能存在着“父子”关系。当这些“父子”关系较为复杂时，可以将整个表中的数据看做树状结构，而基于树状结构数据的查询，称为层次化查询。
1、树状模型
树状模型一个很典型的实例为市场信息。不同层级的市场之间存在着父子关系，如下图：
可以将市场信息存储于表market中，相应的表结构及数据如下所示。
在该表中，列parent_market_id非常重要。该列存储了当前市场的父级市场ID，依据parent_market_id与market_id，可以将原本平行结构的市场信息组装为获得树状模型。
2、层次化查询
自9i版本开始，oracle提供了层次化查询方案递归获得树状模型的信息，层次化查询的语法如下所示：
select 列名1、列名2.....
from 表名
start with 开始条件
connect by 递归条件
其中，start with指定查询的起点，即从哪些记录开始查询；connect by指定递归条件，以获得下一条记录。
例如：在表market中搜索市场“亚洲”及其所有市场信息，相应的SQL语句及搜寻结果如下所示。
select market_id, market_name
from market
start with market_name = '亚洲'
connect by prior market_id = parent_market_id；
start with market_name = '亚洲'指定查询的起始节点为market_name等于'亚洲'的记录；connect by prior market_id = parent_market_id指定如何递归获得下一条记录----前一条记录的market_id等于下一条记录的parent_market_id。
connect by 遵循深度优先的搜索策略，对于以上SQL语句，其执行顺序如下所示：
（1）start with market_name = '亚洲',获得一条记录。该记录为查询的起始结点；
（2）当使用connect by prior parent_market_id = market_id，进行递归时。prior是指前一条记录，即当前记录‘亚洲’。前一条记录的parent_market_id等于下一条的market_id，那么将获得3条记录，即market_id分别为5.6.7的记录。oracle将利用最先获得记录（中国）再次进行递归；
（3）connect by prior parent_market_id = market_id，对于递归操作，前一条（prior）记录为中国，因此，此次的递归操作，会再次获得下级市场信息。
（4）对于记录北京，当oracle再次利用connect by prior market_id = parent_market_id尝试获得下一条记录时，将无法获得任何记录。这意味着已完成了最“左”端最深层的递归查询。那么oracle将返回上一级记录中国，并对第二条记录天津继续进行递归查询。
（5）以此类推，oracle遵循从左至右、深度优先的策略，可以递归的获得亚洲及亚洲之下的所有市场信息。
通过以上步骤及查询语句的执行结果可知，利用connect by 实现的层次化查询返回的实际是一个结果集，该结果集为递归遍历所有经过的每条记录(树中的结点)的集合。
3、层次化查询的相关函数
对于层次化查询，最常用的函数为sys_connect_by_path()函数。层次化查询总是以某条记录为起点，根据connect by所指定的条件递归获得结果集合。
而sys_connect_by_path()函数，则可以对起始至当前记录之间的结果集进行聚合操作。该操作仅限于串联字符串，响应的语法如下：
sys_connect_by_path(列名，分隔符)
其中，列名指定将哪个列的值进行串联，而分割符则指定字符串串联时的分隔符。
例如：为了获得北京至顶级市场的完整路径，可以使用如下SQL语句。
select market_id, market_name, sys_connect_by_path(market_name, '/' ) market_path
from market
start with market_name = '北京'
connect by prior parent_market_id = market_id;
select market_id, market_name, sys_connect_by_path(market_name, '/' )market_path用于获得层次化查询过程中，每条记录的market_id、market_name、起始记录至当前记录的market_name列的字符串串联，串联过程中，使用'/'作为分隔符；start with market_name = '北京' connect by prior parent_market_id = market_id指定层次化查询的定义---从market_name等于北京的记录开始，按照前一条记录的parent_market_id等于下一条记录的market_id的规则进行递归。
分析查询结果可知，市场路径是节节攀升的，鉴于此，可以利用max()函数获得最完整的路径。
select max(sys_connect_by_path(market_name, '/')) market_path
from market
start with market_name = '北京'
connect by prior parent_market_id = market_id;
结果为：/北京/中国/亚洲/全球
其中，max()函数可用于字符串类型，已获得按字母表顺序排列时，处于末位位置的字符串。
4、总结一条记录衍生出多条记录
对于数据表中的某条记录，有时需要多条记录为其服务。这就涉及一条记录衍生出多条记录的问题。在oracle中，如果要使一条记录衍生出多条记录，大致有两种方法，一种是通过窗口函数（将在后面介绍），另一种是通过层次化查询。
对于窗口函数来说，数据表中有多条记录是必要的，如果一个数据表中仅有一条记录，那么无论如何是无法衍生出多条记录的。并且，衍生出的记录数目（窗口大小）也不能大于数据表的记录总数。
针对层次化查询语法，所能获得的记录数目则要灵活的多。connect by 只是指定获得下一条记录的条件，针对start with所指定的记录（如果没有start with，则针对所有记录），oracle会依次扫描数据表中的记录，并以递归的方式进行下去。也就是说，connect by完全具备死循环的条件。
1、二进制转十进制
在oracle 9i以后，oracle提供了bin_to_num()函数，用于将二进制数据转换为数值型，例如：
select bin_to_num(1, 0, 1) a, bin_to_num(1,0) b from dual;
同样可以利用如下SQL语句进行处理：
select sum(data) from ( select substr('101', rownum, 1) * power (2, length('101') - rownum) data
from dual
connect by rownum < = length('101'))
在该SQL语句中，表dual本来仅有一条记录X，但是当利用connect by rownum <=length('101')获得下一条记录时，仍会获得记录X，直至获得的记录总数等于3（二进制101的总位数）。而针对每次获得的记录，oracle依次计算各位置对应的实际数字（利用2的乘方运算），最后将所有数字相加，便获得了二进制数字代表的十进制数字。
