一文带你剖析MySQL到底都有哪些常用的查询

2022-06-15 21:33:37 浏览数 (1)

去重（过滤重复数据）

  • 在 MySQL 中使用 SELECT 语句执行简单的数据查询时，返回的是所有匹配的记录。如果表中的某些字段没有唯一性约束，那么这些字段就可能存在重复值。为了实现查询不重复的数据，MySQL 提供了 DISTINCT 关键字。
  • DISTINCT 关键字的主要作用就是对数据表中一个或多个字段重复的数据进行过滤，只返回其中的一条数据给用户。
  • 使用 DISTINCT 关键字时需要注意以下几点：
  1. DISTINCT 关键字只能在 SELECT 语句中使用。
  2. 在对一个或多个字段去重时，DISTINCT 关键字必须在所有字段的最前面。
  3. 如果 DISTINCT 关键字后有多个字段，则会对多个字段进行组合去重，也就是说，只有多个字段组合起来完全是一样的情况下才会被去重。
代码语言：javascript复制
# 对history表的value字段去重
select distinct history.value from zabbix.hosts,zabbix.items,zabbix.history where hosts.name="zbxproxy03" and items.name="Context switches per second" and items.itemid=history.itemid;

# 对history表的clock和value字段去重
select distinct history.clock,history.value from zabbix.hosts,zabbix.items,zabbix.history where hosts.name="zbxproxy03" and items.name="Context switches per second" and items.itemid=history.itemid;

# 查询去重之后的记录的条数
select count(distinct history.clock,history.value) from zabbix.hosts,zabbix.items,zabbix.history where hosts.name="zbxproxy03" and items.name="Context switches per second" and items.itemid=history.itemid;

别名

  • 为了查询方便，MySQL 提供了 AS 关键字来为表和字段指定别名
  • 当表名很长或者执行一些特殊查询的时候，为了方便操作，可以为表指定一个别名，用这个别名代替表原来的名称。
  • 表的别名不能与该数据库的其它表同名。字段的别名不能与该表的其它字段同名。在条件表达式中不能使用字段的别名
  • 表别名只在执行查询时使用，并不在返回结果中显示。而字段定义别名之后，会返回给客户端显示，显示的字段为字段的别名。
  1. 表别名
代码语言：javascript复制
# 下面为 zabbix 库中的 hosts 表指定别名 h
select h.name,h.host from zabbix.hosts as h where status=0;
  1. 字段别名
代码语言：javascript复制
# 给h.name字段指定别名“主机名”，inter.ip字段指定别名“ip地址”
mysql> select h.name as "主机名",inter.ip as "ip地址" from zabbix.hosts as h,zabbix.interface as inter where h.name="zbxproxy03" and h.hostid=inter.hostid;
 ------------ ---------------- 
| 主机名     | ip地址         |
 ------------ ---------------- 
| zbxproxy03 | 192.168.11.157 |
 ------------ ---------------- 
1 row in set (0.00 sec)

分页查询（限制查询结果的条数）

  • 当数据表中有上万条数据时，一次性查询出表中的全部数据会降低数据返回的速度，同时给数据库服务器造成很大的压力。这时就可以用 LIMIT 关键字来限制查询结果返回的条数。
  • LIMIT 是 MySQL 中的一个特殊关键字，用于指定查询结果从哪条记录开始显示，一共显示多少条记录。
  • LIMIT 关键字有 3 种使用方式，即指定初始位置、不指定初始位置以及与 OFFSET 组合使用。

1. 指定初始位置

  • LIMIT 关键字可以指定查询结果从哪条记录开始显示，显示多少条记录。
  • LIMIT 指定初始位置的基本语法格式如下：
代码语言：javascript复制
LIMIT 初始位置，记录数
  • 其中，“初始位置”表示从哪条记录开始显示；“记录数”表示显示记录的条数。第一条记录的位置是 0，第二条记录的位置是 1。后面的记录依次类推。
  • 注意：LIMIT 后的两个参数必须都是正整数。
  • 规律如下：
