笔记部分

笔记一：触发器部分

我们以 INSERT 触发器的创建为例，讲解触发器的创建和使用。首先创建测试数据表：

1
2
3
4
5
6
7
--创建学生表
create table student(
    stu_id int identity(1,1) primary key,
    stu_name varchar(10),
    stu_gender char(2),
    stu_age int
)

为 student 表创建 INSERT 触发器：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
--创建insert触发器
create trigger trig_insert
on student
after insert
as
begin
    if object_id(N'student_sum',N'U') is null--判断student_sum表是否存在
        create table student_sum(stuCount int default(0));--创建存储学生人数的student_sum表
    declare @stuNumber int;
    select @stuNumber = count(*)from student;
    if not exists (select * from student_sum)--判断表中是否有记录
        insert into student_sum values(0);
    update student_sum set stuCount =@stuNumber; --把更新后总的学生数插入到student_sum表中
end
1
2
3
4
5
6
7
8
--测试触发器trig_insert-->功能是向student插入数据的同时级联插入到student_sum表中，更新stuCount
--因为是后触发器，所以先插入数据后，才触发触发器trig_insert;
insert into student(stu_name,stu_gender,stu_age)values('吕布','男',30);
select stuCount 学生总人数 from student_sum;
insert into student(stu_name,stu_gender,stu_age)values('貂蝉','女',30);
select stuCount 学生总人数 from student_sum;
insert into student(stu_name,stu_gender,stu_age)values('曹阿瞒','男',40);
select stuCount 学生总人数 from student_sum;

另外，因为定义学生总数表 student_sum ，是向 student 表中插入数据后，才计算的学生总数。所以，学生总数表应该禁止用户，向其中插入数据。

1
2
3
4
5
6
7
8
9
--创建insert_forbidden,禁止用户向student_sum表中插入数据
create trigger insert_forbidden
on student_sum
after insert
as
begin
    RAISERROR('禁止直接向该表中插入记录，操作被禁止',1,1)--raiserror 是用于抛出一个错误
rollback transaction
end

实验部分

向 score 表建立一个插入触发器。保证向 score 表中插入的学生信息的学号，必须在 student 表中存在

1
2
3
4
5
6
7
8
9
10
11
create trigger trigger_insert_score
on score
after insert
as
Begin
    if not exists (select * from student where sno in(select sno from inserted))
    Begin
        rollback transaction
        Begin transaction
    End
End
1
2
3
4
5
6
7
insert into score values('1001','2001','89.5')
go
insert into score values('1002','2001','95')
go
insert into score values('1011','2001','88')
go
select * from score

向 student 表插入删除触发器，实现 student 表和 score 表的级联删除；

1
2
3
4
5
6
7
create trigger trigger_delete_student
on student
for delete
as
Begin
delete score where sno in (select sno from deleted)
End
1
2
3
delete from student where sno='1001'
go
select * from student

向 score 表建立触发器，使 grade 列不能手工修改

1
2
3
4
5
6
7
8
create trigger trigger_protect_grade
on score
for update
as
Begin
if update(grade)
    raiserror('cannot modify the grade',16,1)
End
1
2
3
4
5
delete from score where sno='1001'
go
select * from score
go
select * from student

笔记二：存储过程部分

实现从表中查询所有学生基本信息的存储过程（存储过程名称一定要为 proc_student_info

1
2
3
4
5
create procedure proc_student_info
as
Begin
    select * from student
End
1
exec proc_student_info

创建一个带参数的存储过程，输出指定学号的学生信息；

1
2
3
4
5
6
create procedure proc_sno
@sno_input varchar(30)
as
Begin
    select * from student where sno = @sno_input
End
1
exec proc_sno '1001'

创建一个带参数的存储过程，根据指定参数增加学生信息，如果学生编号已经存在则不能增加(调用此存储过程时，会依次填充各个字段值，请注意 insert 时，参数顺序与表字段的顺序一致)；

1
2
3
4
5
6
7
8
9
10
11
12
13
14
create procedure proc_add
@sno varchar(100),
@sname varchar(100),
@sex varchar(100),
@birthday date,
@discipline varchar(100),
@school varchar(100)
as
Begin
    if exists(select * from student where sno = @sno)
        print 'Already have a primary key '+@sno
    else
        insert into student values(@sno,@sname,@sex,@birthday,@discipline,@school)
