product.sql

来自「product 第16章 产品进销存管理系统所需的数据库和程序源代码。」· SQL 代码 · 共 81 行

drop database if exists product;
create database product;
use product;

CREATE TABLE user (
   id int(3) NOT NULL auto_increment,
   name varchar(20) not null,
   password varchar(50) NOT NULL,
   tel varchar(20),
   regtime datetime not null,
   PRIMARY KEY (id)
);


CREATE TABLE company (
   id int(6) not null auto_increment,
   name varchar(100) NOT NULL,
   address varchar(255) NOT NULL,
   tel varchar(100) NOT NULL,
   product varchar(255),
   linkman varchar(100) NOT NULL,
   addtime datetime NOT NULL,
   PRIMARY KEY (id)
);

 
create table product (
   productid int(6) not null auto_increment,
   companyid int(6) not null,
   name varchar(50) not null,
   brand varchar(50) not null,
   type varchar(50) not null,
   introduction text,
   other text,
   primary key (productid)
);

CREATE TABLE buylog(
   id int(6) NOT NULL auto_increment,
   user varchar(20) not null,
   productid int(6) not null,
   amount int(6) not null,
   buyprice decimal(9,2) DEFAULT '0.00' NOT NULL,
   cost decimal(9,2) DEFAULT '0.00' NOT NULL,
   ip varchar(20),
   buytime datetime NOT NULL,
   other text,
   primary KEY (id)
);


create table stock (
   productid int(6) not null,
   amount int(6) not null,
   buyprice decimal(9,2) DEFAULT '0.00' NOT NULL,
   sellprice decimal(9,2) DEFAULT '0.00' NOT NULL,
   cost decimal(9,2) DEFAULT '0.00' NOT NULL,
   introduction text,
   buytime datetime not null,
   other text,
   primary key(productid, buyprice)
);


CREATE TABLE selllog(
   id int(10) NOT NULL auto_increment,
   user varchar(20) not null,
   productid int(6) not null,
   amount int(6) not null,
   buyprice decimal(9,2) DEFAULT '0.00' NOT NULL,
   sellprice decimal(9,2) DEFAULT '0.00' NOT NULL,
   cost decimal(9,2) DEFAULT '0.00' NOT NULL,
   sellmoney decimal(9,2) DEFAULT '0.00' NOT NULL,
   ip varchar(20),
   buytime datetime NOT NULL,
   selltime datetime not null,
   other text,
   primary KEY (id)
);