精通SQL【經(jīng)典SQL語(yǔ)句大全】

1、課程 一 PL/SQL 基本查詢與排序本課重點(diǎn)：1、寫SELECT語(yǔ)句進(jìn)行數(shù)據(jù)庫(kù)查詢2、進(jìn)行數(shù)學(xué)運(yùn)算3、處理空值4、使用別名ALIASES5、連接列6、在SQL PLUS中編輯緩沖，修改SQL SCRIPTS7、ORDER BY進(jìn)行排序輸出。8、使用WHERE 字段。一、寫SQL 命令：不區(qū)分大小寫。SQL 語(yǔ)句用數(shù)字分行，在SQL PLUS中被稱為緩沖區(qū)。最后以；或 / 結(jié)束語(yǔ)句。也可以用RUN來(lái)執(zhí)行語(yǔ)句二、從表中查詢相關(guān)的字段。例1：SQL> SELECT dept_id, last_name, manager_id FROM s_emp;2：SQL> SELECT last_

2、name, salary * 12, commission_pct FROM s_emp;對(duì)于數(shù)值或日期型的字段，可以進(jìn)行相應(yīng)的四則運(yùn)算，優(yōu)先級(jí)與標(biāo)準(zhǔn)的高級(jí)語(yǔ)言相同。 SQL> SELECT last_name, salary, 12 * (salary + 100) FROM s_emp;三、列的別名ALIASES：計(jì)算的時(shí)候特別有用；緊跟著列名，或在列名與別名之間加“AS”；如果別名中含有SPACE，特殊字符，或大小寫，要用雙引號(hào)引起。例(因字體原因，讀者請(qǐng)記?。阂?hào)為英文雙引號(hào)Double Quotation)：SQL> SELECT last_name, salary,12

3、 * (salary + 100) ”Annual Salary” FROM s_emp;四、連接符號(hào)：|連接不同的列或連接字符串使結(jié)果成為一個(gè)有意義的短語(yǔ)：SQL> SELECT first_name | | last_name| , | title ”Employees” FROM s_emp;五、管理NULL值：SQL> SELECT last_name, title,salary * NVL(commission_pct,0)/100 COMM FROM s_emp; 此函數(shù)使NULL轉(zhuǎn)化為有意義的一個(gè)值，相當(dāng)于替換NULL。六、SQL PLUS的基本內(nèi)容，請(qǐng)參考<S

4、QL PLUS 簡(jiǎn)單實(shí)用精髓篇 >七、ORDER BY 操作：與其他SQL92標(biāo)準(zhǔn)數(shù)據(jù)庫(kù)相似，排序如：SELECT expr FROM table ORDER BY column,expr ASC|DESC;另：通過(guò)位置判斷排序：SQL> SELECT last_name, salary*12 FROM s_emp ORDER BY 2;這樣就避免了再寫一次很長(zhǎng)的表達(dá)式。另：多列排序：SQL> SELECT last name, dept_id, salary FROM s_emp ORDER BY dept_id, salary DESC;八、限制選取行：SELECT ex

5、pr FROM table WHERE condition(s) ORDER BY expr;例1：SQL> SELECT first_name, last_name, start_date FROM s_emp WHERE start_date BETWEEN 09-may-91 AND 17-jun-91;例2：SQL> SELECT last_name FROM s_emp WHERE last_name LIKE _a%; /顯示所有第二個(gè)字母為 a的last_name例3：如果有列為NULLSQL> SELECT id, name, credit_rating FR

6、OM s_customer WHERE sales_rep_id IS NULL;優(yōu)先級(jí)：Order Evaluated Operator1 All comparison operators (=, <>, >, >=, <, <=, IN, LIKE, IS NULL, BETWEEN)2 AND3 OR總結(jié)：我們今天主要學(xué)習(xí)了如何進(jìn)行查詢SELECT操作，具體的組合查詢與子查詢將在以后的課堂中學(xué)習(xí)，同時(shí)希望大家可以工作、學(xué)習(xí)中多多摸索，實(shí)踐!課程 二 PL/SQL PL/SQL 查詢行函數(shù)本課重點(diǎn)：1、掌握各種在PL/SQL中可用的ROW函數(shù)2、使用這些

