一. 多表关系

一对多（多对一）

多对一

(资料图片)

一对一

1. 一对多(1). 案例：部门与员工的关系(2). 关系：一个部门对应多个员工，一个员工对应一个部门(3). 实现：在多的一方建立外建，指向一的一方的主键2. 多对多(1).案例：学生与课程的关系(2).关系：一个学生可以选修多门课程，一门课程也可以供多个学生选择(3).实现：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

1createtablestudent( 2idintauto_incrementprimarykeycomment"主键ID", 3namevarchar(10)comment"姓名", 4novarchar(10)comment"学号" 5)comment"学生表"; 6 7 8insertintostudent 9values(null,"黛绮丝","2000100101"),10(null,"谢逊","2000100102"),11(null,"殷天正","2000100103"),12(null,"韦一笑","2000100104");

1createtablecourse( 2idintauto_incrementprimarykeycomment"主键ID", 3namevarchar(10)comment"课程名称" 4)comment"课程表"; 5 6insertintocourse 7values(null,"Java"), 8(null,"PHP"), 9(null,"MySQL"),10(null,"Hadoop");

1createtablestudent_course( 2idintauto_incrementcomment"主键"primarykey, 3studentidintnotnullcomment"学生ID", 4courseidintnotnullcomment"课程ID", 5constraintfk_courseidforeignkey(courseid)referencescourse(id), 6constraintfk_studentidforeignkey(studentid)referencesstudent(id) 7)comment"学生课程中间表"; 8 9insertintostudent_course10values(null,1,1),(null,1,2),11(null,1,3),(null,2,2),12(null,2,3),(null,3,4);

3. 一对一(1). 案例：用户与用户详情的关系(2). 关系：一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升操作效率(3). 实现：在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的(UNIQUE)

1createtabletb_user(2idintauto_incrementprimarykeycomment"主键ID",3namevarchar(10)comment"姓名",4ageintcomment"年龄",5genderchar(1)comment"1:男,2:女",phonechar(11)comment"手机号"6)comment"用户基本信息表";

1createtabletb_user_edu( 2idintauto_incrementprimarykeycomment"主键ID", 3degreevarchar(20)comment"学历", 4majorvarchar(50)comment"专业", 5primaryschoolvarchar(50)comment"小学", 6middleschoolvarchar(50)comment"中学", 7universityvarchar(50)comment"大学", 8useridintuniquecomment"用户ID", 9constraintfk_useridforeignkey(userid)referencestb_user(id)10)comment"用户教育信息表";

1insertintotb_user(id,name,age,gender,phone) 2values(null,"黄渤",45,"1","18800001111"), 3(null,"冰冰",35,"2","18800002222"), 4(null,"码云",55,"1","18800008888"), 5(null,"李彦宏",50,"1","18800009999"); 6 7insertintotb_user_edu(id,degree,major,primaryschool,middleschool, 8university,userid) 9values(null,"本科","舞蹈","静安区第一小学","静安区第一中学","北京舞蹈学院",1),10(null,"硕士","表演","朝阳区第一小学","朝阳区第一中学","北京电影学院",2),11(null,"本科","英语","杭州市第一小学","杭州市第一中学","杭州师范大学",3),12(null,"本科","应用数学","阳泉第一小学","阳泉区第一中学","清华大学",4);

二.多表查询概述1. 数据准备(1). 删除之前emp，dept表

1droptableemp;23droptabledept;

(2). 执行如下脚本，创建emp表与dept表并插入测试数据

1--创建dept表，并插入数据 2createtabledept( 3idintauto_incrementcomment"ID"primarykey, 4namevarchar(50)notnullcomment"部门名称" 5)comment"部门表"; 6 7INSERTINTOdept(id,name) 8VALUES(1,"研发部"),(2,"市场部"), 9(3,"财务部"),(4,"销售部"),10(5,"总经办"),(6,"人事部");

