企业网站seo推广,鹰潭做网站的,房子如何上网站做民宿,国外免费域名网站作者#xff1a;青石路原文#xff1a;cnblogs.com/youzhibing/p/11516154.htmlGROUP BY 后 SELECT 列的限制标准 SQL 规定#xff0c;在对表进行聚合查询的时候#xff0c;只能在 SELECT 子句中写下面 3 种内容#xff1a;通过 GROUP BY 子句指定的聚合键、聚合函数(SUM …作者青石路原文cnblogs.com/youzhibing/p/11516154.htmlGROUP BY 后 SELECT 列的限制标准 SQL 规定在对表进行聚合查询的时候只能在 SELECT 子句中写下面 3 种内容通过 GROUP BY 子句指定的聚合键、聚合函数(SUM 、AVG 等)、常量。我们来看个例子我们有 学生班级表(tbl_student_class) 以及 数据如下 DROPTABLEIFEXISTS tbl_student_class;CREATETABLE tbl_student_class (idint(8)unsignedNOTNULL AUTO_INCREMENTCOMMENT自增主键,snovarchar(12)NOTNULLCOMMENT学号,cnovarchar(5)NOTNULLCOMMENT班级号,cnamevarchar(20)NOTNULLCOMMENT班级名,PRIMARYKEY (id))COMMENT学生班级表;-- ------------------------------ Records of tbl_student_class-- ----------------------------INSERTINTO tbl_student_classVALUES (1,20190607001,0607,影视7班);INSERTINTO tbl_student_classVALUES (2,20190607002,0607,影视7班);INSERTINTO tbl_student_classVALUES (3,20190608003,0608,影视8班);INSERTINTO tbl_student_classVALUES (4,20190608004,0608,影视8班);INSERTINTO tbl_student_classVALUES (5,20190609005,0609,影视9班);INSERTINTO tbl_student_classVALUES (6,20190609006,0609,影视9班);我们想统计各个班(班级号、班级名)一个有多少人、以及最大的学号我们该怎么写这个查询 SQL 我想大家应该都会SELECT cno,cname,count(sno),MAX(sno)FROM tbl_student_classGROUPBY cno,cname;可是有人会想了cno 和 cname 本来就是一对一cno 一旦确定cname 也就确定了那 SQL 是不是可以这么写 SELECT cno,cname,count(sno),MAX(sno)FROM tbl_student_classGROUPBY 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_modeonly_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’依然允许且只显示warning2、如果在非严格模式下设置了NO_ZERO_DATE效果与上面一样’0000-00-00’ 允许但显示warning如果没有设置NO_ZERO_DATEno warning当做完全合法的值3、NO_ZERO_IN_DATE情况与上面类似不同的是控制日期和天是否可为 0 即 2010-01-00 是否合法NO_ENGINE_SUBSTITUTION使用 ALTER TABLE 或 CREATE TABLE 指定 ENGINE 时 需要的存储引擎被禁用或未编译该如何处理。启用 NO_ENGINE_SUBSTITUTION 时那么直接抛出错误不设置此值时CREATE用默认的存储引擎替代ATLER不进行更改并抛出一个 warningSTRICT_TRANS_TABLES设置它表示启用严格模式。注意 STRICT_TRANS_TABLES 不是几种策略的组合单独指 INSERT、UPDATE 出现少值或无效值该如何处理1、前面提到的把 ‘’ 传给int严格模式下非法若启用非严格模式则变成 0产生一个warning2、Out Of Range变成插入最大边界值3、当要插入的新行中不包含其定义中没有显式DEFAULT子句的非NULL列的值时该列缺少值默认模式当我们没有修改配置文件的情况下MySQL 是有自己的默认模式的版本不同默认模式也不同-- 查看 MySQL 版本SELECTVERSION();-- 查看 sql_modeSELECT 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_classGROUPBY 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、一般来说单元素集合的属性和其唯一元素的属性是一样的。这种只包含一个元素的集合让人觉得似乎没有必要特意地当成集合来看待但是为了保持理论的完整性我们还是要严格区分元素和单元素集合参考《SQL基础教程》《SQL进阶教程》(END)关注公众号查看更多优质文章 最近整理一份Java资料《Java从0到1》覆盖了Java核心技术、JVM、Java并发、SSM、微服务、数据库、数据结构等等。获取方式关注公众号并回复Java领取更多Java内容陆续奉上。明天见(ω)♡
