进销存管理系统mypos

/*操作员表*/
CREATE TABLE czyb (
  czyid INT AUTO_INCREMENT PRIMARY KEY,
  czydm VARCHAR (10) NOT NULL,
  czykl VARCHAR (10) NOT NULL,
  czylb INT NOT NULL
) ;

-- 商品表
CREATE TABLE spb (
  spid INT AUTO_INCREMENT PRIMARY KEY,
  spmc VARCHAR (100) NOT NULL,
  spmcsx VARCHAR (100),
  sptm VARCHAR (50),
  spdw VARCHAR (10),
  spbz TEXT
) ;

-- 供货商表
CREATE TABLE ghsb (
  gysid INT AUTO_INCREMENT PRIMARY KEY,
  gysmc VARCHAR (100) NOT NULL,
  spmcsx VARCHAR (100),
  gysdz VARCHAR (255),
  gyslxfs VARCHAR (20),
  gysbz TEXT
) ;

-- 客户表
CREATE TABLE khb (
  khid INT AUTO_INCREMENT PRIMARY KEY,
  khmc VARCHAR (100) NOT NULL,
  khmcsx VARCHAR (100),
  khdz VARCHAR (255),
  khlxfs VARCHAR (20),
  khbz TEXT
) ;

-- 仓库表
CREATE TABLE ckb (
  ckid INT AUTO_INCREMENT PRIMARY KEY,
  ckmc VARCHAR (100) NOT NULL,
  ckdz VARCHAR (255)
) ;

-- 进货总表
CREATE TABLE jhzb (
  jhid INT AUTO_INCREMENT PRIMARY KEY,
  gysid INT,
  jhrq DATE NOT NULL,
  jhje DECIMAL (10, 2) NOT NULL,
  czyid INT,
  jhbz TEXT,
  FOREIGN KEY (gysid) REFERENCES ghsb (gysid),
  FOREIGN KEY (czyid) REFERENCES czyb (czyid)
) ;

-- 进货明细表
CREATE TABLE jhmxb (
  jhmxid INT AUTO_INCREMENT PRIMARY KEY,
  jhid INT,
  spid INT,
  jhdj DECIMAL (10, 2) NOT NULL,
  jhsl INT NOT NULL,
  FOREIGN KEY (jhid) REFERENCES jhzb (jhid),
  FOREIGN KEY (spid) REFERENCES spb (spid)
) ;

-- 销售总表
CREATE TABLE xszb (
  xsid INT AUTO_INCREMENT PRIMARY KEY,
  khid INT,
  xsrq DATE NOT NULL,
  xsje DECIMAL (10, 2) NOT NULL,
  czyid INT,
  xsbz TEXT,
  FOREIGN KEY (khid) REFERENCES khb (khid),
  FOREIGN KEY (czyid) REFERENCES czyb (czyid)
) ;

/*-- 销售明细表*/
CREATE TABLE xsmxb (
  xsmxid INT AUTO_INCREMENT PRIMARY KEY,
  xsid INT,
  spid INT,
  xsdj DECIMAL (10, 2) NOT NULL,
  xssl INT NOT NULL,
  FOREIGN KEY (xsid) REFERENCES xszb (xsid),
  FOREIGN KEY (spid) REFERENCES spb (spid)
) ;

-- 库存表
CREATE TABLE kcb (
  kcid INT AUTO_INCREMENT PRIMARY KEY,
  ckid INT,
  spid INT,
  jhpjdj DECIMAL (10, 2),
  xspjdj DECIMAL (10, 2),
  kcsl INT NOT NULL,
  zgkcl INT,
  zdkcl INT,
  FOREIGN KEY (ckid) REFERENCES ckb (ckid),
  FOREIGN KEY (spid) REFERENCES spb (spid)
) ;

-- 客户姓名索引
CREATE INDEX idx_khmc ON khb (khmc) ;

-- 供应商名称索引
CREATE INDEX idx_gysmc 
ON ghsb (gysmc) ;

-- 商品名称索引
CREATE INDEX idx_spmc 
ON spb (spmc) ;

-- 商品名称首拼索引
CREATE INDEX idx_spmcsx 
ON spb (spmcsx) ;

-- 3.建立视图 
-- 进货表视图
CREATE VIEW jhzb_view AS 
SELECT 
  jhzb.*,
  ghsb.gysmc AS gysmc,
  spb.spmc AS spmc 
FROM
  jhzb 
  JOIN ghsb 
    ON jhzb.gysid = ghsb.gysid 
  JOIN spb 
    ON jhmxb.spid = spb.spid ;

-- 销售表视图
CREATE VIEW xszb_view AS 
SELECT 
  xszb.*,
  khb.khmc AS khmc,
  spb.spmc AS spmc 
FROM
  xszb 
  JOIN khb 
    ON xszb.khid = khb.khid 
  JOIN spb 
    ON xsmxb.spid = spb.spid ;

-- 库存视图
CREATE VIEW kcb_view AS 
SELECT 
  kcb.*,
  ckb.ckmc AS ckmc,
  spb.spmc AS spmc 
FROM
  kcb 
  JOIN ckb 
    ON kcb.ckid = ckb.ckid 
  JOIN spb 
    ON kcb.spid = spb.spid ;

-- 4. 插入记录：
-- 插入操作员表
INSERT INTO czyb (czydm, czykl, czylb) 
VALUES
  ('000', '000', 0),
  ('001', '001', 1) ;

-- 插入商品表
INSERT INTO spb (spmc, spmcsx, sptm, spdw) 
VALUES
  (
    '可口可乐',
    'kkkl',
    '101',
    '瓶'
  ),
  (
    '百事可乐',
    'bskl',
    '102',
    '瓶'
  ),
  (
    '伊利牛奶',
    'ylnn',
    '103',
    '盒'
  ),
  (
    '农夫山泉',
    'nfsq',
    '104',
    '瓶'
  ),
  (
    '涪陵榨菜',
    'flzc',
    '105',
    '袋'
  ),
  (
    '青岛啤酒',
    'dqpj',
    '106',
    '听'
  ) ;

-- 插入供应商表
INSERT INTO ghsb (gysmc, spmcsx, gysdz, gyslxfs) 
VALUES
  (
    '洛阳宏远商贸有限公司',
    'lyhy',
    '洛阳市三川大道120号',
    '13903790379'
  ) ;

-- 插入客户表
INSERT INTO khb (khmc, khmcsx, khdz, khlxfs) 
VALUES
  ('洛阳前进超市',
    'lyqj',
    '洛阳市开元大道40号',
    '13703790379'
  );

-- 插入仓库表
INSERT INTO ckb (ckmc, ckdz) 
VALUES
  (
    '1号仓库',
    '洛阳市武汉路39号'
  ) ;

-- 插入库存表
INSERT INTO kcb (
  ckid,
  spid,
  jhpjdj,
  xspjdj,
  kcsl,
  zgkcl,
  zdkcl
) 
VALUES
  (1, 1, 0, 0, 0, 9999, 10),
  (1, 2, 0, 0, 0, 9999, 10),
  (1, 3, 0, 0, 0, 9999, 10),
  (1, 4, 0, 0, 0, 9999, 10),
  (1, 5, 0, 0, 0, 9999, 10),
  (1, 6, 0, 0, 0, 9999, 10) ;