仓库管理系统

create database Warehouse_management;//建库
use Warehouse_management;

一、建表

1、管理员信息表

CREATE TABLE ManagerInfo (
  Mno CHAR(3) PRIMARY KEY,
  Mname VARCHAR(10) NOT NULL,
  Mgender CHAR(1) DEFAULT '男',
  Mbirhdate DATE,
  Mtelephone CHAR(11) NOT NULL,
  Mhiredate DATE,
  Wno CHAR(3) NOT NULL
);

2、仓库信息表

CREATE TABLE WarehouseInfo (
  Wno CHAR(3) PRIMARY KEY,
  Wname VARCHAR(10) NOT NULL,
  Wtype VARCHAR(7),
  Waddress VARCHAR(30),
  Wcharge CHAR(3),
);

3、货品信息表

CREATE TABLE ProductInfo (
  Pno CHAR(8) PRIMARY KEY,
  Pname VARCHAR(30) NOT NULL,
  Ptype VARCHAR(8),
  Pprice FLOAT,
  Punit VARCHAR(10),
  Pweight VARCHAR(10),
  Pmanufacture VARCHAR(30),
  Pguaranteedate VARCHAR(8),
  Wno CHAR(3),
  Pstock INT
);

4、入库清单表

CREATE TABLE InList (
  Iid INT AUTO_INCREMENT PRIMARY KEY,
  Pno CHAR(8),
  Wno CHAR(3),
  Mno CHAR(3),
  Idate DATE,
  Isupplier VARCHAR(30),
  Iprice FLOAT,
  Inum INT
);

5、出库清单表

CREATE TABLE OutList (
  Oid INT AUTO_INCREMENT PRIMARY KEY,
  Pno CHAR(8),
  Wno CHAR(3),
  Mno CHAR(3),
  Odate DATE,
  Onum INT,
  Odest VARCHAR(30)
);

二、设置外键

alter table ManagerInfo
add constraint fk_manager_warehouse foreign key (Wno) references WarehouseInfo(Wno);

-- 设置管理员表和仓库信息表之间管理员编号的外键约束
alter table WarehouseInfo
add constraint fk_warehouse_manager foreign key (Wcharge) references ManagerInfo(Mno);

-- 设置货品信息表和仓库信息表之间存放仓库编号的外键约束
ALTER TABLE ProductInfo
ADD CONSTRAINT fk_product_warehouse FOREIGN KEY (Wno) REFERENCES WarehouseInfo(Wno);

-- 设置入库清单表和货品信息表之间货品编号的外键约束
ALTER TABLE InList
ADD CONSTRAINT fk_inlist_product FOREIGN KEY (Pno) REFERENCES ProductInfo(Pno);

-- 设置入库清单表和仓库信息表之间仓库编号的外键约束
ALTER TABLE InList
ADD CONSTRAINT fk_inlist_warehouse FOREIGN KEY (Wno) REFERENCES WarehouseInfo(Wno);

-- 设置入库清单表和管理员信息表之间管理员编号的外键约束
ALTER TABLE InList
ADD CONSTRAINT fk_inlist_manager FOREIGN KEY (Mno) REFERENCES ManagerInfo(Mno);

-- 设置出库清单表和货品信息表之间货品编号的外键约束
ALTER TABLE OutList
ADD CONSTRAINT fk_outlist_product FOREIGN KEY (Pno) REFERENCES ProductInfo(Pno);

-- 设置出库清单表和仓库信息表之间仓库编号的外键约束
ALTER TABLE OutList
ADD CONSTRAINT fk_outlist_warehouse FOREIGN KEY (Wno) REFERENCES WarehouseInfo(Wno);

-- 设置出库清单表和管理员信息表之间管理员编号的外键约束
ALTER TABLE OutList
ADD CONSTRAINT fk_outlist_manager FOREIGN KEY (Mno) REFERENCES ManagerInfo(Mno);

三、单表查询

1）查询所有管理员的姓名和联系电话

SELECT Mname, Mtelephone FROM ManagerInfo;

2）查询所有仓库的名称和地址

SELECT Wname, Waddress FROM WarehouseInfo;

3）查询所有产品的名称和价格

SELECT Pname, Pprice FROM ProductInfo;

四、复杂查询

1）查询每个仓库管理员的名字和管理的仓库名称

SELECT MI.Mname, WI.Wname 
FROM ManagerInfo MI
JOIN WarehouseInfo WI ON MI.Wno = WI.Wno;

2）查询所有库存为0的产品

SELECT Pname 
FROM ProductInfo 
WHERE Pstock = 0;

3）查询每个产品的库存数量，要求库存数量超过100的产品排在前面

SELECT Pname, Pstock 
FROM ProductInfo 
ORDER BY Pstock DESC;

五、触发器

1）创建一个在每次插入InList表时自动更新对应产品库存的触发器

DELIMITER //
CREATE TRIGGER UpdateStockAfterIn
AFTER INSERT ON InList
FOR EACH ROW
BEGIN
UPDATE ProductInfo 
SET Pstock = Pstock + NEW.Inum
WHERE Pno = NEW.Pno;
END;
//
DELIMITER ;

2）创建一个在每次删除ManagerInfo表记录时自动删除对应WarehouseInfo表记录的触发器

DELIMITER //
CREATE TRIGGER DeleteWarehouseWhenManagerDeleted
BEFORE DELETE ON ManagerInfo
FOR EACH ROW
BEGIN
DELETE FROM WarehouseInfo WHERE Wcharge = OLD.Mno;
END;
//
DELIMITER ;

3）创建一个在每次插入OutList表时自动计算并更新对应产品库存的触发器

