数据库-函数和分组

函数和分组

内置函数

1. 数学

   • ABS(x) 返回x的绝对值
   • CEILING(x) 返回大于x的最小整数值
   • FLOOR(x) 返回小于x的最大整数值
   • MOD(x,y) 返回x/y的模 (余数)
   • PI() 返回pi的值(圆周率)
   • RAND() 返回0到1内的随机值
   • ROUND(x.y) 返回参数x的四舍五入的有y位小数的值
   • TRUNCATE(x,y) 返回数字x截短为y位小数的结果

   SELECT ROUND(3.1415926,3);  -- 3.142
   SELECT TRUNCATE(3.1415926,3);  -- 3.141
   

2. 聚合

   • AVG(col) 返回指定列的平均值
   • COUNT(col) 返回指定列中非NULL值的个数
   • MIN(col) 返回指定列的最小值
   • MAX(col) 返回指定列的最大值
   • SUM(col) 返回指定列的所有值之和

   SELECT count(id) as 员工数量，
   avg(salary) as 平均薪资，
   sum(salary) as 总薪资，
   min(salary) as 最小薪资
   FROM employee;
   

3. 字符

   • CONCAT(s1,s2… , sn) 将s1,s2...sn连接成字符串
   • CONCAT_ WS(sep,s1,s2... ,sn) 将s1.,s2.... ,sn连接成字符串，并用sep字符间隔
   • TRIM(str) 去除字符串首部和尾部的所有空格
   • LTRIM(tr) 从字符串str中切掉开头的空格
   • RTRIM(str) 返回字符串str尾部的空格

   SELECT CONCAT_WS('@',`name`,salary)
   FROM employee;
   -- 张云@19413.45
   

4. 日期

   • CURDATE()或CURRENT_ DATE() 返回当前的日期
   • CURTIME(或CURRENT_TIME() 返回当前的时间
   • TIMESTAMPDIFF(part, date1,date2) 返回date1到date2之间相隔的part值，part是用于指定的相隔的年或月或日等

   SELECT TIMESTAMPDIFF(HOUR, '2010-1-1 11:11:11', '2010-1-2 11:11:12'); 
   -- 24
   
   -- 计算年龄
   SELECT *,
   TIMESTAMPDIFF(YEAR, birthday, CURDATE()) as age
   from employee
   ORDER BY age;
   

分组

运行顺序：

• from
• join…on…
• where
• group by
• select
• having
• order by
• limit

分组后，只能查询分组的列和聚合列

-- 查询员工分布的居住地，以及每个居住地有多少名员工
-- 天府三街
SELECT location, count(id) as empnumber
FROM employee
GROUP BY location
-- 分组后进行筛选
HAVING empnumber>=40

-- 查询所有薪水在10000以上的员工的分布的居住地，然后仅得到聚集地大于30的结果
SELECT location,count(id) as empnumber
FROM employee
WHERE salary>=10000
GROUP BY location
HAVING count(id)>=30