三范式和语法

• 第一范式(1NF)
• 第二范式(2NF)
• 第三范式(3NF)
• 2NF和3NF区别
• 内连接
• 外连接
• 事务
• 事务的隔离级别
• 引擎
• 索引
  • 为什么建立索引?
• 视图
• 视图操作
  • 创建视图
  • 查看视图
  • 修改视图
  • 更新视图
  • 删除视图

第一范式(1NF)

指数据库表中的每一列都是不可分割的一部分,同一列中不能有多个值.第一范式要求属性值不能再分割成更小的部分.第一范式强调列的原子性,即不能够再分成其他几列.
例如:有一列是电话号码,但是一个人可能有办公电话和家庭电话.若要符合第一范式,就要把电话号码分成家庭电话和办公电话两个属性.

第二范式(2NF)

第二范式首先满足第一范式,1.表中要有主键,2.不是主键的列必须完全依赖主键,而不是只依赖主键的一部分.
例如:订单表,一个order不足以成为主键,主键应该是orderId和productId.discount(折扣),quantity(数量)完全依赖于主键(orderId,productId),而unitPrice,productName等只依赖于productId(商品),所以这个表不符合2NF.不符合2NF的设计容易产生冗余.
所以,这个表要分割为order(orderId,productId,discountId,quantityId)表和product(productId,unitPrice,productName)表

第三范式(3NF)

首先满足第二范式,非主键列必须依赖于主键,不能存在传递.

订单表order(orderId,orderDate,customerId,customerName,customerAddr,customerCity) 其中orderDate,customerId,customerName,customerAddr,customerCity等非主键列都完全依赖与主键,所以符合2NF.但是CustomerName,customerAddr,customerCity直接依赖的是customerId(非主键列),他是通过传递才依赖于主键,所以不符合3NF.
通过拆分,分成两个表order(orderId,orderDate,customerId),customer(customerId,customerName,customerAddr,customerCity).

2NF和3NF区别

• 2NF:非主键列是否完全依赖于主键,还是只依赖于主键的一部分.
• 3NF:非主键列是直接依赖于主键,还是直接依赖于非主键.

内连接

内连接也叫自然连接,只有两个表相匹配的行才能在结果集中出现,返回的结果集选取两个表中所匹配的数据,舍弃不匹配的数据

/*笛卡尔积*/
select d.id,d.name,e.id,e.name,e.dept_id from dept d,emp e;
/*内连接的sql92写法*/
select d.id,d.name,e.id,e.name,e.dept_id from dept d,emp e where d.id=e.dept_id;
/*内连接的sql99写法*/
select d.id,d.name,e.id,e.name,e.dept_id from dept d inner join emp e on d.id=e.dept_id;

外连接

内连接保证两个表中的所有行都满足条件,而外链接不然,外连接不仅仅包含符合连接条件的行,而且还包括左表(左外连接),右表(右外连接),或者两个边表(全外连接)中的所有数据行
内连接只显示符合连接条件的记录,外连接除了显示符合连接条件的记录外,还显示表中的记录

/*左外连接 拿左表中的全部数据,右表只拿相等的数据*/
select d.id,d.name,e.id,e.name,e.dept_id from dept d left outer join emp e on d.id=e.dept_id;
/*右外连接 拿右表中的全部数据,左表只拿相等的数据*/
select d.id,d.name,e.id,e.name,e.dept_id from dept d right outer join emp e on d.id=e.dept_id;

事务

事务的四大特性(ACID)

1. 原子性Atomicity:即事务是一个不可分割的整体,数据修改时要么都操作一遍要么都不操作
2. 一致性Consistency:一个事务执行前后数据库的数据必须保持一致性状态
3. 隔离性Isolation:当两个或者以上的事务并发执行时,为了保证数据的安全性,将一个事务的内部的操作与事务操作隔离起来不被其他事务看到
4. 持久性Durable:更改是永远存在的

事务的隔离级别

