三.Oracle常用数据类型及单行函数总结

　　

1.课外扩展知识
　　　UTF-8 一个中文占3个字节,GBK 一个中文占2个字节
     ctrl+E:可以找到sql查询语句的历史记录(在sql-wiwdow操作)

　　在doc里输desc 表名可以查出该表所有列的类型.(例:desc emp)
　　也可以在pl/sql工具里点表名点edit也可以查出该表所有列的类型.

2.Oracle常用数据类型:
　　在Oracle里,数字建议用number,字符用varchar2,,时间用Date;
    字符串类型:用单引号引起来的字符序列
               CHAR(length)：固定长度字符串，不足自动以空格补齐长度，最多2000个字节。
               例：CHAR(10) 代表只能输10个字节,5个中文(一个中文占2个字节),如果输入的字符只占8个字节,它会自动补空格补齐到10个字节
                   查出来的length长度永远是10.
    VARCHAR2(length)：可变长度字符串，最多4000个字节。
               例：VARCHAR2(10) 代表只能输10个字节,5个中文(一个中文占2个字节),输入字符的字节占多少字节,它的length长度就是多少
    数值类型：
    NUMBER[(precision, scale)]：数值型，可以存储整数、浮点数。最高精度38位。如果没有指定最大位数和精度，就存储38位精度的数字。
    NUMBER(24)  最多24位,而且不能有小数;
    NUMBER(24,4)  最多24位,其中小数最多4位,整数最多20位(24-4)
    
    日期类型
    DATE：存储日期和时间，精确到秒.
    默认存放格式：“DD-MON-YYYY”
    默认显示格式：“DD-MON-YY"
    TIMESTAMP[(seconds_precision)]：存储日期、时间和时区信息，带小数位的秒。时间戳
    如：TIMESTAMP(3)   秒后面小数点为3位。（最多可9位）

    java时间格式:yyyy-MM-dd hh:mm:ss
    oracle时间格式:yyyy-mm-dd hh24:mi:ss

sql语句:
  select length(列头) from emp;代表查询该列头下每列占多少字节.
  
3.伪列:
    Oracle为数据中的表都提供有伪列。伪列就像表的一个列，但是它并没有存储在表中。
    伪列可以从表中查询，但不能插入、更新和删除它们的值.
    
    常用的伪列有ROWID和ROWNUM
    ROWID 是表中每一条记录的唯一标识符，数据库内部使用它来存储行的物理地址。
    该地址可以唯一地标识数据库中的一行，可以使用 ROWID 伪列快速地定位表中的某一行。
    
    ROWNUM 是SQL查询返回的结果集中每一行的行号.
    可以用它来限制查询返回的行数。
    ROWNUM是先查到结果集之后再加上去的一个列.
    
    ROWNUM是一个序列，是oracle数据库从数据文件或缓冲区中读取数据的顺序。
    它取得第一条记录则ROWNUM值为1，第二条为2，依次类推。
    
    rownum>1永远不成立(当满足条件了才加1)
    因为从缓冲区或数据文件中得到的第一条记录的ROWNUM为1，1>1不符合WHERE条件，则被删除；接着取下条，但它的ROWNUM还是1，又被删除，依次类推，便没有了数据
  
  
4.单行函数:

　　　　1.函数概念;
　　　　　　1.1:具有某种功能的程序块，通常包括输入端和输出端;
　　　　2.函数分类;
　　　　　　2.1：单行函数：接受一个数据，输出一个数据;
　　　　　　2.2：多行函数：聚合函数，分组函数。接收一组数据，输出一个数据;
　　　　3.常见字符函数;
　　　　　　3.1:大小写相关 lower(将字符转换为小写),upper(将字符转换为大写),initcap(将字符的首字母单词大写);
　　　　4.常见数值函数;
　　　　　　4.1：ROUND:(四舍五入);select round(10.55) from dual;
　　　　　　4.2：TRUNC:(去尾);select trunc(10.000) from dual;
　　　　　　4.3：MOD:(去余);select mod(10,3) from dual

