数据库基础知识复习

关注

转载最新推荐文章于 2026-07-06 16:12:32 发布 488 阅读 AI 写同款· GEO 优化 ›

数据库知识整理，很详细完整，适合入门或者复习。 数据库知识点整理，包括数据库和表的创建，增删改查，子查询，关联查询，连接，交并差集，索引，序列，视图，各种函数，DDL、DML、DQL的实例。每个知识点都有基于ORACLE scott 账户下employee 表的实例代码。立即下载

1. 1NF 2NF 3NF BCNF

1NF的定义为：符合1NF的关系中的每个属性都不可再分.
这里写图片描述

商品表（编号，品名，进货数量，进货单价，销售数量，销售单价，备注）；

2NF在1NF的基础之上，消除了非主属性对于码的部分函数依赖。
如果码只有一个也就说不是组合属性组成的，那就不存在非主属性对码的部分依赖，因为码不可拆分；
例如：
学生_成绩（学号，姓名，系，系主任，课程，成绩）
第一步：确定码：设 K 为某表中的一个属性或属性组，若除 K 之外的所有属性都完全函数依赖于 K，那么我们称 K 为候选码，简称为码。在实际中我们通常可以理解为：假如当 K 确定的情况下，该表除 K 之外的所有属性的值也就随之确定，那么 K 就是码。一张表中可以有超过一个码。（实际应用中为了方便，通常选择其中的一个码作为主码）
码：（学号，课程）
第二：判断是否存在非主属性对码的部分依赖
（学号，课程）—>（姓名）
姓名只依赖于学号，所以存在非主属性对码的部分依赖
修改：
选课表（学号，课程，成绩）
学生（学号，姓名，系名，系主任）
3NF:在2NF的基础之上，消除了非主属性对于码的传递函数依赖
例如上面的（学号，姓名，系名，系主任）存在这样的传递依赖，系主任依赖于系名，系名依赖于学号，学号–>系名–>系主任
修改：
学生（学号，姓名，系名）
系（系名，系主任）
BCNF: 不存在主属性对于码的部分函数依赖与传递函数依赖
仓库（仓库名，管理员，物品名，数量）
显然上面满足三范式，不存在可分的字段；不存在非主属性对码的部分依赖；不存在非主属性对码的传递依赖：码（仓库名，管理员，物品名）；
但是在码中存在主属性对码的部分依赖管理员依赖于仓库，
修改：
仓库（仓库名，管理员）
物品（仓库名，物品，数量）

参考文档：罗老师数据库三范式

2. 数据库索引

索引是用于加速数据存取的数据对象，合理的使用索引可以大幅降低I/O次数，从而提高数据访问性能。

create index indexName on tableName(col1Name[, col2Name,...]);

索引的使用原则：在大表上建立索引才有意义；在where子句（根据where条件创建索引，并且SQL语句的查询条件是从右向左）；连接条件上经常使用的列上建立索引（外键）；索引的层次不宜超过4层。

索引的缺点：建立索引，系统要占用大约为表的1.2倍的硬盘和内存空间来保存索引；更新数据时，必须同时对索引进行更新，以维持数据和索引的一致性。

聚集索引: 表中存储的数据按照索引的顺序存储,检索效率比普通索引高。逻辑顺序决定了表中相应行的物理顺序。
非聚集索引：该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同。
索引是通过二叉树的数据结构来描述的，我们可以这么理解聚簇索引：索引的叶节点就是数据节点。而非聚簇索引的叶节点仍然是索引节点，只不过有一个指针指向对应的数据块；

Oracle数据库提供以下类型的索引：

B-tree索引（Oracle默认建立B-tree索引）
B-tree聚集索引（B-tree cluster indexes）
Hash聚集索引（Hash cluster indexes）
反向索引（Reverse key indexes）
位图索引（Bitmap indexes）
位图连接索引（Bitmap join indexes）

Oracle也支持基于函数的索引和某个应用的域索引（domain indexes）。
B-TREE索引（二叉树索引，默认情况下，我们建的索引都是此种类型）
一个B树索引只有一个根节点，它实际就是位于树的最顶端的分支节点。可以用下图一来描述B树索引的结构。其中，B表示分支节点，而L表示叶子节点。

