MySQL自连接查询菜单 mysql自身连接查询

多张表联合起来查询即为连接查询，可分为：

• 内连接：等值连接、非等值连接、自连接
• 外连接：右外连接、左外连接

 也就是先把多张表通过某种指定条件用join...on...语法连接起来，然后再进行where... group by... having等条件查询。

 

内连接中的等值连接

// 从emp表中查询ename，从dept表中查询员工所在的部门名称，即dname
mysql> select e.ename as employee,d.dname as department
    -> from emp e
    -> inner join dept d
    -> on e.deptno=d.deptno;

其中inner可以省略

 

内连接中的非等值连接

// 从emp表中查询ename，从salgrade表中查询员工薪资等级
mysql> select e.ename employee,s.grade
    -> from emp e
    -> join salgrade s
    -> on e.sal between s.losal and s.hisal;

 

内连接中的自连接，也就是自己相当于多张表

// 从emp表中查询ename和员工的领导的名字
mysql> select e.ename employee,l.ename leader
    -> from emp e
    -> join emp l
    -> on e.mgr=l.empno;

在该查询结果中，不包括没有领导的员工

 

外连接中表有主次之分，而内连接中表没有主次之分。主表就是查询其中的全部数据，关联查询此表中的数据。右外连接表示join右侧的表是主表，左外连接则join左侧的是主表

// 从emp表中查询ename和员工领导的名字，没有领导的员工也要包括
mysql> select e.ename employee,l.ename leader
    -> from emp e
    -> left outer join emp l
    -> on e.mgr=l.empno;

可以查询到emp表中的全部员工，没有领导的则leader字段显示NULL，上述语句可以改写为右连接，其中的outer可以省略。

mysql> select e.ename employee,l.ename leader
    -> from emp l
    -> right join emp e
    -> on e.mgr=l.empno;

可见，外连接查询的结果的数量>=内连接的查询结果的数量。一条SQL语句中内连接和外连接可以同时存在。

// 从emp表中查询全部员工名字、领导名字，所在部门名字、薪资等级
mysql> select e.ename as employee,l.ename as leader,d.dname as dept,s.grade
    -> from emp e
    -> join dept d
    -> on e.deptno=d.deptno
    -> join salgrade s
    -> on e.sal between s.losal and s.hisal
    -> left join emp l
    -> on e.mgr=l.empno;

上述join...on..语句的顺序不重要，也就是说

mysql> select e.ename as employee, l.ename as leader,d.dname as dept,s.grade
    -> from emp e
    -> left join emp l
    -> on e.mgr=l.empno
    -> join dept d
    -> on e.deptno=d.deptno
    -> join salgrade s
    -> on e.sal between s.losal and s.hisal;

查询出来的结果一样，只是顺序不一样。

 

子查询即查询语句中嵌套查询语句

 where子句中嵌套select：

// 从emp表中查询薪资大于平均薪资的员工姓名和其薪资
mysql> select ename as name,sal as salary
    -> from emp
    -> where sal>(select avg(sal) from emp);

 

from子句中嵌套select，可以把子查询的结果当作一张临时表

// 从emp和salgrade表中查询不同岗位的平均薪资和薪资等级
mysql> select t.job,t.avgsal,s.grade
    -> from (select job,avg(sal) as avgsal from emp group by job) as t
    -> join salgrade s
    -> on t.avgsal between s.losal and s.hisal;

注意，这里最好给子查询的临时表的查询字段都取个别名。

 

此外，union用于合并查询结果集

mysql> select ename,job from emp where job='MANAGER'
    -> union
    -> select ename,job from emp where job='SALESMAN';

与

mysql> select ename,job from emp where job='MANAGER' or job='SALESMAN';

查询结果一样，但是使用union的效率更高。

 

limit用于分页查询，即依照条件取出部分查询结果

mysql> select ename,sal from emp order by sal desc limit 2,3;

其中2是起始位置，0时可以省略，3是取出的结果数量。

mysql> select ename,sal from emp order by sal desc limit 5;
limit (pageNo-1)*pageSize, pageSize;