mysql高级篇-视图索引事务那些事儿

视图

什么是视图

视图是对若干张基本表的引用，一张虚表，查询语句执行的结果，不存储具体的数据，基本表数据发生改变，视图也会跟着变化

定义视图

建议以v_开头作为视图的名称

create view 视图名称 as select语句;

查看视图

show tables

使用视图

视图的用途就是查询

select * from v_视图名称;

删除视图

drop view 视图名称;

事务

什么是事务

事务广泛的应用于订单系统、银行系统等多种场景

事务是一个操作序列，这些操作要么都执行，要么都不执行，它是一个不可分割的单位。

事件的四大特征(简称ACID)

建议阅读 《高性能MySQL》

• 原子性（atomicity）

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

• 一致性（consistency）

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

• 隔离性（isolation）

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

• 持久性（durability）

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

事务命令

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

开启事务命令

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

begin

或者

start transaction

提交事务命令

commit;

回滚事务命令

rollback;

重要说明

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

索引

一般的应用系统对比数据库的读写比例在10:1左右(即有10次查询操作时有1次写的操作)，优化数据库的查询效率的解决方案就是索引

什么是索引

索引是一种特殊的文件（InnoDB数据表上的索引是表空间的一个组成部分），它们包含着数据表里所有的记录的引用指针

索引命令

查看索引

show infdex from 表名;

创建索引

如果指定字段是字符串，需要指定长度，建议长度与定义字段时的长度一致;

字段类型如果不是字符串，可以不填写长度部分

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

删除索引

drop index 索引名称 on 表名;

运行时间监测

• 开启运行时间监测

set profiling=1;

• 查看执行时间监测

show profiles;

注意

• 建立太多的索引将会影响更新和插入的速度，因为它需要同样更新每个索引文件。对于一个经常需要更新和插入的表格，就没有必要为一个很少使用的where字句单独建立索引了，对于比较小的表，排序的开销不会很大，也没有必要建立另外的索引。

• 建立索引会占用磁盘空间

账户管理

mysql账号分类

• 服务实例级账号：该账号就可以删除所有的数据库、连同这些库中的表
• 数据库级别账号：对特定数据库执行增删改查的所有操作
• 数据表级别账号：对特定表执行增删改查等所有操作
• 字段级别的权限：对某些表的特定字段进行操作
• 存储程序级别的账号：对存储程序进行增删改查的操作

mysql账号操作

账户的操作主要包括创建账户、删除账户、修改密码、授权权限等

授予权限

查看所有用户

desc user

字段说明:

• Host表示允许访问的主机
• User表示用户名
• authentication_string表示密码，为加密后的值

创建用户

create user "用户名"@"主机名" [ identified ] by [ password ] <口令>

语法说明如下：

• 用户名

指定创建用户账号，格式为 ‘user_name’@‘host_name’。这里user_name是用户名，host_name为主机名，即用户连接 MySQL 时所在主机的名字。若在创建的过程中，只给出了账户的用户名，而没指定主机名，则主机名默认为“%”，表示一组主机。

• PASSWORD

可选项，用于指定散列口令，即若使用明文设置口令，则需忽略PASSWORD关键字；若不想以明文设置口令，且知道 PASSWORD() 函数返回给密码的散列值，则可以在口令设置语句中指定此散列值，但需要加上关键字PASSWORD。

• IDENTIFIED BY子句

用于指定用户账号对应的口令，若该用户账号无口令，则可省略此子句。

• 口令

指定用户账号的口令，在IDENTIFIED BY关键字或PASSWOED关键字之后。给定的口令值可以是只由字母和数字组成的明文，也可以是通过 PASSWORD() 函数得到的散列值。

用户授权

• mysql8.0以后版本需要在创建用户后再执行grant命令，否则会报错
• 常用权限主要包括:create,alter,drop,insert,update,delete,select
  
• 分配所有权限使用all privileges|all
• 使用百分号%表示此账户可以使用任何ip的主机登录访问此数据库
• 访问主机设置成localhost或具体的ip，表示只允许本机或特定主机访问
• 相同用户名，不同主机为不同账号

# *.*表示所有数据库下的所有表,第一个星表示数据库名，第二个星表示表名
grant 权限列表 on *.* to "用户名"@"访问主机" with grant option;

例

grant select on jing_dong.* to 'laowang'@'localhost';

修改加密插件

• 查看当前用户使用的密码插件

mysql> show variables like '%auth%';

• 查看mysql8支持的密码验证插件

mysql> show plugins;

• 修改用户的密码和验证插件

alter user "用户名"@"主机名" identified with 插件名 by "密码"

例：

alter user 'root'@'localhost' identified with mysql_native_password by 'root';

• 修改系统默认的密码验证插件

  以下修改是在cnf配置文件中修改

  default-authentication-plugin=插件名称
  

  例:

  default-authentication-plugin=caching_sha2_password
  

查看用户权限

# mysql 数据库中存储了用户信息的 user 表
use mysql;

# 查看user表中用户权限
select host, user, authentication_string, plugin from user; 

修改用户密码

#将authentication_string字段设置为空
update user set authentication_string="" where user="用户名";

#修改密码
alter user "用户名"@"主机名" identified by "密码"

删除用户

• 使用drop命令

drop user 用户名@主机名

• 直接操作user数据表

delete from user where user="用户名"