oracle11g database sql語(yǔ)言基礎(chǔ)

1、Oracle11g Database SQL語(yǔ)言基礎(chǔ),2011.8ByKevinlin.林少杰kevinlin.ora@gmail.com,SQL語(yǔ)言簡(jiǎn)介,SQL語(yǔ)言(Structured Query Language) 是訪問(wèn)關(guān)系型數(shù)據(jù)庫(kù)的標(biāo)準(zhǔn)語(yǔ)言。SQL語(yǔ)言可以分為五種類型：查詢(QUERY) - SELECT數(shù)據(jù)處理(DML)- INSERT, UPDATE, DELETE數(shù)據(jù)定義(DDL) - CREATE

2、, ALTER, DROP, RENAME, TRUNCATE事務(wù)控制(TC) - COMMIT, ROLLBACK, SAVEPOINT數(shù)據(jù)控制(DCL) – GRANT, REVOKE,SQL語(yǔ)句可以被嵌入其他語(yǔ)句編寫的程序里進(jìn)行運(yùn)行，比如C++程序，也可以使用JDBC將SQL語(yǔ)句 寫到Java程序里。SQL語(yǔ)句也可以使用一些工具運(yùn)行，與數(shù)據(jù)庫(kù)交互，比如Oracle SQL*Plus，Oracle SQL Devel

3、oper以及其他第三方工具。,查詢數(shù)據(jù),使用SELECT查詢數(shù)據(jù),,,,,,,,,,,,,,,,,,,,,,,,,,,,,,表1,表2,查詢表中的若干行數(shù)據(jù),查詢表中的若干列,聯(lián)合不同表中的數(shù)據(jù),,,,,,,,,,,,,,,,,,,,,,,,,,,,基本SELECT語(yǔ)句,SELECT 子句指定要查詢的數(shù)據(jù)列。FROM 子句指定對(duì)象表。,SELECT *|{[DISTINCT] column|expression [alias],.

4、..}FROM table;,查詢所有列,SELECT *FROM departments;,查詢指定列,SELECT department_id, location_idFROM departments;,SELECT語(yǔ)句編寫,大小寫不敏感?？梢詫懗梢恍谢蚨嘈?。關(guān)鍵字不能縮寫或跨行。語(yǔ)句中的子句(clause)通常分行輸入。縮進(jìn)增加可讀性。在一般的工具中，SQL語(yǔ)句一般以分號(hào)“;”結(jié)尾。,SELECT語(yǔ)句

5、中的四則運(yùn)算,SELECT last_name, salary, 12*(salary+100)FROM employees;,,Null值,Null值表示相應(yīng)的值未知、不可用、未被分配。Null值不同于數(shù)字0或空格。所有與Null值相關(guān)的四則運(yùn)行，結(jié)果為Null。,定義列別名(Column Alias),重命名列名在有四則運(yùn)算的情況下，可以使列名更具有意義列別名在定義時(shí)，緊跟列名，以空格或“as”分隔當(dāng)列別名的大小寫敏

6、感，或者含有空格、特殊字符，需要使用雙引號(hào)。,,,SELECT last_name "Name" , salary*12 "Annual Salary"FROM employees;,,…,,,連接符操作,將列、字符串連接在一起，作為一個(gè)新列。連接符為兩個(gè)豎線：“||”最終形成的列為字符表達(dá)式。,SELECTlast_name||job_id AS "Employees&qu

7、ot;FROM employees;,原義字符串(Literal Character Strings),原義字符指字符、數(shù)字，或者日期。日期和字符需要使用單引號(hào)封裝。每行數(shù)據(jù)顯示一次。,…,SELECT last_name ||' is a '||job_id AS "Employee Details"FROM employees;,,Quote (q) 操作,可以選擇分

8、隔符?？蛇x擇任意方便的分隔符，單字節(jié)或多字節(jié)，或者下列符號(hào): [ ], { }, ( ), 。增加可讀性與實(shí)用性。,SELECT department_name || ' Department' || q'['s Manager Id: ]' || manager_id AS "Department and Manager"

