常见面试题

常见面试题

Q1：请解释`WHERE`和`HAVING`的区别？

WHERE用于常规的过滤结果集的数据，而HAVING用于过滤分组后的数据，与GROUP BY一起使用。
例如以下SQL：

SELECT department, COUNT(*) AS num_employees
FROM employees
WHERE salary > 3000
GROUP BY department
HAVING AVG(salary) > 5000;

这个SQL语句会先过滤出工资大于3000的员工，然后按部门分组，最后过滤出平均工资大于5000的部门。
也就是说，WHERE会先过滤出数据，然后GROUP BY进行分组，HAVING再次过滤分组后的数据。

Q2：什么是`JOIN`？有哪些类型的`JOIN`并解释？

JOIN用于根据两个或多个表之间的相关列将它们的行组合起来，通过使用JOIN可以从多个表中查询数据，常用于处理存储在不同表中的关联数据。
JOIN的类型有：

INNER JOIN(即内连接，或简称JOIN)：只返回两个表中都存在的行。
LEFT OUTER JOIN（即左外连接，或简称LEFT JOIN）：返回左表中的所有行，以及右表中匹配的行。如果右表没有匹配，结果中右表的部分将包含空值。
RIGHT OUTER JOIN（即右外连接，或简称RIGHT JOIN）：返回右表中的所有行，以及左表中匹配的行。如果左表没有匹配，结果中左表的部分将包含空值。
FULL OUTER JOIN（即全外连接，或简称FULL JOIN）：返回左表和右表中的所有行，以及两表中匹配的行。如果左表或右表没有匹配，结果中没有匹配的部分将包含空值。注：在MySQL中不支持FULL OUTER JOIN，可以使用LEFT JOIN和RIGHT JOIN的并集来实现。

Q3：请解释`JOIN`和`UNION`的区别？

JOIN用于将两个或多个表中的行按照相关的列连接起来，它是基于至少一个共同属性，将相关联的行组合在一起。
UNION用于将两个或多个查询的结果集合并为一个结果集，并消除重复行。如果要保留所有重复的行，可以使用UNION ALL。

Q4：请解释`UNION`和`UNION ALL`的区别？

UNION和UNION ALL都用于合并两个或多个查询结果集的行，但UNION会去除重复的行，而UNION ALL不会去除重复的行。UNION的性能通常比UNION ALL差，因为需要对结果进行去重。

Q5：请解释`ROW_NUMBER`、`RANK`和`DENSE_RANK`的区别？

ROW_NUMBER：当需要基于排序顺序给每行一个不重复的序号时，使用ROW_NUMBER。该函数为每个行分配一个唯一的连续整数，从1开始，根据指定的排序顺序，每个行的排名都是独一无二的。
RANK：当需要根据排序顺序给行排名，并且希望相同值的行有相同的排名，但之后的排名要考虑这种“并列”情况时，使用RANK。该函数为每个行分配一个排名，如果有行在排序上是相同的，它们将获得相同的排名。当遇到排名相同的行时，下一个排名会跳过中间的数值（例如，如果有两行并列第1，下一个排名是第3）。
DENSE_RANK：当希望相同值的行有相同的排名，而之后的排名不跳过任何数值时，使用 DENSE_RANK。DENSE_RANK在排名时不会跳过任何数值。如果两行或多行在排序列上相同，它们将获得相同的排名，下一个排名紧随其后（例如，如果有两行并列第一，下一个排名是第二）。

Q6：什么时候使用`GROUP BY`？

GROUP BY用于将表中的多个行根据一个或多个列的值进行分组，对于执行聚合计算非常有用。例如计算平均值（AVG）、总和（SUM）、最大值（MAX）、最小值（MIN）或计数（COUNT）等。另外GROUP BY也可用于数据去重，用于列出不重复的列。

Q7：你使用过哪些函数？

回答该类问题时，面试官主要想考察候选人是否真正用过，以及使用的广度和深度，我们可以采用分类的方法来组织回答以展示广度，对于每个函数分类，列觉几个常用的函数以展现深度。另外，面试官也可能会直接问具体的函数类型，例如“你使用过哪些字符串处理函数”？常用函数类型及示例如下所示：

