MySQL Study Notes _ 10_MySQL Advanced Operations (below) and study notes _ 10_mysql

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. 1. set autocommit = 0; # disable automatic submission
2. 2. delete from t1 where id> 4;
3. 3. savepoint p1; # Set Restore Point
4. 4. delete from t1;
5. 5. rollback to p1; # Roll Back to p1 restore Origin
6. 6. rollback; # Roll back to the original Restore Point
7. 7. commit; # submit data to the server
8. 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?

1. Mysql> \ d // # modify the delimiter //
2. Mysql> create procedure p1 ()
3. -> Begin
4. -> Set @ I = 0;
5. -> While @ I <100 do
6. -> Insert into t2 (name) values (concat ("user", @ I ));
7. -> Set @ I = @ I + 1;
8. -> End while;
9. -> End ;//
10. 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?

1. Mysql> \ d //
2. Mysql> create trigger t1 before inserton t1 for each row
3. -> Begin
4. -> Insert into t2 (name) values (new. name );
5. -> End //
6. 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?

1. Mysql> \ d //
2. Mysql> create trigger t2 before delete on t1 for each row
3. -> Begin
4. -> Delete from t2 where id = old. id;
5. -> End //
6. 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?

1. Mysql> \ d //
2. Mysql> create trigger t3 before update on t1 for each row
3. -> Begin
4. -> Update t2 set id = new. id where id = old. id;
5. -> End //
6. 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;