1、数据库复杂查询复杂查询 复杂查询通常指从两张或两张以上的表进行的连接查询和相关联的子查询一Oracle中表的连接1笛卡尔积： 如果From 子句指定了两张表，则这两张表就合并在一起进行查询。两张表的合并的算法叫做笛卡尔积(与矩阵相乘类似)。笛卡尔积的算法是，将表A的第一行与表B的所有行分别合并，得到与表B 的行数相等的一系列新行；然后将表A的第二行与表B的所有行分别合并，得到与表B 的行数相等的一系列新行，。依次类推，直至将表A的最后一行与表B的所有行分别合并，得到与表B 的行数相等的一系列新行。故最终结果集的列数为两个表的列数之和，其行数为两张表的行数的乘积。2实际应用中表的连接 实际应用中
2、表的连接需要加上限制条件，对结果集进行某种限制。其中等值连接使用最多。连接通常用于不同表之间的连接，但是也可以用于同一表之间的连接。2.1.Oracle用于不同表之间的等值连接语法：Select dept.*,mens.* From dept , mens Where dept. 部门号=mens.所在部门 And 部门号=01 2.2. Oracle用于同一张表之间的等值连接语法 除了连接两个或多个不同的表，连接操作也可用于连接单个的表(即连接自身)，此时必须使用别名来区分同一个表出现的情况。 例如： select t.姓名,t1.性别,t2.所在部门 from men t1,men t2
3、where t1.所在部门=t2.所在部门 又例如：学校开运动会，要求各系派出男女同学组成混合选手，写一查询语句，找出所有可能的组合 2.3.Oracle中的外连接 一个外连接返回两种记录: A.满足连接条件的那些记录 B.来自其中一个表的纪录,这些记录因不满足条件而不能与另一个表的记录连接 Oracle中的外连接包含两种类型:左外连接和右外连接. 查询所有女生所在的系,同时返回那些没有女生的系的信息. Select 姓名,性别, dept.系名 From student,dept Where性别=女 and student.系号(+)=dept. 系号 注意: 连接符号 (+) 放在连接条件
4、等号的哪一边,其含义是不同的.例如, 上述语句改为 Select 姓名,性别, dept.系名 From student,dept Where性别=女 and student.系号=(+)dept. 系号 则输出结果是女生所在系的信息,以及目前暂时没有系的女生信息. 例1：获取与至少一个其它部门拥有相同所在地的所有部门的全部细节。 select t1.* from department t1,department t2 where t1.location=t2.location and t1.dept_no!=t2.dept_no 例2：显示居住在同一城市的所有员工-select t1.* f
5、rom employee t1,employee t2 where t1.domicil=t2.domicil and t1.emp_no!=t2.emp_no -select domicil,count(*) from employee t1 group by domicil having count(*)1select t1.* from employee t1 where t1.domicil in (select t2.domicil from employee t2 where t1.emp_no!=t2.emp_no ) select t1.* from employee t1 w
6、here exists (select t2.domicil from employee t2 where t1.domicil=t2.domicil and t1.emp_no!=t2.emp_no )查询物理课不及格的学生名单,输出其学号,姓名及成绩(三表连结查询) select student.xh,student.xm, t2.成绩 From student,课程,成绩 t2 Where课程.km= 物理 and t2. 成绩ci.provinceID例：有如下学生表和数据：Create table Students( Student_ID number(5), -学生ID monit
7、or_ID number(5), -班长ID name varchar2(20), -姓名 sex varchar2(2), -性别 birthday date, -生日 specialty varchar2(40) -专业)insert into Students values(5,1,白皙,女,to_date(1989-07-09,yyyy-mm-dd),计算机);insert into Students values(1,null,毛泽东,男,to_date(1979-05-19,yyyy-mm-dd),项目管理);insert into Students values(6,1,刘洋,男
8、,to_date(1989-07-09,yyyy-mm-dd),计算机);insert into Students values(7,1,无疑,女,to_date(1989-07-09,yyyy-mm-dd),数学);insert into Students values(8,1,克林顿,男,to_date(1989-07-09,yyyy-mm-dd),物理);insert into Students values(2,null,温家宝,男,to_date(1980-3-21,yyyy-mm-dd),地质探测);insert into Students values(9,2,大地之子,男,to
9、_date(1989-2-2,yyyy-mm-dd),地质探测);要求显示学生与班长的对应信息分析：由于班长也是学生，所以学生与班长的信息在同一表中，但不在同一行记录中，所以需要进行自连接查询实现方案如下：方案1：班长信息也显示select s1.student_id,s1.name 学生名,s1.monitor_iD,s2.name 班长名 from students s1 left join students s2 on s1.monitor_id=s2.student_id方案2：不显示班长信息select s1.student_id,s1.name 学生名,s1.monitor_iD,
10、s2.name 班长名 from students s1 inner join students s2 on s1.monitor_id=s2.student_id例1：在表students中查询姓名为刘洋的班长信息方案1：-同一关系的两个元组select s2.* from students s1, students s2 where s1.name=刘洋 and s1.monitor_id=s2.student_Id方案2：-班长也是学生(子查询)select * from students s2where s2.student_ID=( select monitor_ID from st
11、udents s1 where s1.name=刘洋 )方案3-自连接 select s1.name,s2.* from students s1 inner join students s2 on s1.monitor_id=s2.student_Id where s1.name=刘洋例2：查询班长姓名是毛泽东的所有学生信息的树查询select level,student_ID,name,sex,birthday,ltrim(sys_connect_by_Path(name,-),-) pathfrom students start with name=毛泽东 -student_ID=1con
12、nect by prior student_ID=monitor_ID 7.交叉连接 是指没有连接条件的连接，其结果为笛卡尔乘积。语法：Select column_List From table_Name1 cross join table_Name2二、Oracle中的子查询 当一个select语句嵌入到另外一个select,update或delete等sql语句中时，被全套的select语句就是子查询。使用子查询应当遵守的原则：子查询必须使用括号括起来，否则，无法判断子查询的开始和结束。子查询中不能包含order by子句.子查询允许嵌套多层，但最多不超过255曾在自查询中可以使用两种比较
13、运算符：单行运算符和多行运算符。 子查询分为五种类型，即单行子查询，多行子查询，多列子查询，关联子查询和嵌套子查询。 单行子查询：子查询语句值返回单行单列的结果，即返回一个常量值。 多行子查询：子查询语句值返回多行单列的结果，即返回一系列值。 多列子查询：子查询语句返回多列的结果。 关联子查询：子查询语句引用外查询中的一个列或多个列。也就是说，外部查询和内部查询是相互关联的。嵌套子查询：可以在子查询中继续嵌套子查询，但其嵌套层数不能超过255个。1. 单行子查询在单行子查询中可以使用的比较运算符如下：=(等于) (大于) =(大于等于) (小于) =(小于等于) 或!= (不等于)例1：查询全
14、年级成绩最差和做好的学生信息select * from studentScorewhere score=(select min(t1.score) from studentScore t1) or score=(select max(t2.score) from studentScore t2)2. 多行子查询在多行单列子查询中可以使用的比较运算符如下： 运算符描述In等于列表中的任何一个值any与子查询返回的每一个值进行比较all与子查询返回的所有值进行比较any表示大于最小值all表示大于最大值例1：查询简称为辽的省的所有城市信息select * from cityInfo where p
15、rovinceID in(select xh from provinceInfo where shortName=辽)例2：查询每一个班中成绩最好的学生信息select * from studentScorewhere score in( select max(t2.score) from studentScore t2 group by class )例2：查询成绩小于各班中所有最好成绩的学生信息select * from studentScorewhere score all( select max(t2.score) from studentScore t2 group by class
16、 )3. 多列子查询 在一般的查询中，如果需要比较两个或以上列的数据，那么必须在where子句中使用逻辑运算符组合一个复合条件。但是，通过使用多列自查询技术，可以把一个复合where条件写成单个where子句。 多列子查询可以分为两种类型：成对比较的多列子查询和非成对比较的多列子查询成对比较的多列子查询表示多个列同时相等，才可以称为满足匹配的条件。如果在某些情况下，即使多列条件不成对相等，也可以称为满足匹配的条件，这时称为非成对比较。例1：查询成绩等于该班最好成绩的学生信息(成对比较多列子查询)select * from studentScorewhere (class,score) in (
17、 select class,max(t2.score) from studentScore t2 group by class )例2：查询班级为任意一个班，或者成绩等于该班最差成绩的学生信息(非成对比较多列子查询)select * from studentScorewhere class in (select t2.class from studentScore t2 group by class) or score in ( select min(t1.score) from studentScore t1 group by class)例3：多列子查询作为from子句 查询各班成绩最好的
18、学生信息select * from ( select class,score,dense_rank()over(partition by class order by score desc) mc from studentScore t2)twhere t.mc=14Oracle中的相关联子查询对于任何一个值的子查询中的内部查询取决于外部查询，称为关联子查询。 例1：获取为项目p3工作的所有职员的姓： Select emp_lName From employee Where P3 IN(Select project_no From works_on where works_on.emp_no=
19、employee.emp_no ) 例2：获取位于同一城市的所有部门的详细信息 Select t1.* From department t1 Where t1.Location IN (Select t2.Location From department t2 Where t1.dept_no t2.dept_no) 例3：查找分数小于班级平均分数的学生select * from Studentscore m where score( select avg(score) from Studentscore c where c.class=m.class )例4：用表provinceInfo的字
20、段provinceID替换表cityInfo的provinceID字段，条件是： 表provinceInfo的字段province的值与表cityInfo的province字段的值相等。update cityInfo t1 set t1.provinceID=(select provinceID from provinceInfo t2 where t2.province=t1.province )5. 嵌套子查询例：检索与成绩为100 的学生所在班级的所有学生信息select * from studentscore where class=( select class from studen
21、tScore where score=100 )思考：1设有表Province(省名,ID). NewPost(Province,city,tele,post,ID) 其中ID字段为空，用Province表的Id更新NewPost表的ID(两表的省名相同)2查询年龄在50岁以上，月收入低于本部门平均工资的人员，输出他们的姓名和工资 例3：获取为项目P3工作的所有职员的信息select * from employee where emp_No in(select emp_no from works_on where project_no=P2)select * from employee whe
22、re P3 in(select project_no from works_on where works_on.emp_no=employee.emp_no)(内部查询的值依赖于外部查询的值)例4：获取位于同一城市的所有部门的信息。select t1.* from department t1 where t1.location in (select t2.location from department t2 where t2.dept_not1.dept_no)三Exists函数与子查询 Exists 函数语法：Exists() 当子查询块中包含至少一行，则返回true可以用Exists()
23、函数来表示”交”,用Not Exists()函数来表示”差”思考：查询从未被学生选修的课程，输出课程号和课程名例1：获取为项目P1工作的所有职员的信息 select distinct t1.* from employee t1 where exists( select * from works_on t2 where t1.emp_no=t2.emp_no and t2.project_no=P1)例2：获得工作地点不在成都的部门select distinct t1.* from department t1 where not exists( select * from department
24、t2 where t1.dept_no=t2.dept_no and t2.location=成都)例3：获得没有参加项目的所有职员select t1.* from employee t1 where not exists( select * from works_on t2 where t1.emp_no=t2.emp_no )例4：获得编号最小的那位职员从事的工作select t2.* from works_on t2 where t2.emp_no in ( select min(t1.emp_no) from employee t1 )select t2.* from works_on
25、 t2 where not exists ( select * from employee t1 where t2.emp_not1.emp_no)子查询只能显示外部表中的信息，而连接查询即可以显示外部表中的信息也可以显示内部表中的信息。用连接查询便于阅读理解，也可以帮助Sqlserver找到一种更有效的策略来获取适当的数据，然而，使用子查询可以是某些问题的解决变得简单练习：1查找出生于1970-12-31日之前，工资低于本部门平均工资的职员信息。2查找各部门年龄最小的职员信息select emp_no,emp_name,emp_birthday,emp_salary,dept_no from employee t where emp_birthday1970-12-31 and
