mysql查询实践,有图有真相

作为一个知识码农，mysql查询技能是必须的，现罗列一些常用的语句和写法，以供参考。

示例的表说明：
表一 tb_visit_info 表
说明: 描述设备在时间段的访问信息

表二 tb_device_error_info 表

说明: 描述设备在时间段的访问错误统计

1 查看表数据 tb_visit_info

SELECT * FROM tb_visit_info

2 查看表数据 tb_device_error_info

SELECT * FROM tb_device_error_info

3 分组汇总

针对日期：2018-09-28，根据device_id做了访问总次数的汇总

SELECT device_id,app_id,date,
SUM(visit_num) AS total from tb_visit_info
where date='2018-09-28'
GROUP BY device_id

4 CASE 语句

小技巧：CASE语句：可以动态进行字段二次处理，例如：

SELECT CASE
WHEN total < 20 then "访问量少"
ELSE    "访问量达标"
END AS visit_label,total,device_id,date
FROM (SELECT device_id,app_id,date,
SUM(visit_num) AS total from tb_visit_info
where date='2018-09-28'
GROUP BY device_id) t1

上述语句利用CASE创建了新的字段visit_label，如下图：

5 错误数的汇总

下述语句对于设备做了错误数的汇总求和。

SELECT device_id,date,SUM(error_num)
from tb_device_error_info where date=
'2018-09-28' GROUP BY device_id

6 错误率的计算

将之前的俩个查询分别作为子表，进行错误率的计算，如下：

SELECT t1.device_id,t1.date,
t1.total_error/t2.total From
(SELECT device_id,date,SUM(error_num)
total_error from tb_device_error_info where
date='2018-09-28' GROUP BY device_id)
AS t1,
(SELECT device_id,app_id,date,SUM(visit_num)
AS total from tb_visit_info where date=
'2018-09-28' GROUP BY device_id)
AS t2
where t1.device_id=
t2.device_id and t1.date=t2.date

7 左连接

左连接示例

SELECT * from tb_visit_info t1 LEFT JOIN 
tb_device_error_info t2 ON  t1.device_id=
t2.device_id AND t1.date=t2.date

为了演示方便，从下图可以看出，在满足连接的条件下，设备在满足条件的情况下，对于不同小时段的记录，进行了连接操作，记录条数最后是倍乘的关系。

左连接的定义：在满足连接的条件下记录进行合并，而且，如果左面的表有记录，但是右表没有满足条件的记录，自动补NULL，如上图，结果中最下面那行，就有补NULL的情况出现。右连接同理。

8 右连接

SELECT * from tb_visit_info t1 RIGHT JOIN 
tb_device_error_info t2 ON  t1.device_id=
t2.device_id AND t1.date=t2.date

9 全连接

mysql无全连接命令，可以通过合并命令进行代替，如下：

SELECT * from tb_visit_info t1 RIGHT JOIN 
tb_device_error_info t2 ON 
t1.device_id=t2.device_id
AND t1.date=t2.date
UNION
SELECT * from tb_visit_info t1 LEFT JOIN 
tb_device_error_info t2 ON 
t1.device_id=t2.device_id
AND t1.date=t2.date

对照各图，就可以了解语句的含义，就酱紫啦，谢谢。