1、SQL 語言基礎(chǔ),主要內(nèi)容,基本的SELECT語句 約束和排序數(shù)據(jù) 單行函數(shù) 多表顯示數(shù)據(jù)、組函數(shù)合計數(shù)據(jù) 創(chuàng)建和管理表 子查詢 SELECT 確定哪些列 FROM 確定哪張表,選擇所有列與指定列,SELECT * FROM departments; 用跟在SELECT關(guān)鍵字后面的星號 (*)，你能夠顯示表中數(shù)據(jù)的所有列。 SELECT department_id, location_id FROM departments; 你能夠用SELECT語句來顯示表的指定列，指定列名之間用逗號分隔。,寫SQL 語句,SQL 語句對大小寫不敏感 SQL 語句可以寫成一行或多行 關(guān)鍵字不能簡寫或分開折行

2、子句通常放在不同的行 縮進用于增強可讀性,算術(shù)表達式,用算術(shù)運算符創(chuàng)建數(shù)字和日期數(shù)據(jù)的表達式 操作 說明 + 加 - 減 * 乘 / 除,使用算術(shù)運算符,SELECT last_name, salary, salary + 300 FROM employees; 優(yōu)先級: 乘法和除法比加法和減法的優(yōu)先級高 相同優(yōu)先級的運算符從左到右計算 圓括號用于強制優(yōu)先計算，并且使語句更 清晰 SELECT last_name, salary, 12*salary+100 FROM employees;,空值,null 是一個未分配的、未知的，或不適用的值 null不是0，也不是空格 包含空值的算術(shù)表達式計

3、算結(jié)果為空 SELECT last_name, job_id, salary, commission_pct FROM employees;,定義列別名,列別名： 改變列標(biāo)題的名字 可用于計算結(jié)果 緊跟在列名后面在列名和別名之間可以有選項AS 關(guān)鍵字 如果別名中包含有空格、或者特殊字符、或者大小寫敏感，要求用雙引號 SELECT last_name AS name, commission_pct comm FROM employees;,連字運算符,連字運算符： 連接列或者字符串到其它的列 用兩個豎線表示(|) 構(gòu)造一個字符表達式的合成列 SELECT first_name|last_name

4、 AS Employees FROM employees;,文字字符串,文字字符串是包含在SELECT列表中的一個字符串，一個數(shù)字或者一個日期 日期和字符的文字字符串值必須用單引號括起來 每個文字字符串在每行輸出一次 SELECT last_name| is a |job_id AS Employee Details” FROM employees;,約束和排序數(shù)據(jù),目標(biāo),完成本課后, 您應(yīng)當(dāng)能夠執(zhí)行下列操作： 用一個查詢限制返回的行 用一個查詢分類返回的行,限制選擇的行,用WHERE子句限制返回的行 SELECT * |DISTINCT column|expressionalias,. FR

5、OM 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 WHERE department_id = 90 ;,字符串和日期,字符串和日期的值放在單引號中 字符值區(qū)分大小寫，日期值是格

6、式敏感的 日期的默認(rèn)格式是DD-MON-RR. SELECT last_name, job_id, department_id FROM employees WHERE last_name = Whalen;,比較條件,運算 含義 = 等于 大于 = 大于等于 不等于 比較條件被用于一個表達式與一個值或與另一個表達式的比較。 . WHERE hire_date=01-JAN-95 . WHERE salary=6000 . WHERE last_name=Smith,其它比較條件,操作 含義 BETWEEN.AND. 在兩個值之間(包含) IN(set) 匹配一個任意值列表 LIKE 匹配一個

7、字符模板 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 IN (100, 101, 201);,其它比較條件,使用LIKE條件 使用LIKE條件執(zhí)行有效搜索串值的通配符搜索 搜索條件既可以包含文字也可以包含數(shù)字： %表示任意順序的零個或多個字符 _表示一個字符

8、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條件。,邏輯條件,運算 含義 AND 如果兩個組成部分的條件都為真，返回TRUE OR 如果兩個組成部分中的任一個條件為真，返回TRUE NOT 如果跟隨的條件為假，返回TRUE 可以在WHERE子句中用AND和OR運算符使用多個條

9、件。 使用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_id, salary FROM employees WHERE salary = 10000 OR job_id LIKE %MAN%; 使用NOT操作 SELECT last_name, job_id FROM em

