搜索
第六章子查询Copyright©OracleCorporation,2001.Allrightsreserved.子查询进度表：时间主题25分钟讲演30分钟练习55分钟总共中国科学院西安网络中心©2005OracleSQL入门6-1第六章子查询6-2Copyright©OracleCorporation,2001.Allrightsreserved.目标完成本课后,您应当能够执行下列操作：•描述字查询能够解决的问题类型•定义子查询•列出子查询的类型•写单行和多行子查询课程目标在本课中，你将学习关于SELECT语句的更多的高级特性。你可以在另一个SQL语句的WHERE子句中写子查询以获得基于未知条件值。本课包括单行子查询和多行子查询。中国科学院西安网络中心©2005OracleSQL入门6-2第六章子查询6-3Copyright©OracleCorporation,2001.Allrightsreserved.用子查询解决问题谁的薪水比Abel’s多?哪些雇员的工资高于Abel的工资?主查询：??Abel的薪水是多少???子查询：用子查询解决问题假想你想要写一个查询来找出挣钱比Abel的薪水还多的人。为了解决这个问题，你需要两个查询：一个找出Abel的收入，第二个查询找出收入高于Abel的人。你可以用组合两个查询的方法解决这个问题，放置一个查询到另一个查询中。内查询或子查询返回一个值给外查询或主查询。使用一个子查询相当于执行两个连续查询并且用第一个查询的结果作为第二个查询的搜索值。中国科学院西安网络中心©2005OracleSQL入门6-3第六章子查询6-4Copyright©OracleCorporation,2001.Allrightsreserved.子查询语法•子查询(内查询)在主查询之前执行一次•子查询的结果被用于主查询(外查询)SELECTselect_listFROMtableWHEREexproperator(SELECTselect_listFROMtable);子查询子查询是一个SELECT语句，它是嵌在另一个SELECT语句中的子句。使用子查询你可以用简单的语句构建功能强大的语句。当你需要从表中用依赖于表本身的数据选择行时它们是非常有用的。你可以将子查询放在许多的SQL子句中，包括：WHERE子句HAVING子句FROM子句在语法中：operator包括比较条件，例如、=或IN注：比较条件分为两个种类：单行运算符(,=,=,,,=)和多行运算符(IN,ANY,ALL)。子查询通常涉及一个嵌套的SELECT、子-SELECT或内SELECT语句。字查询通常执行一次。并且它的输出被用于完成主或外查询的查询条件。教师注释另外，子查询可以被放在CREATEVIEW语句中、CREATETABLE语句、UPDATE语句、INSERT语句的INTO子句和UPDATE语句的SET子句中。中国科学院西安网络中心©2005OracleSQL入门6-4第六章子查询6-5Copyright©OracleCorporation,2001.Allrightsreserved.SELECTlast_nameFROMemployeesWHEREsalary(SELECTsalaryFROMemployeesWHERElast_name='Abel');使用子查询11000使用子查询在幻灯片中，内查询确定雇员Abel的薪水，外查询取得内查询的结果，并且使用该结果显示所有薪水高于该数量的雇员。教师注释首先执行子查询(内查询)显示子查询返回的值，然后用内查询返回的结果执行外查询，最后，执行整个查询(包括子查询)，显示相同的结果。中国科学院西安网络中心©2005OracleSQL入门6-5第六章子查询6-6Copyright©OracleCorporation,2001.Allrightsreserved.使用子查询的原则•子查询放在圆括号中•将子查询放在比较条件的右边•在子查询中的ORDERBY子句不需要，除非你正在执行Top-N分析•在单行子查询中用单行运算符，在多行子查询中用多行运算符使用子查询的原则一个子查询必须放在圆括号中。将子查询放在比较条件的右边以增加可读性。Oracle8i以前的版本中，子查询不包含ORDERBY子句。对一个SELECT语句只能用一个ORDERBY子句，并且如果指定了它就必须放在主SELECT语句的最后。从Oracle8i开始，ORDERBY子句可以使用，并且在进行Top-N分析时是必须的。在子查询中可以使用两种比较条件：单行运算符和多行运算符。教师注释一个子查询可以在有相互关系的子查询中被多次执行A。学生可能会问可以写多少子查询，Oracle服务器没有强制限制子查询的数目；限制只与查询所需的缓冲区大小有关。中国科学院西安网络中心©2005OracleSQL入门6-6第六章子查询6-7Copyright©OracleCorporation,2001.Allrightsreserved.子查询的类型主查询子查询返回ST_CLERK•多行子查询ST_CLERKSA_MAN主查询子查询返回•单行子查询子查询的类型单行子查询：从内SELECT语句只返回一行的查询多行子查询：从内SELECT语句返回多行的查询注：还有多列子查询：从内SELECT语句返回多列的查询。教师注释多列子查询也是可用的。中国科学院西安网络中心©2005OracleSQL入门6-7第六章子查询6-8Copyright©OracleCorporation,2001.Allrightsreserved.单行子查询•仅返回一行•使用单行比较符运算符===含义等于大于大于或等于小于小于或等于不等于单行子查询单行子查询是从内查询返回一行的查询。在该子查询类型中用一个单行操作符。幻灯片中列出了单行操作符。例显示那些jobID与雇员141相同的雇员。SELECTlast_name,job_idFROMemployeesWHEREjob_id=(SELECTjob_idFROMemployeesWHEREemployee_id=141);中国科学院西安网络中心©2005OracleSQL入门6-8第六章子查询6-9Copyright©OracleCorporation,2001.Allrightsreserved.SELECTlast_name,job_id,salaryFROMemployeesWHEREjob_id=(SELECTjob_idFROMemployeesWHEREemployee_id=141)ANDsalary(SELECTsalaryFROMemployeesWHEREemployee_id=143);执行单行子查询ST_CLERK2600执行单行子查询SELECT语句可以被考虑为一个查询块。幻灯片中的例子显示jobID与雇员141相同，并且薪水高于雇员143的那些雇员。该例子可以由三个查询块组成：外查询和两个内查询。内查询块首先被执行，产生查询结果分别为ST_CLERK和2600。然后处理外查询块，并且使用内查询的返回值来完成它的查询条件。两个内查询返回单个值(分别是ST_CLERK和2600)，所以这种SQL语句被称为单行子查询。注：外和内查询可以从不同的表中取得数据。中国科学院西安网络中心©2005OracleSQL入门6-9第六章子查询6-10Copyright©OracleCorporation,2001.Allrightsreserved.SELECTlast_name,job_id,salaryFROMemployeesWHEREsalary=(SELECTMIN(salary)FROMemployees);在子查询中使用组函数2500在子查询中使用组函数你可以从主查询中显示数据，该主查询使用一个带组函数的单行子查询。子查询放在圆括号中并且放在比较条件的后面。幻灯片中的例子显示所有其薪水等于最低薪水的雇员的lastname、jobID和salary。MIN组函数返回单个的值(2500)给外函数。中国科学院西安网络中心©2005OracleSQL入门6-10第六章子查询6-11Copyright©OracleCorporation,2001.Allrightsreserved.带子查询的HAVING子句•Oracle服务器首先执行子查询•Oracle服务器返回结果到主查询的HAVING子句中SELECTdepartment_id,MIN(salary)FROMemployeesGROUPBYdepartment_idHAVINGMIN(salary)(SELECTMIN(salary)FROMemployeesWHEREdepartment_id=50);2500带子查询的HAVING子句你不仅可以在WHERE子句中使用子查询，也可以在HAVING子句中使用子查询。Oracle服务器执行子查询，并将结果返回到主查询的HAVING子句中。在幻灯片中的SQL语句显示所有其最低薪水小于部门50的最低薪水的部门号和最低薪水。例找出平均薪水为最低平均薪水的工作岗位。SELECTjob_id,AVG(salary)FROMemployeesGROUPBYjob_idHAVINGAVG(salary)=(SELECTMIN(AVG(salary))FROMemployeesGROUPBYjob_id);中国科学院西安网络中心©2005OracleSQL入门6-11第六章子查询6-12Copyright©OracleCorporation,2001.Allrightsreserved.SELECTemployee_id,last_nameFROMemployeesWHEREsalary=(SELECTMIN(salary)FROMemployeesGROUPBYdepartment_id);什么是子查询错误?ERRORatline4:ORA-01427:single-rowsubqueryreturnsmorethanonerowERRORatline4:ORA-01427:single-rowsubqueryreturnsmorethanonerow单行运算符用于多行子查询子查询错误使用子查询的一个常见的错误是单行子查询返回返回了多行。在幻灯片的SQL语句中，子查询包含一个GROUPBY子句，这就暗示该子查询将返回多行，每个对应它所找到的一组，在这种情况下，子查询的结果将是4400、6000、2500、4200、7000、17000和8300。外查询得到子查询的结果(4400、6000、2500、4200、7000、17000、8300)并且在它的WHERE子句中使用这些结果。WHERE子句包含一个等号(=)运算符，这是一个单行比较运算符，只能使用一个值。=操作符不能接受来自子查询的多个值，并且因此产生错误。为了纠正该错误，改变=操作为IN。中国科学院西安网络中心©2005OracleSQL入门6-12第六章子查询6-13Copyright©OracleCorporation,2001.Allrightsreserved.这个子查询是否有返回行?norowsselectednorowsselectedSELECTlast_name,job_idFROMemployeesWHEREjob_id=(SELECTjob_idFROMemployeesWHERElast_name='Haas');子查询无返回值子查询问题子查询的另一个常见问题是内查询没有返回行。在幻灯片的SQL语句中，子查询包含一个WHERE子句，推测起来，其目的是找名字为Haas的雇员，该语句是正确的，但在执行时选择无行返回。没有名叫Haas的雇员，所以子查询无返回行，外查询得到子查询的结果(null)并且在WHERE子句中使用该结果，外查询找不到一个jobID等于null的雇员，所以也没有行返回。如果一个job存在null值，也没有返回行