SQL实用教程(第四版)实验五T-SQL编程

1、 南昌大学实验报告学生姓名： 廖美君 学 号： 8002213113 专业班级： 实验类型： 验证 综合 设计 创新 实验日期： 2015-5-9 1、 实验项目名称T-SQL编程2、 实验目的与要求1、进一步巩固前面所学知识2、掌握用户自定义类型的使用3、掌握变量的分类及使用4、掌握各种运算符的使用5、掌握各种控制语句的使用6、掌握系统函数及用户自定义函数的使用3、 实验基本原理1、 了解T-SQL支持的各种基本数据类型2、 了解自定义数据使用的一般步骤3、 了解T-SQL各种运算符、控制语句的功能及使用方法4、 了解系统函数的调用方法5、 了解用户自定义函数使用的一般步骤4、 主要仪器设备

2、及耗材计算机，MS SQL Server 2005，OFFICE 5、 实验步骤1、 自定义数据类型的使用 （1）定义一数据类型ID_type，用于描述员工编号SQL语句：USE 廖美君EXEC sp_addtype ID_type, nchar(6),not nullGO实验结果：图5-1定义一数据类型ID_type思考与练习：a）使用界面方式创建一个用户自定义类型图5-2使用界面方式创建用户自定义类型过程（一）图5-3使用界面方式创建用户自定义类型过程（二）图5-4使用界面方式创建用户自定义类型成功（2） 在廖美君数据库中创建表Employees3要求：表结构与Employees类似，只是

