MySQL数据库设计概念及多表查询和事物操作

吾爱主题阅读：180 2024-04-01 23:25:40 评论：0

数据库设计概念

数据库设计简介

1.数据库设计概念

数据库设计就是根据业务系统具体需求，结合我们所选用的DBMS，为这个业务系统构造出最优的数据存储模型。
建立数据库中的表结构以及表与表之间的关联关系的过程。
有哪些表？表里有哪些字段？表和表之间有什么关系？

2.数据库设计步骤

需求分析：数据库是什么？数据具体有哪些属性？数据与属性的特点是什么？
逻辑分析：通过ER图对数据库进行逻辑建模，不需要考虑我们所选用的数据库管理系统。
物理设计：根据数据库自身的特点把逻辑设计转换为物理设计。
维护设计：对新的需求进行建表和对表的优化。

3.表关系简介

在真实的开发中，一个项目中的数据，一般都会保存在同一个数据库中，但是不同的数据需要保存在不同的数据表中。这时不能把所有的数据都保存在同一张表中。
那么在设计保存数据的数据表时，我们就要根据具体的数据进行分析，然后把同一类数据保存在同一张表中，不同的数据进行分表处理。
数据之间必然会有一定的联系，我们把不同的数据保存在不同的数据表中之后，同时还要在数据表中维护这些数据之间的关系。这时就会导致表和表之间必然会有一定的联系。这时要求设计表的人员，就需要考虑不同表之间的具体关系。

在数据库中，表总共存在三种关系，真实的数据表之间的关系：多对多关系、一对多（多对一）、一对一(极少)，(一对一关系就是我们之前学习的Map集合的key-value关系)

表关系（多对多）

1.多对多

如：订单和商品
一个商品对应多个订单，一个订单对应多个商品
实现方式：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

说明：如果两张表是多对多的关系，需要创建第三张表，并在第三张表中增加两列，引入其他两张表的主键作为自己的外键。

2.外键约束

外键用来让两个表的数据之间建立链接，保证数据的一致性和完整性（例如上述多对多中的订单商品表来维护订单表和商品表之间的关系）
使用之间表的目的是维护两表之间多对多的关系：中间表插入的数据，必须在多对多的主表中存在，如果主表的记录在中间表中维护了关系，就不能随意的删除。如果要删除，必须先要删除中间表关联的数据

3.外键约束语法

-- 关键字解释：
constraint: 添加约束，可以不写
foreign key(当前表中的列名): 将某个字段作为外键
references 被引用表名(被引用表的列名) : 外键引用主表的主键

-- 创建表时添加外键约束
CREATE TABLE 表名(
 列名 数据类型,
 …
 [CONSTRAINT] [外键名称] FOREIGN KEY(外键列名) REFERENCES 主表(主表列名) 
); 
-- 建完表后添加外键约束
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称);
-- 删除约束
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

4.创建外键约束

-- 订单表
CREATE TABLE tb_orders
(
  id           int primary key auto_increment,
  payment      double(10, 2),
  payment_type TINYINT, -- 0 微信支付  1 支付宝支付
  status       TINYINT  -- 0 未付款  1 已经支付

);
-- 商品表
CREATE TABLE tb_goods
(
  id    int primary key auto_increment,
  title varchar(100),
  price double(10, 2)
);
-- 订单商品中间表
CREATE TABLE tb_order_goods
(
  id       int primary key auto_increment,
  order_id int, -- 外键，来自于订单表的主键
  goods_id int, -- 外键，来自于商品表的主键
  count    int,  -- 购买商品数量
  foreign key(order_id) references tb_orders(id),
  foreign key(goods_id) references tb_goods(id)
);

5.外键级联

在修改和删除主表的主键时，同时更新或删除从表的外键值，称为级联操作

ON UPDATE CASCADE – 级联更新，主键发生更新时，外键也会更新
ON DELETE CASCADE – 级联删除，主键发生删除时，外键也会删除

6.总结

1.为何要引用外键约束？
让表的数据有效性，正确性。提高查询效率。
2.添加外键约束语法？
constraint 外键约束名 foreign key(当前表的字段名) references 主表(主键)
3.有了外键约束操作数据注意事项?
要求添加数据需要先添加主表，然后添加从表。要求删除数据需要先删除从表，然后再删除主表。

表关系（一对多）

一对多（多对一）

如：部门表和员工表
一个部门对应多个员工，一个员工对应一个部门
实现方式：在多的一方建立外键，指向一的一方的主键

表关系之一对一

一对一

如：用户和用户信息
一对一关系多用于表拆分，将一个实体中经常使用的字段放一张表，不经常使用的字段放另一张表，用于提升查询性能
实现方式：在任意一方加入外键，关联另一方主键，并且设置外键为唯一(UNIQUE)

