[大数据]Java基础知识——DBMS(事务、索引、用户管理、备份、三大范式)

一、事务

要么都成功，要么都失败

1. SQL执行，A给B转账 A现有800元，要给B转200元
2. SQL执行，B收到A的转账 B现有200元，会受到A的转账200元

将一组事务放在一个批次中去执行

1、事务的ACID原则：原子性、一致性、隔离性、持久性

• 原子性：针对同一个事务，上两个步骤要么一起成功，要么一起失败，不能只发生一个动作
• 一致性：事务完成之后，符合逻辑运算。即上两个步骤执行前，A+B的钱为1000元，转账成功后，A+B的钱还是1000元，符合逻辑
• 持久性：表示事务结束之后不随着外界原因而导致数据丢失。在事务还没有提交的时候，服务器断电，那么重启数据库之后，A800元，B200元；如果事务已经发生之后，服务器断电，那么重启数据库之后，A600元，B400元。即事务没有提交就恢复到原状，事务一旦提交就不可逆
• 隔离性：主要是排除其他事物对本事务的影响

如果事务的隔离性出现了错误，就可能会出现以下的情况：

• 脏读：指一个事务读取了另外一个事务未提交的数据
• 不可重复读：在一个事务内读取表中的某一行数据，多次读取的结果不同。这个不一定是错误，只是某些场合不对
• 虚读(幻读)：是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致

2、操作

-- mysql是默认开启事务自动提交的
SET autocommit = 0; -- 关闭
SET autocommit = 1; -- 开启(默认)

-- 手动处理事务
SET autocommit = 0; -- 关闭自动提交

-- 事务开启
START TRANSACTION -- 标记一个事务的开始，从这个之后的sql都在同一个事务内

INSERT XX
INSERT XX

-- 提交：持久化(成功)
COMMIT
-- 回滚：回到原来的样子(失败)
ROLLBACK

-- 事务结束
SET autocommit = 1; -- 开始自动提交

测试事务实现转账：

创建一个shop的数据库，新建一张表

CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ci;
USE shop;

CREATE TABLE `account`(
	`id` INT(3) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(30) NOT NULL,
	`money` DECIMAL(9,2) NOT NULL,
	PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO account(`name`,`money`)
VALUEs('A',2000.00),('B',10000.00);

SET autocommit = 0;-- 关闭自动提交
START TRANSACTION； -- 开启一个事务

UPDATE account SET money=money-500 WHERE `name` = 'A' -- A减500
UPDATE account SET money=money+500 WHERE `name` = 'B'

COMMIT; --提交事务
ROLLBACK; --回滚

SET autocommit = 1; -- 恢复默认值

二、索引

索引是帮助mysql高效获取数据的数据结构。

1、分类

在一个表中，主键索引只能有一个，但唯一索引可以有多个

• 主键索引(PRIMARY KEY)：唯一的标识，主键不可重复，只能有一个列作为主键
• 唯一索引(UNIQUE KEY)：避免重复列出现，唯一索引可以重读，多个列都可以标识为唯一索引
• 常规索引(KEY/INDEX)：默认的
• 全文索引(FullText)：在特定的数据库引擎下才有，能快速定位数据

2、基础语法

-- 索引的使用
-- 1、在创建表的时候给字段添加索引
-- 2、创建完毕后，增加索引

-- 显示所有的索引信息
SHOW INDEX FROM student;

--增加一个全文索引 (索引名) 列名
ALTER TABLE school.student ADD FULLTEXT INDEX `studentname`(`studentname`);

-- EXPLAIN 分析sql执行的情况
EXPLAIN SELECT * FROM student; -- 非全文索引

EXPLAIN SELECT * FROM student WHERE MATCH(studentname) AGAINST('刘');

索引在小数据的时候用处不大，但是在大数据的时候可以减少查询时间。

3、索引原则

• 索引不是越多越好
• 不要对经常变动的数据加索引
• 小数据量的表不需要加索引
• 索引一般加载常用来查询的字段上

推荐文章：http://blog.codinglabs.org/articles/theory-of-mysql-index.html

三、权限管理

-- 创建用户 CREATE USER 用户名 IDENTIFIED BY '密码'
CREATE USER() fan IDENTIFIED BY '123456'

-- 修改密码(当前用户)
SET PASSWORD = PASSWORD('123456')

-- 修改密码(指定用户)
SET PASSWORD FOR fan = PASSWORD('123456')

-- 重命名 RENAME USER 原来名字 TO 新的名字
RENAME USER fan TO fan2

-- 用户授权 ALL PRIVILEGES (除了该别人授权，其他都能干)
GRANT ALL PRIVILEGES ON *.* TO fan2 

-- 查询权限
SHOW GRANTS FOR fan
SHOW GRANTS FOR root@localhost

-- 撤销权限
REVOKE ALL PRIVILEGES ON *.* FROM fan2

-- 删除用户
DROP USER fan2

四、数据库备份

保证重要的数据不丢失、数据转移。

使用cmd进行操作：

#导出
#mysqldump -h 主机 -u 用户名 -p 密码 数据库 表名 > 物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -p123456 school student>D:/a.sql

mysqldump -hlocalhost -uroot -p123456 school student result>D:/b.sql

#mysqldump -h 主机 -u 用户名 -p 密码 数据库 > 物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -p123456 school >D:/b.sql

#导入
#登录的情况下，要切换到指定的数据库
#source 备份文件
source D:/a.sql

五、三大范式

为什么需要数据规范化：

• 信息重复
• 更新异常
• 插入异常：无法正常显示信息
• 删除异常：丢掉有效的信息

1、第一范式

要求数据库表的每一列都是不可再分的原子项

如：

在上面的表中，学校信息不满足原子性要求，修改如下：

2、第二范式

前提：满足第一范式并且要确保每一列都与主键相关，而不能只与主键的一部分相关(针对联合主键)

如：

由于一个订单中可能包含不同的产品，因此主键由订单号和产品号联合组成。但是可以发现，订单金额和订单时间仅仅与订单号相关，与产品号无关，这就不满足第二范式，调整如下：

3、第三范式

在满足第一范式和第二范式的前提下，要消除传递依赖，确保数据表中的每一列数据都和主键直接相关，而不能间接相关

如：

上表中，所有元素都完全依赖于学号，满足第二范式，但是班主任性别和班主任年龄直接依赖的是班主任姓名，而不是主键学号，因此做出调整：

这样就满足了第三范式的要求