（四）Oracle条件查询，分页查询

1. SQL（基础查询）

1.1基础查询

1.1.1. 使用LIKE条件（模糊查询）

当用户在执行查询时，不能完全确定某些信息的查询条件，或者只知道信息的一部分，可以借助LIKE来实现模糊查询。LIKE需要借助两个通配符：

  • %：表示0到多个字符
  • _：标识单个字符

这两个通配符可以配合使用，构造灵活的匹配条件。例如查询职员姓名中第二个字符是‘A’的员工信息：

  1. SELECT ename, job FROM emp WHERE ename LIKE '_A%';

1.1.2. 使用IN和NOT IN

在WHERE子句中可以用比较操作符IN(list)来取出符合列表范围中的数据。其中的参数list表示值列表，当列或表达式匹配于列表中的任何一个值时，条件为TRUE，该条记录则被显示出来。

IN页可以理解为一个范围比较操作符，只不过这个范围是一个指定的值列表，NOT IN(list) 取出不符合此列表中的数据记录。例如查询职位是MANAGER或者CLERK的员工：

  1. SELECT ename, job FROM emp WHERE job IN ('MANAGER', 'CLERK');

查询不是部门10或20的员工：

  1. SELECT ename, job FROM emp WHERE deptno NOT IN (10, 20);

1.1.3. BETWEEN…AND…

BETWEEN…AND…操作符用来查询符合某个值域范围条件的数据，最常见的是使用在数字类型的数据范围上，但对字符类型和日期类型数据也同样适用。例如查询薪水在1500-3000之间的职员信息：

  1. SELECT ename, sal FROM emp
  2. WHERE sal BETWEEN 1500 AND 3000;

1.1.4. 使用ANY和ALL条件

在比较运算符中，可以出现ALL和ANY，表示“全部”和“任一”，但是ALL和ANY不能单独使用，需要配合单行比较操作符>、>=、<、<=一起使用。其中：

  • > ANY(list) : 大于最小
  • < ANY(list)：小于最大
  • > ALL(list)：大于最大
  • < ALL(list)：小于最小

例如，查询薪水比职位是“SALESMAN”的人高的员工信息，比任意一个SALESMAN高都行：

  1. SELECT empno, ename, job, sal, deptno
  2. FROM emp
  3. WHERE sal> ANY (
  4. SELECT sal FROM emp WHERE job = 'SALESMAN');

1.1.5. 使用DISTINCT过滤重复

数据表中有可能存储相同数据的行，当执行查询操作时，默认情况会显示所有行，不管查询结果是否有重复的数据。当重复数据没有实际意义，经常会需要去掉重复值，使用DISTINCT实现。例如查询员工的部门编码，包含所有重复值：

  1. SELECT deptno FROM emp;

查询员工的部门编码，去掉重复值：

  1. SELECT DISTINCT deptno FROM emp;

DISTINCT后面的列可以组合查询，下例查询每个部门的职位，去掉重复值。注意是deptno和job联合起来不重复：

  1. SELECT DISTINCT deptno, job FROM emp;

1.2. 排序

1.2.1. 使用ORDER BY字句

对查询出的数据按一定规则进行排序操作，使用ORDER BY子句。语法如下：

  1. SELECT <*, column [alias], …>
  2. FROM table
  3. [WHERE condition(s)]
  4. [ORDER BY column [ASC | DESC]] ;

注意，ORDER BY必须出现在SELECT中的最后一个子句。下例对职员表按薪水排序：

  1. SELECT ename, sal
  2. FROM emp
  3. ORDER BY sal;

1.2.2. ASC和DESC

排序时默认按升序排列，即由小及大，ASC用来指定升序排序，DESC用来指定降序排序。

因为NULL值视作最大，则升序排列时，排在最后，降序排列时，排在最前。如果不写ASC或DESC，默认是ASC，升序排列。例如，按员工的经理升序排序：

  1. SELECT empno, ename, mgr FROM emp
  2. WHERE deptno = 10 ORDER BY mgr;

