数据库知识总结

--范式
第一范式：
表中的每个字段只能包含单一的数据项，不可再分割；
所有记录具有相同的字段个数；
没有重复的字段。

第二范式：
所有属性完全依赖于主键(主键是组合键的情况)，
即满足了第二范式就已经消除了部分依赖。

第三范式：所有属性完全依赖于主键(主键是简单键的情况)
A表中不允许存在其它表中的非主属性，即如果要想让A表中存在其它(B)表中的属性则必须是
那个(B)表中的主键，否则可能会存在其它(B)表中的非主属性依赖于B表中的主属性，B表中
的主属性又依赖于A表中的主键，这样造成传递依赖，如下：

B表中的非主属性→B表中的主属性→A表中的主属性

所以，满足了第三范式则已经消除了传递依赖。

第四范式：
不允许表中存在一个实体的两个或多个相互独立的多值因子。
违反第四范式出现的问题是找不到(不存在)维护数据的有效方法。

第五范式：
表应分解成较小的表，除非那些表在逻辑上拥有与原始表相同的主键。

修正的第三范式(BCNF)：
所有属性(包括主属性和非主属性)都不传递依赖于任何候选关键字，
即所有属性只能完全依赖于主键。






--事务
事务并发可能出现的问题：
p1. 脏读：读取了一个事务提交之前的数据(此事务有可能回滚)，由此造成读取到了脏数据。
p2. 不可重复读：一个查询的事务中读取了不同的数据值(一个事务中应该每次查询的数据一致)。
p3. 幻读：在一个查询事务时，有别的事务进行了插入或更新操作，致使在一个查询事务中查询的数据不一致。


针对数据库的事务并发可能出现的这些问题，有了以下数据库的事务隔离机制：
1. read-uncommitted：会出现p1、p2、p3
2. read-committed(Oracle默认)：解决p1，未解决p2、p3
4. repeatable-read(MySql默认)：在读取数据后为此读取的数据加锁，不让其他事务修改
8. serializable: 序列化，使事务(序列化)无法并发，所以能解决所有问题。




悲观锁、乐观锁
pessimistic lock：在一个事务A中，不管其他事务是否会影响事务A中涉及的数据，都将该数据加锁，以防止其他事务修改。 悲观锁一般是数据库层加锁。

optimistic lock：在一个事务A中，假设没有其他事务对A事务中涉及的数据造成干扰，如果有再提供相应的解决方案，具体如下：
在数据表中添加一个version字段用以标识数据版本。每次更新操作都将此version字段值加1。当做更新操作时判断如果更新数据中的version值<=数据库中的version值则更新失败(说明已有其他事务更新了)。
乐观锁不能保证其他系统影响数据，所以在多应用系统(都用同一数据库)环境中可以编写存储过程，所有系统都通过存储过程来执行更新动作，即暴露给应用系统的不是数据表而是存储过程(在存储过程中检查数据版本version的值)。



共享锁
共享 (S) 锁允许并发事务读取 (SELECT) 一个资源(即允许在加了共享锁的数据上再加共享锁)。资源上存在共享 (S) 锁时，任何其它事务都不能修改数据。一旦已经读取数据，便立即释放资源上的共享 (S) 锁，除非将事务隔离级别设置为可重复读或更高级别，或者在事务生存周期内用锁定提示保留共享 (S) 锁。

排它锁
排它 (X) 锁可以防止并发事务对资源进行访问。其它事务不能读取或修改排它 (X) 锁锁定的数据(即被加了排他锁的数据不能再加任何锁)。


--in&exsits
in: 拿外表的字段向内表的查询结果遍历是否有相同的。

eg: select count(*) from base_customer_sign where signCustomerUuid in (select uuid from base_customer where trueName like 'user%')--没有group by uuid的被优化了
select count(*) from base_customer_sign where signCustomerUuid in (select uuid from base_customer where trueName like 'user%' group by uuid)

exists: 内表的查询条件都会含有外表的一个字段，看内表的查询是否有结果，如果有结果就是真。
eg: select count(*) from base_customer_sign
where exists (select 1 from base_customer where trueName like 'user%' and base_customer.uuid = base_customer_sign.signCustomerUuid)

所以在都没有数据库优化的情况下：in是内表大，外表小效率低；exists是内表小，外表大效率低。
但由于都是内外表的两重比较，所以如果外表太大，效率都低。


总结：
1、 in可能被优化为 连接
2、 in 在未被优化时，外表小，内表大时（要建临时表并排序 耗时） 效率低
3、 exists 外表数据量大，速度肯定慢，，即使是in同样一样，而对于内表数据量多少跟索引有关。
4、 in 和 exists 在外表返回的数据量很大时也是低效的。


因此，，外表（驱动表） 应该都尽可能的小。




5、 not in 不走索引的，因此不能用
6、 not exists走索引的。


--truncate,delete,drop的异同点
 
注意:这里说的delete是指不带where子句的delete语句
 
相同点:truncate和不带where子句的delete, 以及drop都会删除表内的数据 
不同点: 
1. truncate和 delete只删除数据不删除表的结构(定义) 
    drop语句将删除表的结构被依赖的约束(constrain),触发器(trigger),索引(index); 依赖于该表的存储过程/函数将保留,但是变为invalid状态. 
2.delete语句是dml,这个操作会放到rollback segement中,事务提交之后才生效;如果有相应的trigger,执行的时候将被触发. 
truncate,drop是ddl, 操作立即生效,原数据不放到rollback segment中,不能回滚. 操作不触发trigger. 
3.delete语句不影响表所占用的extent, 高水线(high watermark)保持原位置不动 
显然drop语句将表所占用的空间全部释放 
truncate 语句缺省情况下见空间释放到 minextents个 extent,除非使用reuse storage; truncate会将高水线复位(回到最开始). 
4.速度,一般来说: drop> truncate > delete 
5.安全性:小心使用drop 和truncate,尤其没有备份的时候.否则哭都来不及 
使用上,想删除部分数据行用delete,注意带上where子句. 回滚段要足够大. 
想删除表,当然用drop 
想保留表而将所有数据删除. 如果和事务无关,用truncate即可. 如果和事务有关,或者想触发trigger,还是用delete. 
如果是整理表内部的碎片,可以用truncate跟上reuse stroage,再重新导入/插入数据

----------------------------------------------------------------------------

创建一个和已有表表结构相同的表(oracle数据库)
如果要求新表有旧表的数据：
create table newTable as select * from oldTable (where rownum <= xxx);
通过控制where条件可以控制新表有没有旧表的数据，有多少数据。