End
1
2
3
4
exec proc_add '1004','HMM','female','2019-6-2','English','national school'
go
exec proc_student_info
go

创建一个带参数的存储过程，删除指定学号的学生信息。若成功，则输出 successfully deleted ；若没有该学号，则输出 No such student

1
2
3
4
5
6
7
8
9
10
11
12
create procedure student_del
@sno varchar(100)
as
Begin
    if exists (select * from student where sno = @sno)
    Begin
        delete from student where sno = @sno
        print 'successfully deleted'
    End
    else
    print 'No such student'
End
1
2
3
4
go
exec student_del '1001'
go
exec proc_student_info

笔记三：测试部分编码

利用超市管理数据库的商品表，编程实现：如果商品表中啤酒类平均售价低于10，则将所有啤酒的售价增加10%，直到平均售价达到10为止

1
2
3
4
5
6
7
use supermarket
WHILE (SELECT AVG(SalePrice) FROM Goods) < 10
BEGIN 
	UPDATE Goods SET SalePrice = SalePrice*1.1
	IF (SELECT AVG(SalePrice) FROM Goods) >= 10
	BREAK
END

创建一个函数fun_avgallgoodsale,求超市管理数据库中所有商品的平均售价

1
2
3
4
5
6
7
8
9
10
CREATE FUNCTION fun_avgallgoodsale()
RETURNS decimal(18,2)
AS 
	BEGIN
		DECLARE @name varchar(100),@avg_price decimal(18,2)
		SELECT @name = (SELECT GoodsName) FROM Goods
		SELECT @avg_price = (SELECT AVG(SalePrice)) FROM Goods
		RETURN @name
		RETURN @avg_price
	END

创建一个多语句表值函数fun_avggoodsale,求超市管理数据库各类商品的平均售价

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
USE supermarket
GO
CREATE FUNCTION fun_avggoodsale() 
RETURNS @avg_salePrice TABLE
	(
		good_category varchar(100),
		good_saleprice decimal(18,2)
	)
AS 
BEGIN
	INSERT INTO @avg_salePrice
	SELECT CategoryNO,AVG(SalePrice)
	FROM Goods
	GROUP BY CategoryNO
	RETURN
END

创建一个存储过程proc_avgnumsale,显示指定商品类别的平均数量和平均售价

1
2
3
4
5
6
7
8
9
USE supermarket
GO
CREATE PROCEDURE proc_avgnumsale;1
	@category varchar(100)
AS
SELECT AVG(Number) 平均数量,AVG(SalePrice)
FROM Goods G JOIN Category CA ON G.CategoryNO = CA.CategoryNO
WHERE CategoryName = @category
GO

通过游标cur遍历商品表的数据,并将每个商品的售价增加10%

1
2
3
4
5
6
7
8
9
10
11
12
use supermarket
DECLARE cur CURSOR 
FOR SELECT SalePrice FROM Goods FOR UPDATE
DECLARE @saleprice decimal(18,2)
OPEN cur
FETCH NEXT FROM cur INTO @saleprice
WHILE @@fetch_status = 0
	BEGIN
		UPDATE Goods SET SalePrice = SalePrice * 1.1 WHERE CURRENT OF cur;
	END
CLOSE cur
DEALLOCATE cur

笔记四：学习通习题部分

定义一个用户标量函数,用以实现指定年月,当月有多少天

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
create function getDaysOfMonth(@year int,@month int) returns int 
as begin
	declare @days int
	if @year%400 =0 or (@year %4 =0 and @year %100 != 0)
		set @days = 29
	else
		set @days = 28
	set @days = 
	case 
		when @month = 2 then @days
		when @month  = 4 or @month  = 6 or @month  = 9 or @month  = 11 then 30
		else 31
		end
		return @days
	end

用流程控制语句编写程序,求1×2×3×…×100的积。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
create function getDaysOfMonth(@year int,@month int) returns int 
as begin
	declare @days int
	if @year%400 =0 or (@year %4 =0 and @year %100 != 0)
		set @days = 29
	else
		set @days = 28
	set @days = 
	case 
		when @month = 2 then @days
		when @month  = 4 or @month  = 6 or @month  = 9 or @month  = 11 then 30
		else 31
		end
		return @days
	end