MySQL高级

视图

对于复杂的查询，往往是有多个数据表进行关联查询而得到，如果数据库因为需求等原因发生了改变，为了保证查询出来的数据与之前相同，则需要在多个地方进行修改，维护起来非常麻烦
此时我们可以通过定义视图来解决此类问题

视图是什么

通俗的讲，视图就是一条SELECT语句执行后返回的结果集。所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上。
视图是对若干张基本表的引用，一张虚表，查询语句执行的结果，不存储具体的数据（基本表数据发生了改变，视图也会跟着改变）；

使用视图的好处

方便操作
特别是查询操作
减少复杂的SQL语句
增强可读性

定义视图

create view 视图名称 as select语句;--视图名称建议以v_开头

查看视图

查看表会将所有的视图也列出来，所以我们直接使用查看表的sql语句即可

show tables;

使用视图

视图的用途就是查询，我们将其当作对一个数据表使用查询语句即可

select * from 视图名称;

删除视图

drop view 视图名称;

视图案例

select p.id,p.`province`,c.`city` from provinces as p inner join cities as c on
p.`provinceid` = c.provinceid having p.`province` = '黑龙江省';

视图的修改

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

视图的作用

1、提高了重用性，就像一个函数
2、对数据库重构，却不影响程序的运行
3、提高了安全性能，可以对不同的用户
4、让数据更加清晰

事务

为什么要有事务

事务广泛的运用于订单系统、银行系统等多种场景
比如有下面这种情况
甲用户和乙用户是银行的储户，现在甲要给乙转账500元，那么需要做以下几件事：
1、检查甲的账户余额>500元；
2、甲账户中扣除500元;
3、乙账户中增加500元;
正常的流程走下来，甲账户扣了500，乙账户加了500，皆大欢喜。
但是这样的话存在一个问题。如果A账户扣了钱之后，系统出故障了呢？甲白白损失了500，而乙也没有收到本该属于他的500。
所以上述案例中，隐藏着一个前提条件：甲扣钱和乙加钱，要么同时成功，要么同时失败。事务的需求就在于此
所谓事务,它是一个操作序列，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位
例如，银行转帐工作：从一个帐号扣款并使另一个帐号增款，这两个操作要么都执行，要么都不执行。所以，应该把他们看成一个事务。事务是数据库维护数据一致性的单位，在每个事务结束时，都能保持数据一致性。
上述例子的三个步骤的操作必须打包在一个事务中，任何一个步骤失败，则必须回滚所有的步骤。

事务四大特性(简称ACID)

原子性(Atomicity)
一致性(Consistency)
隔离性(Isolation)
持久性(Durability)

原子性（atomicity）

一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失
败回滚，对于一个事务来说，不可能只执行其中的一部分操作，这就是事务的原子性。

一致性（consistency）

数据库总是从一个一致性的状态转换到另一个一致性的状态。（在前面的例子中，一致性确保了，即使在执
行第二、三条语句之间时系统崩溃，支票账户中也不会损失，因为事务最终没有提交，所以事务中所
做的修改也不会保存到数据库中。）

隔离性（isolation）

通常来说，一个事务所做的修改在最终提交以前，对其他事务是不可见的。（在前面的例子中，当执行完第
二条语句、第三条语句还未开始时，此时有另外的一个账户汇总程序开始运行，则其看到支票帐户的余额并
没有被减去。）

持久性（durability）

一旦事务提交，则其所做的修改会永久保存到数据库。（此时即使系统崩溃，修改的数据也不会丢失。）

事务使用条件

表的引擎类型必须是innodb类型才可以使用事务，这是mysql表的默认引擎

开启事务

开启事务后执行修改命令，变更会维护到本地缓存中，而不维护到物理表中。

begin;
start transaction;
--上述两个语句都是开启事务的命令

提交事务

将缓存中的数据变更维护到物理表中

commit;

使用事务

开启事务后在一个窗口执行修改操作还不提交

在另一个窗口中数据还不会更新

当修改的这个窗口的数据commit提交后才会修改过来。

而当多个窗口都对一个语句进行修改时。

另一个窗口则会堵塞。

回滚事务

出现问题，放弃缓存中变更的数据

rollback;

注：
1、修改数据的命令会自动的触发事务，包括insert、update、delete
2、而在SQL语句中有手动开启事务的原因是：可以进行多次数据的修改，如果成功一起成功，否则一起会滚到之前的数据

