实验5 用户模式和表结构的修改.docx

实验5 用户模式和表结构的修改.docx

《实验5 用户模式和表结构的修改.docx》由会员分享，可在线阅读，更多相关《实验5 用户模式和表结构的修改.docx（16页珍藏版）》请在冰豆网上搜索。

实验5用户模式和表结构的修改

实验5　用户、模式和表结构的修改

【实验目的与要求】

⏹掌握ORACLE数据表进行用户授权，取消权限等相关数据控制命令；

⏹掌握对数据库表结构修改的方法：

⏹变更表和约束

⏹删除表和约束

【实验内容与步骤】

思考问题：

如何使用SQL来创建数据库表？

准备工作：

若上个实验的数据库表还没有创建，请先创建之，并完成数据的插入工作。

5.1.用户的创建与授权

1．用户的创建

（1）用超级用户system在SQL*Plus中登录，并建立两个普通用户如User1，User2：

提示：

createuserusernameidentifiedbypassword;（默认建在SYSTEM表空间下）

给出相应的SQL语句：

（2）显示当前连接用户：

showuser；

showuser;

给出运行结果：

（3）查看系统拥有哪些用户select*fromall_users;

给出运行结果：

2.用户权限的分配与回收（Grant和Revoke）

语法：

GRANTCONNECT,RESOURCETO用户名;

GRANTSELECT,INSERT,DELETEON表名TO用户名;

（1）向新用户user1授权连接权限，使其可连接到数据库，写出相应的SQL语句：

给出相应的SQL语句：

（2）分别以User1、User2的身份从SQL-PLUS登录到数据库，并将各自的密码修改成与其用户名相同：

1）更改user1:

给出相应的SQL语句：

2）更改user2:

给出相应的SQL语句：

（3）查询User1和User2各自有哪些权限，确认：

User1是否具有connect权限?

提示：

select*fromrole_sys_privs;

给出相应的SQL语句：

（4）以system身份登录到数据库，并修改User1用户模式的默认表空间和临时表空间分别为users和temp:

给出相应的SQL语句：

（5）去掉User1的connect权限，并再次以User1的身份试着登录数据库，检查User1是否还有连接的权限。

提示：

REVOKECONNECT,RESOURCEFROM用户名;

REVOKESELECT,INSERT,DELETEON表名FROM用户名1,用户名2;

给出相应的SQL语句：

3.用户锁定与解锁

（1）锁定User2用户：

给出相应的SQL语句：

（2）试图以User2身份登录数据库，结果如何？

给出运行结果：

（3）对User2解锁：

给出相应的SQL语句：

（4）再以User2身份登录数据库，看结果如何？

给出相应语句和运行结果：

练习5-1：

请在OEM中重复上述过程1-3操作。

5.2.修改表结构

SQL用ALTERTABLE语句来修改表结构。

1．添加新列

如果要向Customer表中存储其地址信息，就需要在Customer表中添加列。

语法如下：

Altertableadd（new_column_namedatatype（size）,…….）;

实验5-2-1给出在Customer表内添加地址（Address）字段，使其表结构如下图所示：

给出相应的SQL语句：

2．修改现有列

修改表中现有列的语法如下：

Altertable

modify（existing_column_namedatatype（size）,…….）;

实验5-2-2将客户表中Address的数据类型改为长度为30的字符型，使其结构如下图所示：

给出相应的SQL语句：

练习5-2：

把下列操作的结果记录下来：

（1）将Customer表城市列的大小增加到25。

给出相应的SQL语句：

（2）将Customer表电话列的大小减到12。

给出相应的SQL语句：

注意：

电话列的宽度不能减小，因为“cannotdecreasecolumnlengthbecausesomevalueistoobig”。

3．删除列

删除表中列的语法如下：

Altertable

Drop（existing_column_name）;

实验5-2-3删除Customer表的Address列，使其表结构如下图所示：

给出相应的SQL语句：

5.3.创建对表的约束

约束允许您定义向表中输入数据时必须遵循的某些验证或限制。

1．创建对新表的约束

可在两个级别定义约束－列和表。

列约束

语法如下：

Createtable

（Column_name1datatye（size）constraintPrimarykey,

Column_name2datatype（size）constraintreferencesreferenced_table[（primary_column_nameofreferencedtable）],

Column_name3datatype（size）constraintCheck（）,

Column_name4datatype（size）NOTNULL）;

表约束

参考表的其他列的约束应在表级定义。

语法如下：

Createtable

（Column_name1datatype（size）,

……..

……..

Column_nameNdatatype（size）,

ConstraintPrimarykey（column_name1）,

ConstraintForeignkey（Foreign_column_name）referencesreferenced_table[（primary_column_nameofreferencedtable）],

ConstraintCheck（）

）;

实验5-3-1创建如下的OrderItem表：

给出相应的SQL语句：

相关说明：

约束是用来指定约束的名称的关键词，这是可选的。

在上面的代码中，CK_Qty、FK1、FK2都是约束名称。

