SQLSERVER2008实用教程实验参考答案(实验5)

实验5 T-SQL编程

1. 自定义数据类型

新建一个数据类型ID_Type，用于描述员工编号。新建一个表Employees3，表结构与Employees 表类似，EmployeeID列的数据类型使用自定义的数据类型。

方法1：使用系统内置的存储过程sp_addtype。

USE YGGL

EXEC sp_addtype'ID_Type',

'char(6)','not null'

GO

方法2：使用CREATE TYPE语句

USE YGGL

GO

CREATE TYPE ID_Type FROM char(6)NOT NULL;

--查看可编程性-类型-用户定义数据类型中的变化

IF EXISTS(SELECT name FROM sysobjects WHERE name='Employees3')

DROP TABLE Employees3

CREATE TABLE Employees3

(

EmployeeID ID_Type PRIMARY KEY,

Name char(10)NOT NULL,

Education char(4)NOT NULL,

Birthday date NOT NULL,

Sex bit NOT NULL DEFAULT 1,

WorkYear tinyint NULL,

Address varchar(40)NULL,

PhoneNumber char(12)NULL,

DepartmentID char(3)NOT NULL

)

GO

2. 变量的使用

（1）创建一个名为Female的用户变量，并在Employees表中查询所有女员工的编号、姓名、性别

DECLARE@Female bit--定义变量,指定变量数据类型

SET@Female=0 --为该变量赋初值

SELECT EmployeeID,Name,Sex FROM Employees WHERE Sex=@Female;

（2）定义一个变量，将员工编号为102201的员工的电话号码赋值给该变量

DECLARE@Phone char(12)

SET@Phone=(SELECT PhoneNumber FROM Employees WHERE EmployeeID='102201')

SELECT a.*,@Phone FROM Employees a WHERE EmployeeID='102201';

（3）定义一个变量，用于描述Salary表中000001号员工的实际收入，然后查询该变量DECLARE@RealIncome float

SET@RealIncome=(SELECT InCome-OutCome FROM Salary WHERE

EmployeeID='000001')

SELECT@RealIncome;

3. 运算符的使用

（1）查询员工的实际收入

SELECT InCome-OutCome AS实际收入FROM Salary;

（2）查询工作时间大于5年的员工信息

SELECT*FROM Employees WHERE WorkYear>5;

4.流程控制语句

（1）判断Employees表中是否存在编号为111006的员工，如果存在，则显示该员工信息，如果不存在，则显示查无此人

IF EXISTS(SELECT Name FROM Employees WHERE EmployeeID='111006') SELECT*FROM Employees WHERE EmployeeID='111006'

ELSE

SELECT'查无此人'

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

--先查找实际收入,再进行IF判断

IF

(SELECT b.InCome-b.OutCome AS实际收入FROM Employees a,Salary b WHERE a.EmployeeID=b.EmployeeID AND ='王林')>3000

SELECT b.InCome-b.OutCome AS实际收入FROM Employees a,Salary b WHERE a.EmployeeID=b.EmployeeID AND ='王林'

ELSE

SELECT'收入不高于元'

（3）假设变量X的初始值为0，每次加1，直到X变为5，并打印输出

DECLARE@X INT

SET@X=0

WHILE@X<5

BEGIN

SET@X=@X+1

PRINT'X='+CONVERT(char(1),@X)

END

GO

（4）使用循环输出一个用’*’组成的三角形

DECLARE@i INT

DECLARE@n INT

SET@n=10

SET@i=1

WHILE (@i<@n)

BEGIN

--SPACE函数:返回指定个数重复的空格组成的字符串

--REPLICATE函数:以指定的次数重复字符表达式

PRINT (SPACE((@n-@i)/2)+REPLICATE('*',@i))

SET@i=@i+2

END

GO

（5）使用CASE语句对Employees表按部门进行分类

SELECT EmployeeID,DepartmentID=

CASE DepartmentID

WHEN 1 THEN (SELECT DepartmentName FROM Departments b WHERE

b.DepartmentID='1')

WHEN 2 THEN (SELECT DepartmentName FROM Departments b WHERE

b.DepartmentID='2')

WHEN 3 THEN (SELECT DepartmentName FROM Departments b WHERE

b.DepartmentID='3')

WHEN 4 THEN (SELECT DepartmentName FROM Departments b WHERE

b.DepartmentID='4')

WHEN 5 THEN (SELECT DepartmentName FROM Departments b WHERE

b.DepartmentID='5')

END

FROM Employees ORDER BY DepartmentID;

5. 自定义函数的使用

定义一个函数CHECK_ID：对于一个给定的DepartmentID值，查询该值在Departments表中是否存在，如果存在，则返回1，不存在，则返回0。

CREATE FUNCTION CHECK_ID(@DepartmentID char(3))

RETURNS INTEGER AS

BEGIN

DECLARE@num INT

IF EXISTS(SELECT DepartmentID FROM Departments WHERE

DepartmentID=@DepartmentID)

SET@num=1

ELSE

SET@num=0

RETURN@num

END

GO

写一段程序调用该函数。向Employees表中添加一条记录时，首先调用函数CHECK_ID检索该记录的DepartmentID值在Departments表中是否存在，如果存在，则添加记录，如果不存在，则输出‘非法值’

方法1：