1.怎样写好sql。学习下面几点:
1)mysql的执行顺序,是写sql的核心,很多查询错误多因为对其不了解;
2)如何进行多表查询、优化,这个是很重要的部分;
3)sql语句的函数,sql提供的函数方便了很多操作;
2.mysql语句执行顺序
1)mysql语法顺序
select
from
join
on
where
group by
having
union
order by
limit
2)mysql执行顺序
from
on
join
where
group by
having
select
distinct
union
order by
3.实例如下
表table_1
表table_2
目的:将两表连接,左边全部显示,右表与左表匹配,行可为空。
查询语句如下所示:
select *
from gc_luckygiftalluser lgu
left join gc_luckygiftuserinfo lgi on lgu.userid= lgi.uid
WHERE lgu.luckygiftid = 22 and lgi.luckygiftid = 22;
查询结果:
error,原因:根据执行顺序可知,为from、left join、where、select,执行到left join后结果如下图所示:
紧接着执行where语句中的两个条件,导致左表中luckygiftid为空的消失,正确的sql语句如下所示:
select *
from gc_luckygiftalluser lgu
left join (SELECT * from gc_luckygiftuserinfo where luckygiftid = 22) lgi on lgu.userid= lgi.uid
WHERE lgu.luckygiftid = 22;
注意嵌套查询及where条件的限制。