数据库

Oracle数据库高级查询教程

位置:首页 > 数据库 > Oracle教程,2012-08-25
在一个SQL语句中嵌套另外一个sql语句称为子查询而这个查询语句作为另一个查询语句它的条件,其中包含其他sql语句的这个sql语句称为父查询示例如下如果需要查询商品类别中为图书的所有商品id,名称,价...

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

而这个查询语句作为另一个查询语句它的条件,其中包含其他sql语句的这个sql语句称为父查询

示例如下

如果需要查询商品类别中为图书的所有商品id,名称,价格

select id,name,price from es_product  
where sort_id =(  
select id from es_sort where sortname=  
'图书' )
在where条件中子查询的一般实现步骤如下:
1.我们得先知道需要查询的是什么,明确要显示的结果


比如说我们上述的就是id,name,price

 


2.结果来源于哪里

显然这些商品的相关信息来自于商品表es_product

 


3.需要的查询条件是什么

这里需要的是查询分类为图书,就是sort_id=(图书类别id)

 


4.条件中需要的值来源于哪里

它来自于一个子查询

sort_id=(SELECT id FROM es_sort WHERE sortname =' 图书')

 


5.最后子查询的sql语句就自然而然出来了

 


又例如,我们要查询以李青青为下单人的订单号,收货方式以及订单状态

1.明确显示的结果    id,payment,status

2.结果来自于哪里  es_order

3.需要的条件   user_id=('李青青'的id)

4.这个条件需要的值来自于一个子查询

 user_id =(select id from es_user where realname='李青青')

 


以上说的子查询都是返回的是单行结果,因此使用了=的操作符,

其实我们可以使用>\>=\<\<=\<>\!=的操作符


例如,我们要编写sql语句,查询大于商品平均价格的商品id,名称,价格

select id,name,price from es_product  
where price>(select avg(price) as 商品平均价格 from es_product)  
但是有时子查询返回的结果可能不止一行

比如要查询一号订单下的所有商品id,商品名称和价格

我们使用=操作符的时候,会报一个错误

 

 

 

我们应进行这样的修改

将=操作符改为in,即

select id,name,price from es_product  
where id in(select prod_id from es_orderdetail where order_id=1)
除了使用in我们还可以使用any,all,exists


另外我们需要查询商品表中前5条商品的id,商品名称以及上架时间

要实现这个需求,我们首先要知道有ROWNUM这个内容

 


知道了ROWNUM,SQL就出来了

select id,name,saledate from es_product where rownum<=5 
需求继续升级,我们这次要查询最新上架的前五条商品的记录,很可能大家会这样写

 


但这是错误的,错误的原因是

select的执行顺序

它总是先执行where子句再执行order by语句

而我们的要求是先对表的记录进行排序,再取前五条记录

其思路如下

 


如果有排序,先把排序后的表当成一个虚拟表,再进行操作

上述需求的SQL语句如下

select id,name,saledate,rownum from(select * from es_product order by saledate desc) where rownum<=5
select id,name,saledate,rownum from(select * from es_product order by saledate desc) where rownum<=5切记rownum是动态生成的,在表物理中不存在,所以诸如saledate.rownum并不存在

其中需要的需求如下

 

当到了第二页

我们使用这样的语句

 

其实是错误的,因为rownum是动态生成每一次的查询都不满足条件,导致-
 


不断地生成rownum=1进行查询,所有这个sql显示出来没有结果

所以我们就要将rownum固化

 

通过限制rn的范围取记录

 

另外一个更高级的需求例子

 

要记住rownum取值永远从1开始,它是从每次查询过程中动态生成的

现在我们有这样一个需求

查询所有用户的用户姓名,电话,订单号,已经订单状态,商品所属分类名称

其实我们这些数据来源于两张表

图书表es_product和图书类别表es_sort

我们可以通过表连接实现

select es_product.id,name,price,sortname from es_product,es_sort where es_product.sort_id=es_sort.id and es_sortname='图书'

或者

select a.id,a.name,a.price,b.sortname from es_product a,es_sort b where a.sort_id =b.id and b.sortname='图书'

那什么时候用子查询,什么时候用表查询呢

其实子查询并不要求两个表有相关字段,只要得到子查询的结果结果集就可以,用于父查询。

而连接查询,则必须要求两个表有相关的字段。当查询的列来自于多个表时,可以使用表连接查询

其中表连接包括以下连接

 

非等值连接示例如下;

 

另外一个需求如下

查询所有用户的电话,姓名,订单号,订购日期以及订单状态

 

使用这样一个内连接就是错误的,因为

它只是显示了下过订单的用户资料,而没下过订单的就漏掉了

也就是说当不满足条件的列也要显示处理。

只限制其中一个表的行,而不限制另外一个表的行时使用外连接

在Oracle中使用+来表示外连接是最常见的写法

达到上述需求我们可以这样书写

select a.realname,b.tel,b.id,createtime,status from es_user a leef outer join es_order b on a.id=b.user_id 

还可以这样写

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

当左表存在这条记录,而右表不匹配的时候

 

而右连接是以右表来匹配

这个时候也可以写+,不过这个时候的+应该写在条件的左边了


查询出高于本类商品平均价格的商品类别id,商品id,商品名称,价格,库存量

1.确定要查询的数据以及数据来源

select sort_id,id,name,price,stockcount from es_product

where price>(本类商品平均价格)

2.每类商品平均价格如下

(select sort_id,avg(price) avgprice from es_product-
 


group by sort_id)b

select * from es_product a;

3.将a和b进行连接查询,条件:a.sort_id=b.sort_id同时a.price>b.avgprice

select a.sort_id,id,name,price,stockcount from es_product a  ,

(select sort_id,avg(price) avgprice from es_product

group by sort_id)b

where a.sort_id=b.sort_id and a.price>b.avgprice

需要注意的是

当使用from 子句中使用子查询时,子查询将被作为虚拟表(视图)对待,该虚拟表接下来需要使用时,

必须给该子查询指定别名


现实需求有时候需要将多个查询组合到一个查询中去

这时就需要使用集合查询操作了

这个操作类似于数学中的交集,并集,和补集的操作

交集就是返回两个查询共有的记录,关键字是INTERSECT

并集是返回各个查询的所有记录,关键字为UNION或者UNION ALL

补集就是返回第一个查询检索出的记录减去第二个查询检索出的记录之后的剩余记录,关键字是MINUS-
 


分完组之后筛选只能使用having关键字,而不能使用where

各种示例如下:

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

 

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

 

获取没有被订购过的商品ID

 

需要注意的是:

UNION和UNION ALL都可以用在集合查询的并集操作,不过后者比前者效率高

因为UNION查询出的内容剔除重复行,同时默认按第一个查询的第一列升序排列

而UNION ALL不做这些,在业务要求允许时,最好使用效率高的UNION ALL

使用上述关键字连接两个select语句的时候,查询的列数和列的数据类型要一致

UNION在做重复行查询时,不忽略空值,如果有重复的空行则保留一个空行

INTERSECT以及MINUS查询也无重复行,按第一个查询的第一列升序排列

 

TAGS:Oracle数据库教程Oracle高级查询

猜你喜欢

NewHot