10、ployees 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邏輯條件 8 OR邏輯條件 使用圓括號改變優(yōu)先規(guī)則,ORDER BY子句,用ORDER BY子句排序行 ASC: 升序排序，默認(rèn) DESC: 降序排序 ORDER BY子句在SELECT語句的最后 SELECT last_n

11、ame, job_date FROM employees ORDER BY hire_date ; 語法 : SELECT expr FROM table WHERE condition(s) ORDER BY column, expr ASC|DESC;,單行函數(shù),目標(biāo),完成本課后，您應(yīng)當(dāng)能夠執(zhí)行下列操作： 描述在SQL 中可用的函數(shù)的變量類型 在SELECT語句中使用字符，數(shù)字和日期函數(shù) 描述轉(zhuǎn)換函數(shù)的使用,SQL 函數(shù),函數(shù)是SQL的一個非常強有力的特性，函數(shù)能夠用于下面的目的： 執(zhí)行數(shù)據(jù)計算 修改單個數(shù)據(jù)項 操縱輸出進行行分組 格式化顯示的日期和數(shù)字 轉(zhuǎn)換列數(shù)據(jù)類型 SQL函數(shù)有輸入?yún)?/p>

12、數(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ù)在后面的課程中介紹。,單行函數(shù),單行函數(shù)： 操縱數(shù)據(jù)項 接受多個參數(shù)并返回一個值 作用于每一個返回行 每行返回一個結(jié)果 可以修改數(shù)據(jù)類型 可以嵌套 接受多個參數(shù)，參數(shù)可以是一個列或者一個表達式,單行函數(shù)（續(xù)）,單行函數(shù)的特性包括

13、： 作用于查詢中返回的每一行 每行返回一個結(jié)果 可能返回一個與參數(shù)不同類型的數(shù)據(jù)值 可能需要一個或多個參數(shù) 能夠用在SELECT、WHERE和ORDER BY子句中，可以嵌套 。 function_name(arg1, arg2,.) function_name 是函數(shù)的名字。 arg1, arg2 是由函數(shù)使用的任意參數(shù)，可以由一個列名或者一個表達式提供。,單行函數(shù)（續(xù)）,本課包括下面的單行函數(shù)： 字符函數(shù)：接受字符輸入，可以返回字符或者數(shù)字值 數(shù)字函數(shù)：接受數(shù)字輸入，返回數(shù)字值 日期函數(shù)：對DATE數(shù)據(jù)類型的值進行運算 (除了MONTHS_BETWEEN函數(shù)返回一個數(shù)字，所有日期函數(shù)都返回

14、一個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ù)可以被分為下面兩種： 大小寫處理函數(shù) 字符處理函數(shù) 大小寫處理函數(shù)如下： LOWER(column|expression) 轉(zhuǎn)換字符值為小寫 UPPER(column|expression) 轉(zhuǎn)換字符值為大寫 INITCAP(column|expression) 轉(zhuǎn)換每個單詞的首字母值為大寫，所有 其它值為小寫 字符處理函數(shù)如下

15、： CONCAT(column1|expression1 ,column2|expression2) 連接第一個字符值到第二個字符值；等價于連接運算符 (|) SUBSTR(column|expression,m ,n) 從字符值中返回指定的字符，開始位置在 m，n字符長度 (如果 m 是負(fù)數(shù)，計數(shù)從字符值末尾開始；如果 n 被忽略，返回到串結(jié)束的所有字符)。,LENGTH(column|expression) 返回表達式中的字符數(shù) INSTR(column|expression, string, ,m, n ) 返回一個命名串的數(shù)字位置。隨意地，你可以提供一個位置m作為查找的開始，在字符串中