7、函數(shù)的基本概念3、SELECT語(yǔ)句中使用函數(shù)4、使用轉(zhuǎn)換函數(shù)注意：以下實(shí)例中標(biāo)點(diǎn)均為英文半角一、FUNCTION的作用：進(jìn)行數(shù)據(jù)計(jì)算，修改獨(dú)立的數(shù)據(jù)，處理一組記錄的輸出，不同日期顯示格式，進(jìn)行數(shù)據(jù)類型轉(zhuǎn)換 函數(shù)分為：?jiǎn)为?dú)函數(shù)(ROW)和分組函數(shù)注意：可以嵌套、可以在SELECT, WHERE, 和 ORDER BY中出現(xiàn)。語(yǔ)法：function_name (column|expression, arg1, arg2,.)二、字符型函數(shù)1、LOWER 轉(zhuǎn)小寫2、UPPER轉(zhuǎn)大寫3、INITCAP 首字母大寫4、CONCAT 連接字符，相當(dāng)于 |5、SUBSTR SUBSTR(column|exp

8、ression,m,n)6、LENGTH 返回字符串的長(zhǎng)度7、NVL 轉(zhuǎn)換空值其中，1、2經(jīng)常用來(lái)排雜，也就是排除插入值的大小寫混用的干擾，如：SQL> SELECT first_name, last_name FROM s_emp WHERE UPPER(last_name) = PATEL;FIRST_NAME LAST_NAME- -Vikram PatelRadha Patel三、數(shù)學(xué)運(yùn)算函數(shù)(SQL也能用函數(shù)來(lái)進(jìn)行四舍五入)1、ROUND四舍五入：ROUND(45.923，2) = 45.92ROUND(45.923，0) = 46ROUND(45.923，-1) = 502、

9、TRUNC截取函數(shù)(取地下)TRUNC(45.923，2)= 45.92TRUNC(45.923)= 45(默為去除小數(shù)點(diǎn))TRUNC(45.923，-1)= 403、MOD 余除MOD(1600，300)實(shí)例：SQL> SELECT ROUND(45.923,2), ROUND(45.923,0),ROUND(45.923,-1) FROM SYS.DUAL;四、ORACLE 日期格式和日期型函數(shù)：1、默認(rèn)格式為DD-MON-YY.2、SYSDATE是一個(gè)求系統(tǒng)時(shí)間的函數(shù)3、DUAL'dju:el 是一個(gè)偽表，有人稱之為空表，但不確切。SQL> SELECT SYSDAT

10、E FROM SYS.DUAL;4、日期中應(yīng)用的算術(shù)運(yùn)算符例：SQL> SELECT last_name, (SYSDATE-start_date)/7 WEEKS FROM s_emp WHERE dept_id = 43; DATE+ NUMBER = DATEDATE-DATE= NUMBER OF DAYSDATE + (NUMBER/24) = 加1小時(shí)5、函數(shù)：MONTHS_BETWEEN(date1, date2) 月份間隔，可正，可負(fù)，也可是小數(shù)ADD_MONTHS(date,n) 加上N個(gè)月，這是一個(gè)整數(shù)，但可以為負(fù)NEXT_DAY(date,char) 如：NEXT_

11、DAY (restock_date,FRIDAY),從此日起下個(gè)周五。ROUND(date,fmt)TRUNC(date,fmt)解釋下面的例子：SQL> SELECT id, start_date,MONTHS_BETWEEN (SYSDATE,start_date) TENURE, ADD_MONTHS(start_date,6) REVIEW FROM s_emp WHERE MONTHS_BETWEEN (SYSDATE,start_date)<48; 我們看到： MONTHS_BETWEEN (SYSDATE,start_date)<48，說(shuō)明至今工作未滿一年的員工

