编辑代码

CREATE DATABASE club_management_simple;
USE club_management_simple;

CREATE TABLE student (
    student_id VARCHAR(10) PRIMARY KEY,
    name VARCHAR(20) NOT NULL,
    major VARCHAR(30) NOT NULL,
    phone CHAR(11) NOT NULL
);

CREATE TABLE teacher (
    teacher_id VARCHAR(8) PRIMARY KEY,
    name VARCHAR(20) NOT NULL
);

CREATE TABLE location (
    location_id VARCHAR(10) PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    capacity INT NOT NULL
);

CREATE TABLE club (
    club_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    establish_date DATE NOT NULL,
    location_id VARCHAR(10) NOT NULL,
    status ENUM('active', 'inactive', 'pending') DEFAULT 'pending',
    description TEXT, 
    FOREIGN KEY (location_id) REFERENCES location(location_id)
);

CREATE TABLE activity (
    activity_id INT AUTO_INCREMENT PRIMARY KEY,
    club_id INT NOT NULL,
    title VARCHAR(100) NOT NULL,
    time DATETIME NOT NULL,
    location_id VARCHAR(10) NOT NULL,
    description TEXT,
    max_participants INT,
    status ENUM('planning', 'ongoing', 'completed', 'canceled') DEFAULT 'planning', 
    FOREIGN KEY (club_id) REFERENCES club(club_id),
    FOREIGN KEY (location_id) REFERENCES location(location_id) 
);

CREATE TABLE membership (
    membership_id INT AUTO_INCREMENT PRIMARY KEY,
    club_id INT NOT NULL,
    student_id VARCHAR(10) NOT NULL,
    role ENUM('president', 'vice_president', 'member') DEFAULT 'member',
    status ENUM('active', 'inactive') DEFAULT 'active', 
    join_date DATETIME DEFAULT CURRENT_TIMESTAMP, 
    FOREIGN KEY (club_id) REFERENCES club(club_id),
    FOREIGN KEY (student_id) REFERENCES student(student_id)
);
CREATE TABLE advisor (
    advisor_id INT AUTO_INCREMENT PRIMARY KEY,
    club_id INT NOT NULL,
    teacher_id VARCHAR(8) NOT NULL,
    FOREIGN KEY (club_id) REFERENCES club(club_id),
    FOREIGN KEY (teacher_id) REFERENCES teacher(teacher_id)
);
CREATE TABLE participation (
    participation_id INT AUTO_INCREMENT PRIMARY KEY,
    activity_id INT NOT NULL,
    student_id VARCHAR(10) NOT NULL,
    signup_date DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (activity_id) REFERENCES activity(activity_id),
    FOREIGN KEY (student_id) REFERENCES student(student_id)
);

INSERT INTO student (student_id, name, major, phone) VALUES
('S2025001', '孙继翔', '目标工程', '13811111111'),
('S2025002', '钟明陶', '目标工程', '13822222222'),
('S2025003', '薛惊天', '目标工程', '13833333333'),
('S2025004', '林逸骎', '目标工程', '13844444444'),
('S2025005', '刘俊泽', '火力控制与指挥', '13855555555');

INSERT INTO teacher (teacher_id, name) VALUES
('3031', '张大巧副教授'),
('3032', '李冰讲师'),
('3033', '李邦杰主任');

INSERT INTO location (location_id, name, capacity) VALUES
('T901', '图书馆九楼', 10),
('T101', '学生活动中心', 200),
('L101', '教学楼101教室', 100),
('S704', '第三实验楼704', 20);

INSERT INTO club (name, establish_date, location_id, status, description) VALUES
('目标研习俱乐部', '2020-01-1', 'T901', 'active', '研透全球目标，锻造火眼金睛');

SET @basketball_club_id = LAST_INSERT_ID();

INSERT INTO advisor (club_id, teacher_id) VALUES
(@basketball_club_id, '3031');

INSERT INTO membership (club_id, student_id, role, status) VALUES
(@basketball_club_id, 'S2025001', 'president', 'active'),
(@basketball_club_id, 'S2025002', 'vice_president', 'active'),
(@basketball_club_id, 'S2025003', 'member', 'active'),
(@basketball_club_id, 'S2025004', 'member', 'active');

INSERT INTO activity (club_id, title, time, location_id, description, max_participants, status) VALUES
(@basketball_club_id, '火眼金睛目标判读比赛', '2022-07-10 9:00:00','T901', '与空军工程大学的交流赛', 100, 'completed');

SET @basketball_activity_id = LAST_INSERT_ID();

INSERT INTO participation (activity_id, student_id) VALUES
(@basketball_activity_id, 'S2025001'),
(@basketball_activity_id, 'S2025002'),
(@basketball_activity_id, 'S2025003'),
(@basketball_activity_id, 'S2025004'),
(@basketball_activity_id, 'S2025005'); 

SELECT 
    club_id AS '社团ID',
    name AS '社团名称',
    establish_date AS '成立日期',
    status AS '状态',
    description AS '社团描述'
FROM club
WHERE club_id = @basketball_club_id;

SELECT 
    t.name AS '指导老师'
FROM advisor a
JOIN teacher t ON a.teacher_id = t.teacher_id
WHERE a.club_id = @basketball_club_id;

SELECT 
    m.membership_id AS '成员ID',
    s.student_id AS '学号',
    s.name AS '姓名',
    s.major AS '专业',
    m.role AS '角色',
    m.join_date AS '加入日期'
FROM membership m
JOIN student s ON m.student_id = s.student_id
WHERE m.club_id = @basketball_club_id;

SELECT 
    a.activity_id AS '活动ID',
    a.title AS '活动名称',
    a.time AS '活动时间',
    l.name AS '活动地点',
    a.description AS '活动描述',
    a.status AS '活动状态'
FROM activity a
JOIN location l ON a.location_id = l.location_id
WHERE a.club_id = @basketball_club_id;

SELECT 
    p.participation_id AS '参与ID',
    s.student_id AS '学号',
    s.name AS '姓名',
    s.major AS '专业',
    p.signup_date AS '报名时间'
FROM participation p
JOIN student s ON p.student_id = s.student_id
WHERE p.activity_id = @basketball_activity_id;