746 lines
41 KiB
SQL
746 lines
41 KiB
SQL
create table zr_major
|
||
(
|
||
id int auto_increment
|
||
primary key,
|
||
major_type int null comment '1本科 2专科（高职）',
|
||
major_title varchar(255) null comment '专业名称',
|
||
major_code varchar(11) null comment '专业代码',
|
||
major_mood int null comment '专业人气',
|
||
degree varchar(255) null comment '授予学位',
|
||
`system` varchar(50) null comment '学制',
|
||
major_detailed text null comment '专业简介',
|
||
major_course text null comment '专业课程 genre_id',
|
||
man_ratio decimal(10, 2) null comment '男生比例',
|
||
woman_ratio decimal(10, 2) null comment '女生比例',
|
||
genre_id int null comment '专业类别id',
|
||
optional_advise varchar(255) null comment '选科建议',
|
||
getjob_ratio text null comment '近三年就业率',
|
||
effect text null comment '第一印象',
|
||
school text null comment '开设院校id',
|
||
special_id int null comment '爬数据需要',
|
||
create_time int null comment '更新时间',
|
||
level1 int null comment '分类1 1本科 2专科',
|
||
level2 int null comment '分类2',
|
||
level3 int null comment '分类3',
|
||
is_what varchar(500) null comment '专业详解 是什么',
|
||
learn_what varchar(500) null comment '专业详解 学什么',
|
||
do_what varchar(500) null comment '专业详解 干什么',
|
||
offering_courses varchar(500) null comment '开设课程',
|
||
job text null comment '专业就业',
|
||
type varchar(50) null comment '学科门类',
|
||
type_detail varchar(50) null comment '专业类',
|
||
view_week int null comment '周人气',
|
||
view_month int null comment '月人气',
|
||
view_total int null comment '总人气',
|
||
science_ratio varchar(255) null comment '理科比例',
|
||
liberal_ratio varchar(255) null comment '文科比例',
|
||
postgraduate varchar(500) null comment '考研方向',
|
||
celebrity varchar(500) null comment '社会名人',
|
||
job_industry varchar(1000) null comment '就业行业分布',
|
||
job_post varchar(1000) null comment '就业岗位分布',
|
||
job_region varchar(1000) null comment '就业地区分布',
|
||
avg_salary varchar(15) null comment '平均薪酬',
|
||
majorsalaryavg varchar(255) null comment '本专业薪酬',
|
||
allmajorsalaryavg varchar(255) null comment '所有专业薪酬',
|
||
mostemployedeposition varchar(255) null comment '最多就业岗位',
|
||
mostemploymentindustry varchar(255) null comment '最多就业行业',
|
||
mostemploymentarea varchar(255) null comment '最多就业地区'
|
||
)
|
||
comment '专业表' charset = utf8mb3
|
||
row_format = DYNAMIC;
|
||
|
||
create index idx_major_code
|
||
on zr_major (major_code);
|
||
|
||
create index idx_major_title
|
||
on zr_major (major_title);
|
||
|
||
create index idx_type_detail
|
||
on zr_major (type, type_detail);
|
||
|
||
create table zr_major_genre
|
||
(
|
||
id int auto_increment
|
||
primary key,
|
||
name varchar(255) null comment '分类类别',
|
||
parent_id int null comment '所属专业门类/类别id',
|
||
level_id int default 0 null
|
||
)
|
||
comment '专业类别或课程' charset = utf8mb3;
|
||
|
||
create table zr_one_minute
|
||
(
|
||
id int auto_increment
|
||
primary key,
|
||
fraction varchar(100) null comment '分数段',
|
||
num int null comment '人数',
|
||
cum_num int null comment '累计人数',
|
||
section_id int default 0 null comment '学科ID',
|
||
section_name varchar(255) null comment '学科名称',
|
||
province_id int null comment '省ID',
|
||
province varchar(50) null comment '省',
|
||
create_time int null comment '创建时间',
|
||
year int null comment '年份',
|
||
score int default 0 not null comment '分数',
|
||
sum int default 0 not null comment '分数',
|
||
level varchar(5) null comment '1、本专 2、单列类外语'
|
||
)
|
||
charset = utf8mb3
|
||
row_format = COMPACT;
|
||
|
||
create table zr_planner
|
||
(
|
||
id int unsigned auto_increment
|
||
primary key,
|
||
username varchar(64) default '' not null comment '账户名',
|
||
password varchar(64) default '' not null comment '密码',
|
||
wx_id varchar(255) default '' not null comment '微信openid',
|
||
type int default 1 not null comment '教师类型(1.普通教师 2.志愿规划师 3.专家咨询师 4.3职业规划师)',
|
||
status int default 0 not null comment '认证状态(0.未认证 1.认证中 2.已认证 3.拒绝)',
|
||
createtime int default 0 not null comment '注册时间',
|
||
mobile varchar(11) default '' not null comment '手机号',
|
||
source int default 6 not null comment '1小程序 2公众号 3h5 4头条 5抖音6app',
|
||
avatar varchar(255) not null comment '头像',
|
||
usersig varchar(255) default '' not null comment 'usersig',
|
||
expiration_time int not null comment 'usersig过期时间',
|
||
balance int unsigned default '0' not null comment '余额 单位：分',
|
||
frozen_amount int unsigned default '0' not null comment '冻结金额 单位：分',
|
||
level int unsigned default '1' not null comment '等级id',
|
||
merchant char(8) default '' not null comment '商户号',
|
||
score float(3, 2) unsigned default 5.00 not null comment '评分',
|
||
on_line int unsigned default '0' not null comment '是否是线上：0 线下 1线上',
|
||
deleted int null
|
||
)
|
||
comment '教师信息表' collate = utf8mb4_unicode_ci;
|
||
|
||
create index pid
|
||
on zr_planner (wx_id(191));
|
||
|
||
create table zr_planner_appointment_im
|
||
(
|
||
id int auto_increment
|
||
primary key,
|
||
p_id int unsigned default '0' not null,
|
||
member_id int unsigned default '0' not null,
|
||
status smallint unsigned default '0' not null comment '状态 0申请 1同意 2拒绝',
|
||
start_time int unsigned default '0' not null comment '开始时间',
|
||
end_time int unsigned not null comment '结束时间',
|
||
created_at int unsigned not null comment '申请时间',
|
||
updated_at int unsigned default '0' not null comment '修改时间',
|
||
content varchar(255) default '' not null comment '审批备注'
|
||
)
|
||
comment '预约规划师交流表' charset = utf8mb3;
|
||
|
||
create table zr_planner_cert
|
||
(
|
||
id int auto_increment
|
||
primary key,
|
||
real_name varchar(100) null comment '真实姓名',
|
||
identification tinyint default 1 null comment '身份证明 1 身份证 2护照',
|
||
idcard varchar(18) null comment '身份证 || 回乡证 2护照号码',
|
||
identif_img varchar(255) null comment '护照图 或 回乡证',
|
||
t_type tinyint default 0 null comment '(1普通老师 2:志愿规划师，3:专家咨询师，4职业规划师)',
|
||
zg_type tinyint(1) default 1 null comment '1 教师认证 2高学历认证 3专业认证 4其他认证',
|
||
certificate_img varchar(255) null comment '资格证书图片',
|
||
certificate_name varchar(255) null comment '证书名称',
|
||
head_img varchar(255) null comment '头像',
|
||
main_cat int null comment '主营类目(使用t_type字段)',
|
||
email varchar(50) null comment '邮箱',
|
||
brief varchar(255) null comment '个人简介(旧表专家描述)',
|
||
t_experience text null comment '教学工作经历',
|
||
t_achievements text null comment '教学工作成果',
|
||
phone varchar(11) null comment '手机',
|
||
z_card varchar(255) null comment '身份证正面',
|
||
f_card varchar(255) null comment '身份证反面',
|
||
letter varchar(255) null comment '承诺书',
|
||
status tinyint default 0 null comment '0未申请 1待审核 2审核通过 3审核拒绝 ',
|
||
create_time int null comment '创建时间',
|
||
update_time int null comment '修改时间',
|
||
school_name varchar(255) null comment '任职学校',
|
||
teacher_subject tinyint null comment '教学科目(1:语文，2:数学 3.英语 4.思想政治 5.地理 6.历史 7.化学 8.生物 9.物理)',
|
||
title varchar(255) null comment '老师标签',
|
||
accept_status tinyint unsigned default '1' not null comment '专家可接受预约状态(1:开启，2:关闭)',
|
||
accept_start_time char(8) default '0' not null comment '专家接受预约开始时间',
|
||
accept_end_time char(8) default '0' not null comment '专家接受预约结束时间',
|
||
fraction decimal(4, 2) null comment '专家分数',
|
||
image varchar(255) null comment '专家形象照',
|
||
address varchar(255) null comment '所在地址',
|
||
is_apply tinyint default 0 null comment '老师申请状态(0:未申请，1:已申请)',
|
||
p_id int not null comment '关联规划师id',
|
||
profession varchar(255) null comment '职业名称',
|
||
hot int unsigned default '1' not null comment '热度：0无 1火爆 2紧张'
|
||
)
|
||
comment '教师认证' charset = utf8mb3;
|
||
|
||
create table zr_planner_coin_logs
|
||
(
|
||
id int auto_increment
|
||
primary key,
|
||
p_id int not null comment '规划师id',
|
||
member_id int not null comment '用户id',
|
||
order_number varchar(40) not null comment '订单号',
|
||
money int default 0 not null comment '变动金额 单位：分',
|
||
frozen_amount int default 0 not null comment '冻结金额变动 单位分',
|
||
created_at int unsigned default '0' not null comment '创建时间',
|
||
type int unsigned default '0' not null comment '类型 1 提现申请 2提现成功 3提现失败 4佣金入账 5预约订单成立'
|
||
)
|
||
comment '规划师资金流动表' charset = utf8mb3;
|
||
|
||
create table zr_planner_level
|
||
(
|
||
id int unsigned auto_increment
|
||
primary key,
|
||
level_name varchar(30) not null comment '等级昵称',
|
||
amount int unsigned default '0' not null comment '工资金额'
|
||
)
|
||
comment '规划师等级表' collate = utf8mb3_croatian_ci;
|
||
|
||
create table zr_planner_logs
|
||
(
|
||
id int auto_increment
|
||
primary key,
|
||
path varchar(255) not null comment '路径',
|
||
type varchar(255) not null comment '类型',
|
||
title varchar(255) not null comment '标题',
|
||
remarks varchar(255) not null comment '备注',
|
||
created_at datetime default CURRENT_TIMESTAMP null comment '创建时间',
|
||
m_id int not null comment '学生id',
|
||
p_id int not null comment '规划师id',
|
||
params varchar(255) not null comment '参数'
|
||
)
|
||
comment '规划师操作日志' charset = utf8mb3;
|
||
|
||
create table zr_planner_major
|
||
(
|
||
id int unsigned auto_increment
|
||
primary key,
|
||
school_major_id int not null comment '学校专业分数id',
|
||
volunteer_id int not null comment '学生志愿表id',
|
||
p_id int not null comment '教师id'
|
||
)
|
||
comment '教师端志愿填报关联表' charset = utf8mb3;
|
||
|
||
create table zr_planner_member_follow
|
||
(
|
||
id int unsigned auto_increment
|
||
primary key,
|
||
p_id int unsigned not null comment '规划师id',
|
||
member_id int unsigned not null comment '学员id'
|
||
)
|
||
comment '关注表' charset = utf8mb3;
|
||
|
||
create table zr_planner_merchant
|
||
(
|
||
id int unsigned auto_increment
|
||
primary key,
|
||
username varchar(30) not null comment '用户名',
|
||
password varchar(64) not null comment '密码',
|
||
status int unsigned default '1' not null comment '状态 1可用 0申请中 2拒绝',
|
||
merchant char(8) not null comment '商户号',
|
||
public_key varchar(480) not null comment '商户公钥',
|
||
balance int unsigned default '0' not null comment '余额：单位分',
|
||
frozen_amount int unsigned default '0' not null comment '冻结金额：单位分',
|
||
consumption int unsigned default '0' not null comment '累计消费：单位分',
|
||
private_key varchar(1750) not null comment '商户号私钥',
|
||
created_at int unsigned default '0' not null,
|
||
updated_at int unsigned default '0' not null,
|
||
phone char(11) default '' not null comment '手机号',
|
||
email varchar(64) default '' not null comment '邮箱',
|
||
company varchar(255) not null comment '公司名称',
|
||
address varchar(255) not null comment '公司地址',
|
||
legal_person varchar(20) not null comment '法人',
|
||
credit_number varchar(32) not null comment '统一社会信用代码',
|
||
business_license varchar(255) not null comment '营业执照',
|
||
ip varchar(255) not null comment '白名单ip',
|
||
configure varchar(255) default '' not null comment '配置项',
|
||
constraint merchant
|
||
unique (merchant),
|
||
constraint phone
|
||
unique (phone),
|
||
constraint username
|
||
unique (username)
|
||
)
|
||
comment '规划师商户表' collate = utf8mb3_croatian_ci;
|
||
|
||
create table zr_planner_merchant_coin_log
|
||
(
|
||
id int unsigned auto_increment
|
||
primary key,
|
||
merchant_number char(8) default '0' not null comment '商户号',
|
||
order_id int unsigned default '0' not null comment '订单ID',
|
||
order_number char(32) default '' not null comment '订单号',
|
||
type int unsigned default '1' not null comment '类型1充值 2预约规划服务 3规划服务预约成功 4规划服务预约失败',
|
||
money int default 0 not null comment '金额：分',
|
||
frozen_amount int default 0 not null comment '冻结金额：分',
|
||
created_at int unsigned default '0' not null,
|
||
remarks varchar(255) default '' null comment '备注信息'
|
||
)
|
||
comment '商户资金流动记录表' charset = utf8mb3;
|
||
|
||
create table zr_planner_merchant_count
|
||
(
|
||
id int unsigned auto_increment
|
||
primary key,
|
||
number int unsigned not null comment '配置个数'
|
||
)
|
||
comment '商户号配置信息' charset = utf8mb3;
|
||
|
||
create table zr_planner_merchant_level
|
||
(
|
||
id int unsigned auto_increment
|
||
primary key,
|
||
level_name varchar(30) not null comment '等级名称',
|
||
money int unsigned default '0' not null comment '金额',
|
||
sort int unsigned default '1' not null comment '排序',
|
||
duration int unsigned default '0' null comment '有效时长 ：天',
|
||
created_at int unsigned default '0' not null,
|
||
updated_at int unsigned default '0' not null,
|
||
differ int unsigned default '0' not null comment '跟下一级相差多少金额 0为不可升级'
|
||
)
|
||
comment '商户服务等级表' collate = utf8mb3_croatian_ci;
|
||
|
||
create table zr_planner_merchant_level_info
|
||
(
|
||
id int unsigned auto_increment
|
||
primary key,
|
||
m_l_id int unsigned default '0' not null comment '商户等级id',
|
||
p_l_id int unsigned default '0' not null comment '规划师等级id',
|
||
give_number int unsigned default '0' not null comment '赠送次数',
|
||
money int unsigned default '0' not null comment '单次金额',
|
||
created_at int unsigned default '0' not null,
|
||
updated_at int unsigned default '0' not null
|
||
)
|
||
comment '服务等级详情表' collate = utf8mb3_croatian_ci;
|
||
|
||
create table zr_planner_merchant_member
|
||
(
|
||
id int unsigned auto_increment
|
||
primary key,
|
||
merchant_number char(8) null comment '商户号',
|
||
username varchar(32) not null comment '学员名',
|
||
mobile char(11) not null comment '手机号',
|
||
school_name varchar(100) default '' not null comment '学校名称',
|
||
nemt_year int unsigned default '0' not null comment '年份',
|
||
gender tinyint unsigned not null comment '性别 1女 2男',
|
||
grade int unsigned default '0' not null comment '学员年级 1=>''高一'', 2=>''高二'', 3=>''高三'', 4=>''其他''',
|
||
province_id int unsigned default '0' not null comment '省id',
|
||
province varchar(30) not null comment '省',
|
||
subject varchar(32) not null comment '科目',
|
||
subject_id int unsigned default '0' not null comment '科目 1.理科 2.文科 3.综合 4.艺文 5艺理 6.不分文理 (2 文科 1理科 3艺体 )',
|
||
estimate int unsigned default '0' not null comment '等效分',
|
||
fraction int unsigned default '0' not null comment '高考分数',
|
||
ranking int unsigned default '0' not null comment '高考位次',
|
||
select_subject varchar(255) not null comment '选科',
|
||
intention_province_id varchar(255) default '' null,
|
||
intention_province varchar(255) default '' null comment '意向省市',
|
||
intention_school_id varchar(255) default '' null,
|
||
intention_school varchar(255) default '' null comment '意向学校',
|
||
intention_major_id varchar(255) default '' null,
|
||
intention_major varchar(255) default '' null comment '意向专业',
|
||
status int unsigned default '1' not null comment '状态 ',
|
||
number int unsigned default '0' not null comment '被服务次数',
|
||
created_at datetime default CURRENT_TIMESTAMP not null,
|
||
updated_at int unsigned default '0' not null,
|
||
test_questions text null comment '测试项目：json字符串',
|
||
on_line int unsigned default '1' not null comment '0线下 1线上',
|
||
head_img varchar(255) null comment '头像',
|
||
deleted int default 0 null,
|
||
constraint phone
|
||
unique (mobile, merchant_number)
|
||
)
|
||
comment '商户学员信息' collate = utf8mb3_croatian_ci;
|
||
|
||
create table zr_planner_merchant_order
|
||
(
|
||
id int unsigned auto_increment
|
||
primary key,
|
||
order_number char(32) not null comment '订单号',
|
||
pay_number char(32) not null comment '支付订单号',
|
||
amount int unsigned default '0' not null comment '金额',
|
||
pay_amount int unsigned default '0' not null comment '支付金额',
|
||
m_l_id int unsigned default '0' not null comment '商品服务等级id',
|
||
status int unsigned default '0' not null comment '状态 0 申请 1成功 2失败',
|
||
created_at int unsigned default '0' not null comment '创建时间',
|
||
updated_at int unsigned default '0' not null comment '修改时间',
|
||
merchant_number char(8) not null comment '商户号'
|
||
)
|
||
comment '商户订单' collate = utf8mb3_croatian_ci;
|
||
|
||
create table zr_planner_merchant_service
|
||
(
|
||
id int unsigned auto_increment
|
||
primary key,
|
||
merchant_number char(8) not null comment '商户号',
|
||
m_l_id int unsigned default '0' not null comment '商户服务等级id',
|
||
p_l_id int not null comment '规划师等级id',
|
||
give_number int not null comment '赠送次数',
|
||
money int unsigned default '0' not null comment '每次金额',
|
||
created_at int unsigned default '0' not null,
|
||
updated_at int unsigned default '0' not null,
|
||
expire_at int unsigned default '0' not null comment '到期时间'
|
||
)
|
||
comment '商户服务次数表' collate = utf8mb3_croatian_ci;
|
||
|
||
create table zr_planner_merchant_service_order
|
||
(
|
||
id int unsigned auto_increment
|
||
primary key,
|
||
order_number char(32) not null comment '订单号',
|
||
merchant_number char(8) null comment '商户号',
|
||
member_id int unsigned not null comment '学员id',
|
||
service_id int unsigned default '0' not null comment '服务id',
|
||
im_id int unsigned default '0' not null comment '预约id',
|
||
p_id int unsigned default '0' not null comment '规划师id',
|
||
money int unsigned default '0' not null comment '单次金额',
|
||
created_at datetime default CURRENT_TIMESTAMP null,
|
||
updated_at datetime null,
|
||
status int unsigned default '0' not null comment '状态 1进行中 2已完成 3已取消 4审核中',
|
||
start_time datetime default CURRENT_TIMESTAMP not null comment '开始时间',
|
||
end_time datetime null comment '结束时间',
|
||
content varchar(255) default '' not null comment '备注信息',
|
||
update_at datetime null comment '修改时间'
|
||
)
|
||
comment '规划师服务学员订单表' charset = utf8mb3;
|
||
|
||
create table zr_planner_order
|
||
(
|
||
id int auto_increment
|
||
primary key,
|
||
p_id int not null comment '规划师id',
|
||
member_id int unsigned default '0' not null comment '用户id',
|
||
type smallint unsigned default '1' not null comment '订单类型 1佣金 2提现',
|
||
order_id int unsigned default '0' not null comment '订单id',
|
||
order_number varchar(40) default '' not null comment '订单号',
|
||
proportion int unsigned default '0' not null comment '比例 1~100',
|
||
money int unsigned default '0' not null comment '金额 单位分',
|
||
order_money int unsigned default '0' not null comment '（用户购卡）订单金额 单位分',
|
||
arrival_money int unsigned default '0' not null comment '提现到账金额',
|
||
status tinyint unsigned default '0' not null comment '状态 0未完成 1完成 2超时结束',
|
||
created_at int unsigned default '0' not null comment '创建订单时间',
|
||
updated_at int unsigned default '0' not null comment '修改时间'
|
||
)
|
||
comment '教师佣金列表' charset = utf8mb3;
|
||
|
||
create table zr_planner_province
|
||
(
|
||
id int auto_increment
|
||
primary key,
|
||
name varchar(255) null comment '地区名'
|
||
)
|
||
comment '地区表' charset = utf8mb3;
|
||
|
||
create table zr_planner_student
|
||
(
|
||
id int unsigned auto_increment
|
||
primary key,
|
||
p_id int not null comment '教师id',
|
||
member_id int not null comment '学生id',
|
||
updated_time int default 0 not null,
|
||
create_time int default 0 not null comment '关联时间',
|
||
step int default 0 not null comment '操作步骤（0-未规划 1-专业规划 2-院校规划 3-志愿填报 -4待修改 -5确认结束）',
|
||
grade int default 3 not null comment '学生年级(1.高一 2.高二 3.高三 4.其他)',
|
||
status tinyint default 1 not null comment '状态(1:有效,0:无效)',
|
||
score float(3, 2) unsigned default 5.00 not null comment '评分',
|
||
apply_state tinyint unsigned default '0' not null comment '申请状态0申请中 1通过 2拒绝',
|
||
source char(10) not null comment '学员来源，商户号',
|
||
content varchar(255) default '' not null comment '评论',
|
||
is_adopt smallint default 0 not null comment '是否采纳',
|
||
actual_major varchar(255) default '' not null comment '实际专业',
|
||
actual_school varchar(255) default '' not null comment '实际学校'
|
||
)
|
||
comment '教师的学员表' charset = utf8mb3;
|
||
|
||
create table zr_plannner_step
|
||
(
|
||
id int auto_increment comment '主键'
|
||
primary key,
|
||
value varchar(255) null comment '状态名'
|
||
)
|
||
comment '步骤/状态表' charset = utf8mb3;
|
||
|
||
create table zr_school
|
||
(
|
||
id int auto_increment
|
||
primary key,
|
||
title varchar(100) null comment '学校名称',
|
||
logo varchar(255) null comment '学校logo',
|
||
web_url varchar(500) null comment '官方网址',
|
||
telephone varchar(255) null comment '官方电话',
|
||
email varchar(100) null comment '电子邮箱',
|
||
label varchar(255) null comment '标签',
|
||
campus_scenery text null comment '校园风采',
|
||
brief longtext null comment '学校简介',
|
||
time int null comment '创建时间',
|
||
subordinate varchar(50) null comment '隶属于',
|
||
address varchar(255) null comment '学校地址',
|
||
area_covered decimal(10, 2) null comment '占地面积',
|
||
learning_index decimal(10, 2) null comment '学习指数',
|
||
life_index float(10, 2) null comment '生活指数',
|
||
emp_index decimal(10, 2) null comment '就业指数',
|
||
com_score decimal(10, 2) null comment '综合评分',
|
||
doc_point int null comment '博士点',
|
||
master_program int null comment '硕士点',
|
||
nat_dis int null comment '国家重点学科',
|
||
key_lab int null comment '科研项目',
|
||
dormitory text null comment '宿舍',
|
||
canteen_dormitorytext text null comment '食宿条件图片',
|
||
canteen text null comment '食堂',
|
||
create_time int null comment '创建时间',
|
||
male float(10, 2) null comment '男女比例 男',
|
||
sex float(10, 2) null comment '男女比例 女',
|
||
emp_rate float(10, 2) null comment '就业率',
|
||
china_rate float(10, 2) null comment '国内升学率',
|
||
abroad_rate float(10, 2) null comment '出国率',
|
||
f985 tinyint default 2 null comment '是否985 1是',
|
||
f211 tinyint default 2 null comment '是否211 1是',
|
||
dual_class_name varchar(50) null comment '双一流',
|
||
province_name varchar(100) null comment '省名称',
|
||
province_id int null comment '省ID',
|
||
city_id int null comment '市ID',
|
||
city_name varchar(100) null comment '市名称',
|
||
type int null comment '院校类型',
|
||
school_type int null comment '办学类型',
|
||
school_id int null comment '高考网ID',
|
||
is_sw tinyint(1) default 0 null comment '是否为双万计划学校',
|
||
is_public tinyint default 0 null comment '是否为公办 1公办 2民办 3中外港澳 ',
|
||
dual_class tinyint default 0 null comment '是否为双一流',
|
||
postcode varchar(10) null comment '邮政编码',
|
||
province text null comment '毕业生签约地区流向',
|
||
attr text null comment '毕业生签约单位性质',
|
||
company text null comment '主要签约单位',
|
||
remark text null comment '备注',
|
||
view_total int null comment '人气值',
|
||
ruanke_rank varchar(11) null comment '软科排名',
|
||
xyh_rank int null comment '校友会排名',
|
||
wsl_rank int null comment '武书连排名',
|
||
qs_rank varchar(11) null comment 'QS排名',
|
||
zr_rank int null comment '全国排名',
|
||
code varchar(50) null comment '学校code',
|
||
is_study tinyint default 0 null comment '0 不是研 1为研',
|
||
us_rank int default 0 null comment 'US排名',
|
||
tws_china varchar(11) null comment '泰晤士 排名'
|
||
)
|
||
comment '学校表' charset = utf8mb3;
|
||
|
||
create index school_id
|
||
on zr_school (school_id);
|
||
|
||
create index title
|
||
on zr_school (title);
|
||
|
||
create table zr_school_enrplan
|
||
(
|
||
id int auto_increment
|
||
primary key,
|
||
school_id int null comment 'zr_school school_id',
|
||
title varchar(500) null comment '专业名称',
|
||
cat varchar(50) null comment ' 学科门类',
|
||
cat_id int null comment '学科门类ID
|
||
',
|
||
professional varchar(100) null comment '专业类',
|
||
professional_id int null comment '专业类ID',
|
||
pro_enr int null comment '计划招生',
|
||
`system` varchar(10) null comment '学制',
|
||
section_id int null comment 'dic id',
|
||
section varchar(50) null comment '类型如理科',
|
||
year int null comment '年份',
|
||
create_time int null comment '创建时间',
|
||
province_id int null comment '省id',
|
||
province varchar(50) null comment '省',
|
||
batch_id int null comment 'dic id',
|
||
batch varchar(100) null comment '批次',
|
||
level int null,
|
||
level_name varchar(100) null,
|
||
special_id varchar(50) null comment '更新数据使用',
|
||
special_group int null,
|
||
major_group varchar(24) null comment '专业组',
|
||
major_code varchar(20) null comment '专业代码',
|
||
selected_subject_requirements varchar(255) null comment '选科要求',
|
||
tuition int null,
|
||
school_code varchar(11) null comment '招生代码'
|
||
)
|
||
comment '学校招生计划' engine = MyISAM
|
||
charset = utf8mb3;
|
||
|
||
create index idx_major_code
|
||
on zr_school_enrplan (major_code)
|
||
comment '专业代码';
|
||
|
||
create index idx_year_province_school_code
|
||
on zr_school_enrplan (year, province_id, school_id, school_code);
|
||
|
||
create index school_id
|
||
on zr_school_enrplan (school_id);
|
||
|
||
create index title
|
||
on zr_school_enrplan (title(255));
|
||
|
||
create table zr_school_enrplan_info
|
||
(
|
||
id int auto_increment
|
||
primary key,
|
||
school_id int null comment '学校ID school表id',
|
||
year int null comment '年份',
|
||
province_id int null comment '省ID',
|
||
type varchar(500) default '0' null comment '类',
|
||
batch varchar(500) null comment '批次',
|
||
create_time int null comment '创建时间',
|
||
major_group varchar(255) null comment '专业组'
|
||
)
|
||
comment '学校招生计划关联信息' engine = MyISAM
|
||
charset = utf8mb3;
|
||
|
||
create index school_id
|
||
on zr_school_enrplan_info (school_id);
|
||
|
||
create table zr_school_fra_info
|
||
(
|
||
id int auto_increment
|
||
primary key,
|
||
school_id int null comment '学校ID school表id',
|
||
year int null comment '年份',
|
||
province_id int null comment '省ID',
|
||
type varchar(500) default '0' null comment '类',
|
||
batch varchar(500) null comment '批次',
|
||
create_time int null comment '创建时间'
|
||
)
|
||
comment '学校各省分数线关联信息' engine = MyISAM
|
||
charset = utf8mb3;
|
||
|
||
create index school_id
|
||
on zr_school_fra_info (school_id);
|
||
|
||
create table zr_school_fractional
|
||
(
|
||
id int auto_increment
|
||
primary key,
|
||
year int null comment '年份',
|
||
highest_score int null comment '最高分',
|
||
avg int null comment '平均分',
|
||
lowest_score int null comment '最低分',
|
||
lowest_order int null comment '最低位次',
|
||
pro_control int null comment '省控线',
|
||
type varchar(50) null comment '招生类型',
|
||
batch_id int null comment 'DIC 表ID 录取批次',
|
||
admission_batch varchar(50) null comment '录取批次',
|
||
sigs_id int default 0 null comment 'DIC 表ID 专业组',
|
||
sigs varchar(255) null comment '专业组',
|
||
province_id int null comment '省id',
|
||
province varchar(50) null comment '省',
|
||
section_id int default 0 null comment 'DIC 表ID 类型',
|
||
section varchar(50) null comment '类型',
|
||
create_time int null comment '创建时间',
|
||
school_id int null comment 'zr_school ID',
|
||
selective_grade varchar(255) null comment '选测等级',
|
||
selective_id int null,
|
||
school_name varchar(64) null,
|
||
pro_group varchar(255) null comment '专业组',
|
||
major_score varchar(128) null comment '专业分',
|
||
selected_subject_requirements varchar(255) null comment '选科要求'
|
||
)
|
||
comment '学校各省分数线' charset = utf8mb3;
|
||
|
||
create index school_id
|
||
on zr_school_fractional (school_id);
|
||
|
||
create table zr_school_major
|
||
(
|
||
id int auto_increment
|
||
primary key,
|
||
school_id int null comment '学校 zr_school ID',
|
||
name varchar(100) null comment '专业名称',
|
||
content text null comment '专业介绍',
|
||
is_charact tinyint default 0 null comment '是否为特色专业 0否 1是',
|
||
create_time int null comment '创建时间',
|
||
code varchar(50) null comment 'code',
|
||
year varchar(50) null comment '年份',
|
||
special_type tinyint null comment '1本科 2 专科（高职）',
|
||
major_cat_id int null comment '专业类别ID zr_major_cat',
|
||
special_id int null comment '更新数据需要',
|
||
sigs_id int default 0 null comment '选科要求',
|
||
military_types varchar(500) default '0' null comment '军种类型(0非军队类专业 1海军 2火箭军 3军事航天部队 4军委国防动员部 5空军 6联勤保障部队 7陆军 8网络空间部队 9武警部队 10信息支援部队)'
|
||
)
|
||
comment '学校专业' charset = utf8mb3;
|
||
|
||
create index school_id
|
||
on zr_school_major (school_id);
|
||
|
||
create table zr_school_major_code
|
||
(
|
||
id int auto_increment
|
||
primary key,
|
||
school_code varchar(255) charset utf8mb3 null,
|
||
school_id varchar(255) charset utf8mb3 null,
|
||
major_name varchar(255) charset utf8mb3 null,
|
||
major_code varchar(255) charset utf8mb3 null,
|
||
province varchar(255) charset utf8mb3 null,
|
||
year varchar(10) charset utf8mb3 null,
|
||
subject varchar(10) charset utf8mb3 null,
|
||
batch varchar(255) charset utf8mb3 null
|
||
);
|
||
|
||
create table zr_school_major_score
|
||
(
|
||
id int unsigned auto_increment
|
||
primary key,
|
||
school_id int not null comment 'zr_school ID',
|
||
title char(255) not null comment '专业名称',
|
||
highest_score float(10, 2) null comment '最高分',
|
||
avg float(10, 2) null comment '平均分',
|
||
lowest_score float(10, 2) null comment '最低分',
|
||
lowest_order int null comment '最低位次',
|
||
batch char(32) null comment '录取批次',
|
||
create_time int null comment '创建时间',
|
||
province_id int null comment '省id',
|
||
province char(12) null comment '省',
|
||
year int null comment '年份',
|
||
section char(16) null comment '类型',
|
||
section_id int null comment '类型ID',
|
||
batch_id int null comment '批次ID',
|
||
sigs char(24) null comment '专业组',
|
||
sigs_id int null,
|
||
major_group char(32) default '' not null comment '专业组',
|
||
enrplan_id int default 0 not null comment '招生计划id',
|
||
enrplan int null comment '计划人数',
|
||
is_major_class tinyint unsigned default '0' not null comment '是否是专业组',
|
||
selected_subject_requirements varchar(255) null comment '选科要求',
|
||
special_id varchar(255) null comment '专业ID',
|
||
level2_name varchar(255) null comment '专业二级类别',
|
||
level3_name varchar(255) null comment '专业三级类别',
|
||
level1_name varchar(255) null comment '专业层次',
|
||
level1 varchar(255) null comment '专业层次1级',
|
||
level2 varchar(255) null comment '专业层级2级',
|
||
level3 varchar(255) null comment '专业层级3级'
|
||
)
|
||
comment '学校专业分数线' engine = MyISAM
|
||
charset = utf8mb3
|
||
row_format = DYNAMIC;
|
||
|
||
create index idx_school_province_year
|
||
on zr_school_major_score (school_id, province_id, year);
|
||
|
||
create index idx_school_province_year_batch
|
||
on zr_school_major_score (school_id, province_id, year, batch);
|
||
|
||
create index idx_school_province_year_enrplan
|
||
on zr_school_major_score (school_id, province_id, year, enrplan_id);
|
||
|
||
create index idx_year_level3_lowest
|
||
on zr_school_major_score (year, level3, lowest_score);
|
||
|
||
create index `index`
|
||
on zr_school_major_score (lowest_score, province_id, year, section_id, batch_id);
|
||
|
||
create index school_id
|
||
on zr_school_major_score (school_id);
|
||
|
||
create index title
|
||
on zr_school_major_score (title);
|
||
|
||
create index year
|
||
on zr_school_major_score (year);
|
||
|
||
create definer = root@`%` view 查看库大小 as
|
||
select 1 AS `数据库`, 1 AS `记录数`, 1 AS `数据容量(GB)`, 1 AS `索引容量(MB)`;
|
||
|