数据库实验四

T-SQL程序设计

内容：

1.定义变量并且通过select语句给变量赋值. 查询学号为“201215121”的学生的姓名和年龄，将其分别赋予变量name和age

2.if-lese选择结构的使用. 查询学生信息，如果学生人数多于10人，则只显示前5名，否则显示所有学生信息

3.while循环结构的使用. 创建一个测试表，并使用循环结构快速插入20000条记录

4.编写带有通配符参数的存储过程，查询学生表和成绩表，返回指定姓名的学生姓名、课程名和考试成绩。要求：执行该存储过程时，如果未提供参数，则使用预设的默认值（以“张”打头的姓）

5. 创建触发器，当插入交易记录时，实现自动更新账户余额的功能

6.编写事务进行银行转账

代码

/*1. 定义变量并且通过select语句给变量赋值*/
declare @name varchar(10)
declare @age int
select @name=sname, @age=sage
from student2020205224
where sno='201215123'
select @name as 姓名, @age as 年龄
/*1-练习*/
declare @no int
DECLARE @grade int
SELECT @no=sno, @grade=grade
FROM sc2020205224
where cnp='1' and sno='201215121'
SELECT @no as 学号, @grade as 成绩
/*if-lese选择结构的使用*/
DECLARE @num int
SELECT @num=count(*)
from student2020205224
print '学生人数:'+convert(varchar(5),@num)
IF (@num>10)
  BEGIN
    print '前五名学生信息'
    SELECT TOP 5
        *
    FROM student2020205224
    ORDER BY sno DESC
END
ELSE
  BEGIN
    print '所有学生信息'
    SELECT *
    FROM student2020205224
END
/*练习*/
declare @num1 int
SELECT @num1=AVG(grade)
FROM sc2020205224
WHERE cnp='1'
IF(@num1>70)
BEGIN
    print '前五名学生信息'
    SELECT TOP 5
        *
    FROM sc2020205224
    WHERE cnp='1'
    ORDER BY sno DESC
END
ELSE
BEGIN
    print '所有学生信息'
    SELECT *
    FROM sc2020205224
END
/*3*/
create table TableIndex
(
    ID int identity(1,1),
    DataValue decimal(18,2)
)
/*向TestIndex数据库表中插入20000条数据*/
create table TableIndex
(
    ID int identity(1,1),
    DataValue decimal(18,2)
)/*先建立*/
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
/*练习*/
declare @n1 decimal(5, 0)/*约束中grade的数据类型*/
SELECT @n1 =min(grade)
from sc2020205224
WHERE cnp='1'
WHILE(1=1)
    BEGIN
    SELECT @n1 =min(grade)
    from sc2020205224
    WHERE cnp='1'
    if(@n1<60)
    UPDATE sc2020205224
    set grade =grade+2
    where cnp='1'and grade<60
	else
	break
END
/*4-创建存储过程*/
CREATE PROCEDURE sp2020205224
    @sname varchar(50)='张%'
AS
PRINT  @sname+'同学的考试成绩单如下：'
SELECT sname, cname, grade
FROM student2020205224, course2020205224, sc2020205224
WHERE student2020205224.sno=sc2020205224.sno and course2020205224.cno=sc2020205224.sno
    and sname LIKE  @sname
/*练习*/
CREATE PROCEDURE sp12020205224
    @cnp  INT='1'
AS
SELECT sname, student2020205224.sno, grade, cnp
from student2020205224, sc2020205224
WHERE student2020205224.sno=sc2020205224.sno and sc2020205224.grade>=60 and cnp = @cnp
/*5-创建触发器*/
/*练习1*/
/*建表*/
create table backup2020205224
(
    id char(3) primary key,
    balance int
)
create trigger back
on count2020205224
for delete
as
    INSERT INTO backup2020205224
    (id, balance)
select id, balance
from deleted
/*练习2*/
create trigger gz
on count2020205224
for update
as
DECLARE @af int,@bf int
SELECT @bf=balance
from deleted
SELECT @af=balance
from inserted
if abs(@af-@bf)>2000
BEGIN
    RAISERROR ('每笔交易不能超过2万元，交易失败',16,1)
    ROLLBACK TRANSACTION
END
/*6*/
开摆