跳到主要内容

MySQL 基础与查询

问题​

MySQL 的基本 CRUD 操作有哪些？JOIN、子查询怎么用？

答案​

CRUD 基础​

crud.sql
-- 创建表
CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL,
  age INT DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 插入
INSERT INTO users (name, email, age) VALUES ('Alice', 'alice@example.com', 25);

-- 查询
SELECT name, email FROM users WHERE age > 18 ORDER BY created_at DESC LIMIT 10;

-- 更新
UPDATE users SET age = 26 WHERE id = 1;

-- 删除
DELETE FROM users WHERE id = 1;

JOIN 类型​

join.sql
-- 订单表
CREATE TABLE orders (
  id INT PRIMARY KEY AUTO_INCREMENT,
  user_id INT NOT NULL,
  amount DECIMAL(10,2),
  FOREIGN KEY (user_id) REFERENCES users(id)
);

-- INNER JOIN：只返回两表都有匹配的行
SELECT u.name, o.amount
FROM users u INNER JOIN orders o ON u.id = o.user_id;

-- LEFT JOIN：返回左表所有行（没有订单的用户也返回）
SELECT u.name, COALESCE(SUM(o.amount), 0) AS total
FROM users u LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id;

-- 多表 JOIN
SELECT u.name, o.amount, p.name AS product
FROM users u
  JOIN orders o ON u.id = o.user_id
  JOIN order_items oi ON o.id = oi.order_id
  JOIN products p ON oi.product_id = p.id;

常用函数​

functions.sql
-- 聚合函数
SELECT COUNT(*), SUM(amount), AVG(amount), MAX(amount), MIN(amount)
FROM orders;

-- 分组 + HAVING
SELECT user_id, SUM(amount) AS total
FROM orders
GROUP BY user_id
HAVING total > 1000;

-- 字符串函数
SELECT CONCAT(first_name, ' ', last_name) AS full_name,
       LENGTH(name), UPPER(email)
FROM users;

-- 日期函数
SELECT * FROM orders
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY);

-- 子查询
SELECT * FROM users
WHERE id IN (
  SELECT user_id FROM orders WHERE amount > 1000
);

-- EXISTS
SELECT * FROM users u
WHERE EXISTS (
  SELECT 1 FROM orders o WHERE o.user_id = u.id
);

常见面试问题​

Q1: WHERE 和 HAVING 的区别？​

答案：

  • WHERE：在分组前过滤，不能用聚合函数
  • HAVING：在分组后过滤，可以用聚合函数
SELECT user_id, SUM(amount)
FROM orders
WHERE amount > 10          -- 先过滤金额 > 10 的订单
GROUP BY user_id
HAVING SUM(amount) > 1000; -- 再过滤总金额 > 1000 的用户

Q2: UNION 和 UNION ALL 的区别？​

答案：

  • UNION：合并结果并去重（多一次排序操作）
  • UNION ALL：合并结果不去重（性能更好）

Q3: SQL 的执行顺序？​

答案：

FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

这就是为什么 WHERE 不能用 SELECT 中的别名，但 ORDER BY 可以。

Q4: DELETE、TRUNCATE、DROP 的区别？​

答案：

操作DELETETRUNCATEDROP
类型DMLDDLDDL
删除内容数据（可条件删除）全部数据表 + 数据
事务支持回滚不可回滚不可回滚
速度慢（逐行删除）快最快
自增列不重置重置N/A

Q5: 子查询和 JOIN 怎么选？​

答案：

优先使用 JOIN，因为：

  1. MySQL 优化器对 JOIN 优化更好
  2. JOIN 可以使用索引
  3. 子查询可能产生临时表，性能差

相关链接​