您现在的位置是：网站首页> 编程资料编程资料

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

2023-05-26 503人已围观

简介 MySQL数据库设计概念及多表查询和事物操作_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.掌握左外连接查询格式？

提示：
                    本文由神整理自网络，如有侵权请联系本站删除！
                    

                    本站声明： 

                    1、本站所有资源均来源于互联网，不保证100%完整、不提供任何技术支持； 

                    2、本站所发布的文章以及附件仅限用于学习和研究目的;不得将用于商业或者非法用途；否则由此产生的法律后果，本站概不负责！
                

                

                

                                            
上一篇：mysql数据库自动添加创建时间及更新时间_Mysql_

                                                                
下一篇：详解Mysql数据库平滑扩容解决高并发和大数据量问题_Mysql_

相关内容

点击排行

本栏推荐

猜你喜欢

-六神源码网