MySQL常见面试题

1. 解释MySQL中的主键和外键概念。

答:

• 主键(Primary Key): 用于唯一标识表中的每一行记录。它必须包含唯一值,且不能为空。
• 外键(Foreign Key): 用于建立和加强两个表数据之间的链接的一列或多列。它指向另一个表的主键。

示例:

CREATE TABLE departments (
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR(50)
);

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(50),
    dept_id INT,
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

2. MySQL中的存储引擎是什么?InnoDB和MyISAM有什么区别?

答: 存储引擎是MySQL中用于处理不同类型表的底层软件组件。主要区别:

InnoDB:

• 支持事务
• 支持外键
• 支持行级锁
• 支持崩溃恢复

MyISAM:

• 不支持事务
• 不支持外键
• 只支持表级锁
• 读取速度快,适合读多写少的场景

3. 什么是事务?事务的ACID属性是什么?

答: 事务是一系列数据库操作,要么全部执行成功,要么全部失败回滚。ACID属性:

• 原子性(Atomicity): 事务是不可分割的最小单位,要么全部成功,要么全部失败。
• 一致性(Consistency): 事务开始前和结束后,数据库的完整性约束没有被破坏。
• 隔离性(Isolation): 多个事务并发执行时,一个事务的执行不应影响其他事务的执行。
• 持久性(Durability): 事务完成后,它对数据库的修改应该永久保存。

4. 解释MySQL中的索引。索引的类型有哪些?

答: 索引是一种数据结构,用于加快数据库查询速度。主要类型包括:

• 普通索引: 最基本的索引,没有任何限制。
• 唯一索引: 索引列的值必须唯一,但允许有空值。
• 主键索引: 特殊的唯一索引,不允许有空值。
• 复合索引: 多个列上的索引。
• 全文索引: 用于全文搜索。

示例:

CREATE INDEX idx_name ON table_name(column_name);
CREATE UNIQUE INDEX idx_unique ON table_name(column_name);
ALTER TABLE table_name ADD PRIMARY KEY (column_name);
CREATE INDEX idx_composite ON table_name(column1, column2);
CREATE FULLTEXT INDEX idx_fulltext ON table_name(column_name);

5. 什么是视图?视图的优点是什么?

答: 视图是基于SQL查询的虚拟表。优点包括:

• 简化复杂查询
• 提供额外的安全层
• 启用向后兼容性

示例:

CREATE VIEW employee_details AS
SELECT e.emp_id, e.emp_name, d.dept_name
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id;

6. 解释MySQL中的触发器概念。

答: 触发器是在表上执行特定操作(如INSERT, UPDATE, DELETE)时自动执行的一段SQL代码。

示例:

CREATE TRIGGER before_employee_update
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
    IF NEW.salary < OLD.salary THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Salary cannot be decreased';
    END IF;
END;

7. 什么是存储过程?它有什么优点?

答: 存储过程是一组预编译的SQL语句。优点包括:

• 提高执行效率
• 减少网络流量
• 增强安全性
• 模块化程序设计

示例:

DELIMITER //
CREATE PROCEDURE GetEmployeeCount(OUT total INT)
BEGIN
    SELECT COUNT(*) INTO total FROM employees;
END //
DELIMITER ;

CALL GetEmployeeCount(@total);
SELECT @total;

8. 解释MySQL中的连接(JOIN)类型。

答: MySQL支持以下JOIN类型:

• INNER JOIN: 返回两表中匹配的行
• LEFT JOIN: 返回左表的所有行,即使右表没有匹配
• RIGHT JOIN: 返回右表的所有行,即使左表没有匹配
• FULL JOIN: 返回两表的所有行,MySQL通过LEFT JOIN + UNION + RIGHT JOIN模拟

示例:

SELECT e.emp_name, d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id;

9. 什么是SQL注入?如何防止SQL注入攻击?

答: SQL注入是一种通过在用户输入中插入SQL语句来操纵数据库的技术。防止方法:

• 使用参数化查询
• 验证用户输入
• 限制数据库权限
• 使用存储过程

示例(使用参数化查询):

$stmt = $pdo->prepare("SELECT * FROM users WHERE username = :username AND password = :password");
$stmt->execute(['username' => $username, 'password' => $password]);

当然,我们继续回答剩下的问题:

10. 什么是游标(Cursor)?它在MySQL中如何使用?

答: 游标是一种数据库对象,用于在存储过程中逐行处理查询结果。它允许我们对结果集进行遍历和操作。

示例:

DELIMITER //
CREATE PROCEDURE process_employees()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE emp_name VARCHAR(100);
    DECLARE emp_cursor CURSOR FOR SELECT name FROM employees;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN emp_cursor;
    read_loop: LOOP
        FETCH emp_cursor INTO emp_name;
        IF done THEN
            LEAVE read_loop;
        END IF;
        -- 处理每个员工名字
        SELECT CONCAT('Processing employee: ', emp_name);
    END LOOP;
    CLOSE emp_cursor;
END //
DELIMITER ;

11. 解释MySQL中的范式化(Normalization)概念。

答: 范式化是一种数据库设计技术,用于减少数据冗余和提高数据完整性。主要的范式包括:

• 第一范式(1NF): 每个列都是不可分割的原子值
• 第二范式(2NF): 满足1NF,并且非主键列完全依赖于主键
• 第三范式(3NF): 满足2NF,并且非主键列之间没有传递依赖

12. 什么是死锁(Deadlock)?如何避免死锁?

答: 死锁是指两个或更多事务互相持有对方需要的锁,导致它们都无法继续执行的情况。

避免死锁的方法:

• 以固定的顺序访问表和行
• 保持事务简短并在一个批处理中
• 使用事务隔离级别
• 使用锁超时

13. 解释MySQL中的EXPLAIN命令的作用。

答: EXPLAIN命令用于分析SELECT查询的执行计划。它显示了MySQL如何执行查询,包括使用的索引、表的读取顺序等信息。

示例:

EXPLAIN SELECT * FROM employees WHERE salary > 50000;

14. 什么是MySQL复制(Replication)?它有什么优点?

答: MySQL复制是将数据从一个MySQL数据库服务器(主服务器)复制到一个或多个MySQL数据库服务器(从服务器)的过程。

优点:

• 提高可用性
• 负载均衡
• 备份
• 数据分析

15. 解释MySQL中的事务隔离级别。

答: MySQL支持四种事务隔离级别:

• READ UNCOMMITTED: 最低级别,允许脏读
• READ COMMITTED: 防止脏读,但允许不可重复读
• REPEATABLE READ: 防止脏读和不可重复读,但允许幻读
• SERIALIZABLE: 最高级别,防止所有并发问题

设置隔离级别:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

16. 什么是分区(Partitioning)?它在MySQL中如何使用?

答: 分区是将大表分成更小、更易管理的部分的过程。MySQL支持多种分区类型,如RANGE, LIST, HASH, KEY等。

示例(RANGE分区):

CREATE TABLE sales (
    id INT,
    amount DECIMAL,
    sale_date DATE
)
PARTITION BY RANGE (YEAR(sale_date)) (
    PARTITION p0 VALUES LESS THAN (2020),
    PARTITION p1 VALUES LESS THAN (2021),
    PARTITION p2 VALUES LESS THAN (2022),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);

17. 什么是子查询(Subquery)?它有哪些类型?

答: 子查询是嵌套在另一个查询内的查询。主要类型包括:

• 标量子查询: 返回单个值
• 行子查询: 返回单行
• 列子查询: 返回单列
• 表子查询: 返回多行多列

示例:

SELECT name FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);

18. 什么是MySQL中的临时表?它们有什么用途?

答: 临时表是在会话期间存在的特殊表,会话结束后自动删除。用途包括:

• 存储中间结果
• 简化复杂查询
• 提高查询性能

示例:

CREATE TEMPORARY TABLE temp_high_salary_employees
SELECT * FROM employees WHERE salary > 100000;

19. 解释MySQL中的锁机制。有哪些类型的锁?

答: MySQL使用锁来管理对共享资源的并发访问。主要的锁类型包括:

• 共享锁(读锁): 允许多个事务同时读取一行
• 排他锁(写锁): 防止其他事务读取或写入一行
• 表锁: 锁定整个表
• 行锁: 锁定单个数据行
• 意向锁: 表明事务稍后要在表中的行上设置共享锁或排他锁

示例(显式加锁):

SELECT * FROM employees WHERE id = 1 FOR UPDATE;