12、。LAST_DAY (restock_date) 返回本月的最后一天SQL> select round(sysdate,'MONTH') from dualROUND(SYSD-01-11月-01round(sysdate,'YEAR') = 01-1月 -02ROUND 之后的值比基值大的最小符合值，大家可以用更改系統(tǒng)時(shí)間的方法測(cè)試，以15天為分界線，也是非常形象的四舍五入，而TRUNC恰好相反，是對(duì)現(xiàn)有的日期的截取。五、轉(zhuǎn)換函數(shù)：1、TO_CHAR使一個(gè)數(shù)字或日期轉(zhuǎn)換為CHAR2、TO_NUMBER把字符轉(zhuǎn)換為NUMBER3、TO_DATE字符轉(zhuǎn)換為日

13、期這幾個(gè)函數(shù)較為簡(jiǎn)單，但要多多實(shí)踐，多看復(fù)雜的實(shí)例。SQL> SELECT ID,TO_CHAR(date_ordered,MM/YY) ORDERED FROM s_ord WHERE sales_rep_id = 11; 轉(zhuǎn)換時(shí)，要注意正確的缺省格式：SELECT TO_DATE('03-MAR-92') CORRECT FROM DUAL；/正確SELECT TO_DATE('031092') CORRECT FROM DUAL；/不正確SELECT TO_DATE('031095','MMDDYY') ERRORR

14、FROM DUAL輸出 3月10日SELECT TO_DATE('031095','DDMMYY') ERRORR FROM DUAL輸出 10月3日4、實(shí)例：select to_char(sysdate,'fmDDSPTH "of" MONTH YYYY AM') TODAYS FROM DUAL;TODAYS-SIXTEENTH of 11月 2001 下午大小寫沒有什么影響，引號(hào)中間的是不參與運(yùn)算。實(shí)例 ：SELECT ROUND(SALARY*1.25) FROM ONE_TABLE；意義：漲25%工資后，去除小數(shù)位。

15、在現(xiàn)實(shí)操作中，很有意義。5、混合實(shí)例：SQL> SELECT last_name, TO_CHAR(start_date,fmDD ”of” Month YYYY) HIREDATE FROM s_empWHERE start_date LIKE %91;LAST_NAME HIREDATE- -Nagayama 17 of June 1991Urguhart 18 of January 1991Havel 27 of February 1991這里要注意：fmDD 和 fmDDSPTH之間的區(qū)別。SQL> SELECT id, total, date_ordered FROM s

16、_ord WHERE date_ordered =TO_DATE(September 7, 1992,Month dd, YYYY);六、獨(dú)立的函數(shù)嵌套SQL> SELECT CONCAT(UPPER(last_name),SUBSTR(title,3) ”Vice Presidents”FROM s_empWHERE title LIKE VP%; * 嵌套可以進(jìn)行到任意深度，從內(nèi)向外計(jì)算。例：SQL> SELECT TO_CHAR(NEXT_DAY(ADD_MONTHS(date_ordered,6),FRIDAY),fmDay, Month ddth, YYYY) ”New

17、6 Month Review” FROM s_ord ORDER BY date_ordered;SQL> SELECT last_name,NVL(TO_CHAR(manager_id),No Manager) FROM s_emp WHERE manager_id IS NULL; 對(duì)于例子，大家重要的理解，并多做測(cè)試，并注意英文版和中文版在日期上的區(qū)別。有些教材上的例子，不要盲目的相信其結(jié)果，實(shí)踐后才有發(fā)言權(quán)，希望大家能夠在學(xué)習(xí)的過(guò)程中不要忽略了用，多想一想為什么實(shí)例要如此設(shè)計(jì)，在何種情況下應(yīng)用此實(shí)例來(lái)解決問(wèn)題。這樣，我們才真正掌握了知識(shí)。課程 三 從多個(gè)表中提取數(shù)據(jù)本課重點(diǎn)：1、

18、SELECT FROM 多個(gè)表，使用等連接或非等連接2、使用外連接OUTER JOIN3、使用自連接注意：以下實(shí)例中標(biāo)點(diǎn)均為英文半角一、連接的概念：是指一個(gè)從多個(gè)表中的數(shù)據(jù)進(jìn)行的查詢。連接一般使用表的主鍵和外鍵。連接類型：等連接、不等連接、外連接、自連接二、Cartesian product ：指的是當(dāng)JOIN條件被省略或無(wú)效時(shí)，所有表的行(交叉)都被SELECT出來(lái)的現(xiàn)象。Cartesian product可以產(chǎn)生大量的記錄，除非是你有意如此，否則應(yīng)該加上某種條件限制。SQL> SELECT name, last_name2 FROM s_dept, s_emp;300 rows se

19、lected. 其中一個(gè)表12行，一個(gè)表25行。三、簡(jiǎn)單連接查詢：SELECT table.column, table.column.FROM table1, table2WHERE table1.column1 = table2.column2;如：SQL> SELECT s_emp.last_name, s_emp.dept_id,2 s_3 FROM s_emp, s_dept4 WHERE s_emp.dept_id = s_dept.id;注意：表前綴的重要性：SQL> SELECT s_dept.id ”Department ID”,2 s_regio

20、n.id ”Region ID”,3 s_ ”Region Name”4 FROM s_dept, s_region5 WHERE s_dept.region_id = s_region.id;在WHERE 段中，如果沒有前綴，兩個(gè)表中都有ID字段，就顯得的模棱兩可，AMBIGUOUS。這在實(shí)際中應(yīng)該盡量避免。WHERE 字段中，還可以有其他的連接條件，如在上例中，加上：INITCAP(s_dept.last_name) = Menchu;再如：WHERE s_emp.dept_id = s_dept.id AND s_dept.region_id = s_region.

21、id AND s_mission_pct > 0;四、表別名ALIAS：1、使用別名進(jìn)行多表查詢 。2、僅在這個(gè)查詢中生效，一旦用了表別名，就不能再用表的原有的名字進(jìn)行連接。實(shí)例：SQL> SELECT ”Customer Name”,2 c.region_id ”Region ID”,3 ”Region Name”4 FROM s_customer c, s_region r5 WHERE c.region_id = r.id;別名最多可以30個(gè)字符，但當(dāng)然越少越好。最好也能容易識(shí)別。五、非等連接非等連接一般用在沒有明確的等量關(guān)系的兩個(gè)表；最簡(jiǎn)單的說(shuō)：

22、非等連接就是在連接中沒有“=”出現(xiàn)的連接。SQL> SELECT e.ename, e.job, e.sal, s.grade2 FROM emp e, salgrade s3 WHERE e.sal BETWEEN s.losal AND s.hisal;說(shuō)明：Create a non-equijoin to evaluate an employees salary grade. The salary 必須在另一個(gè)表中最高和最低之間。 其他操作符<= >= 也可以實(shí)現(xiàn)，但是BETWEEN是非常簡(jiǎn)單實(shí)用的。BETWEEN .AND是指閉區(qū)間的，這點(diǎn)要注意 ，請(qǐng)大家測(cè)試。六、外

23、連接語(yǔ)法結(jié)構(gòu)：SELECT table.column, table.columnFROM table1, table2WHERE table1.column = table2.column(+);實(shí)例：SQL> SELECT e.last_name, e.id, 2 FROM s_emp e, s_customer c3 WHERE e.id (+) = c.sales_rep_id4 ORDER BY e.id;顯示.，即使有的客戶沒有銷售代表。* 可以理解為有+號(hào)的一邊出現(xiàn)了NULL，也可以做為合法的條件。外連接的限制：1、外連接符只能出現(xiàn)在信息缺少的那邊。2、在條件中，

24、不能用 IN 或者 OR做連接符。七、自連接同一個(gè)表中使用連接符進(jìn)行查詢；FROM 的后面用同一個(gè)表的兩個(gè)別名。實(shí)例：SQL> SELECT worker.last_name| works for |2 manager.last_name3 FROM s_emp worker, s_emp manager4 WHERE worker.manager_id = manager.id;意味著：一個(gè)員工的經(jīng)理ID匹配了經(jīng)理的員工號(hào)，但這個(gè)像繞口令的連接方式并不常用。以后我們會(huì)見到一種 子查詢：select last_name from s_emp where salary=(select ma

25、x(salary) from s_emp)也可以看作是一種變向的自連接，但通常我們將其課程 四 組函數(shù)本課重點(diǎn)：1、了解可用的組函數(shù)2、說(shuō)明每個(gè)組函數(shù)的使用方法3、使用GROUP BY4、通過(guò)HAVING來(lái)限制返回組注意：以下實(shí)例中標(biāo)點(diǎn)均為英文半角一、概念：組函數(shù)是指按每組返回結(jié)果的函數(shù)。組函數(shù)可以出現(xiàn)在SELECT和HAVING 字段中。GROUP BY把SELECT 的結(jié)果集分成幾個(gè)小組。HAVING 來(lái)限制返回組，對(duì)RESULT SET而言。二、組函數(shù)：(#號(hào)的函數(shù)不做重點(diǎn))1、AVG2、COUNT3、MAX4、MIN5、STDDEV #6、SUM7、VARIANCE #語(yǔ)法：SELEC

26、T column, group_functionFROM tableWHERE conditionGROUP BY group_by_expressionHAVING group_conditionORDER BY column;實(shí)例1：一個(gè)混合實(shí)例，說(shuō)明所有問(wèn)題：SQL> SELECT AVG(salary), MAX(salary), MIN(salary),2 SUM(salary)3 FROM s_emp4 WHERE UPPER(title) LIKE SALES%;AVG(SALARY) MAX(SALARY) MIN(SALARY) SUM(SALARY)- - - -14

27、76 1525 1400 7380說(shuō)明：很多函數(shù)，我們?cè)谥v函數(shù)的已經(jīng)向大家介紹過(guò)，但在此為何叫分組函數(shù)呢，主要是因?yàn)樗鼈兛梢耘cGROUP BY來(lái)形成對(duì)不同組的計(jì)算，相當(dāng)于在很多值中進(jìn)行挑選。* MIN MAX函數(shù)可以接任何數(shù)據(jù)類型。如果是MIN(last_name), MAX(last_name)，返回的是什么呢？千萬(wàn)記住，不是指LAST_NAME的長(zhǎng)度，而是指在FIRST字母的前后順序，第一個(gè)相同，然后比較第二個(gè)，如：xdopt > cssingkdkdk > adopt > acccc實(shí)例2：SQL> SELECT COUNT(commission_pct)2 FR

28、OM s_emp3 WHERE dept_id = 31;返回所有非空行個(gè)數(shù)三、GROUP BY的應(yīng)用：先看一個(gè)簡(jiǎn)單實(shí)例：SQL> SELECT credit_rating, COUNT(*) ”# Cust”2 FROM s_customer3 GROUP BY credit_rating;注意這里別名的應(yīng)用，復(fù)習(xí)一下從前的課程，加了引號(hào)后，就可以用特殊字符，但也僅有三個(gè)：#$_，什么對(duì)象的名字都如此。當(dāng)然空格也是可以的。復(fù)雜實(shí)例：SQL> SELECT title, SUM(salary) PAYROLL2 FROM s_emp3 WHERE title NOT LIKE VP

29、%4 GROUP BY title5 ORDER BY SUM(salary);這里要注意一下幾個(gè)CLAUSE的先后次序。WHERE在這里主要是做參與分組的記錄的限制。*另外，如果要選取出來(lái)一個(gè)不加組函數(shù)的列，如上面的TITLE，就要把這個(gè)列GROUP BY !否則要出錯(cuò)的!信息為：ERROR at line 1:ORA-00937: not a single-group group function理論很簡(jiǎn)單，如果不GROUP BY TITLE，顯示哪一個(gè)呢？這個(gè)在試題中經(jīng)常出現(xiàn)。結(jié)論：不加分組函數(shù)修飾的列必定要出現(xiàn)在GROUP BY 里。錯(cuò)誤實(shí)例：SQL> SELECT dept_i

30、d, AVG(salary)2 FROM s_emp3 WHERE AVG(salary) > 20004 GROUP BY dept_id;WHERE AVG(salary) > 2000*ERROR at line 3:ORA-00934: group function is not allowed here應(yīng)在GROUP BY 后面加上HAVING AVG(salary) > 2000;因?yàn)槭怯脕?lái)限制組的返回。多級(jí)分組實(shí)例：SQL> SELECT dept_id, title, COUNT(*)2 FROM s_emp3 GROUP BY dept_id, tit

