用mysql写一个学生宿舍管理系统,其中有登陆表,宿舍基本信息表,专业基本信息表,学生基本信息表,住宿表,卫生信息表等。并显示其代码。在每个表中插入三到五个记录。
时间: 2024-02-20 19:59:16 浏览: 21
好的,以下是一个简单的学生宿舍管理系统的MySQL代码示例:
创建登陆表:
```
CREATE TABLE login (
id INT NOT NULL AUTO_INCREMENT,
username VARCHAR(50) NOT NULL,
password VARCHAR(50) NOT NULL,
PRIMARY KEY (id)
);
```
创建宿舍基本信息表:
```
CREATE TABLE dormitory (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
capacity INT NOT NULL,
PRIMARY KEY (id)
);
```
创建专业基本信息表:
```
CREATE TABLE major (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
PRIMARY KEY (id)
);
```
创建学生基本信息表:
```
CREATE TABLE student (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
sex ENUM('male', 'female') NOT NULL,
age INT NOT NULL,
major_id INT NOT NULL,
dormitory_id INT NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (major_id) REFERENCES major(id),
FOREIGN KEY (dormitory_id) REFERENCES dormitory(id)
);
```
创建住宿表:
```
CREATE TABLE accommodation (
id INT NOT NULL AUTO_INCREMENT,
student_id INT NOT NULL,
dormitory_id INT NOT NULL,
stay_date DATE NOT NULL,
leave_date DATE NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (student_id) REFERENCES student(id),
FOREIGN KEY (dormitory_id) REFERENCES dormitory(id)
);
```
创建卫生信息表:
```
CREATE TABLE hygiene (
id INT NOT NULL AUTO_INCREMENT,
dormitory_id INT NOT NULL,
date DATE NOT NULL,
cleanliness ENUM('excellent', 'good', 'fair', 'poor') NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (dormitory_id) REFERENCES dormitory(id)
);
```
向每个表中插入三到五个记录:
```
INSERT INTO login (username, password) VALUES
('admin', 'password'),
('user1', '123456'),
('user2', 'abcdef');
INSERT INTO dormitory (name, capacity) VALUES
('Dorm A', 100),
('Dorm B', 150),
('Dorm C', 200);
INSERT INTO major (name) VALUES
('Computer Science'),
('Business Administration'),
('Psychology');
INSERT INTO student (name, sex, age, major_id, dormitory_id) VALUES
('Alice', 'female', 20, 1, 1),
('Bob', 'male', 21, 2, 1),
('Charlie', 'male', 19, 3, 2),
('David', 'male', 20, 1, 2),
('Eve', 'female', 22, 2, 3);
INSERT INTO accommodation (student_id, dormitory_id, stay_date, leave_date) VALUES
(1, 1, '2022-01-01', '2022-06-30'),
(2, 1, '2022-01-01', '2022-06-30'),
(3, 2, '2022-01-01', '2022-06-30'),
(4, 2, '2022-01-01', '2022-06-30'),
(5, 3, '2022-01-01', '2022-06-30');
INSERT INTO hygiene (dormitory_id, date, cleanliness) VALUES
(1, '2022-01-01', 'excellent'),
(1, '2022-02-01', 'good'),
(2, '2022-01-01', 'fair'),
(2, '2022-02-01', 'poor'),
(3, '2022-01-01', 'good');
```
这只是一个简单的示例,实际上一个完整的学生宿舍管理系统可能需要更多的表和字段,并且需要更复杂的查询和操作。