如果出现一条出错信息，显示输入了重复记录或输入的条件不符合上面提到的条件，则Oracle将在出错信息中显示此约束的名称。

主键:

用来指定记录的唯一性。

CHECK约束:

用来将列限制为包含特定值或值的范围，即有条件输入详细信息。

非空值约束:

将不允许用户将列保留为空的。

外键约束：

用于定义参照完整性。

练习5-3-1

在OrderItem表中插入一条Qty值小于0的记录，观察执行结果；

给出相应的SQL语句：

在OrderItem表中插入一条记录，其Ono的值为“0001”，观察执行结果；

给出相应的SQL语句：

2．对现有表创建约束

也可以对现有表实施约束。

语法如下：

Altertable

addconstraint;

在上面的语法中，约束可以是主键或校验或外键约束。

约束的语法与给出INSERT语法的表级约束相同。

实验5-3-2给Orders表添加名为“cn2”如下的约束：

规定Freight属性列的值必须介于0和100之间。

给出相应的SQL语句：

altertableorderscn2addconstraintcn2freight>0andfreight<100;

实验5-3-3给Orders表添加名为“cn1”如下的约束：

Cno属性列的值非空。

给出相应的SQL语句：

altertableordersaddconstraintcn1cnoisnotnull;

练习5-3-2

使用如下的CREATETABLE语句创建Ewage表：

然后，在Ewage表上创建如下的约束：

（1）定义Ewage表的主码是Eno;

给出相应的SQL语句：

（2）规定奖金（Bonus）必须比基本工资（Salary）的3倍小.

给出相应的SQL语句：

3．完整性约束的删除

格式如下：

ALTERTABLE〈tablename〉DROPCONSTRAINT;

实验5-3-4删除前面定义的名为cn1和cn2的约束。

给出相应的SQL语句：

altertableordersdropconstraintcn1;

altertableordersdropconstraintcn2;

5.4.作业与思考练习题

1.创建包含给出的字段和约束的supplier表。

FieldDatatypeConstraint

Scodechar

（2）Primarykey

Snamevarchar2（10）notnull

Statusnumber

（2）

Cityvarchar2（10）notnull

解：

2.创建包含给定字段和约束的parts表。

FieldDatatypeConstraint

pcodechar

（2）Primarykey

pnamevarchar2（10）notnull

colorvarchar2（10）canbeRED,GREENandBLUE.

QOHnumber（4）cannotbelessthanROL

ROLnumber（4）

City　　varchar2（15）notnull

Weightnumber

（2）

解:

3.创建包含给定字段和约束的order表。

FieldDatatypeConstraint

Ordernonumber

（2）Primarykey

Scodechar

（2）refersscodefromsupplier

pcodechar

（2）referspcodefromparts

qty_suppliednumber（4）geaterthan0

order_datedate

解：

4.创建包含给定字段和约束的price表。

FieldDatatypeConstraint

Scodechar

（2）Primarykey

pcodechar

（2）Primarykey

pricenumber（7,2）

解：

5.从零件表删除对颜色字段的约束，然后将约束添加回去。

解：

6.将新列“供应日期”添加到订单表。

解：

7.将价格表中的价格字段宽度增加到（10,2）。

解：

附录：

Oracle数据常见类型

数据类型

描述

VARCHAR2（size）

NVARCHAR2（size）

可变长度的字符串，其最大长度为size个字节。

size的最大值是4000，而最小值是1。

您必须指定一个VARCHAR2的size。

可变长度的字符串，依据所选的国家字符集，其最大长度为size个字符或字节。

size的最大值取决于存储每个字符所需要的字节数，其上限为4000个字节。

您必须为NVARCHAR2指定一个size。

NUMBER（p,s）

精度为p并且数值范围为s的数值。

精度p的范围是从1到38。

数值范围s的范围是从-84到127。

LONG

可变长度的字符数据，其最大长度可达2G或231–1个字节。

DATE

有效日期范围从公元前4712年1月1日到公元后4712年12月31日。

RAW（size）

长度为size字节的原始二进制数据。

size的最大值为2000字节。

您必须为RAW值指定一个size。

LONGRAW

可变长度的原始二进制数据，其最大长度可达2G字节。

CHAR（size）

NCHAR（size）

固定长度的字符数据，其长度为size字节。

size的最大值为2000字节。

默认或最小的size是一个字节。

固定长度的字符数据，其长度依据国家字符集的选择为size个字符或字节。

size的最大值取决于存储每个字符所需要的字节数，其上限为2000个字节。

默认或最小的size是一个字符或字节，这取决于字符集。

CLOB

NCLOB

一个字符大型对象，可容纳单字节的字符。

不支持宽度不等的字符集。

最大大小为4G字节。

一个字符大型对象，可容纳固定宽度的多字节字符。

不支持宽度不等的字符集。

最大大小为4G字节。

储存国家字符集数据。

BLOB

一个二进制大对象。

最大大小为4G字节。

BFILE

包含一个大型二进制文件的定位器，其存储在数据库的外面。

使得可以以字节流I/O访问存在数据库服务器上的外部LOB。

最大大小为4G字节。