SQL实验5-6

实验五 T-SQL编程
一、目的与要求
1.综合．巩固前几个实验所学的内容；
2.掌握用户自定义类型的使用；
3.掌握变量的分类及其使用；
4.掌握各种运算符的使用；
5.掌握各种控制语句的使用；
6.掌握系统函数及用户自定义函数的使用。

二、实验准备
1.了解T-SQL支持的各种基本数据类型；
2.了解自定义数据类型使用的一般步骤；
3.了解T-SQL各种运算符．控制语句的功能及使用方法；
4.了解系统函数的调用方法；
5.了解用户自定义函数使用的一般步骤。

三、实验内容
●实验指导
1.自定义数据类型的使用
1)使用界面创建一个用户定义的数据类型，命名为“DID学号”，用于描述部门编号；
2)使用界面创建NEWDepartments表,表结构与Departments表类似，命名规则相同，用
新数据类型定义新表中部门编号的类型；
3)使用T-SQL命令，自定义一个数据类型，命名为“EID学号”，用于描述员工编号；
4)使用T-SQL命令创建NEWEmployees表,表结构与Employees表类似，命名规则相同，
用新数据类型定义新表中员工编号的类型，用新数据类型定义新表中部门编号的类型。

●指导
使用系统存储过程sp_addtype定义用户定义数据类型，定义字符型数据类型mytype 的语句如下：
sp_addtype ‘mytype’ , ‘varchar(3)’ , ‘not null’
2.变量的使用
●指导：
①局部变量的定义
用DECLARE语句声明局部变量，所有局部变量在声明后均初始化为NULL
语法格式：DECLARE @a char(3),@b int
②局部变量的赋值
当声明局部变量后，可用SET或SELECT语句给其赋值。

语法格式：SET @a＝’SQL’ SELECT @b＝100
注：SET一次只能赋一个值，SELECT可赋多个值，用逗号隔开
●练习：
通过上面的学习，请完成以下题目：
1）声明两个整型的局部变量：@i1和@i2，对@i1赋初值10；@i2的值为@i1乘以5，再显示@i2的结果值。

2）定义一字符型变量@TMP，赋值‘1’，并编写语句：查询部门号等于@TMP的所有部门信息。

3）定义一个变量，用于获取姓名为“钟敏”的员工的实际收入，并输出此变量。

4）定义一游标变量，将所有2号部门的员工信息显示出来。

3.流程控制语句
1）编写语句判断Departments表中是否存在“人事部”，如果存在则显示该部门信息，如果不存在则显示“此部门不存在！”。

2）编写语句判断姓名为“叶凡”的员工实际收入是否高于2500，如果是则显示其实际收入信息，如果不是则显示“实际收入不高于2500！”。

3）用WHILE语句实现计算5000减1、减2、减3、……、一直减到50的结果，并显示最终结果。

4）声明变量@i为整型，@s为字符型，长度为6，为@i赋初值85，分情况判断：当@i在90到100范围内时，@s＝‘优’
当@i在80到89范围内时，@s＝‘良’
当@i在70到79范围内时，@s＝‘中’
当@i在60到69范围内时，@s＝‘及格’
其他：@s＝‘不及格’
4.系统内置函数的使用
1）用函数计算字符串“I am a teacher.”的长度，并写出用“student”替换“teacher”的函数
2）用函数求“You are a student”字符串从11开始，长度为7的子串
3）用函数分别计算“You are a student”和“我们是学生”字符串中字符的个数
4）用函数分别得到字符串“I am a teacher and you are students”中左边14个和右边16个字符组成的字符串
5）将日期：2004/10/28转换成长度为15的字符串
6）将数字1234.567转换为整数部分4位，小数部分1位的定点小数类型
7）分别用函数得到系统当前日期时间中的年、月、日
8）用系统函数查看一下你的机器的名字
9）查询Employees表中全部不同的姓氏
10）查询Employees表中各种长度的名字人数
11）定义一适当类型的变量@TMP1，@TMP2，@TMP3。

将@TMP1赋值’大型数据库SQL SERVER’，将@TMP3赋值为‘SHI YAN WU’
A．将@TMP1左边第三个字符选择出来，赋值给变量@TMP2。

显示@TMP2。

B．将@TMP1从第二个位置取出三个字符，赋值给变量@TMP2，显示@TMP2。

