实验四 T-SQL编程.doc

实验四 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，用户描述员工编号。在“查询分析器”窗口中输入如下程序并执行。执行上面代码，但是出现这样的错误，不知道是不是之前就存在了。在YGGL数据库中创建表Employees3表，表结构与Employees表类似，知识EmployeeID列使用的数据类型为用户自定义数据类型ID_type。（2）变量的使用。对于实验2给出的数据库表结构，创建一个名为female的用户变量，并在SELECT语句中使用该局部变量查找表中所有女员工的编号、姓名。定义一个变量，用户获取员工号码为102201的员工的电话号码。执行完上面语句后可以得到变量phone的值，如上面截图中结果。【思考与练习】定义一个变量，用于描述YGGL数据库的Salary表中000001号员工的实际收入，然后查询该变量。首先declare定义变量RealInCome，然后利用SELECT语句查询EmployeeID=000001的员工的实际收入赋值给RealInCome，最后利用SELECT查询RealInCome变量的值。（3）运算符的使用。使用算数运算符“-”，查询员工的实际收入。 使用比较运算符“”，查询Employees表中工作时间大于5年的员工信息。执行上面代码，在结果栏中显示工作时间大于5年的员工信息。【思考与练习】熟悉各种常用运算符的功能和用法，如LIKE、BETWEEN等。（4）流程控制语句。判断Employees表中是否存在编号为111006的员工，若存在，则显示该员工的信息；若不存在，则显示“查无此人”。执行上面代码，在结果栏中显示是否存在员工编号为111006的员工。【思考与练习】判断姓名为“王林”的雇员的实际收入是否高于3000元，如果是，则显示其收入，否则显示“收入不高于3000”。假设变量X的初始值为0，每次增加1，直至X变为5.【思考与练习】使用循环输出一个用“*”组成的三角形。在这里将用到常用的字符长处理函数，replicate(,)和space()。Replicate的功能是以指定次数重复字符表达式。Space的功能是返回指定个数重复的空格组成的字符串。 使用CASE语句对Employees表按部门进行分类。【思考与练习】使用IF语句实现上面的功能。（使用if语句将Employees表按照部门进行分类。）我首先在“查询分析器”中用T-SQL语句创建了用户自定义函数useif。并且返回s（s是当DepartmentID为相应编号是对应的DepartmentName）。然后重新建一个“查询分析器”，在其中使用函数useif，参数就是DepartmentID。执行上面新建的用户自定义函数，然后在左侧对象资源管理器中的YGGL数据库的“可编程”“函数”“标量函数”里面可以可看到刚刚创建的函数。然后在新的“查询分析器”中调用上面创建的函数useif。虽然会出现红色波浪下划线，并且提示“找不到列 dbo 或用户定义的函数或聚合 dbo.xxx，或者名称不明确。”但是还是执行成功，并且输出结果。（5）自定义函数的使用。定义一个函数实现如下功能：对于给定的DepartmentID值，查询该值在Departments表中是否存在，若存在则返回0，否则返回-1。新建一个查询，在“查询分析器”窗口中输入如下程序并执行。 写一段T-SQL程序调用上述函数。当向Employees表中插入一行记录时，首先调用函数CHECK_ID检索记录的DepartmentID值在表Departments的DepartmentID字段中是否存在对应值，若存在，则该记录插入Employees表。执行上面T-SQL语句，在下方显示1行受影响，说明已经向Employees表中插入数据。【思考与练习】自定义一个函数，计算一个数的阶乘。首先新建一个查询，在“查询分析器”窗口中输入下面程序，并且执行，生成函数factorial(num int)。并将计算结果作为函数返回值。然后重新建一个查询，在“查询分析器”窗口中进行调用函数factorial，并输出计算结果。在结果栏中显示计算结果为“6”。（因为这里num=3作为函数factorial的参数进行计算的）（6）系统内置函数的使用。 求一个数的绝对值。【思考与练习】A、使用 RAND()函数产生一个01的随机数。第一种，不指定 RAND()函数内的种子值，使其随机产生。返回值为float类型。第二种，指定种子值。注：两者区别，若没有指定种子值，那么将随机分配种子值，每次执行结果不一样。对于指定了种子值，返回的结果始终相同。对于上面指定种子值5，则每次产生的结果都是相同的。（上面截图显示）B、使用SQUARE()函数获得一个数的平方。C、使用SQRT()函数返回一个数的平方根。求财务部雇员的总人数。【思考与练习】A、求财务部收入最高的雇员姓名。B、查询员工收入的平均数。 C、聚合函数如何与GROUP BY函数一起使用。举个例子，利用GROUP BY查询不同部门人数聚合函数ASCII函数返回字符表达式最左端字符的ASCII值。【思考与练习】A、使用CHAR()函数将ASCII码代表的字符组成字符串。因为此处char()函数是一个参数的，因此输出字符串做了这样的处理。B、使用LEFT()函数返回从字符串abcdef左边开始的3个字符。获得当前时间和日期。查询YGGL数据库中员工号为000001的员工的出生年份。【思考与练习】A、使用DAY()函数返回指定日期的天数。B、列举出其他的时间日期函数。DATEPART函数。DATEADD函数获取下一时间之前或者之后的时间日期。DATEDIFF函数，返回跨两个指定日期的日期和时间边界数。DATENAME函数，返回代表指定日期的指定日期部分的字符串。DATENAME函数，返回时间为当天的第几小时，第几分钟，第几秒。C、使用其他类型的系统内置函数。使用系统内置函数返回服务器端计算机的主机号和名称。使用系统内置函数返回数据库的编号和名称。4、实验总结 通过本次实验知道了SQL中的常用日期时间函数，并且也将这些函数做了总结，就像上面的截图中显示的那样。同样也