5 mysql高级知识

create view 视图名称 as set 语句
（创建视图 as后接的是查询语句，查询语句结果不可有重复字段，可起别名）
• select * from 视图名称
  （查询视图）
• drop view 视图名称;
  （删除视图）

有下列内容之一，视图不能做修改select子句中包含distinct
• select子句中包含函数
• select语句中包含group by子句
• select语句中包含order by子句
• where子句中包含相关子查询
• from子句中包含多个表
• 如果视图中有计算列，则不能更新
• 如果基表中有某个具有非空约束的列未出现在视图定义中，则不能做insert操作

事务

• 事务是一个操作序列，这些操作要么都执行要么都不执行，它是一个不可分割的工作单位
• 事务的四大特性（简称acid）原子性（A）一个事务被视为像原子一样的最小工作单元是不可分割，要么一起执行要么全部回滚，不会出现执行一部分

数据库总是从一个一致性的状态转换到另一个一致性的状态

通常来说，一个事务所做的修改在最终提交前，对其他事务是不可见的

一旦事务提交，则其所做的修改会永久保存到数据库

事务的状态

• 活动的事务对应的数据库操作正在执行过程中

当事务的最后一个操作执行完成，但由于操作都在内存中执行，所造成的影响并没有刷新到磁盘时

当事务在活动或者部分提交状态时遇到某些错误

事务执行了半截而变为失败的状态

在部分提交状态将修改的数据都同步到磁盘后

事务的命令

• begin;
  （开启事务）
• start transaction;修饰符;
  （开启事务，与上一个不同的是可以指定事务模式，修饰符可为read only，read write）
• commit;
  （提交事务）
• rollback to 保存点名字;
  （回滚到保存点时状态，无保存点则回滚到开启事务前的状态）
• savepoint 保存点名字;
  （创建保存点）
• release savepoint 保存点名字;
  （删除保存点）
• 修改数据的命令会触发自动的触发事务，包括insert、update、delete，可通过show variables like ‘autocommit’;查看并修改自动提交

索引

• 索引是一种特殊的文件（innoDB数据表上的索引是表空间的一个组成部分），它们包含对数据表里所有记录的引用指针，通俗来讲，索引就是一本书的目录
• 目的：提高查询速度，排序
• 原理：B+树 BTREE
• B+树的查询过程
• 索引的命令create unique index 索引名字 on 表名字(字段);
  （创建索引,unique唯一索引）
• show index from 表名;
  （查看索引）
• drop index 索引名 on 表名;
  （删除索引）

主键自动建议索引
• 频繁作为查询条件的字段应该建立索引
• 查询中与其他表关联的字段，外键关系建立索引
• 在高并发的情况下创建复合索引
• 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
  （建立索引的顺序跟排序的顺序保持一致）

频繁更新的字段不适合建立索引
• where条件里面用不到的字段不创建索引
• 表记录太少，当表中数据超过三百万条数据，可以考虑建立索引
• 数据重复且平均的表字段，比如性别，国籍

账户管理

• 在生产环境下操作数据库时，绝对不可以使用root账户连接，而是创建特定的账号，授予这个账户特定的操作权限，然后连接进行操作，主要的操作就是数据的crud
• 账户的操作主要包括创建账户、删除账户、修改密码、授权操作等
• 常用的权限主要包括：create、alter、drop、insert、update、dalete、select
• 需要root实例级用户登陆后才可授权
• create user’username’@’%’ identified by ‘password’;
  （创建用户，username为登陆的用户名，password为登陆的密码，host是指定可以登陆的主机，其中localhost表示本地主机，%表示所有主机）
• alter user user() identified by ‘password’;
  （修改用户）
• drop user username;
  （删除用户）
• grant 权限列表 on 数据库.* to ‘用户名’@‘访问主机’ identified by ‘密码’;
  （授权给用户）

事务的隔离级别

• 隔离性其实比想象要复杂，在sql中定义了四种隔离级别，每种隔离级别都规定了事务中的修改，哪些是在事务内和事务间是可见的，哪些是不可见的，较低级的隔离通常来说能承受更高的并发，系统的开销也会更小
• 未提交读 read uncommitted脏读
• 事务的修改即使没有提交，对其他事务也是可见的，虽然性能是最优的，但是缺乏其他级别的很多好处

大多数数据库默认的隔离级别就是这个，但是mysql不是
• 一个事务只能看见已经提交的事务的修改结果
• 在一个事务中查询到的结果可能不一致

mysql默认的隔离级别，解决了脏读的问题，但无法避免幻行问题，mysql的innodb引擎通过多版本并发控制（mvcc）解决了幻读问题

可串行化会在读取的每一行数据都加锁，所以可能导致大量的锁等待和超时问题
• 隔离级别最高
• 阻塞