mysql中“ “踩坑

mysql中" "踩坑

mysql语句中" "有些坑，今天写sql就遇到这个问题，以下述说我的踩坑过程
测试代码

select student.s_id as "学号",student.s_name as "姓名",student.s_sex as "性别" ,
(select score.s_score from score where score.s_id=student.s_id and score.c_id=(select sourse.c_id from sourse where sourse.c_name="语文")) as "语文",
(select score.s_score from score where score.s_id=student.s_id and score.c_id=(select sourse.c_id from sourse where sourse.c_name="数学")) as "数学",
(select score.s_score from score where score.s_id=student.s_id and score.c_id=(select sourse.c_id from sourse where sourse.c_name="英语")) as "英语",
sum(score.s_score) as "总分",
round(avg(score.s_score),2) as "平均分"
from student left join score on score.s_id=student.s_id
where student.s_id in (1,2,3)
GROUP BY student.s_id ORDER BY "总分" ASC

测试结果

结果分析：
可以看到总分并没有按照我们的原本意愿去排序，sql执行也没有报错，观察上边sql语句，再升序排序语句处，我写的 “总分” 这种格式，这里不能加" "，
正确的写法：

select student.s_id as "学号",student.s_name as "姓名",student.s_sex as "性别" ,
(select score.s_score from score where score.s_id=student.s_id and score.c_id=(select sourse.c_id from sourse where sourse.c_name="语文")) as "语文",
(select score.s_score from score where score.s_id=student.s_id and score.c_id=(select sourse.c_id from sourse where sourse.c_name="数学")) as "数学",
(select score.s_score from score where score.s_id=student.s_id and score.c_id=(select sourse.c_id from sourse where sourse.c_name="英语")) as "英语",
sum(score.s_score) as "总分",
round(avg(score.s_score),2) as "平均分"
from student left join score on score.s_id=student.s_id
where student.s_id in (1,2,3)
GROUP BY student.s_id ORDER BY 总分 ASC

执行结果：

可以看到正确了

分析原因：
我认为在sql语句中加了" "的表示一个值，告诉sql这是一个数据值，在排序那的总分，原本意义是告诉sql要通过总分这一栏的值去比较大小排序，如果给 "总分"这种形式，sql不能将其识别为表的总分栏，所以排序失效，至于没有报错，我还不清楚。
扩展：
当sql语句中出现user等以关键字作为表中的字段时，要加波浪键,这也是一个坑