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

第1页共11页实验5用户、模式和表结构的修改姓名：学号：专业：班级：同组人：无实验日期：【实验目的与要求】掌握ORACLE数据表进行用户授权，取消权限等相关数据控制命令；掌握对数据库表结构修改的方法：变更表和约束删除表和约束【实验内容与步骤】思考问题：如何使用SQL来创建数据库表？准备工作：若上个实验的数据库表还没有创建，请先创建之，并完成数据的插入工作。5.1.用户的创建与授权1．用户的创建（1）用超级用户system在SQL*Plus中登录，并建立两个普通用户如User1，User2：提示：createuserusernameidentifiedbypassword;（默认建在SYSTEM表空间下）给出相应的SQL语句：createuserUser1identifiedbyUser1;createuserUser2identifiedbyUser2;（2）显示当前连接用户：showuser；showuser;给出运行结果：（3）查看系统拥有哪些用户select*fromall_users;第2页共11页给出运行结果：2.用户权限的分配与回收(Grant和Revoke)语法：GRANTCONNECT,RESOURCETO用户名;GRANTSELECT,INSERT,DELETEON表名TO用户名;（1）向新用户user1授权连接权限，使其可连接到数据库，写出相应的SQL语句：给出相应的SQL语句：第3页共11页（2）分别以User1、User2的身份从SQL-PLUS登录到数据库，并将各自的密码修改成与其用户名相同：1)更改user1:给出相应的SQL语句：ALTERUSERUser1IDENTIFIEDBYUser1;2）更改user2:给出相应的SQL语句：ALTERUSERUser1IDENTIFIEDBYUser2;（3）查询User1和User2各自有哪些权限，确认：User1是否具有connect权限?提示：select*fromrole_sys_privs;给出相应的SQL语句：（4）以system身份登录到数据库，并修改User1用户模式的默认表空间和临时表空间分别为users和temp:给出相应的SQL语句：ALTERUSERUser1defaulttablespaceuserstemporarytablespacetemp;(5)去掉User1的connect权限，并再次以User1的身份试着登录数据库，检查User1是否还有连接的权限。提示：REVOKECONNECT,RESOURCEFROM用户名;REVOKESELECT,INSERT,DELETEON表名FROM用户名1,用户名2;给出相应的SQL语句：REVOKECONNECT,RESOUPCEFROMUser1;3.用户锁定与解锁（1）锁定User2用户：给出相应的SQL语句：ALTERUSERUser2ACCOUNTLOCK;（2）试图以User2身份登录数据库，结果如何？给出运行结果：第4页共11页（3）对User2解锁：给出相应的SQL语句：ALTERUSERUser2ACCOUNTUNLOCK;（4）再以User2身份登录数据库，看结果如何？给出相应语句和运行结果：练习5-1：请在OEM中重复上述过程1-3操作。5.2.修改表结构SQL用ALTERTABLE语句来修改表结构。1．添加新列如果要向Customer表中存储其地址信息，就需要在Customer表中添加列。语法如下：Altertabletable_nameadd(new_column_namedatatype(size),…….);实验5-2-1给出在Customer表内添加地址（Address）字段，使其表结构如下图所示：第5页共11页给出相应的SQL语句：ALTERTABLECustomerADDAddressVARCHAR2(40);2．修改现有列修改表中现有列的语法如下：Altertabletablenamemodify(existing_column_namedatatype(size),…….);实验5-2-2将客户表中Address的数据类型改为长度为30的字符型，使其结构如下图所示：给出相应的SQL语句：ALTERTABLECustomerMODIFYADDRESSVARCHAR2(30);练习5-2：把下列操作的结果记录下来：(1)将Customer表城市列的大小增加到25。给出相应的SQL语句：ALTERTABLECustomerMODIFYCITYCHAR(25);(2)将Customer表电话列的大小减到12。给出相应的SQL语句：ALTERTABLECustomerMODIFYTELCHAR(12);注意：电话列的宽度不能减小，因为“cannotdecreasecolumnlengthbecausesomevalueistoobig”。3．删除列删除表中列的语法如下：第6页共11页AltertabletablenameDrop(existing_column_name);实验5-2-3删除Customer表的Address列，使其表结构如下图所示：给出相应的SQL语句：ALTERTABLECustomerDROPADDRESS;5.3.创建对表的约束约束允许您定义向表中输入数据时必须遵循的某些验证或限制。1．创建对新表的约束可在两个级别定义约束－列和表。列约束语法如下：Createtabletable_name(Column_name1datatye(size)constraintconstraint_namePrimarykey,Column_name2datatype(size)constraintconstraint_namereferencesreferenced_table[(primary_column_nameofreferencedtable)],Column_name3datatype(size)constraintconstraint_nameCheck(condition),Column_name4datatype(size)NOTNULL);表约束参考表的其他列的约束应在表级定义。语法如下：Createtabletable_name(Column_name1datatype(size),……..……..Column_nameNdatatype(size),Constraintconstraint_namePrimarykey(column_name1),Constraintconstraint_nameForeignkey(Foreign_column_name)referencesreferenced_table[(primary_column_nameofreferencedtable)],第7页共11页Constraintconstraint_nameCheck(condition));实验5-3-1创建如下的OrderItem表：属性列名含义数据类型完整性约束Ono订单代号长度为5的字符串Pno产品代号长度为4的字符串Qty订购数量整型值大于0Discount折扣宽度为4，小数位数为2的数值型数据主码：(Ono,Pno)外码：Ono（被参照关系是Orders表）外码：Pno（被参照关系是Product表）给出相应的SQL语句：CREATETABLEOrderItem(Onochar(5),Pnochar(4),Qtyint,Discountfloat)相关说明约束是用来指定约束的名称的关键词，这是可选的。在上面的代码中，CK_Qty、FK1、FK2都是约束名称。如果出现一条出错信息，显示输入了重复记录或输入的条件不符合上面提到的条件，则Oracle将在出错信息中显示此约束的名称。主键:用来指定记录的唯一性。CHECK约束:用来将列限制为包含特定值或值的范围，即有条件输入详细信息。非空值约束:将不允许用户将列保留为空的。外键约束：用于定义参照完整性。第8页共11页练习5-3-1在OrderItem表中插入一条Qty值小于0的记录，观察执行结果；给出相应的SQL语句：InsertintoOrderItem(Ono,Pno,Qty,Discount)value(,,-7,);在OrderItem表中插入一条记录，其Ono的值为“0001”，观察执行结果；给出相应的SQL语句：InsertintoOrderItem(Ono,Pno,Qty,Discount)value(0001,,,);2．对现有表创建约束也可以对现有表实施约束。语法如下：Altertabletable_nameaddconstraintconstraint_nameconstraint;在上面的语法中，约束可以是主键或校验或外键约束。约束的语法与给出INSERT语法的表级约束相同。实验5-3-2给Orders表添加名为“cn2”如下的约束：规定Freight属性列的值必须介于0和100之间。给出相应的SQL语句：AltertableOrdersaddcn2int;AltertableOrdersaddconstraintcn2check(Freight100andFreight0);实验5-3-3给Orders表添加名为“cn1”如下的约束：Cno属性列的值非空。给出相应的SQL语句：AltertableOrdersModifyCnoNOTNULL;练习5-3-2使用如下的CREATETABLE语句创建Ewage表：然后，在Ewage表上创建如下的约束：(1)定义Ewage表的主码是Eno;第9页共11页给出相应的SQL语句：AltertableEwageaddconstraintEnoprimarykey;(2)规定奖金（Bonus）必须比基本工资（Salary）的3倍小.给出相应的SQL语句：AltertableEwageaddconstraintcheck_bonuscheck(Bonus3*Salary);3．完整性约束的删除格式如下：ALTERTABLE〈tablename〉DROPCONSTRAINTconstraint_name;实验5-3-4删除前面定义的名为cn1和cn2的约束。给出相应的SQL语句：5.4.作业与思考练习题写出完成经下操作的SQL语句，并在SQLPlus中调试实现。1.创建包含给出的字段和约束的supplier表。FieldDatatypeConstraintScodechar(2)PrimarykeySnamevarchar2(10)notnullStatusnumber(2)Cityvarchar2(10)notnull解：createtablesupplier(Scodechar(2)constraintPK_Scodeprimarykey,Snamevarchar(10)constraintEMP_nameNOTNULL,Statusint(2),Cityvarchar(10)constraintEMP_CITYNOTNULL)2.创建包含给定字段和约束的parts表。FieldDatatypeConstraintpcodechar(2)Primarykeypnamevarchar2(10)notnullcolorvarchar2(10)canbeRED,GREENandBLUE.第10页共11页QOHnumber(4)cannotbelessthanROLROLnumber(4)Cityvarchar2(15)notnullWeightnumber(2)解:createtableparts(Pcodechar(2)constraintPK_PCODEprimarykey,Pnamevarchar(10)constraintEMP_PNAMENOTNULL,Colovarchar(10)constraintCheck_