第4章單行函數(shù)ppt課件

1、1Oracle SQL開發(fā)根底開發(fā)根底2課程構(gòu)造課程構(gòu)造內(nèi)容內(nèi)容課時(shí)（課時(shí)（H H）第一章 Oracle數(shù)據(jù)庫(kù)基礎(chǔ)1.8第二章 編寫簡(jiǎn)單的查詢語(yǔ)句1.2第三章 限制數(shù)據(jù)和對(duì)數(shù)據(jù)排序1.5第四章 單行函數(shù)2.5第五章 多表查詢2第六章 分組函數(shù)2第七章 子查詢2第八章 數(shù)據(jù)操作與事務(wù)控制3.5第九章 表和約束4第十章 其他數(shù)據(jù)庫(kù)對(duì)象2.53第四章 單行函數(shù)目的目的:本章旨在向?qū)W員引見：本章旨在向?qū)W員引見：1)字符函數(shù)、數(shù)字函數(shù)和日期函數(shù)字符函數(shù)、數(shù)字函數(shù)和日期函數(shù)2)轉(zhuǎn)換函數(shù)和通用函數(shù)轉(zhuǎn)換函數(shù)和通用函數(shù)時(shí)間：時(shí)間：2.5學(xué)時(shí)學(xué)時(shí)教學(xué)方法：教學(xué)方法：4本章要點(diǎn)本章要點(diǎn)字符函數(shù)數(shù)值函數(shù)日期函數(shù)轉(zhuǎn)換函

2、數(shù)通用函數(shù)函數(shù)嵌套5第四章 單行函數(shù)單行函數(shù):4.1函數(shù)的概念及分類4.2字符函數(shù)4.3數(shù)字函數(shù)4.4日期函數(shù)4.5轉(zhuǎn)換函數(shù)4.6通用函數(shù)64.1.1單行函數(shù)引見單行函數(shù)引見 1/574.1.1單行函數(shù)引見單行函數(shù)引見 2/5函數(shù)類型函數(shù)類型84.1.1單行函數(shù)引見單行函數(shù)引見 3/5語(yǔ)法:函數(shù)名(參數(shù)1，參數(shù)2,)其中的參數(shù)可以是以下之一：變量列名表達(dá)式94.1.1單行函數(shù)引見單行函數(shù)引見 4/5單行函數(shù)還有以下的一些特征：?jiǎn)涡泻瘮?shù)對(duì)單行操作每行前往一個(gè)結(jié)果有能夠前往值與原參數(shù)數(shù)據(jù)類型不一致轉(zhuǎn)換函數(shù)單行函數(shù)可以寫在SELECT、WHERE、ORDER BY子句中有些函數(shù)沒(méi)有參數(shù)，有些函數(shù)包括

3、一個(gè)或多個(gè)參數(shù)函數(shù)可以嵌套104.1.1單行函數(shù)引見單行函數(shù)引見 5/5單行函數(shù)的分類單行函數(shù)的分類11第四章 單行函數(shù)單行函數(shù):4.1函數(shù)的概念及分類4.2字符函數(shù)4.3數(shù)字函數(shù)4.4日期函數(shù)4.5轉(zhuǎn)換函數(shù)4.6通用函數(shù)124.2字符函數(shù)字符函數(shù)字符函數(shù)：主要指參數(shù)類型是字符型，不同函數(shù)前往值能夠是字符型或數(shù)字類型。134.2.1字符大小寫操作函數(shù)字符大小寫操作函數(shù)LOWER(列名|表達(dá)式)：將大寫或大小寫混合的字符轉(zhuǎn)換成小寫UPPER(列名|表達(dá)式) ：將小寫或大小寫混合的字符轉(zhuǎn)換成大寫INITCAP(列名|表達(dá)式) ：將每個(gè)單詞的第一個(gè)字母轉(zhuǎn)換成大寫，其他的字母都轉(zhuǎn)換成小寫函數(shù)函數(shù)結(jié)果結(jié)

