Mysql 常见错误集锦

此篇收集一些mysql经常容易犯的小错误。

目录

1. 错误代码：Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregate

2. 错误代码： 1248 Every derived table must have its own alias

3. 错误代码： 1111 Invalid use of group function

4. 错误代码：Operand should contain 1 column(s)

---

1. 错误代码：Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregate

先看查询语句

 SELECT salary
 FROM employees
 GROUP BY department_id

改正

 SELECT AVG(salary )
 FROM employees
 GROUP BY department_id

在select中添加了聚合函数，这是由于在以部门号进行聚合过后，没有相应的聚合操作，即经过

 GROUP BY department_id

聚合过后，答案没有进行处理，应该有相应的求最大值最小值平均值之类的操作。

2. 错误代码： 1248 Every derived table must have its own alias

先看查询语句

SELECT MIN(sal)
 FROM(SELECT AVG(salary) sal
 FROM employees
 GROUP BY job_id ) 

错误提示已经恨明显了，每个得到的表都应该有它自己的名字，在From后加上表的别名就可以了

改正：

SELECT MIN(sal)
 FROM(SELECT AVG(salary) sal
 FROM employees
 GROUP BY job_id ) avg_sal

3. 错误代码： 1111 Invalid use of group function

看代码

SELECT MIN(AVG(salary) )
FROM employees
GROUP BY job_id 

mysql中不能聚合函数嵌套，可以嵌套一层，先用子查询实现AVG

改正：

SELECT MIN(sal)
 FROM(SELECT AVG(salary) sal
 FROM employees
 GROUP BY job_id )  avg_sal

4. 错误代码：Operand should contain 1 column(s)

看代码

select university
from user_profile 
where device_id in (select q.device_id,count(q.device_id)
                    from question_practice_detail q
                    group by q.device_id)

原因是in条件后面有多个字段，in后面只能有一个字段。

改正

select university
from user_profile 
where device_id in (select q.device_id
                    from question_practice_detail q
                    group by q.device_id)