多表查询

准备数据

-- 价格
create table price
(

  id    int primary key auto_increment,
  price double
);
-- 水果 
create table fruit
(

  id       int primary key auto_increment,
  name     varchar(20) not null,
  price_id int,
  foreign key (price_id) references price (id)
);
-- 数据
insert into price
values (1, 2.30);
insert into price
values (2, 3.50);
insert into price
values (4, null);
insert into fruit
values (1, '苹果', 1);
insert into fruit
values (2, '橘子', 2);
insert into fruit
values (3, '香蕉', null);

笛卡尔积现象

1.什么是笛卡尔积现象

笛卡尔积问题：把多张表放在一起，同时去查询，会得到一个结果，而这结果并不是我们想要的数据，这个结果称为笛卡尔积。
笛卡尔积缺点：查询到的结果冗余了，里面有很多错误的数据，需要过滤。
多表查询语法：select * from 表名1,表名2;

需求：查询两张表中关于水果的信息，要显示水果名称和水果价格

表设计原则：将价格的主键作为水果的外键

-- 多表查询语法(同时查询多个表获取到需要的数据)
select * from 表名1,表名2;
-- 查询价格(我们向查询水果对应的价格,需要将水果表和价格表同时进行查询;)
select * from fruit,price;

查询结果：

2.笛卡尔积产生原因

fruit表中的每一条记录，都和price表中的每一条进行匹配连接。所得到的最终结果是：fruit表中的条目数乘以price表中的数据的条目数。

将fruit表的每行记录和price表的每行记录组合的结果就是笛卡尔积

3.如何避免笛卡尔积

解决上述查询的方案：在查询两张表的同时添加条件进行过滤，比如fruit表的id和必须和price表的id相同

-- 条件过滤笛卡尔积
select * from fruit,price where fruit.price_id=price.id;

内连接查询

1.什么是内连接

内连接查询又称为交集查询，也就是查询只显示满足条件的数据

2.显示内连接

显示内连接：使用INNER JOIN...ON语句，可以省略INNER关键字

-- 语法核心
select * from 表名1 inner join 表名2 on 条件;
-- 或者
select * from 表名1 join 表名2 on 条件;

3.隐式内连接

看不到JOIN关键字，条件使用WHERE指定

select 列名,列名,... from 表名1,表名2 where 表名1.列名=表名2.列名;

4.示例

查询水果的价格

-- 隐式内连接
select * from fruit,price where fruit.price_id=price.id;
-- 显式内连接
select * from fruit inner join price on fruit.price_id=price.id;

查询苹果的信息，显示苹果的id，名字，价格

-- 方式1
select fruit.id, fruit.name, price.price
from fruit,
   price
where fruit.price_id = price.id
and fruit.name = '苹果';
-- 方式2
select fruit.id, fruit.name, price.price
from fruit
       inner join
   price
   on fruit.price_id = price.id
       and fruit.name = '苹果';

5.总结

1.内连接作用？

过滤笛卡尔积
获取两表的交集部分(都满足条件的部分)

2.什么是隐式内连接和显示内连接？

隐式内连接：看不到JOIN：select 列名,列名....from 表名1,表名2 where 表名1.列名=表名2.列名;
显示内连接：看得到JOIN：select * from 表名1 inner join 表名2 on 条件;

3.内连接查询步骤？

1）确定查询几张表
2）确定表连接条件
3）根据需要在操作外连接查询

1.左外连接

左表的记录全部显示出来
外表只会显示符合搜索条件的记录

语法格式：

select * from 表1 left [outer] join 表2 on 条件;

说明：

left关键字左边的表定义为左表，left关键字右边的表定义为右表，查询的内容以左表为主
如果左表有数据，而右表没有数据对应的数据，仍然会把左表数据进行显示
outer关键字可以省略

练习：

不管能否查到水果对应价格，都要把水果显示出来

-- 左外连接查询
select * from fruit left outer join price on fruit.price_id=price.id;

2.右外连接

右表的记录全部表示出来
左表只会显示符合搜索条件的记录

语法格式：

select * from 表名1 right [outer] join 表名2 on 条件;

说明：

right关键字左边的表定义为左表，right关键字右边的表定义为右表，查询的内容以右表为主
如果右表没有数据，而左表没有对应的数据，仍然会把右表数据进行显示
outer关键字可以省略

练习：

不管能否查到价格对应的水果，都要把价格显示出来

select * from fruit right outer join price on fruit.price_id=price.id;

总结：

1.掌握左外连接查询格式？

select * from 表1 left outer join 表2 on 条件;

表1看作为左表，表2看做为右表

2.左外连接查询特点？

在满足要求的基础上保证左表的数据全部显示

3.掌握右外连接查询格式？

