工作中常用的 MySQL 实用语句速查

常用 MySQL 实用 SQL 操作

在处理数据时，很多逻辑可以直接通过 SQL 语句在数据库层解决，从而减轻后端应用（如 PHP、Python）的计算压力。以下整理了几个常见且实用的场景。

第1步：计算周岁年龄

目的：根据出生日期字段快速计算当前年龄。

操作：使用 TO_DAYS 函数将日期转换为天数相减，再用 DATE_FORMAT 提取年份部分并转为数值。

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

注意：@dateofbirth 应替换为实际的生日列名或变量。

第2步：计算两个时间点的差值

目的：获取两个 datetime 类型字段之间的秒、分钟、小时或天数差异。

操作：利用 UNIX_TIMESTAMP() 将时间戳转换为整数进行减法运算。

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

第3步：显示某一列出现 N 次的值

目的：筛选出特定字段值恰好出现指定次数的记录，常用于去重分析或统计频次。

操作：结合 GROUP BY 和 HAVING 子句实现。

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

其中 N 为目标出现的次数，tbl 为表名，id 为需判断的唯一性字段。

第4步：计算两个日子间的工作日数量

目的：排除周末（周六、周日）和节假日，计算业务时间段内的实际工作日天数。

准备条件：系统中需存在包含日期信息的日历表（如 calendar），该表应包含日期列 d、开始日期 Start、结束日期 Stop 以及是否节假日的标志 holiday（通常 0 表示非假期）。

操作：

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

说明：DAYOFWEEK() 返回的 1 代表周日，7 代表周六，因此排除这两个值即可过滤周末。

第5步：查找表中的主键定义

目的：在不依赖 ORM 或外部工具的情况下，通过 SQL 查询确定某张表的主键列名。

操作：连接 information_schema 系统中的约束表和键列使用表进行查询。

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';  -- 替换为你的表名

第6步：查看数据库占用空间大小

目的：监控数据库实例的整体存储使用情况，包括数据大小、索引大小及空闲空间。

操作：查询 information_schema.tables 并按 schema 汇总。

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;

此查询有助于评估是否需要清理碎片或扩展存储空间。

总结

以上语句覆盖了日常运维与开发中常见的数据处理需求。熟练掌握这些 SQL 片段，能够显著提升排查问题和优化数据的效率。以上就是工作中常用的 mysql 语句分享的详细内容，更多关于 mysql 学习资料的数据请关注本站其它相关文章！