Oracle數(shù)據(jù)庫培訓(xùn)PPT課件

1、SQL 語言基礎(chǔ)主要內(nèi)容l基本的SELECT語句l約束和排序數(shù)據(jù)l單行函數(shù)l多表顯示數(shù)據(jù)、組函數(shù)合計數(shù)據(jù)l創(chuàng)建和管理表l子查詢&操縱數(shù)據(jù)l內(nèi)置約束l創(chuàng)建視圖l其他數(shù)據(jù)庫對象基本的select語句 目標完成本課后, 您應(yīng)當能夠：列出SQL SELECT語句的功能執(zhí)行基本的SELECT語句SQL SELECT語句的功能 列選擇：你能夠使用SELECT語句的列選擇功能選擇表中的列，這些列是你想要用查詢返回的。當你查詢時，你能夠選擇你查詢的表中指定的列。 行選擇：你能夠使用SELECT語句的行選擇功能選擇表中的行，這些行是你想要用查詢返回的。你能夠使用不同的標準限制你看見的行。 連接：你能夠使

2、用SELECT語句的連接功能來集合數(shù)據(jù)，這些數(shù)據(jù)被存儲在不同的表中，在它們之間可以創(chuàng)建連接。在后面的課程中你將學(xué)到更多關(guān)于連接的內(nèi)容。 基本SELECT語句 SELECT *| DISTINCT column|expressionalias,. FROM table; SELECT 確定哪些列FROM 確定哪張表 選擇所有列與指定列 SELECT * FROM departments; 用跟在SELECT關(guān)鍵字后面的星號 (*)，你能夠顯示表中數(shù)據(jù)的所有列。 SELECT department_id, location_id FROM departments; 你能夠用SELECT語句來顯示表

3、的指定列，指定列名之間用逗號分隔。寫SQL 語句SQL 語句對大小寫不敏感SQL 語句可以寫成一行或多行關(guān)鍵字不能簡寫或分開折行子句通常放在不同的行縮進用于增強可讀性 算術(shù)表達式用算術(shù)運算符創(chuàng)建數(shù)字和日期數(shù)據(jù)的表達式 操作 說明 + 加 - 減 * 乘 / 除 使用算術(shù)運算符 SELECT last_name, salary, salary + 300 FROM employees; 優(yōu)先級優(yōu)先級: 乘法和除法比加法和減法的優(yōu)先級高 相同優(yōu)先級的運算符從左到右計算 圓括號用于強制優(yōu)先計算，并且使語句更 清晰 SELECT last_name, salary, 12*salary+100 FRO

4、M employees;空值 null 是一個未分配的、未知的，或不適用的值 null不是0，也不是空格 包含空值的算術(shù)表達式計算結(jié)果為空 SELECT last_name, job_id, salary, commission_pct FROM employees;定義列別名列別名： 改變列標題的名字 可用于計算結(jié)果 緊跟在列名后面在列名和別名之間可以有選項AS 關(guān)鍵字 如果別名中包含有空格、或者特殊字符、或者大小寫敏感，要求用雙引號 SELECT last_name AS name, commission_pct comm FROM employees; 連字運算符連字運算符： 連接列或者

5、字符串到其它的列 用兩個豎線表示(|) 構(gòu)造一個字符表達式的合成列 SELECT first_name|last_name AS Employees FROM employees;文字字符串 文字字符串是包含在SELECT列表中的一個字符串，一個數(shù)字或者一個日期 日期和字符的文字字符串值必須用單引號括起來 每個文字字符串在每行輸出一次 SELECT last_name| is a |job_id AS Employee Details” FROM employees;約束和排序數(shù)據(jù) 目標完成本課后, 您應(yīng)當能夠執(zhí)行下列操作：用一個查詢限制返回的行用一個查詢分類返回的行 限制選擇的行 用WHER

6、E子句限制返回的行 SELECT * |DISTINCT column|expressionalias,. FROM table WHERE condition(s); WHERE子句跟著子句跟著FROM子句子句 WHERE 限制查詢滿足條件的行 condition 由列名、表達式、常數(shù)和比較操作組成 WHERE子句能夠比較列值、文字值、算術(shù)表達式或者函 數(shù)，WHERE子句由三個元素組成： 列名 ， 比較條件 ， 列名、常量或值列表 。使用WHERE子句 SELECT employee_id, last_name, job_id, department_id FROM employees WH

7、ERE department_id = 90 ;字符串和日期字符串和日期的值放在單引號中字符值區(qū)分大小寫，日期值是格式敏感的日期的默認格式是DD-MON-RR. SELECT last_name, job_id, department_id FROM employees WHERE last_name = Whalen; 比較條件 運算 含義 = 等于 大于 = 大于等于 小于 = 小于等于 不等于比較條件被用于一個表達式與一個值或與另一個表達式的比比較條件被用于一個表達式與一個值或與另一個表達式的比較。較。 . WHERE hire_date=01-JAN-95 . WHERE salary

