Oracle培训笔记20060409

通用函数：
decode(val,test1,result1,....testN,resultN,default):参数不固定
返回：如果val=test1,则函数返回result1,如果val=testN,则函数返回resultN，如果都不等，则返回default.
将每个人的职务从英文翻译成中文
select job,decode(job,'MANAGER','经理','SALESMAN','销售','其他') job_title from emp;
练习：假设新的工资政策是CLERK增加10%，MANAGER增加5%，其他人不变，查询每个职工预计的新工资？
 select ename,job,
 decode(job,'CLERK',sal*1.1,'MANAGER',sal*1.05,sal) as sal from emp;
练习：假设工资的级别定义如下
 1000元以下是1级
 2000元以下是2级
 2000和2000以上是3级
请查询每个职工的编号，姓名，工资和工资级别。
 select empno,ename,sal,
 decode(trunc(sal,-3),0,1,1000,2,3) from emp;
练习：假设工资的级别定义如下
 1000元以下是1级
 3000元以下是2级
 3000和3000以上是3级
请查询每个职工的编号，姓名，工资和工资级别。
 select empno,ename,sal,
 decode(trunc(sal,-3),0,1,1000,2,2000,2,3) from emp;
练习：假设工资的级别定义如下
 1250元以下是1级
 2400元以下是2级
 2400和2400以上是3级
请查询每个职工的编号，姓名，工资和工资级别。
 select empno,ename,sal,
 decode(sign(sal-1250),-1,1,
  decode((sal-2400),-1,2,3)) from emp;

在oracle9.0后，case语句：
写法一：
select job,case val when test1,then result 1
  ......
  when testN,then resultN
  else default
 end
from emp;
练习：假设新的工资政策是CLERK增加10%，MANAGER增加5%，其他人不变，请查询谁的新工资高于2000。
  SELECT *
 FROM EMP
  WHERE CASE JOB WHEN 'CLERK' THEN 1.1
                 WHEN 'MANAGER' THEN 1.05
                 ELSE 1
        END * SAL > 2000
 

写法二：
   CASE WHEN 条件1 THEN 结果1
        WHEN 条件2 THEN 结果2
         ... ...
        WHEN 条件n THEN 结果n
        ELSE default
   END
  SELECT JOB,CASE WHEN JOB='CLERK' THEN '普通职员'
                  WHEN JOB='MANAGER' THEN '经理'
                  ELSE '其他职员'
             END JOB_TITLE
    FROM EMP;

练习3，假设工资的级别定义如下
       1000元以下（不含）是1级
       3000元以下（不含）是2级
       3000和3000以上是3级
  请查询每个职工的编号，姓名，工资和工资级别
select empno,ename,sal,case when sal<1000 then 1
    when sal>=1000 and sal<3000 then 2
    else 3
   end as grade
from emp;

练习4，假设工资的级别定义如下
       1250元以下（不含）是1级
       2400元以下（不含）是2级
       2400和2400以上是3级
select empno,ename,sal,case when sal<1250 then 1
    when sal>=1250 and sal<2400 then 2
    else 3
   end as grade
from emp;
请查询每个职工的编号，姓名，工资和工资级别
case判断时先判断前面的条件，如在练习4中，不需要添加sal>=1250,因为判断的范围直接是在不满足前面的条件（sal<1250）内进行的。

单行函数已经结束。

多行函数：（组函数）
没有分组的时候，将查询到到的结果行为一组。
avg
count
max
min
stddev
sum
variange
练习：查询所有职工的人数，工资总额，平均工资，分成总额，平均分成。
 select count(*),sum(sal),avg(sal),sum(comm),avg(comm) from emp;
 COUNT(*)   SUM(SAL)   AVG(SAL)  SUM(COMM)  AVG(COMM)
--------- ---------- ---------- ---------- ----------
       14      29025 2073.21429       2200        550
注意：（1）组函数能够自动忽略带入参数是空值的行。
解决：select count(*),sum(sal),avg(sal),sum(comm),avg(nvl(comm,0)) from emp;

select count(*),count(1),count(2),count(100),count(sal),count(comm) from emp;
 COUNT(*)   COUNT(1)   COUNT(2) COUNT(100) COUNT(SAL) COUNT(COMM)
--------- ---------- ---------- ---------- ---------- -----------
       14         14         14         14         14           4
（2）组函数count()返回函数。
select sum(comm),count(comm) from emp where comm is null;
 SUM(COMM) COUNT(COMM)
---------- -----------
                     0

  分组：select .....
 from.....
 where.....
 group by 分组项列表
分组项可以是列名，可以是表达式，可以是单行函数
select deptno,count(*) from emp group by deptno;
 select select-list
 from.....
 where.....
 group by 分组项列表
在分组的情况下，select-list上可以放置的项目的要求：
 （1）如果是列名，必须是分组项中的一个
 （2）组函数都可以放
 （3）表达式的运算数，单行函数的参数，要么是直接量，要么必须是分组项中的
一个
select c1,c2,count(*)
from emp
group by c1;//这样查询不可以执行，C2是列，却不是分组项
select c1,count(*)
from emp
group by c1,c2;//可以执行
select c1,100,'ABC' count(*)
from emp
group by c1,c2;//可以执行
select c1||c2,count(*)
from emp
group by c1,c2;//可以执行
select c1,c2 count(*)
from emp
group by c1||c2;//不可以执行
select c1,count(*)
from emp
group by c1||'ABC';//不可以执行