　　 单行函数对于从表中查询到的每一行返回一个值(一行一行的执行,每行返回一个值,每一行都会被过滤)
    单行函数可以大致划分为：
        字符函数：接收字符串输入并返回字符串或数值(常用方法参见课件day4)
           例:nvl函数跟nvl2函数
              select nvl('bcd','abc')from dual;表示第一个参数不为null取第一个,否则第二个
              select nvl2('adgg','bcd','abc')from dual;表示第一个参数不为null取第二个,否则第三个
              
        数字函数：接收数值输入并返回数值(常用方法参见课件day4)
            注意round函数四舍五入就可以了
            
        日期函数：对日期进行操作(常用方法参见课件day4)
            例:取当前时间+10天(默认加天)
               select sysdate+10 from dual;
               
        转换函数：从一种数据类型转换成另一种数据类型(常用方法参见课件day4)
            重要:需重点操作
            例:日期转字符串
               select to_char(sysdate,'yyyy-mm-dd')from dual;
               字符转number(9代表要转换成的格式)
               select to_number('$12.3477','$99.9999')from dual;
               number转字符串
               select to_char(123666,'999,999')from dual;
               
        DECODE函数(oracle特有,在Oracle9i以上版本建议使用CASE表达式来替代):
            例:
               select decode('x','a','b','c','d','e','f',6)from dual;
               表示:如果有个参数跟第一个参数匹配,就取这个参数后面的参数,
                    如果没有一个参数跟第一个参数匹配,就取最后落单的那个参数
            
            用case when then 替代:
                select case 'b'
                    when 'a' then 'b'
                    when 'b' then 'c'
                    else '6'
                    end from dual;

            case when then 里还可以接条件表达式:
                select sal,case
                       when sal between 1 and 1000 then '底薪'
                       when sal between 1001 and 3000 then '工薪'
                       else '高薪' and
                    from emp;
                       
        正则表达式函数(常用方法参见课件day4)
        regexp_like()函数
        ^开头,$结尾

 5.单行函数练习：
　　

--别名 t.*代表t表中所有的列
select t.ename from emp t

--所有列查出并查出它的伪列 行号
select t.*,t.rowid,rownum from emp t

--查出表中前5名员工的信息
select * from emp where rownum<=5

--查出表中10后面的员工  (select t.*,rownum rn from emp t)先算出总行数
select * from(select t.*,rownum rn from emp t)where rn>10

--(字符函数)
--返回B的ascii码表相当于B在ascii码表中用什么数字表示（int char 互换） dual(哑表)当操作为常量时可以用
--ascii(x)
select ascii('B') from dual
--chr(x)
select chr(100) from dual
--initcap(驼峰法就是每个单词首字母大写)
select initcap('user')||initcap('name') from dual
--lower(转换小写)
select lower(ename) from emp

--upper(转换大写)
select upper('feng') from dual

--replace()(替换)
select replace(job,'SALESMAN','经理') from emp
--instr()
--返回A在job职位中是第几个坐标，从1开始。找不到A返回0
select job, instr(job,'A') from emp
--反向找
select job, instr(job,'A'，-1) from emp

--substr(截取字符串)从第三个位置开始(包括第三个)
select substr(ename,3)from emp

--concat(x,y)连接字符串只能连接两个
select concat('a','b') from dual

--trim()去空白
select trim('    aa bb   ') from dual
--去左空格
select ltrim('   aa bb    ')from dual
--去右空格
select rtrim('   aa bb    ')from dual

--nvl(x,value)如果x为null,返回value,否则返回x

select nvl('','bb') from dual
select nvl('aa','bb') from dual
select nvl(comm,0)from emp

--nvl2(x,value1,value2)(如果x不为null,执行value1,否则执行value2,相当于if,else)
select nvl2(comm,comm,0) from emp

-- 查询出所有员工的姓名，首字母大写，其它字母小写。
select initcap(ename) from emp

-- 查询出所有员工的姓名，如果姓名中有”S”，全部替换成“8”。
select replace(ename,'S','8') from emp

-- 查询出姓名中有两个“L”的员工信息。<>0表示不等于0
select * from emp where ename like '%L%L%'
select * from emp where instr(ename,'L',1,2)<>0

-- 查询出所有员工的姓名和职位的前5个字符。
select substr(ename,1,5),substr(job,1,5)from emp

