mysql实验总结（杂记2：动态表名，触发器实现约束-＞游标退出之not found导致的矛盾-＞count返回0还是null）

最新推荐文章于 2023-04-27 11:25:21 发布

不依法度

最新推荐文章于 2023-04-27 11:25:21 发布

阅读量1.1k

点赞数

分类专栏：数据库文章标签： mysql

本文链接：https://blog.csdn.net/weixin_50816938/article/details/124220742

版权

数据库专栏收录该内容

7 篇文章 0 订阅

订阅专栏

动态表名

我想实现的效果：我现在要创建表，但是我不确定我创建的表的名字，我需要根据我输入的参数sno（学号）创建表，表名中包含sno这个参数。
如何实现：

mysql的设计就是不支持将表名作为参数，也就是不支持动态表名。所以我用到了存储过程，将参数传入，然后将参数插入建表的sql语句中（sql注入）
（也就是sql语句的预处理）预编译语句就是将此类 SQL 语句中的值用占位符替代，可以视为将 SQL 语句模板化或者说参数化，一般称这类语句叫PreparedStatements。

sql注入

参考博客1，我感觉这样写应该是sql注入

我的代码

CREATE PROCEDURE p7(IN input_sno CHAR(4))
BEGIN
-- 通过concat将创建表的语句和sno参数连接起来，prepare处理语句（不说这叫预处理因为我感觉下面这个代码的写法不算预处理）（创建语法树），执行语句，我这段代码应该算sql注入
  SET @create_tb_sql=CONCAT
	('CREATE TEMPORARY TABLE IF NOT EXISTS temp_tb_',input_sno,'(
		selected_cnt INT,
		avg_grade DOUBLE,
		fail_cnt INT);');
	prepare create_ttb from @create_tb_sql;
	EXECUTE create_ttb;
-- 到这动态表名的临时表就创建好了
-- 后面的都是一些插入语句，可以不可看的
	
	SELECT count(*) INTO @selected_cnt FROM sc WHERE sno=input_sno;
	SELECT AVG(grade) INTO @avg_grade FROM sc WHERE sno=input_sno;
	SELECT count(*) INTO @fail_cnt FROM sc WHERE sno=input_sno AND grade<60;
	
	SET @insert_tb_sql=CONCAT('INSERT INTO temp_tb_',input_sno,' VALUE(@selected_cnt,@avg_grade,@fail_cnt);');-- concat千万注意空格
	PREPARE insert_ttb FROM @insert_tb_sql;
	EXECUTE insert_ttb;
END;
-- 测试
SET @input_sno='S001';
CALL p7(@input_sno);

SET @select_tb_sql=CONCAT('SELECT selected_cnt 选课数,avg_grade 平均成绩,fail_cnt 不及格选课数 FROM temp_tb_',@input_sno);
PREPARE select_ttb FROM @select_tb_sql;
EXECUTE select_ttb;

sql预处理

这一篇说了预处理的原理和执行顺序

语法：

