Oracle數(shù)據(jù)庫(kù)學(xué)習(xí)日記實(shí)用性最強(qiáng)的Oracle學(xué)習(xí)總結(jié)

1、Oracle數(shù)據(jù)庫(kù)學(xué)習(xí)Oracle數(shù)據(jù)庫(kù)學(xué)習(xí)11根本使用51.1常用命令5Connect/Disconnect 數(shù)據(jù)庫(kù)連接命令5PL/SQL連接數(shù)據(jù)庫(kù)配置，Oracle客戶(hù)端的配置文件5配置Oracle數(shù)據(jù)庫(kù)監(jiān)聽(tīng)Assistant6Password 修改密碼命令6Show 顯示登錄用戶(hù)61.1.6Clear 清屏6Exit 退出命令6文件操作命令6& 交互式命令6顯示和設(shè)置環(huán)境的變量72用戶(hù)管理72.1用戶(hù)管理7Create user 創(chuàng)立用戶(hù)7Password 修改密碼7Drop user刪除用戶(hù)7賦予和收回權(quán)限8Profile管理用戶(hù)口令8給賬戶(hù)解鎖9終止口令9口令歷史9刪除profile

2、文件93數(shù)據(jù)類(lèi)型103.1字符型10Char 定長(zhǎng)字符10Varchar2 變長(zhǎng)字符10Clob 字符型大對(duì)象103.2數(shù)值型10Number 數(shù)值型103.3日期10Date 一般日期年、月、日，時(shí)、分、秒10timestamp 精確時(shí)間103.4圖片類(lèi)型11Blob 可存儲(chǔ)圖片、視頻、聲音114表格管理114.1創(chuàng)立表(注意字母大小寫(xiě)，一般為大寫(xiě))114.2添加一個(gè)字段114.3修改字段的長(zhǎng)度114.4刪除一個(gè)字段114.5修改表的名字124.6刪除表124.7所有字段都插入數(shù)據(jù)124.8插入局部字段124.9插入空值124.10查詢(xún)空值124.11修改字段124.12刪除數(shù)據(jù)135簡(jiǎn)單表

3、查詢(xún)135.1查詢(xún)表的結(jié)構(gòu)135.2查詢(xún)表的指定列和所有列135.3Distinct 取消重復(fù)行135.4瘋狂復(fù)制135.5Select中使用列的別名145.6Select中使用算術(shù)表達(dá)式145.7使用NVL函數(shù)處理NULL值145.8“|字符串連接符145.9Where 查詢(xún)條件子句145.10Like 操作符145.11Where 條件中使用IN155.12使用is null的操作符155.13使用邏輯操作符號(hào)155.14Order by排序語(yǔ)句155.15使用列的別名排序156復(fù)雜表查詢(xún)166.1數(shù)據(jù)分組 max，min，avg，sum，count166.2Group by和having

4、子句166.3數(shù)據(jù)分組總結(jié)167多表查詢(xún)177.1多表查詢(xún)，between and語(yǔ)句177.2自連接178子查詢(xún)178.1單行子查詢(xún)178.2多行子查詢(xún)188.3使用any操作符查詢(xún)188.4多列子查詢(xún)188.5From子句中使用子查詢(xún)188.6分頁(yè)查詢(xún)188.7用查詢(xún)結(jié)果創(chuàng)立新表198.8合并查詢(xún)199Java操作Oracle209.1jdbc.odbc橋連接,不能遠(yuǎn)程連接20引sql包20加載驅(qū)動(dòng)20得到連接209.2jdbc.Oracle連接，允許遠(yuǎn)程連接20引sql包20加載驅(qū)動(dòng)20得到連接2010事物2110.1使用子查詢(xún)插入數(shù)據(jù)2110.2使用子查詢(xún)插入數(shù)據(jù)2110.3Oracl

