本文主要是整理 mysql 数据库的基础知识点以及主要的 mysql 操作命令。主要内容包括：

1.表间关系

2.连接查询

3.自连接

4.视图

5.事务

6.索引

7.MySQL 性能检测

一、表间关系

根据 E-R模型 来设计表的时候还应考虑表表之间的关系。常见的表间关系包括：一对一；一对多；多对多。

最常用的关系是 一对多，假如出现多对多的关系的时候便应该进行拆分表。

而一些一对一的关系是为了在 表优化 的时候进行，将一对多的表中某些查询次数较多的数据抽取出来单独做为一张表来存储。

注意：关系在设计的时候应保证不要闭合，否则会造成数据冗余。

如何分析表之间的关系：

第一步：表之间是否有关系

第二步：是几对几的关系

第三步：确定在哪个表中建立对应字段，哪个表进行引用

为了保证表之间的关系的准确性，也为了保证表中数据的准确性。可以在表中设置 外键约束：

-- 创建表时设置外键
create table 表名(
    外键名 类型，
    ...
    foreign key(外键名) references 引用表名(引用字段（一般为id）);
);

-- 单独给表添加外键
alter table 表名 add constraint 约束名 foreign key(外键名) references 外键表名(id);

外键的级联操作

有时在删除表中数据时，如果该数据是另一个表的外键，则会抛出异常。（推荐使用逻辑删除，便可以解决这个问题）

解决办法：

1.在创建表的时候指定级联操作

2.创建表后再修改外键的级联操作

外键操作时的级联操作的类型包括：（不建议使用，直接使用逻辑删除，保护重要数据）

· restrict（限制）：默认值，抛出异常

· cascade（级联）：如果主表的记录删除，则从表中相关联的记录都将被删除

· set null：将外键设置为空

· no action：什么都不做

二、连接查询

有时一次的查询需要多个表中的信息，这时就需要使用连接多个表中的数据进行 连接查询。

关键字： join 表名 on 关系(目前不建议使用子查询，而是使用关联查询)

连接查询分为三种：

1.inner join（内连接）：返回所关联表中都能连接上的结果集

2.left join（左连接）：返回符合左边表中数据的结果集,没有左表对应的数据用null填充

3.right join（右连接）：返回符合右边表中数据的结果,没有右表对应的数据用null填充集

-- 先进行连接之后在进行条件查询和分组操作。最后是排序操作，以查询学生成绩信息为例：
select students.name,subjects.name,scores.score
    from scores
    inner join students on scores.stuid=students.id
    inner join subjects on scores.subid=subjects.id;

三、自关联

有时需要设计一些表结构相同的表，并且这些表之间还存在一些相互引入的问题。这种情况下便可以使用 自关联 （自己引用自己）的设计方式来设计表的结构。

可以将这几个表设计为一个表，然后在表中的一个字段引用当前表中的某些字段。

比如：

需要设计数据库来存储省市区的想关信息。省中包含市，市中包含区县。省中具有id和name字段；市中具有id，name和省的id引用字段；区县中具有id，name以及市的id引用。那么便可以将这三个表设计为一张表。为省添加一个pid字段，但可以存储null值。这样在表中的pid字段引用自己就可以将所有的数据保存在一张表中。

create table areas(
    id int auto_increments primary key not null,
    name varchar(20),
    pid int,
    foreign key(pid) references areas(id)
);

自关联结构在现实生活中的应用场景还是挺多的。比如：分类信息的存储便可以使用角标引用来做成 非递归无限级分类算法。

使用自关联的时候应弄清楚逻辑上表所代表的内容和实际上表所代表的内容。

四、视图

有时在对数据库进行相应的查询命令时，由于查询数据过多会导致数据维护比较复杂。另外，数据库中存储的有些数据并不想让别人知道。那么为了保证数据的查询可以更加高效的执行，同时也为了保护数据的安全性，便可以在数据库上定义视图。

view（视图）：是对数据库中的原始数据进行一层封装后的结果，并没有直接操作原始数据，而是对其进行过滤显示，对视图进行的数据操作最后还是操作的原始数据。

定义视图：

-- 习惯上视图的名称前面添加一个 v_
create view 视图名 as 视图生成查询语句;

定义的视图在数据库中也是以表的形式保存在数据库中，可以使用市 show tables 来查看，所以得需要为其设置v开头的姓名以示区分。

修改视图：

alter view 视图名 as 试图生成查询语句;

五、事务

事务：是一系列数据库操作的整体，要么全部执行，要么都不会执行。保证一次数据操作业务逻辑的完整性。

事务的四大特性：（面试重点）

（1）A:（atomicity）原子性，事务中的全部操作在数据库中是不可分割的，要么全部完成，要么均不执行。

（2）C:（consistency）一致性，几个并行执行的事务，其执行结果必须与按某一顺序串行执行的结果相一致。

（3）I:（isolation）隔离性，事务的执行不受其他事务的干扰，事务执行的中间结果对其他事务是透明的。

（4）D:（durability）持久性，对于任意已提交事务，系统必须保证该事务对数据库的改变不会丢失，即使数据库出现故障。

使用要求：表的类型必须是 innodb 或 bdb类型。

-- 查看表的类型
show create table 表名；

-- 修改表的类型
alter table 表名 engine=innodb；

-- 开启事务：
begin
-- 提交：
commit
-- 回滚：
rollback

注意：只有在涉及到数据修改的时候才会用到事务，事务执行是在内存级的临时表上进行的，只有在commit之后才会将临时表的内容写入到原始数据中去。

六、索引

索引 ：是为了加速数据库的检索速度而在数据库的指定字段上添加对应的索引来提高对这些字段的查找速度。 但是建立过多的索引会增加物理开销，所以索引也不应建立很多。

数据库中的主键和外键是一个特殊的索引，所以按照主键来查数据是非常快的。

可以添加索引的列：

· 越小的数据类型通常更好（越小的数据类型通常在磁盘、内存和cpu缓存中都需要更小的空间，处理起来更快）

· 简单的数据类型更好（整形数据比起字符，处理开销更小，因为字符串更加复杂）

· 尽量避免null，因为在mysql中含有空值的列很难进行查询优化

· 建立的索引应该建立在等值操作上，不要建立在范围值上

· 索引中尽量不要使用 or逻辑

索引分为：单列索引和组合索引

单列索引：一个索引只包含单个列，一个表中可以有多个单列索引。 组合索引：一个索引中包含多个列，

-- 查看索引：
show index from 表名

-- 创建索引：（字符串类型的字端需要指定长度，而int bit等不需要）
create index 索引名 on 表名（字段1（长度），字段2（长度），。。。）；

-- 删除索引：
drop index 索引名 on 表名；

七、mysql中的性能检测

-- 开启性能检测（只针对本次连接有效）
set profiling=1;

-- 执行查询语句

-- 查看执行时间
show profiles;

注意：一般超多100ms的话便需要进行数据库优化：