《2022年SQL实用教程实验五T-SQL编程 .pdf》由会员分享，可在线阅读，更多相关《2022年SQL实用教程实验五T-SQL编程 .pdf（31页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、1 南昌大学实验报告学生姓名：廖美君学号： 8002213113 专业班级：实验类型： 验证 综合 设计 创新实验日期：2015-5-9 一、实验项目名称T-SQL 编程二、实验目的与要求1、进一步巩固前面所学知识2、掌握用户自定义类型的使用3、掌握变量的分类及使用4、掌握各种运算符的使用5、掌握各种控制语句的使用6、掌握系统函数及用户自定义函数的使用三、实验基本原理1、了解 T-SQL 支持的各种基本数据类型2、了解自定义数据使用的一般步骤3、了解 T-SQL 各种运算符、控制语句的功能及使用方法4、了解系统函数的调用方法5、了解用户自定义函数使用的一般步骤四、主要仪器设备及耗材名师资料总结
2、 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 1 页，共 31 页 - - - - - - - - - 2 计算机， MS SQL Server 2005 ，OFFICE 五、实验步骤1、自定义数据类型的使用（1）定义一数据类型ID_type，用于描述员工编号SQL 语句：USE 廖美君EXEC sp_addtypeID_type, nchar(6),not null GO 实验结果：图 5-1 定义一数据类型ID_type 思考与练习：a）使用界面方式创建一个用户自定义类型名师资料总结 -
3、- -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 2 页，共 31 页 - - - - - - - - - 3 图 5-2 使用界面方式创建用户自定义类型过程（一）图 5-3 使用界面方式创建用户自定义类型过程（二）名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 3 页，共 31 页 - - - - - - - - - 4 图 5-4 使用界面方式创建用户自定义类型成功（2）在廖美君数据库中创
4、建表Employees3 要求：表结构与 Employees类似，只是 EmployeesID 列使用的数据类型为用户自定义类型 ID_type。SQL 语句：USE 廖美君GO IF EXISTS(SELECT NameFROM sysobjects WHEREName=EMPLOYEES3) DROPTABLE Employees3 CREATE TABLE Employees3 ( EmployeeID ID_type, Namenchar(10)NOT NULL, Education nchar(4)NOT NULL, Birthday datetimeNOT NULL, Sex bi
5、tNOT NULL DEFAULT 1, Workyear tinyintNULL, Address nvarchar(40)NULL, 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 4 页，共 31 页 - - - - - - - - - 5 PhoneNumber nchar(12)NULL, DepartmentID nchar(3)NOT NULL, PRIMARYKEY (EmployeeID) ) GO 实验结果：图 5-5 创建表 Employees3 2、变量的使用
6、（1）创建一个用户变量要求：对于实验 2给出的数据库表结构，创建一个名为Female 的用户变量，并在SELECT语句中使用该局部变量查找表中所有女性的编号、姓名。SQL语句：USE 廖美君DECLARE female Bit SET female=0 SELECT EmployeeID,Name 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 5 页，共 31 页 - - - - - - - - - 6 FROM Employees WHERE Sex=female 实验结果：图
7、5-6 创建用户变量并查询女员工编号与姓名（2）定义一个变量要求：用于获取号码为102201的员工的电话号码SQL 语句：USE 廖美君DECLARE phone nchar(12) SET phone=(SELECT PhoneNumber FROM Employees WHERE EmployeeID=102201) SELECT phone 实验结果：名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 6 页，共 31 页 - - - - - - - - - 7 图 5-7 定义变
8、量并查询指定员工电话号码思考与练习：a）定义一个变量， 用于描述廖美君数据库的Salary表中 000001号员工的实际收入，然后查询该变量SQL 语句：USE廖美君DECLARE Realincome Money SET RealInCome =(SELECT InCome-OutCome FROM Salary WHERE EmployeeID = 000001 ) SELECT RealInCome 实验结果：名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 7 页，共 31 页
9、 - - - - - - - - - 8 图 5-8 定义变量查询指定员工实际收入3、运算符的使用（1）使用运算符 “ -” 查询员工实际收入SQL语句：USE 廖美君SELECT InCome-OutCome FROM Salary 实验结果：名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 8 页，共 31 页 - - - - - - - - - 9 图 5-9 使用运算符 “ -” 查询员工实际收入（2）使用运算符 “ ” 查询 Employees表中工作时间大于5 年的员工信息
10、SQL 语句：USE廖美君SELECT * FROMEmployees WHEREWorkYear5 实验结果：图 5-10 使用运算符查询员工信息4、流程控制语句（1）判断 Employees表中是否存在编号为111006的员工要求：如果存在则显示该员工信息，否则显示“ 查无此人 ”SQL 语句：USE廖美君IFEXISTS ( SELECTNameFROMEmployees WHERE名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 9 页，共 31 页 - - - - - - -
11、 - - 10 EmployeeID=111006) SELECT * FROMEmployees WHEREEmployeeID=111006 ELSE SELECT查无此人 实验结果：图 5-11 指定员工编号查询员工信息思考与练习：a）判断姓名为 “ 王林” 的员工实际收入是否高于3000 元如果是则显示其收入，否则显示“ 收入不高于 3000”SQL 语句：USE廖美君IF(SELECT InCome-OutCome FROM Employees JOIN Salary ON Employees.EmployeeID =Salary .EmployeeID WHERE Name = 王
12、林 ) 3000 SELECT InCome-OutCome 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 10 页，共 31 页 - - - - - - - - - 11 FROM Employees JOIN Salary ON Employees.EmployeeID =Salary .EmployeeID WHERE Name = 王林 ELSE SELECT 收入不高于 3000 实验结果：图 5-12 查询员工收入情况（2）假设变量 X 的初始值为 0，每次加 1，直至
13、 X 变为 5 SQL 语句：USE廖美君DECLARE X INT SET X=1 WHILE X5 BEGIN SET X=X+1 PRINTX=+CONVERT (nchar(1),x) END GO 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 11 页，共 31 页 - - - - - - - - - 12 实验结果：图 5-13 使用循环语句累加变量思考与练习：a）使用循环输出一个 “ *” 组成的三角形实心三角形 SQL 语句：USE廖美君DECLARE M int
14、DECLARE J int SET J=30 SET M=1 WHILE MJ BEGIN PRINT(Space (J-M)/2)+Replicate(*,M) SET M=M+2 END 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 12 页，共 31 页 - - - - - - - - - 13 实验结果：图 5-14 使用循环输出一个“ *” 组成的实心三角形空心三角形 SQL 语句：USE廖美君DECLARE M INT,J INT SET M=12 SET J=1 PR
15、INT space (M)+* WHILE JM BEGIN PRINT space (M-J)+*+space (2*J-1)+* SET J=J+1 END PRINT Replicate(* ,M+1) 实验结果：名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 13 页，共 31 页 - - - - - - - - - 14 图 5-15 使用循环输出一个“ *” 组成的空心三角形（3）使用 CASE 语句对 Employees表按部门进行分类SQL 语句：USE廖美君GO S
16、ELECT EmployeeID,Name,Address,DepartmentID= CASE DepartmentID WHEN 1 THEN财务部 WHEN 2 THEN人力资源部 WHEN 3 THEN经理办公室 WHEN 4 THEN研发部 WHEN 5 THEN市场部 END FROM Employees 实验结果：名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 14 页，共 31 页 - - - - - - - - - 15 图 5-16 使用 CASE 语句对表按部门
17、进行分类思考与练习：a）使用 IF 语句实现以上功能SQL 语句：CREATE FUNCTION liaomeijun(ID INT) RETURNS nchar(15) AS BEGIN DECLARE DepartmentName nchar(15) IF(ID=1) SET DepartmentName =财务部 IF(ID=2) SET DepartmentName =人力资源部 IF(ID=3) SET DepartmentName =经理办公室 IF(ID=4) SET DepartmentName =研发部 IF(ID=5) 名师资料总结 - - -精品资料欢迎下载 - - -
18、- - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 15 页，共 31 页 - - - - - - - - - 16 SET DepartmentName =市场部 RETURN DepartmentName END GO USE 廖美君GO SELECT Name,Address ,dbo.liaomeijun(DepartmentID ) FROM Employees GO 实验结果：图 5-17 使用 IF 语句对 Employees 表按部门进行分类5、自定义函数的使用（1）定义一个函数实现如下功能：对于一个给定的Departmen
19、tID 值，查询该值在 Departments表中是否存在，若存在则返回0，不存在返回 -1 USE 廖美君CREATE FUNCTION CHECK_ID (departmentid 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 16 页，共 31 页 - - - - - - - - - 17 char(3)RETURNS integer ASBEGINDECLARE num intIF EXISTS (SELECT departmentID FROM departments W
20、HEREdepartmentid =departmentID)SELECT num=0 ELSESELECT num=-1 RETURN num ENDGO 实验结果：图 5-18 定义函数的功能（2）写一段 T-SQL 程序调用上述函数SQL 语句：USE 廖美君GO 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 17 页，共 31 页 - - - - - - - - - 18 DECLARE num intSELECT num=dbo.CHECK_ID (2)IF num=0
21、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) BEGIN 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 18 页，共 31 页 - - -
22、 - - - - - - 19 SET result = result * M; SET M = M +1; END Print result; 实验结果：图 5-20 计算一个数的阶乘6、系统内置函数的使用（1）求一个数的绝对值SQL 语句：USE 廖美君SELECT ABS (-123) 实验结果：名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 19 页，共 31 页 - - - - - - - - - 20 图 5-21 求一个数的绝对值思考与练习：a）使用 RAND() 函数
23、产生一个 01的随机值SQL 语句：USE廖美君SELECT RAND(1) 实验结果：名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 20 页，共 31 页 - - - - - - - - - 21 图 5-22 使用 RAND （）函数产生一个01 的随机值b）使用 SQUARE()函数获得一个数的平方SQL 语句：USE廖美君SELECT SQUARE(113)实验结果：图 5-23 使用 SQUARE() 函数获得一个数的平方c）使用 SQRT 函数返回一个数的平方根SQL
24、语句：USE廖美君SELECT SQRT(169)实验结果：名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 21 页，共 31 页 - - - - - - - - - 22 图 5-24 使用 SQRT 函数返回一个数的平方根（2）求财务部雇员的总人数SQL 语句：USE 廖美君SELECT COUNT(EmployeeID)as 财务部人数FROM Employees WHERE DepartmentID=(SELECT DepartmentID FROM Departments
25、WHERE DepartmentName =财务部 ) 实验结果：名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 22 页，共 31 页 - - - - - - - - - 23 图 5-25 求财务部雇员的总人数思考与练习：a）求财务部收入最高的员工姓名SQL 语句：USE廖美君SELECT NameFROM Employees JOIN Salary ON Employees .EmployeeID = Salary .EmployeeID WHERE InCome = (SEL
26、ECT MAX (InCome) FROM Employees JOIN Salary ON Employees .EmployeeID= Salary.EmployeeID WHERE DepartmentID = (SELECT DepartmentID FROM Departments WHERE DepartmentName = 财务部 )实验结果：名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 23 页，共 31 页 - - - - - - - - - 24 图 5-26求
27、财务部收入最高的员工姓名b）查询员工收入的平均数SQL 语句：USE廖美君SELECT AVG(InCome) FROM Salary 实验结果：图 5-27 查询员工收入的平均数名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 24 页，共 31 页 - - - - - - - - - 25 c）聚合函数如何与GROUP BY 一起使用SQL 语句：SELECT DepartmentID,MAX (InCome) FROM Employees JOIN Salary ON Emplo
28、yees .EmployeeID = Salary .EmployeeID GROUPBY DepartmentID 实验结果：图 5-28 聚合函数与GROUP BY 的使用（3）使用 ASCII 函数返还字符表达式最左端字符的ASCII 值SQL 语句：USE 廖美君SELECT ASCII (abc) 实验结果：名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 25 页，共 31 页 - - - - - - - - - 26 图 5-29 使用 ASCII 函数返还字符表达式的A
29、SCII 值思考与练习：a）使用 CHAR() 函数将 ASCII 码代表的字符组成字符串SQL 语句：USE 廖美君SELECT CHAR(ASCII(abc) 实验结果：名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 26 页，共 31 页 - - - - - - - - - 27 图 5-30 CHAR() 函数的使用b）使用 LEFT()函数返回从字符串 ,abcdef? 左边开始的 3 个字符SQL 语句：USE 廖美君SELECT LEFT(abcdef,3) 实验结果：
30、图 5-31 LEFT() 函数的使用（4）获得当前的日期和时间SQL 语句：USE 廖美君SELECT getdate () 实验结果：名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 27 页，共 31 页 - - - - - - - - - 28 图 5-32 获得当前的日期和时间（5）查询廖美君数据库中员工号为000001的员工出生的年份SQL 语句：USE 廖美君SELECT YEAR(Birthday)FROM Employees WHERE EmployeeID=0000
31、01; 实验结果：名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 28 页，共 31 页 - - - - - - - - - 29 图 5-33 查询指定员工的出生年份思考与练习：a）使用 DAY 函数返回指定日期时间的天数SQL 语句：USE 廖美君SELECT DAY(2015-5-5)实验结果：名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 29 页，共 31 页 -
32、 - - - - - - - - 30 5-34 使用 DAY 函数返回指定日期时间的天数b）列举出其他的时间日期函数SQL 语句：USE 廖美君SELECT DATENAME (YYYY ,2015-5-10)SELECT DATEDIFF(QQ,2015-5-10,2016-5-10)SELECT DATEDIFF(MM ,2015-5-10,2015-10-10)SELECT DATEADD (WW,4,2015-5-10)SELECT DATEADD (DD,9,2015-5-10) 实验结果：图 5-35 列举出其他的时间日期函数c）使用其他类型的系统内置函数SQL 语句：USE 廖
33、美君SELECT CONVERT (VARCHAR (10),8002213113) 实验结果：名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 30 页，共 31 页 - - - - - - - - - 31 图 5-36CONVERT() 函数的使用六、参考资料刘启芬、顾韵华编著 .SQL Server 实用教程（第 4 版）M. 北京：电子工业出版社， 2014 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 31 页，共 31 页 - - - - - - - - -