5、e中的事物2111函數(shù)2211.1字符函數(shù)22lower(char):將字符串轉(zhuǎn)化為小寫(xiě)的格式22upper(char):將字符串轉(zhuǎn)化為大寫(xiě)的格式22length(char):返回字符串的長(zhǎng)度22substr(char,m,n):取字符串的子串22replace(char1,serch_string,replace_string)替換字符串22instr(char1,char2,n,m)取子串在字符串的位置2211.2數(shù)學(xué)函數(shù)23round(n,m) 四舍五入23trunc(n,m) 截取數(shù)字23mod(m,n) 取摩23floor(n) 向下取最大整數(shù)23ceil(n) 向上取最小整數(shù)23a

6、bs(n) 返回?cái)?shù)字n的絕對(duì)值24acos(n) 返回?cái)?shù)字的反余弦值2411.2.8asin(n) 返回?cái)?shù)字的反正弦值24atan(n) 返回?cái)?shù)字的反正切24cos(n) 返回?cái)?shù)字的余弦值24exp(n) 返回e的n次冪24log(m,n) 返回對(duì)數(shù)值24power(m,n) 返回m的n次冪2411.3日期函數(shù)24To_date函數(shù)24sysdate 該函數(shù)返回系統(tǒng)時(shí)間；24add_months(d,n)；24last_day(d) 返回指定日期所在月份的最后一天2511.4轉(zhuǎn)換函數(shù)25To_char轉(zhuǎn)換函數(shù)25To_date函數(shù)2611.5系統(tǒng)函數(shù)26Sys_context函數(shù)2612數(shù)據(jù)庫(kù)

7、管理2712.1數(shù)據(jù)庫(kù)管理員27管理數(shù)據(jù)庫(kù)的用戶(hù)主要有：sys和system27Dba、Sysdba、Sysoper權(quán)限的用戶(hù)27管理初始化參數(shù)2812.2數(shù)據(jù)庫(kù)表的邏輯備份和恢復(fù)28導(dǎo)出28導(dǎo)入3012.3數(shù)據(jù)字典和動(dòng)態(tài)性能視圖31數(shù)據(jù)字典31數(shù)據(jù)字典-用戶(hù)名、權(quán)限、角色31動(dòng)態(tài)性能視圖3213約束3213.1創(chuàng)立約束3313.2刪除約束3413.3顯示約束信息3413.4表級(jí)定義和列級(jí)定義3414索引、權(quán)限3514.1索引35創(chuàng)立索引35索引使用原那么35索引缺點(diǎn)分析35其他索引35顯示表的所有索引3514.2權(quán)限36系統(tǒng)權(quán)限36對(duì)象權(quán)限3615角色3715.1預(yù)定義角色38Connect

8、角色38resource角色38dba角色3815.2自定義角色39建立角色39角色授權(quán)39授予用戶(hù)角色權(quán)限39刪除角色40顯示角色信息40精細(xì)訪(fǎng)問(wèn)控制4016plsql編程4116.1PL/SQL編程介紹41PL/SQL創(chuàng)立存儲(chǔ)過(guò)程41PL/SQL編程分類(lèi)42PL/SQL編寫(xiě)標(biāo)準(zhǔn)42PL/SQL編程塊42PL/SQL編程過(guò)程43PL/SQL編程函數(shù)44PL/SQL編程包45PL/SQL編程觸發(fā)器46PL/SQL編程變量46PL/SQL編程控制結(jié)構(gòu)50PL/SQL編程分頁(yè)過(guò)程編寫(xiě)53PL/SQL編程例外60PL/SQL編程視圖63根本使用Oracle安裝成功后，會(huì)默認(rèn)生成三個(gè)用戶(hù)Sys 用戶(hù)：

