深度思考面试常考sql题

1 推荐工具

在线运行SQL

2 阿里一面

3 百度一面 sql题

学生表student(id,name)
课程表course(id,name)
学生课程表student_course(sid,cid,score)

CREATE TABLE student (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL
);
CREATE TABLE course (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);
CREATE TABLE student_course (
    sid INT,
    cid INT,
    score DECIMAL(5, 2),
    PRIMARY KEY (sid, cid),
    FOREIGN KEY (sid) REFERENCES student(id),
    FOREIGN KEY (cid) REFERENCES course(id)
);

INSERT INTO student (id, name) VALUES
    (1, 'Alice'),
    (2, 'Bob'),
    (3, 'Charlie'),
    (5, 'Alice'),
    (4, 'David');

INSERT INTO course (id, name) VALUES
    (1, 'Mathematics'),
    (2, 'History'),
    (3, 'Science'),
    (4, 'English');

INSERT INTO student_course (sid, cid, score) VALUES
    (1, 1, 90.5),
    (1, 3, 78.0),
    (2, 2, 85.5),
    (2, 4, 92.0),
    (3, 1, 88.0),
    (3, 3, 76.5),
    (4, 2, 89.0),
    (4, 4, 10.0);
    

3.1 查询student表中重名的学生，结果包含id和name，按name,id升序

select id, name from student group by name 
having count(*)>1 order by name asc, id asc;

3.2 在student_course表中查询平均分不及格的学生，列出学生id和平均分

select s.name, s.id, avg(sc.score) from student s inner join student_course sc 
inner join course c on s.id=sc.sid
group by sc.sid having avg(sc.score)<60.0;

3.3 每个学生的总成绩，按照成绩降序排名（查出学生id，学生名称，总成绩，没有成绩的学生不展示）

select  s.id,s.name, sum(sc.score) from student s inner join student_course sc 
on s.id=sc.sid group by sc.sid order by sum(sc.score) desc;

4 一道百度校招二面sql题：考察子查询以及mysql的8窗口函数

力推一个在线运行sql的网站：https://www.json.cn/runcode/run_sql/

有一张学生表stu，包含sid, std_name,course_name,score等几个字段，数据如下，要求查询出每门课程排名前三名的学生记录，

/*
    sid, std_name,course_name,score
    1 小明 语文 90
    2 小明 语文 92
    3 小明 语文 93
    4 小明 语文 94

    5 小泓 数学 92
    6 小泓 英文 93
    7 小刚 科学 94

    语文 94-93-92
    数学
 */


CREATE TABLE stu (
	sid int,
	std_name varchar(255),
	course_name varchar(255),
	score int
);

INSERT INTO stu VALUES (1, 'Tom', 'Chinese', 90);
INSERT INTO stu VALUES (2, 'Tom2', 'Chinese', 91);
INSERT INTO stu VALUES (3, 'Tom3', 'Chinese', 92);
INSERT INTO stu VALUES (4, 'Tom4', 'Chinese', 93);

INSERT INTO stu VALUES (5, 'Tom5', 'English', 93);
INSERT INTO stu VALUES (6, 'Tom6', 'English', 94);

INSERT INTO stu VALUES (7, 'Tom7', 'Math', 91);

4.1 方法一（我写的，面试官不是很认可）：使用group_concat函数(缺点是只能限制拼接后的最长字符串，不能限制拼接的元素数量)

【SQL学习笔记一】Group_Concat()

// 先堆
// select course_name,group_concat(std_name,"-"),group_concat(score, "-") from (select * from stu order by score desc) as tmp group by course_name

// 协会站点，入会当会员

//表
/*
    学生表、部门表、学生-部门表、新闻表
 */


// (select * from stu order by course_name desc, score desc) as tmp

4.2 方法二：使用子查询

