oracle教程從入門到精通

1、韓順平玩轉(zhuǎn)oracle視頻教程筆記一：Oracle認(rèn)證，與其它數(shù)據(jù)庫比較，安裝Oracle安裝會自動的生成sys用戶和system用戶: （1） sys用戶是超級用戶，具有最高權(quán)限，具有sysdba角色，有create database的權(quán)限，該用戶默認(rèn)的密碼是change_on_install （2） system用戶是管理操作員，權(quán)限也很大。具有sysoper角色，沒有create database的權(quán)限，默認(rèn)的密碼是manager （3） 一般講，對數(shù)據(jù)庫維護(hù)，使用system用戶登錄就可以拉 也就是說sys和system這兩個(gè)用戶最大的區(qū)別是在于有沒有create database的權(quán)

2、限。二： Oracle的基本使用-基本命令sql*plus的常用命令 連接命令 1.connect 用法：conn 用戶名/密碼網(wǎng)絡(luò)服務(wù)名as sysdba/sysoper當(dāng)用特權(quán)用戶身份連接時(shí)，必須帶上as sysdba或是as sysoper 2.disconnect 說明: 該命令用來斷開與當(dāng)前數(shù)據(jù)庫的連接 3.psssword 說明: 該命令用于修改用戶的密碼，如果要想修改其它用戶的密碼，需要用sys/system登錄。 4.show user 說明: 顯示當(dāng)前用戶名 5.exit 說明: 該命令會斷開與數(shù)據(jù)庫的連接，同時(shí)會退出sql*plus 文件操作命令 1.start和 說明:

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

4、dit 說明：該命令可以編輯指定的sql腳本 案例：SQL>edit d:a.sql 3.spool 說明：該命令可以將sql*plus屏幕上的內(nèi)容輸出到指定文件中去。 spool d:b.sql 并輸入 spool off 顯示和設(shè)置環(huán)境變量 概述：可以用來控制輸出的各種格式，set show如果希望永久的保存相關(guān)的設(shè)置，可以去修改glogin.sql腳本 1.linesize 說明：設(shè)置顯示行的寬度，默認(rèn)是80個(gè)字符 show linesize set linesize 90 2.pagesize說明：設(shè)置每頁顯示的行數(shù)目，默認(rèn)是14 用法和linesize一樣 至于其它環(huán)境參數(shù)的使

5、用也是大同小異 三：oracle用戶管理oracle用戶的管理 創(chuàng)建用戶 概述：在oracle中要?jiǎng)?chuàng)建一個(gè)新的用戶使用create user語句，一般是具有dba(數(shù)據(jù)庫管理員)的權(quán)限才能使用。 create user 用戶名 identified by 密碼; (oracle有個(gè)毛病，密碼必須以字母開頭，如果以字母開頭，它不會創(chuàng)建用戶) 給用戶修改密碼 概述：如果給自己修改密碼可以直接使用 password 用戶名 如果給別人修改密碼則需要具有dba的權(quán)限，或是擁有alter user的系統(tǒng)權(quán)限 SQL> alter user 用戶名 identified by 新密碼 刪除用戶 概述

6、：一般以dba的身份去刪除某個(gè)用戶，如果用其它用戶去刪除用戶則需要具有drop user的權(quán)限。 比如 drop user 用戶名 【cascade】 在刪除用戶時(shí)，注意： 如果要?jiǎng)h除的用戶，已經(jīng)創(chuàng)建了表，那么就需要在刪除的時(shí)候帶一個(gè)參數(shù)cascade; 用戶管理的綜合案例 概述：創(chuàng)建的新用戶是沒有任何權(quán)限的，甚至連登陸的數(shù)據(jù)庫的權(quán)限都沒有，需要為其指定相應(yīng)的權(quán)限。給一個(gè)用戶賦權(quán)限使用命令grant，回收權(quán)限使用命令revoke。 為了給講清楚用戶的管理，這里我給大家舉一個(gè)案例。 SQL> conn xiaoming/m12; ERROR: ORA-01045: user XIAOMIN

7、G lacks CREATE SESSION privilege; logon denied 警告: 您不再連接到 ORACLE。 SQL> show user; USER 為 "" SQL> conn system/p; 已連接。 SQL> grant connect to xiaoming; 授權(quán)成功。 SQL> conn xiaoming/m12; /后面的為密碼分開來輸入。已連接。 SQL> 注意：grant connect to xiaoming;在這里，準(zhǔn)確的講，connect不是權(quán)限，而是角色。 看圖： 現(xiàn)在說下對象權(quán)限，現(xiàn)在要做