9、超級(jí)管理員 權(quán)限最高 它的角色dba密碼change_on_installSystem 用戶(hù)：是系統(tǒng)管理員 權(quán)限也很高 他的角色是 dbaoper 密碼 managerScott用戶(hù)：普通用戶(hù) 密碼是 tigerSys與system區(qū)別：Sys有create database的權(quán)限，而system沒(méi)有，其他相似，日常對(duì)Oracle管理過(guò)程中使用system就夠了。常用命令Connect/Disconnect 數(shù)據(jù)庫(kù)連接命令Connect 用戶(hù)名/密碼網(wǎng)絡(luò)效勞名as sysdba/sysoper,當(dāng)用特權(quán)用戶(hù)身份連接時(shí)，必須帶上as sysdba或是as sysoper ;Disconnect該

10、命令用來(lái)斷開(kāi)與當(dāng)前數(shù)據(jù)庫(kù)的連接。PL/SQL連接數(shù)據(jù)庫(kù)配置，Oracle客戶(hù)端的配置文件Oracle客戶(hù)端的配制文件，默認(rèn)會(huì)安裝在“C:Oracleora90networkadmin目錄下，名為“tnsnames.ora參考格式如下：YY_192.168.1.7 = - (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.7)(PORT = 1521) - HOST數(shù)據(jù)庫(kù)IP地址,PORT端口 ) (CONNECT_DATA = (SID = YY) -YY數(shù)據(jù)庫(kù)名 (SERVER = DEDI

11、CATED) )配置Oracle數(shù)據(jù)庫(kù)監(jiān)聽(tīng)AssistantPassword 修改密碼命令該命令用于修改用戶(hù)的密碼，如果想修改其他用戶(hù)的密碼，需要用sys/system登錄Show 顯示登錄用戶(hù)Show user;顯示當(dāng)前登錄用戶(hù)名Clear 清屏Clear;清屏Exit 退出命令該命令會(huì)斷開(kāi)與數(shù)據(jù)庫(kù)的連接，同時(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)容輸出到指

12、定的文件中去(假脫機(jī))如：Sqlspool d:b.sql 并輸入sqlspool off；& 交互式命令& 可以替代變量在執(zhí)行時(shí)，需要用戶(hù)輸入。如：SQLselsct * from emp where job=&工作；顯示和設(shè)置環(huán)境的變量可以用來(lái)控制輸出的各種格式，set show 如果希望永久的保存相關(guān)的設(shè)置，可以去修改glogin.sql腳本1) Linsesize 設(shè)置顯示行的寬度，默認(rèn)是80個(gè)字符Sqlshow linesizeSqlset linesize 902) Pagesize 設(shè)置每頁(yè)顯示的行數(shù)目，默認(rèn)是14，用法和linesize一樣用戶(hù)管理用戶(hù)管理 Create use

13、r 創(chuàng)立用戶(hù)創(chuàng)立用戶(hù)必須用DBA權(quán)限，或者擁有alter user系統(tǒng)權(quán)限，密碼只能以字母開(kāi)頭, 新創(chuàng)立的用戶(hù)沒(méi)有任何權(quán)限，不能登錄數(shù)據(jù)庫(kù)，需要授權(quán)。Create user abc identified by m123；-創(chuàng)立用戶(hù)名為abc，密碼為m123的用戶(hù)Password 修改密碼給自己修改密碼可以直接使用Sqlpassword 用戶(hù)名如果給別人修改密碼那么需要具有dba的權(quán)限，或是擁有alter user的系統(tǒng)權(quán)限Sqlalter user 用戶(hù)名 identitied by 新密碼Drop user刪除用戶(hù)在刪除用戶(hù)時(shí)，如果要?jiǎng)h除的用戶(hù)，已經(jīng)創(chuàng)立了表，那么就需要在刪除時(shí)帶一個(gè)參數(shù)ca

14、scade，指刪除該用戶(hù)下面所有的表，一般以dba的身份去刪除某個(gè)用戶(hù)，如果用其他用戶(hù)去刪除用戶(hù)那么需要具有drop user的權(quán)限SqlDrop user 用戶(hù)名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) 如何使用戶(hù)可以管理其他用戶(hù)的表，Select查詢(xún)、i

