乐趣区

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

jiezi

去重（过滤反复数据）

  • 在 MySQL 中应用 SELECT 语句执行简略的数据查问时，返回的是所有匹配的记录。如果表中的某些字段没有唯一性束缚，那么这些字段就可能存在反复值。为了实现查问不反复的数据，MySQL 提供了 DISTINCT 关键字。
  • DISTINCT 关键字的次要作用就是对数据表中一个或多个字段反复的数据进行过滤，只返回其中的一条数据给用户。
  • 应用 DISTINCT 关键字时须要留神以下几点：
  • DISTINCT 关键字只能在 SELECT 语句中应用。
  • 在对一个或多个字段去重时，DISTINCT 关键字必须在所有字段的最后面。

  • 如果 DISTINCT 关键字后有多个字段，则会对多个字段进行组合去重，也就是说，只有多个字段组合起来齐全是一样的状况下才会被去重。

    # 对 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 关键字来为表和字段指定别名
  • 当表名很长或者执行一些非凡查问的时候，为了不便操作，能够为表指定一个别名，用这个别名代替表原来的名称。
  • 表的别名不能与该数据库的其它表同名。字段的别名不能与该表的其它字段同名。在条件表达式中不能应用字段的别名
  • 表别名只在执行查问时应用，并不在返回后果中显示。而字段定义别名之后，会返回给客户端显示，显示的字段为字段的别名。

  • 表别名

    # 上面为 zabbix 库中的 hosts 表指定别名 h
select h.name,h.host from zabbix.hosts as h where status=0;

  • 字段别名

    # 给 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 |

  • row in set (0.00 sec)

    ---
  • 当数据表中有上万条数据时，一次性查问出表中的全副数据会升高数据返回的速度，同时给数据库服务器造成很大的压力。这时就能够用 LIMIT 关键字来限度查问后果返回的条数。
  • LIMIT 是 MySQL 中的一个非凡关键字，用于指定查问后果从哪条记录开始显示，一共显示多少条记录。

  • LIMIT 关键字有 3 种应用形式，即指定初始地位、不指定初始地位以及与 OFFSET 组合应用。

    1. 指定初始地位

  • LIMIT 关键字能够指定查问后果从哪条记录开始显示，显示多少条记录。

  • LIMIT 指定初始地位的根本语法格局如下：

    LIMIT 初始地位，记录数
  • 其中，“初始地位”示意从哪条记录开始显示；“记录数”示意显示记录的条数。第一条记录的地位是 0，第二条记录的地位是 1。前面的记录顺次类推。
  • 留神：LIMIT 后的两个参数必须都是正整数。

  • 法则如下：

    第 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 行记录

    select FROM_UNIXTIME(his.clock),his.value from history as his limit 3,5;

2. 不指定初始地位

  • LIMIT 关键字不指定初始地位时，记录从第一条记录开始显示。显示记录的条数由 LIMIT 关键字指定。

  • LIMIT 不指定初始地位的根本语法格局如下：

    LIMIT 记录数

  • 其中，“记录数”示意显示记录的条数。如果“记录数”的值小于查问后果的总数，则会从第一条记录开始，显示指定条数的记录。如果“记录数”的值大于查问后果的总数，则会间接显示查问进去的所有记录。

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

    mysql> select hostid,name from zabbix.hosts where status=0 limit 3;
