UpdateSql-sunshuai.sql 6.7 KB

--------2021-03-09 基地管理房间预约新加sql  -----
DROP TABLE IF EXISTS `fa_project_building`;
CREATE TABLE `fa_project_building` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `school_id` int(11) NOT NULL COMMENT '所属学校ID',
  `building_name` varchar(200) NOT NULL COMMENT '楼栋名称',
  `building_no` varchar(30) DEFAULT NULL COMMENT '楼栋编号',
  `building_desc` text NOT NULL COMMENT '楼栋介绍',
  `createtime` int(11) DEFAULT NULL COMMENT '创建时间',
  `updatetime` int(11) DEFAULT NULL COMMENT '更新时间',
  `deletetime` int(11) DEFAULT NULL COMMENT '删除时间',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `building_no` (`building_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='楼栋管理表';

DROP TABLE IF EXISTS `fa_project_floor`;
CREATE TABLE `fa_project_floor` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `building_id` int(11) NOT NULL COMMENT '所属楼栋ID',
  `school_id` int(11) NOT NULL COMMENT '所属学校ID',
  `floor_name` varchar(200) NOT NULL COMMENT '楼层名称',
  `floor_no` varchar(30) DEFAULT NULL COMMENT '楼栋编号',
  `createtime` int(11) DEFAULT NULL COMMENT '创建时间',
  `updatetime` int(11) DEFAULT NULL COMMENT '更新时间',
  `deletetime` int(11) DEFAULT NULL COMMENT '删除时间',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `building_id_2` (`building_id`) USING BTREE,
  UNIQUE KEY `building_id` (`building_id`,`floor_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='楼层管理表';

DROP TABLE IF EXISTS `fa_project_room`;
CREATE TABLE `fa_project_room` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `building_id` int(11) NOT NULL COMMENT '所属楼栋ID',
  `floor_id` int(11) NOT NULL COMMENT '所属楼层ID',
  `school_id` int(11) NOT NULL COMMENT '所属学校ID',
  `college_id` int(11) NOT NULL COMMENT '所属学院ID',
  `specialty_id` int(11) NOT NULL COMMENT '所属学院ID',
  `room_name` varchar(200) NOT NULL COMMENT '房间名称',
  `room_no` varchar(30)  NOT NULL COMMENT '房间编号',
  `room_type` tinyint(2)  NOT NULL COMMENT '【1:会议室;2:办公室;3:路演大厅;4:公共办公区;5:实验室;6:多媒体教室;】',
  `room_status` tinyint(1)  NOT NULL DEFAULT '1' COMMENT '【0:不开放;1:开放;】',
  `build_space` decimal(10,2)  NOT NULL COMMENT '房间建筑面积',
  `room_space` decimal(10,2)  NOT NULL COMMENT '房间使用面积',
  `count_people`int(11) NOT NULL COMMENT '容纳人数',
  `room_desc` text NOT NULL COMMENT '房间介绍',
  `createtime` int(11) DEFAULT NULL COMMENT '创建时间',
  `updatetime` int(11) DEFAULT NULL COMMENT '更新时间',
  `deletetime` int(11) DEFAULT NULL COMMENT '删除时间',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `building_id` (`building_id`) USING BTREE,
  KEY `floor_id` (`floor_id`) USING BTREE,
  KEY `college_id` (`college_id`) USING BTREE,
  KEY `specialty_id` (`specialty_id`) USING BTREE,
  UNIQUE KEY `building_id_2` (`building_id`,`floor_id`,`room_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='房间管理表';


DROP TABLE IF EXISTS `fa_project_floor`;
CREATE TABLE `fa_project_floor` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `building_id` int(11) NOT NULL COMMENT '所属楼栋ID',
  `school_id` int(11) NOT NULL COMMENT '所属学校ID',
  `floor_name` varchar(200) NOT NULL COMMENT '楼层名称',
  `floor_no` varchar(30) DEFAULT NULL COMMENT '楼栋编号',
  `createtime` int(11) DEFAULT NULL COMMENT '创建时间',
  `updatetime` int(11) DEFAULT NULL COMMENT '更新时间',
  `deletetime` int(11) DEFAULT NULL COMMENT '删除时间',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `building_id_2` (`building_id`) USING BTREE,
  UNIQUE KEY `building_id` (`building_id`,`floor_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='楼层管理表';

DROP TABLE IF EXISTS `fa_project_room_order`;
CREATE TABLE `fa_project_room_order` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `room_id` int(11) NOT NULL COMMENT '房间ID',
  `user_id` int(11) NOT NULL COMMENT '申请人ID',
  `apply_date` date NOT NULL COMMENT '预约日期',
  `start_time` int(11)  NOT NULL  COMMENT '预约开始时间',
  `end_time` int(11)  NOT NULL  COMMENT '预约结束时间',
  `use_count` int(4)  NOT NULL COMMENT '使用人数',
  `status` tinyint(2)  NOT NULL default '1' COMMENT '【1:待审核;2:已通过;3:未通过;4:预约已取消】',
  `apply_type` tinyint(1)  NOT NULL DEFAULT '1' COMMENT '【1:个人;2:公司;】',
  `apply_desc` text NOT NULL COMMENT '使用设备说明',
  `createtime` int(11) DEFAULT NULL COMMENT '创建时间',
  `updatetime` int(11) DEFAULT NULL COMMENT '更新时间',
  `deletetime` int(11) DEFAULT NULL COMMENT '删除时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='基地预约申请表';

--------2021-03-10  sunshuai 项目双选 修改 fa_project_double 表 sql  -----
ALTER TABLE `innovation`.`fa_project_double`
MODIFY COLUMN `project_des` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT '双选项目描述' AFTER `project_type`,
MODIFY COLUMN `members_standard` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT '成员要求' AFTER `project_des`,
MODIFY COLUMN `teachers_standard` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT '指导教师要求' AFTER `members_standard`;

ALTER TABLE `innovation`.`fa_project_double`
ADD COLUMN `register_id` int(11) NULL DEFAULT 0 COMMENT 'register表id' AFTER `user_id`;

--------2021-03-12  sunshuai 项目双选 修改 fa_project_double 表 sql  -----
ALTER TABLE `innovation`.`fa_project_double`
ADD COLUMN `school_id` int(11) NOT NULL DEFAULT 0 AFTER `project_container_id`;

--------2021-03-18  sunshuai 标签表 修改 fa_match_tags 表 sql  -----
ALTER TABLE `innovation`.`fa_match_tags`
ADD COLUMN `default` tinyint(1) NULL DEFAULT 0 COMMENT '默认标签 1:是0否' AFTER `school_id`;

ALTER TABLE `innovation`.`fa_review_tags`
ADD COLUMN `default` tinyint(1) NULL DEFAULT 0 COMMENT '默认标签1是0否' AFTER `school_id`;



CREATE TABLE `innovation`.`fa_banner`  (
  `banner_id` int(11) NOT NULL AUTO_INCREMENT,
  `image` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT 'banner地址',
  `url` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '跳转地址',
  `school_id` int(11) NULL DEFAULT 0 COMMENT '学校id',
  `sort` int(11) NOT NULL DEFAULT 1 COMMENT '显示顺序',
  `status` enum('normal','hidden') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'normal' COMMENT '状态',
  `createtime` int(11) NOT NULL DEFAULT 0,
  `updatetime` int(11) NOT NULL,
  `deletetime` int(11) DEFAULT NULL,
  PRIMARY KEY (`banner_id`)
);

ALTER TABLE `innovation`.`fa_banner` ENGINE = InnoDB;