大家好,我是anyux。本文介绍MySQL数据库的分组操作,语句拼接,列转行操作。
关于group by 的sql_mode
only_full_group_by
说明:仅是MySQL5.7中存在,5.6和8.0都没有,查看sql_mode
select @@sql_mode;
在带有group by 子句的select中,select后的条件列(非主键列),要么是group by 后面的列,要么需要在函数中
示例group by 错误
select user,host from mysql.user group by user;
报错内容
ERROR 1055 (42000): Expression 2 of SELECT list is not in GROUP BY clause and contains nonaggregated column "mysql.user.Host" which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
解释以上报错 select 查询的字段不在 group by 子句中,并且未使用聚合函数包含"mysql.user.Host",这和sql_mode=only_full_group_by 不兼容 临时关闭sql_mode
set session sql_mode="STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";
永久关闭sql_mode 在/etc/my.cnf中找到[mysqld],粘贴以下内容,保存退出,重启MySQL务
sql_mode="STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
concat的使用
可以将多个列合并成一个列,做列值拼接
select concat(user,host) as "user@host" from mysql.user;
group_concat的使用
可以做列转行
use school;select cno as 学号, group_concat(score) as 成绩总览 from sc group by cno;