实验一 创建数据库及数据库对象管理.docx
《实验一 创建数据库及数据库对象管理.docx》由会员分享，可在线阅读，更多相关《实验一 创建数据库及数据库对象管理.docx（13页珍藏版）》请在冰点文库上搜索。
实验一创建数据库及数据库对象管理
实验一创建数据库及数据库对象管理
一、实验目的：
（1）了解并掌握创建数据库的方法。
（2）了解并掌握创建数据表，索引的方法。
（3）掌握定义数据库完整性的方法。
（4）掌握对数据库对象进行安全性控制的方法。
二、实验步骤：
1、创建数据库方法：
（1）使用SQL语句创建数据库：
创建数据库包括：
定义数据库名；确定数据库文件机器大小；确定事务日值文件的位置和大小。
CREATEDATABASE〈数据库名〉
[ON[PRIMARY][（NAME=〈逻辑数据文件名〉，]
FILENAME='〈操作数据文件路径和文件名〉'
[，SIZE=〈文件长度〉]
[，MAXSIZE=〈最大长度〉]
[，FILERGROWTH=〈文件增长率〉]）[，„n]]
[LOGON（[NAME=〈逻辑日志文件名〉，]
FILENAME='〈操作日志文件路径和文件名〉'
[，SIZE=〈文件长度〉]）[，„n]]
[FORRESTORE]
子句中：
PRIMARY指明主文件名；SIZE说明文件的大小，数据库文件最小为1MB，默认值为3MB；FILEROWTH说明文件的增长率，默认值为10%。
FORRESTORE子句说明重建一个数据库，该重建的数据库用于数据恢复操作。
例如：
CREATEDATABASEtest1
ONPRIMARY
（NAME='test1',
FILENAME='C:
\ProgramFiles\MicrosoftSQLServer\MSSQL.1\MSSQL\Data\test1.mdf',SIZE=10,
MAXSIZE=50,
FILEGROWTH=5）
LOGON
（NAME='test1Log',
FILENAME='c:
\ProgramFiles\MicrosoftSQLServer\MSSQL.1\MSSQL\Data\test1Log.ldf',SIZE=5MB,
MAXSIZE=25MB,
FILEGROWTH=5MB）
（2）使用对象资源管理器创建数据库
2、创建基本表的方法：
（1）使用SQL语句创建基本表
CREATETABLE<表名>
（<列名><数据类型>[<列级完整性约束条件>]
[，<列名><数据类型>[<列级完整性约束条件>]]…
[，<表级完整性约束条件>]）；
例如：
CREATETABLE学生（
学号intPRIMARYKEYCLUSTERED,
姓名CHAR（8）NOTNULL,
性别CHAR
（2）CHECK（性别IN（'男','女'）））
（2）使用对象资源管理器创建基本表
3、定义索引方法
CREATE[UNIQUE][CLUSTER]INDEX<索引名>ON<表名>（<列名>[<次序>][,<列名>[<次序>]]…）；
4、定义数据库完整性
（1）定义表的完整性及约束
1.唯一性约束：
UNIQUE
2.非空值约束：
NOTNULL
3.检查约束（check）：
当对表进行插入或更新操作时，检查其中的列值必须满
足的约束条件。
4.主键约束（primarykey）：
限制表中指定列的取值，以保证任何两列在指定列
上不存在相同的值，同时主键约束列上的值不得为Null。
5.参照约束（foreignkey）：
如果两个表之间由主键和外键相联系，那么在对表
中的数据执行插入、删除和更新操作时，必须遵循它们之间的参照性规则
和各自的数据完整性规则。
6.缺省约束（default）：
向表插入新行时为没有给值的列指定一缺省值，用以增
强数据的完整性。
（2）创建和管理存储过程
CREATEPROCEDURE〈过程名〉[;〈版本号〉][@〈参数名〉〈参数类型〉[=〈缺省值〉][OUTPUT]„]
[WITHRECOMPILE|ENCRYPTION|RECOMPILE，ENCRYPTION]
AS〈SQL语句组〉
1）版本号是可选的整数，它用于将有相同名字的存储过程编为不同的组。
2）OUTPUT选项用于给调用者返回值。
3）RECOMPILE为重编译选项。
它要求每次执行都要对过程重编译和优化，并创建新的查询计划。
4）ENCYPTION为加密选项。
5）参数前加“@”为局部变量，参数前加“@@”则说明为全局变量。
过程的调用语句为：
EXE[UTE]〈过程名〉[[@〈参数名〉=]〈参数〉„[〈版本号〉]
（3）创建和管理触发器
CREATETRIGGER〈触发器名〉ON〈表名〉
[WITHENCRYPTION]
FOR{[DELETE][，][INSERT][，][UPDATE]}[WITHAPPEND][NOTFORREPLICATION]ASBEGIN〈SQL语句组〉
END
1）WITHENCRYPTION为加密选项。
2）DELETE触发器是当对表执行DELETE操作时删除元组，将删除的元组放入deleted表中。
检查delete表中的数据，确定该如何处理。
3）INSERT触发器在对执行插入数据操作时,将插入表中的数据拷贝并送入inserted表中，根据inserted表中的值决定如何处理。
4）UPDATE触发器仅在更新数据操作时将要被更新的原数据移入deleted表中，将更新后的数据备份送入inserted表中，对deleted和inserted表进行检查，并决定如何处理。
5）NOTFORREPLICATION项说明当一个复制过程在修改一个触发器表时，与该表相关联的触发器不能被执行。
例如：
createtriggertrig1
onsforupdate
asdeclare@bageint,@aageint
select@bage=sagefromdeleted
select@aage=sagefrominserted
if（@aage<@bage）
begin
print'年龄只能增长，更改失败'
rollback
end
触发器执行：
5、定义数据库安全性
（1）SQLserver安全认证模式：
windows安全验证和混合安全验证
（2）登录管理
登录（SQL服务器用户）通过账号和口令访问SQLserver数据库
（3）用户管理
登录用户只有成为数据库用户（databaseuser）后，才能访问数据库
（4）角色管理
（5）授权管理
1）通过对象资源管理器授权
2）通过DCL语言授权
GRANT语句的一般格式：
GRANT<权限>[,<权限>]...
[ON<对象类型><对象名>]
TO<用户>[,<用户>]...
[WITHGRANTOPTION];
（6）受限访问
三、实验内容
1、使用SQL语句建立如下数据库及基本表建库名称为“姓名1”，要求：
⑴将主数据库文件“姓名1.MDF”放置在C:
\ProgramFiles\Microsoft
SQL
Server\MSSQL.1\MSSQL\Data文件夹中，其文件大小自动增长为按5MB增长；
⑵将事务日志文件“学号姓名1.LDF”放置在C:
\ProgramFiles\MicrosoftSQLServer\MSSQL.1\MSSQL\Data文件夹中，其文件大小自动增长为按1MB增长。
建表如下：
1.建立学生表Students，其结构为：
学号，姓名，性别，年龄，籍贯，民族，email，
党员否，所在年级。
其中“学号”为主键，唯一标识。
姓名不允许为空。
性别只能为‘男’或‘女’；15〈年龄〈100；民族默认为“汉族”，籍贯默认为“天津”。
在年龄上建立降序索引。
2.建立教师表Teachers，其结构为：
教师号，姓名，性别，年龄，职称，email，
工资。
其中“教师号”为主键，唯一标识。
姓名不允许为空。
职称只能为‘教授’或‘副教授’，‘讲师’，‘助教’；
3.建立课程表Courses，其结构为：
课程号，课程名，学分，学时数。
其中“课
程号”为主键，唯一标识。
课程名不允许为空。
1〈学分〈5；15〈学时数〈1204.建立选课表Choices，其结构为：
学号，课程号，成绩。
其中“学号，课程号”
为主键。
0〈=成绩〈=100
5.建立任课表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，
备份次数据库：
右键任务——备份
升级会员 