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;