代码语言：javascript复制
第1页 limit 0,10 # 按分页显示，每页显示10条记录，从0开始，当前是第1页（第2页的计算方式是，10 0=10，所以，要显示第2页，就要从10开始了）
第2页 limit 10,10 # 按分页显示，每页显示10条记录，从10开始，当前是第2页（第3页的计算方式是，10 10=20，所以，要显示第3页，就要从20开始了）
第3页 limit 20,10 # 按分页显示，每页显示10条记录，从20开始，当前是第3页
第4页 limit 30,10 # 按分页显示，每页显示10条记录，从30开始，当前是第4页
第5页 limit 40,10 # 按分页显示，每页显示10条记录，从40开始，当前是第5页
...依此类推...

案例：从第3行记录开始，每页显示5行记录

代码语言：javascript复制
select FROM_UNIXTIME(his.clock),his.value from history as his limit 3,5;

2. 不指定初始位置

  • LIMIT 关键字不指定初始位置时，记录从第一条记录开始显示。显示记录的条数由 LIMIT 关键字指定。
  • LIMIT 不指定初始位置的基本语法格式如下：
代码语言：javascript复制
LIMIT 记录数
  • 其中，“记录数”表示显示记录的条数。如果“记录数”的值小于查询结果的总数，则会从第一条记录开始，显示指定条数的记录。如果“记录数”的值大于查询结果的总数，则会直接显示查询出来的所有记录。

案例：显示 hosts 表查询结果的前 3 行，SQL 语句和运行结果如下。

代码语言：javascript复制
mysql> select hostid,name from zabbix.hosts where status=0 limit 3;
 -------- ------------ 
| hostid | name       |
 -------- ------------ 