31、le;就是先按照DEPT_ID分組，當(dāng)DEPT_ID相同的時(shí)候，再按TITLE分組，而COUNT(*)以合成的組計(jì)數(shù)。 順序?qū)Y(jié)果有決定性的影響?？偨Y(jié)：本課我們主要學(xué)習(xí)了分組函數(shù)的使用及如何進(jìn)行分組查詢，我們可以想像一下，SQL SERVER中有COMPUTE BY，來(lái)進(jìn)行分組總數(shù)的計(jì)算，但在ORACLE中是沒有的。大家可以建立一個(gè)有多個(gè)列，多個(gè)重復(fù)值的表，然后進(jìn)行各種分組的演示，用得多了，自然明了。課程 五 子查詢本課重點(diǎn)：1、在條件未知的情況下采用嵌套子查詢2、用子查詢做數(shù)據(jù)處理3、子查詢排序注意：以下實(shí)例中標(biāo)點(diǎn)均為英文半角一、概述：子查詢是一種SELECT句式中的高級(jí)特性，就是一個(gè)SEL

32、ECT語(yǔ)句作為另一個(gè)語(yǔ)句的一個(gè)段。我們可以利用子查詢來(lái)在WHERE字段中引用另一個(gè)查詢來(lái)攻取值以補(bǔ)充其無(wú)法事先預(yù)知的子結(jié)果。子查詢可以用在WHERE子句，HAING子句，SELECT或DELETE語(yǔ)句中的FROM 子句。注意：1、子查詢必須在一對(duì)圓括號(hào)里。2、比較符號(hào)：>, =, 或者 IN.3、子查詢必須出現(xiàn)在操作符的右邊4、子查詢不能出現(xiàn)在ORDER BY里 (試題中有時(shí)出現(xiàn)找哪行出錯(cuò))二、子查詢的執(zhí)行過(guò)程：NESTED QUERY MAIN QUERYSQL> SELECT dept_id SQL> SELECT last_name, title2 FROM s_emp

