sql学习记录13--事务管理（commit、rollback、savepoint、）、高级字段约束、索引、数据库安全

数据库管理

数据库控制语言

1. 事务处理

1.事务管理
管理必须成批执行的SQL操作，使之完全执行或完全不执行
（成批执行操作：现在我们到某公司入职，需要将自己信息加入不止一个系统中）
2.管理对象：
INSERT、UPDATE、DELETE
(因为这些会对数据库修改，会导致数据库存在不完整信息)

3.关键字：
COMMIT、ROLLBACK、SAVEPOINT

commit，rollback

提交COMMIT：将sql语句的执行结果写入数据库表中
回退ROLLBACK：撤销sql语句的执行结果
保留点SAVEPOINT：时间点的备份，可以对其发布回退（就是我到某一处，进行之前结果的备份，那么这个点之后如果运行出问题，就进行回退，回退到备份点处的状态）
最后可以释放save point，用RELESEA
注：
（1）CTREATE、DROP不可以回滚
（2）ROLLBACK TO

通过cmd进入mysql
在pet6中插入数据

此时撤回rollback，没有任何改动。
因为执行之后就自动提交结果了.
现在再插入一行，并在插入之前设定提交点

SET AUTOCOMMIT = 0;
#0代表取消自动提交机制

修改自动提交机制

SET AUTOCOMMIT = 0;

0代表取消自动提交机制
1代表增加自动提交机制
（1代表修改数据表后自动提交 ）

此时插入一行新的数据T2

此时回退，就没有刚刚插入的那行数据了，结果：

此时再插入一行数据T3，
此时提交一下，结果：

此时再rollback，就没有用了，结果：

总结：只要提交了，就永久在数据表中，此时使用rollback就没有用了。也就是说rollback可以在commit之前发生，一旦提交（commit），就不能再回退（rollback）。

现在改回来自动提交机制,

SET AUTOCOMMIT = 1;

结果：

发起一个事务

关键字BEGIN，意思开启了一个事务管理

此时删除一行数据T，并回退

回退的结果是撤回了刚刚删除的数据，即恢复数据T：

此时，如果再删除一次数据T并提交一下，再撤回

撤回就不起作用了，即T恢复不了了

现在，使用一下SAVEPOINT，试一下：
开启一个事务BEGIN
删除数据T3，在此处设定savepoint备份点sp1
再删除数据slim

此时，再设一个savepoint备份点，sp2
插入数据test3

以上操作总结：
begin（开始管理事务）
删除T3
savepoint sp1（#1）
删除slim
savepoint sp2
插入test3
现在：回退到sp1，可以看到恢复到了#1状态

以上操作总结：
begin（开始管理事务）
删除T3行
savepoint sp1（#1）
删除slim行
savepoint sp2
插入test3行
回退到sp1（恢复到了#1状态）
释放sp2（RELESE savepoint sp1）
释放sp1（RELESE savepoint sp2）
回退到sp1（ROLLBACK TO sp1）
出现ERROR（因为没有sp1了）
此时commit
结果仍然是#1的状态，如下所示：

2. 高级特性–对字段的约束

2.1 主键（PRIMARY KEY）

1.作用：确保数据的唯一性
2.特征：
（1）每行都有；
（2）行的唯一标识（主键之间不能有重复,，即主键所在的列中每个值都是唯一的）；
（3）不能修改；
（4）删除后不再分配。
（不同DBMS要求不同，要自己测试）
3.主键不能为空，建表的时候就要标注。
4.建表时设定主键

<字段名> <数据类型> NOT NULL UNIQUE

5.建表后增加主键
ALTER TABLE <表名>
ADD CONSTRAINT PRIMARY KEY(<列名>)

2.2 外键(foreign key)

（1）确保引用完整性
（2）特征：该表与其他表的关联；外键一定是其他表的主键.
（3）建表时设定外键
<字段名><数据类型> NOT NULL
REFERENCES KEY <关联表>（关联列）

（4）建表后增加外键
ALTER TABLE <表名>
ADD CONSTRAINT FOREIGN KEY(<列名>)
REFERENCES <关联表>（关联列）

2.3 唯一约束UNIQUE

1.作用：确保行数据唯一性（有点像主键的意思）
2.具体特征：
（1）可以有多个字段（和主键的不同点）
（2）可以是NULL
（3）可以被更新
（4）可以重复使用（？）
（5）不能定义外键
3.语句：
（1）<字段名> <数据类型> UNIQUE
（2）ALTER TABLE <表名>
ADD CONSTRAINT UNIQUE(<列名>)

2.4 检查约束CHECK

1.作用：确保该列数据满足指定条件
2.指定条件：
（1）值的范围（>0）
（2）特定值（性别M/F）
3.语句：
（1）<字段名> <数据类型> NOT NULL
CHECK ( <字段> (限定条件) ）

（2）ALTER TABLE <表名>
ADD CONSTRAINT CHECK ( <字段> （限定条件）)

2.5 默认约束DEFAULT

1.作用：指定默认值
2.语句：
<字段名> <数据类型> DEFAULT<默认值>

索引

建立索引语句：
CREATE INDEX 索引名 ON 表名（列名）;

索引的本质是一种排序，在某列上建立索引，即对该列进行“排序”
（1）可以快速定位、快速排序
（2）可以定义多列，实现组合排序。
使用注意：
（1）要求数据的唯一性较好（某列数据重复的多进行排序的意义不大）

（2）索引提高查询、排序速度，适用于过滤或者排序较多的字段，索引名不能重复

（3）数据修改（增删改）操作时，需要重新更新索引，效率低
（4）占用大量内存（所以要科学使用索引）

数据库安全-DBA

限制数据库操作
（1）表的访问权限（可以用VIEW PROCEDURE实现）
（2）表字段的访问权限（可以用VIEW PROCEDURE实现）
（3）表的修改权限(可以用GPANT REVOKE实现)（比如对不同账号采用不同的使用权限）

数据库管理员（运维工程师）–从事管理和维护DBMS 的相关工作人员的统称
管理内容：
数据库设计、数据库测试、数据库部署、数据库交付
核心目标：
保证数据库管理系统的稳定性、安全性、完整性、高性能。
（稳定性、安全性、完整性是基本的工作，而高性能是持续优化和努力的方向）