注册    登录
关注
Py学习  »  DATABASE

MySQL入门系列：查询简介（四）之类型转换和分组查询

小孩子4919 • 5 年前 • 375 次点击  
阅读 70

MySQL入门系列：查询简介（四）之类型转换和分组查询

前文回顾

之前我们介绍了一些简单查询的方式以及MySQL中表达式和函数的简单使用，本篇文章继续介绍如何使用MySQL进行查询。

类型转换

类型转换的场景

只要某个值的类型与上下文要求的类型不符，MySQL就会根据上下文环境中需要的类型对该值进行类型转换，具体可能发生转换的场景有下边这几种：

  1. 把操作数类型转换为适合操作符计算的相应类型。

    比方说对于加法操作符+来说，它要求两个操作数都必须是数字才能进行计算，所以如果某个操作数不是数字的话，会将其强制转换为数字，比方说下边这个例子：

    1 + 2       →   3
'1' + 2     →   3
'1' + '2'   →   3
复制代码

    虽然'1'、'2'都是字符串，但是如果它们作为加法操作符+的操作数的话，都会被强制转换为数字，所以上边几个表达式其实都会被当作1 + 2去处理的，这些表达式被当作查询对象时的效果如下：

    mysql> SELECT 1 + 2, '1' + 2, '1' + '2';
+-------+---------+-----------+
| 1 + 2 | '1' + 2 | '1' + '2' |
+-------+---------+-----------+
|     3 |       3 |         3 |
+-------+---------+-----------+
1 row in set (0.00 sec)

mysql>
复制代码

  2. 将函数参数转换为该函数期望的类型。

    我们拿用于拼接字符串的CONCAT函数举例，这个函数的字符串类型的值作为参数，如果我们在调用这个函数的时候，传入了别的类型的值作为参数，MySQL会自动把这些值的类型转换为字符串类型的：

    CONCAT('1', '2')    →   '12'
