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.
