2024年06月05日
mryoungbw
148
0 评论
软件设计
PyCharm PyQt5 python SQL PgSQL

数据库的选择

使用的对象-关系数据库：PostgreSQL

创建数据库

创建了名为 pyqt5_test_database 的数据库

CREATE DATABASE pyqt5_test_database;

创建表

创建了7个表，其中表 users 存储账户信息

其他六个表为weapon_systems、weapons、vehicles、weapon_types、vehicle_types、soldiers 存放兵器系统的武器、载具和兵种的相关信息。

-- 武器系统表
CREATE TABLE weapon_systems (
    system_id SERIAL PRIMARY KEY,
    system_name VARCHAR(255) NOT NULL
);

-- 武器类型表
CREATE TABLE weapon_types (
    type_id SERIAL PRIMARY KEY,
    type_name VARCHAR(255) NOT NULL
);

-- 武器表（需要先创建好weapon_types表）
CREATE TABLE weapons (
    weapon_id SERIAL PRIMARY KEY,
    weapon_name VARCHAR(255) NOT NULL,
    weapon_type_id INT REFERENCES weapon_types(type_id)-- 外键
);

-- 载具类型表
CREATE TABLE vehicle_types (
    type_id SERIAL PRIMARY KEY,
    type_name VARCHAR(255) NOT NULL
);

-- 载具表
CREATE TABLE vehicles (
    vehicle_id SERIAL PRIMARY KEY,
    vehicle_name VARCHAR(255) NOT NULL,
    vehicle_type_id INT REFERENCES vehicle_types(type_id)-- 外键
);

-- 兵种表
CREATE TABLE soldiers (
    soldier_id SERIAL PRIMARY KEY,
    soldier_type VARCHAR(255) NOT NULL,
    primary_weapon_id INT REFERENCES weapon_types(type_id)-- 外键
);

插入数据

-- 插入武器系统数据
INSERT INTO weapon_systems (system_name) VALUES
('武器'),
('载具'),
('兵种');

-- 插入武器类型数据
INSERT INTO weapon_types (type_name) VALUES
('突击步枪'),
('半自动步枪'),
('冲锋枪'),
('轻机枪'),
('中型机枪'),
('霰弹枪'),
('反器材步枪'),
('手动枪械步枪'),
('手动枪械卡宾枪'),
('手持型卡宾枪'),
('半自动装填步枪'),
('手枪'),
('近战武器'),
('爆炸物'),
('特殊装备');

-- 插入武器数据
INSERT INTO weapons (weapon_name, weapon_type_id) VALUES
...

-- 插入载具类型数据
INSERT INTO vehicle_types (type_name) VALUES
('定点'),
('飞机'),
('坦克'),
('运输载具');

-- 插入载具数据
INSERT INTO vehicles (vehicle_name, vehicle_type_id) VALUES
...

-- 插入兵种数据
INSERT INTO soldiers (soldier_type, primary_weapon_id) VALUES
('突击兵', 1),
('突击兵', 2),
('突击兵', 13),
('突击兵', 14),
('突击兵', 15),
('医疗兵', 3),
('医疗兵', 13),
('医疗兵', 14),
('医疗兵', 15),
('侦察兵', 7),
('侦察兵', 8),
('侦察兵', 9),
('侦察兵', 10),
('侦察兵', 11),
('侦察兵', 12),
('侦察兵', 13),
('侦察兵', 14),
('侦察兵', 15),
('支援兵', 4),
('支援兵', 5),
('支援兵', 6),
('支援兵', 13),
('支援兵', 14),
('支援兵', 15);

出现的问题及解决方法

创建序列出现的问题及解决方法

错误： syntax error at or near "AUTO_INCREMENT"

AUTO_INCREMENT为MySQL的语法，在PgSQL无法使用，可以使用以下解决方法

  1. 可视化操作：使用PgAdmin完善序列信息，输入以下命令完成序列的创建，否则会出现无法绑定的问题。

    错误： Failing row contains (null, 定点, 固定).null value in column "vehicles_id" violates not-null constraint 

    ALTER TABLE table_name ALTER COLUMN id名 SET DEFAULT nextval('序列名');

  2. 命令行操作：

    -- 创建序列：
