多表查询 等连接 通过两个表具有相同意义的列,建立连接条件. 查询结果只显示两个列中的值是等值条件的行数据 表中同名列被选择
多表查询
等连接
通过两个表具有相同意义的列,建立连接条件.
查询结果只显示两个列中的值是等值条件的行数据
表中同名列被选择时必须添加表名前缀进行修饰 否则无法确定这一列是属于哪个表
n张表等值连接，条件至少有n-1个，否则会产生笛卡尔积
不等连接
a表中的某列数据和b表中一列或多列的关系是非等值关系,大于,小于,不等于,等条件都属于不等连接的范畴
自连接
数据都来自一张表,所以在from字句中需要对表添加别名,添加表别名后才能合法化的引用表中的列名.
本质就是将一张表虚拟成了两张表
外连接
即是选择出满足等连接条件及其以外的行
(+)修饰符号用法:放置在选出结果只包含等连接的列后,则另一列的结果就是等值行+非等值行
全连接（oracle 不提供连接关键字，，需要用集合模式实现）
sql1999（sql99） 有全连接的关键词
当数据需要从多个表中获得时
idle> select * from emp,dept;
...
...
56 rows selected.
这样得到的结果是 56 行,其实两个表产生了笛卡尔集 14 * 4
idle> select count(*) from emp;
count(*)
----------
14
idle> select count(*) from dept;
count(*)
----------
4
idle>
在连接中给定一个等值条件 一般是主键和外键的关系
例如deptno在dept表中是主键 在emp表中是外键
观看dept(部门表)和emp(员工表)的关系
他们是主从关系,dept是主表,emp是从表
emp中的deptno一定要隶属于dept中的deptno
所以他们两个列是等值的
具体的主键和外键关系 我们在以后建表时再详细阐述
等连接
按deptno的等值关系联合两张表的所有列
idle> select * from dept,emp where dept.deptno=emp.deptno;
deptno dname loc empno ename job mgr hiredate sal comm deptno
---------- -------------- ------------- ---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
10 accounting new york 7782 clark manager 7839 1981-06-09 00:00:00 2450 10
10 accounting new york 7839 king president 1981-11-17 00:00:00 5000 10
10 accounting new york 7934 miller clerk 7782 1982-01-23 00:00:00 1300 10
20 research dallas 7566 jones manager 7839 1981-04-02 00:00:00 2975 20
20 research dallas 7902 ford analyst 7566 1981-12-03 00:00:00 3000 20
20 research dallas 7876 adams clerk 7788 1987-05-23 00:00:00 1100 20
20 research dallas 7369 smith clerk 7902 1980-12-17 00:00:00 800 20
20 research dallas 7788 scott analyst 7566 1987-04-19 00:00:00 3000 20
30 sales chicago 7521 ward salesman 7698 1981-02-22 00:00:00 1250 500 30
30 sales chicago 7844 turner salesman 7698 1981-09-08 00:00:00 1500 0 30
30 sales chicago 7499 allen salesman 7698 1981-02-20 00:00:00 1600 300 30
30 sales chicago 7900 james clerk 7698 1981-12-03 00:00:00 950 30
30 sales chicago 7698 blake manager 7839 1981-05-01 00:00:00 2850 30
30 sales chicago 7654 martin salesman 7698 1981-09-28 00:00:00 1250 1400 30
14 rows selected.
idle>
这才是我们要的结果.笛卡尔积几乎我们不会需要.多表查询时基本都带有where子句来描述多个表的关系 避免笛卡尔集
当两个表中有相同的列名时,为了区分 要在列前加上表名作前缀.
不冲突时 直接使用
idle> select deptno,empno,ename,dname,sal from emp a,dept b where b.deptno=a.deptno;
select deptno,empno,ename,dname,sal from emp a,dept b where b.deptno=a.deptno
*
error at line 1:
ora-00918: column ambiguously defined
因为deptno 没有表前缀 冲突
idle>
在联合的两个表内取数据:描述scott在哪个部门
idle> select empno,ename,dname,sal from emp,dept where emp.deptno=dept.deptno and ename='scott';
empno ename dname sal
---------- ---------- -------------- ----------
7788 scott research 3000
idle>
表的别名
格式:表名 别名
给表取别名是很有必要的,因为有的表名很长 不便于引用时书写.
idle> select a.deptno,empno,ename,dname,sal from emp a,dept b where b.deptno=a.deptno and ename='scott';
deptno empno ename dname sal
---------- ---------- ---------- -------------- ----------
20 7788 scott research 3000
idle>
不等连接
就是排除完全相等条件以外的 >,=, between and
主要在于不同表之间显示特定范围的信息(也可以理解成包含关系)
例如: salgrade表把工资划分了5个等级
idle> select * from salgrade;
grade losal hisal
---------- ---------- ----------
1 700 1200
2 1201 1400
3 1401 2000
4 2001 3000
5 3001 9999
idle>
查处每个员工的工资等级
idle> select ename,sal,grade from emp a,salgrade b where sal between losal and hisal;
ename sal grade
---------- ---------- ----------
smith 800 1
james 950 1
adams 1100 1
ward 1250 2
martin 1250 2
miller 1300 2
turner 1500 3
allen 1600 3
clark 2450 4
blake 2850 4
jones 2975 4
scott 3000 4
ford 3000 4
king 5000 5
14 rows selected.
idle>
练习:
1.查询名字是s开头的员工名,员工号,部门号,部门名,部位地理位置
select empno,ename,emp.deptno,dept.dname,dept.loc
from emp,dept
where ename like 's%' and emp.deptno=dept.deptno;
2.查询员工名,部门名,工资,工资等级
select empno,ename,grade
from emp,salgrade
where sal between losal and hisal;
3.查询工作在new york的所有员工
select empno,ename,emp.deptno,dept.dname,dept.loc
from emp,dept
where emp.deptno=dept.deptno and dept.loc='new york';
自连接
同一张表内的连接查询 即把一个表映射成两个表
主要用于表的自参照关系 比如emp中的上下级或层次关系
因为自连接是同一张表之间的链接查询 所以必须定义表别名
例子:根据empno和mgr的对应关系,可以查找出king的下属都有谁
idle> select b.ename||'''s manager is '||a.ename from emp a,emp b where a.empno = b.mgr and a.ename='king';
b.ename||'''smanageris'||a.ename
----------------------------------
blake's manager is king
jones's manager is king
clark's manager is king
idle>