合肥工业大学数据库上机实验3（第3次实验）

  大三上学期的实验了，一直没来得及放到博客上面，博主所在学校的数据库实验都很简单。（不明白为什么截图上传之后为什么那么糊）

文章目录

• 实验9：触发器的创建与使用

• 实验10：存储过程的创建与使用

• 实验11：数据库的安全性

实验9：触发器的创建与使用

1. 实验目的

  本实验的目的是使学生进一步掌握SQL Server触发器的创建及使用方法，加深SQL触发器的理解。通过对数据的更新操作体会其触发器的作用。

2. 实验要求

  1) 掌握T-SQL创建触发器语句Create trigger的使用。

  2) 完成所规定的触发器定义要求。

  3) 通过数据查询检查触发器的使用效果。

  4) 注意操作结果的截图与保存，供撰写实验报告使用。

3. 实验内容

  1) 创建一个作用在Part表上的的触发器P_Ins，确保用户在插入或更新数据时所提供的WEIGHT值是大于0的，否则给出错误提示并回滚此操作。请测试该触发器。测试方法自定。

  2) 创建一个作用在Project表上的触发器J_Update，禁止同时修改项目的名称和所在城市，并进行相应的错误提示。请测试该触发器。测试方法自定。

  3) 为供应商表(Supp)创建DELETE触发器t_dele_Supp：删除一个供应商时，需将供应表（SPJ）删除相应的记录。

  4) 先在数据库中使用SELECT INTO 语句从SPJ、Project、Part表中创建一个新表P_P_QTY。表中的属性列包括：工程号、工程名、零件号、零件名及总数量，其中总数量的值是各工程使用各种零件的数量总和（不考虑零件是由哪个供应商提供的）。然后创建一个作用在SPJ表上的Insert型触发器，该触发器能完成两项任务：

  a) 当用户插入的JNO使用的PNO的值在J_P_QTY中不曾存在时禁止插入；

  b) 每当在SPJ表中插入一条合理记录时，使P_P_QTY表的QTY列的值保持同步更新。请测试该触发器。

4. 实验步骤

  方法1：在查询分析器中完成实验内容。

  1) 创建一个作用在Part表上的的触发器P_Ins的语句：

  示例：

  为学生表(student)创建INSERT触发器t_inst_stu：新增一名学生时，若其班级编号非空，则将班级表（class）中相应班级的人数（c_total）自动加1。

create trigger t_inst_stu on student
for insert
as
begin
if exists(select * from inserted where clsno is no tnull)
begin
update class  set c_total=c_total+1
where clsno=(select clsno from inserted);
end
end

  2) 创建一个作用在Project表上的触发器J_Update的语句：

  示例：

  创建一个触发器t_update,当工程项目表Project发生修改时,显示修改前后的记录。

Create taggert t_update on Project for update 
Begin 
select·from inserted 
select· from deleted 
end

  将Project表中项目代码为‘J1’的项目所在城市修改为‘上海’，查看触发器。

  方法2：利用SQL Server 企业管理器创建和执行触发器

  在SSMS中，先选择指定的服务器和SPJDB数据库，再选择Part表，然后右键单击 “触发器”，在弹出的快捷菜单中选择“新建触发器”选项，出现创建触发器对话框，如下图所示。

图 4.１ 示例操作

5. 实验结果

5.1. 所用命令

--(1)
create trigger p_ins
on part
for insert, update
as
if (select weight from inserted) <= 0
begin
    raiserror('the value cannot be inserted into table part since it is lower than', 16, 1)
    rollback
end
--(1)
create trigger p_ins
	on part for insert, update
	as
	declare @tmp_pno varchar(6), @tmp_pname varchar(16)
	declare @tmp_color varchar(8), @tmp_weight int
	select @tmp_pno = pno, @tmp_pname = pname, @tmp_color = color, @tmp_weight = weight from inserted
	if( @tmp_weight < 0 )
	begin
		raiserror('the value cannot be inserted into table part since it is lower than', 16, 1)
		rollback
	end
--(2)
create trigger J_Update
	on project for update
	as
	declare @tmp_jno varchar(6)
	declare @tmp_jname1 varchar(16), @tmp_city1 varchar(8)
	declare @tmp_jname2 varchar(16), @tmp_city2 varchar(8)
	select @tmp_jname1 = jname, @tmp_city1 = city from inserted
	select @tmp_jname2 = jname, @tmp_city2 = city from deleted
	if( @tmp_jname1 <> @tmp_jname2 and @tmp_city1 <> @tmp_city2 )
	begin
		raiserror( 'it is forbidden to change the project name and its located city simultaneously', 16, 1 )
		rollback transaction
	end

