MySQL数据库多表关联查询

本文列举数据库的多表关联查询及实际使用，以员工表和部门表作为举例：

部门表：dept

create table `dept`  (
  `deptno` int(2) not null,
  `dname` varchar(14),
  `loc` varchar(13),
  primary key (`deptno`)
);

员工表：emp

create table `emp`  (
  `empno` int(4) not null,
  `ename` varchar(10),
  `job` varchar(9),
  `mgr` int(4),
  `hiredate` date,
  `sal` int(7),
  `comm` int(7),
  `deptno` int(2),
  primary key (`empno`),
  index `fk_deptno`(`deptno`),
  constraint `fk_deptno` foreign key (`deptno`) references `dept` (`deptno`) on delete restrict on update restrict
);

SQL关联查询/连接查询

• 内连接：inner join ... on
• 外连接：left/right join ... on
• 自连接：表自身与自身连接查询

一、内连接

1. 查询员工编号为1的员工姓名和所在部门的名称

select ename,dname from emp inner join dept on emp.deptno=dept.deptno;

二、左外连接（left join 前为主表，后为从表，遍历主表中的每一条记录查询）

1. 查询所有部门的名称和该部门的员工姓名

select dname,ename from dept left join emp on dept.deptno=emp.deptno;

2. 查询所有员工的名称及其所在的部门

select ename,dname from emp left join dept on emp.deptno=dept.deptno;

三、右外连接（right join 前为从表，后为主表，遍历主表中的每一条记录查询）

1. 查询所有部门名称及其下的员工姓名

select dname,ename from emp right join dept on dept.deptno=emp.deptno;

自连接

1. 查询所有员工及其上级领导的姓名

select e.ename 员工,p.ename 上级领导 from emp e left join emp p on e.mgr=p.empno;

子查询/嵌套查询

单行子查询：子查询的结果只有一条

1. 查询和scott在同一部门的员工

select * from emp where deptno=(select deptno from emp where ename='scott');

多行子查询：子查询的结果有多条

1. 查询工资和10号部门员工相等的，且不在10号部门的员工

select * from emp where sal in (select sal from emp where deptno=10) and deptno!=10;