Oracle单表查询（二）

1.1在where子句中引用取别名的列

       有时候在做报表的时候，会加上一些条件筛选，直接在别名上加判断会比在列名上加判断更加直观。例如我们要查询工资大于1500的人的工资和提成。

写法如下：

select * from (select SAL AS 工资,coalesce(COMM ,0)AS 提成  from EMP) x WHERE x.工资>1500

 

要注意此处的()不可缺少，因为别名是在执行完

‘select SAL AS 工资,coalesce(COMM ,0)AS 提成  from EMP’这条语句以后才生成的，如果不使用()则会出现：

 

 

1.2限制返回的行数

        例如我们要做一个分页查询，就可以使用rownum这个伪列来过滤，rownum会对查询出的每条数据做一个标识。

先看一下rownum的简单用法，

select * FROM EMP WHERE ROWNUM<5

 

按照正常的逻辑应该是返回第一到第四条数据，没有问题。现在我们想查一下rownum大于3的，照刚才的经验应该是

 

select * FROM EMP WHERE ROWNUM>3;

 

nothing here，这是因为rownum是一个伪列，他会对所查出来的数据排名，只有有了第一个，才会有第二个，所以我们需要把所有的数据取出来才能确认名次。
正确的写法如下：

SELECT * FROM (select ROWNUM AS r,emp.* FROM EMP) e WHERE e.r>3

select ROWNUM AS r,emp.* FROM EMP这句SQL首先将EMP表中所有的数据以及他们对应的ROWNUM取出来了，这时我们就可以从这里面判断哪些行是rownum大于3的了。

这样就可以实现模拟分页功能了，例如我们每次想查三条数据（pageSize），想要显示第二页(pageNumber)，这样就是从[pageSize*(pageNumber-1)+1]到

（pageSize*pageNumber），也就是4~6条。SQL如下：

SELECT * FROM (select ROWNUM AS r,emp.* FROM EMP WHERE ROWNUM<=6) e WHERE e.r>=4

 

1.3从表中随即返回N条数据

我们可以使用bdms_random来对数据随机排列，然后再取三条查看一下

先这样写一条，并多次查询看看

SELECT ENAME,EMPNO FROM EMP WHERE ROWNUM<=3 ORDER BY dbms_random.value

 

 

 

可能大家发现了，每次取出来的都是这三条只不过是这三条的顺序不一样而已，那我们就需要改进。正确写法如下：

SELECT EMPNO,ENAME FROM (SELECT EMPNO,ENAME FROM EMP ORDER BY dbms_random.value) WHERE ROWNUM<4；

查询语句的执行顺序应为：

select最先
rownum次之
order by最后
也就是说先查询出全部，然后给全部的数据执行随机排列，最后取出排在最前面的三个。我们第一个写的相当于是，查出前三条数据，然后对三条数据进行随机排列，显然是不符合要求的。

1.4模糊查询

首先我们创建一个视图

CREATE OR REPLACE VIEW V2 AS
SELECT 'ABCDEF' AS vname from dual
  UNION ALL
SELECT '_BCEFG' AS vname from dual
  UNION ALL
SELECT '_BCEDF' AS vname from dual
  UNION ALL
SELECT '_\BCEDF' AS vname from dual
  UNION ALL
SELECT 'XYCEG' AS vname FROM dual;

查询视图中的所有内容

SELECT * from V2;

 

 

 1.查询出所有包含字符“CED”的

SELECT * FROM V2 WHERE VNAME LIKE '%CED%'

 

2.查询包含字符“_BCE”的，

SELECT * FROM V2 WHERE VNAME LIKE '%_BCE%'

 

       这时发现多了一个“ABCEDF”和“_\BCEDF”这时因为LIKE子句中有两个通配符“%”表示替代0个或多个字符，“_”表示替代一个字符。

在这里“_”被当做通配符了，我们可以使用转义字符。

SELECT * FROM V2 WHERE VNAME LIKE '%\_BCE%' ESCAPE '\'
escape把‘\’标识为转义符，‘\’把‘_’转义为普通字符。

那如果要查询包含_\BCEDF的呢（查询包含转义字符的内容）

只需双写转义字符即可：

SELECT * FROM V2 WHERE VNAME LIKE '%\_\\BCE%' ESCAPE '\'

 

双写处表示此处确实有'\'这个字符，不双写的依然作为转义字符。

对于“%”，跟“_”的处理方法是一样的。