(完整版)Oracle操作语句大全

1、Oracle操作语句大全1.desc(描述） emp描述 emp 这张表2.descdept部门表3.desc salgrade薪水等级4.select *from table查找表中的元素5.dual是系统中的一张空表6.select *from dual7.select sysdate from dual 取出系统时间8.select ename,sal*12 annul sal( 取的别名）from emp; 查找用户姓名和用户的年薪9.任何含有空值的数学表达式的值都是空值select ename,sal*12+comm from emp;10.select ename|sal from

2、 emp 其中的 |相当于将 sal 全部转化为字符串11.表示字符串的方法select ename |ajjf from emp;12.如果其中有一个单引号就用 2个单引号来代替他select ename|sakj ldsfrom emp;13.select distinct deptno from emp(去除部门字段中重复的部分,关键字 distinct)14.select distinct deptno,job from emp;( 去除这 2个字段中重复的组合)15.select *from dept where deptno=10;取出条件 (取出部门编号为 10的记录 )16.s

3、elect * from emp where ename=CLIRK;取出部门中姓名为clirk 的记录 (注意取出过程中ename 用单引号隔开 )17.select ename,sal from emp where sal1500;取出部门中薪水大于1500的人的姓名18.select ename,sal,deptno from emp where deptno 10 取出部门中的部门号不等于10的19.select ename,sal,deptno from emp where enameCBA 取出部门中员工名字大于CBA 的员工( 实际比较的是 ACIIS 码 )20.select

4、ename,sal from emp where sal between 800 and 1500select ename,sal from emp where sal=800 and sal3-04 月-81;宣传符合条件的日期24.select ename,sal,from emp where sal1000 or deptno=10;找出工资薪水大于 1000或者部门号等于 10的员工25.select ename,sal from emp where sal not in(500,1000);查找薪水不在 500到 1000的员工姓名和月薪26.select ename,sal fro

5、m emp where ename like %ALL%;select ename,sal from emp where ename like _%A%;查找姓名中含有 ALL 的客户信息，一个横线代表一个通配符27.select ename,sal from emp where ename like _%$% escape $; 自己指定转易字符select ename,sal from emp where ename like _%;查找中间含有 %相匹配的客户信息，运用转易字符28.select * from dept order by deptno select *from dept

6、order by deptno desc对表中元素按部门号排序默认为升序，可以用desc按降序29.select ename,sal from emp where sal 1000 order by sal desc按照查询条件来查询，并排序（ asc 升序排列）30.select ename,sal*12 from emp where ename not like _%A% and sal800 order by sal desc31.select lower(ename) from emp 将 ename 都转化为小写lower 是函数能将字母转化为小写32.select ename fr

7、om emp where lower(ename) like _%a%;找出 ename33.select substr(ename,2,3) form emp从第 2个字符开始截取34.select chr(65) from dual;将 65转化为字符35.select ascii(A) from dual将 ACSII 码转化为字符串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按指定格式输

8、出select to_char(sal,L99,999,9999) 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-DDHH:MI:ss)from dual;中所有的含有a 的字符3个字符时间格式的显示十二小时制显示系统时间select to_char(sysdate,YYYY-MM-DD HH24:MI:SS) from dual二四小时制显示系统时间39.select ename,hi

9、redate from emp where hiredate to_date(2005-2-3 12:32:23,YYYY-MM-DD HH:MI:SS);40 select sal from emp where salto_number($1,250.00,$9,999.99); 取出比它大的一切字符串（把特定格式的数字转化成字符）41 select ename,sal+nvl(comm,0) from emp;讲comm值为空的用0来替换，单行函数（以一条记录为条件）一条对一条42.select Max(sal) from emp;select Min(sal) from emp;sele

10、ct 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;按部门号进行分组select deptno,job,max(sal) from emp group by job,deptno;按工作和部门号进行分组；44.select ename from emp where sal=

11、(select max(sal) from emp); 子查询，查找部门中薪水最高的员工姓名45.group by 注意：出现在 select 列表中的字段， 如果没有出现在组函数中必须出现在 group by 子句中46.select avg(sal),deptno from emp group by deptno having avg(sal)2000;薪水大于 2000的部门，47.select * from emp where sal100 group by deptno having .order by.选出部门中平均先取数据 -过滤数据 - 分组 -对分组限制 - 排序48.sel

12、ect avg(sal) from emp where sal2000 group by deptno having avg(sal)1500 order by avg(sal) desc;查找部门中平均薪水打印 2000的员工并按部门号进行排序， 查询分组后的平均薪水必须大于1500 ，查询结果按平均薪水从低到高排列49.select ename from emp where sal(select avg(sal) from emp);查找出员工中薪水位于部门平均薪水之上的所有员工50.select ename,sal from emp join(select max(sal) max_sa

13、l 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(emp.deptno=dept.deptno);54.se

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

15、no); 表的自连接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 ename,sal from emp join (select max(sal)

16、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.salbetween s.losal and s.hisal)t group by deptno;60.查找雇员中哪些是经理人select ename from emp where empno in(select

17、 mgr from emp); 61.select distinct e1.sal from emp e1 join emp e2 on(e1.sale2.sal);自连接 (不用组函数求出最高薪水）select distinct sal from emp where not in (select ename from e1.sal from emp e1 join emp e2on(e1.sale2.sal);62.select deptno from(select avg(sal)max_sal deptno fromemp groupby deptno) wheremax_sal=(se

18、lect 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 group by deptno);DML语句：更、删、改、查创建权限，conn sys/admin as sysdbag

19、rant create table,create view to scott;首先在 C:下面建个文件夹备份文件1.createNewUser 方法1.-backup scottexp2.create user(创建用户）用超级管理员模式进入create user yun identified by kang1234 default tablespace users quota 10M on users;grant create session,create table,create view to kafei（给 kafei 这个用户授予权限）3.import the data( 导入备份数

20、据）imp2.insertinsert into dept values (50,game,bj)插入一条记录insert into dept2 (deptno,dname) values (78,games);插入指定的几条记录insert into dept2 select *from dept插入指定的表（表结构要一样）rollback;回退create table emp2 as select * from emp;创建数据库表 2来备份 emp 这张表3.update emp2 set sal=sal*12 where deptno=10;update 的用法4.delete fro

21、m dept2 where deptno set serveroutput on;SQL begin( 必要的 -程序开始执行 )2 dbms_output.put_line(hello world);3 end;(结束 )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_l

22、ine(v_num);6 end;7 /declare*ERROR 位于第1 行 :ORA-01476:除数为0ORA-06512:在 line 4例子 4：declarev_num number:=0;beginv_num:=2/v_num;dbms_output.put_line(v_num);exceptionwhen others thendbms_output.put_line(error);end;/变量声明的规则1.变量名不能够使用保留字，如from,select 等2.第一字符必须是字母。3.变量名最多包含30个字符4.不要与数据库的表或者列同名5.每一行只能声明一个变量常用变

23、量类型1. binary_interger, 整数，主要用来计数，而不是用来表示字段类型2. number 数字类型3. char定长字符串4. varchar2 变长字符串5. date日期6.long7.boolean长字符串，最长2GB布尔类型，可以取true false和null的值例5：declarev_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:=fals

24、e;v_name varchar2(20) not null:=myname;begindbms_output.put_line(v_temp value:|v_temp);end;用- 可以注释一行例6：declarev_empno number(4);v_empno2 emp.empno%type;v_empno3 v_empno2%type;begindbms_output.put_line(test);end;例7table 变量类型set serveroutput on;declaretype type_table_emp_empno is table of emp.empno%ty

25、pe index by binary_integer; v_empnos type_table_emp_empno;beginv_empnos(0):=7369;v_empnos(2):=7869;v_empnos(-1):=9999;dbms_output.put_line(v_empnos(-1);end;例8Record 变量类型set serveroutput on;declaretype type_record_dept is record(deptno dept.deptno%type,dnamedept.dname%type,locdept.loc%type);v_temp ty

26、pe_record_dept;beginv_temp.deptno:=50;v_temp.loc:=aaaa;v_temp.loc:=bj;dbms_output.put_line(v_temp.deptno| |v_temp.dname);end;例9：使用 %rowtype 声明 record 变量（表结构的变化同时也能代理储存过程的变化）set serveroutput on;declarev_temp dept%rowtype;beginv_temp.deptno:=50;v_temp.loc:=aaaa;v_temp.loc:=bj;dbms_output.put_line(v_te

27、mp.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_sal from emp where empno=

28、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_locdept.loc%type:=bj;begininsert into dept2 values(v_deptno,v_dname,v_loc);commit;end;例13：declarev_deptno emp2.deptno%type:=50;v_count numb

29、er;beginupdate emp2 set sal=sal/2 where deptno=v_deptno;dbms_output.put_line(sql%rowcount | 条记录被影响 );(sql 为关键字，代表上一条语句commit;end;/例14：declarev_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 where empno=7369;dbms_

30、output.put_line(sql%rowcount | 条记录被影响 );(sql 为关键字，代表上一条语句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 where empno=7369;select count(*) into v_count from emp2;（ select 必须和into一起使用）dbms_o

31、utput.put_line(sql%rowcount |条记录被影响);commit;end;/PL/SQL里面执行DDL语句beginexecute immediate create table T(nnn varchar2(20) default aaa); end;PL/SQL 的分支语句：declarev_sal emp.sal%type;beginselect sal into v_sal from empwhere empno=7369;if(v_sal1200) thendbms_output.put_line(low);elsif(v_sal=11); end loop;en

32、d;PL/SQL for 循环beginfor k in 1.10 loopdbms_output.put_line(k);end loop;for k in reverse 1.10 loopdbms_output.put_line(k);end loop;end;exception 捕获异常declarev_temp number(4);beginselect empno into v_temp from emp where deptno=10;exceptionwhen too_many_rows thendbms_output.put_line( 太多记录了 );when others

33、 thendbms_output.put_line(error);end;没有数据错误declarev_temp number(4);beginselect empno into v_temp from emp where deptno=2222;exceptionwhen no_data_found thendbms_output.put_line( 没数据 );when others thendbms_output.put_line(error);end;/错误处理create table errorlog(id number primary key,errcode number,errm

34、sg 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;exceptionwhen others thenrollback;v_errcode:=SQLCODE;v_errmsg:=SQLERRN;insert into err

35、orlog values(seq_errorlog_id.nextval,v_errcode,v_errmsg,sysdate); commit;end;游标declarecursor c isselect * from emp;v_emp c%rowtype;beginopen c;fetch c into v_emp;-(取游标的第一个值插入v_emp，在不断的循环)dbms_output.put_line(v_emp.ename);close c;end;例子declarecursor c isselect * from emp;v_emp c%rowtype;beginopen c;l

36、oopfetch c into v_emp;exit when(c%notfound);dbms_output.put_line(v_emp.ename);end loop;close c;end;declarecursor c isselect * from emp;v_emp c%rowtype;beginopen c;fetch c into v_emp;while(c%found) loopdbms_output.put_line(v_emp.ename);fetch c into v_emp;end loop;close c;end;declarecursor c isselect * from emp;beginfor v_emp in c loopdbms_output.put_line(v_emp.ename);end loop;end;带参数的游标declarecurs