15、nsert插入、update修改、delete刪除、all全部、create index；只有sys、system和表的創(chuàng)立者才可以給其他用戶(hù)授予表的管理權(quán)限(假設(shè)emp為Scott用戶(hù)的表)；Grant select on emp to abc；-Scott用戶(hù)將emp表的查詢(xún)權(quán)限授予給abc用戶(hù)Grant update on emp to abc；-Scott用戶(hù)將emp表的修改權(quán)限授予給abc用戶(hù)Grant all on emp to abc；-Scott用戶(hù)將emp表的所有權(quán)限授予給abc用戶(hù)3) 回收權(quán)限命令revoke，誰(shuí)授予的權(quán)限誰(shuí)收回Revoke select on emp t

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

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

18、輸入錯(cuò)誤密碼3次，鎖定的時(shí)間2天Sqlcreate profile lock_a limit failed_login_attempts 3 password_lock_time 2;Sqlalter user abc profile lock_a;-給用戶(hù)abc指定profile選項(xiàng)給賬戶(hù)解鎖Sqlalter user abc account unlock;-給用戶(hù)abc解鎖終止口令為了讓用戶(hù)定期修改密碼可以使用終止口令的指令來(lái)完成，同樣這個(gè)命令也需要dba身份來(lái)操作。案例:創(chuàng)立一個(gè)profile文件，要求該用戶(hù)每隔10天要修改自家的登錄密碼，寬限期為2天。Sqlcreate profile

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

20、it password_life_time 10 password_grace_time 2 password_reuse_time 10;分配給用戶(hù)：Sqlalter user abc profile password_h;刪除profile文件刪除profile文件，如果該profile已經(jīng)分配給客戶(hù)需在后面加參數(shù)cascade，所有受到該profile文件限制的用戶(hù)全部解除該限制。Sqldrop profile password_h cascade;數(shù)據(jù)類(lèi)型字符型Char 定長(zhǎng)字符char(10) 定長(zhǎng)字符 最大2000字符，字符數(shù)缺乏用空格缺乏，查詢(xún)速度快，適合定長(zhǎng)數(shù)據(jù)，如身份證、 號(hào)

21、碼等；Varchar2 變長(zhǎng)字符varchar(20) 變長(zhǎng)字符 最大4000字符；Clob 字符型大對(duì)象clob(30000) 字符型大對(duì)象，最大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í)間可以精確到更小的單位圖片類(lèi)型Blob 可存儲(chǔ)圖片、視頻、聲音 blob 二進(jìn)制數(shù)據(jù)，最大4G，可以存圖片、視頻、聲音等；表格管理創(chuàng)立表(注意字母大小寫(xiě)，一般為

22、大寫(xiě))學(xué)生表：create table student(，-學(xué)生xh number(4), -學(xué)號(hào)xm varchar2(20),-姓名xb char(2),-性別birthday date,-出生日期val number(7,2)-獎(jiǎng)學(xué)金);班級(jí)表：create table class(-班級(jí)classid(4),-班級(jí)編號(hào)name(20),-班級(jí)名稱(chēng));添加一個(gè)字段alter table student add(classid number(2);修改字段的長(zhǎng)度alter table student modify (xm varchar2(30);刪除一個(gè)字段alter table stu

23、dent drop column sal；實(shí)際過(guò)程中不要輕易刪除字段。修改表的名字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) va

24、lues (1004,李四,女);插入空值insert into student (xh,xm,sex,birthday) values (1005,楊慧,女，null);查詢(xún)空值select * from student where birthday is null；-查詢(xún)空值select * from student where birthday is not null；-查詢(xún)非空值修改字段update student set sex=男 where xh=1004; -修改一個(gè)字段update student set sex=男，birthday=1980-04-01 where xh=

25、1004; -修改多個(gè)字段例如：update student 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)還在，寫(xiě)日志，可以恢復(fù)的(回滾)，速度慢delete from student where xh=1005; -刪除一條記錄drop table student；-刪除表結(jié)構(gòu)和數(shù)據(jù)truncate table student;-刪除表中的所有記錄，表結(jié)構(gòu)還在，不寫(xiě)日志

