POS系统开发

i.     SQLyog连接MySQL8.0之前需要先修改root的认证方式，否则会出2058的错误
     ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root';
ii.  创建函数或触发器前要先修变量的值
    SET GLOBAL log_bin_trust_function_creators=1;
   This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)
二、数据库及表的创建
CREATE TABLE `spmcb` (
  `spid` int NOT NULL AUTO_INCREMENT COMMENT '商品名称表',
  `spmc` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '货品名称',
  `sptm` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '货品条码',
  `dw` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '计量单位',
  `dj` decimal(9,2) DEFAULT NULL COMMENT '零售价',
  `spzt` tinyint DEFAULT '0' COMMENT '商品状态(0 正常, 1 下架)',
  `mcsx` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '名称缩写(由触发器自行维护)',
  PRIMARY KEY (`spid`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC;
CREATE TABLE `spkcb` (
  `kcid` int NOT NULL AUTO_INCREMENT COMMENT '库存id',
  `spid` 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`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC;
3、创建PysxCx()函数
SET GLOBAL log_bin_trust_function_creators=1;
DELIMITER $$
CREATE FUNCTION `lx1`.`PysxCx`(zw VARCHAR(20))
    RETURNS VARCHAR(10)
BEGIN
  SET @pysx='';
  SET @l=CHAR_LENGTH(zw);
  SET @i=1;
  WHILE (@i<=@l) DO
    SELECT jp INTO @jp FROM hzpyb WHERE hz=SUBSTR(zw,@i,1);
    SET @pysx=CONCAT(@PYSX,@JP);
    SET @I=@I+1;
  END WHILE;
  RETURN @PYSX;
END$$
DELIMITER ;
4、测试PysxCx()函数
    SELECT pysxcx('张三丰');
5、编写存储过程SpxxWh
DELIMITER $$
CREATE PROCEDURE `lx1`.`SpxxWh`(spid INT,spmc VARCHAR(50),sptm VARCHAR(20),dw VARCHAR(10),dj NUMERIC(9,2),spzt TINYINT)
BEGIN
  IF spid=0 THEN
    INSERT INTO spmcb (spmc,sptm,dw,dj) VALUE (spmc,sptm,dw,dj);
  ELSEIF spid>0 THEN
    UPDATE spmcb SET `spmcb`.`spmc`=spmc,`spmcb`.`sptm`=sptm,`spmcb`.`dw`=dw,`spmcb`.`dj`=dj,`spmcb`.`spzt`=spzt WHERE `spmcb`.`spid`=spid;
  ELSE
    DELETE FROM spmcb WHERE `spmcb`.`spid`=-spid;
  END IF;
END$$
DELIMITER ;
6、测试存储过程SpxxWh
i. 新增
CALL spxxwh(0,'百事可乐','101','瓶',3,0);
SELECT * FROM spmcb;
ii.修改
CALL spxxwh(4,'百事可乐','101','瓶',3.5,0);
SELECT * FROM spmcb;
iii.删除
CALL spxxwh(-4,'百事可乐','101','瓶',3.5,0);
SELECT * FROM spmcb;
6、编写spmcb上的触发器
i. insert before
DELIMITER $$
CREATE TRIGGER `lx1`.`Clmcsx_before_insert_spmcb` BEFORE INSERT ON `lx1`.`spmcb`
FOR EACH ROW BEGIN
  SET new.mcsx=pysxcx(new.spmc);
END$$
DELIMITER ;
验证触发器
ii. insert after
DELIMITER $$
CREATE TRIGGER `lx1`.`Insert_Spkcb_after_insert` AFTER INSERT ON `lx1`.`spmcb`
FOR EACH ROW BEGIN
  INSERT INTO spkcb (spid) VALUE (new.spid);
END$$
DELIMITER ;
验证触发器
iii update before
DELIMITER $$
CREATE TRIGGER `lx1`.`Update_mcsx_before_update_spmc` BEFORE UPDATE ON `lx1`.`spmcb`
FOR EACH ROW BEGIN
  IF (new.spmc<>old.spmc) THEN
    SET new.mcsx=pysxcx(new.spmc);
  END IF;
END$$
DELIMITER ;
验证触发器
iv delete after
DELIMITER $$
CREATE TRIGGER `lx1`.`Delete_spkcb_after_delete_spmcb` AFTER DELETE ON `lx1`.`spmcb`
FOR EACH ROW BEGIN
  DELETE FROM spkcb WHERE spid=old.spid;
END$$
DELIMITER ;
验证触发器
二、应用系统开发
 安装32位ODBC驱动
1. VC_redist.x86
2. mysql-connector-odbc-8.0.28-win32