实验指导书-数据库应用设计

《数据库应用设计》课程

实验项目指导书

承担单位名称： 信息技术学院

适用专业： 计算机科学与技术 撰写人：

1 基本训练型实验项目 创建数据库

匹配课程代码及名称：070785，数据库应用设计 适用专业及本项目实验学时：计算机科学与技术，3学时 一、实验目的及要求

（1）了解SQL Server数据库的逻辑结构和物理结构； （2）学会在对象资源管理器中创建数据库； （3）学会使用T-SQL语句创建数据库。 二、 实验内容

创建用于企业管理的员工管理数据库，数据库名为YGGL。

数据库YGGL的逻辑文件初始大小为5MB，最大大小为10MB，数据库自动增长，增长方式是按5%比例增长。日志文件初始为2MB，最大可增长到5MB，按1MB增长。数据库的逻辑文件名和物理文件名均采用默认值。日志的逻辑文件名和物理文件名也均采用默认值。

要求分别使用对象资源管理器和T-SQL命令完成数据库的创建。 三、实验条件及设备要求

已安装SQL Server 2008数据库管理系统的实验机。 四、实验实施步骤

（一）使用对象资源管理器完成数据库YGGL的创建

1、创建数据库YGGL

使用系统管理员用户以Windows身份验证方式登陆SQL Server服务器，在“对象资源管理器”窗口中选择其中的“数据库”节点，点击鼠标，在弹出的快捷菜单中选择“新建数据库（N）”菜单项，打开“新建数据库”窗口。

在“新建数据库”窗口的“常规”选项卡中输入数据库名“YGGL”，“所有者”为默认值。在“数据库文件”下方的列表栏中，分别设置“数据文件”和“日志文件”的增长方式和增长比例。设置完成后单击“确定”按钮完成数据库的创建。 2、删除YGGL数据库

在对象资源管理器中选择数据库YGGL，右击鼠标，在弹出的快捷菜

单中选择“删除”菜单项。在打开的“删除对象”窗口中单击“确定”按钮，执行删除操作。

（二）使用T-SQL命令完成数据库YGGL的创建

1、创建数据库YGGL

在“查询分析器”窗口中输入如下语句：

CREATE DATABASE YGGL ON (

NAME=’YGGL_Data’,

FILENAME=’ (注：一个本地路径)’, SIZE=10MB, MAXSIZE=50MB FILEGROWTH=5% ) LOG ON (

NAME=’YGGL_Log’,

FILENAME=’ (注：一个本地路径)’, SIZE=2MB, MAXSIZE=5MB, FILEGROWTH=1MB ) GO

单击快捷工具栏的“！执行”按钮，执行上述语句，并在“对象资源管理器”窗口中查看执行结果。 2、删除YGGL数据库

在SQL Server Management Studio界面的快捷工具栏中单击“新建查询”按钮，在“查询分析器”窗口中输入如下脚本后单击“！执行”按钮执行。

USE master GO

DROP DATABASE YGGL

五、实验成绩评定办法

主要评分点：实验流程、调试过程、实验结果。

2 基本训练型实验项目表的创建及表的数据操作

匹配课程代码及名称：070785，数据库应用设计 适用专业及本项目实验学时：计算机科学与技术，3学时 一、实验目的及要求

（1）了解表的结构特点；

（2）了解SQL Server 的基本数据类型； （3）学会在对象资源管理器中创建表； （4）学会使用T-SQL语句创建表；

（5）学会在对象资源管理器中对数据库表进行插入、修改和删除数据操作； （6）学会使用T-SQL语句对数据库表进行插入、修改和删除数据操作. 二、 实验内容

1、在创建好的数据库YGGL中创建数据表

考虑到数据库YGGL要求包含员工的信息、部门的信息以及员工的薪水信息，所以数据库YGGL应包含下列3个表：Employees（员工自然信息）表、Departments（部门信息）表、Salary（员工薪资）表。各表的结构分别如表T2.1、表T2.2和表T2.3所示。

表T2.1 Employees表结构

列名 数据类型 EmployeeID CHAR Name CHAR Education CHAR Birthday DATE Sex BIT Work Year TINYINT Address VARCHAR PhoneNum CHAR DepartmentID CHAR 长度 6 10 4 默认 默认 默认 40 12 3 是否可空 否 否 否 否 否 是 是 是 否 默认值 无 无 无 无 1 无 无 无 无 说明 员工编号，主键 姓名 学历 出生日期 1：男 0：女 工作时间 地址 电话号码 部门号，外键 表T2.2 Departments表结构

