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

1、实验5 用户模式和表结构的修改实验5用户、模式和表结构的修改【实验目的与要求】 掌握ORACLE数据表进行用户授权，取消权限等相关数据控制命令； 掌握对数据库表结构修改的方法： 变更表和约束 删除表和约束【实验内容与步骤】思考问题：如何使用SQL来创建数据库表？准备工作：若上个实验的数据库表还没有创建，请先创建之，并完成数据的插入工作。5.1. 用户的创建与授权1用户的创建（1）用超级用户system在SQL*Plus中登录，并建立两个普通用户如User1，User2：提示：create user username identified by password;（默认建在SYSTEM表空间下）

2、给出相应的SQL语句：（2）显示当前连接用户：show user；show user;给出运行结果：（3）查看系统拥有哪些用户select * from all_users;给出运行结果：2. 用户权限的分配与回收(Grant和Revoke)语法：GRANT CONNECT, RESOURCE TO 用户名;GRANT SELECT,INSERT,DELETE ON表名 TO 用户名;（1）向新用户user1授权连接权限，使其可连接到数据库，写出相应的SQL语句：给出相应的SQL语句：（2）分别以User1、User2的身份从SQL-PLUS登录到数据库，并将各自的密码修改成与其用户名相同：1

3、)更改user1:给出相应的SQL语句：2） 更改user2:给出相应的SQL语句：（3）查询User1和User2各自有哪些权限，确认：User1是否具有connect权限?提示：select * from role_sys_privs ;给出相应的SQL语句：（4）以system身份登录到数据库，并修改User1用户模式的默认表空间和临时表空间分别为users和temp:给出相应的SQL语句：(5) 去掉User1的connect权限，并再次以User1的身份试着登录数据库，检查User1是否还有连接的权限。提示：REVOKE CONNECT, RESOURCE FROM 用户名;REVO

4、KE SELECT, INSERT, DELETE ON表名 FROM 用户名1, 用户名2;给出相应的SQL语句：3. 用户锁定与解锁（1）锁定User2用户：给出相应的SQL语句：（2）试图以User2身份登录数据库，结果如何？给出运行结果：（3）对User2解锁：给出相应的SQL语句：（4）再以User2身份登录数据库，看结果如何？给出相应语句和运行结果：练习5-1：请在OEM中重复上述过程1-3操作。5.2. 修改表结构SQL用ALTER TABLE语句来修改表结构。1添加新列如果要向Customer表中存储其地址信息，就需要在Customer表中添加列。语法如下：Alter tabl

5、e add (new_column_name datatype(size),.);实验5-2-1 给出在Customer表内添加地址（Address）字段，使其表结构如下图所示：给出相应的SQL语句：2修改现有列修改表中现有列的语法如下： Alter table modify(existing_column_name datatype(size),.);实验5-2-2 将客户表中Address的数据类型改为长度为30的字符型，使其结构如下图所示： 给出相应的SQL语句：练习5-2：把下列操作的结果记录下来：(1)将Customer表城市列的大小增加到25。给出相应的SQL语句：(2)将Cust

6、omer表电话列的大小减到 12。给出相应的SQL语句：注意：电话列的宽度不能减小，因为“cannot decrease column length because some value is too big”。3删除列删除表中列的语法如下：Alter table Drop(existing_column_name);实验5-2-3 删除 Customer表的Address 列，使其表结构如下图所示：给出相应的SQL语句：5.3. 创建对表的约束约束允许您定义向表中输入数据时必须遵循的某些验证或限制。 1创建对新表的约束可在两个级别定义约束 列和表。列约束语法如下： Create table

