第五章实验.docx

1、第五章实验5 综合型实验项目T-SQL编程匹配课程代码及名称：070596，数据库管理系统适用专业及本项目实验学时：计算机科学与技术（金融信息），6学时一、实验目的及要求（1）掌握变量的分类及其使用；（2）掌握各种运算符的使用；（3）掌握各种控制语句的使用；（4）掌握系统函数及其用户自定义函数的使用。二、实验内容在已建好的YGGL数据库中，进行变量、运算符、流程控制语句、函数的设计与使用。提交程序源代码（电子版，1周内）和实验报告（纸制、1周内）。三、实验条件及设备要求已安装SQL Server 2008数据库管理系统的实验机。四、实验相关知识点数据库数据类型、程序设计语言。五、实验实施步骤（

2、一）变量的使用1、对于YGGL中的数据表结构，创建一个名为female的用户变量，并在select语句中使用该局部变量查找表中所有女员工的编号、姓名。Declare female bitSet female=0 Select EmployeeID,Name from Employees where Sex=female2、定义一个变量，用于获取号码为102201的员工的电话号码。Declare PhoneNumber char(12)set PhoneNumber=(select PhoneNumber from Employees where EmployeeID=102201)select

3、 PhoneNumber 3、定义一个变量，用于描述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 Emplo

