oracle多表联合查询，统计查询，组函数，排序（orderby），分组过
滤（having。。。
⼀、多表联合查询
通过连接可以建⽴多表查询，多表查询的数据可以来⾃多个表，但是表之间必须有适当的连接条件。为了从多张表中查询，必须识别连接多张表的公共列。⼀般是在WHERE⼦句中⽤⽐较运算符指明连接的条件。
两个表连接有四种连接⽅式：
* 相等连接
* 不等连接(看作单表查询)
* 外连接
* ⾃连接(⾃关联)
1．相等连接
通过两个表具有相同意义的列，可以建⽴相等连接条件。使⽤相等连接进⾏两个表的查询时，只有连接列上在两个表中都出现且值相等的⾏才会出现在查询结果中
显⽰雇员名称和所在部门的编号和名称。
sql中union多表合并执⾏以下查询：
ame, b.id, b.dname
FROM employee a,dept b
WHERE a.id=b.id
说明：相等连接语句的格式要求是，在FROM从句中依次列出两个表的名称，在表的每个列前需要添加表名，⽤“.”分隔，表⽰列属于不同的表。在WHERE条件中要指明进⾏相等连接的列。
以上训练中，不在两个表中同时出现的列，前⾯的表名前缀可以省略。所以以上例⼦可以简化为如下的表⽰：
SELECT ename, b.id, dname
FROM employee a,dept b
WHERE a.id=b.id
2．外连接
在以上的例⼦中，相等连接有⼀个问题：如果某个雇员的部门还没有填写，即保留为空，那么该雇员在查询中就不会出现；或者某个部门还没有雇员，该部门在查询中也不会出现。
为了解决这个问题可以⽤外连，即除了显⽰满⾜相等连接条件的记录外，还显⽰那些不满⾜连接条件的⾏，不满⾜连接条件的⾏将显⽰在最后。外连操作符为(+)，它可以出现在相等连接条件的左侧或右侧。出现在左侧或右侧的含义不同，这⾥⽤如下的例⼦予以说明。
使⽤外连显⽰不满⾜相等条件的记录。
显⽰雇员名称和所在部门的编号和名称。
执⾏以下查询：
左连接⽅法⼀(推荐使⽤，简洁)：
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都会显⽰
3、⾃连接(⼀般⽤在树形权限结构中)
⾃连接就是⼀个表，同本⾝进⾏连接。对于⾃连接可以想像存在两个相同的表(表和表的副本)，可以通过不同的别名区别两个相同的表。
ame||' 的经理是 '||ame AS 雇员经理
FROM employee worker, employee manager
= pno;
------------
执⾏结果为：
1.SMITH 的经理是 FORD
2.ALLEN 的经理是 BLAKE
3.WARD 的经理是 BLAKE
注：在操作多表联合查询时，若出现以下情况，将形成笛卡尔积
– 联接条件被省略
– 联接条件⽆效
– 第⼀个表中的所有⾏被联接到第⼆个表中的所有⾏上
为了避免笛卡尔积，请始终包括有效的联接条件
何为笛卡尔积？
笛卡尔（Descartes）乘积⼜叫直积。假设集合A={a,b}，集合B={0,1,2}，则两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0), (b,1), (b,2)}。可以扩展到多个集合的情况。
⼆、统计查询
通常需要对数据进⾏统计，汇总出数据库的统计信息。
⽐如，我们可能想了解公司的总⼈数和总⼯资额，或各个部门的⼈数和⼯资额，这个功能可以由统计查询完成。
Oracle提供了⼀些函数来完成统计⼯作，这些函数称为组函数，组函数不同于前⾯介绍和使⽤的函数(单⾏函数)。组函数可以对分组的数据进⾏求和、求平均值等运算。组函数只能应⽤于SELECT⼦句、HAVING⼦句或ORDER BY⼦句中。组函数也可以称为统计函数。
组函数:
AVG：求平均值
COUNT：求计数值，返回⾮空⾏数，*表⽰返回所有⾏
MAX：求最⼤值
MIN：求最⼩值
SUM：求和
STDDEV：求标准偏差，是根据差的平⽅根得到的
VARIANCE：求统计⽅差
组函数中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
⼦查询
我们可能会提出这样的问题，在雇员中谁的⼯资最⾼，或者谁的⼯资⽐SCOTT⾼。通过把⼀个查询的结果作为另⼀个查询的⼀部分，可以实现这样的查询功能。
具体的讲：要查询⼯资⾼于SCOTT的雇员的名字和⼯资，必须通过两个步骤来完成，
第⼀步查询雇员SCOTT的⼯资，
第⼆步查询⼯资⾼于SCOTT的雇员。
第⼀个查询可以作为第⼆个查询的⼀部分出现在第⼆个查询的条件中，这就是⼦查询。出现在其他查询中的查询称为⼦查询，包含其他查询的查询称为主查询。
⼦查询⼀般出现在SELECT语句的WHERE⼦句中，Oracle也⽀持在FROM或HAVING⼦句中出现⼦查询。⼦查询⽐主查询先执⾏，结果作为主查询的条件，在书写上要⽤圆括号扩起来，并放在⽐较运算符的右侧。⼦查询可以嵌套使⽤，最⾥层的查询最先执⾏。⼦查询可以在SELECT、INSERT、UPDATE、DELETE等语句中使⽤。
⼦查询按照返回数据的类型可以分为单⾏⼦查询、多⾏⼦查询和多列⼦查询。
查询⽐SCOTT⼯资⾼的雇员名字和⼯资
SELECT ename, sal FROM employee
WHERE sal>(SELECT sal FROM employee WHERE empno=7788);
查询雇员表中排在第6～9位置上的雇员
SELECT ename,sal FROM (SELECT rownum as num,ename,sal FROM employee WHERE rownum<=9 ) WHERE num>=6;
说明：⼦查询出现在FROM从句中，检索出⾏号⼩于等于9的雇员，并⽣成num编号列。在主查询中检索⾏号⼤于等于6的雇员。
注意：以下⽤法不会有查询结果
SELECT ename,sal FROM employee
WHERE rownum>=6 AND rownum<=9
集合运算
多个查询语句的结果可以做集合运算，结果集的字段类型、数量和顺序应该⼀样。
Oracle共有4个集合操作
UNION：并集，合并两个操作的结果，去掉重复的部分
UNION ALL：并集，合并两个操作的结果，保留重复的部分
MINUS：差集，从前⾯的操作结果中去掉与后⾯操作结果相同的部分
INTERSECT：交集，取两个操作结果中相同的部分
查询部门10和部门20的所有职务。
SELECT  job FROM employee WHERE deptno=10
UNION
SELECT  job FROM emp WHERE deptno=20;
查询部门10和20中是否有相同的职务和⼯资。
SELECT  job,sal FROM employee WHERE deptno=10
INTERSECT
SELECT  job,sal FROM employee WHERE deptno=20
查询只在部门表中出现，但没有在雇员表中出现的部门编号
SELECT  deptno FROM dept
MINUS
SELECT  deptno FROM employee
-------------------------------------------------------------------------------------------
⼩结：
⼀、外连接
1、左连接  left join 或 left outer join
SQL语句：select * from student left join course on
student.ID=course.ID
左外连接包含left join左表所有⾏，如果左表中某⾏在右表没有匹配，则结果中对应⾏右表的部分全部为空(NULL)
2、右连接  right join 或 right outer join
SQL语句：select * from student right join course on
student.ID=course.ID
右外连接包含right join右表所有⾏，如果左表中某⾏在右表没有匹配，则结果中对应左表的部分全部为空(NULL)
⼆、内连接  join 或 inner join
SQL语句：select * from student inner join course on student.ID=course.ID
inner join 是⽐较运算符，只返回符合条件的⾏。
此时相当于：select * from student,course where
student.ID=course.ID

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。