查询准备

设计数据库

我们以暗区突围这款游戏为例,数据库设计如下:

  • players 表:玩家 id,姓名,性别,注册时间,仓库 id,等级

  • items 表:物品 id,物品名字,重量,空间,价值,物品信息

  • warehouse 表:仓库 id,物品 id,物品数量,物品占用空间大小

  • records 表:记录 id,卖家 id,物品 id,物品数量( TINYINT, 利用数据库本身进行单次上架数目的限制),物品价格,上架时间,下架时间,买家 id

  • market 表:市场物品 id,物品价格

需要注意的是,物品的价格不等同于其价值,market 表为动态表,其物品价格依赖于 record 表中的物品价格字段,取其最小值,当 records 表中发生变化时(物品被购买,我们规定玩家只会购买市场中最低价格的物品,实际上在这个实验设计中,玩家也只能看到市场中最低价格的物品),我们利用参照完整性对 market 表进行同步修改(重新查询 records 表,获取最新的最低物品价格))

CREATE DATABASE game;

CREATE TABLE players
(
    p_id              INT,
    p_name            VARCHAR(32),
    p_gender          CHAR,
    registration_time DATE,
    warehouse_id      INT,
    p_grade           INT
);

CREATE TABLE items
(
    i_id          INT,
    i_name        VARCHAR(16),
    i_weight      INT,
    i_space       INT,
    i_value       INT,
    i_information TEXT,
    i_class       VARCHAR(16)
);

CREATE TABLE records
(
    r_id         INT,
    seller_id    INT,
    ri_id        INT,
    ri_num       TINYINT,
    ri_price     INT,
    shelf_time   DATE,
    closing_time DATE,
    buyer_id     INT
);

CREATE TABLE market
(
    mi_id    INT,
    mi_price INT
);

INSERT INTO players (p_id, p_name, p_gender, registration_time, warehouse_id, p_grade)
    VALUES 
        (10001, '医疗兵_001', '女', '2022-10-10', 10001, 24),
        (10002, '突击兵_106', '男', '2023-7-2', 10002, 23),
        (10003, '弗雷德', '男', '2023-5-2', 10003, 29),
        (10004, '雷诺伊尔', '男', '2023-3-7', 10004, 29),
        (10005, '多斯', '男', '2022-12-20', 10005, 30),
        (10006, '杰克逊', '男', '2023-1-7', 10006, 23),
        (10007, '寄术大师', '男', '2022-4-7', 10007, 30),
        (10008, '无敌CS大王', '女', '2023-10-21', 10008, 27),
        (10009, '沃伦', '男', '2022-12-10', 10009, 30),
        (10010, '尤文', '男', '2022-1-1', 10010, 30),
        (10011, '阿贾克斯', '男', '2022-1-1', 10011, 30),
        (10012, '德文潘', '男', '2022-1-2', 10012, 30),
        (10013, '兰德尔', '男', '2023-9-24', 10013, 25),
        (10014, '罗尔夫', '男', '2023-11-5', 10014, 21),
        (10015, '卡尔', '男', '2023-7-3', 10015, 25),
        (10016, '思密达', '男', '2023-12-5', 10016, 22),
        (10017, '科特', '男', '2023-11-5', 10017, 23),
        (10018, '萌新', '女', '2024-1-13', 10018, 17);


INSERT INTO items (i_id, i_name, i_weight, i_space, i_value, i_class)
    VALUES 
        (1001, '花瓶', 5, 4, 40, '收藏品'),
        (1002, '保温水壶', 1, 2, 1, '生活用品'),
        (1003, '茶壶', 5, 4, 40, '收藏品'),
        (1004, '金豹雕像', 4, 2, 2, '收藏品'),
        (1005, '金杯', 4, 2, 4, '收藏品'),
        (1006, '金魔方', 4, 1, 5, '收藏品'),
        (1007, '机密文件', 1, 2, 600, '收藏品'),
        (1008, '金狮雕像', 9, 6, 42, '收藏品'),
        (1009, '金块', 4, 2, 5, '收藏品'),
        (1010, '金手镯', 3, 1, 2, '收藏品'),
        (1011, '金手表', 3, 1, 2, '收藏品'),
        (1012, '银徽章', 2, 1, 1, '纪念品'),
        (1013, '银片手链', 2, 1, 1, '收藏品'),
        (1014, '钻戒', 1, 1, 2, '收藏品'),
        (1015, '红色火药', 1, 2, 1, '易燃物'),
        (1016, '绿色火药', 1, 2, 1, '易燃物'),
        (1017, '蓝色火药', 1, 2, 1, '易燃物'),
        (1018, '主客房钥匙', 1, 1, 27, '钥匙'),
        (1019, '201钥匙', 1, 1, 6, '钥匙'),
        (1020, '别墅钥匙', 1, 1, 8, '钥匙'),
        (1021, '储藏室钥匙', 1, 1, 4, '钥匙'),
        (1022, '民宅钥匙', 1, 4, 1, '钥匙'),
        (1023, '北村住宅钥匙', 1, 1, 1, '钥匙'),
        (1024, '马厩钥匙', 1, 1, 2, '钥匙'),
        (1025, '海滨别墅钥匙', 1, 1, 8, '钥匙'),
        (1026, '车库钥匙', 1, 1, 3, '钥匙'),
        (1027, '墓地钥匙', 1, 1, 8, '钥匙'),
        (1028, '一楼休息室钥匙', 1, 1, 10, '钥匙'),
        (1029, '武器贮藏室钥匙', 1, 1, 16, '钥匙'),
        (1030, '水闸房钥匙', 1, 1, 11, '钥匙');