SELECT s1.sid, s1.std_name, s1.course_name, s1.score
FROM stu s1
WHERE (
    SELECT COUNT(DISTINCT s2.score) 
    FROM stu s2 
    WHERE s2.course_name = s1.course_name AND s2.score > s1.score
) < 3
ORDER BY s1.course_name, s1.score DESC;

4.2.1 sql语句解析

当然可以。这是一种子查询的方法，它使用自连接来获取每个课程的前三名学生的成绩。以下是对这个查询的详细解释：

1. 主查询：

   主查询是对stu表的查询，它选取了学生的sid、std_name、course_name和score。

   SELECT s1.sid, s1.std_name, s1.course_name, s1.score
   FROM stu s1
   

2. 子查询：

   对于主查询中的每个学生（或记录），都会运行一次子查询。子查询的目的是计算当前学生的成绩在其课程中的排名。

   SELECT COUNT(DISTINCT s2.score) 
   FROM stu s2 
   WHERE s2.course_name = s1.course_name AND s2.score > s1.score
   

   这里，我们正在数那些在同一课程中但成绩高于当前学生（s1）的其他学生（s2）的数量。因此，如果子查询的结果为0，这意味着当前学生是这门课的第一名；如果结果为1，这意味着当前学生是第二名，以此类推。

3. WHERE条件：

   WHERE (
      ...
   ) < 3
   

   该条件确保我们只选取排名在前三名的学生。只有当子查询的结果为0、1或2时（即该学生的成绩在其课程中排名前三），该学生的记录才会被选中。

4. ORDER BY子句：

   ORDER BY s1.course_name, s1.score DESC;
   

   这个子句首先按课程名称对结果进行排序，然后在每个课程内部，按学生的成绩降序排列。

简单来说，这个查询为每个学生计算他们在自己的课程中的成绩排名，然后选取排名在前三名的学生。

4.2.2 子查询的使用位置

子查询并不仅限于WHERE子句中。它们可以出现在以下位置：

• SELECT子句中，作为派生列。
• FROM子句中，作为派生表。
• WHERE子句中，与操作符（如IN, EXISTS）一起使用。
• HAVING子句中。
• JOIN操作中。

4.2.3 子查询、EXISTS和IN的关系

我的笔记(Sql语句中IN和exists的区别及应用)

• 子查询与IN: 当你需要比较一个值是否在一组值中时，可以使用子查询与IN结合。

  SELECT employee_id FROM employees WHERE department_id IN (SELECT department_id FROM departments WHERE location = 'New York');
  
  

• 子查询与EXISTS: 当你需要检查一个子集是否存在时，可以使用EXISTS。它是一个半关联操作，通常与关联条件结合使用。

  SELECT customer_id FROM customers c WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);
  
  

  EXISTS通常更高效，因为它在找到第一个匹配项后就会停止搜索。

N 和 EXISTS 的区别及应用场景

• 区别：

  • 如你所说，IN和EXISTS关键字在执行查询时使用的策略是不同的。具体执行策略和数据库优化器的实现有关，但大致的概念是：

    • IN子查询首先被执行，然后将得到的结果集返回给主查询，之后主查询进行过滤。
    • 对于EXISTS子查询，通常外层的每一行数据都会检查子查询是否存在满足条件的记录。

• 应用场景：

  • IN更适合子查询返回的结果集小，而主查询的数据集大并且具有适当的索引的场景。
  • EXISTS在外部查询的数据集较小而子查询的数据集较大并且具有适当索引的情况下表现得更好。

• 关于效率：

  • 这两者之间没有固定的“哪个总是更快”的规则，它取决于数据的特性和查询的具体情境。通常，最佳的做法是编写两种查询并检查执行计划或实际执行时间。

4.3 方法三：使用窗口函数：

如上文中的ROW_NUMBER()方法(能够保证强排名，如果两个人并列第三，只返回其中number号靠前的那一个)。除此之外，你还可以使用RANK()【并列的前三名都会返回】或DENSE_RANK()。

