函数

1、内置函数

函数和分组 - 图2

数学

  1. SELECT ABS(-1); -- 1
  2. SELECT ABS(3); -- 3
  4. SELECT CEIL(1.23); -- 2
  5. SELECT CEIL(1.9); -- 2
  7. SELECT FLOOR(1.23); -- 1
  8. SELECT FLOOR(1.93); -- 1
  10. SELECT MOD(1,3); -- 1
  11. SELECT MOD(2,8); -- 2 2/8的余数
  12. SELECT MOD(3,1) -- 0
  14. SELECT PI(); -- 3.141593
  16. SELECT RAND(); -- 其中一次的随机值为 0.34172691809174316
  18. SELECT round(1.5); -- 2
  19. SELECT round(1.25); -- 1
  20. SELECT round(1.2355555, 3); -- 1.236
  21. SELECT round(1, 3); -- 1 // 整数即使传了第二个参数也是保留整数， 不会补足小数
  23. SELECT TRUNCATE(1.3234234,4); -- 1.3234
  24. SELECT TRUNCATE(34,4); -- 34 // 不会补足小数

聚合

聚合函数，查表用
特点，就像一个汇总，所有的行都会汇聚到一行。这样得到的结果表就不能查其他列了。

AVG() 平均值

  1. SELECT AVG(salary) FROM employee;
  3. -- 别名定义
  4. SELECT AVG(salary) AS '平均薪资' FROM employee;

image.pngCOUNT() 计算非NULL个数

  1. SELECT COUNT(id) FROM employee; -- 查出employee.id不为NULL的总数
  3. SELECT COUNT(*) FROM employee; -- 【不推荐写法】* 的查法，就是先把employee表查出来，然后看这个表的每一行，只要每一行中至少一个单元格不为NULL，就算一个数。

image.png

MIN() 最小值获取

  1. SELECT min(salary) FROM employee;

MAX() 最小值获取

  1. SELECT MAX(salary) FROM employee;

SUM() 求和

  1. SELECT SUM(salary) FROM employee;

多个聚合查询

  1. SELECT COUNT(id) AS '员工个数',
  2. AVG(salary) AS '平均薪资',
  3. MAX(salary) AS '最高薪资',
  4. MIN(salary) AS '最低薪资',
  5. SUM(salary) AS '公司总支出'
  6. FROM employee;

image.png

字符

CONCAT()

  1. SELECT CONCAT(`name`, '_', id, '_', location) FROM employee; -- 涂萌紫_2_人民北路

CONCAT_WS()

  1. SELECT CONCAT_WS('/',`name`, location, salary) FROM employee; -- 涂萌紫/人民北路/14684.16

TRIM()

  1. SELECT TRIM('  bar   ');
  3. SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx'); -- 删除指定的首字符x，得到「barxxx」
  5. SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx'); -- 删除指定的首尾字符 x， 得到「bar」
  7. SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz');  -- 删除指定的尾字符 x， 得到「barx」

LTRIM()

  1. SELECT LTRIM('   guo   '); -- 得到‘guo   ’

RTRIM()

  1. SELECT RTRIM('   guo   '); -- 得到‘   guo’

日期

CURDATE()、CURRENT_DATE()

  1. SELECT CURRENT_DATE(); -- 2021-01-19 得到今天

CURTIME()、CURRENT_TIME()

  1. SELECT CURTIME(); -- 22:08:12 得到当前时间点

TIMESTAMPDIFF()

  1. SELECT TIMESTAMPDIFF(MICROSECOND, '2019-2-25', '2021-1-19'); -- 相差毫秒值 59961600000000
  2. SELECT TIMESTAMPDIFF(SECOND, '2019-2-25', '2021-1-19'); -- 相差秒值 59961600
  3. SELECT TIMESTAMPDIFF(MINUTE, '2019-2-25', '2021-1-19'); -- 相差分钟值 999360
  4. SELECT TIMESTAMPDIFF(HOUR, '2019-2-25', '2021-1-19'); -- 相差小时值 16656
  5. SELECT TIMESTAMPDIFF(DAY, '2019-2-25', '2021-1-19'); -- 相差天数 694
  6. SELECT TIMESTAMPDIFF(WEEK, '2019-2-25', '2021-1-19'); -- 相差周 99
  7. SELECT TIMESTAMPDIFF(MONTH, '2019-2-25', '2021-1-19'); -- 相差月 22
  8. SELECT TIMESTAMPDIFF(QUARTER, '2019-2-25', '2021-1-19'); -- 相差季度 7
  9. SELECT TIMESTAMPDIFF(YEAR, '2019-2-25', '2021-1-19'); -- 相差年 1

