1、Oracle數(shù)據(jù)庫學(xué)習(xí)Oracle數(shù)據(jù)庫學(xué)習(xí)11基本使用51.1常用命令51.1.1Connect/Disconnect 數(shù)據(jù)庫連接命令51.1.2PL/SQL連接數(shù)據(jù)庫配置，Oracle客戶端的配置文件51.1.3配置Oracle數(shù)據(jù)庫監(jiān)聽Assistant61.1.4Password 修改密碼命令61.1.5Show 顯示登錄用戶61.1.6Clear 清屏61.1.7Exit 退出命令61.1.8文件操作命令61.1.9& 交互式命令61.1.10顯示和設(shè)置環(huán)境的變量72用戶管理72.1用戶管理72.1.1Create user 創(chuàng)建用戶72.1.2Password 修改密碼72.1.3

2、Drop user刪除用戶72.1.4賦予和收回權(quán)限82.1.5Profile管理用戶口令82.1.6給賬戶解鎖92.1.7終止口令92.1.8口令歷史92.1.9刪除profile文件93數(shù)據(jù)類型103.1字符型103.1.1Char 定長字符103.1.2Varchar2 變長字符103.1.3Clob 字符型大對象103.2數(shù)值型103.2.1Number 數(shù)值型103.3日期103.3.1Date 一般日期（年、月、日，時(shí)、分、秒）103.3.2timestamp 精確時(shí)間103.4圖片類型113.4.1Blob 可存儲(chǔ)圖片、視頻、聲音114表格管理114.1創(chuàng)建表(注意字母大小寫，一

3、般為大寫)114.2添加一個(gè)字段114.3修改字段的長度114.4刪除一個(gè)字段114.5修改表的名字124.6刪除表124.7所有字段都插入數(shù)據(jù)124.8插入部分字段124.9插入空值124.10查詢空值124.11修改字段124.12刪除數(shù)據(jù)135簡單表查詢135.1查詢表的結(jié)構(gòu)135.2查詢表的指定列和所有列135.3Distinct 取消重復(fù)行135.4瘋狂復(fù)制135.5Select中使用列的別名145.6Select中使用算術(shù)表達(dá)式145.7使用NVL函數(shù)處理NULL值145.8“|”字符串連接符145.9Where 查詢條件子句145.10Like 操作符145.11Where 條件

4、中使用IN155.12使用is null的操作符155.13使用邏輯操作符號155.14Order by排序語句155.15使用列的別名排序156復(fù)雜表查詢166.1數(shù)據(jù)分組 max，min，avg，sum，count166.2Group by和having子句166.3數(shù)據(jù)分組總結(jié)167多表查詢177.1多表查詢，between and語句177.2自連接178子查詢178.1單行子查詢178.2多行子查詢188.3使用any操作符查詢188.4多列子查詢188.5From子句中使用子查詢188.6分頁查詢188.7用查詢結(jié)果創(chuàng)建新表198.8合并查詢199Java操作Oracle209.1

5、jdbc.odbc橋連接,不能遠(yuǎn)程連接209.1.1引sql包209.1.2加載驅(qū)動(dòng)209.1.3得到連接209.2jdbc.Oracle連接，允許遠(yuǎn)程連接209.2.1引sql包209.2.2加載驅(qū)動(dòng)209.2.3得到連接2010事物2110.1使用子查詢插入數(shù)據(jù)2110.2使用子查詢插入數(shù)據(jù)2110.3Oracle中的事物2111函數(shù)2211.1字符函數(shù)2211.1.1lower(char):將字符串轉(zhuǎn)化為小寫的格式2211.1.2upper(char):將字符串轉(zhuǎn)化為大寫的格式2211.1.3length(char):返回字符串的長度2211.1.4substr(char,m,n):取字

6、符串的子串2211.1.5replace(char1,serch_string,replace_string)替換字符串2211.1.6instr(char1,char2,n,m)取子串在字符串的位置2211.2數(shù)學(xué)函數(shù)2311.2.1round(n,m) 四舍五入2311.2.2trunc(n,m) 截取數(shù)字2311.2.3mod(m,n) 取摩2311.2.4floor(n) 向下取最大整數(shù)2311.2.5ceil(n) 向上取最小整數(shù)2311.2.6abs(n) 返回?cái)?shù)字n的絕對值2411.2.7acos(n) 返回?cái)?shù)字的反余弦值2411.2.8asin(n) 返回?cái)?shù)字的反正弦值2411

