玩轉oracle實戰(zhàn)教程(韓順平)學習word筆記

1、韓順平玩轉oracle視頻教程筆記一：Oracle認證，與其它數(shù)據(jù)庫比較，安裝oracle的卸載1.停止所有與ORACLE相關的服務。2. 使用OUI（Oracle Universal Installer）卸載Oracle軟件。“開始”->“程序”->“Oracle-OraDb110g_home1|Oracle installation product|Universal installer.3.刪除注冊表內容。運行regedit命令，刪除下面內容：HKEY_LOCAL_MACHINE|SOFTWARE|ORACLE注冊表鍵，刪除此鍵。HKEY_LOCAL_MACHINE|SYST

2、EM|CurrentControlSet|Services，刪除Services鍵下所有以oracle為首的鍵。HKEY_LOCAL_MACHINE|SYSTEM|CurrentControlSet|Services|Eventlog|Application， 刪除此鍵下所有以oracle為首的鍵。HKEY_CLASSES_ROOT，刪除此鍵下所有以Ora，Oracle，Orcl，EnumOra 為前綴的鍵。HKEY_CURRENT_USER|Software| Microsoft|Windows|CurrentVersion|Explorer|MenuOrder|Start Menu|Pro

3、grams， 刪除此鍵下所有以oracle為首的鍵。HKEY_LOCAL_MACHINE|SOFTWARE|ODBC|ODBCINST.INI注冊表鍵，刪除了Microsoft ODBC FOR ORACLE注冊表鍵以外的所有有Oracle字樣的鍵值。HKEY_LOCAL_MACHINE|SYSTEM|CurrentControlSet|Services，刪除以Oracle或OraWeb為前綴的鍵。4. 刪除環(huán)境變量。 刪除環(huán)境變量CLASSPATH，PATH中含有Oracle字樣的值。5.最后在文件系統(tǒng)內刪除ORACLE相關的文件及目錄：刪除系統(tǒng)盤符：Progrm FilesOracle目錄

4、；刪除ORACLE_BASE目錄。我無法刪除D：oracle目錄，重新啟動機器之后才刪除Oracle安裝會自動的生成sys用戶和system用戶: （1） sys用戶是超級用戶，具有最高權限，具有sysdba角色，有create database的權限，該用戶默認的密碼是change_on_install （2） system用戶是管理操作員，權限也很大。具有sysoper角色，沒有create database的權限，默認的密碼是manager （3） 一般講，對數(shù)據(jù)庫維護，使用system用戶登錄就可以拉 也就是說sys和system這兩個用戶最大的區(qū)別是在于有沒有create datab

5、ase的權限。Enterprise manager console(企業(yè)管理器) （10G新應用到瀏覽器中） oracle 10g是通過web管理的 一般默認端口是5500， 也有1158的。 訪問url(請一定保證oracle服務啟動了): http:/ip:1158 (也可能是5500)/em http:/機器名:端口/em二： Oracle的基本使用-基本命令sql*plus的常用命令 連接命令 1. connect 用法：conn 用戶名/密碼網絡服務名as sysdba/sysoper當用特權用戶身份連接時，必須帶上as sysdba或是as sysoper 2.disconnect

6、 說明: 該命令用來斷開與當前數(shù)據(jù)庫的連接 3.psssword 說明: 該命令用于修改用戶的密碼，如果要想修改其它用戶的密碼，需要用sys/system登錄。 4.show user 說明: 顯示當前用戶名 5.exit 說明: 該命令會斷開與數(shù)據(jù)庫的連接，同時會退出sql*plus 6. SHOW PARAMETER NAME;說明：顯示當前數(shù)據(jù)庫實例名（只能用sys和system查）；文件操作命令 1.start和 說明: 運行sql腳本 案例: sql> d:a.sql或是sql>start d:a.sql 2.edit 說明: 該命令可以編輯指定的sql腳本 案例: sq

7、l>edit d:a.sql,這樣會把d:a.sql這個文件打開 3.spool 說明: 該命令可以將sql*plus屏幕上的內容輸出到指定文件中去。 案例: sql>spool d:b.sql 并輸入 sql>spool off 交互式命令 1.& 說明：可以替代變量，而該變量在執(zhí)行時，需要用戶輸入。 select * from emp where job='&job'； 2.edit 說明：該命令可以編輯指定的sql腳本 案例：SQL>edit d:a.sql 3.spool 說明：該命令可以將sql*plus屏幕上的內容輸出到指定文

