java连接多个mysql_java基础之MySQL多表查询1

第三节 多表查询

3.1 内连接查询

实际开发中往往需要针对两张甚至更多张数据表进行操作，而这多张表之间需要使用主键和外键关联在一起，然后使用连接查询来查询多张表中满足要求的数据记录。

一条SQL语句查询多个表，得到一个结果，包含多个表的数据。效率高。在SQL99中，连接查询需要使用join关键字实现。

提供了多种连接查询的类型： cross natural using on

交叉连接(CROSS JOIN)是对两个或者多个表进行笛卡儿积操作，所谓笛卡儿积就是关系代数里的一个概念，表示两个表中的每一行数据任意组合的结果。比如：有两个表，左表有m条数据记录，x个字段，右表有n条数据记录，y个字段，则执行交叉连接后将返回m*n条数据记录，x+y个字段。笛卡儿积示意图如图所示。

笛卡尔积不管是否匹配，都连接。没有实际意义，有理论意义 笛卡尔积便于理解连接查询的原理

【示例7】多表连接查询

-- SQL99多表连接查询

-- 查询员工的编号、姓名和部门编号

select * from emp

select empno,ename,deptno from emp

-- 查询员工的编号、姓名和部门编号、部门的名称

-- 交叉连接

select * from dept -- 4

select * from emp -- 14

select *

from emp

cross join dept -- 56 = 4*14

select *

from dept

cross join emp -- 表有左右之分 左连接 右连接

-- 自然连接

-- 会自动的按照所有的同名列进行匹配，并且同名列只显示一次

-- 优点：简单 natural

select *

from emp

natural join dept

select empno,ename,deptno,dname

from emp

natural join dept

-- 缺点：没有指定各个字段属于哪个表，执行时需要进行判断，效率低了

-- 解决：给各个字段指定表的名字.

select emp.empno,emp.ename,deptno,dept.dname -- 自然连接中同名列不能使用表名前缀

from emp

natural join dept

-- 缺点：表名如果长，字段如果多，SQL语句稍显复杂

-- 解决：不使用表名，使用别名

select e.empno,e.ename,deptno,d.dname

from emp e

natural join dept d

-- Using子句

-- 自然连接的缺点：会自动的按照“所有的”同名列进行匹配，如果希望按照某一个同名列进行匹配，自然连接无能为力

-- 解决：Using子句

select *

from emp e

join dept d

using(deptno) -- e.deptno = d.deptno

select e.empno,e.ename,deptno,d.dname

from emp e

join dept d

using(deptno)

-- on子句

-- natural、using的缺点：都必须有同名列 ，前提

-- 连接查询要求两个表必须有关联列(比如emp的deptno和dept的deptno)，但是不要同名

-- 如果有关联列，但是不同名，那怎么办

select empno,ename,d.deptno,d.dname -- 关联列必须使用前缀

from emp e

join dept d

on (e.deptno = d.deptno)

-- 查询员工的编号、姓名和部门编号、部门的名称，要求薪水大于2000

select empno,ename,e.sal,d.deptno,d.dname -- 关联列必须使用前缀

from emp e

join dept d

on (e.deptno = d.deptno)

where sal > 2000

总结

开发中尽量使用on子句 ，不管是否有同名列，都可以使用；虽然稍显复杂，但是可读性高

SQL99连接查询的优点：连接条件和筛选条件是分开

连接条件：on (e.deptno = d.deptno) using(deptno) natural cross

筛选条件 where sal > 2000

SQL92中 连接条件和筛选条件是没有分开的

3.2 外连接查询

内连接：只显示匹配的数据

外连接：显示匹配的数据，还显示(部分或者全部)不匹配的数据，那就是显示(全部!!??)的数据左外连接 left [outer] join 显示匹配的数据和左边表的不匹配的数据

右外连接 right [outer] join 显示匹配的数据和右边表的不匹配的数据

全外连接 full [outer] join 显示匹配的数据，和左、右边表的不匹配的数据。MySQL不支持全外连接，所以只能采取关键字UNION来联合左、右连接的方法

【示例8】外连接查询

-- SQL99连接查询：内连接 外连接

select * from dept; -- 4

select * from emp; -- 14 40号部门没有员工

update emp set deptno = null where empno = 7788; -- scott员工没有部门

-- 13条数据 ，缺少了没有部门的员工scott，缺少了没有员工的部门40；只显示匹配的数据

select *

from emp e

join dept d

on(e.deptno = d.deptno)

-- 左外连接 left [outer] join

select *

from emp e

left join dept d

on(e.deptno = d.deptno)

-- 右外连接 right [outer] join

select *

from emp e

right join dept d

on(e.deptno = d.deptno)

-- 全外连接

select *

from emp e

left join dept d

on(e.deptno = d.deptno)

union

select *

from emp e

right join dept d

on(e.deptno = d.deptno)

本节作业

1. 练习交叉连接、自然连接、using子句、on子句多表连接查询

2. 练习左外连接、右外连接

本知乎号每日更新2篇java基础视频贴，以及2-4篇java基础技术文章，有兴趣的同学可以关注学习。