7、.2.9atan(n) 返回?cái)?shù)字的反正切2411.2.10cos(n) 返回?cái)?shù)字的余弦值2411.2.11exp(n) 返回e的n次冪2411.2.12log(m,n) 返回對數(shù)值2411.2.13power(m,n) 返回m的n次冪2411.3日期函數(shù)2411.3.1To_date函數(shù)2411.3.2sysdate 該函數(shù)返回系統(tǒng)時(shí)間；2411.3.3add_months(d,n)；2411.3.4last_day(d) 返回指定日期所在月份的最后一天2511.4轉(zhuǎn)換函數(shù)2511.4.1To_char轉(zhuǎn)換函數(shù)2511.4.2To_date函數(shù)2611.5系統(tǒng)函數(shù)2611.5.1Sys_con

8、text函數(shù)2612數(shù)據(jù)庫管理2712.1數(shù)據(jù)庫管理員2712.1.1管理數(shù)據(jù)庫的用戶主要有：sys和system2712.1.2Dba、Sysdba、Sysoper權(quán)限的用戶2712.1.3管理初始化參數(shù)2812.2數(shù)據(jù)庫（表）的邏輯備份和恢復(fù)2812.2.1導(dǎo)出2812.2.2導(dǎo)入3012.3數(shù)據(jù)字典和動(dòng)態(tài)性能視圖3112.3.1數(shù)據(jù)字典3112.3.2數(shù)據(jù)字典-用戶名、權(quán)限、角色3112.3.3動(dòng)態(tài)性能視圖3213約束3213.1創(chuàng)建約束3313.2刪除約束3413.3顯示約束信息3413.4表級定義和列級定義3414索引、權(quán)限3514.1索引3514.1.1創(chuàng)建索引3514.1.2索引

9、使用原則3514.1.3索引缺點(diǎn)分析3514.1.4其他索引3514.1.5顯示表的所有索引3514.2權(quán)限3614.2.1系統(tǒng)權(quán)限3614.2.2對象權(quán)限3615角色3715.1預(yù)定義角色3815.1.1Connect角色3815.1.2resource角色3815.1.3dba角色3815.2自定義角色3915.2.1建立角色3915.2.2角色授權(quán)3915.2.3授予用戶角色權(quán)限3915.2.4刪除角色4015.2.5顯示角色信息4015.2.6精細(xì)訪問控制4016plsql編程4116.1PL/SQL編程介紹4116.1.1PL/SQL創(chuàng)建存儲(chǔ)過程4116.1.2PL/SQL編程分類4

10、216.1.3PL/SQL編寫規(guī)范4216.1.4PL/SQL編程塊4216.1.5PL/SQL編程過程4316.1.6PL/SQL編程函數(shù)4416.1.7PL/SQL編程包4516.1.8PL/SQL編程觸發(fā)器4616.1.9PL/SQL編程變量4616.1.10PL/SQL編程控制結(jié)構(gòu)5016.1.11PL/SQL編程分頁過程編寫5316.1.12PL/SQL編程例外6016.1.13PL/SQL編程視圖63基本使用Oracle安裝成功后，會(huì)默認(rèn)生成三個(gè)用戶Sys 用戶：超級管理員 權(quán)限最高 它的角色dba密碼change_on_installSystem 用戶：是系統(tǒng)管理員 權(quán)限也很高

11、他的角色是 dbaoper 密碼 managerScott用戶：普通用戶 密碼是 tigerSys與system區(qū)別：Sys有create database的權(quán)限，而system沒有，其他相似，日常對Oracle管理過程中使用system就夠了。常用命令Connect/Disconnect 數(shù)據(jù)庫連接命令Connect 用戶名/密碼網(wǎng)絡(luò)服務(wù)名as sysdba/sysoper,當(dāng)用特權(quán)用戶身份連接時(shí)，必須帶上as sysdba或是as sysoper ;Disconnect該命令用來斷開與當(dāng)前數(shù)據(jù)庫的連接。PL/SQL連接數(shù)據(jù)庫配置，Oracle客戶端的配置文件Oracle客戶端的配制文件，默

