17.07.28 SQL 函数

 

排序:order by

  SQL> select last_name, salary from employees order by salary;      默认升序

  SQL> select last_name, salary from employees order by salary desc;      降序

  SQL> select last_name, salary from employees order by last_name;     字符串排序

  SQL> select last_name, hire_date from employees order by hire_date;    日期排序

  SQL> select last_name, salary, commission_pct from employees order by salary desc, commission_pct desc;   

  SQL> select last_name, salary*12*(1+commission_pct) from employees order by 2;      根据位置参数来排序

  SQL> select last_name, salary*12*(1+commission_pct) total_salary from employees order by total_salary;         根据别名进行排序

 

关键字:

  select,from,where,order by

 

 

函数:

  单行函数,多行函数

 

有累计误差的要用函数

  SQL> select last_name,(sysdate - hire_date)/7 from employees;  多少个星期

  SQL> select last_name,months_between(sysdate,hire_date) from employees;    多少个月

  SQL> select last_name,months_between(sysdate,hire_date)/12 from employees;   多少年

  SQL> select add_months(sysdate,120) from dual;    十年后的日期

  SQL> select last_name,hire_date from employees where extract(year from hire_date)=2006;  查找 2006 年入职的员工

  SQL> select last_name,hire_date from employees where extract(month from hire_date)=9;  查找 9 月份入职的员工

 

  

隐含类型转换

明确类型转换

 

characer    ——   to_char(格式化输出) ——>

      ——  to_number    ——>  number

      ——  to_date  ——>

 

其他函数:

  NVL(nvl1,nvl2)

  若 nvl1 不为空,则返回 nvl1; 若 nvl1为空,则返回 nvl2

 

  NVL2(nvl1,nvl2,nvl3)

  若nvl1不为空,则返回nvl2;若nvl1为空,则返回nvl3

 

posted on 2017-08-19 08:38  zhang_x  阅读(95)  评论(0编辑  收藏  举报

导航