mysql数据库知识基本总结

原文连接:Hzy 博客

1.数据库三大范式，解释和举例

1.1 第一范式1NF，要求数据的原子性，也就是不能在分解了,这个很容易理解

• 手机(电话号码(卡1号码，卡2号码))这样就不符合FN1。
• 手机(卡1号码，卡2号码)，这样就符合第一范式。

1.2 第二范式2NF,第二范式是在第一范式的基础上，要求，非主键都要依赖主键，是都不是部分喔.

举个例子，现在我有这样一个表:(学生表)[学号，学生姓名，班级编号，班级信息]

• 可以看到这里，学生姓名是跟学号挂钩的
• 但,班级信息是跟班级钩挂的，而且班级不跟学号挂钩的！
• ,班级,不依赖学号，所以不符合2NF的要求

正确的做法:一对多的关系

• 第一张表：(学生表)[学号,学生姓名，班级编号]
• 第二张表：(班级表)[班级编号，班级信息]

上面的表可能出现的问题：

• 减少数据的记录相同的数据，如果一个班级有多个学生，有多少个学生，我们就要记录几次班级的信息！
• 更新可能导致数据不一致的结果，假如我们更新班级的编号，就必须先找出，所有属于这个班级的学生的行，然后再来改编号，否则，就会出现部分数据不一致的情况。
• 删除操作，假如删除某个班级，可能连带学生的信息也删除了！

1.3 第三范式3NF，是第二范式的子集,就是满足第二范式的条件，还要求，非主键之间不能有传递依赖，第三范式跟第二范式实际有点像。

不符合第三范式，但符合第二范式的例子

• (学生表)[学号，学生姓名，学院编号，学院名称]
• 这里面，一个学生，有姓名，有自己所在的学校编号及名称，非主键都依赖主键，符合第二范式。
• 但不符合第三范式，原因是，学院名称依赖学院编号，具有传递关系，这样是不行的。

正确的做法，把传递关系抽出来，建立一个表，多个多关系，多个学院，多个学生。

• (学生表)[学号，学生姓名]
• (学院表)[学院编号，学院]
• (学院学生表)[学号，学院编号]

参考文章：https://segmentfault.com/a/1190000013695030

2.上面讲三大范式，提到了一对一，一对多，多对多，现在来举个例子了解下。

2.1 一对一，举例：每个人都有自己的身份证。

• 一个人对应一张身份证
• 一张身份证对应一个人
• 表:人[人名,身份证编号]

2.2 一对多，举例：一门语言有多个人都会

一门语言有多个人都会

• 表:人[人名，身份证编号，语言编号]
• 表:语言[语言编号，语言名称]
• 这里：人是多的一方，语言为1,多的一方应该包含1的一方的主键，所以语言编号，应该包含在，人的表中。

反过来，每个人都会多门语言

• 表:人[人名，身份证编号]
• 表:语言[语言编号，语言名称，身份证编号]
• 可以看到，这里表换了个位置，但意思表达就不一样了！每个语言都包含了一个人。
• 多门语言都有同一个身份证编号，就代表，这个身份证编号的人会多门语言啦。

2.3 多对多，我们接着上面来讲，一个人会多门语言，每个人会多门语言，那不就是，多个人会多门语言吗，这就是多对多

• 我们用一个中间表来，记录，人和语言之间的关系。
• 表:人[人名，身份证编号]
• 表:语言[语言编号，语言名称]
• 表:人语言表[身份证编号，语言编号]
• 这里语言编号和身份证编号都是主键。
• 可以通过语言编号，找到会这们语言的所有人。
• 也可以通过身份证编号，找到这个人都会什么语言。

3 讲到了一对一等关系，那肯定也要了解主键啊，外键啊这些东西。

3.1 主键：一行中能够唯一标识这行的字段

• 一个表中只能有一个主键，主键会生成一个唯一索引，并且可以跟外键构成对数据进行约束。
• 注意主键不能为空，但唯一索引是可以为空的，这就是他们两的差别。

