编辑代码

-- 创建数据库
CREATE DATABASE IF NOT EXISTS military_personnel;
USE military_personnel;

-- 创建表结构
CREATE TABLE Personnel (
    person_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    gender ENUM('男','女') NOT NULL,
    birth_date DATE,
    id_number VARCHAR(18) UNIQUE,
    rank VARCHAR(20) NOT NULL,
    department VARCHAR(50) NOT NULL,
    specialty VARCHAR(50),
    join_date DATE NOT NULL,
    status ENUM('现役','预备役','退役','休假') DEFAULT '现役'
);

CREATE TABLE Training (
    training_id INT PRIMARY KEY AUTO_INCREMENT,
    person_id INT NOT NULL,
    course_name VARCHAR(100) NOT NULL,
    course_type VARCHAR(50),
    score DECIMAL(5,2),
    training_date DATE,
    certificate VARCHAR(100),
    FOREIGN KEY (person_id) REFERENCES Personnel(person_id) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE Mission (
    mission_id INT PRIMARY KEY AUTO_INCREMENT,
    person_id INT NOT NULL,
    mission_name VARCHAR(100) NOT NULL,
    start_date DATE,
    end_date DATE,
    status ENUM('计划中','执行中','已完成','已取消') DEFAULT '计划中',
    FOREIGN KEY (person_id) REFERENCES Personnel(person_id) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE Promotion (
    promotion_id INT PRIMARY KEY AUTO_INCREMENT,
    person_id INT NOT NULL,
    old_rank VARCHAR(20) NOT NULL,
    new_rank VARCHAR(20) NOT NULL,
    promotion_date DATE NOT NULL,
    approver VARCHAR(50),
    FOREIGN KEY (person_id) REFERENCES Personnel(person_id) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE RewardPunish (
    rp_id INT PRIMARY KEY AUTO_INCREMENT,
    person_id INT NOT NULL,
    type ENUM('奖励','处分') NOT NULL,
    reason TEXT,
    rp_date DATE NOT NULL,
    FOREIGN KEY (person_id) REFERENCES Personnel(person_id) ON DELETE CASCADE ON UPDATE CASCADE
);

-- 修改表结构示例
ALTER TABLE Training ADD COLUMN remarks TEXT;

-- 插入数据
INSERT INTO Personnel (name, gender, birth_date, id_number, rank, department, specialty, join_date)
VALUES 
('张三', '男', '1990-01-15', '110101199001151234', '上尉', '陆军步兵旅', '步兵指挥', '2012-09-01'),
('李四', '男', '1985-05-20', '120101198505205678', '少校', '海军驱逐舰支队', '舰艇指挥', '2007-07-01'),
('王五', '女', '1992-08-10', '130101199208102345', '中尉', '空军航空兵', '飞行技术', '2014-06-01');

INSERT INTO Training (person_id, course_name, course_type, score, training_date, certificate)
VALUES 
(1, '高级步兵战术', '战术训练', 92.5, '2023-03-15', '合格'),
(2, '舰艇作战指挥', '指挥训练', 88.0, '2022-10-20', '优秀'),
(3, '空中格斗技术', '飞行训练', 95.0, '2023-01-10', '特级');

INSERT INTO Mission (person_id, mission_name, start_date, end_date, status)
VALUES 
(1, '边境巡逻任务', '2023-05-01', '2023-05-15', '已完成'),
(2, '南海护航行动', '2023-04-10', '2023-06-10', '执行中'),
(3, '空中巡逻任务', '2023-06-05', '2023-06-05', '已完成');

INSERT INTO Promotion (person_id, old_rank, new_rank, promotion_date)
VALUES 
(1, '中尉', '上尉', '2020-07-01'),
(2, '上尉', '少校', '2018-10-01'),
(3, '少尉', '中尉', '2019-06-01');

INSERT INTO RewardPunish (person_id, type, reason, rp_date)
VALUES 
(1, '奖励', '在边境巡逻任务中表现突出', '2023-05-20'),
(2, '奖励', '成功指挥护航行动', '2023-06-15'),
(3, '处分', '违反飞行纪律', '2022-11-05');

-- 查询操作
-- 1. 获取所有人员及其参与的任务
SELECT p.name, p.rank, m.mission_name, m.status
FROM Personnel p
LEFT JOIN Mission m ON p.person_id = m.person_id;

-- 2. 统计各部门人员数量
SELECT department, COUNT(*) AS personnel_count
FROM Personnel
GROUP BY department;

-- 3. 获取有奖励记录的人员
SELECT p.name, rp.type, rp.reason
FROM Personnel p
JOIN RewardPunish rp ON p.person_id = rp.person_id
WHERE rp.type = '奖励';

-- 更新操作
UPDATE Personnel SET rank = '少校' WHERE person_id = 1;

-- 删除操作
DELETE FROM Training WHERE training_id = 1;

-- 删除表（谨慎使用）
DROP TABLE IF EXISTS RewardPunish;