+--------+------------+
| hostid | name       |
+--------+------------+
|  10084 | zbxser01   |
|  10331 | {#HV.NAME} |
|  10332 | {#VM.NAME} |

  • rows in set (0.00 sec)

  • 后果中只显示了 3 条记录，阐明“LIMIT 3”限度了显示条数为 3。

3. LIMIT 和 OFFSET 组合应用

  • LIMIT 能够和 OFFSET 组合应用，语法格局如下：

    LIMIT 记录数 OFFSET 初始地位

  • 参数和 LIMIT 语法中参数含意雷同，“初始地位”指定从哪条记录开始显示；“记录数”示意显示记录的条数。

    案例

    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                           |

  • rows in set (0.00 sec)

    ---
  • 关键字：order by
  • 通过条件查问语句能够查问到合乎用户需要的数据，然而查问到的数据个别都是依照数据最后被增加到表中的程序来显示。为了使查问后果的程序满足用户的要求，MySQL 提供了 ORDER BY 关键字来对查问后果进行排序。
  • 在理论利用中常常须要对查问后果进行排序，比方，在网上购物时，能够将商品依照价格进行排序；在医院的挂号零碎中，能够依照挂号的先后顺序进行排序等。

  • ORDER BY 关键字次要用来将查问后果中的数据依照肯定的程序进行排序，其语法格局如下：

    ORDER BY < 字段名 > [ASC|DESC]
  • 语法阐明如下。
  • 字段名：示意须要排序的字段名称，多个字段时用逗号隔开。
  • ASC|DESC：ASC 示意字段按升序排序；DESC 示意字段按降序排序。其中 ASC 为默认值。
  • DESC 是从大到小，ASC 是从小到大
  • 应用 ORDER BY 关键字应该留神以下几个方面：
  • ORDER BY 关键字后能够跟子查问（对于子查问前面教程会具体解说，这里理解即可）。
  • 当排序的字段中存在空值时，ORDER BY 会将该空值作为最小值来看待。

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

    1. 单字段排序

  • 查问 history 表的数据，clock 字段按降序排序（也就是从大到小，从最新到最旧的工夫），并且分页显示，从 0 开始显示，每页显示 10 条记录，以后显示第 1 页
  • 上面的例子就是取以后最新工夫的 10 条历史数据

  • 那么，经验了这些操作阶段：先查问、再排序、后分页

    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 |

  • rows in set (0.15 sec)

    mysql>

2. 多字段排序

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

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

    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 |

  • rows in set (0.17 sec)

    > 留神：在对多个字段进行排序时，排序的第一个字段必须有雷同的值，才会对第二个字段进行排序。如果第一个字段数据中所有的值都是惟一的，MySQL 将不再对第二个字段进行排序。### 2.2 上面的案例，clock 字段按 desc 降序排序，value 字段按 asc 升序排序

    select FROM_UNIXTIME(his.clock),his.value from history as his order by his.clock desc,his.value asc limit 0,100;

    ---
  • 查问条件能够是：
  • 带比拟运算符和逻辑运算符的查问条件
  • 带 BETWEEN AND 关键字的查问条件
  • 带 IS NULL 关键字的查问条件
  • 带 IN 关键字的查问条件

  • 带 LIKE 关键字的查问条件

    1. 繁多条件的查问语句

  • 繁多条件指的是在 WHERE 关键字后只有一个查问条件。

    # 按 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 |

  • rows in set (0.16 sec)

    ## 2. 多条件的查问语句
  • AND：记录满足所有查问条件时，才会被查问进去。
  • OR：记录满足任意一个查问条件时，才会被查问进去。

  • XOR：记录满足其中一个条件，并且不满足另一个条件时，才会被查问进去。

    # 在 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 |

  • 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

  • row in set (0.00 sec)

    > OR、AND 和 XOR 能够一起应用，然而在应用时要留神运算符的优先级

查问条件越多，查问进去的记录就会越少。因为，设置的条件越多，查问语句的限度就更多，可能满足所有条件的记录就更少。为了使查问进去的记录正是本人想要的，能够在 WHERE 语句中将查问条件设置的更加具体。

含糊查问

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

[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”结尾的记录

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”结尾 的记录

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 的记录

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 语句和运行后果如下。

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 关键字。

# 匹配 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 的根本语法格局如下：

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

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

# 确定起始工夫和完结工夫的工夫戳
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 的根本语法格局如下：

IS [NOT] NULL
  • 其中，“NOT”是可选参数，示意字段值不是空值时满足条件。

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

select * from zabbix.users where url not null;

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

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 独自应用

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

    # 查问每台主机名称有多少台
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 |

  • rows in set (0.00 sec)

9.2 GROUP BY 与 GROUP_CONCAT()

GROUP BY 关键字能够和 GROUP_CONCAT() 函数一起应用。GROUP_CONCAT() 函数会把每个分组的字段值都显示进去。

案例：依据 hosts 表中的 STATUS 字段进行分组查问，应用 GROUP_CONCAT() 函数将每个分组的 NAME 字段的值都显示进去，须要晓得每个状态都对应哪些名称的时候，就很有用了

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() 函数计算每一组的记录数

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 显示记录的总和

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 语句和运行后果如下。

# 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 的后果

# 依据 hosts 表中的 status 字段进行分组，并通过 group_concat 将每个分组字段 name 的内容显示进去，查问全量，不过滤
mysql> select status,group_concat(name) from zabbix.hosts group by status\G;        
*************************** 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/mL…

退出移动版