MySQL面试题

发表于 更新于 分类于 本文字数： 2k 阅读时长 ≈ 7 分钟
MySQL面试题，精简于小林coding，仅供学习参考, 持续更新中...

(总结于小林coding，缩减了部分内容，仅供学习参考，正在更新中)

SQL基础

NOSQL和SQL的区别

SQL数据库，指关系型数据库，主要代表：SQL Server、Oracle、MySQL(开源)、PostgreSQL(开源)

关系型数据库存储结构化数据，这些数据逻辑上以行列二维表格的形式存在，每一列代表数据的一个属性，每一行代表一个数据实体

NoSQL指非关系型数据库，主要代表：MongoDB、Redis

NoSQL数据库逻辑上提供了不同于二维表的存储方式，存储方式可以使JSON文档、哈希表或者其他

选择SQL还是NoSQL,考虑一下因素：

ACID vs BASE

关系型数据库支持ACID，即原子性、一致性、隔离性、持久性；相对而言，NoSQL采用更加宽松的模型BASE，即基本可用、软状态、最终一致性

从实用的角度出发，我们需要考虑对于面对的应用场景，ACID是否是必须的。

比如银行应用就必须保证 ACID，否则一笔钱可能被使用两次；
又比如社交软件不必保证ACID，因为一条状态的更新对于所有用户读取先后时间有数秒不同并不影响使用

对于需要保证ACID的应用，我们可以优先考虑SQL。反之则可以优先考虑NoSQL

扩展性对比

NoSQL数据之间无关系，这样就非常容易扩展，也无形之间，在架构的层面上带来了可扩展的能力。比如 redis 自带主从复制模式、 哨兵模式、切片集群模式.

相反关系型数据库的数据之间存在关联性，水平扩展较难，需要解决跨服务器JOIN，分布式事务等问 题。

数据库三大范式是什么

第一范式(1NF)：

要求数据库表的每一列都是不可分割的原子数据项

例：家庭信息：5口人，北京—应该为—家庭人口数：5口人 & 家庭住址：北京

第二范式(2NF)：

在1NF的基础上，非码属性必须完全依赖于候码(在1NF的基础上消除非主属性对主码的部分函数依赖)

第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。

主码：主码是从候选码中选择的一个属性或属性组，用于唯一标识元组。若候选码仅有一个，则它自动成为主码
候码：候选码是能够唯一标识关系中每个元组（记录）的最小属性或属性组。这里的“最小”指候选码的真子集不能成为候选码

例：产品数量、产品折扣、产品价格与”订单号”和”产品号”都相关，但是订单金额和订单时间仅与”订单号”相关，与”产品号”无关， 这样就不满足第二范式的要求，需分成两个表

第三范式（3NF）：

在2NF基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依
赖)

第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。

例：表中所有属性都完全依赖于学号，所以满足第二范式，但是”班主任性别”和”班主任年龄”直接依赖的是”班主任姓名”, 而不是主键”学号”，需分成两个表

MySQL怎么联表查询

数据库有四种联表查询类型：

  1. 内连接(INNER JOIN)
  2. 左外连接(LEFT JOIN)
  3. 右外连接(RIGHT JOIN)
  4. 全外连接(FULL JOIN)
  1. 内连接(INNER JOIN)：
    • 返回两个表中有匹配关系的行
1
2
3
4
SELECT employees.name, departments.name
FROM employees
INNER JOIN departments
ON employees.department_id = departments.id;

这个查询返回每个员工及其所在部门的名字

  1. 左外连接(LEFT JOIN)
    • 返回左表中的所有行，即使在右表中没有匹配的行。未匹配的右表列会包含NULL
1
2
3
4
SELECT employees.name, departments.name
 FROM employees
LEFT JoIN departments
ON employees.department_id = departments.id;

这个查询返回每个员工及其所在部门的名字，包括没有分配部门的员工

  1. 右外连接(RIGHT JOIN)
    • 返回右表中的所有行，即使在左表中没有匹配的行。未匹配的左表列会包含NULL
1
2
3
4
SELECT employees.name, departments.name
 FROM employees