8、這么件事情： * 希望xiaoming用戶可以去查詢emp表 * 希望xiaoming用戶可以去查詢scott的emp表   grant select on emp to xiaoming * 希望xiaoming用戶可以去修改scott的emp表   grant update on emp to xiaoming * 希望xiaoming用戶可以去修改/刪除，查詢，添加scott的emp表   grant all on emp to xiaoming * scott希望收回xiaoming對emp表的查詢權(quán)限   revoke select on em

9、p from xiaoming /對權(quán)限的維護(hù)。 * 希望xiaoming用戶可以去查詢scott的emp表/還希望xiaoming可以把這個(gè)權(quán)限繼續(xù)給別人。 -如果是對象權(quán)限，就加入 with grant 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; 授權(quán)成功。 SQL> conn system/p;

10、 已連接。 SQL> create user xiaohong identified by m123; 用戶已創(chuàng)建。 SQL> grant connect to xiaohong; 授權(quán)成功。 SQL> conn xiaoming/m12; 已連接。 SQL> grant select on scott.emp to xiaohong; 授權(quán)成功。 -如果是系統(tǒng)權(quán)限。 system給xiaoming權(quán)限時(shí)： grant connect to xiaoming with admin option 問題：如果scott把xiaoming對emp表的查詢權(quán)限回收，那么xiao

11、hong會怎樣？ 答案：被回收。 下面是我的操作過程： SQL> conn scott/tiger; 已連接。 SQL> revoke select on emp from xiaoming; 撤銷成功。 SQL> conn xiaohong/m123; 已連接。 SQL> select * from scott.emp; select * from scott.emp 第 1 行出現(xiàn)錯(cuò)誤: ORA-00942: 表或視圖不存在 結(jié)果顯示：小紅受到誅連了。使用profile管理用戶口令 概述：profile是口令限制，資源限制的命令集合，當(dāng)建立數(shù)據(jù)庫的，oracle會自

12、動建立名稱為default的profile。當(dāng)建立用戶沒有指定profile選項(xiàng)，那么oracle就會將default分配給用戶。 1.賬戶鎖定 概述：指定該賬戶(用戶)登陸時(shí)最多可以輸入密碼的次數(shù)，也可以指定用戶鎖定的時(shí)間(天)一般用dba的身份去執(zhí)行該命令。 例子：指定scott這個(gè)用戶最多只能嘗試3次登陸，鎖定時(shí)間為2天，讓我們看看怎么實(shí)現(xiàn)。 創(chuàng)建profile文件 SQL> create profile lock_account limit failed_login_attempts 3 password_lock_time 2; SQL> alter user scott

13、 profile lock_account; 2.給賬戶(用戶)解鎖 SQL> alter user tea account unlock; 3.終止口令 為了讓用戶定期修改密碼可以使用終止口令的指令來完成，同樣這個(gè)命令也需要dba的身份來操作。 例子：給前面創(chuàng)建的用戶tea創(chuàng)建一個(gè)profile文件，要求該用戶每隔10天要修改自己的登陸密碼，寬限期為2天?？纯丛趺醋?。 SQL> create profile myprofile limit password_life_time 10 password_grace_time 2; SQL> alter user tea pro

14、file myprofile; 口令歷史 概述：如果希望用戶在修改密碼時(shí)，不能使用以前使用過的密碼，可使用口令歷史，這樣oracle就會將口令修改的信息存放到數(shù)據(jù)字典中，這樣當(dāng)用戶修改密碼時(shí)，oracle就會對新舊密碼進(jìn)行比較，當(dāng)發(fā)現(xiàn)新舊密碼一樣時(shí)，就提示用戶重新輸入密碼。 例子： 1）建立profile SQL>create profile password_history limit password_life_time 10 password_grace_time 2 password_reuse_time 10 password_reuse_time /指定口令可重用時(shí)間即10天

15、后就可以重用 2）分配給某個(gè)用戶 刪除profile 概述：當(dāng)不需要某個(gè)profile文件時(shí)，可以刪除該文件。 SQL> drop profile password_history 【casade】 注意：文件刪除后，用這個(gè)文件去約束的那些用戶通通也都被釋放了。加了casade，就會把級聯(lián)的相關(guān)東西也給刪除掉四：oracle表的管理(數(shù)據(jù)類型，表創(chuàng)建刪除，數(shù)據(jù)CRUD操作)oracle的表的管理 表名和列的命名規(guī)則 · 必須以字母開頭 · 長度不能超過30個(gè)字符 · 不能使用oracle的保留字 · 只能使用如下字符 A-Z，a-z，0-9，$,#

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

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