16、第n次發(fā)現(xiàn)的位置。m和n的默認(rèn)值是1，意味著在起始開始查找，并且報告第一個發(fā)現(xiàn)的位置。 LPAD(column|expression, n, string) RPAD(column|expression, n, string) 填充字符值左、右調(diào)節(jié)到n字符位置的總寬度 TRIM(leading|trailing|both , trim_character FROM trim_source) 使你能夠從一個字符串修整頭或尾字符(或兩者)。如果trim_character或trim_source是字符文字，你必須放在單引號中。 REPLACE(text, search_string, replac

17、ement_string) 從字符串查找一個文本表達式，如果找到，用指定的值串代替它,字符函數(shù)（續(xù)）,大小寫處理函數(shù),這些函數(shù)轉(zhuǎn)換字符串的大小寫 函 數(shù) 結(jié) 果 LOWER(SQL Course) sql course UPPER(SQL Course) SQL COURSE INITCAP(SQL Course) Sql Course LOWER：轉(zhuǎn)換大小寫混合的字符串為小寫字符串 UPPER：轉(zhuǎn)換大小寫混合的字符串為大寫字符串 INITCAP：將每個單詞的首字母轉(zhuǎn)換為大寫，其他字母為小寫 SELECT The job id for |UPPER(last_name)| is |LOWER(

18、job_id) AS EMPLOYEE DETAILS FROM employees;,字符處理函數(shù),函 數(shù) 結(jié) 果 CONCAT(Hello, World) HelloWorld SUBSTR(HelloWorld,1,5) Hello LENGTH(HelloWorld) 10 INSTR(HelloWorld, W) 6 LPAD(salary, 10,*) *24000 RPAD(salary, 10, *) 24000 * TRIM(H FROM HelloWorld) elloWorld,數(shù)字函數(shù),ROUND:四舍五入指定小數(shù)的值 ROUND(45.926, 2) 45.93 TR