12、認(rèn)會(huì)安裝在“C:Oracleora90networkadmin”目錄下，名為“tnsnames.ora”參考格式如下：YY_192.168.1.7 = - YY_192.168.1.7即數(shù)據(jù)庫名_數(shù)據(jù)庫IP地址 (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.7)(PORT = 1521) - HOST數(shù)據(jù)庫IP地址,PORT端口 ) (CONNECT_DATA = (SID = YY) -YY數(shù)據(jù)庫名 (SERVER = DEDICATED) )配置Oracle數(shù)據(jù)庫監(jiān)聽Assistant

13、Password 修改密碼命令該命令用于修改用戶的密碼，如果想修改其他用戶的密碼，需要用sys/system登錄Show 顯示登錄用戶Show user;顯示當(dāng)前登錄用戶名Clear 清屏Clear;清屏Exit 退出命令該命令會(huì)斷開與數(shù)據(jù)庫的連接，同時(shí)會(huì)退出sql*plus 。 文件操作命令1) Start 和 運(yùn)行sql腳本如：sql d:a.sql 或者sqlStart d:a.sql2) Edit 該命令可以編輯指定的SQL腳本如：Sqledit d:a.sql3) Spool 該命令可以將sql*plus屏幕上的內(nèi)容輸出到指定的文件中去(假脫機(jī))如：Sqlspool d:b.sql

14、并輸入sqlspool off；& 交互式命令& 可以替代變量在執(zhí)行時(shí)，需要用戶輸入。如：SQLselsct * from emp where job=&工作；顯示和設(shè)置環(huán)境的變量可以用來控制輸出的各種格式，set show 如果希望永久的保存相關(guān)的設(shè)置，可以去修改glogin.sql腳本1) Linsesize 設(shè)置顯示行的寬度，默認(rèn)是80個(gè)字符Sqlshow linesizeSqlset linesize 902) Pagesize 設(shè)置每頁顯示的行數(shù)目，默認(rèn)是14，用法和linesize一樣用戶管理用戶管理 Create user 創(chuàng)建用戶創(chuàng)建用戶必須用DBA權(quán)限，或者擁有alter u

15、ser系統(tǒng)權(quán)限，密碼只能以字母開頭, 新創(chuàng)建的用戶沒有任何權(quán)限，不能登錄數(shù)據(jù)庫，需要授權(quán)。Create user abc identified by m123；-創(chuàng)建用戶名為abc，密碼為m123的用戶Password 修改密碼給自己修改密碼可以直接使用Sqlpassword 用戶名如果給別人修改密碼則需要具有dba的權(quán)限，或是擁有alter user的系統(tǒng)權(quán)限Sqlalter user 用戶名 identitied by 新密碼Drop user刪除用戶在刪除用戶時(shí)，如果要?jiǎng)h除的用戶，已經(jīng)創(chuàng)建了表，那么就需要在刪除時(shí)帶一個(gè)參數(shù)cascade，指刪除該用戶下面所有的表，一般以dba的身份去刪除

16、某個(gè)用戶，如果用其他用戶去刪除用戶則需要具有drop user的權(quán)限SqlDrop user 用戶名caseade賦予和收回權(quán)限1) 授予權(quán)限命令grant，常用的角色有connect、dba、resources三種；Resources角色可以在任何一個(gè)表空間建表；Sqlgrant connect to xiaoming;-給xiaoming授予connect角色的權(quán)限SQL revoke sysdba from xiaoming; -回收xiaoming的sysdba權(quán)限2) 如何使用戶可以管理其他用戶的表，Select查詢、insert插入、update修改、delete刪除、all全部、

17、create index；只有sys、system和表的創(chuàng)建者才可以給其他用戶授予表的管理權(quán)限(假設(shè)emp為Scott用戶的表)；Grant select on emp to abc；-Scott用戶將emp表的查詢權(quán)限授予給abc用戶Grant update on emp to abc；-Scott用戶將emp表的修改權(quán)限授予給abc用戶Grant all on emp to abc；-Scott用戶將emp表的所有權(quán)限授予給abc用戶3) 回收權(quán)限命令revoke，誰授予的權(quán)限誰收回Revoke select on emp to abc；-Scott用戶將emp表的查詢權(quán)限從abc用戶收回

