MySQL学习笔记6—事务视图和管理

MySQL学习笔记

本系列文章将结合书本、网络教程和实操层面，总结以新手角度学习MySQL的须知须会。

11. 事务

11.1 事务处理

• 用途：使成批的MySQL操作要么完全执行，要么完全不执行，维护数据库的完整性。

• 关键：将SQL语句分解为逻辑块，并明确规定数据何时回退，何时不回退。

• 术语：

  • 事务(transaction)：一组SQL语句
  • 回退(rollback)：撤销指定SQL语句的过程
  • 提交(commit)：将未存储的SQL语句结果接入写入数据库表
  • 保留点(savepoint)：事务处理中设置的临时占位符，可以对其发布回退

• 支持：InnoDB引擎

11.2 控制

语句	作用	说明
START TRANSACTION;	事务开始
ROLLBACK;	回退(撤回)事务开始后的所有语句	范围：插入、更新和删除语句，不回退CREATE和DROP。
COMMIT;	执行先前的语句	使用ROLLBACK和COMMIT后，事务会自动关闭。
SAVEPOINT delete1; ROLLBACK TO delete1;	使用保留点	执行ROLLBACK或COMMIT后保存点自动释放。
RELEASE SAVEPOINT;	手动释放保留点

• SET AUTOCOMMIT =0;不自动提交更改，直到autocommit被设置为真。

11.3 事务隔离

11.3.1 隔离级别

隔离级别	脏读	不可重复读	幻读	加锁读
读未提交	1	1	1	不加锁
读已提交	0	1	1	不加锁
可重复读	0	0	1	不加锁
可串行化	0	0	0	加锁

• 脏读：一个事务内读取到别的事务还没提交的字段
• 不可重复读：一个事务内读取到别的事务已经提交的更新字段
• 幻读：一个事务内读取到别的事务已经提交的插入字段
• 加锁：

11.3.2 设置级别

• 显示当前级别：SELECT @@tx_isolation;

• 显示系统级别：SELECT @global.tx_isolation;

• 设置级别：SET SESSION TRANSACTION ISOLATION LEVEL level;

  level: read commited, read uncommited, repeatable, serializable.

• 设置系统级别：SET GLOBAL TRANSACTION ISOLATION LEVEL level;

11.4 事务ACID

• 原子性
• 一致性
• 隔离型
• 持久性

11.5 引擎

11.5.1 特点

特性	MyISAM	InnoDB	memory	archive
批量插入速度	高	低	高	非常高
事务	0	1	0	0
全文本搜索	1	0	0	0
外键	0	1	0	0

11.5.2 设置

• 修改： ALTER TABLE tablename ENGINE = engine;

12. 视图

12.1 概念

• 定义：是虚拟表，包含的是一个SQL查询，不包含数据。

• 特性： 用来查看储存在别处数据的一种设施，使用起来和表基本相同。

• 用途：

  • 重用SQL语句
  • 简化操作
  • 使用表的组成部分
  • 保护数据，即给只用户表的特定部分访问权限
  • 更改数据格式和表示，即用视图返回底层表格式不同的数据

• 规则

  • 唯一命名
  • 不能索引、关联触发器或默认值
  • 可以嵌套

12.2 使用

12.2.1 创建和更改

• 语句
  1. CREATE VIEW view_name AS SELECT clause;
  2. ALTER VIEW view_name AS SELECT clause;
• 说明：分别为创建和修改视图。view_name为视图名，clause为查询语句。

12.2.2 查看和删除

• 语句

  1. DESC view_name;
  2. SHOW CREATE VIEW view_name;
  3. DROP VIEW view_name;

• 说明：分别为查看视图、查看视图创建语句和删除视图。

12.2.3 更新

• 可以对视图使用插入、更新和删除，同时将更新基表。
• 视图中有以下操作，则不能更新视图：
  • 分组(GROUP BY)
  • 连接
  • 子查询
  • 并
  • 聚集函数
  • DISTINCT
  • 导出（计算）列
• 应该将视图用于检索，而不是更新。

12. 管理

12.1 管理用户

12.1.1 查看

• 用户信息储存在名为mysql的数据库中

• 语句：USE mysql; SELECT USER FROM user;

• 权限测试方法：打开多个客户机（命令行），一个作为管理登陆，其他作为用户登陆。

12.1.2 创建和删除

• 语句：CREATE USER username @'address' IDENTIFIED BY 'password';

  username为用户名，address为IP，password为密码。

• 重命名：RENAME USER username TO newname;

• 删除：DROP USER username;

12.1.2 修改密码

• 修改本用户密码：SET PASSWORD = PASSWORD('passwrod');
• 修改其他用户密码(需要权限)：SET PASSWORD FOR username @'address' = PASSWORD('password');

12.2 权限

12.2.1 查看

• SHOW GRANTS FOR username @'address';

12.2.2 授权

• GRANT agrant ON db.ob TO username @'address' [IDENTIFIED BY 'password'];

参数	说明
agrant	权限类型，多个用,间隔
db	数据库，*表示全部。
ob	数据对象，如表、视图、储存过程等，*表示全部。

• 使用授权和撤销时，用户必须存在，但是数据库和表可以不存在。
• address未指定，表示所有IP都有连接权限。

12.2.3 撤销

• REVOKE agrant ON db.ob FROM username @'address';

12.2.4 权限

权限	说明	权限	说明
ALL	所有(除GTANT OPTION)	ALTER	修改表
CREATE USER	用户管理	CREATE	创建表
CREATE VIEW	创建视图	DELETE	删除
GRANT OPTION	授权和撤销	DROP	删除表
INDEX	创建和删除索引	INSERT	插入
UPDATE	更新	SELECT	查询
		USAGE	无访问权限

12.3 数据库维护

12.3.1 备份数据

• mysqldump -u root -h host -p dbname [t1,t2,...] > backname.sql

  在命令行bin目录下备份某个数据库或数据库中的表到sql文件。

  结尾不用双引号

• mysqldump -u root -p -all-databases > backname.sql

  备份所有数据库

• mysqldump -u root -p -B db1 db2 ... > backname.sql

  备份多个数据库

• mysqlhotcopy -u root -p dbname adirectory;

  在命令行bin目录下将数据库复制到某个文件夹中（5.7已经没有该命令）

• FLUSH TABLES;

  在备份前使用，保证所有数据被写到磁盘。

12.3.2 恢复数据

• use dbname; source backname.sql

  在mysql命令行将备份的sql文件恢复到指定数据库中。

• mysql -u root -p < backname.sql

  备份时若使用-A,-B，则可以在bin目录命令行直接恢复，不用进入mysql。

12.3.3 数据库维护

• ANALYZE TABLE tablename;

  检查表键是否正确

• CHECK TABLE tablename;

  针对许多问题检查

12.3.4 诊断启动问题

• MySQL服务器在命令行使用mysqld启动，用来排除系统启动问题
  • –help，显示帮助。
  • –safe-mode，装载减去某些最佳配置的服务器。
  • –verbose，显示全文本信息。
  • –version，显示版本。

12.3.5 查看日志文件

类型	名称	更改命令
错误日志	hostname.err	–log-error
查询日志	hostname.log	–log
二进制日志	hostname-bin	–log-bin
缓慢查询日志	hostname-slow.log	–log-slow-queries