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 *`，降低数据传输与处理开销。
为连接字段建索引，加速表连接时数据匹配。
大数据量时，避免复杂嵌套子查询，可优化为连接查询提升性能。
字段与别名
多表有同名字段，用表名.字段名明确所属，防混淆。
合理用表与字段别名，让查询易读，别名简洁有意义。
数据完整性
多表更新删除操作，用事务确保原子性，保证数据一致性。
操作遵循外键约束，避免破坏数据关联关系。
子查询与联合查询
子查询注意执行顺序与结果集大小，避免结果集过大拖慢查询。
联合查询合并结果集，各部分列数与数据类型须一致。