列名 数据类型 DepartmentID CHAR DepartName CHAR Note VARCHAR 长度 3 20 100 是否可空 否 否 是 默认值 无 无 无 说明 部门编号，主键 部门名 备注 表T2.3 Salary表结构

列名 EmployeeID InCome OutCome 数据类型 CHAR FLOAT FLOAT 长度 6 默认 默认 是否可空 否 否 是 默认值 无 无 无 说明 员工编号，主键 收入 支出 要求分别使用对象资源管理器和T-SQL命令完成数据表的创建。

2、分别使用对象资源管理器和T-SQL语句，向数据库YGGL的三个表Employees、Departments和Salary中插入多行数据记录（样本数据如表T3.1、表T3.2和表T3.3所示），然后修改和删除一些记录。使用T-SQL语句进行有限制的修改和删除。

表T3.1 Employees表数据样本

编号 姓名 学历 出生日期 性别 工作时间 住址 电话 部门号 000001 王林 大专 1966-01-23 1976-03-28 1982-12-09 1960-07-30 1972-10-18 1965-09-28 1979-08-10 1974-10-23 1977-04-02 1968-09-20 1978-11-18 1969-09-03 1 1 1 0 1 1 0 1 1 1 1 0

8 3 2 6 3 2 4 1 2 3 2 5 中山路32号 8355668 北京路2号 8321321 2 1 1 1 5 5 3 5 3 4 4 4 010008 伍荣华 本科 020010 王向荣 硕士 020018 李丽 102201 刘明 102208 朱军 108991 钟敏 大专 本科 硕士 硕士 四牌楼10号 8379236 中山路10-2 8341330 解放路12号 8360668 北京东路2 中山北路3 虎踞路2号 8345632 8470223 8349562 111006 张士兵 本科 210678 林涛 大专 北京东路12 8533196 龙蟠路9号 8645231 302566 李玉敏 本科 308759 叶凡 本科 舜玉路10号 8330891 阳光新路1 8446815 504209 陈琳琳 大专 表T3.2 Departments表

部门号 部门名称 备注 1 2 3 4 5 财务部 人力资源部 经理办公室 研发部 市场部 NULL NULL NULL NULL NULL

表T3.3 Salary表

编号 收入 支出 000001 010008 102201 111006 504209 302566 108991 020010 020018 308759 210678 102208 2100.8 1582.62 2569.88 1987.01 2066.15 2980.7 3259.98 2860.0 2347.68 2531.98 2240.0 1980.0 123.09 88.03 185.65 79.58 108.0 210.2 281.52 198.0 180.0 199.08 121.0 100.0 三、实验条件及设备要求

已安装SQL Server 2008数据库管理系统的实验机。 四、实验实施步骤

（一）使用对象资源管理器完成数据表的创建

1、创建表

以创建Employees表为例，在对象资源管理器中展开数据库“YGGL”；选择“表”，右击鼠标，在弹出的快捷菜单中选择“新建表”菜单项；在表设计窗口中输入Employees表的各字段信息；单击工具栏中的“保存”按钮；在弹出的“保存”对话框中输入表名“Employees”，单击“确定”按钮即创建了表Employees。

按同样的操作过程，创建表Departments和表Salary。 2、删除表

在对象资源管理器中展开“数据库”，选择其中的“YGGL”。展开“YGGL”中的“表”节点，右击其中的“dbo.Employees”表，在弹出的快捷菜单中选择“删除”菜单项，打开删除对象窗口。

在“删除对象”窗口中单击“显示依赖关系”按钮，打开“Employees 依赖关系”窗口。在该窗口中确认表“Employees”确实可以删除之后，单击“确定”按钮，返回“删除对象”窗口，在“删除对象”窗口单击“确定”按钮，完成表Employees的删除。 （二）使用T-SQL命令完成数据表的创建

在“查询分析器”窗口中输入以下T-SQL语句：

USE YGGL GO

CREATE TABLE Employees ( EmployeeID Name

Education Birthday Sex

bit

WorkYear

char(6) char(6) char(6) char(6) tinyint

NOT NULL PRIMARY KEY, NOT NULL, NOT NULL, NOT NULL, NULL, NULL,

NOT NULL DEFAULT 1,

Address varchar(40)

PhoneNumber char(12) DepartmentID char(3) ) GO

NULL, NOT NULL

