MySQL-day2|青训营笔记SQL如何将行转成列首先，假设我们有一张分数表（tb_score），表中的数据如下图：

SQL如何将行转成列

首先，假设我们有一张分数表（tb_score），表中的数据如下图：

然后，我们再来看一下转换之后需要得到的结果，如下图：

可以使用case...when...then...实现，也可以使用if语句实现，下面以case...when...then...语句为例：

select userid,
SUM(CASE subject WHEN '语文' THEN score ELSE 0 END) as '语文',
SUM(CASE subject WHEN '数学' THEN score ELSE 0 END) as '数学',
SUM(CASE subject WHEN '英语' THEN score ELSE 0 END) as '英语',
SUM(CASE subject WHEN '政治' THEN score ELSE 0 END) as '政治'

FROM tb_score
GROUP BY userid

对SQL注入的理解

SQL注入是什么

如果我们的服务端SQL语句是通过字符串拼接生成的，那么将非常容易发生SQL注入。SQL注入是指将SQL代码伪装到输入参数中，传递到服务器解析并执行的一种手段。例如：

如果我们在username 处输入： ' or 1=1 #

select * from user where username='' or 1=1 #' and password='123456'

如果服务端的SQL语句是通过拼接的方式生成的，那么在这个情形下，原本用户名和密码校验的SQL语句就发生了变化。因为1=1是必然成立的，#会忽略其后的SQL语句，因此这个SQL将会查询出所有的用户信息。

如果这样拼接：' or 1=1;delete * from users; # ;之后相当于一条新的SQL语句，这不仅仅会在第一条语句查询出所有用户信息，还会在第二条SQL语句删除掉所有的用户信息。这是一种非常危险的操作。

如何解决SQL注入

主要是采用参数校验和SQL预编译实现防止SQL注入的。

输入的参数会在SQL执行前，进行严格的参数类型校验。输入的参数值只会当成数据进行处理，而不是SQL语句的一部分。

SQL预编译技术的实现原理

首先客户端发送一条带有占位符（通常为？）的SQL语句给服务端。
服务端对该SQL语句的语法进行解析，编译，但不执行，生成一个预编译语句，并将该预编译语句的标识符发给客户端。
当客户端后续需要执行该SQl语句时候，只需要传递该预编译语句的标识符和参数值给服务端即可。

它解决了什么问题：

参数在传入时可以进行严格的参数类型校验。
输入的数据只会作为预编译语句的参数，并不会改变SQL语句的结构，因此从根本上防止了SQL语句的拼接。达到防止SQL注入的问题。

如何将一张表的部分数据更新到另外一张表

例如：需要将a的某一列更新到b表中

可以采用关联更新的方式

update b set b.col = a.col from a,b where a.id = b.id;
update b set b.col = a.col from b inner join a  on a.id = b.id;
update b set b.col = a.col from b left join a  on a.id = b.id;

where和Having有什么区别

where和Having通常都是用来筛选的，但是：

where的筛选操作在分组前进行，Having的筛选操作在分组后进行。
where筛选行数据，Having筛选的是分组数据。也就是说where决定展示哪些行出来，Having决定展示哪些分组出来。
where可以使用除了聚合函数之外的任何字段进行过滤，而Having只能使用分组字段和聚合函数进行过滤。

说一说你对索引的理解

索引是数据库的物理结构，它存储在磁盘上，与数据表分离存储。索引是在存储引擎中实现的，不同改的存储引擎支持的索引类型也不一样，比如常见的InnoDB和MyIsam存储引擎支持BTREE索引，而MEMORY支持BTREE索引和HASH索引。

索引是受到局部性原理的启发提出的，索引的页机制很好地运用了局部性原理。局部性原理包括时间局部性和空间局部性，时间局部性是指，如果一个数据在当前被访问，那么在接下来的一段时间里，它被再次访问的概率大于其他未被访问过的数据。空间局部性是指，如果一个数据在当前被访问，那么和他相邻的指令在接下来的时间里被访问的概率将会比较高。

索引的优缺点

索引的优点：

