本篇内容主要讲解“MySQL约束与多表查询实例分析”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“MySQL约束与多表查询实例分析”吧!

概念：约束是作用于表中字段上的规则，用于限制存储在表中的数据。

目的：保证数据库中数据的正确、有效性和完整性。

注意：约束是作用于表中字段上的，可以在创建表/修改表的时候添加约束。

上面我们介绍了数据库中常见的约束，以及约束涉及到的关键字，那这些约束我们到底如何在创建表、

修改表的时候来指定呢，接下来我们就通过一个案例，来演示一下。

案例需求： 根据需求，完成表结构的创建。需求如下：

对应的建表语句为：

CREATETABLEtb_user(idintAUTO_INCREMENTPRIMARYKEYCOMMENT'ID唯一标识',namevarchar(10)NOTNULLUNIQUECOMMENT'姓名',agetinyintunsignedCOMMENT'年龄',statuschar(1)default'1'COMMENT'状态',genderchar(1)COMMENT'性别');

如果你的Mysql是8版本之后 age可以这样创建

ageintcheck(age>0&&age<=120)COMMENT'年龄',

在为字段添加约束时，我们只需要在字段之后加上约束的关键字即可，需要关注其语法。我们执行上面的SQL把表结构创建完成，然后接下来，就可以通过一组数据进行测试，从而验证一下，约束是否可以生效。

insertintotb_user(name,age,status,gender)values('Tom1',19,'1','男'),('Tom2',25,'0','男');insertintotb_user(name,age,status,gender)values('Tom3',19,'1','男');insertintotb_user(name,age,status,gender)values(null,19,'1','男');insertintotb_user(name,age,status,gender)values('Tom3',19,'1','男');insertintotb_user(name,age,status,gender)values('Tom4',80,'1','男');insertintotb_user(name,age,status,gender)values('Tom5',-1,'1','男');insertintotb_user(name,age,status,gender)values('Tom5',121,'1','男');insertintotb_user(name,age,gender)values('Tom5',120,'男');

可见 对于我们设置name的约束： 非空 且唯一 生效。

上面，我们是通过编写SQL语句的形式来完成约束的指定，那加入我们是通过图形化界面来创建表结构时，又该如何来指定约束呢？ 只需要在创建表的时候，根据我们的需要选择对应的约束即可。

外键：用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性。

我们来看一个例子：

左侧的emp表是员工表，里面存储员工的基本信息，包含员工的ID、姓名、年龄、职位、薪资、入职日 期、上级主管ID、部门ID，在员工的信息中存储的是部门的ID dept_id，而这个部门的ID是关联的部门表dept的主键id，那emp表的dept_id就是外键,关联的是另一张表的主键。

注意：目前上述两张表，只是在逻辑上存在这样一层关系；在数据库层面，并未建立外键关联，所以是无法保证数据的一致性和完整性的。

没有数据库外键关联的情况下，能够保证一致性和完整性呢，我们来测试一下。

准备数据

createtabledept(idintauto_incrementcomment'ID'primarykey,namevarchar(50)notnullcomment'部门名称')comment'部门表';INSERTINTOdept(id,name)VALUES(1,'研发部'),(2,'市场部'),(3,'财务部'),(4,'销售部'),(5,'总经办');createtableemp(idintauto_incrementcomment'ID'primarykey,namevarchar(50)notnullcomment'姓名',ageintcomment'年龄',jobvarchar(20)comment'职位',salaryintcomment'薪资',entrydatedatecomment'入职时间',manageridintcomment'直属领导ID',dept_idintcomment'部门ID')comment'员工表';INSERTINTOemp(id,name,age,job,salary,entrydate,managerid,dept_id)VALUES(1,'金庸',66,'总裁',20000,'2000-01-01',null,5),(2,'张无忌',20,'项目经理',12500,'2005-12-05',1,1),(3,'杨逍',33,'开发',8400,'2000-11-03',2,1),(4,'韦一笑',48,'开发',11000,'2002-02-05',2,1),(5,'常遇春',43,'开发',10500,'2004-09-07',3,1),(6,'小昭',19,'程序员鼓励师',6600,'2004-10-12',2,1);