1--创建emp表，并插入数据 2createtableemp( 3idintauto_incrementcomment"ID"primarykey, 4namevarchar(50)notnullcomment"姓名", 5ageintcomment"年龄",jobvarchar(20)comment"职位", 6salaryintcomment"薪资", 7entrydatedatecomment"入职时间",manageridintcomment"直属领导ID", 8dept_idintcomment"部门ID" 9)comment"员工表";1011--添加外键12altertableempaddconstraintfk_emp_dept_idforeignkey(dept_id)13referencesdept(id);1415INSERTINTOemp(id,name,age,job,salary,entrydate,managerid,16dept_id)17VALUES(1,"金庸",66,"总裁",20000,"2000-01-01",null,5),18(2,"张无忌",20,"项目经理",12500,"2005-12-05",1,1),19(3,"杨逍",33,"开发",8400,"2000-11-03",2,1),20(4,"韦一笑",48,"开发",11000,"2002-02-05",2,1),21(5,"常遇春",43,"开发",10500,"2004-09-07",3,1),22(6,"小昭",19,"程序员鼓励师",6600,"2004-10-12",2,1),23(7,"灭绝",60,"财务总监",8500,"2002-09-12",1,3),24(8,"周芷若",19,"会计",48000,"2006-06-02",7,3),25(9,"丁敏君",23,"出纳",5250,"2009-05-13",7,3),26(10,"赵敏",20,"市场部总监",12500,"2004-10-12",1,2),27(11,"鹿杖客",56,"职员",3750,"2006-10-03",10,2),28(12,"鹤笔翁",19,"职员",3750,"2007-05-09",10,2),29(13,"方东白",19,"职员",5500,"2009-02-12",10,2),30(14,"张三丰",88,"销售总监",14000,"2004-10-12",1,4),31(15,"俞莲舟",38,"销售",4600,"2004-10-12",14,4),32(16,"宋远桥",40,"销售",4600,"2004-10-12",14,4),33(17,"陈友谅",42,null,2000,"2011-10-12",1,null);

2. 概述

多表查询就是指从多张表中查询数据。

原来查询单表数据，执行的SQL形式为：select * from emp;

那么我们要执行多表查询，就只需要使用逗号分隔多张表即可，如：select * from emp, dept;

具体的执行结果如下：

此时，我们看到查询结果中包含了大量的结果集，总共102条记录，而这其实就是员工表emp的所有记录(17)与部门表dept的所有记录(6)的所有组合情况，这种现象称之为笛卡尔积。

笛卡尔积：笛卡尔积是指在数学中，两个集合，A集合和B集合的所有组合情况。

而在多表查询中，我们是需要消除无效的笛卡尔积的，只保留两张表关联部分的数据。

在SQL语句中，我们可以给多表查询加上连接查询条件即可去除无效的笛卡尔积。

1select*fromemp,deptwhereemp.dept_id=dept.id;

而由于id为17的员工，没有dept_id字段值，所以在多表查询时，根据连接查询的条件并没有查询到。

三. 多表查询分类

名称	子分类	说明
连接查询	内连接	相当于查询A、B交集部分数据
外连接	mysql不原生支持全外连接
左外连接	查询左表所有数据以及两张表交集部分数据
右外连接	查询右表所有数据以及两张表交集部分数据
自连接	当前表与自身的连接查询，自连接必须使用表别名
子查询

1. 内连接

内连接查询的是两张表交集部分的数据（也就是绿色部分的数据）

(1). 隐式内连接

1SELECT字段列表FROM表1,表2WHERE条件...;

案例1：查询每一个员工的姓名 及关联的部门的名称 (隐式内连接实现)

1selectemp.name,dept.namefromemp,deptwhereemp.dept_id=dept.id;23--为每一张表起别名,简化SQL编写4selecte.name,d.namefromempe,deptdwheree.dept_id=d.id;

(2). 显示内连接

1SELECT字段列表FROM表1[INNER]JOIN表2ON连接条件...;

案例1：查询每一个员工的姓名及关联部门的名称（显式内连接）。

1selecte.name,d.namefromempeinnerjoindeptdone.dept_id=d.id;

注：一旦为表起了别名，就不能再使用表明名来指定对应的字段了，此时只能够使用别名来指定字段

2. 外连接