C．在@TMP1中，将字符串‘ER’用‘AA’替换，并赋值给变量@TMP2，显示@TMP2。

D．将@TMP3中每个字符及对应的ASCII码通过循环语句一个一个的显示出来。

12）统计财务部雇员的平均收入，要求保留到小数点2位
5.自定义函数的使用
1）定义一函数CHECK+学号，实现如下功能：对于一输入的DepartmentID之值，查询该值在Departments表中是否存在，若存在返回0，否则返回-1；
2）编写一段程序调用上述函数。

当向Employees表插入一行记录时，首先调用该函数
检查DepartmentID是否在Departments表中存在，若存在则插入此记录，若不存在则显示“部门不存在！”。

3）创建函数FAC+学号，实现计算一个输入数的阶乘，并输入结果，调用此函数实现计算输入数的阶乘并输出。

四、思考题
1.理解自定义类型与函数的作用与优点；
2.掌握使用T-SQL基本编程方法与技巧。

实验六索引、存储过程和触发器的使用
一、目的与要求
1.掌握索引的使用方法；
2.掌握存储过程的使用方法；
3.掌握触发器的使用方法。

二、实验准备
1.了解索引的作用与分类；
2.掌握索引的创建方法；
3.理解数据完整性的概念及分类；
4.了解各种数据完整性的实现方法；
5.了解存储过程的使用方法；
6.了解触发器的使用方法；
7.了解inserted逻辑表和deleted逻辑表的使用。

三、实验内容
实验6.1 索引、默认值和约束
实验指导
1.建立索引
1）对Employees表中的EmployeesID列建立唯一、非聚合索引。

2）对Salary表中收入升序、支出降序建立复合索引。

2.创建约束对象
1）使用Alter Table命令，在Employees表的性别字段上创建名为“D1+学号后三位”的默认值约束，默认值为1。

2）使用Alter Table命令，删除名为“D1+学号后三位”的默认值约束。

3）创建名为“D2+学号后三位”的默认值对象，并绑定到Employees表的性别字段。

4）删除名为“D2+学号后三位”的默认值对象。

5）使用Create Table命令创建NEWSalary表，结构与Salary表相同，但NEWSalary 表不允许OutCome列大于InCome列。

6）使用Alter Table命令，在Salary表中添加一个名为“C1+学号后三位”的CHECK 约束，约束InCome列不得超过5000元。

7）界面创建名为“C2+学号后三位”的规则对象，规则为取值1-9，并绑定到Departments 表的部门号字段。

8）使用界面查看Departments表是否有主键约束，Employees表是否有外键约束，如果有均删除。

9）使用Alter Table命令，在Departments表的部门号字段上，创建名为“C3+学号后三位”的主键约束。

10）使用Alter Table命令，在Employees表的部门号字段上，创建名为“C4+学号后三位”的外键约束。

实验6.2 存储过程
●实验指导
1.创建存储过程
1)创建添加部门表记录的存储过程“班级简称+学号+P1”，利用三个参数添加记录；
2)创建存储过程“班级简称+学号+P2”，比较指定姓名的两个雇员的年龄，若前者比后
者大就输出0，否则输出1；
3)创建存储过程“班级简称+学号+P3”，要求当指定雇员的年龄大于50岁时将其转到经
理办公室。

2.调用3中的三个存储过程
实验6.3 触发器
●实验指导
创建触发器
1)创建触发器“班级简称+学号+T1”,实现向Salary表插入或修改一记录时，通过触发
器检查记录的EID值在Employees表中是否存在，若不存在，则取消插入或修改操作；
2)创建触发器“班级简称+学号+T2”,当修改Employees表的EID字段值时，将该字段
在Salary表中的对应值也做相应修改；
3）创建触发器“班级简称+学号+T3”,实现删除Employees表中的记录时删除该记录EID字段值在Salary表中对应的记录。

4）创建触发器“班级简称+学号+T4”,当向Employees表中插入记录时，先检查DID 列上的值在Departments表中是否存在，如果存在则执行插入操作，如果不存在则不执行插入操作，并显示提示信息“部门不存在！”。

四、思考题
1.触发器的作用及使用到的系统库与系统变量有哪些；
2.带参数与不带参数的存储过程在调用时的异同点。