mysql 基础(持续更新)

1.where条件转移

sum(if(startTime>xxx and endTime>xxx,money,0)): 满足条件的部分进行求和

count(if(startTime>xxx and endTime>xxx,1,null))：满足条件的部分进行计数

sum(if(startTime>xxx and endTime>xxx,CASE WHEN ... THEN ...,0)): 满足条件的部分进行分类求和

2.IFNULL(sum(case when xxx = '0' then 1 else 0 end),0) as num: 满足条件的累加1

3.json_extract()获取json数据key的value值，方便映射到实体类

json测试数据：

{
    "no": "7",
    "title": "运动方式",
	"text":"测试",
    "content": [{
        "text": "您平时经常进行的运动及时间",
        "item1": [{
            "text": "慢跑 / 快走 / 走跑结合",
            "type": "select",
            "value": "selected"
        }, {
            "text": "上下楼梯 / 爬山",
            "type": "multselect",
            "value": "selected"
        }],
        "item2": [{
            "text": "慢跑222走跑结合",
            "type": "text",
            "value": "慢跑2"
        }, {
            "text": "上下楼梯 / 爬山2",
            "type": "number",
            "value": "33"
        }]
    }]
}

json_extract( t.jsonstr, '$.*' ): 返回全部json的value

["7", "运动方式", [{"text": "您平时经常进行的运动及时间", "item1": [{"text": "慢跑 / 快走 / 走跑结合", "type": "select", "value": "selected"}, {"text": "上下楼梯 / 爬山", "type": "multselect", "value": "selected"}], "item2": [{"text": "慢跑222走跑结合", "type": "text", "value": "慢跑2"}, {"text": "上下楼梯 / 爬山2", "type": "number", "value": "33"}]}]]

json_extract( t.jsonstr, '$.title' ): 返回json中key=”title”的value

"运动方式"

json_extract( t.jsonstr, '$.content' ): 返回json中key=”content”的value

[{"text": "您平时经常进行的运动及时间", "item1": [{"text": "慢跑 / 快走 / 走跑结合", "type": "select", "value": "selected"}, {"text": "上下楼梯 / 爬山", "type": "multselect", "value": "selected"}], "item2": [{"text": "慢跑222走跑结合", "type": "text", "value": "慢跑2"}, {"text": "上下楼梯 / 爬山2", "type": "number", "value": "33"}]}]

json_extract( t.jsonstr, '$**.text' ): 返回最内层所有key=”text”的数据

["您平时经常进行的运动及时间", "慢跑 / 快走 / 走跑结合", "上下楼梯 / 爬山", "慢跑222走跑结合", "上下楼梯 / 爬山2"]

json_extract( t.jsonstr, '$.content[*].item1[*]' ): 返回json中key=content中item1的value

[{"text": "慢跑 / 快走 / 走跑结合", "type": "select", "value": "selected"}, {"text": "上下楼梯 / 爬山", "type": "multselect", "value": "selected"}]

3.using关键字

using等价于join操作中的on，例如两个表根据id字段进行关联，那么以下两种写法是等价的：
1）using(id)
2）on a.id=b.id
以下2个查询是等价的：
select a.name,b.age from test as a
join test2 as b on a.id=b.id

select a.name,b.age from test as a
join test2 as b using(id)