26、，無(wú)法找回刪除的記錄，速度快；savepoint AA；-創(chuàng)立回滾點(diǎn)AArollback to AA;-回滾到AA點(diǎn)，需先創(chuàng)立回滾點(diǎn)簡(jiǎn)單表查詢(xún)查詢(xún)表的結(jié)構(gòu)Sqldesc dept；-查看dept表的結(jié)構(gòu)查詢(xún)表的指定列和所有列Select * from emp;-查詢(xún)?nèi)苛蠸elect ename，sal，job from emp；-查詢(xún)指定列注意：查詢(xún)過(guò)程中盡量少用查詢(xún)?nèi)苛校怨?jié)省查詢(xún)時(shí)間，提高查詢(xún)效率Distinct 取消重復(fù)行Select distinct deptno，job from emp；瘋狂復(fù)制Insert into users(userid,username,userpass

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

28、e ，sal*12 from emp;-查詢(xún)年工資Select 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；-將查詢(xún)出的值和字符串連接在一起Whe

29、re 查詢(xún)條件1948.78+120子句案例1：如何顯示工資高于3000的員工Select ename,sal from emp where sal3000;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排序語(yǔ)句Order by 默認(rèn)升序(asc)，降序(desc) 。如何按照工資的從低到高的順序

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

31、ame,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用于對(duì)查詢(xún)的結(jié)果分組統(tǒng)計(jì),分組查詢(xún)中分組字段必須出現(xiàn)在查詢(xún)結(jié)果中Having子句用于限制分組顯示結(jié)果顯示每個(gè)部門(mén)的平均工資和最高工資Sele

32、ct avg(sal),max(sal),deptno from emp group by deptno;顯示每個(gè)部門(mén)的每種崗位的平均工資和最低工資Select avg(sal),min(sal),deptno，job from emp group by deptno，job;顯示平均工資低于2000的部門(mén)號(hào)和它的平均工資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語(yǔ)句中同時(shí)包含

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

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

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

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

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

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

39、ere deptno=3);Select ename，sal，deptno from emp where sal(select max(sal) from emp where deptno=3);-效率比第一種高使用any操作符查詢(xún)顯示工資比部門(mén)30的任意一個(gè)員工的工資高的員工的姓名、工資和部門(mén)號(hào)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

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

41、sal from emp a1,(Select avg(sal) mysal,deptno from emp group by deptno) a2 where a1.deptno=a2.deptno and a1.sala2.mysal;分頁(yè)查詢(xún)Oracle分頁(yè)方式有三種：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

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

43、d 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;用查詢(xún)結(jié)果創(chuàng)立新表Create table mytable id ,name,sal,job,deptno as select empno,ename,

44、sal,job,deptno from emp ;創(chuàng)立表的同時(shí)把數(shù)據(jù)打入新表中合并查詢(xún)集合操作符號(hào) union ，union all，intersect，minus ，查詢(xún)比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使用該操作用于

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

46、nection ct=DriveManager.getConnection(,用戶(hù)名,密碼);事物set transaction read only;-設(shè)為只讀事物使用子查詢(xún)插入數(shù)據(jù)使用子查詢(xún)插入數(shù)據(jù)時(shí)，一條Insert語(yǔ)句可以插入大量的數(shù)據(jù)，當(dāng)處理行遷移或者裝載外部表的數(shù)據(jù)到數(shù)據(jù)庫(kù)時(shí)，可以使用子查詢(xún)來(lái)插入數(shù)據(jù)。insert into kkk (myid,myname,mydept) select empno,ename,deptno from emp where deptno=10;使用子查詢(xún)插入數(shù)據(jù)使用update語(yǔ)句更新數(shù)據(jù)時(shí)，既可以使用表達(dá)式或者數(shù)值直接修改數(shù)據(jù)，也可以使用子查詢(xún)修改數(shù)

