20201203黄春跃

知识点

Oracle

oracle创始人拉里·埃里森拉里·埃里森和三个朋友合伙于1977年成立了软件开发研究公司，其个人持股60%。之后软件开发研究
公司为美国中央情报局开发了名为oracle的数据库，从此名声大噪。软件开发研究公司也因此改名为
oracle。1989年oracle进入中国以甲骨文命名。
ORACLE 数据库系统是美国 ORACLE 公司（甲骨文）提供的以分布式数据库为核心的一组软件产品，是
目前最流行的客户/服务器(CLIENT/SERVER)或 B/S 体系结构的数据库之一。ORACLE 数据库是目前世界
上使用最为广泛的数据库管理系统，作为一个通用的数据库系统，它具有完整的数据管理功能；作为一
个关系数据库，它是一个完备关系的产品；作为分布式数据库它实现了分布式处理功能。但它的所有知
识，只要在一种机型上学习了 ORACLE 知识，便能在各种类型的机器上使用它。
oracle以数据存储量大，处理速度快，安全性高，容错性强等出色特征，长期以来占据着全球数

Oracle体系结构[理解]

数据库

Oracle 数据库是数据的物理存储。这就包括（数据文件 ORA 或者 DBF、控制文件、联机日志、参数
文件）。其实 Oracle 数据库的概念和其它数据库不一样，这里的数据库是一个操作系统只有一个库。
可以看作是 Oracle 就只有一个大数据库。

实例

一个 Oracle 实例（Oracle Instance）有一系列的后台进程（Backguound Processes)和内存结构
（Memory Structures)组成。一个数据库可以有 n 个实例。

用户

用户是在实例下建立的。不同实例可以建相同名字的用户。

表空间

表空间是 Oracle 对物理数据库上相关数据文件（ORA 或者 DBF 文件）的逻辑映射。一个数据库在逻
辑上被划分成一到若干个表空间，每个表空间包含了在逻辑上相关联的一组结构。每个数据库至少有一
个表空间(称之为 system 表空间)。
每个表空间由同一磁盘上的一个或多个文件组成，这些文件叫数据文件(datafile)。一个数据文件只能
属于一个表空间。

数据文件（dbf、ora）

数据文件是数据库的物理存储单位。数据库的数据是存储在表空间中的，真正是在某一个或者多个数
据文件中。而一个表空间可以由一个或多个数据文件组成，一个数据文件只能属于一个表空间。一旦数
据文件被加入到某个表空间后，就不能删除这个文件，如果要删除某个数据文件，只能删除其所属于的
表空间才行。
注： 表的数据，是由用户放入某一个表空间的，而这个表空间会随机把这些表数据放到
一个或者多个数据文件中。
由于 oracle 的数据库不是普通的概念，oracle 是由用户和表空间对数据进行管理和存放的。但是表
不是由表空间去查询的，而是由用户去查的。因为不同用户可以在同一个表空间建立同一个名字的表！
这里区分就是用户了！

创建表空间[理解]

create tablespace itlb
datafile 'c:\itlb.dbf'
size 100m
autoextend on
next 10m

itlb 为表空间名称
datafile 指定表空间对应的数据文件(物理结构)
size 表空间的初始大小
autoextend on 自动增长 ，当表空间存储都占满时，自动增长
next 一次自动增长的大小

用户[理解]

创建用户

create user itlbuser
identified by itlb
default tablespace itlb
[ACCOUNT LOCK|UNLOCK]

itlbuser 是账号
identified by 后面是密码(密码不能用数字开头)
default tablespace 后面是表空间
LOCK|UNLOCK 创建用户时是否锁定，默认为非锁定状态。锁定的用户无法正常的登录进行数据库
操作
oracle 数据库与其它数据库产品的区别在于，表和其它的数据库对象都是存储在用户下的.

用户授权

尽管用户成功创建，但是还不能正常的登录 Oracle 数据库系统，因为该用户还没有任何权限

Oracle 用户对数据库管理或对象操作的权利，分为系统权限和数据库对象权限。系统权限比如：
CREATE SESSION，CREATE TABLE 等，拥有系统权限的用户，允许拥有相应的系统操作。数据库对象
权限，比如对表中的数据进行增删改操作等，拥有数据库对象权限的用户可以对所拥有的对象进行对应
的操作。

还有一个概念就是数据库角色（role），数据库角色就是若干个系统权限的集合。Oracle 中已存在三
个重要的角色： connect 角色， resource 角色， dba 角色。

CONNECT 角色： 是授予最终用户的典型权利，最基本的。主要应用在临时用户，特别是那些不需
要建表的用户，通常只赋予他们 CONNECT role。CONNECT 是使用 Oracle 的简单权限， 拥有
CONNECT 角色的用户，可以与服务器建立连接会话（session，客户端对服务器连接，称为会
话）。
CREATE SESSION 建立会话
RESOURCE 角色：是授予开发人员的。更可靠和正式的数据库用户可以授予RESOURCE role。
RESOURCE提供给用户另外的权限以创建他们自己的表、序列、过程（procedure）、触发器
（trigger）、索引（index）等。
CREATE PROCEDURE 建立过程
CREATE SEQUENCE 建立序列
CREATE TABLE 建表
CREATE TRIGGER 建立触发器
CREATE TYPE 建立类型
DBA 角色：拥有全部特权，是系统最高权限，只有 DBA 才可以创建数据库结构，并且系统权限也
需要 DBA 授出，且 DBA 用户可以操作全体用户的任意基表，包括删除

