Oracle总结(二)

附件：Oracle参考文档，本人认为是最好的一个。

 

 

一、  约束

1、   分类

a)      主键约束（PRIMARY KEY）：表示一个唯一标识，本事不为空

在创建表的时候加入主键又两种方法

（1）在字段后面加上关键字，primary key

如：ID NUMBER(5) PRIMARY KEY

                            （2）一种是在所有字段后加上语句

                                     constraint person_id_pk primary key(pid);

b)      唯一约束（UNIQUE）：不允许有重复值

创建唯一约束的方法同样有两种，同主键约束。

如：NAME VARCHAR2(15) UNIQUE

c)      检查约束（CHECK）：检查一个列的内容是否合法

创建检查约束的条件两种：

（1）      在字段后加关键词

sex varchar2(2) check(sex in (‘男’,女’))

（2）      加constraint语句

constraint person_age_ck check(sex in(‘男’, ‘女’))

d)      非空约束（NOT NULL）

如：ENAME VARCHAR2(10) NOT NULL

e)      外键约束（foreign key）：在两张表中进行约束

constraint person_book_pid_fk foreign key(pid) references person(pid);

2、   使用外键时应注意

a)      在子表中设置的外键在父表中必须是主键

b)      删除时应先删除子表，再删除父表

也可以使用cascade语句强制删除，如：

drop table book cascade constraint;

c)      使用级联删除

在外键约束中加入语句：on delete cascade

如：constraint person_book_pid_fk foreign key(pid) references person(pid) on delete cascade

3、   修改约束

a)      添加约束

如：alter table person add constraint person_pid_pk primary key(pid)

b)      删除约束

alter person drop constraint person_age_ck;

二、  ROWNUM

如果不用子查询，则只能使用<号进行查询，使用子查询则可以使用各种逻辑符号。

1、  显示前5条记录

select * from emp where rownum<=5;

2、  显示前6-10条数据

select * from

           (select rownum rn, empno, ename from emp where rownum<=10) temp

where temp.rn>5;

三、  集合操作

1、  UNION：将多个查询的结果组合到一个查询结果中，没有重复内容

UNION ALL：将多个查询的结果组合到一个查询结果中，有重复内容

2、  INTERSECT：返回多个查询结果中相同的部分（交集）

3、  MINUS：返回两个查询结果的差集

 

用法为 查询结果 关键字 查询结果

四、  序列

1、   创建语法

create sequence seq_name

[increment by n][start with n]

[maxvalue n | nomaxvalue]

[cycle|nocycle]

[cache n| nocache]

2、   创建序列完成之后，所有的自动增长由用户自己处理，在序列中提供了一下两种操作：

a)      nextVal：取得学列的写一个内容

b)      currVal：取得学列的当前内容

3、    

五、  同义词：可以让其他用户通过一个名称方便地访问“用户名.表名称”

1、  创建语法

CREATE SYNONYM 同义词名称 FOR 用户名.表明

如：create synonym emp for scott.emp

2、  删除同义词

drop synonym emp;

3、   

六、  用户管理

1、  创建语法

create user 用户名 identified by 密码

2、  只有用管理员权限，才能创建用户

3、  为用户分配权限

a)      语法

grant 权限1, 权限2,….to 用户

如：将创建session的权限给test用户

grant create session to test

b)      如果要把多个权限一次性赋予一个用户，则可以将这些权限定义成一组角色。

Oracle提供了两个主要的角色，分别为CONNECT, RESOURCE。

GRANT CONNECT, RESOURCE TO test

c)      修给密码

ALTER USER 用户名 IDENTIFIED BY 密码

d)      在一般系统中，用户第一次登陆时可以修改密码，要完成此功能，可以手工让一个密码失效，格式如下：

ALTER USER 用户名 PASSWORD EXPIRE

e)      锁住用户

ALTER USER 用户名 ACCOUNT LOCK

解锁用户：ALTER USER 用户名 ACCOUNT UNLOCK

f)       如果新建用户想访问其他用户的表，需要授予此张表的访问权限

GRANT SELECT, DELETE ON scott.emp TO test

回收权限，使用REVOKE关键字

REVOKE SELECT, DELETE ON scott.emp FROM test

4、   

七、  数据库备份和恢复

1、   数据库备份使用关键字exp

从cmd下进入到备份文件夹，使用命令exp

输入要备份的用户

2、   数据库恢复使用关键字：imp

进入到备份数据的文件夹，输入命令imp

输入备份的用户

八、  嵌套表：一个表中包含另一个子表

1、  数据库在创建数据表时都要指定字段的类型，所以嵌套表本身也同样需要指定类型，这种类型需要单独定义：

CREATE TYPE project_ty AS OBJECT(

           proid NUMBER(4),

           proname VARCHAR2(50) ,

           prodate DATE

);

/

2、  在定义类型的时候不能加上约束条件

3、  定义类型名称：

CREATE TYPE project_nt AS TABLE OF project_ty;

4、  利用自定义类型创建表

CREATE TABLE department(

deptno NUMBER(2) PRIMARY KEY,

dname VARCHAR2(50) NOT NULL,

projects project_nt

) NESTED TABLE projects STORE AS project_nt_tab_temp;

指定嵌套表存储的位置

5、  插入数据

INSERT INTO department(deptno, dname, projects) VALURS(1, ‘技术部’,

project_nt(

           project_ty(1001, ‘ERP’ ,sysdate)

project_ty(1002, ‘AO’ ,sysdate)

));

6、  查询嵌套表信息

SELECT * FROM TABLE (SELECT projects FROM department WHERE deptno=1);

7、  更新嵌套表

UPDATE TABLE (SELECT projects FROM department WHERE deptno=1) pro

set value(pro) = project_ty(‘1001’, ‘嵌套项目’,TO_DATE(‘2010-3-3’, ‘yyyy-mm-dd’));

8、   

九、  可变数组（属于嵌套表的升级版本，可变数组中实际上是内部的嵌套表的内容的长度进行了限制）

1、  定义可变数组

定义Object TYPE同嵌套表

CREATE TYPE worker_info_list AS VARRAY(10) OF worker_info

2、  表的查询和更新统嵌套表   

十、  数据库设计范式

1、  第一范式（1NF）

每个属性的值域第一范式都是不可分的简单数据项的集合。

2、  第二范式（2NF）：如学生和课程，设计成三张表

如果关系R是1NF，而且每一个非主属性都完全依赖于R的键，则R称为第二范式关系模型。

3、  第三范式（3NF）：解决多对一问题，如学生和学院，最好设置成两张表

如果R是2NF，而且它的任何一个非键属性都不传递得依赖于任何候选键，则R称为第三范式关系模型。