mysql多表查询之数据库数据

-- 部门表
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 '部门表';

-- 员工表
create table tb_emp(
	id int 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_url varchar(300) comment '图像url',
	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 '员工表';


-- ================================== 导入测试数据 ====================================
-- tb_dept
insert into tb_dept (id, name,create_time, update_time) values 
(1,'学工部',now(), now()),
(2,'教研部',now(), now()),
(3,'咨询部',now(), now()),
(4,'就业部',now(), now()),
(5,'人事部',now(), now());

-- tb_emp
insert into tb_emp values
(null, 'zhangsanfeng', '123456789', '张三丰', 1, '1.png', 1, '2010-01-01', 1,now(), now()),
(null, 'weiyixiao', '123456789', '韦一笑', 1, '1.png', 1, '2010-01-01', 1,now(), now()),
(null, 'miejueshitai', '123456789', '灭绝师太', 2, '1.png', 2, '2010-01-01', 2, now(), now()),
(null, 'songqingshu', '123456789', '宋青书', 1, '1.png', 3, '2010-01-01', 3,now(), now()),
(null, 'zhangcuishan', '123456789', '张翠山', 1, '1.png', 4, '2010-01-01', 4,now(), now()),
(null, 'zhangwuji', '123456789', '张无忌', 1, '1.png', 5, '2010-01-01', 4,now(), now()),
(null, 'chenyouliang', '123456789', '陈友谅', 1, '1.png', null, '2010-01-01', 4,now(), now());