降序排列，必须指明，按员工的薪水倒序排序：

  1. SELECT ename, sal FROM emp
  2. ORDER BY sal DESC;

1.2.3. 多个列排序

当以多列作为排序标准时，首先按照第一列进行排序，如果第一列数据相同，再以第二列排序，以此类推。多列排序时，不管正序还是倒序，每个列需要单独设置排序方式。

下例对职员表中的职员排序，先按照部门编码正序排列，再按照薪水降序排列：

  1. SELECT ename, deptno, sal FROM emp
  2. ORDER BY deptno ASC, sal DESC;

1.3. 聚合函数

1.3.1. MAX和MIN

用来取得列或表达式的最大、最小值，可以用来统计任何数据类型，包括数字、字符和日期。例如获取机构下的最高薪水和最低薪水，参数是数字：

  1. SELECT MAX(sal) max_sal, MIN(sal) min_sal
  2. FROM emp;

计算最早和最晚的入职时间，参数是日期：

  1. SELECT MAX(hiredate) max_hire, MIN(hiredate) min_hire
  2. FROM emp;

1.3.2. AVG和SUM

AVG和SUM函数用来统计列或表达式的平均值和和值，这两个函数只能操作数字类型，并忽略NULL值。例如获得机构下全部职员的平均薪水和薪水总和：

  1. SELECT AVG(sal) avg_sal, SUM(sal) sum_sal FROM emp;

1.3.3. COUNT

COUNT函数用来计算表中的记录条数，同样忽略NULL值。例如获取职员表中一共有多少名职员记录：

  1. SELECT COUNT(*) total_num FROM emp;

获得职员表中有多少人是有职位的（忽略没有职位的员工记录）

  1. SELECT COUNT(job) total_job FROM emp;

1.3.4. 聚合函数对空值的处理

聚合函数忽略NULL值。即当emp表中的某列有NULL值，比如某新入职员工没有薪水，比较两条语句的结果：

  1. SELECT AVG(sal) avg_sal FROM emp;
  2. SELECT AVG(NVL(sal,0)) avg_sal FROM emp;

1.4. 分组

1.4.1. GROUP BY子句

上面的例子都是以整个表作为一组。如果希望得到每个部门的平均薪水，而不是整个机构的平均薪水，需要把整个数据表按部门划分成一个个小组，每个小组中包含一行或多行数据，在每个小组中再使用分组函数进行计算，每组返回一个结果。语法如下：

  1. SELECT <*, column [alias], …>
  2. FROM table [WHERE condition(s)]
  3. [GROUP BY group_by_expression]
  4. [HAVING group_condition]
  5. [ORDER BY column [ASC | DESC]] ;

其中划分的小组有多少，最终的结果集行数就有多少。

1.4.2. 分组查询

　　　　　

 

1.4.3. HAVING字句

HAVING子句用来对分组后的结果进一步限制，比如按部门分组后，得到每个部门的最高薪水，可以继续限制输出结果。必须跟在GROUP BY后面，不能单独存在。例如查询每个部门的最高薪水，只有最高薪水大于4000的记录才被输出显示：

  1. SELECT deptno, MAX(sal) max_sal FROM emp
  2. GROUP BY deptno HAVING MAX(sal) >4000;

2. SQL（关联查询）

2.1. 关联基础

2.1.1. 关联的概念

实际应用中所需要的数据，经常会需要查询两个或两个以上的表。这种查询两个或两个以上数据表或视图的查询叫做连接查询，连接查询通常建立在存在相互关系的父子表之间。语法如下：

  1. SELECT table1.column, table2.column
  2. FROM table1, table2
  3. WHERE table1.column1 = table2.column2;

或者：

  1. SELECT table1.column, table2.column
  2. FROM table1JOIN table2
  3. ON(table1.column1 = table2.column2);

2.1.2. 笛卡尔积

笛卡尔积指做关联操作的每个表的每一行都和其它表的每一行做组合，假设两个表的记录条数分别是X和Y，笛卡尔积将返回X * Y条记录。当两个表关联查询时，不写连接条件，得到的结果即是笛卡尔积。例如：

  1. SELECT COUNT(*) FROM emp; --14条记录
  2. SELECT COUNT(*) FROM dept; --4条记录
  3. SELECT emp.ename, dept.dnameFROM emp, dept;--56条记录