应用

年龄、司龄、入职天数等跟日期相关的查询

  1. SELECT `name`, 
  2. TIMESTAMPDIFF(year, birthday, CURRENT_DATE)  AS 'age',
  3. TIMESTAMPDIFF(year, joinDate, CURRENT_DATE)  AS '司龄', 
  4. TIMESTAMPDIFF(day, joinDate, CURRENT_DATE)  AS '入职天数' 
  5. FROM employee;

image.png

2、自定义函数

不讲了，自己学

分组【扩展】

用于比较复杂的查询场景。
但是注意，分组后，只能查询分组的列和聚合列

GROUP BY

将重复的数据合并，并对重复数据进行汇总记录

查询员工分布的居住地，以及每个居住地有多少名员工

  1. -- 查询员工分布的居住地，以及每个居住地有多少名员工
  2. SELECT location, -- 分组后，只能查询分组的列和聚合列
  3. COUNT(id) AS '员工数量' -- 聚合列
  4. FROM employee
  5. GROUP BY location
  6. ORDER BY location;

image.png

按照居住地、性别进行分组和查询

  1. -- 可以按照多列分组、这种情况下，也就可以查分组的多列
  2. SELECT location, ismale, -- 多列查询
  3. COUNT(id) AS '员工数量'
  4. FROM employee
  5. GROUP BY location, ismale -- 多列分组
  6. ORDER BY location;

image.png

HAVING

在GROUP BY分组的基础上进行筛选

  1. -- 查询员工分布的居住地、性别，以及每个居住地不同性别有多少名员工
  2. SELECT location, ismale,
  3. COUNT(id) AS '员工数量'
  4. FROM employee
  5. GROUP BY location, ismale
  6. HAVING ismale = 1 -- 分组后进行筛选，只展示男员工
  7. ORDER BY location;

image.png

HAVING可以使用SELECT的别名

  1. 根据数据库而定，有的数据库不支持（因为having的顺序滞后于select）
  2. having 过滤未分组的字段是不支持的。
    1. -- 查询员工分布的居住地、性别，以及每个居住地不同性别有多少名员工
    2. SELECT location,ismale AS sex, -- 定义别名「sex」
    3. COUNT(id) AS '员工数量'
    4. FROM employee
    5. GROUP BY location, ismale
    6. HAVING sex = 1 -- 使用别名进行筛选
    7. -- HAVING id > 10 -- 这样写报错，因为id没有被分组
    8. ORDER BY location;
    image.png

    运行顺序：

    注意：不同的数据库对于HAVING的处理不同，执行顺序不同。有的是放在SELECT之前，有的是之后。

    HAVING + WHERE

    1. SELECT location,ismale AS sex,
    2. COUNT(id) AS '员工数量'
    3. FROM employee
    4. WHERE location LIKE '%天府%'
    5. GROUP BY location, ismale
    6. HAVING sex = 1
    7. ORDER BY location;

    练习题

    1. 查询渡一每个部门的员工数量

    1. SELECT d.name AS '所属部门',
    2. COUNT(e.id) AS '员工数量'
    3. FROM employee AS e
    4. INNER JOIN department AS d ON e.deptId = d.id
    5. inner JOIN company AS c ON d.companyId = c.id
    6. WHERE c.`name` LIKE '%渡一%'
    7. GROUP BY d.name;

    老师

    1. SELECT d.`name`, COUNT(e.id) AS number
    2. FROM company as c INNER JOIN department as d on c.id = d.companyId
    3. INNER JOIN employee as e on d.id = e.deptId
    4. WHERE c.`name` LIKE '%渡一%'
    5. GROUP BY d.id, d.`name`

    2. 查询每个公司的员工数量

    1. -- 我这里根据原题做了进一步的扩展：根据性别进行了分组
    2. SELECT c.name AS '企业',
    3. CASE e.ismale 
    4. WHEN 1 THEN '男' 
    5. ELSE '女'
    6. END '性别',
    7. COUNT(e.id) AS '员工数量'
    8. FROM employee AS e
    9. INNER JOIN department AS d ON d.id = e.deptId
    10. INNER JOIN company AS c ON c.id = d.companyId
    11. GROUP BY c.name, e.ismale

    老师

    1. SELECT c.`name`, COUNT(e.id) AS number
    2. FROM company as c INNER JOIN department as d on c.id = d.companyId
    3. INNER JOIN employee as e on d.id = e.deptId
    4. GROUP BY c.id, c.`name`

    3. 查询所有公司五年内入职的居住在万家湾的女员工数量

    按照下图的思路往下写，报错了，说什么不在子句中。。。

    1055 -表达式#1的SELECT列表不在GROUP BY子句中，包含非聚合列’company.e。id’在功能上不依赖于GROUP BY子句中的列;与sql_mode=only_full_group_by, Time: 0.00000不兼容