RIGHT JOIN departments
ON employees.department_id = departments.id;

这个查询返回每个员工及其所在部门的名字，包括没有分配员工的部门

  1. 全外连接(FULL JOIN)
    • 返回两个表中的所有行，包括非匹配行。在MySQL中，FULL JOIN 需要使用 UNION来实现，因为MySQL 不支持FULL JOIN
1
2
3
4
5
6
7
8
9
10
11
SELECT employees.name, departments.name
 FROM employees
LEFT JoIN departments
ON employees.department_id = departments.id
 
UNION

SELECT employees.name, departments.name
FROM employees
RIGHT JOIN departments
ON employees.department_id = departments.id;

这个查询返回所有员工和所有部门，包括没有匹配行的记录

MySQL如何避免重复插入数据

  1. 使用UNIQUE约束

在表的相关列添加上UNIQUE约束，确保每个值在该列中唯一

1
2
3
4
5
CREATE TABLE users(
    id    INT PRIMARY KEY AUTO_INCREMENT,
    emai1 VARCHAR(255) UNIQUE,
    name  VARCHAR(255)
)

如果尝试插入重复的email，MySQL会返回错误

  1. 使用INSERT … ON DUPLICATE KEY UPDATE

这中语句允许在插入记录时处理重复键的情况。如果插入记录与现有记录有冲突，可以自己选择更新现有记录

1
2
3
INSERT INTO users (email, name)
VALUES ('example@example.com', 'John Doe')
ON DUPLICATE KEY UPDATE name = VALUES(name);
  1. 使用INSERT IGNORE

该语句会在插入记录时忽略那些因为重复键而发生的错误

1
2
INSERT IGNORE INTO users (email, name)
VALUES ('example@example.com', 'John Doe');

如果email已经存在，这条插入语句将被忽略而不会返回错误

选择哪种方法取决于具体的需求：

  • 如果需要保证全局唯一性，使用UNIQUE约束是最佳做法
  • 如果需要插入和更新结合可以使用ON DUPLICATE KEY UPDATE
  • 对于快速忽略重复插入，INSERT IGNORE是合适的选择

CHAR 和 VARCHAR有什么区别

  • CHAR是固定长度的字符串类型，定义时需要指定固定长度，存储时会在末尾补足空格。
    CHAR适合存储长度固定的数据，如固定长度的代码、状态等，存储空间固定，对于短字符串效率较高。
  • VARCHAR是可变长度的字符串类型，定义时需要指定最大长度，实际存储时根据实际长度占用存储空间。
    VARCHAR适合存储长度可变的数据，如用户输入的文本、备注等，节约存储空间。

Text数据类型可以无限大吗?

MySQL 3种text类型的最大长度如下:
TEXT: 65,535bytes ~ 64kb
MEDIUMTEXT: 16,777,215bytes ~ 16Mb
LONGTEXT: 4,294,967,295bytes ~ 4Gb

说一下外键约束

外键约束的作用是维护表与表之间的关系，确保数据的完整性和一致性

MySQL的关键字in和exist

在MySQL中，IN和EXISTS都是用来处理子查询的关键词，但它们在功能、性能和使用场景上有各自的特点和区别

  • IN
    IN用于检查左边的表达式是否存在于右边的列表或子查询的结果集中。如果存在，则IN返回TRUE，否则返回FALSE。

语法结构：

1
2
3
SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);

或

1
2
3
SELECT column_name(s)
FROM table_name
WHERE column_name IN (SELECT column_name FROM another_table WHERE condition);
  • EXISTS
    EXISTS用于判断子查询是否至少能返回一行数据。它不关心子查询返回什么数据，只关心是否有结果。如果子查询有结果，则EXISTS返回TRUE否则返回FALSE

语法结构：

1
2
3
SELECT column_name(s)
FROM table_name
WHERE EXISTS (SELECT column_name FROM another_table WHERE condition);

或

1
2
SELECT * FROM Customers
WHERE EXISTS (SELECT 1 FROM Orders WHERE Orders.CustomerID = Customers.CustomerID);