MySQL多表查询练习

1.找出销售部门中年纪最大的员工的姓名

mysql> select name,age from dept a ,emp_new b where a.dept1=b.dept2 and dept_name='销售'order by age desc limit 1;
+------+------+
| name | age  |
+------+------+
| 荣七 |   64 |
+------+------+
1 row in set (0.00 sec)

2.求财务部门最低工资的员工姓名

mysql> select name from emp_new e join dept d on e.dept2=d.dept1 where d.dept_name='财务' order by incoming limit 1;
+------+
| name |
+------+
| 王五 |
+------+
1 row in set (0.00 sec)

3. 列出每个部门收入总和高于 9000 的部门名称

mysql> select dept.dept_name from emp_new join dept on emp_new.dept2=dept.dept1 group by dept.dept_name having sum(emp_new.incoming) > 9000;
+-----------+
| dept_name |
+-----------+
| 财务      |
| 销售      |
+-----------+
2 rows in set (0.00 sec)

 4. 求工资在 7500 到 8500 元之间，年龄最大的人的姓名及部门

mysql> select name,dept_name from emp_new join dept on emp_new.dept2=dept.dept1 where incoming between 7500 and 8500 order by age limit 1;
+------+-----------+
| name | dept_name |
+------+-----------+
| 赵六 | 销售      |
+------+-----------+
1 row in set (0.00 sec)

5.找出销售部门收入最低的员工入职时间

mysql> select worktime_start '入职时间' from emp_new join dept on emp_new.dept2=dept.dept1 where dept.dept_name ='销售' order by incoming limit 1;
+------------+
| 入职时间   |
+------------+
| 1970-10-11 |
+------------+
1 row in set (0.00 sec)

6.财务部门收入超过2000元的员工姓名

mysql> select name from emp_new join dept on emp_new.dept2=dept.dept1 where dept.dept_name ='财务' and incoming > 2000;
+------+
| name |
+------+
| 张三 |
| 李四 |
+------+

7.列出每个部门的平均收入及部门名称

mysql> select dept_name,avg(emp_new.incoming) '平均收入' from emp_new join dept on emp_new.dept2=dept.dept1 group by dept.dept_name;
+-----------+-----------+
| dept_name | 平均收入  |
+-----------+-----------+
| 财务      | 3166.6667 |
| 销售      | 8000.0000 |
| 运维      | 7300.0000 |
+-----------+-----------+
3 rows in set (0.00 sec)mysql> select dept_name,round(avg(emp_new.incoming),2) '平均收入' from emp_new join dept on emp_new.dept2=dept.dept1 group by dept.dept_name;
+-----------+----------+
| dept_name | 平均收入 |
+-----------+----------+
| 财务      |  3166.67 |
| 销售      |  8000.00 |
| 运维      |  7300.00 |
+-----------+----------+
3 rows in set (0.00 sec)

8.运维部入职员工的员工号

mysql> select sid '员工号' from emp_new join dept on emp_new.dept2=dept.dept1  where dept.dept_name ='运维';
+--------+
| 员工号 |
+--------+
|   1879 |
+--------+
1 row in set (0.00 sec)

9.财务部门的收入总和；

mysql> select sum(incoming) '总收入' from emp_new join dept on emp_new.dept2=dept.dept1 where dept.dept_name ='财务';
+--------+
| 总收入 |
+--------+
|   9500 |
+--------+
1 row in set (0.00 sec)

10.先按部门号大小排序，再依据入职时间由早到晚排序员工信息表

mysql> select * from emp_new join dept on emp_new.dept2=dept.dept1 order by emp_new.dept2,emp_new.worktime_start;
+------+------+------+----------------+----------+-------+-------+-----------+
| sid  | name | age  | worktime_start | incoming | dept2 | dept1 | dept_name |
+------+------+------+----------------+----------+-------+-------+-----------+
| 1789 | 张三 |   35 | 1980-01-01     |     4000 |   101 |   101 | 财务      |
| 1674 | 李四 |   32 | 1983-04-01     |     3500 |   101 |   101 | 财务      |
| 1776 | 王五 |   24 | 1990-07-01     |     2000 |   101 |   101 | 财务      |
| 1564 | 荣七 |   64 | 1963-10-11     |     8500 |   102 |   102 | 销售      |
| 1568 | 赵六 |   57 | 1970-10-11     |     7500 |   102 |   102 | 销售      |
| 1879 | 牛八 |   55 | 1971-10-20     |     7300 |   103 |   103 | 运维      |
+------+------+------+----------------+----------+-------+-------+-----------+
6 rows in set (0.00 sec)