外连接分为两种，分别是左外连接和右外连接（左外连接是蓝色和绿色部分，右外连接是黄色和绿色部分）

(1). 左外连接

1SELECT字段列表FROM表1LEFT[OUTER]JOIN表2ON条件...;

左外连接相当于查询表1(左表)的所有数据，当然也包含表1和表2交集部分的数据。

案例1：查询emp表的所有数据和对对应的部门信息。

1selecte.*,d.namefromempeleftjoindeptdone.dept_id=d.id;

(2). 右外连接

1SELECT字段列表FROM表1RIGHT[OUTER]JOIN表2ON条件...;

右外连接相当于查询表2(右表)的所有数据，当然也包含表1和表2交集部分的数据

案例2：查询dept表的所有数据和对应的员工信息(右外连接)

1selectd.*,e.*fromemperightjoindeptdone.dept_id=d.id;

注：左外连接和右外连接是可以相互替换的，只需要调整在连接查询时SQL中，表结构的先后顺序就可以了，而我们在日常开发使用时，更偏向于左外连接。

3. 自连接(1). 自连接查询

自己连接自己，就是把一张表连接查询多次

1SELECT字段列表FROM表A别名AJOIN表A别名BON条件...;

对于自连接查询，可以是内连接查询，也可以是外连接查询。

案例1：查询员工及其所属领导的名字

1selecta.name,b.namefromempa,empbwherea.managerid=b.id;

案例2：查询所有员工及其领导的名字，如果员工没有领导，也需要查询出来

1selecta.name"员工",b.name"领导"fromempaleftjoinempbon2a.managerid=b.id;

注：在自连接查询中，必须要为表起别名，要不然我们不清楚所指定的条件、返回的字段到底是哪一张表的字段。

(2). 联合查询

对于union查询，就是把多次查询的结果合并起来，形成一个新的查询结果集。

1SELECT字段列表FROM表A...23UNION[ALL]45SELECT字段列表FROM表B....;

对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致。

union all会将全部的数据直接合并在一起，union会对合并之后的数据去重。

案例1：将薪资低于5000的员工和年龄大于50岁的员工全部查询出来（用union）

1select*fromempwheresalary<50002unionall3select*fromempwhereage>50;

union all查询出来的结果仅仅进行了简单的合并，并未去重。

1select*fromempwheresalary<50002union3select*fromempwhereage>50;

union联合查询会对查询出来的结果进行去重处理。

注：如果多条查询语句查询出来的结果，字段数量不一致，在进行union/union all联合查询时，将会报错

4. 子查询

SQL语句中嵌套SELECT语句，称为嵌套查询，又称子查询。

1SELECT*FROMt1WHEREcolumn1=(SELECTcolumn1FROMt2);

子查询外部的语句可以是 INSERT / UPDATE / DELETE / SELECT中的任何一个。

根据查询结果不同：可分为：

A. 标量子查询（子查询结果为单个值）

B. 列子查询（子查询结果为一列）

C. 行子查询（子查询结果为一行）

D. 表子查询（子查询结果为多行多列）

根据子查询位置，可分为：

A. WHERE之后

B. FROM之后

C. SELECT之后

(1). 标量子查询

子查询返回的结果是单个值（数字、字符串、日期等），最简单的形式，这种子查询称为标量子查询。

**常用的操作符：= <> > >= < <= **

案例1：查询”销售部”的所有员工信息

1select*fromempwheredept_id=(selectidfromdeptwherename="销售部");

案例2：查询在”东方白”入职之后的员工信息

1select*fromempwhereentrydate>(selectentrydatefromempwherename="方东白");

(2). 列子查询

子查询返回的结果是一列（可以是多行），这种子查询称为列子查询

常用的操作符：IN、NOT IN、ANY、SOME、ALL

操作符	描述
IN	在指定的集合范围之内，多选一
NOT IN	不在指定的集合范围之内
ANY	子查询返回列表中，有任意一个满足即可
SOME	与ANY等同，使用SOME的地方都可以使用ANY
ALL	子查询返回列表的所有值都必须满足

案例1：查询”销售部”和”市场部”的所有员工信息