2.1.3. 等值连接

等值连接是连接查询中最常见的一种，通常是在有主外键关联关系的表间建立，并将连接条件设定为有关系的列，使用等号”=”连接相关的表。例如查询职员的姓名、职位以及所在部门的名字和所在城市，使用两个相关的列做等值操作：

  1. SELECT e.ename, e.job, d.dname, d.loc
  2. FROM emp e, dept d
  3. WHERE e.deptno = d.deptno;

2.2. 关联查询

2.2.1. 内连接

内连接返回两个关联表中所有满足连接条件的记录。例如查询员工的名字和所在部门的名字：

  1. SELECT e.ename, d.dname
  2. FROM emp e, dept d
  3. WHERE e.deptno = d.deptno

上面的语法也可以写为：

  1. SELECT e.ename, d.dname
  2. FROM emp e JOIN dept d
  3. ON(e.deptno = d.deptno);

2.2.2. 外连接

内连接返回两个表中所有满足连接条件的数据记录，在有些情况下，需要返回那些不满足连接条件的记录，需要使用外连接，即不仅返回满足连接条件的记录，还将返回不满足连接条件的记录。比如把没有职员的部门和没有部门的职员查出来。外连接的语法如下：

  1. SELECT table1.column, table2.column
  2. FROM table1 [LEFT | RIGHT | FULL] JOIN table2
  3. ON table1.column1 = table2.column2;

外连接查询的例子,Emp表做驱动表:

  1. SELECT e.ename, d.dname
  2. FROM emp e LEFT OUTER JOIN dept d
  3. ON e.deptno = d.deptno;

Dept表做驱动表:

  1. SELECT e.ename, d.dname
  2. FROM emp e RIGHT OUTER JOIN dept d
  3. ON e.deptno = d.deptno;

2.2.3. 全连接

全外连接是指除了返回两个表中满足连接条件的记录，还会返回不满足连接条件的所有其它行。即是左外连接和右外连接查询结果的总和。例如：

  1. SELECT e.ename, d.dname
  2. FROM emp e FULL OUTER JOIN dept d
  3. ON e.deptno = d.deptno;

2.2.4. 自连接

自连接是一种特殊的连接查询，数据的来源是一个表，即关联关系来自于单表中的多个列。表中的列参照同一个表中的其它列的情况称作自参照表。

自连接是通过将表用别名虚拟成两个表的方式实现，可以是等值或不等值连接。例如查出每个职员的经理名字，以及他们的职员编码：

  1. SELECT worker.empnow_empno, worker.enamew_ename, manager.empnom_empno, manager.enamem_ename
  2. FROM emp worker join emp manager
  3. ON worker.mgr = manager.empno;

3. SQL(高级查询)

1.1. 子查询

在SELECT查询中，在WHERE查询条件中的限制条件不是一个确定的值，而是来自于另外一个查询的结果。为了给查询提供数据而首先执行的查询语句叫做子查询。

子查询：嵌入在其它SQL语句中的SELECT语句，大部分时候出现在WHERE子句中。子查询嵌入的语句称作主查询或父查询。主查询可以是SELECT语句，也可以是其它类型的语句比如DML或DDL语句。 

例如查找和SCOTT同职位的员工：

  1. SELECT e.ename, e.job
  2. FROM emp e
  3. WHERE e.job =
  4. (SELECT job FROM emp WHERE ename = 'SCOTT')；

查找薪水比整个机构平均薪水高的员工：

  1. SELECT deptno, ename, sal
  2. FROM emp e
  3. WHERE sal> (SELECT AVG(sal) FROM emp);
/*查询Emp表工作SALESMAN的部门号同时工作不能使SALESMAN*/
SELECT empno, ename, job, sal, deptno FROM emp WHERE deptno IN (SELECT deptno FROM emp WHERE job = 'SALESMAN') AND job <> 'SALESMAN';

