MySQL Study Notes _ 10_MySQL Advanced Operations (below) and study notes _ 10_mysql
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.
[SQL]View plaincopyprint?
- 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 Origin
- 6. rollback; # Roll back to the original Restore Point
- 7. commit; # submit data to the server
- 8. set autocommit = 1; # Enable Automatic commit and disable transaction processing.
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 ()
[SQL]View plaincopyprint?
- Mysql> \ d // # modify the delimiter //
- 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;
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
[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) 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.
[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 trigger t3. If table t1 is modified, the records in Table t2 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 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;