编辑代码

-- 创建数据库
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;

-- 删除表(演示用,实际慎用)
-- DROP TABLE IF EXISTS 使用;
-- DROP TABLE IF EXISTS 列装;
-- DROP TABLE IF EXISTS 装备;
-- DROP TABLE IF EXISTS 部队;
-- DROP TABLE IF EXISTS 军种;

-- ================ 2. 数据操作 ================

-- 2.1 数据插入(增)
-- 插入军种数据
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');

-- 2.2 数据查询(查)
-- 查询所有装备
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.番号 = '第七舰队';

-- 2.3 数据更新(改)
-- 更新装备数量
UPDATE 装备 SET 数量 = 400 WHERE 美军装备代号 = 'F-35A';

-- 更新多列数据
UPDATE 装备 
SET 部署地区 = '欧洲及亚太地区',
    生产年份 = 2017,
    生产厂商 = '洛克希德·马丁公司'
WHERE 美军装备代号 = 'F-35A';

-- 2.4 数据删除(删)
-- 删除单条装备记录(需先删除关联记录)
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 部署地区 = '欧洲战区';