尚硅谷_宋紅康_06_子查詢(xún)

1、第6節(jié)子查詢(xún)講師：宋紅康新浪微博：尚硅谷-宋紅康目標(biāo)通過(guò)本章學(xué)習(xí)，您將可以:描述子查詢(xún)可以解決的問(wèn)題。定義子查詢(xún)。列出子查詢(xún)的類(lèi)型。書(shū)寫(xiě)單行子查詢(xún)和多行子查詢(xún)。使用子查詢(xún)解決問(wèn)題誰(shuí)的工資比Abel 高?Main Query:誰(shuí)的工資比Abel高?Subquery?Abel的工資是多少?子查詢(xún)語(yǔ)法SELECT FROM WHEREselect_list tableexpr operator子查詢(xún)(內(nèi)查詢(xún))在主查詢(xún)之前一次執(zhí)行完成。子查詢(xún)的結(jié)果被主查詢(xún)(外查詢(xún))使用。(SELECTselect_list FROMtable);子查詢(xún)SELECT FROM WHERElast_name employ

2、ees salary 11000(SELECT salary FROMemployeesWHERElast_name = Abel);注意事項(xiàng)子查詢(xún)要包含在括號(hào)內(nèi)。將子查詢(xún)放在比較條件的右側(cè)。單行操作符對(duì)應(yīng)單行子查詢(xún)，多行操作符對(duì)應(yīng)多行子查詢(xún)。子查詢(xún)類(lèi)型 單行子查詢(xún)返回ST_CLERK 多行子查詢(xún)返回ST_CLERKSA_MAN主查詢(xún)子查詢(xún)主查詢(xún)子查詢(xún)單行子查詢(xún)只返回一行。使用單行比較操作符。操作符含義=Equal toGreater than=Greater than or equal toLess than=Less than or equal toNot equal to執(zhí)行單行子查詢(xún)題

3、目：返回job_id與141號(hào)員工相同，salary比143號(hào)員工多的員工 姓名，job_id 和工資SELECT FROM WHERElast_name, job_id, employeessalaryST_CLERKjob_id =)2600ANDsalary (SELECT salary FROMemployeesWHEREemployee_id = 143);(SELECT job_id FROMemployeesWHEREemployee_id = 141在子查詢(xún)中使用組函數(shù)題目：返回公司工資最少的員工的last_name,job_id和salarySELECT FROM WHERE

4、last_name, job_id, salaryemployees2500salary =(SELECT MIN(salary) FROMemployees);子查詢(xún)中的HAVING 子句首先執(zhí)行子查詢(xún)。向主查詢(xún)中的HAVING 子句返回結(jié)果。題目：查詢(xún)最低工資大于50號(hào)部門(mén)最低工資的部門(mén)id和其最低工資SELECT FROM GROUP BYdepartment_id, MIN(salary) employeesdepartment_id250050);HAVINGMIN(salary)(SELECT MIN(salary) FROMemployees WHEREdepartment_id

5、 =非法使用子查詢(xún)SELECT FROM WHEREemployee_id, employeeslast_name=ERRORat line 4:ORA-01427: single-row one rowsubqueryreturns morethan(SELECTMIN(salary) FROMemployeesGROUP BY department_id);salary子查詢(xún)中的空值問(wèn)題SELECT FROM WHERElast_name, employeesjob_idjob_id =no rows selected(SELECT job_id FROMemployeesWHERElas

6、t_name = Haas);多行子查詢(xún)返回多行。使用多行比較操作符。 體會(huì)any和all的區(qū)別操作符含義IN等于列表中的任意一個(gè)ANY和子查詢(xún)返回的某一個(gè)值比較ALL和子查詢(xún)返回的所有值比較在多行子查詢(xún)中使用ANY 操作符題目：返回其它部門(mén)中比job_id為IT_PROG部門(mén)任一工資低的員工的員工號(hào)、姓名、job_id 以及salarySELECT FROM WHEREemployee_id, last_name, job_id,salaryemployees9000, 6000,4800, 4200salaryANDjob_idIT_PROG;(SELECT salary FROMempl

7、oyeesWHEREjob_id = IT_PROG)ANY在多行子查詢(xún)中使用ALL 操作符題目：返回其它部門(mén)中比job_id為IT_PROG部門(mén)所有工資都低的員工 的員工號(hào)、姓名、job_id 以及salarySELECTemployee_id, last_name, job_id,salaryFROM WHEREemployees9000, 6000, 4800,4200salary ANDjob_id IT_PROG;(SELECT salaryFROMemployeesWHEREjob_id = IT_PROG)ALL子查詢(xún)中的空值問(wèn)題SELECT FROM WHEREemp.last_name employees emp emp.employee_id NOT IN(SELECT mgr.manager_idFROMemployees mgr);no rows selected總通過(guò)本章