oracle

数据库的管理

Oracle 对象关系

1. 用户对象

(1) 系统默认

① sys 超级管理员

② system 管理员

③ scott账户

(2) 自定义用户

create user 用户名 identified by 密码

(3) sqlplus的登录

① sqlplus

② sqlplus 用户名/密码

③ sqlplus /nolog

conn 用户名/密码

disconn 断开链接

exit 退出sqlplus的环境

2. 权限

(1) 权限分配

Grant 系统权限 to用户

Grant 对象权限 on 对象名 to用户

(2) 权限分类

① 系统权限

② 对象权限

(3) 撤回权限

Revoke 系统权限 from用户

revoke对象权限 on 对象名 from用户

3. 角色

(1) 自定义角色

Create role 名字

(2) 给角色分配权限

Grant 系统权限 to角色名

Grant 对象权限 on 对象名 to角色名

(3) 将角色分配给用户

Grant 角色名，角色名，。。to角色名

(4) 系统角色

角色是权限的集合，部分常用角色：一般情况下

1）数据库管理员分别授予CONNECT，RESOURCE，DBA

2）数据库开发人员授予CONNECT，RESOURSE

3）数据库的导入导出，授予EXP_PULL_DATABASE，IMP_FULL_DATABASE

角色名	包含权限
CONNECT	ALTER SESSION	修改会话
	CREATE CLUSTER	创建簇
	CREATE DATABASE LINK	创建数据库连接
	CREATE SEQUENCE	创建序列
	CREATE SESSION	创建会话
	CREATE SYNONYM	创建同义词
	CREATE TABLE	创建表
	CREATE VIEW	创建视图
RESOURCE	CREATE CLUSTER	创建簇
	CREATE INDEXTYPE	创建索引类型
	CREATE OPERATOR	定义一个新的操作符
	CREATE PROCEDURE	创建存储过程
	CREATE SEQUENCE	创建序列
	CREATE TABLE	创建表
	CREATE TRIGGER	创建触发器
	CREATE TYPE	创建类型
DBA	所有权限，不受限制
EXP_PULL_DATABASE	SELECT ANY TABLE	查询任何表
	BACKUP ANY TABLE	备份任何表
	EXECUTE ANY PROCEDURE	执行任何存储过程
	EXECUTE ANY TYPE	执行任何类型
	ADMINISTER RESOURCE MANAGER	资源管理
IMP_FULL_DATABASE	EXECUTE_CATALOG_ROLE	执行目录角色
IMP_FULL_DATABASE	SELECT_CATALOG_ROLE	查询目录角色

查询用户与权限，相应的数据字典如下：

数据字典名称	说明
DBA_USERS	数据库用户基本信息表
DBA_SYS_PRIVS	已授予用户或角色的系统权限
DBA_TAB_PRIVS	数据库对象上的所有权限
USER_SYS_PRIVS	登录用户可以查看自己的系统权限
ROLE_SYS_PRIVS	登录用户查看自己的角色
ALL_TABLES	用户自己可以查询的基表信息
USER_TAB_PRIVS	用户自己将那些基表权限授予哪些用户
ALL_TAB_PRIVS	哪些用户给自己授权

二．Oracle的相关概念及体系结构概述

(一) 基本概念

1. 完整性约束

数据的完整性约束条件是一组完整性规则的集合。完整性规则是给定的数据模型中数据及其联系所具有的制约和依存规则，这些规则用来限定基于数据模型的数据库状态及状态的变化，以保证数据库中数据的正确性、有效性和相容性。

1) 域完整性约束

域完整性是保证数据库字段取值的合理性。域完整性规则要求关系中的属性值应是域中的值。另外，一个属性能否为空值（NULL），也是域完整性约束的主要内容。

技术点：非空、默认值、唯一，check

2) 实体完整性约束

实体完整性是指关系的主关键字不能重复也不能取空值。

技术点：主键

3) 参照完整性约束

参照完整性是定义建立关系之间联系的主关键字与外部关键字引用的约束条件。

技术点：外键

表现：1-1 1-M M-1 M-N

4) 用户定义完整性约束

