oracle数据类型及函数

第一节：字符串类型及函数

一、字符串类型

       1.字符类型分 3 种：char(n) 、varchar(n)、varchar2(n) ；

               char(n)固定长度字符串，假如长度不足 n，右边空格补齐；

               varchar(n)可变长度字符串，假如长度不足 n，右边不会补齐；

               varchar2(n)可变长度字符串，Oracle 官方推荐使用，向后兼容性好

               char(n)VSvarchar2(n) ：char(n)查询效率相对较高，varchar2(n)存储空间相对较小；

               select length(userName) as charlength,length(password) as varchar2 length fromt_user3;可以看出 char(n) 能补齐， varchar2(n)不能补齐

二、常用函数

lpad() 向左补全字符串：select lpad(stuno,6,'0') from t_user;

rpad() 向右补全字符串：select rpad(stuno,6,'0') fromt_user;

lower() 返回字符串小写：selec tlower(userName) from t_user;

upper() 返回字符串大写：select upper(userName) from t_user;

initcap() 单词首字符大写：select initcap (userName) from t_user;

length() 返回字符串长度：select length(password) from t_user;

substr() 截取字符串：select substr (userName,1,2) from t_user;

instr() 获取字符串出现的位置：select instr(password,'23',2,2) from  t_user;第一个2表示重第二个位置开始查，第二个二表示第2次出现

ltrim() 删除左侧空格:select ltrim (userName)  from t_user;

rtrim() 删除右侧空格:select rtrim (userName) from t_user;

trim() 删除两侧空格:select trim (userName) from t_user;

concat() 串联字符串：select concat (userName,password) from t_user;

reverse() 反转字符串：select everse (userName) from t_user;

decode()语法:decode(条件，值1，翻译值1，值2，翻译值2，...值n，翻译值n,缺省值)

        含义：if 条件=值1 then return(翻译值1)

             else if 条件=值2 then return (翻译值2).....else return 缺省值 end if

        语法：decode(字段，值1，值2，值3);

        含义：if (表达式==值1) 输出值2 else 就输出 值3

        例：select decode (sign(变量1-变量2)，-1，变量1，变量2) from dual;-----比较大小，这边取较小值

           sign()函数的含义是根据某个值是0，整数或负数，分别返回0,1，-1.

           

           select ename,sex,decode(sex,'F','女'，'男') from dual;---->实现性别的正确输出

           select stuno,SUM(decode(LESSON,'C',MARK,0))AS C,SUM(decode(LESSON,'JAVA',MARK,0))AS  

           JAVA,SUM(decode(LESSON,'MYSQL',MARK,0))AS MYSQL FROM MARKS GROUP BY STUNO;------>进行行列置换

                  

第二节：数值类型及函数

一、数值类型

number 是 oracle 中的数据类型；number(precision,scale)； Precision，scale 均可选；

Precision 代表精度，sacle 代表小数位的位数；

Precision 范围[1,38]scale 范围[-84,127]

举例： 12345.678  Precision 是 8  scale  是 3；             

二、常用函数

 abs() 求绝对值；select  abs(n1) from t_number where id=1;

round() 四舍五入：select  round(n1,2) from t_number where id=1;

ceil() 向上取整：select ceil(n1) from t_number  where id=2;

floor 向下取整：select floor(n1) from t_number where id=2;

Mod()取模：select mod(5,3) from dual;

Sign()正负性：select sign(n1)  from t_number where id=1;

Sqrt() 求平方根：select sqrt(9) from dual;

Power()求乘方：select power(2,3) from dual;

Trunc()截取：select trunc(123.456,2) from dual;截取两位小数位，运行后结果123.45

To_char() 格式化数值：常见的字符匹配有 0、9、，、$、FM、L、C,    FM可以与$,L,C一起使用

select to_char(123.45,'0000.000') from dual;-----------> 1230.450

select to_char(123.45,'9999.999') from dual;----------->  123.450

select to_char(123123,'99,999,999.99') fromd ual;----------->  123,123.00

select to_char(123123.3,'FM99,999,999.99') from dual;----------->123,123,.3  FM可去掉前面空格和后面的0.

select to_char(123123.3,'$99,999,999.99') from dual;-----------> $123,123.3

select to_char(123123.3,'L99,999,999.99') from dual;----------->  ￥123,123.30

select to_char(123123.3,'99,999,999.99C') from dual;-----------> 123,123,30CNY

第三节：日期类型及函数

Date 和 timestamp（时间戳）

Date 包含信息 century（世纪信息） year 年 month 月 day 日 hour 小时 minute 分钟 second 秒

Timestamp 一般用于日期时间要求非常精确的情况，精确到毫秒级；

insertintot_datevalues(1,sysdate,systimestamp);

下面重点讲 date 类型的常用函数：

select sysdate from dual;

select systimestamp from dual;

Add_months 添加月份： selectadd_months(d1,2)from t_date where id=1;

Last_day 返回指定日期月份的最后一天： select last_day(d1) from t_date where id=1;

updatet_datesetd3=to_date('2016-12-20','YYYY-MM-DD') whereid=1;

updatet_datesetd3=to_date('2016-12-2018:31:34','YYYY-MM-DD HH24:MI:SS') whereid=1;

Months_between 返回两个日期的相差月数 select month s_between(d1,d3) fromt_date where id=1;

next_day 返回特定日期之后的一周内的日期：select next_day(d1,2) from t_date where id=1;

Trunc 截取日期： 

              select trunc(d1,'YYYY') from t_date where id=1;

              select trunc(d1,'MM') from t_date where id=1;

              select trunc(d1,'DD') from t_date where id=1;

              select trunc(d1,'HH') from t_date where id=1;

              select trunc(d1,'MI') from t_date where id=1;

Extract 返回日期的某个域：

              select extract(year from sysdate) from dual;

              select extract(month from sysdate) from dual;

              select extract(day from sysdate) from dual;

              select extract(Hour from systimestamp) from dual;

              select extract(minute from systimestamp) from dual;

              select extract(second from systimestamp) from dual;

To_char 将日期转换成字符串：

              selectto_char(d1,'YYYY-MM-DD') fromt_datewhereid=1;

              selectto_char(d1,'YYYY-MM-DDHH24:MI:SS') fromt_datewhereid=1;

第四节：其他常用处理函数

常用的聚合函数：

Max 求最大值：select max(sal) from emp ;

Min 求最小值：select min(sal) from emp;

Avg 求平均值：select avg(sal) from emp;

Sum 求和：select sum(sal) from emp;

Count 统计记录数：select count(ename) from emp;

Nvl 空值处理：select  ename, nvl(sal,0) from  emp;

rownum Oracle 分页：select * from (select a.*,rownumrn from (select*from emp) A where rownum<=10) where rn>5;

Oracle 中的运算： select 2+1 from dual; select 2-1 from dual; select 2*1 from dual; select 2/1 from dual;

条件判断式： Betweenand 范围查询： select*from emp where sal between 900 and 1500; select * from emp where sal >=900 and sal<=1500;

In 集合范围： select ename,hiredate from emp where ename in(select distinct ename from bonus)

Like 模糊查询： select * from emp where ename like'%M%'  

                select * from emp wheree name like'M%' 

                select * from emp  where ename like'_M%'