9、FROM departments;,,消除重復(fù)行,,,SELECT DISTINCT department_idFROM employees;,,…,SELECT DISTINCT department_id , job_idFROM employees;,,可以在多個(gè)列之前使用DISTINCT修飾詞，影響指定的所有列，其結(jié)果是這些列組合后的不同值。,DESCRIBE命令,,DESCRIBE employees,限制返回結(jié)

10、果,,使用WHERE子句限制返回結(jié)果:WHERE 子句在 FROM子句之后。,SELECT *|{[DISTINCT] column|expression [alias],...}FROM table[WHERE condition(s)];,,SELECT employee_id, last_name, job_id, department_idFROM employeesWHERE department_i

11、d = 90 ;,,比較運(yùn)算符(Comparison Operators),,,不等于,,兩個(gè)值之間(包括限值),BETWEEN...AND...,符合列表內(nèi)的值,IN(set),符合字符樣式,LIKE,是null值,IS NULL,小于,<,小于或等于,<=,大于或等于,>=,大于,>,等于,=,意義,運(yùn)算符,,,,,,,,,,,,,,,,,,使用比較運(yùn)算符,,,SELECT last_name, salar

12、yFROM employeesWHERE salary <= 3000 ;,,使用比較運(yùn)算符,,,SELECT last_name, salaryFROM employeesWHERE salary BETWEEN 2500 AND 3500 ;,使用BETWEEN 操作顯示符合范圍的值:,下限,,上限,,,使用比較運(yùn)算符,,SELECT employee_id, last_name, salary, mana

13、ger_idFROM employeesWHERE manager_id IN (100, 101, 201) ;,使用IN 操作符 顯示符合列表內(nèi)值的數(shù)據(jù):,,使用比較運(yùn)算符,,LIKE 操作符篩先符合查找字符串的數(shù)據(jù)行。查找條件可以包括字符或數(shù)字：“%”表示數(shù)字0或者多個(gè)字符?！癬”表示一個(gè)字符。,SELECTfirst_nameFROM employeesWHEREfirst_name LIKE

14、'S%' ;,,使用比較運(yùn)算符,,可以同時(shí)使用 (%, _) 匹配符:使用 ESCAPE “\”標(biāo)識(shí)符查找值中含有“%”和 “_”字符的數(shù)據(jù)。,SELECT last_nameFROM employeesWHERE last_name LIKE '_o%' ;,,SELECT last_nameFROM employeesWHERE last_name LIKE 

15、9;%SA\_%' ESCAPE '\';,,使用比較運(yùn)算符,SELECT last_name, manager_idFROM employeesWHERE manager_id IS NULL ;,使用IS NULL 操作檢驗(yàn)是否有Null值,,邏輯運(yùn)算符,如果條件為假，則返回TRUE,NOT,如果前后兩個(gè)條件有一個(gè)為真，則返回TRUE,OR,如果前后兩個(gè)條件都為真，則返回TRUE,AND,Meani

16、ng,Operator,,,,,,,,,,,使用邏輯運(yùn)算符,SELECT employee_id, last_name, job_id, salaryFROM employeesWHERE salary >= 10000AND job_id LIKE '%MAN%' ;,AND 需要前后兩個(gè)條件為TRUE:,,使用邏輯運(yùn)算符,OR 需要前后兩個(gè)條件有一個(gè)為TRUE:,SELECT emp

17、loyee_id, last_name, job_id, salaryFROM employeesWHERE salary >= 10000OR job_id LIKE '%MAN%' ;,,使用邏輯運(yùn)算符,NOT 操作符可以與IN, BETWEEN, LIKE, and NULL配合使用:,... WHERE job_id NOT IN ('AC_ACCOUNT

18、', 'AD_VP')... WHERE salary NOT BETWEEN 10000 AND 15000... WHERE last_name NOT LIKE '%A%‘... WHERE commission_pct IS NOT NULL,運(yùn)算符的優(yōu)先級(jí)規(guī)則,可以使用括號(hào)改變默認(rèn)的優(yōu)先級(jí)別,Not equal to,6,NOT,7,AND,8,OR,9,

19、IS [NOT] NULL, LIKE, [NOT] IN,4,[NOT] BETWEEN,5,比較運(yùn)算符,3,連接運(yùn)算符,2,四則運(yùn)算,1,運(yùn)算符,優(yōu)先級(jí),,,,,,,,,,,,,,,,,排序ORDER BY,排序使用ORDER BY 子句:ASC: 升序(默認(rèn)行為)DESC: 倒序如果不使用ORDER BY ，相同的兩次查詢返回的結(jié)果順序可以不一樣。使用NULL FIRST和NULL LAST指定NULL值在排序中的位置。

20、ORDER BY 子句中的每個(gè)列都可以單獨(dú)指定排序順序。,排序ORDER BY,默認(rèn)的排序?yàn)樯颍簲?shù)字是最小的值在前 (1 to 999).日期的值最早的在前(01-JAN-11在 01-JAN-12之前)字符按字母表順序(“A” 最前，“Z” 最后).Null 值在升序時(shí)顯示在最后面，在降序時(shí)顯示在最前面可以使用一個(gè)不在SELECT列表中的列來(lái)排序,替換變量Substitution Variables,替換變量臨時(shí)保存數(shù)據(jù)

