一、前言当你熟练掌握单表增删改查之后会发现真实项目开发几乎不会只使用一张数据表。本篇文章依次讲解数据库约束、多表关系设计、多表查询内连接、外连接、子查询以及事务ACID四大特性。所有知识点循序渐进配套实操SQL学习完成后你就能具备设计项目数据表、编写复杂查询SQL的能力。二、数据库约束概述与分类1、约束的概念• 约束是作用于表中列上的规则用于限制加入表的数据• 约束的存在保证了数据库中数据的正确性、有效性和完整性2、约束的分类约束名称描述关键字非空约束保证列中所有数据不能有null值NOT NULL唯一约束保证列中所有数据各不相同UNIQUE主键约束主键是一行数据的唯一标识要求非空且唯一PRIMARY KEY检查约束保证列中的值满足某一条件CHECK默认约束保存数据时未指定值则采取默认值DEFAULT外键约束外键用来让两个表的数据之间建立链接保证数据的一致性和完整性FOREUGN KEY注意旧版本的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_iddepartment.id; -- 显式内连接推荐写法 -- select 字段列表 from 表1 [inner] join 表2 on 条件; select * from staff inner join department on staff.dep_iddepartment.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_iddepartment.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_iddepartment.id;查询department表所有数据和对应的员工信息六、子查询子查询一条SQL语句中嵌套另一条select查询语句嵌套查询结果可以作为条件、临时表使用。子查询根据查询结果不同作用不同分为三类子查询。1、标量子查询单行单列返回单个值一行一列可以直接用 ! 等条件判断select 字段列表 from 表 where 字段名 (子查询);查询研发部所有员工信息2、列子查询多行单列返回一列多行搭配 in any all 等关键字进行条件判断select 字段列表 from 表 where 字段名 in (子查询);查询研发部和销售部所有员工信息3、表子查询多行多列返回多行多列当作虚拟表使用外层继续关联查询select 字段列表 from (子查询) where 条件;查询年龄是18以后不包括18岁的员工信息和部门信息❗注意当使用虚拟表时必须给原始表起别名否则会报错七、数据库事务与四大特征ACID1、事务简介• 数据库的事务Transaction是一种机制、一个操作序列包含了一组数据库操作命令• 事务把所有的命令作为一个整体一起向系统提交或撤销操作请求即这一组数据库命令要么同时成功要么同时失败• 事务是一个不可分割的工作逻辑单元• 经典场景转账、A扣款和B收款必须同时成功2、事务基础操作-- 开启事务 start transaction; 或者 begin; -- 提交事务 commit; -- 回滚事务 rollback;转账示例演示如果不开启事务就会发现出错前面成功操作后面则操作失败开启事务后则不会出现问题3、事务的四大特征 ACID• 原子性Atomicity事务不可分割要么同时成功要么同时失败• 一致性Consistency事务完成时必须使所有数据都保持一致状态• 隔离性Isolation多个事务并发执行互相之间互不干扰• 持久性Durability事务一旦提交或回滚修改永久保存到数据库断电不丢失4、MySQL默认提交在MySQL里面每条SQL语句都是默认提交的。-- 查询事务的默认提交方式 select autocommit; -- 修改事务的提交方式 → 手动提交 set autocommit0;八、结语本篇我们完成了MySQL进阶核心内容学习约束保障数据规范、多表关系教会我们如何设计项目数据表内连接、外连接、子查询是开发中高频使用的复杂查询语法事务保证连续数据操作的安全性。掌握本章全部内容后我们就可以进入JDBC的学习打通Java后端程序和MySQL数据库的交互实现Java代码操作数据库。