Oracle數(shù)據(jù)庫SQL語句大全

1、Oracle SQL語句大全 2009-0443 15:39 1desc描述）emp 描述emp這張表2. desc dept部門表3. desc salgrade薪水等級4. select *from table杏找表屮的元索5. dual是系統(tǒng)中的一張空表6. sclcct *from dual7. select sysdate from dual 取出系統(tǒng)時(shí)間8. select ename,sal*12 "annul sal"（取的別名）from emp; ft找用戶姓名和用戶的年薪9. 任何含有空值的數(shù)學(xué)表達(dá)式的值都是空值select enamesal* 12+co

2、mm from emp;10. select enamellsal from emp其屮的II相當(dāng)于將sal全部轉(zhuǎn)化為字符串II 農(nóng)示字符串的方法select ename irajjf from emp;12如果其屮有一個(gè)單引號就用2個(gè)單引號來代替他 select enameHsakj* IdsTrom emp; .select distinct deptno from emp(去除部門字段屮重復(fù)的部分,關(guān)鍵字distinct)14. select distinct deptno,job from emp;(去除這2個(gè)字段中垂復(fù)的組合)15. select *from dept where d

3、eptno=10;取出條件(取出部門編號為10的記錄)16. select * from emp where ename='CLIRK*;取出部門屮姓名為clirk的記錄(注意取出過程屮 ename用單引號隔開)17. select ename.sal from emp where sai>l5(X):取出部門中薪水人于 1500的人的姓名IS.select enamesahdeptno from emp where deptnoo 1()取岀部門中的部門號不等于 1()的19. select ename,sahdeptno from emp where enamo'CB