18、創(chuàng)建表 建表-學(xué)生表 create table student (    -表名           xh       number(4),   -學(xué)號           xm    varchar2(20),   -姓名    

19、;       sex      char(2),     -性別           birthday date,         -出生日期           sal 

20、60;    number(7,2)   -獎(jiǎng)學(xué)金 ); -班級表 CREATE TABLE class(     classId NUMBER(2),     cName VARCHAR2(40); 修改表 添加一個(gè)字段SQL>ALTER TABLE student add (classId NUMBER(2); 修改一個(gè)字段的長度 SQL>ALTER TABLE student MODIFY (xm VARCHAR2(30); 修改字段的類型/或是名字（不能有數(shù)據(jù)） 不

21、建議做 SQL>ALTER TABLE student modify (xm CHAR(30); 刪除一個(gè)字段  不建議做(刪了之后，順序就變了。加就沒問題，應(yīng)為是加在后面)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', '張三',

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

23、#39;男', '1905-05-06', 10); 插入部分字段 INSERT INTO student(xh, xm, sex) VALUES ('A003', 'JOHN', '女'); 插入空值 INSERT INTO student(xh, xm, sex, birthday) VALUES ('A004', 'MARTIN', '男', null); 問題來了，如果你要查詢student表里birthday為null的記錄，怎么寫sql呢？ 錯(cuò)誤寫法：select

24、 * from student where birthday = null; 正確寫法：select * from student where birthday is null; 如果要查詢birthday不為null,則應(yīng)該這樣寫： select * from student where birthday is not null; 修改數(shù)據(jù) 修改一個(gè)字段UPDATE student SET sex = '女' WHERE xh = 'A001' 修改多個(gè)字段UPDATE student SET sex = '男', birthday = 

25、9;1984-04-01' WHERE xh = 'A001' 修改含有null值的數(shù)據(jù) 不要用 = null 而是用 is null； SELECT * FROM student WHERE birthday IS null; 刪除數(shù)據(jù)DELETE FROM student; 刪除所有記錄，表結(jié)構(gòu)還在，寫日志，可以恢復(fù)的，速度慢。 Delete 的數(shù)據(jù)可以恢復(fù)。 savepoint a; -創(chuàng)建保存點(diǎn) DELETE FROM student; rollback to a;  -恢復(fù)到保存點(diǎn) 一個(gè)有經(jīng)驗(yàn)的DBA，在確保完成無誤的情況下要定期創(chuàng)建還原點(diǎn)。 DRO

26、P TABLE student; -刪除表的結(jié)構(gòu)和數(shù)據(jù)； delete from student WHERE xh = 'A001' -刪除一條記錄； truncate TABLE student; -刪除表中的所有記錄，表結(jié)構(gòu)還在，不寫日志，無法找回刪除的記錄，速度快。五：oracle表查詢(1)oracle表基本查詢 介紹在我們講解的過程中我們利用scott用戶存在的幾張表（emp，dept）為大家演示如何使用select語句，select語句在軟件編程中非常有用，希望大家好好的掌握。 emp 雇員表 clerk  普員工 salesman 銷售 manager&

27、#160; 經(jīng)理 analyst 分析師 president  總裁 mgr  上級的編號 hiredate 入職時(shí)間 sal 月工資 comm 獎(jiǎng)金 deptno 部門 dept部門表 deptno 部門編號 accounting 財(cái)務(wù)部 research  研發(fā)部 operations 業(yè)務(wù)部 loc 部門所在地點(diǎn) salgrade   工資級別 grade    級別 losal    最低工資 hisal    最高工資 簡單的查詢語句 查看表結(jié)構(gòu)

28、DESC emp; 查詢所有列SELECT * FROM dept; 切忌動不動就用select * SET TIMING ON; 打開顯示操作時(shí)間的開關(guān)，在下面顯示查詢操作花費(fèi)的時(shí)間。 CREATE TABLE users(userId VARCHAR2(10), uName VARCHAR2 (20), uPassw VARCHAR2(30); INSERT INTO users VALUES('a0001', '啊啊啊啊', 'aaaaaaaaaaaaaaaaaaaaaaa'); -從自己復(fù)制，加大數(shù)據(jù)量 大概幾萬行就可以了  可

29、以用來測試sql語句執(zhí)行效率 INSERT INTO users (userId,UNAME,UPASSW) SELECT * FROM users; SELECT COUNT (*) FROM users;統(tǒng)計(jì)行數(shù) 查詢指定列SELECT ename, sal, job, deptno FROM emp; 如何取消重復(fù)行DISTINCT SELECT DISTINCT deptno, job FROM emp; 查詢SMITH所在部門，工作，薪水 SELECT deptno,job,sal FROM emp WHERE ename = 'SMITH' 注意：oracle對內(nèi)容

30、的大小寫是區(qū)分的，所以ename='SMITH'和ename='smith'是不同的 使用算術(shù)表達(dá)式 nvl  null 問題：如何顯示每個(gè)雇員的年工資？ SELECT sal*13+nvl(comm, 0)*13 "年薪" , ename, comm FROM emp; 使用列的別名SELECT ename "姓名", sal*12 AS "年收入" FROM emp; 如何處理null值使用nvl函數(shù)來處理 如何連接字符串（|）SELECT ename  | '

31、 is a ' | job FROM emp; 使用where子句問題：如何顯示工資高于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到多個(gè)

32、字符  _：表示任意單個(gè)字符 問題：如何顯示首字符為S的員工姓名和工資？ SELECT ename,sal FROM emp WHERE ename like 'S%' 如何顯示第三個(gè)字符為大寫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的操

33、作符 問題：如何顯示沒有上級的雇員的情況？ 錯(cuò)誤寫法：select * from emp where mgr = '' 正確寫法：SELECT * FROM emp WHERE mgr is null;六：oracle表查詢(2)使用邏輯操作符號 問題：查詢工資高于500或者是崗位為MANAGER的雇員，同時(shí)還要滿足他們的姓名首字母為大寫的J？ SELECT * FROM emp WHERE (sal >500 or job = 'MANAGER') and ename LIKE 'J%' 使用order by 字句  

34、 默認(rèn)asc 問題：如何按照工資的從低到高的順序顯示雇員的信息？ SELECT * FROM emp ORDER by sal; 問題：按照部門號升序而雇員的工資降序排列 SELECT * FROM emp ORDER by deptno, sal DESC; 使用列的別名排序問題：按年薪排序 select ename, (sal+nvl(comm,0)*12 "年薪" from emp order by "年薪" asc; 別名需要使用“”號圈中,英文不需要“”號 分頁查詢 等學(xué)了子查詢再說吧。 Clear 清屏命令 oracle表復(fù)雜查詢 說明在實(shí)際

35、應(yīng)用中經(jīng)常需要執(zhí)行復(fù)雜的數(shù)據(jù)統(tǒng)計(jì)，經(jīng)常需要顯示多張表的數(shù)據(jù)，現(xiàn)在我們給大家介紹較為復(fù)雜的select語句 數(shù)據(jù)分組 max，min， avg， sum， count 問題：如何顯示所有員工中最高工資和最低工資？ SELECT MAX(sal),min(sal) FROM emp e;       最高工資那個(gè)人是誰？ 錯(cuò)誤寫法：select ename, sal from emp where sal=max(sal); 正確寫法：select ename, sal from emp where sal=(select max(sal) from

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

37、 where sal = (SELECT MAX(sal) FROM emp); 顯示工資高于平均工資的員工信息 SELECT * FROM emp e where sal > (SELECT AVG(sal) FROM emp); group by 和 having子句 group by用于對查詢的結(jié)果分組統(tǒng)計(jì)， having子句用于限制分組顯示結(jié)果。 問題：如何顯示每個(gè)部門的平均工資和最高工資？ SELECT AVG(sal), MAX(sal), deptno FROM emp GROUP by deptno; （注意：這里暗藏了一點(diǎn)，如果你要分組查詢的話，分組的字段deptno一

38、定要出現(xiàn)在查詢的列表里面，否則會報(bào)錯(cuò)。因?yàn)榉纸M的字段都不出現(xiàn)的話，就沒辦法分組了） 問題：顯示每個(gè)部門的每種崗位的平均工資和最低工資？ 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ù)分組的總結(jié) 1 分組函數(shù)只能出現(xiàn)在選擇列表、having、order by子句中(不能出現(xiàn)

39、在where中) 2 如果在select語句中同時(shí)包含有g(shù)roup by, having, order by 那么它們的順序是group by, having, order by 3 在選擇列中如果有列、表達(dá)式和分組函數(shù)，那么這些列和表達(dá)式必須有一個(gè)出現(xiàn)在group by 子句中，否則就會出錯(cuò)。 如SELECT deptno, AVG(sal), MAX(sal) FROM emp GROUP by deptno HAVING AVG(sal) < 2000; 這里deptno就一定要出現(xiàn)在group by 中 多表查詢 說明多表查詢是指基于兩個(gè)和兩個(gè)以上的表或是視圖的查詢。在實(shí)際應(yīng)用中

40、，查詢單個(gè)表可能不能滿足你的需求，（如顯示sales部門位置和其員工的姓名），這種情況下需要使用到（dept表和emp表） 問題：顯示雇員名，雇員工資及所在部門的名字【笛卡爾集】？ 規(guī)定：多表查詢的條件是 至少不能少于 表的個(gè)數(shù)-1 才能排除笛卡爾集 （如果有N張表聯(lián)合查詢，必須得有N-1個(gè)條件，才能避免笛卡爾集合） 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

41、 e, dept d WHERE e.deptno = d.deptno and e.deptno = 10; 問題：顯示各個(gè)員工的姓名，工資及工資的級別？ 先看salgrade的表結(jié)構(gòu)和記錄 SQL>select * from salgrade;     GRADE         LOSAL          HISAL -   -   -

42、60;       1          700           1200         2          1201     &#

43、160;    1400         3          1401          2000         4          2001 

44、         3000         5          3001          9999 SELECT e.ename, e.sal, s.grade FROM emp e, salgrade s WHERE e.sal BETWEEN s.losal A

45、ND s.hisal; 擴(kuò)展要求： 問題：顯示雇員名，雇員工資及所在部門的名字，并按部門排序？ 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放到查詢列里面） 自連接自連接是指在同一張表的連接查詢 問題：顯示某個(gè)員工的上級領(lǐng)導(dǎo)的姓名？ 比如顯示員工FORD的上級 SELECT worker.ename, boss.ename FROM emp worker,emp boss WHERE worke

46、r.mgr = boss.empno AND worker.ename = 'FORD' 子查詢 什么是子查詢子查詢是指嵌入在其他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 = 'SM

47、ITH'); 數(shù)據(jù)庫在執(zhí)行sql 是從左到右掃描的， 如果有括號的話，括號里面的先被優(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=.，因?yàn)榈忍?是一對一的） 在多行子查詢中使用all操作符問題：如何顯示工資比部門30的所有員工的工資

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

49、CT ename, sal, deptno FROM emp WHERE sal > ANY (SELECT sal FROM emp WHERE deptno = 30); 擴(kuò)展要求： 大家想想還有沒有別的查詢方法。 SELECT ename, sal, deptno FROM emp WHERE sal > (SELECT min(sal) FROM emp WHERE deptno = 30); 多列子查詢單行子查詢是指子查詢只返回單列、單行數(shù)據(jù)，多行子查詢是指返回單列多行數(shù)據(jù)，都是針對單列而言的，而多列子查詢是指查詢返回多個(gè)列數(shù)據(jù)的子查詢語句。 請思考如何查詢與SMITH的

50、部門和崗位完全相同的所有雇員。 SELECT deptno, job FROM emp WHERE ename = 'SMITH' SELECT * FROM emp WHERE (deptno, job) = (SELECT deptno, job FROM emp WHERE ename = 'SMITH'); 在from子句中使用子查詢請思考：如何顯示高于自己部門平均工資的員工的信息 思路： 1. 查出各個(gè)部門的平均工資和部門號 SELECT deptno, AVG(sal) mysal FROM emp GROUP by deptno; 2. 把上面的查

51、詢結(jié)果看做是一張子表 SELECT e.ename, e.deptno, e.sal, ds.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; 如何衡量一個(gè)程序員的水平？ 網(wǎng)絡(luò)處理能力， 數(shù)據(jù)庫， 程序代碼的優(yōu)化程序的效率要很高 小總結(jié)： 在這里需要說明的當(dāng)在from子句中使用子查詢時(shí)，該子查詢會被作為一個(gè)視圖來對待，因此叫做內(nèi)嵌視圖，當(dāng)在from子句中使用子查詢時(shí)，必須給子查詢指定別名

52、。 注意：別名不能用as，如：SELECT e.ename, e.deptno, e.sal, 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，否則會報(bào)錯(cuò)  （給表取別名的時(shí)候，不能加as；但是給列取別名，是可以加as的） 分頁查詢按雇員的id號升序取出 oracle的分頁一共有三種方式 1.根據(jù)rowid來分   select *

53、from t_xiaoxi where rowid in (select rid from (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í)行時(shí)間0.03秒 2.按分析函數(shù)來分   select * from (select t.*, row_number() over(order by cid desc) rk from t_xiaoxi t)

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

55、kokok select * from (select a1.*, rownum rn from(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相當(dāng)于Oracle分配的行的ID

56、號 3.挑選出610條記錄 先查出1-10條記錄 SELECT e.*, ROWNUM rn 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. 幾個(gè)查詢變化 a. 指定查詢列，只需要修改最里層的子查詢 只查詢雇員的編號和工資 SELECT * FROM (

57、SELECT e.*, ROWNUM rn FROM (SELECT ename, sal 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; 用查詢結(jié)果創(chuàng)建新表這個(gè)命令是一種快捷的建表方式 CREATE TABLE m

58、ytable (id, name, sal, job, deptno) as SELECT empno, ename, sal, job, deptno FROM emp; 創(chuàng)建好之后，desc mytable；和select * from mytable;看看結(jié)果如何？ 合并查詢 合并查詢有時(shí)在實(shí)際應(yīng)用中，為了合并多個(gè)select語句的結(jié)果，可以使用集合操作符號union，union all，intersect，minus 多用于數(shù)據(jù)量比較大的數(shù)據(jù)局庫，運(yùn)行速度快。 1). union 該操作符用于取得兩個(gè)結(jié)果集的并集。當(dāng)使用該操作符時(shí)，會自動去掉結(jié)果集中重復(fù)行。SELECT ename,

59、sal, job FROM emp WHERE sal >2500 UNION SELECT ename, sal, job FROM emp WHERE job = 'MANAGER' 2).union all 該操作符與union相似，但是它不會取消重復(fù)行，而且不會排序。 SELECT ename, sal, job FROM emp WHERE sal >2500 UNION ALL SELECT ename, sal, job FROM emp WHERE job = 'MANAGER' 該操作符用于取得兩個(gè)結(jié)果集的并集。當(dāng)使用該操作符時(shí)，會

60、自動去掉結(jié)果集中重復(fù)行。 3). intersect   使用該操作符用于取得兩個(gè)結(jié)果集的交集。 SELECT ename, sal, job FROM emp WHERE sal >2500 INTERSECT SELECT ename, sal, job FROM emp WHERE job = 'MANAGER' 4). minus 使用改操作符用于取得兩個(gè)結(jié)果集的差集，他只會顯示存在第一個(gè)集合中，而不存在第二個(gè)集合中的數(shù)據(jù)。 SELECT ename, sal, job FROM emp WHERE sal >2500 MINUS SELECT e

61、name, sal, job FROM emp WHERE job = 'MANAGER' （MINUS就是減法的意思） 創(chuàng)建數(shù)據(jù)庫有兩種方法： 1). 通過oracle提供的向?qū)Чぞ摺?   database Configuration Assistant  【數(shù)據(jù)庫配置助手】 2).我們可以用手工步驟直接創(chuàng)建。七：java操作oraclejava連接oracle 介紹：前面我們一直在plsql中操作oracle，那么如何在java 程序中操作數(shù)據(jù)庫呢？ 下面我們舉例說明，寫一個(gè)java，分頁顯示emp表的用戶信息。 Java代碼 1. pack

62、age com.sp;   2.   3. import java.sql.Connection;   4. import java.sql.DriverManager;   5. import java.sql.ResultSet;   6. import java.sql.Statement;   7.   8. /演示  如何使用 jdbc_odbc橋連接方式  

63、; 9. public class TestOracle    10.   11.     public static void main(String args)    12.         try    13.   14.     &

64、#160;       / 1.加載驅(qū)動   15.             Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");   16.   17.           &#

65、160; / 2.得到連接   18.             Connection ct = DriverManager.getConnection(   19.                   

66、0; "jdbc.odbc:testConnectOracle", "scott",    20.   21. "tiger");   22.   23.             / 從下面開始，和SQL Server一模一樣   24.   

67、60;         Statement sm = ct.createStatement();   25.             ResultSet rs = sm.executeQuery("select * from emp");   26.     &#