Oracle操作語句大全

1、 Oracle 操作語句大全1.desc(描述） emp 描述emp這張表2.desc dept 部門表3.desc salgrade 薪水等級4.select *from table 查找表中的元素5.dual 是系統(tǒng)中的一張空表6.select *from dual7.select sysdate from dual 取出系統(tǒng)時間8.select ename,sal*12 "annul sal"(取的別名） from emp; 查找用戶姓名和用戶的年薪9.任何含有空值的數(shù)學(xué)表達(dá)式的值都是空值select ename,sal*12+comm from emp;10.sel

2、ect ename|sal from emp 其中的|相當(dāng)于將sal全部轉(zhuǎn)化為字符串11.表示字符串的方法select ename |'ajjf' from emp;12.如果其中有一個單引號就用2個單引號來代替他select ename|'sakj' 'lds'from emp;13.select distinct deptno from emp (去除部門字段中重復(fù)的部分,關(guān)鍵字distinct)14.select distinct deptno,job from emp;(去除這2個字段中重復(fù)的組合)15.select *from dept

3、 where deptno=10; 取出條件(取出部門編號為10的記錄)16.select * from emp where ename='CLIRK' 取出部門中姓名為clirk的記錄(注意取出過程中ename用單引號隔開)17.select ename,sal from emp where sal>1500; 取出部門中薪水大于1500的人的姓名18.select ename,sal,deptno from emp where deptno<> 10 取出部門中的部門號不等于10的19.select ename,sal,deptno from emp wh

4、ere ename>'CBA' 取出部門中員工名字大于CBA的員工(實際比較的是ACIIS碼)20.select ename,sal from emp where sal between 800 and 1500 select ename,sal from emp where sal>=800 and sal<=1500; (取出800和1500之間的數(shù))21.select ename,sal,comm from emp where comm is null (選出其中的空值) select enmae,sal,comm from emp where comm

5、 is not null(選出其中的非空值)22.select ename,sal,comm from emp where sal in (800,1500,2000);取出這3者之中的 select ename,sal,comm from emp where ename in('simth');23.select ename,sal,hiredate from emp where hiredata>'3-04月-81'宣傳符合條件的日期24.select ename,sal,from emp where sal>1000 or deptno=10;

6、 找出工資薪水大于1000或者部門號等于10的員工25.select ename,sal from emp where sal not in(500,1000); 查找薪水不在500到1000的員工姓名和月薪26.select ename,sal from emp where ename like '%ALL%' select ename,sal from emp where ename like '_%A%' 查找姓名中含有ALL的客戶信息，一個橫線代表一個通配符27.select ename,sal from emp where ename like 

7、9;_%$%' escape '$' 自己指定轉(zhuǎn)易字符 select ename,sal from emp where ename like '_%' 查找中間含有%相匹配的客戶信息，運用轉(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 <>1000 order by sal desc 按照查詢條件來

8、查詢，并排序（asc升序排列）30.select ename,sal*12 from emp where ename not like '_%A%' and sal>800 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 '_%a%' 找出ename 中所有的含有a的字符33.select substr(ename,2,3) form emp 從第2個

9、字符開始截取3個字符34.select chr(65) from dual; 將65轉(zhuǎn)化為字符35.select ascii('A') from dual 將ACSII碼轉(zhuǎn)化為字符串36.select round(23.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,9999') form emp L代表本地

10、字符38.select hiredate from emp select to_char(hiredate,'YYYY-MM-DD HH:MI:SS) from emp; 時間格式的顯示 select to_char(sysdate,'YYYY-MM-DD HH:MI:ss) from dual; 十二小時制顯示系統(tǒng)時間 select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS) from dual 二四小時制顯示系統(tǒng)時間39.select ename,hiredate from emp where hiredate > to_d

11、ate('2005-2-3 12:32:23','YYYY-MM-DD HH:MI:SS');40 select sal from emp where sal>to_number('$1,250.00','$9,999.99'); 取出比它大的一切字符串（把特定格式的數(shù)字轉(zhuǎn)化成字符）41 select ename,sal+nvl(comm,0) from emp; 講comm值為空的用0來替換，單行函數(shù)（以一條記錄為條件）一條對一條42.select Max(sal) from emp; select Min(sal) fr

12、om emp; select avg(sal) from emp; select sum(sal) from emp; select count(*) from emp; 查看表中一共有多少條記錄 select count(*) from emp where deptno=10; 查找部門10一共有多少人；43.select avg(sal),deptno from emp group by deptno; 按部門號進(jìn)行分組 select deptno,job,max(sal) from emp group by job,deptno; 按工作和部門號進(jìn)行分組；44.select ename

13、from emp where sal=(select max(sal) from emp); 子查詢，查找部門中薪水最高的員工姓名45.group by 注意：出現(xiàn)在select列表中的字段，如果沒有出現(xiàn)在組函數(shù)中必須出現(xiàn)在group by子句中46.select avg(sal),deptno from emp group by deptno having avg(sal)>2000; 選出部門中平均薪水大于2000的部門，47.select * from emp where sal>100 group by deptno having .order by. 先取數(shù)據(jù)-過濾數(shù)據(jù)-

14、分組-對分組限制-排序48.select avg(sal) from emp where sal>2000 group by deptno having avg(sal)>1500 order by avg(sal) desc; 查找部門中平均薪水打印2000的員工并按部門號進(jìn)行排序，查詢分組后的平均薪水必須大于1500，查詢結(jié)果按平均薪水從低到高排列49.select ename from emp where sal>(select avg(sal) from emp); 查找出員工中薪水位于部門平均薪水之上的所有員工50.select ename,sal from emp

15、 join(select max(sal) max_sal from emp group by deptno) t on(emp.sal=t,max_sal and emp.deptno=t.deptno); 查找每個部門中薪水最高的51.select e1.ename,e2.ename from emp e1,emp e2 where e1.mgr=e2.empno; 表的自連接52.select dname,ename from emp cross join dept 交叉連接，笛卡爾SQL99中的新語法53.select ename,dname from emp join dept on

16、(emp.deptno=dept.deptno);54.select ename,dname from emp join dept using(deptno); 查找emp和dept表中deptno相同的部分。55.select ename,dname,grade from emp e join dept d on(e.deptno=d.depno) join salgrade s(e.sal between s.losal and s.hisal) (三表查找） where ename not like '_%A%'56.select e1.ename,e2.ename fr

17、om emp e1 join emp e2 on(e1.mgr=e2.deptno); 表的自連接57.select e1.ename,e2.ename from emp e1 left join emp e2 on(e1.mgr=e2.deptno) 左外表連接 select ename,dname from emp e right join dept d on(e.deptno=d.deptno)右外連接 select ename,dname from emp e full join dept d on(e.deptno=d.deptno)全連接58.求部門中薪水最高的 select en

18、ame,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) from(select deptno,ename,grade,from emp join salgrade s on(emp.sal between s.losal and s.hisal)t group by deptno;60.查找雇員中哪些是經(jīng)理人 s

19、elect ename from emp where empno in(select mgr from emp);61.select distinct e1.sal from emp e1 join emp e2 on(e1.sal<e2.sal); 自連接(不用組函數(shù)求出最高薪水） select distinct sal from emp where not in (select ename from e1.sal from emp e1 join emp e2 on(e1.sal<e2.sal);62.select deptno from (select avg(sal) ma

20、x_sal deptno from emp group by deptno) where max_sal=(select max(avg_sal) from (selectavg(sal) avg_sal deptno from emp group by deptno); 查找部門中部門薪水最大的部門號63.求平均薪水最大的部門的部門編號 select deptno,avg_sal from(select avg(sal) avg_sal,deptno from emp group by deptno)where avg_sal=(select max(avg(sal) from emp gr

21、oup by deptno);DML語句：更、刪、改、查創(chuàng)建權(quán)限， conn sys/admin as sysdba grant create table,create view to scott;首先在C:下面建個文件夾備份文件1.createNewUser方法1.-backup scott exp2.create user(創(chuàng)建用戶）用超級管理員模式進(jìn)入 create user yun identified by kang1234 default tablespace users quota 10M on users; grant create session,create table,c

22、reate view to kafei（給kafei這個用戶授予權(quán)限）3.import the data(導(dǎo)入備份數(shù)據(jù)） imp2.insertinsert into dept values (50,'game','bj') 插入一條記錄insert into dept2 (deptno,dname) values (78,'games'); 插入指定的幾條記錄insert into dept2 select *from dept 插入指定的表（表結(jié)構(gòu)要一樣）rollback; 回退create table emp2 as select * fr

23、om emp; 創(chuàng)建數(shù)據(jù)庫表2來備份emp這張表3.update emp2 set sal=sal*12 where deptno=10; update的用法4.delete from dept2 where deptno<25 ; 刪除語句的用法DDL語言1.創(chuàng)建表:create table t(a varchar2(10);2.drop table t 刪除表mit 所有的提交，所有修改都結(jié)束了。對于rollback無效，一個事務(wù)開始于第1條DML語句碰到執(zhí)行DDL DCL語句事務(wù)自動提交 對于rollback無效建表語句建學(xué)生信息表：create table stu(id numb

24、er(6),name varchar2(20) constraint stu_name_nn not null,sex number(1),age number(3),sdate date,grade number(2) default 1,class number(4),email varchar2(50) unique (唯一約束);非空 唯一 主鍵 外鍵 chickcreate table stu(id number(6) primary key,(主鍵約束)name varchar2(20) constraint stu_name_nn not null,(非空約束)sex numbe

25、r(1),age number(3),sdate date,grade number(2) default 1,class number(4),email varchar2(50),constraint stu_name_uui unique(email,name) 組合性約束);主鍵約束方法二create table stu(id number(6),name varchar2(20) constraint stu_name_nn not null,(非空約束)sex number(1),age number(3),sdate date,grade number(2) default 1,c

26、lass number(4) references class(id),(參考class 這張表，參考字段)email varchar2(50),constraint stu_id_pk primary key(id),constraint stu_name_uui unique(email,name) 組合性約束);外鍵約束create table class(id number(4) primary key,(id為被參考字段,被參考的字段必須是主鍵)name varchar2(20) not null)create table stu(id number(6),name varchar2

27、(20) constraint stu_name_nn not null,(非空約束)sex number(1),age number(3),sdate date,grade number(2) default 1,class number(4)email varchar2(50),constraint stu_class_fk foreign key(class) references class(id),constraint stu_id_pk primary key(id),constraint stu_name_uui unique(email,name) 組合性約束);像外鍵中插入關(guān)

28、鍵字，1.insert into class values(1000,'c1');2.insert into stu(id,name,class,email) values(1,'a',1000,'a');3.alter table stu add(addr varchar(20);添加表的結(jié)構(gòu)4.alter table stu drop(addr); 刪除表結(jié)構(gòu)5.alter table stu modify(addr varchar2(150);修改精度6.alter table stu drop constraint stu_class_f

29、k; 刪除約束條件7.alter table stu add constraint stu_class_fk forengn key(class) references class(id),添加約束條件查找當(dāng)前用戶下有哪些表和哪些視圖及哪些約束8.select table_name from user_names9.select view_name from view_names10.select constraint_name,table_name from user_constraints;desc dictionary數(shù)據(jù)字典表desc user_tables當(dāng)前用戶下面有多少張表sel

30、ect table_name from user_tables; 查找當(dāng)前用戶有多少張表索引：創(chuàng)建索引create index idx_stu_email on stu(email);drop index idx_stu_email;查找索引select index_name from user_indexes;索引讀的速度快了，插入速度變慢view 視圖視圖贈加了維護(hù)的量序列：create table arcticle(id number,title varchar2(1024),cont long);序列的創(chuàng)建sequence產(chǎn)生獨一無二的序列，而且是oracle獨有的create seq

31、uence seq;select seq.nextval from dual; 查找序列號insert into arcticle values(seq.nextval,'a','b');往表中插入序列數(shù)據(jù)庫設(shè)計的3范式第一范式： 設(shè)計任何表都要有主鍵，列不可分第二范式： 如果有2個主鍵的話，不能存在部分依賴第三范式， 不能存在傳遞依賴PL-sql例子1：SQL> set serveroutput on;SQL> begin(必要的-程序開始執(zhí)行)2 dbms_output.put_line('hello world');3 end;

32、(結(jié)束)4 /例子2：SQL> declare2 v_name varchar2(20);3 begin4 v_name:='myname'5 dbms_output.put_line(v_name);6 end;7 /myname例子3：SQL> declare2 v_num number:=0;3 begin4 v_num:=2/v_num;5 dbms_output.put_line(v_num);6 end;7 /declare*ERROR 位于第 1 行:ORA-01476: 除數(shù)為 0ORA-06512: 在line 4例子4：declarev_num

33、number:=0;beginv_num:=2/v_num;dbms_output.put_line(v_num);exceptionwhen others thendbms_output.put_line('error');end;/變量聲明的規(guī)則1.變量名不能夠使用保留字，如from,select等2.第一字符必須是字母。3.變量名最多包含30個字符4.不要與數(shù)據(jù)庫的表或者列同名5.每一行只能聲明一個變量常用變量類型1. binary_interger,整數(shù)，主要用來計數(shù)，而不是用來表示字段類型2. number 數(shù)字類型3. char 定長字符串4. varchar2 變

34、長字符串5. date 日期6.long 長字符串，最長2GB7.boolean 布爾類型，可以取true false 和null的值例5：declare v_temp number(1); v_count binary_integer:=0; 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 null:='myname'begin dbms_output.put_li

35、ne('v_temp value:'|v_temp);end;用-可以注釋一行例6：declare v_empno number(4); v_empno2 emp.empno%type; v_empno3 v_empno2%type;begin dbms_output.put_line('test');end;例7table變量類型set serveroutput on;declare type type_table_emp_empno is table of emp.empno%type index by binary_integer; v_empnos ty

36、pe_table_emp_empno;begin v_empnos(0):=7369; v_empnos(2):=7869; v_empnos(-1):=9999; dbms_output.put_line(v_empnos(-1);end;例8Record 變量類型set serveroutput on;declare type type_record_dept is record ( deptno dept.deptno%type, dname dept.dname%type, loc dept.loc%type ); v_temp type_record_dept;begin v_tem

37、p.deptno:=50; v_temp.loc:='aaaa' v_temp.loc:='bj' dbms_output.put_line(v_temp.deptno|' '|v_temp.dname);end;例9：使用%rowtype聲明record變量（表結(jié)構(gòu)的變化同時也能代理儲存過程的變化）set serveroutput on;declare v_temp dept%rowtype;begin v_temp.deptno:=50; v_temp.loc:='aaaa' v_temp.loc:='bj'

38、dbms_output.put_line(v_temp.deptno|' '|v_temp.dname);end;例10；declarev_name emp.ename%type;v_sal emp.sal%type;beginselect ename,sal into v_name,v_sal from emp where empno=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

39、_sal from emp where empno=7369;dbms_output.put_line(v_name|' '|v_sal);end;dbms_output.put_line(v_name|' '|v_sal);end;例12：declarev_deptno dept.deptno%type:=50;v_dname dept.dname%type:='aaaa'v_loc dept.loc%type:='bj'begininsert into dept2 values(v_deptno,v_dname,v_loc);

40、commit;end;例13：declarev_deptno emp2.deptno%type:=50;v_count number;beginupdate emp2 set sal=sal/2 where deptno=v_deptno;dbms_output.put_line(sql%rowcount |'條記錄被影響');(sql為關(guān)鍵字，代表上一條語句commit;end;/例14：declarev_deptno emp2.deptno%type:=50;v_count number;begin-update emp2 set sal=sal/2 where deptn

41、o=v_deptno; select deptno into v_deptno from emp2 where empno=7369;dbms_output.put_line(sql%rowcount |'條記錄被影響');(sql為關(guān)鍵字，代表上一條語句commit;end;/例15declarev_deptno emp2.deptno%type:=50;v_count number;begin-update emp2 set sal=sal/2 where deptno=v_deptno;-select deptno into v_deptno from emp2 wher

42、e empno=7369;select count(*) into v_count from emp2; （select必須和into一起使用）dbms_output.put_line(sql%rowcount |'條記錄被影響');commit;end;/PL/SQL里面執(zhí)行DDL語句beginexecute immediate 'create table T(nnn varchar2(20) default ''aaa'')'end;PL/SQL的分支語句：declare v_sal emp.sal%type;begin se

43、lect sal into v_sal from emp where empno=7369; if(v_sal<1200) then dbms_output.put_line('low'); elsif(v_sal<2000) then dbms_output.put_line('middle'); else dbms_output.put_line('high'); end if;end;pL/Sql循環(huán)declare i binary_integer:=1;begin loop dbms_output.put_line(i); i

44、:=i+1; exit when(i>=11); end loop;end;PL/SQL for循環(huán)beginfor k in 1.10 loop dbms_output.put_line(k);end loop;for k in reverse 1.10 loop dbms_output.put_line(k);end loop;end;exception 捕獲異常declare v_temp number(4);begin select empno into v_temp from emp where deptno=10;exception when too_many_rows th

45、en dbms_output.put_line('太多記錄了');when others then dbms_output.put_line('error');end;沒有數(shù)據(jù)錯誤declare v_temp number(4);begin select empno into v_temp from emp where deptno=2222;exception when no_data_found then dbms_output.put_line('沒數(shù)據(jù)');when others then dbms_output.put_line(

46、9;error');end;/錯誤處理create table errorlog(id number primary key,errcode number,errmsg varchar2(1024),errdate date);create sequence seq_errorlog_id start with 1 increment by 1;declarev_deptno dept.deptno%type:=10;v_errcode number;v_errmsg varchar2(1024);begindelete from dept where deptno=v_deptno;

47、exceptionwhen others then rollback; v_errcode:=SQLCODE; v_errmsg:=SQLERRN; insert into errorlog values(seq_errorlog_id.nextval,v_errcode,v_errmsg,sysdate); commit;end;游標(biāo)declare cursor c is select * from emp; v_emp c%rowtype; begin open c; fetch c into v_emp; -(取游標(biāo)的第一個值插入v_emp，在不斷的循環(huán)) dbms_output.put_line(v_emp.ename); close c; end;例子declare cursor c is select * from emp; v_emp c%rowtype; begin open c; loop fetch c into v_emp; exit when(c%notfound); dbms_output.put_line(v_emp.ename); end loop;