文章目录

NoSQL 和 SQL 的区别？

面试官您好，SQL（关系型数据库）和NoSQL（非关系型数据库）是当今数据存储领域的两大主流阵营。它们之间不是“谁取代谁”的关系，而是两种完全不同的设计哲学，适用于解决不同类型的问题。

我通常会从以下几个核心维度来对比它们：

1. 数据模型 (Data Model) —— 结构化 vs. 灵活

  • SQL (关系型数据库)：

    • 核心：基于关系模型，数据被组织在结构化的二维表（Table）中，表有固定的模式（Schema），包含行（Row）和列（Column）。
    • 特点：强Schema。在写入数据之前，必须先CREATE TABLE定义好表的结构（字段名、数据类型、约束等）。所有存入的数据都必须严格遵守这个结构。
    • 例子：MySQL, PostgreSQL, Oracle。

  • NoSQL (非关系型数据库)：

    • 核心：数据模型非常多样和灵活，没有固定的模式。
    • 主要类型：
      1. 键值存储 (Key-Value)：数据以简单的{key: value}形式存储，非常高效。代表：Redis, Memcached。
      2. 文档存储 (Document)：数据以类似JSON或BSON的文档格式存储，结构灵活，可以嵌套。代表：MongoDB。
      3. 列族存储 (Column-Family)：数据按列族组织，非常适合海量数据的读写。代表：HBase, Cassandra。
      4. 图存储 (Graph)：专门用于存储和查询图结构数据（节点、边）。代表：Neo4j。
    • 特点：动态Schema或无Schema。可以随时向数据中添加新的字段，无需预先定义。

2. 事务与一致性 (Transaction & Consistency)

  • SQL：

    • 核心：遵循ACID原则（原子性、一致性、隔离性、持久性）。
    • 特点：提供非常强大的事务支持，能够保证数据的强一致性。非常适合对数据准确性要求极高的场景。

  • NoSQL：

    • 核心：通常遵循BASE理论（基本可用、软状态、最终一致性）。
    • 特点：为了追求更高的性能和可用性，通常会牺牲强一致性，转而支持最终一致性。它们对事务的支持通常较弱，或者只支持单行/单文档的原子操作。

3. 扩展性 (Scalability)

  • SQL：

    • 扩展方式：通常通过垂直扩展（Scale-up） 来实现，即提升单个服务器的硬件性能。
    • 水平扩展（Scale-out）：虽然也可以通过分库分表、读写分离等方式实现水平扩展，但实现起来相对复杂，且对应用层有一定侵入。

  • NoSQL：

    • 扩展方式：天生为分布式和水平扩展而设计。
    • 特点：可以非常容易地通过增加更多的普通服务器节点，来线性地提升整个集群的性能和存储容量。它们的架构通常内置了数据分片（Sharding）和副本（Replication）机制。

4. 查询能力

  • SQL：

    • 使用标准化的SQL（Structured Query Language） 进行查询。SQL功能强大，支持复杂的多表连接（JOIN）、聚合、分组等操作。

  • NoSQL：

    • 没有统一的查询语言。每种NoSQL数据库都有自己独特的查询API。它们的查询能力通常不如SQL强大，特别是不擅长处理复杂的关系查询。

总结与选型建议

特性SQL (关系型)NoSQL (非关系型)
数据模型结构化 (二维表, 强Schema)多样化 (Key-Value, 文档等, 弱/无Schema)
一致性ACID (强一致性)BASE (最终一致性)
扩展性垂直扩展 (Scale-up) 为主水平扩展 (Scale-out) 为主
事务强大弱或不支持
适用场景事务性强的、数据关系复杂的应用高并发、海量数据、结构不固定的应用

我的选型策略：

  • 什么时候选择SQL数据库？

    • 当业务需要强大的事务保证时，比如金融、电商的订单和支付系统。
    • 当数据之间存在复杂的关系，需要进行频繁的连接查询时。
    • 当数据结构相对稳定，不需要频繁变更时。

  • 什么时候选择NoSQL数据库？

    • 当需要极高的读写性能和海量数据存储时，比如社交网络的信息流、物联网的传感器数据。
    • 当数据结构不固定、经常需要变化时，比如用户画像、商品属性等。
    • 当系统需要极高的可用性和易于水平扩展时。
    • 作为关系型数据库的补充，比如用Redis来做MySQL的热点数据缓存。

在现代架构中，我们通常不会只选择一种，而是将SQL和NoSQL数据库组合使用，让它们各自在最擅长的领域发挥作用，以构建出更健壮、性能更高的系统。

数据库三大范式是什么？

面试官您好，数据库的三大范式（Normal Forms, NF）是我们在进行关系型数据库逻辑设计时，所遵循的一套基本准则和规范。

它的核心目标，是通过对表结构的合理设计，来减少数据冗余、避免数据异常（如插入、更新、删除异常），从而保证数据的一致性和完整性。

我来分别解释一下这三大范式，并用一个例子来贯穿。

假设我们有一个未优化的“订单信息表”：

原始表：订单表 (Order_Info)

订单ID顾客姓名顾客电话商品ID商品名称商品单价购买数量
O001张三138…P01手机50001
O001张三138…P02耳机2002
O002李四139…P01手机50001

第一范式 (1NF): 保证字段的原子性

  • 定义：要求数据库表中的每一个字段（列）都是不可再分的原子值。
  • 核心思想：确保“一格一值”，不能在一个单元格里存储多个值。
  • 如何判断：我们看上面的原始表，每个字段都只包含一个单一的数据，没有像“手机,耳机”这样存储在一个单元格里的情况。所以，这个原始表已经满足了第一范式。

第二范式 (2NF): 消除对主键的部分函数依赖

  • 定义：在满足第一范式的基础上，要求表中的每一个非主键字段，都必须完全依赖于整个主键，而不是只依赖于主键的一部分。
  • 核心思想：这个范式只针对联合主键的情况。如果表是单主键，那么它只要满足1NF，就自动满足2NF。
  • 如何分析我们的例子：
    • 在原始表中，要唯一确定一条记录（比如张三买手机这条），需要 （订单ID, 商品ID） 作为联合主键。
    • 我们来分析非主键字段：
      • 购买数量：它既依赖于订单ID，也依赖于商品ID，是完全依赖。
      • 顾客姓名, 顾客电话：它们只依赖于订单ID，与商品ID无关。这是部分依赖。
      • 商品名称, 商品单价：它们只依赖于商品ID，与订单ID无关。这也是部分依赖。
    • 结论：存在部分依赖，所以不满足第二范式。
  • 如何改造（拆分）：
    • 将部分依赖的字段拆分出去，形成新的表。
    • 订单表 (Orders): (主键: 订单ID)
    • 商品表 (Products): (主键: 商品ID)
    • 订单详情表 (Order_Details): (联合主键: 订单ID, 商品ID)

第三范式 (3NF): 消除对主键的传递函数依赖

  • 定义：在满足第二范式的基础上，要求表中的任何非主键字段，都不能依赖于其他非主键字段。
  • 核心思想：确保所有非主键字段都直接依赖于主键，而不是通过“跳板”间接依赖。
  • 如何分析我们的例子：
    • 我们看改造后的订单表 (Orders)。顾客姓名和顾客电话是直接依赖于主键订单ID吗？
    • 不完全是。更准确地说，订单ID决定了是哪个顾客，而顾客才决定了他的姓名和电话。这里存在一个传递依赖：订单ID -> 顾客ID -> (顾客姓名, 顾客电话)。
    • 结论：存在传递依赖，所以不满足第三范式。
  • 如何改造（再次拆分）：
    • 将传递依赖的字段也拆分出去。
    • 顾客表 (Customers): (主键: 顾客ID)
    • 最终的订单表 (Orders): (主键: 订单ID)，其中包含一个顾客ID作为外键。