8、=6000 . WHERE last_name=Smith 其它比較條件操作操作 含義含義 BETWEEN.AND. 在兩個值之間(包含) IN(set) 匹配一個任意值列表 LIKE 匹配一個字符模板 IS NULL 是一個空值 使用使用BETWEEN條件條件: SELECT last_name, salary FROM employees WHERE salary BETWEEN 2500 AND 3500;使用使用IN條件條件 SELECT employee_id, last_name, salary, manager_id FROM employees WHERE manager_id

9、 IN (100, 101, 201);其它比較條件使用使用LIKE條件條件使用LIKE條件執(zhí)行有效搜索串值的通配符搜索搜索條件既可以包含文字也可以包含數(shù)字： %表示任意順序的零個或多個字符 _表示一個字符 SELECT first_name FROM employees WHERE first_name LIKE S%; 使用使用NULL條件條件用IS NULL操作來測試空值 SELECT last_name, manager_id FROM employees WHERE manager_id IS NULL; NULL條件，包括IS NULL條件和IS NOT NULL條件。 邏輯條件

10、運算 含義 AND 如果兩個組成部分的條件都為真，返回TRUE OR 如果兩個組成部分中的任一個條件為真，返回TRUE NOT 如果跟隨的條件為假，返回TRUE 可以在WHERE子句中用AND和OR運算符使用多個條件。 使用使用AND操作操作：AND要求兩個條件同時為真 SELECT employee_id, last_name, job_id, salary FROM employees WHERE salary =10000 AND job_id LIKE %MAN%; 邏輯條件使用OR操作：OR操作要求兩者之一為真即可 SELECT employee_id, last_name, job

11、_id, salary FROM employees WHERE salary = 10000 OR job_id LIKE %MAN%; 使用NOT操作 SELECT last_name, job_id FROM employees WHERE job_id NOT IN (IT_PROG, ST_CLERK, SA_REP); 注：NOT運算符也可以用于另一個SQL運算符，例如，BETWEEN、LIKE、和NULL。優(yōu)先規(guī)則 求值順序 1 算術(shù)運算 2 連字操作 3 比較操作 4 ISNOTNULL, LIKE, NOTIN 5 NOT BETWEEN 6 NOT邏輯條件 7 AND邏輯條

12、件 8 OR邏輯條件 使用圓括號改變優(yōu)先規(guī)則 ORDER BY子句用ORDER BY子句排序行 ASC: 升序排序，默認 DESC: 降序排序ORDER BY子句在SELECT語句的最后 SELECT last_name, job_date FROM employees ORDER BY hire_date ;語法 : SELECT expr FROM table WHERE condition(s) ORDER BY column, expr ASC|DESC; 單行函數(shù) 目標完成本課后，您應(yīng)當能夠執(zhí)行下列操作：描述在SQL 中可用的函數(shù)的變量類型在SELECT語句中使用字符，數(shù)字和日期函數(shù)

13、描述轉(zhuǎn)換函數(shù)的使用 SQL 函數(shù) 函數(shù)是SQL的一個非常強有力的特性，函數(shù)能夠用于下面的目的： 執(zhí)行數(shù)據(jù)計算 修改單個數(shù)據(jù)項 操縱輸出進行行分組 格式化顯示的日期和數(shù)字 轉(zhuǎn)換列數(shù)據(jù)類型 SQL函數(shù)有輸入?yún)?shù)，并且總有一個返回值。 注：在本課中講述的大多數(shù)函數(shù)是針對SQL的Oracle版的。 SQL 函數(shù) (續(xù)) 有兩種截然不同的函數(shù)： 單行函數(shù) 多行函數(shù) 單行函數(shù) 這些函數(shù)僅對單個行進行運算，并且每行返回一個結(jié)果。有不同類型的單行函數(shù)，本課下面的函數(shù)類型： 字符 數(shù)字 日期 轉(zhuǎn)換 多行函數(shù) 這些函數(shù)能夠操縱成組的行，每個行組給出一個結(jié)果，這些函數(shù)也被稱為組函數(shù)。多行函數(shù)在后面的課程中介紹。單行

14、函數(shù)單行函數(shù)：操縱數(shù)據(jù)項接受多個參數(shù)并返回一個值作用于每一個返回行每行返回一個結(jié)果可以修改數(shù)據(jù)類型可以嵌套接受多個參數(shù)，參數(shù)可以是一個列或者一個表達式單行函數(shù)（續(xù)） 單行函數(shù)的特性包括： 作用于查詢中返回的每一行 每行返回一個結(jié)果 可能返回一個與參數(shù)不同類型的數(shù)據(jù)值 可能需要一個或多個參數(shù) 能夠用在SELECT、WHERE和ORDER BY子句中，可以嵌套 。function_name(arg1, arg2,.) function_name 是函數(shù)的名字。 arg1, arg2 是由函數(shù)使用的任意參數(shù)，可以由一個列名或者一個表達式提供。 單行函數(shù)（續(xù)） 本課包括下面的單行函數(shù)： 字符函數(shù)：接受