7、( Column_name1 datatye(size) constraint Primary key, Column_name2 datatype(size) constraint references referenced_table(primary_column_name of referenced table), Column_name3 datatype(size) constraint Check(), Column_name4 datatype(size) NOT NULL );表约束参考表的其他列的约束应在表级定义。 语法如下：Create table ( Column_nam

8、e1 datatype(size), . . Column_nameN datatype(size) ,Constraint Primary key (column_name1 ),Constraint Foreign key(Foreign_column_name) references referenced_table(primary_column_name of referenced table) ,Constraint Check();实验5-3-1 创建如下的OrderItem表：给出相应的SQL语句：相关说明 ：约束是用来指定约束的名称的关键词，这是可选的。在上面的代码中，CK_Q

9、ty、FK1、FK2 都是约束名称。如果出现一条出错信息，显示输入了重复记录或输入的条件不符合上面提到的条件，则 Oracle 将在出错信息中显示此约束的名称。主键: 用来指定记录的唯一性。CHECK约束: 用来将列限制为包含特定值或值的范围，即有条件输入详细信息。非空值约束: 将不允许用户将列保留为空的。外键约束：用于定义参照完整性。练习5-3-1在OrderItem表中插入一条Qty值小于0的记录，观察执行结果；给出相应的SQL语句：在OrderItem表中插入一条记录，其Ono的值为“0001”，观察执行结果；给出相应的SQL语句：2对现有表创建约束也可以对现有表实施约束。语法如下：Al

10、ter table add constraint ;在上面的语法中，约束可以是主键或校验或外键约束。约束的语法与给出 INSERT 语法的表级约束相同。实验5-3-2 给Orders表添加名为“cn2”如下的约束：规定Freight属性列的值必须介于0和100之间。给出相应的SQL语句：alter table orders cn2 add constraint cn2 freight0 and freight100;实验5-3-3 给Orders表添加名为“cn1”如下的约束：Cno属性列的值非空。给出相应的SQL语句： alter table orders add constraint cn

11、1 cno is not null; 练习5-3-2 使用如下的CREATE TABLE语句创建Ewage表：然后，在Ewage表上创建如下的约束：(1)定义Ewage表的主码是Eno;给出相应的SQL语句：(2)规定奖金（Bonus）必须比基本工资（Salary）的3倍小.给出相应的SQL语句：3完整性约束的删除格式如下：ALTER TABLE tablenameDROP CONSTRAINT ;实验5-3-4 删除前面定义的名为cn1和cn2的约束。给出相应的SQL语句：alter table orders drop constraint cn1;alter table orders dr

12、op constraint cn2;5.4. 作业与思考练习题1. 创建包含给出的字段和约束的 supplier 表。 Field Datatype Constraint Scode char(2) Primary key Sname varchar2(10) not null Status number(2) City varchar2(10) not null解：2. 创建包含给定字段和约束的 parts 表。 Field Datatype Constraint pcode char(2) Primary key pname varchar2(10) not null color varc

13、har2(10) can be RED, GREEN and BLUE. QOH number(4) can not be less than ROL ROL number(4) City varchar2(15) not null Weight number(2)解:3. 创建包含给定字段和约束的 order表。 Field Datatype Constraint Orderno number(2) Primary key Scode char(2) refers scode from supplier pcode char(2) refers pcode from parts qty_su

14、pplied number(4) geater than 0 order_date date解：4. 创建包含给定字段和约束的 price 表。 Field Datatype Constraint Scode char(2) Primary key pcode char(2) Primary key price number(7,2)解：5. 从零件表删除对颜色字段的约束，然后将约束添加回去。解：6. 将新列“供应日期”添加到订单表。解：7. 将价格表中的价格字段宽度增加到 (10,2)。解：附录：Oracle 数据常见类型数据类型描述VARCHAR2(size)NVARCHAR2(size)

15、可变长度的字符串，其最大长度为 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有效日期范围从公元前

16、 4712 年 1 月 1 日到公元后 4712 年 12 月 31 日。RAW(size)长度为 size 字节的原始二进制数据。size 的最大值为 2000 字节。您必须为 RAW 值指定一个 size。LONG RAW可变长度的原始二进制数据，其最大长度可达 2G 字节。CHAR(size)NCHAR(size)固定长度的字符数据，其长度为 size 字节。size 的最大值为 2000 字节。默认或最小的 size 是一个字节。固定长度的字符数据，其长度依据国家字符集的选择为 size 个字符或字节。size 的最大值取决于存储每个字符所需要的字节数，其上限为 2000 个字节。默认或最小的 size 是一个字符或字节，这取决于字符集。CLOBNCLOB一个字符大型对象，可容纳单字节的字符。不支持宽度不等的字符集。最大大小为 4G 字节。一个字符大型对象，可容纳固定宽度的多字节字符。不支持宽度不等的字符集。最大大小为 4G 字节。储存国家字符集数据。BLOB一个二进制大对象。最大大小为 4G 字节。BFILE包含一个大型二进制文件的定位器，其存储在数据库的外面。使得可以以字节流 I/O 访问存在数据库服务器上的外部 LOB。最大大小为 4G 字节。