降低数据库的IO次数，是索引的优点的最大体现。
通过唯一索引，也可以确保数据的唯一性。
索引可以加速表与表之间的连接。当主表与从表联合查询的时候，索引可以加快查询速度。
在分组、排序、过滤等操作时，减少查询中分组、排序、过滤的时间，减少对CPU的消耗。

索引具有很多优点但索引也具有局限性，也存在缺点：

创建和维护索引需要耗费时间和空间。随着数据量的增加耗费的时间和增加也会增加。
索引虽然大大提高了查询速度，但是在进行增删改的时候，由于索引需要动态地去维护，因此也会相应地耗费更多的时间。
索引需要占用磁盘空间，除了数据表需要占用磁盘空间以外，每个索引都需要占用一定的物理磁盘空间，因此，如果存在大量的索引，就有可能使索引文件比表文件更快地达到最大文件尺寸。

索引的实现原理

聚簇索引

InnoDB的BTREE索引中的聚簇索引

InnoDB的BTREE索引中的聚簇索引：

它充分利用了局部性原理，采用的用页加载的机制每次IO加载到内存中的数据，都是以页为单位的，并且驻留一段时间。行数据根据主键进行排序，并将行数据按主键顺序存储在页中，每个页的大小为16KB，当该页存满，会开辟一个新的页进行存储。页和页之间以双向链表的形式进行相连。在页的内部，行数据之间以单向链表的方式相连。
因为存储行数据的页不止一个，因此如何找到存储这个行数据的页很关键，因此开辟一些目录项页来记录数据页的目录项。目录项页的页内存储的是数据页中的起始主键值和对应的页号，当我们想要知道目标数据行存在于哪一个数据页中时，只需要对目录项页进行二分查找即可，时间复杂度为O(log(N))。
当目录项页出现了多个，同理，则在其上继续建立目录项记录页，作为目录项页的目录项页，来减少目录项页的IO次数。

非聚簇索引

InnoDB中的BTREE索引中的聚簇索引

InnoDB的BREE索引中的非聚簇索引仍然根据索引列的值进行排序，和上述索引的区别在于，叶子节点（数据页）中存储的是索引列值和主键值，其它均一致。

这也就意味着，当使用InnoDB的BREE索引中的非聚簇索引进行查询时，得到的将会是主键值。然后根据这个主键值，来到聚簇索引中进行回表查询。因此InnoDB的BREE索引中的非聚簇索引也叫做二级索引。

MyIsam中的BTREE索引中的聚簇索引

InnoDB的BREE索引中的非聚簇索引仍然根据索引列的值进行排序，和上述索引的区别在于，叶子节点（数据页）中存储的是索引列值和数据物理地址，其它均一致。MyIsam的索引和表文件是分开存放的，MyIsam中的索引都是非聚簇索引，无法直接查询到行数据，只能查询到行数据对应的物理地址（偏移量）。然后根据这个偏移量在表文件中进行回表操作，得到行数据。

联合索引

根据索引列的值依次进行排序，例如有两个索引列:c1,c2，则先根据索引列c1进行排序，当c1相同再根据c2进行排序。叶子节点（数据页）中存储的是索引列值和主键值，其它均一致。

它的底层实现原理决定了当使用联合索引必须要遵循最左前缀原则，否则索引会失效。

索引的注意事项

根的位置万年不动，即便索引的结构发生变化，但是根页面的页号作为该索引的入口，始终不变。
内节点目录项的唯一性。如图当以c2建立索引，如果只以索引列值和页号构建目录项，容易因为索引列值相等而产生目录项的冲突。在构建索引的时候，将主键值也考虑进去，就可以很好的避免这个问题。

3.一个页面中最少存储两条数据。InnoDB为了优化存储效率，对数据行的大小加以限制，使得每个16KB大小的页面最少可以存储两条数据。

MySQL索引的分类

普通索引、唯一索引、主键索引、单列索引、组合索引、全文索引、空间索引。

