mysql 经典练习题（mysql 5.7.5及以上版本注意规范only_full_group_by）

注意点：mysql 5.7.5及以上版本新增了依赖检测功能

mysql 5.7.5 以上版本下，运行group by 或者 order by 语句可能报如下错误。在之前版本则不报错

mysql> select S.id 学生编号,S.name 学生姓名,avg(score) 平均成绩 from student S inner join sc on S.id=sc.s_id group by S.id having avg(score)>=60 order by avg(score) desc;
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'practice.S.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'practice.S.name'

报错原因为：mysql 5.7.5 以上版本，新增了依赖检测功能，sql_mode=only_full_group_by

执行以下语句查询是否开启了only_full_group_by

mysql> select @@GLOBAL.sql_mode

出现上图就确认是only_full_group_by引起的报错，也就是你写的sql语句不符合myslq的group by规范。具体可参照官方文档。

解决方法有两种

一. 网上流行一种解决方法，，将错误字段加上ANY_VALUE()函数，即可正常运行，得到想要的结果。

如下报错，错误字段是S.name

Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'practice.S.name'...

重写

mysql> select S.id 学生编号,ANY_VALUE(S.name) 学生姓名,avg(score) 平均成绩 from student S inner join sc on S.id=sc.s_id group by S.id having avg(score)>=60 order by avg(scoore) desc;

即可得到正常执行得到预期结果。

不推荐此方法，书写不规范，再用多余的代码来弥补不规范，得不偿失。

报错原因是：不符合新版本严格的功能依赖性的检测，以下举例：

SELECT co.Name, COUNT(*)
FROM countrylanguage cl, country co
WHERE cl.CountryCode = co.Code
GROUP BY co.Code;
co.Code是一个主键 co，因此所有列co 都在功能上依赖于它，如使用此表示法表示的：

{co.Code} -> {co.*}
因此，co.name在功能上依赖于 GROUP BY列并且查询是有效的。

将相关字段加上主键，规范依赖性，即可正常查询不会报错。

二. 将mysql 的only_full_group_by 配置删除

执行以下两行sql语句，即可删除only_full_group_by 配置，回到mysql 5.7.5之前版本一样的效果

set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

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';

创建student表

CREATE TABLE Student(Id VARCHAR(10), Name VARCHAR(10), Age DATETIME, Sex VARCHAR(10), PRIMARY KEY(Id));
INSERT INTO Student VALUES('01' , '赵雷' , '1990-01-01' , '男');
INSERT INTO Student VALUES('02' , '钱电' , '1990-12-21' , '男');
INSERT INTO Student VALUES('03' , '孙风' , '1990-05-20' , '男');
INSERT INTO Student VALUES('04' , '李云' , '1990-08-06' , '男');
INSERT INTO Student VALUES('05' , '周梅' , '1991-12-01' , '女');
INSERT INTO Student VALUES('06' , '吴兰' , '1992-03-01' , '女');
INSERT INTO Student VALUES('07' , '郑竹' , '1989-07-01' , '女');
INSERT INTO Student VALUES('08' , '王菊' , '1990-01-20' , '女');

创建course表

CREATE TABLE Course(Id VARCHAR(10) NOT NULL, Name VARCHAR(10), T_Id VARCHAR(10), PRIMARY KEY(Id), FOREIGN KEY(T_id) REFERENCES Teacher(Id));
INSERT INTO Course VALUES('01' , '语文' , '02');
INSERT INTO Course VALUES('02' , '数学' , '01');
INSERT INTO Course VALUES('03' , '英语' , '03');

创建teacher表

CREATE TABLE Teacher(Id VARCHAR(10) NOT NULL, Name VARCHAR(10), PRIMARY KEY(Id));
INSERT INTO Teacher VALUES('01' , '张三');
INSERT INTO Teacher VALUES('02' , '李四');
INSERT INTO Teacher VALUES('03' , '王五');

创建sc表

