数据库复杂查询.docx
《数据库复杂查询.docx》由会员分享，可在线阅读，更多相关《数据库复杂查询.docx（32页珍藏版）》请在冰点文库上搜索。
数据库复杂查询
复杂查询
复杂查询通常指从两张或两张以上的表进行的连接查询和相关联的子查询
一．Oracle中表的连接
1．笛卡尔积：
如果From子句指定了两张表，则这两张表就合并在一起进行查询。
两张表的合并的算法叫做笛卡尔积（与矩阵相乘类似）。
笛卡尔积的算法是，将表A的第一行与表B的所有行分别合并，得到与表B的行数相等的一系列新行；然后将表A的第二行与表B的所有行分别合并，得到与表B的行数相等的一系列新行，。
。
。
。
。
。
依次类推，直至将表A的最后一行与表B的所有行分别合并，得到与表B的行数相等的一系列新行。
故最终结果集的列数为
两个表的列数之和，其行数为两张表的行数的乘积。
2．实际应用中表的连接
实际应用中表的连接需要加上限制条件，对结果集进行某种限制。
其中等值连接使用
最多。
连接通常用于不同表之间的连接，但是也可以用于同一表之间的连接。
2.1.Oracle用于不同表之间的等值连接语法：
Selectdept.*,mens.*
Fromdept,mens
Wheredept.部门号=mens.所在部门
[And部门号=’01’]
2.2.Oracle用于同一张表之间的等值连接语法
除了连接两个或多个不同的表，连接操作也可用于连接单个的表（即连接自身），
此时必须使用别名来区分同一个表出现的情况。
例如：
selectt.姓名,t1.性别,t2.所在部门fromment1,ment2
wheret1.所在部门=t2.所在部门
又例如：
学校开运动会，要求各系派出男女同学组成混合选手，写一查询语句，找出
所有可能的组合
2.3.Oracle中的外连接
一个外连接返回两种记录:
A.满足连接条件的那些记录
B.来自其中一个表的纪录,这些记录因不满足条件而不能与另一个表的记录连接
Oracle中的外连接包含两种类型:
左外连接和右外连接.
查询所有女生所在的系,同时返回那些没有女生的系的信息.
Select姓名,性别,dept.系名Fromstudent,dept
Where性别=’女’andstudent.系号（+）=dept.系号
注意:
连接符号（+）放在连接条件等号的哪一边,其含义是不同的.例如,
上述语句改为
Select姓名,性别,dept.系名Fromstudent,dept
Where性别=’女’andstudent.系号=（+）dept.系号
则输出结果是女生所在系的信息,以及目前暂时没有系的女生信息.
例1：
获取与至少一个其它部门拥有相同所在地的所有部门的全部细节。
selectt1.*fromdepartmentt1,departmentt2
wheret1.location=t2.locationandt1.dept_no!
=t2.dept_no
例2：
显示居住在同一城市的所有员工
--selectt1.*fromemployeet1,employeet2
wheret1.domicil=t2.domicilandt1.emp_no!
=t2.emp_no
--selectdomicil,count（*）fromemployeet1groupbydomicilhavingcount（*）>1
selectt1.*fromemployeet1wheret1.domicilin
（selectt2.domicilfromemployeet2wheret1.emp_no!
=t2.emp_no）
selectt1.*fromemployeet1whereexists
（selectt2.domicilfromemployeet2wheret1.domicil=t2.domicil
andt1.emp_no!
=t2.emp_no）
查询物理课不及格的学生名单,输出其学号,姓名及成绩（三表连结查询）
selectstudent.xh,student.xm,t2.成绩
Fromstudent,课程,成绩t2
Where课程.km=’物理’andt2.成绩<60and课程.kh=成绩.kh
And成绩.xh=student.xh
2.内连接
是指满足连接条件的连接操作，即在内连接的结果集中都是满足条件的记录。
语法：
Selectcolumn_List
Fromtable_Name1innerjointable_Name2
Onjoin_Condition
例如：
Selectpi.province,pi.shortName,ci.city,ci.tele,ci.post
FromprovinceInfopi
InnerjoincityInfoci
Onpi.provinceID=ci.provinceID
该语句与下列语句等价，但上面的语法更正式。
Selectpi.province,pi.shortName,ci.city,ci.tele,ci.post
FromprovinceInfopi,cityInfoci
Wherepi.provinceID=ci.provinceID
3.左外连接
是指在结果集中，包含了左表不满足条件的记录，即结果集中包含左表的全部记录和右表与左表匹配的记录。
语法：
Selectcolumn_List
Fromtable_Name1leftouterjointable_Name2
Onjoin_Condition
例如：
Selectpi.province,pi.shortName,ci.city,ci.tele,ci.post
FromprovinceInfopi
LeftouterjoincityInfoci
Onpi.provinceID=ci.provinceID
4.右外连接
是指在结果集中，包含了右表不满足条件的记录，即结果集中包含右表的全部记录和左表与右表匹配的记录。
语法：
Selectcolumn_List
Fromtable_Name1rightouterjointable_Name2
Onjoin_Condition
例如：
Selectpi.province,pi.shortName,ci.city,ci.tele,ci.post
FromprovinceInfopi
rightouterjoincityInfoci
Onpi.provinceID=ci.provinceID
5.全外连接
是指在结果集中，包含了左表和右表中都不满足条件的记录，即结果集中包含左表的全部记录和右表的全部记录。
语法：
Selectcolumn_List
Fromtable_Name1fullouterjointable_Name2
Onjoin_Condition
例如：
Selectpi.province,pi.shortName,ci.city,ci.tele,ci.post
FromprovinceInfopi
fullouterjoincityInfoci
Onpi.provinceID=ci.provinceID
6.自连接
是指一个表与自身相连接。
为了能够进行自连接，必须为表取一个别名。
在实际应用中，自连接常常使用内连接的方式。
而且连接条件往往为不等连接。
语法：
Selectcolumn_List
Fromtable_Nameinnerjointable_Name
Onjoin_Condition
例如：
Selectpi.province,pi.shortName,ci.city,ci.tele,ci.post
FromprovinceInfopi
innerjoinprovinceInfoci
Onpi.provinceID>ci.provinceID
例：
有如下学生表和数据：
CreatetableStudents（
Student_IDnumber（5）,--学生ID
monitor_IDnumber（5）,--班长ID
namevarchar2（20）,--姓名
sexvarchar2
（2）,--性别
birthdaydate,--生日
specialtyvarchar2（40）--专业
）
insertintoStudentsvalues（5,1,'白皙','女',to_date（'1989-07-09','yyyy-mm-dd'）,'计算机'）;
insertintoStudentsvalues（1,null,'毛泽东','男',to_date（'1979-05-19','yyyy-mm-dd'）,'项目管理'）;
insertintoStudentsvalues（6,1,'刘洋','男',to_date（'1989-07-09','yyyy-mm-dd'）,'计算机'）;
insertintoStudentsvalues（7,1,'无疑','女',to_date（'1989-07-09','yyyy-mm-dd'）,'数学'）;
insertintoStudentsvalues（8,1,'克林顿','男',to_date（'1989-07-09','yyyy-mm-dd'）,'物理'）;
insertintoStudentsvalues（2,null,'温家宝','男',to_date（'1980-3-21','yyyy-mm-dd'）,'地质探测'）;
insertintoStudentsvalues（9,2,'大地之子','男',to_date（'1989-2-2','yyyy-mm-dd'）,'地质探测'）;
要求显示学生与班长的对应信息
分析：
由于班长也是学生，所以学生与班长的信息在同一表中，但不在同一行记录中，所以需要进行自连接查询
实现方案如下：
方案1：
班长信息也显示
selects1.student_id,s1.name学生名,s1.monitor_iD,s2.name班长名
fromstudentss1leftjoinstudentss2
ons1.monitor_id=s2.student_id
方案2：
不显示班长信息
selects1.student_id,s1.name学生名,s1.monitor_iD,s2.name班长名
fromstudentss1innerjoinstudentss2
ons1.monitor_id=s2.student_id
例1：
在表students中查询姓名为'刘洋'的班长信息
方案1：
--同一关系的两个元组
selects2.*fromstudentss1,studentss2
wheres1.name='刘洋'ands1.monitor_id=s2.student_Id
方案2：
--班长也是学生（子查询）
select*fromstudentss2
wheres2.student_ID=（
selectmonitor_IDfromstudentss1
wheres1.name='刘洋'
）
方案3
--自连接
selects1.name,s2.*fromstudentss1innerjoinstudentss2
ons1.monitor_id=s2.student_Id
wheres1.name='刘洋'
例2：
查询班长姓名是'毛泽东'的所有学生信息的树查询
selectlevel,student_ID,name,sex,birthday,ltrim（sys_connect_by_Path（name,'-->'）,'-->'）path
fromstudents
startwithname='毛泽东'--student_ID=1
connectbypriorstudent_ID=monitor_ID
7.交叉连接
是指没有连接条件的连接，其结果为笛卡尔乘积。
语法：
Selectcolumn_List
Fromtable_Name1crossjointable_Name2
二、Oracle中的子查询
当一个select语句嵌入到另外一个select,update或delete等sql语句中时，被全套的select语句就是子查询。
使用子查询应当遵守的原则：
子查询必须使用括号括起来，否则，无法判断子查询的开始和结束。
子查询中不能包含orderby子句.
子查询允许嵌套多层，但最多不超过255曾
在自查询中可以使用两种比较运算符：
单行运算符和多行运算符。
子查询分为五种类型，即单行子查询，多行子查询，多列子查询，关联子查询和嵌套子查询。
单行子查询：
子查询语句值返回单行单列的结果，即返回一个常量值。
多行子查询：
子查询语句值返回多行单列的结果，即返回一系列值。
多列子查询：
子查询语句返回多列的结果。
关联子查询：
子查询语句引用外查询中的一个列或多个列。
也就是说，外部查询和内部查询是相互关联的。
嵌套子查询：
可以在子查询中继续嵌套子查询，但其嵌套层数不能超过255个。
1.单行子查询
在单行子查询中可以使用的比较运算符如下：
=（等于）>（大于）>=（大于等于）<（小于）<=（小于等于）<>或!
=（不等于）
例1：
查询全年级成绩最差和做好的学生信息
select*fromstudentScore
wherescore=（selectmin（t1.score）fromstudentScoret1）
orscore=（selectmax（t2.score）fromstudentScoret2）
2.多行子查询
在多行单列子查询中可以使用的比较运算符如下：
运算符
描述
In
等于列表中的任何一个值
any
与子查询返回的每一个值进行比较
all
与子查询返回的所有值进行比较
表示小于最大值
=any
与in运算符等价
>any
表示大于最小值
表示小于最小值
>all
表示大于最大值
例1：
查询简称为’辽’的省的所有城市信息
select*fromcityInfowhereprovinceIDin（
selectxhfromprovinceInfowhereshortName='辽'）
例2：
查询每一个班中成绩最好的学生信息
select*fromstudentScore
wherescorein（
selectmax（t2.score）fromstudentScoret2groupbyclass
）
例2：
查询成绩小于各班中所有最好成绩的学生信息
select*fromstudentScore
wherescoreselectmax（t2.score）fromstudentScoret2groupbyclass
）
3.多列子查询
在一般的查询中，如果需要比较两个或以上列的数据，那么必须在where子句中使用逻辑运算符组合一个复合条件。
但是，通过使用多列自查询技术，可以把一个复合where条件写成单个where子句。
多列子查询可以分为两种类型：
成对比较的多列子查询和非成对比较的多列子查询
成对比较的多列子查询表示多个列同时相等，才可以称为满足匹配的条件。
如果在某些情况下，即使多列条件不成对相等，也可以称为满足匹配的条件，这时称为非成对比较。
例1：
查询成绩等于该班最好成绩的学生信息（成对比较多列子查询）
select*fromstudentScore
where（class,score）in（
selectclass,max（t2.score）fromstudentScoret2groupbyclass
）
例2：
查询班级为任意一个班，或者成绩等于该班最差成绩的学生信息（非成对比较多列子查询）
select*fromstudentScore
whereclassin（selectt2.classfromstudentScoret2groupbyclass）
orscorein（selectmin（t1.score）fromstudentScoret1groupbyclass）
例3：
多列子查询作为from子句
查询各班成绩最好的学生信息
select*from（
selectclass,score,
dense_rank（）over（partitionbyclassorderbyscoredesc）mc
fromstudentScoret2
）t
wheret.mc=1
4．Oracle中的相关联子查询
对于任何一个值的子查询中的内部查询取决于外部查询，称为关联子查询。
例1：
获取为项目p3工作的所有职员的姓：
Selectemp_lNameFromemployee
Where‘P3’IN
（Selectproject_noFromworks_on
whereworks_on.emp_no=employee.emp_no）
例2：
获取位于同一城市的所有部门的详细信息
Selectt1.*Fromdepartmentt1
Wheret1.LocationIN
（Selectt2.LocationFromdepartmentt2
Wheret1.dept_no<>t2.dept_no）
例3：
查找分数小于班级平均分数的学生
select*fromStudentscorem
wherescore<（
selectavg（score）fromStudentscorecwherec.class=m.class
）
例4：
用表provinceInfo的字段provinceID替换表cityInfo的provinceID字段，条件是：
表provinceInfo的字段province的值与表cityInfo的province字段的值相等。
updatecityInfot1
sett1.provinceID=（selectprovinceIDfromprovinceInfot2
wheret2.province=t1.province）
5.嵌套子查询
例：
检索与成绩为100的学生所在班级的所有学生信息
select*fromstudentscorewhereclass=（
selectclassfromstudentScorewherescore=100
）
思考：
1．设有表Province（省名,ID）.NewPost（Province,city,tele,post,ID）其中ID字段为空，用Province表的Id更新NewPost表的ID（两表的省名相同）
2．查询年龄在50岁以上，月收入低于本部门平均工资的人员，输出他们的姓名和工资
例3：
获取为项目’P3’工作的所有职员的信息
select*fromemployeewhereemp_Noin（selectemp_nofromworks_onwhereproject_no='P2'）
select*fromemployeewhere'P3'in（selectproject_nofromworks_on
whereworks_on.emp_no=employee.emp_no）（内部查询的值依赖于外部查询的值）
例4：
获取位于同一城市的所有部门的信息。
selectt1.*fromdepartmentt1wheret1.locationin
（selectt2.locationfromdepartmentt2wheret2.dept_no<>t1.dept_no）
三．Exists函数与子查询
Exists函数语法：
Exists（<子查询>）当子查询块中包含至少一行，则返回true
可以用Exists（）函数来表示”交”,用NotExists（）函数来表示”差”
思考：
查询从未被学生选修的课程，输出课程号和课程名
例1：
获取为项目’P1’工作的所有职员的信息
selectdistinctt1.*fromemployeet1whereexists（
select*fromworks_ont2wheret1.emp_no=t2.emp_noandt2.project_no='P1'）
例2：
获得工作地点不在成都的部门
selectdistinctt1.*fromdepartmentt1wherenotexists（
select*fromdepartmentt2wheret1.dept_no=t2.dept_noandt2.location='成都'）
例3：
获得没有参加项目的所有职员
selectt1.*fromemployeet1wherenotexists（
select*fromworks_ont2wheret1.emp_no=t2.emp_no）
例4：
获得编号最小的那位职员从事的工作
selectt2.*fromworks_ont2wheret2.emp_noin（
selectmin（t1.emp_no）fromemployeet1）
selectt2.*fromworks_ont2wherenotexists（
select*fromemployeet1wheret2.emp_no>t1.emp_no）
子查询只能显示外部表中的信息，而连接查询即可以显示外部表中的信息
也可以显示内部表中的信息。
用连接查询便于阅读理解，也可以帮助Sqlserver找到
一种更有效的策略来获取适当的数据，然而，使用子查询可以是某些问题的解决变得简单
练习：
1．查找出生于1970-12-31日之前，工资低于本部门平均工资的职员信息。
2．查找各部门年龄最小的职员信息
selectemp_no,emp_name,emp_birthday,emp_salary,dept_nofromemployeet
whereemp_birthday<'1970-12-31'and
升级会员 