视频课程:李兴华 Oracle从入门到精通视频课程
学习者:阳光罗诺
视频来源:51CTO学院
总体内容:
- 多表查询的意义以及基本问题。
- 表的连接查询
- SQL:1999语法标准对多表查询的支持。
- 数据的集合操作。
一、认识多表查询
所谓的多表查询就是同时从多张表中取出数据并且显示的一种操作。语法只是做了一些简单的修改。
语法格式:
【③选出所需要的数据列】SELECT [DISTINCT] * 列[别名],列[别名],列[别名]······ 【①确定数据来源(行和列的集合)】FROM 表名称 [别名],表名称 [别名],······ 【②筛选数据行】[WHERE 限定条件] 此时的条件可以是多个语法结构。 【④数据排序】[ORDER BY 排序字段 [ASC|DESC] 可以设置多个]
于是我们就可以按照这样子的语法结构来实现多表查询。本次将会使用emp和dept两张表格进行多表的查询。于是在查询之前,先做一些准备,在数据库中有一个COUNT()函数,这个函数的主要作用:可以统计出一张数据表中的数据量。
准备查询一:查询dept表中的数据量。(4行记录)
代码示例:
select count(*) from dept;
准备查询二:查询emp表中的数据量。(14行记录)
代码示例:
select count(*) from emp;
目前两张表的记录加起来,总记录是18行。根据语法格式可以实现多表的查询。
范例:实现emp与dept的多表查询。
发现在每一行emp表中的记录出现了4次,而4次是dept表中的数据量,所以最终产生了14行X4行=56行记录。
之所以出现这样的情况,主要用于数据库的产生原理有关———数学的集合。在这样子的集合操作里面,我们会将这两个集合(数据表)统一查询,作为乘法的形式出现。结果一定会产生积————笛卡儿积。在任何情况下要进行的多表查询中都一定会存在有笛卡儿积的问题。事实上,这些积的产生对用户而言是没有实质上的用处,需要想办法去消除积。
如果要消除积,就必须有关联字段。
很明显,现在emp和dept数据表之中存在有关联字段(大部分情况下,都习惯将关联字段设置为同名)。我们就可以使用关联字段来消除笛卡儿积。
代码示例:
SELECT * FROM emp,dept WHERE emp.deptno=dept.deptno;
可以得出结论:只要是多表查询,这多张表之间就一定要存在有关联关系。没有关联关系的表是不可能多表查询的。
但是就目前而言,代码依然是存在有问题。
此时进行字段访问的时候采用的是“表名称.字段名称”,如果是表名称短的话,一般是没有什么问题的。而如果表名称长了,例如:“yuzhou_yinghexi_diqiu_yazhoudalu_shanghai_ren”,所以在进行多表查询的时候,强烈建议使用别名。
实际上笛卡儿积的存在对于整个程序的影响是相当巨大的,即使现在可以消除显示的笛卡儿积,但是从本质上来讲,永远无法避免笛卡儿积。
例如:在Oracle的样本数据中有sh的大数据用户。
分析一:取得costs表中的记录数。
SELECT COUNT(*) FROM costs;
多表查询操作案例(分析过程)
范例:要求查询出每一个雇员的编号、姓名、职位、基本工资、部门名称、部门位置。
·确定要使用的数据表
- emp表:雇员的编号、姓名、职位、基本工资
- dept表:部门名称、部门位置
·确定已知的关联字段
- 雇员与部门关联:emp.deptno = dept.deptno
第一步:查询出每一个雇员的编号、姓名、职位、基本工资。现在只需要使用emp一张数据即可。
代码示例:
select e.empno,e.ename,e.job,e.sal from emp e;
第二步:查询出每一个雇员对应的部门信息。需要引入dept表(引入表的时候一定要考虑有关联),这两张表直接可以利用dept表deptno字段关联,所以需要利用WHERE子句来消除笛卡儿积。
代码示例:
select e.empno,e.ename,e.job,e.sal,d.dname,d.loc from emp e,dept d WHERE e.deptno=d.deptno;
以上的操作属于之前基本概念的加强,并且给出了明确的关联字段。可是很多的查询是不会明确给出关联字段的。
范例:要求查询出每一个雇员的编号、姓名、职位、基本工资、工资等级。
- 确定要使用的数据表。
emp表:雇员的编号、姓名、职位、基本工资
salgrade表:工资等级
- 确定已知的关联字段。
雇员与工资等级:emp.sal BETWEEN salgrade.losal AND salgrade.hisal;
第一步:查询出每一个雇员的编号、姓名、职位、基本工资。现在只需要使用emp一张数据即可。
代码示例:
select e.empno,e.ename,e.job,e.sal from emp e;
第二步:增加salgrade表,增加了数据表之后,就需要引入WHERE子句来消除掉笛卡儿积。
代码示例:
select e.empno,e.ename,e.job,e.sal,s.grade from emp e,salgrade s WHERE e.sal BETWEEN s.losal AND s.hisal;
范例:查询每一个雇员的编号、姓名、职位、基本工资、部门名称、工资等级。
确定所需要的数据表:
- emp表:编号、姓名、职位、基本工资
- dept表:部门名称
- salgrade表:工资等级
确定相互关联的字段:
- 雇员与部门:emp.deptno = dept.deptno;
- 雇员与工资等级:emp.sal BETWEEN salgrade.losal AND salgrade.hisal;
第一步:查询每一个雇员的编号、姓名、职位、基本工资。
代码示例:
select e.empno,e.ename,e.job,e.sal from emp e;
第二步:加入部门名称,增加一张表就增加阳光条件就消除笛卡儿积。
代码示例;
select e.empno,e.ename,e.job,e.sal,d.dname from emp e,dept d WHERE e.deptno= d.deptno;
第三步:加入工资等级信息,与原始的消除笛卡儿积条件应该同时满足,所以使用AND 来连接。
示例代码:
select e.empno,e.ename,e.job,e.sal,d.dname from emp e,dept d,salgrade s WHERE e.deptno= d.deptno and e.sal BETWEEN s.losal AND s.hisal;
表的连接
实际上对于两张数据表进行多表查询,对于消除笛卡儿积来讲主要是依靠连接模式来处理的,而对于表的连接模式在数据库的定义上,有两种:
l 内连接:在之前都利用WHERE子句消除了笛卡儿积,这就属于内连接。
l 外连接:分为三种:左外连接、右外连接、全外连接。
为了方便更好的观察到连接的区别,现在已经在dept表中提供了一个没有雇员的部门(40部门),同时在emp表中增加一个没有部门的雇员。
代码示例:
观察1:内连接的实现效果。
观察2:使用左(外)连接。希望所有的雇员信息都显示出来。即使没有对应的部门。
代码示例:
SELECT e.empno,e.ename,d.deptno,d.dname FROM emp e,dept d WHERE e.deptno=d.deptno(+);
此时没有部门的雇员信息出现了,也就是说左表的数据全部显示了。
观察3:使用右(外)连接。将所有的部门信息显示出来。
代码示例:
SELECT e.empno, e.ename, d.deptno, d.dname FROM emp e, dept d WHERE e.deptno(+) = d.deptno;
内连接就是所有满足关联关系的数据出现,不满足的不出现,外连接就是指定一张数据表中的内容全部都显示,但是没有对应的其他的表的数据内容位null。
在Oracle中使用了“(+)”来控制连接方式。
左外连接:关联字段1=关联字段2(+);
右外连接:关联字段1(+)=关联字段2;
大部分情况下一般都只会考虑内连接,但是当你发现所需要的数据不全的时候就可以考虑外连接。、
范例:查询每一个雇员的编号、姓名、职位、领导姓名、领导职位。
确定所需要的数据表。
- emp表(雇员信息):编号、姓名、职位。
- emp表(领导信息):领导姓名、领导职位。
确定已知的关联字段。
- 雇员与领导:emp.mgr = memp.empno
第一步:查询出每一个雇员的编号、姓名、职位。
代码示例:
SELECT e.empno,e.ename,e.job FROM emp e;
第二步:加入领导信息。需要引入自身关联,而后消除笛卡儿积。
代码示例:
SELECT e.empno,e.ename,e.job,m.ename,m.job FROM emp e, emp m WHERE e.mgr=m.empno;
第三步:发现emp表(雇员信息)数据不完整,因为不满足于等值关联判断,所以要让雇员信息显示完整,则必须使用外连接控制。
SQL:1999语法定义
对于数据表的连接操作,从实际使用来讲各个数据库都是有所支持的,所以对于所有的数据库,进行表连接最好的做法是利用以下的语法可以完成:
语法:
SELECT [DISTINCT] * | 列 [别名] ,列 [别名],······ FROM 表1 [别名] [CROSS JOIN 表2 [别名]] [NATURE JOIN 表2 [别名]] [JOIN 表2 [别名] ON (条件)| USING(关联字段)] [LEFT | RIGHT | FULL OUTER JOIN ON(条件)表2];
个人在进行表连接的时候,如果是内连接一定使用等值判断,如果是外连接才会使用LEFT、RIGHT、OUTER等操作。
- 交叉连接:目的是产生笛卡儿积
语法:
SELECT [DISTINCT] * | 列 [别名] ,列 [别名],······ FROM 表1 [别名] [CROSS JOIN 表2 [别名]]
范例:实现交叉连接
代码示例:SELECT * FROM emp CROSS JOIN dept;
- 自然连接:利用关联字段自己进行消除笛卡儿积(只要字段名称相同即可)
语法:
SELECT [DISTINCT] * | 列 [别名] ,列 [别名],······ FROM 表1 [别名] [NATURE JOIN 表2 [别名]]
范例:实现自然连接。(内连接)
代码示例:
SELECT * FROM emp NATURAL JOIN dept;
- 使用自然连接是要求两张表的字段名称相同,但是如果说有一天不相同了呢?或者两张表中中有两组字段都是重名的。所以这种使用ON子句指定关联条件,而利用USING子句设置关联字段。
范例:利用USING子句设置关联字段实现自然连接。
SELECT * FROM emp JOIN dept USING(deptno);
范例:利用ON子句设置关联条件。
SELECT * FROM emp e JOIN dept d ON(e.deptno=d.deptno);
外连接
SELECT [DISTINCT] * | 列 [别名] ,列 [别名],······ FROM 表1 [别名] [LEFT | RIGHT | FULL OUTER JOIN ON(条件)表2];
范例:观察左外连接
SELECT * FROM emp e LEFT OUTER JOIN dept d ON (e.deptno=d.deptno);
范例:观察右外连接。
SELECT * FROM emp e RIGHT OUTER JOIN dept d ON (e.deptno=d.deptno);
范例:全外连接
代码示例:
SELECT * FROM emp e FULL OUTER JOIN dept d ON (e.deptno=d.deptno);
部分截图:
数据集合操作
数学集合:交集、并集、补集。
每一次查询实际上都会返回数据集合,所以返回的结果可以使用UNION、UNION ALL、MINUS、INTERSECT来实现集合的操作。
SELECT [DISTINCT] * 列[别名],列[别名],列[别名]······ FROM 表名称 [别名],表名称 [别名],······ [WHERE 限定条件] 此时的条件可以是多个语法结构。 [ORDER BY 排序字段 [ASC|DESC] 可以设置多个] UNION | UNION ALL | INTERSECT | MINUS SELECT [DISTINCT] * 列[别名],列[别名],列[别名]······ FROM 表名称 [别名],表名称 [别名],······ [WHERE 限定条件] 此时的条件可以是多个语法结构。 [ORDER BY 排序字段 [ASC|DESC] 可以设置多个]