CREATE SEQUENCE 序列名;
-- 将序列与表的列关联：
ALTER TABLE table_name ALTER COLUMN id SET DEFAULT nextval('序列名');
-- 可选地，你可以设置序列的起始值、递增步长和最大值：
-- 将序列的起始值设置为 1
ALTER SEQUENCE 序列名 START WITH 1;
-- 将序列的递增步长设置为 1
ALTER SEQUENCE 序列名 INCREMENT BY 1;
-- 将序列的最大值设置为 9999
ALTER SEQUENCE 序列名 MAXVALUE 9999;
-- 完成上述步骤后，每次向表中插入新记录时，ID 列都会自动递增。可以使用以下命令查看当前序列的值：
SELECT nextval('序列名');

  3. 直接使用PgSQL的 SERIAL 命令进行序列的创建

参考资料

PostgreSQL如何为主键创建自增序列(Sequences)_pg 自增序列-CSDN博客

postgresql设置id自增_postgresql 自增id-CSDN博客

数据查询

普通类别查询的命令

-- 查询所有武器及其类型
SELECT w.weapon_id, w.weapon_name, wt.type_name
FROM weapons w
JOIN weapon_types wt ON w.weapon_type_id = wt.type_id;

-- 查询所有载具及其类型
SELECT v.vehicle_id, v.vehicle_name, vt.type_name
FROM vehicles v
JOIN vehicle_types vt ON v.vehicle_type_id = vt.type_id;

-- 查询所有兵种及其武器类型
SELECT s.soldier_type, wt.type_name AS weapon_type
FROM soldiers s
JOIN weapons w ON s.primary_weapon_id = w.weapon_id
JOIN weapon_types wt ON w.weapon_type_id = wt.type_id;

-- 查询指定武器类型对应的武器
SELECT w.weapon_name
FROM weapons w
JOIN weapon_types wt ON w.weapon_type_id = wt.type_id
WHERE wt.type_name = '突击步枪';
从 weapons 表中选择 weapon_name 列。
通过 JOIN 将 weapons 表与 weapon_types 表连接在一起，连接条件是 weapon_type_id 和 type_id。
使用 WHERE 子句过滤出 type_name 为 '突击步枪' 的记录。

-- 查询指定载具类型对应的载具
SELECT v.vehicle_name
FROM vehicles v
JOIN vehicle_types vt ON v.vehicle_type_id = vt.type_id
WHERE vt.type_name = '定点';
从 vehicles 表中选择 vehicle_name 列。
通过 JOIN 将 vehicles 表与 vehicle_types 表连接在一起，连接条件是 vehicle_type_id 和 type_id。
使用 WHERE 子句过滤出 type_name 为 '定点' 的记录。

创建试图方便查询

创建视图

-- 查询所有武器及其类型视图
CREATE VIEW all_weapons_with_types AS
SELECT w.weapon_id, w.weapon_name, wt.type_name
FROM weapons w
JOIN weapon_types wt ON w.weapon_type_id = wt.type_id;

-- 查询所有载具及其类型视图
CREATE VIEW all_vehicles_with_types AS
SELECT v.vehicle_id, v.vehicle_name, vt.type_name
FROM vehicles v
JOIN vehicle_types vt ON v.vehicle_type_id = vt.type_id;

-- 查询所有兵种及其武器类型视图
CREATE VIEW all_soldiers_with_types AS
SELECT s.soldier_type, wt.type_name AS weapon_type
FROM soldiers s
JOIN weapons w ON s.primary_weapon_id = w.weapon_id
JOIN weapon_types wt ON w.weapon_type_id = wt.type_id;

通过视图查询

SELECT * FROM all_weapons_with_types;
SELECT * FROM all_vehicles_with_types;
SELECT * FROM all_soldiers_with_types;

-- 查询指定武器类型对应的武器
SELECT weapon_id, weapon_name
FROM all_weapons_with_types
WHERE type_name = '武器类型';

-- 查询指定载具类型对应的载具
SELECT vehicle_id, vehicle_name
FROM all_vehicles_with_types
WHERE type_name = '载具类型';

附录

添加详细数据

-- 添加突击步枪
INSERT INTO weapons (weapon_name, weapon_type_id)
VALUES
('StG 44 突击步枪', 1),
('利贝罗勒 1918 自动步枪', 1),
('格韦尔 1-5 突击步枪', 1),
('M2 卡宾枪', 1),
('温彻斯特 1907 型半自动步枪', 1),
('贝达 M1935半自动步枪', 1);

