Andy Niu �����ĵ�

1、使用while
    DROP PROCEDURE IF EXISTS `addstudent`;
    DELIMITER ;;
    CREATE PROCEDURE `addstudent`(iNum int)
    BEGIN
    declare vI int default 0;
    
    start transaction;
    
    while(vI < iNum) do 
    insert into student(id,name) values(vI,concat('Andy',vI));
    set vI = vI+1;
    end while;
    
    commit;
    END
    ;;
    DELIMITER ;
2、使用repeat
    BEGIN
    declare vI int default 0;
    
    start transaction;
    
    repeat 
    insert into student(id,name) values(vI,concat('Andy',vI));
    set vI = vI+1;
    until vI >= iNum end repeat;
    
    commit;
    END
3、使用loop
    BEGIN
    declare vI int default 0;
    
    start transaction;
    
    label_insert: LOOP
    
    insert into student(id,name) values(vi,concat('Andy',vi));
    set vI = vI+1;
    if(vI >= iNum) then 
    leave label_insert; 
    end if;
    
    end LOOP label_insert;
    
    commit;
    END
4、考虑下面的需求，在编程语言中循环控制中有continue，如何实现continue的功能？
    使用 iterate label_insert; 对于LOOP如下：
    BEGIN
    declare vI int default 0;
    
    start transaction;
    
    label_insert: LOOP
    
    if(vI=2) then 
    set vI = vI+1;
    iterate label_insert;
    end if;
    
    insert into student(id,name) values(vi,concat('Andy',vi));
    set vI = vI+1;
    if(vI >= iNum) then 
    leave label_insert; 
    end if;
    
    end LOOP label_insert;
    
    commit;
    END
5、注意：对于while和repeat也是同样的道理，但是需要在while和repeat 前面增加一个标签，表示再次循环的位置，如下：
    label_insert: while
    label_insert: repeat
    mysql中暂时没有for循环。

DROP PROCEDURE IF EXISTS `exe_dynamic_sql`;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `exe_dynamic_sql`(iDynamicSql TEXT)
BEGIN
    set @exeSql = iDynamicSql;
    prepare stmt from @exeSql;
    execute stmt;
    deallocate prepare stmt; -- 释放掉预处理段
END
;;
DELIMITER ;

BEGIN
    declare vPartitionName varchar(32) default '';
    declare vIsOver int default 0;
    
    declare vCursorPartitionName cursor for select partition_name from information_schema.partitions where table_name='alarmlog';
    declare continue handler for sqlstate '02000' set vIsOver = 1;

    open vCursorPartitionName;
    fetch vCursorPartitionName into vPartitionName;

    while(vIsOver = 0) do 
        -- do sth
        fetch vCursorPartitionName into vPartitionName;
    end while;
        
    close vCursorPartitionName;
END

参见

1、mysql存储过程没有return语句，怎么办？
2、使用变通的办法，在BEGIN处打一个标签，leave这个标签，就等价于return，如下：
    label_1:BEGIN
    if(i > 5) then 
        leave label_1;
    end if;
    
    select "aaa";
    END
3、注意：标签一般只能打在begin和loop

1、名称在前，类型在后
2、中划线与下划线有时候是等价的
3、value与values有时候是等价的

1、创建主键
    alter table t1 add primary key(c1);
2、删除主键
    alter table t1 drop primary key;
3、查看主键
    show index from t1;
    或者
    desc t1;

1、类型转换有两种方法，如下：
    mysql> select cast('123' as unsigned integer);
    +---------------------------------+
    | cast('123' as unsigned integer) |
    +---------------------------------+
    |                             123 |
    +---------------------------------+
    1 row in set
    
    mysql> select convert('123',unsigned integer);
    +---------------------------------+
    | convert('123',unsigned integer) |
    +---------------------------------+
    |                             123 |
    +---------------------------------+
    1 row in set
2、需要注意的是，目标类型只能是下面的类型。
    BINARY[(N)] 
    CHAR[(N)] 
    DATE 
    DATETIME 
    DECIMAL 
    SIGNED [INTEGER] 
    TIME 
    UNSIGNED [INTEGER] 
3、注意：在Mysql中float，double是非标准的数据类型，在数据库中保存的是近似值。
    而decimal是标准的数据类型，Mysql内部用字符串保存decimal，精确地保存它们的值。
4、在mysql中，数值作为字符串，字符串作为数值，不需要特殊转化，mysql会自动识别，并转化，如下：
    mysql> select 12.1+13.4;
    +-----------+
    | 12.1+13.4 |
    +-----------+
    | 25.5      |
    +-----------+
    1 row in set
    
    mysql> select '12.1'+'13.4';
    +---------------+
    | '12.1'+'13.4' |
    +---------------+
    |          25.5 |
    +---------------+
    1 row in set
    
    mysql> select concat('12.1','13.4');
    +-----------------------+
    | concat('12.1','13.4') |
    +-----------------------+
    | 12.113.4              |
    +-----------------------+
    1 row in set
    
    mysql> select concat(12.1,13.4);
    +-------------------+
    | concat(12.1,13.4) |
    +-------------------+
    | 12.113.4          |
    +-------------------+
    1 row in set
    注意：字符串转换为数值失败，当做0来处理，如下：
    mysql> select '12.1'+'aaa';
    +--------------+
    | '12.1'+'aaa' |
    +--------------+
    |         12.1 |
    +--------------+
    1 row in set
5、数值作为字符串，字符串作为数值，不需要特殊转化，如下：
    mysql> desc t1;
    +-------+--------------+------+-----+---------+-------+
    | Field | Type         | Null | Key | Default | Extra |
    +-------+--------------+------+-----+---------+-------+
    | c1    | int(11)      | NO   |     | 0       |       |
    | c2    | double       | YES  |     | NULL    |       |
    | c3    | varchar(100) | YES  |     | NULL    |       |
    +-------+--------------+------+-----+---------+-------+
    3 rows in set
    
    mysql> select * from t1;
    +----+-------+-------+
    | c1 | c2    | c3    |
    +----+-------+-------+
    |  1 | 12.35 | 12345 |
    +----+-------+-------+
    1 row in set
    
    // 下面的update语句，数值作为字符串，字符串作为数值，没有问题。
    mysql> update t1 set c2='25.6',c3=100 where c1='1';
    Query OK, 1 row affected
    Rows matched: 1  Changed: 1  Warnings: 0
    
    mysql> select * from t1;
    +----+------+-----+
    | c1 | c2   | c3  |
    +----+------+-----+
    |  1 | 25.6 | 100 |
    +----+------+-----+
    1 row in set

1、创建索引
    alter table t1 add index index_c2(c2);
    或者
    create index index_c2 on t1(c2);
2、删除索引
    alter table t1 drop index index_c2;
    或者
    drop index index_c2 on t1;
3、查看索引
    show index from t1;
    或者
    desc t1;