报错原因
使用GROUP BY 语句违背了sql_mode=only_full_group_by,在MySQL数据库版本为5.7以上的版本,默认开启了 ONLY_FULL_GROUP_BY SQL模式,在此模式下,对于group by操作,如果在select语句中的查询列没有在group by中出现,那么这个SQL就是非法的,因为列不在group by语句中,所以设置了sql_mode=only_full_group_by的数据库,在使用group by时就会报错,换句话说,拒绝选择列表、HAVING 条件或 ORDER BY 列表引用非聚合列的查询,这些列既不在 GROUP BY 子句中命名,也不在功能上依赖于(唯一确定的)GROUP BY 列。
注意
不是说SELECT xx,xx必须是GROUP BY中的列,如SELECT 聚集函数(不在GROUP中的列)也能正常执行
例如
现有两表Student表以及SC表(选课表)。
执行语句
select cno,count(sc.sno),count(student.sno) from student,sc group by cno;
红色部分可以发现sc.sno与studen.sno都没有在GROUP BY的列中,但执行语句依旧正常,因为使用了聚集函数。
而不把sc.sno放在聚集函数中,则执行错误。
select cno,sc.sno from student,sc group by cno;
解决方法
临时解决(重启mysqld后失效)
1.直接在mysql-cli层面做设置
select @@global.sql_mode;
2.将ONLY_FULL_GROUP_BY从sql_mode中移除:
set @@global.sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
3.重启MySQL客户端
注意不要关闭mysqld,否则修改会复原。
在MySQL-server层面修改sql_mode
永久生效,重启依然有效
优化mysql语句
使用派生表查询
例子
BOOKSTORAGE表:
存书(书号,书名,出版社,版次,出版日期,作者,书价,进价,数量)
CREATE TABLE BOOKSTORAGE(
isbn VARCHAR(10) PRIMARY KEY,
name VARCHAR(10) NOT NULL,
publisher VARCHAR(10),
edition VARCHAR(10),
date DATE,
writer VARCHAR(10),
price FLOAT CHECK(price>=0),
pprice FLOAT CHECK(pprice>=0),
number INT CHECK(number>=0)
);
销售(日期,书号,数量,单价)
CREATE TABLE SALE(
date DATE,
isbn VARCHAR(10),
number INT CHECK(number>=0),
price FLOAT CHECK(price>=0),
FOREIGN KEY(isbn) REFERENCES BOOKSTORAGE(isbn)
);
注意先建立BOOKSTORAGE表再建立SALE表,因为SALE表外键参考BOOKSTORAGE,所以要先建立BOOKSTORAGE
插入数据
INSERT
INTO BOOKSTORAGE
VALUES('1','西游记','人民文学出版社','第三版','2003/1/1','吴承恩',20.1,15.5,150),
('2','水浒传','人民文学出版社','第二版','2005/9/1','施耐庵',23.9,17.1,200),
('3','三国演义','中华书局出版社','第五版','2008/6/1','罗贯中',26.3,15.8,210),
('4','红楼梦','人民文学出版社','第四版','2001/6/1','曹雪芹',22.3,17.2,190);
INSERT
INTO SALE
VALUES('2023-3-12','1','30',17.8),
('2023-3-15','1','20',20.1),
('2023-3-12','2','25',18.8),
('2023-3-15','2','25',23.9),
('2023-3-12','3','15',18.6),
('2023-3-15','3','30',26.3),
('2023-3-12','4','22',19.5),
('2023-3-15','4','12',22.3);
注意先插入BOOKSTORAGE,因为SALE参考BOOKSTORAGE
插入结果
求解问题
列出所有日期的销售报表,包括书名、数量和合计金额(每一种书的销售总金额)
方法1修改sql_mode
mysql语句
SELECT SALE.date,BOOKSTORAGE.name,SALE.number,ROUND(SUM(SALE.price*SALE.number),2) money
FROM SALE,BOOKSTORAGE
WHERE SALE.isbn=BOOKSTORAGE.isbn
GROUP BY SALE.isbn,SALE.DATE
ORDER BY SALE.date;
查询出所有的sql_mode
select @@global.sql_mode;
移除ONLY_FULL_GROUP_BY
set @@global.sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
重启mysql后,执行语句
查询出所有的sql_mode
发现没有ONLY_FULL_GROUP_BY
重启mysqld后,再次查询sql_mode
发现有ONLY_FULL_GROUP_BY
方法2优化mysql语句
SELECT SALE2.DATE,name,SALE2.number,SALE2.PRICE
FROM BOOKSTORAGE,(SELECT isbn,date,SUM(number),ROUND(SUM(price*number),2) FROM SALE GROUP BY SALE.isbn,SALE.DATE) AS SALE2(isbn,date,number,price)
WHERE SALE2.isbn=BOOKSTORAGE.isbn
ORDER BY SALE2.DATE ASC;
思考
问题
student表结构
执行mysql语句,select 选择的列不仅仅只有sno,为什么正常输出?
select * from student group by sno;
答案
因为sno属性是主码,即primary key,经测试发现group by **,**为主码的属性名时,select语句中的查询列没有在group by中出现,也是允许的。