MySQL 数据库综合实战 错误集合（未完）

 1.使用多次左/右外连接时出现目标不明确问题

（Not unique table/alias）

select Student.s_id,s_name,s_Score,c_id
from Student 
left outer join Score  on Score.s_id = Student.s_id and Score.c_id = 01
left outer join Score  on Score.s_id = Student.s_id and Score.c_id = 02
;

出现 Not unique table/alias  问题

select Student.s_id,s_name,s_Score,c_id
from Student 
left outer join Score  on Score.s_id = Student.s_id and Score.c_id = 01;

如果我们只用上半部分，则不会报错

问题原因是因为我们进行了两次左连接，意味着把Score分为了两张不同的表，就导致MySQL无法确认我们所指的Score.s_id是那一张表的s_id

解决办法： 在表的名称后面加上 缩写，列如：Student=>st，并将全部的Student 用st替换 Score同理 因为是分为两个不同的表，就可以用不同的名字来区分。

select Student.s_id,s_name,s_Score,c_id
from Student st 
left outer join Score sc1 on sc1.s_id = st.s_id and sc1.c_id = 01
left outer join Score sc2 on sc2.s_id = st.s_id and sc2.c_id = 02
;

 注意第一行代码中的名字也要改（整个代码出现的都要用相应的简写替换）否则就会报错

再次修改

select st.s_id,s_name,s_Score,c_id
from Student st 
left outer join Score sc1 on sc1.s_id = st.s_id and sc1.c_id = 01
left outer join Score sc2 on sc2.s_id = st.s_id and sc2.c_id = 02
;

 修改完成之后，由于Score被分成了两个表，那么原来第一行中出现的s_Score又会不明确

解决方法：把第一行的s_Score换成sc1._Score和sc2.s_Score（c_id同理）

select st.s_id,s_name,sc1.s_Score,sc1.c_id,sc2.s_Score,sc2.c_id
from Student st 
left outer join Score sc1 on sc1.s_id = st.s_id and sc1.c_id = 01
left outer join Score sc2 on sc2.s_id = st.s_id and sc2.c_id = 02
;

 全部改完就可以正常输出啦！

优化：两个c_id内容都是一样的可以加到标题，不光可以减轻代码量，也更加简介明了

select st.s_id,s_name,sc1.s_Score as 'Chinese',sc2.s_Score as 'Maths'
from Student st 
left outer join Score sc1 on sc1.s_id = st.s_id and sc1.c_id = 01
left outer join Score sc2 on sc2.s_id = st.s_id and sc2.c_id = 02
;

2. 禁止使用group函数（Invalid use of group function）

select st.s_id,AVG(s_Score) as 'avg'
from Student st,Score se
where st.s_id = se.s_id
and AVG(s_Score) >= 60
group by st.s_id
;

 

 这里出现错误的原因是因为我用AVG函数求出了每个同学的总成绩，本来想直接简单用and加一个筛选条件，可是group函数不是这么用的0.0

下面是正确的用法：

select st.s_id,AVG(s_Score) as 'avg'
from Student st,Score se
where st.s_id = se.s_id
group by st.s_id having AVG(s_Score)>=60
;

 

 3.聚合查询问题

In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'mysql_exampleTest.st.s_id'; this is incompatible with sql_mode=only_full_group_by

在没有GROUP BY的聚合查询中，SELECT列表的表达式#1包含非聚合列“mysql_exampleTest.st._id”；这与sqlmode=only_full_group_by不兼容

select st.s_id,st.s_name,c_id,s_Score,count(c_id)
from Score se 
left join Student st 
on st.s_id = se.s_id
;

 大概意思就是，count会聚合这个表格，但是由于c_id，和s_Score是多行内容无法全部显示，就起了冲突，两边留一边就可以执行（去掉count 加上group by或者是去掉c_id，和s_Score）

 如果给他直接加上group by 会出现新的错误：

Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'mysql_exampleTest.se.c_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

SELECT列表的表达式#3不在GROUP BY子句中，并且包含非聚合列“mysql_exampleTest.se.c_id”，该列在功能上不依赖于GROUP BY子句的列；这与sqlmode=only_full_group_by不兼容

select st.s_id,st.s_name,c_id,s_Score,count(c_id)
from Score se 
left join Student st 
on st.s_id = se.s_id
group by st.s_id
;

解决方法：

 1.去掉count并加上group by，显示多行

select st.s_id,st.s_name,c_id,s_Score
from Score se 
left join Student st 
on st.s_id = se.s_id
;

 

 2.去掉c_id，和s_Score聚合查询

select st.s_id,st.s_name,count(c_id)
from Score se 
left join Student st 
on st.s_id = se.s_id
group by st.s_id
;