home

Search A2Z 24

MySQL queries & tips

Here is a list of tips that can be usefull for your queries.

ADD AN AUTOMATIC INCREMENT COLUMN FOR A SELECT

SET @N = 0; 
SELECT @N := @N +1 AS number, name, surname FROM people; 

REMAP VALUES INSIDE A QUERY USING CASE…WHEN SYNTAX

SELECT id,title, (CASE date WHEN '0000-00-00' THEN '' ELSE date END) AS date FROM your_table  SELECT id,title, (CASE status WHEN 0 THEN 'open' WHEN 1 THEN 'close' ELSE 'standby' END) AS status  FROM your_table

FIND DUPLICATE RECORDS WITH EMAIL FIELD

SELECT email, COUNT(email) AS q  FROM emails_table GROUP BY email HAVING q > 1  ORDER BY q DESC

EXTRACT RECORDS WITH A RANDOM ORDER

SELECT * FROM your_table ORDER BY RAND()

REPLACE STRINGS IN A FIELD WITH AN UPDATE

UPDATE your_table  SET name=REPLACE(name, 'John', 'Johnny') WHERE name LIKE '%John%'; 

RESET THE AUTOINCREMENT COUNTER IN A TABLE

ALTER TABLE your_table AUTO_INCREMENT = 100 

Next record you insert will have id=100.

Note : Sometime you cannot reset the counter to a value because table may have already been used the value which you want set.

JOINING FIELDS WITH CONCAT FUNCTION

SELECT CONCAT(name,' ',surname) AS complete_name FROM users 

SELECT PARTIAL DATE VALUES WITH DATE FUNCTIONS

SELECT id,title, YEAR(date_field) FROM your_table SELECT id,title,  CONCAT(MONTH(date_field),'/',YEAR(date_field)) as new_date FROM your_table 

INSERTING ROWS IGNORING DUPLICATES ON A FIELD WITH UNIQUE KEY

On a table “tags” with a unique key on ‘tag’ field:

INSERT IGNORE INTO tags (tag) VALUES ('good'); 

You can run this query many times, no error will be returned.

USING FULLTEXT INDEX AND MATCH AGAINST SEARCH

SELECT * FROM articles WHERE MATCH(content_column) AGAINST ('music') 

To work, this tip, need to add the full text index on the content_column. Note that if you already have a table filled with data, adding the index will not create it… so you have to start from an empty table.

 HOW TO SAY “ONE MONTH AGO” IN MYSQL

SELECT user, count(*) AS logins  FROM stat_log  WHERE action='LOGIN' AND dt_when >= DATE_ADD(CURDATE(), INTERVAL -1 MONTH) GROUP BY user 

This where clause with dt_when lets you count the records that has date greater or equal to one month ago.

SET CORRECT CHARSET

SET NAMES 'utf8'; 

Run this query after your connection starts. More info here.

INSERTING FROM A TABLE TO ANOTHER

INSERT INTO yourtable (field1,field2,field3)  SELECT newfield1,newfield2,'fixed value' FROM yourtable2 

You can use this construct to copy rows from a table to another and add also some values that you specify in the second part of the query.

About Author

by Admin
loading...

Share your thoughts!

Login as a member to access comment posting block !! click-here

Thoughts From Other Users (0)

No Comments

Reveal Modal Goodness

This is a default modal in all its glory, but any of the styles here can easily be changed in the CSS.

This is just a simple modal with the default styles, but any type of content can live in here. Lorem ipsum dolor sit amet, consectetur adipiscing elit. Morbi quis sem vel enim eleifend tristique. Etiam tincidunt faucibus pharetra.

×