SQL+SERVER实验指导.doc

《SQL+SERVER实验指导.doc》由会员分享，可在线阅读，更多相关《SQL+SERVER实验指导.doc（43页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、基于SQL SERVER的数据库技术实验指导计算机科学系软件教研室实验一数据库和表的管理实验目的 掌握SQL SERVER常用组件的使用 学会数据库的建立和使用 学会表的建立和使用实验内容1SQL SERVER常用组件的使用2使用企业管理器和T-SQL语句来建立数据库3使用企业管理器和T-SQL语句建立表实验步骤1、实例一本实例讲解企业管理器的使用（1）在“开始”|“程序”|“Microsoft SQL Server”中选择“企业管理器”，打开企业管理器。界面如图所示。图1.1 企业管理器主界面（2）双击“控制台根目录”下的“Microsoft SQL Servers”，出现“SQL Serv

2、er组”，在组中选择相应的SQL Server注册服务器。如下图所示。图1.2 SQL SERVER服务器注册（3）双击“数据库”可以打开本服务器中的相应数据库，之后可以对数据库进行建立、更改、删除和对数据库中的表进行建立、更改和删除操作。结果如下图所示。图1.3 建立数据库（4）在企业管理器中可以对服务器的属性进行相应的设置。右键单击服务器“SYSSYS(WindowNT)”选择属性，出现下图所示属性对话框。通过该对话框，可以对服务器和数据库进行适当的设置。图1.4 SQL SERVER属性窗口（5）企业管理器也可以设置数据库的属性，右键单击某一数据库，选择“属性”，则会出现数据库属性对话框

3、。设置该对话框可以设置数据库的属性。图1.5 数据库属性窗口2、实例二本实例讲解查询分析器的使用（1）在“开始”|“程序”|“Microsoft SQL Server”中选择“查询分析器”，打开查询分析器。界面如图所示。图1.6 SQL 查询分析器窗口（2）如果用户已经设置了密码，则会出现“连接SQL Server”对话框，输入相应的用户名和密码即可进入查询分析器窗口。对话框窗口如下图所示。图1.7 新建查询窗口（3）进入查询分析器窗口后，即可使用查询分析器的各项功能，如使用T-SQL语言查询数据库中的相关信息等。例如，在查询窗口中输入以下内容。USE pubsSelect type,pub_

4、id,priceFrom titlesWhere type= businessOrder by type,pub_id,priceCompute sum(price) By type,pub_idCompute sum(price) By type按“F5”或查询分析器窗口中的来执行查询语句，结果如下图所示。图1.8 查询分析器的使用（3）选择“查询”菜单下的“显示执行计划”和“显示客户统计”选项。接着按“F5”执行查询。此时在文本编辑器的下半部分的4个标签页上分别显示查询的结果网格、执行计划、统计及其相关信息。图1.9 查询分析器执行计划显示窗口图1.10 查询分析器统计显示窗口图1.11

5、查询分析器消息显示窗口3、实例三本实例讲解服务管理器的使用（1）企业管理器和查询分析器在使用前必须保证已经启用服务管理器，服务管理器是主要服务的提供者。它可以提供三种服务：SQL Server服务、MDTC服务和SQL Server服务。服务管理器启动会在通知栏上显示出来，如下图。图1.12 SQL SERVER服务管理器的启动（2）打开服务管理器需要在“开始”|“程序”|“Microsoft SQL Server”中选择“服务管理器”，出现服务管理器窗口，如下图所示。图1.13 SQL SERVER服务管理器的使用接下来可以设置服务管理器的启动、暂停和停止服务，也可以选择相应的服务器，和相应

6、服务器提供的服务。（3）在企业管理器的SQL Server组中单击右键后从快捷菜单中选择“连接”|“断开”选项，即可启动和断开服务器。（4）配置服务器的属性对于管理SQL Server来说很重要，可以使用企业管理器进行配置。在企业管理器中在要进行配置的服务器上右击后，从快捷菜单中选择“属性”选项，即可对服务器属性进行设置，此设置对话框在前面已经讲过，这里不再赘述。4、实例四本实例主要讲解使用企业管理器创建一个数据库。要求数据库名为MYDB，数据库中包含一个数据文件，逻辑文件名为MY_DB_DATA，操作系统文件名为MY_DB_DATA.MDF，文件的初始容量为5MB，最大容量为15MB，文件容

