多表关系

CREATE TABLE dept (
  id          INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT 'ID, 主键',
  name        VARCHAR(10) NOT NULL UNIQUE COMMENT '部门名称',
  create_time DATETIME DEFAULT NULL COMMENT '创建时间',
  update_time DATETIME DEFAULT NULL COMMENT '修改时间'
) COMMENT '部门表';

CREATE TABLE emp (
  id          INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT 'ID, 主键',
  username    VARCHAR(20) NOT NULL UNIQUE COMMENT '用户名',
  password    VARCHAR(32) DEFAULT '123456' COMMENT '密码',
  name        VARCHAR(10) NOT NULL COMMENT '姓名',
  gender      TINYINT UNSIGNED NOT NULL COMMENT '性别, 1:男, 2:女',
  phone       CHAR(11) NOT NULL UNIQUE COMMENT '手机号',
  job         TINYINT UNSIGNED COMMENT '职位, 1班主任, 2讲师, 3学工主管, 4教研主管, 5咨询师',
  salary      INT UNSIGNED COMMENT '薪资',
  image       VARCHAR(255) COMMENT '头像',
  entry_date  DATE COMMENT '入职日期',
  dept_id     INT UNSIGNED COMMENT '部门ID',   -- 关联 dept.id，实现一对多
  create_time DATETIME COMMENT '创建时间',
  update_time DATETIME COMMENT '修改时间'
) COMMENT '员工表';

ALTER TABLE emp
  ADD CONSTRAINT fk_emp_dept
  FOREIGN KEY (dept_id) REFERENCES dept(id);

-- 用户基本信息表
CREATE TABLE tb_user (
    id     INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT 'ID',
    name   VARCHAR(10) NOT NULL COMMENT '姓名',
    gender TINYINT UNSIGNED NOT NULL COMMENT '性别, 1 男 2 女',
    phone  CHAR(11) COMMENT '手机号',
    degree VARCHAR(10) COMMENT '学历'
) COMMENT '用户基本信息表';

-- 测试数据
INSERT INTO tb_user VALUES (1,'白眉鹰王',1,'18812340001','初中'),
                           (2,'青翼蝠王',1,'18812340002','大专'),
                           (3,'金毛狮王',1,'18812340003','初中'),
                           (4,'紫衫龙王',2,'18812340004','硕士');

-- 用户身份信息表
CREATE TABLE tb_user_card (
    id           INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT 'ID',
    nationality  VARCHAR(10) NOT NULL COMMENT '民族',
    birthday     DATE NOT NULL COMMENT '生日',
    idcard       CHAR(18) NOT NULL COMMENT '身份证号',
    issued       VARCHAR(20) NOT NULL COMMENT '签发机关',
    expire_begin DATE NOT NULL COMMENT '有效期限-开始',
    expire_end   DATE COMMENT '有效期限-结束',
    user_id      INT UNSIGNED NOT NULL UNIQUE COMMENT '用户ID',
    CONSTRAINT fk_user_id FOREIGN KEY (user_id) REFERENCES tb_user(id)
) COMMENT '用户身份信息表';

-- 测试数据
INSERT INTO tb_user_card VALUES (1,'汉','1960-11-06','100000100000100001','朝阳区公安局','2000-06-10',NULL,1),
        (2,'汉','1971-11-06','100000100000100002','静安区公安局','2005-06-10','2025-06-10',2),
        (3,'汉','1963-11-06','100000100000100003','昌平区公安局','2006-06-10',NULL,3),
        (4,'回','1980-11-06','100000100000100004','海淀区公安局','2008-06-10','2028-06-10',4);

-- 学生表
CREATE TABLE tb_student (
    id   INT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
    name VARCHAR(10) COMMENT '姓名',
    no   VARCHAR(10) COMMENT '学号'
) COMMENT '学生表';

-- 学生表测试数据
INSERT INTO tb_student(name, no) VALUES ('黛绮丝', '2000100101'),
                                        ('谢逊', '2000100102'),
                                        ('殷天正', '2000100103'),
                                        ('韦一笑', '2000100104');

-- 课程表
CREATE TABLE tb_course (
    id   INT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
    name VARCHAR(10) COMMENT '课程名称'
) COMMENT '课程表';

-- 课程表测试数据
INSERT INTO tb_course (name) VALUES ('Java'), ('PHP'), ('MySQL'), ('Hadoop');

-- 学生课程表（中间表）
CREATE TABLE tb_student_course (
    id         INT AUTO_INCREMENT COMMENT '主键' PRIMARY KEY,
    student_id INT NOT NULL COMMENT '学生ID',
    course_id  INT NOT NULL COMMENT '课程ID',
    CONSTRAINT fk_courseid  FOREIGN KEY (course_id)  REFERENCES tb_course(id),
    CONSTRAINT fk_studentid  FOREIGN KEY (student_id) REFERENCES tb_student(id)
) COMMENT '学生课程中间表';

-- 学生课程表测试数据
INSERT INTO tb_student_course(student_id, course_id) VALUES (1,1),(1,2),(1,3),(2,2),(2,3),(3,4);