Oracle表及保证数据的完整性

DDL语句

数据定义语言（Data Definition Language,DDL）用于定义和维护数据库对象的结构或模式，主要包括以下语句。

（1）CREATE语句：用于创建数据库和数据库对象。

（2）ALTER语句：用于修改数据库和数据库对象的结构。

（3）DROP语句：用于删除数据对象。

（4）TRUNCATE语句：从一个表中删除所有记录，包括为表分配的所有空间。

（5）RENAME语句：重命名对象。

DDL语句是最先使用的语句，因为必须先有数据库对象才能对这些对象执行DML等操作。

常用数据类型

字符型：

CHAR [ (size [ BYTE | CHAR])]固定长度的字节（BYTE）数据或字符（CHAR）数据，长度由size指定，数据长度小于定义长度时，末尾使用空格补齐，最大长度为2000字节，默认长度和最小长度为1字节。

VARCHAR2( size [ BYTE | CHAR])长度可变的字节(BYTE)数据或字符(CHAR)数据，长度由size指定，最大长度为4000字节或字符，最小长度为1字节或1字符。与CHAR不同，必须为VARCHAR2指定size。

LONG变长字符数据类型，VARCHAR2最大长度为4000字节，因此存取4000字节以上大文本时可以用此数据类型，最大为2GB，但是从Oracle 8i开始，不推荐使用LONG类型，其存在的目的是支持向后兼容，建议使用CLOB和NCLOB来替代。

说明：

（1）关于定义CHAR和VARCHAR2类型时指定BYTE和CHAR的区别。

CHAR和VARCHAR2类型默认是以BYTE为单位的，如果显式地定义了CHAR为单位（如CHAR(1 CHAR)或VARCHAR2(1 CHAR)），则CHAR和VARCHAR2会以字符为单位，一个字符的大小从一个字节到4个字节不等，这取决于数据库使用的字符集

数值型

NUMBER[(precision[,scale])]变长度数值类型，可以用来存储0、正负定点或者浮点数。其中，precision指定精度，即最大的数字位数（小数点前和后的数字位数的总和），最大位数为38位；scale指定小数点右边的数字位数。

日期类型

DATE类型

DATE类型用于存储日期和时间信息，包括世纪、年、月、日、时、分、秒，最小精度为秒。格式为“DD-MON-RR”，即以日-月-年的格式显示。

to_date、to_char

大对象类型

BLOB：二进制大对象，主要用于存储二进制数据。这种类型用于存储大量的二进制数据，如图片、视频等。

表中的数据的完整性：如何保证数据的准确性

约束、触发器

完整性约束

数据库的完整性，即实体完整性、参照完整性和用户自定义完整性。使用约束的目的就是保证数据的完整性，防止非法或无效数据的输入。

约束的类型

Oracle数据库共有5种约束：

（1）主键（PRIMARY KEY）约束：使用单列或多列的组合来唯一地标识表中的一行记录，实现实体完整性。主键约束同时满足唯一约束和非空约束（当主键是由多个列组成的复合主键时，复合主键中的任何一个列都不能为空值）。

特点：一个表只能有一个主键约束，主键约束列列值不能为空，不能重复。

s_id char(12) primary key sys_C00001

s_id char(12) constraint aa primary key

s_id char(12) constraint pk_s_id primary key

（2）唯一（UNIQUE）约束：定义该约束的列的值不能相同，约束可以定义在单列或多列的组合上。( 特点：列值不能重复)

（3）外键（FOREIGN KEY）约束：在从表和主表之间建立引用关系，实现参照完整性。（特点：外键约束列的列值来源于所引用主表的引用列的列值。外键引用列列值不能重复。）

（4）非空（NOT NULL）约束：定义该约束的列上的值不能为空值。

（5）条件（CHECK）约束：为某些业务规则设定取值条件，以限制列的取值范围。

定义主键约束的语法格式如下：

[CONSTRAINT constraint_name] PRIMARY KEY(attribute_name,...)

定义唯一约束的语法格式：

[CONSTRANT constraint_name] UNIQUE(attribute_name,....)

定义外键约束的语法

[CONSTRANT constraint_name] FOREIGN KEY(child_attribute,...)

REFERENCES table_name(parent_attribute,...)[NO DELETE CASCADE | ON DELETE SET NULL]

定义非空约束的语法

[CONSTRAINT constraint_name] NOT NULL

定义条件约束的语法

[CONSTRAINT constraint_name] CHECK (condition)

表级约束和列级约束

约束分为表级约束和列级约束两种。紧跟在列的数据类型之后定义的约束为列级约束，表级约束是在定义之前或之后进行定义的约束。它们之间没有太大的区别，然而当一个主键约束或外键约束是由多个列组成时，则必须使用表级约束定义，因为列级约束只能引用当前列，它们不能被用于多属性键。在Oracle数据库中，非空约束是唯一一种只能在列级定义的约束。

在定义外键约束时，注意表级约束和列级约束的区别，使用列级约束定义时，不需要使用FOREIGN KEY(...)。当一个外键约束引用了一个表的主键时，表名后的（引用列...）是可以省略的，但为了清除起见，最好写出所引用的列。

关于约束的几点说明：

（1）一个表只能定义一个主键约束，但可以定义多个唯一约束。

（2）定义外键约束的列取值只有两种可能，要么是被参照的列的值，要么是空值。因此，在定义外键约束时有两个选项ON DELETE CASEDE和ON DELETE SET NULL，用来当被参照的列的值发生改变时对参照列的值进行相应的处理，含义分别如下：

ON DELETE CASCADE：当主表中的被参照数据被删除时，从表中的参照数据行也被删除。

ON DELETE SET NULL：当主表中的被参照数据被删除时，从表中的参照数据被设置为空值。

（3）外键约束所参照的列必须已经建立了主键约束或唯一约束。如果被参照列上没有定义主键约束或唯一约束，则会报错。

（4）被参照列的数据类型必须和定义外键的列的数据类型相同，但是列的名字可以不同。

（5）可以在创建表时在CREATE TABLE语句中定义约束，也可以在建表之后使用ALTER TABLE语句定义或修改约束。

某大学生运动会比赛信息数据库，保存如下几个表

运动员sporter(运动员编号 sproterid,运动员姓名 name,运动员性别 sex,所属系号 department)

项目 item(项目编号 itemid,项目名称 itemname,项目比赛地点 location)

成绩 grade(运动员编号 sporterid,项目编号 itemid，积分 mark)

要求：

（1）定义各个表的主码和外码约束

（2）运动员的姓名和所属系别不能为空值

（3）积分要么为空值，要么为6,4,2,0，分别代表第一，二，三名和其他名次的积分

下方查看历史文章