接下来，我们可以做一个测试，删除id为1的部门信息。

结果，我们看到删除成功，而删除成功之后，部门表不存在id为1的部门，而在emp表中还有很多的员工，关联的为id为1的部门，此时就出现了数据的不完整性。 而要想解决这个问题就得通过数据库的外键约束。

1). 添加外键

CREATETABLE表名(字段名数据类型,...[CONSTRAINT][外键名称]FOREIGNKEY(外键字段名)REFERENCES主表(主表列名));

ALTERTABLE表名ADDCONSTRAINT外键名称FOREIGNKEY(外键字段名)REFERENCES主表(主表列名);

案例:

为emp表的dept_id字段添加外键约束,关联dept表的主键id。

altertableempaddconstraintfk_emp_dept_idforeignkey(dept_id)referencesdept(id);

添加了外键约束之后，我们再到dept表(父表)删除id为1的记录，然后看一下会发生什么现象。 此时

将会报错，不能删除或更新父表记录，因为存在外键约束。

2). 删除外键

ALTERTABLE表名DROPFOREIGNKEY外键名称;

案例：

删除emp表的外键fk_emp_dept_id

altertableempdropforeignkeyfk_emp_dept_id;1删除/更新行为

添加了外键之后，再删除父表数据时产生的约束行为，我们就称为删除/更新行为。具体的删除/更新行为有以下几种:

具体语法为:

ALTERTABLE表名ADDCONSTRAINT外键名称FOREIGNKEY(外键字段)REFERENCES主表名(主表字段名)ONUPDATECASCADEONDELETECASCADE;

演示如下：

由于NO ACTION 是默认行为，我们前面语法演示的时候，已经测试过了，就不再演示了，这里我们再

演示其他的两种行为：CASCADE、SET NULL。

1). CASCADE

altertableempaddconstraintfk_emp_dept_idforeignkey(dept_id)referencesdept(id)onupdatecascadeondeletecascade;

A. 修改父表id为1的记录，将id修改为6

我们发现，原来在子表中dept_id值为1的记录，现在也变为6了，这就是cascade级联的效果

在一般的业务系统中，不会修改一张表的主键值。

B. 删除父表id为6的记录

我们发现，父表的数据删除成功了，但是子表中关联的记录也被级联删除了。

2). SET NULL

在进行测试之前，我们先需要删除上面建立的外键 fk_emp_dept_id。然后再通过数据脚本，将 emp、dept表的数据恢复了。

altertableempaddconstraintfk_emp_dept_idforeignkey(dept_id)referencesdept(id)onupdatesetnullondeletesetnull;

接下来，我们删除id为1的数据，看看会发生什么样的现象。

我们发现父表的记录是可以正常的删除的，父表的数据删除之后，再打开子表 emp，我们发现子表emp 的dept_id字段，原来dept_id为1的数据，现在都被置为NULL了

这就是SET NULL这种删除/更新行为的效果。

我们之前在讲解SQL语句的时候，讲解了DQL语句，也就是数据查询语句，但是之前讲解的查询都是单表查询，而本章节我们要学习的则是多表查询操作，主要从以下几个方面进行讲解。

项目开发中，在进行数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结 构，由于业务之间相互关联，所以各个表结构之间也存在着各种联系，基本上分为三种：

一对多(多对一) 多对多 一对一

案例: 部门 与 员工的关系

关系: 一个部门对应多个员工，一个员工对应一个部门

实现: 在多的一方建立外键，指向一的一方的主键

案例: 学生 与 课程的关系

关系: 一个学生可以选修多门课程，一门课程也可以供多个学生选择

实现: 建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

对应的SQL脚本:

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);3.一对一

案例: 用户与 用户详情的关系

关系: 一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升操作效率

