MySQL知识——基本知识

最新推荐文章于 2023-04-22 11:17:41 发布

java成神之路123

最新推荐文章于 2023-04-22 11:17:41 发布

阅读量300

点赞数

文章标签： mysql

本文链接：https://blog.csdn.net/m0_51392607/article/details/124473991

版权

本文展示了如何设计和管理一个收银系统的数据库，包括供应商、货品、库存、会员、收银员等表的创建，并定义了拼音缩写函数、触发器、存储过程以支持数据的增删改查和退货操作。此外，还实现了扎帐功能，确保财务准确无误。

摘要由CSDN通过智能技术生成

CREATE DATABASE mypos01 CHARSET utf8mb4;
USE mypos01;
CREATE TABLE `tb_gysxxb` (
`gysid` INT NOT NULL AUTO_INCREMENT COMMENT '供应商id',
`gysmc` VARCHAR(50) DEFAULT NULL COMMENT '供应商名称',
`lxren` VARCHAR(50) DEFAULT NULL COMMENT '联系人',
`lxdh` VARCHAR(50) DEFAULT NULL COMMENT '联系电话',
`gyszt` TINYINT DEFAULT '0' COMMENT '供应商状态(0 正常往来, 1 暂停往来, 2 不在往来)',
PRIMARY KEY (`gysid`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `tb_hpxxb` (
`hpid` INT NOT NULL AUTO_INCREMENT COMMENT '货品信息表id',
`hpmc` VARCHAR(50) NOT NULL COMMENT '货品名称',
`hptm` VARCHAR(20) NOT NULL COMMENT '货品条码',
`jldw` VARCHAR(20) DEFAULT NULL COMMENT '计量单位',
`hpzt` TINYINT UNSIGNED DEFAULT '0' COMMENT '0正常1不在进货2不再出售',
`dj` DECIMAL(18,3) DEFAULT NULL COMMENT '定价',
`cxj` DECIMAL(18,3) DEFAULT NULL COMMENT '促销价',
`mcsx` VARCHAR(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '名称缩写', PRIMARY KEY (`hpid`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 ;

CREATE TABLE `tb_hpkcb` (
`kcid` INT NOT NULL AUTO_INCREMENT COMMENT '库存id',
`hpid` INT DEFAULT NULL COMMENT '货品id(用来代表货品)',
`kcsl` DECIMAL(18,3) DEFAULT '0.000' COMMENT '(当前)库存数量',
`zrkc` DECIMAL(18,3) DEFAULT '0.000' COMMENT '昨日库存(用于日清)',
`yckc` DECIMAL(18,3) DEFAULT '0.000' COMMENT '月初库存(用于月结)',
PRIMARY KEY (`kcid`),
KEY `hpid` (`hpid`),
CONSTRAINT `tb_hpkcb_ibfk_1` FOREIGN KEY (`hpid`) REFERENCES `tb_hpxxb` (`hpid`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 ;

CREATE TABLE `tb_hyxxb` (
`hyid` INT NOT NULL AUTO_INCREMENT COMMENT '会员信息表id，从10000开始',
`hyxm` VARCHAR(50) DEFAULT NULL COMMENT '会员姓名',
`hyxb` CHAR(2) DEFAULT NULL COMMENT '会员性别',
`hykh` VARCHAR(20) DEFAULT NULL COMMENT '会员卡号',
`sjhm` VARCHAR(20) DEFAULT NULL COMMENT '手机号码',
`knye` DECIMAL(9,2) DEFAULT NULL COMMENT '卡内余额',
`kyjf` INT DEFAULT NULL COMMENT '可用积分',
`scxfsj` DATETIME DEFAULT NULL COMMENT '上次消费时间',
PRIMARY KEY (`hyid`) USING BTREE
) ENGINE=INNODB AUTO_INCREMENT=10001 DEFAULT CHARSET=utf8mb4;

CREATE TABLE `tb_zzjlb` (
`zzid` INT NOT NULL AUTO_INCREMENT COMMENT '扎帐记录表',
`syyid` INT DEFAULT NULL COMMENT '收银员id',
`zzsj` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '扎帐时间，默认值为系统当前时间',
`ysje` DECIMAL(18,2) DEFAULT NULL COMMENT '应收金额(由系统计算)',
`ssje` DECIMAL(18,2) DEFAULT NULL COMMENT '实收金额(由收银员输入)',
`ccyy` VARCHAR(200) DEFAULT NULL COMMENT '差错原因',
PRIMARY KEY (`zzid`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `tb_syjlb` (
`syid` INT NOT NULL AUTO_INCREMENT COMMENT '收银记录表id',
`syyid` INT DEFAULT NULL COMMENT '收银员id',
`hyid` INT DEFAULT NULL COMMENT '顾客id 0 普通顾客, 1-9999 大客户 ,>=10000 会员',
`sysj` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '收银时间，默认值为系统当前时间',
`zffs` TINYINT DEFAULT NULL COMMENT '支付方式 0 现金, 1 储值卡, 2 支付宝, 3 微信支付',
`ysje` DECIMAL(18,2) DEFAULT NULL COMMENT '应收金额',
`ssje` DECIMAL(18,2) DEFAULT NULL COMMENT '实收金额',
`yhje` DECIMAL(18,2) DEFAULT NULL COMMENT '优惠金额',
`xszt` TINYINT DEFAULT '0' COMMENT '销售状态 0 正常 ,1 已退货',
`zzid` INT DEFAULT '0' COMMENT '扎帐id',
PRIMARY KEY (`syid`),
KEY `zzid` (`zzid`),
CONSTRAINT `fk_zzjlb_syjlb` FOREIGN KEY (`zzid`) REFERENCES `tb_zzjlb` (`zzid`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 ;

CREATE TABLE `tb_syjlmxb` (
`mxid` INT NOT NULL AUTO_INCREMENT COMMENT '收银明细id',
`syid` INT DEFAULT NULL COMMENT '收银员id',
`hpid` INT DEFAULT NULL COMMENT '货品id',
`xssl` DECIMAL(18,3) DEFAULT NULL COMMENT '销售数量',
`dj` DECIMAL(9,2) DEFAULT NULL COMMENT '定价',
`cxj` DECIMAL(9,2) DEFAULT NULL COMMENT '零售价（用来解决货品调价问题）',
PRIMARY KEY `mxid` (`mxid`),
CONSTRAINT `fk_syjlb_syjlmxb` FOREIGN KEY (`syid`) REFERENCES `tb_syjlb` (`syid`),
CONSTRAINT `fk_hpmcb_symxb` FOREIGN KEY(`hpid`)REFERENCES `tb_hpxxb`(`hpid`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `tb_yhxxb` (
`syyid` INT NOT NULL AUTO_INCREMENT COMMENT '收银员id',
`yhm` VARCHAR(50) NOT NULL COMMENT '收银员用户名',
`pwd` VARCHAR(20) NOT NULL COMMENT '密码',
`phone` VARCHAR(20) DEFAULT NULL COMMENT '收银员电话',
`syylb` INT DEFAULT NULL COMMENT '1 收银员\r\n2 经理\r\n3 服务台',
PRIMARY KEY (`syyid`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `hzpyb` (
`hz` VARCHAR(6) DEFAULT NULL,
`jp` VARCHAR(3) DEFAULT NULL,
`py` VARCHAR(30) DEFAULT NULL
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4;

/*拼音缩写相关函数*/
DELIMITER $$
CREATE
FUNCTION `mypos01`.`pysxcx`(zw VARCHAR(50))
RETURNS VARCHAR(10)
DETERMINISTIC
BEGIN
SET @pysx='';
SET @len=CHAR_LENGTH(zw);
SET @i=1;
WHILE @i<=@len DO
   SET @hz=SUBSTR(zw,@i,1);
   SELECT jp INTO @jp FROM hzpyb WHERE hz=@hz;
   SET @pysx=CONCAT(@pysx,@jp);
   SET @i=@i+1;
   END WHILE;
RETURN @pysx;
END$$
DELIMITER ;

/*插入tb_hpxxb之前触发触发器*/

DELIMITER $$

CREATE
TRIGGER `mypos01`.`update_mcsx_before_insert_tb_hpxxb` BEFORE INSERT
ON `mypos01`.`tb_hpxxb`
FOR EACH ROW BEGIN
SET new.mcsx=pysxcx(new.hpmc);
END$$

DELIMITER ;

/*修改tb_hpxxb之前触发触发器*/

DELIMITER $$

CREATE
TRIGGER `mypos01`.`update_mcsx_before_update_tb_hpxxb` BEFORE UPDATE
ON `mypos01`.`tb_hpxxb`
FOR EACH ROW BEGIN
SET new.mcsx=pysxcx(new.hpmc);
END$$

DELIMITER ;

/*tb_hpxxb增删改存储过程*/

DROP PROCEDURE IF EXISTS `mypos01`.`tb_hpxxbcp`;
DELIMITER $$
CREATE
PROCEDURE `mypos01`.`tb_hpxxbcp`(
hpid INT,
hpmc VARCHAR(50),
hptm VARCHAR(20),
jldw VARCHAR(20),
hpzt TINYINT,
dj DECIMAL(18,3),
cxj DECIMAL(18,3)
)
BEGIN
IF hpid=0 THEN
       INSERT INTO tb_hpxxb(hpmc,hptm,jldw,hpzt,dj,cxj) VALUES(hpmc,hptm,jldw,0,dj,cxj);
       SET @hpid=@@identity;
       INSERT INTO tb_hpkcb VALUES(NULL,@hpid,0,0,0);
ELSEIF hpid>0 THEN
       UPDATE tb_hpxxb SET tb_hpxxb.hpmc=hpmc,tb_hpxxb.hptm=hptm,tb_hpxxb.jldw=jldw,
       tb_hpxxb.hpzt=hpzt,tb_hpxxb.dj=dj,tb_hpxxb.cxj=cxj
       WHERE tb_hpxxb.hpid=hpid;
ELSE

DELETE FROM tb_hpkcb WHERE tb_hpkcb.hpid=-hpid;
DELETE FROM tb_hpxxb WHERE tb_hpxxb.hpid=-hpid;
END IF;
END$$

DELIMITER ;

/*货品表信息查询存储过程*/
DROP PROCEDURE IF EXISTS `mypos01`.`tb_hpxxbcx`;
DELIMITER $$

CREATE
PROCEDURE `mypos01`.`tb_hpxxbcx`(hpid INT)
BEGIN
IF hpid=0 THEN
SELECT * FROM tb_hpxxb ;
ELSE
SELECT * FROM tb_hpxxb WHERE tb_hpxxb.`hpid`=hpid ;
END IF;
END$$

DELIMITER ;

/*测试货品信息表的增删改*/
CALL tb_hpxxbcp(0,'康师傅方便面','1012','袋',0,1.5,1);
CALL tb_hpxxbcp(0,'百事可乐','1011','桶',0,3.5,3);
CALL tb_hpxxbcp(0,'涪陵榨菜','1013','袋',0,1.5,1);
CALL tb_hpxxbcp(0,'农夫山泉','1014','瓶',0,1.5,1);

/*修改*/
CALL tb_hpxxbcp(3,'涪陵榨菜','1013','袋',0,2.5,2);

/*删除*/
CALL tb_hpxxbcp(-1,'','','',0,0,0);
CALL tb_hpxxbcp(-2,'','','',0,0,0);
CALL tb_hpxxbcp(-3,'','','',0,0,0);
CALL tb_hpxxbcp(-4,'','','',0,0,0);

/*货品表的查询*/
CALL tb_hpxxbcx(0);

CALL tb_hpxxbcx(9);

DROP PROCEDURE IF EXISTS `mypos01`.`tb_syjlbcp`;
DELIMITER $$

CREATE
PROCEDURE `mypos01`.`tb_syjlbcp`(syyid INT,hyid INT,zffs TINYINT,ysje DECIMAL(18,2),
ssje DECIMAL(18,2),symx VARCHAR(8000) )
BEGIN
/*更新收银记录表*/
INSERT INTO tb_syjlb VALUES(NULL,syyid,hyid,NOW(),zffs,ysje,ssje,ysje-ssje,0,NULL);
SET @syid=@@identity;
/*更新tb_syjlmxb*/
/*j解析symx字符串形式：1,1,2,2,3,1, 形式*/
DROP TABLE IF EXISTS lsb;
CREATE TEMPORARY TABLE lsb(hpid INT,xssl DECIMAL(18,3));
WHILE symx>'' DO
SET @index=POSITION(',' IN symx);
SET @hpid=LEFT(symx,@index-1);
SET symx=SUBSTR(symx,@index+1);
SET @index=POSITION(',' IN symx);
SET @ssxl=LEFT(symx,@index-1);
SET symx=SUBSTR(symx,@index+1);
INSERT INTO lsb VALUES(@hpid,@ssxl);
END WHILE;
INSERT INTO tb_syjlmxb(syid,hpid,xssl,dj,cxj) SELECT @syid,lsb.hpid,xssl,dj,cxj FROM lsb,tb_hpxxb WHERE lsb.hpid=tb_hpxxb.hpid;

/*更新库存*/
DROP TABLE IF EXISTS lsb1;
CREATE TEMPORARY TABLE lsb1(hpid INT,xssl DECIMAL(18,3));
/*合并同类商品数量*/
INSERT INTO lsb1 SELECT hpid,SUM(xssl) FROM lsb GROUP BY hpid;
UPDATE tb_hpkcb b1,lsb1 b2 SET b1.kcsl=b1.kcsl-b2.xssl WHERE b1.hpid=b2.hpid;

/*更新会员信息*/
IF hyid>10000 THEN
UPDATE tb_hyxxb SET knye=knye-ssje ,kyjf=kyjf+FLOOR(ssje)
,scxfsj=NOW()
WHERE tb_hyxxb.`hyid`=hyid;
END IF;

END$$

DELIMITER ;
/*测试*/
/*插入几个会员信息*/

INSERT INTO tb_hyxxb VALUES(NULL,'张三','男','1000001','123454543',100,0,NULL);
INSERT INTO tb_hyxxb VALUES(NULL,'李四','男','1000002','123454543',100,0,NULL);
/*初始化库存*/
UPDATE tb_hpkcb SET kcsl=10;
/*销售商品*/
CALL tb_syjlbcp(1,10001,0,12.5,10,'1,1,2,2,1,1,3,1,');
/*分别查看4个表的变化*/

/*查询销售记录*/
SELECT syid,syyid, sysj,hyid,ysje,ssje,yhje,CASE zffs
WHEN 0 THEN '现金'
WHEN 1 THEN '储值卡'
WHEN 2 THEN '支付宝'
WHEN 3 THEN '微信支付'
END AS 支付方式
FROM tb_syjlb WHERE syyid= 1 AND sysj>=CURDATE() ORDER BY syid DESC;
/*创建视图v_xsjlmxb*/
DROP VIEW IF EXISTS `mpos1`.`v_xsjlmcb`;
CREATE
VIEW `mpos1`.`v_xsjlmcb`
AS
(SELECT mxid,hpmc,jldw ,xssl,tb_syjlmxb.dj,tb_syjlmxb.cxj FROM tb_syjlmxb,tb_hpxxb
WHERE tb_syjlmxb.`hpid`=tb_hpxxb.`hpid` );

/*创建查询销售记录及销售明细存储过程*/

DROP PROCEDURE IF EXISTS `mypos01`.`SyjlCx`;
DELIMITER $$
CREATE
PROCEDURE `mypos01`.`SyjlCx`(cxlb INT,cxcs INT)
BEGIN
IF cxlb=0 THEN
SELECT syid,syyid, sysj,hyid,ysje,ssje,yhje, CASE zffs
WHEN 0 THEN '现金'
WHEN 1 THEN '储值卡'
WHEN 2 THEN '支付宝'
WHEN 3 THEN '微信支付'
END
FROM tb_syjlb WHERE syyid= cxcs AND sysj>=CURDATE() ORDER BY syid DESC;
ELSE
SELECT * FROM v_xsjlmxb WHERE syid=cxcs;
END IF;
END$$
DELIMITER ;
/*验证存储过程*/
CALL SyjlCx(0,1);
CALL SyjlCx(1,5);

/*退货存储过程*/
DELIMITER $$

USE `mypos01`$$

DROP PROCEDURE IF EXISTS `Thjlcp`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `Thjlcp`(syid INT)
BEGIN
SELECT hyid INTO @hyid FROM tb_syjlb WHERE tb_syjlb.`syid`=syid;
SELECT ssje INTO @ssje FROM tb_syjlb WHERE tb_syjlb.`syid`=syid;
/*变更余额与积分*/
IF @hyid>10000 THEN
UPDATE tb_hyxxb SET knye=knye+@ssje,kyjf=kyjf-FLOOR(@ssje) WHERE tb_hyxxb.`hyid`=@hyid;
END IF;
/*还原库存 */
-- 先还原库存
DROP TABLE IF EXISTS lsb;
CREATE TEMPORARY TABLE lsb (hpid INT,xssl NUMERIC(18,3));
INSERT INTO lsb (hpid,xssl) SELECT hpid,SUM(xssl) FROM tb_syjlmxb WHERE `tb_syjlmxb`.`syid`=syid GROUP BY hpid ;
UPDATE tb_hpkcb b1,lsb b2 SET b1.kcsl=b1.kcsl+b2.xssl WHERE b1.hpid=b2.hpid;
DELETE FROM tb_syjlmxb WHERE tb_syjlmxb.`syid`=syid;
DELETE FROM tb_syjlb WHERE tb_syjlb.`syid`=syid;
END$$

DELIMITER ;
/*验证退货过程*/
CALL thjlcp(4);
/*用触发器实现退货*/
/*创建一个新的存储过程*/
DELIMITER $$
CREATE
PROCEDURE `mypos01`.`thjlcp_new`(syid INT)
BEGIN
DELETE FROM tb_syjlb WHERE tb_syjlb.syid=syid;
END$$
DELIMITER ;

/*在tb_syjlb上增加删除触发器，删除一条记录时，用触发器还原库存、会员积分，同时删除销售记录明细表*/

DROP TRIGGER IF EXISTS `mypos01`.`update_hpckb_hyxxb_delete_xsjlmxb_before_delete_syjlb` ;
DELIMITER $$
CREATE
/*[DEFINER = { user | CURRENT_USER }]*/
TRIGGER `mypos01`.`update_hpckb_hyxxb_delete_xsjlmxb_before_delete_syjlb`
BEFORE DELETE
ON `mypos01`.`tb_syjlb`
FOR EACH ROW BEGIN
/*变更余额与积分*/
IF old.hyid>10000 THEN
UPDATE tb_hyxxb SET knye=knye+old.ssje,kyjf=kyjf-FLOOR(old.ssje) WHERE tb_hyxxb.`hyid`=old.hyid;
END IF;
/*还原库存 */
-- 先还原库存
UPDATE tb_hpkcb b1,(SELECT hpid,SUM(xssl) AS xssl FROM tb_syjlmxb WHERE `tb_syjlmxb`.`syid`=old.syid GROUP BY hpid) b2
SET b1.kcsl=b1.kcsl+b2.xssl WHERE b1.hpid=b2.hpid;
DELETE FROM tb_syjlmxb WHERE tb_syjlmxb.`syid`=old.syid;
END$$
DELIMITER ;
/*验证存储过程*/
/*销售一笔*/
CALL tb_syjlbcp(1,10001,0,12.5,10,'1,1,2,2,1,1,3,1,');
CALL thjlcp_new(9)

/*事后退货*/
CREATE
PROCEDURE `mypos01`.`thjlcp_new`(syid INT)
BEGIN
IF syid>0 THEN /*当天退货*/
DELETE FROM tb_syjlb WHERE tb_syjlb.syid=syid;
ELSE /*时候退货*/
/*增加一条负销售*/
INSERT tb_syjlb(syyid,hyid,sysj,zffs,ysje,ssje,yhje,xszt,zzid) SELECT syyid,hyid,NOW(),zffs,-ysje,-ssje,-yhje,-1,zzid
FROM tb_syjlb WHERE tb_syjlb.syid=-syid;
SET @syid_new=@@identity;
/*修改销售状态，以避免多次退货*/
UPDATE tb_syjlb SET xszt=@syid_new WHERE tb_syjlb.syid=-syid;
/*在销售明细表中插入负销售*/
INSERT INTO tb_syjlmxb(syid,hpid,xssl,dj,cxj)
SELECT @syid_new,hpid,-xssl,dj,cxj FROM tb_syjlmxb WHERE tb_syjlmxb.`syid`=-syid;
/*变更库存*/
DROP TABLE IF EXISTS lsb;
CREATE TEMPORARY TABLE lsb (hpid INT,xssl NUMERIC(18,3));
INSERT INTO lsb (hpid,xssl) SELECT hpid,SUM(xssl) FROM tb_syjlmxb WHERE `tb_syjlmxb`.`syid`=-syid GROUP BY hpid ;
UPDATE tb_hpkcb b1,lsb b2 SET b1.kcsl=b1.kcsl+b2.xssl WHERE b1.hpid=b2.hpid;
/*如果是会员，还原积分*/
SELECT hyid INTO @hyid FROM tb_syjlb WHERE tb_syjlb.`syid`=-syid;
SELECT ssje INTO @ssje FROM tb_syjlb WHERE tb_syjlb.`syid`=-syid;
IF @hyid>10000 THEN
UPDATE tb_hyxxb SET knye=knye+@ssje,kyjf=kyjf-FLOOR(@ssje) WHERE tb_hyxxb.`hyid`=@hyid;
END IF;
END IF;
END$$
DELIMITER ;

/*验证存储过程
CALL thjlcp_new(-11)

DELIMITER $$
USE `mypos01`$$
DROP PROCEDURE IF EXISTS `ZzjlCp`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `ZzjlCp`(syyid INT,ssje DECIMAL(18,2))
BEGIN
-- 计算应收金额
SELECT SUM(`tb_syjlb`.`ssje`) INTO @yjje FROM tb_syjlb WHERE `tb_syjlb`.`syyid`=syyid AND zffs=0 AND zzid IS NULL;
-- 插入扎帐记录
IF ABS(@yjje-ssje)<0.01 THEN
INSERT INTO tb_zzjlb (syyid,ysje,ssje,ccyy) VALUE (syyid,@yjje,ssje,'无差错');
ELSE
INSERT INTO tb_zzjlb (syyid,ysje,ssje) VALUE (syyid,@yjje,ssje);
END IF;
SET @zzid=@@identity;
-- 更新收银记录表中的zzid;
UPDATE tb_syjlb SET zzid=@zzid WHERE `tb_syjlb`.`syyid`=syyid AND zzid IS NULL;
END$$
DELIMITER ;