返回
网站建设知识
MySQL数据库的进销存
2025-07-22 10:01  点击:0

一、建表

1、用户

DROP TABLE IF EXISTS `tb_user`;CREATE TABLE `tb_user` (  `user_id` int(20) NOT NULL auto_increment,  `user_name` varchar(50) NOT NULL,  `user_login_name` varchar(50) NOT NULL,  `user_login_password` varchar(50) NOT NULL,  `user_tel` varchar(50) NOT NULL,  PRIMARY KEY  (`user_id`))

2、商品类别

DROP TABLE IF EXISTS `tb_category`;CREATE TABLE `tb_category` (  `category_id` int(20) NOT NULL auto_increment,  `category_name` varchar(50) NOT NULL,  PRIMARY KEY  (`category_id`))

3、商品

DROP TABLE IF EXISTS `tb_goods`;CREATE TABLE `tb_goods` (  `goods_id` int(20) NOT NULL auto_increment,  `goods_name` varchar(50) NOT NULL,  `goods_depot` varchar(50) NOT NULL,  `goods_unit` varchar(50) NOT NULL,  `goods_number` int(50) NOT NULL,  `goods_price` double(50) NOT NULL,  `goods_alarm` int(50) NOT NULL,  `category_id` int(20) NOT NULL,  PRIMARY KEY  (`goods_id`),  KEY `key_goods_category_id` (`category_id`),  ConSTRAINT `key_goods_category_id` FOREIGN KEY (`category_id`) REFERENCES `tb_category` (`category_id`))

4、供应商

DROP TABLE IF EXISTS `tb_company`;CREATE TABLE `tb_company` (  `company_id` int(20) NOT NULL auto_increment,  `company_name` varchar(50) NOT NULL,  `company_address` varchar(50) NOT NULL,  `company_linkman` varchar(50) NOT NULL,  `company_tel` varchar(50) NOT NULL,  PRIMARY KEY  (`company_id`))

5、进货

DROP TABLE IF EXISTS `tb_purchase`;CREATE TABLE `tb_purchase` (  `purchase_id` int(20) NOT NULL auto_increment,  `purchase_price` double(50) NOT NULL,  `purchase_num` int(50) NOT NULL,  `purchase_sum` double(50) NOT NULL,  `purchase_buyer` varchar(50) NOT NULL,  `purchase_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,  `company_id` int(20) NOT NULL,  `goods_id` int(20) NOT NULL,  PRIMARY KEY  (`purchase_id`),  KEY `key_purchase_company_id` (`company_id`),  KEY `key_purchase_goods_id` (`goods_id`),  ConSTRAINT `key_purchase_goods_id` FOREIGN KEY (`goods_id`) REFERENCES `tb_goods` (`goods_id`),  ConSTRAINT `key_purchase_company_id` FOREIGN KEY (`company_id`) REFERENCES `tb_company` (`company_id`))

6、销售

DROP TABLE IF EXISTS `tb_sales`;CREATE TABLE `tb_sales` (  `sales_id` int(20) NOT NULL auto_increment,  `sales_price` double(50) NOT NULL,  `sales_num` int(50) NOT NULL,  `sales_sum` double(50) NOT NULL,  `sales_salesperson` varchar(50) NOT NULL,  `sales_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,  `goods_id` int(20) NOT NULL,  PRIMARY KEY  (`sales_id`),  KEY `key_sales_goods_id` (`goods_id`),  ConSTRAINT `key_sales_goods_id` FOREIGN KEY (`goods_id`) REFERENCES `tb_goods` (`goods_id`))

7、退货

