数据库原理实验4指导书新
更新时间:2023-09-21 16:11:01 阅读量: 工程科技 文档下载
实验4 T-SQL程序设计
实验目的:
1. 掌握T-SQL的基本语法;
2. 会编写存储过程实现复杂功能; 3. 会编写触发器实现复杂的约束条件; 4. 掌握事务的定义方式。 实验内容:
1. 定义变量并且通过select语句给变量赋值.
查询学号为“200215121”的学生的姓名和年龄,将其分别赋予变量name和age。
declare @name varchar(10) declare @age int
select @name=sname,@age=sage from student where sno='200215121' select @name as 姓名,@age as 年龄
运行结果如下: 姓名 年龄
-------- -----------
李勇 31
练习:查询学号为“200215121”的学生选修“1”号课程的成绩,并赋值给变量grade。
2. if-lese选择结构的使用
查询学生信息,如果学生人数多于10人,则只显示前5名,否则显示所有学生信息。
DECLARE @num int
SELECT @num=count(*) from student print '学生人数:'+convert(varchar(5),@num) IF (@num>10) BEGIN
print '前五名学生信息'
SELECT TOP 5 * FROM student ORDER BY sno DESC END ELSE BEGIN
print '所有学生信息'
SELECT * FROM student END
练习:查询“1”号课程的平均成绩,如果平均成绩大于等于70,则显示前5名的成绩,否则显示后5名的成绩。 3. while循环结构的使用
创建一个测试表,并使用循环结构快速插入20000条记录。
create table TableIndex ( ID int identity(1,1), DataValue decimal(18,2))
/*---向TestIndex数据库表中插入20000条数据---*/ declare @r numeric(15,8) declare @n int set @n = 0 while(1=1) begin set @r = rand() insert into TableIndex (DataValue) values(@r) set @n = @n + 1 if(@n>20000) break
end
练习:检查“1”号课程是否有不及格(60分及格)的学生。如有,每人加2分,高于95分的学生不再加分,直至所有学生“1”号课程成绩均及格。
4. 编写带有通配符参数的存储过程,查询学生表和成绩表,返回指定姓名的学生姓名、课程名和考试成绩。要求:执行该存储过程时,如果未提供参数,则使用预设的默认值(以“张”打头的姓) /*---创建存储过程---*/
CREATE PROCEDURE sp_stuInfo @sname varchar(50)='张%' AS
PRINT @sname+'同学的考试成绩单如下:' SELECT sname,cname,grade FROM student,course,sc
WHERE student.sno=sc.sno and course.cno=sc.cno
and sname LIKE @sname
练习:创建存储过程,查看指定课程及格学生的成绩信息,并返回及格的人数。要求及格分数线作为参数由用户输入,默认为60。
5. 创建触发器,当插入交易记录时,实现自动更新账户余额的功能。建表语句如下:
create table count(
id char(3) primary key, balance int)
insert into count values('001', 1000) create table traninfo(
id char(3) foreign key references count(id), amount int)
/*---创建触发器---*/
create trigger autoupdate_balance on traninfo for insert as
update count set balance = balance +i.amount from inserted i where count.id=i.id 练习1:删除交易信息时,要求自动备份被删除的数据到表backupTable中 。 练习2:跟踪用户的交易,交易金额超过20000元,则取消交易,并给出错误提示。
6. 编写事务进行银行转账。 --创建农行帐户表bank CREATE TABLE bank(
customerName CHAR(10), --顾客姓名 currentMoney int --当前余额 )
/*---添加约束:根据银行规定,帐户余额不能少于1元,除非销户----*/ ALTER TABLE bank
ADD CONSTRAINT CK_currentMoney CHECK(currentMoney>=1)
/*--插入测试数据:张三开户,开户金额为800 ;李四开户,开户金额1 ---*/ INSERT INTO bank(customerName,currentMoney) VALUES('张三',1000) INSERT INTO bank(customerName,currentMoney) VALUES('李四',1)
/*---创建事务---*/
/*--开始事务(指定事务从此处开始,后续的T-SQL语句都是一个整体--*/ BEGIN TRANSACTION
/*--定义变量,用于累计事务执行过程中的错误--*/ DECLARE @errorSum INT
SET @errorSum=0 --初始化为0,即无错误
/*--转帐:张三的帐户少1000元,李四的帐户多1000元*/ UPDATE bank SET currentMoney=currentMoney-1000 WHERE customerName='张三'
SET @errorSum=@errorSum+@@error --累计是否有错误 UPDATE bank SET currentMoney=currentMoney+1000 WHERE customerName='李四'
SET @errorSum=@errorSum+@@error --累计是否有错误 /*--根据是否有错误,确定事务是提交还是撤销---*/ IF @errorSum<>0 --如果有错误 BEGIN
print '交易失败,回滚事务' ROLLBACK TRANSACTION END ELSE BEGIN
print '交易成功,提交事务,写入硬盘,永久的保存' COMMIT TRANSACTION END
练习:使用第5题中的count表和traninfo表,编写事务实现存取款过程。 要求首先插入交易记录,然后更新账户余额。 提示:
1. Transact-SQL局部变量和全局变量
Transact-SQL中可以使用两种变量,一种是局部变量(Local Variable)另外一种是全局变量(Global Variable)。
局部变量是用户可自定义的变量,它的作用范围仅在程序内部。在程序中通常用来储存从表中查询到的数据,或当作程序执行过程中暂存变量使用。局部变量必须以“@”开头,而且必须先用DECLARE命令说明后才可使用。其说明形式如下: DECLARE @变量名 变量类型 [@变量名 变量类型…]
其中变量类型可以是SQL Server 支持的所有数据类型,也可以是用户自定义的数据类型。 在Transact-SQL中不能像在一般的程序语言中一样使用“变量=变量值”来给变量赋值。必须使用SELECT或SET命令来设定变量的值,其语法如下: SELECT@局部变量=变量值 SET @局部变量=变量值
例:声明一个长度为10 个字符的变量“id”并赋值 declare @id char(10) select @id=?10010001?
注意:可以在Select命令查询数据时,在Select命令中直接将列值赋给变量。
全局变量是SQL Server系统内部使用的变量,其作用范围并不局限于某一程序,而是任何程序均可随时调用全局变量通常存储一些SQL Server的配置设定值和效能统计数据。用户可在程序中用全局变量来测试系统的设定值或Transact-SQL命令执行后的状态值。
注意:全局变量不是由用户的程序定义的,它们是在服务器级定应义的。只能使用预先说明及定义的变局变量。引用全局变量时,必须以“@@”开头。局部变量的名称不能与全局变量的名称相同、否则会在应用中出错。
2. 逻辑控制语句的语法结构 (1)SQL中的IF-ELSE语句
IF (条件) BEGIN 语句1 语句2 … … END ELSE BEGIN 语句1 语句2 … … END
(2)SQL中的WHILE语句
WHILE (条件)
BEGIN 语句1 语句2 …… BREAK END
3. 定义存储过程的语法 CREATE PROC[EDURE] 存储过程名
@参数1 数据类型 = 默认值 OUTPUT, ? ? ,
@参数n 数据类型 = 默认值 OUTPUT AS
SQL语句
4. 创建触发器的语法:
CREATE TRIGGER trigger_name ON table_name
FOR [DELETE, INSERT, UPDATE] AS
T-SQL语句
触发器是在对表进行插入、更新或删除操作时自动执行的存储过程,通常用于强制业务规则。它是一种高级约束,可以定义比用CHECK 约束更为复杂的约束,可执行复杂的SQL语句(if/while/case),也可引用其它表中的列。
触发器触发时,系统自动在内存中创建deleted表或inserted表。这两个表只读,不允许修改;触发器执行完成后,自动删除。
inserted表,临时保存了插入或更新后的记录行,可以从inserted表中检查插入的数据是否满足业务需求。
deleted 表,临时保存了删除或更新前的记录行,可以从deleted表中检查被删除的数据是否满足业务需求。
正在阅读:
数据库原理实验4指导书新09-21
北京化工大学研究生新生入学考试总题库04-16
中国真皮鞋底行业市场前瞻与未来投资战略分析报告2022-2022年04-16
midas钢管混凝土拱桥计算04-26
某某公司费用报销制度09-11
县财政局“十三五”规划进展情况工作总结及“十四五”发展思路08-03
初中班级第一周情况小结06-06
一位员工的竞争上岗演讲稿08-08
V-V接线的电压互感器二次电压计算02-29
- 5120309265+夏子瑜+电机学实验+三相变压器极性与联接组标号 docx
- 破产管理人工作履职报告
- 基础会计教案(东北财经大学出版社刘永泽主编)
- 幼儿综合素质考试复习资料
- 试论客人投诉的处理和饭店形象的树立
- 人教版小学语文二年级上册快乐阅读练习题
- 自己整理SPSS复习资料
- 《深刻领会习主席关于党和法高度统一关系的重要论述》下83.75分
- qq唯美网名大全ら゛ 浅安时光浅安时光的情侣网名
- 塔吊安装方案(qtz5510) - 图文
- 管理学2
- 教育最新K122019版高考地理一轮复习第一部分自然地理第二章宇宙中的地球第二
- 2013华南理工大学 操作系统课后作业一
- 焊工教案
- 陕西科技大学化工原理课程设计
- STM32L1系列内部EEPROM存储数据
- 2015—2016学年二年级上语文期末测试卷(含解析)人教新课标版
- 城区烟草局打假现状和难点
- 2018年教师招聘小学语文模拟试题及答案一
- 时代光华《中国当前转让定价法规解读》课后答案