SQL实验4：创建表并实施完整性,使用规则和缺省,更新表中数据

大学学生实验报告书

实验名称 学院（系）

实验四：创建表并实施完整性，使用规则缺省，更新表中数据

课程名称 数据库原理与设计 计算机软件工程

班级

成绩

软件学院 专业

实验目的： 1． 掌握创建基本表的语法。 2． 掌握基本表的三类完整性的实施方法。 3． 掌握创建规则和缺省的方法 4． 掌握将规则和缺省绑定到列和自定义数据类型的方法。 5． 掌握更新基本表中数据的语法。 实验内容： 按照下列题目要求写出相应的SQL 语句。 1. 创建用户自定数据类型Typmytype，源自基本类型char，长度为4。 2. 创建图书馆数据库library，包含下列基本表，写出下列基本表的创建语法。 读者表 （Readers） 列名 cReaderNo vReaderName nSgender vDepartment cReaderType dStartTime 读者类别表（ReaderType） 列名 cReaderType vTypename iPermission iQty 描述 借书证号 读者姓名 性别 所属部门 读者类别号 办证时间 数据类型 Char(6) Varchar(50) nChar(1) Varchar(50) Typmytype Smalldatetime 约束 主键 非空 描述 读者类别号 类别名称 权限值 可借阅数量 数据类型 Typmytype Varchar(50) Int Int 约束 主键 非空

指导教师

日期

注：请用A4纸书写，不够另附纸。 第 页，共 页

书籍表（Books） 列名 cBookNo vBookTitle vAuthor cPublisher mPrice dPubDate 借还情况表（Loans） 列名 cReaderNo cBookNo dBorrowDate dReturnDate 描述 借书证号 书籍编号 借书时间 还书时间 数据类型 Char(6) Char(8) Datetime Datetime 约束 非空 非空 非空 描述 书籍编号 书名 作者名 出版社 定价 出版时间 数据类型 Char(8) Varchar(50) Varchar(50) Char(40) Money Datetime 约束 主键 非空 3. 修改基本表Readers，增加新列为读者电话，cPhone，类型为长度12的定长字符串。 4. 修改基本表Readers，增加外键约束，cReaderType列参照ReaderType表的cReaderType列。 5. 修改基本表ReaderType，增加约束，限定iPermission列的取值只能是1，2，3，4。 6. 修改基本表Books，增加新列为ISBN编号，vISBN，类型为长度50的变长字符串。 7. 修改基本表Loans，增加外键约束，其借书证号和书籍编号为外键，分别参照了读者表和书籍表。 8. 创建一个规则rulnumber，规则要求为形如‘××××-×××××××’的字符串，其中‘×’表示任意数字字符。并将规则绑定到Reader表的cPhone列上。 9. 创建一个缺省defdept，取值为‘信息学院’，将其绑定到Readers表的vDepartment上。 10. 创建一个规则ruldate，规则要求日期的取值范围在当前日期的前100年至今，将其绑定到Books表的出版日期列上。验证之。 11. 通过Insert into语句插入数据到各基本表中，数据请自己设计。 12. 通过delete语句删除基本表中的数据，自行设计语句。 13. 通过update语句修改基本表中的数据，自行设计语句。UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值 14. 更新表的方式有restrict和cascade两种方式，请通过自行设定的语句检验两种方式对于数据产生的作用，请写出自行设计的语句并阐明这些语句对基本表作用的结果。 15. 写出如何使得一个普通用户USER1获得查询和更新ContractRecruiter表的（cName，vAddress，cCity）的权限。 16.将查询Department表的权限赋给所有用户。 17.将创建表的权限赋给一个普通用户USER2，并且使得USER2具备传递该权限的能力。 18．创建角色ROLE1，将查询和更新EMPLOYEE表的权限赋给角色ROLE1。 19.将用户USER1和USER2加入到角色ROLE1中。但不希望USER1具备更新EMPLOYEE表的权限，写出所有的相关过程或语句。 20.收回针对所有用户的对Department的查询权限。 1、sp_addtype Typmytype,'char(4)' 2、create database library on primary ( name=librarydata, ) create table Readers ( cReaderNo Char(6) not null primary key, vReaderName Varchar(50) not null, nSgender nChar(1), vDepartment Varchar(50), cReaderType Typmytype, dStartTime Smalldatetime, ) create table ReaderType ( cReaderType Typmytype not null primary key, vTypename Varchar(50) not null, iPermission Int, iQty Int, ) create table Books ( cBookNo Char(8) not null primary key, vBookTitle Varchar(50) not null, vAuthor Varchar(50), cPublisher char(40), mPrice Money, dPubDate Datetime, ) create table Loans ( cReaderNo Char(6)not null , cBookNo Char(8) not null, dBorrowDate Datetime not null, dReturnDate Datetime, ) filename='D:\\数据库\\MSSQL10.MSSQLSERVER\\MSSQL\\DATA\\library.mdf', size=12,maxsize=100,filegrowth=2 3、alter table Readers add cPhone char(12) 4、alter table Readers add constraint FK_cReaderType foreign key(cReaderType) references ReaderType(cReaderType) 5、alter table ReaderType add constraint iPermission check(iPermission in('1','2','3','4')) 6、alter table books add vISBN Varchar(50) 7、alter table Loans add foreign key (cReaderNo) references Readers(cReaderNo), foreign key (cBookNo) references Books(cBookNo) 8、create rule rulnumber as @x like '[0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9][0-9][0-9]' sp_bindrule rulnumber ,'Readers.cPhone' 9、create default defdept as '信息学院' sp_bindefault defdept, 'Readers.vDepartment' 10、create rule ruldate as @x between DATEPART(yy,getdate()) and (DATEPART(yy,getdate())-100) sp_bindrule ruldate ,'Books.dPubDate' 11、Insert into Readers values ('000001','张三','0','信息学院','s001','2010-10-10 08:00:30') Insert into ReaderType values ('s001','本科学生','4','5') 12、delete from ReaderType where cReaderType='s001' 13、update ReaderType set cReaderType='s002' where cReaderType='s001' 14、aLTER TABLE Readers DROP iPermission CASCADE aLTER TABLE Readers DROP iPermission RESTRICT 用restrict限制删除，而用cascade 会级联删。 15、grant update(cName,vAddress,cCity),select on ContractRecruiter TO USER1; 16、grant select on Department TO public 17、grant create on table to USER2 with grant option 18、create role ROLE1 grant select,UPDATE on EMPLOYEE to ROLE1 ; 19、grant ROLE1 to USER1,USER2; create role ROLE1 grant select,UPDATE on EMPLOYEE to ROLE1 ; 20、revoke select on Department from public 指导教师

日期

注：请用A4纸书写，不够另附纸。

第 页，共 页