image.png

  1. SELECT companyName AS '企业名称',
  2. COUNT(result_table.id) AS '员工数量'
  3. FROM (
  4. -- SQL子句
  5. select e.id,e.location, e.ismale,
  6. -- d.name as 'departmentName',
  7. c.name as 'companyName',
  8. timestampdiff(year, e.joinDate, current_date) as '司龄'
  9. from employee as e
  10. inner join department as d on d.id = e.deptId
  11. inner join company as c on c.id = d.companyId
  12. where timestampdiff(year, e.joinDate, current_date) > 5 
  13. and e.location like '%万家湾%' and e.ismale = 0
  14. order by e.id
  15. ) as result_table
  16. group by result_table.companyName

老师

第一种方法：查出来的之后有数据的公司，没数据的公司不展示（where那里筛选掉了）

  1. SELECT c.`name`, COUNT(e.id) AS 数量
  2. FROM company as c INNER JOIN department as d on c.id = d.companyId
  3. INNER JOIN employee as e on d.id = e.deptId
  4. WHERE TIMESTAMPDIFF(YEAR, e.joinDate, CURDATE()) <= 6 -- 20年卡5，21年卡6
  5. AND e.location LIKE '%万家湾%'
  6. AND e.ismale = 0
  7. GROUP BY c.id, c.`name`

期望没数据的公司，number列展示0。于是需要用子句

  1. SELECT company.`name`,
  2. -- 当显示null时换成0
  3. CASE
  4.     WHEN result_table.number IS    NULL THEN
  5.         0
  6.     ELSE
  7.         result_table.number 
  8. END AS 符合条件的员工数量
  9. -- 将上边的结果表当成一张表，左连接到company表。
  10. FROM company LEFT JOIN (
  11.     SELECT c.id, c.`name`, COUNT(e.id) AS number
  12.     FROM company as c INNER JOIN department as d on c.id = d.companyId
  13.     INNER JOIN employee as e on d.id = e.deptId
  14.     WHERE TIMESTAMPDIFF(YEAR, e.joinDate, CURDATE()) <= 6 -- 20年卡5，21年卡6
  15.     AND e.location LIKE '%万家湾%'
  16.     AND e.ismale = 0
  17.     GROUP BY c.id, c.`name`
  18. ) AS result_table ON result_table.id = company.id


4. 查询渡一所有员工分布在哪些居住地，每个居住地的数量

  1. SELECT location AS '居住地',
  2. COUNT(location) AS '居住人数',
  3. -- companyName AS '企业名称'
  4. FROM (
  5. -- SQL子句
  6. select e.id, e.location,
  7. c.name as 'companyName'
  8. from employee as e
  9. inner join department as d on d.id = e.deptId
  10. inner join company as c on c.id = d.companyId
  11. where c.name like '%渡一%'
  12. ) as result_table
  13. GROUP BY location,companyName