18、Revoke update on emp to abc；-Scott用戶將emp表的修改權(quán)限從abc用戶收回Revoke all on emp to abc；-Scott用戶將emp表的所有權(quán)限從abc用戶收回4) 被授予權(quán)限用戶繼續(xù)授予該權(quán)限給其他用戶，如果是對象權(quán)限，后面就加入with grant option；如果是系統(tǒng)權(quán)限，后面就加入with admin option；如果收回某用戶的權(quán)限則該用戶授予給其他下級用戶該權(quán)限將全部收回Grant select on emp to abc with grant option；- Scott用戶將emp表的查詢權(quán)限授予給abc用戶，并且讓abc

19、用戶繼續(xù)給其他用戶授權(quán)Grant select on Scott.emp to abc1 ；abc用戶將Scott.emp表的查詢權(quán)限授予給abc1用戶Profile管理用戶口令Profile是口令限制，資源限制的命令集合，當(dāng)建立數(shù)據(jù)庫時(shí)，Oracle會(huì)自動(dòng)建立名為default的profile，當(dāng)建立用戶沒有指定profile選項(xiàng)，那Oracle就會(huì)將default分配給用戶，賬戶鎖定:指定該賬戶登錄時(shí)最多可以輸入錯(cuò)誤密碼的次數(shù)，也可以指定用戶鎖定的時(shí)間(天)，一般用dba的身份去執(zhí)行該命令。案例：指定abc賬戶登錄時(shí)最多可以輸入錯(cuò)誤密碼3次，鎖定的時(shí)間2天Sqlcreate profile

20、 lock_a limit failed_login_attempts 3 password_lock_time 2;Sqlalter user abc profile lock_a;-給用戶abc指定profile選項(xiàng)給賬戶解鎖Sqlalter user abc account unlock;-給用戶abc解鎖終止口令為了讓用戶定期修改密碼可以使用終止口令的指令來完成，同樣這個(gè)命令也需要dba身份來操作。案例:創(chuàng)建一個(gè)profile文件，要求該用戶每隔10天要修改自家的登錄密碼，寬限期為2天。Sqlcreate profile lock_a1 limit password_life_time

21、 10 password_grace_time 2;Sqlalter user abc profile lock_a1;口令歷史如果希望用戶在修改密碼時(shí)，不能使用以前使用過的密碼，可使用口令歷史，這樣Oracle就會(huì)將口令修改的信息存放到數(shù)據(jù)字典中，這樣當(dāng)用戶修改密碼時(shí)，Oracle就會(huì)對新舊密碼進(jìn)行比較，當(dāng)發(fā)現(xiàn)新舊密碼一樣時(shí)，就提示用戶重新輸入密碼。案例：建立profile文件, password_reuse_time /指定口令可重用時(shí)間，超過該時(shí)間可再次使用：Sqlcreate profile password_h limit password_life_time 10 password

22、_grace_time 2 password_reuse_time 10;分配給用戶：Sqlalter user abc profile password_h;刪除profile文件刪除profile文件，如果該profile已經(jīng)分配給客戶需在后面加參數(shù)cascade，所有受到該profile文件限制的用戶全部解除該限制。Sqldrop profile password_h cascade;數(shù)據(jù)類型字符型Char 定長字符char(10) 定長字符 最大2000字符，字符數(shù)不足用空格不足，查詢速度快，適合定長數(shù)據(jù)，如身份證、手機(jī)號碼等；Varchar2 變長字符varchar(20) 變長字符

