查询准备
设计数据库
我们以暗区突围这款游戏为例,数据库设计如下:
players
表:玩家 id,姓名,性别,注册时间,仓库 id,等级items
表:物品 id,物品名字,重量,空间,价值,物品信息warehouse
表:仓库 id,物品 id,物品数量,物品占用空间大小records
表:记录 id,卖家 id,物品 id,物品数量(TINYINT
, 利用数据库本身进行单次上架数目的限制),物品价格,上架时间,下架时间,买家 idmarket
表:市场物品 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);