单击快捷工具栏的“！执行”图标，执行上述语句，即可创建表Employees。按同样的方法也可以创建表Departments和Salary，并在对象资源管理器中查看结果。

（三）在对象资源管理器中初始化数据库YGGL中所有表的数据

在对象资源管理器中展开“数据库YGGL”节点，选择要进行操作的表“Employees”，右击鼠标，在弹出的快捷菜单上选择“编辑前200行”菜单项，进入“表数据窗口”。

在此窗口中，表中的记录按行显示，每个记录占一行。用户可通过“表数据窗口”向表中加入表T3.1中的记录，输完一行记录后将光标移到下一行即保存了上一行记录。

用同样的方法向Departments和Salary表中分别插入表T3.2和T3.3中的记录。

注意：

● 插入的数据要符合列的类型。试着在

tinyint型的列中插入字符型数

据（如字母），查看发生的情况。

●bit

类型的列在用界面方式插入数据时只能插入True或False。True

表示1，False表示0。

● 不能插入两行有相同主键的数据。例如，如果编号为

000001的员

工信息已经在Employees中存在，则不能向Employees表中再插入编号为000001的数据行。

（四）在对象资源管理器中修改数据库YGGL中的表数据

1、在对象资源管理器中删除表Employees的第1行和Salary的第1行 注意进行删除操作时作为两表主键的EmployeeID的值，以保证数据完整性。

方法：在对象资源管理器中选择表Employees，右击鼠标，在弹出的快捷菜单中选择“编辑前200行”菜单项，在打开的“表数据”窗口中选中要删除的行，右击鼠标，在弹出的快捷菜单中选择“删除”菜单项。

Salary表中数据删除方法相同。

2、在对象资源管理器中将表Employees中编号为020018的记录的部门号改成4

方法：在对象资源管理器中右击表Employees，选择“编辑前200行”菜单项，在“表数据”窗口中将光标定位至编号为020018的记录的DepartmentID字段，将1改成4。将光标移出本行即保存了修改。 （五）使用T-SQL命令插入表数据

1、向表Employees中插入上一步骤中删除的一行数据 在“查询分析器”窗口中输入以下T-SQL语句：

单击快捷工具栏的“！执行”按钮，执行上述语句，在验证操作是否

成功时，可以在对象资源管理器中打开Employees表观察数据的变化。 2、向表Salary插入上一步骤中删除的一行数据

在“查询分析器”窗口中输入以下T-SQL语句：

单击快捷工具栏的“！执行”按钮，执行上述语句，在验证操作是否成功时，可以在对象资源管理器中打开Salary表观察数据的变化。 （六）使用SQL命令语句修改表数据

1、使用SQL命令修改表Salary中某个记录的字段值

执行上述语句，将编号为000001职工收入改为3000。 2、将所有职工收入增加100

执行完上述语句，打开Salary表查看数据的变化。可见，使用SQL语句操作表数据比在界面管理工具中操作表数据更为灵活。 3、使用SQL命令删除表Employees中编号为000001的职工信息

4、删除所有女性员工信息：

五、实验成绩评定办法

主要评分点：实验流程、调试过程、解决问题的能力、实验结果等。

3 基本训练型实验项目数据库的查询和视图1

匹配课程代码及名称：070785，数据库应用设计 适用专业及本项目实验学时：计算机科学与技术，6学时 一、实验目的及要求

（1）掌握SELECT语句的基本语法； （2）掌握子查询的表示； （3）掌握连接查询的表示；

（4）掌握SELECT语句的GROUP BY子句的作用和使用方法； （5）掌握SELECT语句的ORDER BY子句的作用和使用方法。 二、 实验内容

在已建好的YGGL数据库中，进行SELECT语句的基本使用、子查询、连接查询、聚合函数的使用及GROUP BY、ORDER BY子句使用的练习。提交程序源代码（电子版，1周内）和实验报告（纸制、1周内）。 三、实验条件及设备要求

已安装SQL Server 2008数据库管理系统的实验机。 四、实验实施步骤

（一）SELECT语句的基本使用

在YGGL数据库中，打开“查询编辑器”，实现以下查询，并记录查询结果。 1、用SELECT语句查询Departments和Salary表中所有的数据信息。 2、用SELECT语句查询Employees表中每个雇员的地址和电话。

