数据库实验报告(完整版)

数据库实验报告班级：07111103学号：1120111857姓名：曹睿鹏实验一：[实验内容1创建和修改数据库]分别使用SQLServerManagementStudio和Transact-SQL语句，按下列要求创建和修改用户数据库。1.创建一个数据库，要求如下:(1)数据库名testDB。(2)数据库中包含一个数据文件，逻辑文件名为testDB_data，磁盘文件名为testDB_data.mdf，文件初始容量为5MB，最大容量为15MB，文件容量递增值为1MB。(3)事务日志文件，逻辑文件名为TestDB_log,磁盘文件名为TestDB_log.ldf,文件初始容量为5MB,最大容量为10MB，文件容量递增值为1MB。2.对该数据库做如下修改:(1)添加一个数据文件，逻辑文件名为TestDB2_data，实际文件为TestDB2_data.ndf,文件初始容量为1MB，最大容量为6MB，文件容量递增值为1MB。(2)将日志文件的最大容量增加为15MB，递增值改为2MB。方法一：使用SQLServerManagementStudio创建和修改数据库TestDB方法二：使用Transact-SQL语句创建和修改数据库TestDB方法一过于简单，暂不做讨论。下面学习方法二。首先，在sqlsever2008中单击新建查询。然后键入下面的代码。建立新的数据库。1.创建一个数据库，要求如下:2.对该数据库做如下修改:对刚刚的操作进行验证数据均已更新完毕。[实验内容2数据表的创建、修改和查询]1.熟悉有关数据表的创建和修改等工作，并了解主键、外键以及约束的创建和应用，熟练掌握使用SQLServerManagementStudio和CREATETABLE、ALTERTABLE等Transact-SQL语句对数据表的操作方法项目表（Project）字段名数据类型字段长度注释项目编码char10主键名称varchar负责人编码char10客户int开始日期datetime结束日期datetime员工数据表(Employee)字段名数据类型字段长度注释员工编码char10主键姓名varchar性别varchar所属部门varchar工资money方法一：使用SQLServerManagementStudio创建数据表并添加约束方法二:使用Transact-SQL语句创建数据表并添加约束2.向数据库TestDB中的两个数据表项目数据表和员工数据表中添加记录3.在查询分析器中书写Transact-SQL语句完成数据查询。(1)查询项目数据表中客户字段的唯一值，并查看查询结果。(2)查询工资高于2000的项目部的人员的姓名。(3)查询来自CCH公司的项目名称(以CCH开始)和负责人姓名(4)查询每个部门的平均工资，结果按照平均工资的多少排序(5)查询所有的员工姓名和负责人的项目名称(6)使用子查询输出所有负责CCH公司项目(以CCH开始)的员工姓名，以及没有负责REALIDEA公司(以REALIDEA开始)项目的员工姓名(7)将所有REALIDEA公司的项目的结束日期更改为2011年1月8日(8)录入部的张晓峰决定辞职，请将员工数据库中有关他的记录删除，并将他负责的项目移交给杨亭亭。书写语句对数据表做相应的更改4.掌握使用T-SQL语句创建视图的方法，包括视图的建立、删除、修改；了解如何应用视图有选择地查看所需数据，并熟悉通过视图更改数据表中数据的方法。(1)基于表项目数据表和员工数据表创建视图，要求为:视图名为员工项目。包含字段编号、姓名、名称和开始日期。字段别名分别是员工编号、”员工姓名、项目名称、项目开始日期。(2)使用INSERT语句通过视图向员工数据表中添加一条记录，要求姓名字段值为马中兴。(3)建立适当的视图，将所有的表连接起来，观察数据，体会建立多个表的好处下面演示建立表的过程：1.首先运行涂蓝代码，建立员工数据表，并设立主键。因为在项目数据表中需要添加员工数据表的主键为外键。二.建立项目数据表：然后向两表中添加元素。注：在这里有血的教训，没有给varchar限定值的范围导致数据录入不了。。。。。好悲惨，又推到重写的一遍。下面是建好的表：员工数据表项目数据表三、1.查询项目数据表中客户字段的唯一值，并查看查询结果。2.查询工资高于2000的项目部的人员的姓名。3.查询来自CCH公司的项目名称(以CCH开始)和负责人姓名4.查询每个部门的平均工资，结果按照平均工资的多少排序5.查询所有的员工姓名和负责人的项目名称6.使用子查询输出所有负责CCH公司项目(以CCH开始)的员工姓名，以及没有负责REALIDEA公司(以REALIDEA开始)项目的员工姓名*由于编造数据的疏忽，没有加入REALIDEA公司，这时希望改为以KK开头的公司的项目注意到这张表中没有了负责KKK项目的王一飞这个人，现在表有9个元素，原表有10个元素。7.将所有REALIDEA公司的项目的结束日期更改为2011年1月8日先建立试图，然后在视图上操作，经验证结果完全正确（下图第二行）练习一下视图的建立，哈哈8.录入部的张晓峰决定辞职，请将员工数据库中有关他的记录删除，并将他负责的项目移交给杨亭亭。书写语句对数据表做相应的更改/*录入部的张晓峰决定辞职，请将员工数据库中有关他的记录删除，并将他负责的项目移交给杨亭亭。书写语句对数据表做相应的更改*/UPDATE项目数据表SET负责人编码=(SELECT员工编码FROM员工数据表WHERE姓名='刘楠')WHERE负责人编码IN(SELECT员工数据表.员工编码FROM员工数据表WHERE员工数据表.姓名='刘哲'){消息2627，级别14，状态1，第1行违反了PRIMARYKEY约束'表一约束'。不能在对象'dbo.项目数据表'中插入重复键。语句已终止。}DELETEFROM员工数据表WHERE姓名='刘哲'{消息547，级别16，状态0，第1行DELETE语句与REFERENCE约束表一约束冲突。该冲突发生于数据库testDB，表dbo.项目数据表,column'负责人编码'。语句已终止。}由于设定主键时将员工编码和负责人编码均为主键，且员工编码为负责人编码的外键，导致要想修改负责人编码只能先删除于是不能完成这一操作。。。。四、掌握使用T-SQL语句创建视图的方法，包括视图的建立、删除、修改；了解如何应用视图有选择地查看所需数据，并熟悉通过视图更改数据表中数据的方法。1.基于表项目数据表和员工数据表创建视图，要求为:视图名为员工项目。包含字段编号、姓名、名称和开始日期。字段别名分别是员工编号、”员工姓名、项目名称、项目开始日期。下面打开视图界面2.使用INSERT语句通过视图向员工数据表中添加一条记录，要求姓名字段值为马中兴。以下是查看图3.建立适当的视图，将所有的表连接起来，观察数据，体会建立多个表的好处下面是效果图：[实验内容3创建触发器和存储过程]理解触发器的触发过程和类型，掌握创建触发器的方法。1.创建触发器，将插入员工的工资额限制在5000以内。2.创建触发器，将员工工资修改变动额限制在2000以内。3.删除员工数据表数据时，判断该员工是否有负责的项目，如果有，则不允许删除、并给出提示“将负责项目移交后再删除”的提示信息。4.在视图基础上创建insteadof触发器，实现通过视图修改数据的功能，具体题目自拟。5.创建存储过程，要求返回某一特定部门所有员工的工资总和，其中特定部门的名称以存储过程的输入参数进行传递。6.创建函数，题目自拟。7.创建带有游标的存储过程，题目自拟。解答：1.创建触发器，将插入员工的工资额限制在5000以内。说明已经创建了触发器第一条说明正常的插入语句是可以的第二条说明当工资大于5000时不可以被插入。2.创建触发器，将员工工资修改变动额限制在2000以内。以下是验证：3.删除员工数据表数据时，判断该员工是否有负责的项目，如果有，则不允许删除、并给出提示“将负责项目移交后再删除”的提示信息。USEtestDBGOdeleteFROM员工数据表WHERE姓名='赵玉琼'出现如下语句：消息547，级别16，状态0，第1行DELETE语句与REFERENCE约束表一约束冲突。该冲突发生于数据库testDB，表dbo.项目数据表,column'负责人编码'。语句已终止。由于在建表时的疏忽，故导致无法执行删除操作，望老师原谅啊。。。。但触发器建立正确（：4.在视图基础上创建insteadof触发器，实现通过视图修改数据的功能，具体题目自拟。5.创建存储过程，要求返回某一特定部门所有员工的工资总和，其中特定部门的名称以存储过程的输入参数进行传递。这时发现并没有输出钱数，回去寻找原因，发现时@总工资后没加OUTPUT这时输出了正确的结果。6.计算制员工工资是否高于所在部门的工资平均值下面是函数测试结果:[实验内容4备份、恢复数据库及安全控制]理解数据库备份的过程和属性设置，掌握使用SQLServerManagementStudio备份数据库的方法。1.使用SQLServerManagementStudio备份和恢复数据库.2.创建新用户、新用户授予权限、用户权限回收.1.使用SQLServerManagementStudio备份和恢复数据库.关闭数据库再次打开即可恢复，在此不做演示。2.创建新用户、新用户授予权限、用户权限回收.第一次在权限设定页面出错，重新设定用户ccrrpp1，得到正确结果。用T-SQL语句授予权限:用T-SQL语句收回权限：到这里为止实验报告已经完成了。。就是有点小遗憾在建表时加了外键限制导致删除操作执行起来有些BUG，不过总的来说通过这次实验收获了好多的知识，上机操作的收获是看书所无法体会的，非常感谢老师这么辛苦的教授大家知识，谢谢老师。