Oracle数据库(三)——子查询和连接

使用子查询

什么是子查询？子查询就是嵌套查询，它是嵌套在另一个语句中的select语句。在很多情况下，where后面的条件不是一个确切的值或表达式，而是另外一个查询语句的查询结构。子查询不仅仅出现在select语句中，也会出现在delete和update语句中，它本质上是where后面的一个条件表达式。

子查询返回单行：

--查询产品类型为"MP3"的产品名称和产品价格
select productname,productprcie from productinfo
where category = (select categoryid from categoryinfo where categoryname = 'MP3');
--查询出产品价格在最大值和最小值之间的产品，不包含两端的值。脚本如下：
select productname,productprice from productinfo
where productprice > (select min(productprcie) from productinfo) and productprice < (select max(productprice) from productinfo);

子查询返回多行：

如果子查询返回值为多行值，那么需要用到IN关键字，此时IN的用法和前面介绍的方式一致，除此之外，也可以使用量化比较关键字SOME,ANY,ALL,这些需要配合<,<=,=,>,>=使用。

ANY:表示满足子查询结构的如何一个 SOME:和ANY含义相同 ALL:查询的所有结果

--从表productinfo中查询出价格低于知道价格列表中的最大值。指定的价格列表就是指定产品类型编码为""0100030002" 的所有产品价格
select productname,productprice from productinfo
where productprice <
any (select productprice from productinfo where category = '0100030002')and category <> '0100030002'
--some 的用法和any一样，只不过any多用于非""="的环境中
select productname,productprice from productinfo
where productprice =
some (select productprice from productinfo where category = '0100030002')and category <> '0100030002';
--all 找出比指定价格列表还低的产品数据
select productname,productprice from productinfo
where productprice < 
ALL (select productprice from productinfo where category = '0100030002')

连接查询

连接分为内连接，外链接和全连接，还有一种是自连接

内连接也称为简单连接，它会把两个或多个表进行连接，只能查询出匹配的记录，不匹配的记录将无法查询出来，这种连接查询是平常最常用的查询。内连接中最常用的就是等值连接和不等值连接

等值连接：连接条件中使用”=”连接两个条件列表

--查询出productinfo表和categoryinfo表中产品类型编码一致的数据
select p.productname,p.productprcie,c.categoryname
from productinfo p ,categoryinfo c where p.category = c.categoryid;
--或者
select p.productname,p.productprice,c.categoryname
from productinfo p INNER JOIN categoryinfo c ON p.category = c.categoryid;

不等值连接：连接条件不是”=”,但这种方式通常需要和其他等值运算一起使用，否则检索出来的数据很可能没有实际意义

--内连接中的关键字""innner join" 可以直接写成 "join",系统会把""join" 识别成内连接。但是""on" 不能省略
select p.productname,p.productprice,c.categoryname
from productinfo p INNER JOIN categoryinfo c ON p.category IN c.categoryid;
select p.productname,p.productprice,c.categoryname
from productinfo p , categoryinfo c where p.category IN c.catgoryid;

自连接

所谓自连接，就是把自身表的一个引用作为另一个表来处理，这样就能获取一些特殊的数据

--获取表productinfo中数量相等的不同产品
select p.productname,p.productprice,pr.productname,pr.productprice,pr.quantity
from productinfo p,productinfo pr
where p.productid != pr.productid
AND p.quantity = pr.quantity
AND p.rowid < pr.rowid; --去除重复记录，只取rowid比较小的那条

select p.productname,p.productprice,pr.productname,pr.productprice,pr.quantity
from productinfo p join productinfo pr
ON p.productid != pr.productid
AND p.quantity = pr.quantity
AND p.rowid < pr.rowid; --去除重复记录，只取rowid比较小的那条
--语句中省略了""INNER"关键字

外连接

外连接分为左外连接，右外连接，全外连接。它们所表示的含义如下：

左外连接：又称为左向外连接。使用左外连接的查询，返回结果不仅仅是符合连接条件的行记录，还包含了左边表中的全部记录。也就是说，如果左表的某行记录在右表中没有匹配项，则在返回结果中右表的所有选择列表均为空。

右外连接：又称为右向外连接。它与左外连接相反，将右边的表中所有的数据与左表进行匹配，返回的结果除了匹配成功的记录，还包含右表中未匹配成功的记录，并在其左表对应的列补空值

全外连接：返回所有匹配成功的记录，并返回左表未匹配成功的记录，也返回右表未匹配成功的记录

--左连接 要求检索出productinfo表中每个产品对应的产品类型的名称。
select p.productname,p.productprice,p.category,c.categoryid,c.categoryname
from productinfo p LEFT JOIN categoryinfo c ON p.category = c.categoryid;
--右连接 查看那些产品类型还没有对应的产品
select p.productname,p.productprice,p.category,c.categoryid,c.categoryname from productinfo p RIGHT JOIN categoryinfo c ON p.category = c.categoryid;
--全外连接 左右连接的综合
select p.productname,p.productprice,p.category,c.categoryid,c.categoryname from productinfo p FULL JOIN categoryinfo c ON p.category = c.categoryid;

(+)的使用

在Oracle使用外连接，可以用“(+)”表示外连接。虽然这种方式可以实现外连接，但开发人员最好还是使用outer join关键字

(+)的使用方法读者可以简单记一下，该操作符总是放在非主表的一方，并且需要使用where子句，不能存在outer join关键字

--左外连接使用(+)
select p.productname,p.productprice,p.category,c.categoryid,c.categoryname
from productinfo p,categoryinfo c 
where p.category = c.categoryid(+);
--右连接使用(+)
select p.productname,p.productprice,p.category,c.categoryid,c.categoryname from productinfo p, categoryinfo c 
where p.category(+) = c.categoryid;