MySQL 数据库视图、事务、索引---学习总结

视图( view ):

视图概述:

• 通俗的讲, 视图就是一条 select 语句执行后返回的结果集
• 视图是对若干张基本表的引用, 一张虚拟表, 查询语句执行的结果, 不储存具体数据( 基本表数据改变, 视图也会跟着改变 )
• 主要用于方便 查询 操作, 减少复杂的 SQL 语句, 增强可读性

创建视图:

• 视图名: 建议以 v_ 开头
• create view 视图名 as select查询语句;

查看视图:

• select * from 视图名;

重命名视图:

• rename table 旧视图名 to 新视图名;

删除视图:

• drop view 视图名;

视图的作用:

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

---

事务( transaction ):

• 所谓事务, 它是一个操作序列, 这些操作要么执行, 要么不执行, 它是一个不可分割的工作单位()

事务四大特征( 简称ACID ):

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

一个好的事务处理系统：必须具备这些标准特性：

• 原子性（atomicity）:

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

• 一致性（consistency）:

数据库总是从一个一致性的状态转换到另一个一致性的状态，或有非法数据（外键约束之类），事务撤回

• 隔离性（isolation）:

通常来说，一个事务所做的修改在最终提交以前，对其他事务是不可见的，事务独立运行，事务的100%隔离，需要牺牲速度

• 持久性（durability）:

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

开启事务:

• 开启事务后, 执行修改操作, 变更只会维护到本地缓存中, mysql储存在本地, 所以数据不会被修改

begin; 或 start transaction;

提交事务:

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

commit;

回滚事务:

• 放弃缓存中变更的数据

rollback;

注意:

• 修改数据的命令会自动触发事务, 如: insert, update, delete
• 而在 SQL 语句中有手动开启事务的原因是: 可以进行多次数据的修改, 如果成功一起成功, 否则一起回滚到之前的数据
• conn.begin()  # 手动开启事务前会自动提交原来的事务, 即使没有执行commit()

---

索引:

概述:

• 索引是一种特殊的文件( InnoDB 数据表上的索引是表空间的一个组成部分 ), 它们包含着对数据表里所有数据的引用指针
• 通俗的说,  数据库索引好比是一本书前面的目录, 能加快数据库的查询速度, 提高效率

查看索引:

show index from 表名;

创建索引:

• 如果指定字段是字符串, 需要指定长度, 建议长度与定义字段长度一致
• 字段类型如果不是字符串, 可以不填

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

删除索引:

drop index 索引名称 on 表名;

开启时间检测, 关闭把 1 改成 0

set profiling=1;

显示执行时间

show profiles;

注意:

• 建立太多的索引将会影响 更新和 插入 的速度, 因为它需要同样更新每个索引文件
• 建立索引会占用磁盘空间
• 常用的索引有三种 主键索引、普通索引 和 唯一索引 ; 索引不是越多越好，哪些字段需要建索引呢？主键百分之百用到了索引，就是主键索引；
  • 普通索引 key
    • create index 索引名称 on 表名(字段名称(长度))
    • alter table 表名 add index 索引名称(字段名称(长度))
  • 唯一索引 unique，保证这个字段只会有一个值；
    • create unique index 索引名称 on 表名(字段名称(长度))
      • alter table 表名 add unique index 索引名称(字段名称(长度))

---

用户管理:

创建账户 和 授权:

权限列表包含create、alter、drop、insert、update、delete、select、all privileges: 表示所有权限

数据库中的全部表则用  数据库.*  某张表用数据库.表名

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

grant select,update,insert on jing_dong.* to 'cxq'@'localhost' identified by '111111';

MySQL 8.0:

创建用户:

CREATE USER 'xxx'@'%' IDENTIFIED WITH mysql_native_password BY '111111';

给新用户授权：

GRANT ALL PRIVILEGES ON *.* TO '用户名'@'host名称';

刷新权限：

FLUSH PRIVILEGES;

检查用户: ( 权限 & 加密方式 ):

select user, host, plugin, authentication_string from mysql.user

删除用户:

DROP USER 'username'@'host';

 

修改权限 添加权限:

grant 权限名称 on 数据库 to 账户@主机 with grant option;

 

撤销权限:

revoke 权限名称 on 数据库 from 账户@主机;

revoke insert on jing_dong.* from cxq@localhost;

 

修改密码:

使用root登录，修改mysql数据库的user表

使用password()函数进行密码加密

update user set authentication_string=password('新密码') where user='用户名';

 

注意：修改完成后需要刷新权限

flush privileges;

 

备份数据库:

注意：需要先退出MySQL客户端

mysqldump –uroot –p密码 数据库名 > 文件名.sql

恢复

连接mysql，创建“新的数据库”

退出连接，执行如下命令

mysql -uroot –p密码 新数据库名 < 文件名.sql