oracle 11g 007 sql 第6章 子查询和集合 笔记

atx2418

oracle 11g 007 sql 第6章 子查询和集合 笔记

6.1子查询：
子查询可以用于查询条件。子查询时值查询语句的内部入查询以获得临时的结果集。oracle总是自动化带有子查询的查询语句。
如果子查询中的数据源与父查询中的数据可以实现连接操作那么将转化连接操作，否则将首先执行查询，然后执行父查询。

6.2查询条件中的子查询例子：

SQL> select b.job_id,a.last_name
  2  from employees a,jobs b
  3  where b.job_id in (select job_id from jobs);
 
JOB_ID     LAST_NAME
---------- -------------------------
AC_ACCOUNT Abel
AC_ACCOUNT Ande
AC_ACCOUNT Atkinson
AC_ACCOUNT Austin
AC_ACCOUNT Baer
AC_ACCOUNT Baida
AC_ACCOUNT Banda
AC_ACCOUNT Bates
AC_ACCOUNT Bell
AC_ACCOUNT Bernstein
AC_ACCOUNT Bissot
AC_ACCOUNT Bloom
AC_ACCOUNT Bull

6.3建表语句的子查询例子：
创建一个与该视图具有相同结构的空数据表，则可以利用如下的sql语句：

SQL> create table temp_table
  2  as select * from employees
  3  where 1<>1;
 
Table created

SQL> commit;
 
Commit complete

6.4插入语句的子查询：

SQL> insert into temp_table
  2  select * from employees
  3  where salary between 2000 and 3000;
 
26 rows inserted
 
SQL> commit;
 
Commit complete

6.6联合语句

联合语句是指对于合并两个结果集中的所有记录并将其中重复记录剔除（保证结果集中的记录唯一）

6.6.1 求并集（记录唯一）————Union运算：

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

SQL> select last_name,job_id,salary from employees
  2  union
  3  select last_name,job_id,salary from temp_table;
 
LAST_NAME                 JOB_ID         SALARY
------------------------- ---------- ----------
Abel                      SA_REP       11000.00
Ande                      SA_REP        6400.00
Atkinson                  ST_CLERK      2800.00
Austin                    IT_PROG       4800.00
Baer                      PR_REP       10000.00
Baida                     PU_CLERK      2900.00
Banda                     SA_REP        6200.00
Bates                     SA_REP        7300.00
Bell                      SH_CLERK      4000.00
Bernstein                 SA_REP        9500.00
Bissot                    ST_CLERK      3300.00
Bloom                     SA_REP       10000.00
Bull                      SH_CLERK      4100.00
Cabrio                    SH_CLERK      3000.00
Cambrault                 SA_MAN       11000.00
Cambrault                 SA_REP        7500.00
Chen                      FI_ACCOUNT    8200.00
Chung                     SH_CLERK      3800.00
Colmenares                PU_CLERK      2500.00
Davies                    ST_CLERK      3100.00
。。。。。。。。。。。。。。。。。。。。。。。。
107 rows selected

6.6.2求并集——————Union all 运算：

Union all 运算与Union运算都可以看作并集运算，但是Union all只是将两个运算结果集进行简单整合，并不剔除
其中的重复记录数据，这是与Union运算的最大区别。

SQL> select last_name,job_id,salary from employees
  2  union all
  3  select last_name,job_id,salary from temp_table;
 
LAST_NAME                 JOB_ID         SALARY
------------------------- ---------- ----------
OConnell                  SH_CLERK      2600.00
Grant                     SH_CLERK      2600.00
Whalen                    AD_ASST       4400.00
Hartstein                 MK_MAN       13000.00
Fay                       MK_REP        6000.00
Mavris                    HR_REP        6500.00
Baer                      PR_REP       10000.00
Higgins                   AC_MGR       12000.00
Gietz                     AC_ACCOUNT    8300.00
King                      AD_PRES      24000.00
Kochhar                   AD_VP        17000.00
De Haan                   AD_VP        17000.00
Hunold                    IT_PROG       9000.00
Ernst                     IT_PROG       6000.00
Austin                    IT_PROG       4800.00
Pataballa                 IT_PROG       4800.00
Lorentz                   IT_PROG       4200.00
Greenberg                 FI_MGR       12000.00
Faviet                    FI_ACCOUNT    9000.00
Chen                      FI_ACCOUNT    8200.00
。。。。。。。。。。。。。。。。。。。。。。。。
133 rows selected

6.6.3求交集——————intersect运算：

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

SQL> select last_name,job_id,salary from employees
  2  intersect
  3  select last_name,job_id,salary from temp_table;
 
