1. Copy the table structure and data, but do not copy the index and foreign key:
Create table a select * from B;
2. Copy the table structure, index, and foreign key without copying data:
Create table a like B;
3. Copy data only:
Insert into a select * from B;
4. Move the table to another database
Rename table a. t to B. t;
5. Delete duplicate records
-- Create a temporary table
Create table tmp as select * from youtable group by name;
-- Delete the original table
Drop table youtable;
-- Rename a table
Alter table tmp rename youtable;
-- Create a temporary table
Create table tmp like youtable;
-- Retrieve non-duplicate data
Insert into select * from youtable group by name;
-- Clear the original table
Truncate youtable;
-- Insert the original table
Insert into youtable select * from tmp;
-- Rename a table
Drop table tmp;
6. Rename the database
To/var/lib/mysql/
Modify the folder Name of the database.
7. Time Format
SELECT FROM_UNIXTIME (1249488000, '% Y-% m-% d % H: % I: % s ');
SELECT DATE_FORMAT ('2017-10-04 22:23:00 ',' % Y-% m-% d % H: % I: % s ');
SELECT UNIX_TIMESTAMP ('2017-08-06 ');
8. mysql Log
-- View logs
Show binary logs;
Show master logs;
-- Clear logs
Purge master logs to 'mysql-bin.000035 ';
-- Manually delete mysql binlog logs 10 days ago
Purge master logs before DATE_SUB (CURRENT_DATE, INTERVAL 10 DAY );
9. Obtain the number of update records
Select ROW_COUNT ();
10. Obtain the number of records found
Select FOUND_ROWS ();
11. Get the inserted id
Select LAST_INSERT_ID ();
12. Create a special table name
SET SQL _mode = 'ansi _ QUOTES ';
Create table "a-B" (a int );
13. insert non-duplicate data
Insert into node (name) select 'A' where no exists (select id from node where id = 2 and name = 'A ')
14. uuid
Select replace (uuid (),'-','');
15. Add a remote user named username and password
Grant all privileges on *. * TO username @ "%" identified by 'Password' with grant option;
16. import data from a file
Load data infile '/tmp/result100.txt' into table analy_ip_file2 fields terminated by ', 'enabledby' "'Lines terminated by '\ n ';
17. Add a primary key
Alter table userconfig add id int (4) auto_increment primary key;
18. View mysql Parameters
Show variables like '% max % ';
End
This article is from the "one party" blog