-- 查询出姓名字符数超过5个的员工信息。
select * from emp where length(ename)>5 

--instr(从指定位置找字符出现的坐标,如果指定位置没有返回0)
select instr('ABCCBA','C',1,2)from dual

-- 查询出所有员工的姓名和年总收入（(月薪+奖金)*12）。
select ename as 姓名,(sal+nvl2(comm,comm,0))*12 as 总收入 from emp


--日期
--返回当前数据库时间
select sysdate from dual
--返回时间精确到微秒(用得少)
select systimestamp from dual
--对日期进行加减默认是加减天
select sysdate+1 from dual

--add_months(对月份进行加减)
select add_months(sysdate,12)from dual

--months_between(d1,d2)(返回两个日期相差的月数)
select months_between(sysdate,hiredate)from emp
--last_day(d)(返回指定日期当月的最后一天)
select last_day(sysdate)from dual
select last_day(add_months(sysdate,1))from dual
--round(d,)(对指定日期时间取整)(如果参数为年，返回当前年，如果参数为月，取半舍入（一个月31天，16日才能进月）)
--如果参数是天，返回是当日这个星期的第一天（如果是周四进一周）
select round(sysdate,'YEAR')from dual
--next_day(d,day)(返回从d开始的下一个时间值)
select next_day(sysdate,'星期五')from dual

--trunc(d,)(截止日期时间数据，默认为截断当天的开始时间)(截断)
--如果参数是day返回的是当前星期的第一天
select trunc(sysdate,'MONTH')from dual
--查询出每个员工的姓名，以及到今天他共工作了几天。
select ename,sysdate-hiredate from emp


--查询出在当月最后一天入职的员工姓名和入职日期。
select ename,hiredate from emp where last_day(hiredate)=hiredate;

--查询出在当月第一天入职的员工姓名和入职日期。
select ename,hiredate from emp where trunc(hiredate,'MONTH')=hiredate

--round四舍五入
--保留两位小数
select round(13223.6767,2) from dual
--整数
select round(13223.67)from dual

--trunc截断
--保留两位小数
select trunc(13223.6767,2) from dual
--整数
select trunc(13223.67)from dual

--mod取余
select mod(5,2)from dual

--floor(向下取整)
select floor(5.3)from dual

--ceil(向上取整)
select ceil(5.3)from dual

--select ceil(123.456) from dual;
--select floor(123.456) from dual;
--select round(123.456) from dual;
--select round(456.123, -2) from dual;
--select trunc(123.456) from dual;
--select trunc(456.123, -2) from dual;


--类型转换
--to_char转成字符串
select to_char(234,'L99,9') from dual 
--日期转
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')from dual

select * from emp where to_char(hiredate,'mm')='0'
select 
--字符转数字to_char
select to_number('$12,3','$999')from dual
--数字格式转格式
select to_date('2012-12-31','yyyy-mm-dd')from dual


--decode(如果匹配就执行条件匹配后面的，都不匹配就执行落单的，没有落单就返回空)

select decode('a','a',1,'b',2,'c',3,'d',4,'ff') from dual

select ename,decode(job,'PRESIDENT','总裁','SALESMAN','销售员','MANAGER','管理员','ANALYST','分析员',job)from emp


select ename,job,sal,
    case when job='PRESIDENT' then '总裁'
         when job='SALESMAN' then '销售员'
         else job end 职位  ,
           
         
    case when sal<=2000 then '薪水'
         else '高薪' end 薪资水平
    from emp
    
--匹配email地址里有没有@.    
select t.*,t.rowid from EMP t where regexp_like(email,'.+@.+..+')

--匹配email地址里以数字开头(^)以数字结尾（$）的邮箱
select t.*,t.rowid from EMP t where regexp_like(email,'^[0-9]+$')

--instr email邮箱里第一次出现数字的坐标   count(数字在email里出现的次数)
select email,regexp_instr(email,'d'),regexp_count(email,'d')from emp t

select * from emp where regexp_like(ename, '^.*M.*$');

select  regexp_instr('abc123def', 'd') from dual;

select  regexp_replace(ename, 'w{5,}(w)', '1') as n from emp;

select regexp_substr(ename, 'N.*') from emp;