oracle--高级查询（一）

一常用的聚合函数 

      什么是聚合 ：将多行记录压缩成1行或者多行进行演示 

     1. count(*)  count(1) count(列名) 统计数据行的 个数 

     速度比较（oracle9i之前的说法）

        select count(*) --是最慢的  统计表的行数 先去数据库中了解表的结构

        select count(1) --第二慢的  找到数据库中表的一列

        select count(emp.empno) from emp; --第三慢的 因为已经指定了列名 不需要查表结构

        select count(rowid); --rowid直接定位的物理地址

     2 sum(数字的列) 统计当前列的总和

     3 avg（数字类型的列） 同行当前指定列的平均数

         select sum(sal)/count(rowid) from emp;

         select avg(sal) from emp;

     4 max(任意 列) 统计当前列最大的那个值

     5 min(任意列) 统计当前列种最小的那个值
 

聚合函数可以嵌套使用，但这条语句必须是GROUP BY语句。且SELECT子句就只能有这个嵌套聚合函数。

示例：
求出部门平均月薪的最高值。
SELECT MAX(AVG(sal)) FROM emp GROUP BY deptno;
SELECT deptno,AVG(sal) FROM emp GROUP BY deptno HAVING avg(sal)=(SELECT MAX(AVG(sal)) FROM emp GROUP BY deptno);


思考：求出部门平均月薪最高的部门编号和平均月薪
   SELECT dname,av FROM dept JOIN  (SELECT deptno,avg(sal) av FROM emp
   GROUP BY deptno HAVING avg(sal)=(SELECT MAX(AVG(sal)) FROM emp GROUP BY deptno)) e ON dept.deptno=e.deptno;
 
二 分组
     1.什么是分组 :分组指定的条件  【相同的数据数据被分为一组】 最终查询的结果 就是分组的组数
  

  语法：

SELECT [DISTINCT] *|[列名 别名,…] 

FROM 表名 [WHERE 条件]  [GROUP BY 分组条件]

  注意：

如果查询中包含一个聚合函数，而所选择的列不在聚合函数中，那么这些列就必须出现在GROUP BY子句中。聚合函数不能出现在WHERE子句中。
    
      表名 Test
       A列  B列
       1  a
       1  b
       2  c
       2  d
       select A from  Test group by A
       --因为根据A这一列 相同的值被分为一组 最终被分为两组 只有两条被显示
         1
         2
       select A,B from Test group by A  --这是错误的
       两组都被压缩成一行
       1 组 存在a和b两个值  不能被压缩成 一行 只能使用聚合函数压缩成
             一行才能显示


     --查询部门中薪水最高的薪水
select * from emp order by deptno;
  
     2.排序 是针对分组产生 之后的结果在进行排序
      select deptno,max(sal) from emp group by deptno order by deptno


     3. having子句 是对分组之后的结果进行筛选
 
语法：
SELECT [DISTINCT] *|[列名 别名,…] 
FROM 表名
[WHERE 条件]
[GROUP BY 分组条件 [HAVING 组过滤条件]]

[ORDER BY 列名 [ASC]|[DESC], ...];

注意：
HAVING子句必须出现在GROUP BY语句之中。

示例

--查出部门最高的薪水>3000  

select deptno,max(sal) from emp group by deptno having max(sal)>3000;

显示出平均月薪大于2000的部门编号及平均月薪

SELECT deptno,AVG(sal) FROM emp GROUP BY deptno HAVING AVG(sal) > 2000;

员工表：

查询出不是总裁（PRESIDENT）的职位名以及该职位的员工月薪总和，

还要满足同职位的员工的月薪总和大于4500。输出结果按月薪的总和降序排列。采用分步完成。

1)职位不是总裁的职位名：SELECT job FROM emp WHERE job!='PRESIDENT';
2)按职位分组，求出月薪总和：
SELECT job,SUM(sal) FROM emp WHERE job!='PRESIDENT' GROUP BY job;
3)分组后过滤月薪总和大于4500：SELECT job,SUM(sal) FROM emp WHERE job!='PRESIDENT' 
  GROUP BY job HAVING SUM(sal) > 4500;
4)结果按月薪的总计降序排列：
SELECT job,SUM(sal) FROM emp WHERE job!='PRESIDENT' GROUP BY job HAVING SUM(sal) > 4500 ORDER BY SUM(sal) DESC;

三 表连接

多表查询: 使用单个select语句从多个表中取出相关的数据。也叫多表联合查询。
多表查询通常是建立在存在相互关系的主从(父子)表上的。
 
SQL92标准中多表连接的语法
SELECT [DISTINCT] *|[列名 别名,…] 
FROM 主表名 [别名]
JOIN_TYPE 从表名 [别名] ON 连接条件
WHERE 查询条件
GROUPBY 分组表达式
HAVING 组过滤条件
ORDERY BY 排序表达式;


