高级查询

子查询，在一个SQL语句中嵌套另一个SQL语句称为子查询

需求：查询商品类别为“图书”的所有商品的id、商品名称、商品价格

select id,name,price from es_product where sort_id = (select idfrom es_sort where sortname = ‘图书’)

子查询的结果可能不是一行，可能返回多行查询结果的子查询，这样情况就不能使用=、<、>、<=等那些运算符了。

ROWNUM是oracle数据库从数据文件或缓冲区中读取数据的顺序。简单的说，ROWNUM是对符合条件结果的序列号，即先查到结果集之后再加上去的一个列。他总是从1开始的。物理上并不真正存在。

示例：查询商品表中前五条商品记录的id、商品名称、以及上架日期

select id,name,saledate from es_product where rownum <=5;

示例：查询商品列表中最新上架的前五条商品的id、商品名称以及上架日期

select id, name, saledate  from (select * from es_product order by saledate DESC) where rownum <= 5

分页查询

需求：分页显示商品id、商品名称、上架时间、每页显示四条，请编写查询第二页商品信息的sql语句

--将rownum固化

select id,name,saledate,rownum rn from es_product

--通过限制rn的范围取记录

select id,name,saledate from(select id,name,saledate,rownum rn from es_product)
wherern>=5 and rn<=8

按日期降序，分页显示商品id、商品名称、上架时间、每页显示4条

请编写查询第二页商品信息的sql语句

select id,name,saledate from (select id,name,saledate,rownum rn from(select * fromes_product order by saledate DESC))
where rn>=5 and rn <=8

链接查询

需求：查询商品类别为“图书”的所有商品的id、商品名称、商品价格、商品所属分类名称

select es_product.id,name,price,sortname from es_product,es_sort
wherees_product.sort_id = es_sort.id and es_sort,sortname = ‘图书’;

查询字段中id前面添加了es_product.是因为：当被连接的多个表中存在同名字段时，则必须在该字段前面加上“表名.”做为前缀

子查询和链接查询区别：子查询并不要求两个表有相关字段，只要得到的子查询的结果集用于父查询。而连接查询。则必须要求两个表有相关字段。当查询的列来自多个表时，可以使用表连接查。

表连接语法：select要查询的字段 from表1，表2……where链接条件

非等值连接案例：查询所有单价介于20-35元之间的商品名称、价格、库存量以及商品类别名称。

需求：查询所有用户的用户姓名、电话（此处为订单表中的收货人电话）、订单号、订购日期以及订单状态

select a.realname,b.tel,b.id,b.createtime,b.status from es_user a,es_order b
whereb.user_id = a.id

当某些不满足条件的列也需要显示出来。也就是说，只限制其中一个表的行，而不限制另外一个表的行时需要使用外连接。Oracle中使用+表示外连接，或者使用LEFT OUTER JOIN(左连接)RIGHT OUTER JOIN(右外连接) FULL OUTER JOIN(全外连接)

需求：查询所有用户的用户姓名、电话（此处为订单表中的收货人电话）、订单号、订购日期以及订单状态。可以进行如下修改 :

select a.realname,b.tel,b.id,createtime,status
fromes_user a,left out join es_order b
on a.id =b.user_id

左外连接的语法：

select 多表查询的列

from 左表 left [outer] join 右表

on  多表连接的条件

写法二：

select a.realname,b.tel,b.id,createtime,status
from es_user a,es_order b
where a.id = b.user_id(+)

将加号写在右边表示以左边为准，右边补齐。

集合查询：将多个查询组合到一个查询中去

对于Oracle，交集就是返回两个查询共有的记录，关键字是INTERSECT；并集是返回各个查询的所有记录，关键字是UNION或者UNION ALL；补集是返回第一个查询记录检索出的记录减去第二个查询检索出的记录之后剩余的记录，关键字是MINUS。

UNION和UNION ALL区别：UNION和UNION ALL都可以用在结果查询的“并集”操作，不过UNION ALL比UNION效率高，因为UNION查询出的内容剔除重复行，同时默认按第一个查询的第一列升序排序。而UNION ALL不做这些。

注意：分组以后筛选使用HAVING关键字，而不能使用WHERE。

示例1：获取一次订单满200元或者累计满500元的用户

select user_id from es_order where totalamount>200 UNION select user_id fromes_order group by user_id HAVING sum(totalamount) >500

示例2：获取一次订单满200并且订单累计满500元的用户

select user_id from es_order where totalamount>200 INTERSECT select user_id fromes_order group by user_id HAVING sum(totalamount) >500

示例3：获取没有订购过商品ID

select id from es_product MINUS selectprod_id from es_ordertail