Oracle总结（1）

一.基本查询
    1.基本命令
        1> show user 当前用户
        2> select * from tab; 当前用户下的表
            tab: 数据字典(表)
        3> desc 表名-----------查看表结构
        4> set linesize 120  --设置行宽
        5> col ename for a8  --设置行宽
           col sal for 9999
        6> c命令
            SQL> select *
            2  form emp;
            form emp
            *
            第 2 行出现错误:
            ORA-00923: 未找到要求的 FROM 关键字
            SQL> --c 命令
            SQL> 2
              2* form emp
            SQL> c /form/from
              2* from emp
            SQL> /
        7> --DISTINCT 去掉重复记录
            select DISTINCT deptno,job from emp;
                --DISTINCT作用于后面所有的列
        8> --连接符
            concat('Hello','  World')
        9> --dual:伪表
            select 3+2 from dual;
                   3+2                                                                                                              
            ----------                                                                                                              
                     5  
        10> save c:\a.sql -----保存位sql脚本
                已创建 file c:\a.sql
            @c:\a.sql ---------执行sql脚本
    2.过滤和排序
        1> --字符大小写敏感
        2> --日期格式敏感
            select sysdate from dual
            
                SYSDATE                                                                                                                 
                --------------                                                                                                          
                19-6月 -13
            
            -----------------默认格式-----------------
            SQL> select *
              2  from v$nls_parameters;

            PARAMETER                      VALUE                                                                                    
            ------------------------------ ----------------------------------------------------------------                         
            NLS_LANGUAGE                   SIMPLIFIED CHINESE                                                                       
            NLS_TERRITORY                  CHINA                                                                                    
            NLS_CURRENCY                   ￥                                                                                       
            NLS_ISO_CURRENCY               CHINA                                                                                    
            NLS_NUMERIC_CHARACTERS         .,                                                                                       
            NLS_CALENDAR                   GREGORIAN                                                                                
            NLS_DATE_FORMAT                DD-MON-RR                                                                                
            NLS_DATE_LANGUAGE              SIMPLIFIED CHINESE                                                                       
            NLS_CHARACTERSET               ZHS16GBK                                                                                 
            NLS_SORT                       BINARY                                                                                   
            NLS_TIME_FORMAT                HH.MI.SSXFF AM                                                                           

            PARAMETER                      VALUE                                                                                    
            ------------------------------ ----------------------------------------------------------------                         
            NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM                                                                 
            NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR                                                                       
            NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR                                                             
            NLS_DUAL_CURRENCY              ￥                                                                                       
            NLS_NCHAR_CHARACTERSET         AL16UTF16                                                                                
            NLS_COMP                       BINARY                                                                                   
            NLS_LENGTH_SEMANTICS           BYTE                                                                                     
            NLS_NCHAR_CONV_EXCP            FALSE                                                                                    

            已选择19行。
            ------------------------修改默认格式---------------------
            SQL> alter session set NLS_DATE_FORMAT='yyyy-mm-dd';
            
            会话已更改。
        3> between and 在...之间 (小值在前 大值在后)
            SQL> --查询薪水1000~2000之间的员工
            SQL> select *
              2  from emp
              3  where sal between 1000 and 2000;
        4> in 在集合中   not in 不在集合中
            SQL> --查询部门号是10和20的员工
            SQL> select *
              2  from emp
              3  where deptno in (10,20);
        5> like 模糊查询 %(任意多个) _(任意一个)
            --查询名字是4个字的员工
            select *
            from emp
            where ename like '____';
            -------------------------------------
            --查询名字中含有下划线的员工
            select *
            from emp
            where ename like '%\_%' escape '\';
        6> 排序
            SQL> select *
              2  from emp
              3  order by sal;
            SQL> --a命令  append
            SQL> a  desc
              3* order by sal desc
            SQL> /
            -------------------------------------------------
            --order by后面+列名  表达式  别名 序号
            
            select *
          2  from emp
          3  order by comm desc
          4* nulls last   -----空值最后
    3.单行函数
        1> --NULLIF(a,b) 当a=b时, 返回null，否则返回a
            SQL> select nullif('abc','abcd') from dual;

            NUL                                                                             
            ---                                                                             
            abc
        2> --COALESCE :从左往右找到第一个不为null的值
            select comm,sal,COALESCE(comm,sal) from emp;
        3> case...end
            SQL> --条件表达式
            SQL> --涨工资，总裁1000 经理800 其他400
            SQL> select ename,job,sal 涨前薪水,
              2         case job when 'PRESIDENT' then sal+1000
              3                  when 'MANAGER' then sal+800
              4                  else sal+400
              5          end 涨后薪水
              6  from emp;
        4>     SQL> select ename,job,sal 涨前薪水,
              2         decode(job,'PRESIDENT',sal+1000,
              3                    'MANAGER',sal+800,
              4                              sal+400) 涨后薪水
              5  from emp;
    4.组函数
        1> --工资总额
            SQL> select sum(sal) from emp;
        2> --人数
            SQL> select count(*) from emp;
        3> --平均工资
            SQL> select sum(sal)/count(*) 一,avg(sal) 二 from emp;
        4> --平均奖金
            SQL> select sum(comm)/count(*) 一, sum(comm)/count(comm) 二, avg(comm) 三
              2  from emp;

                    一         二         三                                                
            ---------- ---------- ----------                                                
            157.142857        550        550   
        5> --分组数据
            SQL> --求每个部门的平均工资
            SQL> select deptno,avg(sal)
              2  from emp
              3  group by deptno;

                DEPTNO   AVG(SAL)                                                           
            ---------- ----------                                                           
                    30 1566.66667                                                           
                    20       2175                                                           
                    10 2916.66667
        6>    1  select deptno,job,sum(sal)
              2  from emp
              3  group by deptno,job
              4  order by 1
              -------------------------
                  DEPTNO JOB         SUM(SAL)                                                 
        ---------- --------- ----------                                                 
                10 CLERK           1300                                                 
                10 MANAGER         2450                                                 
                10 PRESIDENT       5000                                                 
                20 ANALYST         6000                                                 
                20 CLERK           1900                                                 
                20 MANAGER         2975                                                 
                30 CLERK            950                                                 
                30 MANAGER         2850                                                 
                30 SALESMAN        5600
        7>     SQL> select deptno,avg(sal)
              2  from emp
              3  group by deptno
              4  having avg(sal)>2000;

                DEPTNO   AVG(SAL)                                                           
            ---------- ----------                                                           
                    20       2175                                                           
                    10 2916.66667  
        8>     SQL> /*
            SQL> group by的增强
            SQL> group by deptno,job
            SQL> +
            SQL> group by deptno
            SQL> +
            SQL> group by null
            SQL> =
            SQL> group by rollup(deptno,job)
            SQL>
            SQL> group by rollup(a,b)
            SQL> =
            SQL> group by a,b
            SQL> +
            SQL> group by a
            SQL> +
            SQL> group by null
            SQL> */
            -----------------------------------
            SQL> select deptno,job,sum(sal)
              2  from emp
              3  group by rollup(deptno,job);

                DEPTNO JOB         SUM(SAL)                                                 
            ---------- --------- ----------                                                 
                    10 CLERK           1300                                                 
                    10 MANAGER         2450                                                 
                    10 PRESIDENT       5000                                                 
                    10                 8750                                                 
                    20 CLERK           1900                                                 
                    20 ANALYST         6000                                                 
                    20 MANAGER         2975                                                 
                    20                10875                                                 
                    30 CLERK            950                                                 
                    30 MANAGER         2850                                                 
                    30 SALESMAN        5600                                                 

                DEPTNO JOB         SUM(SAL)                                                 
            ---------- --------- ----------                                                 
                    30                 9400                                                 
                                      29025                                                 

            已选择13行。
        9> break on 列名 根据列值的范围分割输出结果
        
            SQL> break on deptno skip 2
            SQL> select deptno,job,sum(sal)
              2  from emp
              3  group by rollup(deptno,job);

                DEPTNO JOB         SUM(SAL)                                                 
            ---------- --------- ----------                                                 
                    10 CLERK           1300                                                 
                       MANAGER         2450                                                 
                       PRESIDENT       5000                                                 
                                       8750                                                 
                                                                                            
                                                                                            
                    20 CLERK           1900                                                 
                       ANALYST         6000                                                 
                       MANAGER         2975                                                 
                                      10875                                                 
                                                                                            

                DEPTNO JOB         SUM(SAL)                                                 
            ---------- --------- ----------                                                 
                                                                                            
                    30 CLERK            950                                                 
                       MANAGER         2850                                                 
                       SALESMAN        5600                                                 
                                       9400                                                 
                                                                                            
                                                                                            
                                      29025                                                 
                                                                                            
                                                                                            

            已选择13行。
-----------------------------------------------------------------------------------------
            SQL> break on null
            SQL> /

                DEPTNO JOB         SUM(SAL)                                                 
            ---------- --------- ----------                                                 
                    10 CLERK           1300                                                 
                    10 MANAGER         2450                                                 
                    10 PRESIDENT       5000                                                 
                    10                 8750                                                 
                    20 CLERK           1900                                                 
                    20 ANALYST         6000                                                 
                    20 MANAGER         2975                                                 
                    20                10875                                                 
                    30 CLERK            950                                                 
                    30 MANAGER         2850                                                 
                    30 SALESMAN        5600                                                 

                DEPTNO JOB         SUM(SAL)                                                 
            ---------- --------- ----------                                                 
                    30                 9400                                                 
                                      29025                                                 

            已选择13行。

待续.................