技术点：触发器

实体完整性和参照完整性适用于任何关系型数据库系统，它主要是针对关系的主关键字和外部关键字取值必须有效而做出的约束。

2. 关系（Relation）：对应通常所说的二维表。

3. 元组（Tuple）：表中的一行即为一个元组。

4. 属性（Attribute）：表中的一列即为一个属性。

5. 候选关键字（Candidate Key）：如果关系中的某一属性组的值能唯一地标识一个元组，则称该属性组为候选关键字。

6. 主关键字（Primary Key）：如果一个关系中有多个候选关键字，则选定一个为主关键字。

7. 域（Domain）：属性的取值范围。

8. 分量：元组中的一个属性值。

9. 数据库的三范式

1）第一范式：每个属性值都是不可再分的最小数据单位

数据组的每个属性只可以包含一个值

关系中的每个数组必须包含相同数量的值

关系中的每个数组一定不能相同

2）第二范式：数据库中的每个记录必须可被唯一地区分

3) 第三范式：一个关系表中不包含已在其他表中已包含的非主关键字信息

10. 需求分析的步骤

1．需求信息的收集

（1）需求信息的收集应得到的主要材料

（2）需求信息的收集过程中常用的调查方法

2．需求信息的分析

（1）确定系统边界

（2）业务流程分析

3．需求信息的评审

(二) 体系结构

1. 逻辑存储结构

1）数据块（Data Block）：数据块是Oracle逻辑存储结构中最小的逻辑单位，也是执行数据库输入输出操作的最小存储单位。通过v$parameter数据字典来查询数据块的大小

如：SELECT name,VALUE FROM v$parameter WHERE NAME='db_block_size'

2）数据区（Extent）：数据区是由一组连续的Oracle数据块所构成的Oracle存储结构，一个或多个数据块组成一个数据区，一个或多个数据区再组成一个段（Segment）。数据区是Oracle存储分配的最小单位。

3）段（Segment）:段是由一个或多个数据区构成的，它不是存储空间的分配单位，而是一个独立的逻辑存储结构，用于存储表、索引或簇等占用空间的数据对象

4）表空间（TableSpace）：Oracle使用表空间将向相关的逻辑结构（如段，数据区等）组合在一起，表空间是数据库的最大的逻辑划分区域，通常用来存储数据表、索引、回滚段等数据对象，任何数据对象在创建时都必须被指定存储在某个表空间中。

2. 物理存储结构

1）数据文件：数据文件用于保存用户应用程序数据和Oracle系统内部数据的文件。

2）控制文件：二进制文件，记录了数据库的物理结构，其中主要包含数据库名，数据文件与日志文件的名字和位置、数据库建立日期等信息。

3）日志文件：主要功能是记录对数据所做的修改。

4）服务器参数文件：二级制文件，用来记录Oracle数据库的基本参数信息。

5）密码文件，警告文件和跟踪文件

DDL

一。SQL语句的分类

1. DDL语句

用于定义和维护数据库对象的结构或模式，主要包括：CREATE语句、ALTER

语句、DROP语句、TRUNCATE语句、COMMENT语句、RENAME语句。

2. DML语句

用于管理数据库对象中的数据，包括对数据进行增删改查等操作，主要包括：

SELECT语句、INSERT语句、UPDATE语句、DELETE语句、CALL语句、

LOCK TABLE语句。

3. DCL语句

用于设置或更改数据库用户或角色权限等功能，主要包括：GRANT语句、

REVOKE语句。

4. TCL语句

事务控制语言用于控制对数据库中事务的提交与回滚，主要包括：COMMIT语

句、ROLLBACK语句、SAVEPOINT语句、SET TRANSACTION语句。

二。DDL语句

1. 表空间

创建表空间的语法：

create tablespace 空间名字

datafile ‘路径/文件名.mdf’

size ?m

autoextend on;

next ?m

maxsize 最大值

设置账户的默认表空间：

新建账户直接使用default tablespace参数指定。

已有账户可使用修改alt user语句指定。

修改表空间：

1. 先离线表空间

alter tablespace 表空间名 offline

