SQL Server实用教程(第三版)实验5 T-SQL教程.doc

实验5：T-SQL编程1目的与要求（1） 进一步巩固第2章至第4章所学内容；（2） 掌握用户自定义类型的使用；（3） 掌握变量的分类及其使用；（4） 掌握各种运算符的使用；（5） 掌握各种控制语句的使用；（6） 掌握系统函数及其用户自定义函数的使用。2实验准备（1） 了解T-SQL支持的各种基本数据类型；（2） 了解自定义数据类型使用的一般步骤；（3） 了解T-SQL各种运算符、控制语句的功能及使用方法；（4） 了解系统函数的调用方法；（5） 了解用户自定义函数使用的一般步骤。3实验内容 自定义数据类型的使用。（1）对于实验2给出的数据库表结构，再自定义一数据类型ID_type,用于描述员工编号。在“查询分析器窗口”中输入如下程序并执行。 USE YGGLEXEC sp_addtypeID_type， char（6），not nullGO 注意:不能漏掉单引号。【思考与练习】 在 对象资源管理器中展开“数据库- PXSCJ -可编程性”，右击“类型”，选择“新建”选项，在“新建数据类型”窗口中使用界面方式创建一个用户自定义数据类型。（2）在YGGL数据库中创建Employee3表，表结构与Employee3类似,只是EmployeeID列使用的数据类型为用户自定义数据类型ID_type。USE YGGLGOIF EXISTS (SELECT name FROM sysobjects WHETE name=Employee3）/*首先在系统表中查看表是否存在，若存在，则删除该表*/CREATE TABLE Employees3（ EmployeesID ID_type , /*定义字段EmployeeID的类型为ID_type*/ Name char（10） NOT NULL, Education char（4）NOT NULL， Birthday date NOT NULL， Sex bit NOT NULL DEFAULT 1, Work Year tinyint NULL, Address varchar (40) NULL, PhoneNumber char (12)NULL, DepartmentID char (3) NOT NULL, PRIMATY KEY (EmployeeID)GO（2）变量的使用。对于实验2给出的数据表结构，创建一个名为female的用户变量，并在select语句中使用该局部变量查找表中所有女员工的编号、姓名。USE YGGLDECLARE female bitSETfemale=0/*变量赋值完毕，使用以下语句查询*/SELECT EmployeeID,Name FROM EmployeesWHERE Sex=female定义一个变量，用于获取号码为102201的员工的电话号码。DECLAREphone char(12)SETphone=(SELECT PhoneNumberFROM EmployeesWHERE EmployeeID=102201)SELECTphone执行完该语句后可以得到变量phone的值。【思考与练习】 定义一个变量，用于描述YGGL数据库的Salary表中000001号员工的实际收入，然后查询该变量。（3）运算符的使用。（1） 使用算数运算符“-”查询员工的实际收入。SELECT InCome-OutCome FROM Salary（2） 使用比较运算符“”查询Emloyees表中工作时间大于5年的员工信息。SEKECT* FROM Employees WHERE Work Year 5【思考与练习】 熟悉各种常用运算符的功能和用法，如LIKE、BETWEEN等。（4）流程控制语句。（1） 判断Employees表中是否存在编号为111006的员工，如果存在，则显示该员工信息；若不存在，则显示“查无此人”。IF EXISTS(SELECT Name FROM Employees WHERE EmployeeID=111006) SELECT*FROM Employees WHERE Employees WHERE EmployeeID=111006ELSE SELECT 查无此人【思考与练习】 判断姓名为王林的员工实际收入是否高于3000元，如果是，则显示其收入，否则显示“收入不高于3000”.（2） 假设变量X的初始值为0，每次加1，直至X变为5。DECLAREX INTSETX=1WHILEX5BEGIN SETX=X+1 PRINTX=+CONVERT(char（1），X)ENDGO【思考与练习】使用循环输出一个用“*“组成的三角形。（3） 使用case语句对Employees表按部门进行分类。USE YGGLGOSELECT EmployeeID,Name，Address，DepartmentID= CASE DepartmentID WHEN 1 THEN 财务部 WHEN 2 THEN 人力资源部WHEN 3 THEN 经理办公室WHEN 4 THEN 研发部WHEN 5 THEN 市场部 END FROM Employees【思考与练习】使用IF语句实现以上功能。（5）自定义函数的使用 (1) 定义一个函数实现如下功能：对于一个给定的DepartmentID值，查询该值在Departments表中是否存在，若存在则返回0，否则返回-1。create function check_ID(departmentid char(3)returns integer asbegindeclare num intif exists(select departmentID from Departmentswhere departmentid=departmentID)select num=0elseselect num=-1return numendgo写一段TSQL程序调用上述函数。当用Employees表插入一行记录时，首先调用函数CKECK_ID检索该记录的DpartmentID值在表Departments的DepartmentID字段中是否存在对应值，若存在，则将该记录插入Employees表。declare num int select num=dbo.check_ID(2)IF num=0insert Employeesvalues(990210,张英,本科,1982-03-24,0,4,南京市镇江路2号,8497534,2)go【思考与练习】自定义一个函数，计算一个数的阶乘。（6）系统内置函数的使用求一个数的绝对值。select ABS(-123)【思考与练习】a使用rand（）函数产生一个01的随机值；b使用square（）函数获得一个数的平方；c使用sqrt（）返回一个数的平方根；求财务部雇员的总人数；select COUNT(EmployeeID)as 财务部人数from Employeeswhere DepartmentID= (select DepartmentID from Departments where DepartmentName=财务部)【思考与练习】a求财务部收入最高的员工姓名；b查询员工收入的平均数；c聚合函数如何与GROUP BY函数一起使用？使用ASC函数返回字符表达式最左端字符的ASC值。SELECT ASC（abc）【思考与练习】a使用CHAR()函数将ASC码代表的字符组成字符串。b使用LEFT（）函数返回从字符串abcdef左边开始的3个字符。获得当前的日期和时