SQL 网站场景面试题, 编写 SQL,查询每个用户名的平均访问时长,并且平均访问时长超过 100 秒的用户名

Step 1

Q:: 编写 SQL，查询每个用户名的平均访问时长，并且平均访问时长超过 100 秒的用户名

A:: SELECT username, AVG(duration) AS avg_duration FROM user_visits GROUP BY username HAVING AVG(duration) > 100;

Step 2

Q:: 如何优化查询性能？

A:: 可以通过创建索引、优化SQL语句、避免使用子查询和联合查询等方式来优化查询性能。索引可以加速数据检索，但要避免过多的索引以免影响写操作性能。

Step 3

Q:: 解释索引的类型及其适用场景

A:: 主要有聚集索引和非聚集索引。聚集索引将数据物理存储顺序与索引顺序一致，适用于需要顺序访问的数据。非聚集索引则存储索引与数据分离，适用于查找特定值的场景。

Step 4

Q:: 什么是事务？为什么事务重要？

A:: 事务是指数据库操作的一系列动作，要么全部成功要么全部失败。事务确保数据的一致性、隔离性、持久性和原子性，是确保数据库数据可靠性的重要机制。

Step 5

Q:: 什么是范式？数据库设计中为什么要用到范式？

A:: 范式是数据库设计的一套规则，用于减少数据冗余和提高数据一致性。常用的范式有第一范式、第二范式和第三范式。采用范式可以优化数据库结构，使数据更具一致性和完整性。

Step 1

Q:: 编写 SQL 查询，获取每个用户名的平均访问时长，并且平均访问时长超过 100 秒的用户名。

A:: SQL 查询如下：

 
SELECT username, AVG(access_duration) AS avg_duration 
FROM user_access_logs 
GROUP BY username 
HAVING AVG(access_duration) > 100;
 

此查询从 user_access_logs 表中获取每个用户的平均访问时长，并筛选出平均访问时长超过 100 秒的用户名。

Step 2

Q:: 解释 SQL 中的 GROUP BY 子句的作用，以及如何在查询中正确使用它？

A:: GROUP BY 子句用于将查询结果按一列或多列进行分组，以便对每组数据进行聚合操作，如求和、计数、平均等。它常与聚合函数（如 SUM、COUNT、AVG 等）一起使用。例如，上述查询中的 GROUP BY username 将访问日志按用户名分组，每个组内的 access_duration 会被计算出平均值。

Step 3

Q:: HAVING 子句与 WHERE 子句有什么区别？什么时候使用 HAVING 而不是 WHERE？

A:: HAVING 子句用于过滤由 GROUP BY 分组后的数据，而 WHERE 子句用于过滤 GROUP BY 操作之前的数据。如果需要对聚合结果进行过滤，应使用 HAVING，而不是 WHERE。例如，在上面的 SQL 语句中，HAVING AVG(access_duration) > 100 用于筛选出平均访问时长超过 100 秒的分组结果。

Step 4

Q:: 如何优化 SQL 查询以提高性能，特别是在处理大型数据集时？

A:: 优化 SQL 查询的常见方法包括： 1. 确保适当的索引，特别是 WHERE 子句和 JOIN 子句中使用的列。 2. 使用 LIMIT 限制返回的结果集大小，减少不必要的数据处理。 3. 避免在 SELECT 语句中使用 SELECT *，仅选择需要的列。 4. 使用子查询或 CTE（公用表表达式）来简化复杂的查询结构。 5. 分析查询执行计划，并根据结果调整查询。