MySQL 数据库面试题（Day1：基础 + SQL 实操）

一、基础概念类（核心认知）

1. 关系型数据库和非关系型数据库（NoSQL）的核心区别？各举 3 个常用产品及适用场景

类型	常用产品	典型适用场景
关系型	MySQL、Oracle、PostgreSQL	电商订单、金融交易（强一致性）
NoSQL	Redis、MongoDB、Elasticsearch	缓存、日志存储、全文检索

-- 创建学生表（主键示例）
CREATE TABLE student (
  id INT PRIMARY KEY AUTO_INCREMENT, -- 主键（自增）
  stu_no VARCHAR(20) UNIQUE, -- 唯一键（学号）
  name VARCHAR(30) NOT NULL
);

-- 创建成绩表（外键示例）
CREATE TABLE score (
  id INT PRIMARY KEY AUTO_INCREMENT,
  stu_id INT,
  score INT,
  -- 外键关联学生表主键
  FOREIGN KEY (stu_id) REFERENCES student(id) 
  ON DELETE CASCADE -- 级联删除：删除学生时同步删除成绩
  ON UPDATE CASCADE -- 级联更新：学生id修改时同步更新
);

重点注意：在实际的开发过程中，我们是只采用逻辑字段进行外键关联，传统的外键字段对数据库的性能是很拖累的，同样这也符合阿里开发的规范，传统的外键也并不利于阅读和维护。

3. 数据库三大范式（1NF/2NF/3NF）的核心要求？实际开发中是否必须严格遵循？

-- 反范式：订单表冗余用户姓名（避免联表查询）
CREATE TABLE order_info (
  order_id INT PRIMARY KEY,
  user_id INT,
  user_name VARCHAR(30), -- 冗余字段（反3NF）
  order_amount DECIMAL(10,2)
);

二、SQL 实操类（手写必考）

1. COUNT (*)、COUNT (1)、COUNT (列名) 的区别及性能差异？

-- 准备测试数据
INSERT INTO student (stu_no, name) VALUES 
('2024001', '张三'),
('2024002', '李四'),
('2024003', NULL); -- 姓名为NULL

-- 执行统计（对比结果）
SELECT COUNT(*) FROM student; -- 结果：3
SELECT COUNT(1) FROM student; -- 结果：3
SELECT COUNT(name) FROM student; -- 结果：2（忽略NULL）

2. WHERE 和 HAVING 的核心区别？分别适用于什么场景？

-- 准备成绩数据
INSERT INTO score (stu_id, score) VALUES 
(1, 85), (1, 90), (2, 60), (2, 75), (3, 50);

-- 需求1：查询分数≥60的学生的平均分（WHERE过滤）
SELECT stu_id, AVG(score) AS avg_score
FROM score
WHERE score >= 60 -- 分组前过滤掉分数<60的记录
GROUP BY stu_id;
-- 结果：stu_id=1（87.5）、stu_id=2（67.5）

-- 需求2：查询平均分≥70的学生（HAVING过滤）
SELECT stu_id, AVG(score) AS avg_score
FROM score
GROUP BY stu_id
HAVING AVG(score) >= 70; -- 分组后过滤平均分<70的组
-- 结果：stu_id=1（87.5）

3. 内连接、左连接、右连接的区别？用 SQL 举例实现

-- 先插入测试数据（学生+成绩）
INSERT INTO student (stu_no, name) VALUES 
('2024001', '张三'), ('2024002', '李四'), ('2024003', '王五');
INSERT INTO score (stu_id, score) VALUES (1, 85), (2, 90); -- 王五无成绩

-- 1. 内连接：只返回有成绩的学生
SELECT s.name, sc.score
FROM student s
INNER JOIN score sc ON s.id = sc.stu_id;
-- 结果：张三(85)、李四(90)

-- 2. 左连接：返回所有学生，无成绩则score为NULL
SELECT s.name, sc.score
FROM student s
LEFT JOIN score sc ON s.id = sc.stu_id;
-- 结果：张三(85)、李四(90)、王五(NULL)

-- 3. 右连接：返回所有成绩记录，无对应学生则name为NULL
SELECT s.name, sc.score
FROM student s
RIGHT JOIN score sc ON s.id = sc.stu_id;
-- 结果：张三(85)、李四(90)（与内连接结果一致，因成绩表无孤立记录）

4. MySQL 和 Oracle 分页查询的差异？（查询第 11-20 条数据）

-- 准备大量测试数据（插入20条学生记录）
INSERT INTO student (stu_no, name) VALUES 
('2024001', '张三'), ('2024002', '李四'), ('2024003', '王五'),
('2024004', '赵六'), ('2024005', '孙七'), ('2024006', '周八'),
('2024007', '吴九'), ('2024008', '郑十'), ('2024009', '钱十一'),
('2024010', '孙十二'), ('2024011', '李十三'), ('2024012', '张十四'),
('2024013', '王十五'), ('2024014', '刘十六'), ('2024015', '陈十七'),
('2024016', '杨十八'), ('2024017', '黄十九'), ('2024018', '周二十'),
('2024019', '吴二一'), ('2024020', '郑二二');

-- MySQL分页：查询第11-20条（偏移量=10，条数=10）
SELECT * FROM student LIMIT 10, 10;

-- Oracle分页：查询第11-20条
SELECT * FROM (
  SELECT t.*, ROWNUM rn FROM student t WHERE ROWNUM <= 20
) WHERE rn >= 11;

三、高频手写 SQL 场景题

需求：统计每个学生的总分、平均分，且只显示平均分≥70 的学生，按总分降序排列

SELECT 
  s.id, 
  s.name, 
  SUM(sc.score) AS total_score, 
  ROUND(AVG(sc.score), 2) AS avg_score -- 平均分保留2位小数
FROM student s
LEFT JOIN score sc ON s.id = sc.stu_id
GROUP BY s.id, s.name -- 分组字段需包含SELECT中非聚合字段
HAVING AVG(sc.score) >= 70 -- 过滤平均分≥70的学生
ORDER BY total_score DESC; -- 按总分降序