Today to introduce six  useful MySQL's SQL statements, many people may have to implement these functions with PHP.
1 calculate the number of years
You want to calculate the birthday person how old.
SELECT DATE_FORMAT (FROM_DAYS (TO_DAYS (now ()) - TO_DAYS (@ dateofbirth)), '% Y') + 0;
2 the difference between the two time
Made the difference between two datetime values​​. Assuming datetime dt1 and dt2 are the type of the format 'yyyy-mm-dd hh: mm: ss', then the number of seconds difference between them is:
UNIX_TIMESP (dt2) - UNIX_TIMESP (dt1)
Divided by 60 is the difference between the number of minutes divided by 3600 is the difference between the number of hours, then divided by 24 is the difference between the number of days.
3 shows a column appeared in the value of N times
SELECT id
FROM tbl
GROUP BY id
HAVING COUNT (*) = N;
4 calculation of working days between two dates
In addition to the so-called day is Saturday, Sunday and holidays.
SELECT COUNT (*)
FROM calendar
WHERE d BETWEEN Start AND Stop
AND DAYOFWEEK (d) NOT IN (1,7)
AND holiday = 0;
5 in a look-up table primary key
SELECT k.column_name
FROM information_schema.table_c ***** traints t
JOIN information_schema.key_column_usage k
USING (c ***** traint_name, table_schema, table_name)
WHERE t.c ***** traint_type = 'PRIMARY KEY'
AND t.table_schema = 'db'
AND t.table_name = tbl '
6 See how 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;
Want to help you.



This address is http://www.computerites.com/database-knowledge/2011/12/share-six-useful-mysql-statements-71.html