Mysql极简笔记(7)---数据库维护及管理

一、数据库维护

1、事务处理

定义：为了维护数据库的完整性，保证成批的操作要么完全执行要么完全不执行，即原子性，所做的设置

1）术语：

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

2）语句

完全回退：

多个语句
START TRANSACTION					-- 标识回退点
事务中语句						
ROLLBACK							-- 启动回退
多个语句

局部回退：

多个语句
START TRANSACTION					-- 标识回退点，若被执行，则关闭事务，抛出异常
事务中语句							
SAVEPOINT point_1					-- 设置保留点，可以有多个
事务中语句
ROLLBACK TO point_1					-- 回退到相应保留点
事务中语句
ROLLBACK							-- 启动回退
多个语句

注：

• 只有INSERT、UPDATE、DELETE三个可以执行回退
• 提交：在事务处理块中，若要提交则需要加入提交语句COMMIT;
• 隐含事务关闭：当COMMIT或ROLLBACK语句执行后，事务会自动关闭（将来的更改会隐含提交）

2、字符集及校对顺序

字符集：针对不同语言（影响显示）
校对：为规定字符如何比较的指令（影响排序）

查询

显示可用字符集及校对
SHOW CHARACTER SET;
SHOW COLLATION;
显示默认的字符集及校对
SHOW VARIABLES LIKE 'character%';
SHOW VARIABLES LIKE 'collation%';

设置

CREATE TABLE mytable
(
	column1 INT,
	column2 CHAR(10) CHARACTER SET latin1 COLLATE latin1_general_ci  -- 优先级高
	）DEFAULT CHARACTER SET hebrew COLLATE hebrew_general_ci;		 -- 优先级低

注：CHARACTER SET 字符集名 COLLATE 校对顺序名
SELECT * FROM customers ORDER BY firstname COLLATE latin1_general_ci;
注：以上为临时区分大小写设置方法，默认不区分

3、备份诊断及日志

表检测：CHECK TABLE 表名1，表名2；
表修复：REPAIR TABLE 表名；
表优化：OPTIMIZE TABLE 表名；
查看数据库配置：SHOW VARIABLES;和SHOW STATUS;
查看进程线程：SHOW PROCESSLIST;
查找日志目录：SHOW VARIABLES LIKE '%err%';
备份数据库：链接太多了，以后再整理

二、安全管理

1、访问控制

查询用户权限
SHOW GRANTS FOR 账号名;
分配权限
GRANT 权限 ON 范围 TO 账号名
REVOKE 权限 ON 范围 FROM 账号名
权限：

• ALL:除GRANT外的所有权限
• DROP:删除权限
• CREATE:创建表权限
• 其他权限点此处

范围：

• 整个服务器：GRANT ALL REVOKE ALL
• 整个数据库：ON 数据库名.*
• 表：ON 数据库名.表名
• 特定列
• 特定存储过程

2、用户管理

查看用户列表
USE mysql; SELECT user FROM user;
新增账号
CREATE USER 账号名 IDENTIFIED BY ‘密码内容’;
变更密码
SET PASSWORD = '密码明文';（登录账号后输入）
SET PASSWORD FOR 账号名='密码明文';(有点问题)
重命名账号
RENAME USER 账号名 TO 新账号名;
删除账号
DROP USER 账号名;