Oracle 多表查询 ,聚合函数以及分页

今天药忘吃喽~ 2024-02-17 18:02 94阅读 0赞

**一、多表联合查询**   
子查询 - 将一个查询包含到另一个查询中  
连 接 - 合并多个数据表中的列  
联 合 - 合并多个数据表中的行

**(1)子查询**

在雇员中谁的工资最高，或者谁的工资比SCOTT高。通过把一个查询的结果作为另一个查询的一部分，可以实现这样的查询功能。  
具体的讲：要查询工资高于SCOTT的雇员的名字和工资，必须通过两个步骤来完成，  
第一步查询雇员SCOTT的工资，  
第二步查询工资高于SCOTT的雇员。  
第一个查询可以作为第二个查询的一部分出现在第二个查询的条件中，这就是子查询。出现在其他查询中的查询称为子查询，包含其他查询的查询称为主查询。   
  
 子查询一般出现在SELECT语句的WHERE子句中，Oracle也支持在FROM或HAVING子句中出现子查询。子查询比主查询先执行，结果作为主查询的条件，在书写上要用圆括号扩起来，并放在比较运算符的右侧。子查询可以嵌套使用，最里层的查询最先执行。子查询可以在SELECT、INSERT、UPDATE、DELETE等语句中使用。   
 子查询按照返回数据的类型可以分为**单行子查询、多行子查询和多列子查询。**  
  
查询比SCOTT工资高的雇员名字和工资  
SELECT ename, sal FROM emp WHERE sal>(SELECT sal FROM emp WHERE empno=7788);

\--\*\*where exists 是否存在 (select \* from ...)  
select dname from dept d where exists (select \* from emp where d.deptno = emp.deptno and sal>3000)

查询雇员表中排在第5～9位置上的雇员 **rownum是伪列**  
SELECT ename,sal FROM (SELECT rownum as num,ename,sal FROM employee WHERE rownum<10 ) WHERE num>=5;

\--分析函数也可以查询  
select \* from (select ee.\*,rownum r from (select \* from emp order by sal desc) ee) where r>=5 and r<10;

(2) 连接查询--两个表连接有四种连接方式：   
\* 相等连接 只有连接列上在两个表中都出现且值相等的行才会出现在查询结果中

显示雇员名称,职位,工资和所在部门的编号和名称。

select \* from emp; --14  
select \*from dept; --4  
\--等值是 14  
select ename,job,sal,dept.dname from emp inner join dept on dept.deptno = emp.deptno  
  
select ename,job,sal,dept.dname from emp , dept where dept.deptno = emp.deptno

\* 不等连接(看作单表查询)

\--不等值是 =14\*4-14=42  
select ename,job,sal,dept.dname from emp inner join dept on dept.deptno <> emp.deptno  
select ename,job,sal,dept.dname from emp inner join dept on dept.deptno != emp.deptno  
  
select ename,job,sal,dept.dname from emp , dept where dept.deptno != emp.deptno

\* 外连接

左连接方法一(推荐使用，简洁)：

SELECT ename, b.id, dname FROM employee a,dept b WHERE a.id(+)=b.id

左连接方法二：  
SELECT ename, b.id, dname FROM dept b LEFT JOIN employee a ON a.id=b.id  
注意：不管dept是否存在，employee都会显示

\* 自连接(自关联)(一般用在树形权限结构中)

自连接就是一个表，同本身进行连接。对于自连接可以想像存在两个相同的表(表和表的副本)，可以通过不同的别名区别两个相同的表。

SELECT worker.ename||' 的经理是 '||manager.ename AS 雇员经理 FROM employee worker, employee manager   
WHERE worker.mgr = manager.empno;

注：在操作多表联合查询时，若出现以下情况，将形成笛卡尔积  
– 联接条件被省略  
– 联接条件无效  
– 第一个表中的所有行被联接到第二个表中的所有行上  
为了避免笛卡尔积，请始终包括有效的联接条件  
  
