SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for building
-- ----------------------------
DROp TABLE IF EXISTS `building`;
CREATE TABLE `building` (
`dorm_num` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`stu_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`ID` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`build_num` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
`m_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
PRIMARY KEY (`dorm_num`, `stu_id`, `ID`) USING BTREE,
INDEX `work_num`(`ID`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of building
-- ----------------------------
-- ----------------------------
-- Table structure for change_dorm
-- ----------------------------
DROP TABLE IF EXISTS `change_dorm`;
CREATE TABLE `change_dorm` (
`change_num_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`stu_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`old_drom_num` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`new_drom_num` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`status` int NULL DEFAULT NULL,
`reason` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`coun_id` varchar(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
PRIMARY KEY (`change_num_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of change_dorm
-- ----------------------------
-- ----------------------------
-- Table structure for counsellor
-- ----------------------------
DROP TABLE IF EXISTS `counsellor`;
CREATE TABLE `counsellor` (
`coun_id` int NOT NULL,
`coun_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`coun_tel` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`college` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
PRIMARY KEY (`coun_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of counsellor
-- ----------------------------
-- ----------------------------
-- Table structure for dorm_manager
-- ----------------------------
DROP TABLE IF EXISTS `dorm_manager`;
CREATE TABLE `dorm_manager` (
`m_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`ID` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`age` int NOT NULL,
`gender` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`pwd` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`dorm_man_tel` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
PRIMARY KEY (`ID`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of dorm_manager
-- ----------------------------
INSERT INTO `dorm_manager` VALUES ('小钱', '2013', 55, '男', '123456', '13465728921');
INSERT INTO `dorm_manager` VALUES ('小赵', '2014', 55, '女', '123456', '15652532121');
INSERT INTO `dorm_manager` VALUES ('小孙', '2015', 55, '男', '123456', '19823212142');
INSERT INTO `dorm_manager` VALUES ('小张', '2016', 55, '女', '123456', '13267217692');
INSERT INTO `dorm_manager` VALUES ('小李', '2017', 55, '女', '123456', '18976238903');
INSERT INTO `dorm_manager` VALUES ('2022', '2022', 67, '女', '123', '19823212142');
INSERT INTO `dorm_manager` VALUES ('2023', '2023', 70, '男', '123', '19823212142');
-- ----------------------------
-- Table structure for dormitory
-- ----------------------------
DROP TABLE IF EXISTS `dormitory`;
CREATE TABLE `dormitory` (
`dorm_num` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`dormitory_item` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
`peo_num` int NULL DEFAULT NULL,
PRIMARY KEY (`dorm_num`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of dormitory
-- ----------------------------
INSERT INTO `dormitory` VALUES ('2', '窗户损坏', 7);
INSERT INTO `dormitory` VALUES ('201', '漏水', 6);
INSERT INTO `dormitory` VALUES ('504', NULL, 6);
-- ----------------------------
-- Table structure for notice
-- ----------------------------
DROP TABLE IF EXISTS `notice`;
CREATE TABLE `notice` (
`notice_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`content` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`ID` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`time` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
PRIMARY KEY (`notice_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of notice
-- ----------------------------
INSERT INTO `notice` VALUES ('1', '11222', '2022', '2022-06-03');
INSERT INTO `notice` VALUES ('10', '九点关宿舍', '2022', '2022-6-3');
INSERT INTO `notice` VALUES ('11', '六点开门', '2000', '2022-06-04');
INSERT INTO `notice` VALUES ('13', '你好', '2022', '2022-06-03');
INSERT INTO `notice` VALUES ('2', '期末考试通知！
', '2021', '2022-06-03');
INSERT INTO `notice` VALUES ('3', '中关村校区紧急停热抢修通知
', '2020', '2022-06-03');
INSERT INTO `notice` VALUES ('4', '期末考试通知
', '2022', '2022-06-06');
INSERT INTO `notice` VALUES ('5', '2021年度国家社会科学基金项目申报公告！
', '2021', '2022-06-02');
INSERT INTO `notice` VALUES ('6', '为帮助我校教师更深入理解课程思政内涵，提升课程思政建设的意识和能力，2022年1月5日，教师发展中心邀请马克思主义学院副教授孙利老师带来“课程思政三问
', '2022', '2022-06-02');
INSERT INTO `notice` VALUES ('7', '关于东总配电室倒闸操作的停电通知！
', '2023', '2022-06-02');
INSERT INTO `notice` VALUES ('8', '入避免感染风寒！
', '2020', '2022-06-02');
INSERT INTO `notice` VALUES ('9', '入冬提醒近期我校所在地区天气逐渐降低，同学们要注意多穿衣服，少熬夜，避免感染风寒！
', '2022', '2022-06-02');
-- ----------------------------
-- Table structure for ser_item
-- ----------------------------
DROP TABLE IF EXISTS `ser_item`;
CREATE TABLE `ser_item` (
`ser_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`ser_tel` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`dorm_num` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`dorm_item` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`ser_sta` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`ID` int NOT NULL,
PRIMARY KEY (`ID`) USING BTREE,
UNIQUE INDEX `ser_item_ID_uindex`(`ID`) USING BTREE,
INDEX `ser_tel`(`ser_tel`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of ser_item
-- ----------------------------
INSERT INTO `ser_item` VALUES ('小鼠', '1234', '504', '窗台漏水', '1', 1);
INSERT INTO `ser_item` VALUES ('张三', '1234', '504', '阳台漏水', '未完成', 2);
INSERT INTO `ser_item` VALUES ('李四', '123454', '504', '灯坏了', '未完成', 3);
INSERT INTO `ser_item` VALUES ('你好', '1234', '507', '门窗都坏了', '完成中', 4);
INSERT INTO `ser_item` VALUES ('王五', '1234', '503', '门坏了', '完成', 5);
INSERT INTO `ser_item` VALUES ('张三', '1234', '504', '阳台漏水', '未完成', 7);
-- ----------------------------
-- Table structure for servman
-- ----------------------------
DROP TABLE IF EXISTS `servman`;
CREATE TABLE `servman` (
`ser_name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`ser_tel` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`ser_sex` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`ser_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
PRIMARY KEY (`ser_tel`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of servman
-- ----------------------------
-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`stu_id` int NOT NULL,
`stu_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`gender` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`nation` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`college` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`major` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`class` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`dorm_num` int NOT NULL,
`stu_tel` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
PRIMARY KEY (`stu_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES (2, '1', '女', '汉', '计算机', '软件', '班级', 201, NULL);
INSERT INTO `student` VALUES (3, '2', '2', '汉', '2', '2', '2', 2, NULL);
INSERT INTO `student` VALUES (6, '6', '6', '6', '6', '6', '6', 6, NULL);
INSERT INTO `student` VALUES (202021, '小明', '男', '汉', '计算机', '软件工程', '89', 202, NULL);
INSERT INTO `student` VALUES (10181019, '1', '女', '汉', '计算机', '软件', '班级', 201, NULL);
INSERT INTO `student` VALUES (10891067, '1', '女', '汉', '计算机', '软件', '班级', 201, NULL);
INSERT INTO `student` VALUES (20181018, '1', '女', '汉', '计算机', '软件', '班级', 201, NULL);
INSERT INTO `student` VALUES (20202200, '1', '女', '汉', '计算机', '软件', '班级', 201, NULL);
INSERT INTO `student` VALUES (20220604, '上官', '女', '汉', '物理', '物理学', '3', 504, NULL);
-- ----------------------------
-- Table structure for sys_administrator
-- ----------------------------
DROP TABLE IF EXISTS `sys_administrator`;
CREATE TABLE `sys_administrator` (
`ID` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`pwd` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`tel` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
PRIMARY KEY (`ID`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of sys_administrator
-- ----------------------------
INSERT INTO `sys_administrator` VALUES ('123', '123', '123', '123');
-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
`tea_id` int NOT NULL,
`tea_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`tea_tel` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`tea_college` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`tea_mijor` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`tea_class` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
PRIMARY KEY (`tea_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of teacher
-- ----------------------------
-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`username` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
`userpwd` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
`type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES ('20202200', '123456', '学生');
INSERT INTO `user` VALUES ('6', '123456', '学生');
INSERT INTO `user` VALUES ('2', '123456', '学生');
INSERT INTO `user` VALUES ('20181018', '123456', '学生');
INSERT INTO `user` VALUES ('10181019', '123456', '学生');
INSERT INTO `user` VALUES ('10891067', '123456', '学生');
INSERT INTO `user` VALUES ('202021', '123456', '学生');
INSERT INTO `user` VALUES ('20220604', '123456', '学生');
INSERT INTO `user` VALUES ('2022', '123456', NULL);
-- ----------------------------
-- View structure for dorm_num
-- ----------------------------
DROP VIEW IF EXISTS `dorm_num`;
CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `dorm_num` AS select `dormitory`.`dorm_num` AS `DormID`,count(0) AS `DormNum` from (`student` join `dormitory`) where (`student`.`dorm_num` = `dormitory`.`dorm_num`) group by `dormitory`.`dorm_num`;
-- ----------------------------
-- View structure for view_user_info
-- ----------------------------
DROp VIEW IF EXISTS `view_user_info`;
CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `view_user_info` AS select `student`.`stu_id` AS `ID`,`student`.`stu_name` AS `name`,`student`.`gender` AS `gender` from `student` union select `dorm_manager`.`ID` AS `ID`,`dorm_manager`.`m_name` AS `name`,`dorm_manager`.`gender` AS `gender` from `dorm_manager`;
-- ----------------------------
-- View structure for view_user_type
-- ----------------------------
DROp VIEW IF EXISTS `view_user_type`;
CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `view_user_type` AS select `student`.`stu_id` AS `ID`,`student`.`stu_name` AS `name`,`student`.`gender` AS `gender`,'1' AS `type` from `student` union select `dorm_manager`.`ID` AS `ID`,`dorm_manager`.`m_name` AS `name`,`dorm_manager`.`gender` AS `gender`,'2' AS `type` from `dorm_manager`;
-- ----------------------------
-- Procedure structure for dorm_man_notice
-- ----------------------------
DROp PROCEDURE IF EXISTS `dorm_man_notice`;
delimiter ;;
CREATE PROCEDURE `dorm_man_notice`(IN `p_time` varchar(255),OUT `p_name` varchar(255),OUT `p_content` varchar(255))
BEGIN
#Routine body goes here...
END
;;
delimiter ;
-- ----------------------------
-- Procedure structure for DromState
-- ----------------------------
DROP PROCEDURE IF EXISTS `DromState`;
delimiter ;;
CREATE PROCEDURE `DromState`(in p_DromNum int(11))
Begin
declare statusNum int(20);
declare statusNumAll int(20);
declare remainNum int(20);
declare p_sta varchar(20);
select count(*) into statusNum from student where student.dorm_num=p_DromNum;
select peo_num into statusNumAll from dormitory where dormitory.dorm_num=p_DromNum;
if stausNum >= statusNumAll then
set p_sta='满员';
set remainNum = 0;
elseif statusNum= statusNumAll then
set p_sta='满员';
set remainNum = 0;
elseif statusNum4 then
set retStr='高频';
elseif 1<=countNum<=4 then
set retStr='中频';
end if;
select dateStr,retStr;
End
;;
delimiter ;
-- ----------------------------
-- Procedure structure for reader_DormMan_name_pro
-- ----------------------------
DROP PROCEDURE IF EXISTS `reader_DormMan_name_pro`;
delimiter ;;
CREATE PROCEDURE `reader_DormMan_name_pro`(IN readerin VARCHAr(10))
BEGIN
SELECT *
FROM dorm_manager
WHERe m_name LIKE CONCAt('%',readerin,'%')
;
END
;;
delimiter ;
-- ----------------------------
-- Triggers structure for table dorm_manager
-- ----------------------------
DROp TRIGGER IF EXISTS `add_DormMan_after_auto_add_user_trigger`;
delimiter ;;
CREATE TRIGGER `add_DormMan_after_auto_add_user_trigger` AFTER INSERT ON `dorm_manager` FOR EACH ROW Insert into user(username, userpwd) VALUES(NEW.ID, '123456')
;;
delimiter ;
-- ----------------------------
-- Triggers structure for table dorm_manager
-- ----------------------------
DROP TRIGGER IF EXISTS `delete_DromMan_after_auto_add_user_trigger`;
delimiter ;;
CREATE TRIGGER `delete_DromMan_after_auto_add_user_trigger` AFTER DELETE ON `dorm_manager` FOR EACH ROW delete from user where username=OLD.ID
;;
delimiter ;
-- ----------------------------
-- Triggers structure for table student
-- ----------------------------
DROp TRIGGER IF EXISTS `add_student_after_auto_add_user_trigger`;
delimiter ;;
CREATE TRIGGER `add_student_after_auto_add_user_trigger` AFTER INSERT ON `student` FOR EACH ROW Insert into user(username, userpwd) VALUES(NEW.stu_id, '123456')
;;
delimiter ;
-- ----------------------------
-- Triggers structure for table student
-- ----------------------------
DROP TRIGGER IF EXISTS `delete_student_after_auto_add_user_trigger`;
delimiter ;;
CREATE TRIGGER `delete_student_after_auto_add_user_trigger` AFTER DELETE ON `student` FOR EACH ROW delete from user where username=OLD.stu_id
;;
delimiter ;
-- ----------------------------
-- Triggers structure for table student
-- ----------------------------
DROP TRIGGER IF EXISTS `add_student_after_auto_add_dorm_trigger`;
delimiter ;;
CREATE TRIGGER `add_student_after_auto_add_dorm_trigger` AFTER INSERT ON `student` FOR EACH ROW INSERT INTO dormitory (dorm_num, dormitory_item, peo_num) VALUES (NEW.dorm_num, null, peo_num +1)
;;
delimiter ;
SET FOREIGN_KEY_CHECKS = 1;
