MySQL常见函数总结

一 、

文章地址：https://blog.csdn.net/GDYY3721/article/details/130928144?ops_request_misc=%257B%2522request%255Fid%2522%253A%2522172040150516800225541496%2522%252C%2522scm%2522%253A%252220140713.130102334…%2522%257D&request_id=172040150516800225541496&biz_id=0&utm_medium=distribute.pc_search_result.none-task-blog-2_alltop_positive~default-1-130928144-null-null.142^v100pc_search_result_base2&utm_term=SQL%E9%9D%A2%E8%AF%95%E9%A2%98&spm=1018.2226.3001.4187

4. 如何知道每个员工一年的总收入?
答案: SELECT emp_name, salary * 12 + COALESCE(bonus, 0) FROM employee; 解析:查询结果中可以使用各种运算、函数以及表达式。COALESCE 函数用于将空值转换为 0。
——COALESCE 用于返回参数列表中的第一个非 NULL 值。在表达式 COALESCE(bonus, 0) 中，它检查 bonus 字段的值。如果 bonus 是 NULL，则函数返回 0；如果 bonus 有一个非 NULL 的值，则函数返回该值本身。
8. 确认员工中有没有叫做“张三”、“李四” 或“张飞”的人，有的话查出他们的信息。
答案: SELECT * FROM employee WHERE emp_name IN (‘张三’, ‘李四’, ‘张飞’); 解析:IN 用于查找列表中的任意值。
——在SQL中，使用IN操作符时，其后边确实必须加括号，并且括号内是要比较的值的列表，这些值之间用逗号分隔。这是SQL语法的一部分，用于指定IN操作符应该在哪些值中查找匹配项。

9. 只知道某个员工的姓名里有个“云”字，但不知道具体名字，怎么样查看有哪些这样的员工?
答案: SELECT * FROM employee WHERE emp_name LIKE ‘%云%’; 解析:SQL 中的 LIKE 运算符用于字符串的模式匹配。LIKE 支持两个通配符:% 匹配任意多个字符，_ 匹配单个字符。Oracle 区分大小写，MySQL 不区分大小写。
——原文链接：https://blog.csdn.net/GDYY3721/article/details/130928144

10. 有些员工有奖金(bonus)，另一些没有。怎么查看哪些员工有奖金?
答案: SELECT emp_name, bonus FROM employee WHERE bonus IS NOT NULL; 解析:SQL 中的 NULL 表示空值，意味着缺失或者未知数据。判断空值不能直接使用等于或不等于，而需要使用特殊的 IS NULL 和 IS NOT NULL。
———NULL得用法

11. 在前面我们知道了如何查询女员工，如何查看 2010 年 1 月 1 日之后入职的女员工呢?
答案: SELECT emp_name, sex, hire_date FROM employee WHERE sex = ‘女’ AND hire_date > DATE ‘2010-01-01’; 解析:AND、OR 和 NOT 表示逻辑与、逻辑或和逻辑非，可以用于构造复杂的查询条件。
———在 MySQL 中，虽然 DATE 关键字也用于日期和时间函数，但通常不需要在日期字面量前使用 DATE 关键字（尽管在某些上下文中，如类型转换，它可能会被使用）。在大多数情况下，您可以直接使用字符串字面量（用单引号括起来），只要它遵循 ‘YYYY-MM-DD’ 的格式，MySQL 就会自动将其视为日期。因此，在 MySQL 中，您可能会这样写：
SELECT emp_name, sex, hire_date
FROM employee
WHERE sex = ‘女’ AND hire_date > ‘2010-01-01’;
日期用单引号直接括起来就可以

12. 以下查询会不会出错，为什么?
SELECT * FROM employee WHERE 1 = 0 AND 1/0 = 1; 答案:不会出错，但是查不到任何数据。

解析:因为 SQL 对于逻辑运算符 AND 和 OR 使用短路运算(short-circuit evaluation)。也就是说，只要前面的表达式能够 决定最终的结果，不执行后面的计算。
————and 和 or 在SQL中也执行短路运算

13. 如何去除查询结果中的重复记录，比返回如员工性别的不同取值?
答案: SELECT DISTINCT sex FROM employee; 解析:DISTINCT 用于消除查询结果中的重复值，上面的查询只返回两个不同的性别记录。

