多表查詢之前再DQL中初步整理了用select關鍵字進行單表查詢多表查詢是利用數(shù)據(jù)表中的同一外鍵進行連接從而獲取更多的數(shù)據(jù)進行連接查詢。多表查詢多表關系多表查詢概述內(nèi)連接外連接自連接子查詢多表查詢案例我們需要事先插入一些相關的表格學生表createtablestudent(idintauto_incrementprimarykeycomment主鍵ID,namevarchar(10)comment姓名,novarchar(10)comment學號)comment學生表;insertintostudentvalues(null,黛綺絲,2000100101),(null,謝遜,2000100102),(null,殷天正,2000100103),(null,韋一笑,2000100104);課程表createtablecourse(idintauto_incrementprimarykeycomment主鍵ID,namevarchar(10)comment課程名稱)comment課程表;insertintocoursevalues(null,Java),(null,PHP),(null,MySQL),(null,Hadoop);學生課程表createtablestudent_course(idintauto_incrementcomment主鍵primarykey,studentidintnotnullcomment學生ID,courseidintnotnullcomment課程ID,constraintfk_courseidforeignkey(courseid)referencescourse(id),constraintfk_studentidforeignkey(studentid)referencesstudent(id))comment學生課程中間表;insertintostudent_coursevalues(null,1,1),(null,1,2),(null,1,3),(null,2,2),(null,2,3),(null,3,4);用戶基本信息表createtabletb_user(idintauto_incrementprimarykeycomment主鍵ID,namevarchar(10)comment姓名,ageintcomment年齡,genderchar(1)comment1:男, 2:女,phonechar(11)comment手機號)comment用戶基本信息表;insertintotb_user(id,name,age,gender,phone)VALUES(null,黃渤,45,1,18800001111),(null,冰冰,35,2,18800002222),(null,碼云,55,1,18800008888),(null,李彥宏,50,1,18800009999);用戶教育信息表createtabletb_user_edu(idintauto_incrementprimarykeycomment主鍵ID,degreevarchar(20)comment學歷,majorvarchar(50)comment專業(yè),primaryschoolvarchar(50)comment小學,middleschoolvarchar(50)comment中學,universityvarchar(50)comment大學,useridintuniquecomment用戶ID,constraintfk_useridforeignkey(userid)referencestb_user(id))comment用戶教育信息表;insertintotb_user_edu(id,degree,major,primaryschool,middleschool,university,userid)VALUES(null,本科,舞蹈,靜安區(qū)第一小學,靜安區(qū)第一中學,北京舞蹈學院,1),(null,碩士,表演,朝陽區(qū)第一小學,朝陽區(qū)第一中學,北京電影學院,2),(null,本科,英語,杭州市第一小學,杭州市第一中學,杭州師范大學,3),(null,本科,應用數(shù)學,陽泉區(qū)第一小學,陽泉區(qū)第一中學,清華大學,4);內(nèi)連接隱式內(nèi)連接select字段列表from表1,表2where連接條件and篩選條件;顯式內(nèi)連接select字段列表from表1[inner]join表2on連接條件...;內(nèi)連接查詢的是兩張表交集的部分-- 查詢每一個員工的姓名及關聯(lián)部門的名稱selectemp.name,dept.namefromemp,deptwhereemp.dept_iddept.id;-- 起別名selecte.name,de.namefromemp e,dept dewheree.dept_idde.id;-- 顯式查詢selecte.name,d.namefromemp ejoindept done.dept_idd.id;外連接實際上用左連接居多右連接也可以改成左連接。左外連接select字段列表from表1left[outer]join表2on條件...;相當于查詢表1左表的所有數(shù)據(jù)包含表1和表2交集部分的數(shù)據(jù)右外連接select字段列表from表1right[outer]join表2on條件...;-- 查詢emp表的所有數(shù)據(jù)和對應的部門信息(左外連接)selecte.*,d.namefromemp eleftouterjoindept done.dept_idd.id;-- 查詢dept表的所有數(shù)據(jù)和對應的員工信息右外連接selectd.*,e.*fromemp erightouterjoindept done.dept_idd.id;自連接當自身表的兩個字段需要進行連接時必須要分別起別名不然不知道具體是哪張表用的這個字段-- 查詢員工及其所屬領導的名字selecta.name,b.namefromemp a,emp bwherea.manageridb.id;-- 查詢所有員工emp及其領導的名字emp如果員工沒有領導也需要查詢出來selecta.name員工,b.name領導fromemp aleftjoinemp bona.manageridb.id;子查詢用select 進行嵌套將表篩出來一遍之后再進行查詢標量子查詢利用上一個select查出來的結果作為另一個查詢的條件并且第一次查詢出來的結果只有一個信息子查詢返回的結果是單個值數(shù)字、字符串、日期等最簡單的形式。-- 總目標查詢銷售部的所有員工信息-- a.查詢銷售部的所有員工信息 (查出來是4)selectidfromdeptwherename銷售部;-- b.查詢銷售部部門ID, 查詢員工信息select*fromempwheredept_id4;-- 合并4就是a查出來的結果直接替換即可select*fromempwheredept_id(selectidfromdeptwherename銷售部);-- 總查詢“方東白”之后入職的員工信息-- a.查詢“方東白”的入職時間selectentrydatefromempwherename方東白;-- b.查詢所有入職時間晚于此時間的員工信息select*fromempwhereentrydate2009-02-12;-- 總select*fromempwhereentrydate(selectentrydatefromempwherename方東白);列子查詢子查詢返回的結果是一列可以是多行常用操作符IN NOT IN ANY SOME ALL操作符描述IN在指定的集合范圍之內(nèi)多選一NOT IN不在指定的集合范圍之內(nèi)ANY子查詢返回列表中有任意一個滿足即可SOME與 ANY 等同使用 SOME 的地方都可以使用 ANYALL子查詢返回列表的所有值都必須滿足-- 查詢銷售部和市場部的所有員工信息select*fromempwheredept_idin(selectidfromdeptwheredept.name銷售部or市場部);-- 查詢比 財務部 所有人工資都高的員工信息(max 和 any 都可以)-- 先查財務部的id,再查財務部最高的薪水然后是大于這個薪水的人員信息select*fromempwheresalary(selectmax(salary)fromempwheredept_id(selectidfromdeptwherename財務部));select*fromempwheresalaryall(selectsalaryfromempwheredept_id(selectidfromdeptwherename財務部));-- 查詢比研發(fā)部其中任意一人工資高的員工信息select*fromempwheresalaryany(selectsalaryfromempwheredept_id(selectidfromdeptwherename研發(fā)部));行子查詢子查詢返回的結果是一行同時包含多個字段-- 查詢與張無忌的薪資及直屬領導相同的員工信息-- 1.先查出來張無忌的薪資和領導selectsalary,manageridfromempwherename張無忌;-- 查出來薪資和領導一樣的員工信息select*fromempwhere(salary,managerid)(12500,1);-- 總和select*fromempwhere(salary,managerid)(selectsalary,manageridfromempwherename張無忌);表子查詢 查詢返回的是多行多列一張表往往可以放在from后面用于查詢。-- 查詢與鹿杖客,宋遠橋的職位和薪資相同的員工信息-- 1.先查詢兩個人的職位和薪資selectjob,salaryfromempwherenamein(鹿杖客,宋遠橋);jobsalary職員3750銷售4600-- 2.查詢職位和薪資在表中有的信息select*fromempwhere(job,salary)in(selectjob,salaryfromempwherenamein(鹿杖客,宋遠橋));-- 查詢?nèi)肼毴掌谑?2006-01-01 之后的員工信息及其部門信息-- 1.入職日期之后的員工信息select*fromempwhereentrydate2006-01-01;-- 2.查詢這部分員工對應的部門信息selecta.*,b.*from這部分表 aleftjoindept bona.dept_idb.id;-- 總selecta.*,b.*from(select*fromempwhereentrydate2006-01-01)aleftjoindept bona.dept_idb.id;多表查詢案例查詢員工的姓名、年齡、職位、部門信息。查詢年齡小于30歲的員工姓名、年齡、職位、部門信息。查詢擁有員工的部門ID、部門名稱。查詢所有年齡大于40歲的員工及其歸屬的部門名稱如果員工沒有分配部門也需要展示出來。查詢所有員工的工資等級。查詢研發(fā)部所有員工的信息及工資等級。查詢研發(fā)部員工的平均工資。查詢工資比滅絕高的員工信息。查詢比平均薪資高的員工信息。查詢低于本部門平均工資的員工信息。查詢所有的部門信息并統(tǒng)計部門的員工人數(shù)。查詢所有學生的選課情況展示出學生名稱學號課程名稱主要用到empdept表和salgrade表薪資等級將salgrade表插入:createtablesalgrade(gradeint,losalint,hisalint)comment薪資等級表;insertintosalgradevalues(1,0,3000);insertintosalgradevalues(2,3001,5000);insertintosalgradevalues(3,5001,8000);insertintosalgradevalues(4,8001,10000);insertintosalgradevalues(5,10001,15000);insertintosalgradevalues(6,15001,20000);insertintosalgradevalues(7,20001,25000);insertintosalgradevalues(8,25001,30000);12個多表查詢案例-- 1. 查詢員工的姓名、年齡、職位、部門信息。selecte.name,age,job,d.namefromemp e,dept dwheree.dept_idd.id;-- 2. 查詢年齡小于30歲的員工姓名、年齡、職位、部門信息。selecte.name,age,job,d.namefromemp e,dept dwheree.dept_idd.idandage30;selecte.name,e.age,e.job,d.name,fromemp ejoindept done.dept_idd.idwheree.age30;-- 3. 查詢擁有員工的部門ID、部門名稱。selectdistinctd.id,d.namefromemp e,dept dwheree.dept_idd.id;selecte.dept_id,d.namefromemp e,dept dwheree.dept_idd.idgroupbye.dept_id,d.namehavingcount(e.dept_id)0;-- 4. 查詢所有年齡大于40歲的員工及其歸屬的部門名稱如果員工沒有分配部門也需要展示出來。selecte.*,d.namefromemp eleftjoindept done.dept_idd.idwheree.age40;-- 5. 查詢所有員工的工資等級。selecte.*,s.gradefromemp e,salgrade swheree.salarybetweens.losalands.hisal;selecte.*,s.gradefromemp e,salgrade swheree.salarys.losalande.salarys.hisal;-- 6. 查詢研發(fā)部所有員工的信息及工資等級。-- 先在dept找研發(fā)部id然后在emp篩研發(fā)部信息所有員工信息然后求工資等級selecte.*,s.gradefromemp e,dept d,salgrade swheree.dept_idd.idand(e.salarybetweens.losalands.hisal)andd.name研發(fā)部;selecte.*,s.gradefrom(select*fromempwheredept_id(selectidfromdeptwherename研發(fā)部))e,salgrade swheree.salarys.losalande.salarys.hisal;-- 7. 查詢研發(fā)部員工的平均工資。-- 先查出來研發(fā)部的id, 然后再算所有id一樣的人的工資的平均值selectavg(salary)fromemp e,dept dwheree.dept_idd.idandd.name研發(fā)部;selectavg(salary)fromempwheredept_id(selectidfromdeptwherename研發(fā)部);-- 8. 查詢工資比滅絕高的員工信息。select*fromempwheresalary(selectsalaryfromempwherename滅絕);-- 9. 查詢比平均薪資高的員工信息select*fromempwheresalary(selectavg(salary)fromemp);-- 10. 查詢低于本部門平均工資的員工信息。-- 外層查詢每一行內(nèi)層查詢計算該員工所在部門的平均工資然后比較。--select*fromemp ewheree.salary(selectavg(salary)fromempwheree.dept_iddept_id);-- 11. 查詢所有的部門信息并統(tǒng)計部門的員工人數(shù)。selectd.id,d.name,(selectcount(*)fromemp ewheree.dept_idd.id)人數(shù)fromdept d;-- 12. 查詢所有學生的選課情況展示出學生名稱學號課程名稱selects.name學生名稱,s.no學號,c.name課程名稱fromstudent s,course c,student_course scwheres.idsc.studentidandc.idsc.courseid;