练习：查询担任每种职务的人数各是多少？
SQL> select job,count(*)
  2  from emp
  3  group by job;
练习：查询不同年度参加工作的职工的年度，人数，和平均工资？
SQL> select trunc(hiredate,'YYYY'),count(*),avg(sal)
  2  from emp
  3  group by trunc(hiredate,'YYYY');
或者：
SQL> select to_char(hiredate,'YYYY'),count(*),avg(sal)
  2  from emp
  3  group by to_char(hiredate,'YYYY');
在SQL语句中，列的别名不可以被引用，比如上面练习中不可以将分组项起别名然后让其他的引用。
练习：查询每个部门内担任每种职务的人数各有多少？
SQL>  select deptno,job,count(*)
  2   from emp
  3   group by deptno,job;

组的过滤：分好了组以后，在分好的组中挑选需要的数据
select ....
from....
where行过滤条件
group by.....
having 组过滤条件

查询谁的工资最高？
select * from emp where sal=max(sal);//不能够查询到
where需要遍历所有数据
在where中不可以有组函数（多行函数）

组过滤条件的运算数要求：和select-list中的项目要求相同
练习一：查询担任哪种职务的人数超过三人？
SQL> select job,count(*)
  2  from emp
  3  group by job
  4  having count(*) > 3;
练习二：查询哪个部门有一个以上的CLERK?
select deptno,count(*)
from emp
group by deptno,job
having count(*) > 1 and job='CLERK';
或者（其中下面的方法比较好些）
select deptno,count(*)
from emp
where job='CLERK'
group by deptno
having count(*)>1;
练习三：查询各个部门中工资超过1000的人数？
 select deptno,count(*)
 from emp
 where sal>1000
 group by deptno；

排序：将查询到的结果，按照某种次序重新排列
ORDER BY语句写在SELECT语句的最后
select....
from....
where....
group by.....
having.....
order by 排序项列表
排序项：：={列名，表达式，单行函数}ASC|DESC 默认是升序的
select * from emp order by job,sal;
先按前面的排序，当前面排序项中值相同时，按后面的排序项排序
 练习：查询职工所属的部门编号，职工编号，姓名及工资。要求按照部门编号升序排列，同部门的按照职工工资降序排序。
select deptno,empno,ename,sal
from emp
order by deptno,sal desc; 
 练习：查询所有职工的信息，要求按照总收入排序。
 select ename,sal,comm,sal+nvl(comm,0)
 from emp
 order by sal+nvl(comm,0);

在分组情况下，ORDER BY 中的排序项的要求：和分组情况下，SELECT-LIST中的项目一样。
练习：查询各个部门的编号和人数，要求按照人数的降序排列。
 select deptno,count(*)
 from emp
 group by deptno
 order by count(*) desc;
练习：查询各个部门中工资超过2000的人数，要求按照人数降序排列。
select deptno,count(*)
from emp
where sal>2000
group by deptno
order by count(*) desc;
练习：查询各个部门的编号和工资总额，要求按照工资总额的升序排列。
select deptno,sum(sal)
from emp
group by deptno
order by sum(sal);

在结果中清除重复的行
DISTINCT+列名
select distinct deptno from emp;
练习：查询每个部门有哪些职务种类（名称）
 select distinct deptno,job
 from emp；
练习：查询每个部门各有几种职务（数量）
 select deptno,count(distinct job)
 from emp
 group by deptno;

SQL重要操作：连接 JOIN
从多个表中读取相关的数据。
传统写法：
SELECT 列的列表
FROM A，B
[WHERE 条件]；
EMP表，DEPT表
select emp.empno,emp.ename,emp.sal,emp.deptno,dept.deptno,dept.dname from emp,dept;
在所有的组合中选择需要的数据
查询一个人的信息和所在部门的信息
select emp.empno,emp.ename,emp.sal,emp.deptno,dept.deptno,dept.dname from emp,dept where emp.deptno=dept.deptno;
(1)SELECT---查询哪些表的哪些列
(2)FROM----查询哪些表
(3)WHERE----在所有的连接组合中，我们需要的连接组合应该在各个列上满足什么条件
练习一：查询谁在NEW YORK或者DALLAS工作（假设职工部门在哪里，职工就在哪里）
SQL>  select emp.ename,emp.deptno,dept.deptno,dept.loc
  2  from emp,dept
  3  where emp.deptno=dept.deptno and (dept.loc='NEW YORK' or dept.loc='DALLAS');
或者
SQL>  select emp.ename,emp.deptno,dept.deptno,dept.loc
  2  from emp,dept
  3  where emp.deptno=dept.deptno and dept.loc in('NEW YORK','DALLAS');

工资级别的定义：工资的级别和上下限SALGRADE表
练习：查询每个职工的编号，工资和工资级别
 select emp.empno,emp.sal,salgrade.grade
 from emp,salgrade
 where emp.sal between salgrade.losal and salgrade.hisal;
给表起别名
 select eA.empno,A.sal,B.grade
 from emp A,salgrade B
 where A.sal between B.losal and B.hisal;
表别名可以引用的（与列名不同）
查询每个职工的编号，姓名，所在部门的名称和工资级别
select A.empno,A.ename,B.dname,C.grade
from emp A,dept B,salgrade C
where A.deptno=B.deptno and (A.sal between C.losal and C.hisal);

SQL-1999的连接方法：