MySQL进阶：约束、多表设计、多表查询与事务

一、前言

当你熟练掌握单表增删改查之后，会发现真实项目开发几乎不会只使用一张数据表。本篇文章依次讲解数据库约束、多表关系设计、多表查询（内连接、外连接、子查询）以及事务ACID四大特性。所有知识点循序渐进，配套实操SQL，学习完成后，你就能具备设计项目数据表、编写复杂查询SQL的能力。

二、数据库约束：概述与分类

1、约束的概念

• 约束是作用于表中列上的规则，用于限制加入表的数据

• 约束的存在保证了数据库中数据的正确性、有效性和完整性

2、约束的分类

注意：旧版本的MySQL不支持检查约束

3、约束案例（讲解前五种约束）

案例需求：

-- 员工表

create table emp(

id int, -- 员工id，主键且自增长

ename varchar(50), -- 员工姓名，非空且唯一

joindate date, -- 入职日期，非空

salary double(7,2), -- 工资，非空

bonus double(7,2) -- 奖金，如果没有奖金默认为0

);

• 主键约束非空且唯一

​​​​​​​

• 非空约束：值不能为空

• 唯一约束：不能重复，值唯一

• 默认约束：不添加值，采取默认值

注意：给null时不是默认值，值就是null

•❗上述我们的id功能还没实现自增长，自增长的实现要采用auto_increment关键字

4、非空约束

4.1 概念

非空约束用于保证列中所有数据不能有null值

4.2 语法

4.2.1 添加约束

-- 创建表时添加非空约束 create table 表名( 列名 数据类型 not null, ... );

-- 建表后添加非空约束 alter table 表名 modify 字段名 数据类型 not null;

4.2.2 删除约束

alter table 表名 modify 字段名 数据类型;

三、外键约束详解

1、概念

• 外键用来关联两张表，分为父表（主表）和子表（从表），保证关联数据的一致性和完整性。举个例子：部门表（父表）和员工表（子表），员工表保存部门id，通过外键约束，不能给员工分配在一个不存在的部门。

2、语法

2.1 添加约束

-- 创建表时添加外键约束 create table 表名( 列名 数据类型， ... [constraint] [外键名称] foreign key(外键列名) references 主表(主表列名) );

-- 建完表后添加外键约束 alter table 表名 add constraint 外键名称 foreign key(外键字段名称) references 主表名称(主表列名称);

2.2 删除约束

alter table 表名 drop foreign key 外键名称;

3、外键约束案例

创建两个表：部门表（主表）、员工表（从表）

此时已经建立物理连接，删除研发部则报错，因为员工表中有研发部的员工，研发部不为空

删除外键：

❗开发提示：很多企业项目不推荐使用物理外键，依靠业务代码维护关联关系，避免外键带来性能问题。

四、数据库多表设计与表关系

1、数据库设计—简介

1.1 软件研发步骤

1.2 数据库设计概念

• 数据库设计就是根据业务系统的具体需求，结合我们所选用的DBMS，为这个业务系统构造出最优的数据存储模型

• 建立数据库中的表结构以及表与表之间的关联关系的过程

• 有哪些表？表里有哪些字段？表和表之间有什么关系？

1.3 数据库设计的步骤

① 需求分析（数据是什么？数据具有哪些属性？数据与属性的特点是什么？）

② 逻辑分析（通过ER图对数据库进行逻辑建模，不需要考虑我们所选用的数据库管理系统）

③ 物理设计（根据数据库自身的特点把逻辑设计转换为物理设计）

④ 维护设计（1.对新的需求进行建表；2.表优化）

2、三种常见表关系

2.1 一对多（最常用）

• 例如：部门和员工

• 一个部门对应多个员工，一个员工对应一个部门

2.2 多对多

• 例如：商品和订单、学生和课程

• 一个商品对应多个订单，一个订单包含多个商品

• 一个学生上多门课程，一门课程包含多个学生

2.3 一对一

• 例如：用户和用户详情

• 一对一关系多用于表拆分，将一个实体中经常使用的字段放一张表，不经常使用的字段放另一张表，用于提升查询性能

3、多表关系实现

3.1 表关系之一对多

• 一对多（多对一）：

• 如：部门表和员工表

• 一个部门对应多个员工，一个员工对应一个部门

• 实现方式：在多的一方建立外键，指向一的一方的主键（讲解外键约束时演示过）

3.2 表关系之多对多

• 多对多：

• 如：订单和商品

• 一个商品对应多个订单，一个订单包含多个商品

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