16. 员工的姓名是中文，如何按照姓名的拼音顺序进行排序?
答案: – MySQL 实现 SELECT emp_name FROM employee WHERE emp_id <= 10 ORDER BY CONVERT(emp_name USING GBK); – Oracle 实现 SELECT emp_name FROM employee WHERE emp_id <= 10 ORDER BY NLSSORT(emp_name,‘NLS_SORT = SCHINESE_PINYIN_M’); 解析:中文可以按照拼音进行排序，或者按照偏旁部首进行排序。MySQL 中的 GBK 编码支持拼音排序，Oracle 可以指定排 序规则。
———ORDER BY CONVERT(emp_name USING GBK):
这是排序子句，用于指定查询结果的排序方式。
CONVERT(emp_name USING GBK)尝试将emp_name列中的每个值从当前字符集转换为GBK字符集。然而，需要注意的是，CONVERT(… USING …)函数在MySQL中主要用于字符集转换，而不是直接用于排序。
在大多数情况下，排序是基于字符的字节值进行的，而不是基于字符在特定字符集中的表示。因此，如果emp_name列已经是以适合排序的字符集存储的（比如UTF-8），那么转换为GBK可能不会对排序结果产生直接影响，除非转换过程中字符的字节表示发生了显著变化（这通常不是预期的）。
然而，如果emp_name列原本使用的是一种与排序不兼容的字符集（尽管这不太可能是现代数据库中的情况），转换为GBK可能会间接影响排序，因为转换后的字节表示可能更适合某种排序逻辑。但在大多数情况下，这不是CONVERT(… USING …)函数的预期用途。
如果目的是按照某种特定的排序规则（如中文拼音）进行排序，那么应该使用MySQL提供的排序函数或特性，而不是依赖于字符集转换。

18. 薪水最高的 3 位员工都有谁?
答案:---- MySQL 实现
SELECT emp_name, salary FROM employee ORDER BY salary DESC LIMIT 3; 解析:SQL 中用于限制返回数量的关键字是 FETCH，MySQL 使用 LIMIT。

19. 在上面的问题中，如果有 2 个人的排名都是第 3 位，怎么才能都返回(一共 4 条数据)?
答案: – Oracle 12c 实现 SELECT emp_name, salary FROM employee ORDER BY salary DESC FETCH NEXT 3 ROWS WITH TIES; 解析:FETCH 子句支持 WITH TIES 选项，用于返回更多排名相同的数据。另外，还可以使用 PERCENT 按照百分比返回数 据。
——————在MySQL中，如果你想要返回薪水最高的3位员工，但考虑到可能存在并列第三名的情况（即两个或更多人的薪水相同且都是第三高的），你可以使用RANK()或DENSE_RANK()窗口函数来实现这一需求。这两个函数在处理并列排名时有所不同：

RANK()函数在遇到并列排名时，会给予相同的排名，并且下一个排名会跳过相应的位置（例如，如果有两个第一名，则下一个排名是第三名）。
DENSE_RANK()函数则会在并列时给予相同的排名，但下一个排名不会跳过位置（例如，如果有两个第一名，则下一个排名是第二名）。
对于你的需求，使用DENSE_RANK()会更合适，因为它能确保在并列第三名时返回所有相关的记录，即使这意味着返回的记录数会超过3条（在并列情况下）。但是，由于你明确提到想要“如果有 2 个人的排名都是第 3 位，怎么才能都返回(一共 4 条数据)?”，这里我们可以结合DENSE_RANK()和一个子查询或CTE（公用表表达式）来限制结果只返回最高薪水的3个不同排名（即使并列也计算在内）。不过，请注意，这实际上意味着在极端情况下（比如前4名薪水都相同），你可能会返回4条或更多记录。

以下是一个使用DENSE_RANK()的示例SQL查询，它尝试只返回最高薪水的3个不同排名的员工（但请注意，如果前4名薪水相同，它也会返回4条记录）：
WITH RankedSalaries AS ( SELECT emp_id, emp_name, salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS salary_rank FROM employee ) SELECT emp_id, emp_name, salary FROM RankedSalaries WHERE salary_rank <= 3;
这个查询首先使用WITH语句（CTE）来为每个员工的薪水分配一个DENSE_RANK()排名，然后从这个CTE中选择排名在前3的记录。如果前3名中有并列情况，所有并列的员工都会被包括在内。