WITH RankedScores AS (
    SELECT 
        sid,
        std_name,
        course_name,
        score,
        ROW_NUMBER() OVER(PARTITION BY course_name ORDER BY score DESC) AS ranking
    FROM 
        stu
)
SELECT 
    sid,
    std_name,
    course_name,
    score
FROM 
    RankedScores
WHERE 
    ranking <= 3;

4.3.1 什么是窗口函数

窗口函数是SQL的高级功能，允许在结果集的一组行上执行计算，这组行与当前行有某种关系。换句话说，窗口函数为当前行定义了一个"窗口"，并根据这个窗口计算值。这些函数与普通的聚合函数（如SUM、AVG）不同，因为窗口函数不会减少结果集的行数；它们为每一行提供计算结果。

窗口函数通常与OVER子句一起使用，OVER子句定义了为每一行计算函数值的窗口。

4.3.2 解释 WITH RankedScores AS ...

这是一个常用的SQL特性，叫做"公共表表达式" (Common Table Expression, 简称CTE)。CTE为查询提供了一种定义临时结果集的方式，这个结果集只存在查询的执行期间，并且它可以在之后的查询中引用。

在给定的查询中，CTE RankedScores为stu表中的每个学生按照course_name进行分区，并为每个学生计算一个名次ranking，名次基于他们的score从高到低排序。

解释CTE中的查询：

• RANK(): 是一个窗口函数，为每一行提供一个名次。
• OVER(PARTITION BY course_name ORDER BY score DESC): 定义了一个窗口。这里，PARTITION BY course_name意味着为每门课程单独计算名次。ORDER BY score DESC意味着名次是基于分数从高到低排序的。
• AS ranking: 将窗口函数的结果命名为ranking。

所以，这个CTE为每位学生提供了一个名次，基于他们在特定课程中的分数。之后的主查询从这个CTE中选择名次在前三的学生。

4.3.3 常见的窗口函数

• ROW_NUMBER(): 对每个窗口中的行进行编号。
• RANK(): 在窗口内为行分配一个唯一的排名，排名并列的情况下会留下空缺。
• DENSE_RANK(): 与RANK()相似，但不会在排名中留下空缺。
• NTILE(n): 将窗口内的行分为n个大致相等的部分，并为每部分的行提供一个编号。
• LAG(column, n): 返回当前行前n行的值。
• LEAD(column, n): 返回当前行后n行的值。
• FIRST_VALUE(column): 返回窗口内的第一个值。
• LAST_VALUE(column): 返回窗口内的最后一个值。
• SUM(column), AVG(column), MIN(column), MAX(column): 在窗口内计算总和、平均值、最小值和最大值。

4.3.4 举一个例子告诉rank(), dense_rank()和row_number()等窗口函数的区别

1 rank()

WITH RankedScores AS (
    SELECT 
        sid,
        std_name,
        course_name,
        score,
        RANK() OVER(PARTITION BY course_name ORDER BY score DESC) AS ranking
    FROM 
        stu
)
SELECT 
    sid,
    std_name,
    course_name,
    score,
    ranking
FROM 
    RankedScores
WHERE 
    ranking <= 3;

查询结果：

3|Tom3|Chinese|93|1
4|Tom4|Chinese|93|1
2|Tom2|Chinese|91|3
6|Tom6|English|94|1
5|Tom5|English|93|2
7|Tom7|Math|91|1

2 dense_rank()

  
WITH RankedScores AS (
    SELECT 
        sid,
        std_name,
        course_name,
        score,
        DENSE_RANK() OVER(PARTITION BY course_name ORDER BY score DESC) AS ranking
    FROM 
        stu
)
SELECT 
    sid,
    std_name,
    course_name,
    score,
    ranking
FROM 
    RankedScores
WHERE 
    ranking <= 3;

查询结果：