聚合函数：
- COUNT()：计算行数。
- SUM()：计算数值列的总和。
- AVG()：计算数值列的平均值。
- MAX()：找出列中的最大值。
- MIN()：找出列中的最小值。
字符串函数：
- CONCAT()：连接两个或多个字符串。
- UPPER()、LOWER()：将字符串转换为大写或小写。
- SUBSTRING()：返回字符串的一部分。
- REPLACE()：替换字符串中的某些部分。
日期和时间函数：
- NOW()：返回当前的日期和时间。
- CURDATE()：返回当前的日期。
- DATEDIFF()：计算两个日期之间的差值（天数）。
- DATE_ADD()：在日期上增加指定的时间间隔。
窗口函数：
- ROW_NUMBER()：为每一行分配一个唯一的连续整数，根据指定的排序顺序。
- RANK()：如果有行在排序上是相同的，它们将获得相同的排名。
- DENSE_RANK()：如果有行在排序上是相同的，它们将获得相同的排名，下一个排名紧随其后。
- 另外还有前后、首尾、分桶等函数。
条件函数：
- CASE ... WHEN ... THEN ... ELSE ... END：用于根据条件表达式返回不同的值。
- IF：根据条件表达式返回不同的值。
- IFNULL：如果表达式为NULL，则返回另一个值。
数学函数：
- ABS()：返回数值的绝对值。
- ROUND()：将数值四舍五入到指定的小数位数。
- CEIL()、FLOOR()：返回大于或等于、小于或等于指定数值的最小整数。

Q8：如果一个SQL执行时间较长，可能是什么原因，如何进行优化？

当SQL执行时间较长时，可能的原因有：

索引缺失或不当，表缺乏适当的索引会导致数据库进行全表扫描，特别是在大数据量的情况下极为耗时。可以使用EXPLAIN命令查看SQL的执行计划，检查是否存在全表扫描的情况，并根据实际情况添加适当的索引。
查询设计不当，查询逻辑过于复杂或不明确，例如多重嵌套的子查询、过多或不当的连接操作等，可能导致SQL执行效率低下。可以简化查询逻辑，尽可能使用更有效的SQL构造，如替换子查询为连接查询。
表数据量巨大，当表中数据量巨大时，即使有索引，查询性能也可能不理想。可以尝试将数据进行分区，例如使用分区表。
其它原因，例如查询缓存失效，硬件性能限制，并发问题等等。

Q9：什么是表的主键、外键、分区键？

主键：主键是一个或多个字段的组合，用来唯一标识表中的每一行，一个表中只能有一个主键。
外键：外键是一个表中的字段（或字段组合），与另一个表的主键字段对应，外键用于创建两个表之间的关系。
分区键：是用于将表的数据分割为多个分区，每个分区可以独立存储在不同的物理位置，从而提高查询性能。分区键通常用于大型表，以减少查询时扫描的数据量。

Q10：什么是索引？和主键的区别是什么？

索引用于提高数据库查询的性能，索引中存储了表中某些列的值，并按照这些值进行排序，从而在查询时可以快速定位到特定的行。索引可以显著提高查询效率，特别是在数据量较大的情况下。索引可以创建在一个或多个列上。索引通常使用B+树结构（一种数据结构）存储，以提高查询效率。
索引的副作用：索引会占用额外的存储空间，每当表中的数据被插入、删除或更新时，索引也需要被更新，可能会影响写操作的性能。
主键是一种特殊的索引，不仅可以提高查询性能，还用于唯一标识表中的每一行。主键的值必须是唯一的，不能为空，也不能重复。
每个表只能有一个主键，但是可以有多个索引。

持续补充中……

常见面试题

Q1：请解释WHERE和HAVING的区别？

Q2：什么是JOIN？有哪些类型的JOIN并解释？

Q3：请解释JOIN和UNION的区别？

Q4：请解释UNION和UNION ALL的区别？

Q5：请解释ROW_NUMBER、RANK和DENSE_RANK的区别？

Q6：什么时候使用GROUP BY？