MySQL Learning note _10_mysql advanced operations (bottom)

Source: Internet
Author: User
Tags savepoint

 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. 1. Set              autocommit = 0; #关闭自动提交
  2. 2,delete from T1 where ID > 4;
  3. 3, savepoint p1; #设置还原点
  4. 4,delete from T1;
  5. 5,rollback to p1; #回滚到p1还原点
  6. 6,rollback; #回滚到最原始的还原点
  7. 7,commit ; #提交数据到服务器
  8. 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?
  1. mysql>\d//#修改定界符为//
  2. Mysql>Create procedure p1 ()
  3. begin
  4. set @i = 0;
  5. ->while @i <
  6. Insert into T2 (name) values(concat ( "User"  , @i));
  7. set @i = @i + 1;
  8. andend while;
  9. end;//
  10. 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?
  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) 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?
  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 a trigger t3 , if you modify the table T1 then T2 the records in the update 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 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)

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.