DELIMITER //
CREATE TRIGGER UpdateStockAfterOut
AFTER INSERT ON OutList
FOR EACH ROW
BEGIN
UPDATE ProductInfo 
SET Pstock = Pstock - NEW.Onum
WHERE Pno = NEW.Pno;
END;
//
DELIMITER ;

六、存储过程

创建一个存储过程用于添加新产品

DELIMITER //
CREATE PROCEDURE AddProduct(IN p_name VARCHAR(30), IN p_type VARCHAR(8), IN p_price FLOAT, IN p_unit VARCHAR(10), 
                             IN p_weight VARCHAR(10), IN p_manufacture VARCHAR(30), IN p_guaranteedate VARCHAR(8), 
                             IN p_stock INT, IN p_warehouse CHAR(3))
BEGIN
INSERT INTO ProductInfo(Pname, Ptype, Pprice, Punit, Pweight, Pmanufacture, Pguaranteedate, Wno, Pstock) 
VALUES (p_name, p_type, p_price, p_unit, p_weight, p_manufacture, p_guaranteedate, p_warehouse, p_stock);
END;
//
DELIMITER ;

创建一个存储过程用于列出所有产品

DELIMITER //
CREATE PROCEDURE ListAllProducts()
BEGIN
SELECT * FROM ProductInfo;
END;
//
DELIMITER ;

创建一个存储过程用于检查某个产品的库存

DELIMITER //
CREATE PROCEDURE CheckProductStock(IN p_no CHAR(8))
BEGIN
SELECT Pstock FROM ProductInfo WHERE Pno = p_no;
END;

七、视图

查询每个仓库的库存总价值

CREATE VIEW WarehouseInventoryValue AS
SELECT WI.Wname, SUM(PI.Pprice * IL.Inum) AS TotalInventoryValue
FROM WarehouseInfo WI
JOIN ProductInfo PI ON WI.Wno = PI.Wno
JOIN InList IL ON PI.Pno = IL.Pno
GROUP BY WI.Wname;

总（表+外键）

CREATE TABLE ManagerInfo (
  Mno CHAR(3) PRIMARY KEY,
  Mname VARCHAR(10) NOT NULL,
  Mgender CHAR(1) DEFAULT '男',
  Mbirhdate DATE,
  Mtelephone CHAR(11) NOT NULL,
  Mhiredate DATE,
  Wno CHAR(3) NOT NULL
);


CREATE TABLE WarehouseInfo (
  Wno CHAR(3) PRIMARY KEY,
  Wname VARCHAR(10) NOT NULL,
  Wtype VARCHAR(7),
  Waddress VARCHAR(30),
  Wcharge CHAR(3)
);

CREATE TABLE ProductInfo (
  Pno CHAR(8) PRIMARY KEY,
  Pname VARCHAR(30) NOT NULL,
  Ptype VARCHAR(8),
  Pprice FLOAT,
  Punit VARCHAR(10),
  Pweight VARCHAR(10),
  Pmanufacture VARCHAR(30),
  Pguaranteedate VARCHAR(8),
  Wno CHAR(3),
  Pstock INT
);
CREATE TABLE InList (
  Iid INT AUTO_INCREMENT PRIMARY KEY,
  Pno CHAR(8),
  Wno CHAR(3),
  Mno CHAR(3),
  Idate DATE,
  Isupplier VARCHAR(30),
  Iprice FLOAT,
  Inum INT
);

CREATE TABLE OutList (
  Oid INT AUTO_INCREMENT PRIMARY KEY,
  Pno CHAR(8),
  Wno CHAR(3),
  Mno CHAR(3),
  Odate DATE,
  Onum INT,
  Odest VARCHAR(30)
);


alter table ManagerInfo
add constraint fk_manager_warehouse foreign key (Wno) references WarehouseInfo(Wno);

-- 设置管理员表和仓库信息表之间管理员编号的外键约束
alter table WarehouseInfo
add constraint fk_warehouse_manager foreign key (Wcharge) references ManagerInfo(Mno);

-- 设置货品信息表和仓库信息表之间存放仓库编号的外键约束
ALTER TABLE ProductInfo
ADD CONSTRAINT fk_product_warehouse FOREIGN KEY (Wno) REFERENCES WarehouseInfo(Wno);

-- 设置入库清单表和货品信息表之间货品编号的外键约束
ALTER TABLE InList
ADD CONSTRAINT fk_inlist_product FOREIGN KEY (Pno) REFERENCES ProductInfo(Pno);

-- 设置入库清单表和仓库信息表之间仓库编号的外键约束
ALTER TABLE InList
ADD CONSTRAINT fk_inlist_warehouse FOREIGN KEY (Wno) REFERENCES WarehouseInfo(Wno);

-- 设置入库清单表和管理员信息表之间管理员编号的外键约束
ALTER TABLE InList
ADD CONSTRAINT fk_inlist_manager FOREIGN KEY (Mno) REFERENCES ManagerInfo(Mno);

-- 设置出库清单表和货品信息表之间货品编号的外键约束
ALTER TABLE OutList
ADD CONSTRAINT fk_outlist_product FOREIGN KEY (Pno) REFERENCES ProductInfo(Pno);

-- 设置出库清单表和仓库信息表之间仓库编号的外键约束
ALTER TABLE OutList
ADD CONSTRAINT fk_outlist_warehouse FOREIGN KEY (Wno) REFERENCES WarehouseInfo(Wno);

-- 设置出库清单表和管理员信息表之间管理员编号的外键约束
ALTER TABLE OutList
ADD CONSTRAINT fk_outlist_manager FOREIGN KEY (Mno) REFERENCES ManagerInfo(Mno);

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：http://www.mfbz.cn/a/772717.html

如若内容造成侵权/违法违规/事实不符，请联系我们进行投诉反馈qq邮箱809451989@qq.com，一经查实，立即删除！