22. 如何知道每个员工的邮箱长度?
答案: SELECT emp_name, length(email) FROM employee; 解析:length 函数用于返回字符长度。需要注意的是，Oracle 是按照字符数量计算，lengthb 按照字节计算;MySQL 是按照 字节数量计算，char_length 按照字符数量计算。对于汉字这种多字节字符需要注意区分。
———————

24. 以 CSV(逗号分隔符)格式显示员工的姓名、性别、薪水信息，如何写 SQL 查询语句?
答案: – MySQL 实现 SELECT CONCAT_WS(‘,’ emp_name, sex, salary) FROM employee;

– Oracle 实现 SELECT emp_name||‘,’||sex||‘,’||salary FROM employee; 解析:CONCAT 函数用于连接两个字符串。MySQL 中的 CONCAT_WS 扩展了该功能;Oracle 支持使用 || 连接字符串。
———

25. 如何获取员工邮箱中的用户名部分( @ 符号之前的字符串)?
答案: SELECT emp_name, SUBSTR(email, 1, INSTR(email,‘@’) - 1) FROM employee; 解析:此处使用了两个字符串函数，INSTR 函数查找 @ 符号的位置，SUBSTR 函数获取该位置之前的子串。
————————

26. 将员工邮箱中的“.com”替换为“.net”，写出 SQL 语句?
答案: SELECT emp_name, REPLACE(EMAIL, ‘.com’,‘.net’) FROM employee; 解析:REPLACE 函数用于替换字符串中的字串。另外，TRIM 函数用于截断字符串。
—————————————

27. 如何返回随机排序的员工信息?
答案: – MySQL 实现 SELECT emp_name, RAND() FROM employee ORDER BY RAND(); – Oracle 实现 SELECT emp_name, DBMS_RANDOM.VALUE FROM employee ORDER BY DBMS_RANDOM.VALUE; 解析:利用生成随机数的函数进行排序。MySQL 使用 RAND 函数，Oracle 使用 DBMS_RANDOM.VALUE 函数。
———首先，RAND()函数在MySQL中生成一个0到1之间的随机浮点数。当你将这个函数放在SELECT列表中时，它会为每一行生成一个随机的值。但是，当你在ORDER BY子句中再次（隐式地）调用RAND()时，它实际上是为排序过程生成了另一组随机数，这些数与SELECT列表中显示的随机数不一定相同。

如果你的目的是随机排序employee表中的emp_name，你应该只在ORDER BY子句中使用RAND()，如下所示：
SELECT emp_name FROM employee ORDER BY RAND();
这个查询会有效地随机返回employee表中所有员工的emp_name，而不需要在SELECT列表中显式包含RAND()的结果，因为它并不直接影响查询结果的展示。
————————
29. 下图是一个学生成绩表(score)，如何知道每个学生的最高得分?
答案: SELECT student_id, GREATEST(chinese, math, english, history) FROM score;
解析:GREATEST 函数用于返回列表中的最大值，LEAST 函数用于返回列表中的最小值。
—————
30. 如何知道每个员工的工作年限?
答案: SELECT emp_name, EXTRACT( year FROM CURRENT_DATE) - EXTRACT( year FROM HIRE_DATE) FROM employee; 解析:CURRENT_DATE 函数返回当前日期，EXTRACT 函数可以提取日期数据中的各个部分，本例中使用 year 参数获取年 份信息。
——————————————

31. 工资信息比较敏感，不宜直接显示。按照范围显示收入水平，小于 10000 显示为“低收入”，大于等于 10000 并且小于 20000 显示为“中等收入”，大于 20000 显示为“高收入”。如何使用 SQL 实现?
答案: SELECT emp_name, CASE WHEN salary < 10000 THEN ‘低收入’ WHEN salary < 20000 THEN ‘中等收入’ ELSE ‘高收入’

END “薪水等级” FROM employee; 解析:CASE 表达式可以类似于 IF-THEN-ELSE 的逻辑处理。SQL 支持简单 CASE 和搜索 CASE，可以为查询增加基于逻辑 的复杂分析功能。掌握好 CASE 表达式是使用 SQL 进行数据分析的必备技能之一。
———————————————