33、 2 FROM s_emp3 WHERE UPPER(last_name)=BIRI; 3 WHERE dept_id =這里 ，每個(gè)查詢只運(yùn)行一次。當(dāng)然，子查詢要首先被執(zhí)行，大家設(shè)想一下，如果子查詢中有一個(gè)以上的人的LASTNAME為BIRI，會(huì)如何？-會(huì)出錯(cuò)，因?yàn)椴荒苡?來(lái)連接。ORA-1427: single-row subquery returns more thanone row以上的查詢也被稱之為 單行子查詢。DELECT子查詢實(shí)例：delete from new_table where cata_time > to_date('19990901','

34、yyyymmdd') and pro_name=(select pro_name from new_product where pro_addr in ('bj','sh')三、子查詢中的GROUP 函數(shù)的應(yīng)用實(shí)例 1：SQL> SELECT last_name, title, salary2 FROM s_emp3 WHERE salary <4 (SELECT AVG(salary)5 FROM s_emp);實(shí)例2：選擇出工資最高的員工的家庭住址：select emp_addr from employees where salary =

35、(select max(salary) from employees);這是一個(gè)簡(jiǎn)單實(shí)用的例子，可以衍生出很多情況，在實(shí)際應(yīng)用經(jīng)常出現(xiàn)，請(qǐng)大家多多思考。實(shí)例3：SQL> SELECT dept_id, AVG(salary)2 FROM s_emp3 GROUP BY dept_id4 HAVING AVG(salary) >5 (SELECT AVG(salary)6 FROM s_emp7 WHERE dept_id = 32);子查詢被多次執(zhí)行，因?yàn)樗霈F(xiàn)在HAVING 子句中。SQL> SELECT title, AVG(salary)2 FROM s_emp3 GR

