mysql一些复制表、增删改索引、建存储过程、创建函数、创建触发器的一些命令

CREATE TABLE `t1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(32) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `index_name` (`username`)
)

insert into t1(username) values('areyouok'),('howareyou'),('happyeveryday'),('happiness');


复制表结构和数据的语句：
create table t2 like t1;
insert into t2 select * from t1;

注意：直接使用create table t2 as select * from t1;也可以将数据复制到t2表，但是t2表结构与t1不相同。

alter table t1 add index in_name(username);//增加普通索引
show index from t1;//查看t1表共有哪些索引

alter table t1 drop index in_name;//删除t1表上的in_name索引

show index from t1;

alter table t1 add unique index u_name(username); //创建unique索引
show index from t1;

alter table t1 drop index u_name; //删除unique索引

有输入参数的存储过程
mysql> delimiter //
mysql> create procedure proc_t1(IN uid int)
    -> begin
    -> select username from t1 where id=uid
    -> end//

	
	mysql> delimiter //
mysql> create procedure proc_t2(OUT uname varchar(32)) begin select username into uname from t1 where id=2; end//


存储过程中的形参不要与字段名相同才行，不然获取不到数据
mysql> delimiter //
mysql> create procedure proc_t2(OUT username varchar(32))
    -> begin
    -> select username into username from t1 where id=2;
    -> end//
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> call proc_t2(@a);
Query OK, 1 row affected (0.00 sec)

mysql> select @a;
+------+
| @a   |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

存储过程中的参数名与字段名不同，能够正常返回结果
mysql> delimiter //
mysql> create procedure proc_t2(OUT uname varchar(32))
    -> begin
    -> select username into uname from t1 where id=2;
    -> end//
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> call proc_t2(@a);
Query OK, 1 row affected (0.00 sec)

mysql> select @a;
+-----------+
| @a        |
+-----------+
| howareyou |
+-----------+
1 row in set (0.00 sec)

以下是既有输入又有输出参数的存储过程
mysql> delimiter //
mysql> create procedure proc_t3(IN uid int,OUT uname varchar(32))
    -> begin
    -> select username into uname from t1 where id=uid;
    -> end//
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> call proc_t3(1,@a);
Query OK, 1 row affected (0.00 sec)

mysql> select @a;
+----------+
| @a       |
+----------+
| areyouok |
+----------+
1 row in set (0.00 sec)

mysql> call proc_t3(2,@a); 
Query OK, 1 row affected (0.00 sec)

mysql> select @a;
+-----------+
| @a        |
+-----------+
| howareyou |
+-----------+
1 row in set (0.00 sec)

自定义函数
mysql> delimiter //
mysql> create function sayhello(uname varchar(32))
    -> returns varchar(100)
    -> return concat('hello',uname,'!');
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> select sayhello(username) from t1;
+---------------------+
| sayhello(username)  |
+---------------------+
| helloareyouok!      |
| hellohappiness!     |
| hellohappyeveryday! |
| hellohowareyou!     |
+---------------------+
4 rows in set (0.00 sec)

定义触发器，在插入时触发
mysql> delimiter //
mysql> create trigger trg_t1 before insert on t1 for each row
    -> begin 
    -> insert into t2(username) values(new.username);
    -> end//
Query OK, 0 rows affected (0.08 sec)

mysql> delimiter ;
mysql> select * from t2;
+----+---------------+
| id | username      |
+----+---------------+
|  1 | areyouok      |
|  4 | happiness     |
|  3 | happyeveryday |
|  2 | howareyou     |
+----+---------------+
4 rows in set (0.00 sec)

mysql> insert into t1(username) values('wangbaobao');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t2;
+----+---------------+
| id | username      |
+----+---------------+
|  1 | areyouok      |
|  4 | happiness     |
|  3 | happyeveryday |
|  2 | howareyou     |
|  5 | wangbaobao    |
+----+---------------+
5 rows in set (0.00 sec)

定义触发器，在修改时触发
mysql> delimiter //
mysql> create trigger tg_update before update on t1 for each row
    -> begin
    -> update t2 set username=new.username where username=old.username;
    -> end//
Query OK, 0 rows affected (0.07 sec)

mysql> delimiter ;
mysql> update t1 set username='wangbaochai' where id=5;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t1;
+----+---------------+
| id | username      |
+----+---------------+
|  1 | areyouok      |
|  4 | happiness     |
|  3 | happyeveryday |
|  2 | howareyou     |
|  5 | wangbaochai   |
+----+---------------+
5 rows in set (0.00 sec)

mysql> select * from t2;
+----+---------------+
| id | username      |
+----+---------------+
|  1 | areyouok      |
|  4 | happiness     |
|  3 | happyeveryday |
|  2 | howareyou     |
|  5 | wangbaochai   |
+----+---------------+
5 rows in set (0.00 sec)

定义触发器，在删除时触发
mysql> delimiter //
mysql> create trigger tg_delete before delete on t1 for each row
    -> begin
    -> delete from t2 where username=old.username;
    -> end//
Query OK, 0 rows affected (0.06 sec)

mysql> delimiter ;
mysql> delete from t1 where id=2;
Query OK, 1 row affected (0.02 sec)

mysql> select * from t1;
+----+---------------+
| id | username      |
+----+---------------+
|  1 | areyouok      |
|  4 | happiness     |
|  3 | happyeveryday |
|  5 | wangbaochai   |
+----+---------------+
4 rows in set (0.00 sec)

mysql> select * from t2;
+----+---------------+
| id | username      |
+----+---------------+
|  1 | areyouok      |
|  4 | happiness     |
|  3 | happyeveryday |
|  5 | wangbaochai   |
+----+---------------+
4 rows in set (0.00 sec)


mysql> delimiter //
mysql> create procedure proc_filllog()
    begin
    declare i int;
    set i=1;
    while i<10000 do
    insert into webservicelog(fromto,biztype,bizcode,result,errmsg,oprtime) values(1,1,concat('areyouok',i),1,'',adddate('20161001010101',interval i day));
    set i=i+1;
    end while;
    end//