最新《sql-server-数据库应用》实验指导书-版.docx

Four short words sum up what has lifted most successful individuals above the crowd: a little bit more.-author-dateSQL-SERVER-数据库应用实验指导书-2015版目的和要求数据库应用实验教学大纲一、基本信息课程编码020215136课程学时40课程类别专业课实验总学时12开出学期5开出单位计算机系适用专业 计算机科学与技术二、实验安排序号实 验 项 目实验学时每组人数实验类型开出要求1实验一 创建数据库和表以及表操作21验证必做实验二 数据库的查询21验证必做实验三 视图、存储过程、触发器等的建立与维护41验证必做实验四 基于SQL Server数据库的学生信息管理系统的建立41综合必做三、实验目的、内容与要求实验一 创建数据库和表以及表操作(一) 实验目的1了解SQL Server数据库的逻辑结构和物理结构，表的结构特点；2了解SQL Server的基本数据类型，空值概念；3学会在企业管理器中创建数据库和表；4学会使用TSQL语句创建数据库和表；5学会在企业管理器中对数据表进行插入、修改、删除数据操作；6学会使用T-SQL对数据表进行插入、修改、删除数据操作；(二) 实验内容1企业管理器访问数据库，查询分析器的使用；2在企业管理器中创建、删除数据库和表； 3使用T-SQL语句创建数据库和表；4在企业管理器中对数据表进行插入、修改、删除数据操作；5使用T-SQL进行上述操作。(三) 实验要求1掌握在企业管理器中创建、修改、删除数据库和表；2掌握查询分析器，使用TSQL语句创建、修改、删除数据库和表。实验二 数据库的查询(一) 实验目的1掌握SELECT语句的基本语法；2掌握子查询的表示；3掌握连接查询的表示；4掌握数据汇总的方法；5掌握SELECT语句的GROUP BY子句的作用和使用方法；6掌握SELECT语句的ORDER BY子句的作用和使用方法。(二) 实验内容1SELECT语句的基本使用；2子查询的使用，连接查询的使用，数据汇总，Group By、Order By子句的使用。(三) 实验要求1掌握数据库查询的基本的常用语句的使用方法；2掌握数据库查询中的分组、排序等语句的使用方法。实验三 视图、存储过程、触发器等的建立与维护(一) 实验目的1学会使用企业管理器建立视图，应用视图插入、删除、修改数据；2掌握存储过程的使用方法；3掌握触发器的使用方法。 (二) 实验内容1视图插入、删除、修改数据；2创建存储过程，调用存储过程；3创建触发器。(三) 实验要求1利用所创建的数据库和数据表，综合应用视图、存储过程、触发器等知识完善数据库；2掌握应用更新视图数据可以修改基本表数据的方法；3熟练掌握添加、修改、删除记录的存储过程的定义及调用；4掌握通过触发器来实现数据的参照完整性。实验四 基于SQL Server数据库的学生信息管理系统的建立四、考核方式实验的考核方式: 根据实验预习情况、实验出勤情况、原型系统效果验收和实验报告的完成情况确定最终的实验成绩，实验成绩占课程总成绩的20%。实验成绩按百分制。实验预习情况：A实验出勤情况：B原型系统效果验收和实验报告：C实验成绩=A*20%+B*20%+C*60%五、建议教材与教学参考书1、建议教材1郑阿奇SQL Server实用教程第二版北京:电子工业出版社20052 张丽霞数据库应用实验指导书自编20052、教学参考书1李存斌数据库应用技术-SQL Server 2000简明教程北京:中国水利水电出版社.2001 2钱雪忠，罗海驰，程建敏.SQL SERVER 2005实用技术及案例系统开发.第一版.北京：清华大学出版社.2007 3李春葆，曾惠.SQL SERVER 2000应用系统开发教程第一版.北京：清华大学出版社2005 4 李春葆，曾惠SQL SERVER 2000学习与上机实验指导第一版.北京：清华大学出版社20055李存斌.数据库应用技术-SQL Server 2000简明教程.第一版.北京:中国水利水电出版社.2002六、编制说明编制者：数据库课程组 组长：郭琳执笔人：云静 编制时间：2013年8月实验一 创建数据库和表以及表操作一、实验目的1了解SQL Server数据库的逻辑结构和物理结构，表的结构特点；2了解SQL Server的基本数据类型，空值概念；3学会在企业管理器中创建数据库和表；4学会使用TSQL语句创建数据库和表；5学会在企业管理器中对数据表进行插入、修改、删除数据操作；6学会使用T-SQL对数据表进行插入、修改、删除数据操作；二、实验内容 1实验题目(1) 创建用于企业管理的员工管理数据库，数据库名为YGGL，包含员工的信息、部门信息以及员工的薪水信息。数据库YGGL包含下列3个表。 (1)Employees：员工自然信息表。 (2)Departments：部门信息表。 (3)Salary：员工薪水情况表。 各表的结构分别如表T21，表T22，表T23所示。表T21 Employees表结构 列 名 数据类型 长度 是否允许为空值 说 明 EmployeelDChar 6×员工编号，主键 NameChar 10×姓名 BirthdayDatetime 8×出生日期 SexBit 1×性别 AddressChar 20地址 ZipChar 6邮编PhoneNumberChar12电话号码 EmailAddressChar 30电子邮件地址 DepartmentlDChar 3×员工部门号，外键表T22 Departments表结构 列 名 数据类型 长度 是否允许为空值 说 明 DepartmentlD 字符型(char) 3 × 部门编号，主键 DepartmentName 字符型(char) 20 × 部门名 Note 文本(text) 16 备注表T23 Salary表结构 列 名 数据类型 长度 是否允许为空值 说 明 EmployeeID 字符犁(char) 6 × 员工编号，主键 InCome 浮点型(float) 8 × 收入 OutCome 浮点型(float) 8 × 支出 实验步骤 1在企业管理器中创建数据库YGGL 要求：数据库YGGL初始大小为10MB，最大大小为50MB，数据库自动增长，增长方式是按5比例增长；日志文件初始为2MB，最大可增长到5MB(默认为不限制)，按1MB增长(默认是按5比例增长)。数据库的逻辑文件名和物理文件名均采用默认值，分别为 YGGL_data和e：sqldataMSSQLDataYGGLmdf，其中e：sqldataMSSQL为SQL Server的系统安装目录；事务日志的逻辑文件名和物理文件名也均采用默认值分别为YGGLLOG和 e：sqldataMSSQLDataYGGL_Log1df。 以系统管理员Administrator是被授权使用CREATE DATABASE语句的用户登录SQL Server服务器，启动企业管理器一>在服务器上单击鼠标右键一>新建数据库一>输入数据库名“YGGL”一>选择“数据文件”选项卡一>设置增长方式和增长比例一>选择“事务口志”选项卡一设置增长方式和增长比例。 注意：在“数据文件”选项卡和“事务曰志”选项卡中可以分别指定数据库文件和曰志文件的物理路径等特性。2在企业管理器中删除创建的YGGL数据库在企业管理器中选择数据库YGGL一>在YGGL上单击鼠标右键一删除。3使用TSQL语句创建数据库YGGL按照实验步骤1中的要求创建数据库YGGL。启动查询分析器一>在“查询”窗口中输入以下TSQL语句： CREATE DAIABASE YGGL ON( NAME=YGGL_Data， FILENAME=e：ksqldataMSSQLDataYGGLmdf， SIZE=10MB, 、 MAXSIZE=50MB,FILEGROWTH=5) LOGON( NAME=YGGL_Log， FILENAME=e：sqldataMSSQLDataYGGL_Log1df, SIZE=2MB, MAXSIZE=5MB，FILEGROWTH=1MB) G0单击快捷工具栏的执行图标，执行上述语句，并在企业管理器中查看执行结果。 4在企业管理器中分别创建表Employees，Departments和Salary 在企业管理器中选择数据库YGGL一>在YGGL上单击鼠标右键一>新建一>表一>输入 Employees表各字段信息一>单击保存图标一>输入表名Employees，即创建厂表Employees。按同样的操作过程创建表Departments和Salary。 、5在企业管理器中删除创建的Employees，Departments和Salary表 在企业管理器中选择数据库YGGL的表Employeesà在Employees上单击鼠标右键一>删除，即删除了表Employees。按同样的操作过程删除表Departments和Salary。6使用TSQL语句创建表Employees，Departments和Salary 启动查询分析器一.在“查询”窗口中输入以下TSQL语句： USE YGGL CREATE TABLE Employees ( EmployeeID char(6)NOT NULL， Name char(10)NOT NULL， Birthday datetime NOT NULL， Sex bit NOT NULL， ddress char(20)NOT NULL, Zip char(6)NULL， PhoneNumber char(12)NULL， EmailAddree char(20)NULL， DepartmentlD char(3)NOT NULL ) GO 单击快捷工具栏的执行图标，执行上述语句，即可创建表Employees。按同样的操作过程创建表Departments和Salary，并在企业管理器中查看结果。2.实验题目(2) 分别使用企业管理器和TSQL语句，向在以下实验步骤1建立的数据库YGGL的3个表Employees，Departments和Salary中插入多行数据记录，然后修改和删除一些记录。使用 TSQL进行有限制的修改和删除。 实验准备 、 首先了解对表数据的插入、删除、修改都属于表数据的更新操作。对表数据的操作可以在企业管理器中进行，也可以由T-SQL语句实现。 其次要掌握T-SQL中用于对表数据进行插入、修改和删除的命令分别是INSERT, UPDATE和DELETE(或TRANCATE TABLE)。 要特别注意在执行插入、删除、修改等数据更新操作时，必须保证数据完整性。 此外，还要了解使用T-SQL语句在对表数据进行插入、修改及删除时，比在企业管理器中操作表数据更为灵活，功能更强大。实验步骤1 在企业管理器中向数据库YGGL表加入数据2 (1)在企业管理器中向表Employees中加入如表T31所示的记录。表T31数据记录 在企业管理器中选择表Employeesà在其上单击鼠标右键一>选择“返回所有行”一>逐字段输入各记录值，输入完后，关闭表窗口。 (2)在企业管理器向表Departments中插入如表T32所示的数据记录。表T32数据记录(3)在企业管理器中向表Salary中插入如表T33所示的数据记录。表T33数据记录 2在企业管理器中修改数据库YGGL表数据 (1)在企业管理器中删除表Employees的第2，8行和Salary的第2，11行。注意进行删除操作时，作为两表主键的EmployeeID的值，以保持数据完整性。在企业管理器中选择表Employees>在其上单击鼠标右键一>选择“返回所有行”一>选择要删除的行一>单击鼠标右键一>删除一>关闭表窗口。 (2)企业管理器中删除表Departments的第2行，同时也要删除表Employees的第2行。操作方法同(1)。 (3)在企业管理器中将表Employees中编号为020018的记录的部门号改为4。 在企业管理器中选择表Employeesà在其上单击鼠标右键一选择“返回所有行”一>将光标定位至编号为020018的记录的DepartmentID字段，将值1改为4。3.使用T-SQL命令修改数据库YGGL表数据 (1)使用T-SQL命令分别向YGGL数据库Employees，Departments和Salary表中插入1行记录。 启动查询分析器一>在“查询”窗口中输入以下TSQL语句： USE YGGL INSERT INTO Employees VALUES(011112，罗林，1973-5-3，1，解放路100号，210002，4055663，NULL，5) GO INSERT INTO Departments VALUES(2，人资源部，NULL) Go INSERT INTO Salary VALUES(011112，120009，50) GO 单击快捷工具栏的执行图标，执行上述语句。 注意：在企业管理器中分别打开YGGL数据库Employees，Departments和Salary表，观察数据变化。 (2)使用TSQL命令修改表Salary中的某个记录的字段值。 启动查询分析器一在“查询”窗口中输入以下TSQL语句： USE YGGL UPDATE Salary SET income=2890 WHERE EmployeelD=t011112 Go 单击快捷工具栏的执行图标，执行上述语句，将编号为011112的职工收入改为2890。 注意：在企业管理器中分别打开YGGL数据库Salary表，观察数据变化。 (3)修改表Employees和Departments的记录值，仍要注意完整性。 操作过程同(2)。 (4)使用TSQL命令修改表Salary中的所有记录的字段值。 启动查询分析器一>“查询”窗口中输入以下 TSQL语句： USE YGGL UPDATE Salary SET InCome=InCome+100 GO单击快捷工具栏的执行图标，执行上述语句，将所有职工的收入增加100。可见，使用TSQL语句操作表数据比在企业管理器中操作表数据更为灵活。 注意：输入以下TSQL语句，观察数据变化。 SELECT * FROM Salary (5)使用TRANCATE TABLE语句删除表中所有行。 启动查询分析器一>在“查询”窗口中输入以下 TSQL语句： USE YGGL TRANCATE TABLE Salary GO 单击快捷工具栏的执行图标，执行上述语句，将删除Salary表中的所有行。 注意：实验时一般不轻易做这个操作，因为后面实验还要用到这些数据。如要试验该命令的效果，可建一个临时表，输入少量数据后进行。实验二 数据库的查询一、实验目的1掌握SELECT语句的基本语法；2掌握子查询的表示；3掌握连接查询的表示；4掌握数据汇总的方法；5掌握SELECT语句的GROUP BY子句的作用和使用方法；6掌握SELECT语句的ORDER BY子句的作用和使用方法。二、实验内容 1SELECT语句的基本使用 (1)对于实验1给出的数据库表结构，查询每个雇员的所有数据。 在查询分析器的窗口输入如下的语句并执行： USE YGGL SEIECT * FROM Employees GO 【思考与练习】 用SELECT语句查询Departments和Salary表的所有记录。 (2)查询每个雇员的地址和电话。 在查询分析器的窗口输入如下的语句并执行： USE YGGL SELECT Address,PhoneNumber FROM Employees GO 【思考与练习】 用SELECT语句查询Departments和Salary表的一列或若干列。 (3)查询EmployeeID为000001的雇员的地址和电话。 在查询分析器的窗口输入如下的语句并执行： USE YGGL SELECT Address，PhoneNumber 7 FROM Employees WHERE EmployeelD=000001 GO 【思考与练习】 用SELECT语句查询Departments和Salary表中满足指定条件的1列或若干列。 (4)查询Employees表中女雇员的地址和电话，使用AS子句将结果中各列的标题分别指定为地址、电话。 在查询分析器的窗口输入如下的语句并执行： USE YGGL SELECT Address AS地址PhoneNumber AS电话 FROM Employees WHERE sex=0Go注意：使用AS子句可指定目标列的标题。(1) 计算每个雇员的实际收入。在查询分析器的窗口输入如下的语句并执行： USE YGGL SELECT EmployeelD，实际收入=InComeOutComeFROM Salary G0(2) 找出所有姓王的雇员的部门号。在查询分析器的窗口输入如下的语句并执行： USE YGGL SELECT DepartmentID FROM EmployeesWHERE name LIKE王 Go【思考与练习】找出所有其地址中含有中山”的雇员的号码及部门号。(3) 找出所有收入在20003000元之间的雇员号码。在查询分析器的窗口输入如下的语句并执行： USE YGGL SELECT EmployeelDFROM SalaryWHERE InCome BETWEEN 2000 AND 3000 GO 【思考与练习】 找出所有在部门1或2工作的雇员的号码。 注意：在SELECT语句中LIKE、BETWEENAND,，IN，NOT及CONTAIN谓词的作用。3 子查询的使用(1)查找在财务部工作的雇员的情况。在查询分析器的窗口输入如下的语句并执行：USE YGGL SELECT * FROM Employees WHERE DepartmentlD= 。 (SELECT DepartmentlD FROM DepartmentsWHERE DepartmentName=财务部) GO【思考与练习】用子查询的方法查找所有收入在2500元以下的雇员的情况。(2)查找财务部年龄不低于研发部雇员年龄的雇员的姓名。在查询分析器的窗口输入如下的语句并执行： USE YGGL SELECT Name FROM Employees WHERE DepartmentID IN (SELECT DepartmentID FROM Departments WHERE DepartmentName财务部) AND Birthday!>ALL(SELECT Birthday FROM Employees WHERE DepartmentlD IN (SELECT DepartmentlD FROM Departments WHERE DepartmentName=研发部) GO 【思考与练习】用子查询的方法查找研发部比所有财务部雇员收入都高的雇员的姓名。(3)查找比所有财务部的雇员收入都高的雇员的姓名。在查询分析器的窗口输入如下的语句并执行： USE YGGL SELECT Name FROM Employees WHERE EmployeeID IN (SELECT EmployeelD FROM Salary WHERE InCome> ALL ( SELECT InCome FROM Salary WHERE EmployeelD IN (SELECT EmployeelD FROM Employees WHERE DepartmentlD= (SELECT DepartmentlD FROM DepartmentsWHERE DepartmentName=财务部) GO【思考与练习】用子查询的方法查找所有年龄比研发部雇员年龄都大的雇员的姓名。3连接查询的使用(1) 查询每个雇员的情况以及其薪水的情况。在查询分析器的窗口输入如下的语句并执行： USE YGGL SELECT Emploees.*,Salary* FROM Employees,Salary WHERE ElmployeesEmployeelD=SalaryEmployeelD GO【思考与练习】查询每个雇员的情况以及其工作部门的情况。(2) 查找财务部收入在2200元以上的雇员姓名及其薪水详情。在查询分析器的窗口输入如下的语句并执行： USE YGGLSELECT Name,InCome,OutCome FROM Employees，Salary，Departments WHERE EmployeesEmployeelD=SalaryEmployeelD AND EmployeesDepartmentlD=DepartmentsDepartmentlD AND DepartmentName=财务部AND InCome>2000 GO【思考与练习】查询研发部在1966年以前出生的雇员姓名及其薪水详情。4 数据汇总(1)求财务部雇员的平均收入。在查询分析器的窗口输入如下的语句并执行： USE YGGLSELECT AVG(InCome)AS 财务部平均收入 FROM Salary WHERE EmployeelD IN (SELECT EmployeelD FROM Employees WHERE DepartmentlD= (SELECT DepartmentlD FROM DepartmentsWHERE DepartmentName=财务部) GO【思考与练习】查询财务部雇员的最高和最低收入。(3) 求财务部雇员的平均实际收入。在查询分析器的窗口输入如下的语句并执行： USE YGGL SELECT AVG(1nCome-OutCome)AS财务部平均实际收入 FROM Salary WHERE EmployeelD IN (SELECT EmployeeID FROM Employees WHERE DepartanentlD = (SELECT DepartmentlD FROM DepartmentsWHERE DepamnentName=财务部) GO【思考与练习】查询财务部雇员的最高和最低实际收入。(4) 求财务部雇员的总人数。在查询分析器的窗口输入如下的语句并执行： USE YGGL SELECT COUNT(EmployeelD) FROM Employees WHERE DepartmentlD= (SELECT DepartmentlD FROM DepartmentsWHERE DepartmentName=财务部) GO【思考与练习】统计财务部收入在2500元以上雇员的人数。5 GROUP BY,ORDER BY子句的使用(1)求各部门的雇员数。在查询分析器的窗口输入如卜的语句并执行： USE YGGL SELECT COUNT(EmployeelD) FROM Employees GROUP BY DepartmentID Go【思考与练习】统计各部门收入在2000元以上雇员的人数。(2)将各雇员的情况按收入由低到高排列。在查询分析器的窗口输入如下的语句并执行： USE YGGL SELECT Employees*,Salary* FROM Employees，Salary WHERE EmployeesEmployeeID=SalaryEmployeeID ORDER BY InCome G0【思考与练习】将各雇员的情况按出生时间先后排列。 实验三 视图、存储过程、触发器等的建立与维护一、实验目的1学会使用企业管理器建立视图，应用视图插入、删除、修改数据；2掌握存储过程的使用方法；3掌握触发器的使用方法。二、实验内容 此实验是综合视图、存储过程、触发器等知识应用的一个综合性实验。实验要求：1利用所创建的数据库和数据表，综合应用视图、存储过程、触发器等知识完善数据库；2掌握应用更新视图数据可以修改基本表数据的方法；3熟练掌握添加、修改、删除记录的存储过程的定义及调用；4掌握通过触发器来实现数据的参照完整性。实验内容要求： 利用员工管理数据库YGGL中 3个表：Employees：员工自然信息表、Departments：部门信息表、Salary：员工薪水情况表。（1） 利用YGGL各表建立视图实现各种连接查询。建立视图view1,查询所有职工的员工编号、姓名、部门名和收入，并按部门名顺序排列。建立视图view2，查询所有职工的员工编号、姓名和平均工资。建立视图view3，查询各部门名和该部门的所有职工平均工资。（2） 编写对YGGL各表进行插入、修改、删除操作的存储过程，然后编写程序，调用这些存储过程。创建一个为Employees表添加员工记录的存储过程addEmployees。创建一个存储过程delEmployees删除Employees表中指定员工编号的记录。（3） 对于YGGL数据库，请用触发器实现两个表间的参照完整性。在表Departments上创建一个触发器Departments _update，当更改部门编号时同步更改Employees表中对应的部门编号。在表Employees上创建一个触发器Employees _delete，当删除员工记录时同步删除salary表中对应的工资收入记录。参考实例步骤： 1.创建视图（1）班级表（U_CLASSES ）：ID含义为"班号",CLASS含义为"班名",DEPARTMENT含义为所在 系,各字段类型按需要设置是否允许为空，ID字段被设置为主键。（2）成绩表（U_SCORES ）：STUDENT_ID含义为学号,COURSE_ID含义为课程号,SCORE为成绩,各字段类型按需要设置是否允许为空，STUDENT_ID 、COURSE_ID字段被设置为主键。（3）课程表（U_COURSES）：COURSE含义为课程名称， ID含义为课程编号，CREDIT含义为课程学分。（4）学生表(U_STUDENTS)，表结构如下: 序号字段类型(长度)主键含义1IDchar(10)是学号2NAMEvarchar(16)姓名3CLASS_IDtinyint所在班级编号1. 用企业管理器建立一个基于学生表、课程表、成绩表的视图，要求该视图显示学号、姓名、课程、成绩。 1) 启动企业管理器、注册、连接 2) 展开服务器、数据库、在视图上右击，在快捷菜单中执行"新建视图(V)." 3) 在新视图窗口内的关系图窗格内右击鼠标，弹出的菜单即为视图设计菜单，执行"添加表(B)." 4) 再在添加表对话框中选择U_SCORES表，再单击添加按钮。 5) 依此操作，分别添加U_STUDENTS、U_COURSES表，单击关闭按钮。 6) 再在关系窗格内，拖动U_STUDENTS表的"ID"至U_SCORES的STUDENT_ID,拖动U_COURSES表的"ID"至U_SCORES的COURSE_ID,再分别选中U_STUDENTS表的"ID","NAME"列(列前的复选框),U_COURSES表的"COURSE"列以及U_SCORES表的"SCORE"列,然后单击"!"按钮,显示视图结果。7) 单击保存按钮,将视图保存为V_SCORES,单击确定。 2. 用查询分析器建立一个基于学生表、班级表的学生视图(V_STUDENTS),包括学号、姓名、班级、系,SQL语句如下： 1) CREATE VIEW dbo.V_STUDENTSASSELECT dbo.U_STUDENTS.ID,dbo.U_STUDENTS.NAME, dbo.U_CLASSES.CLASS, dbo.U_CLASSES.DEPARTMENTFROM dbo.U_STUDENTS INNER JOINdbo.U_CLASSES ON dbo.U_STUDENTS.CLASS_ID = dbo.U_CLASSES.ID 3. 自己写一个SQL语句建立一个基于课程表的视图(V_COURSES)，要求显示课程编号、课程名、学分。 2创建存储过程在查询分析器编辑窗口输入各存储过程的代码并执行以下程序。(1) 添加职员记录的存储过程EmployeeAdd： USE YGGL GO CREATE PROCEDURE Emplo)reeAdd(employeeid char6)，name char(10)，birthday datetime，sex bit，address char(20)，zip char(6)，phonenumber char(12)，emailaddress char(20)，departmenflD char(3) AS BEGIN INSERT INTO Employees VALUES(employeeid，name，birthday，sex，address，zip，phonenumber,emailaddress，departmentlD) END RETURN GO(2) 修改职员记录的存储过程EmployeeUpdate： USE YGGLGO CREATE PROCEDURE EmployeeUpdate (empid char(6)，employeeid char(6)，name char(10)，birthday datetime， sex bit，address char(20)，zip chat(6),phonenumber char(12)， emailaddress char(20)，departmentlD char(3) AS BEGIN UPDATE E