19、UNC(45.926, 2) 45.92 MOD(1600, 300) 100 ROUND(column|expression, n) 四舍五入列、表達式或值為n位小數(shù)位，或者，如果n被忽略，無小數(shù)位。(如果n是負(fù)值，小數(shù)點左邊的數(shù)被四舍五入) TRUNC(column|expression,n) 截斷列、表達式或值到n位小數(shù)，或者，如果n被忽略，那么n默認(rè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 TRU

20、NC(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函數(shù)經(jīng)常用于確定一個值是奇數(shù)還是偶數(shù),日期的使用,Oracle 數(shù)據(jù)庫用內(nèi)部數(shù)字格式存儲日期：世紀(jì)，年，月，日，小時，分鐘和秒 默認(rèn)日期顯示格式是DD-MON-RR. SELECT last_name, hire_date FROM employees WHERE last_name like G

21、%; SYSDATE函數(shù)返回： Date Time SYSDATE是一個日期函數(shù)，它返回當(dāng)前數(shù)據(jù)庫服務(wù)器的日期和時間。 SELECT SYSDATE FROM DUAL;,用日期計算,從日期加或者減一個數(shù)，結(jié)果是一個日期值 兩個日期相減，得到兩個日期之間的天數(shù) 用小時數(shù)除以24，可以加小時到日期上 既然數(shù)據(jù)庫以數(shù)字方式存儲日期，你就可以用算術(shù)運算符進行計算，例如，加或減。你可以加或減數(shù)字常數(shù)以及日期。 你可以進行下面的運算： 運算 結(jié)果 說明 date + number 日期 加一個天數(shù)到一個日期上 date - number 日期 從一個日期上減一個天數(shù) date - date 天數(shù) 用一個

22、日期減另一個日期 date + number/24 日期 加一個小時數(shù)到一個日期上,日期函數(shù),函 數(shù) 說 明 MONTHS_BETWEEN 兩個日期之間的月數(shù) ADD_MONTHS 加日歷月到日期 NEXT_DAY 下個星期幾是幾號 LAST_DAY 指定月的最后一天 ROUND 四舍五入日期 TRUNC 截斷日期,日期函數(shù)（續(xù)）,MONTHS_BETWEEN(date1, date2)：計算date1和date2之間的月數(shù)，其結(jié)果可以是正的也可以是負(fù)的。如果date1大于date2，結(jié)果是正的，反之，結(jié)果是負(fù)的。結(jié)果的小數(shù)部分表示月的一部分。 ADD_MONTHS(date, n)：添加n個

23、日歷月到date。n的值必須是整數(shù)，但可以是負(fù)的。 NEXT_DAY(date, char)：計算在date之后的下一個周(char)指定天的日期。char的值可能是一個表示一天的數(shù)或者是一個字符串。 LAST_DAY(date)：計算包含date的月的最后一天的日期 ROUND(date,fmt)：返回用格式化模式fmt四舍五入到指定單位的 date ，如果格式模式 fmt 被忽略，date被四舍五入到最近的天。 TRUNC(date, fmt)：返回用格式化模式fmt截斷到指定單位的帶天的時間部分的date，如果格式模式fmt被忽略，date被截斷到最近的天。,使用日期函數(shù),MONTHS_

24、BETWEEN (01-SEP-95,11-JAN-94) 19.6774194 ADD_MONTHS (11-JAN-94,6) 11-JUL-94 NEXT_DAY (01-SEP-95,2) 下個星期五是幾號 08-SEP-95 LAST_DAY(01-FEB-95) 28-FEB-95,使用日期函數(shù)（續(xù)）,假定SYSDATE = 25-JUL-95: ROUND(SYSDATE,MONTH) 01-AUG-95 ROUND(SYSDATE ,YEAR) 01-JAN-96 TRUNC(SYSDATE ,MONTH) 01-JUL-95 TRUNC(SYSDATE ,YEAR) 01-J

25、AN-95 TRUNC(TO_DATE(25-JUL-95) ,YEAR) 01-JAN-95,轉(zhuǎn)換函數(shù),數(shù)據(jù)類型轉(zhuǎn)換 隱式數(shù)據(jù)類型轉(zhuǎn)換 顯式數(shù)據(jù)類型轉(zhuǎn)換 對于直接賦值，Oracle 服務(wù)器能夠自動地進行下面 的轉(zhuǎn)換： 從 到 VARCHAR2 or CHAR NUMBER VARCHAR2 or CHAR DATE NUMBER VARCHAR2 DATE VARCHAR2 對于表達式賦值，Oracle 服務(wù)器能自動地進行下面的轉(zhuǎn)換： 從 VARCHAR2 or CHAR 到 DATE 從 VARCHAR2 or CHAR 到 NUMBER,轉(zhuǎn)換函數(shù)(續(xù)）,顯式數(shù)據(jù)類型轉(zhuǎn)換 SQL 提供三種

26、函數(shù)來從一種數(shù)據(jù)類型轉(zhuǎn)換值到另一種： TO_CHAR(number|date, fmt, nlsparams) 轉(zhuǎn)換一個數(shù)字或日期值為一個VARCHAR2字符串，帶格式 化樣式fmt。 數(shù)字轉(zhuǎn)換：nlsparams 參數(shù)指定下面的字符，它由數(shù)字格式 化元素返回： 小數(shù)字符 99999.99 前導(dǎo)0 09999 本地貨幣符號 L9999 國際貨幣符號 $9999 如果忽略nlsparams或其它參數(shù)，該函數(shù)在會話中使用默認(rèn)參數(shù)值。,TO_CHAR(number|date, fmt, nlsparams) 指定返回的月和日名字及其縮寫的語言。如果忽略該參數(shù)， 該函數(shù)在會話中使用默認(rèn)日期語言 。 T

27、O_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。Nlsparams參數(shù)的目的與 TO_CHAR函數(shù)用于日期轉(zhuǎn)換時的目的相同。,對日期使用TO_CHAR函數(shù),TO_CHAR(date, format_model) 格式模板 必須加單引號，并且區(qū)分大小寫 能夠包含任一有效的日期格式元

28、素 有一個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 周中天的三字母縮寫 DAY 周中天的全名 MON 月的三字母縮寫 DD 月的數(shù)字天,使用TO_NUMBER和TO_DATE函數(shù),轉(zhuǎn)換字符串到數(shù)字，用TO_NUMBER函數(shù)格式化： TO_NUMBER(char, for

29、mat_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, expr3) NULLIF (expr1, expr2) COALESCE (expr1, expr2, ., exprn) NVL 轉(zhuǎn)換空值為一個實際值 NVL2 如果expr1非

30、空，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) NVL(job_id,No Job Yet) 語法: NVL (expr1, expr2) 在語法中： expr1 是包含空值的源值或者表達式 expr2 是用于轉(zhuǎn)換空

31、值的目的值 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 nul

32、l,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ù) 能夠接受多個交替的值。 如果第一個表達式非空，

33、它返回該表達式；否則，它做一個 保留表達式的結(jié)合 。 COALESCE 函數(shù)返回列表中的第一個非空表達式。 語法 COALESCE (expr1, expr2, . exprn) 在語法中： expr1 如果它非空，返回該表達式 expr2 如果第一個表達式為空并且該表達式非空，返回該表達式 exprn 如果前面的表達式都為空，返回該表達式 Select coalesce( , ,bca) from dual;,條件表達式,在SQL 語句中提供IF-THEN-ELSE 邏輯的使用。 兩種用法： CASE表達式 DECODE函數(shù) CASE表達式 CASE expr WHEN comparison

34、_expr1 THEN return_expr1 WHEN comparison_expr2 THEN return_expr2 WHEN comparison_exprn THEN return_exprn ELSE else_expr END,DECODE函數(shù),DECODE(col|expression, search1, result1 , search2, result2,., default) DECODE 函數(shù)在比較表達式 (expression) 和每個查找 (search) 值后解碼表達式，如果表達式與查找相同，返 回結(jié)果。 如果省略默認(rèn)值，當(dāng)沒有查找值與表達式相匹配時返回 一

35、個空值。,多表顯示數(shù)據(jù) 在WHERE 子句中寫連接條件 當(dāng)多個表中有相同的列名時，將表名作為列名的前綴定義連接 當(dāng)數(shù)據(jù)從多表中查詢時，要使用連接 (join) 條件。一個表中 的行按照存在于相應(yīng)列中的公值被連接到另一個表中的行， 即，通常所說的主鍵和外鍵列。,什么是等值連接?,EMPLOYEES DEPARTMENTS,用等值連接返回記錄,SELECT employees.employee_id, employees.last_name, employees.department_id, departments.department_id, departments.location_id FR

36、OM employees, departments WHERE employees.department_id = departments.department_id;,使用表別名,使用表別名簡化查詢 使用表別名改善性能 SELECT e.employee_id, e.last_name, e.department_id, d.department_id, d.location_id FROM employees e , departments d WHERE e.department_id = d.department_id; 原則 表別名最多可以有 30 個字符，但短一些更好。 如果在 F

37、ROM 子句中表別名被用于指定的表，那么在整個 SELECT 語句中都要使用表別名。 表別名應(yīng)該是有意義的。 表別名只對當(dāng)前的 SELECT 語句有效。,多于兩個表的連接,EMPLOYEES DEPARTMENTS LOCATIONS,非等值連接,EMPLOYEES JOB_GRADES,用非等值連接返回記錄,SELECT e.last_name, e.salary, j.grade_level FROM employees e, job_grades j WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal;,外連接,DEPARTMENT

38、S EMPLOYEES,外連接語法,你可以用一個外連接查看那些不滿足連接條件的行 外連接運算符是加號(+) SELECT table1.column, table2.column FROM table1, table2 WHERE table1.column(+)=table2.column; SELECT table1.column, table2.column FROM table1, table2 WHERE table1.column = table2.column(+); 用外連接返回不直接匹配的記錄 如果在連接條件中使用外連接操作，缺少的行就可以被返 回。操作符是一個在圓括號中的加

39、號 (+)，它被放置在連接 的缺少信息的一側(cè)。為了使來自不完善表的一行或多行能夠 被連接，該操作符有產(chǎn)生一個或多個空行的作用。,使用外連接,SELECT e.last_name, e.department_id, d.department_name FROM employees e, departments d WHERE e.department_id (+) = d.department_id ;,什么是組函數(shù)?,組函數(shù)操作行集，給出每組的結(jié)果 EMPLOYEES,在EMPLOYEES 表中的最高薪水,組函數(shù)的類型,AVG 平均值 COUNT 計數(shù) MAX 最大值 MIN 最小值 STDD

40、EV 標(biāo)準(zhǔn)差 SUM 合計 VARIANCE 方差,組函數(shù) (續(xù)),每個函數(shù)接收一個參數(shù)，下面的表確定你可以在語法中使用的選項： 函 數(shù) 說 明 AVG(DISTINCT|ALLn) n 的平均值，忽略空值 COUNT(*|DISTINCT|ALLexpr) 用 * 計數(shù)所有行，包括重復(fù)和帶空值 的行。expr 求除了空計算 MAX(DISTINCT|ALLexpr) expr的最大值，忽略空值 MIN(DISTINCT|ALLexpr) expr的最小值，忽略空值 STDDEV(DISTINCT|ALLx) n 的標(biāo)準(zhǔn)差，忽略空值 SUM(DISTINCT|ALLn) 合計 n 的值，忽略空

41、值 VARIANCE(DISTINCT|ALLx) n 的方差，忽略空值,組函數(shù)的語法,SELECTcolumn, group_function(column), . FROM table WHERE condition GROUP BYcolumn ORDER BYcolumn; 使用組函數(shù)的原則 DISTINCT 使得函數(shù)只考慮不重復(fù)的值；ALL 使得函數(shù)考慮每個值，包括重復(fù)值。默認(rèn)值是 ALL ，因此不需要指定。 用于函數(shù)的參數(shù)的數(shù)據(jù)類型可以是 CHAR、VARCHAR2、NUMBER 或 DATE。 所有組函數(shù)忽略空值。為了用一個值代替空值，用 NVL、NVL2 或 COALESCE

42、函數(shù)。 當(dāng)使用 GROUP BY 子句時，Oracle 服務(wù)器隱式以升序排序結(jié)果集。為了覆蓋該默認(rèn)順序，DESC 可以被用于 ORDER BY 子句。,使用AVG 、SUM、MIN、MAX 函數(shù),你可以使用AVG 和SUM 用于數(shù)字?jǐn)?shù)據(jù) SELECT AVG(salary), MAX(salary), MIN(salary), SUM(salary) FROM employees WHERE job_id LIKE %REP%;,使用COUNT 函數(shù),COUNT(*) 返回一個表中的行數(shù) COUNT 函數(shù)有三中格式： COUNT(*) COUNT(expr) COUNT(DISTINCT ex

43、pr) COUNT(*) 返回表中滿足 SELECT 語句標(biāo)準(zhǔn)的行數(shù)，包括重復(fù)行，包括有空值列的行。如果 WHERE 子句包括在 SELECT 語句中，COUNT(*) 返回滿足 WHERE 子句條件的行數(shù)。 COUNT(expr) 返回在列中的由 expr 指定的非空值的數(shù)。 COUNT(DISTINCT expr) 返回在列中的由 expr 指定的唯一的非空值的數(shù)。 注：expr為列名,組函數(shù)和Null 值,所有組函數(shù)忽略列中的空值。在幻燈片的例子中， 平均值只基于表中的那些 COMMISSION_PCT 列 的值有效的行的計算。平均值計算是用付給所有雇 員的總傭金除以接受傭金的雇員數(shù) (

44、4)。 SELECT AVG(commission_pct) FROM employees;,在組函數(shù)中使用NVL 函數(shù),NVL 函數(shù)強制組函數(shù)包括空值。在幻燈片的例子中，平均值 被基于所有表中的行來計算，不管 COMMISSION_PCT 列 是否為空。平均值的計算是用付給所有雇員的總傭金除以公 司的雇員總數(shù) (20)。 SELECT AVG(NVL(commission_pct, 0) FROM employees;,創(chuàng)建數(shù)據(jù)組:GROUP BY 子句語法,用GROUP BY 子句劃分表中的行到較小的組中 SELECT column, group_function(column) FROM

45、 table WHERE condition GROUP BY group_by_expression ORDER BY column; 在語法中， group_by_expression 指定那些用于將行分組的 列，這些列的值作為行分組的依據(jù)。 使用 WHERE 子句，你可以在劃分行成組以前過濾行。 在 GROUP BY 子句中必須包含列。 在 GROUP BY 子句中你不能用列別名。 默認(rèn)情況下，行以包含在 GROUP BY 列表中的字段的升序排序。你可以用 ORDER BY 子句覆蓋這個默認(rèn)值。 如果在 SELECT 子句中包含了組函數(shù)，就不能選擇單獨的結(jié)果，除非單獨的列出現(xiàn)在 GROU

46、P BY 子句中。如果你未能在 GROUP BY 子句中包含一個字段列表，你會收到一個錯誤信息。,約束分組結(jié)果: HAVING 子句,用HAVING 子句約束分組： 1.行被分組 2.應(yīng)用組函數(shù) 3.匹配HAVING 子句的組被顯示 SELECT column, group_function FROM table WHERE condition GROUP BY group_by_expression HAVING group_condition ORDER BY column;,使用HAVING 子句,SELECT department_id, MAX(salary) FROM employ

47、ees GROUP BY department_id HAVING MAX(salary)10000 ;,嵌套組函數(shù),顯示最大平均薪水 SELECT MAX(AVG(salary) FROM employees GROUP BY department_id;,創(chuàng)建和管理表,目標(biāo),完成本課后, 您應(yīng)當(dāng)能夠執(zhí)行下列操作： 描述主要數(shù)據(jù)庫對象 創(chuàng)建表 描述列定義時可用的數(shù)據(jù)類型 改變表的定義 刪除、改名和截斷表,數(shù)據(jù)庫對象,表 基本存儲單元, 由行和列組成 視圖 邏輯地從一個或多個表中表示數(shù)據(jù)子集 序列 數(shù)字值發(fā)生器 索引 改善一些查詢的性能 同義詞 給對象可選擇的名字,命名規(guī)則,表命名和列命名：

48、必須以字母開始 必須是130 個字符長度 只能包含AZ, az, 09, _, $, 和# 同一個用戶所擁有的對象之間不能重名 不能用Oracle 服務(wù)器的保留字 注：名字是大小寫不敏感的，例如， EMPLOYEES 與 eMPloyees 或 eMpLOYEES 作為同一個名字來處理。,CREATE TABLE語句,用戶必須有： CREATE TABLE權(quán)限 一個存儲區(qū)域 CREATE TABLE schema.table (column datatypeDEFAULT expr, .); 必須指定： 表名 列名、列數(shù)據(jù)類型和列的大小 schema 與所有者的名字一樣 table 表的名字

49、DEFAULT expr 指定默認(rèn)值 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); 確認(rèn)表的創(chuàng)建 DESCRIBE dept,Oracle 數(shù)據(jù)庫中的表,用戶表： 由用戶創(chuàng)建和維護的表

50、的集合 包含用戶信息 數(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_tables ; 查看本用戶所擁有的不同的對象類型 S

51、ELECT 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ù)字?jǐn)?shù)據(jù) DATE 日期和時間值 LONG 最大2G的可變長度字符數(shù)據(jù) CLOB 最大4G的字符數(shù)據(jù) RAW and LONG RAW 原始二進制數(shù)據(jù) BLOB 最大4G的二進制數(shù)據(jù) BFILE 最大4G的，存儲在外部文件中的二 進制數(shù)據(jù) ROWID