LAST_NAME                 JOB_ID         SALARY
------------------------- ---------- ----------
Atkinson                  ST_CLERK      2800.00
Baida                     PU_CLERK      2900.00
Cabrio                    SH_CLERK      3000.00
Colmenares                PU_CLERK      2500.00
Feeney                    SH_CLERK      3000.00
Gates                     SH_CLERK      2900.00
Gee                       ST_CLERK      2400.00
Geoni                     SH_CLERK      2800.00
Grant                     SH_CLERK      2600.00
Himuro                    PU_CLERK      2600.00
Jones                     SH_CLERK      2800.00
Landry                    ST_CLERK      2400.00
Markle                    ST_CLERK      2200.00
Marlow                    ST_CLERK      2500.00
Matos                     ST_CLERK      2600.00
Mikkilineni               ST_CLERK      2700.00
OConnell                  SH_CLERK      2600.00
Olson                     ST_CLERK      2100.00
Patel                     ST_CLERK      2500.00
Perkins                   SH_CLERK      2500.00
 
LAST_NAME                 JOB_ID         SALARY
------------------------- ---------- ----------
Philtanker                ST_CLERK      2200.00
Rogers                    ST_CLERK      2900.00
Seo                       ST_CLERK      2700.00
Sullivan                  SH_CLERK      2500.00
Tobias                    PU_CLERK      2800.00
Vargas                    ST_CLERK      2500.00
 
26 rows selected

6.6.4求差集————minus运算：

minus是集合向的减法运算集，该运算将返回第一个集合中的存在，而个集合中不存在的记录。

SQL> select last_name,job_id,salary from employees
  2  minus
  3  select last_name,job_id,salary from temp_table;
 
LAST_NAME                 JOB_ID         SALARY
------------------------- ---------- ----------
Abel                      SA_REP       11000.00
Ande                      SA_REP        6400.00
Austin                    IT_PROG       4800.00
Baer                      PR_REP       10000.00
Banda                     SA_REP        6200.00
Bates                     SA_REP        7300.00
Bell                      SH_CLERK      4000.00
Bernstein                 SA_REP        9500.00
Bissot                    ST_CLERK      3300.00
Bloom                     SA_REP       10000.00
Bull                      SH_CLERK      4100.00
Cambrault                 SA_MAN       11000.00
Cambrault                 SA_REP        7500.00
Chen                      FI_ACCOUNT    8200.00
Chung                     SH_CLERK      3800.00
Davies                    ST_CLERK      3100.00
De Haan                   AD_VP        17000.00
Dellinger                 SH_CLERK      3400.00
Dilly                     SH_CLERK      3600.00
Doran                     SA_REP        7500.00
。。。。。。。。。。。。。。。。。。。。。。。。
81 rows selected

6.6.5联合语句的混合运算：

对于这四种集合运算———— Union运算、Union all运算、intersect运算、和minus运算，oracle允许进行混合运算在混合运算时，这四种运算
的优先是相同的也就是说，它们将按照自左至右的顺序依次进行。

intersect 和 Union all 的混合运算例子：

SQL> select last_name from employees
  2  union all
  3  select last_name from employees
  4  intersect
  5  select last_name from temp_table;
 
LAST_NAME
-------------------------
Atkinson
Baida
Cabrio
Colmenares
Feeney
Gates
Gee
Geoni
Grant
Himuro
Jones
Landry
Markle
Marlow
Matos
Mikkilineni
OConnell
Olson
Patel
Perkins
 
LAST_NAME
-------------------------
Philtanker
Rogers
Seo
Sullivan
Tobias
Vargas
 
26 rows selected

6.6.6连接：

在大多数查询中，所使用的数据源往往有多个。当多个数据源同时使用时，这些数据源如何进行组合变成为宜个至关重要的问题。
连接即用来指定多个数据源之间的组合关系，默认情况下，多个数据源之间使用的是笛卡尔乘积方式进行组合。除此之外，oracle
还提供了另外几种特殊的组合方式，这些特殊方式有效的补充了笛卡尔乘积的不足。

6.6.7 自然连接：

自然连接所使用的关键字是natural join，其连接原则为，两个数据源的共有的列，并且具有相同的列值。

SQL> select last_name,job_id,salary from employees
  2  natural join jobs;
 