/*

有两个简单例子，以说明 “exists”和“in”的效率问题

1) select * from T1 where exists(select 1 from T2 where T1.a=T2.a) ;

    T1数据量小而T2数据量非常大时，T1<<T2 时，1) 的查询效率高。

2) select * from T1 where T1.a in (select T2.a from T2) ;

     T1数据量非常大而T2数据量小时，T1>>T2 时，2) 的查询效率高。

exists 用法：

请注意 1）句中的有颜色字体的部分 ，理解其含义；

其中 “select 1 from T2 where T1.a=T2.a” 相当于一个关联表查询，相当于

“select 1 from T1,T2     where T1.a=T2.a”

但是，如果你当当执行 1） 句括号里的语句，是会报语法错误的，这也是使用exists需要注意的地方。

“exists（xxx）”就表示括号里的语句能不能查出记录，它要查的记录是否存在。

因此“select 1”这里的 “1”其实是无关紧要的，换成“*”也没问题，它只在乎括号里的数据能不能查找出来，是否存在这样的记录，如果存在，这 1） 句的where 条件成立。

 
*/

SELECT deptno, dname FROM dept d WHERE EXISTS (SELECT * FROM emp e WHERE d.deptno = e.deptno);

SELECT deptno, MIN(sal) min_sal FROM emp GROUP BY deptno HAVING MIN(sal) > (SELECT MIN(sal) FROM emp WHERE deptno = 30);
SELECT e.deptno, e.ename, e.sal FROM emp e, (SELECT deptno, AVG(sal) avg_sal FROM emp GROUP BY deptno) x 

WHERE e.deptno = x.deptno and e.sal>x.avg_sal ORDER BY e.deptno;

SELECT e.ename, e.sal, e.deptno,(SELECT d.deptno FROM dept d WHERE d.deptno = e.deptno)  deptno FROM emp e;

 

1.2. 分页查询

1.2.1. ROWNUM

ROWNUM被称作伪列，用于返回标识行数据顺序的数字。例如：

  1. SELECT ROWNUM, empno, ename, sal
  2. FROM emp;

ROWNUM只能从1计数，不能从结果集中直接截取。下面的查询语句将没有结果：

  1. SELECT ROWNUM, empno, ename, sal
  2. FROM emp
  3. WHERE rownum> 3;

如果利用ROWNUM截取结果集中的部分数据，需要用到行内视图：

  1. SELECT * FROM
  2. (SELECT ROWNUMrn , e.* FROM emp e )
  3. WHERE rn BETWEEN 8 AND 10;

 

/*在查询的最外层控制分页的最小值和最大值。查询语句如下：*/
select * from (select Rownum rn ,e.* from emp e) where rn between 5 and 8;
/*分页查询格式2绝大多数的情况下，第2个查询的效率比第1个高得多。*/
select*from (select Rownum rn ,E.*from (select*from emp) E where rowum <=8 ) where rn >=5;

 

 

 

1.2.2. 使用子查询进行分页

分页策略是指每次只取一页的数据。当每次换页，取下一页的数据。在ORACLE中利用ROWNUM的功能来进行分页。

假设结果集共105条，每20条分为一页，则共6页：

Page1： 1 至 20

Page2： 21 至40

…

PageN: (n - 1) * pageSize + 1 至 n * pageSize

1.2.3. 分页与ORDER BY

按薪水倒序排列，取出结果集中第8到第10条的记录：

  1. SELECT * FROM
  2. (SELECT ROWNUMrn , t.* FROM
  3. (SELECT empno,ename,sal FROM emp ORDER BY sal DESC) t)
  4. WHERE rn BETWEEN 8 AND 10;

根据要查看的页数，计算起点值（(n - 1) * pageSize + 1）和终点值（n * pageSize），替换掉BETWEEN和AND的参数，即得到当前页的记录。

  • 则对(a)进行2次GROUP BY, GROUPING SETS的参数允许重复
posted @ 2017-02-04 17:05  珈子  阅读(979)  评论(0)  编辑  收藏  举报