Oracle数据库（七）子查询

1、子查询的类型：
· 单行子查询 ： 不向外部的SQL语句返回结果，或者只返回一行
· 多行子查询 ： 向外部的SQL语句返回一行或者多行
· 多列子查询 ： 向外部的SQL语句返回多列
· 关联子查询 ： 引用外部的SQL语句中的一列或者多列。
· 嵌套子查询 ： 位于另外一个子查询中。子查询最多能够嵌套 255 层 。
2、编写单行子查询：
单行子查询不向外部的SQL语句返回结果，或者只返回一行。子查询可以放在select语句的where子句，having子句或者from子句中。
1）在 where 子句中使用子查询
子查询可以放在另外一个查询的 where 子句中 ，或者只返回一行 。
select first_name , last_name
from customers
where customer_id =
(select customer_id
from customers
where last_name = ‘Brown’) ;
（1）使用其他单行操作符：
上面的例子在where 子句中使用了等于操作符（=）。在单行子查询中，也可以使用其他的比较操作符，例如 <> ,< , > ,<= , >=.
PS: 下面的例子展示了在外部查询的 where 子句中使用 > 操作符 ；avg() 函数用来计算产品的平均价格，该值会被传递给外部查
询的 where 子句。
select product_id , name , price
from products
where price >
(select avg(price)
from products) ;
(2)在 having 子句中使用子查询：
having 子句的作用就是对行分组进行过滤。在外部查询的 having 子句中也可以使用子查询。
select product_type_id , avg(price)
from products
group by product_type_id
having avg(price) <
(select max(price)
from products ) ;
注意子查询首先使用 avg() 函数计算每种产品的平均价格，将至传递给max()函数，后者返回平均价格的最大值。
(3) 在 from 子句中使用子查询（内联视图）
select product_id
from
(select product_id
from products
where product_id < 3) ;
子查询向外部查询返回 product_id < 3 的行 ，外部查询对结果进行检索。返回这些行的 product_id 值。就外部
的from子句而言，子查询输出仅仅是另外一个数据源。
下面的例子更加有用，它在外部查询中从 products 表中检索 product_id 和 price 列。在子查询中检索一种产品
已经被购买的次数。
select prds.product_id , prds.price , purchases_data.product_count
from products prds ,
(select product_id , count(product_id) product_count
from purchases
group by product_id) purchases_data
where prds.product_id = purchases_data.product_id ;

				select prds.product_id , prds.price , purchases_data.product_count
				from products prds ,
				(select product_id , count(product_id) product_count
				from purchases
				group by product_id) purchases_data
				where prds.product_id = purchases_data.product_id ;
	(4) 可能碰到的两个错误：
		a、单行查询最多返回一行
			如果子查询返回多行，就会出现报错。例如下面的例子：
				select product_id , name
				from products
				where product_id = 
					(select product_id 
					from products
					where name like '%e%') ;
				products 表中有 9 行记录的名字包含了字母 e ，而子查询会视图将这 9 行全部传递给外部查询中等于操
				作符。由于等于操作符只能处理一行，因此这个查询无效，会出现错误。
		b、子查询不能包含 order by 子句：
			子查询不能包含 order by 子句，相反，必须在外部查询中进行任何排序。下面的例子就是在外部进行排序：
				select product_id , name , price
				from products
				where price > 
				(select avg(price)
				from products)
				order by price desc ;

3、编写多行子查询：
多行子查询可以向外面的 SQL 语句返回一行或者多行记录。要处理返回多行记录的子查询，外部查询可以使用 in 、any 、 all
等操作符。

1）在多行子查询中使用 in 操作符：
	in 可以用来检查在一个 至列表中是否包含指定的值。这个值列表也可以来自一个子查询的返回结果。 not in 执行的罗结果
	刚好和 in 相反。
	PS：
		下面的这个例子使用 in 操作符来检查在子查询所返回的 product_id 值列表中是是佛偶包含指定的 product_id ；
		子查询返回产品名中包含字母 e 的产品的 product_id 列的值：
			select product_id , name 
			from products
			where product_id in 
			(select product_id
			from products
			where name like '%e%' ) ;
	PS:
		下面的这个例子是使用 not in 操作符来来检查在 purchases 表中的 product_id 值列表中是否不包含指定的 
		product_id ：
			select product_id , name , price
			from products 
			where product_id not in
			(select product_id 
			from purchases) ;
			
			select name , product_id , price
			from products
			where product_id in
			(select product_id 
			from purchases) ;