何为笛卡尔积？  
笛卡尔（Descartes）乘积又叫直积。假设集合A=\{a,b\}，集合B=\{0,1,2\}，则两个集合的笛卡尔积为\{(a,0),(a,1),(a,2),(b,0),(b,1), (b,2)\}。可以扩展到多个集合的情况。

**(3)联合查询**

\--复制新表  
create table myemp as select \* from emp where empno in (7788,7900);

UNION:并集，合并两个操作的结果，去掉重复的部分

select \* from emp --14  
union  
 select \* from myemp; --2

UNION ALL:并集，合并两个操作的结果，保留重复的部分

select \* from emp --14  
 union all  
 select \* from myemp; --2

MINUS:差集，从前面的操作结果中去掉与后面操作结果相同的部分

select \* from emp --14  
MINUS  
select \* from myemp; --2

INTERSECT:交集，取两个操作结果中相同的部分   
select \* from emp --14  
 intersect  
select \* from myemp; --2

**二、统计查询**  
通常需要对数据进行统计，汇总出数据库的统计信息。  
比如，我们可能想了解公司的总人数和总工资额，或各个部门的人数和工资额，这个功能可以由统计查询完成。  
Oracle提供了一些函数来完成统计工作，这些函数称为组函数，组函数不同于前面介绍和使用的函数(单行函数)。组函数可以对分组的数据进行求和、求平均值等运算。组函数只能应用于SELECT子句、HAVING子句或ORDER BY子句中。组函数也可以称为统计函数。  
  
组函数:  
AVG:求平均值  
COUNT:求计数值，返回非空行数，\*表示返回所有行  
MAX:求最大值  
MIN:求最小值  
SUM:求和

组函数中SUM和AVG只应用于数值型的列，MAX、MIN和COUNT可以应用于字符、数值和日期类型的列。组函数忽略列的空值。  
使用GROUP BY从句可以对数据进行分组。所谓分组，就是按照列的相同内容，将记录划分成组，对组可以应用组函数。  
如果不使用分组，将对整个表或满足条件的记录应用组函数。  
在组函数中可使用DISTINCT或ALL关键字。ALL表示对所有非NULL值(可重复)进行运算(COUNT除外)。DISTINCT表示对每一个非NULL值，如果存在重复值，则组函数只运算一次。如果不指明上述关键字，默认为ALL。  
  
求雇员表中不同职务的个数  
SELECT COUNT(DISTINCT job) FROM employee

按职务统计工资总和  
SELECT job,SUM(sal) FROM employee GROUP BY job

按部门和职务分组统计工资总和  
SELECT deptno, job, sum(sal) FROM employee GROUP BY deptno, job;

统计各部门的最高工资，排除最高工资小于3000的部门。  
SELECT deptno, max(sal) FROM employee GROUP BY deptno HAVING max(sal)>=3000;

按职务统计工资总和并排序  
SELECT job 职务, SUM(sal) 工资总和 FROM employee GROUP BY job ORDER BY SUM(sal);

求各部门平均工资的最高值  
SELECT max(avg(sal)) FROM employee GROUP BY deptno  
  
**三 分页**  
  
\--1)每页显示5个 pageSize=5;  
\--2)总个数是14条 pageCount=14;  
\--3)总页数 3 pageTotal = (14%5==0)?(14/5):(14/5+1);  
\--4)当前页1 currentPage=1;  
  
  
\--第1页 rownum<=5 ==>pageSize\*currentPage  
select \* from  
(select ee.\*,rownum r from (select \* from emp order by sal desc) ee where rownum<=5 )  
where r>0 --r>0 ==>(currentPage-1)\*pageSize  
  
\--第2页  
select \* from  
(select ee.\*,rownum r from (select \* from emp order by sal desc) ee where rownum<=10 )  
where r>5  
  
  
\--第3页  
select \* from  
(select ee.\*,rownum r from (select \* from emp order by sal desc) ee where rownum<=15 )  
where r>10