第五章 SQL 函数 (ORACLE) 1
目录 字符函数 数值函数 日期函数 转换函数 2
字符函数 小写函数 LOWER (col value) Select LOWER(dname), LOWER( SQL COURSE ) From DEPT LOWER (dname) research sales operations accounting LOWER( SQL COURSE ) sql course sql course sql course sql course 3
字符函数 续. 大写函数 UPPER(col value) Select ename From EMP Where ename = UPPER( smith ) ename SMITH 4
字符函数 续. 首字母大写函数 INITCAP(col value) Select INITCAP(dname), INITCAP(loc) From DEPT INITCAP(dname) Accounting Research Sales Operations INITCAP(loc) New York Dallas Chicago Boston 5
字符函数 续. 连接函数 CONCAT(char1,char2) Select ename,job,concat(ename,job) job1 From EMP Where e# = 7902 ename job job1 James clerk Jamesclerk 6
字符函数 续. 左填充函数 LPAD(col value,n, string ) Select LPAD(dname,15, * ), LPAD(dname, 15), LPAD(d#, 10,. ) From DEPT LPAD(dname,15, * ) LPAD(dname,15) LPAD(d#,10,. ) *******Research Research..20 **********Sales Sales..30 *****Operations Operations..40 *****Accounting Accounting..10 7
字符函数 续. 右填充函数 RPAD(col value,n, string ) Select RPAD(dname,15, * ), RPAD(dname, 15), RPAD(d#, 10,. ) From DEPT RPAD(dname,15, * ) RPAD(dname,15) RPAD(d#,10,. ) Research ******* Research 20.. Sales ********** Sales 30.. Operations ****** Operations 40.. Accounting ****** Accounting 10.. 8
字符函数 续. 取子串函数 SUBSTR(col value,pos,n) Select SUBSTR( Oracle,2,4), SUBSTR(dname,2), SUBSTR(dname,3,5) From DEPT SUBSTR( Oracle,2,4) SUBSTR(dname,2) SUBSTR(dname,3,5) racl esearch searc racl ales les racl perations erati racl ccounting count 9
字符函数 续. 查找字符串位置 INSTR(col value, string,pos,n) Select dname, INSTR(dname, A ), INSTR(dname, es ),INSTR(dname, c,1,2) From DEPT Dname INSTR(dname, A ) INSTR(dname, ES ) INSTR(dname, c,1,2) ACCOUNTING 1 0 3 RESEARCH 5 2 0 SALES 2 4 0 OPERATIONS 5 0 0 10
字符函数 续. 左裁剪函数 LTRIM(col value, char/s ) Select dname, LTRIM(dname, A ), LTRIM(dname, AS ), LTRIM(dname, ASOP ) From DEPT Dname LTRIM(dname, A ) LTRIM(dname, AS ) LTRIM(dname, ASOP ) RESEARCH RESEARCH RESEARCH RESEARCH SALES SALES LES LES OPERATIONS OPERATIONS OPERATIONS ERATIONS ACCOUNTING CCOUNTING CCOUNTING CCOUNTING 11
字符函数 续. 右裁剪函数 RTRIM(col value, char/s ) Select dname, RTRIM(dname, G ), RTRIM(dname, GHS ), RTRIM(dname, N ) From DEPT Dname RTRIM(dname, G ) RTRIM(dname, GHS ) RTRIM(dname, N ) RESEARCH RESEARCH RESEARC RESEARCH SALES SALES SALE SALES OPERATIONS OPERATIONS OPERATION OPERATIONS ACCOUNTING ACCOUNTIN ACCOUNTIN ACCOUNTING 12
字符函数 续. 求长度函数 LENGTH(col value) Select LENGTH( SQL COURSE ), LENGTH(d#), LENGTH(dname) From DEPT LENGTH( SQL COURSE ) LENGTH(d#) LENGTH(dname) 10 2 8 10 2 5 10 2 10 10 2 10 13
字符函数 续. 转写函数 TRANSLATE(col value, from, to) Select ename, TRANSLATE(ename, C, P ), job, TRANSLATE(job, AR, IT ) From EMP Where d#=10 ename TRANSLATE(ename, C, P ) job TRANLATE(job, AR, IT ) CLARK PLARK MANAGER MINIGET KING KING PRESIDENT PTESIDENT MILIER MILIER CLERK CLETK 14
字符函数 续. 置换函数 REPLACE(col value, string, replacement_string) Select job, REPLACE(job, SALESMAN, SALESPERSON ) AS job1, ename REPLACE(ename, CO, PX ) AS ename1 From EMP job job1 ename ename1 ANALYST ANALYST SCOTT SPXTT SALESMAN SALESPERSON TURNER TURNER SALESMAN SALESPERSON ALLEN ALLEN MANAGER MANAGER CLARK CLARK 15
函数组合举例 字符函数 续. Select dname, LENGTH(dname),LENGTH(dname)- LENGTH(TRANSLATE(dname, AS, A )) AS namel From DEPT dname LENGTH(dname) namel RESEARCH 8 1 SALES 5 2 OPERATIONS 10 1 ACCOUNTING 10 0 16
数值函数 ROUND(col n) TRUNC(col value,n) CEIL(col value) FLOOR(col value) POWER(col value,n) EXP(n) SQRT(col value) SIGN(col value) 17
数值函数 续. ABS(col value) MOD(value1,value2) LOG(m,n) SIN(n) TAN(n) COS(n) 18
日期函数 日期存储 Century Year Month Day Hours Minutes Seconds 19
日期函数 续. Sysdate 伪列名 (pseudo-column) 返回系统日期. e.g. Select SYSDATE From SYS.dual 注 : Dual 是系统哑表 ( dummy table). 20
日期函数 续. 日期类型运算符 date + number date number date date date +number/24 e.g. Select hiredate, hiredate+7, hiredate-7 From EMP Where hiredate Like %JUN% hiredate hiredate+7 hiredate-7 13-JUN-03 20-JUN-03 06-JUN-03 11-JUN-04 18-JUN-04 04-JUN-04 04-JUN-04 11-JUN-04 28-MAY-04 25-JUN-05 02-JUL-05 18-JUN-05 21
日期函数 续. MONTHS_BETWEEN(date1,date2) Select MONTHS_BETWEEN (SYSDATE,hiredate), MONTHS_BETWEEN( 01-JAN-94, 05-NOV- 98 ) From EMP MONTHS-BETWEEN (SYSDATE,hiredate) MONTHS-BETWEEN( 01-JAN-94, 05-NOV- 98 ) 65.0873622-58.129032 60.5067171-58.129032 22
日期函数 续. ADD_MONTHS(date,n) Select hiredate, ADD_MONTHS(hiredate,3) newdate1, ADD_MONTHS(hiredate,-3) newdate2 From EMP Where d#=10 hiredate newdate1 newdate2 14-MAY-04 14-AUG-04 14-FEB-04 31-OCT-03 31-JAN-04 31-JUL-03 04-JUN-04 04-SEP-04 04-MAR-04 23
日期函数 续. NEXT_DAY(date1,char) Select hiredate, NEXT_DAY(hiredate, FRIDAY ) From EMP newday1,next_day(hiredate,6) newday2 hiredate newday1 newday2 14-MAY-04 19-MAY-04 19-MAY-04 09-JUL-04 14-JUL-04 14-JUL-04 24
日期函数 续. LAST_DAY(date1) Find the date of the last day of the month that contains date1. Select hiredate, LAST_DAY(hiredate), LAST_DAY( 15-MAY- 98) From EMP hiredate LAST_DAY(hiredate) LAST_DAY( 15-MAY-97 ) 04-DEC-94 31-DEC-94 31-MAY-97 02-APR-94 30-APR-94 31-MAY-97 25
日期函数 续. 求首日函数 TRUNC(date1, char ) char= MONTH / YEAR, Select SYSTADE,TRUNC(SYSDATE, MONTH ) newdate1, TRUNC(SYSDATE, YEAR ) newdate2 From SYS.DUAL SYSDATE newdate1 newdate2 04-DEC-09 01-DEC-09 01-JAN-09 26
转换函数 TO_CHAR(date, date picture ) Select TO_CHAR(SYSDATE, DAY, DDTH MONTH YYYY ) From SYS.DUAL TO_CHAR(SYSDATE, DAY, DDTH MONTH YYYY ) FRIDAY, 21TH MARCH 2003 Select TO_CHAR(SYSDATE, fmday, DDTH MONTH YYYY ) From SYS.DUAL TO_CHAR(SYSDATE, fmday, DDTH MONTH YYYY ) FRIDAY, 21TH MARCH 2003 27
例 转换函数 续. Select TO_CHAR(SYSDATE, HH:MI:SS ) From SYS.DUAL TO_CHAR(SYSDATE, HH:MI:SS ) 08:16:24 Select TO_CHAR(SAL, $9,999 ) From EMP TO_CHAR(SAL, $9,999 ) $1,000 $2,975 $1,250 28
TO_NUMBER Select ename, ename, job, sal From EMP Where sal > TO_NUMBER( 1500 ) 转换函数 续. TO_DATE Select e#, ename, hiredate From EMP Where hiredate = TO_DATE( JUN 4, 1994, MONTH dd, YYYY ) e# ename hiredate 7844 TUENER 04-JUN-94 29
转换函数 续. DECODE(col/<exp>, search1,result, [search2,result2,,]default) Select ename,job, DECODE(job, CLERK, WORKER, MANAGER, BOSS, UNDEFINED ) decodejob From EMP ename job decodejob smith CLERK WORKER ALLEN SALESMAN UNFEFINED jones MANAGER BOSS 30
更多例子 转换函数 续. Select status, DECODE(status, 20, 20%, 30, 30% 40, 40%, 10% ) decodestatus From S status decodestatus 20 20% 30 30% 50 10% 40 40% 31
其它函数 转换函数 续. NVL(col value, val) GREATEST(col vale1, col value2, ) LEAST(col value1, col value2, ) VSIZE(col value) 32
作业 根据自己生活 学习中的需求, 创建一个有 2-3 个关系的数据库 写出对应的完整 Create 语句 在创建的数据库上给出两个查询访问 ( 要求有多表 / 分组查询需求等 ), 并用 SQL 语句实现, 要求如下 : 先给出查询题目 然后写出对应的 SQL 语句 33