mysql一些複製表、增刪改索引、建儲存過程、建立函式、建立觸發器的一些命令

錚亮不鏽發表於2016-10-30
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//

相關文章