|  10084 | zbxser01   |
|  10331 | {#HV.NAME} |
|  10332 | {#VM.NAME} |
 -------- ------------ 
3 rows in set (0.00 sec)
  • 结果中只显示了 3 条记录，说明“LIMIT 3”限制了显示条数为 3。

3. LIMIT和OFFSET组合使用

  • LIMIT 可以和 OFFSET 组合使用，语法格式如下：
代码语言：javascript复制
LIMIT 记录数 OFFSET 初始位置
  • 参数和 LIMIT 语法中参数含义相同，“初始位置”指定从哪条记录开始显示；“记录数”表示显示记录的条数。

案例

代码语言：javascript复制
mysql> select name from zabbix.hosts limit 5 offset 100;
 --------------------------------------------------------------- 
| name                                                          |
 --------------------------------------------------------------- 
| Template Module Windows physical disks by Zabbix agent active |
| Template Module Windows services by Zabbix agent              |
| Template Module Windows services by Zabbix agent active       |
| Template Module Zabbix agent                                  |
| Template Module Zabbix agent active                           |
 --------------------------------------------------------------- 
5 rows in set (0.00 sec)

排序（对查询结果排序）

  • 关键字：order by
  • 通过条件查询语句可以查询到符合用户需求的数据，但是查询到的数据一般都是按照数据最初被添加到表中的顺序来显示。为了使查询结果的顺序满足用户的要求，MySQL 提供了 ORDER BY 关键字来对查询结果进行排序。
  • 在实际应用中经常需要对查询结果进行排序，比如，在网上购物时，可以将商品按照价格进行排序；在医院的挂号系统中，可以按照挂号的先后顺序进行排序等。
  • ORDER BY 关键字主要用来将查询结果中的数据按照一定的顺序进行排序，其语法格式如下：
代码语言：javascript复制
ORDER BY <字段名> [ASC|DESC]
  • 语法说明如下。
  1. 字段名：表示需要排序的字段名称，多个字段时用逗号隔开。
  2. ASC|DESC：ASC表示字段按升序排序；DESC表示字段按降序排序。其中ASC为默认值。
  3. DESC是从大到小，ASC是从小到大
  • 使用 ORDER BY 关键字应该注意以下几个方面：
  1. ORDER BY 关键字后可以跟子查询（关于子查询后面教程会详细讲解，这里了解即可）。
  2. 当排序的字段中存在空值时，ORDER BY 会将该空值作为最小值来对待。
  3. ORDER BY 指定多个字段进行排序时，MySQL 会按照字段的顺序从左到右依次进行排序。

1. 单字段排序

  • 查询history表的数据，clock字段按降序排序（也就是从大到小，从最新到最旧的时间），并且分页显示，从0开始显示，每页显示10条记录，当前显示第1页
  • 下面的例子就是取当前最新时间的10条历史数据
  • 那么，经历了这些操作阶段：先查询、再排序、后分页
代码语言：javascript复制
mysql> select FROM_UNIXTIME(his.clock),his.value from history as his order by his.clock desc limit 0,10;
 -------------------------- --------------------- 
| FROM_UNIXTIME(his.clock) | value               |
 -------------------------- --------------------- 
| 2022-03-23 11:39:58      | 0.03327262491210712 |
| 2022-03-23 11:39:57      |  0.2994525692658531 |
| 2022-03-23 11:39:57      |     6.7543337663136 |
| 2022-03-23 11:39:56      |   6.937363366354516 |
| 2022-03-23 11:39:56      |  1.0364532470703125 |
| 2022-03-23 11:39:56      |   2.312458609154667 |
| 2022-03-23 11:39:56      | 0.24791898308140758 |
| 2022-03-23 11:39:55      |  2.2958371233088815 |
| 2022-03-23 11:39:55      |  0.3120714025473114 |
| 2022-03-23 11:39:55      |                   0 |
 -------------------------- --------------------- 
10 rows in set (0.15 sec)

mysql> 

2. 多字段排序

  • ORDER BY 指定多个字段进行排序时，MySQL 会按照字段的顺序从左到右依次进行排序。

2.1 下面的案例，查询 history 表中的 clock 和 value 字段，先按 clock 排序，再按 value 排序，SQL 语句和运行结果如下。

代码语言：javascript复制
mysql> select FROM_UNIXTIME(his.clock),his.value from history as his order by his.clock,his.value desc limit 0,10;
 -------------------------- ------------------- 
| FROM_UNIXTIME(his.clock) | value             |
 -------------------------- ------------------- 
| 2022-03-16 21:46:16      |         96.085049 |
| 2022-03-16 21:46:16      | 3.914951000000002 |
| 2022-03-16 21:46:16      |          2.126223 |
| 2022-03-16 21:46:16      |          0.978738 |
| 2022-03-16 21:46:16      |          0.759366 |
| 2022-03-16 21:46:16      |              0.18 |
| 2022-03-16 21:46:16      |              0.09 |
| 2022-03-16 21:46:16      |              0.07 |
| 2022-03-16 21:46:16      |          0.050624 |
| 2022-03-16 21:46:16      |                 0 |
 -------------------------- ------------------- 
10 rows in set (0.17 sec)

注意：在对多个字段进行排序时，排序的第一个字段必须有相同的值，才会对第二个字段进行排序。如果第一个字段数据中所有的值都是唯一的，MySQL 将不再对第二个字段进行排序。

2.2 下面的案例，clock字段按desc降序排序，value字段按asc升序排序

代码语言：javascript复制
select FROM_UNIXTIME(his.clock),his.value from history as his order by his.clock desc,his.value asc limit 0,100;

条件查询

  • 查询条件可以是：
  1. 带比较运算符和逻辑运算符的查询条件
  2. 带 BETWEEN AND 关键字的查询条件
  3. 带 IS NULL 关键字的查询条件
  4. 带 IN 关键字的查询条件
  5. 带 LIKE 关键字的查询条件

1. 单一条件的查询语句

  • 单一条件指的是在 WHERE 关键字后只有一个查询条件。
代码语言：javascript复制
# 按clock字段从最新到最旧的时间进行排序，并显示前10行记录
mysql> select from_unixtime(his.clock), his.value from zabbix.history as his order by his.clock desc limit 10;
 -------------------------- --------------------- 
| from_unixtime(his.clock) | value               |
 -------------------------- --------------------- 
| 2022-03-23 15:28:28      | 0.08122102270804427 |
| 2022-03-23 15:28:28      | 0.31311299809630666 |
| 2022-03-23 15:28:27      |                 100 |
| 2022-03-23 15:28:27      |                   0 |
| 2022-03-23 15:28:27      |  0.7355883252732085 |
| 2022-03-23 15:28:26      |                   0 |
| 2022-03-23 15:28:26      |                   0 |
| 2022-03-23 15:28:26      |           15.969782 |
| 2022-03-23 15:28:25      |            0.007042 |
| 2022-03-23 15:28:24      |            9.618901 |
 -------------------------- --------------------- 
10 rows in set (0.16 sec)

2. 多条件的查询语句

在 WHERE 关键词后可以有多个查询条件，这样能够使查询结果更加精确。多个查询条件时用逻辑运算符 AND（&&）、OR（||）或 XOR 隔开。

  • AND：记录满足所有查询条件时，才会被查询出来。
  • OR：记录满足任意一个查询条件时，才会被查询出来。
  • XOR：记录满足其中一个条件，并且不满足另一个条件时，才会被查询出来。
代码语言：javascript复制
# 在 events 表中查询 eventid 大于 400，并且 objectid 大于等于 16274 的事件信息，SQL 语句和运行结果如下。
mysql> select eventid,objectid,name from zabbix.events where eventid>400 and objectid>=16274; 
 --------- ---------- ------------------------------------------------------------------ 
| eventid | objectid | name                                                             |
 --------- ---------- ------------------------------------------------------------------ 
|     429 |    17775 | More than 100 items having missing data for more than 10 minutes |
 --------- ---------- ------------------------------------------------------------------ 
1 row in set (0.00 sec)

# 在 events 表中查询 eventid 大于 400，并且 objectid 大于等于 15000 的事件信息，SQL 语句和运行结果如下。
mysql> select eventid,objectid,name from zabbix.events where eventid>400 and objectid>=15000;
 --------- ---------- ------------------------------------------------------------------ 
| eventid | objectid | name                                                             |
 --------- ---------- ------------------------------------------------------------------ 
|     429 |    17775 | More than 100 items having missing data for more than 10 minutes |
 --------- ---------- ------------------------------------------------------------------ 
1 row in set (0.00 sec)

OR、AND 和 XOR 可以一起使用，但是在使用时要注意运算符的优先级

查询条件越多，查询出来的记录就会越少。因为，设置的条件越多，查询语句的限制就更多，能够满足所有条件的记录就更少。为了使查询出来的记录正是自己想要的，可以在 WHERE 语句中将查询条件设置的更加具体。

模糊查询

在 MySQL 中，LIKE 关键字主要用于搜索匹配字段中的指定内容。其语法格式如下：

代码语言：javascript复制
[NOT] LIKE  '字符串'

其中：

  • NOT ：可选参数，字段中的内容与指定的字符串不匹配时满足条件。
  • 字符串：指定用来匹配的字符串。“字符串”可以是一个很完整的字符串，也可以包含通配符。

在 where like 的条件查询中，SQL 提供了四种匹配方式。

  • “%”通配符：表示任意 0 个或多个字符。可匹配任意类型和长度的字符，有些情况下若是中文，请使用两个百分号（%%）表示。
  • ”_“通配符：只能代表单个字符，字符的长度不能为 0。例如，a_b可以代表 acb、adb、aub 等字符串。
  • “:”通配符：表示任意单个字符。匹配单个任意字符，它常用来限制表达式的字符长度语句。
  • “[]”通配符：表示括号内所列字符中的一个（类似正则表达式）。指定一个字符、字符串或范围，要求所匹配对象为它们中的任一个。
  • “[^]”通配符 ：表示不在括号所列之内的单个字符。其取值和 [] 相同，但它要求所匹配对象为指定字符以外的任一个字符。

查询内容包含通配符时,由于通配符的缘故，导致我们查询特殊字符 “%”、“”、“[” 的语句无法正常实现，而把特殊字符用 “[ ]” 括起便可正常查询。

通配符是一种特殊语句，主要用来模糊查询。当不知道真正字符或者懒得输入完整名称时，可以使用通配符来代替一个或多个真正的字符。

1. 带有“%”通配符的查询

%”是 MySQL 中最常用的通配符，它能代表任何长度的字符串，字符串的长度可以为 0。例如，a%b表示以字母 a 开头，以字母 b 结尾的任意长度的字符串。该字符串可以代表 ab、acb、accb、accrb 等字符串。有些情况下若是中文，请使用两个百分号（%%）表示。

案例：从hosts表中的name字段查找所有以“T”开头的记录

代码语言：javascript复制
mysql> select name from zabbix.hosts where name like 'T%';
 ----------------------------------------------------------------------------- 
| name                                                                        |
 ----------------------------------------------------------------------------- 
| Template APP Apache Kafka by JMX                                            |
| Template App Apache Tomcat JMX                                              |
| Template App Apache by HTTP                                                 |
| Template App Apache by Zabbix agent                                         |
| Template App Ceph by Zabbix Agent2                                          |

注意：匹配的字符串必须加单引号或双引号。

案例：从hosts表中的name字段查找所有不以“T”开头的记录

代码语言：javascript复制
mysql> select name from zabbix.hosts where name not like 'T%';
 -------------- 
| name         |
 -------------- 
| mysql-db02   |
| mysql-master |
| zbxproxy01   |
| zbxproxy02   |
| zbxproxy03   |
| zbxproxy04   |
| zbxser01     |
| zbxser02     |

案例：从hosts表中的name字段查找包含有CPU的记录

代码语言：javascript复制
mysql> select name from zabbix.hosts where name like '%CPU%';
 ---------------------------------------------------- 
| name                                               |
 ---------------------------------------------------- 
| Template Module Cisco OLD-CISCO-CPU-MIB SNMP       |
| Template Module HOST-RESOURCES-MIB CPU SNMP        |
| Template Module Linux CPU SNMP                     |
| Template Module Linux CPU by Zabbix agent          |
| Template Module Linux CPU by Zabbix agent active   |
| Template Module Windows CPU by Zabbix agent        |
| Template Module Windows CPU by Zabbix agent active |
 ---------------------------------------------------- 
7 rows in set (0.00 sec)

2. 带有“_”通配符的查询

“_”只能代表单个字符，字符的长度不能为 0。例如，a_b可以代表 acb、adb、aub 等字符串。 案例：在 hosts 表中，查找所有以数字“01”结尾，且“01”前面只有 6 个字符的名称，SQL 语句和运行结果如下。

代码语言：javascript复制
mysql> select name,status from zabbix.hosts where status=0 and name like '______01';
 ---------- -------- 
| name     | status |
 ---------- -------- 
| zbxser01 |      0 |
 ---------- -------- 
1 row in set (0.00 sec)

3. LIKE区分大小写

默认情况下，LIKE 关键字匹配字符的时候是不区分大小写的。如果需要区分大小写，可以加入 BINARY 关键字。

代码语言：javascript复制
# 匹配t开头的行记录，并区分大小写
mysql> select name,status from zabbix.hosts where name like binary 't%';
Empty set, 1 warning (0.00 sec)

注意：mysql8貌似已经废除了该特性，mysql5是可以的

4. 使用通配符的注意事项和技巧

下面是使用通配符的一些注意事项：

  • 注意大小写。MySQL 默认是不区分大小写的。如果区分大小写，像“Tom”这样的数据就不能被“t%”所匹配到。
  • 注意尾部空格，尾部空格会干扰通配符的匹配。例如，“T% ”就不能匹配到“Tom”。
  • 注意 NULL。“%”通配符可以匹配到任意字符，但是不能匹配 NULL。也就是说 “%”匹配不到某数据表中值为 NULL 的记录。

下面是一些使用通配符要记住的技巧。

  • 不要过度使用通配符，如果其它操作符能达到相同的目的，应该使用其它操作符。因为 MySQL 对通配符的处理一般会比其他操作符花费更长的时间。
  • 在确定使用通配符后，除非绝对有必要，否则不要把它们用在字符串的开始处。把通配符置于搜索模式的开始处，搜索起来是最慢的。
  • 仔细注意通配符的位置。如果放错地方，可能不会返回想要的数据。
  • 如果查询内容中包含通配符，可以使用“”转义符

总之，通配符是一种极其重要和有用的搜索工具，以后我们会经常用到它。

范围查询

MySQL 提供了 BETWEEN AND 关键字，用来判断字段的数值是否在指定范围内。 BETWEEN AND 需要两个参数，即范围的起始值和终止值。如果字段值在指定的范围内，则这些记录被返回。如果不在指定范围内，则不会被返回。 使用 BETWEEN AND 的基本语法格式如下：

代码语言：javascript复制
[NOT] BETWEEN 取值1 AND 取值2
  • NOT：可选参数，表示指定范围之外的值。如果字段值不满足指定范围内的值，则这些记录被返回。
  • 取值1：表示范围的起始值。
  • 取值2：表示范围的终止值。

案例：查询2022年3月23号上午10点到11点这个时间段的历史数据

代码语言：javascript复制
# 确定起始时间和结束时间的时间戳
mysql> select itemid,from_unixtime(clock),clock,value,ns from zabbix.history where from_unixtime(clock) like '2022-03-23 10:00:02%';
 -------- ---------------------- ------------ ------- ----------- 
| itemid | from_unixtime(clock) | clock      | value | ns        |
 -------- ---------------------- ------------ ------- ----------- 
|  29162 | 2022-03-23 10:00:02  | 1648000802 |     0 | 277202868 | # 起始时间
 -------- ---------------------- ------------ ------- ----------- 
1 row in set (0.70 sec)

mysql> 
mysql> select itemid,from_unixtime(clock),clock,value,ns from zabbix.history where from_unixtime(clock) like '2022-03-23 11:00:01%';
 -------- ---------------------- ------------ ----------- ---------- 
| itemid | from_unixtime(clock) | clock      | value     | ns       |
 -------- ---------------------- ------------ ----------- ---------- 
|  33064 | 2022-03-23 11:00:01  | 1648004401 | 87.926269 | 39923084 | # 结束时间
 -------- ---------------------- ------------ ----------- ---------- 
1 row in set (0.76 sec)

mysql> 

# 通过BETWEEN AND 关键字来做范围查询，语句如下
mysql> select itemid,from_unixtime(clock),clock,value,ns from zabbix.history where clock BETWEEN 1648000802 AND 1648004401;

空值查询

MySQL 提供了 IS NULL 关键字，用来判断字段的值是否为空值（NULL）。空值不同于 0，也不同于空字符串。如果字段的值是空值，则满足查询条件，该记录将被查询出来。如果字段的值不是空值，则不满足查询条件。使用 IS NULL 的基本语法格式如下：

代码语言：javascript复制
IS [NOT] NULL
  • 其中，“NOT”是可选参数，表示字段值不是空值时满足条件。

案例：使用 IS NULL 关键字来查询 users 表中 url 字段是 NULL 的记录。

代码语言：javascript复制
select * from zabbix.users where url not null;

案例：使用 IS NULL 关键字来查询 users 表中 url 字段是 不为NULL 的记录。

代码语言：javascript复制
select * from zabbix.users where url is not null;

注意：IS NULL 是一个整体，不能将 IS 换成“=”。如果将 IS 换成“=”将不能查询出任何结果，数据库系统会出现“Empty set(0.00 sec)”这样的提示。同理，IS NOT NULL 中的 IS NOT 不能换成“!=”或“<>”。

分组查询（重要&常用）

  • 分组查询的作用是将查询的结果按指定字段分组，字段中数值相等的为一组。 分组以后可以配合count()、agv()、sum()、max()等聚合函数使用。
  • Group by 语句用于结合聚合函数（如count，sum，avg，max，min），根据一个或多个列对结果集进行分组。
  • MySQL 8.0开始 group by 默认是没有排序的，那MySQL 8.0之前和 8.0 就有可能结果出现不同 需要警惕

9.1 GROUP BY单独使用

案例：查询每台主机有多少台

代码语言：javascript复制
# 查询每台主机名称有多少台
mysql> select name "主机名",count(*) "数量"  from zabbix.hosts where status=0 and name not like '%{%' group by name; 
 -------------- -------- 
| 主机名       | 数量   |
 -------------- -------- 
| zbxser01     |      1 |
| mysql-master |      1 |
| mysql-db02   |      1 |
| zbxser02     |      1 |
| zbxproxy01   |      1 |
| zbxproxy02   |      1 |
| zbxproxy04   |      1 |
| zbxproxy03   |      1 |
 -------------- -------- 
8 rows in set (0.00 sec)

9.2 GROUP BY 与 GROUP_CONCAT()

GROUP BY 关键字可以和 GROUP_CONCAT() 函数一起使用。GROUP_CONCAT() 函数会把每个分组的字段值都显示出来。

案例：根据 hosts 表中的 STATUS 字段进行分组查询，使用 GROUP_CONCAT() 函数将每个分组的 NAME 字段的值都显示出来，需要知道每个状态都对应哪些名称的时候，就很有用了

代码语言：javascript复制
SELECT STATUS,GROUP_CONCAT(NAME) FROM zabbix.hosts WHERE STATUS!=5 GROUP BY STATUS;

由结果可以看到，查询结果分为两组，status 字段值为“0”的是一组，值为“3”的是一组，且每组的主机名或者模板名称都显示出来了。

9.3 GROUP BY 与聚合函数

在数据统计时，GROUP BY 关键字经常和聚合函数一起使用。 聚合函数包括 COUNT()，SUM()，AVG()，MAX() 和 MIN()。其中，COUNT() 用来统计记录的条数；SUM() 用来计算字段值的总和；AVG() 用来计算字段值的平均值；MAX() 用来查询字段的最大值；MIN() 用来查询字段的最小值。

案例：根据 hosts 表的 name 字段进行分组查询，使用 COUNT() 函数计算每一组的记录数

代码语言：javascript复制
mysql> select name,count(name) from zabbix.hosts where status=0 and name not like '%{%' group by name;
 -------------- ------------- 
| name         | count(name) |
 -------------- ------------- 
| zbxser01     |           1 |
| mysql-master |           1 |
| mysql-db02   |           1 |
| zbxser02     |           1 |
| zbxproxy01   |           1 |
| zbxproxy02   |           1 |
| zbxproxy04   |           1 |
| zbxproxy03   |           1 |
 -------------- -------------

9.4 GROUP BY 与 WITH ROLLUP

WITH ROLLUP 关键字用来在所有记录的最后加上一条记录，这条记录是上面所有记录的总和，即统计记录数量。

案例：根据 hosts 表中的 name 字段进行分组查询，并使用 WITH ROLLUP 显示记录的总和

代码语言：javascript复制
mysql> select name,count(name) from zabbix.hosts where status=0 and name not like '%{%' group by name with rollup; 
 -------------- ------------- 
| name         | count(name) |
 -------------- ------------- 
| mysql-db02   |           1 |
| mysql-master |           1 |
| zbxproxy01   |           1 |
| zbxproxy02   |           1 |
| zbxproxy03   |           1 |
| zbxproxy04   |           1 |
| zbxser01     |           1 |
| zbxser02     |           1 |
| NULL         |           8 | # 这里就是通过with rollup关键字计算出来的总和

过滤分组

在 MySQL 中，可以使用 HAVING 关键字对分组后的数据进行过滤。 HAVING 关键字和 WHERE 关键字都可以用来过滤数据，且 HAVING 支持 WHERE 关键字中所有的操作符和语法。 但是 WHERE 和 HAVING 关键字也存在以下几点差异：

  • 一般情况下，WHERE 用于过滤数据行，而 HAVING 用于过滤分组。
  • WHERE 查询条件中不可以使用聚合函数，而 HAVING 查询条件中可以使用聚合函数。
  • WHERE 在数据分组前进行过滤，而 HAVING 在数据分组后进行过滤 。
  • WHERE 针对数据库文件进行过滤，而 HAVING 针对查询结果进行过滤。也就是说，WHERE 根据数据表中的字段直接进行过滤，而 HAVING 是根据前面已经查询出的字段进行过滤。
  • WHERE 查询条件中不可以使用字段别名，而 HAVING 查询条件中可以使用字段别名。

案例：了解 WHERE 和 HAVING 关键字的相同点和不同点，分别使用 HAVING 和 WHERE 关键字查询出 hosts 表中的name、host、status。SQL 语句和运行结果如下。

代码语言：javascript复制
# SELECT 关键字后已经查询出了 status 字段，所以 HAVING可用
mysql> select name,host,status from zabbix.hosts having status=0;
 -------------- -------------- -------- 
| name         | host         | status |
 -------------- -------------- -------- 
| zbxser01     | zbxser01     |      0 |
| {#HV.NAME}   | {#HV.UUID}   |      0 |
| {#VM.NAME}   | {#VM.UUID}   |      0 |
| mysql-master | mysql-master |      0 |
| mysql-db02   | mysql-db02   |      0 |
| zbxser02     | zbxser02     |      0 |
| zbxproxy01   | zbxproxy01   |      0 |
| zbxproxy02   | zbxproxy02   |      0 |
| zbxproxy04   | zbxproxy04   |      0 |
| zbxproxy03   | zbxproxy03   |      0 |
 -------------- -------------- -------- 
10 rows in set (0.00 sec)

# SELECT 关键字后没有 status 字段，所以 HAVING报错了
mysql> select name,host from zabbix.hosts having status=0;        
ERROR 1054 (42S22): Unknown column 'status' in 'having clause'
mysql> 

# SELECT 关键字后没有 status 字段，where是OK的
mysql> select name,host from zabbix.hosts where status=0;      
 -------------- -------------- 
| name         | host         |
 -------------- -------------- 
| zbxser01     | zbxser01     |
| {#HV.NAME}   | {#HV.UUID}   |
| {#VM.NAME}   | {#VM.UUID}   |
| mysql-master | mysql-master |
| mysql-db02   | mysql-db02   |
| zbxser02     | zbxser02     |
| zbxproxy01   | zbxproxy01   |
| zbxproxy02   | zbxproxy02   |
| zbxproxy04   | zbxproxy04   |
| zbxproxy03   | zbxproxy03   |
 -------------- -------------- 
10 rows in set (0.00 sec)

mysql> 

因为在 SELECT 关键字后已经查询出了 status 字段，所以 HAVING 和 WHERE 都可以使用。但是如果 SELECT 关键字后没有查询出 status 字段，这时的having就会报错，where是OK的。 由结果可以看出，如果 SELECT 关键字后没有查询出 HAVING 查询条件中使用的 status 字段，MySQL 会提示错误信息：“having子句”中的列“status”未知”。

案例：使用 HAVING 和 WHERE 关键字分别查询status等于0的结果

代码语言：javascript复制
# 根据hosts表中的status字段进行分组，并通过group_concat将每个分组字段name的内容显示出来，查询全量，不过滤
mysql> select status,group_concat(name) from zabbix.hosts group by statusG;        
*************************** 1. row ***************************
            status: 0
group_concat(name): zbxser01,{#HV.NAME},{#VM.NAME},mysql-master,mysql-db02,zbxser02,zbxproxy01,zbxproxy02,zbxproxy04,zbxproxy03
*************************** 2. row ***************************
            status: 3
group_concat(name): Template OS Linux by Zabbix agent,Template App Zabbix Server,Template App Zabbix Proxy,Template Module Zabbix agent,Template OS OpenBSD,Template OS FreeBSD,Template OS AIX,Template OS HP-UX,Template OS Solaris,Template OS Mac OS X,Template OS Windows by Zabbix agent,Template App FTP Service,Template App HTTP Service,Template App HTTPS Service,Template App IMAP Service,Template App LDAP Service,Template App NNTP Service,Template App NTP Service,Template App POP Service,Template App SMTP Service,Template App SSH Service,Template App Telnet Service,Template App Generic Java JMX,Template DB MySQL,Template Server Intel SR1530 IPMI,Template Server Intel SR1630 IPMI,Template VM VMware,Template VM VMware Guest,Template VM VMware Hypervisor,Template Module EtherLike-MIB SNMP,Template Module HOST-RESOURCES-MIB SNMP,Template Module ICMP Ping,Template Module Interfaces Simple SNMP,Template Module Interfaces SNMP,Template Module Interfaces Windows SNMP,Template Module Generic SNMP,Template Net Alcatel Timetra TiMOS SNMP,T
*************************** 3. row ***************************
            status: 5
group_concat(name): ,,,
3 rows in set, 1 warning (0.00 sec)

ERROR: 
No query specified

mysql> 

# 根据hosts表中的status字段进行分组，并通过group_concat将每个分组字段name的内容显示出来，通过having关键字过滤为0的结果
mysql> select status,group_concat(name) from zabbix.hosts group by status having status=0;
 -------- ------------------------------------------------------------------------------------------------------------- 
| status | group_concat(name)                                                                                          |
 -------- ------------------------------------------------------------------------------------------------------------- 
|      0 | zbxser01,{#HV.NAME},{#VM.NAME},mysql-master,mysql-db02,zbxser02,zbxproxy01,zbxproxy02,zbxproxy04,zbxproxy03 |
 -------- ------------------------------------------------------------------------------------------------------------- 
1 row in set, 1 warning (0.01 sec)

# 根据hosts表中的status字段进行分组，并通过group_concat将每个分组字段name的内容显示出来，通过where关键字过滤为0的结果
mysql> select status,group_concat(name) from zabbix.hosts where status=0 group by status;
 -------- ------------------------------------------------------------------------------------------------------------- 
| status | group_concat(name)                                                                                          |
 -------- ------------------------------------------------------------------------------------------------------------- 
|      0 | zbxser01,{#HV.NAME},{#VM.NAME},mysql-master,mysql-db02,zbxser02,zbxproxy01,zbxproxy02,zbxproxy04,zbxproxy03 |
 -------- ------------------------------------------------------------------------------------------------------------- 
1 row in set (0.00 sec)

本文转载于彩虹运维技术栈社区：

https://mp.weixin.qq.com/s/mLerKP2f--8jQjpHuM3ZkQ

云数据库MySQL 云数据库SQLServer

0 人点赞