SQL SERVER 实用教程(第3版)实验5 T SQL编程

实验5T-SQL编程
1.自定义数据类型的使用
1对于实验2给出数据表结构，再自定义一数据类型ID_type，用于描述员工编号。

USE YGGL
EXEC sp_addtype'ID_type',
'char(6)','not null'
GO
2在YGGLXJ数据库中创建Employees表，表结构与Employees类似，只是EmployeeID 列使用数据类型为用户自定义数据类型ID_type
USE YGGL
GO
IF EXISTS(SELECT name FROM sysobjects
WHERE name='Employyees3')
DROP table employees3
/*首先在系统表中查看Employees3表是否存在，若存在，则删除该表*/
CREATE TABLE Employees3
(EmployeeID ID_type,/*定义字段EmployeeID的类型为ID_type*/
Name char(10)NOT NULL,
Education char(4)NOT NULL,
Birthday date NOT NULL,
Sex bit NOT NULL DEFAULT1,
WorkYear tinyint NULL,
Address varchar(40)NULL,
PhoneNumber char(12)NULL,
DepartmentID char(3)NULL,
PRIMARY KEY(EmployeeID)
)
GO
2.变量的使用。

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
3.运算符的使用
1使用运算符“-”查询员工的实际收入
SELECT InCome-OutCome
FROM Salary
2使用比较运算符“>”查询Employees表中工作时间大于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假设变量X的初始值为0，每次加一，直至X变成5
DECLARE@X INT
SET@X=1
WHILE@X<5
BEGIN
SET@X=@X+1
PRINT'X='+CONVERT(char(1),@X)
END
GO
3使用CASE语句对Employees表按部门进行分类USE YGGL
GO
SELECT EmployeeID,Name Address,DepartmentID= CASE DepartmentID
WHEN1THEN'财务部'
WHEN2THEN'人力资源部'
WHEN3THEN'经理办公室'
WHEN4THEN'研发部'
WHEN5THEN'市场部'
END
FROM Employees
5,.自定义函数的使用
①定义一个函数实现如下功能：对于一个给定的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
3写一段T-SQL程序调用上述函数。

当向Employees表插入一行记录时，首先调用函数CHECK-ID检索该记录的的DepartmentID值在表Departmengts和DepartmentID字段中是否存在对应的值，若存在，则将该记录插入Employees表
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
5.系统内置函数的使用
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获得当前的日期和时间
SELECT getdate()
SELECT YEAR(Birthday) FROM Employees WHERE EmployeeID='000001'。