CREATE TABLE IF NOT EXISTS `hf_teachers`(
id INT PRIMARY KEY AUTO_INCREMENT COMMENT '主键id',
is_deleted VARCHAR(1) NOT NULL DEFAULT 'n' COMMENT '是否删除',
create_time DATETIME DEFAULT current_timestamp COMMENT '创建时间',
modify_time DATETIME DEFAULT current_timestamp COMMENT '修改时间',
teacher_name VARCHAR(20) COMMENT '老师姓名',
teacher_number VARCHAR(20) COMMENT '老师编号',
sex VARCHAR(1) NOT NULL DEFAULT '男' COMMENT '性别',
nation VARCHAR(10) COMMENT '民族',
birthday DATE COMMENT '生日',
mobile BIGINT(11) COMMENT '手机号',
other_contaction VARCHAR(50) COMMENT '其他联系方式',
id_card VARCHAR(18) COMMENT '身份证号',
idcard_address VARCHAR(100) COMMENT '身份证地址',
current_address VARCHAR(100) COMMENT '当前住址',
CONSTRAINT check_deleted CHECK (is_deleted='y' OR is_deleted='n'),
CONSTRAINT check_sex CHECK (sex='男' OR sex='女')
)ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT='老师表';
CREATE TABLE IF NOT EXISTS `hf_classes` (
id INT PRIMARY KEY AUTO_INCREMENT COMMENT '主键id',
is_deleted VARCHAR(1) NOT NULL DEFAULT 'n' COMMENT '是否删除',
create_time DATETIME DEFAULT current_timestamp COMMENT '创建时间',
modify_time DATETIME DEFAULT current_timestamp COMMENT '修改时间',
class_name VARCHAR(50) COMMENT '班级名称',
class_number VARCHAR(20) COMMENT '班级编号',
class_address VARCHAR(100) COMMENT '教室位置',
headermaster_id INT NOT NULL COMMENT '班主任的id',
FOREIGN KEY (headermaster_id) REFERENCES `hf_teachers` (id),
CONSTRAINT check_deleted CHECK (is_deleted='y' OR is_deleted='n')
)ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT='班级表';
CREATE TABLE IF NOT EXISTS `hf_students` (
id INT PRIMARY KEY AUTO_INCREMENT COMMENT '主键id',
is_deleted VARCHAR(1) NOT NULL DEFAULT 'n' COMMENT '是否删除',
create_time DATETIME DEFAULT current_timestamp COMMENT '创建时间',
modify_time DATETIME DEFAULT current_timestamp COMMENT '修改时间',
student_name VARCHAR(20) COMMENT '学生姓名',
student_number VARCHAR(20) COMMENT '学号',
class_id INT NOT NULL COMMENT '班级id',
sex VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性别',
nation VARCHAR(10) COMMENT '民族',
birthday DATE COMMENT '生日',
mobile BIGINT(11) COMMENT '手机号',
other_contaction VARCHAR(50) COMMENT '其他联系方式',
home_address VARCHAR(100) COMMENT '家庭住址',
school_time DATE COMMENT '入学时间',
graduation_time DATE COMMENT '毕业时间',
is_graduated VARCHAR(1) NOT NULL DEFAULT 'n' COMMENT '是否已毕业',
emergency_contact VARCHAR(20) COMMENT '紧急联系人',
contact_mobile BIGINT(11) COMMENT '紧急联系人手机号',
contact_relation VARCHAR(20) COMMENT '紧急联系人与学生关系',
FOREIGN KEY (class_id) REFERENCES `hf_classes` (id),
CONSTRAINT check_deleted CHECK (is_deleted='y' OR is_deleted='n'),
CONSTRAINT check_sex CHECK (sex='男' OR sex='女'),
CONSTRAINT check_graduated CHECK (is_graduated='n' or is_graduated='y')
)ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT='学生表';
INSERT INTO hf_teachers (teacher_name, teacher_number,sex, nation, birthday, mobile,
other_contaction,id_card,idcard_address,current_address)
VALUES('李四','T201861009','男','汉族','1989-09-16','17700010009',
'微信:LiSi1234','311001198909161125','浙江省杭州市余杭区紫金花路002号','浙江省杭州市余杭区紫金花路002号');
INSERT INTO hf_classes (class_name, class_number, class_address, headermaster_id)
VALUES('2018届一年一班','C20180101','一号教学楼101','9');
INSERT INTO hf_students (student_name, student_number, class_id, sex, nation, birthday,
home_address, school_time,
emergency_contact, contact_mobile, contact_relation)
VALUES('小苹果','S2018011001','1','女','汉族','2011-01-01',
'浙江省杭州市余杭区文昌路123号','20180301','大苹果','15800010001','妈妈')
INSERT INTO hf_students (student_name, student_number, class_id, sex, nation, birthday,
home_address, school_time,
emergency_contact, contact_mobile, contact_relation)
VALUES('小蜜蜂','S2018011002','1','女','汉族','2011-03-06',
'浙江省杭州市余杭区文昌路123号','20180301','花花','15800010002','妈妈')
05.MySQL_练习SQL
最新推荐文章于 2022-01-03 23:40:57 发布