MySQL數(shù)據(jù)庫(kù) 課件 3.6 子查詢(xún)與集合查詢(xún)1

主講教師：姜云橋任務(wù)3.6子查詢(xún)與union查詢(xún)子項(xiàng)目3數(shù)據(jù)庫(kù)的查詢(xún)開(kāi)發(fā)任務(wù)目標(biāo)1.能熟練使用子查詢(xún)方式完成各種查詢(xún)需求2.能使用集合查詢(xún)完成查詢(xún)需求知識(shí)目標(biāo)1.掌握子查詢(xún)可以出現(xiàn)的位置。2.掌握標(biāo)量子查詢(xún)3.掌握列子查詢(xún)4.掌握行子查詢(xún)5.掌握表子查詢(xún)6.掌握集合查詢(xún)能力目標(biāo)任務(wù)要求利用數(shù)據(jù)庫(kù)jxgl中的student(學(xué)生信息表)、course（課程表）、score（成績(jī)表）、department（院系單位表）、teacher（教師表）和teach（講授表），完成標(biāo)量子查詢(xún)、列子查詢(xún)、行子查詢(xún)、表子查詢(xún)以及合并查詢(xún)?nèi)蝿?wù)必備知識(shí)想一想什么是子查詢(xún)，分為幾種類(lèi)型？任務(wù)必備知識(shí)3.6.1子查詢(xún)子查詢(xún)是指嵌套在其他sql語(yǔ)句（如insert、delete、select、update等）中的查詢(xún)語(yǔ)句。子查詢(xún)也稱(chēng)為內(nèi)查詢(xún)，必須位于括號(hào)之中；包含子查詢(xún)的查詢(xún)被稱(chēng)為外查詢(xún)。MySQL中的子查詢(xún)按照返回結(jié)果可以分為以下幾種類(lèi)型：標(biāo)量子查詢(xún)：返回單個(gè)值（一行一列）的子查詢(xún)。列子查詢(xún)：返回單列結(jié)果（多行一列）的子查詢(xún)。行子查詢(xún)（RowSubquery）：返回單行結(jié)果（一行多列）的子查詢(xún)，標(biāo)量子查詢(xún)是行子查詢(xún)的一個(gè)特例。表子查詢(xún)（TableSubquery）：返回一個(gè)虛擬表（多行多列）的子查詢(xún)，行子查詢(xún)是表子查詢(xún)的一個(gè)特例。任務(wù)必備知識(shí)3.6.1子查詢(xún)按照內(nèi)查詢(xún)與外查詢(xún)之間的關(guān)系可分為：非關(guān)聯(lián)子查詢(xún)：內(nèi)查詢(xún)不依賴(lài)外查詢(xún)的信息，可獨(dú)立運(yùn)行。關(guān)聯(lián)子查詢(xún)：內(nèi)查詢(xún)引用外查詢(xún)的字段，無(wú)法獨(dú)立運(yùn)行。任務(wù)必備知識(shí)一、標(biāo)量子查詢(xún)是指子查詢(xún)返回的是單一值的標(biāo)量，如一個(gè)數(shù)字或一個(gè)字符串?？梢允褂?、>、<、>=、<=、<>等操作符對(duì)子查詢(xún)的標(biāo)量結(jié)果進(jìn)行比較。3.6.1子查詢(xún)【任務(wù)3.6.1】查詢(xún)成績(jī)表score，返回大于平均成績(jī)的記錄mysql>select*->fromscore->wheremark>(selectavg(mark)fromscore);任務(wù)必備知識(shí)二、列子查詢(xún)指子查詢(xún)返回的結(jié)果集是N行一列，該結(jié)果通常來(lái)自對(duì)表的某個(gè)字段查詢(xún)返回，可以使用in、any、some和all等操作符，含義如下。3.6.1子查詢(xún)?nèi)蝿?wù)必備知識(shí)3.6.1子查詢(xún)【任務(wù)3.6.2】查詢(xún)授課任務(wù)的老師的信息mysql>select*fromteacherwheret_noin(selectdistinctt_nofromteach);任務(wù)必備知識(shí)三、行子查詢(xún)子查詢(xún)返回的結(jié)果集是一行N列，該子查詢(xún)的結(jié)果通常是對(duì)表的某行數(shù)據(jù)進(jìn)行查詢(xún)而返回的結(jié)果集。3.6.1子查詢(xún)【任務(wù)3.6.3】查詢(xún)與”劉光明“同學(xué)同性別同系部的學(xué)生的基本信息mysql>select*->fromstudent->where(s_sex,d_no)=(selects_sex,d_nofromstudentwheres_name='劉光明')->ands_name<>'劉光明‘；任務(wù)必備知識(shí)四、表子查詢(xún)子查詢(xún)返回的結(jié)果集是N行N列的一個(gè)表數(shù)據(jù)。表子查詢(xún)通常用于FROM子句或者查詢(xún)條件中。3.6.1子查詢(xún)【任務(wù)3.6.4】使用表子查詢(xún)獲取各系部名稱(chēng)和學(xué)生的數(shù)量mysql>selectd.d_nameas'系部名稱(chēng)',ifnull(s.num,0)as'學(xué)生數(shù)量'->fromdepartmentdleftjoin(-> selectd_no,count(*)asnumfromstudentgroupbyd_no->)s->ond.d_no=s.d_no;任務(wù)必備知識(shí)3.6.1子查詢(xún)?nèi)蝿?wù)必備知識(shí)五、關(guān)聯(lián)子查詢(xún)內(nèi)部查詢(xún)引用了外部查詢(xún)列，即內(nèi)部查詢(xún)會(huì)對(duì)外部查詢(xún)的每行進(jìn)行一次計(jì)算，需要注意，使用關(guān)聯(lián)子查詢(xún)是最容易出現(xiàn)錯(cuò)誤的地方。3.6.1子查詢(xún)【任務(wù)3.6.5】使用關(guān)聯(lián)子查詢(xún)獲得各個(gè)系部的教師的數(shù)量，顯示系部名稱(chēng)，教師數(shù)量mysql>selectd.d_nameas'系部名稱(chēng)',->(selectcount(*)fromteachertwheret.d_no=d.d_no)as'教師數(shù)量' ->fromdepartmentd;任務(wù)必備知識(shí)3.6.1子查詢(xún)+--------------+--------------+|系部名稱(chēng)|教師數(shù)量|+--------------+--------------+|人文學(xué)院|2||信息學(xué)院|5||外語(yǔ)學(xué)院|2||建工學(xué)院|2||機(jī)電學(xué)院|2||珠寶學(xué)院|2||管理學(xué)院|2||金融學(xué)院|3|+--------------+--------------+任務(wù)必備知識(shí)六、exists操作符exists檢查指定查詢(xún)是否產(chǎn)生某些行。根據(jù)子查詢(xún)是否返回行，exists僅返回true或false，不會(huì)返回unkown，這是和其他操作符（如in、any、all等）的區(qū)別，notexists同樣如此，只不過(guò)功能相反。3.6.1子查詢(xún)?nèi)蝿?wù)必備知識(shí)3.6.1子查詢(xún)【任務(wù)3.6.6】返回不存在女性學(xué)生的系部名稱(chēng)mysql>selectd.d_name->fromdepartmentd->wherenotexists(->select1fromstudentstwherest.d_no=d.d_noandst.s_sex='女');任務(wù)必備知識(shí)3.6.2合并查詢(xún)union操作符用于將兩個(gè)查詢(xún)結(jié)果合并成一個(gè)結(jié)果集?；菊Z(yǔ)法格式如下：selectcol_a1,col_a2,...fromtable1union[distinct|all]selectcol_b1,col_b2,...fromtable2;任務(wù)必備知識(shí)【任務(wù)3.6.7】通過(guò)unionall查詢(xún)成績(jī)大于90和小于60的學(xué)生的學(xué)號(hào)mysql>selects_nofromscorewheremark>90->unionall->selects_nofromscorewheremark<60;3.6.2合并查詢(xún)?nèi)蝿?wù)必備知識(shí)【任務(wù)3.6.8】通過(guò)union查詢(xún)成績(jī)大于90和小于60的學(xué)生的學(xué)號(hào)mysql>selects_nofromscorewheremark>90->union->selects_nofromscorewheremark<60;3.6.2合并查詢(xún)?nèi)蝿?wù)必備知識(shí)【任務(wù)3.6.9】通過(guò)union查詢(xún)成績(jī)大于90和小于60的學(xué)生的學(xué)號(hào)，并按學(xué)號(hào)降序顯示mysql>selects_nofromscorewheremark>90->union->selects_nofromscorewheremark<60->orderbys_nodesc;3.6.2合并查詢(xún)?nèi)蝿?wù)必備知識(shí)【任務(wù)3.6.10】通過(guò)查詢(xún)成績(jī)表score成績(jī)第一和最后一名的學(xué)生的學(xué)號(hào)和成績(jī)mysql>(selects_no,markfromscoreorderbymarkdesclimit1) ->union ->(selects_no,markfromscoreorderbymarklimit1);3.6.2合并查詢(xún)?nèi)蝿?wù)實(shí)施在人事管理數(shù)據(jù)庫(kù)rsgl中進(jìn)行如下操作1.查詢(xún)和趙劍同職稱(chēng)、學(xué)歷、政治背景的員工的姓名、職稱(chēng)、學(xué)歷、政治背景，結(jié)果如下:任務(wù)實(shí)施2.查詢(xún)沒(méi)有員工的部門(mén)的名稱(chēng)，結(jié)果如下:3.查年齡大于50，并且不是信息學(xué)院與外語(yǔ)學(xué)院的員工姓名和性別,結(jié)果如下:任務(wù)實(shí)施4.查詢(xún)收入最高（收入=基本收入+福利+補(bǔ)貼-公積金-稅-其他）和最低的員工編號(hào)和收入，結(jié)果如下:5.查詢(xún)超過(guò)基本收入平均值的員工的姓名，結(jié)果如下:任務(wù)實(shí)施要求撰寫(xiě)實(shí)訓(xùn)總結(jié)，把完成任務(wù)過(guò)程中主要操作、命令及關(guān)鍵步驟的截圖，遇到的問(wèn)題與解決方法、未解決或需要進(jìn)一步探討的問(wèn)題、以及任務(wù)實(shí)踐過(guò)程