MySQL

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

INSERT INTO users (name, email, created_at)
VALUES ('vic', 'vic@example.com', NOW());

INSERT INTO users (name, email) VALUES
('a', 'a@example.com'),
('b', 'b@example.com'),
('c', 'c@example.com');

UPDATE users
SET name = 'vic', updated_at = NOW()
WHERE id = 1;

DELETE FROM users WHERE id = 1;

-- 软删除
UPDATE users SET deleted_at = NOW() WHERE id = 1;

INSERT INTO users (id, name, email)
VALUES (1, 'vic', 'vic@example.com')
ON DUPLICATE KEY UPDATE
  name = VALUES(name),
  email = VALUES(email);

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

SELECT u.id, u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.name;

SELECT user_id, SUM(amount) AS total
FROM orders
GROUP BY user_id
HAVING total > 1000
ORDER BY total DESC;

SELECT *
FROM users
WHERE id IN (
  SELECT user_id FROM orders WHERE amount > 100
);

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

CREATE TABLE users (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(64) NOT NULL DEFAULT '',
  email VARCHAR(128) NOT NULL DEFAULT '',
  status TINYINT NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at DATETIME NULL DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uk_email (email),
  KEY idx_status_created (status, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE users ADD COLUMN phone VARCHAR(20) NOT NULL DEFAULT '' AFTER email;
ALTER TABLE users MODIFY COLUMN name VARCHAR(128) NOT NULL DEFAULT '';
ALTER TABLE users DROP COLUMN phone;
ALTER TABLE users ADD KEY idx_phone (phone);

CREATE INDEX idx_name ON users (name);
CREATE UNIQUE INDEX uk_email ON users (email);
DROP INDEX idx_name ON users;
SHOW INDEX FROM users;

SHOW DATABASES;
SHOW TABLES;
SHOW CREATE TABLE users;
DESC users;
SHOW PROCESSLIST;

EXPLAIN SELECT * FROM users WHERE email = 'vic@example.com';
EXPLAIN ANALYZE SELECT * FROM users WHERE status = 1;

START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
-- ROLLBACK;

SELECT * FROM users WHERE name LIKE '%vic%';
SELECT * FROM users WHERE name LIKE 'vic%'; -- 可用索引前缀

SELECT * FROM users WHERE created_at >= CURDATE();
SELECT * FROM users WHERE created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY);
SELECT DATE_FORMAT(created_at, '%Y-%m-%d') AS day, COUNT(*) AS cnt
FROM users
GROUP BY day;