idea内置数据库DataGrip + 多表操作sql语句

多表操作：一对多（多对一），一对一，一对多

可以通过物理外键实现，但实际上更推荐使用逻辑外键

以下均为物理外键使用方法

-- =====================================多表设计================================
-- -------------------一对多   职位 与 员工
-- 员工       子表
create table tb_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女',
    image varchar(300) comment '图像',
    job tinyint unsigned comment '职位，1班主任，2讲师，3学工管理，4教研管理',
    entrydate date comment '入职时间',
    dept_id int unsigned comment '归属部门的id',     -- 新增
    create_time datetime not null comment '创建时间',
    update_time datetime not null comment '修改时间'
) comment '员工表';
-- 部门       父表
create table tb_dept(
    id int unsigned primary key auto_increment comment 'id',
    name varchar(10) not null unique comment '部门名称',
    create_time datetime not null comment '创建时间',
    update_time datetime not null comment '修改时间'
) comment '部门表';
-- 插入测试数据
insert into tb_dept values
    (1,'学业部',now(),now()),(2,'教研部',now(),now()),(3,'咨询部',now(),now()),
    (4,'就业部',now(),now()),(5,'人事部',now(),now()) ;
insert into tb_emp values
    (null,'11','123456','一',1,'1.jpg',4,'2000-01-01',2,now(),now()),
    (null,'22','123456','二',1,'1.jpg',4,'2001-01-01',2,now(),now()),
    (null,'33','123456','三',1,'1.jpg',4,'2004-01-01',2,now(),now()),
    (null,'44','123456','四',2,'1.jpg',4,'2007-01-01',2,now(),now()),
    (null,'55','123456','五',2,'1.jpg',4,'2009-01-01',2,now(),now()),
    (null,'66','123456','六',3,'1.jpg',4,'2010-01-01',2,now(),now()),
    (null,'77','123456','七',3,'1.jpg',4,'2010-01-01',2,now(),now()),
    (null,'88','123456','八',4,'1.jpg',4,'2011-01-01',2,now(),now()),
    (null,'99','123456','九',4,'1.jpg',4,'2014-01-01',2,now(),now()),
    (null,'1010','123456','十',5,'1.jpg',4,'2016-01-01',2,now(),now()),
    (null,'1111','123456','十一',5,'1.jpg',4,'2018-01-01',2,now(),now()),
    (null,'1212','123456','十二',5,'1.jpg',null,'2021-01-01',2,now(),now()) ;
-- -------------------一对一   用户 与 身份证
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 (null,'ttt',1,'12237334444','初中'),
                           (null,'nnn',1,'12283336444','大专'),
                           (null,'nnn',2,'12233834944','jn'),
                           (null,'nnn',2,'12233734444','xn');
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 '签发机关',
     expier_begin date not null comment '有效期限-开始',
     expier_end date comment '有效期限-结束',
     user_id int unsigned not null unique comment '用户id',       -- unique,一对一关键点，外键唯一
     constraint fk_user_id foreign key (user_id) references tb_user(id)
) comment '用户信息表';
insert into tb_user_card values
    (null,'汉','1960-01-01','100000100000001000','昭阳区公安局','2000-01-01',null,1),
    (null,'回','1970-01-01','100000100000001000','静安区公安局','2005-01-01','2025-01-01',2),
    (null,'汉','1960-01-01','100000100000001000','昌平区公安局','2000-01-01',null,3),
    (null,'汉','1966-01-01','100000100000001000','xxx区公安局','2000-01-01','2020-01-01',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
    ('nnn','10000000'),('lll','10000001'),('kkk','10000002'),('mmm','10000003');
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'),('c#'),('python');
create table tb_student_course(
    id int auto_increment primary key comment '主键',
    student_id int not null comment '学生id',
    course_id int not null comment '课程id',
    constraint fk_course_id foreign key (course_id) references tb_course(id),
    constraint fk_student_id 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);