sql中常用不常见函数部分总结

朴灿烈づ我的快乐病毒、 2022-06-14 06:51 115阅读 0赞

## 1. 多个count合并成一条语句 ##

表结构：student  
id,chinese,math,english,name,number,class,date  
  
  
比如有个场景：需要分别统计这一学期（201705）所有班级 数学成绩 大于90 的人数，  
语文成绩大于90 的人数 和 英语成绩大于 90的人数。  
注意这里关键词是“分别统计”。

普通做法：

select count(chinese) as num,class
    from student
    where date = 201705
    and chinese > 90
    group by class

然后复制三份，将字段替换成 math 和 english  
这样，一下需要查询三次，每次消耗同样的资源。  
  
  
改进的查询方法：

select count(case when chinese > 90 then 1 else 0 end) as num1,
           count(case when math > 90 then 1 else 0 end) as num2,
           count(case when english > 90 then 1 else 0 end) as num3,class
    from student
    where date = 201705
    group by class

一条语句完成，同理其他聚合函数，也可以通过case when 实现一条语句计算多个结果。

## 2. 使用 union 或者 union all 将多个结果合并成一个 ##

有时候，使用多个查询成相同的字段列，或者使用多个 or 或者 in，使用 union 可以节省查询资源  
当然，也要看查询的有没有改进或者使用了索引  
  
  
比如合并两张表中的 a,b 字段

select a,b
    from tab1
    union
    select a,b
    from tab2

nion 与 union all的区别 在于前者去重且排序，后者不去重，不排序

## 3. 多个字段与同一个值比较使用函数代替or ##

比如：

where a>10 or b>10 or c >10

等价于

where greatest(a,b,c) > 10

同理 如果查最小

least(a,b,c) < 10

4. 如何使用 or 或者 in  
在有索引的情况下：in和or效率差不多  
没有索引：in的效率 log(n)  or的效率（n）  
详见分析文章：[http://blog.csdn.net/cws1214/article/details/35239101][http_blog.csdn.net_cws1214_article_details_35239101]  
  
  
5. find\_in\_set 与 join 的一对多比较  
find\_in\_set 适合 集合取值少，查询不多的表  
 一般用此字段查询都会进行全表扫描，比如 hoby字段 存的是多个逗号隔开的爱好 'sing','speak','baskball'

类似于 A in (A,B,C) 的感觉

select * from student where find_in_set('sing',hoby);

join 映射表 可以通过添加索引，加快查询速度

select * from student join student_hoby_mapping on student.id = student_hoby_mapping.sid
     where student_hoby_mapping.hoby = 'sing'

那么一般情况下，如果数据量不大, 且查询不太频繁 ,  考虑使用 find\_in\_set，否则用join 加索引可能更快。  
分析：[https://segmentfault.com/q/1010000000124126/a-1020000000124318][https_segmentfault.com_q_1010000000124126_a-1020000000124318]

6. 查询某个字段在哪些数据库的哪些表中存在

select * from information_schema.columns where column_name='order_id';
    
    -- table_schema 字段为数据库名，table_name为表的名称

[http_blog.csdn.net_cws1214_article_details_35239101]: http://blog.csdn.net/cws1214/article/details/35239101
[https_segmentfault.com_q_1010000000124126_a-1020000000124318]: https://segmentfault.com/q/1010000000124126/a-1020000000124318