53 lines
2.8 KiB
SQL
53 lines
2.8 KiB
SQL
-- 学院管理、班级管理建表脚本
|
||
-- 班级绑定在学院下，一学院对应多班级
|
||
|
||
DROP TABLE IF EXISTS `gxmu_class`;
|
||
DROP TABLE IF EXISTS `gxmu_college`;
|
||
|
||
CREATE TABLE `gxmu_college` (
|
||
`id` INT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
||
`college_code` VARCHAR(64) NOT NULL COMMENT '学院编码',
|
||
`college_name` VARCHAR(100) NOT NULL COMMENT '学院名称',
|
||
`short_name` VARCHAR(100) DEFAULT NULL COMMENT '学院简称',
|
||
`leader_name` VARCHAR(50) DEFAULT NULL COMMENT '负责人',
|
||
`leader_phone` VARCHAR(30) DEFAULT NULL COMMENT '负责人电话',
|
||
`sort_number` INT DEFAULT 0 COMMENT '排序号',
|
||
`status` TINYINT DEFAULT 1 COMMENT '状态：1启用 0停用',
|
||
`remark` VARCHAR(500) DEFAULT NULL COMMENT '备注',
|
||
`tenant_id` INT DEFAULT NULL COMMENT '租户ID',
|
||
`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`deleted` TINYINT DEFAULT 0 COMMENT '删除标记：0未删 1已删',
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_gxmu_college_code_tenant` (`college_code`, `tenant_id`),
|
||
UNIQUE KEY `uk_gxmu_college_name_tenant` (`college_name`, `tenant_id`),
|
||
KEY `idx_gxmu_college_status` (`status`),
|
||
KEY `idx_gxmu_college_sort` (`sort_number`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='学院管理表';
|
||
|
||
CREATE TABLE `gxmu_class` (
|
||
`id` INT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
||
`college_id` INT NOT NULL COMMENT '学院ID',
|
||
`class_code` VARCHAR(64) NOT NULL COMMENT '班级编码',
|
||
`class_name` VARCHAR(100) NOT NULL COMMENT '班级名称',
|
||
`grade_year` INT DEFAULT NULL COMMENT '年级',
|
||
`counselor_name` VARCHAR(50) DEFAULT NULL COMMENT '辅导员姓名',
|
||
`counselor_phone` VARCHAR(30) DEFAULT NULL COMMENT '辅导员电话',
|
||
`student_count` INT DEFAULT 0 COMMENT '学生人数',
|
||
`sort_number` INT DEFAULT 0 COMMENT '排序号',
|
||
`status` TINYINT DEFAULT 1 COMMENT '状态：1启用 0停用',
|
||
`remark` VARCHAR(500) DEFAULT NULL COMMENT '备注',
|
||
`tenant_id` INT DEFAULT NULL COMMENT '租户ID',
|
||
`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`deleted` TINYINT DEFAULT 0 COMMENT '删除标记：0未删 1已删',
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_gxmu_class_code_tenant` (`class_code`, `tenant_id`),
|
||
UNIQUE KEY `uk_gxmu_class_name_college_tenant` (`college_id`, `class_name`, `tenant_id`),
|
||
KEY `idx_gxmu_class_college_id` (`college_id`),
|
||
KEY `idx_gxmu_class_status` (`status`),
|
||
KEY `idx_gxmu_class_grade_year` (`grade_year`),
|
||
CONSTRAINT `fk_gxmu_class_college_id`
|
||
FOREIGN KEY (`college_id`) REFERENCES `gxmu_college` (`id`) ON UPDATE CASCADE
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='班级管理表';
|