Mysql Common Statement _ MySQL

Common mysql statements: bitsCN.com

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

BitsCN.com