7、量递增值为2MB。事务日志文件的逻辑文件名为MY_DB_LOG，操作系统文件名为MY_DB_LOG.LDF，文件的初始容量为3MB，最大容量为10MB，文件容量递增为1MB。创建完成后要对其进行修改，添加一个数据文件，逻辑文件名为MY_DB_DATA1，操作系统文件名为MY_DB_DATA1.NDF，文件的初始容量为3MB，最大容量为15MB，文件容量递增值为2MB。将事务日志文件的最大容量改为 20MB，文件容量递增值改为2MB。将创建的数据库删除。具体实现如下：（1）打开企业管理器并展开服务器，右击其中的“数据库”节点，在弹出的快捷菜单中执行“新建数据库”命令，弹出如图所示对话框。图1.1

8、4 使用企业管理器建立数据库（2）选择“常规”选项卡，在“名称”文本框中键入数据库名称为“MYDB”。（3）选择“数据文件”选项卡，在“文件名”列输入MY_DB_DATA，在“位置”列将文件名改为MY_DB_DATA.MDF，在“初始大小”列输入5。（4）选中“文件属性”复选框，在“文件增长”框中选中“按兆字节”单选框，并将其值改为2。（5）在“最大文件大小”框中选中“将文件增长限制为”单选框，并将其改为15，如图所示。图1.15 更改逻辑文件名和物理文件名（6）选择“事务日志”选项卡，在“文件名”列输入MY_DB_LOG，在“位置”列将文件名改为MY_DB_LOG.LDF，在“初始大小”列输

9、入3。（7）选中“文件属性”中的“文件自动增长”复选框，在“文件增长”框中选中“按兆字节”单选框，并将其值改为1。（8）在“最大文件大小”栏中选中“将文件增长限制为”单选框，并将其改为10，如图所示。图1.16 设置文件属性用企业管理器完成修改数据库（1）在企业管理器中选择“数据库”节点，右击刚刚创建的MYDB数据库，在弹出的快捷菜单中选择“属性”命令；（2）选择“数据文件”选项卡，在“数据库文件”网格中的第二行添加数据文件，在其中的“文件名”列输入MY_DB_DATA1，在“位置”列将文件名改为MY_DB_DATA1.DNF，在“分配的空间”列添入3，表示新添加的文件的初始大小为3MB。（3

10、）选中“文件属性”中的“文件自动增长”复选框，在“文件增长”框中选择“按兆字节”单选框，并将其值改为2。（3）在“最大文件大小”栏中选中“将文件增长限制为”单选钮，并将其值改为2，在“最大文件大小”栏中选中“将文件增长限制为”单选框，将其值改为20。如图所示。图1.17 修改数据库用企业管理器删除上面创建的数据库（1）在企业管理器中选择“数据库”节点，右击创建的MYDB数据库，在弹出的快捷菜单中选择“删除”命令，弹出“删除数据库”对话框。图1.18 删除数据库对话框（2）单击“是”按钮，即可删除该数据库。5、实例五本实例讲解使用T-SQL语句完成创建数据库的操作，数据库的要求同上。（1）打开查

11、询分析器，输入如下语句：CREATE DATABASE MYDBON(NAME=MY_DB_DATA,FILENAME=D:PROGRAM FILESMicrosft SQL ServerMSSQLdataMY_DB_DATA.MDF,SIZE=5,MAXSIZE=15,FILEGROWTH=2)LOG ON(NAME=MY_DB_LOG,FILENAME=D:PROGRAM FILESMicrosft SQL ServerMSSQLdataMY_DB_LOG.LDF,SIZE=3,MAXSIZE=10,FILEGROWTH=1)GO（2）按“F5”来执行，创建MYDB数据库。（3）用T-SQ

12、L语句完成修改数据库的操作，语句如下。ALTER DATABASE MYDBADD FILE(NAME=MY_DB_DATA1,FILENAME=C:Program FilesMicrosoft SQL ServerMSSQLdataMY_DB_DATA1.NDF,SIZE=3,MAXSIZE=15,FILEGROWTH=2)GOALTER DATABASE MYDBMODIFY FILE(NAME=MY_DB_LOG,MAXSIZE=20,FILEGROWTH=2)GO6、实例六本实例讲解使用企业管理器操纵表。按下列要求创建、修改和删除用户表：（1）创建一个包含如下各字段的员工信息表empl

