MySQL 增删改查

插入

INSERT INTO users (name, email, age)
VALUES ('Tom', 'tom@example.com', 18);

INSERT INTO users (name, email, age)
VALUES
  ('Alice', 'alice@example.com', 20),
  ('Bob', 'bob@example.com', 22);

更新

UPDATE users
SET age = 19
WHERE id = 1;

删除

DELETE FROM users
WHERE id = 1;

UPDATE 和 DELETE 必须先写清楚 WHERE 条件。

查询

SELECT id, name, email
FROM users
WHERE status = 1
ORDER BY id DESC
LIMIT 10 OFFSET 0;

条件、排序和分页

SELECT *
FROM users
WHERE age BETWEEN 18 AND 30
  AND email LIKE '%@example.com'
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 0;

聚合和分组

SELECT status, COUNT(*) AS total
FROM users
GROUP BY status
HAVING COUNT(*) > 0
ORDER BY total DESC;

WHERE 在分组前过滤行，HAVING 在分组后过滤聚合结果。

关联查询

CREATE TABLE orders (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,
  amount DECIMAL(10, 2) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

SELECT u.id, u.name, o.id AS order_id, o.amount
FROM users AS u
INNER JOIN orders AS o ON o.user_id = u.id
WHERE u.id = 1;

常用连接：

• INNER JOIN：只返回两边都匹配的数据。
• LEFT JOIN：保留左表全部数据，右表没有匹配时为 NULL。
• RIGHT JOIN：保留右表全部数据。

MySQL 不支持标准 FULL OUTER JOIN。

事务

START TRANSACTION;

UPDATE users SET age = age + 1 WHERE id = 1;
INSERT INTO orders (user_id, amount) VALUES (1, 99.00);

COMMIT;
-- ROLLBACK;

MySQL InnoDB 默认隔离级别是 REPEATABLE READ。

索引

CREATE INDEX idx_users_status_created_at ON users (status, created_at);
CREATE UNIQUE INDEX uk_users_email ON users (email);
DROP INDEX idx_users_status_created_at ON users;

执行计划

EXPLAIN
SELECT *
FROM users
WHERE email = 'tom@example.com';

重点关注：

• type：访问类型，常见优先级大致为 const、ref、range、index、ALL。
• key：实际使用的索引。
• rows：预估扫描行数。
• Extra：额外信息，如 Using filesort、Using temporary。