1、实验一 创建数据库及数据库对象管理实验一 创建数据库及数据库对象管理一、实验目的：（1）了解并掌握创建数据库的方法。（2）了解并掌握创建数据表，索引的方法。（3）掌握定义数据库完整性的方法。（4）掌握对数据库对象进行安全性控制的方法。二、实验步骤：1、创建数据库方法：（1）使用SQL语句创建数据库：创建数据库包括：定义数据库名；确定数据库文件机器大小；确定事务日值文件的位置和大小。CREATE DATABASE数据库名ON PRIMARY(NAME =逻辑数据文件名，FILENAME= 操作数据文件路径和文件名，SIZE=文件长度，MAXSIZE=最大长度，FILERGROWTH=文件增长率)
2、，nLOG ON (NAME=逻辑日志文件名，FILENAME= 操作日志文件路径和文件名，SIZE=文件长度 )，nFOR RESTORE子句中：PRIMARY指明主文件名；SIZE说明文件的大小，数据库文件最小为1MB，默认值为3MB；FILEROWTH说明文件的增长率，默认值为10%。FOR RESTORE子句说明重建一个数据库，该重建的数据库用于数据恢复操作。例如：CREATE DATABASE test1ON PRIMARY(NAME = test1,FILENAME = C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDatatest1
3、.mdf, SIZE=10,MAXSIZE=50,FILEGROWTH=5)LOG ON(NAME = test1Log,FILENAME = c:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDatatest1Log.ldf, SIZE=5MB,MAXSIZE=25MB,FILEGROWTH=5MB)（2）使用对象资源管理器创建数据库2、创建基本表的方法：（1）使用SQL语句创建基本表CREATE TABLE （ ， ， ）；例如：CREATE TABLE 学生(学号 int PRIMARY KEY CLUSTERED,姓名 CHAR(8) NOT
4、 NULL,性别 CHAR(2) CHECK (性别 IN(男,女)（2）使用对象资源管理器创建基本表3、定义索引方法CREATE UNIQUE CLUSTER INDEX ON (, )；4、定义数据库完整性（1）定义表的完整性及约束1. 唯一性约束：UNIQUE2. 非空值约束：NOT NULL3. 检查约束(check)：当对表进行插入或更新操作时，检查其中的列值必须满足的约束条件。4. 主键约束(primary key)：限制表中指定列的取值，以保证任何两列在指定列上不存在相同的值，同时主键约束列上的值不得为Null。5. 参照约束(foreign key)：如果两个表之间由主键和外键
5、相联系，那么在对表中的数据执行插入、删除和更新操作时，必须遵循它们之间的参照性规则和各自的数据完整性规则。6. 缺省约束(default)：向表插入新行时为没有给值的列指定一缺省值，用以增强数据的完整性。（2）创建和管理存储过程CREATE PROCEDURE过程名;版本号 参数名参数类型=缺省值OUTPUTWITH RECOMPILE|ENCRYPTION| RECOMPILE，ENCRYPTIONASSQL语句组1) 版本号是可选的整数，它用于将有相同名字的存储过程编为不同的组。2) OUTPUT选项用于给调用者返回值。3) RECOMPILE为重编译选项。它要求每次执行都要对过程重编译和
6、优化，并创建新的查询计划。4) ENCYPTION为加密选项。5) 参数前加“” 为局部变量，参数前加“”则说明为全局变量。过程的调用语句为：EXEUTE过程名参数名=参数版本号（3）创建和管理触发器CREATE TRIGGER触发器名ON表名WITH ENCRYPTIONFORDELETE，INSERT，UPDATE WITH APPEND NOT FOR REPLICATION AS BEGIN SQL语句组END1) WITH ENCRYPTION为加密选项。2) DELETE触发器是当对表执行DELETE操作时删除元组，将删除的元组放入deleted表中。检查delete表中的数据，确
7、定该如何处理。3) INSERT触发器在对执行插入数据操作时,将插入表中的数据拷贝并送入inserted表中，根据inserted表中的值决定如何处理。4) UPDATE触发器仅在更新数据操作时将要被更新的原数据移入deleted表中，将更新后的数据备份送入inserted表中，对deleted和inserted表进行检查，并决定如何处理。5) NOT FOR REPLICATION项说明当一个复制过程在修改一个触发器表时，与该表相关联的触发器不能被执行。例如：create trigger trig1on s for updateas declare bage int,aage intsele
8、ct bage=sage from deletedselect aage=sage from insertedif (aagebage)beginprint年龄只能增长，更改失败rollbackend触发器执行：5、定义数据库安全性（1）SQL server安全认证模式：windows安全验证和混合安全验证（2）登录管理登录（SQL服务器用户）通过账号和口令访问SQL server数据库（3）用户管理登录用户只有成为数据库用户（database user）后，才能访问数据库（4）角色管理（5）授权管理1）通过对象资源管理器授权2）通过DCL语言授权GRANT语句的一般格式：GRANT ,.ON
9、 TO ,.WITH GRANT OPTION;（6）受限访问三、 实验内容1、使用SQL语句建立如下数据库及基本表 建库名称为“姓名1”，要求：将主数据库文件“姓名1.MDF”放置在C:Program FilesMicrosoftSQLServerMSSQL.1MSSQLData文件夹中，其文件大小自动增长为按5MB增长；将事务日志文件“学号姓名1.LDF”放置在C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLData文件夹中，其文件大小自动增长为按1MB增长。 建表如下：1. 建立学生表Students，其结构为：学号，姓名，性别，年龄，籍贯，
10、民族，email，党员否，所在年级。其中“学号”为主键，唯一标识。姓名不允许为空。性别只能为男或女；15年龄100；民族默认为“汉族”，籍贯默认为“天津”。在年龄上建立降序索引。2. 建立教师表Teachers，其结构为：教师号，姓名，性别，年龄，职称，email，工资。其中“教师号”为主键，唯一标识。姓名不允许为空。职称只能为教授或副教授，讲师，助教；3. 建立课程表Courses，其结构为：课程号，课程名，学分，学时数。其中“课程号”为主键，唯一标识。课程名不允许为空。1学分5；15学时数120 4. 建立选课表Choices，其结构为：学号，课程号，成绩。其中“学号，课程号”为主键。0=
11、成绩=1005. 建立任课表Teaching，其结构为：教师号，课程号，班级，学生数。其中“教师号，课程号”为主键。根据如上语义建立表及表见的关联（外键）。2、在对象资源管理器中建库，名称为“姓名2”，使用管理器建立表，将建好的数据库和表转化为SQL语句，和手写的SQL比较。 3、建立如下触发器1. 在学生表上建立触发器trgs1:当有学生记录被修改时给出提示“学生记录被修改”2. 在教师表上建立触发器trgt1:修改教师的薪水只能增长，不能减少。3. 在教师表上建立触发器trgt2:对教师表记录进行修改或插入操作时，如果教师的年龄到达60岁时给出提示“到达退休年龄”，并将此教师记录于离退休教师表中。（离退休教师表需要建立）4. 在任课表上建立触发器trgtc1：插入选课记录时，如果人数小于15人时，给出提示“选课人数不足15人”4、完成如下1. 建立学籍管理用户U1，拥有对学生表及选课表的查询，修改和删除的权限。2. 建立U2，U3，具有对教师表和任课表的查询权限。3. 建立角色role1，具有对学生表的查询权限及对选课表的插入和对成绩的修改权限。4. 将角色role1的权限授予用户U2及U3。5、 在查询分析器中插入一些基本的数据，为后续练习SQL语句做准备。插入一些数据：教师表Teachers选课表Choices，备份次数据库：右键任务备份