select * from 表1 right outer join 表2 on 条件;

4.右外连接查询特点？

在满足要求的基础上,保证右表的数据全部显示

嵌套查询（子查询）

1.什么是子查询

一条查询语句结果作为另一条查询语法一部分。

SELECT 查询字段 FROM 表 WHERE 条件;
举例：
SELECT * FROM employee WHERE salary=(SELECT MAX(salary) FROM employee);

说明：子查询需要放在（）中

三种子查询情况：单行单列、多行单列、多行多列。

2.单行单列

子查询结果是单列，在WHERE后面作为条件

SELECT 查询字段 FROM 表 WHERE 字段=（子查询）;

通常使用比较运算符: > 、>= 、<、<=、=等

3.多行单列

子查询结果是多行单列，结果集类似于一个数组，在WHERE后面作为条件，父查询使用IN运算符

-- IN表示在数值中
SELECT 查询字段 FROM 表 WHERE 字段 IN （子查询）;

4.多行多列

子查询结果是多列，在FROM后面作为表

SELECT 查询字段 FROM （子查询） 表别名 WHERE 条件;

注意：子查询作为表需要取别名，使用as，可以省略，否则这张表没用名称无法访问表中的字段

事务操作

事务的概念

什么是事务

在实际的业务开发中，有些业务操作要多次访问数据库。一个业务要发送多条SQL语句给数据库执行。需要将多次访问数据库的操作视为一个整体来执行，要么所有的SQL语句全部执行成功。如果其中有一条SQL语句失败，就进行事务的回滚，所有的SQL语句全部执行失败。

简而言之，事务指的是逻辑上的一组操作,组成这组操作的各个单元要么全都成功,要么全都失败。

事务作用：保证在一个事务中多次操作数据库表中数据时，要么全都成功,要么全都失败。

事务的应用场景声明

关于事务在实际中的应用场景：

假设我在淘宝买了一部手机，然后当我付完款，钱已经从我的账户中扣除。正当此时，淘宝转账系统宕机了，那么此时淘宝还没有收到钱，而我的账户的钱已经减少了，这样就会导致我作为买家钱已经付过，而卖家还没有收到钱，他们不会发货物给我。这样做显然是不合理。实际生活中是如果淘宝出问题，作为用户的账户中钱是不应该减少的。这样用户就不会损失钱。

还有种情况，就是当我付完款之后，卖家看到我付款成功，然后直接发货了，我如果有权限操作，我可以撤销，这样就会导致我的钱没有减少，但是卖家已经发货，同样这种问题在实际生活中也是不允许出现的。

MySQL中可以有两种方式进行事务的操作：

手动提交事物：先开启，在提交
自动提交事物（默认的）：即执行一条sql语句提交一次事物

数据准备

# 创建账号表
create table account(
	id int primary key auto_increment,
	name varchar(20),
	money double
);
# 初始化数据
insert into account values (null,'a',1000);
insert into account values (null,'b',1000);

手动提交事务

手动提交事务有关的sql语句

SQL语句	描述
start transaction	开启手动控制事物
commit	提交事物
rollback	回滚事物

手动提交事务使用步骤

开启事务–>执行SQL语句–>成功–>提交事务
开启事务–>执行SQL语句–>失败–>回滚事务

演示案例：演示提交事务，a给b转账100元

-- 1.开启事务
start transaction;
-- 2.执行sql语句
update account set money=money-100 where name='a';
update account set money=money+100 where name='b';
-- 3.提交事务
commit;

案例演示：演示回滚事务，a给b转账100元

-- 1.开启事务
start transaction;
-- 2.执行sql语句
update account set money=money-100 where name='a';
update account set money=money+100 where name='b';
-- 3.回滚事务
rollback;

注意：

提交事务(commit) ：事务提交之后，sql语句对数据库产生的操作才会被永久的保存
事务的回滚(rollback)：撤销已经成功执行的sql语句，回到开启事务之前的状态
只要提交事务，那么数据就会长久保存了，就不能回滚事务了。即提交或者回滚事务都是代表结束当前事务的操作

自动提交事务

MySQL的每一条DML(增删改)语句都是一个单独的事务，每条语句都会自动开启一个事务，执行完毕自动提交事务，MySQL默认开始自动提交事务。自动提交，通过修改mysql全局变量autocommit进行控制。

通过以下命令可以查看当前autocommit模式

show variables like '%commit%';

设置自动提交的参数为OFF

set autocommit = 0;  -- 0:OFF  1:ON

案例演示

-- 自动提交事务：每条sql语句就是一个事务，那么执行一条sql语句就会提交一次事务
-- mysql数据库就是自动提交事务
-- a给b转账100元
update account set money=money-100 where name='a';
update account set money=money+100 where name='b';

