mysql常用语句总结

一些自己不常用 最近用到的sql语句 记录一下
1.临时表

将查到的数据临时存在一张表 取名（t）上 用于下一个条件的筛选
select * from (select * from 表名 where id in(select max(id) from 表名GROUP BY ip)) t where ip=“127.0.0.1”

2.不包含 not in

state这个字段不等于1的
select * from user where state not in (1);

3.between and

id在1-10之间
SELECT * FROM user WHERE id BETWEEN 1 AND 10;

4.or， 或

prostatus不等于0 或 warnstatus 不等于0 和 id不等于1 记得用括号括起来
select * from warnlog where （prostatus not in (0) or warnstatus not in (0) ）and id not in （1）;

5.左连接、右连接、内连接
以左连接为例 左表为主表查询左表的全部和右表相同的
左表 left join 右表 on 内连接(c.cooperate_name=a.cooperate_name)
select c.ip,c.cooperate_name,fdtime,totaltime,onlioncount,state,logtime,province,city,street,lng,lat from cooperate c left join address a on c.cooperate_name=a.cooperate_name where c.id in(select max(id) from cooperate GROUP BY ip ) and state=1;

6.in

id有许多的时候 用
select * from group where id in (select max(id) from group where cooperate_name=“蚌埠路基站” GROUP BY group_name);
select * from group where id in (1401,1402,1403,1404,1405,1406);

7.GROUP BY 分组、去重

cooperate_name字段分组后就不会查到 cooperate_name相同的数据了
select cooperate_name from cooperate GROUP BY cooperate_name;

8. order by 字段（id） desc 倒序
select * from warnlog ORDER BY id desc;