The arrangement of sentences commonly used in mysql

This article mainly explains "the arrangement of sentences commonly used in mysql". Interested friends may wish to have a look at it. The method introduced in this paper is simple, fast and practical. Next let the editor to take you to learn "mysql commonly used sentence arrangement" bar!

1. Copy the table structure and data, but not the indexes and foreign keys:

Create table a select * from b

two。 Copy table structure and indexes and foreign keys, without replicating data:

Create table a like b

3. Copy only data:

Insert into a select * from b

4. Move the table to another library

Rename table a.t to b.t

5. Delete duplicate record

Create a new temporary table

Create table tmp as select * from youtable group by name

-- delete the original table

Drop table youtable

-- renaming table

Alter table tmp rename youtable

Create a new temporary table

Create table tmp like youtable

-- take out data that is not duplicated

Insert into select * from youtable group by name

-- empty the original watch

Truncate youtable

-- insert the original table

Insert into youtable select * from tmp

-- renaming table

Drop table tmp

6. Rename the database

To / var/lib/mysql/

Just change the folder name of the corresponding database

7. Time format

SELECT FROM_UNIXTIME (1249488000)% YMUE% MMI% d% HRV% iRV% s')

SELECT DATE_FORMAT ('1997-10-04 22 SELECT DATE_FORMAT,'% Y-%m-%d% HV% iRO% s')

SELECT UNIX_TIMESTAMP ('2009-08-06')

8.mysql log

-- check the log

Show binary logs

Show master logs

-- clear log

PURGE MASTER LOGS TO 'mysql-bin.000035'

-- manually delete mysql binlog logs from 10 days ago

PURGE MASTER LOGS BEFORE DATE_SUB (CURRENT_DATE, INTERVAL 10 DAY)

9. Get the number of update records

Select ROW_COUNT ()

10. Get the number of records found

Select FOUND_ROWS ()

11. Get the inserted id

Select LAST_INSERT_ID ()

twelve。 Create a special table name

SET sql_mode='ANSI_QUOTES'

Create table "a murb" (an int)

13. Insert non-duplicated 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 with password password

GRANT ALL PRIVILEGES ON *. * TO "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', 'ENCLOSED BY' "'LINES TERMINATED BY'\ n'

17. Add primary key

Alter table userconfig add id int (4) auto_increment primary key

18. View parameters

Show variables like'% max%'

End

At this point, I believe that everyone on the "mysql commonly used sentence arrangement" have a deeper understanding, might as well to the actual operation of it! Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!