2) 在多行子查询中使用 any 操作符：
	any 操作符可以用来将一个值与一个列表中的任何东西进行比较。在查询中 any 操作符之前，必须使用一个 = ,<> , <, 
	> , <= 或 >= 操作符。
	PS：
		下面的这个例子使用 any 操作符来检查是否有任何员工的工资低与 salary_grades 表中的任何一级最低工资：
			select  first_name || ' ' || last_name as name , employee_id , salary
			from employees
			where salary < any (
			select low_salary 
			from salary_grades) 
			order by employee_id;
			
3) 在多行子查询中使用 all 操作符：
	all 可以将一个值与一个列表中的所有值进行比较。在查询中 all 操作符之前，必须使用 = , <>, > , < , >= , <= 
	操作符。
	PS:
		下面的例子就是使用 all 操作符来检查是否有任何员工的工资高于 salary_grades 表中的所有级别的最高工资：
			select employee_id , last_name
			from employees
			where salary > all
			(select high_salary 
			from salary_grades ) ;

4、编写多列子查询：
下面的例子中检索每种产品类型中价格最低的产品：
select product_id , product_type_id , name , price
from products
where (product_type_id , price) in
(select product_type_id , min(price)
from products
group by product_type_id) ;
***
由于在子查询中使用了 product_type_id 作为后面的分类依据，因此在对比项中必须添加 product_type_id作为
返回值。

5、编写关联子查询：
关联子查询会引用外部查询中的一列或多列。这种子查询之所以被称为是关联子查询，是因为子查询的确与外部查询相关。
1）关联子查询的例子：
select product_id , product_type_id ,name ,price
from products outer
where price >
(select avg(price)
from products inner
where inner.product_type_id = outer.product_type_id );
****
这个查询中使用了两个别名：outer 用来标识外部查询 ， inner 用来标识内部查询 。内部查询和外部查询使用 product_id
列关联起来 。
在关联子查询中，外部查询中的每一行都被一次一行的传递给子查询。子查询一次读取外部查询中的每一行的值，并将其应用到
子查询上，直到外部子查询被中的所有行都被处理完为止。
2)在关联查询中使用 exists
下面的例子用来检索哪些负责管理的其他员工的员工记录
select employee_id , last_name
from employees outer
where exists
(select employee_id
from employees inner
where inner.manager_id = outer.employee_id) ;
由于 exits 只是检查子查询返回的行的存在性，因此查询不必返回一列；可以只返回一个常量。下面的例子就是简单地
返回常量 1 ；
3)在关联子查询中使用 not exists
下面的这个例子使用 not exists 检索从来未曾被购买的产品：
select product_id , name
from products outer
where not exists
(select 1
from purchases inner
where outer.product_id = inner.product_id) ;
*** exists 和 in 的区别：exists 只是检查行的存在性 ， 而 in 则要检查实际值的存在性 。
通常来讲，exists 的性能都比 in 高，因此应该尽可能的使用 exists ， 而不是 in。

6、编写嵌套子查询：
在子查询内部可以嵌套其他子查询，嵌套层次数最多为 255 。然而，在实际编程时应该尽量少使用嵌套子查询的技术，因为使用表
连接时，查询的性能更高。

7、编写包含子查询的 update 和 delete语句：

1）编写包含子查询的 update 语句：
	在 update 语句中，可以将新列的值设置为单行子查询返回的结果。
		update employees
		set salary = 
		(select avg(high_salary)
		from salary_grades)
		where employee_id = 4 ;
	
2)编写包含子查询的 delete 语句：
	在  delete 语句的 where 子句中，可以使用自查询返回的结果。
		delete from employees
		where salary > 
		(select avg(high_salary)
		from salary_grades) ;