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?
- 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
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?
- mysql>\d//#修改定界符为//
- Mysql>Create procedure p1 ()
- begin
- set @i = 0;
- ->while @i <
- Insert into T2 (name) values(concat ( "User" , @i));
- set @i = @i + 1;
- andend while;
- end;//
- 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?
- mysql>\d//
- Mysql>Create trigger t1 before Inserton t1 for each row
- begin
- Insert into T2 (name) values(new. name );
- -End//
- 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?
- mysql>\d//
- Mysql>Create trigger T2 before delete on t1 for each row
- begin
- --delete from T2 where ID =old.id;
- -End//
- 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?
- 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;
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)