Oracle入门笔记(四)

一、排序

排序： order by 列1 asc/desc ,列2..  asc/desc
   * asc 默认
   * desc

    -- 查询员工的信息，按照由高到低排序
    select  e.* from emp e order by sal;

    select  e.* from emp e order by sal,empno desc;
        * 先以sal列的升序排序，再按照empno降序排序

    -- 查询工作种类为CLERK的员工信息，按照工资降序排序
    select * from emp where job='CLERK' order by sal
 

二、分组

分组： group by
  * 将一个列上数据相同的记录划分为一个范围（组），
    在统一范围中取得最值（max min sum avg count ）
  * 如果查询语句中出现分组的关键字，
    那么select之后可以有 普通的列和聚组函数 两种写法
     切记： 这个普通的列一定是分组的依据列。
  * 分组之前的条件筛选，使用where
    分组之后条件筛选，使用having

-- 按照员工部门分组,查询每个部门的平均工资
    select deptno 部门编号,avg(sal) 平均工资
            from emp group by deptno;

-- 查询高收入群体(工资>2000$)中部门的平均工资
  1 select * from emp where sal>2000
  2 在1的基础上：通过部门编号分组
     select ? from emp
        where sal>2000
        group by deptno
  3 select之后可以出现哪些数据？
     select deptno,avg(sal) from emp
        where sal>2000
        group by deptno


-- 查询高收入群体的所在部门的总成本>5000的部门平均工资
 1 select * from emp where sal>2000
 2 按照部门分组
     select deptno,avg(sal) from emp
        where sal>2000
        group by deptno
 
 3 过滤总成本>5000，需要用到求和
   使用having在分组之后做条件过滤：

select deptno,avg(sal),sum(sal) from emp
     where sal>2000
        group by deptno having sum(sal)>5000;


带有排序的写法：
  select deptno,avg(sal),sum(sal) from emp
        where sal>2000
            group by deptno having sum(sal)>5000
                 order by sum(sal) asc;


  select a.* from
    ( select deptno,avg(sal),sum(sal) ss from emp
          where sal>2000
          group by deptno )a where a.ss>5000;

总结SELECT中使用到的关键字以及优先等级：

select from where group by having order by

优先级最高： from 表名
      其次：  where 条件（普通列/单行函数）
      第三：  group by 分组列1,列2...
                 having 条件（分组后条件过滤）
      最后：  order by 排序列
                select 显示的列

   * 如果查询语句中使用到了分组：
      那么 select 、having 和order by 之后，
        只能出现分组的列或者其他列的多行函数形式

----------------------------------------------------
Exec:

-- 实现成绩表point 的行转列：
 select sid,name,
   max(decode(subject,'语文',score,0)) 语文,
   sum(decode(subject,'数学',score,0)) 数学,
   max(decode(subject,'英语',score)) 英语
    from point group by sid,name;

--- 实现商品的统计： Table: product
   编号pid   名称pname 所在仓库cid   数量cnum
     10        篮球     1             15
     11        足球     1             10
     12        羽毛球   2             20
     13        网球拍   4             20
     14        篮球     3             10
     15        羽毛球   4             15
     16        篮球     1             15

 select  pname,
     sum(decode(cid,1,cnum,0)) "1号仓库",
     sum(decode(cid,2,cnum,0)) "2号仓库",
     sum(decode(cid,3,cnum,0)) "3号仓库",
     sum(decode(cid,4,cnum,0)) "4号仓库"
   from product group by pname;

  实现效果如下：
     商品名称   1号仓库   2号仓库  3号仓库  4号仓库
        篮球     30           0       10       0
    足球     10           0        0       0
    羽毛球   0            20       0       15
    网球拍   0             0       0       20

三、联合查询

联合函数：
   * 可以将多个查询语句联合成为一个查询
   * 必须保证多个查询语句中的列数一致

union all : 联合
   * 如果多个查询语句中出现重复的数据，
     则重复数据计算多次

select ename from emp where ename like '%E%'
union all
select job from emp where ename like '%S%';

union ：联合
   * 多个查询语句中如果出现重复数据，
     则重复数据算作一次

*** 效率比较：
    union all的效率比较高

minus ：差集
   * 从第一个查询的结果中，
      减去第二个查询结果中重复出现的数据
select * from emp where ename like '%E%'
minus
select * from emp where ename like '%S%';

intersect ： 交集
select * from emp where ename like '%E%'
intersect
select * from emp where ename like '%S%';

四、多表连接

 

多表连接：
   * 有很多情况，数据不再同一张表中，需要将多个表中的
     数据级联成为一个查询结果

 笛卡尔积：
 {1,2,3} * {a,b} 
             ==> {1,a} {2,a} {3,a} {1,b} {2,b} {3,b}    
         
―-- 查询员工信息及每个员工对应的部门名
select e.* ,d.*
   from emp e,dept d where e.deptno = d.deptno;

  ** e.deptno = d.deptno
     这种条件判断等式我们称作关联关系
  ** 有了多个表之间的关联关系，能够屏蔽掉笛卡尔积，
      将冗余数据祛除

 Student : sno   sname  age
            1      李雷  12
	    2      小明  10
	    3      小红  13
 Subject : bno   bname
            10    语文
	    20    数学
	    30    英语
 sc :     sid   bid   score  
            1    10      90
	    1    20      80
	    2    10      85
	    3    20      80
	    3    30      90
	   ....

 -- 查询学科平均成绩高于60分的学科名
  select b.bname
   from subject b,sc where b.bno = sc.bid
       group by b.bno,b.bname having avg(sc.score)>60;

--------------------
  select b.bname
   from subject b,
    (select bid,avg(score) from sc 
	group by bid having avg(score)>60 ) a
     where b.bno = a.bid


 -- 查询李雷的总成绩是多少？
   select sum(sc.score) from student s,sc 
       where s.sno = sc.sid
             and s.sname='李雷';

 -- 查询李雷的数学成绩多少？
   李雷 student  数学 subject 成绩 sc
 select s.sname,b.bname, sc.score
    from student s,subject b,sc 
      where s.sno = sc.sid 
         and sc.bid = b.bno
	  and s.sname='李雷'
	  and b.bname='数学'



 -- 查询语文学科的总成绩是多少？
    1 语文 bname subject  成绩 score sc
    2  from subject s,sc 
        where s.bno = sc.bid

    select sum(sc.score) from subject s,sc
              where s.bno = sc.bid and s.bname='语文';

五、伪列:rowid  rownum

 

未完待续。。。。。