温佳鑫

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  email VARCHAR(100) UNIQUE NOT NULL,
  age INT DEFAULT 0,
  status TINYINT DEFAULT 1 COMMENT '1:active 0:inactive',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

DESCRIBE users;                            -- 查看表结构
SHOW CREATE TABLE users;                   -- 查看建表语句

ALTER TABLE users ADD COLUMN phone VARCHAR(20);
ALTER TABLE users MODIFY COLUMN age TINYINT;
ALTER TABLE users DROP COLUMN phone;

DROP TABLE users;                          -- 删除表
TRUNCATE TABLE users;                      -- 清空表（重置自增 ID）

-- INSERT
INSERT INTO users (name, email, age) VALUES ('Alice', 'alice@example.com', 25);
INSERT INTO users (name, email, age) VALUES
  ('Bob', 'bob@example.com', 30),
  ('Charlie', 'charlie@example.com', 28);

INSERT INTO users SET name='David', email='david@example.com';

-- SELECT
SELECT * FROM users;
SELECT name, email FROM users WHERE age > 25;
SELECT * FROM users WHERE name LIKE 'A%';
SELECT * FROM users WHERE email IN ('alice@ex.com', 'bob@ex.com');
SELECT * FROM users WHERE age BETWEEN 20 AND 30;
SELECT * FROM users ORDER BY age DESC, created_at ASC LIMIT 10 OFFSET 0;

-- 聚合
SELECT COUNT(*), AVG(age), MAX(age), MIN(age) FROM users;
SELECT status, COUNT(*) FROM users GROUP BY status;
SELECT age, COUNT(*) FROM users GROUP BY age HAVING COUNT(*) > 1;

-- UPDATE
UPDATE users SET age = 26 WHERE name = 'Alice';
UPDATE users SET age = age + 1 WHERE id = 1;

-- DELETE
DELETE FROM users WHERE id = 3;
DELETE FROM users WHERE age < 18;

CREATE TABLE orders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  product VARCHAR(100),
  amount DECIMAL(10, 2),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

-- INNER JOIN：两表都有的数据
SELECT u.name, o.product, o.amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- LEFT JOIN：左表全保留，右表没有的为 NULL
SELECT u.name, o.product
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

-- 统计每个用户的订单数
SELECT u.name, COUNT(o.id) AS order_count, 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.product, oi.quantity
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN order_items oi ON o.id = oi.order_id;

-- 创建索引
CREATE INDEX idx_email ON users(email);                    -- 单列索引
CREATE UNIQUE INDEX idx_email ON users(email);             -- 唯一索引
CREATE INDEX idx_name_age ON users(name, age);             -- 复合索引
CREATE FULLTEXT INDEX idx_content ON articles(content);    -- 全文索引（MyISAM / InnoDB 5.6+）

-- 查看索引
SHOW INDEX FROM users;

-- 删除索引
DROP INDEX idx_email ON users;

-- 复合索引 (a, b, c)
WHERE a = 1                        -- ✅ 用到索引
WHERE a = 1 AND b = 2              -- ✅ 用到索引
WHERE a = 1 AND b = 2 AND c = 3    -- ✅ 用到索引
WHERE b = 2                        -- ❌ 无法用到索引
WHERE a = 1 AND c = 3              -- 只能用到 a 部分的索引

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

START TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

COMMIT;     -- 提交
-- ROLLBACK; -- 回滚

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;      -- Oracle 默认
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;     -- MySQL 默认
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

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

-- EXISTS（通常比 IN 快）
SELECT * FROM users u WHERE EXISTS (
  SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.amount > 100
);

-- 窗口函数（MySQL 8.0+）
SELECT
  name,
  age,
  ROW_NUMBER() OVER (ORDER BY age DESC) AS rank,
  RANK() OVER (ORDER BY age DESC) AS rank_with_gap,
  DENSE_RANK() OVER (ORDER BY age DESC) AS dense_rank,
  LAG(age, 1) OVER (ORDER BY age) AS prev_age,
  LEAD(age, 1) OVER (ORDER BY age) AS next_age
FROM users;

-- UNION（合并结果集）
SELECT name, email FROM users
UNION
SELECT name, email FROM deleted_users;

-- CASE WHEN
SELECT
  name,
  CASE
    WHEN age < 18 THEN '未成年'
    WHEN age < 60 THEN '成年'
    ELSE '老年'
  END AS age_group
FROM users;

SHOW ENGINES;
SHOW TABLE STATUS WHERE Name = 'users';