4、A* 取出部門屮員工名字人 1 CBA 的員 工(實(shí)際比較的是ACI1S碼)2O.select ename.sal from emp where sal between 800 and 1500 select ename,sal from emp where sal>=800 and sal<=15()();(取出800和 1500之間的數(shù))21 .select enamc,sal,comm from emp where comm is null (選出其中的空值)select enmaesakcomm from emp where comm is not null(選岀其屮的非空

5、值)22. select ename,saLcomm from emp where sal in (800500,2000);取出這3者之屮的select ename,sahcomm from emp where ename in('simN);23. select ename.sal,hiredate from emp where hiredata>*3-04月宜傳符合條件的 U 期找出丁資薪水人T 1（X）0或者杏找薪水不在500到1000的員杳找姓名屮含仃ALL的客戶24. select ename,sahfrom emp where sal> 1 (XX) or d

6、eptno=l(); 部門號等于1()的員工25. select ename.sal from emp where sal not in(5()0J0(X); 工姓名和月薪26. select ename,sal from emp where ename like %ALL%; select ename,sal from emp where ename like倍息，一個(gè)橫線代衣個(gè)通配符27. select ename,sal from emp where ename like '_%$% escape '$： IlL指定轉(zhuǎn)易字符select ename,sal from em

7、p where ename like金找中間含彳J %相匹配的客戶信息，運(yùn)用轉(zhuǎn)易字符28. select from dept order by deptno對表中元素按部門號排序select *from dept order by deptno desc默認(rèn)為升序，可以用 desc按降序29. select ename,sal from emp where sal o 1 (X)() order by sal desc按照杏詢條件來杏詢，并扌II序(asc升序排列)3O.select ename.sal*l2 from emp where ename not likeand sal>80

8、0 order by sal desc31 .select lower(ename) from emp將ename都轉(zhuǎn)化為小lower 函數(shù)能將字母轉(zhuǎn)化為小32. select ename from emp where lower(ename) like找出 enume 中所育的含有 a 的于符33. select substr(ename,2,3) form emp從第2個(gè)字符開始截取3個(gè)字符34. select chr(65) from dual;將65轉(zhuǎn)化為字符35. select asciiCA1) from dual將 ACSII 碼轉(zhuǎn)化為字符串36. select round(23

9、.565)from dual四舍 入36. select round(23,4565,2)from dual 四舍五入到第：位37. select to_char(sal/$99.999.9999,) from emp 按指定格式輸出select to_char(sal/L99,999,9999t) form emp L 代表木地字符38.select hiredate from empselect to_char(hiredate/YYYY-MM-DD HH:MI:SS) from emp; select to_char(sysdate/YYYY-MM-DD HII:MI:ss) from

10、dual;時(shí)間格式的顯示 十1.小時(shí)制顯示系統(tǒng)時(shí)間select to.char(sysdate：YYYY-MM-DD HH24:M1:SS) from dual 統(tǒng)時(shí)間二四小時(shí)制顯示系39.select ename Jiiredate from emp where hi redate > to_date(2(X)5-2-3 12:32:23*/¥ YY Y-MM-DDHH:MI:SSf);40 select sal from emp where sal>tonumbcr(*$ 1,250.007$9,999.99*);取出比它人的切字符串 (把特定格式的數(shù)字轉(zhuǎn)化成字符)4

11、1 select ename,sal+nvl(comm,0) from emp;講 comm 值為空的川0來替換，單行函數(shù)(以一條記錄為條件)一條對一條42. select Max(sal) from emp;select Min(sal) from emp;select avg(sal) from emp;select sum(sal) from emp;select count(*) from emp;資看表屮一共有多少條記錄select count(*) from emp where deptno= 10;倉?戈部門 1()一共右多少人：43. select avg(sal),deptn

12、o from emp group by deptno;按部門號進(jìn)行分組select deptno job.max(sal) from emp group by job,deptno;按丁作和部門號進(jìn)彳 j分組：44. seiect ename from emp where sal=(select max(sal) from emp); 了查詢,查找部門屮薪水最高 的員工姓名45, group by注意：出現(xiàn)在select列表屮的字段，如果沒仃出現(xiàn)在紐函數(shù)屮必須出現(xiàn)在group by 子句中46. selecl avg(sal),deptno from emp group by deptno h

13、aving avg(saI)>2000; 選出部門屮平均 薪水大于2000的部門，47. select from emp where sal>IO() group by deptno havingorder by先取數(shù)據(jù)-過濾數(shù)據(jù)一分組-對分組限制排序4&select avg(sal) from emp where sal>2()(X) group by deptno having avg(saI)>15(X) order by avg(sal) desc;杏找部門屮平均薪水打卬2000的員T.并按部門號進(jìn)行扌U序，杏詢分組肩的平均薪水必須大 于1500,杏詢結(jié)

14、果按平均薪水從低到高排列49. select enamc from emp where sal>(select avg(sal) from emp);杳找出員工屮薪水位于部門平均薪水之上的所有員工50. select ename.sal from emp join(select max(sal) max_sal from emp group by dept no) t on(emp.sal=t,max_sal and emp.deptno=t.deptno);杳找每個(gè)部門中薪水最高的51. select el.ename,e2.ename from emp el.emp e2 where

15、 el.mgr=e2.empno;表ll勺連接52. select dname.ename from emp cross join dept交義連接，笛 匸爾SQL99中的新語法53. selcct ename.dname from emp join dept on(emp.deptno=dept.deptno);54. select ename.dname from emp join dept using(deptno);杳找 emp 利I dep表deptno相同的部分。55. select ename,dname,grade from emp e join dept d on(e.dep

16、tno=d.depno)join salgrade s(e.sal between s.losal and s.hisal) (三表査找)where ename not like56. select eI.enamete2.ename from emp el join emp e2 on(e 1.mgr=e2.deptno); 表lYjI連接57. select e 1 .ename,e2.ename from emp el left join emp e2 on(e 1 .mgr=e2.deptno) 左夕卜表連接 select ename,dname from emp e right jo

17、in dept d on(e.deptno=d.deptno)亍夕卜連接select ename,dname from emp c full join dept d on(e.dcpmo=ddcpmo)全連接58. 求部門屮薪水最商的select ename,sal from emp join (select max(sal) max_sal, deptno from emp group by deptno) t on (emp.sal=t.max_sal and emp.deptno=t.deptno);59. 求部門屮薪水等級的平均值select deptno,avg(grade) fro

18、m(select deplno,ename,gradejrom emp join salgrade s on(emp.sal between sosal and s.hisal)" group by deptno;6().資找雇員中哪些是經(jīng)理人select ename from emp where empno in(select mgr from emp);61 .select distinct el.sal from emp el join emp e2 on(el.sal<c2.sal); | |連接(不用組函數(shù)求出最高 薪水)select distinct sal fro

19、m emp where not in (select ename from el.sal from emp el join emp e2 on(el.sal<e2.sal);62. select deptno from (select avg(sal) max_sal deptno from emp group by deptno) where max_sal=(select max(avg_sal) from (selectavg(sal) avg_sal deptno from emp gro叩by deptno);育找部門屮部門薪水最人的部門號63. 求平均薪水最人的部門的部門編號

20、select deptno,avg_sal from(select avg(sal) avgsaLdeptno from emp group bydeptnojwhere avg_sal=(select max(avg(sal) from enip group by deptno);DML語句：更、刪、改、查創(chuàng)建權(quán)限，conn sys/admin as sysdbagrant create table,create view to scott;首先在c：下面建個(gè)文件夾備份文件1 .createNewUser 7/法1 -backup scottexp2. create user(創(chuàng)建用戶)用超

21、級管理員模式進(jìn)入create user yun identified by kang 1234 default tablespace users quota I0M on users;grant create session,create (able,create view to kafei (給 kafei 這個(gè)用戶授予權(quán)限)3.import the data(導(dǎo)入備份數(shù)據(jù))imp2.insertinsert into dept values (5()Jgame,bj,)插入條記錄insert into dept2 (deptnojname) values (7&'games

22、1);插入指定的幾條記錄insert into dept2 select *from dept插入指定的表(表結(jié)構(gòu)要一樣)rollback;回退create table emp2 as select * from emp;創(chuàng)建數(shù)據(jù)庫表2來備份cmp這張表3. update emp2 set sal=sal*12 where deptno=IO; update 的川法4. delete from dept2 where deptno<25 ;刪除語句的用法DDL語言I 創(chuàng)U表:create table t(a varchar2(10);2. drop table t刪除表3. commit

23、所有的提交，所有修改都結(jié)束了。對于rollback無效，一個(gè)事務(wù)開始于第I條DML語句碰到執(zhí)行DDL DCL語創(chuàng)”務(wù)白動提交 對I' rollback無效建表語句建學(xué)生信息表：create table stu(id number(6),name varchar2(2O) constraint stu_nanie_nn not nullsex number( 1age number(3),sdate date,grade number default 1,class number(4),email varchar2(50) unique(唯一約束);非空唯一上鍵外鍵chickcreate

24、 table stu(id number(6) primary key,(丄鍵約朿)name varchar2(20) constraint stu_name_nn not null/非空約束)sex number( 1),age number(3),sdatc date,grade number(2) default 1,class numbcr(4),email varchar2(50)>constraint stu_name_uui unique(email,name)組合性約束);主鍵約束方法二create table stu(id numbcr(6),name varchar2

25、(20) constraint stu_name_nn not null(非空約束)sex number(l),age number(3),sdate date,grade number(2) default I.class number(4) references class(id),(參考 class 這張衣，參考字段)email varchar2(5O),constraislud_pk primary key(id),constraint stu_name_uui unique(email,name)組合性約束);外鍵約束create table class(id number(4) p

26、rimary key,(id為被參考字段，被參考的字段必須是上鍵)name varchar2(2O) not null)create table stu id number(6)>name varchar2(20) constraint stu_name_nn not null,(非空約束) sex number( 1),age number(3),sdate dale.grade number(2) default 1,class number(4)email varchar2(50),constraint slu_class_fk foreign key(class) referen

27、ces class(id),constraint stud_pk primary key(id),constraint stu_name_uui unique(email,name)組合性約朿)； 像外鍵中插入關(guān)鍵字,1 .insert into class values(l (XX)Jc 1 *);2.insert into stu(id,name,class,email) valuesdKMXha*);3. alter table stu addfaddr varchar(20);添加表的結(jié)構(gòu)4. alter table slu drop(addr);刪除表結(jié)構(gòu)5. alter table

28、 stu modify(addr varchar2( 150);修改精度6. alter table stu drop constraint stu_class_fk;刪除約束條件7. alter table stu add constraint sUi_class_fk forengn key(class) references class(id),添加約束條fl 查找當(dāng)前用戶下有哪些表和哪些視圖及哪些約束8. select from user_names9. select view_name from view_names10. select constraint.n

29、 from user_constraints;desc dictionary數(shù)據(jù)字典表desc user_tables當(dāng)前用戶下面有多少張表select lable_name from usejkibles;杏找片前用八右鄉(xiāng)少張衷索引：創(chuàng)建索引create index idx_stu_email on stu(email);drop index idx_stu_email;査找索引select index_name from userjndexes;索引讀的速度快了，插入速度變慢view視圖視圖贈加了維護(hù)的最序列：create table arcticle(id nu

30、mber,title varchar2(1024),cont long);序列的創(chuàng)建sequence產(chǎn)生獨(dú)一無的序列，而且是oracle獨(dú)仃的create sequence seq;select seq.nextval from dual; 找序列號insert into arcticle valuesfseq.nextval/a'Jb*);往表中插入序列數(shù)據(jù)庫設(shè)計(jì)的3范式第一范式：設(shè)計(jì)任何表都要有丄鍵，列不可分第二范式：如果令2個(gè)主鍵的話，不能存在部分依賴第三范式，不能存在傳遞依賴PL-sql例子1:SQL> set serveroutput on;SQL> begin(

31、必要的“程序開始執(zhí)行)2 dbmoutput.putjinediello world*);3 end;結(jié)束)4/例子2：SQL> declare2 v_name varchar2(20);3 begin4 v_name:=myname*;5 dbmsoutput.putine(v_n“mc);6 end;7/myname例了 3：SQL> declare2 v_num number:=O;3 begin4 v_num:=2/v_num;5 dbms.output.putine(v_num);6 end;7/declareERROR位于第I行:ORA-OI476:除數(shù)為 0ORA-0

32、6512:在 line 4例了 4：declarev_num number:=0;beginv_num:=2/v_num;dbms.output.putinc(v_num);exceptionwhen others thendbms.output.putJineC'error*);end;/變鼠聲明的規(guī)則1 .變用名不能夠使用保留字，如from,select鴿2. 第一字符必須是字母。3. 變昂名最多包含30個(gè)字符4不要與數(shù)據(jù)庫的表或者列同名5每一行只能聲明一個(gè)變昴常用變最類型l. binary_inlerger.整數(shù)，.上要用來訃數(shù)，而不定川來表示字段類型2. number數(shù)字類熨3

33、. char定長字符串4. varchar2變長字符宋5. dale 口期6.long 長字符串，瑕長2GB7.boolean布爾類型，川以取true false和null的值例5：declarevjemp number(l); v_count binaryJnteger:=O;v_sal number(7,2):=4000.00;v_date date:=sysdate;V-pi constant number(3,2):=3.14;V-Valid boolean:=false;v_name varchar2(20) not nullmyname*;begindbms_oinpiHpiH_l

34、ine('v_(emp value:1lv_temp);end;用可以注釋一行例6：declarev_empno number(4);v_empno2 emp.empno%type;v_empno3 v_empno2%type;begindbms_output.putine(tesF);end;例7table變量類型set serveroutput on;declare:ype lype_table_emp_empno is table of emp.enipno%type index by binaryjnteger; v_empnos type_table_emp.empno;be

35、ginv_empnos(0):=7369;v_empnos(2):=7869;v_empnos(-l ):=9999;dbms_outpul.putJine(v_empnos(-1);end;例8Record變?nèi)鳖愋蛃et serveroutput on;declaretype type_record_depl is record(deptno dept.deptno%type,dname dept.dname%type,loc dept.loc%type);vjemp iype_ccod_dcp(;beginvjemp.deptno:=50;vjempvjempoc:='bj：dbm

36、s_output.putjine(v_temp.deptnoir llvtemp.dname);end;例9：使用%rowtype聲明record變杲(表結(jié)構(gòu)的變化同時(shí)也能代理儲存過用的變化)set serveroutput on;declarevjemp dept%rowtype;beginvjemp.deptno:=50;vcmpoc:='aaaa'vjemp.loc'bj*;dbms_output.pul_line(v_temp.deptnoir *llv_temp.dname);end;例 1():declarev_name emp.ename%type;v_s

37、al emp.sal%type;beginselect cnamc,sal into v_name,v_sal from emp where enipno=7369;(將 ename 和 sal 的值放在 v_name 和 v_sal 里血)例11:declarev_name emp.ename%type;v_sal emp.sal%type;beginselect ename,sal into v_name,v_sal from emp where empno=7369;dbms-output.putjine(v_nameir llvsal);end;dbms_outpul.putjine(

38、v_nameir *llv_sal);end;例12： declarev_deptno dept.deptno%type:=50;v_dname dept.dname%type:=,aaaa,;v_locdeptoc%lype:='bj；begininsert into depl2 values（v_deptno,v_dname,v_loc）; commit;end;例13：declarev_deptno emp2.deptno%type:=50;v_coun（ number;beginupdate emp2 set sal=sal/2 where deptno=v_deptno;db

39、ms_output.put_line（sql%rowcount II*條記錄被影響*）；（sql為關(guān)鍵字,代表上條語句 commit;end;/例14：declarev_deptno emp2.deptno%type:=50:v_count number;begin-update emp2 set sal=sal/2 where dcptno=v_dcptno;select deptno into v_deptno from emp2 where empno=7369： dbms_output.putjine（sql%rowcount條記錄被影響）;（sql為關(guān)鍵字,代表上條語句 commit

40、;end;/例15declarev_deplno emp2.deptno%type:=50;v_counl number;begin-update emp2 set sal=sal/2 where deptno=v_dcptno;-select deptno into v_deptno from emp2 where empno=7369;select count（*） into v_count from emp2;（select 必須和 into 起使用） dbms_output.putjine（sql%rowcount條記錄被影響'）；commit;end;PL/SQL甲血執(zhí)行DD

41、L語何beginexecute immediate 'create table T(nnn varchar2(2O) default naaaM)*; end;PL/SQL的分支語句：declarev_sal emp.sal%lype;beginselect sal into v_sal from empwliere empno=7369;if(v_sal<I200) thendbms_output.putineClow);elsif(v_sal<2000) thendbms_output.putjine(,middle,);elsedbms_outputputjine(t

42、high,);end if;end;pL/Sql循環(huán)declarei binaryjnteger:=l:beginloopdbms_output.putinc(i);i:=i+l;exit when(i>=l 1);end loop;PIJSQL for 循環(huán)beginfor k in 1.10 loopdbms_outputputjine(k);end loop;for k in reverse I.10 loop dbms.output. putj ine(k);end loop:end;exception捕獲異常declarev_【cmp number(4);beginselect

43、 empno into v_temp from emp where deptno=l();exceptionwhen too_many_rows thendbms_()ulpul.pul_lineC 太多記錄 了)；when others thendbms_output.put Jine(,error,);end;沒燈數(shù)據(jù)錯(cuò)i吳declarev_(emp number(4);beginselect empno into v_temp from emp where deptno=2222;exceptionwhen no.dala.found thendbms_output.putjine(,沒

44、數(shù)據(jù)when others thendbms_output.putintCerrorJ;end:/錯(cuò)決處理create table errorlog(id number primary key,errcode number,errmsg varchar2( 1024),errdate date);create sequence seq_errorlog_id start with I increment by I;declarev_deplno dept.deptno%type:=10;v_errcode number;v_errmsg varchar2(1024);begindelete f

45、rom dept where deptno=v_deptno;exceptionwhen others thenrollback;v_errcode:=SQLCODE;v_errmsg:=SQLERRN;insert into errorlog values(scq_errorlog_id.nextvaLv_errcode,v_errmsg,sysdate);commit;end;游標(biāo)declarecursor c isselect * from emp;v_emp c%rowtype;beginopen c;fetch c into v_emp;“(取游標(biāo)的第一個(gè)值插入v_emp,在不斷的循環(huán))dbn is_output.put_Iine(v_emp.ename);close c;end;例子declarecursor c isselect * from emp;v_emp c%rowtype;beginopen c;loopfetch c into v_cmp;exit when(c%notfound);dbms_output.putine(v_cmp.enamc);end