关于java:神奇的-SQLGroup-By-真扎心原来是这样

送大家以下 java 学习材料，文末有支付形式

GROUP BY 后 SELECT 列的限度

规范 SQL 规定，在对表进行聚合查问的时候，只能在 SELECT 子句中写上面 3 种内容：通过 GROUP BY 子句指定的聚合键、聚合函数（SUM、AVG 等）、常量。咱们来看个例子：

咱们有 学生班级表（tbl\_student\_class）以及 数据如下：

DROP TABLE IF EXISTS tbl_student_class;
CREATE TABLE tbl_student_class (id int(8) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  sno varchar(12) NOT NULL COMMENT '学号',
  cno varchar(5) NOT NULL COMMENT '班级号',
  cname varchar(20) NOT NULL COMMENT '班级名',
  PRIMARY KEY (id)
) COMMENT='学生班级表';

-- ----------------------------
-- Records of tbl_student_class
-- ----------------------------
INSERT INTO tbl_student_class VALUES ('1', '20190607001', '0607', '影视 7 班');
INSERT INTO tbl_student_class VALUES ('2', '20190607002', '0607', '影视 7 班');
INSERT INTO tbl_student_class VALUES ('3', '20190608003', '0608', '影视 8 班');
INSERT INTO tbl_student_class VALUES ('4', '20190608004', '0608', '影视 8 班');
INSERT INTO tbl_student_class VALUES ('5', '20190609005', '0609', '影视 9 班');
INSERT INTO tbl_student_class VALUES ('6', '20190609006', '0609', '影视 9 班');

咱们想统计各个班（班级号、班级名）一个有多少人、以及最大的学号，咱们该怎么写这个查问 SQL？我想大家应该都会

SELECT cno,cname,count(sno),MAX(sno) 
FROM tbl_student_class
GROUP BY cno,cname;

可是有人会想了，cno 和 cname 原本就是一对一，cno 一旦确定，cname 也就确定了，那 SQL 是不是能够这么写？

SELECT cno,cname,count(sno),MAX(sno) 
FROM tbl_student_class
GROUP BY cno;

执行报错了：

[Err] 1055 - Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.tbl_student_class.cname' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

提示信息：SELECT 列表中的第二个表达式（cname）不在 GROUP BY 的子句中，同时它也不是聚合函数；这与 sql 模式：ONLY\_FULL\_GROUP\_BY 不相容。

为什么 GROUP BY 之后不能间接援用原表（不在 GROUP BY 子句）中的列？ 莫急，咱们缓缓往下看。

SQL 模式

MySQL 服务器能够在不同的 SQL 模式下运行，并且能够针对不同的客户端以不同的形式利用这些模式，具体取决于 sql\_mode 零碎变量的值。DBA 能够设置全局 SQL 模式以匹配站点服务器操作要求，并且每个应用程序能够将其会话 SQL 模式设置为其本人的要求。

模式会影响 MySQL 反对的 SQL 语法以及它执行的 数据验证查看，这使得在不同环境中应用 MySQL 以及将 MySQL 与其余数据库服务器一起应用变得更加容易。更多详情请查阅官网：Server SQL Modes。

MySQL 版本不同，内容会略有不同（包含默认值），查阅的时候留神与本身的 MySQL 版本保持一致。

SQL 模式次要分两类：语法反对类和数据查看类，罕用的如下

语法反对类

• ONLY_FULL_GROUP_BY

对于 GROUP BY 聚合操作，如果在 SELECT 中的列、HAVING 或者 ORDER BY 子句的列，没有在 GROUP BY 中呈现，那么这个 SQL 是不非法的

• ANSI_QUOTES

