几个MYSQL中的小TIPS语句

小结一下6个MYSQL 语句中常用的小TIPS

1) 比如要计算某个人现在多大,假设该人的出生年月变量是@dateofbirth ,
则
SELECT DATE_FORMAT(FROM_DAYS(TO_DAYS(now()) - TO_DAYS(@dateofbirth)), '%Y') + 0;

2 计算日期差
假设两个日期格式为yyyy-mm-dd hh:mm:ss,则可以计算相差的秒数
UNIX_TIMESTAMP( dt2 ) - UNIX_TIMESTAMP( dt1 )

然后再去除60,3600,3600*24就可以得到分钟,小时和日了

3 计算某一列的值出现的次数
  SELECT id  FROM tbl  GROUP BY id  HAVING COUNT(*) = N;

4 寻找一个表的主键
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'


5 看一个数据库大小
  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 ;