52、一個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語句返回的行被插入到新表中。 字段的定義只能包括列名和默認(rèn)值。 如果給出了指定的列，列的數(shù)目必須等于子查詢的SELECT列表的列數(shù)目。 如果沒有給出了指定的列，表的列名應(yīng)和子

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

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

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

56、除Oracle表定義，當(dāng)你刪除一個表時， 數(shù)據(jù)庫丟失表中所有的數(shù)據(jù)，并且所有與其相關(guān)的索引也被 刪除。,改變一個對象的名字,執(zhí)行RENAME語句，改變一個表、視圖、序列或 同義詞 RENAME dept TO detail_dept; 注:你必須是對象的所有者,截斷表,TRUNCATE TABLE語句： 刪除表中所有的行 釋放該表所使用的存儲空間 TRUNCATE TABLE detail_dept; 不能回退用TRUNCATE刪除的行 作為選擇，可以用DELETE語句刪除行,子查詢 子查詢(內(nèi)查詢) 在主查詢之前執(zhí)行一次 子查詢的結(jié)果被用于主查詢(外查詢) 子查詢可以被放在 CREATE V

57、IEW 語句中、CREATE TABLE 語句、UPDATE 語句、INSERT 語句的 INTO 子句 和 UPDATE 語句的 SET 子句中。,使用子查詢,SELECT last_name FROM employees WHERE salary (SELECT salary FROM employees WHERE last_name = Abel);,使用子查詢的原則,子查詢放在圓括號中 將子查詢放在比較條件的右邊 在子查詢中的ORDER BY 子句一般不需要。 在單行子查詢中用單行運算符，在多行子查詢中用 多行運算符 。 子查詢的類型 單行子查詢：從內(nèi) SELECT 語句只返回一行的查詢 多行子查詢：從內(nèi) SELECT 語句返回多行的查詢,單行子查詢,僅返回一行 使用單行比較符 運算符 含義 = 等于