【无标题】

二、多表连接查询 u

-- 创建部门表
 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、查询每个部门的所属员工

mysql> select name,ename 
    from dept3 a left join emp3 b 
    on a.deptno = b.dept_id;

 

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

mysql> select name, ename  
    from dept3 d left join emp3 e 
    on d.deptno = e.dept_id 
    where name = '研发部';

 

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

mysql> select name, ename 
    from dept3 d left join emp3 e 
    on d.deptno = e.dept_id 
    where name ='研发部' or name='销售部';

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

mysql> select name,count(ename) 
            from dept3 d left join emp3 e 
            on d.deptno = e.dept_id
            group by name
            order by count(ename) asc;

 

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

mysql> select name, count(ename)
            from dept3 d left join emp3 e
            on d.deptno = e.dept_id
            group by name
            having count(ename) >= 3;