oracle---伪列和单行函数

一  ORACle的排序 

      排序语句： 
 
       SELECT [DISTINCT] * | [列名 [别名],…]  FROM 表名   WHERE 条件   ORDER BY 排序的列 [ASC | DESC],…; 
 

       select * from emp order by hiredate desc   ( desc表示降序 从大到小 asc 表示升序) 
 
       select * from emp where where job='MANAGER' order by  hiredate desc; (按条件查询出结果后排序) 

       select * from mep order by hiredate desc,sal desc (按条件一升序后 相同的条件一在进行条件二排序) 
 

二 Oracle的伪列 
 
      别名

       在select语句中 每一个被查询的表都有一个别名 默认就是它的表名 可以给表添加一个别名 添加的别名覆盖默认的别名

       select emp.ename from emp t; --错误

       select t.ename from  emp t;

       --如果多表查询时  每一个表都有相同的列名 这时必须要使用 别名

       select t.deptno from emp t,dept d;

      ROWID 是表中每一条记录的唯一标识符，数据库内部使用它来存储行的物理地址
 
       该地址可以唯一地标识数据库中的一行，可以使用 ROWID 伪列快速地定位表中的某一行
 
       无法使用update语句修改 
 

     ROWNUM 是SQL查询返回的结果集中每一行的行号【重点】
 

       可以用它来限制查询返回的行数。 
 

       ROWNUM是先查到结果集之后再加上去的一个列 。 
 

         在oracle中使用rownum来进行分页 

         select t.* ,rownum as rn from emp where rownum>=10 and rownum<=20这是错误的 
 

         因为行的循环查找 从索引1 开始  第一条记录的索引为1  不满足 循环第二条时 记录索引从1 开始 。。。。所以的行的行号都是1 永远不满足 
 

           select * from (select t.*,rownum as rn from emp t) where rn>=10 and rn<=20 这是正确的  （一般使用） 

          分页另一种写法 
 
           select * from (select t.*,rownum as rn from emp t where rownum<=20) where rn>=10 正确的 

          select * from emp where rownum<=10 这是正确 

        记录：rownum大于1的记录永远不可能成立  rownum<等于任何值都成立 

三  oracle的单行函数 
 
单行函数对于从表中查询到的每一行返回一个值，可以出现在 SELECT子句中、 WHERE 子句中、ORDER BY子句中 

单行函数可以大致划分为：

字符函数：接收字符串输入并返回字符串或数值

数字函数：接收数值输入并返回数值

日期函数：对日期进行操作

转换函数：从一种数据类型转换成另一种数据类型

正则表达式函数

字符串函数 
 
      lower(n) 将字符转换成小写         对应java String.toLowerCase
 
      select lower('AAAA') from dual;  --aaaa 

      select lower(ename) from emp;    --列输出结果全部小写 
 
      upper(n) 将字符串转换成大写       对应java String.toUpperCase

      replace（列名,被替换的字符串，替换的字符串）    对应java String.replace或者 replaceAll
 
      select ename,replace(job,'MANAGER','经理')  from emp; --job列中 所有的MANAGER都被替换成了经理 
 
      select replace('AAAtestgggg','test','测试') from dual; --AAA测试gggg 
 


      instr(列名,被搜索的字符串) 
 

      select instr('AAAtestgggg','test') from dual; --4 索引从1开始 

      substr(列名,开始位置,总长度) 
 
       select instr('AAAtestgggg',4,3) from dual; -- java substring  根据 被截取的字符串.substring(开始位置，结束的位置【不包括结束的位置】)  
                                                        javascript 被截取的字符串.substr（开始位置，长度） 被截取的字符串.substring
                                                        oracle substr
      concat(参数1，参数2) 等价于 ||
 
     select concat('a','b') from dual;  --- ab  等价于 'a'||'b'    java "aaa"+"bbb"  
 
      length(列名) 获取字符的长度      java  String.getLength();
 
      trim(列名) 去空格  

      ltrim 去左侧的空格 
 
     rtrim 去右侧的空格 

     nvl(列名,值) 当列的值为空时 输入第二参数的值 如果不为空 输出当前列的值   【重要】 

         select nvl('','bac') from dual -- '' is null 所以输出 bac

     nvl2(列名,值1,值2) 当列的值为空时 输出第三个参数的值 如果不为空输出第二个参数的值 【重要】 
 

     decode(列名,条件1，值1，条件2,值2......,条件n,值n，默认值) 当列的值等于条件n时输出值n 【重要】 
 
                select decode('2','0','男','1','女','人妖') from dual

    数字函数            

      mod(5,2) 取余数 等价于java 5%2 

      round(n) 对整数位进行四舍五入 满5进1 
 
      round(n,p) 对小数位进行四舍五入 比如 round(5.6767,2) 输出为5.68 
 
      trunc(n) 截断小数位 只保留整数位 
 
      trunc(n,p) 截断小数位意外的位数 比如 trunc(5.666,2) 输出为5.66 就是保留p位小数 

    日期函数            

      sysdate 获取系统的当前时间  【重点】
 
      add_months(日期,月数) 将日期加上月数 并返回  比如 (sysdate=2016-3-3)+5个月 =2016-8-3 
 
      select sysdate+天数 from dual 将当前日期加上某个数字 表示+天数 

      months_between(日期1，日期2)  比较日期1和日期2的相差的月份数  结果=日期1-日期2 
 
      last_day(日期) 返回日期对应月份的最后一天 
 
      round(日期,格式) 
 
            YEAR 获取当年的第一天 

            MONTH 获取当月的第一天 

            DAY 获取 第一个周末的第一天 

      next_day(日期，第几天) 获取当前日期下一个星期的第几天  


    转换函数 【每一个都是重点】
 

      to_date(日期字符串,日期格式)  字符串的格式必须由第二个参数来判断 
 
                         日期格式 ： 
 
                         年 yyyy，月mm,日dd,小时（hh12小时制，hh24 24小时制） 分钟 mi 秒 ss 
 
          举例 :select to_date('2015/03/09','yyyy/mm/dd') from dual 


      to_char(数字) 将数字转换成char类型 
 
      to_char(日期,日期格式)  将日期转换成char类型  日期格式 可以任意 输出的内容 根据日期的格式而定 
 
             举例   select to_char(sysdate,'hh24')  输出24小时制的当前时间的hour 
 
      to_number(字符串数字) 将字符串的数字转换成number类型 
 
                       举例    to_number('123')输出数字 123      

   
DECODE函数(oracle特有)

查询出所有员工的姓名和它的领导编号，没有领导的领导编号列值用“无”代替。

Select ename,decode(mgr,null,’无’) from emp;

查询所有的员工姓名和奖金，没有奖金的显示为0：

Select ename,decode(comm,null,0) from emp;

查询出所有员工信息，把它的职位做如下替换：

CLERK ?业务员
SALESMAN ? 销售员
MANAGER ?经理
ANALYST ? 分析人员
PRESIDENT ? 总裁
在Oracle9i以上版本建议使用CASE表达式来替代
CASE expre
      WHEN condition THEN return_value
      WHEN condition2 THEN return_value2
      ELSE default_value 
 END

select ename,decode(job,'CLERK','业务员','SALESMAN','销售员','MANAGER','经理','ANALYST','分析人员','PRESIDENT','总裁') FROM emp;

select ename,case job
when ‘CLERK’ then ‘业务员’
when ‘SALESMAN’then’销售员’
when ‘MANAGER’then’经理’
when ‘ANALYST’then’分析人员’
when ‘PRESIDENT’then’总裁’
end
 FROM emp;