MySQL约束与多表查询实例分析

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

1.约束

概述

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

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

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

约束演示

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

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

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

对应的建表语句为：

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][
外键名称]
FOREIGN
KEY
(
外键字段名
)REFERENCES主表
(
主表列名
));

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

案例:

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

altertableemp
addconstraintfk_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

altertableemp
addconstraintfk_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表的数据恢复了。

altertableemp
addconstraintfk_emp_dept_id
foreignkey(dept_id)referencesdept(id)onupdatesetnullondeletesetnull;

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

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

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

2.多表查询

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

多表关系

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

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

1.一对多

案例: 部门 与 员工的关系

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

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

2.多对多

案例: 学生 与 课程的关系

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

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

对应的SQL脚本:

createtablestudent
(
idintauto_incrementprimarykeycomment'主键ID',
namevarchar(10)comment'姓名',
novarchar(10)comment'学号'
)comment'学生表';
insertintostudent
values(null,'黛绮丝','2000100101'),
(null,'谢逊','2000100102'),
(null,'殷天正','2000100103'),
(null,'韦一笑','2000100104');
createtablecourse
(
idintauto_incrementprimarykeycomment'主键ID',
namevarchar(10)comment'课程名称'
)comment'课程表';
insertintocourse
values(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_course
values(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'员工表';
--添加外键
altertableemp
addconstraintfk_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条记录。

2.概述

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

原来查询单表数据，执行的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字段值，所以在多表查询时，根据连接查询的条件并没有查询到。

4.分类

连接查询

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

外连接：

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

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

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

子查询

1.内连接

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

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

1). 隐式内连接

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

2). 显式内连接

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

案例:

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

表结构: emp , dept

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

selectemp.name,dept.name
fromemp,
dept
whereemp.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.name
fromempe
innerjoindeptdone.dept_id=d.id;
--为每一张表起别名,简化SQL编写
selecte.name,d.namefromempejoindeptdone.dept_id=d.id;

表的别名:

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

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

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

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.name
fromempe
leftouterjoindeptdone.dept_id=d.id;
selecte.*,d.name
fromempe
leftjoindeptdone.dept_id=d.id;

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

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

表结构: emp, dept

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

selectd.*,e.*
fromempe
rightouterjoindeptdone.dept_id=d.id;
selectd.*,e.*
fromdeptd
leftouterjoinempeone.dept_id=d.id;

注意事项：

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

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

3.自连接

1.自连接查询

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

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'领导'
fromempa
leftjoinempbona.managerid=b.id;

注意事项:

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

是哪一张表的字段。

2.联合查询

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

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

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

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

案例:

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

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

select*
fromemp
wheresalary<5000
unionall
select*
fromemp
whereage>50;

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

select*
fromemp
wheresalary<5000
union
select*
fromemp
whereage>50;

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

注意：

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

错。如：

4.子查询

1.概述

1). 概念

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

SELECT*FROMt1WHEREcolumn1=(SELECTcolumn1FROMt2);

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

2). 分类

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

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

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

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

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

根据子查询位置，分为：

A. WHERE之后

B. FROM之后

C. SELECT之后

2.标量子查询

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

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

案例:

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

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

查询 "销售部" 部门ID

selectidfromdeptwherename='销售部';

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

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

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

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

查询 方东白 的入职日期

selectentrydatefromempwherename='方东白';

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

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

3.列子查询

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

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

案例:

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

分解为以下两步:

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

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

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

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

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

分解为以下两步:

查询所有 财务部 人员工资

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

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

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

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

分解为以下两步:

查询研发部所有人工资

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

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

select*
fromemp
wheresalary>any(selectsalaryfromempwheredept_id=(selectidfromdeptwherename='研发部'));

4.行子查询

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

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

案例:

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

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

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

selectsalary,manageridfromempwherename='张无忌';

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

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

5.表子查询

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

常用的操作符：IN

案例:

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

分解为两步执行:

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

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

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

select*
fromemp
where(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')e
leftjoindeptdone.dept_id=d.id;

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