Mysql基础-高级功能

在使用过程中总会出现各种各样的问题，本章学习的语句将会解决其中一部分。

1.视图

​ 在实际的开发过程中存在着数据库结构需要发生变化的情况，一般情况下数据库结构发生变化，对应的代码也要进行修改，当项目很大的时候，其工作量巨大，视图 便是为了应对这一问题（仅用于查询）。类似于软件不同版本对应不同操作系统，其功能代码不变，仅解释器发生变化，视图在程序和数据表之间新建一个虚拟的表将数据库和代码之间隔离开来，使得后期更改表结构对代码改动减少。

-- 创建视图
create view `视图名` as `查询语句`
-- 视图只能进行查询，不能对视图直接进行增删改操作

2.事务

​ 对于一些事情必须要两边同时生效，例如银行转账，一边+200，一边-200，只要有一方失败即转账失败，之前的操作就要作废，都成功才算成功，因此引入了事务来确保数据的一致性。事务SQL样本如下：

-- 建立一个事务 start trabsaction或者begin
start trabsaction;
-- 检查余额数目
select banlance from checking where customer_id = 123456;
update checking set balance=balance-200 where customer_id = 123456;
update savings set balance=balance+200 where customer_id = 123456;
-- 执行完成，提交commit 回滚rollback
commit;

一个很好的事务有以下几种特性，简称(ACID):

• 原子性(atomicity)

一个事务必须视为一个不可分割的最小工作单元

• 一致性(consistency)

当事务中某一处出现问题，整个事务都不会生效。

• 隔离性(isolation)

当一个用户开启事务时，在其提交修改之前，对数据进行的修改仅自己可见，当其他人也对该数据进行修改时，需要等待前一个人事务结束。

• 持久性(durbaility)

数据存储在硬盘中，具有持久性

在python中使用pysql默认开启事务，只有commit或者rollback之后才会结束本次事务。

3.索引

​ 当想要在大量的数据中快速查找数据时，一条条搜索显然效率很低，这时候可以建立一个索引来提高搜索效率进行快速搜索。

-- 创建索引
create index `索引名` on `表名`(`列名(类型长度)`);
-- 显示索引
show index from `表名`;
-- 删除索引
drop index `索引名` on `表名`;

-- 检测语句执行时间
-- 开始时间监控
set profiling='1'
-- 显示执行时间
show profiles;

-- PS:主键和外键会默认设置为索引，索引会占用磁盘空间
-- 索引虽然能够提高搜索效率，但太多索引会降低更新和插入效率

4.权限管理

​ 在数据库的mysql库里查看user表可以看到能够访问数据库的用户，通过增删改查操作能够添加用户，删除用户，修改用户权限。

-- 进入mysql库
use mysql

-- 查看user信息，用户名，登录权限，以及加密后的密码
select host,user,authentication_string from user;
-- host信息%代表可以任意地方登录，localhost只能本地登录，固定ip代表只能特定主机登录

-- 创建用户并授权
-- 常用权限select insert delete drop update所有权限all privileges
grant `权限列表` on `数据库` to `用户名`@`访问主机` indentified by `密码`;
-- 查看用户权限
show grant for `用户名`@`访问主机`;
-- 修改用户权限
grant `权限列表` on `数据库` to `用户名`@`访问主机` with grant option;
-- 修改权限后必须刷新权限
flush privileges;
-- 修改用户密码
update user set authentication_string=password('新密码') where user='用户名';
-- 删除用户
drop user `用户名`@`访问主机`;

5.主从结构

​ 如果一个数据库突然停止工作，很容易造成问题，可以添加从服务器使得两个数据库数据同步，这样在一个库不能使用的时候，不会产生很大影响，从服务器也能够为主服务器分担访问压力。