update Project set jname = '无理超人', city = '合肥' where id = 52;

--(3)
create trigger t_dele_Supp
	on Supp for delete
	as
	declare @tmp_sno1 varchar(6), @tmp_sname1 varchar(16), @tmp_status1 int, @tmp_city1 varchar(8)
	--declare @tmp_sno2 varchar(6), @tmp_sname2 varchar(16), @tmp_status2 int, @tmp_city2 varchar(8)
	--select @tmp_sno2 from spj where @tmp_sname2 = spj.sno
	select @tmp_sno1 = sno, @tmp_sname1 = sname, @tmp_status1 = status, @tmp_city1 = city from deleted
	--select @tmp_sno2 = sno, @tmp_sname2 = sname, @tmp_status2 = status, @tmp_city2 = city from deleted
	--if( @tmp_sno1 = @tmp_sno2 )
	begin
		delete from spj where sno = @tmp_sno1
		print('all the relevant data involed in the deleted data in SPJ has been removed')
	end

select spj.JNO 工程号, jname 工程名, spj.pno 零件号, pname 零件名, sum(qty) 总数量
	into j_p_qty
	from part, project, spj
	where part.pno = spj.pno and project.jno = spj.jno
	group by spj.jno, jname, spj.pno, pname
	order by 工程号;

--(4)
create trigger ins_TO_spj
on spj for insert
as
	declare @tmp_jno varchar(6), @tmp_pno varchar(6), @tmp_qty smallint
	select @tmp_jno = jno, @tmp_pno = pno from inserted
	if not exists (select * from j_p_qty where 工程号 = @tmp_jno and 零件号 = @tmp_pno)
		begin
			raiserror('the data is not allowed to inserted into the table spj since the PNO of tuple does not exist in the table j_o_qty ', 16, 1)
			rollback transaction
		end
	else
		begin
			select @tmp_qty = qty from inserted
			update j_p_qty set 总数量 = 总数量 + @tmp_qty where 工程号 = @tmp_jno and 零件号 = @tmp_pno
		end

insert into spj values('S2', 'P456', 'J3', 555);

insert into spj values('S7', 'P678', 'J10', 234);

insert into spj values('S7', 'P1', 'J10', 234);

insert into spj values('S7', 'P10', 'J55', 234);

5.2. 实验截图

图 5.1 在Part表上创建触发器触发器P_Ins

图 5.2 在表part插入合理测试数据（质量：1000）

图 5.3 在表part插入不合理测试数据（质量：0）

图 5.4 在表part插入不合理测试数据（质量：-1）

图 5.5 在Project表上创建触发器J_Update

图 5.6 在表project上尝试不合理更新

图 5.7 在表project上尝试不合理更新

图 5.8 在表supp上创建触发器t_dele_supp

图 5.9 查询表supp与表spj数据

图 5.10 删除表supp上sno为'S100'的数据项

图 5.11 再次查询表supp与表spj

图 5.12 创建新表j_p_qty

图 5.13 在表spj上创建触发器in_TO_spj

图 5.14 在表spj上尝试不合理插入1

图 5.15 在表spj上尝试不合理插入2

图 5.16 在表spj上尝试合理插入1

图 5.17 在表spj上尝试合理插入2

6. 实验总结

  触发器可以查询其他表，而且可以包含复杂的SQL语句。它们主要用于强制服从复杂的业务规则或要求。

  触发器也可用于强制引用完整性，以便在多个表中添加、更新或删除行时，保留在这些表之间所定义的关系。然而，强制引用完整性的最好方法是在相关表中定义主键和外键约束。如果使用数据库关系图，则可以在表之间创建关系以自动创建外键约束。

实验10：存储过程的创建与使用

1. 实验目的

  本实验的目的是使学生进一步掌握SQL Server存储过程的创建及使用方法，加深对SQL存储过程的理解。通过对存储过程的调用体会其作用。

2. 实验要求

  1) 掌握T-SQL创建触发器语句Create procedure的使用。

  2) 完成所规定的存储过程定义要求。

  3) 调用（EXEC）所创建的过程，检查过程的执行结果。

  4) 注意操作结果的截图与保存，供撰写实验报告使用。