代码演示：

3.3 表关系之一对一

• 一对一：

• 如：用户和用户详情

• 一对一关系多用于表拆分，将一个实体中经常使用的字段放一张表，不经常使用的字段放另一张表，用于提升查询性能

• 实现方式：在任意一方加入外键，关联另一方主键，并且设置外键为唯一(unique)（类比一对多）

五、多表联合查询

1、什么是笛卡尔积

有 A、B 两个集合，取 A、B 所有的组合情况

多张表直接查询，所有数据无序全部组合，产生大量无效数据。

-- 产生笛卡尔积（错误写法） select * from 表名,表名;

如上，有很多数据都是无效数据，所以我们的核心是添加条件过滤掉无效笛卡尔积数据！！！

多表查询可通过连接查询和子查询来实现，而连接查询又分为内连接和外连接。

2、内连接 inner join ... on

作用：查询两张表能够匹配上的数据（相当于查询A、B交集部分），匹配不到的数据不会显示

-- 隐式内连接 -- select 字段列表 from 表1,表2,... where 条件; select * from staff,department where staff.dep_id=department.id; -- 显式内连接（推荐写法） -- select 字段列表 from 表1 [inner] join 表2 on 条件; select * from staff inner join department on staff.dep_id=department.id;

3、外连接

3.1 左外连接 left join ... on

查询左表全部数据（相当于查询A表所有数据和交集部分数据），匹配不到右表数据，右表字段填充null。

-- select 字段列表 from 表1 left [outer] join 表2 on 条件； select * from staff left outer join department on staff.dep_id=department.id;

查询staff表所有数据和对应的部门信息：

3.2 右外连接 right join ... on

查询右表全部数据（相当于查询B表所有数据和交集部分数据），匹配不到左表数据，左表字段填充null。

-- select 字段列表 from 表1 right [outer] join 表2 on 条件； select * from staff right outer join department on staff.dep_id=department.id;

查询department表所有数据和对应的员工信息：

六、子查询

子查询：一条SQL语句中嵌套另一条select查询语句，嵌套查询结果可以作为条件、临时表使用。

子查询根据查询结果不同，作用不同分为三类子查询。

1、标量子查询（单行单列）

返回单个值（一行一列），可以直接用 = != > < 等条件判断

select 字段列表 from 表 where 字段名 = (子查询);

查询研发部所有员工信息：

2、列子查询（多行单列）

返回一列多行，搭配 in any all 等关键字进行条件判断

select 字段列表 from 表 where 字段名 in (子查询);

查询研发部和销售部所有员工信息：

3、表子查询（多行多列）

返回多行多列，当作虚拟表使用，外层继续关联查询

select 字段列表 from (子查询) where 条件;

查询年龄是18以后（不包括18岁）的员工信息和部门信息：

❗注意：当使用虚拟表时，必须给原始表起别名，否则会报错！！！

七、数据库事务与四大特征ACID

1、事务简介

• 数据库的事务（Transaction）是一种机制、一个操作序列，包含了一组数据库操作命令

• 事务把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库命令要么同时成功，要么同时失败

• 事务是一个不可分割的工作逻辑单元

• 经典场景：转账、A扣款和B收款必须同时成功

2、事务基础操作

-- 开启事务 start transaction; 或者 begin; -- 提交事务 commit; -- 回滚事务 rollback;

转账示例演示：

如果不开启事务，就会发现出错前面成功操作，后面则操作失败

开启事务后则不会出现问题

3、事务的四大特征 ACID

• 原子性（Atomicity）：事务不可分割，要么同时成功，要么同时失败

• 一致性（Consistency）：事务完成时，必须使所有数据都保持一致状态

• 隔离性（Isolation）：多个事务并发执行，互相之间互不干扰

• 持久性（Durability）：事务一旦提交或回滚，修改永久保存到数据库，断电不丢失

4、MySQL默认提交

在MySQL里面，每条SQL语句都是默认提交的。

-- 查询事务的默认提交方式 select @@autocommit; -- 修改事务的提交方式 → 手动提交 set @@autocommit=0;

八、结语

本篇我们完成了MySQL进阶核心内容学习：约束保障数据规范、多表关系教会我们如何设计项目数据表：内连接、外连接、子查询是开发中高频使用的复杂查询语法；事务保证连续数据操作的安全性。掌握本章全部内容后，我们就可以进入JDBC的学习，打通Java后端程序和MySQL数据库的交互，实现Java代码操作数据库。