8、件中去。 spool d:b.sql 并輸入 spool off 顯示和設置環(huán)境變量 概述：可以用來控制輸出的各種格式，set show如果希望永久的保存相關的設置，可以去修改glogin.sql腳本 1.linesize 說明：設置顯示行的寬度，默認是80個字符 show linesize set linesize 90 2.pagesize說明：設置每頁顯示的行數(shù)目，默認是14 用法和linesize一樣 至于其它環(huán)境參數(shù)的使用也是大同小異 三：oracle用戶管理oracle用戶的管理 創(chuàng)建用戶 概述：在oracle中要創(chuàng)建一個新的用戶使用create user語句，一般是具有dba(數(shù)

9、據(jù)庫管理員)的權限才能使用。 create user 用戶名 identified by 密碼; (oracle有個毛病，密碼必須以字母開頭，如果以字母開頭，它不會創(chuàng)建用戶) 例子: sql> create user shunping identified by m123default tablespace userstemporary tablespace tempquota 3m on users;identified by 表明該用戶shunping 將用數(shù)據(jù)庫方式驗證 default tablespace users /用戶的表空間在users上temporary tablesp

10、ace temp /用戶shunping的臨時表健在temp 空間quota 3m on users /表明用戶shunping 建立的數(shù)據(jù)對象(表,索引,視圖,pl/sql塊.)最大只能是3m剛剛創(chuàng)建的用戶是沒有任何權限的,因此,需要dba給該用戶授權.sql>grant connect to shunping 如果你希望該用戶建表沒有空間的限制sql>grand resource to shunping如果你希望該用戶成為dbasql>grant dba to shunping給用戶修改密碼 概述：如果給自己修改密碼可以直接使用 password 用戶名 如果給別人修改密

11、碼則需要具有dba的權限，或是擁有alter user的系統(tǒng)權限 SQL> alter user 用戶名 identified by 新密碼 刪除用戶 概述：一般以dba的身份去刪除某個用戶，如果用其它用戶去刪除用戶則需要具有drop user的權限。 比如 drop user 用戶名 【cascade】 在刪除用戶時，注意： 如果要刪除的用戶，已經創(chuàng)建了表，那么就需要在刪除的時候帶一個參數(shù)cascade; 用戶管理的綜合案例 概述：創(chuàng)建的新用戶是沒有任何權限的，甚至連登陸的數(shù)據(jù)庫的權限都沒有，需要為其指定相應的權限。給一個用戶賦權限使用命令grant，回收權限使用命令revoke。 為

12、了給講清楚用戶的管理，這里我給大家舉一個案例。 SQL> conn xiaoming/m12; ERROR: ORA-01045: user XIAOMING lacks CREATE SESSION privilege; logon denied 警告: 您不再連接到 ORACLE。 SQL> show user; USER 為 "" SQL> conn system/p; 已連接。 SQL> grant connect to xiaoming; 授權成功。 查看CONNECT角色具有的權限；select role, privilege, admi

13、n_option from role_sys_privs where upper(role)='CONNECT'SQL> conn xiaoming/m12; /后面的為密碼分開來輸入。已連接。 SQL> 注意：grant connect to xiaoming;在這里，準確的講，connect不是權限，而是角色（角色是權限的集合）。 看圖： 現(xiàn)在說下對象權限，現(xiàn)在要做這么件事情： * 希望xiaoming用戶可以去查詢emp表 * 希望xiaoming用戶可以去查詢scott的emp表   grant select on emp to xiaoming

14、* 希望xiaoming用戶可以去修改scott的emp表   grant update on emp to xiaoming * 希望xiaoming用戶可以去修改/刪除，查詢，添加scott的emp表   grant all on emp to xiaoming * scott希望收回xiaoming對emp表的查詢權限   revoke select on emp from xiaoming /對權限的維護。 * 希望xiaoming用戶可以去查詢scott的emp表/還希望xiaoming可以把這個權限繼續(xù)給別人。 -如果是對象權限，就加入 with gr

