说明:首先需要设置delimiter。delimiter的作用:告诉解释器,这段命令是否已经结束了,mysql是否可以执行了
默认情况下,delimiter是‘;’但是当我们编写procedure时,如果是默认设置,那么一遇到‘;’,mysql就要执行。
WHILE…DO方式
/*循环插入*/
DELIMITER //
CREATE PROCEDURE callback()
BEGIN
DECLARE num INT;
SET num = 100;
WHILE
num < 300 DO
INSERT INTO t_user(user_loginName,user_password,user_name,user_gender,user_email,user_gradeValueInt)
VALUES(CONCAT("YuWang", num),CONCAT("pwd", num),CONCAT("YuWang", num),"男","1024@qq.com",num);
SET num = num + 1;
END WHILE;
END;
//
设置好了存储过程要执行调用才生效
CALL callback();
其他基本命令
查看表
DESC USER;
SHOW FULL FIELDS FROM user;
查看表索引
SHOW KEYS FROM USER;
SHOW index FROM USER;
清空表
DELETE FROM USER;
查看指定库中的PROCEDURE
SELECT NAME FROM mysql.proc WHERE db = ‘test’ AND TYPE = ‘PROCEDURE’;
删除指定PROCEDURE
DROP PROCEDURE IF EXISTS callback;
查看创建代码
SHOW CREATE PROCEDURE callback;
调用存储过程(callback是自己命名的)
CALL callback();
删除存储过程(callback是自己命名的)
DROP PROCEDURE callback;