mysql_01_游标的使用
一、表的创建
1、直接创建表
DROP TABLE IF EXISTS shops_info; /*EMP产品版本版本信息表*/ CREATE TABLE shops_info ( ID INT PRIMARY KEY NOT NULL AUTO_INCREMENT, /*自增ID*/ name VARCHAR(20) DEFAULT '' NOT NULL, price INT DEFAULT 0 NOT NULL, pdesc VARCHAR(20) DEFAULT '0' NOT NULL, CREATETIME DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00', /*创建时间*/
SENDTIME TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, /*默认当前时间*/
MEMO VARCHAR(128) DEFAULT '' NOT NULL /*备注*/ ) ENGINE=INNODB DEFAULT CHARSET=UTF8;
2、存储过程动态创建表
-- 动态创建表shops_info201812 DROP PROCEDURE IF EXISTS CREATETABLE; DELIMITER ;; CREATE DEFINER=`root`@`%` PROCEDURE CREATETABLE(PIYM INT) BEGIN DECLARE STR VARCHAR(4000); DECLARE TABLENAME VARCHAR(16); SET TABLENAME=CONCAT('shops_info',PIYM); SET STR=CONCAT('CREATE TABLE ',TABLENAME, ' ( ID INT PRIMARY KEY NOT NULL AUTO_INCREMENT, /*自增ID*/ name VARCHAR(20) NOT NULL, price INT DEFAULT 0 NOT NULL, pdesc VARCHAR(20) NOT NULL, CREATETIME DATETIME NOT NULL , /*创建时间*/ MEMO VARCHAR(128) NOT NULL /*备注*/ ) ENGINE=INNODB DEFAULT CHARSET=UTF8;'); -- @SQL 在MySQL以@开头的变量是SESSION变量，不需要声明，也不能声明 SET @SQL=STR; -- 预处理需要执行的动态SQL， PREPARE SL FROM @SQL; -- 执行动态sql EXECUTE SL; -- 释放掉预处理段 DEALLOCATE PREPARE SL; END ;; DELIMITER ; -- 存储过程的调用 CALL CREATETABLE(201811); DROP PROCEDURE IF EXISTS CREATETABLE;
二、列的增删改
1、查询表中列的情况
SHOW COLUMNS FROM shops_info;
2、增加列
（1）、普通增加列
alter table shops_info add COLUMN bak1 varchar(20) DEFAULT '' NOT NULL;
（2）、存储过程增加列
--通过存储过程增加列 DELIMITER ;; drop PROCEDURE if EXISTS ADDCOL; CREATE DEFINER=`root`@`%` PROCEDURE ADDCOL() BEGIN IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND COLUMN_NAME='bak3' AND TABLE_NAME='shops_info') THEN ALTER TABLE shops_info ADD bak3 INT DEFAULT 0 NOT NULL; END IF; END;; DELIMITER ; --执行存储过程 CALL ADDCOL(); drop PROCEDURE if EXISTS ADDCOL;
3、删除列
alter table shops_info drop bak1;
4、修改表字段
alter table shops_info modify COLUMN bak1 varchar(64) default '' NOT NULL;
5、修改字段的默认值
ALTER TABLE shops_info ALTER bak1 SET DEFAULT '哈哈';
6、删除字段的默认值
ALTER TABLE shops_info ALTER bak1 DROP DEFAULT;
7、修改数据表的名称
ALTER TABLE shops_info_bak RENAME TO shops_info;
8、修改存储引擎：修改为myisam
alter table tableName engine=myisam;
9、删除外键约束：keyName是外键别名
alter table tableName drop foreign key keyName;
三、索引
1、普通索引
--------普通索引------------- --添加普通索引 create index price_index on shops_info(price); --删除索引 DROP INDEX price_index ON shops_info; --修改表结构(添加索引) alter Table shops_info add index price_index(price) --创建表的时候直接指定索引 create table mytable( id int, username varchar(20), index username_index(username) )
2、唯一索引
------------唯一索引,唯一索引不能有重复值（除了null）------------ --创建索引 create unique index price_index on shops_info(price); --修改表的结构(添加唯一索引) alter Table shops_info add unique index price_index(price) --创建表的时候直接指定唯一索引 create table mytable( id int, username varchar(20), unique index username_index(username) )
3、使用ALTER 命令添加和删除索引
------------使用ALTER 命令添加和删除索引---------------------- --添加一个主键，主键只能作用于一个列上，这意味着索引值必须是唯一的,且不能为NULL alter Table mytable add primary key(id) ; --添加唯一索引，创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。 alter Table mytable add unique index username_index(username); --添加普通索引 alter table mytable add index username_index(username);
4、删除自增长的主键
------------删除自增长的主键（分两步）--------------------- --1、删除自增长 ALTER TABLE shops_info MODIFY column id INT NOT NULL; --2、删除主键 alter table shops_info drop primary key ;
5、查询索引
SHOW INDEX FROM shops_info;
四、插入数据
1、直接插入数据
-- 直接插入数据 insert into shops_info(name,price,pdesc,CREATETIME) SELECT '电饭煲2',3002,'煮饭2',NOW() FROM DUAL WHERE NOT EXISTS(SELECT * FROM shops_info WHERE name='电饭煲2');
2、存储过程插入数据
-- 2、存储过程插入数据 DELIMITER ;; drop PROCEDURE if EXISTS insert_test; CREATE DEFINER=`root`@`%` PROCEDURE insert_test( name1 VARCHAR(20), price1 INT, pdesc1 VARCHAR(20) ) BEGIN IF NOT EXISTS(SELECT * FROM shops_info WHERE name=name1 AND price=price1 AND pdesc=pdesc1 ) THEN INSERT INTO shops_info(name,price,pdesc,CREATETIME) VALUES(name1,price1,pdesc1,NOW()); END IF; END;; DELIMITER ; -- 存储过程的调用 CALL insert_test('海尔冰箱1',3080,'冷冻食品1'); DROP PROCEDURE IF EXISTS insert_test;
3、循环插入数据
delimiter $$ drop PROCEDURE if EXISTS pre; CREATE PROCEDURE pre() BEGIN DECLARE i INT ; SET i = 1 ; WHILE i < 30000 DO INSERT INTO Lf_Keywords (keywoed, kw_state) VALUES (CONCAT('ouyangyu', i), 1) ; SET i = i + 1 ; END WHILE ; END$$ CALL pre () ;
五、游标的使用
1、游标的使用步骤
游标的使用一般分为5个步骤，主要是：定义游标->打开游标->使用游标->关闭游标->释放游标。
-- （1）.定义游标 DECLARE <游标名> CURSOR FOR select语句; -- （2）.打开游标 open <游标名> -- （3）.使用游标 -- 使用游标需要用关键字fetch来取出数据，然后取出的数据需要有存放的地方，我们需要用declare声明变量存放列的数据其语法格式为： -- declare 变量1 数据类型(与列值的数据类型相同) -- declare 变量2 数据类型(与列值的数据类型相同) -- declare 变量3 数据类型(与列值的数据类型相同) -- FETCH [NEXT | PRIOR | FIRST | LAST] FROM <游标名> [ INTO 变量名1,变量名2,变量名3[,…] ] -- NEXT：取下一行的数据，游标一开始默认的第一行之前，故要让游标指向第一行，就必须第一次就执行FETCH NEXT操作 -- INTO：将一行中每个对应的列下的数据放到与列 的数据类型相同的变量中。 -- （4）.关闭游标： close mycursor; -- （5）.释放游标 deallocate mycursor;
2、游标的具体使用
-- 1、未使用循环的游标实例
-- 从表shops_info中使用游标对每一行进行fetch
-- 注意：存储过程申明的变量名称不能和数据库中字段名称一样，否则取不到值
DELIMITER $$ drop PROCEDURE if EXISTS cursor_test; create PROCEDURE cursor_test() BEGIN DECLARE id1 INT; DECLARE name1 VARCHAR(20); DECLARE price1 INT; DECLARE pdesc1 VARCHAR(20); -- 定义游标 DECLARE mycursor CURSOR for select id,name,price,pdesc from shops_info; -- 打开游标 open mycursor; -- 使用游标 FETCH next from mycursor into id1,name1,price1,pdesc1; -- 显示结果 select id1,name1,price1,pdesc1; -- 关闭游标 close mycursor; END $$ DELIMITER ; -- 存储过程的调用 CALL cursor_test(); DROP PROCEDURE IF EXISTS cursor_test;
-- 2、使用while循环的游标实例
DELIMITER $$ drop PROCEDURE if EXISTS cursor_test; create PROCEDURE cursor_test() BEGIN DECLARE tmpName VARCHAR(20) default '' ; DECLARE allName varchar(255) default ''; -- 定义游标 DECLARE mycursor CURSOR for select name from shops_info; -- MySQL游标异常后捕捉,并设置循环使用变量 tmpname 为 null 跳出循环 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET tmpName = null; -- 打开游标 open mycursor; -- 使用游标 FETCH mycursor into tmpName; while (tmpName is not null) do set tmpName = CONCAT(tmpName ,";") ; set allName = CONCAT(allName ,tmpName) ; FETCH mycursor into tmpName; END WHILE; -- 显示结果 select allName; -- 关闭游标 close mycursor; END $$ DELIMITER ; -- 存储过程的调用 CALL cursor_test(); DROP PROCEDURE IF EXISTS cursor_test;
-- 3、使用loop循环的游标实例
DELIMITER $$ drop PROCEDURE if EXISTS cursor_test; CREATE PROCEDURE cursor_test() begin -- 设置终止标记 DECLARE STOPFLAG INT DEFAULT 0; DECLARE tempName VARCHAR(20) default '' ; DECLARE allName VARCHAR(256) default '' ; DECLARE mycursor CURSOR for select name from shops_info; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET STOPFLAG = 1; open mycursor; -- loop循环开始标记，myloop为自己随意取得名称 myloop:loop fetch mycursor into tempName; -- loop循环退出标记 if STOPFLAG = 1 then leave myloop; end if; -- mysql赋值语句 set tempName = CONCAT(tempName,';'); set allName = CONCAT(allName,tempName); -- loop循环结束标记 end loop myloop; select allName; -- 关闭游标 close mycursor; END $$ DELIMITER ; -- 存储过程的调用 CALL cursor_test(); drop PROCEDURE if EXISTS cursor_test;
-- 4、使用repeat循环的游标实例
DELIMITER $$ drop PROCEDURE if EXISTS cursor_test; CREATE PROCEDURE cursor_test() begin -- 设置终止标记 DECLARE STOPFLAG INT DEFAULT 0; DECLARE tempName VARCHAR(20) default '' ; DECLARE allName VARCHAR(256) default '' ; DECLARE mycursor CURSOR for select name from shops_info; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET STOPFLAG = 1; open mycursor; -- repeat循环开始标记，repeat为自己随意取得名称 repeat fetch mycursor into tempName; -- mysql赋值语句 if STOPFLAG = 0 then set tempName = CONCAT(tempName,';'); set allName = CONCAT(allName,tempName); end if; -- repeat循环结束标记 until STOPFLAG = 1 end repeat; select allName; -- 关闭游标 close mycursor; END $$ DELIMITER ; -- 存储过程的调用 CALL cursor_test(); drop PROCEDURE if EXISTS cursor_test;