34. 群发邮件时，多个邮件地址使用分号进行分隔。如何获取所有员工的群发邮件地址?
答案: – MySQL 实现 SELECT GROUP_CONCAT(email SEPARATOR ‘;’) FROM employee; – Oracle 实现 SELECT LISTAGG(email, '; ') WITHIN GROUP (ORDER BY NULL) FROM employee; 解析:使用字符串的聚合函数将多个字符串合并成一个。MySQL 中使用 GROUP_CONCAT 函数，Oracle 使用 LISTAGG 函 数。
————————————
GROUP_CONCAT 有一个默认的长度限制（通常是 1024 字节），如果拼接后的字符串超过了这个长度，它将被截断。你可以通过设置系统变量 group_concat_max_len 来改变这个限制。
你可以通过 ORDER BY 子句在 GROUP_CONCAT 函数内部指定 email 的排序方式，例如 GROUP_CONCAT(email ORDER BY email SEPARATOR ‘;’)。
如果 email 字段包含 NULL 值，这些值在默认情况下会被忽略。你可以使用 COALESCE 函数或其他方法来处理 NULL 值，例如 GROUP_CONCAT(COALESCE(email, ‘N/A’) SEPARATOR ‘;’)。
GROUP_CONCAT(COALESCE(email, ‘N/A’) SEPARATOR ‘;’)
GROUP_CONCAT：这个函数用于将来自多个行的列值连接成一个字符串结果。它通常与 GROUP BY 语句一起使用，以便对表中的行进行分组，并对每组中的值进行拼接。
COALESCE：这个函数返回其参数列表中的第一个非 NULL 值。如果所有的参数都是 NULL，则它返回 NULL。在这个上下文中，它被用来确保如果 email 字段的值为 NULL，则使用 ‘N/A’ 作为替代值。

36. 以下语句能否正常运行，为什么?
SELECT dept_id, COUNT(*), emp_name FROM employee GROUP BY dept_id; 答案:不能运行。解析:使用了 GROUP BY 分组之后，SELECT 列表中只能出现分组字段和聚合函数，不能再出现其他字段。上面的语句中， 按照部门分组后，再查看员工姓名的话，存在逻辑上的错误。因为每个部门有多个员工，应该显示哪个员工呢?
——————————————

37.如果只想查看平均月薪大于 10000 的部门，怎么实现?
select deptid ,avg(salary) from table where ( avg(salary) group by deptid) >= 10000;
答案: SELECT dept_id, AVG(salary) FROM employee GROUP BY dept_id HAVING AVG(salary) > 10000;

解析:HAVING 子句用于对分组后的结果进行过滤，它必须跟在 GROUP BY 之后。

40. 如何通过内连接返回员工所在的部门名称?
select d.dept_id, d.dept_name, e.emp_name from employee e join department d ON (e.dept_id = d.dept_id);
41. 统计每个部门的员工数量，同时显示部门名称信息。如何使用连接查询实现?
答案: SELECT d.dept_name, COUNT(e.emp_name) FROM department d LEFT JOIN employee e ON (e.dept_id = d.dept_id) GROUP BY d.dept_name;
解析:由于某些部门可能还没有员工，不能使用内连接，而需要使用左外连接或者右外连接;否则可能缺少某些部门的结果。
分析：你的数据库中包含department表和employee表，其中department表列出了所有部门，而employee表列出了员工及其所属的部门（通过dept_id字段关联）。这个查询使用了左连接（LEFT JOIN），它的特点是可以保证department表中的所有行都会出现在结果集中，即使它们在employee表中没有匹配的行。
42. 如何知道每个员工的经理姓名(manager)?
答案: SELECT e.emp_name AS “员工姓名”, m.emp_name AS “经理姓名” FROM employee e LEFT JOIN employee m ON (m.emp_id = e.manager) ORDER BY e.emp_id; 解析:通过自连接关联两个员工表，使用左连接是因为有一个员工没有上级，他就是公司的最高领导。
———————————————

48. 以下查询语句的结果是什么?
SELECT * FROM employee WHERE dept_id = (SELECT dept_id FROM department);

答案: 执行出错。解析:外部查询的 WHERE 条件使用了等于号，但是子查询返回了多个值，此时需要使用 IN 来进行匹配。正确的查询语句如 下: SELECT * FROM employee WHERE dept_id IN (SELECT dept_id FROM department); 另外，NOT IN 用于查询不在列表中的值。
————————————————

