系统集成 SpringMVC 的测试框架,为了在测试结束后,清空测试数据库的所有表单,需要写一段 SQL 脚本实现。这个任务到了我这里,尴尬的是,对 MySql 的理解仅在于 CRUD 的我,要写操作数据库的 SQL ,简直是莫大的难事,作为程序员,不会写不是问题,不会查就是罪过了。
然而,在查百度的时候,内心真的有一万只草泥马奔腾而过,没想到很多资料都是无用的。先不说一大堆博客都是复制粘贴而来,更糟糕的是,博客里有写代码根本运行不起来的,这些测试 Demo 还有什么意思,结果查了半天都没找到可用的,也要吐槽百度的搜索引擎,基于商业的排序的算法根本没办法和谷歌的搜索引擎相比,神伤。最后为了解决问题,只能自学 MySql 的一些基本语法,再把这个脚本写出来。
学习所有的 SQL 语法肯定是不现实的,所以,为了实现目标,只能将这个任务切割开来。
删除表 SQL
获取数据库下的所有表 SQL
遍历结果集 SQL
遍历过程中,拼接删除表的 SQL ,并执行该 SQL
SQL:删除表
直接查 MySQL 的 语法:
DROP ‘table’ /*删除数据库下对应名称的表*/
抱着侥幸的心理,希望有可以删除数据库下的所有表的语法,这样就不需要进行下面的步骤了,然并卵,幻想破灭。
SQL:获取数据库下的所有表
select table_name from information_schema.tables ; /*获取数据库下的所有表单,可添加筛选条件*/
解析 : information_schema表
information_schema这张数据表保存了MySQL服务器所有数据库的信息。如数据库名,数据库的表,表栏的数据类型与访问权限等。
information_schema.schemata 表:数据库的信息
information_schema.tables 表:数据库中表的信息
information_schema.columns 表:表的列的信息
information_schema.statistics 表:表的索引信息
只要想找关于数据库的信息,这张表都可以提供相关的信息。
SQL:遍历结果集
涉及到遍历,第一时间就想到了 for / while 这样的关键字,MySql当然提供了相关的语法,但是这次的语句就不再是简单的查询语句,而必须通过函数来实现。
MySql 提供了三种方式:WHILE 、 LOOP 、 REPEAT:
以 REPEAT UNTIL 循环为例,MySQL 给出的方法中需要明确三个参数:
[ label: ] REPEAT /*label:循环名称,可有可无*/
statements /*statements:循环语句内的执行语句*/
UNTIL expression /*expression : 循环条件*/
END REPEAT [ label ] ;
有了前面三点做铺垫,若要实现目标,只要再循环体里拼接出删除表的语句,并执行,也就是以下的重难点。
SQL:拼接语句,执行语句
难点一:循环体内如何遍历结果集
百度关键词:MySql 循环体内如何遍历结果集
百度结果,使用游标,一番查阅后,对游标的理解是:
游标充当了指针的作用
游标可对查询数据库所返回的结果集进行遍历,以便进行相应的操作
尽管游标能遍历结果中的所有行,但他一次只指向一行
难点二:循环的判断条件要怎么写
百度关键词:mysql 判断游标结束
百度结果:使用以下的 SQL:
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
原理不太懂,但是查了一下 02000 的意思:
发生下述异常之一:
SELECT INTO 语句或 INSERT 语句的子查询的结果为空表。
在搜索的 UPDATE 或 DELETE 语句内标识的行数为零。
在 FETCH 语句中引用的游标位置处于结果表最后一行之后。
该SQL的语意是:当游标到了结果集最后一行的时候,设置done=1。
难点三:如何执行拼接好的语句
百度关键词:mysql 如何执行拼接的sql
百度结果:使用 prepare ,语法如下:
PREPARE statement_name FROM preparable_SQL_statement; /*定义*/
EXECUTE statement_name [USING @var_name [, @var_name] ...]; /*执行预处理语句*/
使用的方法和JDBC差不多,所以难度也不算太大,接下来,就要开始实现这个功能:
代码:遍历过程中,拼接删除表的 SQL ,并执行该 SQL
DECLARE done INT DEFAULT 0;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
OPEN sur;
REPEAT /*循环体:使用游标遍历结果集*/
FETCH sur INTO tableName; /* 将游标获取值设置到 tableName 变量中 */
IF NOT done THEN
SET cmd=concat('DROP TABLE ',DB_NAME,'.',tableName);
SET @E=cmd;
PREPARE stmt FROM @E;
EXECUTE stmt; /*执行删除命令*/
DEALLOCATE PREPARE stmt; /*清空删除命令*/
END IF;
UNTIL done END REPEAT; /*更新done值,并判断是否跳出循环*/
CLOSE sur;
结合以上四点,最终的SQL脚本是:
DROP PROCEDURE IF EXISTS procedure_drop_table;
CREATE PROCEDURE procedure_drop_table()
BEGIN
DECLARE DB_NAME varchar(50) DEFAULT "testcase"; -- 测试数据库的名称
DECLARE done INT DEFAULT 0;
DECLARE tableName varchar(50); -- 测试库表明
DECLARE cmd varchar(50); -- 执行命令
DECLARE sur CURSOR -- 游标
FOR
SELECT table_name FROM information_schema.TABLES WHERE table_schema=DB_NAME;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
OPEN sur;
REPEAT
FETCH sur INTO tableName;
IF NOT done THEN
set cmd=concat('DROP TABLE ',DB_NAME,'.',tableName); -- 拼接删除命令
SET @E=cmd;
PREPARE stmt FROM @E;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END IF;
UNTIL done END REPEAT;
CLOSE sur;
END
call procedure_drop_table();
**粗体** _斜体_ [链接](http://example.com) `代码` - 列表 > 引用
。你还可以使用@
来通知其他用户。