mysql查询总结

最近一段时间，业务代码基础跟查询相关的比较多，所以有必要对mysql 的查询语句做一下总结；

1。普通的查询语句：

select column1 from tableName1   where condition1(条件1 ,条件2等等);

        2.left join 之类的；

含义：lleft join :左链接，先满足左右的，不管后面接的on是否满足;right join :反之亦然;

如:  select columnA from tableA a left joio tableB b on a.id=b.a_id;

inner join ; 两边条件都满足；跟双表的where有些相似;

3.group by :分组函数；    像统计一些排名时经常要用过，这里要特别注意 select   columnA from tableA group by columnA; (前面的columnA 与columnA相一致，以及分组函数的定义);

4.子查询，实际当中需要的子查询是很常见的；一种是：select column from table where id in (select id from tableB);

        4.2:select column,(select columnB from tableB where  b.id=a.id) from tableA a 

  

如SELECT u.id, u.real_name, u.mobile, year(curdate())-if(length(card_no)=18,substring(card_no,7,4),if(length(card_no)=15,concat('19',substring(card_no,7,2)),null)) as年龄, 
case if(length(card_no)=18, cast(substring(card_no,17,1) as UNSIGNED)%2, if(length(card_no)=15,cast(substring(card_no,15,1) as UNSIGNED)%2,3)) 
when 1 then '男'
when 0 then '女'
end as 性别,u.add_time as 注册时间,
(select sum(due_capital) from s_user_due_detail where status=1 and user_id=u.id) as 在投金额,
 min(s.add_time) as 首投时间, s.inv_succ as 首投金额, w.wallet_totle as 钱包余额, p.user_interest+p.extra_interest as 标的利率,
d.duration_day as 投资天数,round((SELECT
  
    TIMESTAMPDIFF(
    DAY,
    min(start_time),
    max(start_time)
 ) / (count(1) - 1)
 
FROM
s_user_due_detail  where user_id=u.id
),2) as 复投间隔,TIMESTAMPDIFF(
    DAY,
    u.add_time,
    min(s.add_time) 
 ) as 注册到首投间隔,
(select count(1) from s_investment_detail s1 where s1.user_id=u.id) as 投资次数,max(s.add_time) as 最后投资时间,
TIMESTAMPDIFF(DAY,max(s.add_time),CURDATE()) 未投资天数
from s_user  u left join s_investment_detail  s on  u.id = s.user_id left join s_user_account w on s.user_id = w.user_id LEFT JOIN s_project p on p.id = s.project_id LEFTJOIN s_user_due_detail d on d.project_id = p.id
  where u.id!=0 and u.real_name is not null and real_name!='' GROUP BY u.id

这里需要注意的 是，在子查询中，子查询的条件语句，只能嵌套一层；

3。当然除了上面的这些以外比较常用的外，还是行列转换，时间函数，及一些间隔函数；