【文章內(nèi)容簡介】 UMBER 轉(zhuǎn)換函數(shù) (續(xù)） 顯式數(shù)據(jù)類型轉(zhuǎn)換 SQL 提供三種函數(shù)來從一種數(shù)據(jù)類型轉(zhuǎn)換值到另一種： ? TO_CHAR(number|date,[ fmt], [nlsparams]) 轉(zhuǎn)換一個數(shù)字或日期值為一個 VARCHAR2字符串，帶格式 化樣式 fmt。 數(shù)字轉(zhuǎn)換： nlsparams 參數(shù)指定下面的字符，它由數(shù)字格式 化元素返回： ? 小數(shù)字符 ? 前導(dǎo) 0 09999 ? 本地貨幣符號 L9999 ? 國際貨幣符號 $9999 ? 如果忽略 nlsparams或其它參數(shù)，該函數(shù)在會話中使用默認參數(shù)值。 TO_CHAR(number|date,[ fmt], [nlsparams]) 指定返回的月和日名字及其縮寫的語言。如果忽略該參數(shù)， 該函數(shù)在會話中使用默認日期語言 。 TO_NUMBER(char,[fmt], [nlsparams]) 用由可選格式化樣式 fmt指定的格式轉(zhuǎn)換包含數(shù)字的字符串為 一個數(shù)字。 Nlsparams參數(shù)在該函數(shù)中的目的與 TO_CHAR 函數(shù)用于數(shù)字轉(zhuǎn)換的目的相同 。 TO_DATE(char,[fmt],[nlsparams]) 按照 fmt指定的格式轉(zhuǎn)換表示日期的字符串為日期值。如果忽 略 fmt，格式是 DDMONYY。 Nlsparams參數(shù)的目的與 TO_CHAR函數(shù)用于日期轉(zhuǎn)換時的目的相同。 對日期使用 TO_CHAR函數(shù) TO_CHAR(date, 39。format_model39。) 格式模板 ?必須加單引號，并且區(qū)分大小寫 ?能夠包含任一有效的日期格式元素 ?有一個 fm元素用來刪除填補的空，或者前導(dǎo)零 ?用一個逗號與日期值分開 SELECT employee_id, TO_CHAR(hire_date, 39。MM/YY39。) Month_Hired FROM employees WHERE last_name = 39。Higgins39。 日期格式模板的元素 YYYY 數(shù)字全寫年 YEAR 年的拼寫 MM 月的兩數(shù)字值 MONTH 月的全名 DY 周中天的三字母縮寫 DAY 周中天的全名 MON 月的三字母縮寫 DD 月的數(shù)字天 使用 TO_NUMBER和 TO_DATE函數(shù) ?轉(zhuǎn)換字符串到數(shù)字，用 TO_NUMBER函數(shù)格式化： TO_NUMBER(char[, 39。format_model39。]) ?轉(zhuǎn)換字符串到日期，用 TO_DATE函數(shù)格式化： TO_DATE(char[, 39。format_model39。]) Select to_number(‘ 12345’ ) from dual。 Select to_date(‘ 20230810’ ,’ yyyymmdd’ fromdual。 通用函數(shù) 這些函數(shù)可用于任意數(shù)據(jù)類型，并且適用于空值 ?NVL (expr1, expr2) ?NVL2 (expr1, expr2, expr3) ?NULLIF (expr1, expr2) ?COALESCE (expr1, expr2, ..., exprn) ? NVL 轉(zhuǎn)換空值為一個實際值 ? NVL2 如果 expr1非空， NVL2返回 expr2；如果 expr1為空 ， NVL2返回 expr3。參數(shù) expr1可以是任意數(shù)據(jù)類型 ? NULLIF 比較兩個表達式，如果相等返回空；如果不相等 ，返回第一個表達式 ? COALESCE 返回表達式列表中的第一個非空表達式 NVL函數(shù) 轉(zhuǎn)換一個空值到一個實際的值 ?可用的數(shù)據(jù)類型可以是日期、字符和數(shù)字 ?數(shù)據(jù)類型必須匹配： –NVL(mission_pct,0) –NVL(hire_date,39。01JAN9739。) –NVL(job_id,39。No Job Yet39。) 語法 : NVL (expr1, expr2) 在語法中： expr1 是包含空值的源值或者表達式 expr2 是用于轉(zhuǎn)換空值的目的值 Select nvl(‘ 1’ ,’ not null’ from dual。 注 :如果 expr1為空則返回 expr2r的值 使用 NVL2函數(shù) NVL2 函數(shù)檢查第一個表達式，如果第一個表達式不為空， 那么 NVL2 函數(shù)返回第二個表達式；如果第一個表達式為 空，那么第三個表達式被返回。 ? expr2 expr1 非空時的返回值 ? 語法 NVL2(expr1, expr2, expr3) 在語法中： ? expr1 是可能包含空的源值或表達式 ? expr3 expr1 為空時的返回值 Select NVL2(‘ 1’ ,’ not null’ ,’ null’ ) from dual。 Select NVL2(‘ 1’ ,’ not null’ ,’ null’ ) from dual。 使用 NULLIF函數(shù) NULLIF 函數(shù)比較兩個表達式，如果相等，函數(shù)返回空，如果不相等， 函數(shù)返回第一個表達式。第一個表達式不能為 NULL。 ? 語法 NULLIF (expr1, expr2) 在語法中： ? expr1 是對于 expr2 的被比較原值 ? expr2 是對于 expr1 的被比較原值。 (如果它不等于 expr1， expr1 被返回 )。 Select nullif(‘ abc’ ,’ abcd’ ) from dual。 使用 COALESCE函數(shù) ? COALESCE函數(shù)超過 NVL函數(shù)的優(yōu)點是 COALESCE函數(shù) 能夠接受多個交替的值。 ?如果第一個表達式非空，它返回該表達式；否則，它做一個 保留表達式的結(jié)合 。 COALESCE 函數(shù)返回列表中的第一個非空表達式。 ? 語法 COALESCE (expr1, expr2, ... exprn) ? 在語法中： expr1 如果它非空，返回該表達式 expr2 如果第一個表達式為空并且該表達式非空，返回該表達式 exprn 如果前面的表達式都為空，返回該表達式 Select coalesce(‘ 39。,‘ 39。,39。bca39。) from dual。 條件表達式 在 SQL 語句中提供 IFTHENELSE 邏輯的使用。 兩種用法： –CASE表達式 –DECODE函數(shù) CASE表達式 CASE expr WHEN parison_expr1 THEN return_expr1 WHEN parison_expr2 THEN return_expr2 WHEN parison_exprn THEN return_exprn ELSE else_expr] END DECODE函數(shù) DECODE(col|expression, search1, result1 [, search2, result2,...,][, default]) DECODE 函數(shù)在比較表達式 (expression) 和每個查找 (search) 值后解碼表達式，如果表達式與查找相同，返 回結(jié)果。 如果省略默認值，當沒有查找值與表達式相匹配時返回 一個空值。 多表顯示數(shù)據(jù) 組函數(shù)合計數(shù)據(jù) 目標 完成本課后 , 您應(yīng)當能夠執(zhí)行下列操作： ?寫 SELECT 語句使用等值和非等值連接 從多個表中訪問數(shù)據(jù) ?使用外連接查看不滿足連接條件的數(shù)據(jù) ?識別可用的組函數(shù) ?描述組函數(shù)的使用 ?用 GROUP BY 子句分組數(shù)據(jù) ?用 HAVING 子句包含或排除分組的行 笛卡爾乘積 ?笛卡爾乘積的形成，當： –一個連接條件被遺漏時 –一個連接條件不正確時 –在第一個表中的所有行被連接到第二個表的所有行時 ?為了避免笛卡爾乘積的形成，在 WHERE 子句中應(yīng)當總是包 含正確的連接條件 。 用 Oracle 語法連接表 使用一個連接從多個表中查詢數(shù)據(jù) SELECT , FROM table1, table2 WHERE =。 ?在 WHERE 子句中寫連接條件 ?當多個表中有相同的列名時，將表名作為列名的前綴定義連接 ?當數(shù)據(jù)從多表中查詢時，要使用連接 (join) 條件。一個表中 的行按照存在于相應(yīng)列中的公值被連接到另一個表中的行， 即，通常所說的主鍵和外鍵列 。 什么是等值連接 ? EMPLOYEES DEPARTMENTS 用等值連接返回記錄 SELECT , , , , FROM employees, departments WHERE = 。 使用表別名 ?使用表別名簡化查詢 ?使用表別名改善性能 SELECT , , , , FROM employees e , departments d WHERE = 。 ? 原則 – 表別名最多可以有 30 個字符，但短一些更好。 – 如果在 FROM 子句中表別名被用于指定的表，那么在整個 SELECT 語句中都要使用表別名。 – 表別名應(yīng)該是有意義的。 – 表別名只對當前的 SELECT 語句有效。 多于兩個表的連接 EMPLOYEES DEPARTMENTS LOCATIONS 非等值連接 EMPLOYEES JOB_GRADES 用非等值連接返回記錄 SELECT , , FROM employees e, job_grades j WHERE BETWEEN AND 。 外連接 DEPARTMENTS EMPLOYEES 外連接語法 ?你可以用一個外連接查看那些不滿足連接條件的行 ?外連接運算符是加號 (+) SELECT , FROM table1, table2 WHERE (+)=。 SELECT , FROM table1, table2 WHERE = (+)。 ?用外連接返回不直接匹配的記錄 ?如果在連接條件中使用外連接操作，缺少的行就可以被返 回。操作符是一個在圓括號中的加號 (+)，它被放置在連接 的缺少信息的一側(cè)。為了使來自不完善表的一行或多行能夠 被連接，該操作符有產(chǎn)生一個或多個空行的作用。 使用外連接 SELECT , , FROM employees e, departments d WHERE (+) = 。 什么是組函數(shù) ? 組函數(shù)操作行集，給出每組的結(jié)果 EMPLOYEES 在 EMPLOYEES 表中的最高薪水 組函數(shù)的類型 ?AVG 平均值 ?COUNT 計數(shù) ?MAX 最大值 ?MIN 最小值 ?STDDEV 標準差 ?SUM 合計 ?VARIANCE 方差 組函數(shù) (續(xù) ) 每個函數(shù)接收一個參數(shù)，下面的表確定你可以在語法中使用的選項： 函 數(shù) 說 明 AVG([DISTINCT|ALL]n) n 的平均值，忽略空值 COUNT({*|[DISTINCT|ALL]expr}) 用 * 計數(shù)所有行，包括重復(fù)和帶空值 的行。 expr 求除了空計算 MAX([DISTINCT|ALL]expr) expr的最大值，忽略空值 MIN([DISTINCT|ALL]expr) expr的最小值，忽略