老师

  1. SELECT e.location, COUNT(e.id) AS number
  2. FROM company AS c INNER JOIN department AS d ON c.id = d.companyId
  3. INNER JOIN employee AS e ON d.id = e.deptId
  4. WHERE c.`name` LIKE '%渡一%'
  5. GROUP BY e.location

5. 查询员工人数大于200的公司信息

  1. SELECT c2.*
  2. FROM (
  3. -- -- SQL子句
  4. select c.id
  5. from employee as e
  6. inner join department as d on d.id = e.deptId
  7. inner join company as c on c.id = d.companyId
  8. GROUP BY c.id
  9. HAVING count(e.id) > 200
  10. ) AS result_table
  11. INNER JOIN company AS c2 ON c2.id = result_table.id

老师

在第二题的基础上，加上having过滤即可

  1. SELECT c.`name`, COUNT(e.id) AS number
  2. FROM company as c INNER JOIN department as d on c.id = d.companyId
  3. INNER JOIN employee as e on d.id = e.deptId
  4. GROUP BY c.id, c.`name`
  5. HAVING number > 200

但是这样查出来的表，只显示了公司的名称，不显示其他详细信息。
期望和第三题一样做一个子句查询，补充公司的数据
image.png

  1. SELECT * FROM company
  2. WHERE id in (
  3.     SELECT c.id
  4.     FROM company as c INNER JOIN department as d on c.id = d.companyId
  5.     INNER JOIN employee as e on d.id = e.deptId
  6.     GROUP BY c.id, c.`name`
  7.     HAVING COUNT(e.id) >= 200
  8. )


6. 查询渡一公司里比他平均工资高的员工

  1. SELECT e2.id, 
  2. e2.`name`, 
  3. salary, 
  4. result_table.name AS 'company',
  5. avgSalary
  6. FROM (
  7. -- SQL子句
  8. select c.id, c.`name`, AVG(e.salary) as avgSalary
  9. from employee as e
  10. inner join department as d on d.id = e.deptId
  11. inner join company as c on c.id = d.companyId
  12. where c.name like '%渡一%'
  13. GROUP BY c.id, c.`name`
  14. ) AS result_table
  15. INNER JOIN employee AS e2
  16. WHERE e2.salary > avgSalary
  17. ORDER BY e2.salary

老师

  1. SELECT e.*
  2. FROM company as c INNER JOIN department as d on c.id = d.companyId
  3. INNER JOIN employee as e on d.id = e.deptId
  4. WHERE c.`name` LIKE '%渡一%' AND
  5. e.salary > (
  6.     -- 子句
  7.     SELECT avg(e.salary)
  8.     FROM company as c INNER JOIN department as d on c.id = d.companyId
  9.     INNER JOIN employee as e on d.id = e.deptId
  10.     WHERE c.`name` LIKE '%渡一%'
  11. )
  12. ORDER BY e.salary

7. 查询渡一所有名字为两个字和三个字的员工对应人数

  1. -- 写不出来。。

老师：

  1. SELECT CHAR_LENGTH(e.`name`) as 姓名长度, COUNT(e.`name`) as 员工数量
  2. FROM company as c INNER JOIN department as d on c.id = d.companyId
  3. INNER JOIN employee as e on d.id = e.deptId
  4. WHERE c.`name` LIKE '%渡一%' 
  5. GROUP BY CHAR_LENGTH(e.`name`) -- 按照姓名长度分组
  6. HAVING 姓名长度 in (2,3) -- 这里要么写聚合，要么写别名，已经娶不到e.`name`了

8. 查询每个公司每个月的总支出薪水，并按照从低到高排序

  1. SELECT companyId, companyName, SUM(salary) as salary
  2. FROM three_table_join -- 用了视图
  3. GROUP BY companyId, companyName
  4. ORDER BY salary

老师：

  1. SELECT c.`id`, c.`name`,  SUM(e.salary) AS sumSalary
  2. FROM company as c INNER JOIN department as d on c.id = d.companyId
  3. INNER JOIN employee as e on d.id = e.deptId
  4. GROUP BY c.`name`, c.id
  5. ORDER BY sumSalary -- ORDER BY 顺序最后，所以需要别名