---------------------
单行函数
---------------------
++++++++++++++++++++++++++++++++++++++++++++++++++++++++
字符函数
=======================
replace[替换]
=======================
SQL> select replace(ename, 'A', '中国')from emp;
=======================
instr(C1, C2, I, J)[在一个字符中搜索指定的字符]
=======================
C1 被搜索的字符串
C2 希望搜索的字符串
I 搜索的开始位置 默认为1
J 出现的位置 默认为1
请找出"oracle traning"第二个ra出现的位置?
SQL> select instr('oracle traing', 'ra', 1, 2) from dual;
=======================
ascii
=======================
返回与自定的字符对应的十进制数
SQL> select ascii('A'),ascii('a'),ascii('0')zero, ascii(' ')spac from dual;
ASCII('A') ASCII('A') ZERO SPAC
---------- ---------- ---------- ----------
65 97 48 32
=======================
chr
=======================
给出整数，返回对应的字符
SQL> select chr(54740) zhao, chr(65) from dual;
ZHAO CHR(65)
---- -------
赵 A
=======================
conncat
=======================
连接两个字符串
和 || 的作用是一样的
SQL> select concat('010-', '888888888888') ||'转23' 电话 from dual;
电话
---------------------
010-888888888888转23
=======================
initcap
=======================
返回字符串并将字符串的第一个字母变为大写
SQL> select initcap('smith') upp from dual;
UPP
-----
Smith
=======================
length
=======================
字符的长度
SQL> select length('itao'), length('唐弢') from dual;
LENGTH('ITAO') LENGTH('唐弢')
-------------- --------------
4 2
=======================
lower
=======================
将所有字符小写
SQL> select lower('ITao') from dual;
LOWER('ITAO')
-------------
itao
=======================
upper
=======================
将所有字符大写
SQL> select upper('ITao') from dual;
UPPER('ITAO')
-------------
ITAO
=======================
substr(string, start, count)
=======================
取子字符串 从start开始 取count个
SQL> select substr('qintangtao', 4, 4) from dual;
SUBSTR('QINTANGTAO',4,4)
------------------------
tang
综合练习：首字母小写其余字母大写？
select lower(substr(ename, 1, 1))||upper(substr(ename, 2, length(ename)-1)) from emp; --||连接字符串
select concat(lower(substr(ename, 1, 1)),upper(substr(ename, 2, length(ename)-1))) from emp; --concat连接字符串
=======================
RPAD和LPAD(粘贴字符)
=======================
RPAD 在列的右边粘贴字符
SQL> select rpad('itao', 10, '*') from dual;
RPAD('ITAO',10,'*')
-------------------
itao******
LPAD 在列的左边粘贴字符
SQL> select lpad('itao', 10, '*') from dual;
LPAD('ITAO',10,'*')
-------------------
******itao
=======================
ltrim[左边去掉指定的字符]
rtrim[右边去掉指定的字符]
=======================
SQL> select ltrim('aaaaitao', 'a') from dual;
LTRIM('AAAAITAO','A')
---------------------
itao
SQL> select rtrim('itaoaaaaaaa', 'a') from dual;
RTRIM('ITAOAAAAAAA','A')
------------------------
itao
=======================
sounedex
=======================
返回一个与给定的字符串读音相同的字符串
SQL> create table table1(xm varchar(8));
SQL> insert into table1 values('weather');
SQL> insert into table1 values('wether');
SQL> insert into table1 values('gao');
SQL> select xm from table1 where soundex(xm)=soundex('weather');
XM
--------
weather
wether
=======================
trim(char [from] string)
=======================
去掉前后指定的字符
SQL> select trim('a' from 'aaitaoaaa') from dual;
TRIM('A'FROM'AAITAOAAA')
------------------------
itao
++++++++++++++++++++++++++++++++++++++++++++++++++++++++
数字函数
=======================
atan
=======================
返回一个数字的反正切值
SQL> select atan(1) from dual;
ATAN(1)
----------
0.78539816
=======================
ceil[向上取整]
=======================
返回大于或等于给出数字的最小整数
SQL> select ceil(3.1415), ceil(3.66) from dual;
CEIL(3.1415) CEIL(3.66)
------------ ----------
4 4
=======================
exp
=======================
返回一个数字e的n次方根
SQL> select exp(2),exp(1) from dual;
EXP(2) EXP(1)
---------- ----------
7.38905609 2.71828182
=======================
floor[向下取整]
=======================
SQL> select floor(3.14), floor(3.66) from dual;
FLOOR(3.14) FLOOR(3.66)
----------- -----------
3 3
=======================
mod[取模]
=======================
SQL> select mod(10, 3),mod(3, 3),mod(2,3) from dual;
MOD(10,3) MOD(3,3) MOD(2,3)
---------- ---------- ----------
1 0 2
=======================
power(n1, n2)
=======================
返回n1的n2次方根
SQL> select power(2, 10), power(3,3) from dual;
POWER(2,10) POWER(3,3)
----------- ----------
1024 27
=======================
round[四舍五入]
=======================
SQL> select round(55.5),round(-55.4),trunc(55.5),trunc(-55.4) from dual;
ROUND(55.5) ROUND(-55.4) TRUNC(55.5) TRUNC(-55.5)
----------- ------------ ----------- ------------
56 -55 55 -55
=======================
trunc[截取]
=======================
没有指定截取到第几位 默认截取到整数
SQL> select trunc(456.678), trunc(456.678, 2), trunc(456.678, -2) from dual;
TRUNC(456.678) TRUNC(456.678,2) TRUNC(456.678,-2)
-------------- ---------------- -----------------
456 456.67 400
=======================
sign
=======================
取数字n的符号,大于0返回1,小于0返回-1,等于0返回0
SQL> select sign(123), sign(-123), sign(0) from dual;
SIGN(123) SIGN(-123) SIGN(0)
---------- ---------- ----------
1 -1 0
++++++++++++++++++++++++++++++++++++++++++++++++++++++++
日期函数
=======================
add_honths
=======================
增加或减去月份
SQL> select to_char(add_months(to_date('199912','yyyymm'),2),'yyyymm') from dual;
TO_CHAR(ADD_MONTHS(TO_DATE('19
------------------------------
200002
=======================
sysdate[系统时间]
=======================
SQL> select to_char(sysdate, 'yyyy-mm-dd HH24:mi:ss') from dual;
TO_CHAR(SYSDATE,'YYYY-MM-DDHH2
------------------------------
2012-10-28 18:10:30
=======================
last_day
=======================
返回日期最后一天
SQL> select to_char(last_day(sysdate), 'yyyy-mm-dd') from dual;
TO_CHAR(LAST_DAY(SYSDATE),'YYY
------------------------------
2012-10-31
=======================
months_between(date2, date1)
=======================
给出date2-date1的月份
=======================
next_day(date, 'day')
=======================
计算day离date最近的日期
SQL> select next_day(sysdate,'星期五') next_day from dual;
NEXT_DAY
-----------
2012/11/2
++++++++++++++++++++++++++++++++++++++++++++++++++++++++
转换函数
=======================
to_char
=======================
薪水是否可以显示指定的货币符号
9：显示数字，并忽略前面的0
0：显示数字，如位数不足，则用0补全
.：在指定的位置显示小数点
,：在指定位置显示逗号
$：在数字前加美元
L：在数字前加本地货币符号
C：在数字前加国际货币符号
G：在指定位置显示组分隔符
D：在指定位置显示小数点符号(.)
SQL> select to_char(sal, 'L00G999D99') from emp;
TO_CHAR(SAL,'L00G999D99')
-------------------------
￥00,800.00
￥01,600.00
=======================
to_date
=======================
日期是否可以显示 时/分/秒
yy：两位数字的年份 2004-->04
yyyy：思维数字的年份 2004年
mm：两位数字的月份 8月-->8
dd：两位数字的天30号-->30
hh24：8点-->20
hh12：8点-->08
mi、ss：显示分钟\秒
to_char和to_date的区别：
to_char(hiredate, 'yyyy-mm-dd'):是将日期转换成 字符串
to_date('1991-10-26', 'yyyy-mm-dd'):是将字符串转换成日期对象
=======================
to_number
=======================
字符串转换成数值
SQL> select to_number('123') from dual;
TO_NUMBER('123')
----------------
123
=======================
bfilname(dir, file)
=======================
指定一个外部的二进制文件
insert into file_test values(bfilename('lob_dir1', 'image1.gif'));
=======================
decode
=======================
SQL> select emp.*, decode(deptno,10, '10号部门', 20, '20号部门'，30, '30号部门'，40, '40号部门', '没有部门') from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DECODE(DEPTNO,10,'10号部门',20
----- ---------- --------- ----- ----------- --------- --------- ------ ------------------------------
7369 SMITH CLERK 7902 1980/12/17 800.00 20 20号部门
++++++++++++++++++++++++++++++++++++++++++++++++++++++++
系统函数
=======================
UID[唯一表示当前用户]
=======================
SQL> select uid from dual;
UID
----------
84
=======================
显示当前用户
=======================
SQL> show user;
User is "scott"
或者：
SQL> select user from dual;
USER
------------------------------
SCOTT
=======================
sys_context
=======================
①USEREVN
返回当前用户环境的信息,opt可以是:
ENTRYID,SESSIONID,TERMINAL,ISDBA,LABLE,LANGUAGE,CLIENT_INFO,LANG,VSIZE
ISDBA 查看当前用户是否是DBA如果是则返回true
SQL>select sys_context('userenv', 'isdba') from dual; 或者
SQL> select userenv('isdba') from dual;
USEREN
------
FALSE
SQL> select userenv('isdba') from dual;
USEREN
------
TRUE
SESSION
返回会话标志
SQL>select sys_context('userenv', 'sessionid') from dual; 或者
SQL> select userenv('sessionid') from dual;
USERENV('SESSIONID')
--------------------
152
ENTRYID
返回会话人口标志
SQL>select sys_context('userenv', 'entryid') from dual;或者
SQL> select userenv('entryid') from dual;
USERENV('ENTRYID')
------------------
0
INSTANCE
返回当前INSTANCE的标志
SQL>select sys_context('userenv', 'instance') from dual;或者
SQL> select userenv('instance') from dual;
USERENV('INSTANCE')
-------------------
1
LANGUAGE
返回当前环境变量
SQL>select sys_context('userenv', 'language') from dual;或者
SQL> select userenv('language') from dual;
USERENV('LANGUAGE')
----------------------------------------------------
SIMPLIFIED CHINESE_CHINA.ZHS16GBK
LANG
返回当前环境的语言的缩写
SQL>select sys_context('userenv', 'lang') from dual;或者
SQL> select userenv('lang') from dual;
USERENV('LANG')
----------------------------------------------------
ZHS
TERMINAL
返回用户的终端或机器的标志
SQL>select sys_context('userenv', 'terminal') from dual;或者
SQL> select userenv('terminal') from dual;
USERENV('TERMINA
----------------
GAO
VSIZE(X)
返回X的大小(字节)数
SQL> select vsize(user),user from dual;
VSIZE(USER) USER
----------- ------------------------------
6 SYSTEM
---------------------
多行函数
---------------------
=======================
nvl
=======================
nvl(comm, 0)
1、 如果comm字段不为null 则返回 comm 的值
如果 comm 为null 则返回 0
2、空参加运算，会返回空
=======================
max, min,avg,sum,count
=======================
max：最大值
min：最小值
avg：平均值
1、不会对null进行统计
2、可以这样计算？
sum(comm)/count(*)
sum：总和
count：总记录
1、count(comm)；不会对null进行统计
这些函数，不能用在where子句中
=======================
avg(DISTINCT|ALL)[默认all]
=======================
all表示对所有的值求平均值,distinct只对不同的值求平均值
SQLWKS> create table table3(xm varchar(8),sal number(7,2));
语句已处理。
SQLWKS> insert into table3 values('gao',1111.11);
SQLWKS> insert into table3 values('gao',1111.11);
SQLWKS> insert into table3 values('zhu',5555.55);
SQLWKS> commit;
SQL> select avg(distinct sal) from gao.table3;
AVG(DISTINCTSAL)
----------------
3333.33
SQL> select avg(all sal) from gao.table3;
AVG(ALLSAL)
-----------
2592.59
=======================
cube
=======================
SQL> select deptno, avg(sal) from emp group by cube(deptno, job);
DEPTNO AVG(SAL)
------ ----------
2073.21428
1037.5
3000
2758.33333
1400
5000
10 2916.66666
10 1300
10 2450
10 5000
20 2175
20 950
20 3000
20 2975
30 1566.66666
30 950
30 2850
30 1400
18 rows selected