mysql 常用查询语句(持续更新)_mysql查询语句

1.字符串拼接 —— CONCAT

select  CONCAT('用户名' ,'01') from  user
1

2. 数据去重 —— DISTINCT()

	SELECT	DISTINCT	b.garageId,
					b.AccepterDate 
				FROM
					(
                   SELECT garageId,MAX(AccepterDate) AS AccepterDate FROM  behavior  GROUP BY garageId
					) a
					LEFT JOIN p_recordbehavior b ON a.garageId = b.garageId 
					AND a.AccepterDate = b.AccepterDate 
1
2
3
4
5
6
7
8

3. 查询加序号 —— (@i:=@i+1)

 SET @i=0;
 select systime,(@i:=@i+1)'序号'   from zh_testing as a where flag=0 
1
2

4. 转换时间格式 —— DATE_FORMAT

SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s');
1

5. order by 排序

1.使用 cese when 
 ORDER BY case when t.UpdateTime is null then t.createtime else t.UpdateTime end desc  
 
1
2
3

6. group by + having

having子句用于筛选分组之后的各种数据，通常与“group by”联合使用，该语句弥补了where关键字无法与聚合函数联合使用的不足。
having 后面可以使用 count , sum

SELECT gongcheng_id,count(1) from  gcx_yanghu_repair GROUP BY gongcheng_id HAVING count(1)>1
1

7.left(str, length) 截取字符串，便于匹配

select d.AQ_LH_ID,b.AQ_ID  from aq_jcb as b LEFT JOIN aq_lhjcd as d on  d.AQ_LH_ID =left(b.AQ_ID, 10) 
1

8.模糊查询 instr(content, “”)，类似于like,可以加快查询速度

select d.AQ_LH_ID,b.AQ_ID  from aq_jcb as b LEFT JOIN aq_lhjcd as d on  d.AQ_LH_ID =left(b.AQ_ID, 10) 
where instr(b.AQ_yhnr, "sss") > 0 
1
2

同上图

9.多行转一列 GROUP_CONCAT() +group by

拼接后使用","分割

select GarageId,GROUP_CONCAT(station_id) from p_aggregate_station GROUP BY GarageId
1

10.列转行 通常使用 case when + 条件 + then + 值 + end + 名字，

参考链接：
https://blog.csdn.net/Martin_chen2/article/details/121407417

11.根据条件查询，FIND_IN_SET(str,strlist) 和 in ()

使用 in() 查询数字时，常常会有引号，如“1,2,3”,进而只查询第一个数字，影响查询结果，使用FIND_IN_SET () 则不会有这有这种情况。

SELECT sheshi as sheshimingc FROM `cn_point` WHERE BM_CODE in(546,833) GROUP BY sheshi；

SELECT sheshi as sheshimingc FROM `cn_point` WHERE  FIND_IN_SET(BM_CODE,"546,833") GROUP BY sheshi ；

1
2
3
4