CONCAT('1', 2)      →   '12'
CONCAT(1, 2)        →   '12'
复制代码

    虽然1、2都是数字，但是如果它们作为CONCAT函数的参数的话，都会被强制转换为字符串，所以上边几个表达式其实都会被当作CONCAT('1', '2)去处理的，这些表达式被当作查询对象时的效果如下：

    mysql> SELECT CONCAT('1', '2'), CONCAT('1', 2), CONCAT(1, 2);
+------------------+----------------+--------------+
| CONCAT('1', '2') | CONCAT('1', 2) | CONCAT(1, 2) |
+------------------+----------------+--------------+
| 12               | 12             | 12           |
+------------------+----------------+--------------+
1 row in set (0.00 sec)

mysql>
复制代码

  3. 存储数据时，把某个值转换为某个列需要的类型。

    我们先新建一个简单的表t：

    CREATE TABLE t (
    i1 TINYINT,
    i2 TINYINT,
    s VARCHAR(100)
);
复制代码

    这个表有三个列，列i1和i2是用来存储整数的，列s是用来存储字符串的，如果我们在存储数据的时候填入的不是期望的类型，MySQL会为我们自动转型：

    mysql> INSERT INTO t(i1, i2, s) VALUES('100', '100', 200);
Query OK, 1 row affected (0.01 sec)

mysql>
复制代码

    我们为列i1和i2填入的值是一个字符串值：'100'，列s填入的值是一个整数值：200，虽然说类型都不对，但是由于自动转型的存在，在插入数据的时候字符串'100'会被转型为整数100，整数200会被转型成字符串'200'，所以最后插入成功。

类型转换的注意事项

  1. MySQL会尽量把值转换为表达式中需要的类型，而不是产生错误。

    按理说'23sfd'这个字符串无法转换为数字，但是MySQL规定只要字符串的开头部分包含数字，那么就把这个字符串转换为头部的数字，如果开头并没有包含数字，那么将被转换成0，比方说这样：

    '23sfd'         →   23
'2018-03-05'    →   2018
'11:30:32'      →   11
'sfd'           →   0
复制代码

    看个例子：

    mysql> SELECT '23sfd' + 0, 'sfd' + 0;
+-------------+-----------+
| '23sfd' + 0 | 'sfd' + 0 |
+-------------+-----------+
|          23 |         0 |
+-------------+-----------+
1 row in set, 2 warnings (0.00 sec)

mysql>
复制代码

    不过需要注意的是，这种强制转换不能用于存储数据中，比方说这样：

    mysql> INSERT INTO t(i1, i2, s) VALUES('sfd', 'sfd', 'aaa');
ERROR 1366 (HY000): Incorrect integer value: 'sfd' for column 'i1' at row 1
mysql>
复制代码

    由于i1和i2列需要整数，而填入的字符串'sfd'并不能顺利的转为整数，所以报错了。

  2. 在运算时会自动提升操作数的类型。

    我们知道不同数据类型能表示的数值范围是不一样的，在小的数据类型经过算数计算后得出的结果可能大于该可以表示的范围。比方说t表中有一条记录如下：

    mysql> SELECT * FROM t;
+------+------+------+
| i1   | i2   | s    |
+------+------+------+
|  100 |  100 | 200  |
+------+------+------+
1 row in set (0.00 sec)

mysql>
复制代码

    其中的i1列和i2列的类型都是TINYINT，而TINYINT能表示的最大正整数是127，如果我们把i1列的值和i2列的值相加会发生什么呢？请看：

    mysql> SELECT i1 + i2 FROM t;
+---------+
| i1 + i2 |
+---------+
|     200 |
+---------+
1 row in set (0.00 sec)

mysql>
复制代码

    可以看到最后的结果是200，可是它已经超过TINYINT类型的表示范围了。其实在运算的过程中，MySQL自动将整数类型的操作数提升到了BIGINT，这样就不会产生超过原类型的取值范围了。类似的，有浮点数的运算过程会把操作数自动转型为DOUBLE类型。

分组数据

复杂的数据统计

前边介绍了一些用来统计数据的聚集函数，我们可以方便的使用这些函数来统计出某列数据的行数、最大值、最小值、平均值以及整列数据的和。但是有些统计是比较麻烦的，比如说老师想根据成绩表分别统计处'母猪的产后护理'和'论萨达姆的战争准备'这两门课的平均分，那我们需要下边两个查询：

mysql> SELECT AVG(score) FROM student_score WHERE subject = '母猪的产后护理';
+------------+
| AVG(score) |
+------------+
|    73.0000 |
+------------+
1 row in set (0.00 sec)

mysql> SELECT AVG(score) FROM student_score WHERE subject = '论萨达姆的战争准备';
+------------+
| AVG(score) |
+------------+
|    73.2500 |
+------------+
1 row in set (0.00 sec)

mysql>
复制代码

创建分组

如果课程增加到20门怎么办呢？我们一共需要写20个查询语句，这样神烦哎。为了在一条查询语句中就完成这20条语句的任务，所以引入了分组的概念，就是针对某个列，将该列的值相同的记录分到一个组中。拿subject列来说，按照subject列分组的意思就是将subject列的值是'母猪的产后护理'的记录划分到一个组中，将subject列的值是 '论萨达姆的战争准备'的记录划分到另一个组中，如果subject列还有别的值，则划分更多的组。其中被分组的列我们称之为分组列。所以在student_score表中按照subject列分组后的图示就是这样：

![image_1c7e31ldj11th1s0v4ok1r5719bf9.png-143.7kB][1]

subject列中有多少不重复的课程，那就会有多少个分组。幸运的是，只要我们在GROUP BY子句中添加上分组列就好了，MySQL会帮助我们自动建立分组来方便我们统计信息，

mysql> SELECT subject, AVG(score) FROM student_score GROUP BY subject;
+-----------------------------+------------+
| subject                     | AVG(score) |
+-----------------------------+------------+
| 母猪的产后护理              |    73.0000 |
| 论萨达姆的战争准备          |    73.2500 |
+-----------------------------+------------+
2 rows in set (0.01 sec)

mysql>
复制代码

这个查询的过程是把数据按照subject中的值将所有的数据分成两组，然后对每个分组中的score列调用AVG函数进行数据统计，而不是对整个表的所有记录的score列进行统计。

在使用分组的时候必须要意识到，分组的存在仅仅是为了方便我们统计每个分组中的信息，所以我们只需要把分组列和聚集函数放到查询列表处就好，非分组列出现在查询列表中是非法的！比方说上边对subject列进行分组的例子，我们不能直接将number列放在查询列表中，就像这样：

mysql> SELECT number FROM student_score GROUP BY subject;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'xiaohaizi.student_score.number' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
mysql>
复制代码

可以看到，如果直接把一个非分组列放到查询列表中会报出一个ERROR。

小贴士：

我们是想查询按照`subject`分组后，各个分组中的记录的`score`列的平均分是多少，你把`number`放到查询列表是想表达个啥？正因为把这个`非分组列`摆在这与我们想要查询的东西八竿子打不着，会让服务器懵逼，所以会报出一个`ERROR`！
复制代码

带有WHERE子句的分组查询

上边的例子是将表中每条记录都划分到某个分组中，我们也可以在划分分组的时候就将某些记录过滤掉，这时就需要使用WHERE子句了。比如老师觉得各个科目的平均分太低了，所以想先把分数低于60分的记录去掉之后再统计平均分，就可以这么写：

mysql> SELECT subject, AVG(score) FROM student_score WHERE score >= 60 GROUP BY subject;
+-----------------------------+------------+
| subject                     | AVG(score) |
+-----------------------------+------------+
| 母猪的产后护理              |    89.0000 |
| 论萨达姆的战争准备          |    82.3333 |
+-----------------------------+------------+
2 rows in set (0.00 sec)

mysql>
复制代码

这个过程可以分成两个步骤理解：

  1. 将记录进行过滤后分组。

    在进行分组的时候将过滤到不符合WHERE子句的记录，所以，最后的分组情况其实是这样的（少于60分的记录被过滤掉了）：

    ![image_1c7e57i9phkbqarek21j6o97813.png-103.9kB][2]

  2. 对各个分组进行数据统计。

    统计之后就产生了上述的结果。

过滤分组的统计信息

有时候分组的统计信息产生的太多了，假设subject列中有20门学科，那就会产生20个分组，如果想对这些统计信息做进一步的过滤处理，那就需要使用HAVING子句了。比方说老师想要平均分大于73分的课程，就可以这么写：

mysql> SELECT subject, AVG(score) FROM student_score GROUP BY subject HAVING AVG(score) > 73;
+-----------------------------+------------+
| subject                     | AVG(score) |
+-----------------------------+------------+
| 论萨达姆的战争准备          |    73.2500 |
+-----------------------------+------------+
1 row in set (0.00 sec)

mysql>
复制代码

需要注意的是，我们使用HAVING子句只是想过滤已经划分好的分组，所以通常只根据分组的聚集函数的值组成的布尔表达式来过滤分组。比如上例中的AVG(score) > 73就是一个由聚集函数组成的布尔表达式，所有平均分不大于73分的分组都将被过滤掉。

比方说老师想过查询最高分大于98分的课程的平均分，可以这么写：

mysql> SELECT subject, AVG(score) FROM student_score GROUP BY subject HAVING MAX(score) > 98;
+-----------------------+------------+
| subject               | AVG(score) |
+-----------------------+------------+
| 母猪的产后护理        |    73.0000 |
+-----------------------+------------+
1 row in set (0.00 sec)

mysql>
复制代码

分组和排序

如果我们想对各个分组查询出来的汇总数据进行排序，需要为查询列表中有聚集函数的表达式添加别名，比如想按照各个学科的平均分从大到小降序排序，可以这么写：

mysql> SELECT subject, AVG(score) AS avg_score FROM student_score GROUP BY subject ORDER BY avg_score DESC;
+-----------------------------+-----------+
| subject                     | avg_score |
+-----------------------------+-----------+
| 论萨达姆的战争准备          |   73.2500 |
| 母猪的产后护理              |   73.0000 |
+-----------------------------+-----------+
2 rows in set (0.01 sec)

mysql>
复制代码

嵌套分组

有时候按照某个列进行分组太笼统，也就是说一个分组内可以被继续划分成更小的分组。比方说对于student_info表来说，我们可以先按照department来进行分组，所以可以被划分为2个分组：

![image_1c7e7psvl50i1icl19vpujjfd09.png-156.4kB][3]

我们觉得这样按照department分组后，各个分组可以再按照major来继续分组，从而划分成更小的分组，所以再次分组之后的样子就是这样：

![image_1c7e9f21il3a1ao6duddp3kj3m.png-136.5kB][4]

所以现在有了2个大分组，4个小分组，我们把这种对大的分组下继续分组的的情形叫做嵌套分组，如果你乐意，你可以继续把小分组划分成更小的分组。我们只需要在GROUP BY子句中把各个分组列依次写上，用逗号,分隔开就好了。比如这样：

mysql> SELECT department, major, COUNT(*) FROM student_info GROUP BY department, major;
+-----------------+--------------------------+----------+
| department      | major                    | COUNT(*) |
+-----------------+--------------------------+----------+
| 航天学院        | 电子信息                 |        1 |
| 航天学院        | 飞行器设计               |        1 |
| 计算机学院      | 计算机科学与工程         |        2 |
| 计算机学院      | 软件工程                 |        2 |
+-----------------+--------------------------+----------+
4 rows in set (0.00 sec)

mysql>
复制代码

可以看到，在嵌套分组中，聚集函数将作用在最后一个分组列上，在这个例子中就是major列。

使用分组注意事项

使用分组来统计数据给我们带来了非常大的便利，但是要随时提防有坑的地方：

  1. 如果分组列中含有NULL值，那么NULL也会作为一个独立的分组存在。

  2. 如果存在多个分组列，也就是嵌套分组，聚集函数将作用在最后的那个分组列上。

  3. 如果查询语句中存在WHERE子句和ORDER BY子句，那么GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前。

  4. 非分组列不能单独出现在检索列表中(可以被放到聚集函数中)。

  5. GROUP BY子句后也可以跟随表达式(但不能是聚集函数)。

    上边介绍的GROUP BY后跟随的都是表中的某个列或者某些列，其实一个表达式也可以，比如这样：

    mysql> SELECT concat('专业：', major), COUNT(*) FROM student_info GROUP BY concat('专业：', major);
+-----------------------------------+----------+
| concat('专业：', major)           | COUNT(*) |
+-----------------------------------+----------+
| 专业：电子信息                    |        1 |
| 专业：计算机科学与工程            |        2 |
| 专业：软件工程                    |        2 |
| 专业：飞行器设计                  |        1 |
+-----------------------------------+----------+
4 rows in set (0.00 sec)

mysql>
复制代码

    MySQL会根据这个表达式的值来对记录进行分组，使用表达式进行分组的时候需要特别注意，检索列表中的表达式和GROUP BY子句中的表达式必须完全一样，而且不能使用别名。不过一般情况下我们也不会用表达式进行分组，所以基本没啥用～

  6. WHERE子句和HAVING子句的区别。

    WHERE子句在数据分组前进行过滤，作用于每一条记录，WHERE子句过滤掉的记录将不包括在分组中。而HAVING子句在数据分组后进行过滤，作用于整个分组。

简单查询语句中各子句的顺序

我们上边介绍了查询语句的各个子句，但是除了SELECT之外，其他的子句全都是可以省略的。如果在一个查询语句中出现了多个子句，那么它们之间的顺序是不能乱放的，顺序如下所示：

SELECT [DISTINCT] 查询列表
[FROM 表名]
[WHERE 布尔表达式]
[GROUP BY 分组列表 [HAVING 分组过滤条件] ]
[ORDER BY 排序列表]
[LIMIT 开始行, 限制条数]
复制代码

其中中括号[]中的内容表示可以省略，我们在书写查询语句的时候必须严格遵守这个子句顺序，不然会报错的！

总结

  1. 可能发生转换的场景有下边这几种：

    • 把操作数类型转换为适合操作符计算的相应类型。
    • 将函数参数转换为该函数期望的类型。
    • 存储数据时，把某个值转换为某个列需要的类型。

  2. 类型转换时需要注意的一些地方：

    • MySQL会尽量把值转换为表达式中需要的类型，而不是产生错误。

    • 在运算时可能会自动提升操作数的类型。

  3. MySQL使用GROUP BY子句创建分组，方便我们统计信息。分组的存在仅仅是为了方便我们统计每个分组中的信息，所以我们只需要把分组列和聚集函数放到查询列表处就好。

  4. 对每个分组的统计信息进行过滤使用HAVING子句。

  5. 使用分组注意事项：

    • 如果分组列中含有NULL值，那么NULL也会作为一个独立的分组存在。

    • 如果存在多个分组列，也就是嵌套分组，聚集函数将作用在最后的那个分组列上。

    • 如果查询语句中存在WHERE子句和ORDER BY子句，那么GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前。

    • 非分组列不能单独出现在检索列表中(可以被放到聚集函数中)。

    • GROUP BY子句后也可以跟随表达式(但不能是聚集函数)。

    • WHERE子句在数据分组前进行过滤，作用于每一条记录，WHERE子句过滤掉的记录将不包括在分组中。而HAVING子句在数据分组后进行过滤，作用于整个分组。

  6. 简单的查询语句的格式如下：

    


    
SELECT [DISTINCT] 查询列表
[FROM 表名]
[WHERE 布尔表达式]
[GROUP BY 分组列表 [HAVING 分组过滤条件] ]
[ORDER BY 排序列表]
[LIMIT 开始行, 限制条数]
复制代码

    其中各个部分的含义如下：

    • SELECT 查询列表：

      指定要查询的对象，可以是任意的合法表达式，不过通常我们都是指定列名为查询对象的。

    • [DISTINCT]：

      在查询列表前指定DISTINCT，会将结果集中相同的记录去除掉。

    • FROM 表名

      指定要查询的表。

    • WHERE 布尔表达式

      对表中的记录进行过滤，符合指定布尔表达式的记录将被加入到结果集。

    • GROUP BY 分组列表 [HAVING 分组过滤条件]：

      在统计数据的时候，可以按照分组列表中的列或者表达式将结果分成若干组，分组之后可以使用聚集函数对各组数据进行统计。如果有过滤分组的需求，可以使用HAVING子句来进行过滤。

    • ORDER BY 排序列表：

      查询语句并不保证得到的结果集中记录是有序的，我们可以通过指定ORDER BY子句来明确要排序的列。

    • LIMIT 开始行, 限制条数：

      如果最后查询的得到的结果集中记录太多，我们可以通过LIMIT子句来指定结果集中开始查询的行以及一次查询最多返回的记录条数。

小册

本系列专栏都是MySQL入门知识，想看进阶知识可以到小册中查看：MySQL是怎样运行的链接 。小册的内容主要是从小白的角度出发，用比较通俗的语言讲解关于MySQL内核的一些核心概念，比如记录、索引、页面、表空间、查询优化、事务和锁等，总共的字数大约是三四十万字，配有上百幅原创插图。主要是想降低普通程序员学习MySQL内核的难度，让学习曲线更平滑一点～

Python社区是高质量的Python/Django开发社区
本文地址：http://www.python88.com/topic/29761
Reply
 
375 次点击  
登录后回复
关于   移动版   ·   三行代码   ·   今天看啥   ·   Code   ·   link之家   ·   卧龙搜索   ·   藏经阁   ·   小百科
Py学习 - 专注于Python技术发展的社区(原Django社区)
沪ICP备11025650号