多表查询
关联查询 - 作用:可以跨多表查询
查询出员工的名字和他所在部门的名字【错误】
select e.empno,e.ename,d.deptno,d.dname
from emp e,dept d
错误原因:
以上写法会出现笛卡尔积,产生很多冗余错误的数据,如果要
排除笛卡尔积,则应该使用where字句进行条件的过滤.
正确写法:传统方式
select e.empno,e.ename,d.deptno,d.dname
from emp e,dept d
where e.deptno = d.deptno
[表的主键 = 表的外键]
给表格取别名,可以简化SQL语句,还可以区别不同的列
正确写法:join方式
select e.firstname,d.name from semp e join sdept on e.deptid=d.id;
多表连接查询
1. 內连接[ inner ] join : 多表连接满足指定条件的结果集
table1 t1 inner join table2 t2
on t1.列 = t2.列
a. 等值连接 ： 连接的条件是 = 连接 on t1.列 = t2.列
b.不等值连接 ： 连接的条件是 不相等 连接 on t1.列 > t2.列
c.自然连接 natural join (删除重复列)
- 使用表别名可以简化查询
- 使用表名(表别名)前缀可提高查询效率
inner_join.png
2. 外连接 outer join
（2.1） 左外连接 left [ outer ] join ：
把左表与右表满足条件查询出来，与左表不满足条件的记录也需要查询出来
left_outer_join.png
（2.2） 右外连接 right [ outer ] join ：
把左表与右表满足条件查询出来，与右表不满足条件的记录也需要查询出来
right_outer_join.png
3.自连接: 把表复制一份 作为另一个表
注意： 表一定要取别名
示例
(1). 查询出在 ACCOUNTING 部门的员工编号，姓名
select empno, ename
from emp e inner join dept d
on e.deptno = d.deptno
where d.dname = 'ACCOUNTING ';
(2) 查询出所有部门的所有员工，列出所有部门信息、员工信息
select *
from emp e join dept d
on e.deptno = d.deptno;
自然连接
select * from emp natural join dept
(3). 查询在北京工作的员工的平均薪资
select avg(salary)
from emp e join dept d
on e.deptno = d.deptno
where d.loc = 'beijing';
(4).查询出各部门的员工人数（没有员工的部门也需要统计）
select d.deptno , count(e.empno)
from emp e right outer join dept d
on e.deptno = d.deptno
group by d.deptno;
select d.deptno , count(e.empno)
from dept d left outer join emp e
on e.deptno = d.deptno
group by d.deptno;
+--------+----------+
| deptno | count(*) |
+--------+----------+
| 10 | 3 |
| 20 | 5 |
| 30 | 6 |
| 40 | 0 |
| 50 | 0 |
+--------+----------+
(5). 查询出员工编号，姓名，和该员工上级领导的编号与姓名 （给结果列名 取别名）
select e.empno 员工编号, e.ename 员工姓名,e.mgr 上级领导的编号, m.ename 上级领导的名称
from emp e join emp m
on e.mgr = m.empno ;
(6). 查询出员工编号，姓名，和该员工上级领导的编号与姓名 （给结果列名 取别名 ， 没有上级领导的记录也需要查询）
select e.empno 员工编号, e.ename 员工姓名,e.mgr 上级领导的编号, m.ename 上级领导的名称
from emp e left join emp m
on e.mgr = m.empno ;
(7). 查询出各年份员工入职人数
select YEAR(emp.hiredate),COUNT(emp.empno)
from emp
GROUP BY YEAR(emp.hiredate);
(8). 查询出各年份各月份员工入职人数
select YEAR(emp.hiredate),MONTH(emp.hiredate),COUNT(emp.empno)
from emp
GROUP BY YEAR(emp.hiredate),MONTH(emp.hiredate);
(9). 查询出在 ACCOUNTING 部门的员工编号，姓名
select e.empno,e.ename
from emp e right join dept d
on e.deptno=d.deptno
where d.dname='ACCOUNTING';
(10). 查询在北京工作的员工的平均薪资
select d.deptno,d.dname,avg(e.salary)
from emp e join dept d
on e.deptno=d.deptno
where d.loc='beijing'
group by d.deptno
