Function of SQL

Size: px

Start display at page:

Download "Function of SQL"

战妻时
6 years ago
Views:

1 第五章 SQL 函数 (ORACLE) 1

2 目录字符函数数值函数日期函数转换函数 2

3 字符函数小写函数 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

4 字符函数续. 大写函数 UPPER(col value) Select ename From EMP Where ename = UPPER( smith ) ename SMITH 4

5 字符函数续. 首字母大写函数 INITCAP(col value) Select INITCAP(dname), INITCAP(loc) From DEPT INITCAP(dname) Accounting Research Sales Operations INITCAP(loc) New York Dallas Chicago Boston 5

6 字符函数续. 连接函数 CONCAT(char1,char2) Select ename,job,concat(ename,job) job1 From EMP Where e# = 7902 ename job job1 James clerk Jamesclerk 6

7 字符函数续. 左填充函数 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

8 字符函数续. 右填充函数 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

9 字符函数续. 取子串函数 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

10 字符函数续. 查找字符串位置 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 RESEARCH SALES OPERATIONS

11 字符函数续. 左裁剪函数 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

12 字符函数续. 右裁剪函数 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

13 字符函数续. 求长度函数 LENGTH(col value) Select LENGTH( SQL COURSE ), LENGTH(d#), LENGTH(dname) From DEPT LENGTH( SQL COURSE ) LENGTH(d#) LENGTH(dname)

14 字符函数续. 转写函数 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

15 字符函数续. 置换函数 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

16 函数组合举例字符函数续. 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

17 数值函数 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

18 数值函数续. ABS(col value) MOD(value1,value2) LOG(m,n) SIN(n) TAN(n) COS(n) 18

19 日期函数日期存储 Century Year Month Day Hours Minutes Seconds 19

20 日期函数续. Sysdate 伪列名 (pseudo-column) 返回系统日期. e.g. Select SYSDATE From SYS.dual 注 : Dual 是系统哑表 ( dummy table). 20

21 日期函数续. 日期类型运算符 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 JUN JUN JUN JUN JUN JUN JUN MAY JUN JUL JUN-05 21

22 日期函数续. 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 )

23 日期函数续. 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 AUG FEB OCT JAN JUL JUN SEP MAR-04 23

24 日期函数续. NEXT_DAY(date1,char) Select hiredate, NEXT_DAY(hiredate, FRIDAY ) From EMP newday1,next_day(hiredate,6) newday2 hiredate newday1 newday2 14-MAY MAY MAY JUL JUL JUL-04 24

25 日期函数续. 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 DEC MAY APR APR MAY-97 25

26 日期函数续. 求首日函数 TRUNC(date1, char ) char= MONTH / YEAR, Select SYSTADE,TRUNC(SYSDATE, MONTH ) newdate1, TRUNC(SYSDATE, YEAR ) newdate2 From SYS.DUAL SYSDATE newdate1 newdate2 04-DEC DEC JAN-09 26

27 转换函数 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

28 例转换函数续. 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

29 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

30 转换函数续. 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

31 更多例子转换函数续. 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

32 其它函数转换函数续. NVL(col value, val) GREATEST(col vale1, col value2, ) LEAST(col value1, col value2, ) VSIZE(col value) 32

33 作业根据自己生活学习中的需求, 创建一个有 2-3 个关系的数据库写出对应的完整 Create 语句在创建的数据库上给出两个查询访问 ( 要求有多表 / 分组查询需求等 ), 并用 SQL 语句实现, 要求如下 : 先给出查询题目然后写出对应的 SQL 语句 33

untitled

untitled 1 2 3 4 5 A 800 700 600 500 400 300 200 100 0-100 10000 9500 9000 8500 8000 7500 7000 6500 6000 2006.1-2007.5 A 1986.1-1991.12 6 7 6 27 WIND A 52.67 2007 44 8 60 55 50 45 40 35 30 25 20 15 10 2001-05 2002-02