传智播客Java培训--Oracle数据库中的函数

     天气变的越来越热了，这个时候对我们这些胖人来说简直是煎熬啊，每天都热的浑身难受，稍微动动就一身臭汗，在教室还好点，有中央空调和柜式空调一起吹着，可回到宿舍就不行了，因为宿舍的哥几个都欠了房东不少水电费，房东不给空调遥控器，宿舍跟蒸笼一样，每天只能弄个湿毛巾披在身上，然后旁边放盆水，毛巾不凉了就蘸一下，不行了，快要被逼疯了，还是得再动员一下宿舍的哥几个去交水电费吧，不然再这样下去这条小命估计得玩完啦！
    今天是进入oracle学习的第二天，老师带着敲了一天的sql语句，有点枯燥，但是很重要，必须要重点掌握，这其中有许多sql中的函数是以前闻所未闻的，而且还不好记，刚学过了没多久就忘记了，感觉还是练的少，得多找点时间练一练。总结一下今天的内容,以前学过的内容就不在罗嗦了，只总结新学习的内容，这里面的例子都是基于Scott数据库的emp表的操作:
1 Select语句中的算术操作符：
对NUMBER型数据可以使用算数操作符创建表达式（+  -  *  /）
Select enam,sal*2 from emp
对DATE型数据可以使用部分算数操作符创建表达式 （+  -）
2 字符串的连结操作符:
将列或字符与其它列用双竖线连结,产生的结果列是另一个字符表达式：
Select ename || ‘work at’|| job from emp
3空值的使用：
空值是指不可用、未分配的值,也就是没有值。空值不等于零或空格，任何类型的字段都可以允许空值作为值的存在，包括空值的任何算术表达式都等于空，包括空值的连接表达式等于与空字符串连接，也就是原来的字符串。
4定义列的别名：
 Select ename as “姓名”,sal as “薪水” from emp;   （as也可以不加，如果别名中使用特殊字符,或者是强制大小写敏感,或有空格时,都需加双引号）
5 去除重复记录
使用DISTINCT关键字可从查询结果中清除重复行
字段不重复, distinct作用域后面所有字段
     Select distinct job,deptno from emp
    行不重复
Select distinct * from emp
 6获取当前日期时间值
Select sysdate from dual;
dual是虚表，里边的数据没有意义，用来计算临时数据
 7 大小写转换函数
lower (strexp)  返回字符串,并将所有的字符小写
Select lower(ename) from emp
upper (strexp)   返回字符串,并将所有的字符大写
Select upper (ename) from emp
initcap(strexp)  将字符串的(每个单词的)第一个字母变为大写,后面的小写;
Select initcap (ename) from emp
 8 字符处理函数
 concat(strexp, strexp):  连接两个字符串
 concat(“ni”,”hao”) 返回nihao
substr(str,start_index,length) :从指定的位置截取指定长度的字符串
substr ('spring',1,3) 返回spr
length(strexp):返回字符串的长度
length('String') 返回6
lpad( string1, padded_length, [ pad_string ] )   在列的左边粘贴字符
string1是需要粘贴字符的字符串，padded_length是返回的字符串的数量，如果这个数量比原字符串的长度要短，lpad函数将会把字符串截取成padded_length;pad_string是个可选参数，这个字符串是要粘贴到string1的左边，如果这个参数未写，lpad函数将会在string1的左边粘贴空格。
Lpad(123,5,’*’); 返回**123
rpad(粘贴字符)    RPAD 在列的右边粘贴字符
rpad (123,5,’*’); 返回123**
 trim():截取字符串两端特殊字符
replace(str,search_str, [replace_str]):将每次在str中出现的search_str用replace_str替换
9数字函数:是对数字进行操作的函数
round：传回一个数值,该数值是按照指定的小数位数进行四舍五入运算的结果
round(45.926, 2) 返回45.93
trunc函数返回处理后的数值，不做舍入处理，直接截去
trunc (45.926, 2) 返回45.92
mod（number1,number2）两个数值相除并返回其余数。
Mod(300,200) 返回100 
10 日期函数
months_between (date2,date1)  两日期相差多少月
select months_between('01-4月-08','02-8月-08') from dual; 返回4
add_months (date1,number)  加月份到日期得到新的日期
select add_months(to_date('01-4月-08','dd-mon-yy'),6) from dual; 返回01-10月-08
next_day (date,'day')  给出日期date和星期x之后计算下一个星期的日期
select next_day(to_date('01-4月-08'),'星期二') from dual; 返回08-4月-08
last_day (date1)  计算指定日期所在月份的最后一天的日期
select last_day(to_date('01-4月-08')) from dual; 返回30-4月-08
11 转换函数
使用TO_CHAR 函数操作日期
TO_CHAR(date, ‘fmt’) ：是字符类型的函数，转化日期为字符，格式(‘fmt’)必须用单引号括起来,并且是大小写敏感，可包含任何有效的日期格式，fmt值的宽度正好能容纳所有的有效数字。
格式控制符 描述
YYYY  YYY YY 以数字表示全年(分别代表4位、三位、两位)的数字年
YEAR 年的拼写
MM(mm) 两位数字月
MONTH 月的全拼
MON 月名称的缩写
DD 数字日
DAY 星期的全拼
D  星期中的第几天
DY 表示三位缩写的星期
select to_char(to_date('05-4月-08'),'mon-dd-yy') from dual  返回4月 -05-08
使用TO_CHAR函数处理数字
TO_CHAR(number, ‘fmt’)：是字符类型的函数，转化数字为字符
数据格式控制符
9 代表一位数字，如果当前位有数字，显示数字，否则不显示(小数部分仍然会强制显示）
0 强制显示该位，如果当前位有数字，显示数字，否则显示0
$ 增加美元符号显示
L 增加本地货币符号显示(RMB )
. 小数点符号
, 千分位符号  3,000,000,000.00
select to_char(100.11'$99,999.99') from employees 返回 $100.11
select to_char(100.11,'$000,000.00') from employees 返回$000100.11
使用TO_NUMBER函数将字符转换为数字
TO_NUMBER(char [, 'fmt']))
select to_number('$123.12','$99,999.99') from dual 返回123.12
使用TO_DATE函数将字符转换为日期
TO_DATE(char[, 'fmt'])
select to_date('01-4月-08','DD-month-yy') from dual 返回01-4月 -08
12 其他函数
NVL (expr1, expr2) 将空值转换为替换的值，支持多种数据格式可以是日期,字符,数字，NVL的两个参数数据类型必须匹配 ,否则出错 
Select NVL(comm,0) from emp
NVL2 (expr1, expr2, expr3) 如果expr0不为Null,返回expr1, 为Null,返回expr2.
Select NVL2(comm,comm.,0) from emp
NULLIF (expr1, expr2) 比较两个表达式，如果相等返回空值，如果不等返回第一个表达式。
Select nullif(length(first_name),length(last_name)) from emp
COALESCE (expr1, expr2, ..., exprn) 返回表达式列表里的第一个非空表达式的值，表达式列表可以包含很多表达式,当第一个满足值非空时，返回这个表达式的值。 expr1,expr2,...,exprn的数据类型必须一致. 实现逻辑的IF-THEN-ELSE
CASE expr
  WHEN comparison_expr1 THEN return_expr1
  [WHEN comparison_expr2 THEN return_expr2
  WHEN comparison_exprn THEN return_exprn
  ELSE else_expr]
  END
SELECT   last_name,
      case job_id
               when '总经理'     then  1.15*salary
               when '副总经理'   then  1.10*salary
               else    salary
               end   as “工资”
     FROM  employees;
DECODE函数 DECODE函数类似于一系列CASE 或 IF-THEN-ELSE 语句
   DECODE(col/expression, search1, result1
            [, search2, result2,...,]
            [, default])

 SELECT   last_name,salary,
                    decode(job_id,
                                 '总经理',1.15*salary,
                                 '副总经理',1.10*salary,
                                  salary)  as “工资”
              FROM  employees;