SQLServer数据库设计实验指导书.doc

软件2012（12）SQL Server数据库设计实验指导书SQL Server数据库设计实验指导书软2012（12）SQL server 数据库设计课程实验一、本实验课在培养实验能力中的地位及作用数据库技术是一个理论和实际紧密联系的技术，而SQL server数据库设计则是数据库具体应用的一个数据库开发系统。在学习过程中，除了解SQL server系统的设计环境及各种工具的使用方法、数据库系统结构、数据库应用方法等知识外，通过实验对相关技术加深认识，切实加强学生的实践能力，是教学中一个重要且必要的环节。SQL server数据库设计是一门实践性很强的课程，只有通过上机实践才能真正领会主教材中介绍的知识。二、应达到的实验能力标准本实验的教学目标是使学生掌握如何使用SQL server 数据库开发系统，了解数据库设计及相关操作的基本概念与方法，进而学会建立与组织、操作数据库。上机实验的主要目标如下:(1)通过上机操作，加深对数据库系统理论知识的理解。(2)通过使用具体的DBMS，了解一种实际的数据库管理系统并掌握其操作技术。(3)通过上机实验，提高动手能力，提高分析问题和解决问题的能力。三、实验要求学生在实验课前认真做好预习，事先写好实验报告。实验结束及时提交实验报告。三、实验成绩考核方法实验成绩在课程总成绩中占25%，包括平时每次实验考核与最后一次实验考试。实验内容目录实验1 SQL server常用工具的使用及数据库的创建实验2 表的创建与操作实验3 数据的简单查询 实验4 数据的高级查询实验5 SQL Server视图的创建及使用 实验6 SQL Server 的程序设计、存储过程与触发器实验7 SQL Server的数据库安全、恢复与备份实验8 综合练习实验1 常用工具的使用及数据库的创建和管理实验目的：1. 掌握SQL server的安装方法及其常用工具的使用方法2. 掌握SQL server数据库的创建和管理实验内容：1熟悉SQL server常用工具。2在图形界面下创建数据表名为student的数据库，并设置数据库主文件名为syudent_data,大小为10MB;日志文件为student_log,大小为2MB.其他参数取默认值。3. 使用TransactSQL语言创建一个student1数据库，主文件逻辑名为student1_data，物理文件名为student1.mdf,为10MB,增长速度为10%;数据库的日志文件逻辑名为syudent1_log, 物理文件名为student1.ldf,初始化大小为1MB,最大尺寸为5MB, 增长速度为1 MB；文件存放路径为c:data文件夹下。Create database student1On primry(name=student1_data, filename=c:datastudent1.mdf, size=10,maxsize=unlimited, filegrowth=10%)log on(name=student1_log, filename=c:datastudent1.ldf, size=1,maxsize=5, filegrowth=1)4. 在图形界面下创建student,sc,course三个表,并添加数据。5. 用查询分析器中，查看pubs数据库中authors表的数据信息。Use pubsGoSelect * from authors6. 把student数据库备份与恢复7. 把C:Program FilesMicrosoft SQL ServerMSSQLdatastudent_data, student_log文件复制到自己U盘，然后再把该数据库文件恢复。实验2 表的创建与操作实验目的：1. 掌握用SQL 语句创建表与表的修改修改2. 掌握用菜单添加、修改表格数据3. 掌握索引的建立与主键、外键建立实验内容：1在student数据库中创建一个名为pupil的表，要求：(sno char(6) not null,sname char(10) not null,ssex char(2) not null, sage int, sdept char(20)，其中表中字段满足：sno设置为主键，sname字段设置惟一性约束；2将pupil表的ssex和birthday设置检查性约束，要求ssex只能为男或女，sdept字段设置默认约束，值为软件学院3在student数据库中创建一个名为course的表（cno, cname, teacher）。4分别在student,sc,course创建三个表的主键5在学生表中，创建按年龄降序索引6创建一个学生选课表sc(sno char(6) not null,cno char(10) not null,grade real).将sc表创建外键约束，把sc表的sno和student表的sno关联起来，在这两个表之间创建一种制约关系。7. 分别往student,sc,course三个表添加5条记录 8查询年纪小于20的学生信息9查询成绩及格的选课信息的学生的学号10查询成绩及格的选课信息的学生的学号，姓名实验3 数据的简单查询实验目的：1. 掌握SELECT 语句的基本语法2. 掌握子查询及order by 子句用法3. 掌握SELECT 语句的统计函数的作用和使用方法实验内容：1针对student表查询所有学生的基本信息，并按出生日期升序排列。2针对student表查询女同学的信息和女同学的人数。3在student表中查询所有男同学的年龄。4在student表中，(1) 查询刘姓学生的信息;(2) 查询polity为团员或党员的学生信息。5在sc表中，(1) 查询各门课程的选课人数; (2) 查询缺少成绩的学生的学号及课程号;(3) 查询每个学生选课选课数;6查询与刘成同一个系的学生情况。7查询选修了课程名为MIS的学生的学号和姓名8查询pubs数据库的Publishers表中居住在Boson或不住在Paris的出版商信息。9查询pubs数据库的salers表中saleID为s01，s05，s07的销售人员的信息。10查询pubs数据库的authors表中作者居住的城市和州。11在Northwind数据库的Products表中查询出价格在5-10元之间的产品信息。12在Northwind数据库的orders表中的数据，先按CustomerID的升序排列，当CustomerID相同时再按照SaleID的降序排列。注意：如果没有Northwind，pubs 示例数据库，自己会添加实验4 数据的高级查询实验目的：1. 掌握表中数据的查询方法及操作方法2. 掌握连接查询与嵌套查询的方法3. 掌握SELECT 语句的GROUP BY 和COMPUTE BY子句使用方法实验内容：1在student数据库中，分别用等值连接与自然连接查询计算机系选课的学生的所有信息。2针对student、sc、course三张表所有选课学生的姓名、选修课程名及成绩3在student、sc、course三张表中，查出不及格学生的姓名。4查出数据库成绩80以上格学生的姓名。5统计男女同学人数。6查询比张三的所有成绩都高的学生的档案信息（嵌套查询）。7在Northwind数据库的Order Details表中，使用group by子句对所有订单按订单号汇总每类产品的数量信息。 注：引用表 order details8用UNION子句将Customers表中顾客ID、姓名及Salers表中销售人员的ID、姓名组合在一个结果集中。9在Northwind数据库的Products表中显示所有价格高于平均价格的产品。10将Products表中的数据按CategoryID进行分组，然后分别统计每一组产品的平均价格及总库存。11在Northwind数据库的Products表中，对所有价格大于5元且组平均价格大于15元的价格表目，列出所有的产品的类型、平均价格及总库存。补充：在查询的基础上创建新表，例如：将查询结果保存到新表temp_orders。SELECT * INTO temp_orders FROM orders ORDER BY CustomerID,SaleID DESC 实验5 数据更新与视图的创建及使用实验目的：1. 插入数据、修改数据、删除数据2. 通过企业管理器和Transact_SQL语句对视图的创建及调用3. 掌握视图的修改与删除4. 掌握用Insert,Update通过视图更新数据实验内容：1向学生表，用insert语句向其中插入一条新的记录：（0007，张三，男，20,计算机系）2利用update语句将编号为0004的学生年龄改为233在sc表中，利用update语句把英语成绩提高5%4删除年龄小于20的学生记录。5删除王军的的选课记录。6通过Transact_SQL语句创建一个视图，计算各门课程的平均分。7创建一个视图，显示高等数学未过的学生的信息。8创建一个视图，查询的学号为99521学生的成绩。9在STUDENT 库中以student表为基础，建立计算机学院学生的视图（视图名为：V_计算机系学生）。在使用该视图时，将显示学生信息。10使用视图“V_计算机系学生”查询所有男同学的信息。11在Northwind库中创建了一个基于表employees的视图v_employees，然后通过该视图修改表employees中的记录。12利用上题（题7）视图v_employees删除表employees中姓名为张然的记录。补充：视图常用_SQL语句创建：create view 视图名 with encryption as select_statement with check option修改：利用 alter view 视图名删除：利用 drop view 视图名实验6 SQL Server程序设计、存储过程与触发器实验目的：1. 掌握SQL 语言流程控制语句与游标的使用2. 掌握SQL Server 的存储过程3. 掌握SQL Server 的触发器实验内容：1在pubs中根据作者所在州的名字,打印出作者的姓名和洲的全名。2在pub库中，若titles表中不存在书价低于$30的情况下,反复执行Begin-end语句块中内容：首先将所有书的价格提高50%,并在最高书价超过$50的情况下跳出循环。3使用Transact_SQL语句定义一个名为student_cur的游标，用于访问表中性别为男学生的Student_id,Name,Age和Sex 四个字段的信息。4使用Transact_SQL语句通过student_cur的游标，读取表中Student_id,Name,Age和Sex 四个字段的值。5创建一个存储过程stugradeinfo,查询班级、学号、姓名、性别、课程名称、分数。6利用企业管理器创建一个存储过程stu_info，根据传入的编号，查询某学生的基本信息。7创建一个insert触发器,当在pupil表中插入一条新记录时，给出你已经插入了一条新记录！的提示信息。8创建一个insert触发器,当在pupil表中插入一条新记录时，不允许在学号中出现重复的编号或出现空值。9创建一个insert触发器,当在sc表中插入一条新记录时，sno和cno必须是已经存在的学号和课程号，且grade应该在0-100之间。10创建一个after触发器,在pupil表中删除某学生的记录时，删除其相应的选课记录。11创建一个instead of 触发器，当在course表中上出记录时，不允许删除course表中的数据。实验7 SQL Server的数据库安全、恢复与备份实验目的：1. 掌握SQL Server数据库的完整性约束2. 掌握SQL Server 的恢复与备份，数据的导入与导出3. 掌握SQL Server 的安全与权限实验内容：1在sc表中，成绩值应该在0-100之间，对成绩字段创建CHECK约束，使取值在正常范围内。2在pupile表中，对polity字段建立一个默认约束，内容为团员。3在student数据库中建立一个规则,其名称为“专业规则”. 将专业规则绑定到学生表的专业字段上,可以限制录入或修改记录时提供的专业名称必须是电子商务,计算机网络,信息管理。 4使用企业管理器对一数据库做一次完全数据备份与恢复。5把下列文本文件导入到SQL Server数据库当中。6将pupil表导出到一个文本文件与access数据库当中。7利用DTS设计器将表从一个数据库复制到另一个数据库当中。8创建了一个名叫“ssq”，密码是“518405”，默认数据库为student的登陆帐户。9使用系统存储过程sp_password修改登录帐户的密码，把ssq帐户的密码改为208107。实验8 综合练习实验目的：1. 复习表的基本操作2. 复习基本查询与高级查询 3. 掌握SQL Server 用户自定义函数的创建方法实验内容：1从pupil表中检索出所有姓张的学生资料。2从pupil表中检索出名字的第二个字是“伟”或“炜”的所有学生资料。3在pub库中，若titles表中经济类书籍的平均价格低于20美元，就检索所有经济类图书；否则，则打印