3|Tom3|Chinese|93|1
4|Tom4|Chinese|93|1
2|Tom2|Chinese|91|2
1|Tom|Chinese|90|3
6|Tom6|English|94|1
5|Tom5|English|93|2
7|Tom7|Math|91|1

3 ROW_NUMBER()

WITH RankedScores AS (
    SELECT 
        sid,
        std_name,
        course_name,
        score,
        ROW_NUMBER() OVER(PARTITION BY course_name ORDER BY score DESC) AS ranking
    FROM 
        stu
)
SELECT 
    sid,
    std_name,
    course_name,
    score,
    ranking
FROM 
    RankedScores
WHERE 
    ranking <= 3;

3|Tom3|Chinese|93|1
4|Tom4|Chinese|93|2
2|Tom2|Chinese|91|3
6|Tom6|English|94|1
5|Tom5|English|93|2
7|Tom7|Math|91|1

4.3.5 `SUM(column)`, `AVG(column)`, `MIN(column)`, `MAX(column)`这些函数是对窗口内所有的记录生效的吧 2 `LAG(column, n)`， `LEAD(column, n)`和`NTILE(n)`，分别给几个demo

1. 是的，SUM(column), AVG(column), MIN(column), MAX(column)这些函数都是对窗口内的所有记录生效的，而具体的窗口范围由PARTITION BY和ORDER BY决定。

2. DEMO:

   • LAG(column, n): 这个函数用于返回当前行上方n行的值。如果没有这么多行，它将返回NULL。

     SELECT 
         column,
         LAG(column, 1) OVER (ORDER BY some_column) AS prev_value
     FROM table_name;
     

     这将给出当前行和前一行的值。

   • LEAD(column, n): 与LAG类似，但返回当前行下方n行的值。

     SELECT 
         column,
         LEAD(column, 1) OVER (ORDER BY some_column) AS next_value
     FROM table_name;
     

     这将给出当前行和下一行的值。

   • NTILE(n): 这个函数用于将结果集分成n个大致相等的部分，并为每部分的行分配一个编号。

     SELECT
         column,
         NTILE(4) OVER (ORDER BY some_column) AS quartile
     FROM table_name;
     

     这将为每行分配一个从1到4的值，表示它在哪个四分位数中。

请注意，这些DEMO只是基本用法的示例，实际应用可能会更复杂，取决于具体的数据和需求。

4.4 方法四：在某些数据库（如MySQL）中，可以使用用户定义的变量来计算每个课程的排名。

SET @ranking = 0;
SET @prev_course = '';
SELECT sid, std_name, course_name, score
FROM (
    SELECT 
        sid, 
        std_name, 
        course_name, 
        score,
        @ranking := IF(@prev_course = course_name, @ranking + 1, 1) AS ranking,
        @prev_course := course_name
    FROM stu
    ORDER BY course_name, score DESC
) AS RankedScores
WHERE ranking <= 3;

4.4.1 解释一下用法

这个查询是使用MySQL的用户定义变量来实现学生的成绩排名功能。它的目标是为每门课程中的学生按分数降序排名，并只选择排名前三的学生。

我来逐步解释这个查询：

1. 设置用户定义变量

SET @ranking = 0;
SET @prev_course = '';

这里，我们初始化两个用户定义变量：

• @ranking：用于存储当前学生的排名。
• @prev_course：用于存储上一行的课程名，这样我们可以检查当前行的课程是否与上一行的课程相同。

2. 子查询

SELECT 
    sid, 
    std_name, 
    course_name, 
    score,
    @ranking := IF(@prev_course = course_name, @ranking + 1, 1) AS ranking,
    @prev_course := course_name
FROM stu
ORDER BY course_name, score DESC

这个子查询做了以下几件事：

• 首先，它对结果集进行排序，使得同一门课程的学生按其分数降序排列。
• 使用IF函数来检查当前行的课程名是否与前一行的课程名相同。如果相同，@ranking加1；如果不同，@ranking重置为1。
• 更新@prev_course为当前行的课程名。

