Mysql数据库设计

mysql  Ver 8.0.32 for macos13 on arm64 (MySQL Community Server - GPL)

CREATE TABLE `users`
(
    `uid`            int                                                    NOT NULL AUTO_INCREMENT,
    `username`       varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin NOT NULL,
    `password`       varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin NOT NULL,
    `email`          varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin NOT NULL,
    `usertype`       int                                                             DEFAULT '0',
    `regdate`        datetime                                               NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `expirationdate` datetime                                                        DEFAULT NULL,
    PRIMARY KEY (`uid`),
    UNIQUE KEY `username` (`username`),
    UNIQUE KEY `password` (`password`),
    CONSTRAINT `users_chk_1` CHECK ((`usertype` IN (0, 1, 2)))
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb3
  COLLATE = utf8mb3_bin;

CREATE TABLE `address`
(
    `aid`      int NOT NULL AUTO_INCREMENT,
    `uid`      int NOT NULL,
    `name`     varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin DEFAULT NULL,
    `tel`      varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin DEFAULT NULL,
    `province` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin DEFAULT NULL,
    `city`     varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin DEFAULT NULL,
    `county`   varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin DEFAULT NULL,
    `detail`   varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin DEFAULT NULL,
    `comment`  varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin DEFAULT NULL,
    PRIMARY KEY (`aid`),
    KEY `fk_address_uid` (`uid`),
    CONSTRAINT `fk_address_uid` FOREIGN KEY (`uid`) REFERENCES `users` (`uid`)
) ENGINE = InnoDB
  AUTO_INCREMENT = 9
  DEFAULT CHARSET = utf8mb3
  COLLATE = utf8mb3_bin;

CREATE TABLE `shopcar`
(
    `scid`     int NOT NULL AUTO_INCREMENT,
    `uid`      int NOT NULL,
    `iid`      int NOT NULL,
    `quantity` int DEFAULT NULL,
    PRIMARY KEY (`scid`),
    KEY `fk_shopcar_iid` (`iid`),
    KEY `fk_shopcar_uid` (`uid`),
    CONSTRAINT `fk_shopcar_iid` FOREIGN KEY (`iid`) REFERENCES `items` (`iid`),
    CONSTRAINT `fk_shopcar_uid` FOREIGN KEY (`uid`) REFERENCES `users` (`uid`)
) ENGINE = InnoDB
  AUTO_INCREMENT = 12
  DEFAULT CHARSET = utf8mb3
  COLLATE = utf8mb3_bin;

CREATE TABLE `items`
(
    `iid`      int                                                    NOT NULL AUTO_INCREMENT,
    `name`     varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin NOT NULL,
    `price`    decimal(8, 2)                                          NOT NULL,
    `material` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin DEFAULT NULL,
    `quantity` int                                                    NOT NULL,
    `comment`  varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin DEFAULT NULL,
    `img`      varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin DEFAULT NULL, -- 图片url地址
    PRIMARY KEY (`iid`)
) ENGINE = InnoDB
  AUTO_INCREMENT = 58
  DEFAULT CHARSET = utf8mb3
  COLLATE = utf8mb3_bin;

CREATE TABLE `orders`
(
    `oid`        int      NOT NULL AUTO_INCREMENT,
    `uid`        int      NOT NULL,
    `orderdate`  datetime NOT NULL,
    `comment`    varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin DEFAULT NULL,
    `aid`        int      NOT NULL,
    `totalprice` decimal(8, 2)                                          DEFAULT NULL,
    `status`     int                                                    DEFAULT NULL,
    PRIMARY KEY (`oid`),
    KEY `fk_orders_uid` (`uid`),
    KEY `fk_order_aid` (`aid`),
    CONSTRAINT `fk_order_aid` FOREIGN KEY (`aid`) REFERENCES `address` (`aid`),
    CONSTRAINT `fk_orders_uid` FOREIGN KEY (`uid`) REFERENCES `users` (`uid`),
    CONSTRAINT `orders_chk_1` CHECK ((`totalprice` >= 0)),
    CONSTRAINT `orders_chk_2` CHECK ((`status` IN (0, 1, 2, 3)))
) ENGINE = InnoDB
  AUTO_INCREMENT = 19
  DEFAULT CHARSET = utf8mb3
  COLLATE = utf8mb3_bin;

CREATE TABLE orderitems
(
    oiid        int PRIMARY KEY AUTO_INCREMENT,
    iid         int NOT NULL,                                -- 物品号
    oid         int NOT NULL,                                -- 此物品属于的订单号
    quantity    int,
    extendprice decimal(8, 2),                               -- 此订单中此物品的价格
    discount    decimal(3, 2),                               -- 折扣, 针对vip来说
    tax         decimal(3, 2),                               -- 税率
    returnflag  int CHECK ( returnflag IN (0, 1)) DEFAULT 0, -- 退货标记
    comment     varchar(255),
    CONSTRAINT fk_orderitems_iid FOREIGN KEY (iid) REFERENCES items (iid),
    CONSTRAINT fk_orderitems_oid FOREIGN KEY (oid) REFERENCES orders (oid)
);

CREATE TABLE `itemclass`
(
    `cid`       int NOT NULL AUTO_INCREMENT,
    `classname` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin DEFAULT NULL,
    `comment`   varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin DEFAULT NULL,
    PRIMARY KEY (`cid`)
) ENGINE = InnoDB
  AUTO_INCREMENT = 5
  DEFAULT CHARSET = utf8mb3
  COLLATE = utf8mb3_bin;

CREATE TABLE `vippayment`
(
    `vpid`     int      NOT NULL AUTO_INCREMENT,
    `uid`      int      NOT NULL,
    `paydate`  datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `payprice` decimal(8, 2)     DEFAULT NULL,
    PRIMARY KEY (`vpid`),
    KEY `fk_vippayment_uid` (`uid`),
    CONSTRAINT `fk_vippayment_uid` FOREIGN KEY (`uid`) REFERENCES `users` (`uid`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb3
  COLLATE = utf8mb3_bin;

CREATE TABLE `icsmapping`
(
    `icid` int NOT NULL AUTO_INCREMENT,
    `iid`  int NOT NULL,
    `cid`  int NOT NULL,
    PRIMARY KEY (`icid`),
    UNIQUE KEY `uq_iid_cid` (`iid`, `cid`),
    KEY `fk_itemclass_cid` (`cid`),
    CONSTRAINT `fk_item_iid` FOREIGN KEY (`iid`) REFERENCES `items` (`iid`),
    CONSTRAINT `fk_itemclass_cid` FOREIGN KEY (`cid`) REFERENCES `itemclass` (`cid`)
) ENGINE = InnoDB
  AUTO_INCREMENT = 16
  DEFAULT CHARSET = utf8mb3
  COLLATE = utf8mb3_bin;

CREATE TABLE `log_orders`
(
    `loid`    int      NOT NULL AUTO_INCREMENT,
    `logtime` datetime NOT NULL,
    `comment` varchar(255) COLLATE utf8mb3_bin DEFAULT NULL,
    `oid`     int      NOT NULL,
    PRIMARY KEY (`loid`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb3
  COLLATE = utf8mb3_bin;

CREATE TABLE `log_users`
(
    `luid`    int      NOT NULL AUTO_INCREMENT,
    `logtime` datetime NOT NULL,
    `uid`     int      NOT NULL,
    `type`    int                              DEFAULT NULL,
    `comment` varchar(255) COLLATE utf8mb3_bin DEFAULT NULL,
    PRIMARY KEY (`luid`),
    CONSTRAINT `log_users_chk_1` CHECK ((`type` IN (0, 1, 2)))
) ENGINE = InnoDB
  AUTO_INCREMENT = 8
  DEFAULT CHARSET = utf8mb3
  COLLATE = utf8mb3_bin;

# 假设税率 tax位于3% - 10%, 对每笔订单明细随机生成tax,
DROP PROCEDURE updateTaxRandom;
DELIMITER //
CREATE PROCEDURE updateTaxRandom()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE row_id INT;
    DECLARE random_tax DECIMAL(5, 2);
    -- 定义一个游标，选择所有的行
    DECLARE cur CURSOR FOR SELECT oiid, tax FROM orderitems;
    -- 设置终止条件
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    OPEN cur;
    read_loop:
    LOOP
        -- 读取下一行
        FETCH cur INTO row_id, random_tax;
        IF done THEN
            LEAVE read_loop;
        END IF;
        -- 更新每行的 tax 为随机3-10的小数
        UPDATE orderitems SET tax = ROUND(3 + RAND() * (10 - 3), 2) WHERE oiid = row_id;
    END LOOP;
    CLOSE cur;
END //
DELIMITER ;

CALL updateTaxRandom();

UPDATE items SET price = price + 20;

UPDATE orderitems,items
SET extendprice = price * (1 + tax / 100) * (discount / 10)
WHERE orderitems.iid = items.iid
  AND discount <> 0;

UPDATE orderitems,items
SET extendprice = price * (1 + tax / 100)
WHERE orderitems.iid = items.iid
  AND discount = 0;

@SpringBootTest
class SqlexamApplicationTests {

    public static int getIntegerBetween(int v1, int v2) {
        // 生成位于 v1 和 v2 之间的随机整数
        int minValue = Math.min(v1, v2);
        int maxValue = Math.max(v1, v2);
        return (int) (Math.random() * (maxValue - minValue + 1) + minValue);
    }

    @Autowired
    private UserMapper userMapper;

    @Autowired
    private ItemMapper itemMapper;

    @Test
    void contextLoads() {
        List<User> users = userMapper.getUsers();
        System.out.println(users);
    }

    @Test
    void insertUser() {
        int n = 3;
        for (int i = 0; i < n; i++) {
            User user = new User();
            user.setUsername("user" + i);
            user.setPassword("userpass" + i);
            user.setEmail("user" + i + "@sql.com");
            user.setRegdate(LocalDateTime.now().toString());
            user.setUsertype(0);
            user.setExpirationdate(null);

            userMapper.insertUser(
                    user.getUsername(),
                    user.getPassword(),
                    user.getEmail(),
                    user.getUsertype(),
                    user.getRegdate(),
                    user.getExpirationdate()
            );
        }
    }

    @Test
    void insertItem() {
        int n = 20;
        for (int i = 0; i < n; i++) {
            Item item = new Item();
            item.setName("name" + i);
            item.setPrice(String.valueOf(getIntegerBetween(10, 200)));
            item.setQuantity(getIntegerBetween(20, 50));
            item.setComment("comment" + i);
            item.setImg("img" + i);

            itemMapper.insertItem(item.getName(), item.getPrice(), item.getQuantity(), item.getComment(), item.getImg());
        }
    }

    @Test
    void insertAddress() {
        int n = 20;
        for (int i = 0; i < n; i++) {
            Address address = new Address();
            address.setUid(getIntegerBetween(1, 6));
            address.setName("收货人" + i);
            address.setTel(generatePhoneNumber());
            address.setProvince("province" + i + i);
            address.setCity("city" + i);
            address.setCounty("county" + i);
            address.setDetail("detail" + i);
            address.setComment("comment" + i + i + i);

            userMapper.insertAddress(address);
        }
    }

    @Test
    void insertOrder() {
        int n = 50;
        for (int i = 0; i < n; i++) {
            Order order = new Order();
            int uid = getIntegerBetween(1, 6);
            List<Address> addressByUid = userMapper.getAddressByUid(uid);
            Address address = addressByUid.get(getIntegerBetween(0, addressByUid.size() - 1));
            int aid = address.getAid();

            order.setUid(uid);
            order.setAid(aid);
            order.setOrderdate(generateDateBetweenYears(2022,2023));
            order.setStatus(getIntegerBetween(0, 2));
            order.setComment("comment" + uid + aid);

            userMapper.insertOrder(order);
        }
    }

    @Test
    void insertOrderItems(){
        // item 20 orders 50
        int n = 150;
        int itemIndex = 1;
        int orderIndex = 1;
        for (int i = 0; i < n; i++) {
            itemIndex = itemIndex % 20 + 1;
            orderIndex = orderIndex % 50 + 1;
            Orderitem orderitem = new Orderitem();
            orderitem.setIid(itemIndex);
            orderitem.setOid(orderIndex);
            orderitem.setReturnflag(0); // 默认不退货
            orderitem.setQuantity(getIntegerBetween(1,3));
            orderitem.setComment("comment" + orderIndex + itemIndex);
            userMapper.insertOrderitem(orderitem);
            itemIndex++;
            orderIndex++;
        }
    }

    @Test
    void testJAVASE(){
        int a = 21;
        a %= 21;
        System.out.println(a);
    }


    public static LocalDateTime generateDateBetweenYears(int year1, int year2) {
        // 获取指定年份的第一秒
        LocalDateTime startDateTime = LocalDateTime.of(year1, 1, 1, 0, 0, 0);
        // 获取指定年份的最后一秒
        LocalDateTime endDateTime = LocalDateTime.of(year2, 12, 31, 23, 59, 59);
        // 生成介于两个年份之间的随机秒数
        long randomSecond = ThreadLocalRandom.current().nextLong(
                startDateTime.toEpochSecond(ZoneOffset.UTC),
                endDateTime.toEpochSecond(ZoneOffset.UTC)
        );
        // 构建最终的LocalDateTime
        return LocalDateTime.ofEpochSecond(randomSecond, 0, ZoneOffset.UTC);
    }

    public static String generatePhoneNumber() {
        Random random = new Random();
        StringBuilder phoneNumber = new StringBuilder("1"); // 国内手机号以1开头
        for (int i = 0; i < 10; i++) {
            phoneNumber.append(random.nextInt(10));
        }
        return phoneNumber.toString();
    }

}

CREATE TABLE orders1
(
    oid        int PRIMARY KEY AUTO_INCREMENT,
    uid        int      NOT NULL,
    totalprice decimal(8, 2),
    orderdate  datetime NOT NULL,
    comment    varchar(255),
    aid        int      NOT NULL,
    status     int CHECK ( status IN (0, 1, 2)),
    CONSTRAINT fk_order1_uid FOREIGN KEY (uid) REFERENCES users (uid),
    CONSTRAINT fk_order1_aid FOREIGN KEY (aid) REFERENCES address (aid)
);
DESC orders1;

CREATE INDEX orders_index ON orders (uid ASC, aid DESC);

CREATE OR REPLACE VIEW userbuys AS
SELECT DISTINCT users.uid           '用户编号',
       username            '用户名',
       extendprice         '价格',
       items.name          '商品名',
       orderitems.quantity '数量',
       orderdate           '日期',
       detail              '详细地址'
FROM users,
     orders,
     orderitems,
     items,
     address
WHERE orders.uid = users.uid
  AND orders.oid = orderitems.oid
  AND orders.aid = address.aid
  AND orderitems.iid = items.iid;

SELECT *
FROM userbuys;

-- 查询销售额最大的商品
CREATE VIEW maxSale AS
SELECT items.iid                           AS '商品编号',
       name                                AS '商品名',
       items.wholesale                     AS '商品进价',
       price                               AS '商品售价',
       SUM(orderitems.quantity)            AS '本单数量',
       SUM(price - orderitems.extendprice) AS '本单利润'
FROM orderitems,
     items
WHERE orderitems.iid = items.iid
GROUP BY items.iid
ORDER BY SUM(orderitems.quantity) DESC;

SELECT * FROM maxSale;

CREATE VIEW maxProfit AS
SELECT items.iid                           AS '商品编号',
       name                                AS '商品名',
       items.wholesale                     AS '商品进价',
       price                               AS '商品售价',
       SUM(orderitems.quantity)            AS '本单数量',
       SUM(price - orderitems.extendprice) AS '本单利润'
FROM orderitems,
     items
WHERE orderitems.iid = items.iid
GROUP BY items.iid
ORDER BY sum(price - orderitems.extendprice) DESC;

SELECT * FROM maxProfit;

# 查询用户和对应的地址信息
SELECT users.uid     AS '用户编号',
       username      AS '用户名',
       users.regdate AS '注册日期',
       name          AS '收货人姓名',
       province      AS '省份',
       city          AS '城市',
       county        AS '区域',
       detail        AS '详细'
FROM users,
     address
WHERE address.uid = users.uid;

# 查询已完成(status = 2)订单和其详细信息
SELECT orders.oid          '订单编号',
       name                '商品名',
       orderitems.quantity '购买数量',
       price               '商品原价',
       extendprice         '购买价格',
       orderdate           '订单日期'
FROM orders,
     orderitems,
     items
WHERE orders.oid = orderitems.oid
  AND orderitems.iid = items.iid
  AND orders.status = 2;

SELECT users.uid        AS '用户编号',
       username         AS '用户名',
       CASE users.usertype
           WHEN 0 THEN '普通用户'
           WHEN 1 THEN 'VIP用户'
           ELSE '管理员'
           END          AS '用户类型',
       SUM(extendprice) AS '累计花费'
FROM users,
     orders,
     orderitems
WHERE orders.uid = users.uid
  AND orders.oid = orderitems.oid
GROUP BY users.uid
ORDER BY SUM(extendprice) DESC;

INSERT INTO orders
VALUES (NULL, 1, now(), 'comment-new1', 15, NULL, 0);

INSERT INTO orders
VALUES (NULL, 999, now(), 'comment-new2', 15, NULL, 0);

INSERT INTO orders
VALUES (NULL, 1, NOW(), 'comment-new3', 15, NULL, 4);

SELECT *
FROM log_users,
     users
WHERE users.uid = log_users.uid
  AND username = 'admin';

DELETE
FROM log_users
WHERE uid = (SELECT uid FROM users WHERE username = 'admin');

SELECT *
FROM orderitems
WHERE oid IN (SELECT oid FROM orders WHERE uid IN (SELECT uid FROM users WHERE usertype = 1));

UPDATE orderitems
SET discount = 7.50
WHERE oid IN (SELECT oid FROM orders WHERE uid IN (SELECT uid FROM users WHERE usertype = 1));
UPDATE orderitems
SET discount = 0
WHERE oid IN (SELECT oid FROM orders WHERE uid IN (SELECT uid FROM users WHERE usertype IN (0,2)));

UPDATE orderitems,items
SET extendprice = price * (1 + tax / 100) * (1 - discount / 100)
WHERE orderitems.iid = items.iid;

SELECT *
FROM orderitems;

UPDATE orders,orderitems
SET totalprice = (SELECT SUM(extendprice) FROM orderitems WHERE orders.oid = orderitems.oid)
WHERE orders.oid = orderitems.oid;

SELECT *
FROM orders;

CREATE ROLE 'admin_user';
GRANT SELECT,UPDATE,INSERT ON TABLE users TO 'admin_user';
GRANT SELECT,UPDATE,INSERT ON TABLE address TO 'admin_user';
GRANT SELECT,UPDATE,INSERT ON TABLE vippayment TO 'admin_user';

REVOKE UPDATE, INSERT ON TABLE users FROM 'admin_user';
REVOKE UPDATE, INSERT ON TABLE address FROM 'admin_user';
REVOKE UPDATE, INSERT ON TABLE vippayment FROM 'admin_user';

DELIMITER $$ -- 作用，username用户支付费用成为vip
DROP PROCEDURE IF EXISTS tovip;
CREATE PROCEDURE tovip(IN destuser varchar(255), IN payprice decimal(8, 2), IN expirdate datetime)
BEGIN
    DECLARE destuid int DEFAULT NULL; -- 目标用户是否存在
    DECLARE destusertype int DEFAULT NULL; -- 用户是否已经是vip
    SELECT uid INTO destuid FROM users WHERE username = destuser;
    IF destuid IS NOT NULL
    THEN
        SELECT usertype INTO destusertype FROM users WHERE uid = destuid;
        IF destusertype = 1 THEN
            SELECT 'USER IS VIP!'; -- 用户已经是VIP
        ELSE
            UPDATE users SET expirationdate = expirdate, usertype = 1 WHERE username = destuser;
            INSERT INTO vippayment VALUES (NULL, destuid, now(), payprice);
        END IF;
    ELSE
        SELECT 'USER NOT EXIST!'; -- 用户不存在
    END IF;
END $$;

CALL tovip('zhangsan', 100.00, DATE_ADD(CURDATE(), INTERVAL 1 YEAR));

DELIMITER $$
DROP PROCEDURE IF EXISTS makeOrderFromShopCar;
CREATE PROCEDURE IF NOT EXISTS makeOrderFromShopCar(IN destuid int, IN destaid int)
BEGIN
    DECLARE destiid int DEFAULT NULL;
    DECLARE destquantity int DEFAULT NULL;
    DECLARE newoid int DEFAULT NULL;
    DECLARE destdiscount decimal(3, 2) DEFAULT 0.0;
    DECLARE destusertype int DEFAULT 0;
    DECLARE cur CURSOR FOR SELECT iid, quantity FROM shopcar WHERE uid = destuid;
    -- 定义错误处理
    DECLARE EXIT HANDLER FOR SQLSTATE '02000' CLOSE cur;
    -- 开启游标
    OPEN cur;
    -- 创建一个order存储所有orderitems
    SELECT (MAX(oid) + 1) INTO newoid FROM orders;
    INSERT INTO orders VALUES (newoid, destuid, now(), NULL, destaid, NULL, 0);
    -- 判断用户类型, 是否给予折扣
    SELECT usertype INTO destusertype FROM users WHERE uid = destuid;
    IF destusertype <> 0 THEN
        SELECT 7.5 INTO destdiscount;
    END IF;
    -- 进入循环, 遍历购物车, 生成订单明细, 删除购物车内商品, 商品对应库存减少
    loop1:
    LOOP
        -- 遍历购物车
        FETCH cur INTO destiid,destquantity;
        -- 判断商品库存是否充足
        IF destquantity <= (SELECT quantity FROM items WHERE iid = destiid) THEN
            -- 生成订单明细
            INSERT INTO orderitems
            VALUES (NULL, destiid, newoid, destquantity, NULL, destdiscount, 4.5, 0,
                    concat('comment', destiid, newoid));
            -- 删除购物车内商品
            DELETE FROM shopcar WHERE uid = destuid AND iid = destiid;
            -- 商品对应库存减少
            UPDATE items SET quantity = quantity - destquantity WHERE iid = destiid;
        ELSE
            SELECT 'ITEMS QUANTITY IS NOT ENOUGH!';
            -- error
            SIGNAL SQLSTATE '45000'
    				SET MESSAGE_TEXT = 'ITEMS QUANTITY IS NOT ENOUGH!';
        END IF;
    END LOOP;
END $$