3.2 外键：一张表中，通过某个列可以跟另一张表关联起来，这个列就是外键

3.3 知道什么是主键和外键以后，我们通过sql语句来实现写写看

现在我们要创建一个学生表，和一个课程表。

创建一个名为students的表,其中使用primary key设置name为主键

CREATE TABLE STUDENTS(
    name varchar(20),
    sex varchar(10),
    primary key(name)
);

创建一个名为courses的表，其中使用foregin key设置了一个关联students表中的字段。

• 只有在students里有的学生，才能存在课程表中，换句话说就是，课程表中不能有压根不存在的学生。
• 想要删除sutdent表，之前必须先删除课程表！

CREATE TABLE COURSE(
    course_id varchar(20),
    course_name varchar(20),
    student_name varchar(20),
    foregin key(student_name) references STUDENTS(name)
)

3.4 知道什么是主键和外键后，考虑一个问题，如果一个sutdents表中，有学生重名怎么办？这时候复合主键，联合主键就派上用场了。

• 复合主键:主键中包含一个以上的字段,这里通过name,id来确认一个学生

create table students(
    id mediumint auto_increment,
    name varchar(20),
    sex varchar(10),
    primary key(id,name);
)

• 联合主键:联合主键呢其实和复合主键挺像，区别就是主要体现在多个表上。

比如我有一张学生表和一张课程表，那我在建一张学生课程表，我如何避免学生选择相同的课程两次，那我创建一个联合索引(stu_id,courses_id)就能避免这种情况了，跟复合主键不同的地方就在于，stu_id和courses_id都对应了一张表。

通常能一个字段解决的主键就用一个字段，而不是用多个字段来做主键。

4. 了解完主键后，上文提到了索引，那索引是什么呢？我们都知道索引能加快查询速度，那索引是如何加快查询的，有什么优点缺点…

4.1 索引的分类

• 主键索引(primary)：顾名思义，就是主键上用的索引，不能有空值
• 普通索引(index): 这个就是普通索引~~
• 复合索引(index (a,b,c)):就是一个字段以上的索引，支持三种方式索引查询，[a],[a,b],[a,b,c],没错就是从左到右三种组合，不支持[b,c]这样的查找。
• 唯一索引(unique):唯一索引就是索引的那一列中的值不能有相同的，要唯一，所以叫唯一索引！
• 全文索引(fulltext):就是从数据库中筛选中我们想要的搜索结果，类似搜索引擎的功能。

4.2 索引的创建，查看，删除

创建普通索引：create table stu(id int,name varchar(20),index(name));
创建唯一索引：create table stu(id int,name varchar(20),unique index(name));
create index <索引名> on <表名>[字段]
创建：create table stu(id int,name varchar(20),fulltext key name_fulltext(name) );
创建全文索引：create fulltext index <全文索引名> on <表名>[字段名1，字段名2..]
查看索引：show index from <表名> [from<数据库名>]
删除索引：drop index <索引名> on <表名>

其实记住下面这个就行了

CREATE TABLE table_name[col_name data type]
[unique|fulltext|spatial][index|key][index_name](col_name[length])[asc|desc]

4.3 现在知道怎么创建索引啦，那我们什么情况下应该使用索引呢？

加索引的情况

• 当我们经常频繁使用某个字段的时候，我们可以考虑加索引，提高查询速度。
• 在需要order by,group,distinct,where的字段上可以考虑加索引。
• 当一张表，尽可能的不需要修改，只需要查询时，可以加索引，加快查询。
• 简单说就是，对经常需要使用的字段，又发现速度不高的时候，可以考虑加索引。

加索引的优点

• 显而易见的，提高了查询效率。
• 提高表跟表之间的连接。
• 对排序，分组友好，也就是上面说的情况。

加索引的缺点

• 每增加一个索引，索引都会占用一定的空间，表的体积会变大，占用更多的磁盘空间。
• 当进行增加，删除，修改操作时，还需要维护索引的，降低数据库的性能。