-- 添加半自动步枪
INSERT INTO weapons (weapon_name, weapon_type_id)
VALUES
('Karabin 1938M半自动步枪', 2),
('M1加兰德步枪', 2),
('特纳 SMLE 步枪', 2),
('格韦尔 43 步枪', 2),
('M1A1 卡宾枪', 2),
('格韦尔 1-5 半自动步枪', 2);

-- 添加冲锋枪
INSERT INTO weapons (weapon_name, weapon_type_id)
VALUES
('二式冲锋枪', 3),
('ZK-383 冲锋枪', 3),
('MP34 冲锋枪', 3),
('索米 KP/-31 冲锋枪', 3),
('斯登冲锋枪', 3),
('百式冲锋枪', 3),
('MP28 冲锋枪', 3),
('MP40 冲锋枪', 3),
('M1928A1 冲锋枪', 3),
('埃尔马 EMP 冲锋枪', 3),
('伯莱塔 38 型冲锋枪', 3),
('M3 冲锋枪', 3),
('威尔甘冲锋枪', 3);

-- 添加轻机枪
INSERT INTO weapons (weapon_name, weapon_type_id)
VALUES
('刘易斯机枪', 4),
('KE7 机枪', 4),
('布轮轻机枪', 4),
('FG42 伞兵机枪', 4),
('拉提 M26', 4),
('麦德森机枪', 4),
('十一年式轻机枪', 4),
('绍沙轻机枪', 4),
('九七世车载机枪', 4);

-- 添加中型机枪
INSERT INTO weapons (weapon_name, weapon_type_id)
VALUES
('MG 42 机枪', 5),
('M1919A6', 5),
('M1922 机枪', 5),
('MG 34 机枪', 5),
('S2-200 机枪', 5),
('VGO 空勤机枪', 5);

-- 添加霰弹枪
INSERT INTO weapons (weapon_name, weapon_type_id)
VALUES
('M30 复合枪', 6),
('12 口径自动霰弹枪', 6),
('Model 37', 6);

-- 添加反器材步枪
INSERT INTO weapons (weapon_name, weapon_type_id)
VALUES
('博斯反坦克步枪', 7),
('PzB 39 反坦克步枪', 7);

-- 添加手动枪械步枪
INSERT INTO weapons (weapon_name, weapon_type_id)
VALUES
('从林卡宾枪', 8),
('M28 步枪榴弹发射器', 8),
('突击队卡宾枪', 8);

-- 添加手动枪械卡宾枪
INSERT INTO weapons (weapon_name, weapon_type_id)
VALUES
('毛瑟 Kar98k 步枪', 9),
('罗斯 Mk Ⅲ 步枪', 9),
('K31/43', 9),
('李-恩菲尔德 No.4 Mkl步枪', 9),
('格韦尔 M95/30 步枪', 9),
('Krag-Jorgensen 步枪', 9),
('九九式步枪', 9);

-- 添加手持型卡宾枪
INSERT INTO weapons (weapon_name, weapon_type_id)
VALUES
('P08 卡宾枪', 10),
('战壕卡宾枪', 10);

-- 添加半自动装填步枪
INSERT INTO weapons (weapon_name, weapon_type_id)
VALUES
('RSC 半自动步枪', 11),
('鲁格 1906 半自动步枪', 11),
('Model8半自动步枪', 11),
('ZH-29 步枪', 11);

-- 添加手枪
INSERT INTO weapons (weapon_name, weapon_type_id)
VALUES
('M1911 手枪', 12),
('韦伯利 Mk V转轮手枪', 12),
('M27 左轮手枪', 12),
('P38 手枪', 12),
('红宝石半自动手枪', 12),
('PPKS', 12),
('P08 手枪', 12),
('PPK', 12),
('M1911 手枪', 12),
('九四式手枪', 12),
('解放者', 12),
('斯太尔 M1912 手枪', 12),
('威尔洛德微声手枪', 12);

-- 添加近战武器
INSERT INTO weapons (weapon_name, weapon_type_id)
VALUES
('指虎', 13),
('匕首', 13),
('突击队弯刀', 13),
('尖嘴镐', 13),
('侦察兵小刀 M1916', 13),
('撬管', 13),
('K98 刺刀', 13),
('钗', 13),
('短柄斧', 13),
('棍棒', 13),
('铁铲', 13),
('廓尔喀弯刀', 13),
('英军小刀', 13),
('割草刀', 13);