INSERT INTO market (mi_id, mi_price)
    VALUES 
        (1001, 44),
        (1002, 1),
        (1003, 47),
        (1004, 2),
        (1005, 5),
        (1006, 6),
        (1008, 49),
        (1009, 6),
        (1010, 2),
        (1011, 3),
        (1012, 1),
        (1013, 1),
        (1014, 2),
        (1015, 1),
        (1016, 1),
        (1017, 1),
        (1018, 32),
        (1019, 6),
        (1020, 10),
        (1021, 4),
        (1022, 1),
        (1023, 1),
        (1024, 2),
        (1025, 8),
        (1026, 3),
        (1028, 1),
        (1029, 2),
        (1030, 13);

INSERT INTO records (r_id, seller_id, ri_id, ri_num, ri_price, shelf_time, closing_time, buyer_id)
    VALUES 
        (100001, 10001, 1001, 3, 44, '2024-4-10', '2024-4-11', 10002),
        (100002, 10001, 1022, 1, 1, '2024-4-1', null, null),
        (100003, 10001, 1030, 2, 13, '2024-4-10', '2024-4-11', 10005),
        (100004, 10001, 1004, 1, 2, '2024-4-7', '2024-4-8', 10006),
        (100005, 10001, 1023, 1, 1, '2024-4-7', '2024-4-8', 10005),
        (100006, 10001, 1008, 4, 49, '2024-4-7', '2024-4-8', 10009),
        (100007, 10001, 1011, 1, 13, '2024-4-2', '2024-4-8', 10013),
        (100008, 10001, 1017, 1, 1, '2024-4-2', '2024-4-8', 10018),
        (100009, 10001, 1014, 5, 2, '2024-4-1', '2024-4-8', 10017),
        (100010, 10002, 1006, 1, 6, '2024-4-1', '2024-4-8', 10012),
        (100011, 10002, 1015, 1, 1, '2024-4-5', '2024-4-8', 10013),
        (100012, 10002, 1017, 5, 1, '2024-4-5', '2024-4-8', 10001),
        (100013, 10002, 1028, 1, 1, '2024-4-5', '2024-4-8', 10017),
        (100014, 10002, 1005, 5, 5, '2024-4-5', '2024-4-8', 10003),
        (100015, 10002, 1008, 2, 50, '2024-4-5', '2024-4-8', 10006),
        (100016, 10002, 1018, 5, 32, '2024-4-7', '2024-4-8', 10009),
        (100017, 10003, 1001, 1, 46, '2024-4-7', '2024-4-8', 10005),
        (100018, 10003, 1021, 2, 4 , '2024-4-5', '2024-4-8', 10004),
        (100019, 10003, 1020, 1, 10, '2024-4-5', '2024-4-8', 10010),
        (100020, 10003, 1029, 2, 2, '2024-4-8', '2024-4-10', 10011),
        (100021, 10004, 1017, 1, 1, '2024-4-8', '2024-4-10', 10003),
        (100022, 10004, 1013, 1, 1, '2024-4-8', '2024-4-11', 10001),
        (100023, 10005, 1012, 1, 1, '2024-4-8', null, null),
        (100024, 10005, 1015, 3, 1, '2024-4-8', '2024-4-9', 10006),
        (100025, 10005, 1016, 1, 1, '2024-4-8', '2024-4-9', 10012),
        (100026, 10005, 1019, 1, 6, '2024-4-1', '2024-4-9', 10014),
        (100027, 10007, 1023, 4, 1, '2024-4-1', '2024-4-9', 10016),
        (100028, 10007, 1024, 1, 2, '2024-4-1', '2024-4-9', 10017),
        (100029, 10007, 1025, 3, 8, '2024-4-3', '2024-4-9', 10013),
        (100030, 10007, 1009, 1, 6, '2024-4-3', '2024-4-9', 10001),
        (100031, 10007, 1026, 1, 3, '2024-4-3', '2024-4-8', 10002),
        (100032, 10007, 1028, 2, 1, '2024-4-7', '2024-4-8', 10003),
        (100033, 10007, 1030, 1, 13,'2024-4-7', '2024-4-8', 10004),
        (100034, 10008, 1022, 1, 1 ,'2024-4-4', '2024-4-8', 10005),
        (100035, 10008, 1029, 1, 2 ,'2024-4-4', null, null),
        (100036, 10009, 1029, 3, 2 ,'2024-4-4', null, null),
        (100037, 10009, 1010, 1, 2 ,'2024-4-10', '2024-4-11', 10008),
        (100038, 10012, 1003, 1, 47,'2024-4-10', '2024-4-11', 10009),
        (100039, 10013, 1001, 4, 46,'2024-4-3', '2024-4-5', 10010),
        (100040, 10013, 1002, 1, 1 ,'2024-4-3', null, null),
        (100041, 10014, 1013, 2, 1 ,'2024-4-3', '2024-4-4', 10012),
        (100042, 10015, 1016, 1, 1 ,'2024-4-3', '2024-4-4', 10013),
        (100043, 10016, 1015, 1, 1 ,'2024-4-3', '2024-4-5', 10014),
        (100044, 10016, 1023, 3, 1 ,'2024-4-3', '2024-4-7', 10015),
        (100045, 10016, 1017, 1, 1 ,'2024-4-1', '2024-4-4', 10018),
        (100046, 10017, 1017, 1, 1 ,'2024-4-1', null, null),
        (100047, 10017, 1018, 1, 32,'2024-4-1', '2024-4-1', 10004),
        (100048, 10017, 1015, 5, 1 ,'2024-4-1', '2024-4-2', 10006),
        (100049, 10017, 1001, 1, 48,'2024-4-1', '2024-4-3', 10007),
        (100050, 10017, 1016, 1, 1 ,'2024-4-1', '2024-4-3', 10011);

results matching ""

    No results matching ""