MySQL高级——视图&事务&索引&账号管理

一. 视图

简单来说, 视图就是一张虚拟表, 一般只用来查询 而不用来修改
创建视图

create view 视图名 as select 语句:(这个查询出来的东西就是这个虚拟表的数据)

使用虚拟表

select * from 虚拟表名:

删除虚拟表

drop view 虚拟表名

视图修改

有下列内容之一，视图不能做修改

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

视图的作用

1. 提高安全性,对于不同的人给予他不同的查询数据
2. 对数据库进行重构,缺不影响程序
3. 提高重用性
4. 使数据更加清晰

二. 事务

事务就是一个操作序列,同时成功 同时失败 ,是一个不可分割的操作单位

四大特性:

1. 原子性:就是要么成功要么失败, 两者不可分割
2. 一致性:就是从一个状态转换成另一个状态.,只有成功或者失败
3. 隔离性:当一个事务没有提交之前, 对其他事务是不可见的
4. 持久性:一旦事务提交,数据库就会对数据的修改进行永久保存,即使系统崩溃

事务命令

事务只能在搜索引擎为innodb的才可以开启,其他搜索不支持事务
开启命令 开启事务后执行修改命令，变更会维护到本地缓存中，而不维护到物理表中
start transaction 或者 begin
提交事务将缓存中的数据变更维护到物理表中
commit

回滚事务

rollback; 放弃缓存中变更的数据

三. 索引

索引是什么?
索引是一个特殊文件,innodb搜索引擎里面的索引是表空间的组成部分,包含着数据表里面所有记录的引用指针
索引的目的

1. 提高查询效率
2. 缩小查询范围

索引的种类

1. 单值索引:给一个字段起创建索引
2. 复合索引: 给多个字段添加索引, 他们是同一个索引名称,他们是有创建顺序的,写在前面的字段的索引为1, 下一个字段索引为2, 以此类推
3. 唯一索引:值不能重复

索引的使用

查询索引
show index from 表名;
创建索引
create index 索引名 on 表名(字段(长度));注:索引名一般是以idx_+需要添加字段 长度指字符串类型的字段长度
删除索引
drop index 索引名 on 表名;
开启运行时间监测：
set profiling=1;
查看执行的时间：
show profiles;

适合建立索引的情况

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

不适合建立索引的情况

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

四. 账号管理

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

MySQL账户体系：根据账户所具有的权限的不同，MySQL的账户可以分为以下几种
服务实例级账号： 启动了一个mysql，即为一个数据库实例；如果某用户如root,拥有服务实例级分配的权限，那么该账号就可以删除所有的数据库、连同这些库中的表
数据库级别账号： 对特定数据库执行增删改查的所有操作
数据表级别账号： 对特定数据表执行增删改查等所有操作
字段级别的权限： 对某些表的特定字段进行操作
存储程序级别的账号： 对存储程序进行增删改查的操作
账户的操作主要包括创建账户、删除账户、修改密码、授权权限等

授予权限

主要操作有: 查看所有用户, 修改密码, 删除用户

查看所有用户

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

desc user;

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

创建账户&授权

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

实例
创建一个laowang的账号，密码为123456，只能通过本地访问, 并且只能对jd数据库中的所有表进行读操作
创建账户并授予所有权限

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