总结与实践

范式核心要求解决的问题
1NF字段原子性(基础)
2NF非主键字段完全依赖于（整个）主键部分依赖
3NF非主键字段直接依赖于主键（无传递依赖）传递依赖

在实际的数据库设计中，我们通常会力求满足第三范式（3NF），这能最大程度地减少数据冗余，保证数据一致性。但有时，为了查询性能，我们也会进行 “反范式化” 设计，适度地增加一些冗余字段来避免复杂的多表连接（JOIN）查询，这是一种在“数据一致性”和“查询效率”之间的权衡。

MySQL 怎么连表查询？

面试官您好，在MySQL（以及大多数关系型数据库）中，当我们需要从多个关联的表中获取数据时，就需要使用连接查询（JOIN）。

最核心的连接查询主要有以下几种。我们可以用两张简单的示例表来直观地理解它们的区别：

students表 (学生表)

idnameclass_id
1张三101
2李四102
3王五103

classes表 (班级表)

idname
101一班
102二班
104四班

1. 内连接 (INNER JOIN) —— “取交集”

  • 定义：这是最常用的一种连接。它只返回两个表中连接字段能够匹配上的行。可以理解为取两个表的交集。
  • SQL示例：
    SELECT s.name AS student_name, c.name AS class_name
FROM students s
INNER JOIN classes c ON s.class_id = c.id;
  • 结果：
    student_nameclass_name
    张三一班
    李四二班
    (王五因为class_id=103在classes表中找不到匹配，被排除；四班因为在students表中没有学生关联，也被排除。)

2. 左外连接 (LEFT JOIN / LEFT OUTER JOIN) —— “左表为王”

  • 定义：它会返回左表（FROM子句后的第一个表）的所有行，即使在右表中没有匹配的记录。
  • 工作机制：以左表为基础，去右表中查找匹配的行。
    • 如果找到了匹配行，就将右表的字段合并进来。
    • 如果没找到匹配行，右表的所有字段将显示为 NULL。
  • SQL示例：
    SELECT s.name AS student_name, c.name AS class_name
FROM students s
LEFT JOIN classes c ON s.class_id = c.id;
  • 结果：
    student_nameclass_name
    张三一班
    李四二班
    王五NULL

3. 右外连接 (RIGHT JOIN / RIGHT OUTER JOIN) —— “右表为王”

  • 定义：与左连接相反，它会返回右表（JOIN子句后的表）的所有行，即使在左表中没有匹配的记录。
  • SQL示例：
    SELECT s.name AS student_name, c.name AS class_name
FROM students s
RIGHT JOIN classes c ON s.class_id = c.id;
  • 结果：
    student_nameclass_name
    张三一班
    李四二班
    NULL四班

4. 全外连接 (FULL OUTER JOIN) —— “合二为一”

  • 定义：它会返回两个表中的所有行。如果某一行在另一个表中没有匹配，那么另一个表的字段将显示为NULL。可以看作是左连接和右连接结果的并集。
  • 注意：MySQL本身不直接支持FULL OUTER JOIN关键字。但我们可以通过 LEFT JOIN UNION RIGHT JOIN 来模拟实现。
  • 模拟SQL示例：
    SELECT s.name, c.name FROM students s LEFT JOIN classes c ON s.class_id = c.id
UNION
SELECT s.name, c.name FROM students s RIGHT JOIN classes c ON s.class_id = c.id;
  • 结果：
    student_nameclass_name
    张三一班
    李四二班
    王五NULL
    NULL四班

总结与我的选型

连接类型核心作用
INNER JOIN只返回两个表中都能匹配上的数据
LEFT JOIN保留左表所有数据，右表无匹配则为NULL
RIGHT JOIN保留右表所有数据，左表无匹配则为NULL
FULL OUTER JOIN保留两个表的所有数据，无匹配的另一方为NULL (MySQL需模拟)

在选择连接方式时，我主要考虑：

  • 是否只需要两个表中都有的关联数据？ -> 用 INNER JOIN。
  • 是否需要以一个表为基础，去补充另一个表的信息？ -> 用 LEFT JOIN 或 RIGHT JOIN。
  • 是否需要两个表的全部信息，不管是否关联得上？ -> 用 FULL OUTER JOIN（在MySQL中用UNION模拟）。

MySQL如何避免重复插入数据？

面试官您好，在MySQL中避免重复插入数据，是一个保证数据唯一性和完整性的核心问题。我会从数据库表结构层面（治本） 和SQL语句层面（治标） 这两个维度来设计解决方案。

方案一：数据库表结构层面 —— 建立唯一性约束 (治本之策)

这是最根本、最可靠的解决方案，它利用数据库自身的能力来强制保证数据的唯一性。

1. 设置主键 (PRIMARY KEY)

  • 作用：主键本身就具有唯一且非空的特性。如果我们的重复数据是基于某个ID字段来判断的，那么将这个ID字段设为主键，就是最直接的办法。任何试图插入重复主键值的操作，都会被数据库直接拒绝，并返回一个错误。

2. 建立唯一索引 (UNIQUE KEY)

  • 作用：这是最常用、最灵活的方式。我们可以为一个或多个字段的组合，创建一个唯一索引。
  • 单字段唯一：比如，在用户表中，username或email字段必须是唯一的。
    -- 创建表时定义
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    UNIQUE KEY `uk_username` (`username`)
);

-- 或为已存在的表添加
ALTER TABLE users ADD UNIQUE INDEX `uk_username` (`username`);
  • 多字段联合唯一：比如，在一个“用户-角色”关联表中，一个用户只能拥有一个特定的角色一次。(user_id, role_id)这个组合必须是唯一的。
    CREATE TABLE user_roles (
    user_id INT,
    role_id INT,
    UNIQUE KEY `uk_user_role` (`user_id`, `role_id`)
);
  • 优点：
    • 绝对可靠：由数据库层面保证，无论应用逻辑如何，重复数据都无法插入。
    • 性能高：唯一索引本身也能加速对这些字段的查询。

方案二：SQL语句层面 —— 在插入时进行判断和处理

有时候，我们不希望插入重复数据时直接抛出异常，而是希望有一些更优雅的处理方式。

1. INSERT IGNORE INTO

  • 作用：当执行INSERT操作时，如果因为唯一键冲突（主键或唯一索引）而导致插入失败，这条INSERT语句会被默默地忽略掉，不会产生任何错误。
  • SQL示例：
    INSERT IGNORE INTO users (username) VALUES ('admin');
  • 适用场景：只关心“数据必须存在”，不关心是新插入的还是已存在的，并且不希望程序因为重复插入而中断。

2. REPLACE INTO

  • 作用：这是一个比较“暴力”的方式。当执行REPLACE操作时，如果唯一键冲突，它会先删除那条旧的记录，然后再插入一条新的记录。
  • 警告：这个操作的本质是DELETE + INSERT，如果表上有触发器，会先后触发删除和插入的触发器。并且，如果id是自增的，它会消耗一个新的ID，而不是更新旧的。需要非常谨慎使用。