LAST_NAME                 JOB_ID         SALARY
------------------------- ---------- ----------
OConnell                  SH_CLERK      2600.00
Grant                     SH_CLERK      2600.00
Whalen                    AD_ASST       4400.00
Hartstein                 MK_MAN       13000.00
Fay                       MK_REP        6000.00
Mavris                    HR_REP        6500.00
Baer                      PR_REP       10000.00
Higgins                   AC_MGR       12000.00
Gietz                     AC_ACCOUNT    8300.00
King                      AD_PRES      24000.00
Kochhar                   AD_VP        17000.00
De Haan                   AD_VP        17000.00
Hunold                    IT_PROG       9000.00
Ernst                     IT_PROG       6000.00
Austin                    IT_PROG       4800.00
Pataballa                 IT_PROG       4800.00
Lorentz                   IT_PROG       4200.00
Greenberg                 FI_MGR       12000.00
Faviet                    FI_ACCOUNT    9000.00
Chen                      FI_ACCOUNT    8200.00
。。。。。。。。。。。。。。。。。。。。。。。。
107 rows selected

6.6.8 内连接

自然连接强制使用两表之间的公共列作为搜索条件，而且要求公共列的值必须相等。这带来了极大的限制。因此，自然连接并不常用
而内连接突破了这两种约束，内连接可以自行指定连接列和连接条件，内连接运算的关键字为inner join：

SQL> select last_name,a.job_id,salary
  2  from employees a inner join jobs b
  3  on a.job_id=b.job_id;
 
LAST_NAME                 JOB_ID         SALARY
------------------------- ---------- ----------
OConnell                  SH_CLERK      2600.00
Grant                     SH_CLERK      2600.00
Whalen                    AD_ASST       4400.00
Hartstein                 MK_MAN       13000.00
Fay                       MK_REP        6000.00
Mavris                    HR_REP        6500.00
Baer                      PR_REP       10000.00
Higgins                   AC_MGR       12000.00
Gietz                     AC_ACCOUNT    8300.00
King                      AD_PRES      24000.00
Kochhar                   AD_VP        17000.00
De Haan                   AD_VP        17000.00
Hunold                    IT_PROG       9000.00
Ernst                     IT_PROG       6000.00
Austin                    IT_PROG       4800.00
Pataballa                 IT_PROG       4800.00
Lorentz                   IT_PROG       4200.00
Greenberg                 FI_MGR       12000.00
Faviet                    FI_ACCOUNT    9000.00
Chen                      FI_ACCOUNT    8200.00
。。。。。。。。。。。。。。。。。。。。。。。。
107 rows selected

6.6.9外连接

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

左连接例子：

SQL> select last_name,a.job_id,salary from employees a
  2  left join jobs b on a.job_id=b.job_id;
 
LAST_NAME                 JOB_ID         SALARY
------------------------- ---------- ----------
OConnell                  SH_CLERK      2600.00
Grant                     SH_CLERK      2600.00
Whalen                    AD_ASST       4400.00
Hartstein                 MK_MAN       13000.00
Fay                       MK_REP        6000.00
Mavris                    HR_REP        6500.00
Baer                      PR_REP       10000.00
Higgins                   AC_MGR       12000.00
Gietz                     AC_ACCOUNT    8300.00
King                      AD_PRES      24000.00
Kochhar                   AD_VP        17000.00
De Haan                   AD_VP        17000.00
Hunold                    IT_PROG       9000.00
Ernst                     IT_PROG       6000.00
Austin                    IT_PROG       4800.00
Pataballa                 IT_PROG       4800.00
Lorentz                   IT_PROG       4200.00
Greenberg                 FI_MGR       12000.00
Faviet                    FI_ACCOUNT    9000.00
Chen                      FI_ACCOUNT    8200.00
。。。。。。。。。。。。。。。。。。。。。。。。
107 rows selected

6.6.10 层次化查询：

关系型数据库中，同一个数据表中的记录具有相同的列。因此,不同的记录之间有在着平行关系，但是有时候各记录之间也可能存在父子关系
当这些父子关系较为复杂时，我们可以将整个表中的数据看做一棵树状结构，而基于树状结构数据的查询，称为层次化查询。

例子：

 SQL> select last_name,salary from employees
  2  start with job_id='SH_CLERK'
  3  connect by prior employee_id=department_id;
 
LAST_NAME                     SALARY
------------------------- ----------
Taylor                       3200.00
Fleaur                       3100.00
Sullivan                     2500.00
Geoni                        2800.00
Sarchand                     4200.00
Bull                         4100.00
Dellinger                    3400.00
Cabrio                       3000.00
Chung                        3800.00
Dilly                        3600.00
Gates                        2900.00
Perkins                      2500.00
Bell                         4000.00
Everett                      3900.00
McCain                       3200.00
Jones                        2800.00
Walsh                        3100.00
Feeney                       3000.00
OConnell                     2600.00
Grant                        2600.00
 
20 rows selected