47、據(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語(yǔ)句組成，該組的dml語(yǔ)句要么全部成功，要么全部失敗，dml語(yǔ)句指數(shù)據(jù)操作語(yǔ)言，即增、刪、改語(yǔ)句，沒(méi)有查詢(xún)語(yǔ)句。如：網(wǎng)上轉(zhuǎn)賬就是典型的要用事物來(lái)處理，用以保證數(shù)據(jù)的一致性。事物的幾個(gè)重要操作：1、設(shè)置保存點(diǎn)，savepoint a2、取消局部事物， rollback to

48、a3、取消全部事物， rollback函數(shù)字符函數(shù)lower(char):將字符串轉(zhuǎn)化為小寫(xiě)的格式select lower(ename) from emp;-把所有人的名字按小寫(xiě)顯示，多個(gè)逗號(hào)分開(kāi)upper(char):將字符串轉(zhuǎn)化為大寫(xiě)的格式select upper(ename) from emp;-把所有人的名字按大寫(xiě)顯示，多個(gè)逗號(hào)分開(kāi)length(char):返回字符串的長(zhǎng)度select * from emp where length(ename)=5;-顯示正好有5個(gè)字符的名字substr(char,m,n):取字符串的子串select substr(ename,1,3) from e

49、mp;-顯示所有姓名的前三個(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è)字符為大寫(xiě)顯示所有姓名1) 首字母大寫(xiě)顯示姓名Select upper(substr(ename,1,1) from emp;2) 從第二個(gè)字母小寫(xiě)顯示姓名Select lower(substr(ename,2,length

50、(ename)-1) from emp;3) 兩者合并Select upper(substr(ename,1,1)|lower(substr(ename,2,length(ename)-1) from emp;-以首個(gè)字符為大寫(xiě)顯示所有姓名數(shù)學(xué)函數(shù)數(shù)學(xué)函數(shù)的輸入?yún)?shù)和返回值的數(shù)據(jù)類(lèi)型都是數(shù)字類(lèi)型的，數(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ù)，那么四舍五入到

51、小數(shù)點(diǎn)的m位前；select round(sal,1) from emp;trunc(n,m) 截取數(shù)字該函數(shù)用于截取數(shù)字，如果省掉m，就截去小數(shù)局部，如果m是正數(shù)就截取到小數(shù)點(diǎn)的m位后，如果m是負(fù)數(shù)，那么截取到小數(shù)點(diǎn)的前m位；select trunc(sal,1) from emp;mod(m,n) 取摩如mod(10,2)值為0,mod(10,3)值為1，可用函數(shù)測(cè)試表dual測(cè)試；floor(n) 向下取最大整數(shù)返回小于或是等于n的最大整數(shù),如floor(55.66)返回55；select floor(sal,1) from emp;ceil(n) 向上取最小整數(shù)返回大于或是等于n的最小

52、整數(shù)如ceil(55.66)返回56select ceil(sal,1) from emp;案例：顯示在一個(gè)月為30天的情況所有員工的日薪金，忽略余數(shù)。Select trunc(sal/30),ename from emp;Select floor(sal/30),ename from emp;其他數(shù)學(xué)函數(shù)：abs(n) 返回?cái)?shù)字n的絕對(duì)值select abs(-13) from dual; -求-13的絕對(duì)值，dual是函數(shù)測(cè)試表acos(n) 返回?cái)?shù)字的反余弦值asin(n) 返回?cái)?shù)字的反正弦值atan(n) 返回?cái)?shù)字的反正切cos(n) 返回?cái)?shù)字的余弦值exp(n) 返回e的n次冪log(