3. INSERT INTO ... ON DUPLICATE KEY UPDATE (推荐)

  • 作用：这是最常用、最灵活、最推荐的“存在即更新”方案。
  • 工作机制：当执行INSERT操作时，如果发生唯一键冲突，它不会报错，而是会转而去执行UPDATE子句中指定的更新逻辑。
  • SQL示例：假设我们想在用户登录时，更新其最后登录时间，如果用户不存在就创建。
    INSERT INTO users (username, last_login_time) 
VALUES ('admin', NOW())
ON DUPLICATE KEY UPDATE 
last_login_time = NOW();
  • 优点：一条SQL就优雅地实现了“Insert or Update”（也叫Upsert）的逻辑，非常高效和方便。

总结与我的最佳实践

  • 第一道防线（必须有）：在数据库表上建立唯一键约束（主键或唯一索引）。这是保证数据完整性的根本，不能依赖应用层的逻辑。
  • 第二道防线（按需选择）：在应用代码中，根据业务需求选择合适的SQL处理方式：
    • 希望存在就忽略，不存在就插入 -> 使用INSERT IGNORE。
    • 希望存在就更新，不存在就插入 -> 强烈推荐使用ON DUPLICATE KEY UPDATE。
    • 避免在应用层做“先SELECT再INSERT”的逻辑。这在并发下存在竞态条件，不是一个可靠的方案。

通过这两层防线的结合，我们就能非常健壮地处理数据重复插入的问题。

CHAR 和 VARCHAR有什么区别？

面试官您好，CHAR和VARCHAR是MySQL中最常用的两种字符串类型，它们最核心的区别在于其长度的存储和处理方式，这个区别直接导致了它们在存储空间、性能和适用场景上的巨大差异。

1. 核心区别：定长 vs. 变长

  • CHAR(N) (定长)

    • 定义：CHAR是一种固定长度的字符串类型。当我们定义一个CHAR(10)的字段时，无论我们实际存入的数据是"abc"（3个字符）还是"hello"（5个字符），它在数据库中永远都会占用10个字符的存储空间。
    • 存储机制：如果存入的数据长度小于N，MySQL会在其右侧用空格进行填充，以补足到指定的长度。在读取时，这些尾部的空格通常会被自动去除。

  • VARCHAR(N) (变长)

    • 定义：VARCHAR是一种可变长度的字符串类型。N在这里代表的是最大长度。
    • 存储机制：它只会根据实际存入的数据长度来分配存储空间。但除了数据本身，它还需要额外的1到2个字节来记录其实际长度。
      • 如果最大长度N小于等于255，用1个字节记录长度。
      • 如果最大长度N大于255，用2个字节记录长度。

2. 存储空间与性能的权衡

特性CHAR(N)VARCHAR(N)
空间开销固定，可能浪费空间可变，通常更节省空间
更新效率更高、更稳定可能导致性能问题（页分裂）
碎片化不易产生容易产生

  • 空间对比：

    • 当存储的字符串长度非常接近甚至就等于N时，CHAR可能比VARCHAR更节省空间，因为它省去了那1-2个字节的长度记录开销。
    • 当存储的字符串长度差异很大时，VARCHAR的优势就非常明显了，能极大地节约磁盘空间。

  • 性能对比（这是一个更深入的考量点）：

    • CHAR的优势：因为长度固定，CHAR类型的字段在进行更新（UPDATE）操作时，通常不会改变记录的物理长度。这使得数据库在原地更新数据变得非常容易，不容易导致页分裂（Page Split），因此更新性能更稳定。
    • VARCHAR的劣势：如果一个VARCHAR字段的值从一个短字符串更新为一个长字符串（比如从"hi"更新为"hello world"），导致该行的总长度超出了当前数据页的剩余空间，就可能触发代价高昂的页分裂操作，从而影响性能并产生碎片。

3. 选型建议与最佳实践

基于以上对比，我的选型策略非常明确：

  • 什么时候选择CHAR？

    1. 当字段的长度是固定不变或几乎不变的时候。这是CHAR最理想的场景。
      • 典型例子：MD5哈希值（固定32位）、UUID（固定36位）、性别（‘M’/‘F’）、邮政编码、身份证号等。
    2. 当存储的字符串非常短时（比如1-10个字符），即使长度可变，使用CHAR的性能和空间优势也可能超过VARCHAR。

  • 什么时候选择VARCHAR？

    1. 绝大多数场景下，当字段的长度是可变的，都应该使用VARCHAR。
      • 典型例子：用户名、商品标题、文章内容、备注信息等。
    2. 在使用VARCHAR时，一个重要的最佳实践是为N设置一个合理的、尽可能小的最大长度。比如，一个用户名字段，设置成VARCHAR(50)就比VARCHAR(255)要好得多。这不仅能节省空间，还能利用到MySQL的内存优化（比如在排序时可以使用内存临时表）。

总结一下，CHAR追求的是处理速度和性能的稳定性，以可能浪费空间为代价；而VARCHAR追求的是空间的极致利用，以可能在更新时产生一些性能开销为代价。在实际设计中，我们需要根据数据的具体特性来做出最合适的选择。

VARCHAR 后面代表字节还是字符？

面试官您好，在现代的MySQL版本（5.0及以后）中，VARCHAR(N)括号里的数字N，明确代表的是“字符数”（Character Count），而不是字节数。

1. 核心概念：字符 vs. 字节

  • 字符：是我们人类语言中最小的意义单位，比如一个英文字母'A'、一个数字'1'、或者一个汉字'中'，都算作一个字符。
  • 字节：是计算机中存储数据的基本单位。一个字符究竟占用多少个字节，完全取决于数据库所使用的字符集（Charset）。

2. 字符集的影响

不同的字符集，每个字符占用的字节数是不同的：

  • latin1或ascii字符集：

    • 每个字符（主要是英文字母、数字）都只占用1个字节。
    • 在这种情况下，VARCHAR(10)最多能存10个字符，最大占用10个字节。

  • gbk字符集：

    • 一个英文字母占用1个字节，一个汉字占用2个字节。
    • VARCHAR(10)仍然能存10个字符，比如10个汉字，此时它会占用10 * 2 = 20个字节。

  • utf8mb4字符集 (现在最推荐的通用字符集)：

    • 这是一个可变长度的字符集。一个英文字母占用1个字节，一个常用汉字占用3个字节，而一些生僻字或Emoji表情可能占用4个字节。
    • 对于VARCHAR(10)，它可以存：
      • 10个英文字母（占用10字节）。
      • 10个常用汉字（占用30字节）。
      • 10个Emoji表情（占用40字节）。
      • 或者它们的任意组合，只要总字符数不超过10。

3. 存储开销的完整计算

VARCHAR的总物理存储开销，等于真实数据的字节数，再加上1到2个字节用于记录长度的“前缀”。

总开销 = (真实数据的字节数) + (1或2字节的长度前缀)

4. 一个重要的“边界”

VARCHAR的N虽然理论上最大可以设置到65535，但实际上它会受到MySQL单行最大长度（65535字节） 的限制。

  • 比如，在一张utf8mb4编码的表中，由于一个字符最多可能占用4个字节，所以你最多只能定义一个VARCHAR(16383)左右的字段（16383 * 4约等于65532），因为还要给其他字段和一些内部开销留出空间。

总结

VARCHAR(N)中的N是字符数。这是一个非常人性化的设计，因为它让我们在定义字段时，可以更专注于业务含义（比如“用户名最多20个字”），而不需要去过多地关心底层不同字符集导致的字节换算问题。但我们在设置N的大小时，也需要对字符集有一个基本的了解，以便估算其可能占用的最大物理空间。