4、果LOWER(SQL Course)UPPER(SQL Course)INITCAP(SQL Course)sql courseSQL COURSESql Course144.2.2字符處置函數(shù)字符處置函數(shù)CONCAT:銜接兩個(gè)值,等同于|格式：CONCAT(column1|expression1,column2|expression2)SUBSTR:前往第一個(gè)參數(shù)中從n1字符開場(chǎng)長(zhǎng)度為n2的子串，假設(shè)n1是負(fù)值，表示從后向前數(shù)的abs(n1)位，假設(shè)n2省略,取n1之后的一切字符格式：SUBSTR(column | expression,n1,n2)LENGTH:取字符長(zhǎng)度格式:LENGTH

5、(column | expression)INSTR:前往s1中，子串s2從n1開場(chǎng)，第n2次出現(xiàn)的位置。n1,n2默許值為1格式:INSTR(s1,s2,n1,n2)LPAD:前往s1被s2從左面填充到n1長(zhǎng)度。格式：LPAD(s1,n1,s2)RPAD:前往s1被s2從右面填充到n1長(zhǎng)度。格式：RPAD(s1,n1,s2)TRIM:去除字符串頭部或尾部頭尾的字符格式:TRIM(leading | trailing | both trim_character From trim_source)REPLACE:把s1中的s2用s3交換。格式:REPLACE(s1,s2,s3)154.2.2字符

6、處置函數(shù)字符處置函數(shù)CONCAT(Good, String)SUBSTR(String,1,3)LENGTH(String)INSTR(String, r)GoodStringStr63函數(shù)函數(shù)結(jié)果結(jié)果164.2.2字符處置函數(shù)字符處置函數(shù)LPAD(sal,10,*)RPAD(sal,10,*)TRIM(S FROM SSMITH)REPLACEabc,b,d)*50005000*MITHadc函數(shù)函數(shù)結(jié)果結(jié)果174.2.2字符處置函數(shù)字符處置函數(shù)查找公司員工編號(hào)，用戶名查找公司員工編號(hào)，用戶名first_name與與last_name銜接成銜接成一個(gè)字符串，職位編號(hào)及一個(gè)字符串，職位編號(hào)及l(fā)

7、ast_name的長(zhǎng)度，要求職位從第的長(zhǎng)度，要求職位從第四位起匹配四位起匹配ACCOUNT，同時(shí)，同時(shí)last_name中至少包含一個(gè)中至少包含一個(gè)e字母。字母。SELECT employee_id, CONCAT(first_name, last_name) NAME, job_id, LENGTH (last_name) length FROM employeesWHERE SUBSTR(job_id, 4) = ACCOUNTAND INSTR(last_name, e)0;18第四章 單行函數(shù)單行函數(shù):4.1函數(shù)的概念及分類4.2字符函數(shù)4.3數(shù)字函數(shù)4.4日期函數(shù)4.5轉(zhuǎn)換函數(shù)4.6

8、通用函數(shù)194.3數(shù)字函數(shù)數(shù)字函數(shù)ROUND(列名|表達(dá)式, n)：將列或表達(dá)式所表示的數(shù)值四舍五入到小數(shù)點(diǎn)后的第n位。TRUNC(列名|表達(dá)式,n)：將列或表達(dá)式所表示的數(shù)值截取到小數(shù)點(diǎn)后的第n位。MOD(m,n)：取m除以n后得到的余數(shù)。204.3數(shù)字函數(shù)數(shù)字函數(shù)SELECT ROUND(65.654,2),ROUND(65.654,0),ROUND(65.654,-1)FROM DUAL;SELECT TRUNC(65.654,2),TRUNC(65.654,0),TRUNC(65.654,-1)FROM DUAL;SELECT employee_id, last_name, salar

9、y, MOD(salary,900)FROM employeesWHERE department_id=90;21第四章 單行函數(shù)單行函數(shù):4.1函數(shù)的概念及分類4.2字符函數(shù)4.3數(shù)字函數(shù)4.4日期函數(shù)4.5轉(zhuǎn)換函數(shù)4.6通用函數(shù)224.4.1日期類型數(shù)學(xué)運(yùn)算日期類型數(shù)學(xué)運(yùn)算常用的日期運(yùn)算如下：日期類型列或表達(dá)式可以加減數(shù)字，功能是在該日期上加減對(duì)應(yīng)的天數(shù)。如：10-AUG-06+15結(jié)果是25-AUG-06。日期類型列或表達(dá)式之間可以進(jìn)展減操作，功能是計(jì)算兩個(gè)日期之間間隔了多少天。如：10-AUG-06-4-AUG-06結(jié)果四舍五入后是6天。假設(shè)需求加減相應(yīng)小時(shí)或分鐘，可以運(yùn)用n/24來(lái)實(shí)