4.4 知道了索引的优缺点，顺便记录一些常用的索引优化。

• like参数,以通配符开头:这样数据库进行全盘扫描，而不是使用索引。
• where语句后符合最左前缀的原则:比如，学生表中，学号的索引用的最频繁，那学号索引就排在最左边，名称索引用的少就排后边。
• 查询时，使用学号就会使用索引，速度更快，
• 但使用名称就会全表扫描，速度慢。
• 少用!= 或<>操作符，要不然数据库就会进行全表扫描。
• 索引参与计算类似 select * from balance where money+1>0;就会进行全表扫描。
• 对字段进行null值判断类似 select * from balance where money is null, 解决办法：null可以设置成0，然后判断是否等于0。
• 用or连接条件:用or会进行全表扫描，可以尝试用union all来代替。

4.5 为什么索引能加快查询速度，索引是怎么实现的。

先占位置

5.mysql增删查改操作

6. 了解sql中的存储过程

6.1 什么是存储过程？

存储过程就是把一些常用的比较繁琐的操作，编写成一个函数，然后我们使用的时候，直接调用这个函数就好了，不同写一长串的sql语句.

• 好处：
• 1.减少重复操作
• 2.统一接口

6.1 创建和调用存储过程

创建: create procedure <过程名> [[in|out|inout]参数,类型][标签]: begin <sql语句> end [标签]
举例: 
delimiter //
create procedure test()
label1:begin
select * from students;
end label1 //

• in 类似函数输入的参数
• out 类似函数返回的参数
• inout 传进来的参数也是返回值。

调用：使用call来调用存储过程

调用 call 存储过程名称[参数]
call test();

7. 了解sql中的游标(cusor)

7.1为什么要使用游标，游标是啥玩意。

游标可以帮助我们一行一行的遍历一个表中的内容记录，充当指针的作用，然后我们可以在遍历的过程中进行相应想要的操作！

7.2 游标的定义，使用举例

• 游标定义: declare 游标名称 cursor for <select查询出来的集合>
• 定义完游标后的使用

declare done int default false; 
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;    --防止游标循环溢出。
open 游标名称;
[while (条件) do|循环名称:loop|repeat]
fetch xxx,xxx into 游标名称; --这里是取出游标中的记录
[end while|end loop|until (条件) end repeat]
close 游标名称;

• 游标的使用例子，在存储过程中使用游标,查看某个学生名字出现的次数。

create producre findName(in name1 varchar(20))
begin
    declare stu cursor for select name form students; -- 定义学生表的游标
    declare total int default 0;
    declare done int default false; 
    declare continue handler for not found set done=1;
    open stu;
    repeat
        fetch name in stu;
        if name1 == name
            set total = total +1;
        end if;
    until done end repeat;
    close stu;
    select total;
end

8. 了解sql中的视图(view)

视图可以理解成由select语句结果组合成起来的一张虚拟表。

• 可以对视图进行增删查改操作，但有条件限制。
• 当原先的表中的数据发生改变，视图也会动态改变。

使用视图的好处。

• 视图配合grant，绑定用户权限和视图，可以隐藏一些敏感信息。
• 写短一点的sql语句。

视图的创建，删除，修改

创建视图 create [algorithm=[megre|temptable|undefined]] view 视图名 as select 查询语句;  
修改视图 alter view 视图名 as 新的select查询语句;
删除视图 drop view 视图名;

9. 了解sql中的事务

9.1 什么是事务

事务就是数据库中的一组操作。要保证原子性，要么成功，要么失败回滚，不能只执行了一部分。

事务中有四个特性

• 原子性：就是上面所说的。
• 一致性：就是查看出来的结果是一致的，比如a给b200,一致性就是a-200，b就必定+了200，而不是a-200，b却还没加200.
• 隔离性：不同事务一起操作同一个资源时，进行的隔离级别。
• 持久性:就是能永久的存到数据库中。

