¶自增列

IDENTITY [ (种子, 增量) ]

¶建表

-- 武器系统表
CREATE TABLE "TEST0623"."weapon_systems" (
    system_id  int IDENTITY(1,1) PRIMARY KEY,
    system_name VARCHAR(255) NOT NULL
);

-- 武器类型表
CREATE TABLE "TEST0623"."weapon_types" (
    type_id  int IDENTITY(1,1)  PRIMARY KEY,
    type_name VARCHAR(255) NOT NULL
);

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

-- 载具类型表
CREATE TABLE "TEST0623"."vehicle_types" (
    type_id  int IDENTITY(1,1)  PRIMARY KEY,
    type_name VARCHAR(255) NOT NULL
);

-- 载具表
CREATE TABLE "TEST0623"."vehicles" (
    vehicle_id  int IDENTITY(1,1)  PRIMARY KEY,
    vehicle_name VARCHAR(255) NOT NULL,
    vehicle_type_id INT REFERENCES "TEST0623"."vehicle_types"(type_id)-- 外键
);

-- 兵种表
CREATE TABLE "TEST0623"."soldiers" (
    soldier_id  int IDENTITY(1,1)  PRIMARY KEY,
    soldier_type VARCHAR(255) NOT NULL,
    primary_weapon_id INT REFERENCES "TEST0623"."weapon_types"(type_id)-- 外键
);

¶插入

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

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



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



-- 插入兵种数据
INSERT INTO "TEST0623"."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);

-- 提交更改
commit;

-- 添加突击步枪
INSERT INTO "TEST0623"."weapons" (weapon_name, weapon_type_id)
VALUES
('StG 44 突击步枪', 1),
('利贝罗勒 1918 自动步枪', 1),
('格韦尔 1-5 突击步枪', 1),
('M2 卡宾枪', 1),
('温彻斯特 1907 型半自动步枪', 1),
('贝达 M1935半自动步枪', 1),
('Karabin 1938M半自动步枪', 2),
('M1加兰德步枪', 2),
('特纳 SMLE 步枪', 2),
('格韦尔 43 步枪', 2),
('M1A1 卡宾枪', 2),
('格韦尔 1-5 半自动步枪', 2),
('二式冲锋枪', 3),
('ZK-383 冲锋枪', 3),
('MP34 冲锋枪', 3),
('索米 KP/-31 冲锋枪', 3),
('斯登冲锋枪', 3),
('百式冲锋枪', 3),
('MP28 冲锋枪', 3),
('MP40 冲锋枪', 3),
('M1928A1 冲锋枪', 3),
('埃尔马 EMP 冲锋枪', 3),
('伯莱塔 38 型冲锋枪', 3),
('M3 冲锋枪', 3),
('威尔甘冲锋枪', 3),
('刘易斯机枪', 4),
('KE7 机枪', 4),
('布轮轻机枪', 4),
('FG42 伞兵机枪', 4),
('拉提 M26', 4),
('麦德森机枪', 4),
('十一年式轻机枪', 4),
('绍沙轻机枪', 4),
('九七世车载机枪', 4),
('MG 42 机枪', 5),
('M1919A6', 5),
('M1922 机枪', 5),
('MG 34 机枪', 5),
('S2-200 机枪', 5),
('VGO 空勤机枪', 5),
('M30 复合枪', 6),
('12 口径自动霰弹枪', 6),
('Model 37', 6),
('博斯反坦克步枪', 7),
('PzB 39 反坦克步枪', 7),
('从林卡宾枪', 8),
('M28 步枪榴弹发射器', 8),
('突击队卡宾枪', 8),
('毛瑟 Kar98k 步枪', 9),
('罗斯 Mk Ⅲ 步枪', 9),
('K31/43', 9),
('李-恩菲尔德 No.4 Mkl步枪', 9),
('格韦尔 M95/30 步枪', 9),
('Krag-Jorgensen 步枪', 9),
('九九式步枪', 9),
('P08 卡宾枪', 10),
('战壕卡宾枪', 10),
('RSC 半自动步枪', 11),
('鲁格 1906 半自动步枪', 11),
('Model8半自动步枪', 11),
('ZH-29 步枪', 11),
('M1911 手枪', 12),
('韦伯利 Mk V转轮手枪', 12),
('M27 左轮手枪', 12),
('P38 手枪', 12),
('红宝石半自动手枪', 12),
('PPKS', 12),
('P08 手枪', 12),
('PPK', 12),
('M1911 手枪', 12),
('九四式手枪', 12),
('解放者', 12),
('斯太尔 M1912 手枪', 12),
('威尔洛德微声手枪', 12),
('指虎', 13),
('匕首', 13),
('突击队弯刀', 13),
('尖嘴镐', 13),
('侦察兵小刀 M1916', 13),
('撬管', 13),
('K98 刺刀', 13),
('钗', 13),
('短柄斧', 13),
('棍棒', 13),
('铁铲', 13),
('廓尔喀弯刀', 13),
('英军小刀', 13),
('割草刀', 13),
('黏性炸药', 14),
('M1A1 火箭筒', 14),
('刺雷', 14),
('PIAT榴弹发射器', 14),
('防空铁拳', 14),
('反人员地雷', 14),
('反坦克火箭筒', 14),
('反坦克地雷', 14),
('手枪火焰喷射器', 14),
('破片手雷', 14),
('燃烧手雷', 14),
('反坦克捆绑式手雷', 14),
('反坦克榴弹手枪', 14),
('破片榴弹步枪', 14),
('黏性手雷', 14),
('冲击手雷', 14),
('RMN50 手持迫击炮', 14),
('九九式地雷', 14),
('锥形炸药', 14),
('M2 火焰喷射器', 15),
('武士刀', 15),
('飞刀', 15),
('弹药箱', 15),
('绷带', 15),
('医疗箱', 15),
('标记瞄准镜', 15),
('信号枪', 15),
('信标', 15),
('烟雾手雷', 15),
('烟雾弹步枪', 15),
('狙击手诱饵', 15),
('医疗注射器', 15),
('苦无', 15),
('双管信号枪', 15),
('爆竹', 15);

COMMIT;

¶查询

¶普通查询

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

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

¶视图查询

创建视图

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

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

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

通过视图查询

SELECT * FROM "TEST0623"."all_weapons_with_types";
SELECT * FROM "TEST0623"."all_vehicles_with_types";
SELECT * FROM "TEST0623"."all_soldiers_with_types";