MySQL 13 tips for using

MySQL as one of the most successful open source relational database, has a large number of fans (I also), in this article, we carefully collected 10 of the most practical MySQL query skills, hoping to bring everyone a surprise, if you have very good SQL, Please leave a message to share with us!

Related reading:SQL,MYSQL

UseCase to redefine the numeric type

SELECT ID,title,  

 (' 0000-00-00 'END) as date     

From your_table

 5.    select id< Span lang= "en-US" >,title 

 6.         (case status when < Span lang= "en-US" >0 then  ' open '  when 1 Then  "close"  ELSE < Span lang= "en-us" > ' standby '  end)  as status      

From your_table

Look for duplicateEmail Record field

SELECT email, COUNT(email) as q     

>1

ORDER by Q DESC

Take out a random order to get a record

* FROM  your_table ORDER by RAND() 

Useupdate replaces the characters in the specified field

UPDATE your_table

SET name=REPLACE(name,' gbin1.com ',' gbtags.com ')  

'%john% ';

Resets the specifiedAuto-increment value in table

=2

The next time you insert data, the ID automatically increases to 2

ForSELECT statement to add an automatically added field column

 1.    set @n < Span lang= "en-US" >= 0            

@N: =@N+1 as number, name, surname from Gbtags_users; 

Useconcat to connect fields

SELECT CONCAT(name,",surname) as Complete_name from users  

use The date method to remove the specified part of a date type

SELECT ID,title, year(Date_field) from your_table    

SELECT ID,title,  

 3.       concat (month  (date_field '/' , year (date_field< Span class= "pun" > as New_date         

From your_table

The above statement, the first select takes out the year, the second takes out the month and the year combination

Forduplicate insert for unique key (unique key ) Type field does not error insert statement

(tag)(' good ');    

This INSERT statement can be executed multiple times without error, and repeated insertions are ignored

Use full-text indexing and match search

 1.    select * < Span lang= "en-US" >from articles where< Span class= "PLN" > Match ( content_column)  Against  ( ' music ' < Span lang= "en-us" >)               

You need to first add the full-text search index to the specified column (content_column). Note that if you already have data in the table, the index is not created, so you need to use an empty table to execute the statement

If the query was one months ago

SELECT user, Count(*) as logins   

From Stat_log

 3.       where action  = ' LOGIN '  and Dt_when >= Date_add  (curdate (),  INTERVAL -1 Month)    

GROUP by user

This statement allows you to query fields dt_when all records from one months ago

Setting the correct character set

' UTF8 ';

Please execute this statement after the connection has started

Insert another table from one table

(field1,field2,field3)     

SELECT newfield1,newfield2,' fixed value '   

From Yourtable2

This statement can quickly help you to quickly add specific content from one table to another table.

MySQL 13 tips for use (RPM)