• 读未提交：事务中的修改，即使没有提交，其他事务也可以看得到，脏读。如果一个事务已经开始写数据，则另外一个事务则不允许同时进行写操作，但允许其他事务读此行数据。该隔离级别可以通过“排他写锁”实现。一个在写事务另一个虽然不能写但是能读到还没有提交的数据

• 读已提交：可以避免脏读但是可能出现不可重复读。允许写事务，读取数据的事务允许其他事务继续访问该行数据，但是未提交的写事务将会禁止其他事务访问该行。事务T1读取数据，T2紧接着更新数据并提交数据，事务T1再次读取数据的时候，和第一次读的不一样。即虚读

• 可重复读：禁止写事务，读事务会禁止所有的写事务，但是允许读事务，避免了不可重复读和脏读，但是会出现幻读，即第二次查询数据时会包含第一次查询中未出现的数据

• 序列化：禁止任何事务，一个一个进行；提供严格的事务隔离。它要求事务序列化执行，事务只能一个接着一个地执行，但不能并发执行。如果仅仅通过“行级锁”是无法实现事务序列化的，必须通过其他机制保证新插入的数据不会被刚执行查询操作的事务访问到。

引擎

count 运算上的区别 : 因为MyISAM缓存有表meta-data (行数等) , 因此在做COUNT(*)时对于一个结构很好的查询是不需要消耗多少资源的。对于innodb是没有这种缓存

MyISAM强调的是性能，每次查询具有原子性，其执行速度比innodb类型更快，但是不提供事务支持。innodb提供事务支持事务，外部建等高级功能

总的来说MyISAM更适合读密集的表，而Innodb更适合写密集的表，在数据库主从分离的情况下，经常选择MyISAM做主库存储引擎

索引

为什么建立索引?

优点：

1. 通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性
2. 可以大大加快数据的检索速度
3. 可以加速表和表之间的连接,特别是在实现数据的参考完整性方面特别有意义
4. 在使用分组和排序子句进行数据检索时,可以显著减少查询中分组和排序的时间
5. 可以再查询的过程中,使用优化隐藏器,提高系统的性能

缺点：

1. 创建索引和维护索引需要耗费时间,时间随着数据量的增加而增加
2. 索引需要占用物理空间,除了数据表占数据空间外,每一个索引还要占用一定的物理空间,如果要建立聚簇索引,需要的空间会更大.
3. 当对表中的数据进行增加,删除和修改的时候,索引也需要动态的维护,这样就降低了数据的维护速度

合适的:经常需要搜索的列上,经常需要范围查询的,主键等
不合适的:经常不用来查询的,大字段比如text段等.

• 哈希索引：

只有memory（内存）存储引擎支持哈希索引，哈希索引用索引列的值计算该值的hashCode，然后在hashCode相应的位置存执该值所在行数据的物理位置，因为使用散列算法，因此访问速度非常快，但是一个值只能对应一个hashCode，而且是散列的分布方式，因此哈希索引不支持范围查找和排序的功能。

视图

视图是为了封装一些复杂的操作或者一些重复的操作.比如在多个地方使用相同的查询结果,或者sql语句比较复杂,封装成视图,下一次直接使用.

作用:

1. 提高了重用性,对数据库重构,不影响程序运行
2. 提高了安全性,可以对不用的用户,让数据更加清晰,特别是查询操作,减少复杂的sql语句
3. 增强可读性,更加安全,数据库授权命令不能限定到特定行和特定列,但是通过合理创建视图,可以把权限限定到行列级别

视图操作

创建视图

create view viewname as select * from tablename;

查看视图

1. describe viewname;
2. show table status like ‘视图名’\G;
3. show create view 视图名;
4. select * from information_schema.views;

修改视图

alter view 视图名 as select * from 表名;

更新视图

1. update 视图名 set 字段名=值;
2. insert into 表名 values(值,值)
3. delete from 视图名 where 字段=值

删除视图

drop view if exists 视图名;