1、基于 SQL SERVER的数据库技术实验指导计算机科学系软件教研室- 1 -实验一 数据库和表的管理实验目的 掌握 SQL SERVER 常用组件的使用 学会数据库的建立和使用 学会表的建立和使用实验内容1SQL SERVER 常用组件的使用2使用企业管理器和 T-SQL 语句来建立数据库3使用企业管理器和 T-SQL 语句建立表实验步骤1、实例一本实例讲解企业管理器的使用（1）在“开始”|“程序”|“Microsoft SQL Server”中选择“企业管理器” ，打开企业管理器。界面如图所示。- 2 -图 1.1 企业管理器主界面（2）双击“控制台根目录”下的“Microsoft SQL
2、 Servers”，出现“SQL Server 组” ，在组中选择相应的 SQL Server 注册服务器。如下图所示。图 1.2 SQL SERVER服务器注册（3）双击“数据库”可以打开本服务器中的相应数据库，之后可以对数据库进行建立、更改、- 3 -删除和对数据库中的表进行建立、更改和删除操作。结果如下图所示。图 1.3 建立数据库（4）在企业管理器中可以对服务器的属性进行相应的设置。右键单击服务器“SYSSYS(WindowNT)”选择属性，出现下图所示属性对话框。通过该对话框，可以对服务器和数据库进行适当的设置。- 4 -图 1.4 SQL SERVER属性窗口（5）企业管理器也可以
3、设置数据库的属性，右键单击某一数据库，选择“属性” ，则会出现数据库属性对话框。设置该对话框可以设置数据库的属性。- 5 -图 1.5 数据库属性窗口2、实例二本实例讲解查询分析器的使用（1）在“开始”|“程序”|“Microsoft SQL Server”中选择“查询分析器” ，打开查询分析器。界面如图所示。- 6 -图 1.6 SQL 查询分析器窗口（2）如果用户已经设置了密码，则会出现“连接 SQL Server”对话框，输入相应的用户名和密码即可进入查询分析器窗口。对话框窗口如下图所示。图 1.7 新建查询窗口（3）进入查询分析器窗口后，即可使用查询分析器的各项功能，如使用 T-SQL
4、 语言查询数据库中的相关信息等。例如，在查询窗口中输入以下内容。USE pubs- 7 -Select type,pub_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 个标签页上分别
5、显示查询的结果网格、执行计划、统计及其相关信息。- 8 -图 1.9 查询分析器执行计划显示窗口图 1.10 查询分析器统计显示窗口图 1.11 查询分析器消息显示窗口3、实例三本实例讲解服务管理器的使用（1）企业管理器和查询分析器在使用前必须保证已经启用服务管理器，服务管理器是主要服务的提供者。它可以提供三种服务：SQL Server 服务、MDTC 服务和 SQL Server 服务。服务管理器启动会在通知栏上显示出来，如下图。图 1.12 SQL SERVER服务管理器的启动（2）打开服务管理器需要在“开始”|“程序”|“Microsoft SQL Server”中选择“服务管理器” ，
6、出现服务管理器窗口，如下图所示。- 9 -图 1.13 SQL SERVER服务管理器的使用接下来可以设置服务管理器的启动、暂停和停止服务，也可以选择相应的服务器，和相应服务器提供的服务。（3）在企业管理器的 SQL Server 组中单击右键后从快捷菜单中选择“连接”|“断开”选项，即可启动和断开服务器。（4）配置服务器的属性对于管理 SQL Server 来说很重要，可以使用企业管理器进行配置。在企业管理器中在要进行配置的服务器上右击后，从快捷菜单中选择“属性”选项，即可对服务器属性进行设置，此设置对话框在前面已经讲过，这里不再赘述。4、实例四本实例主要讲解使用企业管理器创建一个数据库。要
7、求数据库名为 MYDB，数据库中包含一个数据文件，逻辑文件名为 MY_DB_DATA，操作系统文件名为 MY_DB_DATA.MDF，文件的初始容量为 5MB，最大容量为 15MB，文件容量递增值为2MB。事务日志文件的逻辑文件名为 MY_DB_LOG，操作系统文件名为 MY_DB_LOG.LDF，文件的初始容量为 3MB，最大容量为 10MB，文件容量递增为 1MB。创建完成后要对其进行修改，添加一个数据文件，逻辑文件名为 MY_DB_DATA1，操作系统文件名为 MY_DB_DATA1.NDF，文件的初始容量为 3MB，最大容量为 15MB，文件容量递增值为2MB。将事务日志文件的最大容量
8、改为 20MB，文件容量递增值改为 2MB。将创建的数据库删除。具体实现如下：（1）打开企业管理器并展开服务器，右击其中的“数据库”节点，在弹出的快捷菜单中执行“新建数据库”命令，弹出如图所示对话框。- 10 -图 1.14 使用企业管理器建立数据库（2）选择“常规”选项卡，在“名称”文本框中键入数据库名称为“MYDB” 。（3）选择“数据文件”选项卡，在“文件名”列输入 MY_DB_DATA，在“位置”列将文件名改为 MY_DB_DATA.MDF，在“初始大小”列输入 5。（4）选中“文件属性”复选框，在“文件增长”框中选中“按兆字节”单选框，并将其值改为 2。（5）在“最大文件大小”框中选
9、中“将文件增长限制为”单选框，并将其改为 15，如图所示。- 11 -图 1.15 更改逻辑文件名和物理文件名（6）选择“事务日志”选项卡，在“文件名”列输入 MY_DB_LOG，在“位置”列将文件名改为MY_DB_LOG.LDF，在“初始大小”列输入 3。（7）选中“文件属性”中的“文件自动增长”复选框，在“文件增长”框中选中“按兆字节”单选框，并将其值改为 1。（8）在“最大文件大小”栏中选中“将文件增长限制为”单选框，并将其改为 10，如图所示。- 12 -图 1.16 设置文件属性用企业管理器完成修改数据库（1）在企业管理器中选择“数据库”节点，右击刚刚创建的 MYDB 数据库，在弹出
10、的快捷菜单中选择“属性”命令；（2）选择“数据文件”选项卡，在“数据库文件”网格中的第二行添加数据文件，在其中的“文件名”列输入 MY_DB_DATA1，在“位置”列将文件名改为 MY_DB_DATA1.DNF，在“分配的空间”列添入 3，表示新添加的文件的初始大小为 3MB。（3）选中“文件属性”中的“文件自动增长”复选框，在“文件增长”框中选择“按兆字节”单选框，并将其值改为 2。（3）在“最大文件大小”栏中选中“将文件增长限制为”单选钮，并将其值改为 2，在“最大文件大小”栏中选中“将文件增长限制为”单选框，将其值改为 20。如图所示。- 13 -图 1.17 修改数据库用企业管理器删除
11、上面创建的数据库（1）在企业管理器中选择“数据库”节点，右击创建的 MYDB 数据库，在弹出的快捷菜单中选择“删除”命令，弹出“删除数据库”对话框。图 1.18 删除数据库对话框（2）单击“是”按钮，即可删除该数据库。5、实例五本实例讲解使用 T-SQL 语句完成创建数据库的操作，数据库的要求同上。（1）打开查询分析器，输入如下语句：CREATE DATABASE MYDBON(NAME=MY_DB_DATA,FILENAME=D:PROGRAM FILESMicrosft SQL ServerMSSQLdataMY_DB_DATA.MDF,- 14 -SIZE=5,MAXSIZE=15,FI
12、LEGROWTH=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-SQL 语句完成修改数据库的操作，语句如下。ALTER DATABASE MYDBADD FILE(NAME=MY_DB_DATA1,FILENAME=C:Program FilesMicrosoft SQL ServerMSSQLdataMY_DB_DATA1.NDF,S
13、IZE=3,MAXSIZE=15,FILEGROWTH=2)GOALTER DATABASE MYDBMODIFY FILE(NAME=MY_DB_LOG,MAXSIZE=20,FILEGROWTH=2)GO6、实例六本实例讲解使用企业管理器操纵表。按下列要求创建、修改和删除用户表：（1）创建一个包含如下各字段的员工信息表 employees创建的表如 A.1 所示。字段名 数据类型 注释empid Char(10) 员工代号，主键，非空name Char(20) 员工姓名，非空department Tinyint 员工所在部门代号，非空age Tinyint 员工年龄memo Varchar
14、(60) 备注表 1.1 员工信息表 employees- 15 -（2）创建一个包含如下各字段的部门信息表 departments创建的表如表 A.2 所示字段名 数据类型 注释depid tinyint 部门代号，主键，非空depname Char(10) 部门名称，非空memo Varchar(60) 备注表 1.2 部门信息表 departments（3）创建一个包含如下字段的表 tmp 字段名 数据类型 注释id uniqueidentifier表 1.3 表 tmp（4）对上面的表作如下修改为 employees 表添加一个日期型的出生日期字段，添加一个浮点型的工资字段，删除年龄字
15、段。将 departments 表的 depid 字段设为主键。将 employees 表中的 depname 字段名改为 department，数据类型为 tint int 型，并设为外键，与 departments 表中的主键关联，名为 FK_emp_dep。将 departments 表中的 depname 字段长度改为 20。将 employees 表中的 salary 字段的值限定在 0 至 10000 之间。（5）删除表 tmp具体实现如下：（1）用企业管理器完成创建表的操作具体步骤如下：打开企业管理器并展开服务器，单击其中的“数据库”节点，展开其中的一个数据库，并选择其下一级节点
16、“表” 。右击“表” ，在弹出的快捷菜单中执行“新建表”命令，打开表设计窗口，在窗口中按照 employees 表的要求输入字段名及类型，并将“empid”设为主键。单击“保存”按钮，在弹出的“选择名称”对话框中输入表名 employess，单击“确定”按钮关闭窗口，完成了 employees 表的创建。重复步骤 1 至 3，但省去创建主键的步骤，建立 departments 表和 tmp 表。- 16 -图 1.19 建立表结构窗口（2）用企业管理器完成修改表的操作具体步骤如下：打开企业管理器并展开服务器，单击其中的“数据库”节点，展开其中的一个数据库，并单击其下一级节点“表” 。在企业管理
17、器右侧窗格中选择 employees 表，右击后，在弹出的快捷菜单中选择“设计表”命令，打开表设计窗口，添加一个日期型的出生日期字段及一个浮点型的工作字段，删除原有的年龄字段，并将 department 的数据类型改为 tiny int 型，保存后关闭窗口。图 1.20 修改表窗口- 17 -在企业管理器右侧窗格中选择 department 表，右击后，在弹出的快捷菜单中选择“设计表”命令，打开表设计窗口。单击 depid 所在行，单击工具条上的“设置主键”按钮，则向 departments 表中添加了一个主键。将 depname 所在行的“长度”一列的值改为 20，保存后关闭窗口。在企业管理
18、器右侧窗格中选择 employees 表，右击后，在弹出的快捷菜单中选择“设计表”命令，打开表设计窗口。单击表设计窗口上的“表和索引属性”按钮，在弹出的菜单中选择“关系”选项卡，并单击“新建”按钮。在“关系名”文本框中输入 FK_emp_dep，在“主键表”列中选中 departments 表及其中的“dep_id“字段，在“外键表”列中选中 employess 表及其中的 department 字段，然后按“关闭”按钮。单击图 1.21 中的“约束”选项卡，在其中单击“新建”按钮。在“关系名”文本框中输入 CK_emp_salary，在“约束表达式”文本框中输入表达式 salary=0 an
19、d 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
20、 employees.salary15003、实例三本实例讲解通配符和聚集函数的使用，打开查询分析器，输入如下语句。（1）查询 employees 表中姓望的职工的所有数据。22SELECT * 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）找出 emp
21、loyees 表中所有未添写出生日期的职工。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）查询所有不属于开发部的员工的基
22、本信息。SELECT e.empid,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
23、（2）在所有填写了出生日期的数据中，找出年龄最大的三位员工的信息。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 departmen
24、t on employees.department=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）将所有工资低于
25、 1500 的职员涨 200 元。update employeesset salary =salary+200where salary50（6）删除属于销售部的所有员工。24DELETE employeesFROM employees inner join departmentson employees.department=departments.depidwhere department.depname=销售部（7）删除所有员工的数据。语句如下：TRUNCATE TABLE employees25实验三 索引、视图及数据转换、备份和恢复技术实验目的 掌握索引的使用方法 掌握视图的创建和使用
26、 掌握数据库的转换、备份和恢复技术实验内容1索引的使用2视图的使用3数据库的转换、备份和恢复实验步骤1、实例一本实例讲解如何创建和使用索引。为表 employees 中的 name 创建索引，要求索引名为 IDX_name,索引类型为非聚焦索引。为表 departments 中的 depid 创建索引，要求索引名为 IDX_DEPID,索引类型为非聚焦索引。操作过程：（1）用企业管理器完成创建索引创建 IDX_name 索引文件的操作如下：在 SQL Server 2000 企业管理器中，展开服务器组，然后展开服务器实例。展开“数据库”文件夹，再展开要在其中创建索引的数据库 mydb。单击“表
27、”目录。右击目标表 employees，然后执行“所有任务”|“管理索引”命令，单击“新建”按钮，此时出现如图 3.1 所示的“新建索引”对话框。选择 name 列，在索引文件名输入“IDX_name” ，单击“确定”按钮关闭“新建索引”对话框，返回“管理索引”对话框，单击“关闭”按钮，结束索引建立过程。创建 IDX_depid 索引文件的操作，与上述方法相同。（2）用 Transact-SQL 语句创建索引操作为表 employees 中的 name 创建索引，要求索引名为 IDX_name，索引类型为非聚集索引。语26句如下：USE mydbGOCREATE INDEX IDX_name
28、ON employees(name)GO为表 departments 创建索引，语句如下：USE mydbGOCREATE INDEX IDX_depid ON department(depid)GO图 3.1 新建索引对话框2、实例二本实例讲解如何创建和使用视图。按要求创建视图视图名为 empinfo。包含字段 empid、name、depname、birthdate、 salary。字段别名分别是“雇员编号” 、 “雇员姓名” 、 “部门” 、 “出生日期”和“工资” 。使用 INSERT 语句通过视图向雇员数据表添加一条记录，要求“姓名”为“张明” 、 “工资”为“2000” 。使用 U
29、ADATE 语句通过视图将插入记录的雇员工资改为 2100。27执行系统存储过程 sp_rename 将视图改名为“雇佣信息” 。（1）使用企业管理器创建视图步骤如下：在 SQL Server 2000 企业管理器中，展开服务器组，然后展开服务器实例。展开“数据库”文件夹，再展开数据库 mydb.在该数据库下面右击“视图”目录，然后单击“新建视图”命令。右击关系图窗格，然后选择“添加表”命令。当出现 “添加表”对话框时，选择 department 和 employees，使其出现在关系图窗格中，然后单击“关闭”按钮。操作网格，选择视图中各列，如图 3.2 所示。在工具栏上单击“运行”按钮，在数
30、据结果区将显示包含在视图中的数据行。在工具栏上单击“保存”按钮，弹出保存视图对话框，在该对话框中输入视图名 empinfo，单击“确定”按钮完成视图的创建。图 3.2 创建视图对话框（2）使用 Transact-SQL 打开查询分析器，在查询窗口输入 CREATE VIEW 语句创建视图，并指定字段别名。语句如下：USE mydbCREATE VIEW empinfo(雇员编号，雇佣姓名，部门，出生日期，工资)28ASSELECT employees.empid,employees.name,departments.depname,employees.birthdate,employees.s
31、alaeryFROM departments INNER JOINEmployees ON departments.depid=employees.departmentGOEXEC sp_helptextempinfoGO（3）使用 INSERT 语句通过视图向雇员数据表中添加一条记录，要求姓名为“张明” 、工资为2000。语句如下：USE my_dbINSERT INTO empinfo(雇员姓名，工资)VALUES （张明 ，2000）GO（4）使用 UPDATE 语句通过视图将插入记录的雇员工资改为 2100，语句如下：USE mydbGOUPDATE empinfoSET 工资=210
32、0WHERE 雇员姓名=张明GO（5）执行系统存储过程 sp_rename 将视图改名为“雇员信息” ，语句如下：USE mydbGOEXEC sp_rename empinfo,雇员信息3、实例三本实例主要讲解使用 SQL Server 2000 企业管理器备份和恢复数据库 MYDB，数据转换可以参阅讲稿中的相关内容。操作过程:（1）创建备份设备打开企业管理器并展开服务器，展开其中的“管理”节点，右击“备份” ，在快捷菜单中选择“新建备份设备”命令。在弹出“备份设备属性-新设备”对话框中，在“名称”文本框中键入“MYDB 备份设备” ，如图 3.3 所示。29图 3.4 备份设备属性对话框单击“确定”按钮。（2）备份数据库在企业管理器的服务器中，右击其中的数据库 MYDB，在弹出的快捷菜单中执行“所有任务”|“备份数据库”命令。在出现的“SQL Server 备份”对话框中选择“常规”选项卡。在该选项卡的“名称”文本框中键入“MYDB”数据库备份。在“备份”单选钮中选择“数据库-完全”选项。单击“添加”按钮，在弹出的对话框中选择“数据库-完全设备” ，然后从下拉列表中选择“MYDB 备份设备”如图 3.5 所示。单击“选项”选项卡，在该选项卡中选中“完成验证备份”复选框。单击“确定”按钮。出现“备份进度”对话框，备份完成后，将弹出消息框“备份操作及验证已成功完成”