21、，使用“&”和“&&”符號(hào)替換變量可用于：WHERE 條件子句ORDER BY 子句列表達(dá)式表名整個(gè)SELECT語(yǔ)句,SELECT employee_id, last_name, job_id, &&column_nameFROM employeesORDER BY &column_name ;,SQL函數(shù)SQL functions,函數(shù),輸入,,,,參數(shù) 1,

22、參數(shù)2,參數(shù)n,函數(shù)進(jìn)行運(yùn)算,,輸出,結(jié)果,SQL函數(shù)的兩種類型,,,單行函數(shù),多行函數(shù),,,,每行返回一個(gè)結(jié)果,多行返回一個(gè)結(jié)果,函數(shù),單行函數(shù)Single-Row Functions,用于處理數(shù)據(jù)對(duì)象接收參數(shù)，然后輸出一個(gè)結(jié)果每返回一行數(shù)據(jù)就要進(jìn)行運(yùn)算每行返回一個(gè)結(jié)果可以修改數(shù)據(jù)類型可以被嵌套參數(shù)可以是一個(gè)列或一個(gè)表達(dá)式,function_name [(arg1, arg2,...)],轉(zhuǎn)換函數(shù),字符函數(shù),數(shù)字函數(shù),日期

23、函數(shù),一般函數(shù),字符函數(shù)Character Functions,字符函數(shù),LOWERUPPERINITCAP,CONCATSUBSTRLENGTHINSTRLPAD | RPADTRIMREPLACE,,,大小寫轉(zhuǎn)換函數(shù),字符處理函數(shù),大小寫轉(zhuǎn)換函數(shù)Case-Conversion,sql course,LOWER('SQL Course'),Sql Course,INITCAP('SQL Cour

24、se'),SQL COURSE,UPPER('SQL Course'),結(jié)果,函數(shù),,,,,,,,,字符處理函數(shù),BLACK and BLUE,REPLACE('JACK and JUE','J','BL'),10,LENGTH('HelloWorld'),6,INSTR('HelloWorld', 'W'),***

25、**24000,LPAD(salary,10,'*'),24000*****,RPAD(salary, 10, '*'),HelloWorld,CONCAT('Hello', 'World'),elloWorld,TRIM('H' FROM 'HelloWorld'),Hello,SUBSTR('HelloWorld',1,5

26、),結(jié)果,函數(shù),,,,,,,,,,,,,,,,數(shù)字函數(shù),100,MOD(1600, 300),45.93,ROUND(45.926, 2),45.92,TRUNC(45.926, 2),結(jié)果,函數(shù),,,,,,,,,,,日期函數(shù),'08-SEP-95',NEXT_DAY ('01-SEP-95','FRIDAY'),'28-FEB-95',LAST_DAY ('

27、;01-FEB-95'),19.6774194,MONTHS_BETWEEN ('01-SEP-95','11-JAN-94'),‘29-FEB-96',ADD_MONTHS (‘31-JAN-96',1),Result,Function,,,,,,,,,,,01-JUL-03,TRUNC(SYSDATE ,'MONTH'),01-JAN-03

28、,TRUNC(SYSDATE ,'YEAR'),01-AUG-03,ROUND(SYSDATE,'MONTH'),01-JAN-04,ROUND(SYSDATE ,'YEAR'),,,,,,,,,,轉(zhuǎn)換函數(shù),,,隱式轉(zhuǎn)換,顯式轉(zhuǎn)換,數(shù)據(jù)類型轉(zhuǎn)換,數(shù)據(jù)類型的隱式轉(zhuǎn)換,,NUMBER,VARCHAR2 or CHAR,DATE,VARCHAR2 or CHAR,To,From,,,,,,,,,

29、,VARCHAR2 or CHAR,NUMBER,VARCHAR2 or CHAR,DATE,To,From,,,,,,,,,,… where hire_date > '01-JAN-90',… where name_varchar > 2345,不建議使用隱式轉(zhuǎn)換，可能導(dǎo)致SQL的性能下降,數(shù)據(jù)類型的顯式轉(zhuǎn)換,,數(shù)字類型,字符類型,,,TO_CHAR(),TO_NUMBER(),日期類型,TO_CHAR()

30、,TO_DATE(),日期轉(zhuǎn)換為字符,SELECT last_name, TO_CHAR(hire_date, 'fmDD Month YYYY') AS HIREDATEFROM employees;,…,,,數(shù)字轉(zhuǎn)換為字符,SELECT TO_CHAR(salary, '$99,999.00') SALARYFROM employeesWHERE last_

31、name = 'Ernst';,字符轉(zhuǎn)換為日期、數(shù)字,SQL> select TO_number('6,000.00','99,999.00') from dual;TO_NUMBER('6,000.00','99,999.00')---------------------------------

32、 6000,SQL> select TO_date('20110818','YYYYMMDD') from dual;TO_DATE('201------------18-AUG-11,函數(shù)嵌套,,單行函數(shù)可以嵌套到任意層。函數(shù)的運(yùn)算順序是從內(nèi)到外。,,F3(F2(F1(col,arg1),arg2),arg3),Step 1 = Result 1,Step 2 = Res

33、ult 2,Step 3 = Result 3,,,一般函數(shù),下面的函數(shù)與NULL值的使用有關(guān):,條件表達(dá)式Conditional Expressions,IF-THEN-ELSE的邏輯判斷有兩種方法：CASEDECODE,CASE expr WHEN comparison_expr1 THEN return_expr1 [WHEN comparison_expr2 THEN return_expr2

34、 WHEN comparison_exprn THEN return_exprn ELSE else_expr]END,DECODE(col|expression, search1, result1 [, search2, result2,...,] [, default]),組函數(shù),,,對(duì)各組的數(shù)據(jù)行進(jìn)行運(yùn)算，每組返回一個(gè)值,EMPLOYEES,,Maxi

35、mum salary in EMPLOYEES table,,…,,組函數(shù)類型,,對(duì)各組的數(shù)據(jù)行進(jìn)行運(yùn)算，每組返回一個(gè)值,AVGCOUNTMAXMINSTDDEV(平均差)SUMVARIANCE(方差),組函數(shù),,,,,數(shù)據(jù)分組 GROUP BY,,,表EMPLOYEES,…,,,,,,在EMPLOYEES 表中，計(jì)算每個(gè)部門的平均工資,SELECT column, group_function(column)FRO

36、M table[WHERE condition][GROUP BY group_by_expression][ORDER BY column];,多個(gè)列的GROUP BY,SELECT department_id dept_id, job_id, SUM(salary)FROM employeesGROUP BY department_id, job_id ORDER BY department_

37、id;,組函數(shù)的使用,在SELECT子句中沒(méi)有使用組函數(shù)的數(shù)據(jù)列，必須在GROUP BY 子句中列出:,SELECT department_id, COUNT(last_name)FROM employees;,SELECT department_id, job_id, COUNT(last_name)FROM employeesGROUP BY department_id;,組函數(shù)的使用,,不能在WHERE 子句中限制組

38、，需要使用HAVING。不能在WHERE 子句中使用組函數(shù)。,錯(cuò)誤：SELECT department_id, AVG(salary)FROM employeesWHERE AVG(salary) > 8000GROUP BY department_id;,正確：SELECT department_id, AVG(salary)FROM employeesGROUP BY depart

39、ment_idHAVING AVG(salary) > 8000;,組函數(shù)的嵌套,SELECT MAX(AVG(salary))FROM employeesGROUP BY department_id;,多個(gè)表的數(shù)據(jù)查詢,SQL:1999標(biāo)準(zhǔn)中的JOIN語(yǔ)法,使用join從多個(gè)表中查詢數(shù)據(jù)：table1.column 表示表table1中的列名NATURAL JOIN 用于對(duì)兩個(gè)表中相同的

40、列進(jìn)行joinJOIN table2 USING column_name 基于指定的列進(jìn)行對(duì)等joinJOIN table2 ON table1.column_name = table2.column_name 基于on條件指定的列進(jìn)行對(duì)等joinLEFT/RIGHT/FULL OUTER 用于外連接CROSS JOIN 進(jìn)行笛卡爾乘積,SELECTtable1.column, table2.columnFROMtabl

41、e1[NATURAL JOIN table2] |[JOIN table2 USING (column_name)] |[JOIN table2 ON (table1.column_name = table2.column_name)]|[LEFT|RIGHT|FULL OUTER JOIN table2 ON (table1.column_name = table2.column_name)]|[CROSS JO

42、IN table2];,NATURAL JOIN,NATURAL JOIN 基于兩個(gè)表中所有相同的列返回兩個(gè)表中相同的列的值相同的數(shù)據(jù)行如果兩個(gè)表中，名字相同的列的數(shù)據(jù)類型定義不同，那么會(huì)返回錯(cuò)誤,使用USING進(jìn)行join,如果兩個(gè)表中的列名相同，但數(shù)據(jù)類型不同，那么可以使用using進(jìn)行對(duì)等join.使用USING只能指定的一個(gè)名字相同的列,使用ON進(jìn)行join,使用ON 可以指定任意的條件或相關(guān)的列進(jìn)行join.使用

43、ON 可以增加語(yǔ)句的易讀性。,SELECT e.employee_id, e.last_name, e.department_id, d.department_id, d.location_idFROM employees e JOIN departments dON (e.department_id = d.department_id);,,,,使用ON進(jìn)行join,,SELECT employee_i

44、d, city, department_nameFROM employees e JOIN departments dON d.department_id = e.department_id JOIN locations lON d.location_id = l.location_id;,…,,額外條件,,SELECT e.employee_id, e.last_name, e.departmen

45、t_id, d.department_id, d.location_idFROM employees e JOIN departments dON (e.department_id = d.department_id)AND e.manager_id = 149 ;,如要使用額外條件，可以使用AND或WHERE子句：,,SELECT e.employee_id, e.last_name, e.de

46、partment_id, d.department_id, d.location_idFROM employees e JOIN departments dON (e.department_id = d.department_id)WHERE e.manager_id = 149 ;,或,,自連接self-join,,自連接是指一個(gè)表與自己進(jìn)行join,SELECT worker.last_name

47、 emp, manager.last_name mgrFROM employees worker JOIN employees managerON (worker.manager_id = manager.employee_id);,…,表EMPLOYEES,查詢結(jié)果,非對(duì)等連接Nonequijoins,,,SELECT e.last_name, e.salary, j.grade_levelFROM employe

48、es e JOIN job_grades jON e.salary BETWEEN j.lowest_sal AND j.highest_sal;,,…,,外連接Outer Joins,,,EMPLOYEES,DEPARTMENTS,There are no employees in department 190.,,…,,內(nèi)連接和外連接,,在SQL:1999標(biāo)準(zhǔn)中，只返回匹配記錄的JOIN稱為內(nèi)連接 (inn

49、er join)除返回匹配記錄的結(jié)果外，還要從左邊(右邊)的表中返回不相匹配的數(shù)據(jù)記錄的join稱為左外(右外)連接 (left /right outer join)既返回內(nèi)連接的結(jié)果，又返回左外連接與右外連接的join稱為全外連接 (full outer join)外連接種類：LEFT OUTERRIGHT OUTERFULL OUTER,左外連接Left Outer Join,,,SELECT e.last_name,

50、e.department_id, d.department_nameFROM employees e LEFT OUTER JOIN departments dON (e.department_id = d.department_id) ;,,…,,全外連接Left Outer Join,,,SELECT e.last_name, d.department_id, d.department_nameFROM emplo

51、yees e FULL OUTER JOIN departments dON (e.department_id = d.department_id) ;,…,,,笛卡爾乘積Cartesian Products,,,以下情況會(huì)進(jìn)行笛卡爾乘積:沒(méi)有join條件Join條件無(wú)效所有數(shù)據(jù)都符合join條件盡量避免笛卡爾乘積,子查詢subquery,,問(wèn)題：誰(shuí)的工資比員工A高?,,哪個(gè)員工的工資比員工A高?,,主查詢:,,員工A的

52、工資是多少?,,子查詢:,,子查詢語(yǔ)法,,子查詢首先運(yùn)行子查詢的結(jié)果被主查詢使用子查詢放于小括號(hào)中單行操作符必須與單行子查詢匹配多行操作符必須與多行子查詢匹配,SELECTselect_listFROMtableWHEREexpr operator (SELECTselect_list FROMtable);,,Exists,,EXISTS 測(cè)試子查詢是否具有相關(guān)的數(shù)據(jù)行如果找到相關(guān)

53、數(shù)據(jù)行:子查詢停止運(yùn)行返回結(jié)果為TRUE如果沒(méi)有找到相關(guān)數(shù)據(jù)行:返回結(jié)果為FALSE繼續(xù)子查詢，直到子查詢結(jié)束,NOT Exists 和 NOT in,,如果子查詢返回NULL值，則NOT IN 返回假當(dāng)沒(méi)有null值時(shí)NOT in 與 not exists效果相同,SELECT department_id, department_nameFROM departments dWHERE NOT EXISTS (SELE

54、CT 'X' FROM employees WHERE department_id = d.department_id);,查找哪個(gè)部門沒(méi)有員工(表employees中有一行數(shù)據(jù)的department_id為null),SELECT department_id, department_nameFROM departments dWHERE

55、department_id NOT IN (SELECT department_id FROM employees);,No rows selected.,IN 、ANY 、ALL,,ANY意謂著大于子查詢結(jié)果的最小值=ANY 和IN的效果相同ANY中的子查詢?nèi)绻祷?行，則ANY ，=ANY，!=ANY ，<=ANY等操作都判斷為假，主查詢不返回?cái)?shù)據(jù),SELE

56、CT employee_id, last_name, job_id, salaryFROM employeesWHERE salary 'IT_PROG';,,,,IN 、ANY 、ALL,,ALL意謂著大于子查詢結(jié)果的最大值A(chǔ)LL中的子查詢?nèi)绻祷?行，則ALL ，=ALL，!=ALL ，<=ALL等操作都判斷為真，主查詢返回符合其他條件的數(shù)據(jù),SELECT employee_id, last_na

57、me, job_id, salaryFROM employeesWHERE salary 'IT_PROG';,,,,集合操作符Set Operators,UNION/UNION ALL,A,B,A,B,A,B,INTERSECT,A,B,MINUS,集合操作符說(shuō)明,兩個(gè)查詢語(yǔ)句中引用的列數(shù)量要一致第二個(gè)查詢的列的數(shù)據(jù)類型須要與第一個(gè)查詢的列一一對(duì)應(yīng)可以使用括號(hào)改變執(zhí)行順序除UNION ALL外，重復(fù)行

58、只顯示一次列名的顯示以第一個(gè)查詢?yōu)闇?zhǔn)默認(rèn)情況下，除UNION ALL外，查詢結(jié)果以升序排序,UNION,,,SELECT employee_id, job_idFROM employeesUNIONSELECT employee_id, job_idFROM job_history;,,…,…,,UNION ALL,,,,SELECT employee_id, job_id, department_idFROM

59、 employeesUNION ALLSELECT employee_id, job_id, department_idFROM job_historyORDER BY employee_id;,,…,…,,,INTERSECT,,,,SELECT employee_id, job_idFROM employeesINTERSECTSELECT employee_id, job_idFROM job_his

60、tory;,,MINUS,,,SELECT employee_idFROM employeesMINUSSELECT employee_idFROM job_history;,,…,列數(shù)量與類型匹配,,,,SELECT location_id, department_name "Department", TO_CHAR(NULL) "Warehouse location"

