来源：http://www.cnblogs.com/bzx888/p/4820712.html
有关的语句和操作基本都是按照实战中的顺序来总结的，比如创建用户，建表，序列初始化，插入数据的顺序呢。
这篇文章的基表是大家最为熟知的Scott用户下的emp员工表，dept部门表以及salgrade薪水等级表，一切的语句都是围绕它写的。
下面来看一下Oracle中常用的操作都有哪些吧！
一.用户的有关操作。
- 创建用户
| 1 | create user scott identified by 123456; |
- 给用户分配权限
| 1 2 3 | grant connect ,resource to scott; grant create view to scott; grant create synonym to scott; |
- 撤销用户权限
| 1 2 3 | revoke connect ,resource from scott; revoke create view from scott; revoke create synonym from scott; |
- 删除用户
| 1 | drop user scott cascade ; |
- 修改用户密码
| 1 2 3 4 5 6 7 | alter user scott identified by 123456; --命令修改 conn scott/123456 password ; --命令可视化修改1 connect scott/123456 password ; --命令可视化修改2 |
- 设置用户是否锁定
| 1 2 | alter user scott account lock; alter user scott account unlock; |
二.表空间的有关操作。
- 创建表空间
| 1 2 3 4 | create tablespace mysapce datafile 'D:a.ora' size 10M --绝对路径和大小 extent management local uniform size 1M; --每个分区的大小 |
- 扩展表空间
| 1 2 | alter tablespace mysapce add datafile 'D:b.ora' size 10M; |
- 为ORACLE对象指定表空间
| 1 2 | create user space_text identified by 123456 account unlock default tablespace mysapce; --创建表、索引也可以指定表空间；一旦指定，表空间无法修改。 |
- 删除表空间
| 1 | drop tablespace mysapce; |
三.DDL的有关操作。
- 表table
| 1 2 3 4 5 6 7 8 9 10 11 | --创建员工表 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 ); |
| 1 2 3 4 5 6 | --创建部门表 CREATE TABLE DEPT( DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY , DNAME VARCHAR2(14) , LOC VARCHAR2(13) --地址 ); |
| 1 2 3 4 5 6 | --创建工资等级表 CREATE TABLE SALGRADE( GRADE NUMBER, --等级 LOSAL NUMBER, --等级中最低的薪水 HISAL NUMBER --等级中最高的薪水 ); |
- 视图view
| 1 2 3 4 5 6 7 8 | --为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
| 1 2 3 4 5 6 7 8 9 10 | --为员工表的EMPNO创建一个序列 create sequence emp_empno_seq start with 1001 increment by 1 nomaxvalue nocycle cache 10; select emp_empno_seq.currval from dual;<span style= "color: #008000;" >查询序列的当前值</span> select emp_empno_seq.nextval from dual;<span style= "color: #008000;" >查询序列的下一个值</span> |
- 同义词synonym
| 1 2 3 4 5 6 | --为视图emp_dept_salgrade创建同义词 create synonym eds for emp_dept_salgrade; select * from eds;<span style= "color: #008000;" >通过视图的同义词来查询视图中的数据 </span> |
- 触发器trigger
| 1 2 3 4 5 6 7 | --为员工表的empno创建一个自动插入的触发器 create or replace trigger emp_empno_tri before insert on emp for each row begin :new.empno:=emp_empno_seq.nextval;<span style= "color: #008000;" > --语句级（for each row）触发器里面可以:new.列名来给进行操作。</span> end ; |
- 存储过程procedure
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | --创建一个可以控制行数的乘法表的过程。 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 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | --创建一个求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;<span style= "color: #008000;" > --调用函数</span> <span style= "color: #008000;" >备注：存储函数的调用可以放在表达式的位置，即表达式在哪里成立，它就可以在哪里调用。</span> |
- 事务rollback、commit、savepoint
三.常用的结构查询。
- 查询用户和用户的信息
| 1 2 | select username,user_id, password ,default_tablespace from dba_users; select * from dba_users; |
- 查询用户所拥有的角色
| 1 2 | select * from user_role_privs; --系统用户 select * from session_roles; --普通用户 |
- 查询用户的权限
| 1 | select * from user_sys_privs;普通用户和系统用户都可以 |
- 查看表中列的字符长度和字节长度
| 1 | select length(ename),lengthb(ename) from emp; |
- 查询表的相关信息
| 1 2 | SELECT table_name, tablespace_name, temporary FROM user_tables; |
- 查询表中列的相关信息
| 1 2 | SELECT table_name,column_name, data_type, data_length, data_precision, data_scale FROM user_tab_columns; |
- 对表进行重命名
| 1 | rename student to mystudent; |
- 给表添加备注
| 1 | comment on table student is '我的练习' ; |
- 给表中列添加备注
| 1 | comment on column student.sno is '学生号' ; |
- 查看表和视图的备注信息
| 1 | select * from user_tab_comments where table_name= 'STUDENT' ; |
- 查看表和视图中列的备注信息
| 1 | select * from user_col_comments where table_name= 'STUDENT' ; |
- 查看表的结构
| 1 | describe student; |
- 截断表
| 1 | truncate table student; |
- 使用连接运算符
| 1 2 | select empno||ename as employees from emp; select concat(empno,ename) as employees from emp; |
- 查看表的约束信息
| 1 | select * from user_constraints where table_name= 'EMP' ; |
- 查看列的约束信息
| 1 | select * from user_cons_columns where column_name= 'SNO' ; |
- 查看序列的信息
| 1 | select * from user_sequences where sequence_name= 'EMP_EMPNO_SEQ' ; |
- 查看索引的信息
| 1 | select * from user_indexes; |
- 查看视图的信息
| 1 | select * from user_views; |
- 查看同义词
| 1 | select * from user_synonyms; |
- 查看触发器
| 1 | select * from user_triggers; |
- 查看存储过程
| 1 | select * from user_procedures; |
四.DML的有关操作。
- 插入数据insert
| 1 2 3 4 5 6 7 8 9 10 11 | --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 ; <span style= "color: #008000;" > --使用Oracle中的多行插入方法，关键字union，select自己想要的数据，与dual伪表组建一个完整的结构。 </span> |
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 | --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 ; <span style= "color: #008000;" > --这里使用了触发器emp_empno_tri来自动插入emp表的empno员工编号</span> |
| 1 2 3 4 5 6 7 | --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
| 1 | update emp set sal=3000 where empno=1004; |
- 删除数据delete
| 1 | delete from emp where empno=1004;<span style= "color: #008000;" > --from可以省略</span> |
- 查询数据select
查询数据是DML语句中最关键的部分，也是最难的部分，在这里有许多围绕scott用户的实例，都是稍微复杂一点的查询，简单的就没必要写了。
1.最常用。
| 1 2 3 | select * from emp; select * from dept; select * from salgrade; |
2.内部连接。
2-1.查询每个员工所在的部门,使用where连接.
| 1 | select e.empno,e.ename,d.dname from emp e,dept d where e.deptno=d.deptno; |
2-2.inner join on连接.
| 1 | select e.empno,e.ename,d.dname from emp e inner join dept d on e.deptno=d.deptno; |
2-3.inner join using连接.
| 1 | select e.empno,e.ename,d.dname from emp e inner join dept d using(deptno); |
3.外部连接。
3-1.左外连接：例如：查询出部门的员工的情况（显示所有部门）.
| 1 | select e.ename,d.dname from emp e left join dept d using(deptno); |
3-2.右外连接用(+).
| 1 | select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno(+); |
3-3.右外连接：例如：查询出所有的员工的部门情况（显示了所有员工）.
| 1 | select e.ename,d.dname from emp e right join dept d using(deptno); |
3-4.右外连接用(+).
| 1 | select e.ename,d.dname from emp e,dept d where e.deptno(+)=d.deptno; |
4.自连接。
4-1.查询出员工及他的上级。
| 1 2 3 | 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.查询工资高于平均工资的员工信息.
| 1 | select * from emp where sal>( select avg (sal) from emp); |
5-2.使用ANY查询任意满足工资低于最低档工资的员工信息.
| 1 | select * from emp where sal< any ( select losal from salgrade); |
5-3.查询所有员工所属部门.
| 1 | select dname from ( select distinct dname from dept); |
5-4.查询满足大于每个部门的最低工资的员工信息.
| 1 | select * from emp where sal> all ( select min (sal) from emp group by deptno); |
5-5.查询出每个部门中，高出本部门平均工资的员工的雇员号和姓名.
| 1 2 | 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子查询.
| 1 | select * from emp a where not exists ( select 1 from emp b where a.deptno=10); |
5-6.2.in的效率低，但比较好理解.
| 1 | select * from emp where deptno not in 10; |
5-7.查询emp表中可以管理别的员工的员工.
| 1 | select ename from emp a where exists( select ename from emp b where a.empno=b.mgr); |
5-8.删除中部门重复行.
| 1 | delete emp where rowid not in ( select min (rowid) from emp group by deptno); |
5-9.查找emp表第6-10条记录.
| 1 2 | select * from ( select rownum m,ename,sal,deptno from emp where rownum<=10) where m>5; |