CREATE DATABASE db_shop;

USE db_shop;

/*
 Navicat MySQL Data Transfer

 Source Server         : localhost_3306_1
 Source Server Type    : MySQL
 Source Server Version : 50718 (5.7.18-log)
 Source Host           : localhost:3306
 Source Schema         : db_shop

 Target Server Type    : MySQL
 Target Server Version : 50718 (5.7.18-log)
 File Encoding         : 65001

 Date: 29/03/2024 11:25:00
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for category
-- ----------------------------
DROP TABLE IF EXISTS `category`;
CREATE TABLE `category`  (
  `id` INT(11) NOT NULL,
  `name` VARCHAR(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `p_id` INT(11) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = MYISAM CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Records of category
-- ----------------------------

-- ----------------------------
-- Table structure for comment
-- ----------------------------
DROP TABLE IF EXISTS `comment`;
CREATE TABLE `comment`  (
  `id` INT(11) NOT NULL AUTO_INCREMENT COMMENT '评价编号',
  `goods_id` INT(11) UNSIGNED NOT NULL COMMENT '评价商品',
  `user_id` INT(11) UNSIGNED NOT NULL COMMENT '评价用户',
  `content` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT '评价内容',
  `add_time` DATETIME NULL DEFAULT NULL COMMENT '添加时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = INNODB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Records of comment
-- ----------------------------

-- ----------------------------
-- Table structure for goods
-- ----------------------------
DROP TABLE IF EXISTS `goods`;
CREATE TABLE `goods`  (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `type` VARCHAR(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
  `name` VARCHAR(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `price` DECIMAL(7, 2) UNSIGNED NULL DEFAULT NULL,
  `num` INT(11) NULL DEFAULT 0,
  `add_time` DATETIME NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `name`(`name`) USING BTREE
) ENGINE = INNODB AUTO_INCREMENT = 11 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Records of goods
-- ----------------------------
INSERT INTO `goods` VALUES (1, '书籍', '西游记', 50.40, 20, '2018-01-01 13:40:40');
INSERT INTO `goods` VALUES (2, '糖类', '牛奶糖', 7.50, 200, '2018-02-02 13:40:40');
INSERT INTO `goods` VALUES (3, '糖类', '水果糖', 2.50, 100, '2018-06-09 11:20:55');
INSERT INTO `goods` VALUES (4, '服饰', '休闲西服', 800.00, 10, '2018-04-04 13:40:40');
INSERT INTO `goods` VALUES (5, '饮品', '果汁', 2.50, 70, '2018-05-05 13:40:40');
INSERT INTO `goods` VALUES (6, '书籍', '论语', 109.00, 50, '2018-01-03 13:40:40');
INSERT INTO `goods` VALUES (7, '水果', '西瓜', 1.50, NULL, '2018-02-05 13:40:40');
INSERT INTO `goods` VALUES (8, '水果', '苹果', 3.00, 100, '2018-03-05 13:40:40');
INSERT INTO `goods` VALUES (9, '服饰', '牛仔裤', 120.00, 10, '2018-05-04 13:40:40');
INSERT INTO `goods` VALUES (10, '书籍', '红楼梦', 50.50, 15, '2018-05-06 13:40:40');

-- ----------------------------
-- Table structure for new_goods
-- ----------------------------
DROP TABLE IF EXISTS `new_goods`;
CREATE TABLE `new_goods`  (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `type` VARCHAR(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `name` VARCHAR(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `price` DECIMAL(7, 2) UNSIGNED NULL DEFAULT NULL,
  `num` INT(11) NULL DEFAULT 0,
  `add_time` DATETIME NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `name`(`name`) USING BTREE
) ENGINE = INNODB AUTO_INCREMENT = 7 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Records of new_goods
-- ----------------------------

-- ----------------------------
-- Table structure for orders
-- ----------------------------
DROP TABLE IF EXISTS `orders`;
CREATE TABLE `orders`  (
  `o_id` INT(11) NOT NULL,
  `add_time` DATETIME NULL DEFAULT NULL,
  `goods_id` INT(11) NULL DEFAULT NULL,
  PRIMARY KEY (`o_id`) USING BTREE,
  INDEX `goo_ord`(`goods_id`) USING BTREE,
  CONSTRAINT `goo_ord` FOREIGN KEY (`goods_id`) REFERENCES `goods` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = INNODB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Records of orders
-- ----------------------------

-- ----------------------------
-- Table structure for people
-- ----------------------------
DROP TABLE IF EXISTS `people`;
CREATE TABLE `people`  (
  `id` INT(11) NULL DEFAULT NULL,
  `name` VARCHAR(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `mobile` VARCHAR(11) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL
) ENGINE = INNODB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Records of people
-- ----------------------------

-- ----------------------------
-- Table structure for people1
-- ----------------------------
DROP TABLE IF EXISTS `people1`;
CREATE TABLE `people1`  (
  `id` INT(11) NULL DEFAULT NULL,
  `name` VARCHAR(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `age` TINYINT(4) NULL DEFAULT NULL,
  `status` TINYINT(4) NULL DEFAULT NULL,
  INDEX `n_index`(`name`, `age`, `status`) USING BTREE
) ENGINE = INNODB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Records of people1
-- ----------------------------

-- ----------------------------
-- Table structure for people2
-- ----------------------------
DROP TABLE IF EXISTS `people2`;
CREATE TABLE `people2`  (
  `id` INT(11) NULL DEFAULT NULL,
  `name` VARCHAR(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `mobile` VARCHAR(11) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  UNIQUE INDEX `m_unique`(`id`) USING BTREE,
  UNIQUE INDEX `n_unique`(`name`) USING BTREE
) ENGINE = INNODB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Records of people2
-- ----------------------------

-- ----------------------------
-- Table structure for people3
-- ----------------------------
DROP TABLE IF EXISTS `people3`;
CREATE TABLE `people3`  (
  `id` INT(11) NULL DEFAULT NULL,
  `name` VARCHAR(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `intro` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NULL,
  FULLTEXT INDEX `i_fulltext`(`intro`)
) ENGINE = INNODB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Records of people3
-- ----------------------------

-- ----------------------------
-- Table structure for people4
-- ----------------------------
DROP TABLE IF EXISTS `people4`;
CREATE TABLE `people4`  (
  `id` INT(11) NULL DEFAULT NULL,
  `name` VARCHAR(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `geom` GEOMETRY NOT NULL,
  SPATIAL INDEX `g_apatial`(`geom`)
) ENGINE = INNODB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Records of people4
-- ----------------------------

-- ----------------------------
-- Table structure for reply
-- ----------------------------
DROP TABLE IF EXISTS `reply`;
CREATE TABLE `reply`  (
  `id` INT(11) NOT NULL AUTO_INCREMENT COMMENT '回复编号',
  `comment_id` INT(11) NOT NULL COMMENT '评价编号',
  `user_id` INT(11) UNSIGNED NOT NULL COMMENT '评价用户',
  `content` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT '回复内容',
  `add_time` DATETIME NULL DEFAULT NULL COMMENT '添加时间',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `rep_com`(`comment_id`) USING BTREE,
  CONSTRAINT `rep_com` FOREIGN KEY (`comment_id`) REFERENCES `comment` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = INNODB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Records of reply
-- ----------------------------

-- ----------------------------
-- Table structure for test_goods
-- ----------------------------
DROP TABLE IF EXISTS `test_goods`;
CREATE TABLE `test_goods`  (
  `g_id` INT(11) NOT NULL AUTO_INCREMENT,
  `g_name` VARCHAR(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `g_price` DECIMAL(7, 2) UNSIGNED NULL DEFAULT NULL,
  PRIMARY KEY (`g_id`) USING BTREE,
  UNIQUE INDEX `g_name`(`g_name`) USING BTREE
) ENGINE = INNODB AUTO_INCREMENT = 7 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Records of test_goods
-- ----------------------------
INSERT INTO `test_goods` VALUES (1, '西游记', 50.40);
INSERT INTO `test_goods` VALUES (2, '牛奶糖', 7.50);
INSERT INTO `test_goods` VALUES (3, '水果糖', 2.50);
INSERT INTO `test_goods` VALUES (4, '休闲西服', 800.00);
INSERT INTO `test_goods` VALUES (5, '果汁', 3.00);
INSERT INTO `test_goods` VALUES (6, '论语', 109.00);

-- ----------------------------
-- View structure for v_table2
-- ----------------------------
DROP VIEW IF EXISTS `v_table2`;
CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `v_table2` AS SELECT id,NAME,price FROM goods ;

-- ----------------------------
-- View structure for v_table3
-- ----------------------------
DROP VIEW IF EXISTS `v_table3`;
CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `v_table3` AS SELECT orders.o_id,goods.name FROM goods JOIN orders ON goods.id=orders.o_id ;

-- ----------------------------
-- Function structure for func
-- ----------------------------
DROP FUNCTION IF EXISTS `func`;
DELIMITER ;;
CREATE FUNCTION `func`()
 RETURNS INT(11)
RETURN (SELECT COUNT(*) FROM goods)
;;
DELIMITER ;

-- ----------------------------
-- Procedure structure for proc1
-- ----------------------------
DROP PROCEDURE IF EXISTS `proc1`;
DELIMITER ;;
CREATE PROCEDURE `proc1`()
BEGIN
DECLARE var1,var2,var3,g_id INT;
SET var1=1,var2=2;
SET var3=var1+var2;
SELECT id INTO g_id FROM goods WHERE id=1;
END
;;
DELIMITER ;

-- ----------------------------
-- Procedure structure for proc8
-- ----------------------------
DROP PROCEDURE IF EXISTS `proc8`;
DELIMITER ;;
CREATE PROCEDURE `proc8`(p1 INT)
BEGIN
   label1: LOOP
     SET p1 = p1 + 1;
     IF p1 < 10 THEN
       ITERATE label1;
     END IF;
     LEAVE label1;
   END LOOP label1;
   SET @Y = p1;
END
;;
DELIMITER ;

-- ----------------------------
-- Procedure structure for proc9
-- ----------------------------
DROP PROCEDURE IF EXISTS `proc9`;
DELIMITER ;;
CREATE PROCEDURE `proc9`(IN flag VARCHAR(10),OUT gname VARCHAR(30),OUT gprice DECIMAL(7,2))
BEGIN
DECLARE g_id INT;
DECLARE g_name VARCHAR(30);
DECLARE g_price DECIMAL(7,2);
DECLARE g_cursor CURSOR FOR SELECT id,NAME,price FROM db_shop.goods;
DECLARE EXIT HANDLER FOR NOT FOUND CLOSE g_cursor;
OPEN g_cursor;
REPEAT
FETCH g_cursor INTO g_id,g_name,g_price;
IF g_id=flag THEN
SELECT g_name,g_price INTO gname,gprice;
END IF;
UNTIL g_id=flag
END REPEAT;
CLOSE g_cursor;
END
;;
DELIMITER ;

SET FOREIGN_KEY_CHECKS = 1;