CREATE TABLE SC(S_Id VARCHAR(10), C_Id VARCHAR(10), Score DECIMAL(18,1), FOREIGN KEY(S_Id) REFERENCES Student(Id), FOREIGN KEY(C_Id) REFERENCES Course(Id));
INSERT INTO SC VALUES('01' , '01' , 80);
INSERT INTO SC VALUES('01' , '02' , 90);
INSERT INTO SC VALUES('01' , '03' , 99);
INSERT INTO SC VALUES('02' , '01' , 70);
INSERT INTO SC VALUES('02' , '02' , 60);
INSERT INTO SC VALUES('02' , '03' , 80);
INSERT INTO SC VALUES('03' , '01' , 80);
INSERT INTO SC VALUES('03' , '02' , 80);
INSERT INTO SC VALUES('03' , '03' , 80);
INSERT INTO SC VALUES('04' , '01' , 50);
INSERT INTO SC VALUES('04' , '02' , 30);
INSERT INTO SC VALUES('04' , '03' , 20);
INSERT INTO SC VALUES('05' , '01' , 76);
INSERT INTO SC VALUES('05' , '02' , 87);
INSERT INTO SC VALUES('06' , '01' , 31);
INSERT INTO SC VALUES('06' , '03' , 34);
INSERT INTO SC VALUES('07' , '02' , 89);
INSERT INTO SC VALUES('07' , '03' , 98);

--------------------------分------------------割---------------------线---------------------------

练习题 ---> 先不要看答案，自己试着写出来，再来看答案，效果最好。

1. 查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数

2. 查询同时存在" 01 "课程和" 02 "课程的学生信息和课程分数

3. 查询存在" 01 "课程但不存在" 02 "课程的学生id和课程分数(不存在时显示为 null )

4. 查询不存在" 01 "课程但存在" 02 "课程的学生id和课程分数(不存在时显示为 null )

5. 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩，并按平均成绩从大到小排序

6. 查询在 SC 表存在成绩的学生信息

7. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )

8. 查有成绩的学生编号，姓名，选课总数，总成绩

9 .查询「李」姓老师的数量

--------------------------分------------------割---------------------线---------------------------

答案

1. 查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数

SELECT S.*, A.*, B.C_Id, B.Score FROM Student S, (SELECT * FROM SC WHERE C_Id=1)A INNER JOIN (SELECT * FROM SC WHERE C_Id=2)B ON A.S_Id=B.S_Id WHERE A.Score>B.Score AND S.Id=A.S_Id;

2. 查询同时存在" 01 "课程和" 02 "课程的学生信息和课程分数

SELECT S.*, A.*, B.S_Id, B.Score FROM Student S, (SELECT * FROM SC WHERE C_Id=1)A INNER JOIN (SELECT * FROM SC WHERE C_Id=2)B ON A.S_Id=B.S_Id WHERE S.Id=A.S_Id;

3. 查询存在" 01 "课程但不存在" 02 "课程学生id和课程分数(不存在时显示为 null )

SELECT * FROM (SELECT * FROM SC WHERE C_Id=1)A LEFT JOIN (SELECT * FROM SC WHERE C_Id=2)B ON A.S_Id=B.S_Id WHERE B.Score IS NULL;

4. 查询不存在" 01 "课程但存在" 02 "课程的学生id和课程分数(不存在时显示为 null )

SELECT * FROM (SELECT * FROM SC WHERE C_Id=1)A RIGHT JOIN (SELECT * FROM SC WHERE C_Id=2)B ON A.S_Id=B.S_Id WHERE A.Score IS NULL;

5. 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩，并按平均成绩从大到小排序

SELECT S.Id, S.Name, AVG(Score)平均成绩 FROM Student S INNER JOIN SC ON S.Id=SC.S_Id GROUP BY S_Id HAVING AVG(Score)>=60 ORDER BY AVG(Score) DESC;

6. 查询在 sc 表存在成绩的学生信息

SELECT S.* FROM Student S WHERE S.Id IN (SELECT S_Id FROM SC);

7. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )

SELECT S.Id, S.Name, A.选课总数, A.总成绩 FROM Student S LEFT JOIN (SELECT S_Id, COUNT(C_Id)选课总数, SUM(Score)总成绩 FROM SC GROUP BY S_Id)A ON S.Id=A.S_Id;

8. 查有成绩的学生编号，姓名，选课总数，总成绩

SELECT S.Id, S.Name, A.选课总数, A.总成绩 FROM Student S INNER JOIN (SELECT S_Id, COUNT(C_Id)选课总数, SUM(Score)总成绩 FROM SC GROUP BY S_Id)A ON S.Id=A.S_Id;

9 .查询「李」姓老师的数量

SELECT COUNT(*)李姓老师数量 FROM Teacher WHERE Name RLIKE '^李.*';

mysql 经典练习题（mysql 5.7.5及以上版本注意规范only_full_group_by）

Python自动化测试研究院： 560151970 （q群）

B站主页：https://space.bilibili.com/403609135