/*
 Navicat Premium Data Transfer

 Source Server         : mysql
 Source Server Type    : MySQL
 Source Server Version : 50726
 Source Host           : localhost:3306
 Source Schema         : xsglxt

 Target Server Type    : MySQL
 Target Server Version : 50726
 File Encoding         : 65001

 Date: 17/12/2021 12:16:15
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for ad
-- ----------------------------
DROP TABLE IF EXISTS `ad`;
CREATE TABLE `ad`  (
  `Ano` char(11) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '编号',
  `Aaccount` char(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '账号',
  `Apassword` char(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '密码',
  PRIMARY KEY (`Ano`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of ad
-- ----------------------------
INSERT INTO `ad` VALUES ('0', 'admin', 'admin');

-- ----------------------------
-- Table structure for book
-- ----------------------------
DROP TABLE IF EXISTS `book`;
CREATE TABLE `book`  (
  `Bno` char(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '编号',
  `Wno` char(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '销售人员工号',
  `Cno` char(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '客户编号',
  `Gno` char(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '商品编号',
  `Bnum` int(11) NOT NULL COMMENT '购买商品数量',
  `Bdate` date NOT NULL COMMENT '订单日期',
  PRIMARY KEY (`Bno`, `Wno`, `Cno`, `Gno`) USING BTREE,
  INDEX `Wno`(`Wno`) USING BTREE,
  INDEX `Cno`(`Cno`) USING BTREE,
  INDEX `Gno`(`Gno`) USING BTREE,
  CONSTRAINT `book_ibfk_1` FOREIGN KEY (`Wno`) REFERENCES `worker` (`Wno`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `book_ibfk_2` FOREIGN KEY (`Cno`) REFERENCES `client` (`Cno`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `book_ibfk_3` FOREIGN KEY (`Gno`) REFERENCES `goods` (`Gno`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of book
-- ----------------------------
INSERT INTO `book` VALUES ('1', '258', 'c', '8', 30, '2021-07-02');
INSERT INTO `book` VALUES ('19', '147', 'b', '7', 40, '2021-07-02');
INSERT INTO `book` VALUES ('2', '258', 'b', '4', 20, '2021-05-12');
INSERT INTO `book` VALUES ('20', '147', 'b', '7', 40, '2021-07-02');
INSERT INTO `book` VALUES ('3', '369', 'a', '2', 10, '2021-02-01');
INSERT INTO `book` VALUES ('4', '258', 'a', '4', 50, '2021-05-12');
INSERT INTO `book` VALUES ('5', '147', 'a', '5', 30, '2021-06-16');
INSERT INTO `book` VALUES ('6', '369', 'b', '3', 40, '2021-05-14');
INSERT INTO `book` VALUES ('7', '369', 'c', '4', 20, '2021-06-17');
INSERT INTO `book` VALUES ('8', '369', 'b', '6', 4, '2021-06-17');

-- ----------------------------
-- Table structure for client
-- ----------------------------
DROP TABLE IF EXISTS `client`;
CREATE TABLE `client`  (
  `Cno` char(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '编号',
  `Cname` char(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '姓名',
  `Cphone` char(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '手机号',
  PRIMARY KEY (`Cno`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of client
-- ----------------------------
INSERT INTO `client` VALUES ('a', '王总', '14725836911');
INSERT INTO `client` VALUES ('b', '刘总', '12347586911');
INSERT INTO `client` VALUES ('c', '胡总', '36541234876');

-- ----------------------------
-- Table structure for goods
-- ----------------------------
DROP TABLE IF EXISTS `goods`;
CREATE TABLE `goods`  (
  `Gno` char(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '编号',
  `Sno` char(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '类别编号',
  `Gname` char(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '商品名',
  `Gpirce` char(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '价格',
  `Gnum` char(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '数量',
  PRIMARY KEY (`Gno`, `Sno`) USING BTREE,
  INDEX `Sno`(`Sno`) USING BTREE,
  INDEX `Gno`(`Gno`) USING BTREE,
  CONSTRAINT `goods_ibfk_1` FOREIGN KEY (`Sno`) REFERENCES `sort` (`Sno`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of goods
-- ----------------------------
INSERT INTO `goods` VALUES ('1', '1', '笔记本', '4500', '52');
INSERT INTO `goods` VALUES ('10', '3', '毛巾', '15', '630');
INSERT INTO `goods` VALUES ('11', '2', '猪肉', '20', '340');
INSERT INTO `goods` VALUES ('12', '1', '电视', '3400', '35');
INSERT INTO `goods` VALUES ('13', '3', '牙刷', '6', '350');
INSERT INTO `goods` VALUES ('2', '2', '西瓜', '15', '200');
INSERT INTO `goods` VALUES ('3', '2', '雪梨', '3', '450');
INSERT INTO `goods` VALUES ('4', '3', '牛仔裤', '99', '250');
INSERT INTO `goods` VALUES ('5', '3', '枕头', '50', '200');
INSERT INTO `goods` VALUES ('6', '1', '手机', '3900', '30');
INSERT INTO `goods` VALUES ('7', '2', '火龙果', '2', '220');
INSERT INTO `goods` VALUES ('8', '3', '火机', '4', '320');
INSERT INTO `goods` VALUES ('9', '1', '计算器', '20', '300');

-- ----------------------------
-- Table structure for sort
-- ----------------------------
DROP TABLE IF EXISTS `sort`;
CREATE TABLE `sort`  (
  `Sno` char(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '编号',
  `Sname` char(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '商品类别名称',
  PRIMARY KEY (`Sno`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of sort
-- ----------------------------
INSERT INTO `sort` VALUES ('1', '电脑数码');
INSERT INTO `sort` VALUES ('2', '食品生鲜');
INSERT INTO `sort` VALUES ('3', '日用百货');

-- ----------------------------
-- Table structure for worker
-- ----------------------------
DROP TABLE IF EXISTS `worker`;
CREATE TABLE `worker`  (
  `Wno` char(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '工号',
  `Wpassword` char(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '密码',
  `Wname` char(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '姓名',
  `Wsex` char(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '性别',
  PRIMARY KEY (`Wno`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of worker
-- ----------------------------
INSERT INTO `worker` VALUES ('123', '123', '浩重', '女');
INSERT INTO `worker` VALUES ('147', '147', '张三', '男');
INSERT INTO `worker` VALUES ('258', '258', '李四', '男');
INSERT INTO `worker` VALUES ('357', '357', '张博', '女');
INSERT INTO `worker` VALUES ('369', '369', '王五', '女');

-- ----------------------------
-- View structure for v_book
-- ----------------------------
DROP VIEW IF EXISTS `v_book`;
CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `v_book` AS select `book`.`Bno` AS `Bno`,`worker`.`Wname` AS `Wname`,`client`.`Cname` AS `Cname`,`v_goods`.`Sname` AS `Sname`,`v_goods`.`Gname` AS `Gname`,`book`.`Bnum` AS `Bnum`,`book`.`Bdate` AS `Bdate`,`v_book_cash`.`cash` AS `cash` from ((((`book` join `worker`) join `client`) join `v_goods`) join `v_book_cash`) where ((`book`.`Cno` = `client`.`Cno`) and (`book`.`Wno` = `worker`.`Wno`) and (`book`.`Gno` = `v_goods`.`Gno`) and (`book`.`Bno` = `v_book_cash`.`Bno`));

-- ----------------------------
-- Records of worker
-- ----------------------------
INSERT INTO `worker` VALUES ('1', '李四', '胡总', '日用百货', '火机', 30, '2021-07-02', 120);
INSERT INTO `worker` VALUES ('19', '张三', '刘总', '食品生鲜', '火龙果', 40, '2021-07-02', 80);
INSERT INTO `worker` VALUES ('2', '李四', '刘总', '日用百货', '牛仔裤', 20, '2021-05-12', 1980);
INSERT INTO `worker` VALUES ('20', '张三', '刘总', '食品生鲜', '火龙果', 40, '2021-07-02', 80);
INSERT INTO `worker` VALUES ('3', '王五', '王总', '食品生鲜', '西瓜', 10, '2021-02-01', 150);
INSERT INTO `worker` VALUES ('4', '李四', '王总', '日用百货', '牛仔裤', 50, '2021-05-12', 4950);
INSERT INTO `worker` VALUES ('5', '张三', '王总', '日用百货', '枕头', 30, '2021-06-16', 1500);
INSERT INTO `worker` VALUES ('6', '王五', '刘总', '食品生鲜', '雪梨', 40, '2021-05-14', 120);
INSERT INTO `worker` VALUES ('7', '王五', '胡总', '日用百货', '牛仔裤', 20, '2021-06-17', 1980);
INSERT INTO `worker` VALUES ('8', '王五', '刘总', '电脑数码', '手机', 4, '2021-06-17', 15600);

-- ----------------------------
-- View structure for v_book_cash
-- ----------------------------
DROP VIEW IF EXISTS `v_book_cash`;
CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `v_book_cash` AS select `book`.`Bno` AS `Bno`,(`book`.`Bnum` * `goods`.`Gpirce`) AS `cash` from (`book` join `goods`) where (`book`.`Gno` = `goods`.`Gno`);

-- ----------------------------
-- Records of worker
-- ----------------------------
INSERT INTO `worker` VALUES ('1', 120);
INSERT INTO `worker` VALUES ('19', 80);
INSERT INTO `worker` VALUES ('2', 1980);
INSERT INTO `worker` VALUES ('20', 80);
INSERT INTO `worker` VALUES ('3', 150);
INSERT INTO `worker` VALUES ('4', 4950);
INSERT INTO `worker` VALUES ('5', 1500);
INSERT INTO `worker` VALUES ('6', 120);
INSERT INTO `worker` VALUES ('7', 1980);
INSERT INTO `worker` VALUES ('8', 15600);

-- ----------------------------
-- View structure for v_goods
-- ----------------------------
DROP VIEW IF EXISTS `v_goods`;
CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `v_goods` AS select `goods`.`Gno` AS `Gno`,`goods`.`Sno` AS `Sno`,`sort`.`Sname` AS `Sname`,`goods`.`Gname` AS `Gname`,`goods`.`Gpirce` AS `Gpirce`,`goods`.`Gnum` AS `Gnum` from (`goods` join `sort`) where (`goods`.`Sno` = `sort`.`Sno`);

-- ----------------------------
-- Records of worker
-- ----------------------------
INSERT INTO `worker` VALUES ('1', '1', '电脑数码', '笔记本', '4500', '52');
INSERT INTO `worker` VALUES ('12', '1', '电脑数码', '电视', '3400', '35');
INSERT INTO `worker` VALUES ('6', '1', '电脑数码', '手机', '3900', '30');
INSERT INTO `worker` VALUES ('9', '1', '电脑数码', '计算器', '20', '300');
INSERT INTO `worker` VALUES ('11', '2', '食品生鲜', '猪肉', '20', '340');
INSERT INTO `worker` VALUES ('2', '2', '食品生鲜', '西瓜', '15', '200');
INSERT INTO `worker` VALUES ('3', '2', '食品生鲜', '雪梨', '3', '450');
INSERT INTO `worker` VALUES ('7', '2', '食品生鲜', '火龙果', '2', '220');
INSERT INTO `worker` VALUES ('10', '3', '日用百货', '毛巾', '15', '630');
INSERT INTO `worker` VALUES ('13', '3', '日用百货', '牙刷', '6', '350');
INSERT INTO `worker` VALUES ('4', '3', '日用百货', '牛仔裤', '99', '250');
INSERT INTO `worker` VALUES ('5', '3', '日用百货', '枕头', '50', '200');
INSERT INTO `worker` VALUES ('8', '3', '日用百货', '火机', '4', '320');

-- ----------------------------
-- View structure for v_sale_cash
-- ----------------------------
DROP VIEW IF EXISTS `v_sale_cash`;
CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `v_sale_cash` AS select `goods`.`Gno` AS `Gno`,`goods`.`Gname` AS `Gname`,(`goods`.`Gpirce` * `v_sale_num`.`salenum`) AS `cash` from (`v_sale_num` join `goods`) where (`v_sale_num`.`Gno` = `goods`.`Gno`);

-- ----------------------------
-- Records of worker
-- ----------------------------
INSERT INTO `worker` VALUES ('2', '西瓜', 150);
INSERT INTO `worker` VALUES ('3', '雪梨', 120);
INSERT INTO `worker` VALUES ('4', '牛仔裤', 8910);
INSERT INTO `worker` VALUES ('5', '枕头', 1500);
INSERT INTO `worker` VALUES ('6', '手机', 15600);
INSERT INTO `worker` VALUES ('7', '火龙果', 160);
INSERT INTO `worker` VALUES ('8', '火机', 120);

-- ----------------------------
-- View structure for v_sale_num
-- ----------------------------
DROP VIEW IF EXISTS `v_sale_num`;
CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `v_sale_num` AS select `book`.`Gno` AS `Gno`,sum(`book`.`Bnum`) AS `salenum` from `book` group by `book`.`Gno`;

-- ----------------------------
-- Records of worker
-- ----------------------------
INSERT INTO `worker` VALUES ('2', 10);
INSERT INTO `worker` VALUES ('3', 40);
INSERT INTO `worker` VALUES ('4', 90);
INSERT INTO `worker` VALUES ('5', 30);
INSERT INTO `worker` VALUES ('6', 4);
INSERT INTO `worker` VALUES ('7', 80);
INSERT INTO `worker` VALUES ('8', 30);

-- ----------------------------
-- View structure for v_sale_numandcash
-- ----------------------------
DROP VIEW IF EXISTS `v_sale_numandcash`;
CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `v_sale_numandcash` AS select `xsglxt`.`goods`.`Gname` AS `name`,`a`.`sb` AS `salenum`,(`a`.`sb` * `xsglxt`.`goods`.`Gpirce`) AS `cash` from (`xsglxt`.`goods` join (select `xsglxt`.`book`.`Gno` AS `Gno`,sum(`xsglxt`.`book`.`Bnum`) AS `sb` from `xsglxt`.`book` group by `xsglxt`.`book`.`Gno`) `a` on((`xsglxt`.`goods`.`Gno` = `a`.`Gno`)));

-- ----------------------------
-- Records of worker
-- ----------------------------
INSERT INTO `worker` VALUES ('西瓜', 10, 150);
INSERT INTO `worker` VALUES ('雪梨', 40, 120);
INSERT INTO `worker` VALUES ('牛仔裤', 90, 8910);
INSERT INTO `worker` VALUES ('枕头', 30, 1500);
INSERT INTO `worker` VALUES ('手机', 4, 15600);
INSERT INTO `worker` VALUES ('火龙果', 80, 160);
INSERT INTO `worker` VALUES ('火机', 30, 120);

-- ----------------------------
-- View structure for v_worker_cash
-- ----------------------------
DROP VIEW IF EXISTS `v_worker_cash`;
CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `v_worker_cash` AS select `worker`.`Wname` AS `name`,sum((`book`.`Bnum` * `goods`.`Gpirce`)) AS `cash` from ((`worker` join `book`) join `goods`) where ((`book`.`Gno` = `goods`.`Gno`) and (`worker`.`Wno` = `book`.`Wno`)) group by `worker`.`Wno`;

-- ----------------------------
-- Records of worker
-- ----------------------------
INSERT INTO `worker` VALUES ('张三', 1660);
INSERT INTO `worker` VALUES ('李四', 7050);
INSERT INTO `worker` VALUES ('王五', 17850);

-- ----------------------------
-- Procedure structure for bookadd
-- ----------------------------
DROP PROCEDURE IF EXISTS `bookadd`;
delimiter ;;
CREATE PROCEDURE `bookadd`(IN Bno CHAR(50),
IN Wno CHAR(50),
IN Cno CHAR(50),
IN Gnos CHAR(50),
IN Bnum INT,
IN Bdate DATE)
BEGIN 
INSERT INTO book VALUES(Bno,Wno,Cno,Gnos,Bnum,Bdate);
UPDATE goods SET Gnum=Gnum-Bnum WHERE Gno = Gnos;
END
;;
delimiter ;

-- ----------------------------
-- Procedure structure for bookremove
-- ----------------------------
DROP PROCEDURE IF EXISTS `bookremove`;
delimiter ;;
CREATE PROCEDURE `bookremove`(IN Bnos CHAR(50),
IN lastGnoName CHAR(50),
IN lastnum INT)
BEGIN
DELETE FROM book WHERE Bno=Bnos;
UPDATE goods SET Gnum=Gnum+lastnum WHERE Gname=lastGnoName;
END
;;
delimiter ;

-- ----------------------------
-- Procedure structure for bookupdate
-- ----------------------------
DROP PROCEDURE IF EXISTS `bookupdate`;
delimiter ;;
CREATE PROCEDURE `bookupdate`(IN Bnos CHAR(50),
IN Cnos CHAR(50),
IN lastGno CHAR(50),
IN Gnos CHAR(50),
IN lastnum INT,
IN Bnums INT)
BEGIN
UPDATE goods SET Gnum=Gnum+lastnum WHERE Gno = lastGno;
UPDATE book SET Cno=Cnos,Gno=Gnos,Bnum=Bnums WHERE Bno=Bnos;
UPDATE goods SET Gnum=Gnum-Bnums WHERE Gno = Gnos;
END
;;
delimiter ;

SET FOREIGN_KEY_CHECKS = 1;