这里写图片描述

对于分支节点块（包括根节点块）来说，其所包含的索引条目都是按照顺序排列的（缺省是升序排列）。每个索引条目（也可以叫做每条记录）都具有两个字段。第一个字段表示当前该分支节点块下面所链接的索引块中所包含的最小键值；第二个字段为四个字节，表示所链接的索引块的地址，该地址指向下面一个索引块。在一个分支节点块中所能容纳的记录行数由数据块大小以及索引键值的长度决定。比如从上图一可以看到，对于根节点块来说，包含三条记录，分别为（0 B1）、（500 B2）、（1000 B3），它们指向三个分支节点块。其中的0、500和1000分别表示这三个分支节点块所链接的键值的最小值。而B1、B2和B3则表示所指向的三个分支节点块的地址。

对于叶子节点块来说，其所包含的索引条目与分支节点一样，都是按照顺序排列的（缺省是升序排列，也可以在创建索引时指定为降序排列）。每个索引条目（也可以叫做每条记录）也具有两个字段。第一个字段表示索引的键值，对于单列索引来说是一个值；而对于多列索引来说则是多个值组合在一起的。第二个字段表示键值所对应的记录行的ROWID，该ROWID是记录行在表里的物理地址。如果索引是创建在非分区表上或者索引是分区表上的本地索引的话，则该ROWID占用6个字节；如果索引是创建在分区表上的全局索引的话，则该ROWID占用10个字节。

bitmap索引(位图索引)：
位图（bitmap）索引是另外一种索引类型，它的组织形式与B树索引相同，也是一棵平衡树。与B树索引的区别在于叶子节点里存放索引条目的方式不同。从前面我们知道，B树索引的叶子节点里，对于表里的每个数据行，如果被索引列的值不为空的，则会为该记录行在叶子节点里维护一个对应的索引条目。而位图索引则不是这样，其叶子节点里存放的索引条目如下图所示。
假设某个表T里所有的记录在列C1上只具有三个值：01、02和03。在表T的C1列上创建位图索引以后，则叶子节点的内容如图9-14所示。可以看到，位图索引只有三个索引条目，也就是每个C1列的值对应一个索引条目。位图索引条目上还包含表里第一条记录所对应的ROWID以及最后一条记录所对应的ROWID。索引条目的最后一部分则是由多个bit位所组成的bitmap，每个bit位就对应一条记录。
这里写图片描述

当发出where c1=’01’这样的SQL语句时，oracle会去搜索01所在的索引条目，然后扫描该索引条目中的bitmap里所有的bit位。第一个bit位为 1，则说明第一条记录上的C1值为01，于是返回第一条记录所在的ROWID（根据该索引条目里记录的start ROWID加上行号得到该记录所在的ROWID）。第二个bit位为0，则说明第二条记录上的C1值不为01，依此类推。另外，如果索引列为空，也会在位图索引里记录，也就是将对应的bit位设置为0即可。如果索引列上不同值的个数比较少的时候，比如对于性别列（男或女）等，则使用位图索引会比较好，因为它对空间的占用非常少（因为都是用bit位来表示表里的数据行），从而在扫描索引的时候，扫描的索引块的个数也比较少。可以试想一下，如果在列的不同值非常多的列上，比如主键列上，创建位图索引，则产生的索引条目就等于表里记录的条数，同时每个索引条目里的bitmap里，只有一个1，其它都是0。这样还不如B树索引的效率高。如果被索引的列经常被更新的话，则不适合使用位图索引。因为当更新位图所在的列时，由于要在不同的索引条目之间修改bit位，比如将第一条记录从01变为02，则必须将01所在的索引条目的第一个bit位改为0，再将02所在的索引条目的第一个bit位改为1。因此，在更新索引条目的过程中，会锁定位图索引里多个索引条目。也就是同时只能有一个用户能够更新表T，从而降低了并发性。位图索引比较适合用在数据仓库系统里，不适合用在OLTP系统里。

收录于