SQL进阶技巧：日期计算与不常用查询

本文档介绍了SQL在日常工作中一些不常用的有趣和实用功能，主要集中在MySQL数据库环境中。以下是其中几个关键知识点： 1. 计算年数：在MySQL中，如果你想根据用户的出生日期计算当前年龄，可以使用DATE_FORMAT函数和FROM_DAYS、TO_DAYS函数。例如： ``` SELECT DATE_FORMAT(FROM_DAYS(TIMESTAMPDIFF(YEAR, @dateofbirth, NOW())),'%Y')+0; ``` 这段代码首先计算出从出生日期到当前日期的天数差异，然后转换成年数。 2. 两个时间差的计算：对于两个datetime类型的值，可以通过UNIX_TIMESTAMP函数将它们转换为Unix时间戳（秒数），然后相减得到秒数差。如需转换为其他单位（分钟、小时或天数），则需进行相应的除法运算。 3. 统计重复值：查询某列出现特定次数的值，可以用GROUP BY和HAVING子句，如： ``` SELECT id FROM tbl GROUP BY id HAVING COUNT(*) = N; ``` 这将返回id值在表中出现N次的所有记录。 4. 计算工作日：确定两个日期之间的工作日，需要排除周末和节假日，可以借助calendar表来完成。示例代码： ``` SELECT COUNT(*) FROM calendar WHERE date BETWEEN 'Start' AND 'Stop' AND DAYOFWEEK(date) NOT IN (1, 7) -- 排除周六和周日 AND holiday = 0; ``` 5. 查找表的主键：要获取一张表的主键列名，可以使用information_schema系统视图，代码如下： ``` SELECT k.column_name FROM information_schema.table_constraints t JOIN information_schema.key_column_usage k ON t.constraint_name = k.constraint_name AND t.table_schema = 'db' AND t.table_name = 'tbl' WHERE t.constraint_type = 'PRIMARY KEY'; ``` 这会返回指定表的主键列名。 6. 数据库大小监控：最后，你可以通过information_schema系统视图来查看数据库的总体大小和空闲空间： ``` SELECT table_schema AS 'DbName', ROUND(SUM(data_length + index_length) / 1024 / 1024, 3) AS 'DbSize(MB)', ROUND(SUM(data_free) / 1024 / 1024, 3) AS 'FreeSpace(MB)' FROM information_schema.tables GROUP BY table_schema; ``` 这个查询会按数据库名称分组，计算每个数据库的数据占用空间和剩余空间。 这些SQL技巧在日常维护、数据分析和优化中可能会派上用场，尤其是在处理日期、计数和数据库管理任务时。掌握并灵活运用这些技巧，可以帮助你更高效地操作MySQL数据库。