DROP TABLE IF EXISTS `tb_return`;CREATE TABLE `tb_return` (  `return_id` int(20) NOT NULL auto_increment,  `return_num` int(50) NOT NULL,  `return_sum` double(50) NOT NULL,  `return_person` varchar(50) NOT NULL,  `return_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,  `goods_id` int(20) NOT NULL,  `sales_id` int(20) NOT NULL,  PRIMARY KEY  (`return_id`),  KEY `key_return_goods_id` (`goods_id`),  KEY `key_return_sales_id` (`sales_id`),  ConSTRAINT `key_return_goods_id` FOREIGN KEY (`goods_id`) REFERENCES `tb_goods` (`goods_id`),  ConSTRAINT `key_return_sales_id` FOREIGN KEY (`sales_id`) REFERENCES `tb_sales` (`sales_id`))

二、插入数据

1、用户

insert into tb_user(user_name,user_login_name,user_login_password,user_tel)select '张三','zhangsan','000000','14755778090' unionselect '李四','lisi','000000','13673740829' unionselect '王五','wangwu','000000','18974958526' unionselect '赵六','zhaoliu','000000','15167764562'

2、商品类别

insert into tb_category(category_name)select '水果' unionselect '蔬菜' unionselect '方便面' unionselect '牛奶'

3、商品

insert into tb_goods(goods_name,goods_depot,goods_unit,goods_number,goods_price,goods_alarm,category_id)select '火龙果','仓库B','箱','200','30.00','50',1 unionselect '猕猴桃','仓库B','箱','100','47.00','50',1 unionselect '白菜','仓库C','箱','100','21.00','50',2 unionselect '萝卜','仓库C','箱','100','28.00','50',2 unionselect '康师傅老坛酸菜面','仓库A','箱','200','38.00','100',3 unionselect '统一100红烧牛肉面','仓库A','箱','200','37.00','100',3 unionselect '蒙牛','仓库D','箱','100','47.00','50',4 unionselect '伊利','仓库D','箱','100','47.00','50',4

4、供应商

insert into tb_company(company_name,company_address,company_linkman,company_tel)select '统一','台湾','吴修齐','15674352989' unionselect '康师傅','上海市','韦俊贤','13644567859' unionselect '蒙牛','内蒙古自治区','宁高宁','15674352989' unionselect '伊利','内蒙古呼和浩特','潘刚','18648978956' unionselect '绿色','宁波市','乐世德','13847805208'

5、进货

insert into tb_purchase(purchase_price,purchase_num,purchase_sum,purchase_buyer,company_id,goods_id)select '20.00','200',20.00*200,'张三',5,1 unionselect '37.00','100',37.00*100,'张三',5,2 unionselect '11.00','100',11.00*100,'张三',5,3 unionselect '18.00','100',18.00*100,'张三',5,4 unionselect '28.00','200',28.00*200,'王五',2,5 unionselect '27.00','200',27.00*200,'王五',1,6 unionselect '37.00','100',37.00*100,'李四',3,7 unionselect '37.00','100',37.00*100,'赵六',4,8

6、销售

insert into tb_sales(sales_price,sales_num,sales_sum,sales_salesperson,goods_id)select 30.00,4,30.00*4,'张三',1 unionselect 47.00,2,47.00*2,'李四',2 unionselect 21.00,1,21.00,'王五',3 unionselect 28.00,1,28.00,'赵六',4

7、退货

insert into tb_return(return_num,return_sum,return_person,goods_id,sales_id)select 1,21.00,'王五',3,3 unionselect 1,28.00,'赵六',4,4

三、管理

A、员工模块

1、实现员工数据的插入、修改、删除（存储过程）

// 插入DROP PROCEDURE IF EXISTS p_adduser;CREATE PROCEDURE p_adduser(username VARCHAR(50),userloginname VARCHAR(50),userloginpasswor VARCHAR(50),usertel VARCHAR(50))BEGIN    INSERT INTO tb_user(user_name,user_login_name,user_login_password,user_tel)    VALUES(username,userloginname,userloginpasswor,usertel);END;// 调用方式1：call p_adduser('田七','tianqi','000000','13747353529');// 调用方式2：set @username='朱八';set @userloginname='zhuba';set @userloginpassword='000000';set @usertel='13789599431';call p_adduser(@username,@userloginname,@userloginpassword,@usertel);

调用后：

// 修改DROP PROCEDURE IF EXISTS p_updateuser;CREATE PROCEDURE p_updateuser(username VARCHAR(50),userloginname VARCHAR(50),userloginpassword VARCHAR(50),usertel VARCHAR(50),userid INT(20))BEGIN    UPDATE tb_user SET user_name=username,user_login_name=userloginname,user_login_password=userloginpassword,user_tel=usertel    WHERE user_id=userid;END;// 调用call p_updateuser('田七1','tianqi1','000000','13789599441',6);

调用后：

// 删除DROP PROCEDURE IF EXISTS p_deleteuser;CREATE PROCEDURE p_deleteuser(userid int(20))BEGIN    DELETE FROM tb_user WHERE user_id=userid;END;// 调用call p_deleteuser(6);

调用后：

B、供应商模块

1、实现供应商数据的插入、修改、删除（存储过程）

// 插入DROP PROCEDURE IF EXISTS p_addcompany;CREATE PROCEDURE p_addcompany(companyname VARCHAR(50),companyaddr VARCHAR(50),companylinkman VARCHAR(50),companytel VARCHAR(50))BEGIN    INSERT INTO tb_company(company_name,company_address,company_linkman,company_tel)    VALUES(companyname,companyaddr,companylinkman,companytel);END;// 调用call p_addcompany('娃哈哈','杭州','宗馥莉','15673576689');

调用后：

// 修改DROP PROCEDURE IF EXISTS p_updatecompany;CREATE PROCEDURE p_updatecompany(companyname VARCHAR(50),companyaddr VARCHAR(50),companylinkman VARCHAR(50), companytel VARCHAR(50), companyid INT(20))BEGIN    UPDATE tb_company SET company_name=companyname, company_address=companyaddr, company_linkman=companylinkman, company_tel=companytel    WHERE company_id=companyid;END;// 调用call p_updatecompany('娃哈哈1','浙江杭州','宗馥莉','15673576689',6);

调用后：

// 删除DROP PROCEDURE IF EXISTS p_deletecompany;CREATE PROCEDURE p_deletecompany(companyid INT(20))BEGIN    DELETE FROM tb_company WHERE company_id=companyid;END;// 调用call p_deletecompany(6);

调用后：

C、库存管理

1、能够实现对商品数据的编辑，包括增、删、改（存储过程）

// 插入DROP PROCEDURE IF EXISTS p_addgoods;CREATE PROCEDURE p_addgoods(goodsname VARCHAR(50),goodsdepot VARCHAR(50),goodsunit VARCHAR(50),goodsnumber INT(50),goodsprice DOUBLE,goodsalarm INT(50),categoryid INT(20))BEGIN    INSERT INTO tb_goods(goods_name,goods_depot,goods_unit,goods_number,goods_price,goods_alarm,category_id)    VALUES(goodsname,goodsdepot,goodsunit,goodsnumber,goodsprice,goodsalarm,categoryid);END;// 调用CALL p_addgoods ('重庆酸辣粉','A仓库','箱',200,25.00,100,3);

调用后：

// 修改DROP PROCEDURE IF EXISTS p_updgoods;CREATE PROCEDURE p_updategoods(goodsname VARCHAR(50),goodsdepot VARCHAR(50),goodsunit VARCHAR(50),goodsnumber INT(50),goodsprice DOUBLE,goodsalarm INT(50),categoryid INT(20),goodsid INT(20))BEGIN    UPDATE tb_goods SET goods_name=goodsname,goods_depot=goodsdepot,goods_unit=goodsunit,goods_number=goodsnumber,goods_price=goodsprice,goods_alarm=goodsalarm,category_id=categoryid    WHERE goods_id=goodsid;END;// 调用CALL p_updategoods ('重庆酸辣粉1','A仓库','箱',600,25.00,200,3,9);

调用后：

// 删除DROP PROCEDURE IF EXISTS p_deletegoods;CREATE PROCEDURE p_deletegoods(goodsid INT(20))BEGIN    DELETE FROM tb_goods WHERE goods_id=goodsid;END;// 调用CALL p_deletegoods(9);

调用后：

2、能够实现对商品数据，可以根据商品类别、商品名称、价格、库存数量进行查询（存储过程）

DROP PROCEDURE IF EXISTS p_querygoods;CREATE PROCEDURE p_querygoods(goodsname VARCHAR(50),categoryname VARCHAR(50),minnum INT(50),maxnum INT(50),minprice DOUBLE,maxprice DOUBLE)BEGIN    DECLARE querysql varchar(5000);    DECLARE goodssql varchar(1000);    DECLARE categorysql varchar(1000);    DECLARE numsql varchar(1000);    DECLARE pricesql varchar(1000);    SET querysql='select * from tb_goods where 1=1 ';    IF(goodsname IS NOT NULL) THEN        SET goodssql=ConCAT(' and goods_name like ''%',goodsname,'%'' ');        SET querysql=ConCAT(querysql,goodssql);    END IF;    IF(categoryname IS NOT NULL) THEN        SET categorysql=ConCAT(' and category_id in(select category_id from tb_category where category_name like ''%',categoryname,'%'') ');        SET querysql=ConCAT(querysql,categorysql);    END IF;    IF(minnum IS NOT NULL)AND(maxnum IS NOT NULL) THEN        SET numsql=ConCAT(' and goods_number between ',minnum,' and ',maxnum,' ');        SET querysql=ConCAT(querysql,numsql);    END IF;    IF(minprice IS NOT NULL)AND(maxprice IS NOT NULL) THEN        SET pricesql=ConCAT(' and goods_price between ',minprice,' and ',maxprice,' ');        SET querysql=ConCAT(querysql,pricesql);    END IF;    SET @querysql=querysql;    PREPARE querysqltemp FROM @querysql;    EXECUTE querysqltemp;    DEALLOCATE PREPARE querysqltemp;END;// 调用CALL p_querygoods(NULL,'面',0,1000,0,100);

调用结果：

3、能够查询低于警报线的商品数量（视图实现）

DROP VIEW IF EXISTS v_alarmgoodsnum;CREATE VIEW v_alarmgoodsnum ASSELECT * FROM tb_goods WHERE goods_number
D、进货管理
1、能够实现对进货单的编辑，包括增、删、改（存储过程）
// 插入DROP PROCEDURE IF EXISTS p_addpurchase;CREATE PROCEDURE p_addpurchase(purchaseprice DOUBLE,purchasenum INT(50),purchaseman VARCHAR(50),companyid INT(50),goodsid INT(20))BEGIN    INSERT INTO tb_purchase(purchase_price,purchase_num,purchase_sum,purchase_buyer,company_id,goods_id)    VALUES(purchaseprice,purchasenum,purchaseprice*purchasenum,purchaseman,companyid,goodsid);END;//调用CALL p_addpurchase(89.00,200,'张三',3,7);
调用后：
// 修改DROP PROCEDURE IF EXISTS p_updatepurchase;CREATE PROCEDURE p_updatepurchase(purchaseprice DOUBLE,purchasenum INT(50),purchaseman VARCHAR(50),companyid INT(50),goodsid INT(20),purchaseid INT(20))BEGIN    UPDATE tb_purchase SET purchase_price=purchaseprice,purchase_num=purchasenum,purchase_sum=purchaseprice*purchasenum,purchase_buyer=purchaseman,company_id=companyid,goods_id=goodsid    WHERE purchase_id=purchaseid;END;// 调用CALL p_updatepurchase(90.00,150,'张三',3,7,9);
调用后：
// 删除DROP PROCEDURE IF EXISTS p_deletepurchase;CREATE PROCEDURE p_deletepurchase(purchaseid INT(20))BEGIN    DELETE FROM tb_purchase WHERE purchase_id=purchaseid; END;// 调用CALL p_deletepurchase(9);
调用后：
2、编写触发器，要求进货单的数据一旦插入则库存随之变化
// 插入CREATE TRIGGER t_insertpurchaseAFTER INSERT ON tb_purchase FOR EACH ROWBEGIN    DECLARE goodsid INT(20);    DECLARE goodsnumber INT(50);    SELECT goods_id INTO goodsid FROM tb_purchase WHERE purchase_id=new.purchase_id;    SELECT purchase_num INTO goodsnumber FROM tb_purchase WHERE purchase_id=new.purchase_id;    UPDATE tb_goods SET goods_number=goods_number+goodsnumber WHERE goods_id=goodsid;END;// 调用LOCK TABLE tb_purchase WRITE; --锁表CALL p_addpurchase(89.00,200,'张三',3,7);// 查询UNLOCK TABLE; --解锁SELECT * FROM TB_PURCHASE;SELECT * FROM TB_GOODS;
调用后：

// 修改CREATE TRIGGER t_updatepurchaseAFTER UPDATE ON tb_purchase FOR EACH ROWBEGIN    DECLARE goodsid INT(20);    DECLARE goodsnumber INT(50);    DECLARE goodsnumberold INT(50);    SELECT goods_id INTO goodsid FROM tb_purchase WHERE purchase_id=new.purchase_id;    SELECT purchase_num INTO goodsnumber FROM tb_purchase WHERE purchase_id=new.purchase_id;    SELECT old.purchase_num INTO goodsnumberold FROM tb_purchase WHERE purchase_id=old.purchase_id;    UPDATE tb_goods SET goods_number=goods_number+(goodsnumber-goodsnumberold) WHERE goods_id=goodsid;END;// 调用LOCK TABLE tb_purchase WRITE;CALL p_updatepurchase(89.00,100,'张三',3,7,18);// 查询UNLOCK TABLE; --解锁SELECT * FROM TB_PURCHASE;SELECT * FROM TB_GOODS;
查询结果：
// 删除CREATE TRIGGER t_deletepurchaseAFTER DELETE ON tb_purchase FOR EACH ROWBEGIN    UPDATE tb_goods SET goods_number=goods_number-old.purchase_num WHERE goods_id=old.goods_id;END;// 调用LOCK TABLE tb_purchase WRITE;CALL p_deletepurchase(18);// 查询UNLOCK TABLE;SELECT * FROM TB_PURCHASE;SELECT * FROM TB_GOODS;
查询结果：
3、能够对进货信息，根据商品名称、商品类别、进货时间（开始、结束）、以及价格进行查询（存储过程）
DROP PROCEDURE IF EXISTS p_purchasedetails;CREATE PROCEDURE p_purchasedetails(goodsname VARCHAR(50),categoryname VARCHAR(50),starttime TIMESTAMP,endtime TIMESTAMP,minprice DOUBLE,maxprice DOUBLE)BEGIN    DECLARE querysql varchar(5000);    DECLARE goodssql varchar(1000);    DECLARE categorysql varchar(1000);    DECLARE timesql varchar(1000);    DECLARE pricesql varchar(1000);    SET querysql='select * from tb_purchase where 1=1 ';    IF(goodsname IS NOT NULL) THEN        SET goodssql=ConCAT(' and goods_id in(select goods_id from tb_goods where goods_name like ''%',goodsname,'%'') ');        SET querysql=ConCAT(querysql,goodssql);    END IF;    IF(categoryname IS NOT NULL) THEN        SET categorysql=ConCAT(' and goods_id in(select goods_id from tb_goods where category_id in(select category_id from tb_category where category_name like ''%',categoryname,'%'')) ');        SET querysql=ConCAT(querysql,categorysql);    END IF;    IF(starttime IS NOT NULL)AND(endtime IS NOT NULL) THEN        SET timesql=ConCAT(' and purchase_date between ''',starttime,''' and ''',endtime,''' ');        SET querysql=ConCAT(querysql,timesql);    END IF;    IF(minprice IS NOT NULL)AND(maxprice IS NOT NULL) THEN        SET pricesql=ConCAT(' and purchase_price between ',minprice,' and ',maxprice,' ');        SET querysql=ConCAT(querysql,pricesql);    END IF;    SET @querysql=querysql;    PREPARE querysqltemp FROM @querysql;    EXECUTE querysqltemp;    DEALLOCATE PREPARE querysqltemp;END;// 调用CALL p_purchasedetails('','','2016-01-01','2016-09-29',0,1000);
调用结果：
E、销售管理
1、能够实现对销售单的编辑，包括增、删、改（存储过程）
// 插入DROP PROCEDURE IF EXISTS p_addsales;CREATE PROCEDURE p_addsales(salesprice DOUBLE,salesnum INT(50),salesperson VARCHAR(50),goodsid INT(20))BEGIN    INSERT INTO tb_sales(sales_price,sales_num,sales_sum,sales_salesperson,goods_id)    VALUES(salesprice,salesnum,salesnum*salesprice,salesperson,goodsid);END;// 调用CALL p_addsales(29,123,'张三',6);
调用结果：
// 修改DROP PROCEDURE IF EXISTS p_updatesales;CREATE PROCEDURE p_updatesales(salesprice DOUBLE,salesnum INT(50),salesperson VARCHAR(50),goodsid INT(20),salesid int(20))BEGIN    UPDATE tb_sales SET sales_price=salesprice,sales_num=salesnum,sales_sum=salesnum*salesprice,sales_salesperson=salesperson,goods_id=goodsid    WHERE sales_id=salesid;END;// 调用CALL p_updatesales(30,100,'张三1',6,5);
调用结果：
// 删除DROP PROCEDURE IF EXISTS p_deletesales;CREATE PROCEDURE p_deletesales(salesid int(20))BEGIN    DELETE FROM tb_sales WHERE sales_id=salesid; END;// 调用CALL p_deletesales(5);
调用结果：
2、编写触发器，要求销售单的数据一旦插入则库存随之变化
// 插入CREATE TRIGGER t_insertsalesAFTER INSERT ON tb_sales FOR EACH ROWBEGIN    DECLARE goodsid INT(20);    DECLARE goodsnumber INT(50);    SELECT goods_id INTO goodsid FROM tb_sales WHERE sales_id=new.sales_id;    SELECT sales_num INTO goodsnumber FROM tb_sales WHERE sales_id=new.sales_id;    UPDATE tb_goods SET goods_number=goods_number-goodsnumber WHERE goods_id=goodsid;END;// 调用LOCK TABLE tb_sales WRITE;CALL p_insertsales(18);// 查询UNLOCK TABLE;SELECT * FROM TB_SALES;SELECT * FROM TB_GOODS;
查询结果：
// 修改CREATE TRIGGER t_updatesalesAFTER UPDATE ON tb_sales FOR EACH ROWBEGIN    DECLARE goodsid INT(20);    DECLARE goodsnumber INT(50);    DECLARE goodsnumberold INT(50);    SELECT goods_id INTO goodsid FROM tb_sales WHERE sales_id=new.sales_id;    SELECT sales_num INTO goodsnumber FROM tb_sales WHERE sales_id=new.sales_id;    SELECT old.sales_num INTO goodsnumberold FROM tb_sales WHERE sales_id=old.sales_id;    UPDATE tb_goods SET goods_number=goods_number-(goodsnumber-goodsnumberold) WHERE goods_id=goodsid;END;// 调用LOCK TABLE tb_sales WRITE;CALL p_updatesales(30,100,'张三1',6,8);// 查询UNLOCK TABLE;SELECT * FROM TB_SALES;SELECT * FROM TB_GOODS;
查询结果：
// 删除CREATE TRIGGER t_deletesalesAFTER DELETE ON tb_sales FOR EACH ROWBEGIN    UPDATE tb_goods SET goods_number=goods_number+old.sales_num  WHERE goods_id=old.goods_id;END;// 调用LOCK TABLE tb_sales WRITE;CALL p_deletesales(8);// 查询UNLOCK TABLE;SELECT * FROM TB_SALES;SELECT * FROM TB_GOODS;
查询结果：
3、能够对销售信息，根据商品名称、商品类别、销售时间（开始、结束）、以及价格进行查询（存储过程）
DROP PROCEDURE IF EXISTS p_saledetails;CREATE PROCEDURE p_saledetails(goodsname VARCHAR(50),categoryname VARCHAR(50),starttime TIMESTAMP,endtime TIMESTAMP,minprice DOUBLE,maxprice DOUBLE)BEGIN    DECLARE querysql varchar(5000);    DECLARE goodssql varchar(1000);    DECLARE categorysql varchar(1000);    DECLARE timesql varchar(1000);    DECLARE pricesql varchar(1000);    SET querysql='select * from tb_sales where 1=1 ';    IF(goodsname IS NOT NULL) THEN        SET goodssql=ConCAT(' and goods_id in(select goods_id from tb_goods where goods_name like ''%',goodsname,'%'') ');        SET querysql=ConCAT(querysql,goodssql);    END IF;    IF(categoryname IS NOT NULL) THEN        SET categorysql=ConCAT(' and goods_id in(select goods_id from tb_goods where category_id in(select category_id from tb_category where category_name like ''%',categoryname,'%'')) ');        SET querysql=ConCAT(querysql,categorysql);    END IF;    IF(starttime IS NOT NULL)AND(endtime IS NOT NULL) THEN        SET timesql=ConCAT(' and sales_date between ''',starttime,''' and ''',endtime,''' ');        SET querysql=ConCAT(querysql,timesql);    END IF;    IF(minprice IS NOT NULL)AND(maxprice IS NOT NULL) THEN        SET pricesql=ConCAT(' and sales_price between ',minprice,' and ',maxprice,' ');        SET querysql=ConCAT(querysql,pricesql);    END IF;    SET @querysql=querysql;    PREPARE querysqltemp FROM @querysql;    EXECUTE querysqltemp;    DEALLOCATE PREPARE querysqltemp;END;// 调用CALL p_saledetails('','','2016-09-09','2016-09-28',0,100);
调用结果：
F、退货管理
1、能够实现对退货单的编辑，包括增、删、改（存储过程）
// 插入DROP PROCEDURE IF EXISTS p_addreturn;CREATE PROCEDURE p_addreturn(returnnum INT(50),returnsum DOUBLE,returnperson VARCHAR(50),goodsid INT(20),salesid INT(20))BEGIN    INSERT INTO tb_return(return_num,return_sum,return_person,goods_id,sales_id)    VALUES(returnnum,returnsum,returnperson,goodsid,salesid);END;// 调用CALL p_addreturn(1,47,'李四',2,2);
调用结果：
// 修改DROP PROCEDURE IF EXISTS p_updatereturn;CREATE PROCEDURE p_updatereturn(returnnum INT(50),returnsum DOUBLE,returnperson VARCHAR(50),goodsid INT(20),salesid INT(20),returnid INT(20))BEGIN    UPDATE tb_return SET return_num=returnnum,return_sum=returnsum,return_person=returnperson,goods_id=goodsid,sales_id=salesid    WHERE return_id=returnid;END;// 调用CALL p_updatereturn(2,2*47,'李四',2,2,3);
调用结果：
// 删除DROP PROCEDURE IF EXISTS p_deletereturn;CREATE PROCEDURE p_deletereturn(returnid INT(20))BEGIN    DELETE FROM tb_return WHERE return_id=returnid;END;// 调用CALL p_deletereturn(3);
调用结果：
2、编写触发器，要求退货单的数据一旦插入则库存随之变化
// 插入CREATE TRIGGER t_insertreturnAFTER INSERT ON tb_return FOR EACH ROWBEGIN    DECLARE goodsid INT(20);    DECLARE goodsnumber INT(50);    SELECT goods_id INTO goodsid FROM tb_return WHERE return_id=new.return_id;    SELECT return_num INTO goodsnumber FROM tb_return WHERE return_id=new.return_id;    UPDATE tb_goods SET goods_number=goods_number+goodsnumber WHERE goods_id=goodsid;END;// 调用LOCK TABLE tb_sales WRITE;CALL p_addreturn(1,47,'李四',2,2);// 查询UNLOCK TABLE;SELECT * FROM TB_RETURN;SELECT * FROM TB_GOODS;
查询结果：
// 修改CREATE TRIGGER t_updatereturnAFTER UPDATE ON tb_return FOR EACH ROWBEGIN    DECLARE goodsid INT(20);    DECLARE goodsnumber INT(50);    DECLARE goodsnumberold INT(50);    SELECT goods_id INTO goodsid FROM tb_return WHERE return_id=new.return_id;    SELECT return_num INTO goodsnumber FROM tb_return WHERE return_id=new.return_id;    SELECT old.return_num INTO goodsnumberold FROM tb_return WHERE return_id=old.return_id;    UPDATE tb_goods SET goods_number=goods_number+(goodsnumber-goodsnumberold) WHERE goods_id=goodsid;END;// 调用LOCK TABLE tb_return WRITE;CALL p_updatereturn(2,2*47,'李四',2,2,4);// 查询UNLOCK TABLE;SELECT * FROM TB_RETURN;SELECT * FROM TB_GOODS;
查询结果：
// 删除CREATE TRIGGER t_deletereturnAFTER DELETE ON tb_return FOR EACH ROWBEGIN    UPDATE tb_goods SET goods_number=goods_number-old.return_num WHERE goods_id=old.goods_id;END;// 调用LOCK TABLE tb_return WRITE;CALL p_deletereturn(4);// 查询UNLOCK TABLE;SELECT * FROM TB_RETURN;SELECT * FROM TB_GOODS;
查询结果：
3、能够对退货信息，根据商品名称、商品类别、退货时间（开始、结束）、以及价格进行查询（存储过程）
DROP PROCEDURE IF EXISTS p_returndetails;CREATE PROCEDURE p_returndetails(goodsname VARCHAR(50),categoryname VARCHAR(50), starttime timestamp, endtime timestamp, minprice double, maxprice double)BEGIN    DECLARE querysql VARCHAR(5000);    DECLARE goodsnamesql VARCHAR(1000);    DECLARE categorynamesql VARCHAR(1000);    DECLARE timesql VARCHAR(1000);    DECLARE pricesql VARCHAR(1000);    SET querysql='select * from tb_return where 1=1 ';    IF(goodsname IS NOT NULL) THEN        SET goodsnamesql=ConCAT(' and goods_id in(select goods_id from tb_goods where goods_name like ''%',goodsname,'%'') ');        set querysql=ConCAT(querysql,goodsnamesql);    END IF;    IF(categoryname IS NOT NULL) THEN        SET categorynamesql=ConCAT(' and goods_id in(select goods_id from tb_goods where category_id in(select category_id from tb_category where category_name like ''%',categoryname,'%''))');        SET querysql=ConCAT(querysql,categorynamesql);    END IF;    IF(starttime IS NOT NULL)AND(endtime IS NOT NULL) THEN        SET timesql=ConCAT(' and return_date between ''',starttime,''' and ''',endtime,''' ');        SET querysql=ConCAT(querysql,timesql);    END IF;    IF(minprice IS NOT NULL)AND(maxprice IS NOT NULL) THEN        SET pricesql=ConCAT(' and return_sum between ',minprice,' and ',maxprice,' ');        SET querysql=ConCAT(querysql,pricesql);    END IF;    SET @querysql=querysql;    PREPARE querysqltemp FROM @querysql;    EXECUTE querysqltemp;    DEALLOCATE PREPARE querysqltemp;END;// 调用CALL p_returndetails('白','菜','2016-09-09','2016-09-30',0,30);
调用结果：
G、统计模块
1、能够统计某个时间段之内（开始时间及结束时间）各个商品类别的销售金额；（存储过程）
DROP PROCEDURE IF EXISTS p_salessum;CREATE PROCEDURE p_salessum(starttime timestamp,endtime timestamp)BEGIN    SELECT x.category_id,category_name,总金额 FROM    (        SELECT category_id,sum(sales_sum) 总金额 FROM tb_sales a JOIN tb_goods b ON a.goods_id=b.goods_id WHERE sales_date        BETWEEN starttime AND endtime GROUP BY b.category_id    ) x     JOIN tb_category y ON x.category_id=y.category_id;END;// 调用call p_salessum('2016-09-09','2016-09-30');
调用结果：
2、能够统计某个时间段之内（开始时间及结束时间）各个商品类别的进货金额；（存储过程）
DROP PROCEDURE IF EXISTS p_purchasesum;CREATE PROCEDURE p_purchasesum(starttime timestamp,endtime timestamp)BEGIN    SELECT x.category_id,category_name,总金额 FROM    (        SELECT category_id,sum(purchase_sum) 总金额 FROM tb_purchase a JOIN tb_goods b ON a.goods_id=b.goods_id WHERE purchase_date        BETWEEN starttime AND endtime GROUP BY b.category_id    ) x     JOIN tb_category y ON x.category_id=y.category_id;END;// 调用call p_purchasesum('2016-09-09','2016-09-30');
调用结果：

3、能够根据年份求出每个商品类别在该年内12个月的销售金额；（存储过程）
DROP PROCEDURE IF EXISTS p_yearsales;CREATE PROCEDURE p_yearsales(inyear int)BEGIN    SELECT x.category_id,category_name,总金额 FROM     (        SELECT category_id,sum(sales_sum) 总金额 FROM tb_sales a JOIN tb_goods b on a.goods_id=b.goods_id where DATE_FORMAT(a.sales_date,'%Y')=inyear         GROUP BY b.category_id    ) x     JOIN tb_category y on x.category_id=y.category_id;END;// 调用call p_yearsales(2016);
调用结果：
4、能够根据年份求出每个商品类别在该年内12个月的进货金额；（存储过程）
DROP PROCEDURE IF EXISTS p_yearpurchase;CREATE PROCEDURE p_yearpurchase(inyear int)BEGIN    SELECT x.category_id,category_name,总金额 FROM     (        SELECT b.category_id,sum(purchase_sum) 总金额 FROM tb_purchase a JOIN tb_goods b ON a.goods_id=b.goods_id WHERE DATE_FORMAT(purchase_date,'%Y')=inyear         GROUP BY b.category_id    ) x  JOIN tb_category y ON x.category_id=y.category_id;END;// 调用CALL p_yearpurchase(2016);
调用结果：
5、能够根据进货表、销售表、退货表三表联合统计处商品的库存信息，与库存表做匹配一旦数据不对则显示该数据；（存储过程）
DROP PROCEDURE IF EXISTS p_prompt;CREATE PROCEDURE p_prompt()BEGIN    SELECT t.goods_id '商品编号',goods_name '商品名',purchase_num '进货量',sales_num '销售量',return_num '退货量',stock_num '应有库存',goods_number '实际库存' FROM    (        SELECT goods_id,purchase_num,sales_num,return_num,purchase_num-sales_num+return_num stock_num FROM         (            SELECT IFNULL(IFNULL(x.goods_id,y.goods_id),z.goods_id)goods_id,IFNULL(purchase_num,0)purchase_num,IFNULL(sales_num,0)sales_num,IFNULL(return_num,0)return_num from             (                SELECT goods_id,sum(purchase_num)purchase_num FROM tb_purchase GROUP BY goods_id            ) x LEFT JOIN            (                SELECT goods_id,sum(sales_num)sales_num FROM tb_sales GROUP BY goods_id            ) y ON x.goods_id=y.goods_id LEFT JOIN             (                SELECT goods_id,sum(return_num)return_num FROM tb_return GROUP BY goods_id            ) z ON y.goods_id=z.goods_id            UNION            SELECT IFNULL(IFNULL(x.goods_id,y.goods_id),z.goods_id)goods_id,IFNULL(purchase_num,0)purchase_num,IFNULL(sales_num,0)sales_num,IFNULL(return_num,0)return_num from             (                SELECT goods_id,sum(purchase_num)purchase_num FROM tb_purchase GROUP BY goods_id            ) x RIGHT JOIN            (                SELECT goods_id,sum(sales_num)sales_num FROM tb_sales GROUP BY goods_id            ) y ON x.goods_id=y.goods_id RIGHT JOIN             (                SELECT goods_id,sum(return_num)return_num FROM tb_return GROUP BY goods_id            ) z ON y.goods_id=z.goods_id        )x    ) t JOIN tb_goods y ON t.goods_id=y.goods_id WHERE stock_num!=goods_number;END;// 调用call p_prompt();
调用结果：
6、求某个时间段之内各个商品类别的销售金额以及进货金额；（存储过程）
DROP PROCEDURE IF EXISTS p_profit;CREATE PROCEDURE p_profit(starttime TIMESTAMP, endtime TIMESTAMP)BEGIN    SELECT x.category_id,y.category_name,z.salessum,x.purchasesum,(z.salessum-x.purchasesum) profit FROM     (        SELECT category_id,sum(purchase_sum) purchasesum FROM tb_purchase a JOIN tb_goods b ON a.goods_id=b.goods_id WHERE purchase_date        BETWEEN starttime AND endtime GROUP BY b.category_id    ) x,    (        SELECT category_id,sum(sales_sum) salessum FROM tb_sales a JOIN tb_goods b ON a.goods_id=b.goods_id WHERE sales_date        BETWEEN starttime AND endtime GROUP BY b.category_id    ) z ,    tb_category y WHERE x.category_id=y.category_id AND z.category_id=y.category_id;END;// 调用call p_profit('2016-09-09','2016-09-30');call p_profit('2016-09-09 00:00:00','2016-09-30 00:00:00');
调用结果：