3、查询Employees表中的部门号和性别，要求使用DISTINCT消除重复行。 4、查询EmployeeID为000001的雇员的地址和电话。 5、查询月收入高于2000元的员工号码。 6、查询1970年以后出生的员工的姓名和住址。 7、查询所有财务部的员工的号码和姓名。

8、查询Employees表中女雇员的地址和电话，使用AS子句将结果中各列的标题分别指定为地址、电话。

9、查询Employees表中男员工的姓名和出生日期，要求将各列标题用中文表示。

10、查询Employees表中员工的姓名和性别，要求SEX值为1时显示为“男”，为0时显示为“女”。

11、查询Employees员工的姓名、住址和收入水平，2000元以下显示低收入，2000-3000元显示为中等收入，3000元以上显示为高收入。 12、计算每个雇员的实际收入。 13、利用聚合函数，实现以下查询：

a. 计算Salary表中员工月收入的平均数。 b. 获得Employees表中最大员工号码。 c. 计算Salary表中所有员工的总支出。 d. 查询财务部雇员的最高和最低实际收入。 14、找出所有姓王的雇员的部门号。

15、找出所有其地址中含有“中山”的雇员的号码及部门。

16、查找员工号码倒数第二个数字为０的员工的姓名、地址和学历。 17、找出所有收入在2000－3000元之间的员工号码。

18、找出所有在部门“1”或“2”工作的雇员的号码。【注意：了解在SELECT语句中LIKE、BETWEEN…AND、IN、NOT以及CONTAIN谓词的作用。】 19、使用INTO子句，由表Salary创建“收入在1500元以上的员工”表，包括编号和收入。

20、使用INTO子句，由表Employees创建“男员工”表，包括编号和姓名。 （二）子查询的使用

在YGGL数据库中，打开“查询编辑器”，实现以下查询，并记录查询结果。 1、用子查询的方法查找在财务部工作的雇员的情况。

2、用子查询的方法查找所有收入在2500元以下的雇员的情况。

3、用子查询的方法查找财务部年龄不低于研发部雇员年龄的雇员的姓名。 4、用子查询的方法查找研发部比所有财务部雇员收入都高的雇员的姓名。 （三）连接查询的使用

在YGGL数据库中，打开“查询编辑器”，实现以下查询，并记录查询结果。 1、查询每个雇员的情况及其薪水的情况。 2、查询每个雇员的情况及其工作部门的情况。

3、使用内连接的方法查询名字为“王林”的员工所在的部门

4、使用内连接的方法查找出不在财务部工作的所有员工信息。 5、查找财务部收入在2000元以上的雇员姓名及其薪水详情。 6、查询研发部在1976以前出生的雇员姓名及其薪水详情。 （四）GROUP BY、ORDER BY子句的使用

在YGGL数据库中，打开“查询编辑器”，实现以下查询，并记录查询结果。 1、查找Employees表中男性和女性的人数。 2、按部门列出在该部门工作的员工的人数。

3、按员工的学历分组，排列出本科、大专和硕士的人数。 4、查找员工数超过2的部门名称和员工数量。

5、按员工的工作年份分组，统计各个工作年份的人数，例如，工作一年的多少人，工作两年的多少人。

6、将各雇员的情况按收入由低到高排列。 7、将员工的信息按出生的时间从小到大排列。

8、在ORDER BY子句中使用子查询，查询员工姓名、性别和工龄信息，要求按实际收入从大到小排列。 五、实验成绩评定办法

主要评分点：查询语句是否正确，查询的效率是否满足要求，视图创建是否正确。

4 综合型实验项目数据库的查询和视图2

匹配课程代码及名称：070785，数据库应用设计 适用专业及本项目实验学时：计算机科学与技术，3学时 一、实验目的及要求

（1）熟悉视图的概念和作用； （2）掌握视图的创建方法； （3）掌握如何让查询和修改视图。 二、 实验内容

在已建好的YGGL数据库中，设计并创建视图，并进行视图的查询、更新、删除操作。提交程序源代码（电子版，1周内）和实验报告（纸制、1周内）。

三、实验条件及设备要求

已安装SQL Server 2008数据库管理系统的实验机。 四、实验实施步骤

（一）创建视图

在YGGL数据库中，打开“查询编辑器”，设计并创建以下视图，并记录结果。

1、创建YGGL数据库上的视图DS_VIEW，视图包含Departments表的全部列。

2、创建YGGL数据库上的视图EMPLOYEES_VIEW，视图包含“员工号码”、“姓名”、“所在部门名称”和“实际收入”这几列。