唯一索引要求索引列的值必须唯一。
主键索引要求索引列的值非空且唯一。
组合索引只在遵循最左前缀原则时生效。
全文索引类型为FULLTEXT，普通索引为精确匹配，全文索引为词语匹配。全文索引不仅仅会存储该值，还会存储该值相关的所有词语。这使得全文索引将会占用更多的存储空间。同时全文索引更适用于模糊查询，在普通查询上性能低于普通索引。
空间索引在GIS应用开发时会有涉及。
空间索引和全文索引MyIsam支持，InnoDB不支持。

MyIsam和InnoDB索引的区别

InnoDB表一定存在聚簇索引，有主键时，主键索引就是聚簇索引，没有主键时，会用一个唯一且不为空的索引列做为主键，成为此表的聚簇索引，如果不存在这种字段，则自动生成一个隐含主键，类型为长整型，长度为6字节，并成为此表的聚簇索引。而MyIsam不存在聚簇索引。
InnoDB的聚簇索引叶子结点data域存储的是数据行，InnoDB的非聚簇索引叶子结点data域存储的是主键的值，需要通过聚簇索引回表查询才能得到数据行。而MyIsam只有非聚簇索引，MyIsam非聚簇索引的data域存储的是数据行的物理地址（偏移量），然后通过这个偏移量在数据表中回表查询得到数据行。
MyIsam的回表是非常迅速的，因为只需要查询数据表中对应的偏移量的数据行即可。而InnoDB需要通过聚簇索引进行一次回表查询（往往伴随多次IO），虽然时间也不长，但是其速度也会慢于MyIsam的根据偏移量直接查询目标物理地址的速度。
MyIsam的索引和数据文件是分离的，而InnoDB存储引擎的数据文件就是聚簇索引本身。

索引的代价

时间上的代价

索引虽然会大大加速我们的查询速度。但是索引的建立、索引的维护，也会占据较多的时间。 B+树的每层，都是按照索引列的值，从小到大排列成的双向链表。同时无论是叶子结点还是内节点中的记录，也就是说，不管是数据行记录、还是目录项记录，他们也都是按照索引列的值从小到大进行排列的。当我们对数据行记录进行增删改的操作时，将会有很大的几率引起记录移位、页面分裂、页面回收等操作，来维护记录和节点的顺序性。这些维护操作将会产生较大的时间开销，给性能拖后腿。

空间上的代价

每建立一个索引都需要去维护一个B+树，而B+树的每一个叶子节点都会对应16KB大小的存储空间。一棵很大的B+树需要很多叶子结点，那将会占用很大的一片存储空间。若一张表具有多个占用存储空间较大的索引，则其索引文件有可能会先于数据文件超过最大文件尺寸。

如何判断是否要添加索引（如何评估一个索引的创建是否合理）

当唯一性是某种数据本身的特征时，指定唯一索引，使用唯一索引在保证数据完整性的同时，也加快了查询速度。
在进行频繁排序、分组、范围查询的时候，应该考虑添加索引。因为索引MySQL的BTREE索引是有序的，并且是根据局部性原理提出的，采用页加载的机制，数值相等或相近的数据行往往会存在于一个页里，会被一同加载到内存中并且驻留一段时间，这种机制有助于减少磁盘的IO次数，优化分组、和范围查询的效率。
对于经常更新的表，索引的数量要尽可能的少。应该对经常查询的字段添加索引，避免对不必要的字段添加索引。
对于数据量比较小的表尽量不要添加索引，有可能并不会产生优化效果。一方面因为数据量比较小，直接遍历表和便利索引的速度差异不大，另一方面是创建了索引以后，在进行增删改操作时，维护索引的顺序性还要占用一定的性能。这种不应该建立索引。
针对选择性非常差的字段，比如性别：只有男女两种取值。查询效率的提升并不明显，还会造成空间上的浪费，并且维护成本较高，这种也不应该建立索引。
where条件中用不到的字段不应该建立索引。
参与列计算的列不适合建立索引。

如何判断你添加的索引在查询时候有没有生效

可以使用EXPLAIN语句进行查看

EXPLAIN SELECT * FROM comments WHERE id = 9;

possible_keys为可以选择使用的索引，key为本次查询使用的索引。在possible_key和key中都出现了主键，说明本次查询实际使用的索引是主键索引。

MySQL-day2|青训营笔记