10、現(xiàn)。234.4.2常用日期函數(shù)常用日期函數(shù) 1/6SYSDATE：前往系統(tǒng)日期MONTHS_BETWEEN：前往兩個(gè)日期間隔的月數(shù)ADD_MONTHS：在指定日期根底上加上相應(yīng)的月數(shù)NEXT_DAY：前往某一日期的下一個(gè)指定日期 LAST_DAY：前往指定日期當(dāng)月最后一天的日期ROUND(date,fmt)將date按照f(shuō)mt指定的格式進(jìn)展四舍五入，fmt為可選項(xiàng)，假設(shè)沒(méi)有指定fmt，那么默以為DD，將date四舍五入為最近的天。格式碼：世紀(jì)CC,年YY，月MM,日DD，小時(shí)HH24，分MI，秒SSTRUNC(date,fmt)將date按照f(shuō)mt指定的格式進(jìn)展截取，fmt為可選項(xiàng)，假設(shè)沒(méi)有指

11、定fmt，那么默以為DD，將date截取為最近的天。 EXTRACT：前往從日期類型中取出指定年、月、日 244.4.2常用日期函數(shù)常用日期函數(shù) 2/6MONTHS_BETWEEN 函數(shù)演示函數(shù)演示公司員工效力的月數(shù)。公司員工效力的月數(shù)。 ADD_MONTHS 函數(shù)演示函數(shù)演示99年公司員工轉(zhuǎn)正日期。年公司員工轉(zhuǎn)正日期。SELECT last_name, salary, MONTHS_BETWEEN(SYSDATE,hire_date) monthsFROM employeesORDER BY months;SELECT last_name, salary, hire_date, ADD_MO

12、NTHS(hire_date,3) new_dateFROM employeesWHERE hire_date01-1月-2019;254.4.2常用日期函數(shù)常用日期函數(shù) 3/6NEXT_DAY 函數(shù)演示函數(shù)演示下周一的日期。下周一的日期。LAST_DAY 函數(shù)演示函數(shù)演示06年年2月月2日所在月份最后一天。日所在月份最后一天。 SELECT NEXT_DAY(02-2月-06,星期一) NEXT_DAYFROM DUAL;SELECT LAST_DAY(02-2月-2019) LAST DAYFROM DUAL;264.4.2常用日期函數(shù)常用日期函數(shù) 4/6ROUND函數(shù)演示函數(shù)演示98年入

13、職員工入職日期按月四舍五年入職員工入職日期按月四舍五入。入。 SELECT employee_id, hire_date, ROUND(hire_date, MONTH)FROM employeesWHERE SUBSTR(hire_date,-2,2)=98;274.4.2常用日期函數(shù)常用日期函數(shù) 5/6TRUNC 函數(shù)演示函數(shù)演示98年入職員工入職日期按月截?cái)?。年入職員工入職日期按月截?cái)唷?SELECTemployee_id, hire_date, TRUNC(hire_date, MONTH)FROMemployeesWHERESUBSTR(hire_date,-2,2)=98;284.

14、4.2常用日期函數(shù)常用日期函數(shù) 6/6EXTRACT 函數(shù)語(yǔ)法部門編號(hào)是90的部門中一切員工入職月份。EXTRACT (YEAR MONTHDAY FROM 日期類型表達(dá)式)SELECT last_name, hire_date, EXTRACT (MONTH FROM HIRE_DATE) MONTHFROM employeesWHERE department_id = 90;29第四章 單行函數(shù)單行函數(shù):4.1函數(shù)的概念及分類4.2字符函數(shù)4.3數(shù)字函數(shù)4.4日期函數(shù)4.5轉(zhuǎn)換函數(shù)4.6通用函數(shù)304.5轉(zhuǎn)換函數(shù)轉(zhuǎn)換函數(shù)314.5.1數(shù)據(jù)類型隱性轉(zhuǎn)換數(shù)據(jù)類型隱性轉(zhuǎn)換假設(shè)不同的數(shù)據(jù)類型之間關(guān)