内连接：通过条件关联后无法关联的数据丢弃  inner join   on


外连接：
(左外，右外) 以指定的表为主表 left/right join on  
能关联的显示，不能关联的也保留（主表）


全连接： 将左右外连接的结果合并，去重 full join on

1.内连接语法：通过条件关联后无法关联的数据丢弃  inner join   on

SELECT [DISTINCT] *|[列名 别名,…] 
FROM 主表名 别名
[INNER] JOIN 从表名 别名 ON 连接条件

[WHERE 条件];

列出所有连接表中与连接条件相匹配的数据行。使用表别名，可以简化语句。

内连接分类

等值连接：在连接条件中使用等号(=)运算符来比较被连接列的列值  连接条件的列名相同时，可以使用USING (列名)来简化。

非等值连接：在连接条件中使用除等号运算符以外的其它比较运算符来比较被连接的列的列值。

      !=、>、>=、<、<=、LIKE、BETWEEN AND、

查询出员工姓名及其所在的部门名。

SELECT ename,dname FROM emp e INNER JOIN dept d ON e.deptno=d.deptno

查询出月薪大于2000的员工姓名、月薪、受雇日期及其所在的部门名，输出结果按受雇日期排序。

SELECT ename,sal,hiredate,dname FROM emp INNER JOIN dept ON emp.deptno=dept.deptno WHERE emp.sal>2000 ORDER BY hiredate;

查询出每个员工的姓名、月薪以及月薪等级。
select ename,sal,grade from emp e INNER JOIN salgrade s on sal between s.loal and s.hisal;
查询出每个员工的姓名、职位、月薪、部门名称、部门位置、以及月薪的等级，结果按员工编号排序。

select ename,job,sal,dname,loc,grade from emp e 

INNER JOIN dept d ON e.depto=d.deptno

        INNER JOIN salgrade s ON e.sal between s.losal and s.hisal;

2.外连接语法：

(左外，右外) 以指定的表为主表 left/right join on  能关联的显示，不能关联的也保留（主表）

SELECT [DISTINCT] *|[列名 别名,…] 
FROM 主表名 别名
(LEFT|RIGHT|FULL) [OUTER] JOIN 从表名 别名 ON 连接条件
[WHERE 条件];

不仅列出所有连接表中与连接条件相匹配的数据行，还列出左表(左外连接)、右表(右外连接)或两个表(全外连接)中符合WHERE过滤条件的数据行。

查询出所有的部门编号、部门名及该部门下的所有员工的姓名。

SELECT dept.deptno,dname,ename FROM dept LEFT JOIN emp ON dept.deptno=emp.deptno;

查询出每个员工的编号、姓名、职位及它的领导的姓名及职位

SELECT e.empno,e.ename,e.job,m.ename,m.job FROM emp e LEFT JOIN emp m ON e.mgr=m.empno;

3.交叉连接：它返回所有连接表中所有数据行的笛卡尔积

SELECT … FROM join_table  CROSS JOIN join_table2;

     单表查询 -》多表连接查询 

   笛卡儿积【交叉连接】{1,2,3}交叉{4,5} 可以参考【笛卡儿积.png】
     1     
     2      4

     3      5

     最终产生的结果  14 15 24 25 34 35   交叉产生的记录数就是 两个集合的个数的乘积

  数据库表的笛卡儿积

    数据行的笛卡尔积
   select * from table1，table2
   
  举例说明 92语法的内连，左外连 ，右外连

     用户表（UserInfo）

     用户id 用户名
       1     test

       2     user

     文章表 (Article)

       文章id 文章内容 用户id
        1       java      1
        2       oracle    3 


    1  test   1  java  1
    1  test   2   oracle 3
    2  user   1   java  1

    2  user   2  oracle  3

    //86语法
    select * from userinfo u,arcticle a where u.用户id=a.用户id
    1  test   1  java  1

    //92语法

    内联 必须都满足条件才显示记录

        select * from userinfo  inner join arcticle a on u.用户id=a.用户id  --是86语法的改进 结果和86语法是相同 

     外联

         --左外连 以左边的表为主表 左表如果存在某条记录和右中所有的记录都无法关联 就保留一条记录

        select * from userinfo  left join arcticle a on u.用户id=a.用户id  

         1  test   1  java  1

         2  user   null null null

   建议 
     多表查询 需要先找到两张表的关系
     如果需要做多表连接查询 尽量使用 内联 找到两张表的关系  直接将条件 在on后  (如果有特殊情况 如果主表中的记录没有连接也要输出 只能使用左外连接)