CREATE TABLE

CREATE TABLE [IF NOT EXISTS] table_name (
  column_name data_type [constraints],
  ...
  [INDEX | PRIMARY KEY | UNIQUE | FOREIGN KEY]
) [ENGINE=InnoDB] [DEFAULT CHARSET=utf8mb4];

CREATE TABLE IF NOT EXISTS users (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50) NOT NULL UNIQUE COMMENT '用户名',
  email VARCHAR(100) NOT NULL UNIQUE COMMENT '邮箱',
  password_hash VARCHAR(255) NOT NULL COMMENT '密码哈希',
  status TINYINT DEFAULT 1 COMMENT '0:禁用 1:启用',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_email (email),
  INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

CREATE TABLE orders (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  order_no VARCHAR(32) NOT NULL UNIQUE COMMENT '订单号',
  user_id BIGINT UNSIGNED NOT NULL COMMENT '用户ID',
  total_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  status TINYINT DEFAULT 0 COMMENT '0:待付款 1:已付款 2:已发货 3:已完成',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE RESTRICT,
  INDEX idx_user_id (user_id),
  INDEX idx_status_created (status, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

DESC users;
SHOW CREATE TABLE users;

CREATE TEMPORARY TABLE tmp_active_users AS
  SELECT id, username FROM users WHERE status = 1;