Command line

mysql -u root -p root -D test -e 'show tables from test;'

Query for the command line

show columns from table_name;
show tables;
show full processlist (load)

Query to get inspiration from

-- ON DUPLICATE
INSERT INTO table (id, time) VALUES (1,1),(2,2),...,(10,10) ON DUPLICATE KEY UPDATE time=VALUES (time)

-- ORDER BY FIELD
SELECT * FROM medias_contributions WHERE marker IN ('media:a','media:b','media:c') ORDER BY FIELD (marker, 'media:c', 'media:a', 'media:b')

-- LAST_INSERT_ID
UPDATE table SET num=num+10, id=LAST_INSERT_ID(...)

-- REGEX
SELECT * FROM animal WHERE nom REGEXP "^.{5}$";

-- NATURAL SORTING TRICK
SELECT names FROM your_table ORDER BY names + 0, name

-- COUNT ROW (this is not more efficient than two queries)
SELECT SQL_CALC_FOUND_ROWS t1.* (count les row malgre le limit) puis SELECT FOUND_ROWS()

-- DATES
UPDATE events SET date_starts = DATE_ADD(date_starts, INTERVAL 14 DAY)
update contributions set updated = adddate(updated,interval 3 day),  created = adddate(created,interval 3 day), published = adddate(published,interval 3 day) where id > 40000

-- GROUP CONCAT
SET [GLOBAL | SESSION] group_concat_max_len = val;
select group_concat(images.id order by images.id, ','), contributions.id from images join contributions on images.object_id = contributions.id where (object_type = 1 or object_type is null) and place = 1 group by contributions.id having count(*) > 1

Geolocation

SET @latitude := 48.890980579936;
SET @longitude := 2.4131774995498;
SELECT city_name,
(6366*acos(cos(radians(@latitude))*cos(radians(`city_latitude_deg`))*cos(radians(`city_longitude_deg`) -radians(@longitude))+sin(radians(@latitude))*sin(radians(`city_latitude_deg`)))) AS dist
FROM cities_france
HAVING dist <= 10
ORDER by dist ASC

Variables in SQL

$sql = "SET @rank := 0;";
$sql = '/* update_skill update */ UPDATE skill SET skill = 100-((100*(bayesian_time_taken-'.$data['min'].'))/('.$data['max'].'-'.$data['min'].')), rank = greatest(0,@rank := @rank + 1) ORDER BY bayesian_time_taken ASC';

Count and date

SELECT 'Semaine 1', COUNT(*) FROM Ads WHERE FROM_UNIXTIME(createdAt) BETWEEN DATE_ADD('2014-12-29 00:00', INTERVAL 0*7 DAY) AND DATE_ADD('2014-12-29 00:00', INTERVAL 1*7 DAY) -- UNION ... etc
 SELECT
 	extract(week from FROM_UNIXTIME(createdAt)),         
 	extract(year from FROM_UNIXTIME(createdAt)),        
 count(*) FROM Ads GROUP BY
 	extract(week from FROM_UNIXTIME(createdAt)),           
 	extract(year from FROM_UNIXTIME(createdAt))
 ;

Perf check

EXPLAIN SELECT SQL_NO_CACHE

Type Storage Minimum Value Maximum Value
  (Bytes) (Signed/Unsigned) (Signed/Unsigned)
TINYINT 1 -128 127
    0 255
SMALLINT 2 -32768 32767
    0 65535
MEDIUMINT 3 -8388608 8388607
    0 16777215
INT 4 -2147483648 2147483647
    0 4294967295
BIGINT 8 -9223372036854775808 9223372036854775807
    0 18446744073709551615