5.单行函数，多行函数，字符函数，数字函数，日期函数，数据类型转换，数字和字符串转换，通用函数（case和decode），casedecode
1 多行函数（理解：有多个输入，但只输出1个结果）
SQL>select count(*) from emp;
COUNT(*)
-------------
14
B 字符函数Lower
select Lower('Hello') 转小写,
upper('hello') 转大写,
initcap('hello woRld') 首字母大写
from dual;
结果：
转小 转大 首字母大写
---------- -----------
helloHELLO Hello World
C字符函数 concat：
select concat('hello','world') from dual
concat函数嵌套：
select concat (concat('hello', 'world'),'cccc') 函数嵌套 from dual;
函数嵌套
--------------
helloworldcccc
D字符函数 substr()
SUBSTR(a,b) --- 从a中第b位去字符串
substr(a, b, c) 从a中第b位, c个字符....
select substr('abcdefg1111', 2) 从第二位取子串,
substr('abcdefg1111', 2, 4) 取4个字符
from dual;
从第二位取 取4
----------------- ------
bcdefg1111 bcde
E length字符数 lengthb字节数
注意：一个中文字符,两个字节
select length('中国abc') 字符数,
lengthb('中国abc') 字节数
from dual;
运行结果：
字符数 字节数
---------- ----------
5
F instr(),lpad(),rpad(),trim()函数
select instr('abcdefg', 'efg') 求子串位置 from dual;
求子串位置
----------------
5
selectlpad('abcd', 10, '*') lpad, rpad('abcd', 10, '*') rpad from dual;
LPAD RPAD
---------- ----------
******abcd abcd******
--trim 去掉前后指定的字符,字符可以是空格,也可以不是空格 .
selecttrim('A' from 'ABCDEFg') from dual;
TRIM('
------
BCDEFg
2 数字函数
|
第二个参数 |
含义 |
|
2> 0 |
保留2位 |
|
= 0 |
保留到个位 |
|
-1 |
保留到10位 |
|
-2 |
保留到百位（要看十位 四舍五入） |
selectround(45.926, 2) AA,
round(45.926, 1) BB,
round(45.926, 0) CC,
round(45.926) DD,
round(45.926, -1) EE,
round(45.926, -2) FF
from dual;
结果：
AA BB CC DD EE FF
---------- ---------- ---------- ---------- ---------- ----------
45.93 45.9 46 46 50 0
3 日期函数
时间的计算.......
oracle数据库中 date包含 日期和时间
mysql 3中数据类型 date 、time 、times.
A selectto_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') from dual ;
TO_CHAR(SYSDATE,'YY
-------------------
2014-10-0716:34:17
B ----昨天今天明天
select sysdate-1 昨天,
sysdate 今天,
sysdate+1 明天
from dual;
selectto_char(sysdate-1, 'yyyy-mm-dd hh24:mi:ss') 昨天,
sysdate 今天,
sysdate+1 明天
from dual;
C ----查询员工的入职时间,按照 周 月 年 方式显示.....
select(sysdate-hiredate)/7 周,
(sysdate-hiredate)/30 月,
(sysdate-hiredate)/365 年
from emp;
select (sysdate-hiredate)/7 周,
(sysdate-hiredate)/30 月,
(sysdate-hiredate)/365
from emp;
周 月 年
---------- ---------- ----------
1763.95625 411.589791 33.8292979
1754.67053 409.423125 33.6512157
1754.38482 409.356458 33.6457363
D 查询员工的入职时间, 入职月数
selectename,
(sysdate-hiredate)/30 估计月,
MONTHS_BETWEEN(sysdate, hiredate) 函数计算月
from emp
select ename,
(sysdate-hiredate)/30 估计月,
MONTHS_BETWEEN(sysdate, hiredate) 函数计算月
from emp;
ENAME 估计月 函数计算月
---------- ---------- ----------
SMITH 411.589872 405.699876
ALLEN 409.423205 403.603102
WARD 409.356539 403.538586
JONES 408.056539 402.183747
MARTIN 402.089872 396.345037
selectnext_day(sysdate, '星期六') from dual ;
NEXT_DAY(SYSDA
--------------
11-10月-14
4 数据类型转换
select * from emp where hiredate >'01-1月 -81'
A 日期相关类型转换
比这个日期都要大的08-9月 -81 所有员工信息 按照三种方式实现
(1):隐式类型转换
select*
from emp
where hiredate > '01-1月 -81';
(2):字符串转成日期..显示类型转换
select*
from emp
where hiredate > to_date('1981-01-0102:03:04', 'yyyy-mm-dd hh24:mi:ss');
(3):日期转换成为日期类型的字符串：
select*
from emp
where to_char(hiredate, 'yyyy-mm-ddhh24:mi:ss') > '1981-01-01 02:03:04'
5 数字和字符串转换
A --查询员工的薪水:两位小数 本地货币代码 千位符
Y1,250.00
select empno, ename, to_char(sal, 'L9,999.99')
fromemp
结果：
EMPNO ENAME TO_CHAR(SAL,'L9,999
---------- ---------- -------------------
7369SMITH ￥800.00
7499ALLEN ￥1,600.00
7521WARD ￥1,250.00
7566JONES ￥2,975.00
7654MARTIN ￥1,250.00
7698BLAKE ￥2,850.00
7782CLARK ￥2,450.00
B 把这个字符￥1,250.00,转成数字.....
select to_number('￥1,250.00','L9,999.99') from dual ;
TO_NUMBER('￥1,250.00','L9,999.99')
-----------------------------------
1250
6 通用函数
A 给员工涨工资:总裁 1000 经理:800 其他涨500,
===============================================
分析：前后工资给列出来
if (job == 'PRESIDENT')
SAL+1000
else if (job == 'MANAGER')
SAL+800
else
SAL+500
================================================
使用的知识点：
CASE expr WHEN comparison_expr1 THEN return_expr1
[WHEN comparison_expr2 THENreturn_expr2
WHEN comparison_exprn THEN return_exprn
ELSE else_expr]
END
===============================================
转化：
CASE job when 'PRESIDENT' then sal+1000
when'MANAGER' then sal+800
elsesal + 500
END
=============================================
select ename, job, sal 涨前工资,
(
CASE job when 'PRESIDENT' then sal+1000
when'MANAGER' then sal+800
elsesal + 500
END
)
涨后工资
from emp
-----------------------------------------------------------------------------------------------------
结果：
ENAME JOB 涨前工资 涨后工资
------------------- ---------- ----------
SMITH CLERK 800 1300
ALLEN SALESMAN 1600 2100
WARD SALESMAN 1250 1750
JONES MANAGER 2975 3775
MARTIN SALESMAN 1250 1750
BLAKE MANAGER 2850 3650
CLARK MANAGER 2450 3250
SCOTT ANALYST 3000 3500
KING PRESIDENT 5000 6000
TURNER SALESMAN 1500 2000
ADAMS CLERK 1100 1600
JAMES CLERK 950 1450
FORD ANALYST 3000 3500
MILLER CLERK 1300 1800
已选择14行。
B 第二种方法
===================================================
语法：
DECODE(col|expression, search1, result1
[, search2, result2,...,]
[, default])
==================================================
转化：
decode(job, 'PRESIDENT', sal+10000, 'MANAGER', sal+800, sal+500)
===================================================
select ename, job, sal 涨前工资,
(
decode(job, 'PRESIDENT', sal+10000,'MANAGER', sal+800, sal+500)
)
涨后工资
from emp;
TO_CHAR 是把日期或数字转换为字符串
TO_DATE 是把字符串转换为数据库中得日期类型转换函数
TO_CHAR
使用TO_CHAR函数处理数字
TO_CHAR(number, '格式')
TO_CHAR(salary,’$99,999.99’);
使用TO_CHAR函数处理日期
TO_CHAR(date,’格式’)
TO_DATE
使用TO_DATE函数将字符转换为日期
TO_DATE(char[, '格式'])
类型转换函数
每个函数都可以强制将一个表达式转换成某种特定数据类型。
语法
CBool(expression)
CByte(expression)
CCur(expression)
CDate(expression)
CDbl(expression)
CDec(expression)
CInt(expression)
CLng(expression)
CSng(expression)
CStr(expression)
CVar(expression)
CStr(expression)
必要的 expression 参数可以是任何字符串表达式或数值表达式。
返回类型
函数名称决定返回类型，如下所示：
函数 返回类型 expression 参数范围
CBool Boolean 任何有效的字符串或数值表达式。
CByte Byte 0 至 255。
CCur Currency -922,337,203,685,477.5808 至922,337,203,685,477.5807。
CDate Date 任何有效的日期表达式。
CDbl Double 负数从 -1.79769313486231E308 至 -4.94065645841247E-324；正数从 4.94065645841247E-324 至 1.79769313486232E308。
CDec Decimal 零变比数值，即无小数位数值，为
+/-79,228,162,514,264,337,593,543,950,335。对于 28 位小数的数值，范围则为
+/-7.9228162514264337593543950335；最小的可能非零值是 0.0000000000000000000000000001。
CInt Integer -32,768 至 32,767，小数部分四舍五入。
CLng Long -2,147,483,648 至 2,147,483,647，小数部分四舍五入。
CSng Single 负数为 -3.402823E38 至 -1.401298E-45；正数为 1.401298E-45 至 3.402823E38。
CStr String 依据 expression 参数返回 Cstr。
CVar Variant 若为数值，则范围与 Double 相同；若不为数值，则范围与 String 相同。
说明
如果传递给函数的 expression 超过转换目标数据类型的范围，将发生错误。
通常，在编码时可以使用数据类型转换函数，来体现某些操作的结果应该表示为特定的数据类型，而不是缺省的数据类型。例如，当单精度、双精度或整数运算发生的情况下，使用 CCur 来强制执行货币运算。
应该使用数据类型转换函数来代替 Val，以使国际版的数据转换可以从一种数据类型转换为另一种。例如，当使用 Ccur 时，不同的小数点分隔符、千分位分隔符和各种货币选项，依据系统的区域设置都会被妥善识别。
当小数部分恰好为 0.5 时，Cint 和 CLng 函数会将它转换为最接近的偶数值。例如，0.5 转换为 0、1.5 转换为 2。Cint 和 CLng 函数不同于 Fix 和 Int 函数，Fix 和 Int 函数会将小数部分截断而不是四舍五入。并且 Fix 和 Int 函数总是返回与传入的数据类型相同的值。
使用 IsDate 函数，可判断 date 是否可以被转换为日期或时间。Cdate 可用来识别日期......余下全文>>