15、聯(lián)，假設(shè)不顯式轉(zhuǎn)換數(shù)據(jù)，那么它會(huì)根據(jù)以下規(guī)假設(shè)不同的數(shù)據(jù)類型之間關(guān)聯(lián)，假設(shè)不顯式轉(zhuǎn)換數(shù)據(jù)，那么它會(huì)根據(jù)以下規(guī)那么對(duì)數(shù)據(jù)進(jìn)展隱式轉(zhuǎn)換：那么對(duì)數(shù)據(jù)進(jìn)展隱式轉(zhuǎn)換： 對(duì)于對(duì)于INSERT和和UPDATE操作，操作，oracle會(huì)把插入值或者更新值隱式轉(zhuǎn)換為字會(huì)把插入值或者更新值隱式轉(zhuǎn)換為字段的數(shù)據(jù)類型段的數(shù)據(jù)類型對(duì)于對(duì)于SELECT語(yǔ)句，語(yǔ)句，oracle會(huì)把字段的數(shù)據(jù)類型隱式轉(zhuǎn)換為變量的數(shù)據(jù)類型會(huì)把字段的數(shù)據(jù)類型隱式轉(zhuǎn)換為變量的數(shù)據(jù)類型當(dāng)比較一個(gè)字符型和數(shù)值型的值時(shí)，當(dāng)比較一個(gè)字符型和數(shù)值型的值時(shí)，oracle會(huì)把字符型的值隱式轉(zhuǎn)換為數(shù)值會(huì)把字符型的值隱式轉(zhuǎn)換為數(shù)值型型當(dāng)比較字符型和日期型的數(shù)據(jù)時(shí)，

16、當(dāng)比較字符型和日期型的數(shù)據(jù)時(shí)，oracle會(huì)把字符型轉(zhuǎn)換為日期型會(huì)把字符型轉(zhuǎn)換為日期型用銜接操作符用銜接操作符(|)時(shí)，時(shí)，oracle會(huì)把非字符類型的數(shù)據(jù)轉(zhuǎn)換為字符類型會(huì)把非字符類型的數(shù)據(jù)轉(zhuǎn)換為字符類型假設(shè)字符類型的數(shù)據(jù)和非字符類型的數(shù)據(jù)假設(shè)字符類型的數(shù)據(jù)和非字符類型的數(shù)據(jù)(如如number、date、rowid等等)作算作算術(shù)運(yùn)算，那么術(shù)運(yùn)算，那么oracle會(huì)將字符類型的數(shù)據(jù)轉(zhuǎn)換為適宜的數(shù)據(jù)類型，這些數(shù)據(jù)會(huì)將字符類型的數(shù)據(jù)轉(zhuǎn)換為適宜的數(shù)據(jù)類型，這些數(shù)據(jù)類型能夠是類型能夠是number、date、rowid等等324.5.2數(shù)據(jù)類型顯性轉(zhuǎn)換數(shù)據(jù)類型顯性轉(zhuǎn)換 1/2通常是在字符類型、日期類型

17、、數(shù)字類型之間進(jìn)展顯性轉(zhuǎn)換。主要有3個(gè)顯性函數(shù)： TO_CHARTO_NUMBERTO_DATE334.5.2數(shù)據(jù)類型顯性轉(zhuǎn)換數(shù)據(jù)類型顯性轉(zhuǎn)換 2/2344.5.3TO_CHAR函數(shù)函數(shù) 1/6TO_CHAR(date|number ,fmt)把日期類型/數(shù)字類型的表達(dá)式或列轉(zhuǎn)換為字符類型。fmt指的是需求顯示的格式：需求寫在單引號(hào)中，并且是大小寫敏感可包含任何有效的日期格式354.5.3TO_CHAR函數(shù)函數(shù) 2/6常用日期格式Y(jié)YYY：4位數(shù)字表示年份；YY：2位數(shù)字表示年份，但是無(wú)世紀(jì)轉(zhuǎn)換(與RR區(qū)別在后面章節(jié)引見)；RR：2位數(shù)字表示年份，有世紀(jì)轉(zhuǎn)換與YY區(qū)別在后面章節(jié)引見；YEAR：

