爱站程序员基地一. Sql语句
1. 建表语句：
CREATE TABLE `dept` (`id` INT(11) NOT NULL AUTO_INCREMENT,`deptName` VARCHAR(30) DEFAULT NULL,`address` VARCHAR(40) DEFAULT NULL,ceo INT NULL ,PRIMARY KEY (`id`)) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULTCHARSET=utf8;CREATE TABLE `emp` (`id` INT(11) NOT NULL AUTO_INCREMENT,`empno` INT NOT NULL ,`name` VARCHAR(20) DEFAULT NULL,`age` INT(3) DEFAULT NULL,`deptId` INT(11) DEFAULT NULL,PRIMARY KEY (`id`)#CONSTRAINT `fk_dept_id` FOREIGN KEY (`deptId`) REFERENCES `t_dept` (`id`)) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
2. 设置参数：
在执行创建函数之前，首先请保证 log_bin_trust_function_creators 参数为 1，即 on 开启状态。
否则会报错：
查询：show variables like \'log_bin_trust_function_creators\';设置：set global log_bin_trust_function_creators=1;当然，如上设置只存在于当前操作，想要永久生效，需要写入到配置文件中：在[mysqld]中加上 log_bin_trust_function_creators=1
3. 编写随机函数：
# 编写随机函数# 随机产生字符串DELIMITER $$CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)BEGINDECLARE chars_str VARCHAR(100) DEFAULT \'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ\';DECLARE return_str VARCHAR(255) DEFAULT \'\';DECLARE i INT DEFAULT 0;WHILE i < n DOSET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1)); SET i = i + 1;END WHILE;RETURN return_str;END $$# 如果要删除函数，则执行：drop function rand_string;
4. 随机产生部门编号：
# 随机产生部门编号# 用于随机产生多少到多少的编号DELIMITER $$CREATE FUNCTION rand_num (from_num INT ,to_num INT) RETURNS INT(11) BEGINDECLARE i INT DEFAULT 0;SET i = FLOOR(from_num +RAND()*(to_num -from_num+1)) ;RETURN i;END$$# 如果要删除函数：drop function rand_num;
5. 创建往emp表中插入数据的存储过程：
# 创建往emp表中插入数据的存储过程DELIMITER $$CREATE PROCEDURE insert_emp( START INT , max_num INT ) BEGINDECLARE i INT DEFAULT 0;#set autocommit =0 把 autocommit 设置成 0SET autocommit = 0; REPEATSET i = i + 1;INSERT INTO emp (empno, NAME ,age ,deptid ) VALUES ((START+i) ,rand_string(6) rand_num(30,50),rand_num(1,10000));UNTIL i = max_num END REPEAT; COMMIT;END$$# 删除# DELIMITER ;# drop PROCEDURE insert_emp;
6. 创建往dept 表中插入数据的存储过程：
#执行存储过程，往 dept表添加随机数据DELIMITER $$CREATE PROCEDURE `insert_dept`( max_num INT ) BEGINDECLARE i INT DEFAULT 0;SET autocommit = 0;REPEATSET i = i + 1;INSERT INTO dept ( deptname,address,ceo ) VALUES (rand_string(8),rand_string(10),rand_num(1,500000)); UNTIL i = max_numEND REPEAT;COMMIT;END$$# 删除# DELIMITER ;# drop PROCEDURE insert_dept;
7. 调用存储过程：
# 添加数据到部门表# 执行存储过程，往 dept 表添加 1 万条数据DELIMITER ;CALL insert_dept(10000);# 添加数据到员工表# 执行存储过程，往 emp 表添加 50 万条数据DELIMITER ;CALL insert_emp(100000,500000);
8. 删除索引的存储过程：
# 删除索引的存储过程DELIMITER $$CREATE PROCEDURE `proc_drop_index`(dbname VARCHAR(200),tablename VARCHAR(200)) BEGINDECLARE done INT DEFAULT 0;DECLARE ct INT DEFAULT 0;DECLARE _index VARCHAR(200) DEFAULT \'\';DECLARE _cur CURSOR FOR SELECT index_name FROM information_schema.STATISTICSWHERE table_schema=dbname AND table_name=tablename AND seq_in_index=1 AND ndex_name <>\'PRIMARY\';DECLARE CONTINUE HANDLER FOR NOT FOUND set done=2 ;OPEN _cur;FETCH _cur INTO _index; WHILE _index<>\'\' DOSET @str = CONCAT(\"drop index \",_index,\" on \",tablename ); PREPARE sql_str FROM @str ;EXECUTE sql_str;DEALLOCATE PREPARE sql_str;SET _index=\'\';FETCH _cur INTO _index;END WHILE; CLOSE _cur; END$$
9. 执行存储过程：
CALL proc_drop_index(\"dbname\",\"tablename\");
