MySQL数据库设计概念及多表查询和事物操作-木庄网络博客

本文整理自网络，侵删。

数据库设计概念

数据库设计简介

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.什么是子查询

阅读剩余部分

数据库系统概念第6版

￥41.1元 机械工业出版社

本书主要讲述了数据模型、基于对象的数据库和XML、数据存储和查询、事务管理、体系结构等方面的内容。

转载请注明出处：木庄网络博客 » MySQL数据库设计概念及多表查询和事物操作

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

目录