3. 实验内容

  1) 创建一个不带参数的存储过程P_S1，选择所有在北京的供应商。

  2) 创建一个带参数的存储过程P_S2，该存储过程的作用是：当任意输入一个工程代号时，将返回供应该工程零件的供应商的名称和零件名称以及工程的名称。

  3) 创建一个存储过程p_s_info,根据输入的供应商代码，返回其供应的工程项目、供应的零件和数量。

  要求：输入参数为供应商代码。

  4) 使用SQL语句分别执行p_s1、p_s2和p_s_info，并查看显示结果。

4. 实验步骤

  方法1：在查询分析器中完成实验内容。

  1) 在编辑框中可以输入创建存储过程的T_SQL语句，单击“检查语法”，则可以检查语法是否正确；单击“保存”按钮，即可保存该存储过程。

  2) 在查询编辑器里执行存储过程。

  T_Sql语句为：

  Exec p_s1

  方法2：在SSMS中，选择指定的服务器和数据库，单击 “可编程性”，在弹出的快捷菜单中选择“新建存储过程”选项，出现创建存储过程对话框，如下图所示。

图 4.１ 创建存储过程示例

5. 实验结果

5.1. 所用命令

create procedure P_S1 as
begin
	select * from supp where city = '北京';
end

create procedure P_S2
@tmp_jnp varchar(10)
as
begin
	select jname, pname, sname from supp, part, project, spj where spj.sno = supp.sno and spj.pno = part.pno and spj.jno = project.jno and spj.jno = @tmp_jnp;
end

--供应商表 Supp,  零件表 Part, 项目表 Project, 供应情况表 SPJ
create procedure p_s_info
@tmp_sno char(20)
as
begin
	if not exists (select * from spj where sno = @tmp_sno)
	begin
		raiserror('The entered project number is not in the supply information table or was incorrectly entered.', 16, 1);
	end
	select jno as '工程项目', pno '供应零件', qty as '供应数量' from spj where sno = @tmp_sno;
end

5.2. 实验截图

图 5.1 创建存储过程p_s1

图 5.2 执行存储过程p_s1

图 5.3 创建存储过程p_s2

图 5.4 执行存储过程p_s2（参数'j1'）

图 5.5 执行存储过程p_s2（参数'j6'）

图 5.6 创建存储过程p_s_info

5.7 执行存储过程p_s_info（参数为'S98'）

图 5.8 执行存储过程p_s_info（参数为'S1'）

图 5.9 执行存储过程p_s_info（参数为'111'，不合理）

图 5.10 执行存储过程p_s_info（参数为'S1'）

图 5.11 执行存储过程p_s_info（参数为'S101'））

6. 实验总结

  调用一个行数不多的存储过程与直接调用SQL 语句的网络通信量可能不会有很大的差别，可是如果存储过程包含上百行SQL 语句，那么其性能绝对比一条一条的调用SQL 语句要高得多。

  由于存储过程对数据库的访问是通过存储过程来进行的，因此数据库开发人员可以在不改动存储过程接口的情况下对数据库进行任何改动，而这些改动不会对应用程序造成影响。

实验11：数据库的安全性

1. 实验目的

  1) 掌握 SQL Server中有关用户、权限的管理方法；

  2) 掌握SQL语言的控制功能；

  3) 加深对数据库安全性的理解。

2. 实验要求

  1) 在实验之前做好准备；

  2) 利用SSMS可视化设置数据库用户及操作权限；

  3) 利用Transact-SQL语句设置数据库用户及操作权限。

3. 实验内容

  1) 管理和设计SQL Server登录信息，实现服务器级的安全控制。

  2) 设计和实现数据库级的安全保护机制。

  3) 设计和实现数据库对象级的安全保护机制。

4. 实验步骤

  在查询分析器中完成实验内容。

5. 实验结果

5.1. 所用命令

  本部分命令均在指导书里，不另行介绍。

5.2. 实验截图

  本部分实验截图与实验指导书差别不大，不另行介绍。

6. 实验总结

  要想成功访问 SQL Server 数据库中的数据， 我们需要两个方面的授权：

  获得准许连接 SQL Server 服务器的权利；

  获得访问特定数据库中数据的权利（select, update, delete, create table）。

  具体要完成的内容有：在 SQL Server 服务器级别，创建登陆帐户；创建数据库用户；通过加入数据库角色，赋予数据库用户“dba”权限；特别注意需为用户分配一个关系模式。

本文地址：https://blog.csdn.net/u25th_engineer/article/details/107140897