第五章实验.docx
《第五章实验.docx》由会员分享，可在线阅读，更多相关《第五章实验.docx（17页珍藏版）》请在冰豆网上搜索。
第五章实验
5综合型实验项目T-SQL编程
匹配课程代码及名称：
070596，数据库管理系统
适用专业及本项目实验学时：
计算机科学与技术（金融信息），6学时
一、实验目的及要求
（1）掌握变量的分类及其使用；
（2）掌握各种运算符的使用；
（3）掌握各种控制语句的使用；
（4）掌握系统函数及其用户自定义函数的使用。
二、实验内容
在已建好的YGGL数据库中，进行变量、运算符、流程控制语句、函数的设计与使用。
提交程序源代码（电子版，1周内）和实验报告（纸制、1周内）。
三、实验条件及设备要求
已安装SQLServer2008数据库管理系统的实验机。
四、实验相关知识点
数据库数据类型、程序设计语言。
五、实验实施步骤
（一）变量的使用
1、对于YGGL中的数据表结构，创建一个名为female的用户变量，并在select语句中使用该局部变量查找表中所有女员工的编号、姓名。
Declare@femalebit
Set@female=0
SelectEmployeeID,NamefromEmployeeswhereSex=@female
2、定义一个变量，用于获取号码为102201的员工的电话号码。
Declare@PhoneNumberchar（12）
set@PhoneNumber=（selectPhoneNumberfromEmployeeswhereEmployeeID='102201'）
select@PhoneNumber
3、定义一个变量，用于描述YGGL数据库的Salary表中000001号员工的实际收入，然后查询该变量。
Declare@RealIncomefloat
set@RealIncome=（selectIncome-OutcomefromSalary
whereEmployeeID='000001'）
select@RealIncome
（二）运算符的使用
1、使用算数运算符“-”查询员工的实际收入。
selectInCome-OutComefromSalary
2、使用比较运算符“>”查询Emloyees表中工作时间大于5年的员工信息。
select*fromEmployeeswhereWorkYear>5
（三）流程控制语句
1、判断Employees表中是否存在编号为111006的员工，如果存在，则显示该员工信息；若不存在，则显示“查无此人”。
ifEXISTS（selectNameFROMEmployeeswhereEmployeeID='111006'）
select*fromEmployeeswhereEmployeeID='111006'
else
select'查无此人'
2、判断姓名为王林的员工实际收入是否高于3000元，如果是，则显示其收入，否则显示“收入不高于3000”。
ifEXISTS（selectInCome-OutComefromSalary,Employees
where（InCome-OutCome）>3000
and='王林'
and=
selectInCome-OutComefromSalary,Employees
where（（InCome-OutCome）>3000
and='王林'
and=
else
select'收入不高于'
3、假设变量X的初始值为0，每次加1，直至X变为5。
Declare@Xint
set@X=0
while@X<5
Begin
Set@X=@X+1
print'X='+convert（char
（1）,@X）
end
go
4、使用循环输出一个用“*”组成的三角形。
（三角形类型不限）
declare@iint
declare@nint
Set@i=1
set@n=20
while@i<@n
Begin
print（Space（（@n-@i）/2）+replicate（'*',@i））
set@i=@i+2
end
go
5、使用case语句对Employees表按部门进行分类。
go
selectEmployeeID,Name,Address,DepartmentID=caseDepartmentID
when1then'财务部'
when2then'人力资源部'
when3then'经理办公室'
when4then'研发部'
when5then'市场部'
end
fromEmployees
（四）自定义函数的使用
1、定义一个函数实现如下功能：
对于一个给定的DepartmentID值，查询该值在Departments表中是否存在，若存在则返回0，否则返回-1。
createfunctioncheck_id（@DepartmentIDchar（3））
returnsIntegerAS
begin
declare@numint
ifexists（SELECTDepartmentIDFROMDepartments
WHERE@DepartmentID=DepartmentID）
select@num=0
else
select@num=-1
return@num
end
go
2、写一段T-SQL程序调用上述函数。
当用Employees表插入一行记录时，首先调用函数CKECK_ID检索该记录的DpartmentID值在表Departments的DepartmentID字段中是否存在对应值，若存在，则将该记录插入Employees表。
createfunctioncheck_id（@DepartmentIDchar（3））
returnsIntegerAS
begin
declare@numint
ifexists（SELECTDepartmentIDFROMDepartments
WHERE@DepartmentID=DepartmentID）
select@num=0
else
select@num=-1
return@num
end
go
3、自定义一个函数，计算一个数的阶乘。
createfunctiontest_jc（@nbigint）
returnsbigint
as
begin
if@nnotbetween0and20returnnull
if@n<2return1
return（@n-1）*@n）
end
go
select（10）
（五）系统函数的使用
1、求一个数的绝对值。
SelectABS（-55）
2、使用rand（）函数产生一个0~1的随机值。
selectrand（）*1
3、使用函数获得一个数的平方。
selectsquare（16）
4、使用sqrt（）返回一个数的平方根。
selectsqrt（4）
5、求财务部雇员的总人数。
selectcount（EmployeeID）as财务总人数
fromEmployees
whereDepartmentID=（selectDepartmentIDfromDepartmentswhereDepartmentName='财务部'）
6、求财务部收入最高的员工姓名。
selecttop1
fromSalary,Employees,Departments
where=
and=
AND='财务部'
orderbydesc
7、查询员工收入的平均数。
selectAVG（Income-OutCome）fromSalary
8、使用ASCⅡ函数返回字符表达式最左端字符的ASCⅡ值。
selectASCII（'Abc'）
9、使用CHAR（）函数将ASCⅡ码代表的字符组成字符串。
selectASCII（'Abc'）
10、使用LEFT（）函数返回从字符串‘abcdef’左边开始的3个字符。
selectLEFT（'adgshsqw',3）
11、获得当前的日期和时间。
selectGETDATE（）
12、查询YGGL数据库中员工号为000001的员工出生的年份。
selectYEAR（Birthday）fromEmployeeswhereEmployeeID='000001'
13、使用DAY（）函数返回指定日期时间的天数。
selectDAY（'2016-4-10'）
14、列举出其他的时间日期函数。
selectMONTH（'2016-4-10'）
15、使用其他类型的系统内置函数。
selectCOS（0）
六、实验报告要求
1、按照山东女子学院实验报告格式书写。
2、关键部分的内容规范和要求如下：
（一）、实验目的及要求
指导教师给出的实验目的及具体实验要求。
（二）、实验使用的主要设备（含软件系统）
设备：
名称、规格型号、数量；
软件：
系统、软件名称、版本；
其他实验器材。
（三）、实验操作过程及内容
按照实验步骤写出操作要求，能够实现要求的语句及结果。
（四）、实验结论、问题与建议（含取得的成果）
总结实验过程，记录实验过程中所遇的问题及调试过程、处理方法，简述实验效果，回答实验思考题等。
七、实验成绩评定办法
主要评分点：
实验流程、调试过程、解决问题的能力、实验结果、实验效果等。
1、创建数据库YGGL
在“查询分析器”窗口中输入如下语句：
CREATEDATABASEYGGL
ON
（
NAME=’YGGL_Data’,
FILENAME=’（注：
一个本地路径）’,
SIZE=10MB,
MAXSIZE=50MB
FILEGROWTH=5%
）
LOGON
（
NAME=’YGGL_Log’,
FILENAME=’（注：
一个本地路径）’,
SIZE=2MB,
MAXSIZE=5MB,
FILEGROWTH=1MB
）
GO
1、在创建好的数据库YGGL中创建数据表
考虑到数据库YGGL要求包含员工的信息、部门的信息以及员工的薪水信息，所以数据库YGGL应包含下列3个表：
Employees（员工自然信息）表、Departments（部门信息）表、Salary（员工薪资）表。
各表的结构分别如表、表和表所示。
USEYGGL
GO
CREATETABLEEmployees
（EmployeeIDchar（6）NOTNULLPRIMARYKEY,
Namechar（6）NOTNULL,
Educationchar（6）NOTNULL,
Birthdaychar（6）NOTNULL,
SexbitNOTNULLDEFAULT1,
WorkYeartinyintNULL,
Addressvarchar（40）NULL,
PhoneNumberchar（12）NULL,
DepartmentIDchar（3）NOTNULL
）
GO
表Employees表结构
列名
数据类型
长度
是否可空
默认值
说明
EmployeeID
CHAR
6
否
无
员工编号，主键
Name
CHAR
10
否
无
姓名
Education
CHAR
4
否
无
学历
Birthday
DATE
默认
否
无
出生日期
Sex
BIT
默认
否
1
1：
男0：
女
WorkYear
TINYINT
默认
是
无
工作时间
Address
VARCHAR
40
是
无
地址
PhoneNum
CHAR
12
是
无
电话号码
DepartmentID
CHAR
3
否
无
部门号，外键
表Departments表结构
列名
数据类型
长度
是否可空
默认值
说明
DepartmentID
CHAR
3
否
无
部门编号，主键
DepartName
CHAR
20
否
无
部门名
Note
VARCHAR
100
是
无
备注
表Salary表结构
列名
数据类型
长度
是否可空
默认值
说明
EmployeeID
CHAR
6
否
无
员工编号，主键
InCome
FLOAT
默认
否
无
收入
OutCome
FLOAT
默认
是
无
支出
要求分别使用对象资源管理器和T-SQL命令完成数据表的创建。
2、分别使用对象资源管理器和T-SQL语句，向数据库YGGL的三个表Employees、Departments和Salary中插入多行数据记录（样本数据如表、表和表所示），然后修改和删除一些记录。
使用T-SQL语句进行有限制的修改和删除。
表Employees表数据样本
编号
姓名
学历
出生日期
性别
工作时间
住址
电话
部门号
000001
王林
大专
1966-01-23
1
8
中山路32号
8355668
2
010008
伍荣华
本科
1976-03-28
1
3
北京路2号
8321321
1
020010
王向荣
硕士
1982-12-09
1
2
四牌楼10号
8379236
1
020018
李丽
大专
1960-07-30
0
6
中山路10-2
8341330
1
102201
刘明
本科
1972-10-18
1
3
解放路12号
8360668
5
102208
朱军
硕士
1965-09-28
1
2
北京东路2
8345632
5
108991
钟敏
硕士
1979-08-10
0
4
中山北路3
8470223
3
111006
张士兵
本科
1974-10-23
1
1
虎踞路2号
8349562
5
210678
林涛
大专
1977-04-02
1
2
北京东路12
8533196
3
302566
李玉敏
本科
1968-09-20
1
3
龙蟠路9号
8645231
4
308759
叶凡
本科
1978-11-18
1
2
舜玉路10号
8330891
4
504209
陈琳琳
大专
1969-09-03
0
5
阳光新路1
8446815
4
表Departments表
部门号
部门名称
备注
1
财务部
NULL
2
人力资源部
NULL
3
经理办公室
NULL
4
研发部
NULL
5
市场部
NULL
表Salary表
编号
收入
支出
000001
010008
102201
111006
504209
302566
108991
020010
020018
308759
210678
102208
升级会员 