Oracle学习笔记：SQL查询总结

Oracle学习笔记：SQL查询总结

1 查询语句

 1.1获取唯一记录——distinct

获取员工唯一工资状况的SQL语句如下所示。

SQL> select distinct e.employee_name, s.salary

    from employees e, salary s

    where e.employee_id = s.employee_id;

 1.2分组——group by子句

  在数据库查询中，分组是一个非常重要的应用。分组是指将数据表中所有记录中，以某个或者某些列为标准，划分为一组。

  进行分组查询应该使用group by子句。group by子句指定分组标准，并将数据源按照该标准进行划分，然后循环处理每组数据。

 1.3  过滤分组——having子句

 where子句可以过滤from子句所指定的数据源，但是对于group by子句所产生的分组无效。为了将分组按照一定条件进行过滤，应该使用having子句。

 为了获得工资总额大于10000的记录，应该使用如下SQL语句。

   select e.employee_id, e.employee_name, sum(s.salary) total_salary

   from employees e, salary s

   where e.employee_id = s.employee_id

   group by e.employee_id, e.employee_name

   having (sum(s.salary))>10000

 1.4  排序——order by子句

  order by子句用于排序结果集。order by子句在使用时需要指定排序标准和排序方式。排序标准是指按照结果集中哪个或哪些列进行排序；order by有两种排序方式——升序(asc，同时也是默认排序方式)和降序（desc）。

  对该搜寻结果按照工资水平由高到低的顺序排列，SQL语句如下所示。

SQL> select distinct e.employee_name, s.salary

    from employees e, salary s

    where e.employee_id = s.employee_id

    order by s.salary desc;

  1.5  order by与group by子句

  当两种子句同时存在时，Oracle首先执行group by子句，然后才进行排序操作

  order by子句所指定的排序列，必须出现在select表达式中。

  获得员工工资总额状况，并按照工资总额由高到低的顺序进行排列。

SQL> select e.employee_name, sum(s.salary) total_salary

    from employees e, salary s

    where e.employee_id = s.employee_id

    group by e.employee_name

    order by total_salary desc;

  1.6  order by与distinct

  order by子句与distinct关键字同时使用时，也必须遵循这样一个

  规则——order by子句所指定的排序列，必须出现在select表达式中。

 获得所有员工姓名，并按工资进行升序排列。

SQL> select e.employee_name,s.salary

    from employees e, salary s

    where e.employee_id = s.employee_id

    order by s.salary desc;

    

2  子查询

 子查询是指在查询语句的内部嵌入查询，以获得临时的结果集。

 Oracle总是自动优化带有子查询的查询语句。如果子查询中的数

 据源与父查询中的数据可以实现连接操作，那么将转化为连接操

 作；否则，将首先执行子查询，然后执行父查询。

 2.1  查询条件中的子查询

 例如，在表employees中存储了员工信息。但是其中某些员工并未领取过工资。

 那么可以利用子查询作为查询条件来获得所有领取过工资的员工信息。

SQL> select * from employees

    where employee_id in (select employee_id from salary);

 2.2  建表语句中的子查询

 子查询的另外一个应用场景用于建表语句中。

 例如，Oralce的内置视图user_objects描述了当前用户所有对象信息。

 现欲创建一个与该视图具有相同结构的空数据表，则可以利用如下SQL语句。

SQL> create table tmp_user_objects

    as select * from user_objects where 1<>1;

 2.3  插入语句中的子查询

 我们也可以在插入语句中使用子查询。这相当于向表中批量插入数据。

 创建的数据表为空表，我们可以利用子查询向其中插入数据。

SQL> insert into tmp_user_objects

    select * from user_objects

    where object_type='TABLE';

select * from user_objects where object_type='TABLE'

用于获得视图user_objects中object_type为“TABLE”的所有记录。

该子查询的所有记录，将被插入表tmp_user_objects中。

