SQL实验五：T-SQL编程
（
二 〇 一 五 年 四 月
《数据库原理及应用》实验报告
学校代码： 10128 学 号： 201120905048
题 目：T-S Q L 编程 学生姓名：孙跃 学 院：理学院 系 别：数学系
专 业：信息与计算科学 班 级：信计12-2 任课教师：侯睿
一、实验目的
（1）进一步巩固第2章～第4章所学内容；
（2）掌握用户自定义类型的使用；
（3）掌握变量的分类及其使用；
（4）掌握各种运算符的使用；
（5）掌握各种控制语句的使用；
（6）掌握系统函数及用户自定义函数的使用。
二、实验内容
1.自定义数据类型的使用。
（1）对于实验2给出的数据库表结构，再自定义一数据类型ID_type，用于描述员工编号。
（2）在YGGL数据库中创建Employees3表，表结构与Employees类似，只是EmployeeID列使用的数据类型为用户自定义类型ID_type。
2.变量的使用。
（1）对于实验2给出的数据库表结构，创建一个名为female的用户变量，并在SELECT语句中使用该局部变量查找表中所有女员工的编号、姓名。
（2）定义一个变量，用于获取号码为102201的员工的电话号码。
3.运算符的使用。
（1）使用算数运算符“-”查询员工的实际收入。
（2）使用比较运算符“>”查询Employees表中工作时间大于5年的员工信息。
4.流程控制语句。
（1）判断Employees表中是否存在编号为11006的员工，如果存在则显示该员工信息，不存在则显示查无此人。
（2）假设变量X的初始值为0，每次加1，直至X变为5。
（3）使用CASE语句对Employees表按部门进行分类。
5.自定义函数的使用。
（1）定义一个函数实现如下功能：对于一个给定的DepartmentID值，查询该值在Departments表中是否存在，若存在则返回0，否则返回-1。
（2）写一段T-SQL程序调用上述函数。
6.系统内置函数的使用。
（1）求一个数的绝对值。
（2）求财务部雇员的总人数。
（3）使用ASCII函数返回字符表达式最左端字符的ASCII值。
（4）获得当前的日期和时间。
三、实验程序
自定义数据类型的使用
1.对于实验2给出的数据库表结构，再自定义一数据类型ID_type，用于描述员工编号。
USE YGGL
EXEC sp_addtype'ID_type','char(6)','not null'
GO
2.在YGGL数据库中创建Employees3表，表结构与Employees类似，只是EmployeeID列使用的数据类型为用户自定义类型ID_type。
USE YGGL
GO
IF EXISTS(SELECT name FROM sysobjects WHERE name='Employees3') DROP table employees3
CREATE TABLE Employees3
(
EmployeeID ID_type,
Name char(10)NOT NULL,
Education char(4)NOT NULL,
Birthday datetime NOT NULL,
Sex bit NOT NULL DEFAULT 1,
WorkYear tinyint NULL,
Address varchar(40)NULL,
PhoneNumber char(12)NULL,
DepartmentID char(3)NOT NULL,
PRIMARY KEY(EmployeeID)
)
GO
变量的使用
1.对于实验2给出的数据库表结构，创建一个名为female的用户变量，并在SELECT语句中使用该局部变量查找表中所有女员工的编号、姓名。
USE YGGL
DECLARE @female bit
SET @female=0
SELECT EmployeeID,Name
FROM Employees
WHERE Sex=@Female
2.定义一个变量，用于获取号码为102201的员工的电话号码。
DECLARE @phone char(12)
SET @phone=(SELECT PhoneNumber
FROM Employees
WHERE EmployeeID='102201')
SELECT @phone
运算符的使用
1.使用算数运算符“-”查询员工的实际收入。
SELECT InCome-OutCome
FROM Salary
2.使用比较运算符“>”查询Employees表中工作时间大于5年的员工信息。
SELECT*
FROM Employees
WHERE WorkYear>5
3.判断Employees表中是否存在编号为11006的员工，如果存在则显示该员工信息，不存在则显示“查无此人”。IF EXISTS(SELECT Name FROM Employees WHERE EmployeeID='111006') SELECT*FROM Employees WHERE EmployeeID='111006'
ELSE
SELECT'查无此人'
4.假设变量X的初始值为0，每次加1，直至X变为5。
DECLARE @X INT
SET @X=1
WHILE @X<5
BEGIN
SET @X=@X+1
PRINT'X='+CONVERT(char(1),@X)
END
GO
5.使用CASE语句对Employees表按部门进行分类。
USE YGGL
GO
SELECT EmployeeID,Name,Address,DepartmentID=
CASE DepartmentID
WHEN1 THEN'财务部'
WHEN2 THEN'人力资源部'
WHEN3 THEN'经理办公室'
WHEN4 THEN'研发部'
WHEN5 THEN'市场部'
END
FROM Employees
自定义函数的使用
1.定义一个函数实现如下功能：对于一个给定的DepartmentID值，查询该值在Departments表中是否存在，若存在则返回0，否则返回-1。
CREATE FUNCTION CHECK_ID(@departmentid char(3))
RETURNS integer AS
BEGIN
DECLARE @num int
IF EXISTS(SELECT departmentID FROM departments
WHERE @departmentid=departmentID)
SELECT @num=0
ELSE
SELECT @num=-1
RETURN @num
END
GO
2.写一段T-SQL程序调用上述函数。
USE YGGL
GO
DECLARE @num int
SELECT @num=dbo.CHECK_ID('2')
IF @num=0
INSERT Employees
VALUES('990210','张英','本科','1982-03-24',0,4,'南京镇江路号','8497534','2')
GO
系统内置函数的使用
1.求一个数的绝对值。
SELECT ABS(-123)
2.求财务部雇员的总人数。
USE YGGL
SELECT COUNT(EmployeeID)AS财务部人数
FROM Employees
WHERE DepartmentID=
(
SELECT DepartmentID
FROM Departments
WHERE DepartmentName='财务部'
)
3.使用ASCII函数返回字符表达式最左端字符的ASCII值。
SELECT ASCII('abc')
4.（1）获得当前的日期和时间。
SELECT getdate()
（2）查询YGGL数据库中员工号为000001的员工出生的年份
SELECT YEAR(Birthday)
FROM Employees
WHERE EmployeeID='000001'
四、实验结果
1.对于实验2给出的数据库表结构，再自定义一数据类型ID_type，用于描述员工编号。
2.在YGGL数据库中创建Employees3表，表结构与Employees类似，只是EmployeeID列使用的数据类型为用户自定义类型ID_type。
3.对于实验2给出的数据库表结构，创建一个名为female的用户变量，并在SELECT语句中使用该局部变量查找表中所有女员工的编号、姓名。
4.定义一个变量，用于获取号码为102201的员工的电话号码。
5.使用算数运算符“-”查询员工的实际收入。
6.使用比较运算符“>”查询Employees表中工作时间大于5年的员工信息。
7.判断Employees表中是否存在编号为11006的员工，如果存在则显示该员工信息，不存在则显示“查无此人”。
8.假设变量X的初始值为0，每次加1，直至X变为5。
9.使用CASE语句对Employees表按部门进行分类。
10.定义一个函数实现如下功能：对于一个给定的DepartmentID值，查询该值在Departments表中是否存在，若存在则返回0，否则返回-1。
11.写一段T-SQL程序调用上述函数。
12.求一个数的绝对值。
13.求财务部雇员的总人数。
14.使用ASCII函数返回字符表达式最左端字符的ASCII值。
15.获得当前的日期和时间。
16.查询YGGL数据库中员工号为000001的员工出生的年份。
五、实验总结
通过本次实验，我对第2章～第4章所学内容进一步巩固和加深，对课堂上没有理解的问题重新进行了学习，掌握了用户自定义类型、各种运算符、各种控制语句、系统函数及用户自定义函数的使用，也掌握变量的分类及其使用。由于编程量较大，也锻炼了我编程操作要细致谨慎，由于前一阶段的基础知识没有完全掌握，导致本次实验中遇到不少问题，因此在今后的学习中我要吸取经验，认真学习。