MySQL Advanced Operations (below)
V. MySQL pre-processing statements
1. Set the pre-processing stmt and pass a data as the where judgment condition.
Prepare stmt from "select * from table_name where id> ?";
2. Set a variable
Set @ I = 1;
3. Execute the pre-processing statement
Execute stmt using @ I;
4. Delete preprocessing commands
Drop prepare stmt;
Vi. MySQL Transaction Processing
[Note] the MyISAM storage engine does not support transactions. InnoDB Storage engine should be used.
1. set autocommit = 0; # disable automatic submission 2. delete from t1 where id> 4; 3. savepoint p1; # set Restore Point 4. delete from t1; 5. rollback to p1; # Roll Back to p1 Restore Point 6 and rollback; # Roll back to the original Restore Point 7 and commit; # submit data to Server 8 and set autocommit = 1; # Enable Automatic commit and disable Transaction Processing
VII. MySQL Storage
1. Create a storage p1 ()
Mysql> \ d // # modify the delimiter to // mysql> create procedure p1 ()-> begin-> set @ I = 0; -> while @ I <100 do-> insert into t2 (name) values (concat ("user", @ I);-> set @ I = @ I + 1; -> end while;-> end; // mysql> \ d;
2. Run p1 ()
Callp1 ();
3. view the status information of procedure
Showprocedure status \ G
4. View Details of procedurep1
Show create procedure p1 \ G
VIII. MySQL triggers
1. Create a trigger
# Create a trigger named t1. when data is inserted into Table t1, an action is triggered: insert number to table t2
mysql>\d //mysql>create trigger t1 before inserton t1 for each row ->begin ->insert into t2(name) values(new.name); ->end//mysql>\d ;
# Create trigger t2. If table t1 deletes data, the trigger is triggered and data in table t2 is also deleted accordingly.
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 trigger t3. If table t1 is modified, the records in Table t2 are also modified accordingly.
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 a trigger
Drop trigger trigger_name;
[Appendix] delete all data in the table: truncatetable_name; # Faster. You can also clear the auto_increment list.
9. rearranging the auto_increment Value
How can I restore the Automatically increasing IDs in MySQL?
1. Do not use delete from table_name when clearing the table;
Instead, truncate [table] table_name;
Or
2. Run the alter command to modify the table after clearing the content.
Alter table table_name auto_increment = 1;