3. 主查询

SELECT sid, std_name, course_name, score
FROM ... AS RankedScores
WHERE ranking <= 3;

这个主查询简单地从子查询的结果中选择排名前三的学生记录。

总的来说，这个查询使用MySQL的用户定义变量为每门课程的学生分数进行排名，并只选择排名前三的记录。

4.5 类似于这种难度的sql，你还能再多出几个嘛

当然可以，这里是一些类似难度的SQL题目以及它们的背景描述：

1. 获取每个员工的工资排名:
   假设您有一个employees表格，其中包含id, name, 和salary字段。您需要编写一个SQL查询来获取每个员工的工资排名，不考虑并列情况。

2. 查找顾客购买最多商品的日期:
   假设您有一个orders表格，其中包含id, customer_id, product_id, 和order_date字段。您的任务是确定哪一天有最多的不同客户下单。

3. 查找没有订单的顾客:
   假设您有两个表格，customers和orders。您需要列出那些至今还没有下单的客户。

4. 获取每个产品的总销售额:
   您有一个products表和一个sales表。您需要为每种产品计算总销售额。

5. 查找连续三天访问网站的用户:
   您有一个user_visits表，其中包含user_id和visit_date字段。您的任务是找出连续三天都访问网站的用户。

6. 查找上一个月的最佳销售员:
   您有一个sales表和一个employees表。您需要找出上个月销售额最高的员工。

这只是几个SQL难题的例子，实际上有无数的可能性和场景，您可以根据具体的数据模型和需求来定制。

5 MySQL 8的新特性：

MySQL 8的新特性：

MySQL 8在2018年发布，并带来了许多新的特性和改进：

• 数据字典事务性：以前，MySQL中的元数据存储在多个文件和非事务性表中。在MySQL 8.0中，有了一个新的数据字典，所有的元数据都存储在单独的事务性表中。

• 增强的UTF-8支持：默认的字符集从latin1更改为utf8mb4，支持更多的字符集，如Emoji、各种符号等。

• 窗口函数：这为SQL在MySQL中提供了标准的窗口函数，如ROW_NUMBER(), LEAD(), LAG(),等等。

• 公共表表达式 (Common Table Expressions, CTEs)：通过使用WITH子句，MySQL 8.0开始支持CTEs。

• 原子DDL语句：DDL操作（如创建、更改、删除表或索引）现在是原子的，并完全支持事务。

• 角色支持：MySQL 8.0添加了对SQL角色的原生支持。

• 性能模式：一个新的性能查询工具，用于监控服务器和查询的性能。

• JSON增强：对JSON数据类型的增强和改进。

• 优化器改进：查询执行计划的改进、直方图的引入等。

• 其他安全、复制和性能的改进。

6 索引怎么走

6.1 select * from user where A = “xxx” and C = “YYY”；这个时候怎么去找呢？C="YYY"是什么时候去判断的呢？

答：

首先如果A只是一个普通的索引，会首先根据条件A查询到所有匹配的主键，并且加载到内存中，随后肯定会在内存中二次回表查询，在回表的时候是一个接一个的搜索的，这时会在聚簇索引的叶子节点处判断C是否等于YYY.

6.1.1 如果是普通的索引，第一次查询索引时得到的主键是放回内存中嘛，然后再一条一条去查找主键索引拿到数据？还是说不用写回内存

当我们谈论数据库索引，尤其是MySQL的InnoDB存储引擎时，需要考虑以下几点：

1. 缓冲池（Buffer Pool）：InnoDB有一个内存区域叫做缓冲池，它用于缓存数据页和索引页。这是为了减少磁盘IO操作。

2. 普通索引与主键索引查询：

   • 当使用普通索引进行查询时，首先会查找索引页，从中找到对应的主键值。
   • 接下来，这些主键值会被用来在聚簇索引（即主键索引）中查找完整的数据行。

