实验9 使用T-SQL编写程序
- 格式：doc
- 大小：41.50 KB
- 文档页数：3
下载文档原格式
合集下载
用T-SQL指令创建数据库和表
（5）删除 MyDb 数据库中学生(sc)表在 grade 属性列上的 check 约束, 约束名为 ck_grade。
（6）删除第 1 题中添加的 Telehpone 属性列
（7）为 MyDb 数据库中学生(Student)表的 Ssex 列添加默认值约束，设置其默认值为’男’， 默认值约束的名字为 Stu_sex_default
操作： USE master /*改变当前数据库为要删除数据库以外的数据库，否则可能提示 MyDB 数据库正在使用无法删除*/ DROP DATABASE MyDB
四、实验结果（本实验源程序清单及运行结果或实验结论、实验设计图）
1.create database MyDb on (name=MyDb_data, filename='d:\sql\MyDb_data.mdf', size=3, maxsize=4, filegrowth=10% ) log on (name=MyDb_log, filename='d:\sql\MyDb_log.ldf', size=1, maxsize=2, filegrowth=10%) 2.(2) use MyDb go create table student (sno char(9) primary key, sname char(6) not null, ssex char(2) default('男'), sage int, sdept varchar(10) ) (3) create table course (cno varchar(3) primary key,/*裂解完整性约束*/ cname varchar(12), cpno varchar(3), ccredit int ) (4)create table sc (sno char(9) not null,
数据库原理实验报告-T-SQL编程
XX大学XX学院图1图22、用户存储过程的编辑修改首先创建存储过程select_students, 在此基础上，将该过程更改为检索计算机专业的学生信息，用修改过程命令重新定义该存储过程。
2.1 输入以下代码：alter procedure select_students with encryptionasselect*from Student whereSdept='CS'order by Sno2.2 通过EXEC语句执行存储过程输入语句：EXEC select_students输出结果如下：3、用户存储过程的删除3.1 通过DROP语句删除存储过程输入语句：DROP procedure select_students输出结果如下：二、编写触发器1、在SQL中展开Student_and_Course数据库，在其中选择对应的表，将表展开，在展开的选项中右击“触发器”，在弹出的快捷菜单中选择“新建触发器”命令，在如图3所示的触发器编辑区中给出对应的触发器代码。
2、创建一个名为TR_AGEde1的触发器，要求在插入和更新时检查Student表中Sage是否在15至60之中，如果不在提示“年龄不合法”。
输入语句：CREATE TRIGGER TR_AGEON StudentFOR INSERT,UPDATEASDECLARE@age INTSELECT@age=SageFROM StudentIF@age NOT BETWEEN 15 AND 60BEGINROLLBACK TRANSACTIONRAISERROR ('年龄不合法',16,10)END图33、执行以下更新数据库的SQL代码，将在显示框内显示有关的信息，表示触发了数据表Student中的触发器TR_AGE.输入语句：UPDATE StudentSET Sage=26WHERE Sname='李勇'执行结果如图5：图54、同样，执行以下命令也会触发触发器TR_AGE.输入语句：INSERT INTO Student VALUES('200215128','张博','男',17,'CS') 完成3、4两步的输出结果如图6：图65、查看所创建触发器的详细信息，输入以下代码：USE student_and_courseEXEC sp_helptrigger StudentEXEC sp_helptext TR_AGE执行结果如图7：图7。
第9章 T-SQL编程
9.1.3 运算符
4. 比较运算符 比较运算符(又称关系运算符)见表9-2，用于测试 两个表达式的值是否相同，其运算结果为逻辑值， 可以为三种之一：TRUE、FALSE 及 UNKNOWN（NULL数据参与运算时）。
9.1.3 运算符
【例9-5】使用比较运算符计算表达式的值。 DECLARE @Exp1 int, @Exp2 int SET @Exp1=30 SET @Exp2=50 IF @Exp1>@Exp2 SELECT @Exp1 AS 小数据
2. 赋值运算符 T-SQL中只有一个赋值运算符，即等号（=）。赋值 运算符使我们能够将数据值指派给特定的对象。另 外，还可以使用赋值运算符在列标题和为列定义值 的表达式之间建立关系。 【例9-3】创建一个@MyCounter变量，然后赋值运 算符将@MyCounter设置为表达式返回的值。 DECLARE @MyCounter int SET @MyCounter = 10
第9章 T-SQL编程
9.1 T-SQL编程基础 9.1.1 标识符 标识符是用来标识事物的符号，其作用类似于给 事物起的名称。标识符分为两类：常规标识符和 分隔标识符。
9.1.1 标识符
1. 常规标识符 常规标识符格式的规则如下： (1) 常规标识符必须以汉字、字母（包括从 a 到 z 和 从 A 到 Z 的拉丁字符以及其他语言的字母字符）、 下划线_、@或#开头，后续字符可以是：汉字、字 母、基本拉丁字符或其他国家/地区字符中的十进制 数字、下划线_、@、#。 (2) 常规标识符不能是SQL Server保留字，SQL Server保留字不区分大小写。 (3) 常规标识符最长不能超过128个字符。
如何使用T-SQL编程
T-SQL中的变量分为：
局部变量
局部变量必须以标记@作为前缀，如@num 局部变量的使用是先声明，再赋值 全局变量必须以标记@@作为前缀，如@@version 全局变量由系统定义和维护，只能读取，不能修改全局变量的值
全局变量
ห้องสมุดไป่ตู้
1.1 T-SQL变量概述
全局变量
变量名
@@ERROR @@IDENTITY
3.2 IF ELSE条件语句
IF (条件)
实现“陆逊”取款金额为15000，判断余额是否大于取款金额，如果小于15000则显示余额不足。
语句
DECLARE @BALANCE
FLOAT
ELSESELECT
@BALANCE = BALANCE FROM ALL_PURPOSE_CARD WHERE CARD_NO =
小结2
逻辑运算符ALL、ANY、EXISTS的区别是什么？ NOT EXISTS 与 NOT IN的区别是什么？
3. T-SQL中的流程控制
在没有流程控制语句的情况下，SQL语句只能顺序执行。 流程控制语句是结构化程序设计的保障，为编写复杂的SQL结构化 程序提供了支持。 在T-SQL中，流程控制主要指一些关键字的使用，如IF ELSE、 WHILE、CASE WHEN THEN END、RETURN、BREAK、 BEGIN END、GOTO。 流程控制语句支持嵌套，但一个语句不能跨多个批处理用户定义的 函数和存储过程。
'6225887706549011' 语句 IF @BALANCE > 15000 BEGIN INSERT INTO ALL_CARD_EXCHANGE(CARD_ID,EXGET,BALANCE,EXTIME,EXADDRESS) VALUES(1, 15000,@BALANCE-15000,'2011-12-25','ATM取款') UPDATE ALL_PURPOSE_CARD SET BALANCE = @BALANCE-15000 WHERE CARD_NO = '6225887706549011' END ELSE
第9章用T-SQL编程
第9章⽤T-SQL编程第9章⽤T-SQL编程本章学习⽬标Transact-SQL相对于ANSI SQL最⼤的不同在于它提供了丰富的编程结构。
灵活使⽤这些编程的控制结构，⽤户就可以实现任意复杂的应⽤规则，从⽽可以编出任意复杂的查询控制语句。
在SQL-Server 2000中，⽤户还可以使⽤T-SQL语句编写服务器端的程序，这些程序由批处理、注释、程序中使⽤的变量、改变批中语句的执⾏顺序的流控制语⾔、错误和消息的处理等成分组成。
本章主要对使⽤T-SQL语句编程⽅⾯的内容进⾏详细的讲解，在讲解的过程中采⽤循序渐进的⽅式，⾸先对多条语句的执⾏过程进⾏讲解，以激发读者的学习兴趣，接下来才对编程中具体涉及到的变量、语句、函数以及事务进⾏详细讲解。
通过对本章的学习，读者应熟悉在SQL Server中执⾏多条语句的⽅式，熟悉变量以及事务等的定义、应⽤或调⽤，并能掌握使⽤各种流控制语句进⾏编程的⽅法。
学习重点与难点执⾏多条T-SQL语句变量和流控制语句系统函数和⽤户⾃定义函数事务及SQL-Server的锁机制9．1 执⾏多条T-SQL语句当要完成的任务不能由单独的T-SQL语句来完成时，SQL Server提供了批、脚本、存储过程以及触发器等⼏种⽅式来组织多条T-SQL语句。
本节着重介绍批和脚本的内容，⽽关于存储过程和触发器的内容将在后⾯两章讨论。
9.1.1 批所谓批，是从客户机传递到服务器上的⼀组完整的数据和SQL指令。
⼀个批是由⼀条或多条T-SQL语句组成的语句集，这些语句⼀起提交给服务器并在服务器端作为⼀个整体来执⾏。
SQL Server将批中的语句作为⼀个整体编译为⼀个执⾏计划。
因为批中的语句是作为⼀个整体提交给服务器的，所以可以节省系统开销。
在查询分析器中，使⽤“GO”命令标志⼀个批的结束。
GO不是通⽤的T_SQL语句，它的作⽤只是通知查询分析器有多少语句包含在当前批中，查询分析器将两个GO之间的语句组成⼀个字符串交给服务器去执⾏。
用T-SQL指令创建数据库和表[方案]
![用T-SQL指令创建数据库和表[方案]](https://img.taocdn.com/s1/m/b1e636cf51e2524de518964bcf84b9d529ea2c5f.png)
实验报告
阅读并验证教材P87例8-例10，若实验不通过请分析原因（注意是否存在表级约束条件）。
5、初步了解在查询分析器中数据的插入、修改和删除方法
完成下列操作，初步了解插入、修改和删除数据的SQL语句格式（教材P115~P118）：（1）向S tudent表中插入两条记录
（2）修改Student表中的记录：将李勇从信息系（IS）转入计科系（CS）
（3）删除Student表中李勇的记录：李勇退学
（4）完善数据库
按教材P82图3.2所示内容向学生-课程数据库的3个表中添加数据。
6、验证参照的完整性约束中级联更新和级联删除
(1)级联删除
从学生表中删除学号为200215122的学生指令如下：
查看成绩表中的信息输入如下指令，看学号为200215122学生的成绩是否级联删除
(2)级联更新
把学号为200215121学生的学号修改为200215128，指令如下：
查看成绩表中的信息输入如下指令，看学号为200215121学生的学号是否级联更新
8、掌握用T-SQL语句删除数据库和表的方法
（1）删除表
格式：DROP TABLE <表名>
（2）删除数据库：
格式：DROP DA TABASE <数据库名>。
实验使用tsql语言创建和修改表答案
实验4 使用T-SQL语言创立和修改表一、实验目的1.掌握使用查询分析器利用T-SQL语句创立数据库表的方法。
2.掌握使用查询分析器利用T-SQL语句修改数据库表构造、删除数据库表的方法。
3.掌握如何使用查询分析器利用T-SQL语句创立和使用用户自定义数据类型。
二、实验内容新建人事管理系统数据库，包括三个表：员工根本信息表、部门信息表、员工工资信息表。
三个表的构造如表3-7、表3-8、表3-9。
完成以下各题。
表3-7 员工根本信息表表3-8部门信息表表3-9员工工资信息表1.使用T-SQL语句自定义一个数据类型TELEPHONE_COD，为字符型CHAR(15)，允许空值。
SQL代码如下：USE 人事管理系统EXEC SP_ADDTYPE TELEPHONE_CODE,'CHAR(15)','NULL'2.使用查询分析器利用T-SQL语句创立员工根本信息表。
SQL代码如下：USE 人事管理系统CREATE TABLE 员工根本信息表(员工编号INT NOT NULL CONSTRAINT PK_YGID PRIMARY KEY,员工姓名NV ARCHAR(50) NOT NULL,员工登录名NV ARCHAR(20) NOT NULL,员工登录密码BINARY(20) NULL,员工电子邮件NV ARCHAR(50) NOT NULL,员工所属部门编号INT NULL,员工根本工资INT NULL,员工职位名称NV ARCHAR(50) NULL,员工TELEPHONE_CODE NULL,员工报到日期DATETIME NOT NULL,员工自我介绍NV ARCHAR(200) NULL,员工剩余假期INT NULL,员工的级别INT NULL,员工照片IMAGE NULL)3.使用查询分析器利用T-SQL语句创立部门信息表。
SQL代码如下：USE 人事管理系统CREATE TABLE 部门信息表( 部门编号INT NOT NULL CONSTRAINT PK_BMID PRIMARY KEY,部门名称CHAR(10) NULL,部门描绘CHAR(50) NULL,部门经理编号INT NULL)4.使用查询分析器利用T-SQL语句创立员工工资信息表。
第9章 T-SQL编程
2018年10月27日星期 六
7. 一元运算符
一元运算符只对一个表达式执行操作，该表达式可以 是数值数据类型类别中的任何一种数据类型。具体为： +（正）：数值为正； -（负）：数值为负； ~（位非）： 返回数字的非。其中：+（正）和 -（负）运算符可以 用数值数据类型类别中任一数据类型的任意表达式。~ （位非）运算符只能用于整数数据类型类别中任一数 据类型的表达式。
2018年10月27日星期 六
9.1.3 运算符
运算符是一种符号，用来指定要在一个或多个 表达式中执行的操作。在Microsoft SQL Server 2009系统中，可以使用的运算符可以分为算术运 算符、逻辑运算符、赋值运算符、字符串连接运 算符、按位运算符、一元运算符及比较运算符等。
2018年10月27日星期 六
2018年10月27日星期 六
2. 分隔标识符
符合所有常规标识符格式规则的标识符可以使用分 隔标识符，也可以不使用分隔标识符。不符合常规标 识符格式规则的标识符必须使用分隔标识符。 分隔标识符括在方括号 [ ] 或双引号 “ ” 中。
在下列情况下，需要使用分隔标识符：
(1) 使用保留关键字作为对象名或对象名的一部分。
2018年10月27日星期 六
运行时错误（如算术溢出或违反约束）会产生以下两 种影响之一： (1) 大多数运行时错误将停止执行批处理中当前语句 和它之后的语句； (2) 某些运行时错误（如违反约束）仅停止执行当前 语句。而继续执行批处理中其它所有语句。 在遇到运行时错误之前执行的语句不受影响。唯一的 例外是如果批处理在事务中而且错误导致事务回滚，在 这种情况下，回滚运行时错误之前所进行的未提交的数 据修改。
2018年10月27日星期 六
7. 一元运算符
一元运算符只对一个表达式执行操作，该表达式可以 是数值数据类型类别中的任何一种数据类型。具体为： +（正）：数值为正； -（负）：数值为负； ~（位非）： 返回数字的非。其中：+（正）和 -（负）运算符可以 用数值数据类型类别中任一数据类型的任意表达式。~ （位非）运算符只能用于整数数据类型类别中任一数 据类型的表达式。
2018年10月27日星期 六
9.1.3 运算符
运算符是一种符号，用来指定要在一个或多个 表达式中执行的操作。在Microsoft SQL Server 2009系统中，可以使用的运算符可以分为算术运 算符、逻辑运算符、赋值运算符、字符串连接运 算符、按位运算符、一元运算符及比较运算符等。
2018年10月27日星期 六
2018年10月27日星期 六
2. 分隔标识符
符合所有常规标识符格式规则的标识符可以使用分 隔标识符，也可以不使用分隔标识符。不符合常规标 识符格式规则的标识符必须使用分隔标识符。 分隔标识符括在方括号 [ ] 或双引号 “ ” 中。
在下列情况下，需要使用分隔标识符：
(1) 使用保留关键字作为对象名或对象名的一部分。
2018年10月27日星期 六
运行时错误（如算术溢出或违反约束）会产生以下两 种影响之一： (1) 大多数运行时错误将停止执行批处理中当前语句 和它之后的语句； (2) 某些运行时错误（如违反约束）仅停止执行当前 语句。而继续执行批处理中其它所有语句。 在遇到运行时错误之前执行的语句不受影响。唯一的 例外是如果批处理在事务中而且错误导致事务回滚，在 这种情况下，回滚运行时错误之前所进行的未提交的数 据修改。
2018年10月27日星期 六
T-SQL编程
T-SQL编程
一．实验目的：
1．熟练掌握变量、常量的使用方法
2．熟练掌握常用函数
3．熟练掌握用户自定义函数
要求：
1．将“学生成绩管理系统”数据库附加到当前数据库中。
二．实验内容
1．定义局部变量@date，将当前系统日期放入该变量中。
然后输出该变量。
2．定义局部变量@avg，“成绩信息”表中的平均成绩放入该局部变量中。
然后输出该变量。
3．执行以下函数：
、 4．创建一个标量值函数fun_name ，要求根据输入的学号返回对应的学生的姓名.
5. 创建一个标量值函数fun_class_name ，要求根据输入的班级编号返回对应的班级名
6．创建表值函数func_student,要求根据输入的学生学号，返回所对应学生信息。
7．创建表值函数fun_savg,该表值函数无参数，返回每门课程的平均成绩。
8．创建表值函数fun_avg ，要求根据输入的课程编号，返回所对应课程的平均成绩。
参考答案：4．
5．
6．
7．
8．。
第9章 T-SQL 编程
money smallmoney Decimal、Numeric float、real Datetime smalldatetime char / varchar / text nchar / nvarchar / ntext
由0和1表示真、假。
(±922万亿) (±21万)，精确到万分之一。 ±1038-1，最大位数38位 (±1.79E+308) (±3.40E+38) 1753.1.1-9999.12.31,精确到3.33毫秒 1900.1.1-2079.12.31，精确到分钟 定/变长单字节字符,最长8000； 定/变长双字节字符，最长4000；
常量也称为字面值或标量值，是表示一个特定数据值的符号。 常量是指在程序运行过程中，其值不会改变的量。
类 型 整型常量 实型常量 字符串常量 双字节字符串 整型常量 日期型常量 货币型常量 二进制常量 说 明 没有小数点和指数E 60，25，-365 例 如
decimal或numeric带小数点的常数， 15.63、-200.25 float或real带指数E的常数 +123E-3、-12E5 单引号引起来 '学生'，'this is database' 前缀N必须是大写，单引号引起来 没有小数点和指数E 单引号(')引起来 精确数值型数据，前缀$ 前缀0x。 N'学生' 60，25，-365
2013年7月24日星期三
16
第9章 T-SQL 编程
定义：DECLARE 局部变量名 数据类型[,…n] 赋值：SET 局部变量名=表达式[,…n]
其中：局部变量名称必须以符号“@”开头。必须符合标识符命 名规则。数据类型是任何由系统提供的或用户定义的数据类型。变 量不能是 text、ntext 或 image 数据类型。
由0和1表示真、假。
(±922万亿) (±21万)，精确到万分之一。 ±1038-1，最大位数38位 (±1.79E+308) (±3.40E+38) 1753.1.1-9999.12.31,精确到3.33毫秒 1900.1.1-2079.12.31，精确到分钟 定/变长单字节字符,最长8000； 定/变长双字节字符，最长4000；
常量也称为字面值或标量值，是表示一个特定数据值的符号。 常量是指在程序运行过程中，其值不会改变的量。
类 型 整型常量 实型常量 字符串常量 双字节字符串 整型常量 日期型常量 货币型常量 二进制常量 说 明 没有小数点和指数E 60，25，-365 例 如
decimal或numeric带小数点的常数， 15.63、-200.25 float或real带指数E的常数 +123E-3、-12E5 单引号引起来 '学生'，'this is database' 前缀N必须是大写，单引号引起来 没有小数点和指数E 单引号(')引起来 精确数值型数据，前缀$ 前缀0x。 N'学生' 60，25，-365
2013年7月24日星期三
16
第9章 T-SQL 编程
定义：DECLARE 局部变量名 数据类型[,…n] 赋值：SET 局部变量名=表达式[,…n]
其中：局部变量名称必须以符号“@”开头。必须符合标识符命 名规则。数据类型是任何由系统提供的或用户定义的数据类型。变 量不能是 text、ntext 或 image 数据类型。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
实验9 使用T-SQL编写程序
【实验目的】
１）掌握常用函数的使用方法。
２）掌握流程控制语句的使用方法。
【实验环境】
Sql server 2005
【实验重点及难点】
1）启动SQL Server 2005查询编辑器。
2）应用转换函数。
3）应用聚合函数。
4）应用字符串函数。
5）应用IF〃〃〃ELSE语句。
6）应用WHILE语句。
【实验内容】
（1）应用转换函数
1）打开“SQL Server Manageement Studio”窗口。
2）单击“标准”工具栏上的“新建查询”按钮，打开“查询编辑器”窗口。
3）在窗口内直接输入以下语句，求Course表中课程号为“7”的课程名称的长度，并输入结果。
declare @课程名称长度int
select @课程名称长度=len(Cname)
from course
where Cno ='7'
print'课程名称长度为'+str(@课程名称长度);
4）单击“SQL编辑器”工具栏上的“分析”按钮，检查输入的T-SQL语句是否有语法错误。
如果有语法错误，则进行修改，直到没有语法错误为止。
5）确保无语法错误后，单击“SQL编辑器”工具栏上的“执行”按钮。
将执行结果记录下来。
（２）应用聚合函数
１）在“查询编辑器”窗口内输入以下语句，统计Student表中的学生人数，并输出结果。
declare @学生人数int
select @学生人数=count(*)
from Student
print'学生人数为'+str(@学生人数);
2 )单击“SQL编辑器”工具栏上的“执行”按钮。
将执行结果记录下来。
（3）应用字符串函数
1)在“查询编辑器”窗口内输入以下语句，将字符串“Welcome to SQL Server”转换为大写字母输出。
declare @temp char(20)
select @temp =upper('Welcome to SQL Server')
print @temp;
2)单击“SQL编辑器”工具栏上的“执行”按钮。
将执行结果记录下来。
（4）应用IF〃〃〃ELSE语句
1在“查询编辑器”窗口内输入以下语句，查询学号为“200215121”的学生的平均分是否超过了85分，若超过则输出“XX考出了高分”，否则输出“XX考的一般”。
declare @Sname varchar(10),@Sno char(8)
set @Sno ='200215121'
select @Sname = Sname
from Student
where Sno = @Sno
if(select avg(Grade)
from sc
where Sno = @Sno )> 85
print @Sname +'考出了高分'
else print @Sname +'考得一般';
2)单击“SQL编辑器”工具栏上的“执行”按钮。
将执行结果记录下来。
思考题：
1. 按照“性别、学号、姓名、年龄、院系”的顺序列出学生信息，其中性别按以下规定显示：性别为男显示为男生，性别为女显示为女生，其他显示为“条件不明”。
select Ssex =
case
when Ssex='男'then'男生'
when Ssex='女'then'女生'
else'条件不明'
end, Sno, Sname, Sage, Sdept
from student;
2.按照院系降序显示所有学生的“院系，学号、姓名、性别、年龄”等信息，其中院系按照以下规定显示：院系为CS显示为计算机系，院系为IS显示为信息系，院系为MA显示为数学系，院系为EN显示为外语系，院系为CM显示为中医系，院系为WM显示为西医系，其他显示为院系不明。
select Sdept =
case
when Sdept ='CS'then'计算机系'
when Sdept ='IS'then'信息系'
when Sdept ='MA'then'数学系'
when Sdept ='EN'then'外语系'
when Sdept ='CM'then'中医系'
when Sdept ='WM'then'西医系'
else'院系不明'
end, Sno, Sname, Ssex, Sage
from student;
order by Sdept DESC
3.显示所有院系（要求不能重复，不包括空值），并在结果集中增加一列字段“院系规模”，其中若该院系人数>=5则该字段值为“规模很大”，若该院系人数大于等于4小于5则该字段值为“规模一般”，若该院系人数大于等于2小于4则该字段值为“规模稍小”，否则显示“规模很小”。
select Sdept,'院系规模'=
case
when count(Sno)>=5 then'规模很大'
when count(Sno)>=4 then'规模一般'
when count(Sno)>=2 then'规模稍小'
else'规模很小'
end
from student
where Sdept is not NULL
group by Sdept;。