oracle_函数

sqlplus  用户名、密码

host clear;

show user;

select * from tab;

desc 表的名字；

show linesize;

set linesize 100;

col 列名 for 9999;

set pagesize 30;

a命令 a  desc(至少两个空格）；

/表示执行上一条sql或pl/sql；

c命令 先找出错误出现在哪一行；c /被改变的、改变后的； /执行。

1，查询员工表的年基本工资和总薪水

     select EMPNO 员工编号,ENAME 员工姓名,JOB 员工职称,SAL 员工月薪,SAL*12 员工年薪,NVL(COMM, 0)员工奖金,SAL*12+NVL(COMM, 0) 员工总薪水  FROM EMP;

表达式中出现null ,则结果为null；而且null 永远！=null;

2.判断是否为null 

     select EMPNO 员工编号,ENAME 员工姓名,JOB 员工职称,SAL 员工月薪,SAL*12 员工年薪,NVL(COMM, 0)员工奖金,SAL*12+NVL(COMM, 0) 员工总薪水  FROM EMP

where COMM IS NOT NULL;(IS NULL);

3  别名

   3.1   EMPNO  as "员工编号"

   3.2   EMPNO  "员工编号"        3.1与3.2没有区别

   3.3   EMPNO  员工编号           3.3此时不能有空格等特殊字符，如有则用上面的别名。

4  distinct 的作用字段      

   4.1    select distinct job  from emp;

   4.2    select distinct job,mgr  from emp;

   说明distinct 作用于后面的所有列。

5   连接符

select 'hello'||'world'  from dual;

select concat('he','llo')　from dual;

6 oracle数据库中的单引号双引号

   单引号：字符串 或日期：字符串大小写敏感（mysql并不区分大小写）；日期的格式敏感；

    双引号：列的别名

7 生成文本文件的命令。

   spool d:文件名称.txt;

  spllo off;

 8 更改日期的格式 （DD-MON-RR）17-12月-80

  select  * from  v$nls_parameters; 从数据字典中查询已存在的参数。

 9  between and ;小的在前，大的在后，包含边界；

     in();可以有null;

    not in ();不可以有null； 

   where 条件的解析顺序从右往左；注意条件的先后，可以达到优化sql的效果。

10，排序

   order by 列，表达式，别名，序号

   asc: 先按第一个，第一个相同，按第二个，依次排列。

   desc :只作用于最近的字段

   null值为最大；

简化参数

1.单行函数（常用的单行函数）

  1.1字符函数

      UPPER

      LOWER

     INITCAP：首字母大写

     CONCAT

     SUBSTR ： (1)SUBSTR(a,b);(2)SUBSTR(a,b,c)

     INSTR :   select  instr('aaabbb','c') from dual; 查找得到返回下标，从1开始。没有就返回0；

     LENGTH:字符数

     LENGTHB:字节数

     LPAD:   

     RPAD:      select LPAD('AAA ',10,'%') from dual;  

     TRIM  : 去掉前后指定的字符  select trim('h' from 'h hgh d hello worldh h') from dual; 

     REPLACE :  select REPLACE('hello nihao' ,'l','*')　from dual;

   1.2 数字函数

     ROUND:四舍五入 。round(a,b) b，保留小数的位数，可以为负数。

     TRUNC:同上。

    MOD(A,B);取余数。

   1.3 时间和日期

         TO_CHAR（a,b）:按照b的格式将a转化成字符串:select to_char(sysdate,'yyyy-MM-dd hh:mm:ss')　from dual;

         日期型数据加减一个数字仍然为日期型数据、

          两个日期相减，返回之间相差的天数

          用日期除24，来减去日期中的小时数。除以7获得周数。

         select   ename 姓名,sysdate-hiredate 天, (sysdate-hiredate)/30 月，(sysdate-hiredate)/7 星期，(sysdate-hiredate)/365 年   from emp;返回近似值

          months_between：日期之间相差的月份

          add_months：多少个月之后

          next_day:下一个星期几？每个星期一，数据库自动备份。

          last_day

           round （a，b）

           trunc（a，b） 'year' 'month' 'day'

''          分布式数据库：逻辑上在一起，物理上不在一起。待续。。。。。。。

 1.4数据类型转换 

     1.4.1隐式类型转化：必须是可以转换的

     1.4.2显式类型转化

                 数字                                     字符串                                     日期

                                > to_char                                     >  to_date

                                <  to_number                              < to_char

 1.4.2.1: select  to_char(hiredate,'YYYY-MM-DD "是" DY')from emp;                     1981-05-01 是 星期五 

 1.4.2.2:  select  to_date('1981-05-01 是 星期五','YYYY-MM-DD "是" DY')from dual;    解析成日期类型

1.4.2.3: select  to_char(sal,'L99,999.99') from emp;    ￥2,975.00

1.4.2.4: select  to_number('￥2,975.00','L99,999.99') from dual; 

1.5通用函数

NVL（a，c）;当a==null，返回c

NVL2(a,b,c);当当a==null，返回c，否则，返回b.

nullif(a,b);当a==b返回null,否则返回a.

coalesce(...);返回第一个不是null的值。

1.6条件表达式

  1.6.1： if -then -else 的逻辑

                select  ename,job,sal 涨前，case job   when 'PRESIDENT' then sal+1000  
                                                                                     when 'MANAGER' then sal+6000  
                                                                                    else sal  
                                                                                    end 涨后 
                                                                                    from emp;   

  1.6.2 :  decode函数

              select  ename,job,sal 涨前，decode(job ,'PRESIDENT' , sal+1000  
                                                                                       , 'MANAGER'  , sal+6000  
                                                                                                                  , sal  
                                                                                                                 )涨后 
                                                                                                                 from emp;   

 1.6.3:

       select ename||'  '||sal||'  '||
       case when sal<=1000 then sal||'   差'
       when sal>1000 and sal <=2000 then sal||'   一般'
       when sal>2000 and sal <= 3000 then sal||'   良'
       when sal > 3000 and sal < 5600 then sal||'   优'
       end sal
       from emp ;

2.多行参数

 2.1 count ,avg ,sum ,max.min

   组函数会自动过滤空值，可以在其中嵌套滤空函数来清除滤空。

 2.2求部门的平均工资。

      select deptno 部门,TO_CHAR(ROUND(AVG(SAL),2), 'L9,999.99') 平均工资 from emp GROUP BY deptno; 

2.3求部门的平均工资。

select deptno 部门,job 职位,TO_CHAR(ROUND(AVG(SAL),2), 'L9,999.99') 平均工资 from emp GROUP BY deptno,job order by 1;

  注意：select a,b,c .组函数（参数列表） from 表明 group by 必须包含前面所有的不包含在参数列表中的字段，而此处的字段可以不再前面出现。

2.4having可以和组函数一起使用，这是和where最大的区别

2.5 group by 语句的增强

 select deptno 部门,job 职位,TO_CHAR(ROUND(AVG(SAL),2), 'L9,999.99') 平均工资 ,sum(sal) 部门总薪水 from emp GROUP BY rollup(deptno,job) order by 1;

3.多表查询

3.1迪卡尔集

3.2自连接。

 select e.ename 员工的名字,b.ename 老板名字
   from emp e,emp b
  where e.mgr=b.empno;

不适合操作数据较多的表

3.3层次查询（本质上是单表查询，只有一张表不会产生迪卡尔集）

select level,empno,ename,mgr
from emp 
connect by prior  empno=mgr
start with mgr is null            （=null不争）                 

order by 1 ;

4；子查询

4.1子查询需要注意的问题 

1括号

2书写风格

3可以where select having from 后添加子查询

4不可以在group by 放置子查询

5from后面的子查询需要格外注意

6子查询和主查询可以不是同一张表，

7一般不再子查询中排序，有特殊情况top-n

8一般先子查询再主查询，但相关子查询例外

9单行子查询只能使用单行操作符，多行子查询只能使用多行操作符。

10，子查询中的null

详解

3.select后面的子查询必须是单行查询

5.from后面放置子查询。

select ename from (select ename,job from emp);