mysql常用sql

1.微博表blogs：

微博评论表blogs_comm：

两表联合查询将微薄信息跟评论数一起列出的sql语句：

select b.*,count(c.bid) as num from blogs as b left join blogs_comm as c on b.id=c.bid group by c.bid order by b.id

结果：

2.查询一个表t1中某字段(name)同时出现两次的字段名：

表t1数据如下：

select count(1) as num,name from t1 group by name having num=2;

注：如果要对两个字段进行排重，就应该这样写：

select count(1) from t1 group by concat(name,time);

结果：

查询一个表图t1中某字段(name)同时出现两次的记录：

select * from t1 as t where 2=(select count(1) from t1 where t1.name=t.name);

结果：

3.distinct去除重复值：

如下表t1：去除重复的uid(重复的只保留前一条)

(1)select *,count(distinct(uid)) from t1 group by uid;

结果：

(2).如果想在重复的列表中按一定的顺序，只取其中一条：

select *,count(distinct(uid)) from(select * from t1 order by time desc) as t2 group by uid;

结果：

4.一条语句删除多个表的数据：

delete b,c from blogs as b left join comment as c on b.id=c.wid left join collect as l on b.id=l.wid where b.id=27;

 

5.一条sql语句查询a表主键id关联b表sid的多条记录，并按b表的主键id倒叙排列，同时只显示a表中有的记录：

select *,a.id as aid from  a left join (select * from b order by id desc) as i on a.id=i.sid group by i.sid;