15、字符輸入，可以返回字符或者數(shù)字值 數(shù)字函數(shù)：接受數(shù)字輸入，返回數(shù)字值 日期函數(shù)：對DATE數(shù)據(jù)類型的值進行運算 (除了MONTHS_BETWEEN函數(shù)返回一個數(shù)字，所有日期函數(shù)都返回一個DATE數(shù)據(jù)類型的值。) 轉(zhuǎn)換函數(shù)：從一個數(shù)據(jù)類型到另一個數(shù)據(jù)類型轉(zhuǎn)換一個值 通用函數(shù)： NVL 、 NVL2、 NULLIF、 COALSECE、 CASE 、 DECODE字符函數(shù) 單行字符函數(shù)接受字符數(shù)據(jù)作為輸入，既可以返回字符值也單行字符函數(shù)接受字符數(shù)據(jù)作為輸入，既可以返回字符值也可以返回數(shù)字值。字符函數(shù)可以被分為下面兩種：可以返回數(shù)字值。字符函數(shù)可以被分為下面兩種： 大小寫處理函數(shù) 字符處理函數(shù)大小寫

16、處理函數(shù)如下：大小寫處理函數(shù)如下：LOWER(column|expression) 轉(zhuǎn)換字符值為小寫 UPPER(column|expression) 轉(zhuǎn)換字符值為大寫 INITCAP(column|expression) 轉(zhuǎn)換每個單詞的首字母值為大寫，所有 其它值為小寫 字符處理函數(shù)如下：字符處理函數(shù)如下：CONCAT(column1|expression1 ,column2|expression2) 連接第一個字符值到第二個字符值；等價于連接運算符 (|) SUBSTR(column|expression,m ,n) 從字符值中返回指定的字符，開始位置在 m，n字符長度 (如果 m 是負數(shù)

17、，計數(shù)從字符值末尾開始；如果 n 被忽略，返回到串結(jié)束的所有字符)。 LENGTH(column|expression) 返回表達式中的字符數(shù)INSTR(column|expression, string, ,m, n ) 返回一個命名串的數(shù)字位置。隨意地，你可以提供一個位置m作為查找的開始，在字符串中第n次發(fā)現(xiàn)的位置。m和n的默認值是1，意味著在起始開始查找，并且報告第一個發(fā)現(xiàn)的位置。 LPAD(column|expression, n, string) RPAD(column|expression, n, string) 填充字符值左、右調(diào)節(jié)到n字符位置的總寬度 TRIM(leading|

18、trailing|both , trim_character FROM trim_source) 使你能夠從一個字符串修整頭或尾字符(或兩者)。如果trim_character或trim_source是字符文字，你必須放在單引號中。 REPLACE(text, search_string, replacement_string) 從字符串查找一個文本表達式，如果找到，用指定的值串代替它 字符函數(shù)（續(xù)）大小寫處理函數(shù)這些函數(shù)轉(zhuǎn)換字符串的大小寫這些函數(shù)轉(zhuǎn)換字符串的大小寫 函 數(shù) 結(jié) 果LOWER(SQL Course) sql courseUPPER(SQL Course) SQL COURSEI

19、NITCAP(SQL Course) Sql Course LOWER：轉(zhuǎn)換大小寫混合的字符串為小寫字符串 UPPER：轉(zhuǎn)換大小寫混合的字符串為大寫字符串 INITCAP：將每個單詞的首字母轉(zhuǎn)換為大寫，其他字母為小寫 SELECT The job id for |UPPER(last_name)| is |LOWER(job_id) AS EMPLOYEE DETAILS FROM employees; 字符處理函數(shù) 函 數(shù) 結(jié) 果CONCAT(Hello, World) HelloWorldSUBSTR(HelloWorld,1,5) HelloLENGTH(HelloWorld) 10IN

20、STR(HelloWorld, W) 6LPAD(salary, 10,*) *24000RPAD(salary, 10, *) 24000 *TRIM(H FROM HelloWorld) elloWorld數(shù)字函數(shù)ROUND:四舍五入指定小數(shù)的值 ROUND(45.926, 2) 45.93 TRUNC(45.926, 2) 45.92 MOD(1600, 300) 100 ROUND(column|expression, n) 四舍五入列、表達式或值為n位小數(shù)位，或者，如果n被忽略，無小數(shù)位。(如果n是負值，小數(shù)點左邊的數(shù)被四舍五入) TRUNC(column|expression,n)

21、 截斷列、表達式或值到n位小數(shù)，或者，如果n被忽略，那么n默認為0 MOD(m,n) 返回m除以n的余數(shù) 使用ROUND函數(shù) SELECT ROUND(45.923,2), ROUND(45.923,0),ROUND(45.923,-1) FROM DUAL;使用TRUNC函數(shù) SELECT TRUNC(45.923,2), TRUNC(45.923),TRUNC(45.923,-2) FROM DUAL;使用MOD函數(shù) SELECT last_name, salary, MOD(salary, 5000) FROM employees WHERE job_id = SA_REP;注：MOD函

