sql基本语句（二）单行函数
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 ------------ sangjc
2.操作数字的函数
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 40
3）ceil 取整，上进位，和trunc全部去掉正好相反
scott@TEST>select ceil(45.001)from dual; CEIL(45.001) ------------ 46
4）abs 取绝对值
scott@TEST>select abs(-23.00)from dual; ABS(-23.00) ----------- 23
5）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-RR
2）重新设定日期的显示格式
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:33
3）日期的函数操作
计算两个日期之间的天数。两个日期相减的结果单位为天，往往是带小数点。我们可以通过函数进行取整
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-08
2.显式数据类型转换
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 eighteenth
2）数字转换为字符串
注意：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 ---------------- 141
to_number 将十六进制的数转换成十进制的数
scott@TEST>select to_number('abc32','xxxxxxx')from dual; TO_NUMBER('ABC32','XXXXXXX') ---------------------------- 703538
5.操作数据为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 0
7.分组统计函数
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