49.哪些员工的月薪高于本部门的平均值?
回答： select e.name ,salary from e where salary > (select avg(salary) from e group by dept_id ) group by dept_id;
答案: SELECT emp_name, salary FROM employee e WHERE salary > (SELECT AVG(salary) FROM employee WHERE dept_id = e.dept_id);
解析:使用关联子查询获取每个员工所在部门的平均月薪，然后传递给外部查询进行判断。
————————————————
显示员工信息时，增加一列，用于显示该员工所在部门的人数。如何编写 SQL 查询?
回答： select ，（select count() from e group by dept_id) from e where dept_id = e.dept_id;
答案（文心一言）：SELECT
e.employee_id,
e.employee_name,
e.dept_id,
COUNT() OVER (PARTITION BY e.dept_id) AS dept_employee_count
FROM
employee e
WHERE
e.dept_id = (SELECT dept_id FROM department WHERE some_condition); – 假设这返回单个 dept_id；
答案: SELECT emp_name, (SELECT COUNT() FROM employee WHERE dept_id = e.dept_id) AS dept_count FROM employee e;
解析:SELECT 列表中同样可以使用关联子查询。
子查询版本：SELECT
e.emp_name,
(SELECT COUNT(*) AS dept_count FROM employee WHERE dept_id = e.dept_id) AS dept_count
FROM
employee e;

————————
53. 按照部门和职位统计员工的数量，同时统计部门所有职位的员工数据，再加上整个公司的员工数量。如何用一个查询实现?
– Oracle 实现 SELECT dept_id, job_id, COUNT() FROM employee GROUP BY ROLLUP (dept_id, job_id);
——SELECT dept_id, job_id, COUNT() AS employee_count
FROM employee
GROUP BY dept_id, job_id WITH ROLLUP;
分析：这个查询将按 dept_id 和 job_id 对 employee 表中的行进行分组，并计算每个组的员工数量。WITH ROLLUP 修饰符将添加额外的汇总行，首先是按 dept_id 汇总的（此时 job_id 为 NULL），然后是整体汇总（此时 dept_id 和 job_id 都为 NULL）。
结果将类似于以下形式

dept_id	job_id	employee_count
1	A	5
1	B	3
1	NULL	8 – 这是 dept_id=1 的所有职位的总计
2	C	4
2	NULL	4 – 这是 dept_id=2 的所有职位的总计
NULL	NULL	12 – 这是所有部门和职位的总计

解析:GROUP BY 支持扩展的 ROLLUP 选项，可以生成按照层级进行汇总的结果，类似于财务报表中的小计、合计和总 计。MySQL 中使用 WITH ROLLUP，与 SQL 标准不太一致。
————————————————
57. 如何获取员工在公司组织结构中的结构图，也就是从最高领导到员工的管理路径?
答案: – MySQL 实现
WITH RECURSIVE employee_paths (emp_id, emp_name, path) AS (
SELECT emp_id, emp_name, CAST(emp_name AS CHAR(200))
FROM employee
WHERE manager IS NULL – 假设manager为NULL表示没有上级，即最高领导
UNION ALL
SELECT e.emp_id, e.emp_name, CONCAT(ep.path, ‘->’, e.emp_name)
FROM employee e
INNER JOIN employee_paths ep ON e.manager = ep.emp_id
)
SELECT * FROM employee_paths;

——解释：

FROM employee_paths ep JOIN employee e ON ep.emp_id = e.manager ) SELECT * FROM employee_paths ORDER BY path; 查询结果如下(显示部分内容): 解析:同样是利用递归通用表表达式实现数据的遍历。Oracle 中省略 RECURSIVE 即可。通用表表达式是 SQL 中非常强大的功能，可以帮助我们简化复杂的连接查询和子查询，并且可以完成递归处理和层次遍历
———————————————

59. 查询员工的月薪，同时返回该员工所在部门的平均月薪。如何使用聚合函数实现?
答案: SELECT emp_name, salary, AVG(salary) OVER (PARTITION BY dept_id) FROM employee; 解析:窗口函数 AVG 基于部门(dept_id)分组后的数据计算平均月薪，为每个员工返回一条记录。窗口函数中的 PARTITION BY 作用类似于 GROUP BY 子句。虽然也可以使用关联子查询与聚合函数实现相同的功能，显然窗口函数更加简 单易懂。
————————————————