13、oyees创建的表如A.1所示。字段名数据类型注释empidChar(10)员工代号，主键，非空nameChar(20)员工姓名，非空departmentTinyint员工所在部门代号，非空ageTinyint员工年龄memoVarchar(60)备注表1.1 员工信息表 employees（2）创建一个包含如下各字段的部门信息表departments创建的表如表A.2所示字段名数据类型注释depidtinyint部门代号，主键，非空depnameChar(10)部门名称，非空memoVarchar(60)备注表1.2 部门信息表departments（3）创建一个包含如下字段的表tmp 字段

14、名数据类型注释iduniqueidentifier表1.3 表tmp（4）对上面的表作如下修改为employees表添加一个日期型的出生日期字段，添加一个浮点型的工资字段，删除年龄字段。将departments表的depid字段设为主键。将employees表中的depname字段名改为department，数据类型为tint int型，并设为外键，与departments表中的主键关联，名为FK_emp_dep。将departments表中的depname字段长度改为20。将employees表中的salary字段的值限定在0至10000之间。（5）删除表tmp具体实现如下：（1）用企业管理

15、器完成创建表的操作具体步骤如下：打开企业管理器并展开服务器，单击其中的“数据库”节点，展开其中的一个数据库，并选择其下一级节点“表”。右击“表”，在弹出的快捷菜单中执行“新建表”命令，打开表设计窗口，在窗口中按照employees表的要求输入字段名及类型，并将“empid”设为主键。单击“保存”按钮，在弹出的“选择名称”对话框中输入表名employess，单击“确定”按钮关闭窗口，完成了employees表的创建。重复步骤1至3，但省去创建主键的步骤，建立departments表和tmp表。图1.19 建立表结构窗口（2）用企业管理器完成修改表的操作具体步骤如下：打开企业管理器并展开服务器，单

16、击其中的“数据库”节点，展开其中的一个数据库，并单击其下一级节点“表”。在企业管理器右侧窗格中选择employees表，右击后，在弹出的快捷菜单中选择“设计表”命令，打开表设计窗口，添加一个日期型的出生日期字段及一个浮点型的工作字段，删除原有的年龄字段，并将department的数据类型改为tiny int型，保存后关闭窗口。图1.20 修改表窗口在企业管理器右侧窗格中选择department表，右击后，在弹出的快捷菜单中选择“设计表”命令，打开表设计窗口。单击depid所在行，单击工具条上的“设置主键”按钮，则向departments表中添加了一个主键。将depname所在行的“长度”一列的

17、值改为20，保存后关闭窗口。在企业管理器右侧窗格中选择employees表，右击后，在弹出的快捷菜单中选择“设计表”命令，打开表设计窗口。单击表设计窗口上的“表和索引属性”按钮，在弹出的菜单中选择“关系”选项卡，并单击“新建”按钮。在“关系名”文本框中输入FK_emp_dep，在“主键表”列中选中departments表及其中的dep_id字段，在“外键表”列中选中employess表及其中的department字段，然后按“关闭”按钮。单击图1.21中的“约束”选项卡，在其中单击“新建”按钮。在“关系名”文本框中输入CK_emp_salary，在“约束表达式”文本框中输入表达式salary=

18、0 and salary=0 and salary2000（4）查询employees表中工资超过1500，并且属于开发部的人员，并用departments表中的部门名称来取代其编号显示出来。SELECT employees.empid,employees.name,employees.depname,employees.birthdate,employees.salaryFROM departments INNER JOIN employeesON departments.depid=employees.departmentsWHERE departments.depname=开发部AND

19、employees.salary15003、实例三本实例讲解通配符和聚集函数的使用，打开查询分析器，输入如下语句。（1）查询employees表中姓望的职工的所有数据。SELECT * FROM employees WHERE name LIKE王%（2）查询employees表中姓名只含有两个字的职工。SELECT * FROM employees WHERE name LIKE_（3）查询employees表中所有2世纪60年代出生的职工。SELECT * FROM employees WHERE year(birthday)LIKE1960-9（4）找出employees表中所有未添写出

20、生日期的职工。SELECT * FROM employees WHERE birthdate IS NULL（5）查询所有开发部员工的工资总额。SELECT SUM(employees.salary)AS工资和，department.depname AS部门FROM departments INNER JOINemployees ON department.depid=employees.departmentsWHERE departments.depname=开发部GROUP BY departments.depname（6）查询所有不属于开发部的员工的基本信息。SELECT e.empid

