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
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
