文章目录
一、多表连接1、应用场景2、等值连接3、非等值连接4、自连接5、标准连接(1).交叉连接(2).自然连接(3).using子句连接(4).on子句连接(5).外连接二、知识点补充1、自动行号一、多表连接
1、应用场景
常常用于主表和从表的连接上。
对于并发量特别巨大的表或数据库，建议取消主从关系，也就是不设置外键。阿里规范：数据库设计时，可以使用主从关系，但是建立物理数据库时，必须删除。
2、等值连接
格式
select 列名列表from 表1, 表2where 表1.列1 = 表2.列2[ and 条件表达式];
使用示例
selectempno, ename, job, mgr, hiredate, sal, comm,d.deptno, dname, locfrom emp e, dept dwheree.deptno = d.deptnoand job = 'CLEAR'and dname = 'RESEARCH';
注意点
没有任何条件的多表查询，结果将会是所有被查询列的笛卡尔积
3、非等值连接
使用示例
selecte.ename, e.sal, s.gradefrom emp e, salgrade swhere e.sal between s.losal and s.hisal;
4、自连接
应用场景
往往用于树形表（用来描述目录或类别）
eg：
使用示例
selecte.empno, e.ename, e.job, e.hiredate, e.sal, m, e.mgr,m.ename as MGR_Namefrom emp e, emp mwhere e.mgr = m.empno;
5、标准连接
(1).交叉连接
说明
cross join
查询的结果即为各列的笛卡尔积
使用示例
select ename, dnamefrom empcross join dept;-- 多表查询的效果与上述语句效果相同select ename, dnamefrom emp, dept;
(2).自然连接
说明
natural join
自然连接是对两个表之间相同名字和数据类型的列进行的等值连接，查询的效果相当于等值连接
注意点
如果两个表之间相同名称的列的数据类型不同，则会产生错误
使用示例
selectempno, ename, job, mgr, hiredate, sal, comm,d.deptno, dname, locfrom emp enatural join dept d;-- 多表查询的效果与上述语句效果相同selectempno, ename, job, mgr, hiredate, sal, comm,d.deptno, dname, locfrom emp e, dept dwheree.deptno = d.deptno
(3).using子句连接
使用示例
-- 用using子句指定连接与(2)中示例效果相同selectempno, ename, job, mgr, hiredate, sal, comm,d.deptno, dname, locfrom emp ejoin dept d using (deptno);
(4).on子句连接
使用示例
-- 用on子句指定连接与(2)中示例效果相同selectempno, ename, job, mgr, hiredate, sal, comm,d.deptno, dname, locfrom emp ejoin dept d on e.deptno = d.deptno;-- 三表查询select e.empno, e.ename, d.dname, m.enamefrom emp ejoin dept d on e.deptno = d.deptnojoin emp m on e.mgr = m.empno;-- 可以用外连接弥补没有显示“老板”的缺点
(5).外连接
说明
使用示例
-- 左外连接示例selecte.empno, e.name,d.deptno, d.dnamefrom emp eleft join dept d on e.deptno = d.deptno-- 右外连接示例selecte.empno, e.name,d.deptno, d.dnamefrom emp eleft join dept d on e.deptno = d.deptno-- 查询没有员工的部门信息select d.deptno, dname, locfrom dept dleft join emp e on d.deptno = e.deptnowhere e.deptno is null;-- 查询存在员工的部门信息select distinct d.deptno, dname, locfrom dept djoin emp e on d.deptno = e.deptno;-- 显示所有员工的[编号、项目、领导姓名、部门名称]select e.empno, e.ename, d.dname, m.enamefrom emp eleft join dept d on e.deptno = d.deptnoleft join emp m on e.mgr = m.empno;
注意点
连接查询通常建议使用关键字distinct来消除重复行注意是否需要连续left join或者连续right join
二、知识点补充
1、自动行号
格式
select(@id := @id + 1) autoline, 列名列表from 表名, (select @id := 0) t;
