mysql 多表查询

create table `dept`(
`dept1` VARCHAR(10) COMMENT "部门id",
`dept_name` VARCHAR(20) COMMENT "部门名称"
) COMMENT "部门类别表";

insert into dept values(101,"chaiwu"),
                       (102,"yunwei"),
                       (103,"xiaoshou"),
                       (104,"jidian"),
                       (105,"zhubo"),
                       (106,"HR");

create table `emp`(
    `id` varchar(10) COMMENT "员工id",
    `name` VARCHAR(25) COMMENT "名字",
    `age` VARCHAR(10) COMMENT "年龄",
    `worktime` VARCHAR(10) COMMENT "入职时间",
    `dept2` varchar(10) COMMENT "部门id",
    `incoming` int(10) COMMENT "工资"
) COMMENT "员工信息表";

insert into emp (id,name,age,worktime,incoming,dept2) 
            VALUES (1500,'wangyi',25,'2022/2/1',8900,101),
                   (1501,'hufei',27,'2022/3/1',7500,102),
                   (1502,'heisen',30,'2022/3/20',8900,102),
                   (1503,'huahudie',25,'2022/8/9',9000,102),
                   (1504,'baimu',25,'2022/01/01',4000,103),
                   (1505,'leilei',27,'2016/05/09',27800,106),
                   (1506,'liuxiaoyi',28,'2017/05/09',16000,105),
                   (1507,'haiwu',27,'2018/09/06',6300,104),
                   (1508,'wanggou',22,'2023/2/1',4900,101);

create table `dept`(
`dept1` VARCHAR(10) COMMENT "部门id",
`dept_name` VARCHAR(20) COMMENT "部门名称"
) COMMENT "部门类别表";
  
create table `emp`(
    `id` varchar(10) COMMENT "员工id",
    `name` VARCHAR(25) COMMENT "名字",dept
    `age` VARCHAR(10) COMMENT "年龄",
    `worktime` VARCHAR(10) COMMENT "入职时间",
    `dept2` varchar(10) COMMENT "部门id",
    `incoming` int(10) COMMENT "工资"
) COMMENT "员工信息表";                          

列出每个部门里面有那些员工及部门名称;
select dept_name,group_concat(name) from dept left join emp on dept.dept1=emp.dept2 group by dept_name;
运维部门的收入总和；
select sum(incoming) "收入总和"from dept left join emp on dept.dept1=emp.dept2 where dept_name="yunwei";
HR部入职员工的员工号
select id from dept left join emp on dept.dept1=emp.dept2 where dept_name="HR";
财务部门收入超过5000元的员工姓名
select  dept_name,incoming from dept left join emnp on dept.dept1=emp.dept2 where dept_name="chaiwu" and incoming>"5000";

找出销售部收入最低的员工的入职时间；
select emp.worktime from dept left join emp on dept.dept1 = emp.dept2 where dept_name="xiaoshou"order by incoming limit 1;

找出年龄小于平均年龄的员工的姓名，ID和部门名称
select id,name,dept_name,age from dept left join emp on dept.dept1 = emp.dept2 where age<(select avg(age)from emp) group by id,name,dept_name,age;

求财务部门最低工资的员工姓名；
select emp.name from dept left join emp on dept.dept1 = emp.dept2 where dept_name="chaiwu" order by  incoming  limit 1;