20170728上课笔记

round(XX,X) 算法四舍五入

trunc（XX,X）算法不四舍五入

order by

自定义字段order by时需要order by其自定义名字，也可以select

a,b,c from table order 1 代表a2代表b3代表c

讲义进booklist/sql/sql language reference/Functions 查看具体函数方法内容

分组条件group by to_char('data字段','yyyy')//即可取出年按年分组

例：select to_char(t.hire_date, 'yyyy'),count(salary)  from employees t where 1=1 group by to_char(t.hire_date, 'yyyy');

sysdate 返回当前服务器时间

select sysdate from dual;

months_between('','') //查询两个时间之间有多少月份

add_months(sysdate,120)//加120个月 即为10年

next_day('2017-06-31',friday);//查询参数时间的下一个周几

round(sysdate,'month') //对日期做四舍五入  原则一样，过半则加 比如月30天过15天则四舍五入

 select * from t11 where name like '%\_%' escape '\';

　　NAME

　　----------

　　aa_a

escape 定义转义符 让特殊字符作为字符串使用

nvl(字段,'XX');//若字段值为空，则替换为XX

nvl2（字段，‘XX’，‘XXX’）//若字段不为空，返回XX 为空返回XXX

extract 和 to_char()功能雷同

select nvl(to_char(department_id),'wubumen') from employees// department_id为int类型tochar转换为char类型才能输出wubumen

 

select last_name, job_id, salary,

case job_id

  when 'IT_PROG' then salary+1000

  when 'SA_REP' then salary+1500

  when 'ST_CLERK' then salary+2000

  else salary

end new_salary

from employees;

//判断输出

select last_name, job_id, salary,

decode( job_id,

  'IT_PROG', salary+1000,

  'SA_REP',  salary+1500,

  'ST_CLERK', salary+2000,

  salary) new_salary

from employees;

//以上两函数功能一样

 

实例练习

 

select last_name,

case

  when salary>20000 and salary<50000 then 'A'

    when salary>15000 and salary<=25000 then 'B'

  when salary>10000 and to_char(salary)<=15000 then 'C'

  when salary>5000 and salary<=10000 then 'D'

  else 'E'

end fenji

from employees;

转载于:https://www.cnblogs.com/guoxf/p/7253472.html