1 REMAP VALUES INSIDE A QUERY USING CASE…WHEN SYNTAX
2 FIND DUPLICATE RECORDS WITH EMAIL FIELD
SELECT
email,
COUNT
(email)
AS
q
FROM
emails_table
GROUP
BY
HAVING
q > 1
ORDER
BY
q
DESC
3 EXTRACT RECORDS WITH A RANDOM ORDER
SELECT * FROM your_table ORDER BY RAND()
4 RESET THE AUTOINCREMENT COUNTER IN A TABLE
Next record you insert will have id=100.
5 ADD AN AUTOMATIC INCREMENT COLUMN FOR A SELECT
set
@N = 0;
SELECT
@N := @N +1
AS
number, value
FROM
table;
6 JOINING FIELDS WITH CONCAT FUNCTION
SELECT CONCAT(name,’ ‘,surname) AS complete_name FROM users
7 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
8 INSERTING ROWS IGNORING DUPLICATES ON A FIELD WITH UNIQUE KEY
On a table “people” with a unique key on ‘name’ field:
INSERT IGNORE INTO people (name) VALUES (‘john’)
You can run this query many times, no error will be returned.
9 USING FULLTEXT INDEX AND MATCH AGAINST SEARCH
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.
10 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.
11 SET CORRECT CHARSET
1
|
SET NAMES 'utf8' ; |
Run this query after your connection starts. More info about charsets:
How to get correct characters :
-
create fields on db with utf8_general_ci collation
-
make the query ‘set names’ after any connection (look the block code below, you need mysql 5)
-
put meta charset utf8 tag in the html head block
-
read and write strings to db and to html without using utf8_encode or other functions
after connecting to your db make this query:
mysql_query(
"SET NAMES 'utf8';"
);
Sometimes these steps do not work, and you start say f**ck f**ck! Well, it’s also happened to me, and I’ve notice that sometimes php.ini make some problems because it has a wrong default charset setted. So, if this is your problem, put this line at the beginning of your php files: