mysql数据库知识基本总结 - Hzy的博客

原文连接: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
定义完游标后的使用

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

4.1 索引的分类

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

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

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

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

5.mysql增删查改操作

6. 了解sql中的存储过程

6.1 什么是存储过程？

6.1 创建和调用存储过程

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

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

7.2 游标的定义，使用举例

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

9. 了解sql中的事务

9.1 什么是事务

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

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

10. 了解sql中的触发器

10.1 什么是触发器

创建触发器

查看触发器

参考文章

FEATURED TAGS

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