61、 FROM departmentsUNIONSELECT location_id, TO_CHAR(NULL) "Department", state_provinceFROM locations;,集合操作符與ORDER BY,,,ORDER BY 只能在整個(gè)查詢的最后面出現(xiàn)一次ORDER BY 中指定的列，必須在第一個(gè)查詢語(yǔ)句中指定的列默認(rèn)情況下，第一個(gè)查詢語(yǔ)句中的第一個(gè)列作為返回結(jié)果的排序依

62、據(jù)，順序?yàn)樯?多列子查詢Multiple-Column Subqueries,,主查詢,WHERE (MANAGER_ID, DEPARTMENT_ID) IN,子查詢結(jié)果,100 90102 60124 50,,,,多列子查詢Multiple-Column Subqueries,不成對(duì)比較成對(duì)比較,成對(duì)比較子查詢,返回與名字為John的員工在同一個(gè)部門，

63、且同一個(gè)領(lǐng)導(dǎo)的員工,,SELECTemployee_id, manager_id, department_idFROMempl_demoWHERE (manager_id, department_id) IN (SELECT manager_id, department_id FROM empl_demo

64、 WHERE first_name = 'John')AND first_name 'John';,不成對(duì)比較子查詢,返回與名字為John的員工中任意一個(gè)領(lǐng)導(dǎo)相符和任意一個(gè)部門相符的員工當(dāng)名字為John多于一人時(shí)，與成對(duì)比較子查詢的返回結(jié)果不同。,,SELECT employee_id, manager_id, department_idFROM emp