2. 到物理文件那修改名字 (与代码无关)

3. 通知oracle 表空间物理文件更名为什么了

alter tablespace 表空间名 rename datafile 原始名字 to 新的名字

4. 上线

alter tablespace 表空间名 online

删除表空间：

2drop tablespace 空间名

将已有表移动到指定的表空间（要求：账户对表空间有使用权，且被移动的表

都属于账户）

alter table 表名 move tablespace 表空间；

2. 表

创建表的语法：

CREATE TABLE table_name(

column_name type [CONSTRAINT constraint_definition DEFAULT default_exp]

[, column_name type[CONSTRAINT constraint_definition DEFAULT default_exp]

…

)[TABLESPACE tablespace_name];

Oracle数据类型：

1) CHAR(size) 固定长度的字符数据,其长度为size个字节;size的最大值是2000

字节,而最小值和默认值是1;

2) NCHAR(size) 也是固定长度。根据Unicode标准定义；

3) VARCHAR2(size) 可变长度的字符串,其最大长度为size个字节;size的最大

值是4000,而最小值是1;你必须指定一个VARCHAR2的size；

4) NVARCHAR2(size) 可变长度的字符串,依据所选的国家字符集,其最大长度

为size个字符或字节;size的最大值取决于储存每个字符所需的字节数,其上限为

4000;你必须指定一个NVARCHAR2的size;

5) LONG：变长字符数据类型，VARCHAR2最大长度为4000，因此存取4000

字节以上大文本时可以用此数据类型，最大是2GB，但是从Oracle8i开始，不

推荐使用LONG类型，其存在的目的是支持向后兼容，建议使用CLOB和

NCLOB来替代

6) NUMBER(p,s) 精度为p并且数值范围为s的数值;精度p的范围从1到38;数值

范围s的范围是从-84到127;

例如:NUMBER(5,2) 表示整数部分最大3位，小数部分为2位；

NUMBER(5,-2) 表示数的整数部分最大为7其中对整数的倒数2位为0,前面的取

整。

NUMBER 表示使用默认值,即等同于NUMBER(5);

7) FLOAT：NUMBER的子类型，最大精度是38位。

8) BINARY_FLOAT：存储单精度32位浮点数。

9) BINARY_DOUBLE: 存储双精度64位浮点数。

10) DOUBLE PRECISION、REAL、INT、INTEGER和SMALLINT等。

11) DATE ：用于存储日期和时间信息，包括世纪、年、月、日、时、分、秒，其

最小精度为秒。有效日期范围从公元前4712年1月1日到公元后4712年12月31日

312) TIMESTAMP：TIMESTAMP也是一种存储日期和时间的数据类型，但是它对

DATE类型进行了扩展，所能表示的时间信息比DATE类型更加精确，因为它不仅包

括了世纪、年、月、日、时、分、秒的信息，而且还包括了小数秒的信息。

13) INTERVAL：用于表示时间间隔的数据类型。

14) CLOB：一个字符大型对象,可容纳单字节的字符;不支持宽度不等的字符集;最

大为4G字节，主要存放英文字符。

15) NCLOB：一个字符大型对象,可容纳单字节的字符;不支持宽度不等的字符集;最

大为4G字节;储存国家字符集

16) BLOB：一个二进制大型对象;最大4G字节

17) BFILE ：包含一个大型二进制文件的定位器,其储存在数据库的外面；使得可以

以字符流I/O访问存在数据库服务器上的外部LOB;最大大小为4G字节

外键约束三种行为：

Oracle在外键的删除上有NO ACTION(类似RESTRICT)、SET NULL和CASCADE

三种行为：

1）NO ACTION：默认，当删除被引用表时，如果引用表已经引用了数据，则提示

已找到子记录，不能删除

2）SET NULL：SET NULL指当删除主表中被引用列的数据时，将子表中相应引用

列的值设置为NULL值。SET NULL有个前提就是外键引用列必须可以设置为

NULL。

如：

ALTER TABLE TB_STUDENT ADD CONSTRAINT

FK_TB_STUDENT_CLASS_ID FOREIGN KEY (CLASS_ID) REFERENCES

