mysql之查询常用关键字一
时间查询sysdate()
SELECT t.* FROM testgroup.m_user t where t.created >sysdate();
排序order by
排序逆序
SELECT t.* FROM testgroup.m_user t where t.created <sysdate() order by id desc;
排序正序
SELECT t.* FROM testgroup.m_user t where t.created <sysdate() order by id;
分组GROUP BY
排序前
配合count
select user_id,count(*) from testgroup.m_blog group by user_id;
配合AVG
select user_id,avg(user_id) from testgroup.m_blog group by user_id;
配合SUM
select user_id,sum(user_id) from testgroup.m_blog group by user_id;
搭配MAX
select user_id,max(id) from testgroup.m_blog group by user_id;
搭配MIN
select user_id,min(id) from testgroup.m_blog group by user_id;
限制条数
select user_id,id from testgroup.m_blog limit 3;
筛选分组后的数据having
要查询id加起来大于12的user_id,我们不能直接通过where来筛选,因为数据库中不存在这样一条记录
不实用having
select sum(id) from testgroup.m_blog group by user_id
使用having
select sum(id) from testgroup.m_blog group by user_id HAVING sum(id)>12;