23、 最大4000字符；Clob 字符型大對象clob(30000) 字符型大對象，最大4G，可以存圖片、視頻等；數(shù)值型Number 數(shù)值型 number(5,2) 可以表示-10的38次方到10的38次方，如number(5)表示5位整數(shù)，number(5,2)表示3位整數(shù)2位小數(shù)；日期Date 一般日期（年、月、日，時(shí)、分、秒）包含年月日和時(shí)分秒；timestamp 精確時(shí)間時(shí)間可以精確到更小的單位圖片類型Blob 可存儲(chǔ)圖片、視頻、聲音 blob 二進(jìn)制數(shù)據(jù)，最大4G，可以存圖片、視頻、聲音等；表格管理創(chuàng)建表(注意字母大小寫，一般為大寫)學(xué)生表：create table student(，-

24、學(xué)生xh number(4), -學(xué)號xm varchar2(20),-姓名xb char(2),-性別birthday date,-出生日期val number(7,2)-獎(jiǎng)學(xué)金);班級表：create table class(-班級classid(4),-班級編號name(20),-班級名稱);添加一個(gè)字段alter table student add(classid number(2);修改字段的長度alter table student modify (xm varchar2(30);刪除一個(gè)字段alter table student drop column sal；實(shí)際過程中不要輕易

25、刪除字段。修改表的名字rename student to stu;-將STUDENT修改為STU刪除表drop table student;-刪除STUDENT表所有字段都插入數(shù)據(jù)insert into student values(1003,張三,01-5月-05,10);日期格式默認(rèn)為：DD-MON-YY該日期格式： alter session set nls_date format =YYYY-MM-DD;日期格式可以任意修改，Y年份，M月份，D日；插入部分字段insert into student (xh,xm,sex) values (1004,李四,女);插入空值insert in

26、to student (xh,xm,sex,birthday) values (1005,楊慧,女，null);查詢空值select * from student where birthday is null；-查詢空值select * from student where birthday is not null；-查詢非空值修改字段update student set sex=男 where xh=1004; -修改一個(gè)字段update student set sex=男，birthday=1980-04-01 where xh=1004; -修改多個(gè)字段例如：update student

27、 set sal=sal*1.5 where sex=男; -所有男性員工薪水加1.5倍update student set birthday is null where xh=1004; -修改為空值刪除數(shù)據(jù)delete from student；-刪除所有記錄，表結(jié)構(gòu)還在，寫日志，可以恢復(fù)的(回滾)，速度慢delete from student where xh=1005; -刪除一條記錄drop table student；-刪除表結(jié)構(gòu)和數(shù)據(jù)truncate table student;-刪除表中的所有記錄，表結(jié)構(gòu)還在，不寫日志，無法找回刪除的記錄，速度快；savepoint AA；-創(chuàng)

28、建回滾點(diǎn)AArollback to AA;-回滾到AA點(diǎn)，需先創(chuàng)建回滾點(diǎn)簡單表查詢查詢表的結(jié)構(gòu)Sqldesc dept；-查看dept表的結(jié)構(gòu)查詢表的指定列和所有列Select * from emp;-查詢?nèi)苛蠸elect ename，sal，job from emp；-查詢指定列注意：查詢過程中盡量少用查詢?nèi)苛?，以?jié)省查詢時(shí)間，提高查詢效率Distinct 取消重復(fù)行Select distinct deptno，job from emp；瘋狂復(fù)制Insert into users(userid,username,userpass) select * from users;-向users表

29、中添加users自己表中的數(shù)據(jù)案例1：查詢SMITH 的薪水，工作，所在部門Select deptno，job，sal from emp where ename=SMITH；注意：Select語句中的大小寫不區(qū)分，但是單引號中的內(nèi)區(qū)分大小寫Select中使用列的別名Select ename “姓名”， sal*12 as “年收入” from emp;-從emp表中查詢ename別名“姓名”、sal*12別名“年收入”Select中使用算術(shù)表達(dá)式注意：在select運(yùn)算過程中如果其中有一個(gè)值為NULL則整個(gè)值為NULLSelect ename ，sal*12 from emp;-查詢年工資Se

30、lect sal*12+comm*12 “年總工資”,ename，comm from emp；-年總工資等于年工資加年獎(jiǎng)金可以使用 +，-，*，/ 進(jìn)行運(yùn)算使用NVL函數(shù)處理NULL值Nvl(comm,0);如果comm值為空（NULL）則返回值為后面的0，如果comm值不為空則返回comm的值Select sal*12+nvl(comm,0)*12 “年總工資”,ename，comm from emp；-年總工資等于年工資加年獎(jiǎng)金“|”字符串連接符Select ename |是| job from emp；-將查詢出的值和字符串連接在一起Where 查詢條件1948.78+120子句案例1：

31、如何顯示工資高于3000的員工Select ename,sal from emp where sal3000;案例2：如何查找1982.1.1后入職的員工Select ename,hiredate from emp where hiredate1-1月-1981；案例3：如何顯示工資在2000到2500的員工情況Select ename,sal from emp where sal=2000 and sal500 or job=MANAGER) and ename like J%;Order by排序語句Order by 默認(rèn)升序(asc)，降序(desc) 。如何按照工資的從低到高的順序顯示