3、EmployeesID列使用的数据类型为用户自定义类型ID_type。SQL语句：USE 廖美君GO IF EXISTS(SELECT Name FROM sysobjects WHERE Name=EMPLOYEES3)DROP TABLE Employees3CREATE TABLE Employees3(EmployeeID ID_type,Name nchar(10) NOT NULL,Education nchar(4) NOT NULL,Birthday datetime NOT NULL,Sex bit NOT NULL DEFAULT 1,Workyear tinyint NU

4、LL,Address nvarchar(40) NULL,PhoneNumber nchar(12) NULL,DepartmentID nchar(3) NOT NULL,PRIMARY KEY (EmployeeID)GO实验结果：图5-5创建表Employees32、变量的使用（1）创建一个用户变量要求：对于实验2给出的数据库表结构，创建一个名为Female的用户变量，并在SELECT语句中使用该局部变量查找表中所有女性的编号、姓名。SQL语句：USE 廖美君DECLARE female BitSET female=0SELECT EmployeeID,NameFROM Employee

5、sWHERE Sex=female实验结果：图5-6创建用户变量并查询女员工编号与姓名（2） 定义一个变量要求：用于获取号码为102201的员工的电话号码SQL语句：USE 廖美君DECLARE phone nchar(12)SET phone=(SELECT PhoneNumber FROM Employees WHERE EmployeeID=102201)SELECT phone实验结果：图5-7定义变量并查询指定员工电话号码思考与练习：a）定义一个变量，用于描述廖美君数据库的Salary表中000001号员工的实际收入，然后查询该变量SQL语句：USE 廖美君DECLARE Reali

6、ncome MoneySET RealInCome = (SELECT InCome-OutCome FROM SalaryWHERE EmployeeID = 000001)SELECT RealInCome实验结果：图5-8定义变量查询指定员工实际收入3、 运算符的使用（1）使用运算符“-”查询员工实际收入SQL语句：USE 廖美君SELECT InCome-OutComeFROM Salary实验结果：图5-9使用运算符“-”查询员工实际收入（2）使用运算符“”查询Employees表中工作时间大于5年的员工信息SQL语句：USE 廖美君SELECT *FROM EmployeesWHE

7、RE WorkYear5实验结果：图5-10使用运算符查询员工信息4、流程控制语句（1） 判断Employees表中是否存在编号为111006的员工要求：如果存在则显示该员工信息，否则显示“查无此人”SQL语句：USE 廖美君 IF EXISTS ( SELECT Name FROM Employees WHERE EmployeeID=111006 ) SELECT * FROM Employees WHERE EmployeeID=111006ELSE SELECT 查无此人实验结果：图5-11指定员工编号查询员工信息思考与练习：a）判断姓名为“王林”的员工实际收入是否高于3000元如果是

8、则显示其收入，否则显示“收入不高于3000”SQL语句：USE 廖美君IF(SELECT InCome-OutCome FROM Employees JOIN Salary ON Employees.EmployeeID = Salary.EmployeeIDWHERE Name = 王林) 3000SELECT InCome-OutCome FROM Employees JOIN Salary ON Employees.EmployeeID = Salary.EmployeeIDWHERE Name = 王林ELSESELECT 收入不高于3000实验结果：图5-12查询员工收入情况（2）

9、假设变量X的初始值为0，每次加1，直至X变为5SQL语句：USE 廖美君DECLARE X INTSET X=1WHILE X5 BEGIN SET X=X+1 PRINTX=+CONVERT (nchar(1),x) ENDGO实验结果：图5-13使用循环语句累加变量思考与练习：a）使用循环输出一个“*”组成的三角形实心三角形SQL语句：USE 廖美君DECLARE M intDECLARE J intSET J=30SET M=1WHILE MJBEGIN PRINT(Space(J-M)/2)+Replicate(*,M)SET M=M+2END实验结果：图5-14使用循环输出一个“*”

10、组成的实心三角形空心三角形SQL语句：USE 廖美君DECLARE M INT,J INTSET M=12SET J=1PRINT space(M)+*WHILE JMBEGINPRINT space(M-J)+*+space(2*J-1)+*SET J=J+1ENDPRINT Replicate(* ,M+1)实验结果：图5-15使用循环输出一个“*”组成的空心三角形（3） 使用CASE语句对Employees表按部门进行分类SQL语句：USE 廖美君GO SELECT EmployeeID,Name,Address,DepartmentID=CASE DepartmentIDWHEN 1

11、THEN 财务部WHEN 2 THEN 人力资源部WHEN 3 THEN 经理办公室WHEN 4 THEN 研发部WHEN 5 THEN 市场部ENDFROM Employees实验结果：图5-16使用CASE语句对表按部门进行分类思考与练习：a）使用IF语句实现以上功能SQL语句：CREATE FUNCTION liaomeijun(ID INT)RETURNS nchar(15) ASBEGINDECLARE DepartmentName nchar(15)IF(ID=1)SET DepartmentName=财务部IF(ID=2)SET DepartmentName=人力资源部IF(ID

12、=3)SET DepartmentName=经理办公室IF(ID=4)SET DepartmentName=研发部IF(ID=5)SET DepartmentName=市场部RETURN DepartmentNameENDGOUSE 廖美君GOSELECT Name ,Address ,dbo.liaomeijun(DepartmentID )FROM Employees GO实验结果：图5-17使用IF语句对Employees表按部门进行分类5、 自定义函数的使用（1） 定义一个函数实现如下功能：对于一个给定的DepartmentID值，查询该值在Departments表中是否存在，若存在则

13、返回0，不存在返回-1USE 廖美君CREATE FUNCTIONCHECK_ID (departmentidchar(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实验结果：图5-18定义函数的功能（2）写一段T-SQL程序调用上述函数SQL语句：USE 廖美君GO DECLARE num in

14、t SELECT num=dbo.CHECK_ID(2) IF num=0 INSERT Employees VALUES (800113,廖美君,高中,1994-12-19,0,4,南京东路号,213113,2) GO实验结果：图5-19使用函数插入信息思考与练习：a） 计算一个数的阶乘SQL语句：USE 廖美君DECLARE M INT;DECLARE result INT;SET M = 1;SET result = 1; WHILE(M9) BEGINSET result = result * M;SET M = M +1; ENDPrint result;实验结果：图5-20计算一个

15、数的阶乘6、系统内置函数的使用（1） 求一个数的绝对值SQL语句：USE 廖美君SELECT ABS (-123)实验结果：图5-21求一个数的绝对值思考与练习：a） 使用RAND()函数产生一个01的随机值SQL语句：USE 廖美君SELECT RAND(1)实验结果：图5-22使用RAND（）函数产生一个01的随机值b）使用SQUARE()函数获得一个数的平方SQL语句：USE 廖美君SELECT SQUARE(113)实验结果：图5-23使用SQUARE()函数获得一个数的平方c）使用SQRT函数返回一个数的平方根SQL语句：USE 廖美君SELECT SQRT(169)实验结果：图5-

16、24使用SQRT函数返回一个数的平方根（2） 求财务部雇员的总人数SQL语句：USE 廖美君SELECT COUNT(EmployeeID)as 财务部人数FROM Employees WHERE DepartmentID= (SELECT DepartmentID FROM Departments WHERE DepartmentName=财务部)实验结果：图5-25求财务部雇员的总人数思考与练习：a）求财务部收入最高的员工姓名SQL语句：USE 廖美君SELECT Name FROM Employees JOIN SalaryON Employees.EmployeeID = Salary

17、.EmployeeIDWHERE InCome =(SELECT MAX(InCome) FROM Employees JOIN SalaryON Employees.EmployeeID= Salary.EmployeeIDWHERE DepartmentID =(SELECT DepartmentID FROM DepartmentsWHERE DepartmentName = 财务部)实验结果：图5-26求财务部收入最高的员工姓名b）查询员工收入的平均数SQL语句：USE 廖美君SELECT AVG(InCome) FROM Salary实验结果：图5-27查询员工收入的平均数c）聚合函

18、数如何与GROUP BY一起使用SQL语句：SELECT DepartmentID,MAX(InCome) FROM Employees JOIN SalaryON Employees.EmployeeID = Salary.EmployeeIDGROUP BY DepartmentID实验结果：图5-28聚合函数与GROUP BY的使用（3） 使用ASCII 函数返还字符表达式最左端字符的ASCII值SQL语句：USE 廖美君SELECT ASCII (abc)实验结果：图5-29使用ASCII 函数返还字符表达式的ASCII值思考与练习：a）使用CHAR()函数将ASCII码代表的字符组成字符串SQL语句：USE 廖美君SELECT CHAR(ASCII(abc)实验结果：图5-30 CHAR()函数的使用b）使用LEFT()函数返回从字符串abcdef左边开始的3个字符SQL语句：USE 廖美君SELECT LEFT(abcdef,3)实验结果：图5-31 LEFT()函数的使用（4） 获得当前的日期和时间 SQL语句：USE 廖