实现: 在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的(UNIQUE)

对应的SQL脚本:

createtabletb_user(idintauto_incrementprimarykeycomment'主键ID',namevarchar(10)comment'姓名',ageintcomment'年龄',genderchar(1)comment'1:男,2:女',phonechar(11)comment'手机号')comment'用户基本信息表';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(id,name,age,gender,phone)values(null,'黄渤',45,'1','18800001111'),(null,'冰冰',35,'2','18800002222'),(null,'码云',55,'1','18800008888'),(null,'李彦宏',50,'1','18800009999');insertintotb_user_edu(id,degree,major,primaryschool,middleschool,university,userid)values(null,'本科','舞蹈','静安区第一小学','静安区第一中学','北京舞蹈学院',1),(null,'硕士','表演','朝阳区第一小学','朝阳区第一中学','北京电影学院',2),(null,'本科','英语','杭州市第一小学','杭州市第一中学','杭州师范大学',3),(null,'本科','应用数学','阳泉第一小学','阳泉区第一中学','清华大学',4);3.多表查询概述1.数据准备

1). 删除之前 emp, dept表的测试数据

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

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

dept表共6条记录，emp表共17条记录。

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

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

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

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

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

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

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

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

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

连接查询

内连接：相当于查询A、B交集部分数据

外连接：

左外连接：查询左表所有数据，以及两张表交集部分数据

右外连接：查询右表所有数据，以及两张表交集部分数据

自连接：当前表与自身的连接查询，自连接必须使用表别名

子查询

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

内连接的语法分为两种: 隐式内连接、显式内连接。先来学习一下具体的语法结构。

1). 隐式内连接

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

2). 显式内连接

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

案例:

A. 查询每一个员工的姓名 , 及关联的部门的名称 (隐式内连接实现)

表结构: emp , dept

连接条件: emp.dept_id = dept.id

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

B. 查询每一个员工的姓名 , 及关联的部门的名称 (显式内连接实现) --- INNER JOIN ...

ON ...

表结构: emp , dept

连接条件: emp.dept_id = dept.id

selecte.name,d.namefromempeinnerjoindeptdone.dept_id=d.id;--为每一张表起别名,简化SQL编写selecte.name,d.namefromempejoindeptdone.dept_id=d.id;

表的别名:

①. tablea as 别名1 , tableb as 别名2 ;

②. tablea 别名1 , tableb 别名2 ;

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

外连接分为两种，分别是：左外连接 和 右外连接。具体的语法结构为：

1). 左外连接

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

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

2). 右外连接

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

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

案例:

A. 查询emp表的所有数据, 和对应的部门信息

由于需求中提到，要查询emp的所有数据，所以是不能内连接查询的，需要考虑使用外连接查询。

表结构: emp, dept

连接条件: emp.dept_id = dept.id

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

B. 查询dept表的所有数据, 和对应的员工信息(右外连接)

由于需求中提到，要查询dept表的所有数据，所以是不能内连接查询的，需要考虑使用外连接查询。

表结构: emp, dept

连接条件: emp.dept_id = dept.id

selectd.*,e.*fromemperightouterjoindeptdone.dept_id=d.id;selectd.*,e.*fromdeptdleftouterjoinempeone.dept_id=d.id;

注意事项：

左外连接和右外连接是可以相互替换的，只需要调整在连接查询时SQL中，表结构的先后顺

序就可以了。而我们在日常开发使用时，更偏向于左外连接。

自连接查询，顾名思义，就是自己连接自己，也就是把一张表连接查询多次。我们先来学习一下自连接的查询语法：

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

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

案例：

A. 查询员工 及其 所属领导的名字

表结构: emp

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

B. 查询所有员工 emp 及其领导的名字 emp , 如果员工没有领导, 也需要查询出来

表结构: emp a , emp b

selecta.name'员工',b.name'领导'fromempaleftjoinempbona.managerid=b.id;

注意事项:

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

是哪一张表的字段。

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

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

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

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