-- 查看mysql是否自动提交事务
-- autocommit的值是on表示自动提交事务，值是off表示关闭自动提交事务
show variables like '%commit%';
-- 我们可以使用命令临时设置mysql变为手动提交事务，即将自动提交事务关闭
-- 下次重新连接mysql依然是自动提交事务
set autocommit = 0; -- 0 表示关闭自动提交事务 1表示开启自动事务
update account set money=money-100 where name='a'

注意：

1）MySql默认自动提交。即执行一条sql语句提交一次事务。

2）设置autocommit为off状态，只是临时性的，下次重新连接mysql，autocommit依然变为on状态。

3）如果设置autocommit为off状态，那么当我们执行一条sql语句，就不会自动提交事务，重新启动可视化工具，数据并没有改变。

4）如果设置autocommit为on状态，如果我们先执行start transaction 然后在执行修改数据库的语句：

update account set money = money-100 where name='a';
update account set money = money+100 where name='b';

那么此时就表示上述修改数据库的sql语句都在同一个事务中，此时必须手动提交事务，即commit；

换句话说，如果我们手动开启事务start transaction那么此时mysql就不会自动提交事务，必须手动提交事务。

5）如果设置autocommit为on状态，如果我们不执行start transaction 直接执行修改数据库的语句：

update account set money = money-100 where name='a';
update account set money = money+100 where name='b';

那么此时mysql就会自动提交事务，即上述每条sql语句就是一个事务

事务原理和四大特征

事务原理

原理说明

一个用户登录成功以后，服务器会创建一个临时日志文件。日志文件用来保存用户事务状态。
如果没有使用事务，则所有的操作直接写到数据库中，不会使用日志文件。
如果开启事务，将所有的写操作写到日志文件中。
如果这时用户提交了事务，则将日志文件中所有的操作写到数据库中。
如果用户回滚事务，则日志文件会被清空，不会影响到数据库的操作。

事务的四大特征

事务的四大特征（ACID）

数据库的事务必须具备ACID特征，ACID是指Atomicity（原子性）、Consistensy（一致性）、Isolation（隔离性）和Durabiliyt（持久性）

隔离性（Isolation）

多个用户并发的访问数据库时，一个用户的事务不能被其他用户的事物干扰，多个并发的事务之间相互隔离

持久性（Durability）

指一个事务一旦被提交，它对数据库的改变是永久性的，哪怕数据库发生异常，重启之后数据依然会存在

原子性（Atomicity）

指事务包装的一组sql语句（一组业务逻辑）是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生

一致性（Consistency）

一致性是指数据处于一种语义上有意义且正确的状态；

事务一致性是指事务执行的结果必须是使数据从一个一致性状态变到另一个一致性状态。

事务的成功与失败，最终数据库的数据都是符合实际生活的业务逻辑。一致性绝大多数依赖业务逻辑和原子性

事务的并发访问引发的三个问题(面试)

事务在操作时的理想状态：多个事务之间互不影响，如果隔离级别设置不当就可能引发并发访问问题

并发访问的问题	含义
脏读	一个事务读取到了另一个事务中尚未提交的数据。最严重，杜绝发生。
不可重复读	一个事务中两次读取的数据内容不一致，要求的是一个事务中多次读取时数据是不一致的，这是事务update时引发的问题
幻读(虚读)	一个事务内读取到了别的事务插入或者删除的数据，导致前后读取记录行数不同。这是insert或delete时引发的问题

1.脏读：指一个事务读取了另外一个事务未提交的数据。(非常危险)

2.不可重复读：在一个事务内多次读取表中的数据，多次读取的结果不同。

3.幻读（虚读）:一个事务内读取到了别的事务插入或者删除的数据，导致前后读取记录行数不同

4.总结

赃读：一个事务读取另一个事务还没有提交的数据,一定避免。
不可重复读：一个事务读取多次数据内容不一样，主要是update语句。事务已经提交了。可以发生的。
幻读（虚读）：一个事务读取多次数量不一样，主要是delete或者insert语句。事务已经提交了。可以发生的。

事务的隔离级别

通过以上问题演示，我们发现如果不考虑事务的隔离性，会遇到脏读、不可重复读和虚读等问题。所以在数据库中我们要对上述三种问题进行解决。MySQL数据库规范规定了4种隔离级别，分别用于描述两个事务并发的所有情况。

事物隔离级别

上面的级别最低，下面的级别最高。是表示会出现这种问题，否表示不会出现这种问题。

级别	名字	隔离级别	脏读	不可重复读	幻读	数据库默认隔离级别
1	读未提交	read uncommitted	是	是	是
2	读已提交	read committed	否	是	是	Oracle和SQL Server
3	可重复读	repeatable read	否	否	是	MySQL
4	串行化	serializable	否	否	否