INT(1) 和 INT(10) 在 MySQL 中有什么不同？

面试官您好，INT(1)和INT(10)的区别：在存储空间、计算逻辑和可存储的数据范围方面，INT(1)和INT(10)没有任何区别。

1. 破除误解：括号里的数字不是长度限制

很多初学者会误以为INT(1)只能存1位数的整数，INT(10)能存10位数的整数。这是完全错误的。

  • 存储空间固定：在MySQL中，INT这个数据类型，无论你怎么写，它在磁盘上占用的存储空间永远是固定的4个字节（32位）。
  • 存储范围固定：因此，它的存储范围也永远是固定的。对于有符号的INT，范围是-2147483648到2147483647。
  • 结论：无论你定义的是INT(1)还是INT(10)，你都可以往里面存入123456789这样的数字，只要它在INT的有效范围内。

2. 唯一的区别：显示宽度 (Display Width)

那么，括号里的这个数字到底是什么意思呢？

  • 它仅仅是一个 “显示宽度” 的元数据提示。这个概念是从早期的命令行客户端继承下来的，用于告诉客户端在显示这个字段的查询结果时，应该预留多少个字符的宽度。

3. 唯一生效的场景：配合ZEROFILL使用

在现代的MySQL客户端和各种编程语言的驱动中，这个“显示宽度”提示基本上已经被完全忽略了。它唯一还能产生可见效果的场景，就是当这个字段同时被设置了 ZEROFILL 属性时。

  • ZEROFILL的作用：它会自动地用前导零，来填充数字，使其达到指定的“显示宽度”。
  • 举例说明：
    假设我们有一个字段id，类型是INT(5) ZEROFILL。
    • 如果我们存入的值是123，那么查询出来显示时，就会变成 00123。
    • 如果我们存入的值是123456（超过了显示宽度），它不会被截断，查询出来仍然是 123456。ZEROFILL只负责补零，不负责截断。
  • 注意：一旦为字段设置了ZEROFILL，该字段会自动变为 UNSIGNED（无符号）。

4. 现代开发的最佳实践

  • 为什么这个特性现在几乎无用了？
    • 因为数据的展示格式，现在几乎完全是由应用程序的后端或前端代码来控制的，而不是依赖于数据库的ZEROFILL这种底层特性。比如，我们需要一个5位数的订单号，我们会在Java代码里用String.format("%05d", orderId)来实现，而不是在数据库里。
  • 我的实践建议：
    • 在创建表时，直接使用INT或BIGINT即可，完全不需要在后面加括号和数字。比如：
      CREATE TABLE my_table (
    id INT,
    user_id BIGINT
);
    • 这样做，代码更简洁，也避免了给其他开发者带来不必要的困惑。

总结一下，INT(1)和INT(10)在功能和存储上完全一样。括号里的数字是一个历史遗留的、只在配合ZEROFILL时才生效的“显示宽度”属性，在现代应用开发中，我们应该直接忽略它，使用不带括号的INT。

TEXT 数据类型可以无限大吗？

面试官您好，TEXT数据类型并不是无限大的，它有明确的长度限制。

MySQL为了满足不同长度文本的存储需求，提供了TEXT类型的一个“家族”，主要有以下几种：

类型最大长度 (字节数)约等于长度记录开销
TINYTEXT255 (2^8 - 1)255 B1字节
TEXT65,535 (2^16 - 1)64 KB2字节
MEDIUMTEXT16,777,215 (2^24 - 1)16 MB3字节
LONGTEXT4,294,967,295 (2^32 - 1)4 GB4字节

TEXT与VARCHAR的核心区别

在选择存储长文本时，我们经常会在TEXT和VARCHAR之间犹豫。它们有几个本质的区别：

  1. 行内存储 vs. 行外存储：

    • VARCHAR：在MySQL中，VARCHAR的数据通常是存储在数据行内部的（除非行总长度超过了限制）。
    • TEXT：为了不让单行数据过大，TEXT类型的数据通常是存储在行外的专用存储空间中，而在数据行内部，只保留一个指向这块外部空间的指针。

  2. 默认值：

    • VARCHAR字段可以有默认值（DEFAULT）。
    • TEXT（以及BLOB）字段不能有默认值。

  3. 索引：

    • VARCHAR字段可以被直接创建完整索引。
    • TEXT字段因为可能非常大，不能直接创建完整索引。如果需要索引，必须指定一个前缀长度，比如INDEX(content(255))，只对内容的前255个字符创建索引。

使用TEXT类型的注意事项与最佳实践

正是因为TEXT类型的这些底层特性，我们在使用它时需要特别注意：

  1. 性能开销：由于数据可能存储在行外，每次读取TEXT字段，都可能需要一次额外的磁盘I/O（去获取指针指向的数据），这会比直接读取行内的VARCHAR性能要差。因此，在查询时，应该避免不必要地SELECT *，只在确实需要时才查询TEXT字段。
  2. 排序与分组：对TEXT字段进行ORDER BY或GROUP BY操作，性能会非常低下，因为它可能需要在磁盘上创建巨大的临时表。应尽量避免这种操作。
  3. 内存使用：如果在查询中涉及到对TEXT字段的排序或连接，MySQL可能会在内存中分配大量的临时空间，容易导致内存问题。

选型建议

  • VARCHAR优先原则：如果能够预估出文本的最大长度，并且这个长度在MySQL的行长度限制内（通常几千个字符内），总是优先选择VARCHAR。比如，文章标题、用户简介等，用VARCHAR(255)或VARCHAR(1000)就足够了。VARCHAR的性能通常更好。
  • 什么时候用TEXT？ 只有当需要存储的文本长度非常不确定，且可能非常大（超过VARCHAR的最大限制，或者几十KB以上）时，才应该选择TEXT类型。
    • 典型场景：用户发表的文章正文、商品详情的长描述、存储的JSON或XML文档等。
    • 在TEXT家族中，也应该按需选择最小的类型。比如，如果确认内容不会超过64KB，就用TEXT，而不是MEDIUMTEXT或LONGTEXT，因为更小的类型，其指针和长度记录的开销也更小。

总结一下，TEXT不是无限大的，它是一个为了存储超长文本而设计的“重型武器”。在使用它时，我们必须意识到它带来的性能开销，并遵循“按需查询、避免排序”的最佳实践。

IP 地址如何在数据库里存储？

面试官您好，在数据库中存储IP地址，主要有两种主流的方法：使用字符串类型（如VARCHAR）和使用整型（如INT或BIGINT）。

这两种方法各有优劣，但在追求性能和存储效率的场景下，将IP地址转换为整型来存储，是更优的、也是业界推荐的最佳实践。

方案一：使用字符串 (VARCHAR) 存储

这是最直观、最简单的方式。

  • 如何做：直接在表中创建一个VARCHAR(15)（对于IPv4）的字段来存储点分十进制格式的IP地址字符串，如"192.168.1.1"。
  • 优点：
    • 可读性极高：在数据库中直接查询时，看到的就是我们熟悉的IP地址格式，非常直观，便于调试和人工查看。
    • 实现简单：应用程序无需任何转换，直接将获取到的IP字符串存入即可。
  • 缺点：
    • 存储空间占用大：存储一个IPv4地址，最长需要15个字节，远大于整型的4个字节。
    • 查询和比较效率低：基于字符串的比较、排序和范围查询，其效率远低于基于整数的数值运算。比如，要查询一个IP段，需要使用LIKE或者复杂的字符串函数，无法高效地利用索引。