索引

索引的目的在于提高查询效率。
一般的应用系统对比数据库的读写比例在10:1左右(即有10次查询操作时有1次写的操作)，而且插入操作和更新操作很少出现性能问题，
遇到最多、最容易出问题还是一些复杂的查询操作，所以查询语句的优化显然是重中之重。
当数据库中数据量很大时，查找数据会变得很慢。
这时我们便需要用到索引。
更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。

索引原理

除了词典，生活中随处可见索引的例子，如火车站的车次表、图书的目录等。它们的原理都是一样的，通过不断的缩小想要获得数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是我们总是通过同一种查找方式来锁定数据。
数据库也是一样，但显然要复杂许多，因为不仅面临着等值查询，还有范围查询(>、<、between、in)、模糊查询(like)、并集查询(or)等等。数据库应该选择怎么样的方式来应对所有的问题呢？

索引主要是像上图一样，通过将数据分块的方式，来优化查找。

索引的使用

查看索引

show index from table_name;

创建索引

如果指定字段是字符串，需要指定长度，建议长度与定义字段时的长度一致
字段类型如果不是字符串，可以不填写长度部分

create index 索引名字 on 表名(字段名称(长度));

删除索引

drop index 索引名字 on 表名;

索引案例

创建测试表test

create table text(title varchar(30));

使用python程序向表中加入十万条数据

开启运行时间监测：

set profiling=1;

查找第5万条数据，再查看执行时间：

show profiles;

为表title_index的title列创建索引：

create index title_index on test(title(10));

再查找第5万条数据并查看执行时间：

show profiles;

对二者进行比较。

适合建立索引的情况

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

不适合建立索引的情况

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

*账户管理

在生产环境下操作数据库时，绝对不可以使用root账户连接，而是创建特定的账户，授予这个账户特定的操
作权限，然后连接进行操作，主要的操作就是数据的crud

MySQL账户体系

根据账户所具有的权限的不同，MySQL的账户可以分为以下几种

服务实例级账号

启动了一个mysqld，即为一个数据库实例；如果某用户如root,拥有服务实例级分配
的权限，那么该账号就可以删除所有的数据库、连同这些库中的表

数据库级别账号

对特定数据库执行增删改查的所有操作

数据表级别账号

对特定表执行增删改查等所有操作

字段级别的权限

对某些表的特定字段进行操作

存储程序级别的账号

对存储程序进行增删改查的操作
账户的操作主要包括创建账户、删除账户、修改密码、授权权限等

授予权限

需要使用实例级账户登录后操作，以root为例
主要操作包括：
1、查看所有用户
2、修改密码
3、删除用户

查看所有用户

所有用户及权限信息存储在mysql数据库的user表中
查看user表的结构

desc user;

主要字段说明：
1、Host表示允许访问的主机
2、User表示用户名
3、authentication_string表示密码，为加密后的值

查看所有用户:

select host,user,authentication_string from user;

创建账户、授权

需要使用实例级账户登录后操作，以root为例
常用权限主要包括：create、alter、drop、insert、update、delete、select
如果分配所有权限，可以使用all privileges

创建账户&授权:

grant 权限列表 on 数据库 to '用户名'@'访问主机' identified by '密码';

比如创建一个 Mike 的账号，密码为 123456 ，只能通过本地访问, 并且只能对 jd 数据库中的所有表进行读操作.
第一步：使用root登录：

mysql -uroot -p
回车后写密码，然后回车

第二步：创建账户并授予所有权限

grant select on jd.* to 'Mike'@'localhost' identified by '123456';

说明：
可以操作jd数据库的所有表，方式为: jd.*
访问主机通常使用 百分号% 表示此账户可以使用任何ip的主机登录访问此数据库
访问主机可以设置成 localhost或具体的ip，表示只允许本机或特定主机访问

查看用户有哪些权限

show grants for Mike@localhost;

第三步：退出root登录

quit

第四步：使用Mike账号登录

mysql -ulaowang -p
回车后写密码，然后回车

若创建一个Jake的账号，密码为 123456，可以任意电脑进行链接访问, 并且对 jd 数据库中的所有表
拥有所有权限则是：

grant all privileges on jd.* to "Jake"@"%" identified by "123456"