21、,e.name,e.birthdate,e.salary,departments.depid AS 部门FROM departments INNER JOINemployees AS e ON departments.depid=e.departmentWHERE NOT(department.depname=开发部)4、实例四本实例讲解使用分组和排序语句，打开查询分析器，输入如下语句。（1）显示工资最高的三位员工的信息。SELECT TOP 3 employees. *FROM employeesORDER BY employees.salary DESC（2）在所有填写了出生日期的数据中，

22、找出年龄最大的三位员工的信息。SELECT TOP 3 employees.*FROM employeesWHERE biethdate is NOT NULLORDER BY birthdate（3）找出所有工资介于2000到3000之间的职工。select * FROM employees where salary=2000 and salary3000（8）查询各部门的平均工资，并将结果排序。语句如下：SELECT departments.depname,avg(salary)From employessINNER join department on employees.departm

23、ent=departments.depidgroup by department.depname order by avg(salary)5、实例五本实例讲解如何使用UPDATE、INSERT和DELETE语句。（1）向employees表中添加一条数据。insert into employeesvalues(0010,赵月,NULL,1980-12-10,null)（2）将所有“工资”一项为空的职员，工资定为600。update employeesset salary=600where salary is null（3）将所有工资低于1500的职员涨200元。update employees

24、set salary =salary+200where salary50（6）删除属于销售部的所有员工。DELETE employees FROM employees inner join departmentson employees.department=departments.depidwhere department.depname=销售部（7）删除所有员工的数据。语句如下：TRUNCATE TABLE employees实验三 索引、视图及数据转换、备份和恢复技术实验目的 掌握索引的使用方法 掌握视图的创建和使用 掌握数据库的转换、备份和恢复技术实验内容1索引的使用2视图的使用3数据

25、库的转换、备份和恢复实验步骤1、实例一本实例讲解如何创建和使用索引。为表employees中的name创建索引，要求索引名为IDX_name,索引类型为非聚焦索引。为表departments中的 depid 创建索引，要求索引名为IDX_DEPID,索引类型为非聚焦索引。操作过程：（1）用企业管理器完成创建索引创建IDX_name索引文件的操作如下：在SQL Server 2000企业管理器中，展开服务器组，然后展开服务器实例。展开“数据库”文件夹，再展开要在其中创建索引的数据库mydb。单击“表”目录。右击目标表employees，然后执行“所有任务”|“管理索引”命令，单击“新建”按钮，此

26、时出现如图3.1所示的“新建索引”对话框。选择name列，在索引文件名输入“IDX_name”，单击“确定”按钮关闭“新建索引”对话框，返回“管理索引”对话框，单击“关闭”按钮，结束索引建立过程。创建IDX_depid索引文件的操作，与上述方法相同。（2）用Transact-SQL语句创建索引操作为表employees中的name创建索引，要求索引名为IDX_name，索引类型为非聚集索引。语句如下：USE mydbGOCREATE INDEX IDX_name ON employees(name)GO为表departments创建索引，语句如下：USE mydbGOCREATE INDEX

27、IDX_depid ON department(depid)GO图3.1 新建索引对话框2、实例二本实例讲解如何创建和使用视图。按要求创建视图视图名为empinfo。包含字段empid、name、depname、birthdate、 salary。字段别名分别是“雇员编号”、“雇员姓名”、“部门”、“出生日期”和“工资”。使用INSERT语句通过视图向雇员数据表添加一条记录，要求“姓名”为“张明”、“工资”为“2000”。使用UADATE语句通过视图将插入记录的雇员工资改为2100。执行系统存储过程sp_rename将视图改名为“雇佣信息”。（1）使用企业管理器创建视图步骤如下：在SQL Se

28、rver 2000企业管理器中，展开服务器组，然后展开服务器实例。展开“数据库”文件夹，再展开数据库mydb.在该数据库下面右击“视图”目录，然后单击“新建视图”命令。右击关系图窗格，然后选择“添加表”命令。当出现 “添加表”对话框时，选择department 和employees，使其出现在关系图窗格中，然后单击“关闭”按钮。操作网格，选择视图中各列，如图3.2所示。在工具栏上单击“运行”按钮，在数据结果区将显示包含在视图中的数据行。在工具栏上单击“保存”按钮，弹出保存视图对话框，在该对话框中输入视图名empinfo，单击“确定”按钮完成视图的创建。图3.2 创建视图对话框（2）使用Tran

