on Tips

6 useful MySQL queries

10 comments
Logo-mysql
Tweet about this on TwitterShare on FacebookShare on Google+Share on LinkedInShare on RedditShare on StumbleUpon

The art of query building is the art of using Structured Query Language to formulate correct, efficient database questions and commands. In SELECT queries, you can use JOIN, WHERE and HAVING clauses to scope the result to specific rows and columns, GROUP BY to combine result rows into analytic summaries, and UNION to combine the results of multiple queries. INSERT, DELETE and UPDATE commands may reference JOINs. INSERT … SELECT inserts a query result into another table. DELETEs and UPDATEs may be scoped by WHERE clauses.

1. Age in years

You have a birth date and need to calculate how old is the guy. Assume the @dateofbirth is this date:

SELECT DATE_FORMAT(FROM_DAYS(TO_DAYS(now()) - TO_DAYS(@dateofbirth)), '%Y') + 0; 

2. Difference between two dates

Find the difference between two datetime values in seconds, minutes, hours or days. If dt1 and dt2 are datetime values of the form ‘yyyy-mm-dd hh:mm:ss’, the number of seconds between dt1 and dt2 is

UNIX_TIMESTAMP( dt2 ) - UNIX_TIMESTAMP( dt1 )

To get the number of minutes divide by 60, for the number of hours divide by 3600, and for the number of days, divide by 3600 * 24.

3. Display column values which occur N times

SELECT id
FROM tbl
GROUP BY id
HAVING COUNT(*) = N; 

4. Count business days between two dates

The simplest support for counting business days between any two dates is a calendar table with columns d date and holiday bool populated for all days in all possibly relevant years. Then the following query gives the inclusive number of business days between dates Start and Stop:

SELECT COUNT(*)
FROM calendar
WHERE d BETWEEN Start AND Stop 
  AND DAYOFWEEK(d) NOT IN(1,7)
  AND holiday=0; 

5. Find primary key of the table

SELECT k.column_name 
FROM information_schema.table_constraints t 
JOIN information_schema.key_column_usage k 
USING (constraint_name,table_schema,table_name)
WHERE t.constraint_type='PRIMARY KEY' 
  AND t.table_schema='db'
  AND t.table_name='tbl'

6. Find out how big is your database

SELECT 
  table_schema AS 'Db Name',
  Round( Sum( data_length + index_length ) / 1024 / 1024, 3 ) AS 'Db Size (MB)',
  Round( Sum( data_free ) / 1024 / 1024, 3 ) AS 'Free Space (MB)'
FROM information_schema.tables
GROUP BY table_schema ; 

I hope that this not so common queries will help you out.

Tweet about this on TwitterShare on FacebookShare on Google+Share on LinkedInShare on RedditShare on StumbleUpon



  • Pingback: Tweets that mention 6 useful MySQL queries | CodeForest -- Topsy.com()

  • Very useful, but you should have provided database scheme.

  • I find it interesting that too many times developers depend on PHP to do simple tasks that are easily done with MySQL.

    Performance wise, is it better to use MySQL than PHP for this? Perhaps a good theme for a new tut in PHP mythbusters series? 🙂

  • Could you give an example where the “5. Find primary key of the table” query would be useful?

    It seems like it would be easier, faster, and more convenient to simply just “DESC myTable” and look for the PRI in the key column.

  • Dave

    Thanks! More would be great.

  • Tony Lukasavage

    #4 is not entirely accurate as it doesn’t account for holidays.

  • Pingback: Clearing Old Business Process Instances from LiveCycle Process Management | LiveCycle Product Blog()

  • huarong

    #4
    Where can we get the calendar table ?

  • 24saat

    nice post. twitted. thank you.

  • nacknack

    Great very useful there is so many information that can’t help to web deloveper. May be the author know how make MySQL queries for exemple for dating site man search for woman and woman serch for man it will be intresting…