CREATE DATABASE IF NOT EXISTS us_military_equipment_query_system;
USE us_military_equipment_query_system;
CREATE TABLE 军种 (
名称 VARCHAR(50) PRIMARY KEY,
职能 TEXT NOT NULL
);
CREATE TABLE 部队 (
番号 VARCHAR(50) PRIMARY KEY,
地区 VARCHAR(100) NOT NULL,
类型 VARCHAR(50) NOT NULL,
隶属军种 VARCHAR(50) NOT NULL,
FOREIGN KEY (隶属军种) REFERENCES 军种(名称)
);
CREATE TABLE 装备 (
美军装备代号 VARCHAR(50) PRIMARY KEY,
类型 VARCHAR(50) NOT NULL,
数量 INT NOT NULL,
部署地区 VARCHAR(100) NOT NULL,
生产年份 YEAR
);
CREATE TABLE 列装 (
军种 VARCHAR(50) NOT NULL,
装备 VARCHAR(50) NOT NULL,
PRIMARY KEY (军种, 装备),
FOREIGN KEY (军种) REFERENCES 军种(名称),
FOREIGN KEY (装备) REFERENCES 装备(美军装备代号)
);
CREATE TABLE 使用 (
部队 VARCHAR(50) NOT NULL,
装备 VARCHAR(50) NOT NULL,
PRIMARY KEY (部队, 装备),
FOREIGN KEY (部队) REFERENCES 部队(番号),
FOREIGN KEY (装备) REFERENCES 装备(美军装备代号)
);
ALTER TABLE 装备 ADD 生产厂商 VARCHAR(100);
ALTER TABLE 部队 MODIFY 地区 VARCHAR(150);
ALTER TABLE 列装 ADD 列装日期 DATE;
INSERT INTO 军种 (名称, 职能) VALUES
('美国陆军', '陆地作战、地面突击、本土防御及海外部署'),
('美国海军', '远洋作战、海上威慑、航母打击群部署及海上封锁'),
('美国空军', '空中作战、战略轰炸、空天防御及空中运输'),
('美国海军陆战队', '两栖作战、快速反应及海外应急行动'),
('美国海岸警卫队', '沿海巡逻、搜救、缉毒及海上安全执法');
INSERT INTO 部队 (番号, 地区, 类型, 隶属军种) VALUES
('第七舰队', '西太平洋', '航母战斗群', '美国海军'),
('第101空降师', '欧洲战区', '空降部队', '美国陆军'),
('第3海军陆战队远征部队', '亚太地区', '两栖作战部队', '美国海军陆战队'),
('第8航空队', '美国本土', '战略轰炸机部队', '美国空军'),
('第5海岸警卫区', '大西洋沿岸', '巡逻部队', '美国海岸警卫队');
INSERT INTO 装备 (美军装备代号, 类型, 数量, 部署地区, 生产年份, 生产厂商) VALUES
('M1A2艾布拉姆斯', '主战坦克', 2500, '欧洲前沿基地', 1992, '通用动力陆地系统'),
('F-35A', '战斗机', 380, '美国本土及亚太基地', 2015, '洛克希德·马丁'),
('尼米兹级CVN-76', '航空母舰', 1, '波斯湾', 1995, '纽波特纽斯造船厂'),
('AH-64阿帕奇', '武装直升机', 800, '全球热点地区', 1986, '波音公司'),
('THAAD反导系统', '导弹防御系统', 15, '亚太及中东地区', 2008, '洛克希德·马丁');
INSERT INTO 列装 (军种, 装备, 列装日期) VALUES
('美国陆军', 'M1A2艾布拉姆斯', '1992-03-12'),
('美国陆军', 'AH-64阿帕奇', '1986-09-30'),
('美国海军', 'F-35A', '2015-07-22'),
('美国海军', '尼米兹级CVN-76', '1995-09-30'),
('美国空军', 'F-35A', '2016-01-15'),
('美国海军陆战队', 'F-35A', '2015-12-03'),
('美国海军陆战队', 'AH-64阿帕奇', '1987-04-18');
INSERT INTO 使用 (部队, 装备) VALUES
('第七舰队', 'F-35A'),
('第七舰队', '尼米兹级CVN-76'),
('第101空降师', 'M1A2艾布拉姆斯'),
('第101空降师', 'AH-64阿帕奇'),
('第3海军陆战队远征部队', 'F-35A'),
('第8航空队', 'F-35A');
SELECT * FROM 装备;
SELECT * FROM 装备 WHERE 部署地区 LIKE '%欧洲%';
SELECT
m.名称 AS 军种,
COUNT(l.装备) AS 装备数量
FROM 军种 m
LEFT JOIN 列装 l ON m.名称 = l.军种
GROUP BY m.名称
ORDER BY 装备数量 DESC;
SELECT
e.美军装备代号 AS 装备代号,
e.类型 AS 装备类型,
e.数量,
e.部署地区,
m.名称 AS 列装军种,
l.列装日期
FROM 装备 e
JOIN 使用 u ON e.美军装备代号 = u.装备
JOIN 部队 t ON u.部队 = t.番号
JOIN 列装 l ON e.美军装备代号 = l.装备
JOIN 军种 m ON l.军种 = m.名称
WHERE t.番号 = '第七舰队';
UPDATE 装备 SET 数量 = 400 WHERE 美军装备代号 = 'F-35A';
UPDATE 装备
SET 部署地区 = '欧洲及亚太地区',
生产年份 = 2017,
生产厂商 = '洛克希德·马丁公司'
WHERE 美军装备代号 = 'F-35A';
DELETE FROM 使用 WHERE 装备 = 'THAAD反导系统';
DELETE FROM 列装 WHERE 装备 = 'THAAD反导系统';
DELETE FROM 装备 WHERE 美军装备代号 = 'THAAD反导系统';
DELETE FROM 使用 WHERE 部队 IN (SELECT 番号 FROM 部队 WHERE 隶属军种 = '美国海岸警卫队');
DELETE FROM 列装 WHERE 军种 = '美国海岸警卫队';
DELETE FROM 部队 WHERE 隶属军种 = '美国海岸警卫队';
DELETE FROM 军种 WHERE 名称 = '美国海岸警卫队';
START TRANSACTION;
INSERT INTO 军种 (名称, 职能) VALUES ('美国太空军', '太空作战及卫星防御');
INSERT INTO 部队 (番号, 地区, 类型, 隶属军种) VALUES ('太空作战司令部', '全球', '太空部队', '美国太空军');
COMMIT;
CREATE INDEX idx_equipment_type ON 装备(类型);
CREATE INDEX idx_unit_region ON 部队(地区);
CREATE VIEW 部队装备视图 AS
SELECT
t.番号 AS 部队番号,
t.地区 AS 部署地区,
t.类型 AS 部队类型,
e.美军装备代号 AS 装备代号,
e.类型 AS 装备类型,
e.数量 AS 装备数量
FROM 部队 t
JOIN 使用 u ON t.番号 = u.部队
JOIN 装备 e ON u.装备 = e.美军装备代号;
SELECT * FROM 部队装备视图 WHERE 部署地区 = '欧洲战区';