启用 ANSI\_QUOTES 后，不能用双引号来援用字符串，因为它被解释为辨认符，作用与 \` 一样。设置它当前，update t set f1=”” …，会报 Unknown column‘’in field list 这样的语法错误

• PIPES_AS_CONCAT

将 || 视为字符串的连贯操作符而非 或 运算符，这和 Oracle 数据库是一样的，也和字符串的拼接函数 CONCAT() 相相似

• NO_TABLE_OPTIONS

应用 SHOW CREATE TABLE 时不会输入 MySQL 特有的语法局部，如 ENGINE，这个在应用 mysqldump 跨 DB 品种迁徙的时候须要思考

• NO_AUTO_CREATE_USER

字面意思不主动创立用户。在给 MySQL 用户受权时，咱们习惯应用 GRANT … ON … TO dbuser 顺道一起创立用户。设置该选项后就与 oracle 操作相似，受权之前必须先建设用户

数据查看类

• NO_ZERO_DATE

认为日期‘0000-00-00’非法，与是否设置前面的严格模式无关

1、如果设置了严格模式，则 NO\_ZERO\_DATE 天然满足。但如果是 INSERT IGNORE 或 UPDATE IGNORE，’0000-00-00’仍然容许且只显示 warning；

2、如果在非严格模式下，设置了 NO\_ZERO\_DATE，成果与下面一样，’0000-00-00’容许但显示 warning；如果没有设置 NO\_ZERO\_DATE，no warning，当做齐全非法的值；

3、NO\_ZERO\_IN\_DATE 状况与下面相似，不同的是管制日期和天，是否可为 0，即 2010-01-00 是否非法；

• NO_ENGINE_SUBSTITUTION

应用 ALTER TABLE 或 CREATE TABLE 指定 ENGINE 时，须要的存储引擎被禁用或未编译，该如何解决。启用 NO\_ENGINE\_SUBSTITUTION 时，那么间接抛出谬误；不设置此值时，CREATE 用默认的存储引擎代替，ATLER 不进行更改，并抛出一个 warning

• STRICT_TRANS_TABLES

设置它，示意启用严格模式。留神 STRICT\_TRANS\_TABLES 不是几种策略的组合，独自指 INSERT、UPDATE 呈现少值或有效值该如何解决：

1、后面提到的把‘’传给 int，严格模式下非法，若启用非严格模式则变成 0，产生一个 warning；

2、Out Of Range，变成插入最大边界值；

3、当要插入的新行中，不蕴含其定义中没有显式 DEFAULT 子句的非 NULL 列的值时，该列短少值；

默认模式

当咱们没有批改配置文件的状况下，MySQL 是有本人的默认模式的；版本不同，默认模式也不同

-- 查看 MySQL 版本
SELECT VERSION();

-- 查看 sql_mode
SELECT @@sql_mode;

咱们能够看到，5.7.21 的默认模式蕴含：

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

而第一个：ONLY\_FULL\_GROUP\_BY 就会束缚：当咱们进行聚合查问的时候，SELECT 的列不能间接蕴含非 GROUP BY 子句中的列。 那如果咱们去掉该模式（从“严格模式”到“宽松模式”）呢？

咱们发现，上述报错的 SQL

-- 宽松模式下 能够执行
SELECT cno,cname,count(sno),MAX(sno) 
FROM tbl_student_class
GROUP BY cno;

能失常执行了，然而个别状况下不举荐这样配置，线上环境往往是“严格模式”，而不是“宽松模式”；尽管案例中，无论是“严格模式”，还是“宽松模式”，后果都是对的，那是因为 cno 与 cname 惟一对应的，如果 cno 与 cname 不是惟一对应，那么在“宽松模式下”cname 的值是随机的，这就会造成难以排查的问题，有趣味的能够去试试。 那为什么会有 ONLY\_FULL\_GROUP\_BY 模式呢？ 咱们持续往下看

阶
–

阶（order）是用来辨别汇合或谓词的阶数的概念。谓词逻辑中，依据输出值的阶数对谓词进行分类。

\= 或者 BETWEEEN 等输出值为一行的谓词叫作 ” 一阶谓词 ”，而像 EXISTS 这样输出值为行的汇合的谓词叫作 ” 二阶谓词 ”（HAVING 的输出值也是汇合，但它不是谓词）。以此类推，三阶谓词=输出值为 ” 汇合的汇合 ” 的谓词，四阶谓词=输出值为 ” 汇合的汇合的汇合 ” 的谓词，然而 SQL 里并不会呈现三阶以上的状况，所以不必太在意。

简略点如下图

谈到了阶，就不得不谈下集合论；集合论是 SQL 语言的根基，因为它的这个个性，SQL 也被称为面向汇合语言。只有从汇合的角度来思考，能力明确 SQL 的弱小威力。通过上图，置信大家也都能看到，这里不做更深刻的解说了，有趣味的能够去查相干材料。

为什么聚合后不能再援用原表中的列

很多人都晓得聚合查问的限度，然而很少有人能正确地了解为什么会有这样的束缚。表 tbl\_student\_class 中的 cname 存储的是每位学生的班级信息。

但须要留神的是，这里的 cname 只是每个学生的属性，并不是小组的属性，而 GROUP BY 又是聚合操作，操作的对象就是由多个学生组成的小组，因而，小组的属性只能是均匀或者总和等统计性质的属性，如下图

询问每个学生的 cname 是能够的，然而询问由多个学生组成的小组的 cname 就没有意义了。对于小组来说，只有 ” 一共多少学生 ” 或者 ” 最大学号是多少？” 这样的问法才是有意义的。

强行将实用于个体的属性套用于个人之上，纯正是一种分类谬误；而 GROUP BY 的作用是将一个个元素划分成若干个子集，应用 GROUP BY 聚合之后，SQL 的操作对象便由 0 阶的 ” 行 ” 变为了 1 阶的 ” 行的汇合 ”，此时，行的属性便不能应用了。

SQL 的世界其实是层级明显的等级社会，将低阶概念的属性用在高阶概念上会导致秩序的凌乱，这是不容许的。此时我置信大家都明确：为什么聚合后不能再援用原表中的列。

单元素汇合也是汇合

当初的集合论认为单元素汇合是一种失常的汇合。单元素汇合和空集一样，次要是为了放弃实践的完整性而定义的。因而对于以集合论为根底的 SQL 来说，当然也须要严格地区分元素和单元素汇合。因而，元素 a 和汇合 {a} 之间存在着十分醒目的层级差异。

a ≠ {a}

这两个层级的区别别离对应着 SQL 中的 WHERE 子句和 HAVING 子句的区别。WHERE 子句用于解决 ” 行 ” 这种 0 阶的对象，而 HAVING 子句用来解决 ” 汇合 ” 这种 1 阶的对象。

总结

1、SQL 严格辨别层级，包含谓词逻辑中的层级（EXISTS），也包含集合论中的层级（GROUP BY）；

2、有了层级辨别，那么实用于个体上的属性就不适用于个人了，这也就是为什么聚合查问的 SELECT 子句中不能间接援用原表中的列的起因；

3、一般来说，单元素汇合的属性和其惟一元素的属性是一样的。这种只蕴含一个元素的汇合让人感觉仿佛没有必要特意地当成汇合来对待，然而为了放弃实践的完整性，咱们还是要严格辨别元素和单元素汇合；