一篇让Java程序猿随时可以翻看的Oracle总结

一篇让Java程序猿随时可以翻看的Oracle总结

前言 :Oracle学习也有十几天了，但是呢，接下来还要学习许多其他的东西，并不能提步不前，所以在此总结了以下Oracle中常用的命令和语句，没有语法都是实例，以便以后工作的时候随时翻看，毕竟是自己的东西，一看就懂。

有关的语句和操作基本都是按照实战中的顺序来总结的，比如创建用户，建表，序列初始化，插入数据的顺序呢。

这篇文章的基表是大家最为熟知的Scott用户下的emp员工表，dept部门表以及salgrade薪水等级表，一切的语句都是围绕它写的。

下面来看一下Oracle中常用的操作都有哪些吧！

一.用户的有关操作。

• 创建用户

create user scott identified by 123456;

• 给用户分配权限

grant connect,resource to scott; grant create view to scott; grant create synonym to scott;

• 撤销用户权限

revoke connect,resource from  scott; revoke create view from scott; revoke create synonym from scott;

• 删除用户

drop user scott cascade;

• 修改用户密码

alter user scott identified by 123456;--命令修改  conn scott/123456 password;--命令可视化修改1  connect scott/123456 password;--命令可视化修改2

• 设置用户是否锁定

alter user scott account lock; alter user scott account unlock;

二.表空间的有关操作。

• 创建表空间

create tablespace mysapce datafile 'D:a.ora' size 10M--绝对路径和大小 extent management local uniform size 1M;--每个分区的大小

• 扩展表空间

alter tablespace mysapce add datafile 'D:b.ora' size 10M;

• 为ORACLE对象指定表空间

create user space_text identified by 123456 account unlock default tablespace mysapce; --创建表、索引也可以指定表空间；一旦指定，表空间无法修改。

• 删除表空间

drop tablespace mysapce;

三.DDL的有关操作。

• 表table

--创建员工表   CREATE TABLE EMP（   EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,  ENAME VARCHAR2(10),  JOB VARCHAR2(9),   MGR VARCHAR(10),    --上司  HIREDATE DATE,    --入职日期  SAL NUMBER(7,2),  --薪水  COMM NUMBER(7,2), --津贴  DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT   );

--创建部门表   CREATE TABLE DEPT(   DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,   DNAME VARCHAR2(14) ,  LOC VARCHAR2(13) --地址   );

--创建工资等级表   CREATE TABLE SALGRADE(    GRADE NUMBER, --等级  LOSAL NUMBER, --等级中最低的薪水  HISAL NUMBER  --等级中最高的薪水   );

• 视图view

--为emp表的empno,ename,sal和dept表的dname和salgrade表的grade创建一个视图   create view emp_dept_salgrade   as   select e.empno,e.ename,e.sal,d.dname,s.grade from   emp e inner join dept d using(deptno)   inner join salgrade s on e.sal between s.losal and s.hisal;      select * from emp_dept_salgrade;--通过视图查询

• 序列sequence

--为员工表的EMPNO创建一个序列   create sequence emp_empno_seq   start with 1001   increment by  1   nomaxvalue   nocycle   cache 10;    select emp_empno_seq.currval from dual;查询序列的当前值   select emp_empno_seq.nextval from dual;查询序列的下一个值

• 同义词synonym

--为视图emp_dept_salgrade创建同义词   create synonym eds for  emp_dept_salgrade;    select * from eds;通过视图的同义词来查询视图中的数据   

• 触发器trigger

--为员工表的empno创建一个自动插入的触发器   create or replace trigger emp_empno_tri   before insert on emp   for each row   begin     :new.empno:=emp_empno_seq.nextval;--语句级（for each row）触发器里面可以:new.列名来给进行操作。   end;

• 存储过程procedure

--创建一个可以控制行数的乘法表的过程。 create or replace procedure nine_nine(nine_line in number) as begin   for  i in 1..nine_line loop     for j in 1..i loop       dbms_output.put(i||'*'||j||'='||i*j||'  ');     end loop;       dbms_output.put_line('');   end loop; end;  --调用这个乘法过程 set serveroutput on; execute nine_nine(9);

• 存储函数function

--创建一个求1!+2!+..+20!的值的存储函数 create or replace function one_tw return number as value_sum number:=0; value_loop number:=1; begin   for i in 1..20 loop     value_loop:=value_loop*i;     value_sum:=value_sum+value_loop;   end loop;   return value_sum; end;  select one_tw() from dual;--调用函数  备注：存储函数的调用可以放在表达式的位置，即表达式在哪里成立，它就可以在哪里调用。

• 事务rollback、commit、savepoint

四.DML的有关操作。

• 插入数据insert