32、雇員的信息Select * from emp order by sal;按照部門號升序而雇員的工資降序排列Select * from emp order by deptno， sal desc;使用列的別名排序注意：別名需要加雙引號” ，英文不用加雙引號，給列加別名時(shí)中間可以加as 。Select ename,sal*12 “年薪” from emp order by “年薪” asc;復(fù)雜表查詢數(shù)據(jù)分組 max，min，avg，sum，count顯示所有員工中最高工資和最低工資Select max(sal),min(sal) from emp;顯示工資最高員工的名字，工作崗位Select e

33、name,sal from emp where sal=(Select max(sal) from emp)顯示所有員工的平均工資和工資總和Select avg(sal),sum(sal) from emp;計(jì)算共有多少員工Select count(*) from emp；顯示工資高于平均工資的員工信息Select * from emp where sal(Select avg(sal) from emp);Group by和having子句Group by用于對查詢的結(jié)果分組統(tǒng)計(jì),分組查詢中分組字段必須出現(xiàn)在查詢結(jié)果中Having子句用于限制分組顯示結(jié)果顯示每個(gè)部門的平均工資和最高工資Sel

34、ect avg(sal),max(sal),deptno from emp group by deptno;顯示每個(gè)部門的每種崗位的平均工資和最低工資Select avg(sal),min(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子句中；2、 如果在select語句中同時(shí)包

35、含group by，having，order by那么他們的順序是group by，having，order by ；3、 在選擇列中如果有列、表達(dá)式和分組函數(shù)，那么這些列和表達(dá)式必須有一個(gè)出現(xiàn)在group by子句中，否則就會(huì)出錯(cuò)案例1:顯示平均工資低于2000的部門號和它的平均工資并按平均工資排序Select avg(sal),max(sal),deptno from emp group by deptno having avg(sal)2000 order by avg(sal);多表查詢多表查詢，between and語句多表查詢是指基于兩個(gè)和兩個(gè)以上的表或是視圖的查詢,多表查詢查詢條

36、件不能少于表的個(gè)數(shù)減1 。案例1：顯示雇員名，雇員工資及所在部門的名字；【笛卡爾集】Select a1.ename,a1.sal,a2.dname from emp a1 dept a2 where a1.deptno=a2.deptno;案例2：顯示部門號為10的部門名、員工名和工資Select a1.ename,a1.sal,a2.dname from emp a1 dept a2 where a1.deptno=a2.deptno and a1.deptno=10; 案例3：顯示部門號為10的部門名、員工名和工資Select a1.ename,a1.sal,a2.grade from e

37、mp a1,salgrade a2 between a2.losal and a2.hisal;案例4：顯示雇員名、雇員工資及所在部門的名字，并按部門排序Select a1.ename,a1.sal,a2.dname from emp a1 dept a2 where a1.deptno=a2.deptno order by a1.deptno;自連接自連接是指在同一張表的鏈接查詢顯示FORD上級的姓名Select a1.ename，a2.ename from a1.emp，a2.emp where a1.mgr=a2.empno and a1.ename=FORDSavepoint;左連接和

38、右連接左連接和右連接以如下方式來實(shí)現(xiàn)：查看如下語句： SELECT emp_name, dept_name FORM Employee, Department WHERE Employee.emp_deptid(+) = Department.deptid 此SQL使用了右連接，即“(+)”所在位置的另一側(cè)為連接的方向，右連接說明等號右側(cè)的所有記錄均會(huì)被顯示，無論其在左側(cè)是否得到匹配，也就是說上例中無論會(huì)不會(huì)出現(xiàn)某個(gè)部門沒有一個(gè)員工的情況，這個(gè)部門的名字都會(huì)在查詢結(jié)果中出現(xiàn)。 反之： SELECT emp_name, dept_name FORM Employee, Department WH

39、ERE Employee.emp_deptid = Department.deptid(+)則是左連接，無論這個(gè)員工有沒有一個(gè)能在Department表中得到匹配的部門號，這個(gè)員工的記錄都會(huì)被顯示一般的相等連接select * from a,b where a.id = b.id;這個(gè)是內(nèi)連接子查詢子查詢是指嵌入其他sql語句中的select語句，也叫嵌套查詢注意：數(shù)據(jù)庫在執(zhí)行sql時(shí)是從后往前執(zhí)行，有括號先執(zhí)行括號里面的語句，包含多個(gè)條件時(shí)，盡量將能過濾數(shù)據(jù)量大的條件放在后面，以提高執(zhí)行效率單行子查詢單行子查詢是指子語句只返回一行數(shù)據(jù)的子查詢語句如：顯示與SMITH同一部門的所有員工SELE

40、CT * from emp where deptno=(Select deptno from emp where ename=SMITH);多行子查詢多行子查詢指子語句返回多行數(shù)據(jù)的子查詢?nèi)纾翰樵兒筒块T10的工作相同雇員的名字、崗位、工資、部門號SELECT * from emp where job in (Select distinct job from emp where deptno=10)顯示工資比部門30的所有員工的工資高的員工的姓名、工資和部門號Select ename，sal，deptno from emp where salall (select sal from emp wh

41、ere deptno=3);Select ename，sal，deptno from emp where sal(select max(sal) from emp where deptno=3);-效率比第一種高使用any操作符查詢顯示工資比部門30的任意一個(gè)員工的工資高的員工的姓名、工資和部門號Select ename，sal，deptno from emp where salany(select sal from emp where deptno=30);Select ename，sal，deptno from emp where sal(select min(sal) from emp

42、where deptno=30);多列子查詢多列子查詢是指查詢返回多個(gè)列數(shù)據(jù)的子查詢語句查詢與SMITH的部門和崗位完全相同的所有雇員Select * from EMP where (deptno,job)=(select deptno,job from emp where ename=SMITH);-注意列名前后順序?qū)?yīng)From子句中使用子查詢當(dāng)在from子句中使用子查詢時(shí)，該子查詢會(huì)被作為一個(gè)視圖來對待，因此叫做內(nèi)嵌圖，當(dāng)在from子句中使用子查詢時(shí)，必須給予查詢指定別名。案例1：顯示高于自己部門平均工資員工的信息Select a1.ename,a1.sal,a1.deptno,a2.my