案例:

A. 将薪资低于 5000 的员工 , 和 年龄大于 50 岁的员工全部查询出来.

当前对于这个需求，我们可以直接使用多条件查询，使用逻辑运算符 or 连接即可。 那这里呢，我们 也可以通过union/union all来联合查询.

select*fromempwheresalary<5000unionallselect*fromempwhereage>50;

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

select*fromempwheresalary<5000unionselect*fromempwhereage>50;

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

注意：

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

错。如：

1). 概念

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

SELECT*FROMt1WHEREcolumn1=(SELECTcolumn1FROMt2);

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

2). 分类

根据子查询结果不同，分为：

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

B. 列子查询(子查询结果为一列)

C. 行子查询(子查询结果为一行)

D. 表子查询(子查询结果为多行多列)

根据子查询位置，分为：

A. WHERE之后

B. FROM之后

C. SELECT之后

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

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

案例:

A. 查询 "销售部" 的所有员工信息

完成这个需求时，我们可以将需求分解为两步：

查询 "销售部" 部门ID

selectidfromdeptwherename='销售部';

根据 "销售部" 部门ID, 查询员工信息

select*fromempwheredept_id=(selectidfromdeptwherename='销售部');

B. 查询在 "方东白" 入职之后的员工信息

完成这个需求时，我们可以将需求分解为两步：

查询 方东白 的入职日期

selectentrydatefromempwherename='方东白';

查询指定入职日期之后入职的员工信息

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

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

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

案例:

A. 查询 "销售部" 和 "市场部" 的所有员工信息

分解为以下两步:

查询 "销售部" 和 "市场部" 的部门ID

selectidfromdeptwherename='销售部'orname='市场部';

根据部门ID, 查询员工信息

select*fromempwheredept_idin(selectidfromdeptwherename='销售部'orname='市场部');

B. 查询比 财务部 所有人工资都高的员工信息

分解为以下两步:

查询所有 财务部 人员工资

selectsalaryfromempwheredept_id=(selectidfromdeptwherename='财务部');

比 财务部 所有人工资都高的员工信息

select*fromempwheresalary>all(selectsalaryfromempwheredept_id=(selectidfromdeptwherename='财务部'));

C. 查询比研发部其中任意一人工资高的员工信息

分解为以下两步:

查询研发部所有人工资

selectsalaryfromempwheredept_id=(selectidfromdeptwherename='研发部');

比研部其中任意一人工资高的员工信息

select*fromempwheresalary>any(selectsalaryfromempwheredept_id=(selectidfromdeptwherename='研发部'));4.行子查询

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

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

案例:

A. 查询与 "张无忌" 的薪资及直属领导相同的员工信息 ;

这个需求同样可以拆解为两步进行:

查询 "张无忌" 的薪资及直属领导

selectsalary,manageridfromempwherename='张无忌';

查询与 "张无忌" 的薪资及直属领导相同的员工信息 ;

select*fromempwhere(salary,managerid)=(selectsalary,manageridfromempwherename='张无忌');

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

常用的操作符：IN

案例:

A. 查询与 "鹿杖客" , "宋远桥" 的职位和薪资相同的员工信息

分解为两步执行:

查询 "鹿杖客" , "宋远桥" 的职位和薪资

selectjob,salaryfromempwherename='鹿杖客'orname='宋远桥';

查询与 "鹿杖客" , "宋远桥" 的职位和薪资相同的员工信息

select*fromempwhere(job,salary)in(selectjob,salaryfromempwherename='鹿杖客'orname='宋远桥');

B. 查询入职日期是 "2006-01-01" 之后的员工信息 , 及其部门信息

分解为两步执行:

入职日期是 "2006-01-01" 之后的员工信息

select*fromempwhereentrydate>'2006-01-01';

.查询这部分员工, 对应的部门信息;

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

到此，相信大家对“MySQL约束与多表查询实例分析”有了更深的了解，不妨来实际操作一番吧！这里是亿速云网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！