仓库管理系统

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);