15、ant option   grant select on emp to xiaoming with grant option 我的操作過程： SQL> conn scott/tiger; 已連接。 SQL> grant select on scott.emp to xiaoming with grant option; 授權成功。 SQL> conn system/p; 已連接。 SQL> create user xiaohong identified by m123; 用戶已創(chuàng)建。 SQL> grant connect to xiaohong; 授權成

16、功。 SQL> conn xiaoming/m12; 已連接。 SQL> grant select on scott.emp to xiaohong; 授權成功。 -如果是系統(tǒng)權限。 system給xiaoming權限時： grant connect to xiaoming with admin option 問題：如果scott把xiaoming對emp表的查詢權限回收，那么xiaohong會怎樣？ 答案：被回收。 下面是我的操作過程： SQL> conn scott/tiger; 已連接。 SQL> revoke select on emp from xiaomin

17、g; 撤銷成功。 SQL> conn xiaohong/m123; 已連接。 SQL> select * from scott.emp; select * from scott.emp 第 1 行出現(xiàn)錯誤: ORA-00942: 表或視圖不存在 結果顯示：小紅受到誅連了。使用profile管理用戶口令 概述：profile是口令限制，資源限制的命令集合，當建立數(shù)據(jù)庫的，oracle會自動建立名稱為default的profile。當建立用戶沒有指定profile選項，那么oracle就會將default分配給用戶。 1.賬戶鎖定 概述：指定該賬戶(用戶)登陸時最多可以輸入密碼的次數(shù)，

18、也可以指定用戶鎖定的時間(天)一般用dba的身份去執(zhí)行該命令。 例子：指定scott這個用戶最多只能嘗試3次登陸，鎖定時間為2天，讓我們看看怎么實現(xiàn)。 創(chuàng)建profile文件 SQL> create profile lock_account limit failed_login_attempts 3 password_lock_time 2; SQL> alter user scott profile lock_account; 2.給賬戶(用戶)解鎖 SQL> alter user tea account unlock; 3.終止口令 為了讓用戶定期修改密碼可以使用終止口令

19、的指令來完成，同樣這個命令也需要dba的身份來操作。 例子：給前面創(chuàng)建的用戶tea創(chuàng)建一個profile文件，要求該用戶每隔10天要修改自己的登陸密碼，寬限期為2天?？纯丛趺醋?。 SQL> create profile myprofile limit password_life_time 10 password_grace_time 2; SQL> alter user tea profile myprofile; 口令歷史 概述：如果希望用戶在修改密碼時，不能使用以前使用過的密碼，可使用口令歷史，這樣oracle就會將口令修改的信息存放到數(shù)據(jù)字典中，這樣當用戶修改密碼時，orac

20、le就會對新舊密碼進行比較，當發(fā)現(xiàn)新舊密碼一樣時，就提示用戶重新輸入密碼。 例子： 1）建立profile SQL>create profile password_history limit password_life_time 10 password_grace_time 2 password_reuse_time 10 password_reuse_time /指定口令可重用時間即10天后就可以重用 2）分配給某個用戶 刪除profile 概述：當不需要某個profile文件時，可以刪除該文件。 SQL> drop profile password_history 【casad

21、e】 注意：文件刪除后，用這個文件去約束的那些用戶通通也都被釋放了。若創(chuàng)建的PROFILE已經授權給了某個用戶，使用CASCADE級聯(lián)收回相應的限制，收回限制信息后將以系統(tǒng)默認的PROFILE對該用戶進行限制。加了casade，就會把級聯(lián)的相關東西也給刪除掉四：oracle表的管理(數(shù)據(jù)類型，表創(chuàng)建刪除，數(shù)據(jù)CRUD操作)oracle的表的管理 表名和列的命名規(guī)則 · 必須以字母開頭 · 長度不能超過30個字符 · 不能使用oracle的保留字 · 只能使用如下字符 A-Z，a-z，0-9，$,#等 oracle支持的數(shù)據(jù)類型 字符類 char 