1select*fromempwheredept_idin2(selectidfromdeptwherename="销售部"orname="市场部");

案例2：查询比”财务部”所有人工资都高的员工信息

1select*fromempwheresalary>all(selectsalaryfromemp2wheredept_id=(selectidfromdeptwherename="财务部"));

案例3：查询比研发部其中任意一人工资高的员工信息

1select*fromempwheresalary>any(selectsalaryfromemp2wheredept_id=(selectidfromdeptwherename="研发部"));

(3). 行子查询

子查询返回的结果是一行（可以是多列），这种子查询称为行子查询

常用的操作符：=、<>、IN、NOT IN

案例1：查询与”张无忌”的薪资及直属领导相同的员工信息。

1select*fromempwhere(salary,managerid)=(selectsalary,managerid2fromempwherename="张无忌");

(4). 表子查询

子查询返回的结果是多行多列，这种子查询称为表子查询。

常见的操作符：IN

案例1：查询与”鹿杖客”，”宋远桥”的职位和薪资相同的员工信息

1select*fromempwhere(job,salary)in(selectjob,salaryfromemp2wherename="鹿杖客"orname="宋远桥");

案例2：查询入职日期是”2006-01-01”之后的员工信息及其部门信息。

1selecte.*,d.*from(select*fromempwhereentrydate>"2006-01-01")2eleftjoindeptdone.dept_id=d.id;

四. 多表查询案例

准备环境数据：

1createtablesalgrade( 2gradeint,losalint, 3hisalint 4)comment"薪资等级表"; 5 6insertintosalgradevalues(1,0,3000); 7insertintosalgradevalues(2,3001,5000); 8insertintosalgradevalues(3,5001,8000); 9insertintosalgradevalues(4,8001,10000);10insertintosalgradevalues(5,10001,15000);11insertintosalgradevalues(6,15001,20000);12insertintosalgradevalues(7,20001,25000);13insertintosalgradevalues(8,25001,30000);

主要设计三张表：emp员工表，dept部门表，salgrade薪资等级表

1. 查询员工的姓名、年龄、职位、部门信息(隐式内连接)

1selecte.name,e.age,e.job,d.namefromempe,deptd2wheree.dept_id=d.id;

2. 查询年龄小于30岁的员工的姓名、年龄、职位、部门信息（显式内连接）

1selecte.name,e.age,e.job,d.namefromempe2innerjoindeptdone.dept_id=d.idwheree.age<30;

3. 查询拥有员工的部门ID、部门名称

1selectdistinctd.id,d.namefromempe,deptd2wheree.dept_id=d.id;

4.查询所有年龄大于40岁的员工及其归属的部门名称；如果员工没有分配部门，也需要展示出来。

1selecte.*,d.namefromempeleftjoindeptdone.dept_id=d.id2wheree.age>40;

5. 查询所有员工的工资等级

1selecte.*,s.grade,s.losal,s.hisalfromempe,salgrades2wheree.salarybetweens.losalands.hisal;

6. 查询”研发部”所有员工的信息及工资等级

1selecte.*,s.gradefromempe,deptd,salgrades2wheree.dept_id=d.idand(e.salarybetweens.losalands.hisal)andd.name="研发部";

7. 查询”研发部”员工的平均工资

1selectavg(e.salary)fromempe,deptd2wheree.dept_id=d.idandd.name="研发部";

8. 查询工资比”灭绝”高的员工信息

1select*fromempwheresalary>(selectsalaryfromempwherename="灭绝");

9. 查询比平均薪资高的员工信息

1select*fromempwheresalary>(selectavg(salary)fromemp);

10. 查询低于本部门平均工资的员工信息

1select*fromempe2wheree2.salary<(selectavg(e1.salary)from2empe1wheree1.dept_id=e2.dept_id);

11. 查询所有的部门信息，并统计部门的员工人数

1selectd.id,d.name,(selectcount(*)fromempe2wheree.dept_id=d.id)"人数"fromdeptd;

更多mysql学习请关注微信公众号”云哥技术yun3k”，回复”mysql学习”，免费领取mysql全套学习资料。

关键词：