MySQL数据库常用SQL语句：计算年龄、时间差、统计与查询

1. 利用日期函数计算年龄

在处理用户信息时，经常需要根据出生日期（Date of Birth）自动计算当前年龄。MySQL提供了日期转换函数，可以通过计算总天数并格式化输出来实现。

假设我们有一个变量 @dateofbirth 存储用户的生日，以下语句可计算出精确到年的年龄：

SELECT DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW()) - TO_DAYS(@dateofbirth)), '%Y') + 0;

该逻辑的核心在于将日期转换为自公元元年以来的天数差，再重新格式化为年份字符串，最后通过加 0 将其强制转换为数值类型，以便后续进行数值比较或计算。

2. 计算两个时间点的时间差

当需要计算两个 DATETIME 类型字段之间的秒、分、小时或天数差异时，UNIX_TIMESTAMP() 函数是最高效的工具。它将时间戳转换为自 Unix 纪元（1970-01-01 00:00:00 UTC）以来的秒数。

假设 dt1 为开始时间，dt2 为结束时间：

• 秒数差：UNIX_TIMESTAMP(dt2) - UNIX_TIMESTAMP(dt1)
• 分钟数差：秒数差除以 60
• 小时数差：分钟数差除以 60，即秒数差除以 3600
• 天数差：小时数差除以 24

这种基于整数的除法运算避免了复杂的时间函数嵌套，适用于大多数精确到分钟的日志分析场景。

3. 查找某列出现特定次数的记录

在进行数据去重或异常检测时，可能需要找出某个字段出现恰好 N 次的值。这可以通过 GROUP BY 结合 HAVING 子句轻松实现。

SELECT id
FROM tbl
GROUP BY id
HAVING COUNT(*) = N;

此语句首先按 id 分组统计每组的行数，然后过滤出组内数量等于指定参数 N 的记录。这在处理重复数据清洗或筛选特定频率的事件时非常有用。

4. 计算两个日期间的工作日天数

在业务报表中，计算有效工作日（排除周末和法定节假日）是一个常见需求。如果系统中存在一张包含所有日期信息的辅助表 calendar，其中记录了是否为假期（holiday=0），可以使用以下查询：

SELECT COUNT(*)
FROM calendar
WHERE d BETWEEN Start AND Stop
AND DAYOFWEEK(d) NOT IN(1, 7)
AND holiday = 0;

解析如下：

• BETWEEN Start AND Stop：限定日期范围。
• DAYOFWEEK(d) NOT IN (1, 7)：DAYOFWEEK() 返回 1 代表周日，7 代表周六，排除这两者即可去除周末。
• holiday = 0：确保未计入标记为假期的日子。

若没有现成的日历表，通常需要在应用层或存储过程中通过循环生成日期序列进行比对，或使用更复杂的递归 CTE 方案。

5. 查询表中的主键约束信息

当需要了解某张表的主键定义，或者在其他表中引用该主键时，可以直接查询 MySQL 的系统视图 information_schema。以下示例展示了如何从 db 数据库中查找名为 tbl 的表的主键列名：

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';

这里连接了 table_constraints（约束定义）和 key_column_usage（列使用情况）两张元数据表，通过匹配约束类型为 'PRIMARY KEY' 来获取具体的列名 column_name。

6. 查看数据库整体占用空间

对于数据库管理员而言，监控各个 Schema（数据库）的数据文件大小和索引大小至关重要。以下 SQL 语句可以汇总显示每个数据库的名称、总大小（MB）以及空闲空间（MB）：

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;

关键点说明：

• data_length：数据占用的字节数。
• index_length：索引占用的字节数。
• data_free：分配给表但未使用的字节数（通常由删除操作或行锁定产生碎片）。
• 除以 1024 * 1024 是将字节转换为兆字节（MB），ROUND(..., 3) 保留三位小数以保证精度。

总结

上述六条 SQL 语句涵盖了从基础的数据查询、时间处理到高级的系统元数据读取和空间管理。熟练运用 information_schema 系统表以及内置日期函数，可以帮助开发者更高效地解决日常开发和维护中的实际问题。以上就是六条比较有用的MySQL数据库操作的SQL语句小结的详细内容，更多关于MySQL数据库实用技巧的资料请关注本站其它相关文章！