18、年份的英文拼寫；MM：兩位數(shù)字表示月份；MONTH：月份英文拼寫；364.5.3TO_CHAR函數(shù)函數(shù) 3/6常用日期格式續(xù)DY：星期的英文前三位字母；DAY：星期的英文拼寫；D：數(shù)字表示一星期的第幾天，星期天是一周的第一天。DD：數(shù)字表示一個(gè)月中的第幾天；DDD：數(shù)字表示一年中的第幾天。374.5.3TO_CHAR函數(shù)函數(shù) 4/6常用時(shí)間格式AM 或PM：上下午表示；HH 或HH12或HH24：數(shù)字表示小時(shí)。HH12代表12小時(shí)計(jì)時(shí)，HH24代表24小時(shí)計(jì)時(shí)；MI：數(shù)字表示分鐘；SS：數(shù)字表示秒；384.5.3TO_CHAR函數(shù)函數(shù) 5/6一些特殊格式TH：顯示數(shù)字表示的英文序數(shù)詞，如：DD

19、TH顯示天數(shù)的序數(shù)詞。SP：顯示數(shù)字表示的拼寫。SPTH：顯示數(shù)字表示的序數(shù)詞的拼寫。 “字符串:如在格式中顯示字符串，需求兩端加雙引號(hào)。TO_CHAR(SYSDATE,DDSPTH)TO_CHAR(SYSDATE,DD “of MONTH )394.5.3TO_CHAR函數(shù)函數(shù) 6/6日期到字符型轉(zhuǎn)換TO_CHAR 函數(shù)進(jìn)展日期到字符型復(fù)雜格式轉(zhuǎn)換演示。ALTER SESSION SET NLS_LANGUAGE = AMERICAN;SELECT employee_id, last_name, TO_CHAR(hire_date,Day ,the Ddspth of YYYY HH24:M

20、I:SS) hire_dateFROM employeesWHERE department_id=90;404.5.4TO_NUMBER函數(shù)和函數(shù)和TO_DATE函數(shù)函數(shù) 1/3TO_NUMBER(char,fmt) 把字符類型列或表達(dá)式轉(zhuǎn)換為數(shù)字類型。運(yùn)用格式和TO_CHAR中轉(zhuǎn)換成字符類型中的格式一樣TO_DATE(char,fmt) 把字符類型列或表達(dá)式轉(zhuǎn)換為日期類型。格式和TO_CHAR中轉(zhuǎn)換成字符類型中的格式一樣。414.5.4TO_NUMBER函數(shù)和函數(shù)和TO_DATE函數(shù)函數(shù) 2/3詳細(xì)格式如下：9：一位數(shù)字；0：一位數(shù)字或前導(dǎo)零；$：顯示為美圓符號(hào)；L：顯示按照區(qū)域設(shè)置的本地貨

21、幣符號(hào)；.：小數(shù)點(diǎn);,：千位分割符；424.5.4TO_NUMBER函數(shù)和函數(shù)和TO_DATE函數(shù)函數(shù) 3/3TO_CHAR 函數(shù)進(jìn)展數(shù)字到字符型格式轉(zhuǎn)換函數(shù)進(jìn)展數(shù)字到字符型格式轉(zhuǎn)換留意：進(jìn)展數(shù)字類型到字符型轉(zhuǎn)換，格式中的寬度一定要超越留意：進(jìn)展數(shù)字類型到字符型轉(zhuǎn)換，格式中的寬度一定要超越實(shí)踐列寬度，否那么會(huì)顯示為實(shí)踐列寬度，否那么會(huì)顯示為#。 SELECT last_name, TO_CHAR(salary, $99,999.00) salaryFROM employeesWHERE last_name = King;SELECT last_name, TO_CHAR(salary, $9,

22、999.00) salaryFROM employeesWHERE last_name = King;434.5.5RR功能功能當(dāng)前年份當(dāng)前年份2019201920192019指定的日期指定的日期27-OCT-9527-OCT-1727-OCT-1727-OCT-95RR 格式格式2019201920192019YY 格式格式2019191720192095假設(shè)當(dāng)前年份假設(shè)當(dāng)前年份的最后兩位數(shù)的最后兩位數(shù)是：是：04904950995099前往的日期是當(dāng)前世紀(jì)前往的日期是當(dāng)前世紀(jì)的日期的日期前往的日期是下一個(gè)前往的日期是下一個(gè)世紀(jì)的日期世紀(jì)的日期前往的日期是上一個(gè)世前往的日期是上一個(gè)世紀(jì)的日期

