MySQL多表查询练习

MySQL查询-多表

建表：

mysql> create table if not exists emp3(
    ->  eid varchar(20) primary key , -- 员工编号
    ->  ename varchar(20), -- 员工名字
    ->  age int, -- 员工年龄
    ->  dept_id varchar(20) -- 员工所属部门
    -> );
mysql> desc emp3;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| eid     | varchar(20) | NO   | PRI | NULL    |       |
| ename   | varchar(20) | YES  |     | NULL    |       |
| age     | int(11)     | YES  |     | NULL    |       |
| dept_id | varchar(20) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+

mysql> create table if not exists dept3(
    ->  deptno varchar(20) primary key , -- 部门号
    ->  name varchar(20) -- 部门名字
    -> );
    
mysql> DESC dept3;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| deptno | varchar(20) | NO   | PRI | NULL    |       |
| name   | varchar(20) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+

插入数据：

insert into dept3 values('1001','研发部');
insert into dept3 values('1002','销售部');
insert into dept3 values('1003','财务部');
insert into dept3 values('1004','人事部');

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');

查询：

查询每个部门的所属员工：

mysql> SELECT e.ename,d.name from emp3 e inner join dept3 d on e.dept_id=d.deptno ;
+--------------+-----------+
| ename        | name      |
+--------------+-----------+
| 乔峰         | 研发部    |
| 段誉         | 研发部    |
| 虚竹         | 研发部    |
| 阿紫         | 研发部    |
| 扫地僧       | 销售部    |
| 李秋水       | 销售部    |
| 鸠摩智       | 销售部    |
| 天山童姥     | 财务部    |
| 慕容博       | 财务部    |
+--------------+-----------+

查询研发部门的所属员工:

mysql> SELECT d.name,e.ename from emp3 e inner join dept3 d on e.dept_id=d.deptno where d.name='研发部';
+-----------+--------+
| name      | ename  |
+-----------+--------+
| 研发部    | 乔峰   |
| 研发部    | 段誉   |
| 研发部    | 虚竹   |
| 研发部    | 阿紫   |
+-----------+--------+

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

mysql> SELECT d.name,e.ename from emp3 e inner join dept3 d on e.dept_id=d.deptno where d.name in ('研发部','销售部');
+-----------+-----------+
| name      | ename     |
+-----------+-----------+
| 研发部    | 乔峰      |
| 研发部    | 段誉      |
| 研发部    | 虚竹      |
| 研发部    | 阿紫      |
| 销售部    | 扫地僧    |
| 销售部    | 李秋水    |
| 销售部    | 鸠摩智    |
+-----------+-----------+

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

mysql> select d.name,count(1) from dept3 d join emp3 e on e.dept_id=d.deptno group by d.name ;
+-----------+----------+
| name      | count(1) |
+-----------+----------+
| 研发部    |        4 |
| 财务部    |        2 |
| 销售部    |        3 |
+-----------+----------+

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

mysql> select d.name,count(1) from dept3 d join emp3 e on e.dept_id=d.deptno group by d.name having count(1)>=3;
+-----------+----------+
| name      | count(1) |
+-----------+----------+
| 研发部    |        4 |
| 销售部    |        3 |
+-----------+----------+