SQL复杂应用实际使用总结

1.查询语句的执行顺序

from、where、groupby、select、having、order by、limit

2.多个表信息关联查询

涉及表：dev,level,template

SQL语句：

select a.id,b.name nameRoom,a.catagory,a.sound,a.ledid,group_concat(a.id_level) idLevel,group_concat(c.name) title, group_concat(c.info) info,a.template,d.json from dev a left join level b on a.id_level = b.id left join level c on b.parentid =c.id left join template d on a.`template` = d.`template` WHERE a.id = '40:2C:76:6C:2A:1E' group by a.id 

 查询结果：

将上面SQL语句简化：

select * from dev a left join level b on a.id_level = b.id left join level c on b.parentid =c.id left join template d on a.`template` = d.`template` WHERE a.id = '40:2C:76:6C:2A:1E'

查询到是两行记录：

left join level b on a.id_level = b.id 

 left join level c on b.parentid =c.id

 

 left join template d on a.`template` = d.`template`

 然后加上前面的条件：

select a.id,b.name nameRoom,a.catagory,a.sound,a.ledid,group_concat(a.id_level) idLevel,group_concat(c.name) title, group_concat(c.info) info,a.template,d.json from dev a left join level b on a.id_level = b.id left join level c on b.parentid =c.id left join template d on a.`template` = d.`template` WHERE a.id = '40:2C:76:6C:2A:1E'

 group_concat函数把符合条件的结果用逗号分隔多个记录值到查询结果，比如上图中idLevel、title的值。

就得到最初查询结果。group by a.id发现没有实际作用

3.SQL支持分页查询

完成SQL后台支持后，对前端开放端口类似如下：

返回报文如下：