9.2 事务隔离级别，以及在并发时可能出现的问题。

• 脏读：两个事务同时操作一个资源时，事务A还没修改完成，事务B就读取了资源并进行处理，这个资源就是"脏数据"
• 幻读：同一个事务，同样的查询条件，前次和后次多或少了字段。强调数据的新增或删除
• 不可重复读：同一个事务，同样的查询条件，前次和后次不一样，强调的是数据的被修改
• 更新丢失：两个事务同时操作一个资源时,先后覆盖，其中一个数据的更新丢失。

数据的隔离级别

• 读取未提交内容:事务间可以看到对方未提交的执行结果,可能出现脏读的问题
• 读取提交内容:事务间可以只能看到对方提交后的执行结果,这样就不会有脏读的问题啦，但可能出现不可重读的问题，第二次跟第一次读取的结果可能不一致，因为可能变其他事务修改过了。同样也可能出现幻读。
• 可重读:可重读解决了同一sql语句前后执行结果不一致的情况，但幻读依然会发生！
• 可串行化:最强的隔离级别，事务中每次读写都加锁，但这样速度就会慢很多，以及一些锁的问题。

数据库中是如何解决这些问题的。

• 加锁
• 使用MVCC,多版本并发控制(行级锁的变种)

9.3 上面中提到了锁，现在就来说说锁。

• 乐观锁:默认不是并发环境，不加锁，先跑，失败后在根据策略处理
• 悲观锁，默认就是并发环境，都上锁！

共享锁(读锁)，排他锁(写锁)

• 共享锁，同一时刻，允许多个连接读取同一资源。
• 排他锁，同一时刻，只能有一个连接可以进行对资源进行写操作。

表锁，行锁

• 表锁：某个连接占用整个表的写锁，导致其他连接这个表的都会阻塞。
• 行锁: 某个连接占用了表中的行的写锁，其他连接这个行的连接会阻塞。

select操作

• select * from table 使用的mvvc，不用加锁
• select * from table lock in share mode; 加了共享锁。
• select * from table for update; 加了排他锁。

insert,update,detele，操作都会加写锁。

• select * from table where ? for update
• insert into table values;
• update table set ? where?;
• detele from table where?;

死锁问题，双方都在等待对方的资源，导致无限等待。

• 可能发生的情况，两个事务中都互相占用了对方接下来准备获得的锁，就会发生死锁。
• 简单的办法，我们让其中给事务设置一个等待超时回滚时间,用·innodb_lock_wait_timeout`来设置超时时间
• 使用wait-for graph算法来主动进行死锁检测，这个不太清楚。

避免死锁的情况

• sql按顺序来访问表和行
• 事务避免太大！

结论就是在可重读级别下，我们通过加锁，可以防止幻读的问题。

疑问1，mvvc是如何实现的？

10. 了解sql中的触发器

10.1 什么是触发器

触发器就是与表有关的数据库对象，满足一定的条件时就会触发，并执行其中的语句！

创建触发器

CREATE
trigger 触发器名称
trigger_time trigger_event
ON tbl_name for each row
　　[trigger_order]
trigger_body

trigger_time: { BEFORE | AFTER } --事件前执行，事件后执行

trigger_event: { INSERT | UPDATE | DELETE } --插入或更新或删除时执行触发器

trigger_order: { FOLLOWS | PRECEDES } 其他触发器名称 
follows,在触发器之后
Precedes,在触发器之前

查看触发器

触发器信息都储存在information_schema数据库下的triggers表,可以使用select来查看。

参考文章

• https://juejin.im/post/5c2c8dace51d455d382ee046
• https://zhuanlan.zhihu.com/p/23624390
• https://zhuanlan.zhihu.com/p/27700617
• https://blog.csdn.net/qq_30546099/article/details/86610107
• https://juejin.im/post/5a9ca0d6518825555c1d1acd
• https://blog.csdn.net/qq_22222499/article/details/79060495