I’m going to list on this thread, those little tricks and tips that I’ve come across over time that really help when using Mysql.
I will probably add to this thread as I find out more.

For example, if you’re in command line Mysql, you can show the DDL for a table quite easily:

Code:

show create table table_name;

A useful tip when running sql queries (again in command line mode) is to terminate a query with \G switch instead of a semi-colon. This displays records in row format rather than columns

Normally Mysql queries are case insensitive – if you search for a field containing ‘%king%’, the query will bring back results where the field contains ‘King’, ‘kinG’ or even ‘KING’
To try and force Mysql to be case sensitive use the ‘binary’ keyword – for example

Code:

Select mainfield from T1Name where binary mainfield like '%King%'

If you are performing date operations on a datetime field, use the date function, for example:

Code:

select dateposted from entries where date(dateposted) = '2008-04-14' \G

Lastly, if you want to break down the number of orders recorded against different countries into discrete groupings of price, the following code is very useful:

Code:

SELECT 5*FLOOR(productprice/5),
  SUM(CASE WHEN cnty = 'UK' THEN 1 ELSE 0 END) as UK,
  SUM(CASE WHEN cnty = 'FR' THEN 1 ELSE 0 END) as FR
FROM orders o
WHERE o.cnty IN ('UK','FR')
GROUP BY 5*FLOOR(productprice/5);

…More tips to follow soon

Original post blogged on b2evolution.