36、OUP BY title4 HAVING AVG(salary) =5 (SELECT MIN(AVG(salary)6 FROM s_emp7 GROUP BY title);對(duì)子查詢，我們了解這么多在理論上已經(jīng)覆蓋了所有的知識(shí)點(diǎn)，對(duì)于UPDATE 和DELETE的子查詢，不作為重點(diǎn)，但也要練習(xí)掌握。今天到這，謝謝大家。課程 六 運(yùn)行時(shí)應(yīng)用變量本課重點(diǎn)：1、創(chuàng)建一個(gè)SELECT語(yǔ)句，提示USER在運(yùn)行時(shí)先對(duì)變量賦值。2、自動(dòng)定義一系列變量，在SELECT運(yùn)行時(shí)進(jìn)行提取。3、在SQL PLUS中用ACCEPT定義變量注意：以下實(shí)例中標(biāo)點(diǎn)均為英文半角一、概述：變量可以在運(yùn)行時(shí)應(yīng)用，變量可以出現(xiàn)在

37、WHERE 字段，文本串，列名，表名等。1、我們這里的運(yùn)行時(shí)，指的是在SQL PLUS中運(yùn)行。2、ACCEPT ：讀取用戶輸入的值并賦值給變量3、DEFINE：創(chuàng)建并賦值給一個(gè)變量4、在做REPORT時(shí)經(jīng)常使用，比如對(duì)某個(gè)部門的銷售信息進(jìn)行統(tǒng)計(jì)，部門名稱可以以變量代替。SQL PLUS不支持對(duì)輸入數(shù)據(jù)的有效性檢查，因此提示要簡(jiǎn)單且不模棱兩可。二、應(yīng)用實(shí)例：1、SQL> SELECT id, last_name, salary2 FROM s_emp3 WHERE dept_id = &department_number;2、可以在賦值前后進(jìn)行比較：SET VERIFY ON.1*

38、 select * from emp where lastname='&last_name'輸入 last_name 的值: adopt原值 1: select * from emp where lastname='&last_name'新值 1: select * from emp where lastname='adopt'-如果在原語(yǔ)句中沒有單引號(hào)，那么在輸入值的時(shí)候要手工加上單引號(hào)。一般字符和日期型要在語(yǔ)句中加上單引號(hào)。SET VERIFY OFF 之后，原值和新值這兩句消失。這在ORACLE8I中是默認(rèn)為ON。3、子句為

39、變量：WHERE &condition; 要注意引號(hào)三、DEFINE和ACCEPT的應(yīng)用：1、SET ECHO OFF /使內(nèi)容不 顯示在用戶界面ACCEPT p_dname PROMPT Provide the department name: SELECT , r.id, ”REGION NAME”FROM s_dept d, s_region rWHERE d.region_id = r.idAND UPPER() LIKE UPPER(%&p_dname%)/SET ECHO ON存為文件：l7prompt.SQLSQL> S

40、TART l7promptProvide the department name: sales2、SQL> DEFINE dname = salesSQL> DEFINE dnameDEFINE dname = ”sales” (CHAR)SQL> SELECT name2 FROM s_dept3 WHERE lower(name) = &dname;可以正常執(zhí)行了。SQL> DEFINE dname 主要是顯示當(dāng)前的變量是否賦值，值是什么。當(dāng)然，我們可以用UNDEFINEGO 來(lái)使變量恢復(fù)初始，不然它會(huì)一直保持下去。3、如果變量在SQL SCRIPT文件中確

41、定 ：可以SQL> START l7param President 來(lái)賦值?？偨Y(jié)：本課主要針對(duì)較古老的SQLPLUS方法，在REPORT和結(jié)果集生成方面使用變量，達(dá)到方便操作，動(dòng)態(tài)修改的目的。課程 七 其他數(shù)據(jù)庫(kù)對(duì)象SEQUENCE創(chuàng)建實(shí)例：SQL> CREATE SEQUENCE s_dept_id2 INCREMENT BY 13 START WITH 514 MAXVALUE 99999995 NOCACHE6 NOCYCLE;Sequence created.1、NEXTVAL和CURRVAL的用法只有在INSERT 中，才可以作為子查詢出現(xiàn)。以下幾個(gè)方面不可用子查詢：SE

42、LECT 子句OF A VIEW有DISTINCT的出現(xiàn)的SELECT。有GROUP BY，HAVING，ORDER BY的SELECTSELECT 或DELETE，UPDATE 中的子查詢。子句。DEFAULT選項(xiàng)中不能用。2、編輯SEQUENCE只有OWNER或有ALTER權(quán)限的用戶才能修改SEQUENCE未來(lái)的NUMBER受修改的影響。不能修改START WITH，如果變，則要RECREATE。修改會(huì)受到某些有效性檢驗(yàn)的限制，如MAXVALUE3、刪除：DROP SEQUENCE sequence;ORACLE對(duì)象之INDEX一、INDEX概述：是ORACLE的一種數(shù)據(jù)對(duì)象，用POINT

43、ER來(lái)加速查詢行。通過(guò)快速路徑存取方法定位數(shù)據(jù)并減少I/O。 INDEX獨(dú)立于表。INDEX由ORACLE SERVER來(lái)使用和保持。二、索引如何建立？1、自動(dòng)：通過(guò)PRIMARY KEY和UNIQUE KEY約束來(lái)建立。2、用戶手工建立非唯一性索引。三、創(chuàng)建方法：語(yǔ)法：CREATE INDEX indexON table (column, column.);何時(shí)建立INDEX：此列經(jīng)常被放到WHERE字段或JOIN來(lái)作條件查詢。此列含有大量的數(shù)據(jù)。此列含有大量的空值。兩個(gè)或幾個(gè)列經(jīng)常同時(shí)放到WHERE字段進(jìn)行組合查詢表很大而且只有少于2-4% 的ROW可能被查詢的時(shí)候。以下情況不要建立索引：

44、表很??；表被更新頻繁。四、查看已經(jīng)存在的索引：1、USER_INDEXES可以查詢索引名和類型。2、USER_IND_COLUMNS包含索引名、表名、列名。實(shí)例：SQL> SELECT ic.index_name, ic.column_name,2 ic.column_position col_pos, ix.uniqueness3 FROM user_indexes ix, user_ind_columns ic4 WHERE ic.index_name = ix.index_name5 AND ic.table_name = S_EMP;五、刪除索引：DROP INDEX index

45、;SYNONYMS 同義詞語(yǔ)法：CREATE PUBLIC SYNONYM synonym for object;注意：此對(duì)象不能包含在一個(gè)包里；一個(gè)私有的同義詞不能與同一USER的其他對(duì)象重名。DROP SYNONYM D_SUM；課程 八 用戶訪問(wèn)控制本課重點(diǎn)：1、創(chuàng)建用戶2、創(chuàng)建角色來(lái)進(jìn)行安全設(shè)置3、使用GRANT或REVOKE 來(lái)控制權(quán)限注意：以下實(shí)例中標(biāo)點(diǎn)均為英文半角一、概述：ORACLE通過(guò)用戶名和密碼進(jìn)行權(quán)限控制。數(shù)據(jù)庫(kù)安全：系統(tǒng)安全和數(shù)據(jù)安全系統(tǒng)權(quán)限：使用戶可以訪問(wèn)數(shù)據(jù)庫(kù)對(duì)象權(quán)限：操縱數(shù)據(jù)庫(kù)中的對(duì)象SCHEMA：各種對(duì)象的集合二、系統(tǒng)權(quán)限：1、超過(guò)80個(gè)權(quán)限可用。2、DBA有最

46、高的系統(tǒng)權(quán)限：CREATE NEW USERREMOVE USERSREMOVE ANY TABLEBACKUP ANY TABLE三、創(chuàng)建用戶1、CREATE USER user IDENTIFIED BY password;2、系統(tǒng)權(quán)限：CREATE SESSION Connect to the database.CREATE TABLE Create tables in the users schema.CREATE SEQUENCE Create a sequence in the users schema.CREATE VIEW Create a view in the users

47、schema.CREATE PROCEDURE Create a stored procedure, function, or package in the users schema.3、授權(quán)用戶系統(tǒng)權(quán)限：GRANT privilege , privilege. TO user , user.;GRANT CREATE TABLE TO SCOTT；四、角色的使用1、概念：角色是一組權(quán)限的命名，可以授予給用戶。這樣就如同給了某個(gè)用戶一個(gè)權(quán)限包。2、創(chuàng)建、授予給角色：CREATE ROLE MANAGER；GRANT CREATE TABLE，CREATE VIEW TO MANAGER；GRA

48、NT MANAGER TO CLARK五、修改密碼：ALTER USER user IDENTIFIED BY password;六、對(duì)象權(quán)限：1、語(yǔ)句：GRANT object_priv(, object_priv.)|ALL(columns) ON objectTO user, user.|role|PUBLICWITH GRANT OPTION;2、實(shí)例：最簡(jiǎn)單：SQL> GRANT select2 ON s_emp3 TO sue, rich;稍復(fù)雜：SQL> GRANT update (name, region_id) 2 ON s_dept3 TO scott, man

49、ager;SQL> GRANT select, insert2 ON s_dept3 TO scott4 WITH GRANT OPTION;課程 九 聲明變量本課重點(diǎn)：1、了解基本的PLSQL塊和區(qū)域2、描述變量在PLSQL中的重要性3、區(qū)別PLSQL與非PLSQL變量4、聲明變量5、執(zhí)行PLSQL塊注意：以下實(shí)例中標(biāo)點(diǎn)均為英文半角一、概述：1、PLSQL 塊結(jié)構(gòu)：DECLARE - 可選變量聲明定義BEGIN - 必選SQL 和PLSQL 語(yǔ)句EXCEPTION - 可選錯(cuò)誤處理END；- 必選二、實(shí)例：declarevjob varchar(9);v_count number:=0

50、;vtotal date:=sysdate +7;c_tax constant number(3,2):=8.25;v_valid boolean not null:=true;beginselect sysdate into vtotal from dual;end;/上例中，如果沒有這個(gè)SELECT語(yǔ)句，會(huì)如何？出錯(cuò)，說(shuō)明必須有STATEMENTS如果： select sysdate from dual into vtotal ;同樣，也不行。而且變量與賦值的類型要匹配。三、%TYPE的屬性聲明一個(gè)變量使之與數(shù)據(jù)庫(kù)某個(gè)列的定義相同或與另一個(gè)已經(jīng)定義過(guò)的變量相同 所以%TYPE要作為列名的后

51、綴：如：v_last_name s_emp.last_name%TYPE;v_first_name s_emp.first_name%TYPE; -這樣做的好處是我們不必去知曉此列的類型與定義 或：v_balance NUMBER(7,2);v_minimum_balance v_balance%TYPE := 10;四、聲明一個(gè)布爾類型的變量1 只有TRUE、FALSE、NULL可以賦值給BOOLEAN變量2 此變量可以接邏輯運(yùn)算符NOT、AND、OR。3、變量只能產(chǎn)生TRUE、FALSE、NULL。實(shí)例：VSAL1：=50000；VSQL2：=60000；VCOMMSAL BOOLEAN：

52、=(VSAL1<VSQL2)；-其實(shí)是把TRUE賦值給此變量。五、LOB 類型的變量共有CLOB、BLOB、BFILE、NCLOB幾種，這里不做為重點(diǎn)。六：使用HOST VARIABLESSQL> variable n numberSQL> print n：n=v_sal /12;:n這個(gè)加了：前綴的變量不是PLSQL變量，而是HOST。七、以下幾個(gè)PLSQL聲明變量，哪個(gè)不合法？A 、DECLAREV_ID NUMBER(4)；B、DECLAREV_X，V_Y，V_Z VARCHAR2(9)；C、DECLAREV_BIRTH DATE NOT NULL；D、DECLAREV

53、_IN_STOCK BOOLEAN：=1；E、DECLARETYPE NAME_TAB IS TABLE OF VARCHAR2(20)INDEX BY BINARY_INTEGER；DEPT_NAME NAME_TAB；上面的習(xí)題我會(huì)在下章給出答案，這也正是聲明變量的規(guī)則和難點(diǎn)。課程 十 寫執(zhí)行語(yǔ)句本課重點(diǎn)：1、了解PLSQL執(zhí)行區(qū)間的重要性2、寫執(zhí)行語(yǔ)句3、描述嵌套塊的規(guī)則4、執(zhí)行且測(cè)試PLSQL塊5、使用代碼慣例注意：以下實(shí)例中標(biāo)點(diǎn)均為英文半角一、PLSQL 塊的語(yǔ)法規(guī)則：1、語(yǔ)句可以跨躍幾行。2、詞匯單元可以包括：分隔符、標(biāo)識(shí)符、文字、和注釋內(nèi)容。3、分隔符：+-*/=<>

54、|.4、標(biāo)識(shí)符：最多30個(gè)字符，不能有保留字除非用雙引號(hào)引起。字母開頭，不與列同名。5、文字串：如 V_ENAME:='FANCY'要用單引號(hào)括起來(lái)。數(shù)值型可以用簡(jiǎn)單記數(shù)和科學(xué)記數(shù)法。6、注釋內(nèi)容：?jiǎn)涡袝r(shí)用- 多行用/* */與C很相似二、SQL函數(shù)在PL/SQL的使用：1、可用的：?jiǎn)涡袛?shù)值型、字符型和轉(zhuǎn)換型，日期型。2、不可用的：最大、最小、DECODE、分組函數(shù)。實(shí)例：BEGINSELECT TO_CHAR(HIREDATE,'MON,DD,YYYY') FROM EMP;END;V_comment:=user|':'|sysdate; -

