当前位置：首页 > news >正文

MySQL基础面试题集锦

news 2025/5/15 2:44:12

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_spentFROM users uJOIN orders o ON u.id = o.user_idGROUP 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)
BEGINSELECT o.* FROM orders oWHERE o.user_id = user_idORDER BY o.order_date DESC;
END //
DELIMITER ;-- 调用存储过程
CALL get_user_orders(1);