22、;   定長 最大2000個字符。 例子：char(10)  小韓前四個字符放小韓，后添6個空格補全 如小韓 varchar2(20)  變長  最大4000個字符。 例子：varchar2（10） 小韓 oracle分配四個字符。這樣可以節(jié)省空間。 clob(character large object) 字符型大對象 最大4G char 查詢的速度極快浪費空間，查詢比較多的數(shù)據(jù)用。 varchar 節(jié)省空間 數(shù)字型number范圍 -10的38次方 到 10的38次方 可以表示整數(shù)，也可以表示小數(shù) number(5,2) 表示一位小數(shù)有5位

23、有效數(shù)，2位小數(shù) 范圍：-999.99到999.99 number(5) 表示一個5位整數(shù) 范圍99999到-99999 日期類型 date 包含年月日和時分秒   oracle默認格式  1-1月-1999 timestamp 這是oracle9i對date數(shù)據(jù)類型的擴展?？梢跃_到毫秒。 圖片blob 二進制數(shù)據(jù) 可以存放圖片/聲音  4G   一般來講，在真實項目中是不會把圖片和聲音真的往數(shù)據(jù)庫里存放，一般存放圖片、視頻的路徑，如果安全需要比較高的話，則放入數(shù)據(jù)庫。 怎樣創(chuàng)建表 建表-學生表 create table stude

