函数

内置函数

  1. 数学
  • ABS(X) 返回x的绝对值 使用方法select ABS(-1),以下函数用法一样
  • CEILING(X) 返回大于x的最小整数值
  • FLOOR(x) 返回小于x的最大整数值
  • MOD(x,y) 返回x/y的模(余数)
  • PI() 返回pi的值(圆周率)
  • RAND() 返回0-1内的随机值
  • ROUND(x,y) 返回参数x的四舍五入的有y位小数的值
  • TRUNCATE(x,y) 返回数字截断为y位小数的结果
  1. 聚合
  • AVG(col) 返回指定列的平均值 select AVG(salary) from employee ,以下函数用法相同
  • count(col) 返回指定列中的非null值得个数
  • MIN(col) 返回指定列的最小值
  • MAX(col) 返回指定列的最大值
  • SUM(col) 返回指定列的所有值之和
  1. 字符
  • CONCAT(s1,s2,…),将s1,s2,…拼接为一个字符串 select CONCAT(name,salary) as 姓名和薪水 from employee 以下函数用法相同
  • CONCAT_WS(SEP,s1,s2,…) 将s1,s2,…;拼接为一个字符串,并用sep链接
  • TRIM(str) 去除字符串首部和尾部的所有空格
  • LTRIM(str) 从字符串str中去掉开头的空格
  • RTRIM(str) 从字符串str中去掉结尾的空格
  1. 日期
  • CURDATE()或CURRENT_DATE() 返回当前日期 select CURDATE(),以下函数用法相同
  • CURTIME()或CURRENT_TIME() 返回当时时间
  • TIMESTAMPDIFF(part,date1,date2) 返回date1到date2之间相隔的part值,part是用于指定的相隔的年或越或日

自定义函数 (不需要)

分组

  1. 分组的时候,只能查询分组列和聚合列
  1. select  location ,count(id) as 员工数 from employee group by location

以上location是分组列,count(id)是聚合列

  1. 当使用了分组之后,条件筛选用HAVING字句
    1. select  location ,count(id) as sumnumer from employee group by location HAVING  sumnumer >40
    以上是按照location分组后,查询总数大于40的数据

demo

  1. 查询渡一每个部门的员工的数量 ``sql select d.name as 部门名称,count(e.id) 员工数量 from company as c inner join department as d on c.id=d.companyId inner join employee as e on e.deptId=d.id WHERE c.name` like ‘%渡一%’ group by d.id,d.name
  2. 2. 查询每个公司的员工数量
  3. ```sql
  4. select c.name as 公司名称,count(e.id) 员工数量 from company as c 
  5. inner join department as d  on c.id=d.companyId 
  6. inner join employee as e  on e.deptId=d.id
  7. group by c.id,c.name
  1. 查询所有公司五年内入职居住在万家湾的女性员工 ```sql select c.name as 公司名称,count(e.id) 员工数量 from company as c inner join department as d on c.id=d.companyId inner join employee as e on e.deptId=d.id where TIMESTAMPDIFF(YEAR,e.joinDate,CURDATE()) <=10 AND e.location like ‘%万家湾%’ and e.ismale=0 group by c.id,c.name
  2. 4. 查询渡一所有员工分布在哪些居住地,每个居住地的数量
  3. ```sql
  4. select e.location as 居住地, count(e.id) as 数量 from  employee as e INNER JOIN department as d on e.deptId=d.id 
  5. inner JOIN company as c on c.id=d.companyId
  6. where c.name like '%渡一%'
  7. GROUP BY e.location

  1. 查询员工人数大于200的公司信息(子查询)

    1. SELECT * from company WHERE id in ( select c.id as id from company as c 
    2. inner join department as d  on c.id=d.companyId 
    3. inner join employee as e  on e.deptId=d.id
    4. group by c.id,c.name
    5. HAVING  count(e.id)>200)

  2. 查询渡一公司里比他平均公司高的员工(子查询) ```sql select e.name,e.salary from employee as e INNER JOIN department as d on e.deptId=d.id inner JOIN company as c on c.id=d.companyId where c.name like ‘%渡一%’ and e.salary> (select AVG(e.salary) from employee as e INNER JOIN department as d on e.deptId=d.id inner JOIN company as c on c.id=d.companyId where c.name like ‘%渡一%’ )

  2. 7. 查询渡一名字是两个字和三个字的人员数量
  3. ```sql
  4. select CHAR_LENGTH(e.name) as 姓名长度,count(e.id) as 人数 from  employee as e INNER JOIN department as d on e.deptId=d.id 
  5. inner JOIN company as c on c.id=d.companyId
  6. where c.name like '%渡一%'  
  7. GROUP BY   CHAR_LENGTH(e.name)
  1. 查询每个公司的每个月的支出总流水,并按照从低到高排序 ``sql select c.name,SUM(e.salary) AS sumsalary from employee as e INNER JOIN department as d on e.deptId=d.id inner JOIN company as c on c.id=d.companyId GROUP BY c.id,c.name` ORDER BY sumsalary asc

```