SQL子查询

子查询

在外部的select、update或delete语句内部包含的select语句称为子查询。

子查询语句根据返回记录的行数分为：单行子查询和多行子查询。

单行子查询是指向外部语句返回一行记录或者不返回记录。如果返回到记录只有一列，则称为标量子查询。

如果子查询返回多列，称为多列子查询；

如果子查询中引用了外部SQL语句中的一列或多列，称为关联子查询；

子查询中还可以包含子查询，称为嵌套子查询，子查询最多可嵌套255层。

1，单行子查询

该子查询要么返回一行记录，要么不返回记录。它可以放在外部SQL语句的where、having或from子句中。

【示例】查询和名字为’Fred’的员工具有同一个上司的其它员工的编号和姓名。

eg:

SQL> select employee_id, first_name || ' ' || last_name name from employees where manager_id = (select manager_id from employees where first_name= 'Fred') and first_name <> 'Fred';

(将manager_id = 的数值替换为查找出该数值的语句 (select manager_id from employees where first_name= 'Fred'))

【示例】查询价格大于所有产品平均价格的产品的编号、产品名、价格。

eg：

SQL> select product_id, name, price from products where price > (select avg(price) from products);

【示例】对产品按照产品类型进行分组，输出产品组平均价格高于全部产品平均价格的产品类型编号和平均价格。

SQL> select product_type_id, avg(price) from products having avg(price) > (select avg(price) from products) group by product_type_id ;
 
(注：分组的关键字只能用having)

【示例】检索每一个产品的编号，产品名，被购买的次数，被购买的数量

1* select p.product_id, p.name, t.cou, t.qun from (select product_id, count(product_id) cou, sum(quantity) qun from purchases group by product_id) t, products p where p.product_id = t.product_Id
SQL> /

放在外部SQL语句的FROM子句中的子查询也叫做内联视图，因为它是给FROM子句提供内联数据。

注：如果单行子查询返回了多行记录，则会出现错误。

子查询中不能使用ORDER BY子句，排序操作都必须放在外部查询中完成。

2，多行子查询

因为子查询会返回多条记录，所以在外部SQL语句中可以使用IN,ANY,ALL等操作符。

【示例】查询购买了编号为2的产品的顾客的信息。

【示例】查询产品订购数量小于1号产品和2号产品订购数量的产品的编号和产品名。

eg:

SQL> select * from customers where customer_id in (select customer_id from purchases where product_id = 2);

SQL> select product_id, sum from (select * from ( select product_id, sum(quantity) sum from purchases group by product_id) t) where sum < 5

SQL> /

SQL> select p.name,t.id,t.sum1 from (select p.product_id id,sum(quantity) sum1 from purchases pu inner join products p on pu.product_id=p.product_id group by p.product_id) t,products p where t.id=p.product_id and t.sum1 < all( select t.sum1 from (select p.product_id id,sum(quantity) sum1 from purchases pu inner join products p on pu.product_id=p.product_id group by p.product_id) t,products p where t.id=p.product_id and t.id in(1,2));

3，多列子查询

子查询返回1列以上。如果返回多条记录，同样可以使用IN、ANY 或ALL。

【示例】查询每种产品类型中价格最低的产品的编号、产品名和价格。

思路：在Where子句中，使用子查询先找出每个产品类型的编号及对应的最低价格，返回一个集合；在外层查询中使用IN操作符判断某个产品是否位于返回的集合中。

该题也可使用内联视图实现相应的功能。

4，关联子查询

子查询会引用外部SQL语句中的一列或多列。在查询时，关联子查询对于外部查询中的每一行都会运行一次。

例：要查询价格高于同类产品平均价格的产品的信息。子查询中要根据外部查询中的产品类别计算该类产品的平均价格，然后外部查询使用子查询返回的平均价格进行过滤。

外部查询中的每一行都被一次一行的传递给子查询，子查询当前行中的某个值进行查询，直到外部查询中的所有行都被处理完，才返回整个查询的结果。

【示例】查询价格高于同类产品平均价格的产品的信息

如果只关心子查询是否有记录返回而不关心返回的值是什么，此时可使用EXISTS操作符，该操作符用于检查是否有记录被返回。只要有记录被返回，该操作符就返回true。

例：要检索负责管理其它员工的员工的信息，此时只需判断是否存在员工被该员工管理。

【示例】检索负责管理其它员工的员工的信息

NOT EXISTS 是相反的判断，即不存在返回的记录时该操作符返回true。

【示例】使用NOT EXISTS检索从来未被卖出的产品的信息

注：当子查询返回空值时，NOT EXISTS操作符也是返回true。

在UPDATE和DELETE语句中包含子查询

1，当要更改表中的记录时，使用UPDATE语句，该语句的格式为：

UPDATE tableName SET column=value WHERE condition
【示例】更新某产品的产品名

【示例】将编号为2的员工的工资改为和3号员工的工资相同

2，当要删除表中的记录时，使用DELETE语句，格式为：

DELETE FROM tableName WHERE condition

【示例】删除purchases表中的某条记录

【示例】删除价格最低的产品的信息