MySQL Learning note _10_mysql advanced operations (bottom)

 MySQLAdvanced Operations (bottom)

V.MySQL preprocessing statements

1 , setting up preprocessing stmt , passing a data as where the judging conditions

Prepare stmt from "SELECT * FROM table_name where ID >?";

2 , set a variable

Set @i = 1;

3 , execute preprocessing statements

Execute stmt using @i;

4 , delete preprocessing directives

Drop prepare stmt;

Six, MySQL Transaction Processing

"Attention" MyISAM the storage engine does not support transactions and should use the InnoDB storage engine

[SQL]View Plaincopyprint?

2. 1. Set              autocommit = 0; #关闭自动提交
4. 2,delete from T1 where ID > 4;
6. 3, savepoint p1; #设置还原点
8. 4,delete from T1;
10. 5,rollback to p1; #回滚到p1还原点
12. 6,rollback; #回滚到最原始的还原点
14. 7,commit ; #提交数据到服务器
16. 8,set autocommit = 1; #开启自动提交, turn off transaction processing

1, set autocommit = 0;              #关闭自动提交2, delete from T1 where ID > 4;3, savepoint p1;                        #设置还原点4, delete from t1;5, rollback to P1;                       #回滚到p1还原点6, rollback;                                #回滚到最原始的还原点7, commit;                                #提交数据到服务器8, set autocommit = 1;                #开启自动提交, turn off transaction processing

Seven, MySQL Storage

1 , create a storage P1 ()

[SQL]View Plaincopyprint?

2. mysql>\d//#修改定界符为//
4. Mysql>Create procedure p1 ()
6. begin
8. set @i = 0;
10. ->while @i <
12. Insert into T2 (name) values(concat ( "User"  , @i));
14. set @i = @i + 1;
16. andend while;
18. end;//
20. mysql>\d;

mysql>\d//                             #修改定界符为//mysql>create procedure p1 ()        ->begin        ->set @i = 0;        ->while @i < does        ->insert into T2 (name) VALUES (concat ("User", @i));        ->set @i = @i + 1;       ->end while;       ->end;//mysql>\d;

2 , Execution P1 ()

CALLP1 ();

3 , view procedure of the Status Information

Showprocedure Status \g

4 , view Procedurep1 the specific information

Show CREATE PROCEDURE P1 \g

Eight, MySQL Trigger

1 , creating triggers

# Create a name that is named T1 the trigger, when the T1 when data is inserted in the table, an action is raised: T2 number of inserts in table

[SQL]View Plaincopyprint?

2. mysql>\d//
4. Mysql>Create trigger t1 before Inserton t1 for each row
6. begin
8. Insert into T2 (name) values(new. name  );
10. -End//
12. mysql>\d;

mysql>\d//mysql>create trigger T1 before Inserton T1 for each row        ->begin        ->insert into T2 (name) valu ES (new.name);        ->end//mysql>\d;

# Create a trigger T2 , if the table T1 Delete data, trigger is raised, table T2 also delete the data in the

[SQL]View Plaincopyprint?

2. mysql>\d//
4. Mysql>Create trigger T2 before delete on t1 for each row
6. begin
8. --delete from T2 where ID =old.id;
10. -End//
12. mysql>\d;

mysql>\d//mysql>create Trigger T2 before delete on T1 for each row       ->begin      ->delete from T2 where ID =old.id;      ->end//mysql>\d;

# Create a trigger t3 , if you modify the table T1 then T2 the records in the update are also modified accordingly.

[SQL]View Plaincopyprint?

2. mysql>\d//
4. Mysql>Create trigger T3 before update on t1 for each row
6. begin
8. update T2 set ID =new.id where id = old.id;
10. -End//
12. mysql>\d;

mysql>\d//mysql>create trigger t3 before update on T1 for each row        ->begin        ->update T2 set ID =new.id where id = old.id;        ->end//mysql>\d;

2 , delete triggers

Drop Trigger trigger_name;

"Attached" deletes all data from the table: Truncatetable_name; # faster and can also be emptied auto_increment List

Nine, rearrange auto_increment value

MySQL automatic growth in the ID How to recover?

1 , when emptying the table, do not use DELETE from table_name;

instead: truncate [table] table_name;

Or

2 , empty content and use it directly after Alter Command Modify Table

ALTER TABLE table_name auto_increment = 1;

MySQL Learning note _10_mysql advanced operations (bottom)