mysql关键字小结2

时间/日期函数 time()和date()
date用来查询日期,用法示例

SELECT * FROM `user` where DATE(create_time) = '2022-06-14'

日期范围限制搜索

SELECT * FROM `user` where DATE(create_time) BETWEEN
'2022-06-14' AND '2022-06-20'

数值处理函数

聚集函数

SELECT AVG(salary) FROM `employees`

使用示例

count(*) 返回特定列所有的行数.

SELECT COUNT(*) FROM `employees`

SELECT COUNT(email) FROM `user`

返回有email的用户账号数据.

一个sql里可以使用多次函数,例如

SELECT
    COUNT( gdp ) AS world_count,
    AVG( gdp ) AS world_gdp 
FROM
    world

数组分组
GROUP BY示例

SELECT sex,COUNT(*) AS aslary_sex FROM `salary`

SELECT sex,COUNT(*) AS aslary_sex FROM `salary`
GROUP BY sex

group by必须放置在where语句之后,order by之前.

having 对where查询出来的数据进一步过滤.

SELECT username ,COUNT(*) AS usernum FROM `userorder` WHERE orderprice > 10 GROUP BY userid

SELECT username ,COUNT(*) AS usernum FROM `userorder` WHERE orderprice > 10 GROUP BY userid
HAVING
    COUNT( * ) > 2;

ORDER BY 和 GROUP BY

SELECT username, SUM(orderprice) AS usernum FROM `userorder` GROUP BY userid

使用order by

SELECT username, SUM(orderprice) AS usernum FROM `userorder` GROUP BY userid ORDER BY usernum

依照和的大小排序.