统计语句:
-- 《第一种使用count方法》:
SELECT tid,count(*) from ycb_mcs_user_ticket GROUP BY tid with ROLLUP。 加上with ROLLUP是对分组查询后的结果进行统计。
-- 《第二种使用count方法》:
SELECT count( tid = 36 OR NULL ) from ycb_mcs_user_ticket GROUP BY tid
-- 《第三种使用sum方法》:
SELECT sum( IF ( tid = 36,1,0 ) ) from ycb_mcs_user_ticket GROUP BY tid
分页语句:
《使用limit方法》:
SELECT * from ycb_mcs_user_ticket ORDER BY id limit 100,10
-- 改写为以下方式会加快查询速率:
SELECT * from ycb_mcs_user_ticket INNER JOIN (SELECT id from ycb_mcs_user_ticket ORDER BY id limit 100,10 ) as lim USING(id)
JSON字段操作:
-- 查询
SELECT count(*) FROM ycb_mcs_station_settings WHERE JSON_CONTAINS(settings,'{"usable_power":"70"}')
-- 更新
update ycb_mcs_station_settings set settings = json_set(settings,"$.usable_power","70") WHERE JSON_CONTAINS(settings,'{"usable_power":"60"}')