方案二：使用整型 (INT UNSIGNED) 存储 (推荐)

这是更专业、性能更好的方式。

  • 核心思想：IP地址本质上是一个32位（IPv4）的无符号整数。我们可以利用数据库的函数，将其与整数形式进行相互转换。

  • 如何做 (以IPv4为例)：

    • 数据类型：使用INT UNSIGNED（4字节无符号整型）来存储。
    • 转换函数：MySQL提供了两个非常方便的函数：
      • INET_ATON('ip_address'): 将点分十进制的IP字符串，转换为一个32位无符号整数。
      • INET_NTOA(integer_ip): 将整数形式的IP，转换回点分十十进制的字符串。

  • SQL示例：

    -- 创建表
CREATE TABLE access_logs (
    ip_address INT UNSIGNED NOT NULL,
    ...
);

-- 插入数据
INSERT INTO access_logs (ip_address) VALUES (INET_ATON('192.168.1.1'));

-- 查询数据并转换回字符串显示
SELECT INET_NTOA(ip_address) FROM access_logs WHERE ip_address = INET_ATON('192.168.1.1');

  • 优点：

    1. 存储空间小：INT UNSIGNED只需要4个字节，相比VARCHAR(15)，空间占用大大减少。
    2. 查询效率极高：可以非常高效地进行数值比较、排序和范围查询。比如，要查询一个IP段（从start_ip到end_ip），只需要用BETWEEN操作即可：
      SELECT ... FROM access_logs 
WHERE ip_address BETWEEN INET_ATON('192.168.0.0') AND INET_ATON('192.168.255.255');
      这种查询可以完美地利用索引，性能极佳。

  • 对于IPv6：IPv6是128位的，需要使用VARBINARY(16)来存储其二进制形式，MySQL同样提供了对应的INET6_ATON()和INET6_NTOA()函数。

总结与选型

对比维度VARCHARINT UNSIGNED
存储空间 (IPv4)7 ~ 15 字节 + 长度前缀4 字节 (固定)
可读性高低 (需要函数转换)
查询/比较效率低高
范围查询复杂，低效简单，高效 (BETWEEN)

我的选型建议：

  • 对于那些对性能和存储要求不高、且需要频繁人工查看的少量数据场景（比如后台管理的黑白名单配置），使用VARCHAR是简单可行的。
  • 但对于所有大批量的、需要进行高效查询和分析的场景，比如用户访问日志、安全审计日志、IP地理位置库等，毫无疑问应该选择使用INT UNSIGNED来存储。这是一种用“可读性”换取“巨大性能和存储优势”的专业做法，是业界的最佳实践。

说一下外键约束

面试官您好，外键（Foreign Key）约束是关系型数据库中一个非常重要的概念，它的核心作用是在两个表之间建立一种强制性的关联关系，以保证数据的引用完整性（Referential Integrity）。

1. 外键是做什么的？

我们可以用一个简单的例子来理解它：一个订单表 (orders)和一个顾客表 (customers)。

  • 常理上，每一笔订单都必须属于一个真实存在的顾客。我们不希望数据库里出现一笔“无主”的、找不到顾客的订单。
  • 外键约束就是用来在数据库层面，强制执行这个业务规则的。

2. 外键是如何工作的？

  1. 定义：我们会在“从表”（orders表）中创建一个字段，比如customer_id。然后，为这个customer_id字段添加一个外键约束，让它引用“主表”（customers表）的主键（id）。
    -- 主表：顾客表
CREATE TABLE customers (id INT PRIMARY KEY, ...);

-- 从表：订单表
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    -- 定义外键约束
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);
  2. 强制的约束行为：一旦这个外键关系建立，数据库就会像一个“严格的门卫”，执行一系列规则：
    • 插入/更新从表时：当你试图在orders表中插入一条记录时，数据库会检查你提供的customer_id，是否在customers表的主键id中真实存在。如果不存在，数据库会拒绝这次操作。
    • 删除/更新主表时：当你试图从customers表中删除一个顾客时，数据库会检查orders表中是否还有订单在引用这个顾客。如果有关联的订单存在，那么直接的删除操作会失败，以防止产生“孤儿订单”。

3. 外键的级联操作 (ON DELETE / ON UPDATE)

为了更灵活地处理主表记录被删除或更新时的情况，外键约束还提供了几种级联操作策略：

  • ON DELETE CASCADE: 当主表中的记录被删除时，所有从表中引用该记录的行也会自动地被一并删除。
  • ON DELETE SET NULL: 当主表记录被删除时，从表中对应行的外键字段会被自动设置为NULL。
  • ON DELETE RESTRICT / ON DELETE NO ACTION: 这是默认的行为，即如果存在子表记录，就禁止删除主表记录。

ON UPDATE也有类似的级联操作。

4. 外键的优点与缺点（实践中的权衡）

  • 优点：

    1. 数据完整性的终极保障：由数据库层面来保证数据的一致性，这是最可靠的，比完全依赖应用层的逻辑校验要健壮得多。
    2. 清晰地表达数据关系：外键清晰地定义了表与表之间的依赖关系，使得数据库结构更易于理解。

  • 缺点与争议（为什么很多互联网公司不用）：

    1. 性能开销：每次对从表进行INSERT/UPDATE，或者对主表进行DELETE/UPDATE，数据库都需要进行一次额外的检查，这在高并发的写入场景下，会带来一定的性能损耗。
    2. 数据库的强耦合：外键使得数据库表之间产生了紧密的耦合。在进行数据库迁移、分库分表等架构演进时，外键会成为一个巨大的障碍。
    3. 增加了数据库的复杂性：级联操作虽然方便，但也可能因为一次误操作导致数据的意外连锁删除，存在一定风险。

选型结论

  • 在一些传统的、对数据一致性要求极高、且写入并发不那么夸张的企业级应用（如ERP、CRM） 中，使用外键是一个非常好的实践。
  • 但在高并发、高流量、追求极致性能和快速迭代的互联网应用中，很多公司会选择放弃使用物理外键。他们会把数据一致性的保证，上移到应用层的业务逻辑中去实现，以换取数据库的更高性能和更大的架构灵活性。

总结一下，外键是保证数据引用完整性的强大数据库特性。但在实践中，我们需要在“数据的强一致性”和“系统的高性能与灵活性”之间，根据具体的业务场景和架构目标，做出一个明智的权衡。

MySQL 的关键字 IN 和 EXIST 了解吗？

面试官您好，IN和EXISTS是SQL中两个非常重要的关键字，它们都用于子查询中，来实现“一个表中的记录是否存在于另一个表中”的判断。但它们的底层执行逻辑完全不同，这也导致了它们在不同场景下的性能表现差异巨大。

1. IN 的工作原理

  • SQL写法：
    SELECT * FROM classes WHERE id IN (SELECT class_id FROM students);
  • 执行逻辑：
    1. 首先，执行子查询：它会先完整地执行括号里的子查询SELECT class_id FROM students，并将所有查询到的class_id构建成一个内存中的临时集合或哈希表。如果子查询结果集很大，这里可能会有较大的内存和时间开销。
    2. 然后，执行外层查询：接着，它会遍历外层表classes的每一行。
    3. 进行判断：对于classes表中的每一行，它会拿着这一行的id，去上一步构建好的那个内存集合中进行查找，判断是否存在。
  • 核心特点：先执行子查询，再执行主查询。子查询只执行一次。

