Oracle常见函数

目标	1.字符串操作    2.数值操作   3. 日期操作   4.null值操作
字符串操作	------------------1.1 char和varchar ------------------------------   char :定长，达不到定长长度空格补齐；   浪费空间换取查询效率。   varchar:变长，实际数据多少就多少。     节约空间但是比char查询效率低。   ------------------1.2 字符串的字符编码----------------------------      字符串存储长度都是按字节计算的。  每一个英文在数据库中占一个字节。  中文：GBK  2个     UTF-8：2-4个字节   -----------------1.3char和varchar最大存储长度--------------------      char最大长度：2000个字节，也就2000个英文字母，gbk:1000个汉字  varchar最大长度：4000个字节，也就4000个英文字母，gbk:2000个汉字  char可以不指定长度，默认一个字节。  varchar必须指定长度。   ----------------1.4long和clob ------------------------------------    long可以认为是varchar的加长版，存变长，最大2G。       long的不足：（基本不用）     每一张表只能出现一个long类型的 不能是主键 不能建立索引 不能出现在查询条件中    clob存储可以是定长也可以是变长，最大4GB。一般用clob代替long ------------------------字符串的函数操作--------------------------- ------------------1.5字符串拼接函数concat和||--------------------    concat()用于字符串的拼接，但多个拼接比较繁琐，建议用字符串拼接符号||   1.6length()返回字符串的长度，varchar返回实际长度，char返回的是定长。 1.7将字符串中的字母进行转换（dual:虚表或伪表）     upper()：将字符串所有字母转大写。     lower()：将所有字符串转成小写。     initcap()：将所有的字符串单词的首字母转大写。 ---------------------注意：函数中间是单引号，不能写双引号 ------------------1.8trim()函数用于截取字符串--------------------- trim('e'from'elite')as t1 from dual; ltrim()函数用于截取左边的字符串 select ltrim('elite','e')as t2 from dual; rtrim()函数用于截取右边的字符串 select rtrim('elite','e')as t3 from dual; 将上面三个函数整合到一张表中，分别用别名显示在表中的字段 select trim('m'from'museum')as t1,ltrim('museum','m')as t2,rtrim('museum','m')as t3 from dual; ------------1.9补位函数 pad 有左补位lpad()和右补位r(pad);-------- SELECT rpad(ename,10,' ')as ename,lpad(sal,6,'$')as sal FROM emp; ------------------1.10截取字符串中字串函数substr()---------------     将字符串从m位开始，截取n个字符串，注意字符串首位从1开始 m为0直接从字符串首位开始，m为负数从字符串尾部开始，n大于字符串长度，直接截取到字符串的尾部。 ---------------1.11 instr()函数用来返回字符串中字串的位置。-------    总结：字符串类型 char varchar2 long  clob        字符串函数 连接concat()或|| 长度length() 转大小写upper(),lower(),iniecap()              字符串截取函数 trim()^^^,补位函数lpad()、rpad(),   截取字串位置的函数substr，截取字串的函数instr()。
Oracle数值操作	------------------------2.1数值类型：-----------------------------      number(p)：表示数值类型，p表示整数位也表示数值的总位数，取值的范围是1-38.----用来存id 年龄等数字  number(p,s)：p表示总位数（包括s），取值1-38，s表示小数后面的位数。---用来存金额。                注意：如果指定是s,没有指定p,p默认就是38位 --------------------------------数值操作函数------------------------    2.2--round()对数值进行四十五入操作 查询语句建议全大写，这样的话效率会高一些。 SELECT ROUND(46.768,2) FROM DUAL;--46.77 SELECT ROUND(46.768,1) FROM DUAL;--46.8 SELECT ROUND(46.768,0) FROM DUAL;--47 SELECT ROUND(46.768,-1) FROM DUAL;--50 m必须是整数，默认的是0.可以是负数也可以正数，表示小数点后第几位 2.3TRUNC()函数用于截取数字(舍弃) SELECT TRUNC(46.768,2) FROM DUAL; --46.76 SELECT TRUNC(46.768,1) FROM DUAL; --46.7 SELECT TRUNC(46.768) FROM DUAL; --46  不写相当于默认就是0 SELECT TRUNC(46.768,-2) FROM DUAL; --0 -----------------------2.4MOD()函数用于取模----------------------- SELECT ENAME,SAL,MOD(SAL,1000) AS RESULT FROM EMP;--表示m取模于n后的结果 SELECT ENAME,SAL,MOD(SAL,0)AS RESULT FROM EMP;--如果n为0,直接返回m -----------------2.5CEIL()/FLOOR()取天花板或地板函数-------------- SELECT CEIL(46.768)AS RESULT FROM DUAL;--47取比当前小数大的整数     转换大小写的快捷键ctrl+引号 SELECT FLOOR(46.768)AS RESULT FROM DUAL; --46取比当前小数小的整数   总结：数值类型 number(p)表示整数   number(p,s)表示小数   四舍五入函数round()   截取数值函数：trunc()   取模函数  mod()   取整函数  ceil()  floor()
Oracle日期操作	3.1日期类型  date内部7个字节   分别表示 世纪 年 月 日 时 分 秒 timestamp  时间戳 最大精度达到11个字节，前7位于date相同，8-11表示纳秒，4个字节存储 日期关键字：sysdate 用于获取系统时间，精确到时分秒，但是只显示年月日 可以将sysdate作为某一列的默认值也可以作为新值插入到表中 SYSTIMESTAMP也是Oracle的内部日期函数，返回当前系统日期和时间，精确到毫秒。 3.2日期转换函数  日期转换函数  字符串转为日期 查询2002以前入职的员工 TO_DATE()将给定的字符串以指定的格式进行转换 SELECT ENAME,HIREDATE FROM EMP WHERE HIREDATE<TO_DATE('2002-01-02','YYYY-MM-DD');     日期转换为字符串      to_char()是将日期按照指定的格式转换成字符串 将此时的系统时间转换为  Xxxx年xx月xx日 xx时xx分xx秒 SELECT TO_CHAR(SYSTIMESTAMP,'YYYY"年"mm"月"dd"日"hh24"点"mi"分"ss"秒"day')FROM DUAL; 3.3 日期常用函数 last_day(); 返回当月的最后一天 SELECT LAST_DAY(SYSDATE) FROM DUAL; 指定返回15年2月份的最后一天是多少 SELECT LAST_DAY('01-2月-15')FROM DUAL; add_monthS(DATE ,I) I可以正数也可以是负数也可以是小数，小数位自动舍弃，正数往后，负数往前 查询员工入职后的20周年纪念日。 SELECT ENAME,ADD_MONTHS(HIREDATE,20*12)AS A FROM EMP; 计算3个月后的今天是哪一天，星期几？ SELECT TO_CHAR((ADD_MONTHS(SYSDATE,-10*12)),'YYYY-MM-DD DAY')FROM DUAL;   months_between() 返回两个日期之间间隔的月数，如果间隔的月之间不为整数就返回小数，第二个参数比第一个参数时间晚的话会返回一个负数      select ename, months_between(hiredate,sysdate)hiredate from emp; NEXT_DAY() 查询下个礼拜几是哪一天， 中间参数在中文环境可以写中文，英文环境下要写英文的星期几， 嫌麻烦的可以采用数字来1-7代替，注意1表示的星期天，7表示的星期六 下个礼拜三是哪一天 SELECT TO_CHAR((NEXT_DAY(SYSDATE,'星期三')),'YYYY-MM-DD DAY')AS NEXTWEEK FROM DUAL; 下个礼拜四是哪一天 SELECT TO_CHAR((NEXT_DAY(SYSDATE,5)),'YYYY-MM-DD DAY')AS NEXTWEEK FROM DUAL;--注意本周星期四还未过，所以显示本周的周四 比较函数 参数类型必须匹配，如果后面参数类型，不同于前面的参数的数据类型，会做自动转换，能转换就比较，不能转换就报错。但自动转换要注意符合前面参数的格式 比较后，哪个小返回哪个。 SELECT LEAST(SYSDATE,'09-9月-16')FROM DUAL;   EXTRACT():抽取或提取 从当前的系统时间中提取今年的数字 SELECT EXTRACT(YEAR FROM SYSDATE)AS YEAR FROM DUAL; 从指定的时间中获取hour SELECT EXTRACT(HOUR FROM TIMESTAMP'2016-10-10 15:35:23)FROM DUAL;
空值操作	含义：表示未知或暂时不存在的  即null 4.1 向表中插入空值 ----不能违反非空约束    显示插入：null    隐式插入：不给值 4.2 更新空值         注意：更新空值时，要加上条件限制，     否则会将数据库表中该列中所有的字段值更新为null      在更新为null值的时候，也不能违反非空约束