TB_CLASS (ID) ON DELETE SET NULL;

3）CASCADE：指当删除主表中被引用列的数据时，级联删除子表中相应的数据

行。

如：

ALTER TABLE TB_STUDENT ADD CONSTRAINT

FK_TB_STUDENT_CLASS_ID FOREIGN KEY (CLASS_ID) REFERENCES

TB_CLASS (ID) ON DELETE CASCADE

4）级联更新：Oracle本身没有提供级联更新的功能，触发器共同完成。（需配合

触发器，详见触发器章节）

与约束有关的数据字典视图：

1) dba_constraints、all_constraints、user_constraints：显示已定义的约束信息。

2) dba_cons_columns、all_cons_columns、user_cons_columns：显示约束相关

列的信息。

3) 例如：查询student表的表结构，注意，表名要大写SELECT * FROM

USER_TAB_COLUMNS WHERE table_name='STUDENT'

增加列或约束：

4 ALTER TABLE table_name ADD

(column_name type [ CONSTRAINT constraint_definition DEFAULT default_exp ]

[ , column_name type [ CONSTRAINT constraint_definition DEFAULT default_exp ]

…

);

修改列modify：

修改列的类型 ALTER TABLE 表名 MODIFY 列名数据类型

修改列的约束 ALTER TABLE 表名 MODIFY 列名约束关键字

修改列的默认值 ALTER TABLE 表名 MODIFY 列名 DEFAULT ’值‘

重命名列名：ALTER TABLE 表名 RENAME COLUMN 旧列名 TO 新列名。

删除列drop：ALTER TABLE 表名 DROP COLUMN 列名。

重命名表名：RENAME 原表名 TO 新表名

删除表：DROP TABLE 表名

DML

一。insert 语句

语法：

单表单条插入

INSERT INTO <source object>[(<column list>)]

VALUES(<value list>);

单表多条插入

INSERT INTO <source object> SELECT <column list>

FROM <source object list> WHERE <where predicates>

INSERT ALL

INTO <source object> VALUES(<value list>)

...

INTO <source object> VALUES(<value list>)

SELECT *

FROM dual;

多表插入

INSERT ALL

WHEN <when predicates> THEN

INTO <source object>

...

WHEN <when predicates> THEN

INTO <source object>

ELSE INTO <source object>

SELECT <column list>

FROM <source object list>

WHERE <where predicates>

...

二。update语句

语法：

update 表名 set 列名1=值1,列名2=值2,列名3=值3..... where 条件

2update 利用另外一张表关联更新本表数据的命令结构如下：

update 表1

set 列名=（select 列名 from 表2 where 表1.列名=表2.列名）

where exists (select 1 from 表2 where 表1.列名=表2.列名)

三。delete 语句

语法：

delete from 表名 where 条件

当delete from不加where条件时，表示是把表中的数据全部删除。

四。truncate

语法：

truncate table 表名；

runcate命令也是数据删除命令，他是直接把Oracle表数据一次删除的命

令，truncate命令是一个DDL命令，不同于delete是DML命令。

五。truncate 和 delete区别

1、TRUNCATE 是 DDL 命令，命令执行完就提交，删除的数据不能恢

复； DELETE 命令是 DML 命令，命令执行完需提交后才能生效，删除后

的数据可以通过日志文件恢复。

2、如果表中的数据量较大，TRUNCATE的速度比DELETE速度快很多。

3、truncate删除将重新设置表索引的初始大小，而delete不能。

4、delete能够触发表上相关的delete触发器，而truncate则不会触发。

5、delete删除的原理是一次一条从表中删除数据，并将删除操作当做事

物记录在数据库的日志当中，以便进行数据回滚。而truncate是一次性进

行数据页的删除，因此执行速度快，但是不能回滚。

总结：truncate命令是属于DDL命令，一次性删除表中所有数据，并且数

据不能恢复，在实际开发过程当中truncate命令慎用

一. Oracle数据库的安装和配置

目标：

数据库的安装和配置；

数据库管理；

数据库的连接、创建、删除、选择等操作。

重点