MySQL第四天作业

MySQL第四天作业

学习MySQL第四天：单表查询与多表查询
作业分为两部分，单表查询与多表查询
单表查询部分

多表查询

– 创建部门表

create table if not exists dept3(deptno varchar(20) primary key , -- 部门号name varchar(20) -- 部门名字
);
-- 创建员工表
create table if not exists emp3(eid varchar(20) primary key , -- 员工编号ename varchar(20), -- 员工名字age int, -- 员工年龄dept_id varchar(20) -- 员工所属部门
);
-- 给dept3表添加数据
insert into dept3 values('1001','研发部');
insert into dept3 values('1002','销售部');
insert into dept3 values('1003','财务部');
insert into dept3 values('1004','人事部');
-- 给emp3表添加数据
insert into emp3 values('1','乔峰',20, '1001');
insert into emp3 values('2','段誉',21, '1001');
insert into emp3 values('3','虚竹',23, '1001');
insert into emp3 values('4','阿紫',18, '1001');
insert into emp3 values('5','扫地僧',85, '1002');
insert into emp3 values('6','李秋水',33, '1002');
insert into emp3 values('7','鸠摩智',50, '1002'); 
insert into emp3 values('8','天山童姥',60, '1003');
insert into emp3 values('9','慕容博',58, '1003');
insert into emp3 values('10','丁春秋',71, '1005');

查询表中数据，确保添加成功

1、查询每个部门的所属员工

select name,group_concat(ename) from emp3 right join dept3 on emp3.dept_id=dept3.deptno group by name;

2、查询研发部门的所属员工

select name,group_concat(ename) from emp3,dept3 where emp3.dept_id=dept3.deptno annd name='研发部';

3、查询研发部和销售部的所属员工

select name,group_concat(ename) from emp3,dept3 where emp3.dept_id=dept3.deptno and (name='研发部' or name='销售部') group by name;

4、查询每个部门的员工数,并升序排序

select name,count(1) from emp3,dept3 where emp3.dept_id=dept3.deptno group by name order by 	count(1);

5、查询人数大于等于3的部门，并按照人数降序排序

select name,count(1) from emp3,dept3 where emp3.dept_id=dept3.deptno group by name having count(1)>=3 order by count(1) desc;