可以把某个权限授予某个角色，可以把权限、角色授予某个用户。系统权限只能由 DBA
用户授权，对象权限由拥有该对象的用户授权，授权语法是：

-- GRANT 角色|权限 TO 用户
-- 例如:
grant CONNECT to itlbuser;
GRANT RESOURCE TO itlbuser;

-- 回收权限的语法是：
REVOKE 角色|权限 FROM 用户
-- 修改用户的密码语法是：
ALTER USER 用户名 IDENTIFIED BY 新密码
-- 修改用户处于锁定（非锁定）状态
ALTER USER 用户名 ACCOUNT LOCK|UNLOCK

Oracle数据类型[应用]

Oracle 数据库的核心是表，表中的列使用到的常见数据类型如下：

对于日期类型，可以使用 sysdate 内置函数可以获取当前的系统日期和时间，返回 DATE类型，用
systimestamp 函数可以返回当前日期、时间和时区。

表的管理[应用]

建表

--  
Create table 表名（
字段 1 数据类型 [default 默认值] [not null],
字段 2 数据类型 [default 默认值] [not null],
...
字段 n 数据类型 [default 默认值] [not null]
）;
-- 
create table person(
pid number(10) not null,
name varchar2(10),
gender number(1) default 1,
birthday date
);
insert into person(pid, name, gender, birthday)
values(1, '张三', 1, to_date('1999-12-22', 'yyyy-MM-dd'));

删除表

-- DROP TABLE 表名 

修改表

在 sql 中使用 alter 可以修改表:

添加列

ALTER TABLE 表名称 ADD(列名 1 类型 [DEFAULT 默认值]，列名 2 类型
[DEFAULT 默认值]...)
alter table person add(phone number(11));

修改列属性

ALTER TABLE 表名称 MODIFY(列名 1 类型 [DEFAULT 默认值]，列名 2 类型
[DEFAULT 默认值]...)

修改列名

ALTER TABLE 表名称 RENAME COLUMN 列名 1 TO 列名 2 

删除列

ALTER TABLE 表名称 DROP COLUMN 列名

添加约束

ALTER TABLE 表名 ADD CONSTRAINT 约束名 约束内容 

常见约束：
not null （非空）：如果在列上定义了not null，那么当插入数据时，必须为列提供，数据不能
为NULL。约束只能在列级定义，不能在表级定义。
unique （唯一）：当定义了唯一约束后，该列值是不能重复的，但是可以为null。
primary key （主键）：用于唯一的标识表行的数据，当定义主键约束后，该列不但不能重复而
且不能为NULL。一张表最多只能有一个主键（联合主键），但是可以由多个unique约束。
创建主键或唯一约束后，ORACLE会自动创建一个与约束同名的索引（UNIQUENES为UNIQUE唯一索
引）。需要注意的是：每个表只能有且有一个主键约束。

foreign key （外键）：用于定义主表和从表之间的关系，外键约束要定义在从表上，主表则必
须具有主键约束或是unique约束，当定义外键约束后，要求外键列数据必须在主表的主键列存在
或是为NULL。
用来维护从表（Child Table）和主表（Parent Table）之间的引用完整性. 外键约束是个有争议性
的约束，它一方面能够维护数据库的数据一致性，数据的完整性。防止错误的垃圾数据入库； 另
外一方面它会增加表插入、更新等SQL性能的额外开销，不少系统里面通过业务逻辑控制来取消外
键约束。例如在数据仓库中，就推荐禁用外键约束。
check （检查）：用于强制行数据必须满足的条件，假定在sal列上定义了check约束，并要求sal
列值在1000～2000之间，如果不在1000～2000之间就会提示出错。可以为Null (MySQL 没有)
default （默认）：为列中的值设置默认值，default ....,如果已经定了值，默认值就无效了

约束命名规范[了解]

非空约束 NN_表名_列名
唯一约束 UK_表名_列名
主键约束 PK_表名
外键约束 FK_表名_列名
条件约束 CK_表名_列名
默认约束 DF_表名_列名
-- 当然，如果约束名称超过32位长度，建议应该缩写表名或者用其他规则

创建约束

not null（非空)

1. create table t1( pid number(10) not null);
2. create table t1(pid number(10) constraint nn_t1_id not null);
3. alter table t1 modify pid constraint nn_t1_id not null;

unique（唯一）

1. create table t1(qq number, constraint un_t1_qq unique(qq));
2. create table t1(qq number constraint un_t1_qq unique);
2. alter table t1 add constraint un_t1_qq unique(qq);

primary key（主键）

1. create table t1(id number, constraint pk_t1_id primary key(id));
2. create table t1(id number constraint pk_t1_id primary key;
3. alter table t1 add constraint pk_t1_id primary key(id);

foreign key（外键）

1. create table t2(id number,cc number,constraint fk_t2_id foreign
key(id) references t1(tid));
2. alter table t2 add constraint pk_t2_id foreign key(id) references
t1(tid);