表结构文档

-- 示例：在线书店系统 数据库设计（演示用）

CREATE TABLE `user` (
  id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '用户ID',
  username VARCHAR(50) NOT NULL COMMENT '用户名',
  password VARCHAR(255) NOT NULL COMMENT '密码',
  email VARCHAR(100) DEFAULT NULL COMMENT '邮箱',
  user_type VARCHAR(20) NOT NULL DEFAULT 'customer' COMMENT '用户类型',
  create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

CREATE TABLE `customer` (
  id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '客户ID',
  user_id BIGINT NOT NULL COMMENT '用户ID',
  level INT NOT NULL DEFAULT 1 COMMENT '会员等级',
  balance DECIMAL(12,2) NOT NULL DEFAULT 0 COMMENT '账户余额',
  phone VARCHAR(20) DEFAULT NULL COMMENT '手机号'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='客户表';

CREATE TABLE `admin` (
  id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '管理员ID',
  user_id BIGINT NOT NULL COMMENT '用户ID',
  role VARCHAR(20) NOT NULL DEFAULT 'admin' COMMENT '角色',
  department VARCHAR(100) DEFAULT NULL COMMENT '部门'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='管理员表';

CREATE TABLE `address` (
  id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '地址ID',
  customer_id BIGINT NOT NULL COMMENT '客户ID',
  province VARCHAR(50) NOT NULL COMMENT '省份',
  city VARCHAR(50) NOT NULL COMMENT '城市',
  detail VARCHAR(255) NOT NULL COMMENT '详细地址'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='收货地址表';

CREATE TABLE `category` (
  id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '分类ID',
  name VARCHAR(100) NOT NULL COMMENT '分类名称',
  description VARCHAR(255) DEFAULT NULL COMMENT '分类说明'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='图书分类表';

CREATE TABLE `book` (
  id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '图书ID',
  category_id BIGINT NOT NULL COMMENT '分类ID',
  title VARCHAR(200) NOT NULL COMMENT '书名',
  author VARCHAR(100) NOT NULL COMMENT '作者',
  price DECIMAL(10,2) NOT NULL DEFAULT 0 COMMENT '售价',
  stock INT NOT NULL DEFAULT 0 COMMENT '库存'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='图书表';

CREATE TABLE `order` (
  id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '订单ID',
  customer_id BIGINT NOT NULL COMMENT '客户ID',
  address_id BIGINT NOT NULL COMMENT '地址ID',
  total_amount DECIMAL(12,2) NOT NULL DEFAULT 0 COMMENT '订单金额',
  status VARCHAR(20) NOT NULL DEFAULT 'pending' COMMENT '订单状态',
  create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单表';

CREATE TABLE `order_item` (
  id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '明细ID',
  order_id BIGINT NOT NULL COMMENT '订单ID',
  book_id BIGINT NOT NULL COMMENT '图书ID',
  quantity INT NOT NULL DEFAULT 1 COMMENT '数量',
  unit_price DECIMAL(10,2) NOT NULL DEFAULT 0 COMMENT '单价',
  subtotal DECIMAL(12,2) NOT NULL DEFAULT 0 COMMENT '小计'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单明细表';