24、nt (    -表名           xh       number(4),   -學號           xm    varchar2(20),   -姓名        

25、0;  sex      char(2),     -性別           birthday , date       -出生日期           sal      number(7,2) &

26、#160; -獎學金 ); -班級表 CREATE TABLE class(     classId NUMBER(2),     cName VARCHAR2(40); 修改表 添加一個字段SQL>ALTER TABLE student add (classId NUMBER(2); 修改一個字段的長度 SQL>ALTER TABLE student MODIFY (xm VARCHAR2(30); 修改字段的類型/或是名字（不能有數(shù)據(jù)） 不建議做 SQL>ALTER TABLE student modify (x

27、m CHAR(30); 刪除一個字段  不建議做(刪了之后，順序就變了。加就沒問題，應為是加在后面)SQL>ALTER TABLE student DROP COLUMN sal; 修改表的名字   很少有這種需求 SQL>RENAME student TO stu; 刪除表 SQL>DROP TABLE student; 添加數(shù)據(jù) 所有字段都插入數(shù)據(jù) INSERT INTO student VALUES ('A001', '張三', '男', '01-5月-05', 10); or

28、acle中默認的日期格式dd-mon-yy dd日子（天） mon 月份  yy  2位的年  09-6月-99 1999年6月9日 修改日期的默認格式（臨時修改，數(shù)據(jù)庫重啟后仍為默認；如要修改需要修改注冊表） ALTER SESSION SET NLS_DATE_FORMAT ='yyyy-mm-dd' 修改后，可以用我們熟悉的格式添加日期類型： INSERT INTO student VALUES ('A002', 'MIKE', '男', '1905-05-06', 10); 插

29、入部分字段 INSERT INTO student(xh, xm, sex) VALUES ('A003', 'JOHN', '女'); 插入空值 INSERT INTO student(xh, xm, sex, birthday) VALUES ('A004', 'MARTIN', '男', null); 問題來了，如果你要查詢student表里birthday為null的記錄，怎么寫sql呢？ 錯誤寫法：select * from student where birthday = null; 正

30、確寫法：select * from student where birthday is null; 如果要查詢birthday不為null,則應該這樣寫： select * from student where birthday is not null; 修改數(shù)據(jù) 修改一個字段UPDATE student SET sex = '女' WHERE xh = 'A001' 修改多個字段UPDATE student SET sex = '男', birthday = '1984-04-01' WHERE xh = 'A001&#

31、39; 修改含有null值的數(shù)據(jù) 不要用 = null 而是用 is null； SELECT * FROM student WHERE birthday IS null; 刪除數(shù)據(jù)DELETE FROM student; 刪除所有記錄，表結構還在，寫日志，可以恢復的，速度慢。 Delete 的數(shù)據(jù)可以恢復。 savepoint a; -創(chuàng)建保存點 DELETE FROM student; rollback to a;  -恢復到保存點 一個有經驗的DBA，在確保完成無誤的情況下要定期創(chuàng)建還原點。 DROP TABLE student; -刪除表的結構和數(shù)據(jù)； delete from

32、 student WHERE xh = 'A001' -刪除一條記錄； truncate TABLE student; -刪除表中的所有記錄，表結構還在，不寫日志，無法找回刪除的記錄，速度快。五：oracle表查詢(1)oracle表基本查詢 介紹在我們講解的過程中我們利用scott用戶存在的幾張表（emp，dept）為大家演示如何使用select語句，select語句在軟件編程中非常有用，希望大家好好的掌握。 emp 雇員表 clerk  普員工 salesman 銷售 manager  經理 analyst 分析師 president  總裁

33、mgr  上級的編號 hiredate 入職時間 sal 月工資 comm 獎金 deptno 部門 dept部門表 deptno 部門編號 accounting 財務部 research  研發(fā)部 operations 業(yè)務部 loc 部門所在地點 salgrade   工資級別 grade    級別 losal    最低工資 hisal    最高工資 簡單的查詢語句 查看表結構DESC emp; 查詢所有列SELECT * FROM dept; 切忌動不動

34、就用select * SET TIMING ON; 打開顯示操作時間的開關，在下面顯示查詢時間。 CREATE TABLE users(userId VARCHAR2(10), uName VARCHAR2 (20), uPassw VARCHAR2(30); INSERT INTO users VALUES('a0001', '啊啊啊啊', 'aaaaaaaaaaaaaaaaaaaaaaa'); -從自己復制，加大數(shù)據(jù)量 大概幾萬行就可以了  可以用來測試sql語句執(zhí)行效率 INSERT INTO users (userId,UNAM

35、E,UPASSW) SELECT * FROM users; SELECT COUNT (*) FROM users;統(tǒng)計行數(shù) 查詢指定列SELECT ename, sal, job, deptno FROM emp; 如何取消重復行DISTINCT SELECT DISTINCT deptno, job FROM emp; 查詢SMITH所在部門，工作，薪水 SELECT deptno,job,sal FROM emp WHERE ename = 'SMITH' 注意：oracle對內容的大小寫是區(qū)分的，所以ename='SMITH'和ename='s

36、mith'是不同的 使用算術表達式 nvl  null 問題：如何顯示每個雇員的年工資？ SELECT sal*13+nvl(comm, 0)*13 "年薪" , ename, comm FROM emp; 使用列的別名SELECT ename "姓名", sal*12 AS "年收入" FROM emp; 如何處理null值使用nvl函數(shù)來處理 如何連接字符串（|）SELECT ename  | ' is a ' | job FROM emp; 使用where子句問題：如何顯示工

37、資高于3000的 員工？ SELECT * FROM emp WHERE sal > 3000; 問題：如何查找1982.1.1后入職的員工？ SELECT ename,hiredate FROM emp WHERE hiredate >'1-1月-1982' 問題：如何顯示工資在2000到3000的員工？ SELECT ename,sal FROM emp WHERE sal >=2000 AND sal <= 3000; 如何使用like操作符 %：表示0到多個字符  _：表示任意單個字符 問題：如何顯示首字符為S的員工姓名和工資？ SEL

38、ECT ename,sal FROM emp WHERE ename like 'S%' 如何顯示第三個字符為大寫O的所有員工的姓名和工資？ SELECT ename,sal FROM emp WHERE ename like '_O%' 在where條件中使用in 問題：如何顯示empno為7844, 7839,123,456 的雇員情況？ SELECT * FROM emp WHERE empno in (7844, 7839,123,456); 使用is null的操作符 問題：如何顯示沒有上級的雇員的情況？ 錯誤寫法：select * from emp

39、where mgr = '' 正確寫法：SELECT * FROM emp WHERE mgr is null;六：oracle表查詢(2)使用邏輯操作符號 問題：查詢工資高于500或者是崗位為MANAGER的雇員，同時還要滿足他們的姓名首字母為大寫的J？ SELECT * FROM emp WHERE (sal >500 or job = 'MANAGER') and ename LIKE 'J%' 使用order by 字句   默認asc 問題：如何按照工資的從低到高的順序顯示雇員的信息？ SELECT * FRO

40、M emp ORDER by sal; 問題：按照部門號升序而雇員的工資降序排列 SELECT * FROM emp ORDER by deptno, sal DESC; 使用列的別名排序問題：按年薪排序 select ename, (sal+nvl(comm,0)*12 "年薪" from emp order by "年薪" asc; 別名需要使用“”號圈中,英文不需要“”號 分頁查詢 等學了子查詢再說吧。 Clear 清屏命令 oracle表復雜查詢 說明在實際應用中經常需要執(zhí)行復雜的數(shù)據(jù)統(tǒng)計，經常需要顯示多張表的數(shù)據(jù)，現(xiàn)在我們給大家介紹較為復雜的s

41、elect語句 數(shù)據(jù)分組 max，min， avg， sum， count 問題：如何顯示所有員工中最高工資和最低工資？ SELECT MAX(sal),min(sal) FROM emp e;       最高工資那個人是誰？ 錯誤寫法：select ename, sal from emp where sal=max(sal); 正確寫法：select ename, sal from emp where sal=(select max(sal) from emp); 注意：select ename, max(sal) from emp;這語句

42、執(zhí)行的時候會報錯，說ORA-00937：非單組分組函數(shù)。因為max是分組函數(shù)，而ename不是分組函數(shù). 但是select min(sal), max(sal) from emp;這句是可以執(zhí)行的。因為min和max都是分組函數(shù)，就是說：如果列里面有一個分組函數(shù)，其它的都必須是分組函數(shù)，否則就出錯。這是語法規(guī)定的問題：如何顯示所有員工的平均工資和工資總和？ 問題：如何計算總共有多少員工問題：如何擴展要求： 查詢最高工資員工的名字，工作崗位 SELECT ename, job, sal FROM emp e where sal = (SELECT MAX(sal) FROM emp); 顯示工資

43、高于平均工資的員工信息 SELECT * FROM emp e where sal > (SELECT AVG(sal) FROM emp); group by 和 having子句 group by用于對查詢的結果分組統(tǒng)計， having子句用于限制分組顯示結果。 問題：如何顯示每個部門的平均工資和最高工資？ SELECT AVG(sal), MAX(sal), deptno FROM emp GROUP by deptno; （注意：這里暗藏了一點，如果你要分組查詢的話，分組的字段deptno一定要出現(xiàn)在查詢的列表里面，否則會報錯。因為分組的字段都不出現(xiàn)的話，就沒辦法分組了） 問題：

44、顯示每個部門的每種崗位的平均工資和最低工資？ SELECT min(sal), AVG(sal), deptno, job FROM emp GROUP by deptno, job; 問題：顯示平均工資低于2000的部門號和它的平均工資？ SELECT AVG(sal), MAX(sal), deptno FROM emp GROUP by deptno having AVG(sal) < 2000; 對數(shù)據(jù)分組的總結 1 分組函數(shù)只能出現(xiàn)在選擇列表、having、order by子句中(不能出現(xiàn)在where中) 2 如果在select語句中同時包含有group by, having,

45、 order by 那么它們的順序是group by, having, order by 3 在選擇列中如果有列、表達式和分組函數(shù)，那么這些列和表達式必須有一個出現(xiàn)在group by 子句中，否則就會出錯。 如SELECT deptno, AVG(sal), MAX(sal) FROM emp GROUP by deptno HAVING AVG(sal) < 2000; 這里deptno就一定要出現(xiàn)在group by 中 多表查詢 說明多表查詢是指基于兩個和兩個以上的表或是視圖的查詢。在實際應用中，查詢單個表可能不能滿足你的需求，（如顯示sales部門位置和其員工的姓名），這種情況下需

46、要使用到（dept表和emp表） 問題：顯示雇員名，雇員工資及所在部門的名字【笛卡爾集】？ 規(guī)定：多表查詢的條件是 至少不能少于 表的個數(shù)-1 才能排除笛卡爾集 （如果有N張表聯(lián)合查詢，必須得有N-1個條件，才能避免笛卡爾集合） SELECT e.ename, e.sal, d.dname FROM emp e, dept d WHERE e.deptno = d.deptno; 問題：顯示部門號為10的部門名、員工名和工資？ SELECT d.dname, e.ename, e.sal FROM emp e, dept d WHERE e.deptno = d.deptno and e.de

47、ptno = 10; 問題：顯示各個員工的姓名，工資及工資的級別？ 先看salgrade的表結構和記錄 SQL>select * from salgrade;     GRADE         LOSAL          HISAL -   -   -         1

48、0;         700           1200         2          1201          1400    

49、;     3          1401          2000         4          2001        

50、60; 3000         5          3001          9999 SELECT e.ename, e.sal, s.grade FROM emp e, salgrade s WHERE e.sal BETWEEN s.losal AND s.hisal; 擴展要求： 問題：顯示雇員名，雇員工資及所在部門的名字，并按部門排

51、序？ SELECT e.ename, e.sal, d.dname FROM emp e, dept d WHERE e.deptno = d.deptno ORDER by e.deptno; （注意：如果用group by，一定要把e.deptno放到查詢列里面） 自連接自連接是指在同一張表的連接查詢 問題：顯示某個員工的上級領導的姓名？ 比如顯示員工FORD的上級 SELECT worker.ename, boss.ename FROM emp worker,emp boss WHERE worker.mgr = boss.empno AND worker.ename = 'FO

52、RD' 子查詢 什么是子查詢子查詢是指嵌入在其他sql語句中的select語句，也叫嵌套查詢。 單行子查詢 單行子查詢是指只返回一行數(shù)據(jù)的子查詢語句 請思考：顯示與SMITH同部門的所有員工？ 思路：1 查詢出SMITH的部門號 select deptno from emp WHERE ename = 'SMITH' 2 顯示 SELECT * FROM emp WHERE deptno = (select deptno from emp WHERE ename = 'SMITH'); 數(shù)據(jù)庫在執(zhí)行sql 是從左到右掃描的， 如果有括號的話，括號里面的先

53、被優(yōu)先執(zhí)行。 多行子查詢多行子查詢指返回多行數(shù)據(jù)的子查詢 請思考：如何查詢和部門10的工作相同的雇員的名字、崗位、工資、部門號 SELECT DISTINCT job FROM emp WHERE deptno = 10; SELECT * FROM emp WHERE job IN (SELECT DISTINCT job FROM emp WHERE deptno = 10); （注意：不能用job=.，因為等號=是一對一的） 在多行子查詢中使用all操作符問題：如何顯示工資比部門30的所有員工的工資高的員工的姓名、工資和部門號？ SELECT ename, sal, deptno FRO

54、M emp WHERE sal > all (SELECT sal FROM emp WHERE deptno = 30); 擴展要求： 大家想想還有沒有別的查詢方法。 SELECT ename, sal, deptno FROM emp WHERE sal > (SELECT MAX(sal) FROM emp WHERE deptno = 30); 執(zhí)行效率上， 函數(shù)高得多 在多行子查詢中使用any操作符問題：如何顯示工資比部門30的任意一個員工的工資高的員工姓名、工資和部門號？ SELECT ename, sal, deptno FROM emp WHERE sal >

55、 ANY (SELECT sal FROM emp WHERE deptno = 30); 擴展要求： 大家想想還有沒有別的查詢方法。 SELECT ename, sal, deptno FROM emp WHERE sal > (SELECT min(sal) FROM emp WHERE deptno = 30); 多列子查詢單行子查詢是指子查詢只返回單列、單行數(shù)據(jù)，多行子查詢是指返回單列多行數(shù)據(jù)，都是針對單列而言的，而多列子查詢是指查詢返回多個列數(shù)據(jù)的子查詢語句。 請思考如何查詢與SMITH的部門和崗位完全相同的所有雇員。 SELECT deptno, job FROM emp W

56、HERE ename = 'SMITH' SELECT * FROM emp WHERE (deptno, job) = (SELECT deptno, job FROM emp WHERE ename = 'SMITH'); 在from子句中使用子查詢請思考：如何顯示高于自己部門平均工資的員工的信息 思路： 1. 查出各個部門的平均工資和部門號 SELECT deptno, AVG(sal) mysal FROM emp GROUP by deptno; 2. 把上面的查詢結果看做是一張子表 SELECT e.ename, e.deptno, e.sal, d

57、s.mysal FROM emp e, (SELECT deptno, AVG(sal) mysal FROM emp GROUP by deptno) ds WHERE e.deptno = ds.deptno AND e.sal > ds.mysal; 如何衡量一個程序員的水平？ 網絡處理能力， 數(shù)據(jù)庫， 程序代碼的優(yōu)化程序的效率要很高 小總結： 在這里需要說明的當在from子句中使用子查詢時，該子查詢會被作為一個視圖來對待，因此叫做內嵌視圖，當在from子句中使用子查詢時，必須給子查詢指定別名。 注意：別名不能用as，如：SELECT e.ename, e.deptno, e.sa

58、l, ds.mysal FROM emp e, (SELECT deptno, AVG(sal) mysal FROM emp GROUP by deptno) as ds WHERE e.deptno = ds.deptno AND e.sal > ds.mysal; 在ds前不能加as，否則會報錯  （給表取別名的時候，不能加as；但是給列取別名，是可以加as的） 分頁查詢按雇員的id號升序取出 oracle的分頁一共有三種方式 1.根據(jù)rowid來分   select * from t_xiaoxi where rowid in (select rid from

59、 (select rownum rn, rid from(select rowid rid, cid from t_xiaoxi order by cid desc) where rownum<10000) where rn>9980) order by cid desc; 執(zhí)行時間0.03秒 2.按分析函數(shù)來分   select * from (select t.*, row_number() over(order by cid desc) rk from t_xiaoxi t) where rk<10000 and rk>9980; 執(zhí)行時間1.01秒 3

60、.按rownum來分   select * from (select t.*,rownum rn from(select * from t_xiaoxi order by cid desc)t where rownum<10000) where rn>9980; 執(zhí)行時間0.1秒 其中t_xiaoxi為表名稱，cid為表的關鍵字段，取按cid降序排序后的第9981-9999條記錄，t_xiaoxi表有70000多條記錄。 個人感覺1的效率最好，3次之，2最差。 /測試通過的分頁查詢okokok select * from (select a1.*, rownum rn f

61、rom(select ename,job from emp) a1 where rownum<=10)where rn>=5; 下面最主要介紹第三種：按rownum來分 1. rownum 分頁    SELECT * FROM emp;     2. 顯示rownumoracle分配的 SELECT e.*, ROWNUM rn FROM (SELECT * FROM emp) e;  rn相當于Oracle分配的行的ID號 3.挑選出610條記錄 先查出1-10條記錄 SELECT e.*, ROWNUM r

62、n FROM (SELECT * FROM emp) e WHERE ROWNUM <= 10; 如果后面加上rownum>=6是不行的， 4. 然后查出6-10條記錄 SELECT * FROM (SELECT e.*, ROWNUM rn FROM (SELECT * FROM emp) e WHERE ROWNUM <= 10) WHERE rn >= 6; 5. 幾個查詢變化 a. 指定查詢列，只需要修改最里層的子查詢 只查詢雇員的編號和工資 SELECT * FROM (SELECT e.*, ROWNUM rn FROM (SELECT ename, sal

63、 FROM emp) e WHERE ROWNUM <= 10) WHERE rn >= 6; b. 排序查詢，只需要修改最里層的子查詢 工資排序后查詢6-10條數(shù)據(jù) SELECT * FROM (SELECT e.*, ROWNUM rn FROM (SELECT ename, sal FROM emp ORDER by sal) e WHERE ROWNUM <= 10) WHERE rn >= 6; 用查詢結果創(chuàng)建新表這個命令是一種快捷的建表方式 CREATE TABLE mytable (id, name, sal, job, deptno) as SELECT

64、 empno, ename, sal, job, deptno FROM emp; 創(chuàng)建好之后，desc mytable；和select * from mytable;看看結果如何？ 合并查詢 合并查詢有時在實際應用中，為了合并多個select語句的結果，可以使用集合操作符號union，union all，intersect，minus 多用于數(shù)據(jù)量比較大的數(shù)據(jù)局庫，運行速度快。 1). union 該操作符用于取得兩個結果集的并集。當使用該操作符時，會自動去掉結果集中重復行。 SELECT ename, sal, job FROM emp WHERE sal >2500 UNION SELECT ename, sal, job FROM emp WHERE job = 'MANAGER' 2).union all 該操作符與union相似，但是它不會取消重復行，而且不會排序。 SELECT ename, sal, job FROM emp WHERE sal >2500 UNION ALL SELECT ename, sal, job FROM emp WHERE job = 'MANAGER' 該操作符用于取得兩個結果集的并集。當使用該操作符時，會自動去