2. EXISTS 的工作原理

  • SQL写法：
    SELECT * FROM classes c WHERE EXISTS (SELECT 1 FROM students s WHERE s.class_id = c.id);
  • 执行逻辑：
    1. 首先，执行外层查询：它会先遍历外层表classes的第一行。
    2. 然后，执行子查询（关联查询）：拿到外层第一行的c.id后，它会去执行括号里的子查询SELECT 1 FROM students s WHERE s.class_id = c.id。
    3. 进行判断：
      • 子查询的目的不是为了返回数据，而只是为了判断 “是否存在匹配的行”。所以我们通常写SELECT 1或SELECT *，性能没区别。
      • 只要子查询能找到哪怕一行匹配的数据，它就会立即停止执行，并向外层返回TRUE。
      • 如果子查询扫描了整个students表都没有找到匹配的行，它就向外层返回FALSE。
    4. 循环：接着，它会继续取classes表的第二行，重复第2、3步，直到遍历完整个classes表。
  • 核心特点：先执行主查询，子查询的执行次数取决于主查询的结果集大小。它是一种关联子查询（Correlated Subquery）。

性能对比与选型法则

理解了它们的原理，我们就能得出一个非常经典的性能优化法则：

“小表驱动大表”

  • 当子查询的结果集（B表）很小时：

    • 应该使用IN。因为IN会先把这个小结果集加载到内存里，外层的大表（A表）在进行匹配时，是在高效的内存集合里查找，速度很快。
    • 如果此时用EXISTS，外层的大表有多少行，子查询就要被执行多少次，效率会很低。

  • 当外层查询的表（A表）很小时：

    • 应该使用EXISTS。因为EXISTS会先遍历这个小表，子查询的执行次数就很少。
    • 并且，EXISTS的子查询通常能利用到索引。在WHERE s.class_id = c.id这个条件上，如果students表的class_id字段有索引，那么每次子查询都会非常快。
    • 如果此时用IN，它会先去执行那个大结果集的子查询，可能会消耗大量时间和内存。

一个简单好记的结论：

场景推荐使用
外层表大，内层(子查询)表小IN
外层表小，内层(子查询)表大EXISTS

关于NOT IN和NOT EXISTS

这个法则在NOT的场景下，结论通常是相反的，但更重要的是：

  • NOT IN有一个巨大的“陷阱”：如果子查询的结果集中包含了任何NULL值，那么NOT IN的整个查询结果将永远为空，这通常不是我们想要的结果。
  • NOT EXISTS则没有这个问题，它的逻辑更严谨。
  • 因此，在需要进行“不存在”判断时，强烈推荐总是使用NOT EXISTS，以避免NOT IN带来的NULL值陷阱。

MySQL中的一些基本函数，你知道哪些？

面试官您好，MySQL提供了非常丰富的内置函数，它们极大地增强了SQL的查询和处理能力。在我的日常开发中，我经常会使用到以下几类函数：

1. 字符串函数 (String Functions)

这类函数用于处理和操作字符串，非常常用。

  • CONCAT(s1, s2, ...): 用于拼接多个字符串。
  • LENGTH(str) vs CHAR_LENGTH(str):
    • LENGTH()返回字符串的字节长度。
    • CHAR_LENGTH()返回字符串的字符长度。在处理多字节字符（如UTF-8编码的汉字）时，这个区别非常重要。
  • SUBSTRING(str, pos, len): 从字符串中截取子串。
  • UPPER(str) / LOWER(str): 将字符串转换为大写或小写。
  • REPLACE(str, from_str, to_str): 替换字符串中的子串。
  • TRIM(str): 去除字符串首尾的空格。
  • GROUP_CONCAT(expr): 这是一个聚合函数，可以将一个分组内的多行字符串，用逗号拼接成一个单一的字符串。非常适合做一些“一对多”关系的报表展示。

2. 数值函数 (Numeric Functions)

这类函数用于进行数学运算。

  • ROUND(x, d): 对数字x进行四舍五入，保留d位小数。
  • CEIL(x) / FLOOR(x): 向上取整和向下取整。
  • ABS(x): 返回数字的绝对值。
  • RAND(): 生成一个0到1之间的随机数。

3. 日期和时间函数 (Date and Time Functions)

处理日期时间是后端开发的日常，这些函数必不可少。

  • NOW() / CURRENT_TIMESTAMP(): 获取当前的日期和时间。
  • CURDATE(): 只获取当前日期。
  • CURTIME(): 只获取当前时间。
  • DATE_FORMAT(date, format): 将日期格式化成指定的字符串。比如DATE_FORMAT(NOW(), '%Y-%m-%d')会得到"2023-10-27"。
  • DATE_ADD(date, INTERVAL expr unit) / DATE_SUB(...): 对日期进行加减运算。比如DATE_ADD(NOW(), INTERVAL 1 DAY)就是获取明天的日期。
  • DATEDIFF(date1, date2): 计算两个日期之间的天数差。

4. 聚合函数 (Aggregate Functions)

这些函数通常与GROUP BY子句一起使用，用于进行统计计算。

  • COUNT(expr): 计算行数。COUNT(*)或COUNT(1)计算总行数，COUNT(column)计算该列非NULL值的行数。
  • SUM(expr): 求和。
  • AVG(expr): 求平均值。
  • MAX(expr) / MIN(expr): 求最大/最小值。

5. 控制流函数 (Control Flow Functions)

这类函数让SQL也能实现一些简单的逻辑判断。

  • IF(expr1, expr2, expr3): 如果expr1为真，返回expr2，否则返回expr3。类似于Java中的三元运算符。
  • IFNULL(expr1, expr2): 如果expr1不为NULL，返回expr1，否则返回expr2。非常适合用来处理NULL值的默认显示。
  • CASE ... WHEN ... THEN ... ELSE ... END: 实现更复杂的多条件判断，类似于Java中的switch或多重if-else。

6. 类型转换函数 (Cast Functions)

  • CAST(expr AS type) / CONVERT(expr, type): 用于显式地进行数据类型转换。

熟练地运用这些内置函数，可以让我们将很多原本需要在Java代码中处理的逻辑，下沉到数据库层面来完成，通常能获得更好的性能，并且让SQL查询本身更具表现力。

SQL 查询语句的执行顺序是怎么样的？

面试官您好，SQL查询语句的执行顺序，指的是数据库查询引擎在逻辑上处理一个查询的步骤顺序，这个顺序与我们编写SQL语句的顺序有很大的不同。

理解这个逻辑执行顺序，对于我们编写正确的SQL以及进行性能优化（比如理解索引为什么会生效），都至关重要。

我们编写的SQL顺序

通常，我们编写一个复杂的SELECT语句，其顺序是这样的：
SELECT -> FROM -> WHERE -> GROUP BY -> HAVING -> ORDER BY -> LIMIT

数据库逻辑上的执行顺序

而数据库在解析和执行这个查询时，其逻辑上的处理流程，大致遵循以下顺序：

1. FROM 和 JOIN —— 确定数据源

  • (1) FROM: 首先，确定查询的主表。
  • (2) JOIN ... ON ...: 接着，处理JOIN子句。根据ON中的连接条件，将多个表的数据合并起来，形成一个包含所有相关数据的、巨大的中间结果集。

