多表连接技术
7.1解析简单查询:
全表扫描:指针以第1条记录为起点,按顺序逐行加工,直至第1条记录的末尾
横向的选取+纵向的投影=结果集
7.2多表连接
7.2、1多表连接利弊
优势:
1)减少冗余数据即优化存储空间和减轻IO负担
2)实现了不同类型的表连接
3)字段的灵活添加,每个表中的字段都是相对独立的(不受主外键的限制),添加和删除都具有灵活性
不足之处:
1)多表连接语句会冗长繁杂且易读性较差
3)多个表的连接查询仅能在单个数据库上实现
7.2.2多表连接中各表之间的一一对应
1)一对一的关系
把表分成两半是最简单对应关系
2)一对多关系
这两个表以界定主外键约束的方式满足第三范式标准中的相应要求
3)多对多的关系
多对多的关系
非标准对应关系:两个表之间是多对多关系时,一般需设置中间表,中间表最少应有两个表主键,如此一来,就可以使得中间表和各个表各自都是一对多
7.2.3多表连接类型及语法
交叉连接,即笛卡尔积
非等值连接
等值连接(内连)
外连接(延伸内连、左连接、右连接、全连接)
自连接
自然联系(内连、隐含联系条件、联系字段的自动搭配)
复合连接(若干结果集的并接,交接,差接)
7.2.1交叉连接,笛卡尔积
连接条件不成立或者略去,2个表中的全部行出现连接且全部行合并后返回(n×m)
SQL99的写法:
SCOTT@prod>;select*fromempecrossjoindeptd
Oracle写法:
SCOTT@prod>;select*fromempe,deptd
7.2.2非等值联接:(联接条件不包括=”)
SQL99的写法:
SCOTT@prod>;selectempno,ename,sal,grade,losal,hisalfromempjoinsalgradeonsalbetweenlosalandhisal
Oracle写法:
SCOTT@prod>;selectempno,ename,sal,grade,losal,hisalfromemp,salgradewheresalbetweenlosalandhisal
7.2.3等值连接是内连接的一种典型形式
SQL99的写法:
SCOTT@prod>;selecte.ename,d.locfromempeinnerjoindeptdone.deptno=d.deptno
Oracle写法:
SCOTT@prod>;selecte.ename,d.locfromempe,deptdwheree.deptno=d.deptno
7.2.4等值连接using字句,通常使用
等值连接中的连接字段也可相同
比如one.deptno=d.deptno
亦可有所不同
比如one.empno=e.mgr
若衔接的字段一致,则可用using字句化繁为简
如one.deptno=d.detpno.;换成using(deptno)
举例如下:
SCOTT@prod>;selectdeptno,e.ename,d.locfromempeinnerjoindeptdusing(deptno);using里也可以多列,使用using关键字注意事项:
1.当select结果列表项含有using关键字指出的那一个关键字时,则不指出这个关键字是哪一个表格
3.on与using关键字互斥,即不可以同时存在
7.2.5外接(含左外接、右外接、全外接)
1)左外连接语法
SQL99语法:
SCOTT@prod>;select*fromempeleftouterjoindeptdone.deptno=d.deptno
Oracle语法:
SCOTT@prod>;select*fromempe,deptdwheree.deptno=d.deptno(+)
2)左连接需要了解2个要点
1.怎样判断左,右表的位置
SQL99书写方法:由from后的表格顺序决定,第1个表格是左侧表格
SCOTT@prod>;selecte.ename,d.locfromempeleftjoindeptdone.deptno=d.deptno
from后面的第一表emp表是左表,=”在左或右的位置不重要
Oracle写法:由where后=”位决定,=”位左侧是左表
SCOTT@prod>;selecte.ename,d.locfromempe,deptdwheree.deptno=d.deptno(+)
=”左emp表是左表格,from后表格的位置并不重要
2.左连,主要指左表
1左连由左表带动,每一行参与到与右表相匹配的行列中,在相匹配时连接成一行,若不能相匹配,则左表行列中不会缺少这个相连的行列,此时右表中内容填空即成
②左连和左表同时存在时,则左连的结果集只与左表行数有关系,而不是与左表或右表没有关系
3还可左表和右表均为同一表格,称为自左连”
没有一定之规是按业务需求确定的
两表间通常用主外键决定一对多,外键表为明细表(如emp与dept),用deptno决定父子关系(如emp)
您需要查询每一位雇员的工作位置,此时使用外键表即emp明细表作为左表是天经地义的
SCOTT@prod>;selecte.ename,d.locfromempeleftouterjoindeptdone.deptno=d.deptno
您需要查询一下各部门的雇员人数,需要按各部门的号码进行统计(40个部门没雇员也是统计的),此时用主键表的形式来进行表格的制作就比较合理了
SCOTT@prod>;selectd.deptno,count(e.ename)fromempe,deptdwhered.deptno=e.deptno(+)groupbyd.deptno
导出如下:
SCOTT@prod>;selectd.deptno,e.enamefromdeptdleftouterjoinempeone.deptno=d.deptno
上述3点,都是个人认识,右连法则也是如此
2)右外侧连接
SQL99语法:
SCOTT@prod>;select*fromemperightjoindeptdone.deptno=d.deptno
Oracle语法:
SCOTT@prod>;select*fromempe,deptdwheree.deptno(+)=d.deptno
3)全外连接
SQL99语法:
SCOTT@prod>;select*fromempefulljoindeptdone.deptno=d.deptno
Oracle语法:(没有、相当于union的连接)
SCOTT@pro>
select*fromempe,deptdwheree.deptno=d.deptno(+)
Unionist
select*fromempe,deptdwheree.deptno(+)=d.deptno
7.2.6自连接
SQL99语法:
SCOTT@prod>;selecte1.empno,e2.mgrfromempe1crossjoinempe2
Oracle语法:
SCOTT@prod>;selecte1.empno,e2.mgrfromempe1,empe2
注:一定要用别名来区分各种表格
7.2.6自然连接,属内连的等值连接
用关键字naturaljoin是自然连接
SCOTT@prod>;selecte.ename,d.locfromempenaturaljoindeptd
若存在多列组合匹配的情况下,进行自动多列匹配
7.3)对于每个复合查询,定义一个集合运算符?
Union,在2个select结果集上执行并集操作并在默认规则排序的情况下重复行只执行1次
UnionAll中,两个select结果集的并集操作包含了全部重复的行且没有排序
在Intersect中,执行2个select结果集的交集操作和只执行1次重复行,而执行默认规则
在Minus中,在默认规则排序的前提下,差操作两个select结果集而不取重复行
复合查询操作具有并、交、差三个算子
例:
SQL>;createtabledept1asselect*fromdeptwhererownum<;=1
SQL>;insertintodept1values(80,"MARKTING","BEIJING")
SQL>;select*fromdept
DEPTNODNAMECOL
-------------------------------------
10ACCOUNTINGNEWYORK.
20LOOKINGFORDALLAS
ThirtySALESCHICAGO
FortyOPERATIONSBOSTON
SQL>;select*fromdept1
DEPTNODNAMECOL
-------------------------------------
10ACCOUNTINGNEWYORK.
80MARKTINGBEIJING
1)Tradeunions
SQL>
Select*fromthedepartment.
unionization
select*fromdept1
DEPTNODNAMECOL
-------------------------------------
10ACCOUNTINGNEWYORK.
20LOOKINGFORDALLAS
ThirtySALESCHICAGO
FortyOPERATIONSBOSTON
80MARKTINGBEIJING
3)Unionsall
SQL>
Select*fromthedepartment.
unionismall
select*fromdept1
DEPTNODNAMECOL
-------------------------------------
10ACCOUNTINGNEWYORK.
20LOOKINGFORDALLAS
ThirtySALESCHICAGO
FortyOPERATIONSBOSTON
10ACCOUNTINGNEWYORK.
80MARKTINGBEIJING
特别说明:可见仅unionall结果集没有排序
3)Intersect.
SQL>
Select*fromthedepartment.
intersecting
select*fromdept1
DEPTNODNAMECOL
-------------------------------------
10ACCOUNTINGNEWYORK.
4)minus,注:minus是什么人
SQL>
Select*fromthedepartment.
minus
select*fromdept1
DEPTNODNAMECOL
-------------------------------------
20LOOKINGFORDALLAS
ThirtySALESCHICAGO
FortyOPERATIONSBOSTON
SQL>
select*fromdept1.
minus
select*fromdept
DEPTNODNAMECOL
-------------------------------------
80MARKTINGBEIJING
7.4复合查询的一些注意事项
1)列名不一定是一样的,但是应该是类型匹配和顺序对应的,大型的类型配对即可,如char到varchar2、date到timestamp均可,字段数应该相等,不要等待需补充
createtablea(id_aint,name_achar(10))
creatingtableb(id_bint,name_bchar(10),salnumber(10,2))
insertintoavalues(1,"sohu")
insertedintoavalues(2,"sina")
insertintobvalues(1,"sohu",1000)
insertbvalues(2,"yahoo",2000);e!
commit
SQL>;select*froma
ID_ANAME_A
--------------------
hu1
2ndsina
SQL>;select*fromb
ID_BNAME_BSAL
------------------------------
hu1,000
2yahoo2000
SQL>
selectid_a,name_afromalist.
unionization
chooseid_b,name_boutofb
2)4个集合运算符的优先级是按照依次发生的次序进行的,如果有特殊需要可采用()
3)对于复合查询,orderby采用别名排序:
1.缺省在复合查询之后,结果集按照全部字段(unionall除外)合并隐式进行排序
不想缺省排序的话还可以用orderby显式排序
SQL>;selectid_a,name_anamefroma.
unionization
selectid_b,name_bfromtheb.
orderbyname
SQL>
selectid_a,name_afromalist.
unionization
chooseid_b,name_bfromb.
Orderby2persons
2.显式orderby指参考首条select语句列元因此orderby后面的列只能作为第一个select中用到的列,别名和列号③在查询中,trie值越大,所需查找到的数据也就越多;而对某个对象而言,该对象所包含的属性或参数越多时,其查询处理效率越低若为补全null值要求orderby,需采用别名
SQL>
selectid_a,name_aname,to_number(null)froma.
unionization
selectid_b,name_bname,salfromb.
orderbysal
报错误:ORA-00904:SAL”:标识符失效
下列3种写法均对:
SQL>
selectid_a,name_aname,to_number(null)froma.
unionization
selectid_b,name_bname,salfromb.
orderedbythree
SQL>
selectid_b,name_bname,salfromb.
unionization
selectid_a,name_aname,to_number(null)froma.
orderbysal
SQL>
selectid_a,name_aname,to_number(null)aafroma.
unionization
chooseid_b,name_bname,salaaofb.
orderbyaa
3.排序为复合查询结果集,单个表不可以单独进行,orderby只可以进行1次并显示在末行中
SQL>
selectid_a,name_afromaorderbyid_a.
unionization
selectid_b,name_bfromborderbyid_b
报错误:ORA-00933:SQL命令没有被适当终止