[数据库][mysql] 知识拾遗

这里记录一些没有整理的数据库和mysql相关知识。

1.Mysql常见存储引擎

1）InnoDB存储引擎

　　为表提供事务处理、回滚、崩溃修复和多版本并发控制功能（或机制）。支持自动增长键值（AUTO_INCREMENT）、以及外键。

InnoDB创建的表存储在frm文件中。优点是良好的事务处理、崩溃修复、并发控制。缺点是读写效率差，占用的空间大。

2）MyISAM存储引擎

　　将表保存为三个文件：frm（存储表结构）、MYD（Data，存储数据）、MYI（Index，存储索引）。保存的数据支持静态型、动态型（可变长）、压缩型。

3）MEMORY存储引擎

　　数据文件保存在内存中，而仅在磁盘上保存表的结构（frm文件）。默认使用hash索引，因此能比B树和B+树更快。

2.Mysql操作类型

1）DDL操作

　　即操作数据库或者操操作数据库表。

　　a.操作数据库： create 和 drop；

　　b.操作数据库表：

　　添加字段

　　　　alter table tablename add 字段名 类型（长度）[first //添加到第一列][after 字段名 //添加到指定字段后];

　　删除字段

　　　　alter table tablename drop 字段名;

　　修改字段（名称、类型、长度、约束、描述等）

　　　　alter table tablename modify 字段名 新类型 新约束;

　　　　alter tabe tablename change 旧字段名 新字段名 新类型 新约束;

　　修改表名

　　　　rename table tablename to newname;

　　删除数据库

　　　　drop table tablename;

2）DML操作

　　即操作数据库表内记录值的操作。

　　1）插入 insert into tablename[(t1, t2, ...)] values(val1, val2, ...); 可以批量插入，并且批量插入时可以减少IO操作，提高效率。

　　2）删除 delete from tablename [where condition];

　　3)  修改 update tablename set 字段名 = 值, ..., [where condition];

3）DQL操作

　　即查询数据表的操作。

　　select 字段(或者*) from tablename [where condition] ;

　　查询辅助字段：

　　inner join ...（内联目标） on ...（） 内联外键，效果是将两张表（通过外键关联）拼接并进行查询。其他的连接还有左连接和右连接。

　　like 模糊查询，即进行字段正则匹配。

　　group by 分组后分开处理（一般用于返回聚合函数统计值，如SUM、COUNT、AVG、MAX、MIN）。

　　having 筛选满足特定字段条件的记录（行）。

　　order by 字段 desc; 按照某一字段降序排序。

　　limit 索引，页容量; 限制返回的结果数量。配合order可以查询最后数条记录。

3）DCL操作

　　即管理数据库系统中的对象权限。包括创建/修改用户、控制权限转移等。

　　常见的是grant（给予用户权限） 、 revoke（撤销用户权限）和show（查看用户、权限、密码套件等） 命令。

　　例：

　　查看权限

　　show grants for ‘username’@’host’;

　　添加所有数据库所有表的所有权限（并允许传递）：

　　gtant all privileges on *.* to ‘username’@’host’ with grant option;

　　删除权限：

　　revoke all privileges on *.* from 'username'@'host';

　　添加完后刷新属性：

　　Flush privileges;

3. 事务

　　Mysql事务是将一组sql语句放在同一个批次内执行。如果某一个sql语句错误，则取消该批次所有语句的执行。

1）ACID性质

　　A：原子性，事务中的sql语句被当做整体执行，执行时要么全部成功，要么全部失败。

　　C：一致性，确保事务执行后要么成功（数据变成提交后的状态），要么失败（回到提交前的状态），不会因为事务的执行产生其他状态。

　　I：隔离性，即事务处理时，事务间相互不影响，彼此独立透明。

　　D：持久性，事务一旦提交成功，对数据的影响是永久的。

2）确保ACID的机制

　　A：通过undo log记录回滚操作需要的信息（未提交事务的操作记录），当执行失败或调用rollback后，会利用undo log进行数据回滚。

　　C：数据库层面保证（即undo log和redo log的回滚机制）以及两阶段事务提交确保持久化时的一致性（第一阶段：写入redo log，第二阶段：写入binglog，更改redo log状态为commit）。

　　I：写操作使用锁机制进行隔离，读写操作依靠MVCC机制实现。

　　D：事务提交后，数据会被保存到磁盘上。不过若频繁进行读写操作可能导致IO拥塞，因此数据会先被写入buffer pool，然后被定期地更新到磁盘中（刷脏操作）。为了避免刷脏前宕机导致数据丢失，每次提交时必须先写入redo log，成功后方才认为事务提交成功。当mysql重启时，可以读取redo log进行恢复。

2）事务使用

　　开启事务：start transaction;　　

　　设置自动提交，这会使得事务语句完成后自动提交：SET autocommit=1;

　　回滚 rollback;

3）事务隔离级别

　　被放在另外一篇博客：https://www.cnblogs.com/cheungilin/articles/15368795.html

4.视图

　　视图可以被看作一次select查询的留存。使用视图通常是为了向用户隐藏数据库的某些结构（数据独立）、使得表符合业务逻辑（简单易用）、避免用户操作某些字段（安全）。

　　视图中可以有多张表，或者嵌套多个视图。但当数据来自多张表时，不允许添加和删除数据。

　　视图中的数据是原表数据的“引用”，即会影响原表。

　　显然，在视图上只能进行DML操作。

1）视图读

　　看过innoDB MVCC机制的同学可能还记得其中涉及“视图读”的概念。即在RR和RC事务隔离级别时，使用select查询数据时，会自动生成一个ReadView，记录当前活跃的事务id，用以确认是否能够读取某个版本的记录。

2）视图操作

　　创建视图：create view 视图名 as 查询语句;

　　查看视图：select 字段1,... from 视图名;

5.数据库设计-三大范式

1）第一范式： 确保每个字段的原子性。

2）第二范式：一张表仅描述一个实体，即确保表中每个字段均和主键相关。满足第一范式，除主键外其他键依赖于主键。

3）第三范式：确保每一列的值都和逐渐字节相关。满足第二范式，除了主键外的列均只能依赖主键，列之间不相互依赖。

6.mysql数据类型

 1）数值

TINYINT（1Byte）、SMALLINT（2Byte）、MEDIUMINT（3Byte）、INT（4Byte）、BIGINT（8Byte）、FLOAT(4Byte) 、DOUBLE（8Byte）、DECIMAL（依赖M和D值）。

2）日期和时间

DATE、TIME、YEAR、DATETIME、TIMESTAMP

3）字符串

CHAR（255Bytes）、VCHAR（可变长，65535Bytes）、TINYTEXT（255Bytes）、TEXT（65535Bytes）、MEDIUMTEXT（16MB）、LONGTEXT（4GB）。

4）二进制文本

TINYBLOB（255Bytes）、BLOB（65535Bytes）、MEDIUMBLOBS（16MB）、LONGBLOB（4GB）。

6.left join、right join、inner join

左连接：返回左表中所有记录以及由表中连结字段相等的记录。

右连接：返回右表中所有记录以及由表中连结字段相等的记录。

等值连接：只返回连接字段相等的记录。