2. WHERE —— 行级过滤

  • (3) WHERE: 对上一步生成的中间结果集，逐行应用WHERE子句中的条件进行过滤。只有满足条件的行才会被保留下来。
    • 这个阶段是索引大显身手的地方。如果WHERE条件中的字段有索引，数据库就能高效地进行过滤，而无需全表扫描。

3. GROUP BY —— 分组

  • (4) GROUP BY: 如果有GROUP BY子句，数据库会将上一步过滤后的结果，按照指定的列进行分组，形成多个组。

4. HAVING —— 组级过滤

  • (5) HAVING: 对上一步分组后的结果，应用HAVING子句中的条件进行过滤。只有满足条件的分组才会被保留下来。
    • HAVING与WHERE的关键区别：WHERE在分组前对行进行过滤；HAVING在分组后对组进行过滤。HAVING子句中可以使用聚合函数（如COUNT(*) > 5），而WHERE中不能。

5. SELECT —— 选取列与计算

  • (6) SELECT: 现在，查询引擎才真正开始处理SELECT子句。它会从上一步的结果中，选取出我们最终需要的那些列，并可以进行计算、使用函数、或者为列起别名。

6. DISTINCT —— 去重

  • (7) DISTINCT: 如果SELECT后面有DISTINCT关键字，引擎会对上一步的结果进行去重。

7. ORDER BY —— 排序

  • (8) ORDER BY: 对上一步的结果，按照ORDER BY子句中指定的列和顺序进行排序。
    • 注意：ORDER BY通常在最后阶段执行，所以如果排序的字段没有索引，当结果集很大时，这个排序操作会非常耗费内存和CPU。

8. LIMIT / OFFSET —— 分页

  • (9) LIMIT: 最后，如果有利LIMIT子句，引擎会从排序好的结果中，截取出指定范围的行，作为最终的查询结果返回给客户端。

总结

用一个流程图来概括就是：
FROM/JOIN -> WHERE -> GROUP BY -> HAVING -> SELECT -> DISTINCT -> ORDER BY -> LIMIT

这个逻辑执行顺序解释了很多SQL现象，比如：

  • 为什么WHERE子句中不能使用SELECT中定义的别名？因为SELECT在WHERE之后才执行。
  • 为什么ORDER BY可以用别名？因为它在SELECT之后执行。

理解这个顺序，是编写正确、高效SQL的基石。

SQL题：给学生表、课程成绩表，求不存在 01 课程但存在 02 课程的学生的成绩

面试官您好，这是一个非常经典的SQL面试题，它考察的是如何通过查询来实现集合的“差集”操作。

为了解决这个问题，我先假设有两张表：student（学生表）和score（成绩表），结构如下：

student (学生表)

s_ids_name
01赵雷
……
05周梅

score (课程成绩表)

s_idc_ids_score
010180
010290
………
050276

目标：找出“周梅”这位同学，并返回他/她的所有成绩。

我会从几种不同的思路来给出解决方案：

解法一：使用IN和NOT IN (最直观)

这种解法最符合我们人类的思考逻辑。

  • 思路：

    1. 第一步：找出所有选了'02'课程的学生的ID。
    2. 第二步：找出所有选了'01'课程的学生的ID。
    3. 第三步：从第一步的结果集中，筛选出那些不在第二步结果集中的学生ID。
    4. 第四步：根据筛选出的学生ID，去score表中查询他们的所有成绩。

  • SQL实现：

    SELECT *
FROM score
WHERE s_id IN (
    -- 步骤3: 找出只选了02，但没选01的学生ID
    SELECT s_id FROM score WHERE c_id = '02'
    EXCEPT -- 或者使用 s_id NOT IN (...)
    SELECT s_id FROM score WHERE c_id = '01'
);

    或者使用更通用的NOT IN：

    SELECT *
FROM score
WHERE s_id IN (SELECT s_id FROM score WHERE c_id = '02')
  AND s_id NOT IN (SELECT s_id FROM score WHERE c_id = '01');
    • 注意：NOT IN的子查询中，如果结果包含NULL，可能会产生意想不到的结果。使用NOT EXISTS通常更健壮。

解法二：使用LEFT JOIN和IS NULL (性能通常更好)

这种解法通过LEFT JOIN来巧妙地实现“差集”的逻辑。

  • 思路：

    1. 先找出所有选了'02'课程的学生记录。
    2. 将这个结果集，与所有选了'01'课程的学生记录进行左连接，连接条件是s_id相等。
    3. 如果一个选了'02'课程的学生，也选了'01'，那么左连接一定能成功匹配上，右边的字段将不会是NULL。
    4. 反之，如果一个选了'02'课程的学生，没有选'01'，那么左连接会失败，右边的字段将全部为NULL。
    5. 我们只需要筛选出那些连接后右边字段为NULL的记录，就找到了目标学生。

  • SQL实现：

    SELECT s02.*
FROM 
    (SELECT * FROM score WHERE c_id = '02') AS s02
LEFT JOIN 
    (SELECT * FROM score WHERE c_id = '01') AS s01 
ON s02.s_id = s01.s_id
WHERE 
    s01.s_id IS NULL; -- 关键：筛选出那些在右表中找不到匹配的记录

    （注：如果题目要求返回该学生的所有成绩，而不是仅02课程的成绩，则需要将上述查询作为子查询）

解法三：使用GROUP BY和HAVING (思路巧妙)

这种解法利用了分组和聚合函数来在一个查询中完成筛选。

  • 思路：

    1. 按学生ID (s_id) 进行分组。
    2. 在每个分组内，我们去统计这个学生选了'01'课程的次数和选了'02'课程的次数。
    3. 使用HAVING子句来筛选出那些 “选了'02'课程的次数大于0，并且选了'01'课程的次数等于0” 的那些学生。

  • SQL实现：

    SELECT s_id
FROM score
GROUP BY s_id
HAVING
    -- 确保选了'02'
    SUM(CASE WHEN c_id = '02' THEN 1 ELSE 0 END) > 0
AND
    -- 确保没选'01'
    SUM(CASE WHEN c_id = '01' THEN 1 ELSE 0 END) = 0;

    然后，同样可以用这个结果作为子查询，去score表里查出该学生的所有成绩。

总结与推荐

解法优点缺点/注意
NOT IN最符合直觉NULL值陷阱，子查询结果大时性能可能不佳
LEFT JOIN性能通常较好，逻辑严谨SQL写法稍显复杂
GROUP BY/HAVING思路巧妙，一次扫描完成聚合函数的使用需要熟练掌握

我个人在实践中，更倾向于使用 LEFT JOIN 或 NOT EXISTS 的方式来解决这类问题。

SQL题：给定一个学生表 student_score（stu_id, subject_id, score），查询总分排名在 5-10 名的学生 id 及对应的总分

面试官您好，这是一个非常经典的Top N问题扩展，考察的是如何进行分组、排序和范围筛选。我会根据数据库是否支持窗口函数，提供两种不同的解决方案。

方案一：使用窗口函数 (Window Functions) —— 推荐的最佳实践