29、sact-SQL 打开查询分析器，在查询窗口输入CREATE VIEW语句创建视图，并指定字段别名。语句如下：USE mydbCREATE VIEW empinfo(雇员编号，雇佣姓名，部门，出生日期，工资)ASSELECT employees.empid,employees.name,departments.depname,employees.birthdate,employees.salaeryFROM departments INNER JOINEmployees ON departments.depid=employees.departmentGOEXEC sp_helptextemp

30、infoGO（3）使用INSERT语句通过视图向雇员数据表中添加一条记录，要求姓名为“张明”、工资为2000。语句如下：USE my_dbINSERTINTO empinfo(雇员姓名，工资)VALUES （张明，2000）GO（4）使用UPDATE语句通过视图将插入记录的雇员工资改为2100，语句如下：USE mydbGOUPDATE empinfoSET 工资=2100WHERE 雇员姓名=张明GO（5）执行系统存储过程sp_rename 将视图改名为“雇员信息”，语句如下：USE mydbGOEXEC sp_rename empinfo,雇员信息3、实例三本实例主要讲解使用SQL Ser

31、ver 2000企业管理器备份和恢复数据库MYDB，数据转换可以参阅讲稿中的相关内容。操作过程:（1）创建备份设备打开企业管理器并展开服务器，展开其中的“管理”节点，右击“备份”，在快捷菜单中选择“新建备份设备”命令。在弹出“备份设备属性-新设备”对话框中，在“名称”文本框中键入“MYDB备份设备”，如图3.3所示。图3.4 备份设备属性对话框单击“确定”按钮。（2）备份数据库在企业管理器的服务器中，右击其中的数据库MYDB，在弹出的快捷菜单中执行“所有任务”|“备份数据库”命令。在出现的“SQL Server备份”对话框中选择“常规”选项卡。在该选项卡的“名称”文本框中键入“MYDB”数据库

32、备份。在“备份”单选钮中选择“数据库-完全”选项。单击“添加”按钮，在弹出的对话框中选择“数据库-完全设备”，然后从下拉列表中选择“MYDB备份设备”如图3.5所示。单击“选项”选项卡，在该选项卡中选中“完成验证备份”复选框。单击“确定”按钮。出现“备份进度”对话框，备份完成后，将弹出消息框“备份操作及验证已成功完成”图3.5 备份数据库对话框单击“确定”完成数据库备份。（3）恢复数据库在企业管理器的服务器中，右击要还原的数据库MYDB，在弹出的快捷菜单中执行“所有任务”|“还原数据库”命令。在出现的“还原数据库”对话框的“还原”单选钮组中选中“从设备”单选钮。单击“选择设备”钮，从弹出的对话

33、框中选择备份设备“MYDB备份设备”。单击选钮“还原备份集”，选择其中的单选钮“数据库-完全”，如图3.6所示。图3.6 还原数据库对话框单击“确定”按钮，执行还原操作。出现“还原进度”对话框，还原完成后，将弹出消息框“数据库MYDB的还原已顺利完成”。单击“确定”按钮。实验四 触发器及存储过程、自定义函数实验目的 掌握触发器的使用 掌握存储过程的使用 掌握使用自定义函数实验内容1.触发器的创建和使用实验步骤1、实例一本实例讲解创建触发器。使用T-SQL语句，按下列要求对数据库MYDB的表employees创建、删除触发器。基于表employees创建AFTER INSERT触发器NEWEMP

34、,将插入员工的工资额限制在1000以内。基于表employees创建AFTER UPDATE触发器CHANGEMP，将员工的工资变动额限制在1500以内。使用DROP TRIGGER语句删除用户创建的触发器打开查询分析器，输入具体语句如下：（1）创建AFTER INSERT触发器。USE MYDBGOCREATE TRIGGER NEWEMP ON employeesAFTER INSERTASIF(SELECT salary FROM inserted)100BEGINPRINT 新员工工资不能超过1000ROLLBACKENDGO（2）创建AFTER UPDATE触发器。USE MYDBGOCREATE TRIGGER CHANAGEMP ON employeesBEGINIF（SELECT MAX(ABS（inserted.salary-deleted.salary)） FROM inserted JOIN deletedON inserted.empid=deleted