55、會(huì)編譯出錯(cuò)V_comment:=user|':'|to_char(sysdate); -正確如果有可能，PLSQL都會(huì)進(jìn)行數(shù)據(jù)一致性的轉(zhuǎn)換，但ORACLE推薦你應(yīng)該進(jìn)行顯示的轉(zhuǎn)換，因?yàn)檫@樣會(huì)提高性能。三、嵌套塊和變量作用區(qū)域1、執(zhí)行語(yǔ)句允許嵌套時(shí)嵌套。2、嵌套塊可以看作正常的語(yǔ)句塊。3、錯(cuò)誤處理模塊可以包括一個(gè)嵌套塊4、exponential指數(shù) 邏輯、算數(shù)、連接、小括號(hào)5、看正面實(shí)例：declarevjob varchar(9);v_count number:=0;vtotal date:=sysdate +7;c_tax constant number(3,2):=8.25

56、;v_valid boolean not null:=true;ttt vtotal%type;begin-select sysdate into vtotal from dual;-體會(huì)有無(wú)此句與結(jié)果的影響dbms_output.put_line (vtotal);end;/注意：在執(zhí)行塊之前，要在SQL PLUS中執(zhí)行：SET SERVEROUTPUT ON三、以實(shí)例來(lái)說(shuō)明函數(shù)的參數(shù)聲明作用域declarev_weight number(3):=600;v_message varchar2(255):='product10000'begindeclare-sub-blockv_weight number(3):=1;v_message