43、sal from emp a1,(Select avg(sal) mysal,deptno from emp group by deptno) a2 where a1.deptno=a2.deptno and a1.sala2.mysal;分頁查詢Oracle分頁方式有三種：1、rownum第一步：Select a1.*，rownum rn from (select * from emp) a1 where rownum=10;-找出前10行記錄;第二步：Select * from （Select a1.*，rownum rn from (select * from emp) a1 where

44、 rownum=6;-找出6-10行記錄;案例：A 指定查詢列，只需修改最里層的子查詢Select * from （Select a1.*，rownum rn from (select ename，sal from emp) a1 where rownum=6;B 按順序查詢，只需修改最里層的子查詢Select * from （Select a1.*，rownum rn from (select * from emp order by sal) a1 where rownum=6;C所有查詢修改，只需修改最里層的子查詢2、Rowid Select * from t_xiaoxi where r

45、owid in (select rown rn,rid from (select rowid rid ,cid from t_xiaoxi order by cid desc) where rownum9980) order by cid desc;3、按分析函數(shù)Select * from （select t.* ,row_number() over(order by cid desc) rk from t_xiaoxi t） where rk9980;用查詢結(jié)果創(chuàng)建新表Create table mytable （id ,name,sal,job,deptno） as select empno

46、,ename,sal,job,deptno from emp ;創(chuàng)建表的同時(shí)把數(shù)據(jù)打入新表中合并查詢集合操作符號 union ，union all，intersect，minus ，查詢比and、or速度快。1、union該操作符用于取得兩個(gè)結(jié)果集的并表，當(dāng)使用該操作符時(shí)，會(huì)自動(dòng)去掉結(jié)果集中的重復(fù)行。Select ename，sal，job from emp where sal2500 union select ename,sal,job from emp where job=MANAGER;2、union all該操作和union相似，但是它不會(huì)取消重復(fù)行，而且不會(huì)排序3、Intersect

