之前为了各种统计报表写SQL语句实现不同的需求,查询了很多关于SQL语句里面各种“函数”的一些基本使用,今天刚好有时间,把它整理记录下,以下写的方法,仅仅是实现功能需求,没有考虑优化这些。(第一次写博客,也是自己在实际的工作中用到的一些方法,如有不当之处,欢迎指出,非常感谢!)
1、left join 、right join 左连接/右链接
左连接查询以左边的表为主,右边的为辅,优先展示左边表的查询字段,右边的查询字段有就展示,无则为空,右链接查询则相反。如果对查询结果的所有字段有条件需求,就再where后面添加。也可以用在update和delete中进行联合更新和删除。
另外,这种左/右连接查询和多表查询写法基本没得太大的变化,只是效率感觉快点点。
左:select t1.name,t2.num from Table1 t1 left join Table2 t2 on t1.id=t2.pid where t1.type='1'
右:select t1.name,t2.num from Table1 t1 right join Table2 t2 on t1.id=t2.pid where t1.type='1'
多:select t1.name,t2.num from Table1 t1,Table2 t2 where t1.id=t2.pid and t1.type='1'
2、concat 拼接字符串
对于查询出来的某个字段,可使用concat做一些拼接。基本用法就是concat('拼接的字符串 ',' 拼接的字符串',......),后面可以添加别名区分。
select concat('名称:',t1.name)`newname` from Table1 t1 where t1.type='1'
3、group_concat 查询结果自动拼接用逗号隔开
可以配个distinct、group_by使用,把分组后的结果,拼接成一个字段,中间用逗号隔开,基本使用就是group_concat(字段)。
select group_concat(t1.name)`newname` from Table1 t1 where t1.type='2'
4、union all、union 合并多个查询结果集
union all 是把多个表查询的结果全部合并展示,不去重,union则会去重。注意,多个表中查询的字段、个数和顺序都要一致,而且都是以前面个表查询的字段名为主,每个查询的where条件写在各自查询的后面,这个union我是大部分用来写树形结构的查询的,很不错。
select t1.name from Table1 t1 where t1.type='1' union select t1.name from Table2 t2 where t2.type='2'
5、find_in_set、instr 模糊查询
它们两个的功能和like类似,可作为一种判断条件使用,但实际效果不一样,find_in_set('字段B','表字段A')更适合于精确查询,当前查询的字段A,是否存在和字段B完全相同的结果,如果有就查询出当前行;instr('表字段A','字段B')是模糊查询,当前查询的字段A是否包含字段B,包含就查询出来。
F:select t1.name from Table1 t1 where t1.type='1' and find_in_set('测试名称',t1.name)
I:select t1.name from Table1 t1 where t1.type='1' and instr(t1.name,'测试')
6、case when...then ...else..end 满足某种条件的时候出现新的情况
可对于查询的某个字段做一些基本判断,但满足when后面的情况,就实现then后面的写法,否则就是else后面的,when和then可以写多个,作用有点类似于if else的感觉。
select (case when t1.name is null then (select t2.name from Table2 t2 where t2.type='2') else t1.address end)`newname`
from Table1 t1 where t1.type='1'
7、regexp 正则表达式匹配符
查询字段A后面跟regexp (正则表达式),查询出所有满足条件的数据,也可以当做特殊的模糊查询用。正则表达式也网上寻找。
select t1.name from Table1 t1 where t1.type='1' and t1.name regexp '测试'
8、exists、not exists 条件判断
可用来替代in和not in的写法,效率高点,查询出满足或者是不满足exists 条件的结果集。
select t1.name from Table1 t1 where exists (select 1 from Table2 where t2.type='2' and t2.id=t1.id)
9、if 判断某种结果
类似于case when 那个,对某个查询的结果字段做一些判断处理,基本用法if('条件','true结果','false结果')。也可以用在where后面做一些条件的判断处理,针对不同的情况使用不用的判断条件。
select if(t1.name is not null,'修改','不修改')`newname` from Table1 t1 where t1.type='1'
10、left、right、substr 截取字符串
对于查询出来的结果字段做截取处理,也类似于java中的substring这些。基本使用就是left('字段','截取位数')。
select left(t1.name,2)`newname` from Table1 t1 where t1.type='1'
11、date_format、year 日期函数
针对于判断日期,最好不要使用字符串大于小于判断,还是要假吧意思转换下,类似于这种时间日期转换的还有很多,可以网上自行查找。
select t1.name from Table1 t1 where t1.type='2' and date_format(t1.create_time, '%Y%m') > date_format('2019-11-10','%Y%m')
以上基本就是目前所用过的全部的功能方法,万变不离其宗,很多功能用法基本都是相通的,其中的很多功能还有其他的一些用法,可以网上自行查找,最好还是test一下看看具体效果。
总结:
数据库最本质的作用还是作为存储数据用的,SQL语句的作用我认为还是单纯的查询出结果就行,如果考虑查询语句优化,很多还是不建议在SQL语句中使用。但个人认为不管用什么方法,只要能得到正确结果的,都是好的。