--dept-- INSERT INTO DEPT  select 10,'ACCOUNTING','NEW YORK' from dual union select 20,'RESEARCH','DALLAS' from dual union select 30,'SALES','CHICAGO' from dual union select 40,'OPERATIONS','BOSTON' from dual; commit;                                    --使用Oracle中的多行插入方法，关键字union，select自己想要的数据，与dual伪表组建一个完整的结构。 

--emp-- INSERT INTO EMP(ename,job,mgr,hiredate,sal,comm,deptno) select 'SMITH','CLERK',1009,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20 from dual union select 'ALLEN','SALESMAN',1006,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30 from dual union select 'WARD','SALESMAN',1006,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30 from dual union select 'JONES','MANAGER',1009,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20 from dual union select 'MARTIN','SALESMAN',1006,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30 from dual union select 'BLAKE','MANAGER',1009,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30 from dual union select 'CLARK','MANAGER',1009,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10 from dual union select 'SCOTT','ANALYST',1004,to_date('13-10-87','dd-mm-rr')-85,3000,NULL,20 from dual union select 'KING','PRESIDENT',1007,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10 from dual union select 'TURNER','SALESMAN',1006,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30 from dual union select 'ADAMS','CLERK',1009,to_date('13-10-87', 'dd-mm-rr')-51,1100,NULL,20 from dual union select 'JAMES','CLERK',1009,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30 from dual union select 'FORD','ANALYST',1004,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20 from dual union select 'MILLER','CLERK',1004,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10 from dual; commit;                                 --这里使用了触发器emp_empno_tri来自动插入emp表的empno员工编号

--salgrade-- INSERT INTO SALGRADE VALUES (1,700,1200); INSERT INTO SALGRADE VALUES (2,1200,1400); INSERT INTO SALGRADE VALUES (3,1400,2000); INSERT INTO SALGRADE VALUES (4,2000,3000); INSERT INTO SALGRADE VALUES (5,3000,9999); commit;

• 更新数据update

update emp set sal=3000 where empno=1004;

• 删除数据delete

delete from emp where empno=1004;--from可以省略

• 查询数据select

查询数据是DML语句中最关键的部分，也是最难的部分，在这里有许多围绕scott用户的实例，都是稍微复杂一点的查询，简单的就没必要写了。

1.最常用。

select * from emp; select * from dept; select * from salgrade;

2.内部连接。

2-1.查询每个员工所在的部门,使用where连接.

select e.empno,e.ename,d.dname from emp e,dept d where e.deptno=d.deptno;

2-2.inner join on连接.

select e.empno,e.ename,d.dname from emp e inner join dept d on e.deptno=d.deptno;

2-3.inner join using连接.

select e.empno,e.ename,d.dname from emp e inner join dept d using(deptno);

3.外部连接。

3-1.左外连接：例如：查询出部门的员工的情况（显示所有部门）.

select e.ename,d.dname from emp e left join dept d using(deptno);

3-2.右外连接用(+).

select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno(+);

3-3.右外连接：例如：查询出所有的员工的部门情况（显示了所有员工）.

select e.ename,d.dname from emp e right join dept d using(deptno);

3-4.右外连接用(+).

select e.ename,d.dname from emp e,dept d where e.deptno(+)=d.deptno;

4.自连接。

4-1.查询出员工及他的上级。

select a.ename as 员工,b.ename as 上级 from emp a ,emp b where a.mgr=b.empno;    select a.ename as 上级,b.ename as 上级 from emp a inner join emp b on a.mgr=b.empno;

5.子查询。

5-1.查询工资高于平均工资的员工信息.

select * from emp where sal>(select avg(sal) from emp);

5-2.使用ANY查询任意满足工资低于最低档工资的员工信息.

select * from emp where sal<any(select losal from salgrade);

5-3.查询所有员工所属部门.

select dname from (select distinct dname from dept);

5-4.查询满足大于每个部门的最低工资的员工信息.

select * from emp where sal>all(select min(sal) from emp group by deptno);

5-5.查询出每个部门中，高出本部门平均工资的员工的雇员号和姓名.

select empno as 雇员号,ename as 姓名 from emp outer where sal>   (select avg(sal) from emp inner where inner.deptno=outer.deptno );

5-6.查询不在部门10的员工信息:注意子查询中的1，由于只关心子查询是否返回TRUE值，使用1可以提高查询的效率.

5-6.1.EXISTS子查询效率高于IN子查询.

select * from emp a where not exists (select 1 from emp b where a.deptno=10);

5-6.2.in的效率低，但比较好理解.

select * from emp where deptno not in 10;

5-7.查询emp表中可以管理别的员工的员工.

select ename  from emp a  where exists(select ename  from emp b where a.empno=b.mgr);

5-8.删除中部门重复行.

delete emp where rowid not in (select min(rowid) from emp group by deptno);

5-9.查找emp表第6-10条记录.

select * from(select rownum m,ename,sal,deptno from emp where rownum<=10) where m>5;