几个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 ;
