内置函数
数学
| 函数名 | 描述 |
|---|---|
| ABS(x) | 返回 x 的绝对值 |
| CEILING(x) | 返回大于 x 的最小整数值 |
| FLOOR(x) | 返回小于 x 的最大整数值 |
| MOD(x,y) | 返回 x/y 的模（余数） |
| PI() | 返回 pi 的值（圆周率） |
| RAND() | 返回 ０ 到 １ 内的随机值 |
| ROUND(x,y) | 返回参数 x 的四舍五入的有 y 位小数的值 |
| TRUNCATE(x,y) | 返回数字 x 截短为 y 位小数的结果 |
聚合
| 函数名 | 描述 |
|---|---|
| AVG(col) | 返回指定列的平均值 |
| COUNT(col) | 返回指定列中非 NULL 值的个数 |
| MIN(col) | 返回指定列的最小值 |
| MAX(col) | 返回指定列的最大值 |
| SUM(col) | 返回指定列的所有值之和 |
字符
| 函数名 | 描述 |
|---|---|
| CONCAT(s1, s2…, sn) | 将 s1, s2…, sn 连接成字符串 |
| CONCAT_WS(sep, s1, s2…, sn) | 将 s1, s2…, sn 连接成字符串，并用 sep 字符间隔 |
| TRIM(str) | 去除字符串首部和尾部的所有空格 |
| LTRIM(str) | 从字符串 str 中切掉开头的空格 |
| RTRIM(str) | 返回字符串 str 尾部的空格 |
日期
| 函数名 | 描述 |
|---|---|
| CURDATE() 或 CURRENT_DATE() | 返回当前的日期 |
| CURTIME() 或 CURRENT_TIME() | 返回当前的时间 |
| TIMESTAMPDIFF(part, date1, date2) | 返回 date1 到 date2 之间相隔的 part 值，part 是用于指定的相隔的年或月或日等 |
分组
- 分组后，只能查询 分组的列 和 聚合列
- 运行顺序：from -> join… on… -> where -> group by -> select -> having -> order by -> limit
-- 1. 查询员工分布的居住地，以及每个居住地有多少名员工，显示 员工大于等于 40 的居住地
SELECT
location, count(id) as empnumber
FROM
employee
GROUP BY
location
HAVING
empnumbe r>= 40
-- 2. 查询所有薪水在 10000 以上的员工的分布的居住地，然后仅得到聚集地大于 30 的结果
SELECT
location, count(id) as empnumber
FROM
employee
WHERE
salary >= 10000
GROUP BY
location
HAVING
count(id) >= 30
练习
-- 1. 查询腾讯每个部门的员工数量
SELECT
d.`name`, COUNT(e.id) as number
FROM
company as c
INNER JOIN department as d on c.id = d.companyId
INNER JOIN employee as e on d.id = e.deptId
WHERE
c.`name` like '%腾讯%'
GROUP BY
d.id, d.`name`;
-- 2. 查询每个公司的员工数量
SELECT c.`name`, COUNT(e.id) as number
FROM company as c INNER JOIN department as d on c.id = d.companyId
INNER JOIN employee as e on d.id = e.deptId
GROUP BY c.id, c.`name`
-- 3. 查询所有公司 5 年内入职的居住在万家湾的女员工数量
SELECT
c.`name`,
CASE
WHEN r.number is NULL THEN 0
ELSE r.number
END as number
FROM
company c
LEFT JOIN (
SELECT
c.id,
c.`name`,
COUNT(e.id) as number
FROM
company as c
INNER JOIN department as d on c.id = d.companyId
INNER JOIN employee as e on d.id = e.deptId
WHERE
TIMESTAMPDIFF(YEAR,e.joinDate,CURDATE()) <= 5 AND e.location like '%万家湾%'
GROUP BY
c.id, c.`name`
) as r on c.id = r.id
-- 4. 查询腾讯所有员工分布在哪些居住地，每个居住地的数量
SELECT
e.location,
count(e.id) as empnumber
FROM
company as c
INNER JOIN department as d on c.id = d.companyId
INNER JOIN employee as e on d.id = e.deptId
WHERE
c.`name` LIKE '%腾讯%'
GROUP BY
e.location
-- 5. 查询员工人数大于 200 的公司信息
SELECT
*
FROM
company
WHERE
id in (
SELECT
c.id
FROM
company as c
INNER JOIN department as d on c.id = d.companyId
INNER JOIN employee as e on d.id = e.deptId
GROUP BY
c.id, c.`name`
HAVING
count(e.id) >= 200
)
-- 6. 查询腾讯公司里比它平均工资高的员工
SELECT
e.*
FROM
company as c
INNER JOIN department as d on c.id = d.companyId
INNER JOIN employee as e on d.id = e.deptId
WHERE
c.`name` LIKE '%腾讯%'
AND
e.salary > (
-- 查询腾讯的平均薪资
SELECT
avg(e.salary)
FROM
company as c
INNER JOIN department as d on c.id = d.companyId
INNER JOIN employee as e on d.id = e.deptId
WHERE
c.`name` LIKE '%腾讯%'
)
-- 7. 查询腾讯所有名字为两个字和三个字的员工对应人数
SELECT
CHAR_LENGTH(e.`name`) as 姓名长度,
COUNT(E.ID) as 员工数量
FROM
company as c
INNER JOIN department as d on c.id = d.companyId
INNER JOIN employee as e on d.id = e.deptId
WHERE
c.`name` LIKE '%腾讯%'
GROUP BY
CHAR_LENGTH(e.`name`)
HAVING
姓名长度 in (2, 3)
-- 8. 查询每个公司每个月的总支出薪水，并按照从低到高排序
SELECT
c.`name`,
SUM(e.salary) as sumofsalary
FROM
company as c
INNER JOIN department as d on c.id = d.companyId
INNER JOIN employee as e on d.id = e.deptId
GROUP BY
c.id, c.`name`
ORDER BY
sumofsalary
打赏作者
您的打赏是我前进的动力
微信
支付宝
MySql👉 联表查询
上一篇
评论
