05.MySQL_练习SQL

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','妈妈')