# 定义预处理语句
PREPARE stmt_name（Prepare 指令后面便是 SQL 语句模板的名称） FROM preparable_stmt(一段预处理语句）;
# 执行预处理语句
EXECUTE stmt_name [USING @var_name [, @var_name] ...];
# 删除(释放)定义
{DEALLOCATE | DROP} PREPARE stmt_name;

预编译语句的优势在于归纳为：一次编译、多次运行，省去了解析优化等过程；此外预编译语句能防止 SQL 注入。

使用样例
在这里插入图片描述

触发器实现约束

非空约束

实验的这一题要求我用触发器实现非空约束

首先你要知道：触发器里面sql执行失败的话，是撤销了当前这个与触发器关联的insert/delete/update语句了

这边给出两种写法

写法1：
解释一下：

SIGNAL：使用SIGNAL语句在存储的程序(例如存储过程，存储函数，触发器或事件)中向调用者返回错误或警告条件。 SIGNAL语句提供了对返回值(如SQLSTATE)的信息的控制。
signal的使用：

    SIGNAL condition_value
    [SET signal_information_item
    [, signal_information_item] ...]

详细的可以参考一下官方文档，链接在这

SQLSTATE和MESSAGE_TEXT都是系统变量。
SQLERROR返回的是SQLSTATE值。SQLSTATE值是包含五个字符的字符串。五个字符包含数值或者大写字母，代表各种错误或者警告条件的代码。成功的标志是由00000表示的。
MESSAGE_TEXT是终止该过程后返回的包含错误信息的文本
SIGNAL 是“返回”错误的方法。SIGNAL 向处理程序、应用程序的外部部分或客户端提供错误信息

先看一下这个是我网上找的方法：

CREATE TRIGGER sno_not_null
BEFORE INSERT ON sc FOR EACH ROW
BEGIN
	IF(ISNULL(NEW.sno)) THEN
  SIGNAL SQLSTATE '12345' SET MESSAGE_TEXT = '插入记录失败：sno不为空！';
	END IF;
END;

我查了一下SQLSTATE是没有’12345‘这个值的，所有我觉得这个方法其实是方法2：触发器sql语句出现异常，执行中断。(没有得到证实，我去问问老师，后面补充）

真实的方法1：
所以我们应该改成，认为返回’45000‘

CREATE TRIGGER sno_not_null
BEFORE INSERT ON sc FOR EACH ROW
BEGIN
	IF(ISNULL(NEW.sno)) THEN
  SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '插入记录失败：sno不为空！';
	END IF;
END;

INSERT INTO sc(cno,grade) VALUES('C001',97);-- 测试

插入数据不满足约束条件的话：
（显示的是我们自定义的message_text) 在这里插入图片描述
方法2：
因为这句SELECT * INTO mesg FROM stu;有问题，所以触发器sql语句出现异常，执行中断。
这个是自动返回’45000‘，message_text也是由mysql自定义的。

CREATE TRIGGER sno_not_null
BEFORE INSERT ON sc FOR EACH ROW
BEGIN
	DECLARE mesg VARCHAR(100);
	if(New.sno IS NOT NULL) THEN SET mesg='ok';
	ELSE 
		SELECT * INTO mesg FROM stu;	
	END IF;
END;
INSERT INTO sc(cno,grade) VALUES('C001',97);-- 测试

插入数据不满足约束条件的话：
（这里就不会显示我自定义的message_text，是mysql提供的）
在这里插入图片描述

选课数不超过五门

上面那种理解了自然会写其他约束了，所以就放个代码就好。

CREATE TRIGGER sno_limit5
BEFORE INSERT ON sc FOR EACH ROW
BEGIN
	DECLARE selected_cnt INT;
	SELECT count(*) INTO selected_cnt FROM sc GROUP BY sno HAVING sno=NEW.sno;
	IF(selected_cnt=5) THEN
  SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '选课失败！每个学生每个学期最多选修 5 门课.！';
	END IF;
END;

group by的返回值

和group by联合使用查找为空返回null，不和group by联合使用查找为空返回0.
在这里插入图片描述
第二种情况就是我做这次实验遇到的，完整代码看下一个标题
sql语句

临时表

初始临时表是空的，我查询SNP=’S001‘的数据，没有，返回NULL。

mysql游标not found退出和查询结果not found的矛盾

这是题目
在这里插入图片描述
其实本来可以手动计算每个学生获得的总学分，但是我想尝试一下用sql计算，结果翻了个大车，改来改去还是有问题，下面这段代码是完成了我想要的效果，但是本身有问题。

说一下我实现计算每个学生获得的总学分的思路：
我创建了一个临时表temp_total，定义了一个游标接收sc表的学生记录结果集。遍历每一条学生记录，将每一条学生记录的学号，课程号，成绩抽取出来赋值给变量，如果成绩大于等于60，根据课程号查找该课程的学分，接着根据学号去临时表中查找看是否已经记录这个人，如果没有这个人向临时表中插入（学号，课程学分），如果已经有这个人，就在这个人原有信息的总学分sum上修改，总学分sum=之前学分sum+当前课程学分temp_credit。

翻车历程

翻车一：
先说一下mysql游标的退出，它好像没有自动检测结果集空了的效果，所以我得定义一个exit handler for not found，这是我的第一次翻车，我们在检验所建临时表中是否有本条学生记录的时候：初始表为空，第一次查询肯定没有记录，返回null(not found），然后游标就关闭了…然后我就只有一条记录…

翻车二：
后来我不定义handler了，临时表中的数据都没问题，但是游标遍历结果集退不出去，陷入WHILE的死循环了，所以close cursor名这句包括后面的都没法执行。

最后我展示了一下我计算的每个人的总学分，手动将计算后的数据填进stu了…亏大发了（也不算，还是有点收获的，强颜欢笑emohahaha

最后实现效果但有问题的sql

CREATE PROCEDURE add_totalcredit()
BEGIN
  DECLARE temp_sno CHAR(4);
  DECLARE temp_grade DOUBLE;
  DECLARE temp_cno CHAR(4);
  DECLARE temp_credit INT;
	DECLARE has_sno INT;

	
	DECLARE total INT;
	DECLARE add_sum CURSOR FOR SELECT DISTINCT sno,cno,grade FROM sc;
-- 	DECLARE CONTINUE HANDLER FOR not found close add_sum;		
	
	CREATE TEMPORARY TABLE IF NOT EXISTS temp_total(
		sno CHAR(4),
		sum INT
	);
	
	OPEN add_sum;
	
	WHILE TRUE DO
		FETCH add_sum INTO temp_sno,temp_cno,temp_grade;-- 获取sc表的每一条记录
-- 		SELECT temp_sno,temp_cno,temp_grade;-- 获取sc表的每一条记录(测试
		IF temp_grade>60 THEN
			SELECT credit INTO temp_credit FROM cou WHERE cno=temp_cno;-- 获得课程对应的学分
			SET has_sno = NULL;
			SELECT COUNT(*) INTO has_sno FROM temp_total GROUP BY sno HAVING sno=temp_sno;
-- 			SELECT COUNT(*) FROM temp_total GROUP BY sno HAVING sno=temp_sno;（测试
-- 			SELECT temp_sno,has_sno;（测试
			IF has_sno IS NULL THEN
				INSERT INTO temp_total VALUE(temp_sno,temp_credit);-- 如果之前没有记录新增记录
			ELSE UPDATE temp_total SET sum=temp_credit+sum WHERE sno=temp_sno;-- 有记录修改总学分就行
			END IF;
		END IF;
		
	END WHILE;
	-- 后面的都执行不了，退出不了while循环，因为我没有设置handler
	-- 为什么不设置呢，因为如果设置exit handler for not found，会在临时表中第一次查询指定sno的时候找不到（还没有插入数据），返回null也就是not found
	-- 直接退出，所以最后这段代码我只能收到输出临时表，得到每个人获得的总学分
	CLOSE add_sum;
	SELECT * FROM temp_total;
	DROP TEMPORARY TABLE temp_total;
	
	
END;

CALL add_totalcredit();-- 调用存储过程

DROP PROCEDURE add_totalcredit;-- 删除存储过程