You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
144 lines
4.4 KiB
144 lines
4.4 KiB
SET NAMES 'utf8mb4';
|
|
SET FOREIGN_KEY_CHECKS = 0;
|
|
SET FOREIGN_KEY_CHECKS = 1;
|
|
|
|
/*人*/
|
|
|
|
DROP TABLE IF EXISTS admin;
|
|
-- 超管
|
|
CREATE TABLE admin (
|
|
username varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
|
|
password varchar(255) DEFAULT NULL,
|
|
name varchar(255) DEFAULT NULL,
|
|
gender varchar(255) DEFAULT NULL,
|
|
age int DEFAULT NULL,
|
|
phone_num varchar(255) DEFAULT NULL,
|
|
email varchar(255) DEFAULT NULL,
|
|
avatar varchar(255) DEFAULT NULL,
|
|
PRIMARY KEY (username) USING BTREE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
|
|
|
|
DROP TABLE IF EXISTS dorm_manager;
|
|
-- 宿管
|
|
CREATE TABLE dorm_manager (
|
|
username varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
|
|
password varchar(255) DEFAULT NULL,
|
|
dormbuild_id int DEFAULT NULL,
|
|
name varchar(255) DEFAULT NULL,
|
|
gender varchar(255) DEFAULT NULL,
|
|
age int DEFAULT NULL,
|
|
phone_num varchar(255) DEFAULT NULL,
|
|
email varchar(255) DEFAULT NULL,
|
|
avatar varchar(255) DEFAULT NULL,
|
|
PRIMARY KEY (username) USING BTREE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
|
|
|
|
DROP TABLE IF EXISTS student;
|
|
-- 学生
|
|
CREATE TABLE student (
|
|
id int NOT NULL AUTO_INCREMENT,
|
|
username varchar(255) DEFAULT NULL,
|
|
password varchar(255) DEFAULT NULL,
|
|
name varchar(255) DEFAULT NULL,
|
|
age int DEFAULT NULL,
|
|
gender varchar(255) DEFAULT NULL,
|
|
phone_num varchar(255) DEFAULT NULL,
|
|
email varchar(255) DEFAULT NULL,
|
|
avatar varchar(255) DEFAULT NULL,
|
|
PRIMARY KEY (id)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
|
|
|
|
DROP TABLE IF EXISTS visitor;
|
|
-- 访客
|
|
CREATE TABLE visitor (
|
|
id int NOT NULL AUTO_INCREMENT,
|
|
name varchar(255) DEFAULT NULL,
|
|
gender varchar(255) DEFAULT NULL,
|
|
phone_num varchar(255) DEFAULT NULL,
|
|
origin_city varchar(255) DEFAULT NULL,
|
|
visit_time varchar(255) DEFAULT NULL,
|
|
content varchar(255) DEFAULT NULL,
|
|
PRIMARY KEY (id)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
|
|
|
|
DROP TABLE IF EXISTS user;
|
|
-- 用户
|
|
CREATE TABLE user (
|
|
id int NOT NULL AUTO_INCREMENT,
|
|
username varchar(255) DEFAULT NULL,
|
|
password varchar(255) DEFAULT NULL,
|
|
identity varchar(255) DEFAULT NULL,
|
|
avatar varchar(255) DEFAULT NULL,
|
|
PRIMARY KEY (id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
|
|
|
|
/*非人*/
|
|
|
|
DROP TABLE IF EXISTS dorm_build;
|
|
-- 宿舍楼
|
|
CREATE TABLE dorm_build (
|
|
id int NOT NULL AUTO_INCREMENT,
|
|
dormbuild_id int DEFAULT NULL,
|
|
dormbuild_name varchar(255) DEFAULT NULL,
|
|
dormbuild_detail varchar(255) DEFAULT NULL,
|
|
PRIMARY KEY (id) USING BTREE
|
|
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
|
|
|
|
DROP TABLE IF EXISTS dorm_room;
|
|
-- 寝室
|
|
CREATE TABLE dorm_room (
|
|
dormroom_id int NOT NULL,
|
|
dormbuild_id int DEFAULT NULL,
|
|
floor_num int DEFAULT NULL,
|
|
max_capacity int DEFAULT NULL,
|
|
current_capacity int DEFAULT NULL,
|
|
first_bed varchar(255) DEFAULT NULL,
|
|
second_bed varchar(255) DEFAULT NULL,
|
|
third_bed varchar(255) DEFAULT NULL,
|
|
fourth_bed varchar(255) DEFAULT NULL,
|
|
PRIMARY KEY (dormroom_id) USING BTREE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
|
|
|
|
DROP TABLE IF EXISTS adjust_room;
|
|
-- 调宿
|
|
CREATE TABLE adjust_room (
|
|
id int NOT NULL AUTO_INCREMENT,
|
|
username varchar(255) DEFAULT NULL,
|
|
name varchar(255) DEFAULT NULL,
|
|
currentroom_id int DEFAULT NULL,
|
|
currentbed_id int DEFAULT NULL,
|
|
towardsroom_id int DEFAULT NULL,
|
|
towardsbed_id int DEFAULT NULL,
|
|
state varchar(255) DEFAULT NULL,
|
|
apply_time varchar(255) DEFAULT NULL,
|
|
finish_time varchar(255) DEFAULT NULL,
|
|
PRIMARY KEY (id) USING BTREE
|
|
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
|
|
|
|
DROP TABLE IF EXISTS repair;
|
|
-- 保修
|
|
CREATE TABLE repair (
|
|
id int NOT NULL AUTO_INCREMENT,
|
|
repairer varchar(255) DEFAULT NULL,
|
|
dormbuild_id int NOT NULL,
|
|
dormroom_id int NOT NULL,
|
|
title varchar(255) DEFAULT NULL,
|
|
content varchar(255) DEFAULT NULL,
|
|
state varchar(255) DEFAULT NULL,
|
|
order_buildtime datetime DEFAULT NULL,
|
|
order_finishtime datetime DEFAULT NULL,
|
|
PRIMARY KEY (id,dormbuild_id,dormroom_id)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=1258344450 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
|
|
|
|
DROP TABLE IF EXISTS notice;
|
|
-- 通知
|
|
CREATE TABLE notice (
|
|
id int NOT NULL AUTO_INCREMENT,
|
|
title varchar(255) DEFAULT NULL,
|
|
content varchar(255) DEFAULT NULL,
|
|
author varchar(255) DEFAULT NULL,
|
|
release_time varchar(255) DEFAULT NULL,
|
|
PRIMARY KEY (id)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
|
|
|
|
select * from admin; |