开发一套ERP 数据库规整

CREATE DATABASE IF NOT EXISTS ERPCHARACTER SET utf8mb4COLLATE utf8mb4_general_ci;#人工手动判断 告警状态 下订单时自动增加
#通过订单编号区分 交付和未交付的日期和时间USE ERP;CREATE TABLE INBOUND (id_auto INT NOT NULL AUTO_INCREMENT PRIMARY KEY,order_number INT NOT NULL,id INT NOT NULL,name VARCHAR(32),color VARCHAR(32),name_and_color VARCHAR(32),zh_size VARCHAR(32),u_size VARCHAR(32),factory_name VARCHAR(32),input INT,input_date VARCHAR(32)
);CREATE TABLE OUTBOUND(id_auto INT NOT NULL AUTO_INCREMENT PRIMARY KEY,id INT NOT NULL,name VARCHAR(32),color VARCHAR(32),name_and_color VARCHAR(32),zh_size VARCHAR(32),u_size VARCHAR(32),factory_name VARCHAR(32),output INT,output_date VARCHAR(32)
);CREATE TABLE FACTORY (id_auto INT NOT NULL AUTO_INCREMENT PRIMARY KEY,order_number INT NOT NULL,id INT NOT NULL,factory_name VARCHAR(32),brand_name VARCHAR(32),order_counts INT,deliver INT,not_deliver INT,order_date VARCHAR(32)
);CREATE TABLE STORE(id_auto INT NOT NULL AUTO_INCREMENT PRIMARY KEY,id INT NOT NULL,name VARCHAR(32),color VARCHAR(32),name_and_color VARCHAR(32),zh_size VARCHAR(32),u_size VARCHAR(32),store INT,threshold INT,alarm boolean
);CREATE TRIGGER RTC_FACTORYAFTER INSERT ON INBOUND FOR EACH ROW
BEGINUPDATE  FACTORYSET deliver = deliver + NEW.inputWHERE (factory_name = NEW.factory_name) and (order_number = NEW.order_number) and (id = NEW.id);UPDATE FACTORYSET not_deliver = FACTORY.order_counts - FACTORY.deliverWHERE (factory_name = NEW.factory_name) and (order_number = NEW.order_number) and (id = NEW.id);
END;CREATE TRIGGER RTC_STORE_APPENDAFTER INSERT ON INBOUND FOR EACH ROW
BEGINUPDATE STORESET store = store + NEW.inputWHERE (id = NEW.id) and (factory_name = NEW.factory_name);
END;CREATE TRIGGER RTC_STORE_POPAFTER INSERT ON OUTBOUND FOR EACH ROW
BEGINUPDATE STORESET store = store - NEW.outputWHERE (id = NEW.id) and (factory_name = NEW.factory_name);
END;DROP TRIGGER WATCH_INBOUND;CREATE TRIGGER WATCH_INBOUNDAFTER INSERT ON INBOUND FOR EACH ROW
BEGININSERT INTOSTORE(id,name,color,name_and_color,zh_size,u_size,store,threshold,alarm)VALUES(NEW.id,NEW.name,NEW.color,NEW.name_and_color,NEW.zh_size,NEW.u_size,NEW.input,0,FALSE)ON DUPLICATE KEY UPDATEid=NEW.id,name=NEW.name,color=NEW.color,name_and_color=NEW.name_and_color,zh_size=NEW.zh_size,u_size=NEW.u_size,store=NEW.input+store,threshold=0,alarm=FALSE;
END;

大型数据库的维护还挺有意思的

挺有意思的,减少了很多应用层的逻辑,直接在插入时就完成了,数据的操作

所有的数据关联,就可以在数据库中完成操作

开发下来还是底层程序有意思

在让数据关系复杂些，在提高下性能,就会使用类似指针的操作,引用,数据,在来一个临时表

CREATE DATABASE IF NOT EXISTS ERPCHARACTER SET utf8mb4COLLATE utf8mb4_general_ci;USE ERP;CREATE TABLE INBOUND (id_auto INT NOT NULL AUTO_INCREMENT PRIMARY KEY,order_number INT NOT NULL,id INT NOT NULL,name VARCHAR(32),color VARCHAR(32),name_and_color VARCHAR(32),zh_size VARCHAR(32),u_size VARCHAR(32),factory_name VARCHAR(32),input INT,input_date VARCHAR(32)
);CREATE TABLE OUTBOUND(id_auto INT NOT NULL AUTO_INCREMENT PRIMARY KEY,id INT NOT NULL,name VARCHAR(32),color VARCHAR(32),name_and_color VARCHAR(32),zh_size VARCHAR(32),u_size VARCHAR(32),factory_name VARCHAR(32),output INT,output_date VARCHAR(32)
);CREATE TABLE FACTORY (id_auto INT NOT NULL AUTO_INCREMENT PRIMARY KEY,order_number INT NOT NULL,id INT NOT NULL,factory_name VARCHAR(32),brand_name VARCHAR(32),order_counts INT,deliver INT,not_deliver INT,order_date VARCHAR(32)
);CREATE TABLE STORE(id_auto INT NOT NULL AUTO_INCREMENT PRIMARY KEY,id INT NOT NULL,name VARCHAR(32),color VARCHAR(32),name_and_color VARCHAR(32),zh_size VARCHAR(32),u_size VARCHAR(32),store INT,threshold INT,alarm boolean,factory_name VARCHAR(32)
);# DROP TRIGGER RTC_FACTORY;CREATE TRIGGER RTC_FACTORYAFTER INSERT ON INBOUND FOR EACH ROW
BEGINUPDATE  FACTORYSET deliver = deliver + NEW.inputWHERE (factory_name = NEW.factory_name) and (order_number = NEW.order_number) and (id = NEW.id);UPDATE FACTORYSET not_deliver = FACTORY.order_counts - FACTORY.deliverWHERE (factory_name = NEW.factory_name) and (order_number = NEW.order_number) and (id = NEW.id);
END;# DROP TRIGGER RTC_STORE_APPEND;CREATE TRIGGER RTC_STORE_APPENDAFTER INSERT ON INBOUND FOR EACH ROW
BEGINUPDATE STORESET store = store + NEW.inputWHERE (id = NEW.id) and (factory_name = NEW.factory_name);
END;# DROP TRIGGER RTC_STORE_POP;CREATE TRIGGER RTC_STORE_POPAFTER INSERT ON OUTBOUND FOR EACH ROW
BEGINUPDATE STORESET store = store - NEW.outputWHERE id = NEW.id;
END;# DROP TRIGGER WATCH_INBOUND;CREATE TRIGGER WATCH_INBOUNDAFTER INSERT ON INBOUND FOR EACH ROW
BEGININSERT INTOSTORE(id,name,color,name_and_color,zh_size,u_size,store,threshold,alarm)VALUES(NEW.id,NEW.name,NEW.color,NEW.name_and_color,NEW.zh_size,NEW.u_size,NEW.input,0,FALSE)ON DUPLICATE KEY UPDATEid=NEW.id,name=NEW.name,color=NEW.color,name_and_color=NEW.name_and_color,zh_size=NEW.zh_size,u_size=NEW.u_size,store=NEW.input+store,threshold=0,alarm=FALSE;
END;