（二）查询视图

在YGGL数据库中，打开“查询编辑器”，对所创建的视图进行查询，并记录结果。

1、从视图DS_VIEW中查询出部门号为3的部门名称。

2、从视图EMPLOYEES_VIEW中查询出姓名为“王林”的员工的实际收入。 （三）更新视图

在更新视图前需要了解可更新视图的概念，了解什么视图是不可以进行修改的。更新视图真正更新的是和视图关联的表。

1、向视图DS_VIEW中插入一行数据“6，广告部，广告业务”

执行完该命令，使用SELECT语句分别查看视图DS_VIEW和基本表Departments中发生的变化。

尝试向视图EMPLOYEES_VIEW中插入一行数据看看会发生什么情况。 2、修改视图DS_VIEW，将部门号为5的部门名称修改为“生产车间” 执行完该命令，使用SELECT语句分别查看视图DS_VIEW和基本表Departments中发生的变化。

3、将视图EMPLOYEES_VIEW中员工号为“000001”的员工的姓名修改为“王浩”。

4、删除视图DS_VIEW中部门号为“1”的一行数据。 （四）删除视图 删除视图DS_VIEW。

五、实验成绩评定办法

主要评分点视图创建是否合理，视图的修改，视图的删除，视图的更新。

5 综合型实验项目T-SQL编程

匹配课程代码及名称：070785，数据库应用设计 适用专业及本项目实验学时：计算机科学与技术，6学时 一、实验目的及要求

（1）掌握变量的分类及其使用； （2）掌握各种运算符的使用； （3）掌握各种控制语句的使用；

（4）掌握系统函数及其用户自定义函数的使用。 二、 实验内容

在已建好的YGGL数据库中，进行变量、运算符、流程控制语句、函数的设计与使用。提交程序源代码（电子版，1周内）和实验报告（纸制、1周内）。 三、实验条件及设备要求

已安装SQL Server 2008数据库管理系统的实验机。 四、实验相关知识点

数据库数据类型、程序设计语言。 五、实验实施步骤

（一）变量的使用

1、对于YGGL中的数据表结构，创建一个名为female的用户变量，并在select语句中使用该局部变量查找表中所有女员工的编号、姓名。 2、定义一个变量，用于获取号码为102201的员工的电话号码。

3、定义一个变量，用于描述YGGL数据库的Salary表中000001号员工的实际收入，然后查询该变量。

（二）运算符的使用

1、使用算数运算符“-”查询员工的实际收入。

2、使用比较运算符“>”查询Emloyees表中工作时间大于5年的员工信息。 （三）流程控制语句

1、判断Employees表中是否存在编号为111006的员工，如果存在，则显示该员工信息；若不存在，则显示“查无此人”。

2、判断姓名为王林的员工实际收入是否高于3000元，如果是，则显示其收入，否则显示“收入不高于3000”。

3、假设变量X的初始值为0，每次加1，直至X变为5。 4、使用循环输出一个用“*”组成的三角形。（三角形类型不限） 5、使用case语句对Employees表按部门进行分类。 （四）自定义函数的使用

1、定义一个函数实现如下功能：对于一个给定的DepartmentID值，查询该值在Departments表中是否存在，若存在则返回0，否则返回-1。

2、写一段T-SQL程序调用上述函数。当用Employees表插入一行记录时，首先调用函数CKECK_ID检索该记录的DpartmentID值在表Departments的DepartmentID字段中是否存在对应值，若存在，则将该记录插入Employees表。 3、自定义一个函数，计算一个数的阶乘。 （五）系统函数的使用 1、求一个数的绝对值。

2、使用rand（）函数产生一个0~1的随机值。 3、使用square（）函数获得一个数的平方。 4、使用sqrt（）返回一个数的平方根。 5、求财务部雇员的总人数。 6、求财务部收入最高的员工姓名。 7、查询员工收入的平均数。

8、使用ASCⅡ函数返回字符表达式最左端字符的ASCⅡ值。 9、使用CHAR()函数将ASCⅡ码代表的字符组成字符串。

10、使用LEFT（）函数返回从字符串‘abcdef’左边开始的3个字符。 11、获得当前的日期和时间。

12、查询YGGL数据库中员工号为000001的员工出生的年份。 13、使用DAY（）函数返回指定日期时间的天数。 14、列举出其他的时间日期函数。 15、使用其他类型的系统内置函数。

本文来源：https://www.bwwdw.com/article/59ut.html