MySQL-常用sql

简介

记录常用的sql

如果想按照月来进行分组并统计 count，可以使用 DATE_FORMAT() 或数据库支持的类似函数，将时间字段转换为“年-月”的格式。以 MySQL 为例，按月分组可以这样做：

SELECT DATE_FORMAT(created_at, ‘%Y-%m’) , COUNT(*) AS count
FROM your_table
GROUP BY DATE_FORMAT(created_at, ‘%Y-%m’) ;

解释：

•    DATE_FORMAT(created_at, '%Y-%m')：将 created_at 转换为 “年-月” 格式（如 2024-10），只保留年和月部分。
•    COUNT(*)：统计每个月的记录数。
•    GROUP BY year_month：按转换后的 “年-月” 进行分组。
•    ORDER BY year_month：按 “年-月” 排序输出。

如果使用 PostgreSQL，类似操作可以用 TO_CHAR() 函数：

SELECT TO_CHAR(created_at, ‘YYYY-MM’) AS year_month, COUNT(*) AS count
FROM your_table
GROUP BY year_month
ORDER BY year_month;

这会按月统计每个月的记录数，并按时间顺序返回。
要按时间字段按天进行 GROUP BY 操作并统计 count，可以使用 SQL 语句中的 DATE() 函数，将时间字段转换为日期（去掉时分秒部分），然后按天进行分组。以下是一个简单的 SQL 例子，假设表名为 your_table，时间字段为 created_at：

SELECT DATE(created_at) AS date, COUNT(*) AS count
FROM your_table
GROUP BY DATE(created_at)
ORDER BY date;

解释：

•    DATE(created_at)：将 created_at 转换为日期，去掉时分秒。
•    COUNT(*)：统计每一天的记录数。
•    GROUP BY DATE(created_at)：按转换后的日期字段进行分组。
•    ORDER BY date：按日期排序输出。

这样，每一天的记录数将会被统计出来并按日期显示。
DATE_FORMAT 函数解释：

在 MySQL 中，DATE_FORMAT() 是一个非常有用的日期格式化函数，它允许你将日期或日期时间类型的数据转换为指定的字符串格式。该函数的基本语法如下：

DATE_FORMAT(date, format);

其中：

• date：是要格式化的日期或日期时间类型的数据，可以是一个日期列、日期函数的结果或者一个日期值。
• format：是一个包含格式化说明符的字符串，用于指定日期或时间的输出格式。

常用的日期和时间格式化说明符：

• %Y：四位数字表示的年份，例如：2024。
• %y：两位数字表示的年份，例如：24。
• %m：月份，使用两位数字表示，从 01 到 12。
• %c：月份，使用数字表示，从 1 到 12。
• %M：月份的英文全称，例如：January、February 等。
• %b 或 %h：月份的英文缩写，例如：Jan、Feb 等。
• %d：月份中的天，使用两位数字表示，从 01 到 31。
• %e：月份中的天，使用数字表示，从 1 到 31。
• %D：带有英文后缀的月份中的天，例如：1st、2nd、3rd 等。
• %j：一年中的第几天，从 001 到 366。
• %W：星期几的英文全称，例如：Monday、Tuesday 等。
• %a 或 %w：星期几的英文缩写，例如：Mon、Tue 等。
• %H：24 小时制的小时数，从 00 到 23。
• %h 或 %I：12 小时制的小时数，从 01 到 12。
• %i：分钟数，使用两位数字表示，从 00 到 59。
• %s：秒数，使用两位数字表示，从 00 到 59。
• %p：AM 或 PM。

示例使用：
假设你有一个表 orders，其中包含一个 order_date 列，其存储的是订单日期，你可以使用 DATE_FORMAT 函数将其转换为不同的格式，以下是一些示例：

1. 将日期格式化为 YYYY-MM-DD：

   SELECT DATE_FORMAT(order_date, '%Y-%m-%d') AS formatted_date FROM orders;

2. 将日期格式化为 YYYY-MM-DD HH:MM:SS：

   SELECT DATE_FORMAT(order_date, '%Y-%m-%d %H:%i:%s') AS formatted_date_time FROM orders;

3. 将日期格式化为 Month Day, Year：

   SELECT DATE_FORMAT(order_date, '%M %d, %Y') AS formatted_date FROM orders;

注意事项：

• DATE_FORMAT 函数仅适用于日期或日期时间类型的数据，如果你尝试将其应用于非日期类型的数据，可能会得到意外的结果或错误。
• 不同的数据库系统可能具有不同的日期格式化函数或使用不同的格式化说明符。如果你使用的是 PostgreSQL，可以使用 TO_CHAR() 函数；对于 Oracle，可以使用 TO_CHAR() 函数；对于 SQL Server，可以使用 CONVERT() 或 FORMAT() 函数。因此，在将 SQL 代码迁移到其他数据库时，需要注意这些差异。
• 在 GROUP BY 或 ORDER BY 子句中使用 DATE_FORMAT 生成的结果时，确保在这些子句中使用相同的格式，以保证分组或排序的一致性。

通过使用 DATE_FORMAT 函数，你可以灵活地将日期数据以各种方式进行展示，满足不同的业务需求，如报表生成、数据导出等。你可以根据实际的业务场景和所需的日期显示效果，选择合适的格式化说明符组合。