22、數(shù)經(jīng)常用于確定一個值是奇數(shù)還是偶數(shù) 日期的使用 Oracle 數(shù)據(jù)庫用內(nèi)部數(shù)字格式存儲日期：世紀，年，月，日，小時，分鐘和秒 默認日期顯示格式是DD-MON-RR. SELECT last_name, hire_date FROM employees WHERE last_name like G%; SYSDATE函數(shù)返回： Date Time SYSDATE是一個日期函數(shù)，它返回當前數(shù)據(jù)庫服務(wù)器的日期和時間。 SELECT SYSDATE FROM DUAL; 用日期計算從日期加或者減一個數(shù)，結(jié)果是一個日期值兩個日期相減，得到兩個日期之間的天數(shù)用小時數(shù)除以24，可以加小時到日期上 既然數(shù)據(jù)庫

23、以數(shù)字方式存儲日期，你就可以用算術(shù)運算符進行計算，例如，加或減。你可以加或減數(shù)字常數(shù)以及日期。 你可以進行下面的運算： 運算 結(jié)果 說明 date + number 日期 加一個天數(shù)到一個日期上 date - number 日期 從一個日期上減一個天數(shù) date - date 天數(shù) 用一個日期減另一個日期 date + number/24 日期 加一個小時數(shù)到一個日期上 日期函數(shù) 函 數(shù) 說 明 MONTHS_BETWEEN 兩個日期之間的月數(shù)ADD_MONTHS 加日歷月到日期NEXT_DAY 下個星期幾是幾號LAST_DAY 指定月的最后一天ROUND 四舍五入日期TRUNC 截斷日期日期

24、函數(shù)（續(xù)） MONTHS_BETWEEN(date1, date2)：計算date1和date2之間的月數(shù)，其結(jié)果可以是正的也可以是負的。如果date1大于date2，結(jié)果是正的，反之，結(jié)果是負的。結(jié)果的小數(shù)部分表示月的一部分。 ADD_MONTHS(date, n)：添加n個日歷月到date。n的值必須是整數(shù)，但可以是負的。 NEXT_DAY(date, char)：計算在date之后的下一個周(char)指定天的日期。char的值可能是一個表示一天的數(shù)或者是一個字符串。 LAST_DAY(date)：計算包含date的月的最后一天的日期 ROUND(date,fmt)：返回用格式化模式fm

25、t四舍五入到指定單位的 date ，如果格式模式 fmt 被忽略，date被四舍五入到最近的天。 TRUNC(date, fmt)：返回用格式化模式fmt截斷到指定單位的帶天的時間部分的date，如果格式模式fmt被忽略，date被截斷到最近的天。 使用日期函數(shù)MONTHS_BETWEEN (01-SEP-95,11-JAN-94) 19.6774194ADD_MONTHS (11-JAN-94,6)11-JUL-94NEXT_DAY (01-SEP-95,2) 下個星期五是幾號 08-SEP-95LAST_DAY(01-FEB-95) 28-FEB-95使用日期函數(shù)（續(xù)）假定假定SYSDAT

26、E = 25-JUL-95:ROUND(SYSDATE,MONTH) 01-AUG-95ROUND(SYSDATE ,YEAR) 01-JAN-96TRUNC(SYSDATE ,MONTH) 01-JUL-95 TRUNC(SYSDATE ,YEAR) 01-JAN-95TRUNC(TO_DATE(25-JUL-95) ,YEAR) 01-JAN-95 轉(zhuǎn)換函數(shù)數(shù)據(jù)類型轉(zhuǎn)換數(shù)據(jù)類型轉(zhuǎn)換隱式數(shù)據(jù)類型轉(zhuǎn)換顯式數(shù)據(jù)類型轉(zhuǎn)換對于直接賦值，Oracle 服務(wù)器能夠自動地進行下面的轉(zhuǎn)換： 從 到VARCHAR2 or CHAR NUMBERVARCHAR2 or CHAR DATENUMBER VARCH

27、AR2 DATE VARCHAR2對于表達式賦值，Oracle 服務(wù)器能自動地進行下面的轉(zhuǎn)換：從從 VARCHAR2 or CHAR 到 DATE從從 VARCHAR2 or CHAR 到 NUMBER轉(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ù)字格式化元素返回： l小數(shù)字符 99999.99 前導(dǎo)0 09999 本地貨幣符號 L9999 國際貨幣符號 $9

28、999 如果忽略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，格式是 DD-MON-YY。Nl

29、sparams參數(shù)的目的與TO_CHAR函數(shù)用于日期轉(zhuǎn)換時的目的相同。對日期使用TO_CHAR函數(shù)TO_CHAR(date, format_model)格式模板必須加單引號，并且區(qū)分大小寫能夠包含任一有效的日期格式元素有一個fm元素用來刪除填補的空，或者前導(dǎo)零用一個逗號與日期值分開 SELECT employee_id, TO_CHAR(hire_date, MM/YY) Month_Hired FROM employees WHERE last_name = Higgins; 日期格式模板的元素YYYY 數(shù)字全寫年YEAR 年的拼寫MM 月的兩數(shù)字值MONTH 月的全名DY 周中天的三字母縮

