oracle11g database sql語言基礎(chǔ)

1、Oracle11g Database SQL語言基礎(chǔ),2011.8ByKevinlin.林少杰kevinlin.ora@gmail.com,SQL語言簡介,SQL語言(Structured Query Language) 是訪問關(guān)系型數(shù)據(jù)庫的標準語言。SQL語言可以分為五種類型：查詢(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語句可以被嵌入其他語句編寫的程序里進行運行，比如C++程序，也可以使用JDBC將SQL語句 寫到Java程序里。SQL語句也可以使用一些工具運行，與數(shù)據(jù)庫交互，比如Oracle SQL*Plus，Oracle SQL Devel

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

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

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

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

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

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

9、FROM departments;,,消除重復行,,,SELECT DISTINCT department_idFROM employees;,,…,SELECT DISTINCT department_id , job_idFROM employees;,,可以在多個列之前使用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 ;,,比較運算符(Comparison Operators),,,不等于,,兩個值之間(包括限值),BETWEEN...AND...,符合列表內(nèi)的值,IN(set),符合字符樣式,LIKE,是null值,IS NULL,小于,<,小于或等于,<=,大于或等于,>=,大于,>,等于,=,意義,運算符,,,,,,,,,,,,,,,,,,使用比較運算符,,,SELECT last_name, salar

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

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

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

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

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

17、loyee_id, last_name, job_id, salaryFROM employeesWHERE salary >= 10000OR job_id LIKE '%MAN%' ;,,使用邏輯運算符,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ōu)先級規(guī)則,可以使用括號改變默認的優(yōu)先級別,Not equal to,6,NOT,7,AND,8,OR,9,

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

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

21、，使用“&”和“&&”符號替換變量可用于：WHERE 條件子句ORDER BY 子句列表達式表名整個SELECT語句,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ù)進行運算,,輸出,結(jié)果,SQL函數(shù)的兩種類型,,,單行函數(shù),多行函數(shù),,,,每行返回一個結(jié)果,多行返回一個結(jié)果,函數(shù),單行函數(shù)Single-Row Functions,用于處理數(shù)據(jù)對象接收參數(shù)，然后輸出一個結(jié)果每返回一行數(shù)據(jù)就要進行運算每行返回一個結(jié)果可以修改數(shù)據(jù)類型可以被嵌套參數(shù)可以是一個列或一個表達式,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)換，可能導致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ù)的運算順序是從內(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):,條件表達式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ù),,,對各組的數(shù)據(jù)行進行運算，每組返回一個值,EMPLOYEES,,Maxi

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

36、M table[WHERE condition][GROUP BY group_by_expression][ORDER BY column];,多個列的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子句中沒有使用組函數(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ù)。,錯誤：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;,多個表的數(shù)據(jù)查詢,SQL:1999標準中的JOIN語法,使用join從多個表中查詢數(shù)據(jù)：table1.column 表示表table1中的列名NATURAL JOIN 用于對兩個表中相同的

40、列進行joinJOIN table2 USING column_name 基于指定的列進行對等joinJOIN table2 ON table1.column_name = table2.column_name 基于on條件指定的列進行對等joinLEFT/RIGHT/FULL OUTER 用于外連接CROSS JOIN 進行笛卡爾乘積,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 基于兩個表中所有相同的列返回兩個表中相同的列的值相同的數(shù)據(jù)行如果兩個表中，名字相同的列的數(shù)據(jù)類型定義不同，那么會返回錯誤,使用USING進行join,如果兩個表中的列名相同，但數(shù)據(jù)類型不同，那么可以使用using進行對等join.使用USING只能指定的一個名字相同的列,使用ON進行join,使用ON 可以指定任意的條件或相關(guān)的列進行join.使用

43、ON 可以增加語句的易讀性。,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進行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,,自連接是指一個表與自己進行join,SELECT worker.last_name

47、 emp, manager.last_name mgrFROM employees worker JOIN employees managerON (worker.manager_id = manager.employee_id);,…,表EMPLOYEES,查詢結(jié)果,非對等連接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標準中，只返回匹配記錄的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,,,以下情況會進行笛卡爾乘積:沒有join條件Join條件無效所有數(shù)據(jù)都符合join條件盡量避免笛卡爾乘積,子查詢subquery,,問題：誰的工資比員工A高?,,哪個員工的工資比員工A高?,,主查詢:,,員工A的

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

53、數(shù)據(jù)行:子查詢停止運行返回結(jié)果為TRUE如果沒有找到相關(guān)數(shù)據(jù)行:返回結(jié)果為FALSE繼續(xù)子查詢，直到子查詢結(jié)束,NOT Exists 和 NOT in,,如果子查詢返回NULL值，則NOT IN 返回假當沒有null值時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);,查找哪個部門沒有員工(表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等操作都判斷為假，主查詢不返回數(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,集合操作符說明,兩個查詢語句中引用的列數(shù)量要一致第二個查詢的列的數(shù)據(jù)類型須要與第一個查詢的列一一對應可以使用括號改變執(zhí)行順序除UNION ALL外，重復行

58、只顯示一次列名的顯示以第一個查詢?yōu)闇誓J情況下，除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 只能在整個查詢的最后面出現(xiàn)一次ORDER BY 中指定的列，必須在第一個查詢語句中指定的列默認情況下，第一個查詢語句中的第一個列作為返回結(jié)果的排序依

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

63、且同一個領(lǐng)導的員工,,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';,不成對比較子查詢,返回與名字為John的員工中任意一個領(lǐng)導相符和任意一個部門相符的員工當名字為John多于一人時，與成對比較子查詢的返回結(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)子查詢對每個數(shù)據(jù)行都要進行 一次。,,,,,GET主查詢fetch到一行數(shù)據(jù),EXECUTE子查詢根據(jù)這行數(shù)據(jù)的值進行運算,USE根據(jù)子查詢的結(jié)果，決定是否返

67、回這行數(shù)據(jù),,,關(guān)聯(lián)子查詢 vs 嵌套子查詢,嵌套子查詢：子查詢首先執(zhí)行，且執(zhí)行一次，返回相關(guān)結(jié)果主查詢使用子查詢返回的結(jié)果進行一次運算關(guān)聯(lián)子查詢：主查詢fetch到一行數(shù)據(jù)根據(jù)這行數(shù)據(jù)，進行子查詢運算根據(jù)子查詢的結(jié)果，決定是否返回這行數(shù)據(jù)重復以上過程，直到主查詢結(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é)果存放在臨時空間在復雜的查詢中適當使用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ù)到表中修改表中