3  联合语句

 联合语句是指对于多个查询所获得结果集进行集合操作。

 这些集合操作包括：union、union all、intersect和minus。

 这些集合运算都是二元运算，运算结果仍然是一个记录集合。

 3.1  求并集（记录唯一）——union运算

  union运算实际是合并两个结果集中的所有记录，并将其中重复记录剔除（保证结果集中的记录唯一）。

  实例：数据库中存在着两个表a_students和b_students，

  分别存储了参加了a培训班和b培训班的学生信息。

  select student_id, student_name from a_students

    union 

    select student_id, student_name from b_students;

 3.2  求并集——union all运算

 union all运算与union运算都可看做并集运算。

 但是union all只是将两个运算结果集进行简单整合，并不剔除其中的重复数据。

 这是与union运算的最大区别。

 只需在上例union后加上all即可

 3.3  求交集——intersect运算

 interseect运算是指交集运算。

 该运算可以获得两个结果集的交集——即同时存在于两个结果集中的记录。

   在表a_students和表b_students中均存在着学生信息。

   现欲获得既参加了a班，又参加了b班的学生姓名。

   即同时存在于两个表中的学生姓名，则可以使用intersect运算。

   相应的SQL语句如下所示。

   SQL> select student_name from a_students

    intersect

    select student_name from b_students;

  3.4  求差集——minus运算

  minus是集合间的减法运算。该运算将返回第一个集合中存在，

  而第二个集合中不存在的记录。

  现欲获得参加了a培训班，但是未参加b培训的学生。

  此时，我们可以利用minus运算获得存在于表a_students中，

  而不存在于表b_students中的学生姓名。

  SQL> select student_name from a_students

    minus

    select student_name from b_students;

  3.5  联合语句的混合运算

  对于这四种集合运算——union运算、uion all运算、intersect运算和minus运算，

  Oracle允许进行混合运算。在混合运算时，这四种运算的优先级是相同的，

  也就是说，他们将按照自左至右的顺序依次进行。

  intersect和union all的混合运算。

  SQL> select student_name from a_students

    intersect

    select student_name from a_students

    union all

    select student_name from b_students

4  连接

  在大多数查询中，所使用的数据源往往有多个。

  当多个数据源同时使用时，这些数据源如何进行

  组合便成为了一个至关重要的问题。连接即用来

  指定多个数据源之间的组合关系。默认情况下，

  多个数据源之间使用的是笛卡尔积方式进行组合。

  除此之外，Oracle还提供了另外几种特殊的组合方式。

  这些特殊方式有效补充了笛卡尔积的不足

  

  4.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

   4.2  内连接

   自然连接强制使用两个表之间的公共列作为搜寻条件；

   而且要求公共列的值必须相等。这带来了极大的限制，

   因此，自然连接并不常用。而内连接突破了这两种约束。

   内连接可以自行指定连接列和连接条件。内连接运算的关键字为inner join。

  同样为了实现获得员工工资状况这一需求，利用内连接的SQL语句如下所示。

SQL> select e.employee_id, e.employee_name, s.month, s.salary

    from employees e

    inner join salary s

    on e.employee_id = s.employee_id;

   4.3  外连接

   内连接所指定的两个数据源，处于平等的地位。而外连接不同，外连接总是以一个数据源为基础，将另外一个数据源与之进行条件匹配。即使条件不匹配，基础数据源中的数据总是出现在结果集中。那么，依据哪个数据源作为基础数据源，便出现了两种外连接的方式——左（外）连接和右（外）连接。因为内连接没有左右之分，所以习惯上，我们将左外连接和右外连接简称为左连接和右连接。

    1．左连接

    2. 右连接

    3. 外连接的简略写法

    简略写法的优劣

    5．完全连接

   语句片断：

　　SELECT MODELNAME, TABLENAME

　　FORM EMCD_MONITOR_MODEL , EMCD_MONITOR_TABLE

　　WHERE EMCD_MONITOR_TABLE.MODELID(+) =EMCD_MONITOR_MODEL.ID 

此SQL文使用了右连接，即“(+)”所在位置的另一侧为连接的方向，右连接说明等号右侧的所有记录均会被显示，无论其在左侧是否得到匹配，如果没有匹配则显示为空；

始终记住(+)所在位置的另一侧为连接的方向，如A.bid(+)=B.id，即以该方向的B表作为根，扫描B表以驱动A的扫描匹配；显示的时候B的所有信息均显示，对应的另一侧A的记录有则显示，没有则空着。

反之， A.bid=B.id (+)为左连接；

相当于标准SQL里面的left/right outer join

 

  5  层次化查询

  关系型数据库中，同一个数据表中的记录具有相同的列，因此，

  不同的记录之间存在着平行关系。但是，有时候，各记录之间也

  可能存在着“父子”关系。当这些“父子”关系较为复杂时，我们可以

  将整个表中的数据看做树状结构，而基于树状结构数据的查询，

  称为层次化查询。

  5.1  树状模型

  树状模型一个很典型的实例为市场信息。不同层级的市场之间存在着父子关系。

  

  

   

   5.2  层次化查询

自9i版本开始，Oracle提供了层次化查询方案来递归获得树状模型的信息。层次化查询的语法如下所示。

select 列名1、列名2...

from 表名

start with 开始条件

connect by 递归条件

其中，start with指定查询的起点，即从哪些记录开始进行查询；connect by指定递归条件，如果获得下一条记录。

5.3  层次化查询的相关函数

对于层次化查询，最常用的函数为sys_connect_by_path()函数。层次化查询总是以某条记录为起点，根据connect by所指定的条件递归获得结果集合。而sys_connect_by_path()函数，则可以对起始至当前记录之间的结果集进行聚合操作。该操作仅限于串联字符串，相应的语法如下所示。

sys_connect_by_path(列名,分隔符)

其中，��名指定将哪个列的值进行串联，而分隔符则指定字符串串联时的分隔符。