30、寫DAY 周中天的全名MON 月的三字母縮寫DD 月的數(shù)字天 使用TO_NUMBER和TO_DATE函數(shù)轉(zhuǎn)換字符串到數(shù)字，用TO_NUMBER函數(shù)格式化：TO_NUMBER(char, format_model)轉(zhuǎn)換字符串到日期，用TO_DATE函數(shù)格式化：TO_DATE(char, format_model) Select to_number(12345) from dual; Select to_date(20000810,yyyy-mm-dd fromdual;通用函數(shù)這些函數(shù)可用于任意數(shù)據(jù)類型，并且適用于空值NVL (expr1, expr2)NVL2 (expr1, expr2, e

31、xpr3)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(commission_pct,0)NVL(hire_date,01-JAN-97)NV

32、L(job_id,No Job Yet) 語法: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

33、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

34、;使用COALESCE函數(shù) COALESCE函數(shù)超過NVL函數(shù)的優(yōu)點是COALESCE函數(shù) 能夠接受多個交替的值。如果第一個表達式非空，它返回該表達式；否則，它做一個 保留表達式的結(jié)合 。COALESCE 函數(shù)返回列表中的第一個非空表達式。函數(shù)返回列表中的第一個非空表達式。 語法 COALESCE (expr1, expr2, . exprn) 在語法中： expr1 如果它非空，返回該表達式 expr2 如果第一個表達式為空并且該表達式非空，返回該表達式 exprn 如果前面的表達式都為空，返回該表達式 Select coalesce( , ,bca) from dual;條件表達式在在SQ

35、L 語句中提供語句中提供IF-THEN-ELSE 邏輯的使用。邏輯的使用。兩種用法： CASE表達式 DECODE函數(shù) CASE表達式 CASE expr WHEN comparison_expr1 THEN return_expr1 WHEN comparison_expr2 THEN return_expr2 WHEN comparison_exprn THEN return_exprn ELSE else_expr ENDDECODE函數(shù)DECODE(col|expression, search1, result1 , search2, result2,., default)DECODE

36、 函數(shù)在比較表達式 (expression) 和每個查找 (search) 值后解碼表達式，如果表達式與查找相同，返回結(jié)果。 如果省略默認值，當沒有查找值與表達式相匹配時返回一個空值。多表顯示數(shù)據(jù)&組函數(shù)合計數(shù)據(jù) 目標完成本課后, 您應(yīng)當能夠執(zhí)行下列操作：寫SELECT 語句使用等值和非等值連接 從多個表中訪問數(shù)據(jù)使用外連接查看不滿足連接條件的數(shù)據(jù)識別可用的組函數(shù)描述組函數(shù)的使用用GROUP BY 子句分組數(shù)據(jù)用HAVING 子句包含或排除分組的行 笛卡爾乘積笛卡爾乘積的形成，當： 一個連接條件被遺漏時 一個連接條件不正確時 在第一個表中的所有行被連接到第二個表的所有行時為了避免笛卡爾

37、乘積的形成，在WHERE 子句中應(yīng)當總是包 含正確的連接條件 。用Oracle 語法連接表使用一個連接從多個表中查詢數(shù)據(jù) SELECT table1.column, table2.column FROM table1, table2 WHERE table1.column1 =table2.column2;在WHERE 子句中寫連接條件當多個表中有相同的列名時，將表名作為列名的前綴定義連接 當數(shù)據(jù)從多表中查詢時，要使用連接 (join) 條件。一個表中 的行按照存在于相應(yīng)列中的公值被連接到另一個表中的行， 即，通常所說的主鍵和外鍵列。 什么是等值連接?EMPLOYEES DEPARTMENTS

38、 用等值連接返回記錄SELECT employees.employee_id, employees.last_name, employees.department_id, departments.department_id, departments.location_id FROM employees, departments WHERE employees.department_id = departments.department_id;使用表別名使用表別名簡化查詢使用表別名改善性能 SELECT e.employee_id, e.last_name, e.department_id, d

39、.department_id, d.location_id FROM employees e , departments d WHERE e.department_id = d.department_id; 原則 表別名最多可以有 30 個字符，但短一些更好。 如果在 FROM 子句中表別名被用于指定的表，那么在整個 SELECT 語句中都要使用表別名。 表別名應(yīng)該是有意義的。 表別名只對當前的 SELECT 語句有效。多于兩個表的連接 EMPLOYEES DEPARTMENTS LOCATIONS非等值連接EMPLOYEES JOB_GRADES用非等值連接返回記錄 SELECT e.las

40、t_name, e.salary, j.grade_level FROM employees e, job_grades j WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal;外連接 DEPARTMENTS EMPLOYEES外連接語法你可以用一個外連接查看那些不滿足連接條件的行外連接運算符是加號(+) SELECT table1.column, table2.column FROM table1, table2 WHERE table1.column(+)=table2.column; SELECT table1.column, ta

41、ble2.column FROM table1, table2 WHERE table1.column = table2.column(+); 用外連接返回不直接匹配的記錄 如果在連接條件中使用外連接操作，缺少的行就可以被返 回。操作符是一個在圓括號中的加號 (+)，它被放置在連接 的缺少信息的一側(cè)。為了使來自不完善表的一行或多行能夠 被連接，該操作符有產(chǎn)生一個或多個空行的作用。 使用外連接SELECT e.last_name, e.department_id, d.department_nameFROM employees e, departments dWHERE e.department

42、_id (+) = d.department_id ;什么是組函數(shù)?組函數(shù)操作行集，給出每組的結(jié)果 EMPLOYEES在EMPLOYEES表中的最高薪水組函數(shù)的類型AVG 平均值COUNT 計數(shù)MAX 最大值MIN 最小值STDDEV 標準差SUM 合計VARIANCE 方差 組函數(shù) (續(xù)) 每個函數(shù)接收一個參數(shù)，下面的表確定你可以在語法中使用的選項： 函 數(shù) 說 明 AVG(DISTINCT|ALLn) n 的平均值，忽略空值 COUNT(*|DISTINCT|ALLexpr) 用 * 計數(shù)所有行，包括重復(fù)和帶空值 的行。expr 求除了空計算 MAX(DISTINCT|ALLexpr) e

43、xpr的最大值，忽略空值 MIN(DISTINCT|ALLexpr) expr的最小值，忽略空值 STDDEV(DISTINCT|ALLx) n 的標準差，忽略空值 SUM(DISTINCT|ALLn) 合計 n 的值，忽略空值 VARIANCE(DISTINCT|ALLx) n 的方差，忽略空值 組函數(shù)的語法 SELECTcolumn, group_function(column), .FROM tableWHERE conditionGROUP BYcolumnORDER BYcolumn;使用組函數(shù)的原則 DISTINCT 使得函數(shù)只考慮不重復(fù)的值；ALL 使得函數(shù)考慮每個值，包括重復(fù)值

44、。默認值是 ALL ，因此不需要指定。 用于函數(shù)的參數(shù)的數(shù)據(jù)類型可以是 CHAR、VARCHAR2、NUMBER 或 DATE。 所有組函數(shù)忽略空值。為了用一個值代替空值，用 NVL、NVL2 或 COALESCE 函數(shù)。 當使用 GROUP BY 子句時，Oracle 服務(wù)器隱式以升序排序結(jié)果集。為了覆蓋該默認順序，DESC 可以被用于 ORDER BY 子句。 使用AVG 、SUM、MIN、MAX 函數(shù)你可以使用AVG 和SUM 用于數(shù)字數(shù)據(jù) SELECT AVG(salary), MAX(salary), MIN(salary), SUM(salary)FROM employeesWHE

45、RE job_id LIKE %REP%;使用COUNT 函數(shù)COUNT(*) 返回一個表中的行數(shù) COUNT 函數(shù)有三中格式： COUNT(*) COUNT(expr) COUNT(DISTINCT expr) COUNT(*) 返回表中滿足 SELECT 語句標準的行數(shù)，包括重復(fù)行，包括有空值列的行。如果 WHERE 子句包括在 SELECT 語句中，COUNT(*) 返回滿足 WHERE 子句條件的行數(shù)。 COUNT(expr) 返回在列中的由 expr 指定的非空值的數(shù)。 COUNT(DISTINCT expr) 返回在列中的由 expr 指定的唯一的非空值的數(shù)。注：expr為列名組函

46、數(shù)和Null 值所有組函數(shù)忽略列中的空值。在幻燈片的例子中，平均值只基于表中的那些 COMMISSION_PCT 列的值有效的行的計算。平均值計算是用付給所有雇員的總傭金除以接受傭金的雇員數(shù) (4)。 SELECT AVG(commission_pct)FROM employees;在組函數(shù)中使用NVL 函數(shù)NVL 函數(shù)強制組函數(shù)包括空值。在幻燈片的例子中，平均值被基于所有表中的行來計算，不管 COMMISSION_PCT 列是否為空。平均值的計算是用付給所有雇員的總傭金除以公司的雇員總數(shù) (20)。 SELECT AVG(NVL(commission_pct, 0)FROM employee

47、s;創(chuàng)建數(shù)據(jù)組:GROUP BY 子句語法用GROUP BY 子句劃分表中的行到較小的組中SELECT column, group_function(column)FROM tableWHERE conditionGROUP BY group_by_expressionORDER BY column;在語法中， group_by_expression 指定那些用于將行分組的列，這些列的值作為行分組的依據(jù)。使用 WHERE 子句，你可以在劃分行成組以前過濾行。 在 GROUP BY 子句中必須包含列。 在 GROUP BY 子句中你不能用列別名。 默認情況下，行以包含在 GROUP BY 列表中

48、的字段的升序排序。你可以用 ORDER BY 子句覆蓋這個默認值。 如果在 SELECT 子句中包含了組函數(shù)，就不能選擇單獨的結(jié)果，除非單獨的列出現(xiàn)在 GROUP BY 子句中。如果你未能在 GROUP BY 子句中包含一個字段列表，你會收到一個錯誤信息。 約束分組結(jié)果: HAVING 子句用HAVING 子句約束分組：1.行被分組2.應(yīng)用組函數(shù)3.匹配HAVING 子句的組被顯示 SELECT column, group_functionFROM tableWHERE conditionGROUP BY group_by_expressionHAVING group_conditionORD

49、ER BY column;使用HAVING 子句SELECT department_id, MAX(salary)FROM employeesGROUP BY department_idHAVING MAX(salary)10000 ; 嵌套組函數(shù)顯示最大平均薪水SELECT MAX(AVG(salary)FROM employeesGROUP BY department_id; 創(chuàng)建和管理表 目標完成本課后, 您應(yīng)當能夠執(zhí)行下列操作：描述主要數(shù)據(jù)庫對象創(chuàng)建表描述列定義時可用的數(shù)據(jù)類型改變表的定義刪除、改名和截斷表 數(shù)據(jù)庫對象表 基本存儲單元, 由行和列組成視圖 邏輯地從一個或多個表中表示數(shù)據(jù)

50、子集序列 數(shù)字值發(fā)生器索引 改善一些查詢的性能同義詞 給對象可選擇的名字 命名規(guī)則表命名和列命名：必須以字母開始必須是130 個字符長度只能包含AZ, az, 09, _, $, 和#同一個用戶所擁有的對象之間不能重名不能用Oracle 服務(wù)器的保留字 注：名字是大小寫不敏感的，例如， EMPLOYEES 與 eMPloyees 或 eMpLOYEES 作為同一個名字來處理。 CREATE TABLE語句用戶必須有： CREATE TABLE權(quán)限 一個存儲區(qū)域CREATE TABLE schema.table(column datatypeDEFAULT expr, .);必須指定： 表名 列

51、名、列數(shù)據(jù)類型和列的大小 schema 與所有者的名字一樣 table 表的名字 DEFAULT expr 指定默認值 column 列的名字 datatype 列的數(shù)據(jù)類型和長度引用另一個用戶的表表屬于另一個用戶，不在該用戶的方案中在那些表名字的前面使用所有者的名字作為 前綴 如果一個表不屬于本用戶，那么，其所有者的名字 必須放在表名的前面SELECT * FROM user_b.employees; 創(chuàng)建表創(chuàng)建表CREATE TABLE dept( deptno NUMBER(2), dname VARCHAR2(14), loc VARCHAR2(13);確認表的創(chuàng)建DESCRIBE d

52、ept Oracle 數(shù)據(jù)庫中的表用戶表： 由用戶創(chuàng)建和維護的表的集合 包含用戶信息數(shù)據(jù)字典： 由Oracle 服務(wù)器創(chuàng)建和維護的表的集合 包含數(shù)據(jù)庫信息 有四種數(shù)據(jù)字典視圖，每一種有一個特定的前綴來反映其不同的目的。 USER_ 這些視圖包含關(guān)于用戶所擁有的對象的信息。 ALL_ 這些視圖包含所有用戶可訪問的表 (對象表和相關(guān)的表) 的信息。DBA_ 這些視圖是受限制的視圖，它們只能被分配有 DBA 角色的用戶所 訪問。 V$ 這些視圖是動態(tài)執(zhí)行的視圖，包含數(shù)據(jù)庫服務(wù)器的性能、存儲器 和鎖的信息。 查詢數(shù)據(jù)字典查看本用戶所擁有的表的名稱 SELECT table_name FROM user

53、_tables ;查看本用戶所擁有的不同的對象類型SELECT DISTINCT object_type FROM user_objects ;查看本用戶所擁有的表、視圖、同義詞和序列SELECT * FROM user_catalog ;數(shù)據(jù)類型 數(shù)據(jù)類型 說 明VARCHAR2(size) 可變長度的字符數(shù)據(jù)CHAR(size) 固定長度的字符數(shù)據(jù)NUMBER(p,s) 可變長度的數(shù)字數(shù)據(jù)DATE 日期和時間值LONG 最大2G的可變長度字符數(shù)據(jù)CLOB 最大4G的字符數(shù)據(jù)RAW and LONG RAW 原始二進制數(shù)據(jù)BLOB 最大4G的二進制數(shù)據(jù)BFILE 最大4G的，存儲在外部文件中

54、的二 進制數(shù)據(jù)ROWID 一個64進制的數(shù)制系統(tǒng)，表示表中 一行的唯一地址 用子查詢創(chuàng)建表該方法既可以創(chuàng)建表還可以將從子查詢返回的行插入新創(chuàng)建的表中。 CREATE TABLE dept80 AS SELECT employee_id, last_name, salary*12 ANNSAL, hire_date FROM employees WHERE department_id = 80;原則 被創(chuàng)建的表要帶指定的列名，并且由SELECT語句返回的行被插入到新表中。 字段的定義只能包括列名和默認值。 如果給出了指定的列，列的數(shù)目必須等于子查詢的SELECT列表的列數(shù)目。 如果沒有給出了指定

55、的列，表的列名應(yīng)和子查詢中的列名是相同的。 完整性規(guī)則不會被傳遞到新表中，僅列的數(shù)據(jù)類型被定義。 ALTER TABLE語句用ALTERTABLE語句來：添加一個新列修改一個已存在的列為新列定義一個默認值刪除一個列 添加列ALTER TABLE table ADD (column datatypeDEFAULT expr, column datatype.);修改列ALTER TABLE table MODIFY (column datatypeDEFAULT expr, column datatype.);刪除列ALTER TABLE table DROP(column); 添加新列用ADD

56、字句添加列ALTER TABLE dept80 ADD (job_id VARCHAR2(9); 添加新列的原則 你可以添加或修改列。 你不能指定新添加的列的位置，新列將成為最后一列。 修改列可以改變列的數(shù)據(jù)類型、大小和默認值A(chǔ)LTER TABLE dept80 MODIFY(last_name VARCHAR2(30);對默認值的改變只影響后來插入表中的數(shù)據(jù) 原則 你可以增加寬度或一個數(shù)字列的精度。 你可以增加數(shù)字列或字符列的寬度。 你可以減少一個列的寬度，但僅在列中只包含空值或表中沒有行時。 你可以改變數(shù)據(jù)類型，但僅在列中只包含空值時。 你可以轉(zhuǎn)換一個CHAR列到VARCHAR2數(shù)據(jù)類型或

57、轉(zhuǎn)換一個VARCHAR2列到 CHAR 數(shù)據(jù)類型僅當列中只包含空值時，或者你不改變列的大小時。 對默認值的改變僅影響以后插入的列。刪除列用DROP COLUMN子句從表中刪除列ALTER TABLE dept80DROP COLUMN job_id; 原則 列可以有也可以沒有數(shù)據(jù)。 用ALTER TABLE語句，一次只能有一列被刪除。 表被修改后必須至少保留一列。 一旦一列被刪除，它不能再恢復(fù)。 刪除表在表中的所有數(shù)據(jù)和結(jié)構(gòu)都被刪除任何未決的事務(wù)都被提交所有的索引被刪除你不能回退DROP TABLE語句DROP TABLE dept80;DROP TABLE語句刪除Oracle表定義，當你刪除

58、一個表時，數(shù)據(jù)庫丟失表中所有的數(shù)據(jù)，并且所有與其相關(guān)的索引也被刪除。 改變一個對象的名字執(zhí)行RENAME語句，改變一個表、視圖、序列或同義詞RENAME dept TO detail_dept;注:你必須是對象的所有者截斷表TRUNCATE TABLE語句： 刪除表中所有的行 釋放該表所使用的存儲空間TRUNCATE TABLE detail_dept;不能回退用TRUNCATE刪除的行作為選擇，可以用DELETE語句刪除行子查詢&操縱數(shù)據(jù) 目標完成本課后完成本課后, 您應(yīng)當能夠執(zhí)行下列操作：您應(yīng)當能夠執(zhí)行下列操作：描述子查詢能夠解決的問題類型定義子查詢列出子查詢的類型寫單行和多行子查

59、詢 描述每個DML 語句插入行到表中更新表中的行從表中刪除行控制事務(wù)用子查詢解決問題 用子查詢解決問題 假想你想要寫一個查詢來找出掙錢比 Abel 的薪水還多的人。為了解決這個問題，你需要兩個查詢：一個找出 Abel 的收入，第二個查詢找出收入高于 Abel 的人。 你可以用組合兩個查詢的方法解決這個問題，放置一個查詢到另一個查詢中。 內(nèi)查詢或子查詢返回一個值給外查詢或主查詢。使用一個子查詢相當于執(zhí)行兩個連續(xù)查詢并且用第一個查詢的結(jié)果作為第二個查詢的搜索值。 子查詢語法SELECT select_listFROM tableWHERE exproperator (SELECTselect_li

60、st FROM table); 子查詢(內(nèi)查詢) 在主查詢之前執(zhí)行一次子查詢的結(jié)果被用于主查詢(外查詢)子查詢可以被放在 CREATE VIEW 語句中、CREATE TABLE 語句、UPDATE 語句、INSERT 語句的 INTO 子句和 UPDATE 語句的 SET 子句中。使用子查詢SELECT last_nameFROM employeesWHERE salary (SELECT salary FROM employees WHERE last_name = Abel);使用子查詢的原則子查詢放在圓括號中將子查詢放在比較條件的右邊在子查詢中的ORDER BY 子句一般不需要。在單行子查詢中用單行運算符，在多行子查詢中用多行運算符 。子查詢