Today to introduce six useful MySQL's SQL statements, many people may have to implement these functions with .
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
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 (*)
WHERE d BETWEEN Start AND Stop
AND DAYOFWEEK (d) NOT IN (1,7)
AND holiday = 0;
5 in a look-up table primary key
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
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)';
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