发布时间 : 星期四 文章SQLSERVER2008实用教程实验参考答案(实验5)更新完毕开始阅读240c2d8be2bd960591c67783
实验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 ( ) GO
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
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' SELECT '查无此人' ELSE
（2）判断姓名为“王林”的员工的实际收入是否高于3000元，如果是，则显示其收入，如果不是，则显示“收入不高于3000元”
--先查找实际收入,再进行IF判断 IF
(SELECT b.InCome-b.OutCome AS 实际收入 FROM Employees a,Salary b WHERE a.EmployeeID=b.EmployeeID AND a.Name='王林')>3000
SELECT b.InCome-b.OutCome AS 实际收入 FROM Employees a,Salary b WHERE a.EmployeeID=b.EmployeeID AND a.Name='王林' ELSE
SELECT '收入不高于元'
（3）假设变量X的初始值为0，每次加1，直到X变为5，并打印输出
DECLARE @X INT SET @X=0 WHILE @X<5 BEGIN GO
SET @X=@X+1
PRINT 'X='+CONVERT(char(1),@X)
END
（4）使用循环输出一个用’*’组成的三角形
DECLARE @i INT DECLARE @n INT SET @n=10 SET @i=1
WHILE (@i<@n) BEGIN GO
--SPACE函数:返回指定个数重复的空格组成的字符串
--REPLICATE函数:以指定的次数重复字符表达式 PRINT (SPACE((@n-@i)/2)+REPLICATE('*',@i)) SET @i=@i+2
END
（5）使用CASE语句对Employees表按部门进行分类
SELECT EmployeeID,DepartmentID=
CASE DepartmentID
WHEN 1 THEN (SELECT DepartmentName FROM Departments b WHERE WHEN 2 THEN (SELECT DepartmentName FROM Departments b WHERE WHEN 3 THEN (SELECT DepartmentName FROM Departments b WHERE WHEN 4 THEN (SELECT DepartmentName FROM Departments b WHERE WHEN 5 THEN (SELECT DepartmentName FROM Departments b WHERE
b.DepartmentID='1') b.DepartmentID='2') b.DepartmentID='3') b.DepartmentID='4') 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 GO
DECLARE @num INT
IF EXISTS (SELECT DepartmentID FROM Departments WHERE
SET @num=1 SET @num=0
DepartmentID=@DepartmentID)
ELSE
RETURN @num
END
写一段程序调用该函数。向Employees表中添加一条记录时，首先调用函数CHECK_ID检索该记录的DepartmentID值在Departments表中是否存在，如果存在，则添加记录，如果不存在，则输出‘非法值’ 方法1：
DECLARE @num INT
SET @num=dbo.CHECK_ID('2') IF @num=1
INSERT INTO Employees VALUES('990210','张英','本科','1982-03-24',0,4,'南京东路号','12345678','2') ELSE
SELECT '非法值' GO;
方法2：
IF(dbo.CHECK_ID('3'))=1
INSERT INTO Employees VALUES('555556','小李','本科','1980-05-02',0,5,'将军街','99999999','3') ELSE
SELECT '非法值'
6. 系统内置函数的使用
（1）求-123的绝对值ABS SELECT ABS(-123); （2）使用RAND（）函数产生一个0~1的随机值
SELECT RAND(3); SELECT RAND();
（3）求22.36的平方SQUARE SELECT SQUARE(22.36); （4）求144的算术平方根SQRT SELECT SQRT(144); （5）日期函数GETDATE(),YEAR(),MONTH(),DAY()
SELECT GETDATE() AS 当前时间; --格式化输出
SELECT CONVERT(varchar(100),GETDATE(),23) AS 日期; SELECT CONVERT(varchar(100),GETDATE(),24) AS 时间; SELECT CONVERT(varchar(100),GETDATE(),120) AS 日期和时间;
SELECT YEAR(Birthday) AS 年,MONTH(Birthday) AS 月,DAY(Birthday) AS 日 FROM Employees;