4、yees where WorkYear5（三）流程控制语句1、判断Employees表中是否存在编号为111006的员工，如果存在，则显示该员工信息；若不存在，则显示“查无此人”。if EXISTS(select Name FROM Employees where EmployeeID=111006)select * from Employees where EmployeeID=111006else select 查无此人2、判断姓名为王林的员工实际收入是否高于3000元，如果是，则显示其收入，否则显示“收入不高于3000”。if EXISTS(select InCome-OutCome f

5、rom Salary, Employees where(InCome-OutCome)3000 and =王林 and =select InCome-OutCome from Salary, Employees where(InCome-OutCome)3000 and =王林 and =else select 收入不高于3、假设变量X的初始值为0，每次加1，直至X变为5。Declare X intset X=0while X5Begin Set X=X+1print X=+convert(char(1),X)endgo4、使用循环输出一个用“*”组成的三角形。（三角形类型不限）declare

6、 i intdeclare n intSet i=1set n=20while inBegin print(Space(n-i)/2)+replicate(*,i)set i=i+2endgo5、使用case语句对Employees表按部门进行分类。goselect EmployeeID,Name,Address,DepartmentID= case DepartmentIDwhen 1 then 财务部when 2 then 人力资源部 when 3 then 经理办公室 when 4 then 研发部when 5 then 市场部endfrom Employees（四）自定义函数的使用1、

7、定义一个函数实现如下功能：对于一个给定的DepartmentID值，查询该值在Departments表中是否存在，若存在则返回0，否则返回-1。create function check_id(DepartmentID char(3)returns Integer ASbegindeclare num intif exists(SELECT DepartmentID FROM Departments WHERE DepartmentID=DepartmentID)select num=0elseselect num=-1return numendgo2、写一段T-SQL程序调用上述函数。当用E

8、mployees表插入一行记录时，首先调用函数CKECK_ID检索该记录的DpartmentID值在表Departments的DepartmentID字段中是否存在对应值，若存在，则将该记录插入Employees表。create function check_id(DepartmentID char(3)returns Integer ASbegindeclare num intif exists(SELECT DepartmentID FROM Departments WHERE DepartmentID=DepartmentID)select num=0elseselect num=-1r

9、eturn numendgo3、自定义一个函数，计算一个数的阶乘。create function test_jc(n bigint)returns bigint asbeginif n not between 0 and 20 return nullif n2 return 1return (n-1)*n) endgoselect (10)（五）系统函数的使用1、求一个数的绝对值。Select ABS(-55)2、使用rand（）函数产生一个01的随机值。select rand()*13、使用 函数获得一个数的平方。select square(16)4、使用sqrt（）返回一个数的平方根。se

10、lect sqrt(4)5、求财务部雇员的总人数。select count(EmployeeID) as 财务总人数 from Employees where DepartmentID =(select DepartmentID from Departments where DepartmentName=财务部)6、求财务部收入最高的员工姓名。select top 1 from Salary,Employees,Departments where = and = AND =财务部 order by desc7、查询员工收入的平均数。 select AVG(Income-OutCome) fro

11、m 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=00000113、使用DAY（）函数返回指定日期时间的天数。

12、select DAY(2016-4-10)14、列举出其他的时间日期函数。select MONTH(2016-4-10)15、使用其他类型的系统内置函数。select COS(0)六、实验报告要求1、按照山东女子学院实验报告格式书写。2、关键部分的内容规范和要求如下：（一）、实验目的及要求指导教师给出的实验目的及具体实验要求。（二）、实验使用的主要设备（含软件系统）设备：名称、规格型号、数量；软件：系统、软件名称、版本；其他实验器材。（三）、实验操作过程及内容按照实验步骤写出操作要求，能够实现要求的语句及结果。（四）、实验结论、问题与建议（含取得的成果）总结实验过程，记录实验过程中所遇的问题及

13、调试过程、处理方法，简述实验效果，回答实验思考题等。七、实验成绩评定办法主要评分点：实验流程、调试过程、解决问题的能力、实验结果、实验效果等。1、创建数据库YGGL在“查询分析器”窗口中输入如下语句：CREATE DATABASE YGGLON( NAME=YGGL_Data, FILENAME= (注：一个本地路径), SIZE=10MB, MAXSIZE=50MB FILEGROWTH=5%)LOG ON( NAME=YGGL_Log, FILENAME= (注：一个本地路径), SIZE=2MB, MAXSIZE=5MB, FILEGROWTH=1MB)GO1、在创建好的数据库YGGL中

14、创建数据表考虑到数据库YGGL要求包含员工的信息、部门的信息以及员工的薪水信息，所以数据库YGGL应包含下列3个表：Employees（员工自然信息）表、Departments（部门信息）表、Salary（员工薪资）表。各表的结构分别如表、表和表所示。USE YGGLGOCREATE TABLE Employees( EmployeeID char(6) NOT NULL PRIMARY KEY, Name char(6) NOT NULL, Education char(6) NOT NULL, Birthday char(6) NOT NULL, Sex bit NOT NULL DEFA

15、ULT 1,WorkYear tinyint NULL, Address varchar(40) NULL,PhoneNumber char(12) NULL,DepartmentID char(3) NOT NULL)GO表 Employees表结构列名数据类型长度是否可空默认值说明EmployeeIDCHAR6否无员工编号，主键NameCHAR10否无姓名EducationCHAR4否无学历BirthdayDATE默认否无出生日期SexBIT默认否11：男 0：女Work YearTINYINT默认是无工作时间AddressVARCHAR40是无地址PhoneNumCHAR12是无电话号码

16、DepartmentIDCHAR3否无部门号，外键表 Departments表结构列名数据类型长度是否可空默认值说明DepartmentIDCHAR3否无部门编号，主键DepartNameCHAR20否无部门名NoteVARCHAR100是无备注表 Salary表结构列名数据类型长度是否可空默认值说明EmployeeIDCHAR6否无员工编号，主键InComeFLOAT默认否无收入OutComeFLOAT默认是无支出要求分别使用对象资源管理器和T-SQL命令完成数据表的创建。2、分别使用对象资源管理器和T-SQL语句，向数据库YGGL的三个表Employees、Departments和Sala

17、ry中插入多行数据记录（样本数据如表、表和表所示），然后修改和删除一些记录。使用T-SQL语句进行有限制的修改和删除。表 Employees表数据样本编号姓名学历出生日期性别工作时间住址电话部门号000001王林大专1966-01-2318中山路32号83556682010008伍荣华本科1976-03-2813北京路2号83213211020010王向荣硕士1982-12-0912四牌楼10号83792361020018李丽大专1960-07-3006中山路10-283413301102201刘明本科1972-10-1813解放路12号83606685102208朱军硕士1965-09-28

18、12北京东路283456325108991钟敏硕士1979-08-1004中山北路384702233111006张士兵本科1974-10-2311虎踞路2号83495625210678林涛大专1977-04-0212北京东路1285331963302566李玉敏本科1968-09-2013龙蟠路9号86452314308759叶凡本科1978-11-1812舜玉路10号83308914504209陈琳琳大专1969-09-0305阳光新路184468154表 Departments表部门号部门名称备注1财务部NULL2人力资源部NULL3经理办公室NULL4研发部NULL5市场部NULL表 Salary表编号收入支出000001010008102201111006504209302566108991020010020018308759210678102208