3. IO操作：

   • 如果所需的索引页或数据页已经在缓冲池中，那么就不需要进行磁盘IO操作，直接从缓冲池中读取即可。
   • 如果所需的页面不在缓冲池中，就需要从磁盘读入到缓冲池中，然后再进行查询。

关于你的问题，当使用普通索引查询时：

• 首先，系统会查找普通索引并找到对应的主键值。这些主键值不是单独“写回”内存的，但是索引页（包含主键值）可能会被加载到缓冲池中，如果它们还没有被加载。

• 然后，系统会使用这些主键值去查询聚簇索引（主键索引）来获取完整的数据行。同样，如果所需的数据页还不在缓冲池中，它们会从磁盘被加载进来。

总结：不是简单地说“写回内存”，而是数据和索引页在必要时会被加载到缓冲池中，以减少后续的磁盘IO操作。

6.1.2 这个过程中使用了索引下推技术嘛？

答：没有，索引下推是指在引擎层使用A的字段进行索引匹配时，也会要求使用C的字段进行匹配。这里A仅仅是一个普通索引，叶子节点进存放主键而不是记录，所以不能在匹配A的同时又匹配C，因为没有C字段，C字段存放在主键索引的叶子节点中。如果A是一个覆盖索引，则可能会使用索引下推技术，因为叶子节点存放的是一条记录，可以做到既匹配A又匹配C。

6.1.3 主键索引有可能使用索引下推技术吧

是的，主键索引（也称为聚簇索引）同样可以使用索引下推（Index Condition Pushdown，ICP）技术。索引下推是MySQL 5.6及更高版本引入的一项优化技术，目的是更早地在索引层面筛选记录，从而避免不必要的数据行读取和额外的I/O开销。

索引下推的基本思路是将部分WHERE条件从存储引擎层推送到索引层，这样只有满足条件的记录才会被读取和返回。无论是聚簇索引还是非聚簇索引，只要能在索引层面完成更多的筛选工作，就可以使用索引下推技术。

例如，考虑以下查询：

SELECT * FROM users WHERE id > 1000 AND name = 'John';

如果id是一个主键索引，而name是在数据行中的一个字段，不使用索引下推的传统查询方式是：

1. 在主键索引上找到id > 1000的所有记录。
2. 为每个找到的记录读取完整的数据行。
3. 在存储引擎层筛选name = 'John'的记录。

使用索引下推的查询方式则是：

1. 在主键索引上找到id > 1000的所有记录。
2. 在索引层面就筛选出name = 'John'的记录。
3. 只为已筛选的记录读取完整的数据行。

可以看出，索引下推可以减少不必要的数据行读取，从而提高查询性能。

6.2 如果现在给这个表建立一个(A,B,C)的联合索引，select * from user where A = “xxx” and C = “YYY”；查询方式是怎么样的呢？

答：这个过程中可能会使用索引下推技术，联合索引树的一个分支包含了A，B，C三个元素，查询这个树的时候，在进行A字段的判断的时候，同时会判断C是否等于YYY

7 一道题考察索引优化(百度一面)

7.1 建立联合索引(a,b)，查询的时候是where b=xxx and a=yyy会走索引嘛（重点考察的是mysql自己的一些优化）

答：有可能会，有可能不会，mysql的优化器应该会自己把b=xxx and a=yyy调整成完全匹配联合索引的样子

8 考察连接查询和笛卡尔积（用友校招一面）

给出两张表t1和t2，有两条sql语句：t2上一个外键是t1的主键

t1

id

1

2

3

t2:

id fid

1 1

2 2

8.1 select t1.id from t1 inner join t2 on t1.id on t2.fid 会产生几条数据

答：2

8.2 select t1.id from t1 left join t2 on t1.id on t2.fid 会产生几条数据

答：3

8.3 如果我把9.2中的on条件删除了，变成select t1.id from t1 inner join t2；它还会执行吗？

答：会，返回的是笛卡尔积