mysql数据库的基础（三）

mysql存储引擎
mysql有不同的存储形式，也就是有不同的存储引擎
MyIsam引擎的数据表会在数据库目录下产生3个文件三个文件分别为：
table.MYD：存放数据
table.MYI：存放索引
table.frm：表的结构
Innodb引擎的数据表会在数据库目录下产生一个table.frm的表结构的文件，所有innodb表的数据会公用一个数据文件ibdata1.
mysql5.5之前数据表的默认引擎为MyIsam，5.5以后的表引擎是Innodb。

字符集和乱码问题
我们的客户端是什么样的字符就告诉mysql服务器是什么样的字符，mysql字符转换器能将客户端字符集转换成服务器字符集。如果客户端的字符集是gbk，但是设置客户端字符集设置的是utf8这时会出现乱码，所以客户端是什么字符集就告诉服务器是什么字符集。
show variables like ‘%character%’

视图
当对某一个结果集查询比较多的时候，我们可以创建一个view，view是一个sql语句的结果集，
create view 视图名称 as 查询语句
如果我们的一张表的某些列不想被人看到，我们可以使用视图
我们也能简化复杂的查询，比如我们的子查询，我们可以把子查询的结果集声明成一个视图。
当我们修改物理表时，视图中的数据也会随之发生变化。
如果视图的每一行数据是与物理表时一一对应的，那么修改视图数据，也会修改物理表数据。
如果视图的每一行数据是由物理表中的多行数据算出来的，（比如视图中存放的是物理表的分组的平均值）那么修改视图数据，也不会修改物理表数据。
视图中的algorithm(算法)
视图存放的原理
视图默认是放在information_schema数据库下的views表里。
创建视图的算法有两种，一种是merge算法，还有一种是temptable
比如：create view v1 as select goods_id,goods_name,goods_price from goods;
select * from v1 order by shop_price desc limit 3
其实上面sql语句的原理是将创建视图的语句存储起来，然后当我们查询时，将创建视图的语句和查询视图的语句拼接起来，这种算法是merge算法如下：
select goods_id,goods_name,goods_price from goods order by shop_price desc limit 3.
创建视图的语句本省比较复杂，很难再和查询视图的语句合并，这时mysql会先执行视图创建语句在视图中形成临时表，查询语句再去查询临时表，有点类似于子查询。
比如：
create view v2 as select count(goods_id),goods_name,avg(goods_market_price - goods_price）as pj from goods;
select goods_name ,pj from v2
这样会形成一个临时表。 当我们执行查询之前，会先执行创建视图的语句，会形成一个临时表，然后我们的查询语句在查临时表

当我们创建视图是可以指定创建视图的算法
create algorithm=merge|temptable view 视图名 as 查询
创建视图时algorithm这个选项可以不写，到时候创建视图，会自己根据创建视图的语句，自动指定算法。
查看所有表和视图
show tables
查看表结构和视图
desc 视图名
查看创建视图过程
show create view 视图名称
查看表的详细信息
show table status where name ="表名|视图" \G
如果是个视图他的comment注释是view。
删除视图
drop view 视图名
索引
数据库的数据归根结底是存储在硬盘上的文件，那我们进行查询，怎么能以最快的速度查到想要的文件。这是我们要使用索引维护我们的数据，索引是高效查询的数据结构，当我们查找数据时首先查找索引，索引会以一定的算法（hash或btree）很快的定位到行数据放在那里。就像数的目录和数的内容一样。
索引的使用提高了查询速度，降低了增删改的速度，所以不是索引添加的越多越好。
加索引的条件：
1 一般表中数据量比较大的时候加索引
2在查询频次比较高的列上加索引
3列中的数据重复度比较低加索引效果更好
索引的分类
主键索引 primary key：一张表只能有一个主键。
唯一索引 unique key：如果某一列的数据要求不能重复，且查询频次比较高时我们可以建立唯一索引。
普通索引 key:如果一列查询频次比较高，我们可以建立一个普通索引。
全文索引 fulltext：在英文环境下，mysql把会把英文单词作为一个一个的分词，当我们搜索单词时我们能用到全文索引，在中文环境下，我们的汉字都是连着的，只有搜索的文字和数据库存放的文字一样时，才能使用上全文索引，这种一般不符合需求，所以全文索引在中文下无效，一般选择使用第三方软件，比如：sphinx或者使用ElasticSearch。
组合索引：将多列的值看成一个整体，然后建立索引，组合索引要遵循最左原则。
https://www.jianshu.com/p/fd781d6e1158
冗余索引：我们可以在同一列上建立多个索引，比如我们在全文索引上在加上普通索引。
建立索引时可以给 某一列的前一部分建立索引。比如email列的前10个字符创建索引，在需要在建立索引时后面加个长度比如：unique key email(email(10))

create table user(
id int(10) primary key auto_increment, 主键索引
name char(5) not null,
email char(20) not null,
firstname varchar(20),
lastname varchar(20),
key name(name), 普通索引
unique key email(email), 唯一索引
key xingming(firstname,lastname) 组合索引
)

索引的操作：
在navcate中我们可以使用图形化工具操作索引
show index from 表 查看表的索引
show create table 表 查看表的结构，也能看到索引。
删除索引：alter table 表 drop index 索引名称。
drop index 索引名 on 表。
explain 查询语句 查看查询语句使用到那个索引了。
事务
比如：我们的转账业务，张三要将钱转给李四，首先；应该执行
update card set money=money-500 where user=张三
update cart set money=money+500 where user=李四
只有上面的两条SQL语句都执行成功时，整个转钱的过程才算执行成功。这个时候我们要用到事务。
事务的使用
1开启事务
start transaction；
2执行sql语句
3要么提交，要么回滚
提交:commit 回滚：rollback()
只有innodb引擎的表才能使用事务。
事务的特性（ACID）
隔离性
隔离性是多个事物的时候, 相互不能干扰。
原子性
我们的sql语句要么都执行成功，要么都执行失败
一致性
一致性关注数据的可见性，中间状态的数据对另一个进程不可见，只有最初状态和最终状态的数据对外可见
持久性
事务一旦执行完毕后，就撤销不回来了。