写一个学生公寓管理系统MySQL文件

以下是一个简单的学生公寓管理系统MySQL文件，包含了学生、公寓、房间、维修等表的设计和示例数据：

-- 创建学生表
CREATE TABLE student (
id INT(11) NOT NULL AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
gender ENUM('male', 'female') NOT NULL,
birthday DATE NOT NULL,
phone VARCHAR(20) NOT NULL,
email VARCHAR(50) NOT NULL,
address VARCHAR(100) NOT NULL,
PRIMARY KEY (id)
);

-- 创建公寓表
CREATE TABLE apartment (
id INT(11) NOT NULL AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
address VARCHAR(100) NOT NULL,
PRIMARY KEY (id)
);

-- 创建房间表
CREATE TABLE room (
id INT(11) NOT NULL AUTO_INCREMENT,
apartment_id INT(11) NOT NULL,
room_number VARCHAR(10) NOT NULL,
capacity INT(11) NOT NULL,
status ENUM('available', 'occupied', 'maintenance') NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (apartment_id) REFERENCES apartment(id)
);

-- 创建维修表
CREATE TABLE maintenance (
id INT(11) NOT NULL AUTO_INCREMENT,
room_id INT(11) NOT NULL,
description TEXT NOT NULL,
start_date DATE NOT NULL,
end_date DATE NOT NULL,
cost DECIMAL(10, 2) NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (room_id) REFERENCES room(id)
);

-- 插入示例数据
INSERT INTO student (name, gender, birthday, phone, email, address)
VALUES
('张三', 'male', '1999-01-01', '13800000001', 'zhangsan@example.com', '北京市海淀区xx路xx号'),
('李四', 'male', '1999-02-02', '13800000002', 'lisi@example.com', '北京市朝阳区xx路xx号'),
('王五', 'female', '1999-03-03', '13800000003', 'wangwu@example.com', '北京市东城区xx路xx号');

INSERT INTO apartment (name, address)
VALUES
('北苑公寓', '北京市朝阳区北苑路xx号'),
('东苑公寓', '北京市朝阳区东苑路xx号');

INSERT INTO room (apartment_id, room_number, capacity, status)
VALUES
(1, '101', 2, 'available'),
(1, '102', 2, 'available'),
(2, '201', 4, 'available'),
(2, '202', 4, 'occupied'),
(2, '203', 4, 'maintenance');

INSERT INTO maintenance (room_id, description, start_date, end_date, cost)
VALUES
(5, '水管漏水', '2021-01-01', '2021-01-03', 100.00),
(5, '电灯损坏', '2021-01-05', '2021-01-07', 80.00);