-- 添加爆炸物
INSERT INTO weapons (weapon_name, weapon_type_id)
VALUES
('黏性炸药', 14),
('M1A1 火箭筒', 14),
('刺雷', 14),
('PIAT榴弹发射器', 14),
('防空铁拳', 14),
('反人员地雷', 14),
('反坦克火箭筒', 14),
('反坦克地雷', 14),
('手枪火焰喷射器', 14),
('破片手雷', 14),
('燃烧手雷', 14),
('反坦克捆绑式手雷', 14),
('反坦克榴弹手枪', 14),
('破片榴弹步枪', 14),
('黏性手雷', 14),
('冲击手雷', 14),
('RMN50 手持迫击炮', 14),
('九九式地雷', 14),
('锥形炸药', 14);

-- 添加特殊装备
INSERT INTO weapons (weapon_name, weapon_type_id)
VALUES
('M2 火焰喷射器', 15),
('武士刀', 15),
('飞刀', 15),
('弹药箱', 15),
('绷带', 15),
('医疗箱', 15),
('标记瞄准镜', 15),
('信号枪', 15),
('信标', 15),
('烟雾手雷', 15),
('烟雾弹步枪', 15),
('狙击手诱饵', 15),
('医疗注射器', 15),
('苦无', 15),
('双管信号枪', 15),
('爆竹', 15);


-- 定点
INSERT INTO vehicles (vehicle_type_id, vehicle_name) VALUES
(1, '10式'),
(1, '九三式重机枪'),
(1, '6 磅砲'),
(1, '40mm 防空砲'),
(1, '維克斯'),
(1, 'Flak 38'),
(1, 'Pak 40 反坦克砲'),
(1, 'Mg34 定點機槍');

-- 飞机
INSERT INTO vehicles (vehicle_type_id, vehicle_name) VALUES
(2, 'P51D 战斗机'),
(2, 'P51K 战斗机'),
(2, 'P-70 夜间战斗机'),
(2, 'A-20 轰炸机'),
(2, 'MK VA 喷火式战斗机'),
(2, 'MK VB 喷火式战斗机'),
(2, 'FB MKVI型蚊式轰炸机'),
(2, '布伦海姆 MKIF 轰炸机'),
(2, 'MKI型布伦享式轰炸机'),
(2, 'MKI 型蚊式轰炸机'),
(2, '零式战斗机五二型'),
(2, '零式战斗机一一型'),
(2, 'F4U-1C 海盗式战斗机'),
(2, 'F4U-1A海盗式战斗机'),
(2, 'BF 109 G-6 战斗机'),
(2, 'BF 109 G-2 战斗机'),
(2, 'JU-88A轰炸机'),
(2, 'JU-88 C轰炸机'),
(2, '斯图卡 B-1'),
(2, '斯图卡 B-2');

-- 坦克
INSERT INTO vehicles (vehicle_type_id, vehicle_name) VALUES
(3, '八八式高射炮'),
(3, '九七式'),
(3, '特二式内火艇'),
(3, 'T34卡利欧波'),
(3, '谢尔曼坦克'),
(3, 'LVT'),
(3, 'M8 灰狗'),
(3, '突击虎坦克'),
(3, '四号坦克'),
(3, '38T坦克'),
(3, '虎Ⅰ坦克'),
(3, '四号自行高射炮'),
(3, '四号突击炮'),
(3, 'SdKfz 234 美洲豹'),
(3, '丘吉尔鳄鱼坦克'),
(3, 'MK VI 型瓦伦丁坦克'),
(3, 'T17E1 猎鹿大装甲车'),
(3, '丘吉尔 MK VI 坦克'),
(3, '防空 MKI型瓦伦丁坦克'),
(3, '丘吉尔自行火炮'),
(3, '瓦伦丁自行反坦克炮');

-- 运输载具
INSERT INTO vehicles (vehicle_type_id, vehicle_name) VALUES
(4, '九五式小型乘用车'),
(4, '小艇'),
(4, '吉普威利斯'),
(4, 'Lcvp登陆艇'),
(4, 'M3 冲锋车'),
(4, 'T48 gmc 9000分炮车'),
(4, 'SD.KFZ 251 半履带车'),
(4, 'Sd. kfz.251 pakwage'),
(4, '履带摩托车'),
(4, '水桶车'),
(4, '通用载具'),
(4, '拖拉机');

发表评论

博客信息

麻花与千层饼的头像
麻花与千层饼

一只菜鸟

25

文章数

24

评论数

169天

运行天数

目录