MySQL数据库常用语法查询第一期_mysql查询每小时的数据-优快云博客

本文链接：https://blog.youkuaiyun.com/m0_72638029/article/details/142502298

前言

作为软件测试工程师，我们常用的是查询操作以下是我工作中处理的一些问题，非基础教程分享，是工作经验分享。

一、查询操作：

1、以每个小时为单位分组查询出每个小时的用户注册量（group by hour：分组。 COUNT(*)：聚合函数统计总数）

问题：当天注册用户量异常时，我们需要排查到是那个时间段出现了问题可以使用SQL统计每个小时注册的用户量进行分析。

查询出2024年1月5号每个小时男士的注册量

select HOUR(create_date) as hour,count(*) as count from member 
where date(create_date) = '2024-01-05' group by hour;

查询出2024年1月5号每个小时总用户注册量大于6的时间段

SELECT HOUR(create_date) AS hour, COUNT(*) AS count  
FROM member  
WHERE DATE(create_date) = '2024-01-05' 
GROUP BY HOUR(create_date)  
HAVING count > 6;

讲解

SELECT HOUR(create_date) AS hour, COUNT(*) AS count：
- HOUR(create_date)：提取create_date字段的小时部分，结果命名为hour。
- COUNT(*) AS count：统计每个小时内的记录数，即每个小时内创建了多少个用户，结果命名为count。
FROM member：
- 数据来自member表。
WHERE DATE(create_date) = '2024-01-05'：
- 筛选出创建日期为2024年1月5日的用户记录。
GROUP BY HOUR(create_date)：
- 按照创建时间的小时部分分组，分别统计每个小时段内的用户数量。
HAVING count > 6：
- 过滤掉用户数小于或等于6的小时段，只显示用户数大于6的小时段。

`WHERE` 与 `HAVING` 的区别：

WHERE：用于在数据分组之前筛选记录。它过滤的是原始表中的数据。也就是说，WHERE子句决定了哪些行会进入分组过程。比如在本例中，WHERE DATE(create_date) = '2024-01-05'限制了只考虑创建日期为2024年1月5日的记录。
HAVING：用于在分组之后对结果集进行过滤。与WHERE不同，HAVING子句处理的是已经被分组后的聚合结果。只有聚合后的数据才能使用HAVING来进行筛选。在这个查询中，HAVING count > 6是用来过滤掉那些统计结果小于或等于6的小时段。

为什么使用 `HAVING` 而不是 `WHERE`？

WHERE 不能用于过滤聚合函数（如COUNT()、SUM()等）的结果，因为聚合函数是在GROUP BY之后计算的。而 HAVING 是专门为过滤聚合结果设计的子句，因此在统计每个小时段用户数量之后，使用 HAVING 来筛选符合条件的小时段。

2、模糊查询（%：0——无限个任意字符。 _：任意一个字符）

需要导出符合自己创建号段手机号的男用户进行接口批量操作发送消息，此时需要通过模糊搜索匹配手机号开头包含136111111的用户。并且只需要导出手机号、id、no，login_name四个字段。

SELECT id, phone, no, login_name FROM `member` 
WHERE  sex = 1 and `phone` LIKE '136111111%' LIMIT 100;

讲解

1. `SELECT id, phone, no, login_name`：

从 member 表中选择四列数据：id、phone（电话）、no（编号）和 login_name（登录名）。

2. `FROM member`：

数据来源于 member 表。

3. `WHERE sex = 1`：

查询条件是 sex = 1，假设表示性别为男性（具体含义取决于数据库定义，通常 1 表示男性，0 表示女性）。

4. `AND phone LIKE '136111111%'`：

phone 字段的值必须匹配 LIKE '136111111%' 的模式。
LIKE 用于字符串模式匹配，'136111111%' 表示查询电话号码以“136111111”开头的记录。

5. `LIMIT 100`：

限制返回的记录数为最多 100 条

3、为空和不为空查询

