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;