第五章 实验

5 综合型实验项目T-SQL编程匹配课程代码及名称：070596，数据库管理系统适用专业及本项目实验学时：计算机科学与技术（金融信息），6学时一、实验目的及要求（1）掌握变量的分类及其使用；（2）掌握各种运算符的使用；（3）掌握各种控制语句的使用；（4）掌握系统函数及其用户自定义函数的使用。

二、实验内容在已建好的YGGL数据库中，进行变量、运算符、流程控制语句、函数的设计与使用。

提交程序源代码（电子版，1周内）和实验报告（纸制、1周内）。

三、实验条件及设备要求已安装SQL Server 2008数据库管理系统的实验机。

四、实验相关知识点数据库数据类型、程序设计语言。

五、实验实施步骤（一）变量的使用1、对于YGGL中的数据表结构，创建一个名为female的用户变量，并在select 语句中使用该局部变量查找表中所有女员工的编号、姓名。

Declare @female bitSet @female=0Select EmployeeID,Name from Employees where Sex=@female2、定义一个变量，用于获取号码为102201的员工的电话号码。

Declare@PhoneNumber char(12)set@PhoneNumber=(select PhoneNumber from Employees whereEmployeeID='102201')select@PhoneNumber3、定义一个变量，用于描述YGGL数据库的Salary表中000001号员工的实际收入，然后查询该变量。

Declare@RealIncome floatset@RealIncome=(select Income-Outcome from Salarywhere EmployeeID='000001')select@RealIncome（二）运算符的使用1、使用算数运算符“-”查询员工的实际收入。

select InCome-OutCome from Salary2、使用比较运算符“>”查询Emloyees表中工作时间大于5年的员工信息。

select*from Employees where WorkYear>5（三）流程控制语句1、判断Employees表中是否存在编号为111006的员工，如果存在，则显示该员工信息；若不存在，则显示“查无此人”。

if EXISTS(select Name FROM Employees where EmployeeID='111006') select*from Employees where EmployeeID='111006'elseselect'查无此人'2、判断姓名为王林的员工实际收入是否高于3000元，如果是，则显示其收入，否则显示“收入不高于3000”。

if EXISTS(select InCome-OutCome from Salary,Employeeswhere(InCome-OutCome)>3000and ='王林'and Employees.EmployeeID=Salary.EmployeeID)select InCome-OutCome from Salary,Employeeswhere((InCome-OutCome)>3000and ='王林'and Employees.EmployeeID=Salary.EmployeeID)elseselect'收入不高于'3、假设变量X的初始值为0，每次加1，直至X变为5。

Declare@X intset@X=0while@X<5BeginSet@X=@X+1print'X='+convert(char(1),@X)endgo4、使用循环输出一个用“*”组成的三角形。

（三角形类型不限）declare@i intdeclare@n intSet@i=1set@n=20while@i<@nBeginprint(Space((@n-@i)/2)+replicate('*',@i))set@i=@i+2endgo5、使用case语句对Employees表按部门进行分类。

goselect EmployeeID,Name,Address,DepartmentID=case DepartmentID when 1 then'财务部'when 2 then'人力资源部'when 3 then'经理办公室'when 4 then'研发部'when 5 then'市场部'endfrom Employees（四）自定义函数的使用1、定义一个函数实现如下功能：对于一个给定的DepartmentID值，查询该值在Departments表中是否存在，若存在则返回0，否则返回-1。

create function check_id(@DepartmentID char(3))returns Integer ASbegindeclare@num intif exists(SELECT DepartmentID FROM DepartmentsWHERE@DepartmentID=DepartmentID)select@num=0elseselect@num=-1return@numendgo2、写一段T-SQL程序调用上述函数。

当用Employees表插入一行记录时，首先调用函数CKECK_ID检索该记录的DpartmentID值在表Departments的DepartmentID字段中是否存在对应值，若存在，则将该记录插入Employees表。

create function check_id(@DepartmentID char(3))returns Integer ASbegindeclare@num intif exists(SELECT DepartmentID FROM DepartmentsWHERE@DepartmentID=DepartmentID)select@num=0elseselect@num=-1return@numendgo3、自定义一个函数，计算一个数的阶乘。

create function test_jc(@n bigint)returns bigintasbeginif@n not between 0 and 20 return nullif@n<2 return 1return (dbo.test_jc(@n-1)*@n)endgoselect dbo.test_jc(10)（五）系统函数的使用1、求一个数的绝对值。

Select ABS(-55)2、使用rand（）函数产生一个0~1的随机值。

select rand()*13、使用函数获得一个数的平方。

select square(16)4、使用sqrt（）返回一个数的平方根。

select sqrt(4)5、求财务部雇员的总人数。

select count(EmployeeID)as财务总人数from Employeeswhere DepartmentID=(select DepartmentID from Departments where DepartmentName='财务部')6、求财务部收入最高的员工姓名。

select top 1 from Salary,Employees,Departmentswhere Employees.EmployeeID=Salary.EmployeeIDand Employees.DepartmentID=Departments.DepartmentIDAND Departments.DepartmentName='财务部'order by Salary.InCome desc7、查询员工收入的平均数。

select AVG(Income-OutCome)from Salary8、使用ASCⅡ函数返回字符表达式最左端字符的ASCⅡ值。

select ASCII('Abc')9、使用CHAR()函数将ASCⅡ码代表的字符组成字符串。

select ASCII('Abc')10、使用LEFT（）函数返回从字符串‘abcdef’左边开始的3个字符。

select LEFT('adgshsqw',3)11、获得当前的日期和时间。

select GETDATE()12、查询YGGL数据库中员工号为000001的员工出生的年份。

select YEAR(Birthday)from Employees where EmployeeID='000001'13、使用DAY（）函数返回指定日期时间的天数。

select DAY('2016-4-10')14、列举出其他的时间日期函数。

select MONTH('2016-4-10')15、使用其他类型的系统内置函数。

select COS(0)六、实验报告要求1、按照山东女子学院实验报告格式书写。

2、关键部分的内容规范和要求如下：（一）、实验目的及要求指导教师给出的实验目的及具体实验要求。

（二）、实验使用的主要设备（含软件系统）设备：名称、规格型号、数量；软件：系统、软件名称、版本；其他实验器材。

（三）、实验操作过程及内容按照实验步骤写出操作要求，能够实现要求的语句及结果。

（四）、实验结论、问题与建议（含取得的成果）总结实验过程，记录实验过程中所遇的问题及调试过程、处理方法，简述实验效果，回答实验思考题等。

七、实验成绩评定办法主要评分点：实验流程、调试过程、解决问题的能力、实验结果、实验效果等。

1、创建数据库YGGL在“查询分析器”窗口中输入如下语句：CREATE DATABASE YGGLON(NAME=’YGGL_Data’,FILENAME=’ (注：一个本地路径)’,SIZE=10MB,MAXSIZE=50MBFILEGROWTH=5%)LOG ON(NAME=’YGGL_Log’,FILENAME=’ (注：一个本地路径)’,SIZE=2MB,MAXSIZE=5MB,FILEGROWTH=1MB)GO1、在创建好的数据库YGGL中创建数据表考虑到数据库YGGL要求包含员工的信息、部门的信息以及员工的薪水信息，所以数据库YGGL应包含下列3个表：Employees（员工自然信息）表、Departments（部门信息）表、Salary（员工薪资）表。