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

实验5用户、模式和表结构的修改姓名：学号：专业：班级：同组人：实验日期：【实验目的与要求】n 掌握ORACLE数据表进行用户授权，取消权限等相关数据控制命令；n 掌握对数据库表结构修改的方法：n 变更表和约束n 删除表和约束【实验内容与步骤】思考问题：如何使用SQL来创建数据库表？准备工作：若上个实验的数据库表还没有创建，请先创建之。5.1用户的创建与授权1用户的创建（1）用超级用户system在SQL*Plus中登录，并建立两个普通用户如User1，User2：给出相应的SQL语句和运行结果:提示：create user username identified by password;（默认建在SYSTEM表空间下）（2）显示当前连接用户：show user；给出相应的SQL语句和运行结果:（3）查看系统拥有哪些用户select * from all_users;给出相应的SQL语句和运行结果:2. 用户权限的分配与回收(Grant和Revoke)（1）向新用户user1授权连接权限，使其可连接到数据库，写出相应的SQL语句：给出相应的SQL语句和运行结果:提示：GRANT CONNECT, RESOURCE TO 用户名;GRANT SELECT,INSERT,DELETE ON表名 TO 用户名;（2）分别以User1、User2的身份从SQL-PLUS登录到数据库，并将各自的密码修改成与其用户名相同：（3）查询User1和User2各自有哪些权限，确认：.User1是否具有connect权限提示：select * from role_sys_privs ;（4）以system身份登录到数据库，并修改User1用户模式的默认表空间和临时表空间分别为users和temp:给出相应的SQL语句和运行结果:(5) 去掉User1的connect权限，并再次以User1的身份试着登录数据库，检查User1是否还有连接的权限。给出相应的SQL语句和运行结果:提示：REVOKE CONNECT, RESOURCE FROM 用户名;REVOKE SELECT, INSERT, DELETE ON表名 FROM 用户名1, 用户名2;3. 用户锁定与解锁（1）锁定User2用户：给出相应的SQL语句和运行结果:（2）试图以User2身份登录数据库，结果如何？给出相应的SQL语句和运行结果:（3）对User2解锁：给出相应的SQL语句和运行结果:（4）再以User2身份登录数据库，看结果如何？给出相应的SQL语句和运行结果:练习5-1：1. 请在OEM中重复上述过程1-3。5.2修改表结构SQL用ALTER TABLE语句来修改表结构。1添加新列如果要向Customer表中存储其地址信息，就需要在Customer表中添加列。语法如下：Alter table add (new_column_name datatype(size),.);实验5-2-1 给出如下所示的语句，在Customer表内添加地址（Address）字段。给出运行结果：2修改现有列修改表中现有列的语法如下： Alter table modify(existing_column_name datatype(size),.);实验5-2-2 将客户表中Address的数据类型改为长度为30的字符型。给出运行结果：练习5-2：把下列操作的结果记录下来：(1) 将Customer表城市列的大小增加到25。给出相应的SQL语句和运行结果:(2) 将Customer表电话列的大小减到 12。给出相应的SQL语句和运行结果:注意：电话列的宽度不能减小，因为“cannot decrease column length because some value is too big”。3删除列删除表中列的语法如下：Alter table Drop(existing_column_name);实验5-2-3 删除 Customer表的Address 列。给出运行结果：5.3创建对表的约束约束允许您定义向表中输入数据时必须遵循的某些验证或限制。 1创建对新表的约束可在两个级别定义约束 列和表。列约束语法如下： Create table ( 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_name1 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表：给出运行结果：T约束是用来指定约束的名称的关键词，这是可选的。在上面的代码中，CK_Qty、FK1、FK2 都是约束名称。如果出现一条出错信息，显示输入了重复记录或输入的条件不符合上面提到的条件，则 Oracle 将在出错信息中显示此约束的名称。主键: 用来指定记录的唯一性。CHECK约束: 用来将列限制为包含特定值或值的范围，即有条件输入详细信息。非空值约束: 将不允许用户将列保留为空的。外键约束：用于定义参照完整性。练习5-3-1(1) 在OrderItem表中插入一条Qty值小于0的记录，观察执行结果；给出相应的SQL语句和运行结果:insert into OrderItem(Ono,Pno,Qty)values(10001,20001,-2);(2) 在OrderItem表中插入一条记录，其Ono的值为“0001”，观察执行结果；给出相应的SQL语句和运行结果:insert into OrderItem(Ono,Pno,Qty)values(0001,20001,2);2对现有表创建约束也可以对现有表实施约束。语法如下：Alter table add constraint ;在上面的语法中，约束可以是主键或校验或外键约束。约束的语法与给出 INSERT 语法的表级约束相同。实验5-3-2 给Orders表添加名为“cn2”如下的约束：规定Freight属性列的值必须介于0和100之间。给出相应的SQL语句和运行结果:实验5-3-3 给Orders表添加名为“cn1”如下的约束：Cno属性列的值非空。给出相应的SQL语句和运行结果:alter table OrderItemmodify Cno not null;练习5-1-2 使用如下的CREATE TABLE语句创建Ewage表：Create table Wwage(Eno char(4), salary INT, Allowance INT, Bonus INT);然后，在Ewage表上创建如下的约束：(1)定义Ewage表的主码是Eno;给出相应的SQL语句和运行结果:alter table Wwageadd constraint PK_ID primary key (Eno);(2) 规定奖金（Bonus）必须比基本工资（Salary）的3倍小；给出相应的SQL语句和运行结果:alter table Wwageadd constraint PK_ID primary key (Eno);alter table Wwageadd constraint check_bscheck(Bonus*3 salary);3完整性约束的删除格式如下：ALTER TABLE tablenameDROP CONSTRAINT ;实验5-3-4 删除前面定义的名为cn1和cn2的约束。给出相应的SQL语句和运行结果:alter table OrderItemdrop constraint check_cn1;alter table OrderItemdrop constraint check_cn2;5.4作业与思考练习题1. 创建包含给出的字段和约束的 supplier 表。FieldDatatypeConstraintScodechar(2)Primary keySnamevarchar2(10)not nullStatusnumber(2)City varchar2(10)not null给出相应的SQL语句和运行结果:create table supplier( Scode char(2) constraint pk_su primary key, Sname varchar2(10) not null, Status number(2), City varchar2(10) not null);2. 创建包含给定字段和约束的 parts 表。FieldDatatypeConstraintpcodechar(2)Primary keypnamevarchar2(10)not nullcolorvarchar2(10)can be RED, GREEN and BLUE.QOHnumber(4)can not be less than ROLROLnumber(4)City varchar2(15)not nullWeightnumber(2)给出相应的SQL语句和运行结果:create table parts( pcode char(2) constraint pk_parts primary key, pname varchar2(10) not null, color varchar2(10) constraint ck_color check (color in (red,green,blue), QOH number(4), ROL number(4), City varchar2(10) not null, Weight number(2), constraint ck_notless check (QOHROL);3. 创建包含给定字段和约束的 orders 表。FieldDatatypeConstraintOrdernonumber(2) Primary keyScodechar(2)refers scode from supplierpcodechar(2)refers pcode from partsqty_supplied number(4)geater than 0order_datedate给出相应的SQL语句和运行结果:create table orders2( Orderno number(2) constraint pk_orders2 primary key, Scode char(2), pcode char(2), qty_supplied number(4) constraint ck_geater check(qty_supplied0), order_date date, constraint fk_1 foreign key(Scode) references supplier(Scode), constraint fk_2 foreign key(pcode) references parts(pcode);4. 创建包含给定字段和约束的 price 表。FieldDatatypeConstraintScodechar(2)Primary keypcodechar(2)Primary keyprice number(7,2)给出相应的SQL语句和运行结果:create table price(Scodechar(2),pcodechar(2),price number(7,2),constraint PK_price primary key (Scode,pcode);5. 从零件表删除对颜色字段的约束，然后将约束添加回去。给出相应的SQL语句和运行结果:alter