Oracle表的管理3

1、数据库开发与实现本章知识点表的管理 完整性管理 数据更新 5.1表的管理 数据类型创建表 修改表 删除表 插入数据 修改数据 删除数据表约束 5.5.1 数据类型 1字符型 2数值型 3日期/时间型 4大对象（LOB）型 5Rowid型 1字符型数 据 类 型最 大 长 度说 明CHAR(size)2000字节固定长度字符串，size表示存储的字符数量NCHAR(size)2000字节固定长度的NLS（National Languange Support，国家语言支持）字符串，size表示存储的字符串数量。NLS字符串的作用是用本国语言和格式来存储、处理和检索数据NVARCHAR2(size)

2、4000字节可变长度的NLS字符串，size表示存储的字符数量VARCHAR2(size)4000字节可变长度字符串，size表示存储的字符数量LONG2GB可变长度字符串，只为提供向后兼容而保留，不建议使用RAW2000字节可变长度二进制字符串LONGRAW2GB可变长度二进制字符串，只为提供向后兼容而保留，不建议使用2数值型 数 据 类 型说 明NUMBER(p, s)包含小数位的数值类型。参数p表示精度，参数s表示小数点后的位数。例如number(10, 2)表示小数点之前最多可以有8位数字，小数位有两位数字NUMERIC (p, s)与number(p, s)相同FLOAT浮点数类型。

3、属于近似数据类型，它并不存储为多数数字指定的精确值，只储存这些值的最近似值。DEC(p, s)与number(p, s)相同DECIMAL (p, s)与number(p, s)相同INTEGER整数类型INT同integerSMALLINT短整类型REAL实数类型，与float一样，属于近似数据类型DOUBLE双精度类型3日期/时间型 数 据 类 型说 明DATE日期类型TIMESTAMP()与date数据类型相比，timestamp类型可以精确到微秒，微秒的精确范围为09，默认为6TIMESTAMP () WITH TIME ZONE带时区偏移量的timestamp数据类型TIMESTAM

4、P () WITH LOCAL TIME ZONE带本地时区偏移量的timestamp数据类型INTERVAL YEAR () TO MONTH使用year和month日期时间字段存储一个时段。年份精度指定表示年份的数字的位数，默认为2INTERVAL DAY () TO SECOND ()用于按照日、小时、分钟和秒来存储一个时段。日精度表示day字段的位数，默认为2；微秒的精确范围为09，默认为64大对象（LOB）型 数 据 类 型说 明BFILE指向服务器文件系统上的二进制文件的文件定位器，该二进制文件保存在数据库之外BLOB保存非结构化的二进制大对象数据CLOB保存单字节或多字节字符数据

5、NCLOB保存unicode编码字符数据5Rowid型 数 据 类 型说 明ROWID64位基本编号系统（64 base number system），表示行在表中的唯一地址UROWID(size)通用的rowid类型，即可以保存物理rowid，也可以保存逻辑rowid5.5.2 创建表 表Users的结构 编 号字 段 名 称数 据 结 构说 明1UserIdNUMBER用户编号2UserNameVARCHAR2 40用户名3UserTypeNUMBER 1用户类型（1表示管理用户，2表示普通用户）4UserPwdVARCHAR2 40密码1在Oracle Enterprise Manage

6、r中使用表管理器创建表 【例5.1】 以sys用户登录Oracle Enterprise Manager，使用表管理器创建表Users。（1）使用sys用户登录Oracle Enterprise Manager。（2）打开“方案”页面，可以在/“数据库对象”栏目中找到“表”超链接 表管理页面 单击“表”超链接，打开表管理页面 选择方案 单击“方案”后面的 图标，可以打开选择方案的窗口，从中选择需要显示的方案 选择表组织 在表管理页面中单击“创建”按钮，打开“表组织”页面 表管理器 在“表组织”页面中选择“标准，按堆组织”，然后单击“继续”按钮，打开“创建表”页面 2使用CREATE TABLE

7、语句创建表 CREATE TABLE 方案名.表名 ( 列名1 数据类型, 列名2 , 列名n 数据类型 )【例5.2】 参照表5.1创建表Users的SQL语句如下：CREATE TABLE Users (UserId Number, UserName Varchar2(40), UserType Number(1), UserPwd Varchar2(40) ); 注意事项 各列之间要有逗号，语句结束要加分号 表和列的命名规则 必须以字母开头； 长度为130 个字符； 只能包含AZ, az, 09, _, $和#； 对于同一个用户不能使用和其他对象相同 的名字 不能是Oracle的保留字