这是在支持窗口函数的数据库（如MySQL 8.0+, PostgreSQL, Oracle等）中，最简洁、最高效、最推荐的解法。

  • 思路：

    1. 使用GROUP BY子句，按stu_id分组，并用SUM(score)计算出每个学生的总分。
    2. 在第一步的结果集之上，使用DENSE_RANK()或RANK()窗口函数，对总分（total_score）进行降序排名。
    3. 最后，将整个带有排名的结果作为一个子查询（或CTE），在外层查询中筛选出排名在5到10之间的记录。

  • 为什么用DENSE_RANK()或RANK()？

    • RANK()：如果出现并列名次，会跳过之后的排名。比如，两个人并列第2，那么下一个名次就是第4。
    • DENSE_RANK()：如果出现并列名次，不会跳过之后的排名。比如，两个人并列第2，下一个名次仍然是第3。在大多数“Top N”的场景中，DENSE_RANK()更符合业务直觉。
    • ROW_NUMBER()：不考虑并列，为每一行分配一个唯一的、连续的排名。

  • SQL实现 (使用DENSE_RANK)：

    -- 使用CTE (Common Table Expression) 让查询更清晰
WITH RankedScores AS (
    SELECT
        stu_id,
        SUM(score) AS total_score,
        -- 对总分进行降序排名
        DENSE_RANK() OVER (ORDER BY SUM(score) DESC) AS score_rank
    FROM
        student_score
    GROUP BY
        stu_id
)
-- 筛选出排名在5到10之间的学生
SELECT
    stu_id,
    total_score
FROM
    RankedScores
WHERE
    score_rank BETWEEN 5 AND 10;
    • 不使用CTE的嵌套子查询写法：
    SELECT stu_id, total_score
FROM (
    SELECT
        stu_id,
        SUM(score) AS total_score,
        DENSE_RANK() OVER (ORDER BY SUM(score) DESC) AS score_rank
    FROM student_score
    GROUP BY stu_id
) AS RankedScores
WHERE score_rank BETWEEN 5 AND 10;

方案二：使用LIMIT和OFFSET (兼容旧版MySQL)

在不支持窗口函数的旧版MySQL中，我们可以通过先排序，再使用LIMIT和OFFSET来模拟这个分页查询。

  • 思路：

    1. 先计算出每个学生的总分，并按总分降序排列。
    2. 使用LIMIT子句来获取指定范围的记录。LIMIT 6 OFFSET 4意味着“跳过前4条记录（排名1-4），然后取接下来的6条记录（排名5-10）”。

  • SQL实现：

    SELECT
    stu_id,
    SUM(score) AS total_score
FROM
    student_score
GROUP BY
    stu_id
ORDER BY
    total_score DESC
-- 跳过前4名，然后取6条记录
LIMIT 6 OFFSET 4;
    • 另一种等效写法：LIMIT 4, 6（第一个参数是offset，第二个是count）。

  • 这种方法的局限性：

    • 无法正确处理并列排名。LIMIT只是简单地按物理行号来截取。如果第4名和第5名是并列的，这种方法可能会错误地将并列第4名的某个学生排除掉，或者只包含部分并列第10名的学生。而窗口函数则能完美处理并列情况。

总结

方案优点缺点/注意
窗口函数逻辑清晰、能正确处理并列排名需要数据库版本支持（如MySQL 8.0+）
LIMIT OFFSET兼容性好，写法简单无法正确处理并列排名

因此，在支持窗口函数的现代数据库中，使用DENSE_RANK()或RANK()是解决此类排名问题的标准、最佳实践。在旧版MySQL等不支持的环境中，可以使用 ORDER BY + LIMIT 作为一种近似的、简化的解决方案，但必须清楚地意识到它无法处理并列排名的问题。

SQL题：查某个班级下所有学生的选课情况

面试官您好，这是一个非常典型的多表关联查询问题。我会根据具体的需求——是只看有选课的学生，还是看所有学生，来提供不同的解决方案。

前提：表结构假设

我们先假设有三张表：students (学生信息), classes (学生班级), student_courses (学生选课)。

目标：查询“一班”所有学生的选课情况。

解法一：使用INNER JOIN (只展示有选课记录的学生)

这是最基础、最直接的解法，通过多级JOIN将三张表关联起来。

  • 思路：

    1. 以students表为基础。
    2. 用students.s_id和classes.s_id内连接classes表，筛选出所有学生。
    3. 用students.s_id和student_courses.s_id内连接student_courses表，获取选课信息。
    4. 最后，用WHERE子句筛选出class_name = '一班'的记录。

  • SQL实现：

    SELECT
    s.s_id,
    s.s_name,
    c.class_name,
    sc.course_name
FROM
    students s
INNER JOIN
    classes c ON s.s_id = c.s_id
INNER JOIN
    student_courses sc ON s.s_id = sc.s_id
WHERE
    c.class_name = '一班';

  • 优点：逻辑清晰，易于理解。

  • 缺点：如果某个学生没有选任何课，由于INNER JOIN的特性，他将不会出现在结果中。如果需求是“即使没选课也要展示出来”，就需要用LEFT JOIN。

解法二：使用LEFT JOIN (展示所有学生，包括未选课的)

如果需要展示班级里所有学生，无论他们是否选了课，LEFT JOIN是更完整、更严谨的选择。

  • 思路：与解法一类似，但将连接student_courses表的INNER JOIN改为LEFT JOIN。这样，即使学生在student_courses表中没有匹配的记录，学生信息本身也会被保留。

  • SQL实现：

    SELECT
    s.s_id,
    s.s_name,
    c.class_name,
    sc.course_name -- 如果没选课，这里会是NULL
FROM
    students s
INNER JOIN -- 先用INNER JOIN筛选出一班的学生
    classes c ON s.s_id = c.s_id AND c.class_name = '一班'
LEFT JOIN -- 再用LEFT JOIN关联他们的选课情况
    student_courses sc ON s.s_id = sc.s_id;

  • 优点：能保证“一班”的所有学生都会出现在结果中，信息更完整。

解法三：使用GROUP_CONCAT (将选课情况合并展示)

有时候，我们不希望每个学生的每门课都占一行，而是希望每个学生只占一行，他选的所有课程合并在一个字段里显示。

  • 思路：

    1. 先像解法二一样，通过LEFT JOIN连接所有表并筛选出“一班”的学生。
    2. 在得到的结果集上，按学生ID和姓名进行GROUP BY分组。
    3. 使用GROUP_CONCAT()聚合函数，将每个学生分组内的所有course_name用逗号拼接起来。

  • SQL实现：

    SELECT
    s.s_id,
    s.s_name,
    c.class_name,
    -- 使用GROUP_CONCAT将课程名拼接，如果为NULL则显示提示信息
    IFNULL(GROUP_CONCAT(sc.course_name SEPARATOR ', '), '未选课') AS courses
FROM
    students s
INNER JOIN
    classes c ON s.s_id = c.s_id
LEFT JOIN 
    student_courses sc ON s.s_id = sc.s_id
WHERE
    c.class_name = '一班'
GROUP BY
    s.s_id, s.s_name, c.class_name;

  • 优点：结果集更紧凑，可读性更强，非常适合在报表或前端页面直接展示。

总结与推荐

解法适用场景
INNER JOIN只需展示有选课记录的学生。
LEFT JOIN需展示班级内所有学生，并标明其选课情况。
GROUP_CONCAT需将结果聚合展示，让每个学生只占一行。
# java # mysql # sql
Logo
乐奇 Rokid 开放社区

更多推荐

cover

国庆文旅安保上新！AR智能眼镜守护景区，黑科技护航假期出游

avatar 乐奇 Rokid 开放社区
cover

2027中国深圳智能穿戴产业链展览会

avatar 乐奇 Rokid 开放社区
cover

AR巡检哪家好

avatar 乐奇 Rokid 开放社区