问题：查询上线后父渠道为空的脏数据数据有多少个用户在想办法处理

SELECT COUNT(1) FROM `member_extend`  
WHERE `parent_source_type` IS NULL OR `parent_source_type` = '';

SELECT COUNT(1)：
- 返回满足条件的记录总数。COUNT(1) 和 COUNT(*) 没有本质区别，都是统计符合条件的记录数。
FROM member_extend：
- 数据来自 member_extend 表。
WHERE parent_source_type IS NULL OR parent_source_type = ''：
- 筛选 parent_source_type 为空 (NULL) 或者等于空字符串 ('') 的记录。

查询生产环境有多少个用户的邮箱不是空的，确定数量。

SELECT COUNT(*) AS total FROM member  
WHERE email IS not NULL;

SELECT COUNT(*) AS total：
- 返回满足条件的记录总数。COUNT(*) 统计所有符合条件的记录并命名为 total。
FROM member：
- 数据来自 member 表。
WHERE email IS NOT NULL：
- 筛选 email 不为 NULL 的记录。

讲解

注意事项：

NULL 和空字符串的区别：
- NULL：表示没有值，即字段未存储任何值（为空）。
- 空字符串 ''：表示字段有值，但该值为空字符串，通常意味着字段有内容但没有实际意义。
- 这两者在查询时要区别对待，因为它们的存储方式不同。例如，第一条 SQL 同时查询 NULL 和空字符串。
查询时要注意的 SQL 逻辑：
- = 和 != 不能用于 NULL 比较：NULL 是特殊值，不能使用 =、!= 进行比较。必须使用 IS NULL 或 IS NOT NULL 来判断是否为 NULL。
- 使用组合条件时要小心：当字段既可能为 NULL 又可能为空字符串时，必须同时处理这两种情况，像第一条 SQL 使用了 OR 来处理这种组合。

4、子查询和连表查询

适用于查询少量数据时不需要连表操作。查询两个表方便快捷。

问题：测试用户L1219066、M0746644、M0098034没有收到站外消息推送，此时需要检查device_token是否连接上，好进一步确认问题。

SELECT `id`, `member_id`, `device_token` FROM `member_extend` 
WHERE `member_id` IN (SELECT `id` FROM `member` WHERE `no` IN 
("L1219066","M0746644","M0098034"));

SELECT id, member_id, device_token：
- 从 member_extend 表中选择这三列：id、member_id 和 device_token。
FROM member_extend：
- 查询的数据来源于 member_extend 表。
WHERE member_id IN (SELECT id FROM member WHERE no IN ("L1219066","M0746644","M0098034"))：
- member_id 必须是从 member 表中查询到的 id，而这些 id 对应的 no 是 "L1219066"、"M0746644" 和 "M0098034"。
- 内部的子查询 SELECT id FROM member WHERE no IN ("L1219066", "M0746644", "M0098034") 从 member 表中获取符合条件的用户的 id，然后这些 id 被用于外部查询中的 WHERE member_id IN (...)。

问题：测试主播数据统计需要导出公会主播的用户id、no、以及手机号跑接口进行批量数据操作

SELECT a.`id`, a.`no`, a.`phone`, v.`type`
FROM `member` a
JOIN `member_anchor` v ON a.`id` = v.`member_id`
WHERE v.`type` = 1
ORDER BY a.`id` DESC;

1. `SELECT a.id, a.no, a.phone, v.type`：

从两个表中选择 member 表的 id、no、phone 字段，以及 member_anchor 表的 type 字段。
a 是表 member 的别名，v 是表 member_anchor 的别名。

2. `FROM member a`：

从 member 表中检索数据，表使用了别名 a。

3. `JOIN member_anchor v ON a.id = v.member_id`：

使用 JOIN 语法进行表连接，其中 a.id（member 表的 id 字段）与 v.member_id（member_anchor 表的 member_id 字段）进行匹配连接。
这意味着查询的结果只包含 member 和 member_anchor 表中 id 和 member_id 相等的记录。