MySQL基础面试题集锦-CSDN博客

本文链接：https://blog.csdn.net/weixin_46261399/article/details/147881285

MySQL基础面试题集锦

一、SQL基础语法

1. 数据库和表操作

-- 创建数据库
CREATE DATABASE test_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 创建表
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_active BOOLEAN DEFAULT TRUE
);

-- 修改表结构
ALTER TABLE users ADD COLUMN last_login DATETIME AFTER email;

2. 基本CRUD操作

-- 插入数据
INSERT INTO users (username, email) VALUES ('john_doe', 'john@example.com');

-- 查询数据
SELECT * FROM users WHERE is_active = TRUE ORDER BY created_at DESC LIMIT 10;

-- 更新数据
UPDATE users SET last_login = NOW() WHERE id = 1;

-- 删除数据
DELETE FROM users WHERE is_active = FALSE AND created_at < DATE_SUB(NOW(), INTERVAL 1 YEAR);

二、查询进阶

1. 多表连接查询

-- 内连接
SELECT o.order_id, u.username, o.order_date 
FROM orders o
JOIN users u ON o.user_id = u.id;

-- 左外连接
SELECT u.username, COUNT(o.order_id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.username;

2. 子查询

-- 找出消费金额高于平均值的用户
SELECT username, total_spent
FROM (
    SELECT u.username, SUM(o.amount) AS total_spent
    FROM users u
    JOIN orders o ON u.id = o.user_id
    GROUP BY u.username
) user_totals
WHERE total_spent > (SELECT AVG(amount) FROM orders);

三、聚合函数与分组

-- 常用聚合函数
SELECT 
    COUNT(*) AS total_users,
    MAX(created_at) AS newest_user,
    MIN(created_at) AS oldest_user
FROM users;

-- 分组统计
SELECT 
    DATE(created_at) AS signup_date,
    COUNT(*) AS daily_signups,
    SUM(CASE WHEN is_active THEN 1 ELSE 0 END) AS active_users
FROM users
GROUP BY DATE(created_at)
HAVING COUNT(*) > 5;

四、索引与性能

1. 索引操作

-- 创建索引
CREATE INDEX idx_email ON users(email);
CREATE UNIQUE INDEX idx_username ON users(username);

-- 复合索引
CREATE INDEX idx_user_status ON users(is_active, created_at);

-- 查看索引
SHOW INDEX FROM users;

2. 执行计划分析

-- 查看查询执行计划
EXPLAIN SELECT * FROM users WHERE username = 'john_doe';

-- 强制使用索引
SELECT * FROM users FORCE INDEX (idx_username) WHERE username LIKE 'j%';

五、事务与锁

1. 事务控制

-- 事务示例
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;
COMMIT;
-- 出错时 ROLLBACK;

2. 锁机制

-- 悲观锁
SELECT * FROM products WHERE id = 1 FOR UPDATE;

-- 乐观锁（通过版本号）
UPDATE products 
SET stock = stock - 1, version = version + 1
WHERE id = 1 AND version = 5;

六、常见面试问题与解答

1. 基础概念题

Q: CHAR和VARCHAR有什么区别？

-- CHAR是固定长度，VARCHAR是可变长度
CREATE TABLE example (
    fixed CHAR(10),    -- 总是占用10个字符空间
    variable VARCHAR(10)  -- 根据实际内容占用空间
);

Q: MySQL中JOIN有哪些类型？

-- INNER JOIN: 只返回匹配的行
SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.id;

-- LEFT JOIN: 返回左表所有行，右表无匹配则为NULL
SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id;

-- RIGHT JOIN: 返回右表所有行，左表无匹配则为NULL
SELECT * FROM table1 RIGHT JOIN table2 ON table1.id = table2.id;

-- FULL OUTER JOIN: MySQL不直接支持，可通过UNION实现
SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id
UNION
SELECT * FROM table1 RIGHT JOIN table2 ON table1.id = table2.id WHERE table1.id IS NULL;

2. 性能优化题

Q: 如何优化慢查询？

-- 1. 找出慢查询
SELECT * FROM mysql.slow_log ORDER BY query_time DESC LIMIT 10;

-- 2. 使用EXPLAIN分析
EXPLAIN SELECT * FROM large_table WHERE unindexed_column = 'value';

-- 3. 添加适当索引
ALTER TABLE large_table ADD INDEX idx_unindexed (unindexed_column);

-- 4. 重写复杂查询
-- 优化前
SELECT * FROM orders WHERE user_id IN (
    SELECT id FROM users WHERE created_at > '2023-01-01'
);

-- 优化后
SELECT o.* FROM orders o
JOIN users u ON o.user_id = u.id
WHERE u.created_at > '2023-01-01';

3. 实际问题解决

Q: 如何实现分页查询？

-- 基本分页
SELECT * FROM products ORDER BY id LIMIT 10 OFFSET 20;  -- 第3页，每页10条

-- 高性能分页（对于大表）
SELECT * FROM products WHERE id > 1000 ORDER BY id LIMIT 10;  -- 记住上一页最后ID

Q: 如何处理重复数据？

-- 查找重复记录
SELECT email, COUNT(*) 
FROM users 
GROUP BY email 
HAVING COUNT(*) > 1;

-- 删除重复记录（保留ID最小的）
DELETE u1 FROM users u1
INNER JOIN users u2 
WHERE u1.id > u2.id AND u1.email = u2.email;

七、视图与存储过程

1. 视图

-- 创建视图
CREATE VIEW active_users AS
SELECT id, username, email 
FROM users 
WHERE is_active = TRUE;

-- 使用视图
SELECT * FROM active_users ORDER BY username;

2. 存储过程

DELIMITER //
CREATE PROCEDURE get_user_orders(IN user_id INT)
BEGIN
    SELECT o.* FROM orders o
    WHERE o.user_id = user_id
    ORDER BY o.order_date DESC;
END //
DELIMITER ;

-- 调用存储过程
CALL get_user_orders(1);