65、l_demoWHERE manager_id IN (SELECT manager_id FROM empl_demo WHERE first_name = 'John')AND department_id IN (SELECT department_id

66、 FROM empl_demo WHERE first_name = 'John') AND first_name 'John';,,,關(guān)聯(lián)子查詢,關(guān)聯(lián)子查詢用于行與行的處理，關(guān)聯(lián)子查詢對(duì)每個(gè)數(shù)據(jù)行都要進(jìn)行 一次。,,,,,GET主查詢fetch到一行數(shù)據(jù),EXECUTE子查詢根據(jù)這行數(shù)據(jù)的值進(jìn)行運(yùn)算,USE根據(jù)子查詢的結(jié)果，決定是否返

67、回這行數(shù)據(jù),,,關(guān)聯(lián)子查詢 vs 嵌套子查詢,嵌套子查詢：子查詢首先執(zhí)行，且執(zhí)行一次，返回相關(guān)結(jié)果主查詢使用子查詢返回的結(jié)果進(jìn)行一次運(yùn)算關(guān)聯(lián)子查詢：主查詢fetch到一行數(shù)據(jù)根據(jù)這行數(shù)據(jù)，進(jìn)行子查詢運(yùn)算根據(jù)子查詢的結(jié)果，決定是否返回這行數(shù)據(jù)重復(fù)以上過(guò)程，直到主查詢結(jié)束,使用關(guān)聯(lián)子查詢,,,SELECT column1, column2, ... FROM table1 WHERE column1 opera

68、tor (SELECT column1, column2 FROM table2 WHERE expr1 = .expr2);,outer,outer,,,,,SELECT e.employee_id, last_name,e.job_idFROM employees e WHERE 2 <

69、;= (SELECT COUNT(*) FROM job_history WHERE employee_id = e.employee_id);,WITH,,,使用WITH，可以在查詢中多次引用相同的子查詢使用WITH 返回的結(jié)果存放在臨時(shí)空間在復(fù)雜的查詢中適當(dāng)使用WITH，可以提高性能,WITH示例,,,,,WITH dept_costs AS ( SELECT

70、 d.department_name, SUM(e.salary) AS dept_total FROM employees e JOIN departments d ON e.department_id = d.department_id GROUP BY d.department_name),avg_cost AS ( SELECT SUM(dept_total)/COUNT(*) AS

71、dept_avg FROM dept_costs)SELECT * FROM dept_costs WHERE dept_total > (SELECT dept_avg FROM avg_cost)ORDER BY department_name;,處理數(shù)據(jù),DML Data Manipulation Language,,,DML 的作用:添加新的數(shù)據(jù)到表中修改表中