子查詢和集合查詢嵌入在其他sql語(yǔ)句中的select語(yǔ)句稱為子查詢。為在多個(gè)select語(yǔ)句的結(jié)果集上進(jìn)行集合操作，可以使用union,unionall,intersect和minus操作符子查詢根據(jù)返回結(jié)果的不同，子查詢分為單行子查詢，多行子查詢，多列子查詢單行子查詢：返回一行一列數(shù)據(jù)給外部的(主)SQL語(yǔ)句多行子查詢：返回多行一列數(shù)據(jù)給外部的(主)SQL語(yǔ)句多列子查詢：返回多列(單行或多行)數(shù)據(jù)給外部的(主)SQL語(yǔ)句相關(guān)子查詢：子查詢引用了外部(主)查詢中包含的表列，并且子查詢不能再外部(主)查詢之前求值，需要根據(jù)外部(主)查詢才能獲得值，這樣的子查詢稱為相關(guān)子查詢另外，SQL語(yǔ)言允許子查詢嵌套，其嵌套的深度因版本而異，oraclelOg的嵌套深度允許高達(dá)255級(jí)。嵌套子查詢的查詢效率較低，使用時(shí)應(yīng)當(dāng)考慮。在數(shù)據(jù)定義語(yǔ)言(DDL)和數(shù)據(jù)操縱語(yǔ)言(DML)語(yǔ)句中可以使用子查詢一、單行子查詢只返回一行一列數(shù)據(jù)的子查詢稱為單行子查詢?cè)赪HERE子句中使用子查詢例1:在teacher表中，查詢工資低于平均工資的所有教師SELECT*FROMTeachersWHEREwage<(SELECTAVG(wage)FROMTeachers---所有教師ide平均工資)；例2:在students表中，查詢與某同學(xué)('王天儀’同專業(yè)的所有學(xué)生SELECT*FROMStudentsWHEREspecialty=(SELECTspecialtyFROMStudentsWHEREname='王天儀');--王天儀同學(xué)的專業(yè)例3:在students表中，查詢年齡小于某同學(xué)('王天儀')的所有學(xué)生SELECT*FROMStudentsWHEREdob>(SELECTdobFROMStudentsWHEREname='王天儀');--王天儀同學(xué)的生日在HAVING子句中使用子查詢?cè)趆aving子句中使用子查詢時(shí)，該子查詢的結(jié)果作為主查詢的分組條件例4:在teacher表中，查詢部門平均工資高于最低部門部門平均工資的部門和平均工資SELECTdepartment_id,AVG(wage)AS平均工資FROMTeachersGROUPBYdepartment_idHAVINGAVG(wage)>(SELECTMIN(AVG(wage))FROMTeachersGROUPBYdepartment_id);所有部門的最低平均工資3.在FROM子句中使用子查詢，該子查詢的結(jié)果作為主查詢的視圖例5:在students表的男同學(xué)中，查詢計(jì)算機(jī)專業(yè)的所有學(xué)生SELECT*FROM(SELECT*FROMStudentsWHEREsex='男')WHEREspecialty='計(jì)算機(jī)'；二、多行子查詢返回多行一列數(shù)據(jù)的子查詢稱為多行子查詢。擋在where子句中使用多行子查詢時(shí)，須使用多行比較符IN,ANY,ALL.其作用如下：In:匹配于子查詢結(jié)果的任意一個(gè)值，結(jié)果為true,否則為falseANY:只要符合子查詢結(jié)果的任意一個(gè)值，結(jié)果為true,否則為falseALL:必須匹配于子查詢結(jié)果的所有值，結(jié)果為true,否則為false1.在多行子查詢使用IN/NOTIN操作符例6:在students表中檢索王姓同學(xué)的學(xué)號(hào)與姓名SELECTstudent_id,nameFROMStudentsWHEREstudent_idIN(SELECTstudent_idFROMStudentsWHEREnameLIKE'王%');--王姓同學(xué)的學(xué)號(hào)例7:查詢未被學(xué)生選學(xué)的課程SELECTcourse_id,course_nameFROMCoursesWHEREcourse_idNOTIN(SELECTcourse_idFROMStudents_grade);--學(xué)生已經(jīng)選學(xué)的課程id2.在多行子查詢使用ANY操作符例8查詢工資低于任意一個(gè)部門平均工資的教師信息SELECT*FROMTeachersWHEREwage<ANY(SELECTAVG(wage)FROMTeachersGROUPBYdepartment_id);所有部門的平均工資3.在多行子查詢使用ALL操作符例9:查詢工資高于各部門平均工資的教師信息SELECT*FROMTeachersWHEREwage>ALL(SELECTAVG(wage)FROMTeachersGROUPBYdepartment_id);所有部門的平均工資三、多列子查詢返回多列（單行或多行）數(shù)據(jù)的子查詢語(yǔ)句。返回單行多列的子查詢可以參照單行子查詢的例子編寫插敘你語(yǔ)句；單貨多行多列的子查詢，可以參照多行子查詢的例子編寫查詢語(yǔ)句例10:利用子查詢，在students表中檢索與王天一專業(yè)相同，生日相同的同學(xué)SELECT*FROMStudentsWHERE(specialty,dob)=(SELECTspecialty,dobFROMStudentsWHEREname='王天儀');--檢索王天一事務(wù)專業(yè)和生日例11:在teachers表中檢索在各自部門工資最低的教師SELECT*FROMTeachersWHERE(department_id,wage)IN(SELECTdepartment_id,MIN(wage)FROMTeachersGROUPBYdepartment_id);各部門id和最低工資四、相關(guān)子查詢有時(shí)，子查詢引用了外部(主)查詢中包含的一列或多列，子查詢不能在外部前求(主)查詢之值，需要依靠外部(主)查詢才能獲得值，這樣的子查詢稱為相關(guān)子查詢例12：在teachers表中檢索工資高于所在部門平均工資的教師SELECT*FROMteacherst1WHEREwage>(SELECTAVG(wage)FROMteacherst2WHEREt2.department_id=t1.department_id);--同一部門?在相關(guān)子查詢中使用EXISTS例13:在course表中已經(jīng)選學(xué)的課程SELECTcourse_id,course_nameFROMcoursescWHEREEXISTS(SELECT2FROMstudents_gradesgWHEREsg.course_id=c.course_id);--已經(jīng)選學(xué)的課程等價(jià)于：SELECTcourse_id,course_nameFROMcoursescWHEREcourse_idin(SELECTcourse_idFROMstudents_grade)?在相關(guān)子查詢中使用NOTEXISTS例14：在course表中檢索未被選學(xué)的課程SELECTcourse_id,course_nameFROMcoursescWHERENOTEXISTS(SELECT2FROMStudents_gradesgWHEREsg.course_id=c.course_id);等價(jià)于：SELECTcourse_id,course_nameFROMcoursescWHERHourseidnotin(SELECTcourse_idFROMStudents_gradesg);3?使用IN例15:在departments表中檢索已經(jīng)安排了教師的系部SELECTdepartment_id,department_nameFROMdepartmentsWHEREdepartment_idIN(SELECTdepartment_idFROMteachers);4.使用NOTIN例16:在departments表中檢索沒有教師的系部SELECTdepartment_id,department_nameFROMdepartmentsWHEREdepartment_idNOTIN(SELECTdepartment_idFROMteachers);五嵌套子查詢Sql語(yǔ)言允許子查詢嵌套，oracle允許最多嵌套255級(jí)。嵌套子查詢時(shí)代碼閱讀困難，修改和維護(hù)也不方便，性能比較差，使用時(shí)應(yīng)當(dāng)考慮。例17:在students表中檢索與王天一同學(xué)同專業(yè)的所有學(xué)生SELECT*FROM(SELECT*FROMstudentsWHEREspecialty=(SELECTspecialtyFROMstudentsWHEREname='王天儀'));集合查詢集合操作有并，交，差這3中運(yùn)算。集合操作符分別為UNION(UNIONALL)INTERSECT和MINUS。Union:用于獲得兩個(gè)查詢你結(jié)果的并集，且在并集合中自動(dòng)去掉重復(fù)值，以并集結(jié)果集的第一列進(jìn)行排序Union:ALL用于獲得兩個(gè)查詢你結(jié)果的并集，且在并集合中保留重復(fù)值，以并集結(jié)果集的第一列進(jìn)行排序Intersect:用于獲得兩個(gè)結(jié)果集的交集，交集以結(jié)果集的第一列進(jìn)行排序。minus:用于獲得兩個(gè)結(jié)果集的差集，差集以結(jié)果集的第一列進(jìn)行排序。創(chuàng)建副課程表minors表CREATETABLEMinors(minor_idNUMBER(5)CONSTRAINTminor_pkPRIMARYKEY,minor_nameVARCHAR2(30)NOTNULL,credit_hourNUMBER(2))；配割I(lǐng)B爵輿或lo乙OL)smmv/\s」ou!i/\ioinilaasNi：(i?￡.swWM44/totoOsanivasjouiiaioinilaasNiujioupiesjnoHd3HMINSERTINTOMinorsVALUES(10301,'工程制圖',3);創(chuàng)建課程表course2表CREATETABLEMinors(course_idNUMBER(5)CONSTRAINTcourse_pkPRIMARYKEY,course_nameVARCHAR2(30)NOTNULL,credit_hourNUMBER(2));INSERTINTOcourse2VALUES(10201,'工程制圖',3);INSERTINTOMinorsVALUES(10301,'自動(dòng)控制原理',4);一、使用集合操作符1.使用集合操作符UNIONALL例1SELECTcourse_id,course_name,credit_hourFROMCoursesUNIONALLSELECTminor_id,minor_name,credit_hourFROMMinors;例2:將courses表與minors表進(jìn)行unionall操作SELECTcourse_id,course_name,credit_hourFROMCoursesUNIONALLSELECTminor_id,minor_name,credit_hourFROMMinorsORDERBY1;例3:將students表計(jì)算機(jī)專業(yè)檢索集與男生檢索集進(jìn)行unionall操作SELECT*FROMStudentsWHEREspecialty='計(jì)算機(jī)'UNIONALLSELECT*FROMStudentsWHEREsex='男';例4：將students表計(jì)算機(jī)專業(yè)檢索集與男生檢索集進(jìn)行unionall操作，且將結(jié)果排序SELECT*FROMStudentsWHEREspecialty='計(jì)算機(jī)'UNIONALLSELECT*FROMStudentsWHEREsex='男'ORDERBY1;2.使用集合操作符UNION例5：將courses表與minirs表進(jìn)行union操作SELECTcourse_id,course_name,credit_hourFROMCoursesUNIONSELECTminor_id,minor_name,credit_hourFROMMinors;例6：學(xué)生（）*專業(yè)男生SELECT*FROMStudentsWHEREspecialty='計(jì)算機(jī)'UNIONSELECT*FROMStudentsWHEREsex='男';使用集合操作符INTERSECT例7：:將courses表與miniors表進(jìn)行intersect操作SELECTcourse_id,course_name,credit_hourFROMCoursesINTERSECTSELECTminor_id,minor_name,credit_hourFROMMinors;例8：:將students表計(jì)算機(jī)專業(yè)檢索集與男生檢索集進(jìn)行intersect操作SELECT*FROMstudentsWHEREspecialty='計(jì)算機(jī)'INTERSECTSELECT*FROMstudentsWHEREsex='男';使用集合操作符MINUS用于獲取兩個(gè)查詢結(jié)果集的差集。差集中具有在第一個(gè)查詢結(jié)果集中存在，而在第二個(gè)查詢結(jié)果集中不存在的數(shù)據(jù)，并且以差集中的第一列對(duì)差集進(jìn)行排序例9將courses表與minors表進(jìn)行minus操作SELECTcourse_id,course_name,credit_hourFROMCoursesMINUSSELECTminor_id,minor_name,credit_hourFROMMinors;例10:將students表計(jì)算機(jī)專業(yè)檢索集與男生檢索集合進(jìn)行minus操作SELECT*FROMStudentsWHEREspecialty='計(jì)算機(jī)'MINUSSELECT*FROMStudentsWHEREsex='男';組合使用集合操作符集合操作運(yùn)算符具有相同優(yōu)先級(jí)，可以用括號(hào)改變優(yōu)先級(jí)CREATETABLECourses2(course_idNUMBER(5)CONSTRAINTcourse2_pkPRIMARYKEY,course_nameVARCHAR2(30)NOTNULL,credit_hourNUMBER(2));INSERTINTOCourses2VALUES(10201,'自動(dòng)控制原理',4);INSERTINTOCourses2VALUES(10301,'工程制圖',3);例11：將courses表先與minors表進(jìn)行intersect操作，然后與(SELECTcourse_id,course_name,credit_hourFROMCoursesINTERSECTcourses2表進(jìn)行union操作SELECTminor_id,minor_name,credit_hourFROMMinors)UNIONSELECTcourse_id,course_name,credit_hourFROMCourses2;例12：將minors表與courses2表進(jìn)行intersect操作，然后與表SELECTcourse_id,course_name,credit_hourFROMCoursesUNIONcourses進(jìn)行union操作(SELECTminor_id,minor_name,credit_hourFROMMinorsINTERSECTSELECTcourse_id,course_name,credit_hourFROMCourses2);二、集合操作的進(jìn)一步討論1.集合操作中的ORDERBY子句集合操作只有一個(gè)orderby子句，并且必須在語(yǔ)句的末尾。第一個(gè)select子句中的列名來(lái)自第一個(gè)select子句中的列別名集合操作結(jié)果集中的位置編號(hào)例13:在ORDERBY子句使用列名作為排序表達(dá)式Orderby子句的多種形態(tài)：SELECTcourse_id,course_name,credit_hourFROMcoursesUNIONSELECTminor_id,minor_name,credit_hourFROMminorsORDERBYcourse_name;例14:在ORDERBY子句使用列別名SELECTcourse_id,course_nameASname,credit_hourFROMcoursesUNIONSELECTminor_id,minor_name,credit_hourFROMminorsORDERBYname;例15:在ORDERBY子句使用列位置編號(hào)SELECTcourse_id,course_name,credit_hourFROMcoursesUNIONSELECTminor_id,minor_name,credit_hourFROMminorsORDERBY2;集合操作中的SELECT語(yǔ)句如果要想為某個(gè)列制定一個(gè)別名，則必須在集合曹組偶的第一個(gè)select子句中完成。例16:Union中含有復(fù)雜的SELECT語(yǔ)句。SELECTcourse_name,SUM(credit_hour)FROMcoursesWHEREcredit_hour>3GROUPBYcourse_nameUNIONSELECTminor_name,SUM(credit_hour)FROMminorsWHEREcredit_hour>2GROUPBYminor_nameORDERBYcourse_name;集合操作中的數(shù)據(jù)類型在集合操作的結(jié)果集中，包含來(lái)自兩個(gè)原表的行。這個(gè)結(jié)果集中的每一列都有一個(gè)特定的數(shù)據(jù)類型。數(shù)據(jù)類型相同但寬度不同，oracle自動(dòng)使其寬度相同數(shù)據(jù)類型不同時(shí)，可以通過類型轉(zhuǎn)換函數(shù)使其具有相同類型例5.2_17數(shù)據(jù)類型相同但寬度不同CREATETABLEtable_1(column_11NUMBER(3),column_12VARCHAR2(2));INSERTINTOtable_1VALUES(111,'aa');INSERTINTOtable_1VALUES(222,'bb');INSERTINTOtable_1VALUES(333,'cc');CREATETABLEtable_2(column_21NUMBER(5),column_22VARCHAR2(4));INSERTINTOtable_2VALUES(44444,'dddd');INSERTINTOtable_2VALUES(55555,'eeee');INSERTINTOtable_2VALUES(66666,'ffff');SELECTcolumn_11,column_12FROMtable_1UNIONSELECTcolumn_21,column_22FROMtable_2;例5.2_數(shù)據(jù)類型不相同CREATETABLEtable_3(column_31NUMBER(3),column_32VARCHAR2(2),column_33VARCHAR2(10));INSERTINTOtable_3VALUES(111,'aa','aaaaaaaaaa');INSERTINTOtable_3VALUES(222,'bb','bbbbbbbbbb');INSERTINTOtable_3VALUES(333,'cc','cccccccccc');CREATETABLEtable_4(column_41VARCHAR2(3),column_42NUMBER(2),column_43DATE);INSERTINTOtable_4VALUES('ddd',44,'07-5月-1988');INSERTINTOtable_4VALUES('eee',55,'07-5月-1988');INSERTINTOtable_4VALUES('fff',66,'07-5月-1988');COLUMNcolumn_1FORMATa10COLUMNcolumn_32FORMATa10COLUMNcolumn_33FORMATa10SELECTTO_CHAR(column_31)AScolumn_1,column_32,column_33FROMtable_3UNIONSELECTcolumn_41,TO_CHAR(column_42),TO_CHAR(column_43,'YYYY-MM-DD')FROMtable_4;練習(xí)：.用兩種方法檢索出全國(guó)的省名.查詢某個(gè)省(如遼寧省)的全部城市信息3.查詢某個(gè)省(如遼寧省)中城市名包含某個(gè)字(如‘陽(yáng)’)的全部城市信息4.查詢城市名中包含’京’的所有城市信息檢索城市數(shù)最少(/最多)的省及其城市數(shù)，顯示省名和城市數(shù)(用別名cityCount)寫出將各個(gè)字段的前后空格刪除的sql語(yǔ)句顯示各條記錄city字段的實(shí)際長(zhǎng)度，查詢cityinfo中城市名最長(zhǎng)(/最短)的信息查詢cityinfo中各個(gè)字段的最大(/最小)長(zhǎng)度查詢tele字段為空值或空串的記錄11獲得安徽和'北京'的城市數(shù)將cityinfo表中的省保存到新表provicneinfo中用provicneinfo中省ID更新cityinfo表的provicneiD字段--找出具有相同城市數(shù)的省，并顯示省名和該省城市總數(shù)withmyGroupas(selectprovince,count(*)astotalfromCityinfogroupbyprovineeorderby2desc)selectprovince,totalfrommyGroupwheretotalin(selecttotalfrommyGroupgroupbytotalhavingcount(*)>1)連接查詢連接查詢是指在兩個(gè)或兩個(gè)以上的表或視圖上進(jìn)行的查詢。依據(jù)連接條件，連接查詢組合兩個(gè)或兩個(gè)以上的表或視圖中的數(shù)據(jù)，形成查詢結(jié)果。連接條件中，可以使用相等(=),不相等(<>!=),小于(<),小于等于(<=),大于(>),大于等于(>=)Likein和betweenand等比較運(yùn)算符，其中使用”"運(yùn)算符作為連接條件的連接查詢，稱為相等連接，使用除””之外的運(yùn)算符作為連接條件的連接查詢，稱為不等連接在連接查詢時(shí)，如果表中過的一些行在其他表中不存在匹配行，內(nèi)連接查詢結(jié)果中刪除原表中的這些行，而外連接查詢結(jié)果中保留原表中的這些行。另外，作為特殊情況之一，連接查詢不使用任何連接條件，稱這樣的查詢?yōu)榻徊?笛卡爾乘積)連接查詢；特殊情況之二，連接查詢?cè)谝粋€(gè)表或視圖上進(jìn)行，這樣的查詢稱為自連接一、連接查詢1內(nèi)連接查詢1.1簡(jiǎn)單內(nèi)連接1.相等連接使用比較運(yùn)算符””作為連接條件例6.1_1SELECTteacher_id,name,department_nameFROMTeachers,DepartmentsWHERETeachers.department_id=Departments.department_id;例6.12SELECTs.student_id,name,course_id,scoreFROMStudentss,Students_gradesgWHEREs.student_id=sg.student_id;例6.1_3SELECTc.course_id,course_name,student_id,scoreFROMCoursesc,Students_gradesgWHEREc.course_id=sg.course_id;2.不等連接使用除”=”之外的運(yùn)算符作為連接條件的連接查詢，稱為不等連接例6.1_4SELECTstudent_id,score,gradeFROMStudents_gradesg,GradesgWHEREsg.scoreBETWEENg.low_scoreANDg.high_score;2復(fù)雜內(nèi)連接使用篩選條件例6.1_5SELECTteacher_id,name,department_nameFROMTeacherst,DepartmentsdWHEREt.department_id=d.department_idANDtitle='講師';例6.1_6SELECTs.student_id,name,course_id,scoreFROMStudentss,Students_gradesgWHEREs.student_id=sg.student_idANDspecialty='計(jì)算機(jī)';例6.1_7SELECTc.course_id,course_name,student_id,scoreFROMCoursesc,Students_gradesgWHEREc.course_id=sg.course_idANDcourse_name='C++語(yǔ)言程序設(shè)計(jì)例6.1_8SELECTs.student_id,,count(*)AS所修課程門數(shù)FROMStudentss,Students_gradesgWHEREs.student_id=sg.student_idGROUPBYs.student_id,HAVINGcount(*)>1ORDERBYs.student_id;多(兩個(gè)以上)表連接例6.1_9SELECT,course_name,scoreFROMStudentss,Coursesc,Students_gradesgWHEREs.student_id=sg.student_idANDc.course_id=sg.course_id;例6.1_10SELECTs.student_id,,c.course_name,AVG(sg.score)AS平均成績(jī)FROMStudentss,Coursesc,Students_gradesgWHEREs.student_id=sg.student_idANDc.course_id=sg.course_idGROUPBYs.student_id,,c.course_name;3外連接查詢外連接插敘那是由內(nèi)連接插敘你擴(kuò)展產(chǎn)生的，內(nèi)連接查詢返回滿足連接的記錄；接查而外連詢則在內(nèi)連接查詢結(jié)果的基礎(chǔ)上，部分或全部添加回被內(nèi)連接查詢從原表中刪除的記錄。據(jù)此，外連接插敘你有分為左外連接、右外連接和全外連接查詢3種類型。左外連接添加回內(nèi)連接查詢從第一個(gè)表中刪除的所有行。右外連接添加回內(nèi)連接查詢從第二個(gè)表中刪除的所有行。全外連接添加回內(nèi)連接查詢從兩個(gè)表中刪除的所有行。3。1左外連接例1SELECTteacher_id,name,depart