毕业设计数据库

CREATE TABLE `admin` (
    `admin_id` INT NOT NULL AUTO_INCREMENT,
    `admin_name` VARCHAR(64) NOT NULL COMMENT '管理员名称',
    `admin_password`  VARCHAR(64) NOT NULL COMMENT '管理员密码',
    `admin_level` INT NOT NULL COMMENT '管理员等级',
    `create_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
    PRIMARY KEY (`admin_id`)
) COMMENT '管理员表';
CREATE TABLE `seller` (
    `seller_id` INT NOT NULL AUTO_INCREMENT,
    `seller_username` VARCHAR(64) NOT NULL COMMENT '商家用户名',
    `seller_password` VARCHAR(64) NOT NULL COMMENT '商家密码',
    `shop_name` VARCHAR(64) NOT NULL COMMENT '商家名称',
    `shop_phone` VARCHAR(64) NOT NULL COMMENT '商家电话',
    `shop_address` VARCHAR(64) NOT NULL COMMENT '商家地址',
    `create_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
    PRIMARY KEY (`seller_id`)
) COMMENT '商家表';
CREATE TABLE `buyer` (
    `buyer_id` INT NOT NULL AUTO_INCREMENT,
    `buyer_username` VARCHAR(64) NOT NULL COMMENT '买家用户名',
    `buyer_password` VARCHAR(64) NOT NULL COMMENT '买家密码',
    `buyer_phone` VARCHAR(64) NOT NULL COMMENT '买家电话',
    `buyer_address` VARCHAR(64) NOT NULL COMMENT '买家地址',
    `create_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
    PRIMARY KEY (`buyer_id`)
) COMMENT '买家表';
CREATE TABLE `product_cate` (
  `category_id` int(11) NOT NULL AUTO_INCREMENT,
  `category_name` varchar(64) NOT NULL COMMENT '类目名字',
  `seller_id` int(11) NOT NULL COMMENT '商家编号',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  PRIMARY KEY (`category_id`),
  KEY `line` (`seller_id`),
  CONSTRAINT `line` FOREIGN KEY (`seller_id`) REFERENCES `seller` (`seller_id`) ON DELETE CASCADE ON UPDATE CASCADE
) COMMENT '类目表';
CREATE TABLE `product_info` (
  `product_id` int(11) NOT NULL AUTO_INCREMENT,
  `product_name` varchar(64) NOT NULL COMMENT '商品名称',
  `product_price` decimal(8,2) NOT NULL COMMENT '单价',
  `product_stock` int(11) NOT NULL COMMENT '库存',
  `product_description` varchar(64) DEFAULT NULL COMMENT '描述',
  `product_icon` varchar(512) DEFAULT NULL COMMENT '小图',
  `category_id` int(11) NOT NULL DEFAULT '0' COMMENT '类目编号',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  PRIMARY KEY (`product_id`),
  KEY `info` (`category_id`),
  CONSTRAINT `info` FOREIGN KEY (`category_id`) REFERENCES `product_cate` (`category_id`) ON DELETE CASCADE ON UPDATE CASCADE
) COMMENT '商品表';
CREATE TABLE `order_master` (
  `order_id` varchar(32) NOT NULL,
  `buyer_name` varchar(32) NOT NULL COMMENT '买家名字',
  `buyer_phone` varchar(32) NOT NULL COMMENT '买家电话',
  `buyer_address` varchar(128) NOT NULL COMMENT '买家地址',
  `order_amount` decimal(8,2) NOT NULL COMMENT '订单总金额',
  `pay_status` tinyint(3) NOT NULL DEFAULT '0' COMMENT '支付状态,默认0未支付',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  PRIMARY KEY (`order_id`)
) COMMENT '订单表';
CREATE TABLE `order_detail` (
  `detail_id` varchar(32) NOT NULL,
  `order_id` varchar(32) NOT NULL,
  `product_id` varchar(32) NOT NULL,
  `product_name` varchar(64) NOT NULL COMMENT '商品名称',
  `product_price` decimal(8,2) NOT NULL COMMENT '商品价格',
  `product_quantity` int(11) NOT NULL COMMENT '商品数量',
  `product_icon` varchar(512) DEFAULT NULL COMMENT '商品小图',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  PRIMARY KEY (`detail_id`),
  KEY `idx_order_id` (`order_id`)
) COMMENT='订单详情表';