快乐学习
前程无忧、中华英才非你莫属!

Oracle实例入门

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

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

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

   这篇文章的基表是大家最为熟知的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;<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;

  

二.表空间的有关操作。

  • 创建表空间

1

2

3

4

create tablespace mysapce

datafile &apos;D:a.ora&apos; size 10M<code–<绝对路径和大小

extent management local

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

  • 扩展表空间

1

2

alter tablespace mysapce

add datafile &apos;D:b.ora&apos; size 10M;

  • 为ORACLE对象指定表空间

1

2

create user space_text identified by 123456 account unlock default tablespace mysapce;

<code–<创建表、索引也可以指定表空间;一旦指定,表空间无法修改。

  • 删除表空间

1

drop tablespace mysapce;

  

三.DDL的有关操作。

  • 表table

1

2

3

4

5

6

7

8

9

10

11

<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

  );

  

1

2

3

4

5

6

<code–<创建部门表

 CREATE TABLE DEPT(

 DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,

   DNAME VARCHAR2(14) ,

   LOC VARCHAR2(13) <code–<地址

 );

  

1

2

3

4

5

6

<code–<创建工资等级表

  CREATE TABLE SALGRADE(

  GRADE NUMBER, <code–<等级

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

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

  );

  

  • 视图view

1

2

3

4

5

6

7

8

<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

1

2

3

4

5

6

7

8

9

10

<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

1

2

3

4

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

  create synonym eds for  emp_dept_salgrade;

 

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

  • 触发器trigger

1

2

3

4

5

6

7

<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

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

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

create or replace procedure nine_nine(nine_line in number)

as

begin

  for  in 1..nine_line loop

    for in 1..i loop

      dbms_output.put(i||&apos;*&apos;||j||&apos;=&apos;||i*j||&apos;  &apos;);

    end loop;

      dbms_output.put_line(&apos;&apos;);

  end loop;

end;

 

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

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

<code–<创建一个求1!+2!+..+20!的值的存储函数

create or replace function one_tw

return number

as

value_sum number:=0;

value_loop number:=1;

begin

  for 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 &apos;我的练习&apos;;

  • 给表中列添加备注

1

comment on column student.sno is &apos;学生号&apos;;

  • 查看表和视图的备注信息

1

select from user_tab_comments where table_name=&apos;STUDENT&apos;;

  • 查看表和视图中列的备注信息

1

select from user_col_comments where table_name=&apos;STUDENT&apos;;

  • 查看表的结构

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=&apos;EMP&apos;;

  • 查看列的约束信息

1

select from user_cons_columns where column_name=&apos;SNO&apos;;

  • 查看序列的信息

1

select from user_sequences where sequence_name=&apos;EMP_EMPNO_SEQ&apos;;

  • 查看索引的信息

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

<code–<dept–

INSERT INTO DEPT

select 10,&apos;ACCOUNTING&apos;,&apos;NEW YORK&apos; from dual

union

select 20,&apos;RESEARCH&apos;,&apos;DALLAS&apos; from dual

union

select 30,&apos;SALES&apos;,&apos;CHICAGO&apos; from dual

union

select 40,&apos;OPERATIONS&apos;,&apos;BOSTON&apos; from dual;

commit;                                    <code–<使用Oracle中的多行插入方法,关键字union,select自己想要的数据,与dual伪表组建一个完整的结构。

  

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

<code–<emp–

INSERT INTO EMP(ename,job,mgr,hiredate,sal,comm,deptno)

select &apos;SMITH&apos;,&apos;CLERK&apos;,1009,to_date(&apos;17-12-1980&apos;,&apos;dd-mm-yyyy&apos;),800,NULL,20 from dual

union

select &apos;ALLEN&apos;,&apos;SALESMAN&apos;,1006,to_date(&apos;20-2-1981&apos;,&apos;dd-mm-yyyy&apos;),1600,300,30 from dual

union

select &apos;WARD&apos;,&apos;SALESMAN&apos;,1006,to_date(&apos;22-2-1981&apos;,&apos;dd-mm-yyyy&apos;),1250,500,30 from dual

union

select &apos;JONES&apos;,&apos;MANAGER&apos;,1009,to_date(&apos;2-4-1981&apos;,&apos;dd-mm-yyyy&apos;),2975,NULL,20 from dual

union

select &apos;MARTIN&apos;,&apos;SALESMAN&apos;,1006,to_date(&apos;28-9-1981&apos;,&apos;dd-mm-yyyy&apos;),1250,1400,30 from dual

union

select &apos;BLAKE&apos;,&apos;MANAGER&apos;,1009,to_date(&apos;1-5-1981&apos;,&apos;dd-mm-yyyy&apos;),2850,NULL,30 from dual

union

select &apos;CLARK&apos;,&apos;MANAGER&apos;,1009,to_date(&apos;9-6-1981&apos;,&apos;dd-mm-yyyy&apos;),2450,NULL,10 from dual

union

select &apos;SCOTT&apos;,&apos;ANALYST&apos;,1004,to_date(&apos;13-10-87&apos;,&apos;dd-mm-rr&apos;)-85,3000,NULL,20 from dual

union

select &apos;KING&apos;,&apos;PRESIDENT&apos;,1007,to_date(&apos;17-11-1981&apos;,&apos;dd-mm-yyyy&apos;),5000,NULL,10 from dual

union

select &apos;TURNER&apos;,&apos;SALESMAN&apos;,1006,to_date(&apos;8-9-1981&apos;,&apos;dd-mm-yyyy&apos;),1500,0,30 from dual

union

select &apos;ADAMS&apos;,&apos;CLERK&apos;,1009,to_date(&apos;13-10-87&apos;&apos;dd-mm-rr&apos;)-51,1100,NULL,20 from dual

union

select &apos;JAMES&apos;,&apos;CLERK&apos;,1009,to_date(&apos;3-12-1981&apos;,&apos;dd-mm-yyyy&apos;),950,NULL,30 from dual

union

select &apos;FORD&apos;,&apos;ANALYST&apos;,1004,to_date(&apos;3-12-1981&apos;,&apos;dd-mm-yyyy&apos;),3000,NULL,20 from dual

union

select &apos;MILLER&apos;,&apos;CLERK&apos;,1004,to_date(&apos;23-1-1982&apos;,&apos;dd-mm-yyyy&apos;),1300,NULL,10 from dual;

commit;                                 <code–<这里使用了触发器emp_empno_tri来自动插入emp表的empno员工编号

  

1

2

3

4

5

6

7

<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.最常用。

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 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实例入门

分享到:更多 ()

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址

特别的技术,给特别的你!

联系QQ:1071235258QQ群:226134712
error: Sorry,暂时内容不可复制!