Tags: command line

Bumper tricks

By admin on Jul 2, 2009 | In Daily Commute - Standard hints/tips, Formula 1 - More advanced stuff | Send feedback »

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

... and here's one.
If you want to change the order of a sort (perhaps sort by 1, 4, 3 ,2 instead of 1, 2, 3,4) you can use the field function within the Order by clause.
For example

Order By Field(sort_field,1,4,3,2)

Read more about this extremely useful function HERE Of course you can always use the CASE statement in the ORDER BY clause, but it's always good to have a choice!

Sometimes it's good to know the range of values in your table fields - what the maximum value is, the minimum length stored etc - especially when you're working out what kind of index to implement. In this scenario, the procedure analyse is very useful. In fact it examines the result from a query and returns an analysis of the results. To use it append PROCEDURE ANALYSE to the end of a SELECT statement eg:

Code:

select * from orders procedure analyse(0,0)\G

There's a very useful article on using this when
indexing text columns in mysql here

Tags: command line, dates, tips