8、名字是不区分大小写的 5.1.3 修改表 在Oracle Enterprise Manager中，可以对表进行修改操作，包括修改表名、添加列、删除列、修改列属性等。首先搜索并选中要修改的表，单击“编辑”按钮，打开编辑表页面 使用ALTER TABLE语句修改表 （1）添加列。在ALTER TABLE语句中使用ADD子句可以在表中添加列。【例5.3】 在表Users中添加一个列tmpcol，数据类型为NUMBER(5,2)，语句如下：ALTER TABLE Users ADD (tmpcol NUMBER(5,2);（2）修改列名 V在ALTER TABLE语句中使用RENAME COLUMNT

9、O子句可以修改列名。【例5.4】 将表Users中tmpcol列的名称修改为tmpcol_1，语句如下：ALTER TABLE Users RENAME COLUMN tmpcol TO tmpcol_1;（3）删除列 在ALTER TABLE语句中使用DROP COLUMN子句可以删除列。【例7.5】 将表Users中的列tmpcol_1删除，语句如下：ALTER TABLE Users DROP COLUMN tmpcol_1;（4）将列设置为不可用 在ALTER TABLE语句中使用SET UNUSED子句可以将列设置为不可用。【例5.6】 将表Users中的列tmpcol_1设置为不可

10、用，语句如下：ALTER TABLE Users SET UNUSED (tmpcol_1);（5）删除不可用的列 在ALTER TABLE语句中使用DROP UNUSED COLUMNS子句可以删除不可用的列。【例5.7】 删除表Users中所有的不可用列，语句如下：ALTER TABLE Users DROP UNUSED COLUMNS;5.1.4 删除表 在Oracle Enterprise Manager的表管理页面中，选中要删除的表，单击“使用选项删除”按钮，打开删除选项窗口 使用DROP TABLE语句删除表 【例5.8】 删除表Users，可以使用下面的代码：DROP TABL

11、E Users;5.1.5 插入数据 INSERT INTO(列名1,列名2,列名n)VALUES(值1,值2,值n);【例5.9】向表Users中插入如表所示的数据。UserIdUserNameUserTypeUserPwd1Admin1Admin2Lee1Lee3User2Users4Zxh2Zxh【例5.9】 INSERT INTO Users (UserId, UserName, UserType, UserPwd) VALUES ( 1, Admin, 1, Admin);INSERT INTO Users (UserId, UserName, UserType, UserPwd)

12、VALUES ( 2, Lee, 1, Lee);INSERT INTO Users (UserId, UserName, UserType, UserPwd) VALUES ( 3, User, 2, Users);INSERT INTO Users (UserId, UserName, UserType, UserPwd) VALUES ( 4, Zxh, 2, Zxh);COMMIT; SELECT * FROM Users;使用COMMIT语句可以将前面INSERT语句插入的数据真正写入到数据库中。上面语句的运行结果为：USERID USERNAME USERTYPE USERPWD-

13、 - - -1 Admin 1 Admin2 Lee 1 Lee3 User 2 Users4 Zxh 2 Zxh【例5.10】 可以对上面的INSERT语句省略列名序列，代码如下：INSERT INTO Users VALUES ( 1,Admin,1,Admin);INSERT INTO Users VALUES ( 2,Lee,1,Lee);INSERT INTO Users VALUES ( 3,User,2,Users);INSERT INTO Users VALUES ( 4,Zxh,2,Zxh);5.1.6 修改数据 UPDATE 表名 SET 列名1 = 值1, 列名2 = 值

14、2, , 列名n = 值nWHERE 更新条件表达式【例5.11】 将用户Admin的密码修改为111111，可以使用下面的SQL语句：UPDATE Users SET UserPwd=111111 WHERE UserName=Admin;COMMIT WORK;SELECT UserName, UserPwd FROM Users;运行结果为：USERNAME USERPWD- - Admin 111111Lee lEEUser UsersZxh Zxh5.1.7 删除数据 DELETE 表名WHERE 删除条件表达式当执行DELETE语句时，指定表中所有满足WHERE子句条件的行都将被删

15、除。【例5.12】 删除表Users中列UserName等于空（）的数据，可以使用以下SQL语句：DELETE FROM Users WHERE UserName = ;COMMIT WORK;5.1.8 设置DEFAULT列属性【例5.13】 创建表tmpUsers，设置列UserPwd列的默认值为111111，代码如下：CREATE TABLE USERMAN.tmpUsers (UserId Number Primary Key, UserName Varchar2(40) NOT NULL UNIQUE, UserPwd Varchar2(40) DEFAULT(111111) );然

16、后执行下面的INSERT语句，向tmpUsers中插入一条新记录，但并没有指定UserPwd列的值。INSERT INTO USERMAN.tmpUsers (UserId, UserName) VALUES(1, user);再查看表Userstmp中的数据。可以看到，列UserPwd的值被默认设置为111111。5.1.9 表约束 表约束（Constraint）是Oracle提供的一种强制实现数据完整性的机制，它包括： 主键（PRIMARY KEY）约束，主键是表中的一列或一组列，它们的值可以唯一地标识表中的每一行。 非空（NOT NULL）约束，指定列的值不允许为空。 唯一键（UNIQU

17、E KEY）约束，可以保证除主键外的其他列的数据唯一性，以防止在列中输入重复的值。 检查（CHECK）约束，指定表中一列或多列可以接受的数据值或格式。例如，表Users中的工资列UserPwd的值应该不为空。 外键（FOREIGN KEY）约束，用于建立和加强两个表数据之间连接的一列或多列。通过将表中的主键列添加到另一个表中，可创建两个表之间的连接。这个主键列就成为第2个表的外键。外键约束可以确保添加到外键表中的任何行在主表中都存在相应的行。1创建和验证主键约束 可以在CREATE TABLE语句和ALTER TABLE语句中创建约束。例如，要创建用户表USERMAN.Users1 列名数据类

18、型具体说明UserIdNUMBER记录编号，主键UserNameVARCHAR2(50)用户名UserPwdVARCHAR2(50)密码【例5.14】 创建Users1的语句如下：CREATE TABLE Users1 (UserIdNUMBER PRIMARY KEY, UserName VARCHAR2 (40), UserPwd VARCHAR2(40) );【例5.15】 创建表Users2，其结构与表Users1完全相同，代码如下：CREATE TABLE USERMAN.Users2 (UserIdNUMBER, UserName VARCHAR2(40), UserPwd VAR

19、CHAR2(40), CONSTRAINT PK_USERID PRIMARY KEY(UserId) );【例5.16】 将表Users的UserId列设置为主键列，代码如下：ALTER TABLE USERMAN. UsersADD CONSTRAINT UserId_PKPRIMARY KEY(UserId);【例5.17】 执行下面的INSERT语句向表Users1中插入两条记录，它们的UserId列值是重复的。INSERT INTO USERMAN.Users1 VALUES(1, TEST, PWD);INSERT INTO USERMAN.Users1 VALUES(1, USR

20、, PWD);在执行第2条语句时，系统将提示违反了唯一约束条件 2创建和验证非空约束 设置为非空约束的列不接受空值。在CREATE TABLE语句中可以创建非空约束，方法是在列定义的后面直接使用NOT NULL关键字。【例5.18】 创建表Users3，并指定UserName列和UserPwd列为非空约束，代码如下：CREATE TABLE USERMAN.Users3 (UserIdNUMBER, UserName VARCHAR2(40) NOT NULL, UserPwd VARCHAR2(40) NOT NULL, CONSTRAINT PK_USERID PRIMARY KEY(Us

21、erId) );【例5.19】 将表Users中UserName列设置为NOT NULL，代码如下：ALTER TABLE USERMAN.Users MODIFY UserName NOT NULL;【例5.20】 将表Users中UserName列设置为允许空。ALTER TABLE USERMAN.Users MODIFY UserName NULL;验证一下NOT NULL约束的作用 向表User3中插入数据，其UserName值为NULL，代码如下：INSERT INTO USERMAN.Users3 (UserId, UserPwd) VALUES(1,123456);执行上面的语

22、句会提示无法插入NULL 3创建和验证唯一约束 一个表中只能存在一个主键，如果其他列也具有唯一性，可以使用UNIQUE关键字进行约束。在CREATE TABLE语句和ALTER TABLE语句中都可以创建唯一约束。【例5.21】 创建表Users4，在UserName列上定义唯一约束，使用的SQL语句如下：CREATE TABLE USERMAN.Users4 (UserIdNUMBER Primary Key, UserName VARCHAR2(40) NOT NULL UNIQUE, UserPwd VARCHAR2(40) NOT NULL使用CONSTRAINT关键字定义唯一约束 也

23、可以使用CONSTRAINT关键字定义唯一约束，并指定约束的名称。【例5.22】 创建表Users5，其结构与表Users完全相同，在UserName列上定义唯一约束，使用的SQL语句如下：CREATE TABLE USERMAN.Users5 (UserIdNUMBER PRIMARY KEY, UserName VARCHAR2(40), UserPwd VARCHAR2(40), CONSTRAINT UK_USERNAME UNIQUE(UserName) );使用ALTER TABLE语句创建唯一约束 【例5.23】 将表Users的UserName列设置唯一约束，代码如下：ALTE

24、R TABLE USERMAN. UsersADD CONSTRAINT UK_USERNAME1UNIQUE(UserName);定义了唯一约束的列不允许存在重复的记录 【例5.24】 执行下面的INSERT语句向表Users5中插入两条记录，它们的UserName列值是重复的。INSERT INTO USERMAN.Users5 VALUES(100, test, PWD);INSERT INTO USERMAN.Users5 VALUES(101, test, 1234);4创建和验证检查约束 在CREATE TABLE语句和ALTER TABLE语句中都可以创建检查约束。【例5.25】

25、 创建表Users6，规定UserPwd字段的长度不能小于6，则可以在UserPwd列上定义检查约束，使用的SQL语句如下：CREATE TABLE USERMAN.Users6 (UserIdnumber PRIMARY KEY, UserName varchar2(40), UserPwd varchar2(40) CONSTRAINT CK_USERPWD CHECK(LENGTH(UserPwd)=6) );LENGTH()是Oracle函数，用于计算字符串的长度。使用ALTER TABLE语句创建检查约束 【例5.26】 将表Users的UserPwd列设置检查约束，规定UserPwd字段的长度不能小于3,代码如下：ALTER TABLE USERMAN.UsersADD CONSTRAINT CK_USERPWD2 CHECK(LENGTH(UserPwd)=3);每次向表中插入数据或更新表中数据时，Oracle都会检查字段中的数据是否满足检查约束 例如，执行下面的INSERT语句向表Users6中插入记录，它的UserPwd列值的长度小于6。INSERT IN