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

一、事务

1、事务的ACID原则：原子性、一致性、隔离性、持久性2、操作 二、索引

1、分类2、基础语法3、索引原则 三、权限管理四、数据库备份五、三大范式

1、第一范式2、第二范式3、第三范式

要么都成功，要么都失败

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

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

原子性：针对同一个事务，上两个步骤要么一起成功，要么一起失败，不能只发生一个动作一致性：事务完成之后，符合逻辑运算。即上两个步骤执行前，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高效获取数据的数据结构。

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

主键索引(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('刘');

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

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

推荐文章：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、第一范式

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

如：

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

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

如：

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

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

如：

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

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