11.找出哪个部门还没有员工入职；

mysql> select dept.dept_name from dept left join emp_new on emp_new.dept2=dept.dept1 where emp_new.dept2 is null;
+-----------+
| dept_name |
+-----------+
| 行政      |
+-----------+
1 row in set (0.00 sec)

12.列出部门员工收入大于7000的部门编号，部门名称；

mysql> select dept.dept1,dept.dept_name from emp_new join dept on emp_new.dept2=dept.dept1 where emp_new.incoming > 7000 group by dept.dept1,dept.dept_name;
+-------+-----------+
| dept1 | dept_name |
+-------+-----------+
|   102 | 销售      |
|   103 | 运维      |
+-------+-----------+
2 rows in set (0.00 sec)

13.列出每一个部门的员工总收入及部门名称；

mysql> select dept.dept_name '部门名称',sum(emp_new.incoming) '员工总收入'  from emp_new join dept on emp_new.dept2=dept.dept1 group by dept.dept_name;
+----------+------------+
| 部门名称 | 员工总收入 |
+----------+------------+
| 财务     |       9500 |
| 销售     |      16000 |
| 运维     |       7300 |
+----------+------------+
3 rows in set (0.00 sec)

14.列出每一个部门中年纪最大的员工姓名，部门名称；

mysql> select emp_new.name,dept.dept_name from emp_new join dept on emp_new.dept2=dept.dept1 where(emp_new.dept2,emp_new.age) in (select dept2,max(age) from emp_new group by dept2);
+------+-----------+
| name | dept_name |
+------+-----------+
| 张三 | 财务      |
| 荣七 | 销售      |
| 牛八 | 运维      |
+------+-----------+
3 rows in set (0.00 sec)mysql> select name as "姓名", dept_name as "部门名称" from dept,(select max(age) age,dept2 from emp_new group by dept2) em, emp_new where dept.dept1=em.dept2 and em.age=emp_new.age;
+------+----------+
| 姓名 | 部门名称 |
+------+----------+
| 张三 | 财务     |
| 荣七 | 销售     |
| 牛八 | 运维     |
+------+----------+
3 rows in set (0.00 sec)

15.求李四的收入及部门名称

mysql> select e.incoming,d.dept_name from emp_new e join dept d on e.dept2=d.dept1 where e.name='李四';
+----------+-----------+
| incoming | dept_name |
+----------+-----------+
|     3500 | 财务      |
+----------+-----------+
1 row in set (0.00 sec)

16.列出部门员工数大于1个的部门名称

mysql> select d.dept_name from emp_new e join dept d on e.dept2=d.dept1 group by d.dept_name having count(e.sid) > 1;
+-----------+
| dept_name |
+-----------+
| 财务      |
| 销售      |
+-----------+
2 rows in set (0.00 sec)

小结

连接方式

        依业务需求选连接类型。内连接取匹配行；外连接（左、右、全外）保留一侧或两侧表所有行；交叉连接得笛卡尔积，非特定需求慎用。

        理解不同连接对结果集影响，如左连接以左表为主，即便右表无匹配也有左表记录。

连接条件

        精准设置连接条件，基于关联字段建立条件，保证结果准确。

        防止遗漏连接条件致笛卡尔积，使结果集庞大且无意义。

性能优化

        仅选必要字段，不用`SELECT *`，降低数据传输与处理开销。

        为连接字段建索引，加速表连接时数据匹配。  

        大数据量时，避免复杂嵌套子查询，可优化为连接查询提升性能。

字段与别名

        多表有同名字段，用表名.字段名明确所属，防混淆。  

        合理用表与字段别名，让查询易读，别名简洁有意义。

数据完整性

        多表更新删除操作，用事务确保原子性，保证数据一致性。

        操作遵循外键约束，避免破坏数据关联关系。

子查询与联合查询

        子查询注意执行顺序与结果集大小，避免结果集过大拖慢查询。

        联合查询合并结果集，各部分列数与数据类型须一致。