23、紀(jì)的日期前往的日期是當(dāng)前世紀(jì)前往的日期是當(dāng)前世紀(jì)的日期的日期假設(shè)指定兩位數(shù)年份是：假設(shè)指定兩位數(shù)年份是：44第四章 單行函數(shù)單行函數(shù):4.1函數(shù)的概念及分類4.2字符函數(shù)4.3數(shù)字函數(shù)4.4日期函數(shù)4.5轉(zhuǎn)換函數(shù)4.6通用函數(shù)454.6通用函數(shù)通用函數(shù)與空值NULL相關(guān)的一些函數(shù)，完成對(duì)空值NULL的一些操作。主要包括以下函數(shù)：NVLNVL2NULLIFCOALESCE條件處置函數(shù)：CASE表達(dá)式DECODE464.6.1NVL函數(shù)函數(shù)NVL (表達(dá)式1, 表達(dá)式2)函數(shù)功能是空值轉(zhuǎn)換，把空值轉(zhuǎn)換為其他值，處理空值問(wèn)題。表達(dá)式1是需求轉(zhuǎn)換的列或表達(dá)式，表達(dá)式2是假設(shè)第一個(gè)參數(shù)為空時(shí)，需求轉(zhuǎn)換的

24、值。NVL(comm,0)NVL(hire_date,01-JAN-06)NVL(job_id,No Job Yet)留意：數(shù)據(jù)格式可以是日期、字符、數(shù)字，但數(shù)據(jù)類型必需匹配。 474.6.2NVL2函數(shù)函數(shù)NVL2(表達(dá)式1, 表達(dá)式2, 表達(dá)式3)函數(shù)是對(duì)第一個(gè)參數(shù)進(jìn)展檢查。假設(shè)第一個(gè)參數(shù)不為空，那么輸出第二個(gè)參數(shù)；假設(shè)第一個(gè)參數(shù)為空，那么輸出第三個(gè)參數(shù)。表達(dá)式1可以為任何數(shù)據(jù)類型。SELECT last_name, salary,NVL2(commission_pct, salary + commission_pct,salary) incomeFROM employeesWHERE l

25、ast_name LIKE _a%;484.6.3NULLIF函數(shù)函數(shù)NULLIF (表達(dá)式1, 表達(dá)式2)函數(shù)主要是完成兩個(gè)參數(shù)的比較。當(dāng)兩個(gè)參數(shù)不相等時(shí)，前往值是第一個(gè)參數(shù)值；當(dāng)兩個(gè)參數(shù)相等時(shí)，前往值是空值。SELECT last_name, LENGTH(last_name) LEN_last_NAME, , LENGTH() LEN_EMAIL, NULLIF(LENGTH(last_name), LENGTH() resultFROM employeesWHERE last_name LIKE D%;494.6.4COALESCE函數(shù)函數(shù)COALESCE (表達(dá)式1, 表達(dá)式2, .

26、 表達(dá)式n)函數(shù)是對(duì)NVL函數(shù)的擴(kuò)展。COALESCE函數(shù)的功能是前往第一個(gè)不為空的參數(shù)，參數(shù)個(gè)數(shù)不受限制。SELECT last_name, COALESCE(commission_pct, salary*1.1, 100) comm , department_idFROM employeesWHERE department_id in (50,80)ORDER BY COMM;504.6.5CASE和和DECODE函數(shù)函數(shù) 1/3CASE語(yǔ)法CASE expr WHEN comparison_expr1 THEN return_expr1 WHEN comparison_expr2 THE

27、N return_expr2 WHEN comparison_exprn THEN return_exprn ELSE else_exprEND514.6.5CASE和和DECODE函數(shù)函數(shù) 2/3SELECT last_name, commission_pct, (CASE commission_pct WHEN 0.1 THEN 低 WHEN 0.2 THEN 中 WHEN 0.3 THEN 高 ELSE 無(wú) END) Commission FROM employeesWHERE commission_pct IS NOT NULLORDER BY last_name;CASE例如例如524.6.5CASE和和DECODE函數(shù)函數(shù) 3/3DECODE(字段|表達(dá)式, 條件1