Mysql Operation Tips

2. Copy table structure + table data
   Mysql> Create tables t2 like T1;
   mysql> INSERT INTO T2 select * from T1;
4. MySQL Index
   A, Alert Table is used to create a normal index, unique unique index (the current column value is not repeatable) or Primary key primary key index
   Mysql> ALTER TABLE table_name ADD index index_name (column_list);
   Mysql> ALTER TABLE table_name add Unique (column_list);
   Mysql> ALTER TABLE table_name add Primary Key (column_list);
   
   B, creat index (non-processing primary key index)
   Mysql> CREATE INDEX index_name on table_name (column_list);
   Mysql> Create Unique Index index_name on table_name (column_list);
   
   C, DROP Index (non-processing primary key index)
   Mysql> DROP INDEX index_name on table_name;
   
   d, ALTER TABLE drop
   Mysql> ALTER TABLE table_name DROP INDEX index_name;
   Mysql> ALTER TABLE table_name drop PRIMARY key;
   
   
6. MySQL View
   Create a view (view can be updated in real-time based on the main table)
   Mysql> CREATE VIEW v_t1 as SELECT * from T1 where id>4 and ID <11;
   Mysql> Show tables;
   Mysql> Show CREATE View v_t1;
   mysql> drop View v_t1;
   
   
8. Mysql built-in functions
   Concat ("string1", "string2")//link string
   LCase (STRING)//Convert lowercase
   UCase (String)//Convert uppercase
   Length (String)//string
   LTrim (String)//Remove space before string
   RTrim (String)//Remove space after string
   Repeat (string,count)//repeat string count times
   Replace SEARCH_STR with REPLACE_STR in replace (STR,SEARCH_STR,REPLACE_STR)//str
   
   
10. MySQL preprocessing statements
    Set STMT1 preprocessing, passing a data as a where judging condition
    mysql> prepare STMT1 from ' select * from T1 where id>? ';
    Set a variable
    Mysql> set @i=5;
    Performing STMT1 preprocessing
    Mysql> execute STMT1 using @i;
    Remove preprocessing STMT1
    mysql> drop prepare STMT1;
    
    
12. MySQL transaction (engine=myisam table engine does not support transaction mechanism)
    to see if the table engine supports
    mysql> show create table T1;
    Set the table engine to INNODB
    mysql> ALTER TABLE T1 ENGINE=INNODB;
    Turn off the autocommit feature
    Mysql> set autocommit=0;
    View current autocommit settings
    mysql> select @ @autocommit;
    Submit
    Mysql> commit;
    A record was removed from table T1
    mysql> Delete from t1 where ID =11;
    At this point, make a P1 restore point
    mysql> savepoint p1;
    Delete a record from table T1 again
    mysql> delete from t1 where ID =10;
    Make a P2 restore point again
    mysql> savepoint p2;
    At this point, restore to P1 restore point, of course, P2 These restore points after the automatic invalidation
    mysql> rollback to P1;
    Return to the original restore point
    Mysql>roolback;
    
    
14. Mysql Storage (batch processing)
    Example: Create a storage P1, create 100 users,
    mysql> \d//execute ";" temporarily replace with "//"
    Mysql> Create Proceduce p1 ()
    ->begin
    ->set @i=1;
    ->while @i<100 do
    ->insert to T1 (name) value (concat ("User", @i));
    ->set @[email protected]+1;
    ->end while;
    ->end//
    mysql> \d;
    View Storage
    Mysql> Show procedure Status\g
    mysql> Show CREATE PROCEDURE p1\g
    Execute
    mysql> call P1 ();
    
    
16. Mysql triggers
    Modify delimiter to//
    Mysql> \d//
    Example: Creating a trigger TG1, when inserting data into a T1 table, the T2 table is also inserted into the data
    Mysql> CREATE trigger TG1 before insert on T1 for each row
    ->begin
    ->insert into T2 (name) values (new.name);
    ->end//
    Mysql> \d;
    View triggers
    Mysql> show triggers;
    Delete Trigger
    mysql> drop trigger TG1;
    Create trigger TG2, t2 table is also deleted when data is deleted to the T1 table
    Mysql> CREATE trigger TG2 before delete on T1 for each row
    ->begin
    ->delete from T2 where Name=old.name;
    ->end//
    
    Create trigger TG3, t2 table is also updated when data is updated to the T1 table
    Mysql> CREATE trigger TG3 before update on T1 for each row
    ->begin
    ->update T2 set name=new.name where name=old.name;
    ->end//
    
    
18. Reflow auto_increment
    Mysql Database Auto-Grow ID recovery reordering
    Mysql> ALTER TABLE TableName auto_increment = 1;
    
    Other:
    Federated queries
    Mysql> SELECT * FROM t1 union select *from T2
    Quick Delete
    Mysql> TRUNCATE TABLE tablename;

Mysql how-to tips