搜索
NeusoftInstituteofInformationDate:25.Feb2005ITEducation&Training第五章多表连接和子查询NeusoftInstituteofInformationDate:25.Feb2005ITEducation&Training本章要点•多表连接的定义•等值连接•不等值连接•外连接•自连接•子查询的语法•子查询的使用NeusoftInstituteofInformationDate:25.Feb2005ITEducation&Training从多个表中取得数据EMPLOYEESDEPARTMENTS……NeusoftInstituteofInformationDate:25.Feb2005ITEducation&Training使用Oracle的语法连接多个表•使用表连接从多个表中查询数据SELECTtable1.column,table2.columnFROMtable1,table2WHEREtable1.column1=table2.column2;–在WHERE子句中写入连接条件–当多个表中有重名列时，必须在列的名字前加上表名作为前缀NeusoftInstituteofInformationDate:25.Feb2005ITEducation&Training•Oracle8i以前的表连接:•等值连接--Equijoin•非等值连接--Non-equijoin•外连接--Outerjoin•自连接--Selfjoin连接的类型NeusoftInstituteofInformationDate:25.Feb2005ITEducation&Training连接的类型•Oracle9i的表连接中，8i的语法同样支持。•SQL:1999适应性连接:•交叉连接--Crossjoins•自然连接--Naturaljoins•使用Using子句的连接•完全外连接或者左右外连接•外连接中可以使用任意的连接条件NeusoftInstituteofInformationDate:25.Feb2005ITEducation&Training什么是等值连接?EMPLOYEESDEPARTMENTSForeignkey外键Primarykey主键……NeusoftInstituteofInformationDate:25.Feb2005ITEducation&Training使用AND操作符增加查询条件EMPLOYEESDEPARTMENTS……NeusoftInstituteofInformationDate:25.Feb2005ITEducation&Training使用表的别名•使用表的别名简化了查询•提高了查询的性能SELECTe.employee_id,e.last_name,e.department_id,d.department_id,d.location_idFROMemployeese,departmentsdWHEREe.department_id=d.department_id;NeusoftInstituteofInformationDate:25.Feb2005ITEducation&Training对多表作等值连接查询EMPLOYEESLOCATIONSDEPARTMENTS•为了连接n个表，至少需要n-1个连接条件。例如，为了连接三个表，至少需要两个连接条件…NeusoftInstituteofInformationDate:25.Feb2005ITEducation&Training对多表作等值连接查询SELECTe.employee_id,e.last_name,e.department_id,d.department_id,d.location_id,l.cityFROMemployeese,departmentsd,locationslWHEREe.department_id=d.department_idANDd.location_id=l.location_id;NeusoftInstituteofInformationDate:25.Feb2005ITEducation&Training非等值连接EMPLOYEESJOB_GRADES在EMPLOYEES表中所有薪水位于JOB_GRADES表最低薪水和最高薪水之间雇员的薪水情况.…NeusoftInstituteofInformationDate:25.Feb2005ITEducation&Training非等值连接•使用非等值连接查询可以查询两个表中具有非等值的相关关系的数据。操作符可以是比较运算符，也可以是between···and或者是in、like。•如：通过员工信息表的薪水与工资级别表的最低最高值进行比较，找到员工的薪水级别。SELECTe.last_name,e.salary,s.gradeFROMemployeese,salgradesWHEREe.salaryBETWEENs.losalANDs.hisal;NeusoftInstituteofInformationDate:25.Feb2005ITEducation&Training外连接EMPLOYEESDEPARTMENTS没有雇员属于190部门…NeusoftInstituteofInformationDate:25.Feb2005ITEducation&Training外连接•使用外连接可以看到不满足连接条件的记录•外连接运算符是(+)•有左外连接和右外连接SELECTtable.column,table.columnFROMtable1,table2WHEREtable1.column(+)=table2.column;SELECTtable.column,table.columnFROMtable1,table2WHEREtable1.column=table2.column(+);NeusoftInstituteofInformationDate:25.Feb2005ITEducation&Training自连接(self-join)EMPLOYEES(WORKER)EMPLOYEES(MANAGER)WORKER表中的MANAGER_ID等于MANAGER表中的EMPLOYEE_ID……NeusoftInstituteofInformationDate:25.Feb2005ITEducation&Training自连接•如：查询员工和其管理者之间的关系，并按“员工姓名workfor管理者”格式显示。SELECTworker.last_name||'worksfor'||manager.last_nameFROMemployeesworker,employeesmanagerWHEREworker.manager_id=manager.employee_id;NeusoftInstituteofInformationDate:25.Feb2005ITEducation&Training支持SQL1999的新连接标准•包括以下新的TABLEJOIN的句法结构•CROSSJOIN——它在两个表格中创建了一个笛卡尔积，就象是在Oracle8i中没写WHERE时一样•NATURALJOIN——这是一个很有用的Oracle9i的句法，它通过从WHERE子句中自动连接标准来改善SQL的稳定性•USING子句——它可以通过名字来具体指定连接NeusoftInstituteofInformationDate:25.Feb2005ITEducation&Training支持SQL1999的新连接标准•ON子句——这个句法允许在两个表中为连接具体指定列名•LEFTOUTERJOIN——它返回表格中左边的行和右边的数值，如果没有搭配的行的话，则返回空•RIGHTOUTERJOIN——它返回表格中右边的行和左边的数值，如果没有搭配的行的话，则返回空•FULLOUTERJOIN——它返回的是两个表格中所有的行，用空填满每一个空格。这在Oracle8i中则没有相应的此种句法NeusoftInstituteofInformationDate:25.Feb2005ITEducation&TrainingSQL1999新连接标准语法SELECTtable1.column,table2.columnFROMtable1[CROSSJOINtable2]|[NATURALJOINtable2]|[JOINtable2USING(column_name)]|[JOINtable2ON(table1.column_name=table2.column_name)]|[LEFT|RIGHT|FULLOUTERJOINtable2ON(table1.column_name=table2.column_name)];NeusoftInstituteofInformationDate:25.Feb2005ITEducation&Training交叉连接CROSSJOIN•CROSSJOIN产生了一个笛卡尔积，就象是在连接两个表格时忘记加入一个WHERE子句一样Selectlast_name,department_idfromemployees,departments;•在Oracle9i中，可以使用CROSSJOIN来达到相同的结果Selectlast_name,department_idfromemployeescrossjoindeptartments;NeusoftInstituteofInformationDate:25.Feb2005ITEducation&Training自然连接NATURALJOIN•NATURALJOIN子句基于两个表中列名完全相同的多个列产生连接•从两个表中选出连接列的值相等的所有行•如果两个列的名称相同,但是具有不同的数据类型，则查询会返回一个错误Selectlast_name,department_namefromdepartmentsnaturaljoinemployees;NeusoftInstituteofInformationDate:25.Feb2005ITEducation&TrainingUSING子句•如果一些列有相同的名称但是类型不匹配,自然连接将出错，可以在自然连接的NATURALJOIN子句上使用USING子句来设置用于等值连接的•不要在参照列上使用表名或者别名作为前缀Selectdepartment_name,cityfromdepartmentsJOINlocationsUSING(location_id);NeusoftInstituteofInformationDate:25.Feb2005ITEducation&TrainingON子句•自然连接的条件是基于表中所有同名列的等值连接•为了设置任意的连接条件或者指定连接的列，需要使用ON子句•连接条件与其它的查询条件分开书写•使用ON子句使查询语句更容易理解Selectdepartment_name,cityfromdepartmentdJOINlocationlON(d.location_id=l.id);NeusoftInstituteofInformationDate:25.Feb2005ITEducation&Training多表连接•多表连接就是两个以上的表被连接所用的Selectemp_id,city_name,dept_namefromlocationslJOINdepartmentsdON(d.location_id=l.location_id)JOINemployeeseON(d.department_id=e.department_id);NeusoftInstituteofInformationDate:25.Feb2005ITEducation&Training左外连接LEFTOUTERJOIN•在LEFTOUTE