1.字符操作函数：
大小写操作函数：
lower：将字符串转换成小写
upper：将字符串转换成大写
initcap：将字符串转换成首字母大写
SQL> select lower(ename),upper(ename),initcap(ename)from emp;
LOWER(ENAME) UPPER(ENAME) INITCAP(ENAME)
-------------------- -------------------- --------------------
smith SMITH Smith
allen ALLEN Allen
ward WARD Ward
jones JONES Jones
martin MARTIN Martin
blake BLAKE Blake
clark CLARK Clark
scott SCOTT Scott
king KING King
turner TURNER Turner
adams ADAMS Adams
LOWER(ENAME) UPPER(ENAME) INITCAP(ENAME)
-------------------- -------------------- --------------------
james JAMES James
ford FORD Ford
miller MILLER Miller
14 rows selected.
SQL> select lower('mf TR'),upper('mf TR'),initcap('mf TR') from dual;
LOWER('MFT UPPER('MFT INITCAP('M
---------- ---------- ----------
mf tr MF TR Mf Tr字符串操作函数：concat、length、substr、instr、trim、replace、lpad、rpad
① concat：将两个字符连接到一起，只能连接两个字符。如果想要连接多个字符，可以嵌套使用，或者借助||，也可以实现
SQL> select ename,job,concat(ename,job) from emp;
ENAME JOB CONCAT(ENAME,JOB)
-------------------- ------------------ --------------------------------------
SMITH CLERK SMITHCLERK
ALLEN SALESMAN ALLENSALESMAN
WARD SALESMAN WARDSALESMAN
JONES MANAGER JONESMANAGER
MARTIN SALESMAN MARTINSALESMAN
BLAKE MANAGER BLAKEMANAGER
CLARK MANAGER CLARKMANAGER
SCOTT ANALYST SCOTTANALYST
KING PRESIDENT KINGPRESIDENT
TURNER SALESMAN TURNERSALESMAN
ADAMS CLERK ADAMSCLERK
ENAME JOB CONCAT(ENAME,JOB)
-------------------- ------------------ --------------------------------------
JAMES CLERK JAMESCLERK
FORD ANALYST FORDANALYST
MILLER CLERK MILLERCLERK
14 rows selected.
scott@TEST>select ename,job,concat(concat(ename,' IS A '),job)from emp;
ENAME JOB CONCAT(CONCAT(ENAME,'ISA'),JOB)
-------------------- ------------------ --------------------------------------------------
SMITH CLERK SMITH IS A CLERK
ALLEN SALESMAN ALLEN IS A SALESMAN
WARD SALESMAN WARD IS A SALESMAN
JONES MANAGER JONES IS A MANAGER
MARTIN SALESMAN MARTIN IS A SALESMAN
BLAKE MANAGER BLAKE IS A MANAGER
CLARK MANAGER CLARK IS A MANAGER
SCOTT ANALYST SCOTT IS A ANALYST
KING PRESIDENT KING IS A PRESIDENT
TURNER SALESMAN TURNER IS A SALESMAN
ADAMS CLERK ADAMS IS A CLERK
JAMES CLERK JAMES IS A CLERK
FORD ANALYST FORD IS A ANALYST
MILLER CLERK MILLER IS A CLERK
14 rows selected.
scott@TEST>select ename,job,concat(ename||' IS A ',job)from emp;
ENAME JOB CONCAT(ENAME||'ISA',JOB)
-------------------- ------------------ --------------------------------------------------
SMITH CLERK SMITH IS A CLERK
ALLEN SALESMAN ALLEN IS A SALESMAN
WARD SALESMAN WARD IS A SALESMAN
JONES MANAGER JONES IS A MANAGER
MARTIN SALESMAN MARTIN IS A SALESMAN
BLAKE MANAGER BLAKE IS A MANAGER
CLARK MANAGER CLARK IS A MANAGER
SCOTT ANALYST SCOTT IS A ANALYST
KING PRESIDENT KING IS A PRESIDENT
TURNER SALESMAN TURNER IS A SALESMAN
ADAMS CLERK ADAMS IS A CLERK
JAMES CLERK JAMES IS A CLERK
FORD ANALYST FORD IS A ANALYST
MILLER CLERK MILLER IS A CLERK
14 rows selected.② 下面三个函数是求字符串的长度，字符串要用单引号。
length：按照字
lengthb：按照字节
lengthc：unicode的长度
select length('张三') from dual;
LENGTH('张三') --------------
2
SQL> select lengthb('张三') from dual;
LENGTHB('张三') ---------------
4
SQL> select lengthc('张三') from dual;
LENGTHC('ÕÅÈý') ---------------
2③ substr（字符串,m,n）,m是从第几个字符开始，如果为负的意思是从后边的第几个开始。n是输多少个，如果不写，就一直复制到结尾。
SQL> select ename,substr(ename,1,1)"first",substr(ename,-1,1)"last"from emp;
ENAME fi la
-------------------- -- --
SMITH S H
ALLEN A N
WARD W D
JONES J S
MARTIN M N
BLAKE B E
CLARK C K
SCOTT S T
KING K G
TURNER T R
ADAMS A S
JAMES J S
FORD F D
MILLER M R
14 rows selected.④ instr 求子串在父串中的位置，0表示没有在父串中找到该子串。显示的是父串中第一次出现的位置
scott@TEST>select ename,instr(ename,'A')"a" from emp;
ENAME a
-------------------- ----------
SMITH 0
ALLEN 1
WARD 2
JONES 0
MARTIN 2
BLAKE 3
CLARK 3
SCOTT 0
KING 0
TURNER 0
ADAMS 1
JAMES 2
FORD 0
MILLER 0
14 rows selected.⑤ trim 截掉连续的字符段，一般用来去掉空格。
leading 截掉前置的连续字符
trailing 截掉后置的连续字符
both 前置和后置的同时截掉，如果不指明，默认的同时截取掉。
scott@TEST>select trim(leading 'a'from 'aaaaabababaaaaa')from dual;
TRIM(LEADING'A'FROM'
--------------------
bababaaaaa
scott@TEST>select trim(trailing 'a'from 'aaaaabababaaaaa')from dual;
TRIM(TRAILING'A'FROM
--------------------
aaaaababab
scott@TEST>select trim(both 'a'from 'aaaaabababaaaaa')from dual;
TRIM(BOTH'
----------
babab
scott@TEST>select trim('a'from 'aaaaabababaaaaa')from dual;
TRIM('A'FR
----------
babab⑥ lpad 在列的左边铺垫
rpad 在列的右边铺垫
scott@TEST>select lpad(ename,20,'-') ename,rpad (ename,20,'a')ename from emp;
ENAME ENAME
---------------------------------------- ----------------------------------------
---------------SMITH SMITHaaaaaaaaaaaaaaa
---------------ALLEN ALLENaaaaaaaaaaaaaaa
----------------WARD WARDaaaaaaaaaaaaaaaa
---------------JONES JONESaaaaaaaaaaaaaaa
--------------MARTIN MARTINaaaaaaaaaaaaaa
---------------BLAKE BLAKEaaaaaaaaaaaaaaa
---------------CLARK CLARKaaaaaaaaaaaaaaa
---------------SCOTT SCOTTaaaaaaaaaaaaaaa
----------------KING KINGaaaaaaaaaaaaaaaa
--------------TURNER TURNERaaaaaaaaaaaaaa
---------------ADAMS ADAMSaaaaaaaaaaaaaaa
---------------JAMES JAMESaaaaaaaaaaaaaaa
----------------FORD FORDaaaaaaaaaaaaaaaa
--------------MILLER MILLERaaaaaaaaaaaaaa
14 rows selected.
//左边以‘-’做铺垫满二十位，右边以a为铺垫满二十位如果位数不足，按照截取后的结果显示，不报错
scott@TEST>select lpad(sal,2,' ')ename,rpad(sal,10,' ')ename,sal from emp;
ENAM ENAME SAL
---- -------------------- ----------
80 800 800
16 1600 1600
12 1250 1250
29 2975 2975
12 1250 1250
28 2850 2850
24 2450 2450
30 3000 3000
50 5000 5000
15 1500 1500
11 1100 1100
95 950 950
30 3000 3000
13 1300 1300
14 rows selected.⑦replace 替换
scott@TEST>select replace('sangmu','mu','jc')from dual; //将mu替换成jc
REPLACE('SAN
------------
sangjc2.操作数字的函数
1）round是四舍五入
以小数点为核心，2是小数点后两位，0可以不写，表示取整，-1表示小数点前一位
scott@TEST>select round(45.923,2),round(45.923,0),round(45.923,-1)from dual;
ROUND(45.923,2) ROUND(45.923,0) ROUND(45.923,-1)
--------------- --------------- ----------------
45.92 46 50
scott@TEST>select round(45.925,2),round(45.323,0),round(44.923,-1)from dual;
ROUND(45.925,2) ROUND(45.323,0) ROUND(44.923,-1)
--------------- --------------- ----------------
45.93 45 40 //注意这两个对比取值，四舍五入！四舍五入看的是截取位数的后边一个数字，当取整的时候，后边的数字是小数点。2）trunc 直接截断，全部舍弃
scott@TEST>select trunc(45.925,2),trunc(45.323,0),trunc(44.923,-1)from dual;
TRUNC(45.925,2) TRUNC(45.323,0) TRUNC(44.923,-1)
--------------- --------------- ----------------
45.92 45 403）ceil 取整，上进位，和trunc全部去掉正好相反
scott@TEST>select ceil(45.001)from dual;
CEIL(45.001)
------------
464）abs 取绝对值
scott@TEST>select abs(-23.00)from dual;
ABS(-23.00)
-----------
235）mod 取余数
scott@TEST>select sal,mod(sal,2000)from emp; //对工资除以2000取余
SAL MOD(SAL,2000)
---------- -------------
800 800
1600 1600
1250 1250
2975 975
1250 1250
2850 850
2450 450
3000 1000
5000 1000
1500 1500
1100 1100
950 950
3000 1000
1300 1300
14 rows selected.3.操作日期的函数
元素 | 结果 |
YYYY | 用数字表示的完整年份 |
YEAR | 拼写出的年份（用英文表示） |
RR | 显示年份，不显示世纪 |
MM | 月份的两位数值 |
MONTH | 月份的完整名称 |
MON | 月份的三个字母缩写 |
DY | 一周中某日的三个字母缩写 |
DAY | 一周中某日的完整名称 |
DD | 用数字表示的月份中某 |
mi | 分 |
ss | 秒 |
1）查看当前数据库的时间和显示格式
为了美观，设置了col value for a20，代表的含义是凡是列的名称是value的，都按照20个宽度来显示，取消定义用 col value clear
scott@TEST>select sysdate from dual;
SYSDATE
------------
18-APR-17
scott@TEST>col value for a20
scott@TEST>select * from nls_session_parameters where parameter='NLS_DATE_FORMAT';
PARAMETER VALUE
------------------------------------------------------------ --------------------
NLS_DATE_FORMAT DD-MON-RR2）重新设定日期的显示格式
scott@TEST>alter session set NLS_DATE_FORMAT='yyyy/mm/dd:hh24:mi:ss';
Session altered.
scott@TEST>select sysdate from dual;
SYSDATE
-------------------
2017/04/18:21:46:333）日期的函数操作
计算两个日期之间的天数。两个日期相减的结果单位为天，往往是带小数点。我们可以通过函数进行取整
scott@TEST>select round(sysdate-hiredate) days,sysdate,hiredate from emp;
DAYS SYSDATE HIREDATE
---------- -------------------- --------------------
13271 2017/04//18:01:35:11 1980/12//17:00:00:00
13206 2017/04//18:01:35:11 1981/02//20:00:00:00
13204 2017/04//18:01:35:11 1981/02//22:00:00:00
13165 2017/04//18:01:35:11 1981/04//02:00:00:00
12986 2017/04//18:01:35:11 1981/09//28:00:00:00
13136 2017/04//18:01:35:11 1981/05//01:00:00:00
13097 2017/04//18:01:35:11 1981/06//09:00:00:00
10957 2017/04//18:01:35:11 1987/04//19:00:00:00
12936 2017/04//18:01:35:11 1981/11//17:00:00:00
13006 2017/04//18:01:35:11 1981/09//08:00:00:00
10923 2017/04//18:01:35:11 1987/05//23:00:00:00
12920 2017/04//18:01:35:11 1981/12//03:00:00:00
12920 2017/04//18:01:35:11 1981/12//03:00:00:00
12869 2017/04//18:01:35:11 1982/01//23:00:00:00
14 rows selected.计算两个日期之间的月间隔数
scott@TEST>select months_between(sysdate,hiredate),sysdate,hiredate from emp;
MONTHS_BETWEEN(SYSDATE,HIREDATE) SYSDATE HIREDATE
-------------------------------- -------------------- --------------------
436.034391 2017/04//18:01:35:14 1980/12//17:00:00:00
433.937617 2017/04//18:01:35:14 1981/02//20:00:00:00
433.873101 2017/04//18:01:35:14 1981/02//22:00:00:00
432.518262 2017/04//18:01:35:14 1981/04//02:00:00:00
426.679553 2017/04//18:01:35:14 1981/09//28:00:00:00
431.55052 2017/04//18:01:35:14 1981/05//01:00:00:00
430.292456 2017/04//18:01:35:14 1981/06//09:00:00:00
359.969875 2017/04//18:01:35:14 1987/04//19:00:00:00
425.034391 2017/04//18:01:35:14 1981/11//17:00:00:00
427.324714 2017/04//18:01:35:14 1981/09//08:00:00:00
358.840843 2017/04//18:01:35:14 1987/05//23:00:00:00
424.486004 2017/04//18:01:35:14 1981/12//03:00:00:00
424.486004 2017/04//18:01:35:14 1981/12//03:00:00:00
422.840843 2017/04//18:01:35:14 1982/01//23:00:00:00
14 rows selected.在日期上加上6个月的时间
scott@TEST>select hiredate,add_months(hiredate,6) from emp;
HIREDATE ADD_MONTHS(HIREDATE,
-------------------- --------------------
1980/12//17:00:00:00 1981/06//17:00:00:00
1981/02//20:00:00:00 1981/08//20:00:00:00
1981/02//22:00:00:00 1981/08//22:00:00:00
1981/04//02:00:00:00 1981/10//02:00:00:00
1981/09//28:00:00:00 1982/03//28:00:00:00
1981/05//01:00:00:00 1981/11//01:00:00:00
1981/06//09:00:00:00 1981/12//09:00:00:00
1987/04//19:00:00:00 1987/10//19:00:00:00
1981/11//17:00:00:00 1982/05//17:00:00:00
1981/09//08:00:00:00 1982/03//08:00:00:00
1987/05//23:00:00:00 1987/11//23:00:00:00
1981/12//03:00:00:00 1982/06//03:00:00:00
1981/12//03:00:00:00 1982/06//03:00:00:00
1982/01//23:00:00:00 1982/07//23:00:00:00
14 rows selected.从该日期开始，下个星期五是哪一天。注意，日期的格式和字符集是敏感的，如果是中文的客户端，则用‘星期五’来表达，如果是英文客户端，则用‘Friday’来表达
scott@TEST>select hiredate,next_day(hiredate,'friday') from emp;
HIREDATE NEXT_DAY(HIREDATE,'F
-------------------- --------------------
1980/12//17:00:00:00 1980/12//19:00:00:00
1981/02//20:00:00:00 1981/02//27:00:00:00
1981/02//22:00:00:00 1981/02//27:00:00:00
1981/04//02:00:00:00 1981/04//03:00:00:00
1981/09//28:00:00:00 1981/10//02:00:00:00
1981/05//01:00:00:00 1981/05//08:00:00:00
1981/06//09:00:00:00 1981/06//12:00:00:00
1987/04//19:00:00:00 1987/04//24:00:00:00
1981/11//17:00:00:00 1981/11//20:00:00:00
1981/09//08:00:00:00 1981/09//11:00:00:00
1987/05//23:00:00:00 1987/05//29:00:00:00
1981/12//03:00:00:00 1981/12//04:00:00:00
1981/12//03:00:00:00 1981/12//04:00:00:00
1982/01//23:00:00:00 1982/01//29:00:00:00
14 rows selected.该日期的月底是哪一天
scott@TEST>select hiredate,last_day(hiredate) from emp;
HIREDATE LAST_DAY(HIREDATE)
-------------------- --------------------
1980/12//17:00:00:00 1980/12//31:00:00:00
1981/02//20:00:00:00 1981/02//28:00:00:00
1981/02//22:00:00:00 1981/02//28:00:00:00
1981/04//02:00:00:00 1981/04//30:00:00:00
1981/09//28:00:00:00 1981/09//30:00:00:00
1981/05//01:00:00:00 1981/05//31:00:00:00
1981/06//09:00:00:00 1981/06//30:00:00:00
1987/04//19:00:00:00 1987/04//30:00:00:00
1981/11//17:00:00:00 1981/11//30:00:00:00
1981/09//08:00:00:00 1981/09//30:00:00:00
1987/05//23:00:00:00 1987/05//31:00:00:00
1981/12//03:00:00:00 1981/12//31:00:00:00
1981/12//03:00:00:00 1981/12//31:00:00:00
1982/01//23:00:00:00 1982/01//31:00:00:00
14 rows selected.日期的进位和截取。数字的进位和截取是以小数点为中心，我们去小数点前或后的值，而日期的进位和截取是以年，月，日，时，分，秒为中心。
如果是按照月截取，则看后边的天数，如果过半，则进位，不过半则舍弃，如果正好是15，则舍弃。
如果是按照年截取，和按月一样，过半则进，不过半则舍。
scott@TEST>select hiredate,round(hiredate,'mm'),round(hiredate,'month') from emp;
HIREDATE ROUND(HIREDATE,'MM') ROUND(HIREDATE,'MONT
-------------------- -------------------- --------------------
1980/12//17:00:00:00 1981/01//01:00:00:00 1981/01//01:00:00:00
1981/02//20:00:00:00 1981/03//01:00:00:00 1981/03//01:00:00:00
1981/02//22:00:00:00 1981/03//01:00:00:00 1981/03//01:00:00:00
1981/04//02:00:00:00 1981/04//01:00:00:00 1981/04//01:00:00:00
1981/09//28:00:00:00 1981/10//01:00:00:00 1981/10//01:00:00:00
1981/05//01:00:00:00 1981/05//01:00:00:00 1981/05//01:00:00:00
1981/06//09:00:00:00 1981/06//01:00:00:00 1981/06//01:00:00:00
1987/04//19:00:00:00 1987/05//01:00:00:00 1987/05//01:00:00:00
1981/11//17:00:00:00 1981/12//01:00:00:00 1981/12//01:00:00:00
1981/09//08:00:00:00 1981/09//01:00:00:00 1981/09//01:00:00:00
1987/05//23:00:00:00 1987/06//01:00:00:00 1987/06//01:00:00:00
1981/12//03:00:00:00 1981/12//01:00:00:00 1981/12//01:00:00:00
1981/12//03:00:00:00 1981/12//01:00:00:00 1981/12//01:00:00:00
1982/01//23:00:00:00 1982/02//01:00:00:00 1982/02//01:00:00:00
14 rows selected.
scott@TEST>select hiredate,round(hiredate,'yy'),round(hiredate,'year') from emp;
HIREDATE ROUND(HIREDATE,'YY') ROUND(HIREDATE,'YEAR
-------------------- -------------------- --------------------
1980/12//17:00:00:00 1981/01//01:00:00:00 1981/01//01:00:00:00
1981/02//20:00:00:00 1981/01//01:00:00:00 1981/01//01:00:00:00
1981/02//22:00:00:00 1981/01//01:00:00:00 1981/01//01:00:00:00
1981/04//02:00:00:00 1981/01//01:00:00:00 1981/01//01:00:00:00
1981/09//28:00:00:00 1982/01//01:00:00:00 1982/01//01:00:00:00
1981/05//01:00:00:00 1981/01//01:00:00:00 1981/01//01:00:00:00
1981/06//09:00:00:00 1981/01//01:00:00:00 1981/01//01:00:00:00
1987/04//19:00:00:00 1987/01//01:00:00:00 1987/01//01:00:00:00
1981/11//17:00:00:00 1982/01//01:00:00:00 1982/01//01:00:00:00
1981/09//08:00:00:00 1982/01//01:00:00:00 1982/01//01:00:00:00
1987/05//23:00:00:00 1987/01//01:00:00:00 1987/01//01:00:00:00
1981/12//03:00:00:00 1982/01//01:00:00:00 1982/01//01:00:00:00
1981/12//03:00:00:00 1982/01//01:00:00:00 1982/01//01:00:00:00
1982/01//23:00:00:00 1982/01//01:00:00:00 1982/01//01:00:00:00
14 rows selected.4.转换函数
1.隐式数据类型转换
这种转换由系统自动完成，将输入的数据类型自动转换成输出的数据类型，如：
varchar2 or char to number
varchar2 or char to date
number to varchar2
date to varchar2
需要注意的是，在类型转换成时间类型的时候，要注意时间类型的格式要正确。
scott@TEST>select ename,sal from emp where sal='800';
ENAME SAL
-------------------- ----------
SMITH 800
scott@TEST>select last_day('26-APR-08')from dual;
LAST_DAY('26
------------
30-APR-082.显式数据类型转换
sql提供了三种函数，用于数据类型转换 to_char、to_date、to_number
1）日期转化为字符串，请说明字符串的格式。
加上fm可以消除前置的零和空格
scott@TEST>select ename,to_char(hiredate,'/yyyy/mm/dd')from emp;
ENAME TO_CHAR(HIREDATE,'/YYY
-------------------- ----------------------
SMITH /1980/12/17
ALLEN /1981/02/20
WARD /1981/02/22
JONES /1981/04/02
MARTIN /1981/09/28
BLAKE /1981/05/01
CLARK /1981/06/09
SCOTT /1987/04/19
KING /1981/11/17
TURNER /1981/09/08
ADAMS /1987/05/23
JAMES /1981/12/03
FORD /1981/12/03
MILLER /1982/01/23
14 rows selected.
scott@TEST>select ename,to_char(hiredate,'fm/yyyy/mm/dd')from emp;
ENAME TO_CHAR(HIREDATE,'FM/Y
-------------------- ----------------------
SMITH /1980/12/17
ALLEN /1981/2/20
WARD /1981/2/22
JONES /1981/4/2
MARTIN /1981/9/28
BLAKE /1981/5/1
CLARK /1981/6/9
SCOTT /1987/4/19
KING /1981/11/17
TURNER /1981/9/8
ADAMS /1987/5/23
JAMES /1981/12/3
FORD /1981/12/3
MILLER /1982/1/23
14 rows selected.日期格式样式的元素
scott@TEST>select to_char(sysdate,'yyyy year mm month mon dd day dy ddsp ddspth')from dual;
TO_CHAR(SYSDATE,'YYYYYEARMMMONTHMONDDDAYDYDDSPDDSPTH')
----------------------------------------------------------------------------------------------------
2017 twenty seventeen 04 april apr 18 tuesday tue eighteen eighteenth2）数字转换为字符串
注意：G只能和D一起用
，只能和.一起使用
元素 | 结果 |
9 | 代表一位数字 |
0 | 强制显示零，但不会改变结果 |
$ | 放置一个浮动的美元符号 |
L | 使用浮动的本地货币符号 |
. | 显示小数点 |
, | 显示作为千位指示符的逗号 |
G | 是千分符 |
D | 是小数点 |
scott@TEST>select ename,to_char(sal,'9999.000')salary from emp; //如果给的位数不够显示，则显示不出来
ENAME SALARY
-------------------- ------------------
SMITH 800.000
ALLEN 1600.000
WARD 1250.000
JONES 2975.000
MARTIN 1250.000
BLAKE 2850.000
CLARK 2450.000
SCOTT 3000.000
KING 5000.000
TURNER 1500.000
ADAMS 1100.000
JAMES 950.000
FORD 3000.000
MILLER 1300.000
14 rows selected.
scott@TEST>select ename,to_char(sal,'$00099999000.00')salary from emp;
ENAME SALARY
-------------------- --------------------------------
SMITH $00000000800.00
ALLEN $00000001600.00
WARD $00000001250.00
JONES $00000002975.00
MARTIN $00000001250.00
BLAKE $00000002850.00
CLARK $00000002450.00
SCOTT $00000003000.00
KING $00000005000.00
TURNER $00000001500.00
ADAMS $00000001100.00
JAMES $00000000950.00
FORD $00000003000.00
MILLER $00000001300.00
14 rows selected.
scott@TEST>select ename,to_char(sal,'l99,999.000') salary from emp; //这里是L 不是1
ENAME SALARY
-------------------- ------------------------------------------
SMITH $800.000
ALLEN $1,600.000
WARD $1,250.000
JONES $2,975.000
MARTIN $1,250.000
BLAKE $2,850.000
CLARK $2,450.000
SCOTT $3,000.000
KING $5,000.000
TURNER $1,500.000
ADAMS $1,100.000
JAMES $950.000
FORD $3,000.000
MILLER $1,300.000
14 rows selected.
scott@TEST>select ename,to_char(sal,'9G999D99') salary from emp;
ENAME SALARY
-------------------- ------------------
SMITH 800.00
ALLEN 1,600.00
WARD 1,250.00
JONES 2,975.00
MARTIN 1,250.00
BLAKE 2,850.00
CLARK 2,450.00
SCOTT 3,000.00
KING 5,000.00
TURNER 1,500.00
ADAMS 1,100.00
JAMES 950.00
FORD 3,000.00
MILLER 1,300.00
14 rows selected.在数据库中16进制的表示是按照字符串来描述的，所以要想将十进制的数转换成十六进制的数使用to_char函数转换
scott@TEST>select to_char(321,'xxx')from dual; //xxx表示转换成多少位
TO_CHAR(
--------
141
scott@TEST>select to_char(321,'xx')from dual; //位数要够 不然会报错
TO_CHA
------
###
scott@TEST>select to_char(321,'xxxxxxx')from dual; //为了不报错，可以多写几位
TO_CHAR(321,'XXX
----------------
141to_number 将十六进制的数转换成十进制的数
scott@TEST>select to_number('abc32','xxxxxxx')from dual;
TO_NUMBER('ABC32','XXXXXXX')
----------------------------
7035385.操作数据为null的函数
1）综合数据类型函数
NVL(expr1,expr2)
如果expr1为非空，就返回expr1，如果expr1为空返回expr2，两个表达式的数据类型一定得一致
NVL2(expr1,expr2,expr3)
如果 expr1为非空，就返回expr2，如果expr1为空返回expr3
NULLIF(expr1,expr2)
如果expr1和expr2相同就返回空，否则返回expr1
COALESCE(expr1,expr2,...,exprn)
返回括号内第一个非空的值
scott@TEST>select ename,nvl(comm,0) from emp; //如果奖金不为空，则输出奖金，奖金为null，则输出0
ENAME NVL(COMM,0)
-------------------- -----------
SMITH 0
ALLEN 300
WARD 500
JONES 0
MARTIN 1400
BLAKE 0
CLARK 0
SCOTT 0
KING 0
TURNER 0
ADAMS 0
JAMES 0
FORD 0
MILLER 0
14 rows selected.
scott@TEST>select ename,nvl(comm,a)from emp; // 两个表达式类型不一致，会报错
select ename,nvl(comm,a)from emp
*
ERROR at line 1:
ORA-00904: "A": invalid identifier
scott@TEST>select ename,nvl2(comm,comm+sal,sal) from emp; //如果奖金不为空，则输出奖金加工资，否则输出工资
ENAME NVL2(COMM,COMM+SAL,SAL)
-------------------- -----------------------
SMITH 800
ALLEN 1900
WARD 1750
JONES 2975
MARTIN 2650
BLAKE 2850
CLARK 2450
SCOTT 3000
KING 5000
TURNER 1500
ADAMS 1100
JAMES 950
FORD 3000
MILLER 1300
14 rows selected.
scott@TEST>select nullif(comm,0)from emp; //如果奖金和第二个表达式一样，则输出为空
NULLIF(COMM,0)
--------------
300
500
1400
14 rows selected.
scott@TEST>select coalesce(comm,sal,0) from emp; //输出第一个不为空的表达式值
COALESCE(COMM,SAL,0)
--------------------
800
300
500
2975
1400
2850
2450
3000
5000
0
1100
950
3000
1300
14 rows selected.6.分支的函数
1.case 语句
scott@TEST>select ename,job,sal,
2 case job when 'CLERK' then 1.1*sal // 工作为clerk的工资涨百分之十
3 when 'SALESMAN' then 1.2*sal // 工作为salesman的工资涨百分之二十
4 else sal end aa // 剩下的取sal值不变，伪列别名为aa
5 from emp;
ENAME JOB SAL AA
-------------------- ------------------ ---------- ----------
SMITH CLERK 800 880
ALLEN SALESMAN 1600 1920
WARD SALESMAN 1250 1500
JONES MANAGER 2975 2975
MARTIN SALESMAN 1250 1500
BLAKE MANAGER 2850 2850
CLARK MANAGER 2450 2450
SCOTT ANALYST 3000 3000
KING PRESIDENT 5000 5000
TURNER SALESMAN 1500 1800
ADAMS CLERK 1100 1210
JAMES CLERK 950 1045
FORD ANALYST 3000 3000
MILLER CLERK 1300 1430
14 rows selected.
scott@TEST>select ename,job,sal,
2 case when job='CLERK' then 1.1*sal //工作为clerk的工资涨百分之十
3 when ename='ALLEN' then 1.2*sal //名字为allen的员工工资涨百分之二十
4 else sal end aa //剩下的工资不变，伪列别名为aa
5 from emp;
ENAME JOB SAL AA
-------------------- ------------------ ---------- ----------
SMITH CLERK 800 880
ALLEN SALESMAN 1600 1920
WARD SALESMAN 1250 1250
JONES MANAGER 2975 2975
MARTIN SALESMAN 1250 1250
BLAKE MANAGER 2850 2850
CLARK MANAGER 2450 2450
SCOTT ANALYST 3000 3000
KING PRESIDENT 5000 5000
TURNER SALESMAN 1500 1500
ADAMS CLERK 1100 1210
JAMES CLERK 950 1045
FORD ANALYST 3000 3000
MILLER CLERK 1300 1430
14 rows selected.decode函数，和case语句一样都是分支语句，但只能改写标准case函数，特殊的如上例第二种就不支持
scott@TEST>select ename,job,sal,decode(
2 job
3 ,'CLERK',1.1*sal
4 ,'SALESMAN',1.2*sal
5 ,sal) aa
6 from emp;
ENAME JOB SAL AA
-------------------- ------------------ ---------- ----------
SMITH CLERK 800 880
ALLEN SALESMAN 1600 1920
WARD SALESMAN 1250 1500
JONES MANAGER 2975 2975
MARTIN SALESMAN 1250 1500
BLAKE MANAGER 2850 2850
CLARK MANAGER 2450 2450
SCOTT ANALYST 3000 3000
KING PRESIDENT 5000 5000
TURNER SALESMAN 1500 1800
ADAMS CLERK 1100 1210
JAMES CLERK 950 1045
FORD ANALYST 3000 3000
MILLER CLERK 1300 1430
14 rows selected.不同工资上的税率不同，每2000一个台阶8000以上一律百分之四十五的税率。
scott@TEST>select ename,sal,
2 decode(trunc(sal/2000,0), //工资税率
3 0,0.00,
4 1,0.09,
5 2,0.20,
6 3,0.30,
7 4,0.40,
8 0.45) aa
9 from emp;
ENAME SAL AA
-------------------- ---------- ----------
SMITH 800 0
ALLEN 1600 0
WARD 1250 0
JONES 2975 .09
MARTIN 1250 0
BLAKE 2850 .09
CLARK 2450 .09
SCOTT 3000 .09
KING 5000 .2
TURNER 1500 0
ADAMS 1100 0
JAMES 950 0
FORD 3000 .09
MILLER 1300 07.分组统计函数
1）组函数
scott@TEST>select min(hiredate),max(hiredate)from emp;
MIN(HIREDATE MAX(HIREDATE
------------ ------------
17-DEC-80 23-MAY-87
//日期的小为早，大为晚
scott@TEST>select count(*),count(comm)from emp;
COUNT(*) COUNT(COMM)
---------- -----------
14 4
//所有组函数，除了count(*)以外，都忽略null值，count是计数，查看有多少行，count(列)是查看该列有多少非空的行
scott@TEST>select avg(comm),avg(nvl(comm,0))from emp;
AVG(COMM) AVG(NVL(COMM,0))
---------- ----------------
550 157.142857
//求平均的奖金，奖金为非空的人的平均，和大平均，所有的人平均，如果奖金为空，就用零来替代
scott@TEST>select sum(comm),count(comm),count(*)from emp;
SUM(COMM) COUNT(COMM) COUNT(*)
---------- ----------- ----------
2200 4 14
//上面的语法验证了组函数忽略null的值
scott@TEST>select count (distinct deptno)from emp;
COUNT(DISTINCTDEPTNO)
---------------------
3
//计算有多少不同的部门代码的个数2）group by 子句
按照部门号码分组，统计工资有多少
scott@TEST>select deptno,sum(sal)from emp group by deptno;
DEPTNO SUM(SAL)
---------- ----------
30 9400
20 10875
10 8750分组的列不在select列表中，这样查询有利于子查询使用，只列出各个部门的工资总和而不显示部门名称
scott@TEST>select sum(sal) from emp group by deptno;
SUM(SAL)
----------
9400
10875
8750在有主函数的select中，不是组函数的列，一定要放在group by子句中
多列分组，每列都一样的才放到一起进行统计
scott@TEST>select deptno,job,sum(sal) from emp group by deptno,job;
DEPTNO JOB SUM(SAL)
---------- ------------------ ----------
20 CLERK 1900
30 SALESMAN 5600
20 MANAGER 2975
30 CLERK 950
10 PRESIDENT 5000
30 MANAGER 2850
10 CLERK 1300
10 MANAGER 2450
20 ANALYST 6000
9 rows selected.having是在结果中再次筛选。having一定得出现在group by 子句得后边。不能独立存在
scott@TEST>select job,avg(sal) from emp group by job having avg(sal)>2000;
JOB AVG(SAL)
------------------ ----------
PRESIDENT 5000
MANAGER 2758.33333
ANALYST 3000当在group by子句中使用having 子句时，查询结果中只返回满足having条件的组
在一个sql语句中可以有where子句和having子句，where子句作用在查询结果分组前，将不符合where条件的行去掉，即在分组之前过滤数据，条件中不能包含聚合函数having子句的作用是选满足条件的组，即在分组之后过滤数据，条件中经常包含聚合函数
使用order by排序时order by子句置于group by 之后 并且 order by 子句的排序标准不能出现在select查询之外的列
在使用group by 时，有一个规则需要遵守，即出现在select列表中的字段，如果没有在组函数中，那么必须出现在group by 子句中。（select中的字段不可以单独出现，必须出现在group语句中或者在组函数中。）
select语句在逻辑上是sql语句最后处理的一个语句
巧用decode函数，改变排版方式
scott@TEST>select sum(decode(deptno,10,1,0)) "10",
2 sum(decode(deptno,20,1,0)) "20",
3 sum(decode(deptno,30,1,0)) "30"
4 from emp;
10 20 30
---------- ---------- ----------
3 5 6
scott@TEST>select sum(decode (to_char(hiredate,'yyyy'),'1980',1,0))"1980",
2 sum(decode(to_char(hiredate,'yyyy'),'1981',1,0))"1981"，
3 sum(decode(to_char(hiredate,'yyyy'),'1982',1,0))"1982"，
4 sum(decode(to_char(hiredate,'yyyy'),'1987',1,0))"1987"
5 from emp;
1980 1981 1982 1987
---------- ---------- ---------- ----------
1 10 1 2
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
