Oracle实例入门-同乐学堂

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

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

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

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

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

一.用户的有关操作。

创建用户

1	`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;

删除用户

1	`drop` `user` `scott` `cascade;`

修改用户密码

alter user scott identified by 123456;<code--<命令修改

conn scott/123456

password;<code--<命令可视化修改1

connect scott/123456

password;<code--<命令可视化修改2

设置用户是否锁定

1 2	`alter` `user` `scott account lock;` `alter` `user` `scott account unlock;`

二.表空间的有关操作。

创建表空间

create tablespace mysapce

datafile 'D:a.ora' size 10M<code--<绝对路径和大小

extent management local

uniform size 1M;<code--<每个分区的大小

扩展表空间

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;` <code--<创建表、索引也可以指定表空间；一旦指定，表空间无法修改。

删除表空间

1	`drop` `tablespace mysapce;`

三.DDL的有关操作。

表table

<code--<创建员工表

CREATE TABLE EMP（

EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,

ENAME VARCHAR2(10),

JOB VARCHAR2(9),

MGR VARCHAR(10), <code--<上司

HIREDATE DATE, <code--<入职日期

SAL NUMBER(7,2), <code--<薪水

COMM NUMBER(7,2), <code--<津贴

DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT

);

<code--<创建部门表

CREATE TABLE DEPT(

DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,

DNAME VARCHAR2(14) ,

LOC VARCHAR2(13) <code--<地址

);

<code--<创建工资等级表

CREATE TABLE SALGRADE(

GRADE NUMBER, <code--<等级

LOSAL NUMBER, <code--<等级中最低的薪水

HISAL NUMBER <code--<等级中最高的薪水

);

视图view

<code--<为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;<code--<通过视图查询

序列sequence

<code--<为员工表的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

<code--<为视图emp_dept_salgrade创建同义词

create synonym eds for emp_dept_salgrade;

select * from eds;通过视图的同义词来查询视图中的数据

触发器trigger

<code--<为员工表的empno创建一个自动插入的触发器

create or replace trigger emp_empno_tri

before insert on emp

for each row

begin

:new.empno:=emp_empno_seq.nextval;<code--<语句级（for each row）触发器里面可以:new.列名来给进行操作。

end;

存储过程procedure

<code--<创建一个可以控制行数的乘法表的过程。

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;

<code--<调用这个乘法过程

set serveroutput on;

execute nine_nine(9);

存储函数function

<code--<创建一个求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;<code--<调用函数

备注：存储函数的调用可以放在表达式的位置，即表达式在哪里成立，它就可以在哪里调用。

事务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;`<code--<系统用户 `select` `` `from` `session_roles;`<code--<普通用户

查询用户的权限

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

<code--<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; <code--<使用Oracle中的多行插入方法，关键字union，select自己想要的数据，与dual伪表组建一个完整的结构。

<code--<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; <code--<这里使用了触发器emp_empno_tri来自动插入emp表的empno员工编号

<code--<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;`<code--<from可以省略

查询数据select

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

　　　　1.最常用。

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.查询出员工及他的上级。

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;`

打赏

Oracle实例入门

相关推荐

特别的技术，给特别的你！

觉得文章有用就打赏一下文章作者

非常感谢你的打赏，我们将继续给力更多优质内容，让我们一起创建更加美好的网络世界！

支付宝扫一扫打赏

微信扫一扫打赏