多表查询之前再DQL中初步整理了用select关键字进行单表查询多表查询是利用数据表中的同一外键进行连接从而获取更多的数据进行连接查询。多表查询多表关系多表查询概述内连接外连接自连接子查询多表查询案例我们需要事先插入一些相关的表格学生表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专业,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,本科,舞蹈,静安区第一小学,静安区第一中学,北京舞蹈学院,1),(null,硕士,表演,朝阳区第一小学,朝阳区第一中学,北京电影学院,2),(null,本科,英语,杭州市第一小学,杭州市第一中学,杭州师范大学,3),(null,本科,应用数学,阳泉区第一小学,阳泉区第一中学,清华大学,4);内连接隐式内连接select字段列表from表1,表2where连接条件and筛选条件;显式内连接select字段列表from表1[inner]join表2on连接条件...;内连接查询的是两张表交集的部分-- 查询每一个员工的姓名及关联部门的名称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左表的所有数据包含表1和表2交集部分的数据右外连接select字段列表from表1right[outer]join表2on条件...;-- 查询emp表的所有数据和对应的部门信息(左外连接)selecte.*,d.namefromemp eleftouterjoindept done.dept_idd.id;-- 查询dept表的所有数据和对应的员工信息右外连接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查出来的结果作为另一个查询的条件并且第一次查询出来的结果只有一个信息子查询返回的结果是单个值数字、字符串、日期等最简单的形式。-- 总目标查询销售部的所有员工信息-- 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在指定的集合范围之内多选一NOT IN不在指定的集合范围之内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财务部));-- 查询比研发部其中任意一人工资高的员工信息select*fromempwheresalaryany(selectsalaryfromempwheredept_id(selectidfromdeptwherename研发部));行子查询子查询返回的结果是一行同时包含多个字段-- 查询与张无忌的薪资及直属领导相同的员工信息-- 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(鹿杖客,宋远桥));-- 查询入职日期是 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岁的员工及其归属的部门名称如果员工没有分配部门也需要展示出来。查询所有员工的工资等级。查询研发部所有员工的信息及工资等级。查询研发部员工的平均工资。查询工资比灭绝高的员工信息。查询比平均薪资高的员工信息。查询低于本部门平均工资的员工信息。查询所有的部门信息并统计部门的员工人数。查询所有学生的选课情况展示出学生名称学号课程名称主要用到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. 查询研发部所有员工的信息及工资等级。-- 先在dept找研发部id然后在emp筛研发部信息所有员工信息然后求工资等级selecte.*,s.gradefromemp e,dept d,salgrade swheree.dept_idd.idand(e.salarybetweens.losalands.hisal)andd.name研发部;selecte.*,s.gradefrom(select*fromempwheredept_id(selectidfromdeptwherename研发部))e,salgrade swheree.salarys.losalande.salarys.hisal;-- 7. 查询研发部员工的平均工资。-- 先查出来研发部的id, 然后再算所有id一样的人的工资的平均值selectavg(salary)fromemp e,dept dwheree.dept_idd.idandd.name研发部;selectavg(salary)fromempwheredept_id(selectidfromdeptwherename研发部);-- 8. 查询工资比灭绝高的员工信息。select*fromempwheresalary(selectsalaryfromempwherename灭绝);-- 9. 查询比平均薪资高的员工信息select*fromempwheresalary(selectavg(salary)fromemp);-- 10. 查询低于本部门平均工资的员工信息。-- 外层查询每一行内层查询计算该员工所在部门的平均工资然后比较。--select*fromemp ewheree.salary(selectavg(salary)fromempwheree.dept_iddept_id);-- 11. 查询所有的部门信息并统计部门的员工人数。selectd.id,d.name,(selectcount(*)fromemp ewheree.dept_idd.id)人数fromdept d;-- 12. 查询所有学生的选课情况展示出学生名称学号课程名称selects.name学生名称,s.no学号,c.name课程名称fromstudent s,course c,student_course scwheres.idsc.studentidandc.idsc.courseid;