数据库设计与优化-优快云博客

本文链接：https://blog.youkuaiyun.com/Bug_marker/article/details/122025547

1、数据库的多表设计

一个项目中的数据，一般都会保存在同一个数据库中，但是不能把所有类型的数据都保存在同一张数据表中，不同类型的数据需要保存在不同的数据表中。

数据表之间的关系：多对多关系，一对多（多对一）、一对一

外键：在一个表中去引用另外一张表的主键作为该表的字段，这个字段称为外键，一旦有了外键，我们就可以理解为表和表之间产生了外键约束。
作用：减少数据的冗余，维护多表之间的数据的完整性，减少垃圾数据。
主表：主键被应用的表。
从表：存在外键的表。

#表已经存在添加外键
alter table 当前表单 add foreign key（当前表的列） references 被引用表名（引用表的列）；

#创建表时添加外键
foreign key（当前表中的列表） references 被引用表名（被引用表的列名）；

1、从从表中添加数据
#这里的值1为从表的主键，值3为主表的主键（从表的外键）这个值必须存在
insert into 表名 values(值1，值2，值3);
如果添加的外键的值在主表中是不存在的，则无法添加成功，要先添加主表数据，在添加从表数据。

2、给从表表中更新数据
#从表外键更新的数据值，必须是主表存在的数据
update 表名 set 列名= 值 where 条件语句
如果更新的外键的值在主表中是不存在的，则无法更新成功，要先更新主表数据，在更新从表数据。

3、给从表表中删除数据
#主表中的主键正在被从表的外键引用，无法删除，需要先删除从表中引用的所有数据后才可以删除
delete from 从表 where 条件语句。
如果从表的外键还在引用着主表的数据，则主表该数据不能被删除，要先删除从表中所有引用主表的数据后，才可以删除主表的数据。

2、级联操作

级联操作：指的是通过操作主表的数据，从而影响到从表中的数据。因为级联操作存在很大的风险，所以设置外键的时候，默认不级联操作。
级联更新：外键约束语句 on update cascade;
级联删除：外键约束语句 on delete caseade;
#设置级联操作
alter table 从表 add foreign key(did) references 主表(id) on update cascade on delete cascade
删除了主表中的数据之后，从表中和主表关联的所有数据都会被珊瑚，这样操作的风险过高，一般开发中几乎不会使用。

3、数据库设计三大范式

范式设计就是指设计原则。

第一范式：要求表的每个字段必须是不可分割的独立单元。
第二范式：在第一范式的基础上，要求每张表只表达一个意思，表的每个字段和表的主键有依赖。
第三范式：在第二范式的基础上，要求每张表的主键之外的其它字段只能和主键有直接决定性依赖关系，这里讲的就是外键的使用。

4、多表查询

#A表保存的水果名称
create table A(
    A_id int primary key auto_increment,
    A_name varchar(20) not null
);
insert into A values(1,"苹果");
insert into A values(2,"橘子");
insert into A values(3,"香蕉");

#B表保存的水果的名称
create table B(
    B_id int primary key auto_increment,
    B_price double
);
insert into B values(1,2.30);
insert into B values(2,3.50);
insert into B values(4,null);

笛卡尔积：

是指在数学中，两个集合A和B的笛卡尔积，又称直积，表示为A X B,第一个对象是A的成员而第二个对象是 B 的所有可能有序对的其中一个成员。

简单的说就是两个集合相乘的结果。假设集合A={a, b}，集合B={0, 1, 2}，则两个集合的笛卡尔积为{(a, 0), (a, 1), (a, 2), (b, 0), (b, 1), (b, 2)}。

笛卡尔积问题：

笛卡尔积的数据，对程序是没有意义的，我们需要对笛卡尔积中的数据再次进行过滤。对于多表查询操作，需要过滤出满足条件的数据，需要把多个表进行连接，连接之后需要加上过滤的条件。

过滤条件：

内连接查询
外连接查询：左外连接、右外连接、全（外）连接、自连接

查询所有水果的价格：

#语法一
select * from A,B where a.a_id = b.b_id;

#语法二，使用内连接
select * from a inner join b on a.a_id = b.b_id;

#左外连接：用左边表去右边表中查询对应记录，不管是否找到，都将显示左边表中全部记录。
select * from a left outer join b on a.a_id = b.b_id;

#右外连接：用右边表去左边表查询对应记录，不管是否找到，右边表全部记录都将显示
select * from a right outer join b on a.a_id = b.b_id;

#全外连接：左外连接和右外连接的结果合并，但是会去掉重复的记录
#mysql数据库不支持此语法select * from a full outer join b on a.a_id = b.b_id;
select * from a left outer join b on a.a_id = b.b_id 
union
select * from a right outer join b on a.a_id = b.b_id;

5、SQL关联子查询。

子查询：把一个SQL语句的查询结果作为另外一个SQL语句查询的参数存在。

#要求：查询价格最贵的水果名称-->数据表A水果表，数据表B价格表。

#B表中查询出最高价格
select max(b_price) from b;
#通过最高价格，找到对应的id
select b_id from b where b_price = (select max(b_price)from b);
#通过水果的id，找到水果表中的水果名称
select * from a where a_id=(select b_id from b where b_price = (select max(b_price) from b);

6、索引

索引：

在MySQL中也叫做“键”，是存储引擎用于快速找到记录的一种数据结构。
索引优化应该是对查询性能优化最有效的手段，能够轻易将查询性能提高好几个数量级。
索引相当于字典的音序表，如果要查某个子，如果不使用音序表，则需要从几百页中逐页去查。
索引的作用：提高查询速度。索引的类型：单列索引和组合索引

6.1索引语法

建表的时候创建索引：

index（字段名称）#普通索引字段的值可以重复
unique index（字段名称） #唯一索引这个字段的值不能重复

#准备数据
create table person(
    id int primary key auto_increment,
    name varchar(30) not null,
    age int,
    index(name)
);
insert into person values(null,'熊大',3);
insert into person values(null,'张三',2);
insert into person values(null,'李四',200);
insert into person values(null,'王五',5);
insert into person values(null,'赵六',6);

# 如果此时没有给name字段设置索引，那么会从表中的每一个字段的每一个数据中进行查找效率低
# 如果此给name字段设置索引，那么只会从name字段中进行查找
select * from person where name like '熊%';
select * from person where age > 2;#只有根据进行查询的时候，索引才会生效

组合索引：

index（字段1，字段2...）

组合索引的使用：组合索引存在最左原则，左侧的字段1如果在查询的时候使用则是有效索引，如果左侧的字段1没有使用，只是使用了字段2，那么索引也不会有效果。
select * from person where name like '熊%';
select * from person where sname like '熊%' and age = 2;

表创建后创建索引