47、使用該操作用于取得兩個(gè)結(jié)果集的交集。4、Minus使用該操作符用于取得兩個(gè)結(jié)果集的差集，它只會(huì)顯示存在第一個(gè)集合中，而不存在第二個(gè)集合中的數(shù)據(jù)，用一個(gè)集合減去另一個(gè)集合。Java操作Oraclejdbc.odbc橋連接,不能遠(yuǎn)程連接引sql包Import java.sql.*加載驅(qū)動(dòng)Class.forName(“sun.jdbc.odbc.jdbcqdbcDriver”);得到連接Connection ct=DriveManager.getConnection(”jdbc:odbc:數(shù)據(jù)源”,”用戶名”,”密碼”);jdbc.Oracle連接，允許遠(yuǎn)程連接引sql包Import java.sq

48、l.*加載驅(qū)動(dòng)Class.forName(“Oracle.jdbc. Driver. OracleDriver”);得到連接Connection ct=DriveManager.getConnection(”jdbc:oracle:thin:127.0.0.1:1521:數(shù)據(jù)庫名稱”,”用戶名”,”密碼”);事物set transaction read only;-設(shè)為只讀事物使用子查詢插入數(shù)據(jù)使用子查詢插入數(shù)據(jù)時(shí)，一條Insert語句可以插入大量的數(shù)據(jù)，當(dāng)處理行遷移或者裝載外部表的數(shù)據(jù)到數(shù)據(jù)庫時(shí)，可以使用子查詢來插入數(shù)據(jù)。insert into kkk (myid,myname,mydept

49、) select empno,ename,deptno from emp where deptno=10;使用子查詢插入數(shù)據(jù)使用update語句更新數(shù)據(jù)時(shí)，既可以使用表達(dá)式或者數(shù)值直接修改數(shù)據(jù)，也可以使用子查詢修改數(shù)據(jù)。案例1：使員工SCOTT的崗位、工資、補(bǔ)助與SMITH員工一樣Update emp set (job,sal,comm)=(select job,sal,comm from emp where ename=SMITH) where ename=scott;Oracle中的事物事物用于保證數(shù)據(jù)的一致性，它由一組相關(guān)的dml語句組成，該組的dml語句要么全部成功，要么全部失敗，dm

50、l語句指數(shù)據(jù)操作語言，即增、刪、改語句，沒有查詢語句。如：網(wǎng)上轉(zhuǎn)賬就是典型的要用事物來處理，用以保證數(shù)據(jù)的一致性。事物的幾個(gè)重要操作：1、設(shè)置保存點(diǎn)，savepoint a2、取消部分事物， rollback to a3、取消全部事物， rollback函數(shù)字符函數(shù)lower(char):將字符串轉(zhuǎn)化為小寫的格式select lower(ename) from emp;-把所有人的名字按小寫顯示，多個(gè)逗號分開upper(char):將字符串轉(zhuǎn)化為大寫的格式select upper(ename) from emp;-把所有人的名字按大寫顯示，多個(gè)逗號分開length(char):返回字符串的長度

51、select * from emp where length(ename)=5;-顯示正好有5個(gè)字符的名字substr(char,m,n):取字符串的子串select substr(ename,1,3) from emp;-顯示所有姓名的前三個(gè)字符,中間數(shù)值指從第幾個(gè)字符取值，后面的數(shù)值指取幾個(gè)字符，1個(gè)漢字算1個(gè)字符；replace(char1,serch_string,replace_string)替換字符串Select replace(ename,A,我) from emp;-顯示所有姓名，用我替換所有Ainstr(char1,char2,n,m)取子串在字符串的位置案例：以首個(gè)字符為大

52、寫顯示所有姓名1) 首字母大寫顯示姓名Select upper(substr(ename,1,1) from emp;2) 從第二個(gè)字母小寫顯示姓名Select lower(substr(ename,2,length(ename)-1) from emp;3) 兩者合并Select upper(substr(ename,1,1)|lower(substr(ename,2,length(ename)-1) from emp;-以首個(gè)字符為大寫顯示所有姓名數(shù)學(xué)函數(shù)數(shù)學(xué)函數(shù)的輸入?yún)?shù)和返回值的數(shù)據(jù)類型都是數(shù)字類型的，數(shù)學(xué)函數(shù)包括cos,cosh,exp,ln,log,sin,sinh,sqrt,tan,tanh,acos,asin,atan,round等；round(n,m) 四舍五入該函數(shù)用于執(zhí)行四舍五入，如果省掉m，則四舍五入到整數(shù)；如果m是正數(shù)，則四舍五入到小數(shù)點(diǎn)的m位后；如果m是負(fù)數(shù)，則四舍五入