数据库 这个术语的用法很多，但就从 SQL 的角度来看，数据库是一个以某种有组织的方式存储的数据集合。

最简单的办法是将数据库想象为一个文件柜。这个文件柜是一个存放数据的物理位置，不管数据是什么，也不管数据是如何组织的。

数据库（database）

保存有组织的数据的容器（通常是一个文件或一组文件）。

注意：误用导致混淆

我们通常用数据库这个术语来代表他们使用的数据库软件，这是不正确的，也因此产生了许多混淆。

确切地说，数据库软件应称为 数据库管理系统 （即 DBMS）。数据库是通过 DBMS 创建和操纵的容器，而具体它究竟是什么，形式如何，各种数据库都不一样。

你往文件柜里放资料时，并不是随便将它们扔进某个抽屉就完事了，而是在文件柜中创建文件，然后将相关的资料放入特定的文件中。

在数据库领域中，这种文件称为表。表是一种结构化的文件，可用来存储某种特定类型的数据。表可以保存顾客清单、产品目录，或者其他信息清单。

表（table）

某种特定类型数据的结构化清单。

这里的关键一点在于，存储在表中的数据是同一种类型的数据或清单。决不应该将顾客的清单与订单的清单存储在同一个数据库表中，否则以后的检索和访问会很困难。应该创建两个表，每个清单一个表。

数据库中的每个表都有一个名字来标识自己。这个名字是唯一的，即数据库中没有其他表具有相同的名字。

说明：表名

使表名成为唯一的，实际上是数据库名和表名等的组合。有的数据库还使用数据库所属用户的名字作为唯一名的一部分。也就是说，虽然在相同数据库中不能两次使用相同的表名，但在不同的数据库中完全可以使用相同的表名。

表具有一些特性，这些特性定义了数据在表中如何存储，包含存储什么样的数据，数据如何分解，各部分信息如何命名等信息。描述表的这组信息就是所谓的 模式 （schema），模式可以用来描述数据库中特定的表，也可以用来描述整个数据库（和其中表的关系）。

关于数据库和表的布局及特性的信息。

理解列的最好办法是将数据库表想象为一个网格，就像个电子表格那样。网格中每一列存储着某种特定的信息。例如，在顾客表中，一列存储顾客编号，另一列存储顾客姓名，而地址、城市、县以及邮政编码全都存储在各自的列中。

提示：分解数据

正确地将数据分解为多个列极为重要。例如，城市、县、邮政编码应该总是彼此独立的列。通过分解这些数据，才有可能利用特定的列对数据进行分类和过滤（如找出特定县或特定城市的所有顾客）。如果城市和县组合在一个列中，则按县进行分类或过滤就会很困难。

你可以根据自己的具体需求来决定把数据分解到何种程度。例如，一般可以把门牌号和街道名一起存储在地址里。这没有问题，除非你哪天想用街道名来排序，这时，最好将门牌号和街道名分开。

数据库中每个列都有相应的数据类型。数据类型（datatype）定义了列可以存储哪些数据种类。例如，如果列中存储的是数字（或许是订单中的物品数），则相应的数据类型应该为数值类型。如果列中存储的是日期、文本、注释、金额等，则应该规定好恰当的数据类型。

所允许的数据的类型。每个表列都有相应的数据类型，它限制（或允许）该列中存储的数据。

数据类型限定了可存储在列中的数据种类（例如，防止在数值字段中录入字符值）。数据类型还帮助正确地分类数据，并在优化磁盘使用方面起重要的作用。因此，在创建表时必须特别关注所用的数据类型。

注意：数据类型兼容

数据类型及其名称是 SQL 不兼容的一个主要原因。虽然大多数基本数据类型得到了一致的支持，但许多高级的数据类型却没有。更糟的是，偶然会有相同的数据类型在不同的 DBMS 中具有不同的名称。对此用户毫无办法，重要的是在创建表结构时要记住这些差异。

1. 什么是约束

使用数据库约束就是保证数据库完整性的方法。数据库设计的完整性实际上就是为了保证数据的正确性，那么为了保证数据正确，在数据库中涉及的完整性主要有三个，即 实体完整性 、 区域完整性 、 参考完整性 。

实体完整性

实体完整性针对表中的行数据，要求表中的主键字段都不能为空或者重复的值。

例如，每个人的身份证号码都是唯一的，在学校里每个学生的学号是唯一的，银行卡的卡号也是唯一的，等等。

区域完整性

区域完整性针对表中的列数据，是保证输入到数据库中的数据是在有效范围内的，可以通过数据类型或使用检查约束来设置。

比如，输入身份证号码要有 15 位或 18 位，输入年龄只能是数字，输入姓名不能有字母，年龄范围在 1～120 之间等。

参照完整性

参照完整性，可以保证数据库中相关联的表里数据的正确性，使用外键约束就可以保证参照完整性。确保数据表的参照完整性，就可以避免错误地删除或增加数据。

比如，学生选了课程，如果因为某种原因，学校取消了该课程，那么可能导致学生该时段没有课程可上，但是加上外键约束后，学校如果想取消该课程，首先得通知学生不选该课程，然后才能删除。所以使用参照完整性设计数据表就会避免产生脏数据。

注意：不同 DBMS 的约束有几种不同类型的约束，每个 DBMS 都提供自己的支持。因此，这里给出的例子在不同的 DBMS 上可能有不同的反应。在进行试验之前，请参阅具体的 DBMS 文档。

约束是在数据库中保证数据库里表中数据完整性的手段。在 Oracle 中使用的约束有主键约束、外键约束、唯一约束、检查约束、非空约束、默认约束 6 个，其中主键约束和唯一约束都被认为是唯一约束，而外键约束被认为是参照约束。

主键（Primary Key）约束

主键约束在每个数据表中只能有一个，但是一个主键约束可以由多个列组成，通常把由多个列组成的主键又叫做复合主键或组合主键。主键约束可以保证主键列的数据没有重复值且值不为空，也可以说它是保证记录唯一且不为空的一种方式。

表中任意列只要满足以下条件，都可以用于主键：

任意两行的主键值都不相同。

每行都具有一个主键值（即列中不允许 NULL 值）。

包含主键值的列从不修改或更新。（大多数 DBMS 不允许这么做，但如果你使用的 DBMS 允许这样做，好吧，千万别！）

主键值不能重用。如果从表中删除某一行，其主键值不分配给新行。

外键（Foreign Key）约束

外键约束之所以被称为是参照约束，是因为它主要用来把一个表中的数据和另一个表中的数据进行关联，表和表之间的关联是为了保证数据库中数据的完整性。使用外键保证数据的完整性，也叫参照完整性。

下面创建 商品信息表，结构如表所示。

在实际的操作当中，当客户下订单时会选择商品，而商品必须保证已经存在，所以在商品信息表中商品编号是主键，而在订单信息表中商品编号是外键，当商品信息表中的商品编号与订单信息表中的商品编号设置为外键约束后，在订单信息表中的商品编号就可以使用商品信息表中的商品编号代替。

设置完外键约束后，要求订单信息表中商品编号字段的值必须存在于商品信息表中，同时当在商品信息表中删除一种商品时，如果订单信息表正在使用该种商品，那么商品信息表中的该商品数据就无法被删除，这样就保证了数据库中数据的完整性。

提示：外键有助防止意外删除

除帮助保证引用完整性外，外键还有另一个重要作用。在定义外键后，DBMS 不允许删除在另一个表中具有关联行的行。例如，不能删除关联订单的顾客。删除该顾客的唯一方法是首先删除相关的订单（这表示还要删除相关的订单项）。由于需要一系列的删除，因而利用外键可以防止意外删除数据。

有的 DBMS 支持称为 级联删除 （cascading delete）的特性。如果启用，该特性在从一个表中删除行时删除所有相关的数据。例如，如果启用级联删除并且从 Customers 表中删除某个顾客，则任何关联的订单行也会被自动删除。

唯一（unique）约束

唯一约束和主键约束一样都是设置表中的列不能重复的约束，区别就是一个表中只能有一个主键约束，却可以有多个唯一约束，通常情况下设置唯一约束的目的就是为了使非主键列没有重复值。

唯一约束与主键约束的另一个区别是如果数据表中的某一列中有空值，那么就不能把这个列设置为主键列，但可以设置成唯一约束。

比如，在商品信息表中把商品的编号设置成了主键，但是还要保证商品的名称不重名，就可以对商品名称设置唯一约束。

检查（check）约束

检查约束是用来指定表中列的值的取值范围的，该约束更适合完成与业务逻辑相关的限制。

比如，商品信息表中商品数量的列，如果要求商品数量在 10 到 500 之间，就可以使用检查约束进行设置，当输入的值不在有效范围内时，就会出现错误，这样就保证了数据库数据的有效性。

非空（not null）约束

非空约束是用来约束表中的列不允许为空的。

例如，在员工信息表中员工身份证号码列，要求员工 必须输入时，可以使用非空约束来保证该列不能为空。

默认（default）约束

插入数据没有赋值时，给一个默认值。

1. 什么是事务

事务就是组包含 1 条或多条语句的逻辑单元，每个事务都是一个原子单位，在事务中的语句被作为一个整体，要么一起被提交，作用在数据库上，使数据库数据永久的修改；要么一起撤销，对数据库不做任何的修改。

例如银行账户之间的汇款转账操作。该操作在数据库中由以下 3 步完成。

源账户减少存储金额，减少 800。

目标账户增加存储金额，增加 800。

在事务日志中记录该事务。

整个交易过程，我们看做一个事务，如果操作失败，那么该事务就会回滚，所有该事务中的操作将撤销，目标账户和源账户上的资金都不会出现变化，如果操作成功，那么将是对数据库永久的修改，即使以后服务器断电，硬盘损坏，也不会对该修改结果有影响。

事务在没有提交之前可以回滚，一旦事务提交就不能再撤销修改了。

1.1. 事务的 ACID 特性

事务是构成单一逻辑工作单元的操作集合，具有 ACID 特性，即 ATOMIC（原子性）、CONSiSTENT（一致性）、ISOLATED（隔离性）、DURABLE（持久性）。事务的这些特性，适用于任何的数据库，例如 Oracle、MySQL 等，只有具备以上 4 个特点才能称为一个事务。

事务的原子性是指，事务中程序是数据库的逻辑工作单位，它对数据的修改要么全部执行，要么完全不执行。原子也意味着不可分割，不管有多少程序，只要在同一个事务中，那么它们就是一个整体，如果都执行成功才意味着该事务成功，而只要有一个操作失败，那么同一个事务中的其他操作即使执行成功也没有用，事务会使其全部撤销。

事务的一致性是指，事务执行的前后数据库都必须处于一致状态，它是相对脏读而言的。只有在事务完成后才能被所有使用者看见，保证了数据的完整性。

例如在银行转账时，从 A 账户取款但没有放到 B 账户中的时候是不一致的，数据也是不完整的，其他使用者此时不能看到 A 中修改后的数据，只有存到 B 账户中，交易完成并提交事务，这时才算数据一致，所有用户也会看到修改后的数据。

分离性是指并发事务之间不能相互干扰，也就是说一个事务操作的数据不会被其他事务看到和操作。

持久性是指一旦事务提交完成，那么这将是对数据永久的修改，即使被修改后的数据遭到破坏，也不会出现回到修改之前的情况。

开发人员在修改数据的时候一定要提交事务，否则数据将无法保存。但不建议过于频繁地提交事务，主要原因是每次提交事务都需要时间，如果有 80000 行记录，而每条记录都做提交事务操作，那么事务本身将是性能的主要消耗者，所以适当地减少事务提交次数很有必要，例如可以每 100 行提交一次，实际情况可以根据项目要求决定。

1.2. 控制事务

当一个事务被执行并成功完成时，虽然从输出结果来看目标表已经被修改了，但实际上目标表并不是立即被修改。当事务成功完成时，利用事务控制命令最终认可这个事务，可以把事务所做的修改保存到数据库，也可以撤销事务所做的修改。

事务控制命令只与 DML 命令 insert 、 update 和 delete 配合使用，比如我们不会在创建表之后使用 commit 语句，因为当表被创建之后，它会自动被提交给数据库。也不能使用 rollback 语句来恢复被撤销的表。

此外，还有其他类似的语句，也是不能被撤销的，例如 truncate 语句。所以，在运行新的命令前，最好先确认一下用户所使用的 DBMS 在事务方面的相关规定。当事务完成之后，事务信息被保存在数据库里的指定区域或临时回退区域。所有的修改都被保存到这个临时回退区域，直到事务控制命令出现。当事务控制命令出现时，所做的修改要么被保存到数据库，要么被放弃，然后临时回退区域被清空。

事务基本控制语句有如下几个：

commit：提交事务

savepoint：设置保存点

rollback：回滚事务

rollback to savepoint：回滚至保存点

代码演示：

commit;
savepoint a1;
rollback to a1;
rollback;
在 MySQL 数据库中，在命令行中，事务默认是自动提交的，要显式开启事务需要使用命令 begin，修改后如需提交使用 commit。
在 MySQL 远程连接工具中，与 Oracle 保持一致。
    2. 什么是锁
锁定是数据库引擎为了避免数据出现异常，而限制多个用户在同一时间访问相同数据块的一种机制。锁定机制是通过锁（LOCK）来实现的，当对一个数据源加锁后，此数据源就有了一定的访问限制，也就是对此数据源进行了锁定。
    2.1. 认识锁
锁出现在数据共享的环境中，它是一种机制，在访问相同资源时，可以防止事务之间的破坏性交互。例如，在多个会话同时操作某表时，优先操作的会话需要对其锁定。
在 DBMS 中，允许一个事务锁定不同类型的资源，包括数据行、表，也有可能是数据库本身，锁的模式根据需要处理的事件不同而有所差异。
在 DBMS 中，通常锁不是由开发者主动使用的，而是由系统自动进行加锁和解锁。在 DBMS 中，锁由数据库引擎中的锁管理器在内部管理。当执行 SQL 语句时，数据库查询处理器会自动决定将要访问哪些资源，并根据访问的类型和事务隔离级别设置来确定保护每一资源所需的锁的类型。然后，查询处理器将向锁管理器请求适当的锁，如果与其他事务所持有的锁不会发生冲突，锁管理器将授予该锁。
例如，当一个会话对表 A 的某行记录进行修改时，另一个会话也来修改该行记录，在没有任何处理的情况下保留的数据会有随机性，而这种数据是没有任何意义的，为脏数据。如果此时使用了行级锁，第一个会话修改记录时封锁该行，那么第二个会话此时只能等待，这样就避免了脏数据的产生。
    2.2. 锁的分类
了解两种基本的锁，一种是排他锁（X 锁），另一种是共享锁（S 锁）。
排他锁也可以叫写锁。
这种模式的锁防止资源的共享，用做数据的修改。
假如有事务 T 给数据 A 加上该锁，那么其他的事务将不能对 A 加任何的锁，所以此时只允许 T 对该数据进行读取和修改，直到事务完成将该类型的锁释放为止。
共享锁也可以叫读锁。
该模式锁下的数据只能被读取，不能被修改。
如果有事务 T 给数据 A 加上共享锁后，那么其他事务不能对其加排他锁，只能加共享锁。加了该锁的数据可以被并发地读取。
锁是实现并发的主要手段，在数据库中应用频繁，但很多都由数据库自动管理，当事务提交后会自动释放锁。
听到视图会比较陌生，实际上视图的创建和操作比较简单。在直观印象中它和表类似，但某些表的功能它不具备。
根据官方的文档可以这样理解视图：它是一个基于一个表或多个表的逻辑表，视图本身不包含任何数据。
通俗来说，可以把视图看成是虚拟的表，只是一个查询语句的结果，它的数据最终是从表中获取的，这些表通常称为 源表 或 基表 。当基表的数据发生变化时，视图里的数据同样发生变化。通常视图的数据源有三种情况：
单一表的子集。
多表操作结果集。
视图的子集。
使数据简化
在表中很多数据对业务来说是冗余的，这时开发者会使用比较复杂的 SQL 语句得到自己想要的。实际开发中不能要求每个人都能做到这一点，所以，通常情况下由一个人把该复杂语句做成视图，其他人员直接调用该视图即可。这样对视图使用人员就简化了数据，隐藏了数据的复杂性。
使数据更加独立
程序开发时，大多数是程序直接访问数据库的表，当这些表的结构随着业务的变化而不得不重新设计时会影响到程序（通常表一旦设计完成就很难再做修改），所以可以使得程序直接访问视图。这样视图就可以把程序和数据库的表隔离开来，降低开发者的劳动成本。
增加安全性
视图可以查询表指定的列来展现给用户，而不必让使用者完全看见表的所有字段。这种情况很多是一个公司提供给其他合作伙伴查询数据的接口，而视图通常也会设成只读属性。
警告：性能问题
因为视图不包含数据，所以每次使用视图时，都必须处理查询执行时需要的所有检索。如果你用多个联结和过滤创建了复杂的视图或者嵌套了视图，性能可能会下降得很厉害。因此，在部署使用了大量视图的应用前，应该进行测试。
索引用来排序数据以加快搜索和排序操作的速度。想像一本书后的索引，可以帮助你理解数据库的索引。
假如要找出书中所有的数据类型这个词，简单的办法是从第 1 页开始，浏览每一行。虽然这样做可以完成任务，但显然不是一种好的办法。浏览少数几页文字可能还行，但以这种方式浏览整部书就不可行了。随着要搜索的页数不断增加，找出所需词汇的时间也会增加。
这就是书籍要有索引的原因。索引按字母顺序列出词汇及其在书中的位置。为了搜索数据类型一词，可在索引中找出该词，确定它出现在哪些页中。然后再翻到这些页，找出数据类型一词。
使索引有用的因素是什么？很简单，就是恰当的排序。找出书中词汇的困难不在于必须进行多少搜索，而在于书的内容没有按词汇排序。如果书的内容像字典一样排序，则索引没有必要（因此字典就没有索引）。
数据库索引的作用也一样。主键数据总是排序的，这是 DBMS 的工作。因此，按主键检索特定行总是一种快速有效的操作。
但是，搜索其他列中的值通常效率不高。例如，如果想搜索住在某个县的客户，怎么办？因为表数据并未按县排序，DBMS 必须读出表中所有行（从第一行开始），看其是否匹配。这就像要从没有索引的书中找出词汇一样。
解决方法是使用索引。可以在一个或多个列上定义索引，使 DBMS 保存其内容的一个排过序的列表。在定义了索引后，DBMS 以使用书的索引类似的方法使用它。DBMS 搜索排过序的索引，找出匹配的位置，然后检索这些行。
在开始创建索引前，应该记住以下内容：
索引改善检索操作的性能，但降低了数据插入、修改和删除的性能。在执行这些操作时，DBMS 必须动态地更新索引。
索引数据可能要占用大量的存储空间。
并非所有数据都适合做索引。取值不多的数据（如县）不如具有更多可能值的数据（如姓或名），能通过索引得到那么多的好处。
索引用于数据过滤和数据排序。如果你经常以某种特定的顺序排序数据，则该数据可能适合做索引。
可以在索引中定义多个列（例如，县加上城市）。这样的索引仅在以县加城市的顺序排序时有用。如果想按城市排序，则这种索引没有用处。
没有严格的规则要求什么应该索引，何时索引。大多数 DBMS 提供了可用来确定索引效率的实用程序，应该经常使用这些实用程序。
提示：检查索引
索引的效率随表数据的增加或改变而变化。许多数据库管理员发现，过去创建的某个理想的索引经过几个月的数据处理后可能变得不再理想了。最好定期检查索引，并根据需要对索引进行调整。
为了处理订单，必须核对以保证库存中有相应的物品。
如果物品有库存，需要预定，不再出售给别的人，并且减少物品数据以反映正确的库存量。
库存中没有的物品需要订购，这需要与供应商进行某种交互。
关于哪些物品入库（并且可以立即发货）和哪些物品退订，需要通知相应的顾客。
这显然不是一个完整的例子，它甚至超出了我们所学范例的范围，但足以表达我们的意思了。执行这个处理需要针对许多表的多条 SQL 语句。此外，需要执行的具体 SQL 语句及其次序也不是固定的，它们可能会根据物品是否在库存中而变化。
那么，怎样编写代码呢？可以单独编写每条 SQL 语句，并根据结果有条件地执行其他语句。在每次需要这个处理时（以及每个需要它的应用中），都必须做这些工作。
可以创建存储过程。简单来说，存储过程就是为以后使用而保存的一条或多条 SQL 语句。可将其视为批文件，虽然它们的作用不仅限于批处理。
    1. 为什么要使用存储过程
我们知道了什么是存储过程，那么为什么要使用它们呢？
理由很多，下面列出一些主要的。
通过把处理封装在一个易用的单元中，可以简化复杂的操作（如前面例子所述）。
由于不要求反复建立一系列处理步骤，因而保证了数据的一致性。如果所有开发人员和应用程序都使用同一存储过程，则所使用的代码都是相同的。
这一点的延伸就是防止错误。需要执行的步骤越多，出错的可能性就越大。防止错误保证了数据的一致性。
简化对变动的管理。如果表名、列名或业务逻辑（或别的内容）有变化，那么只需要更改存储过程的代码。使用它的人员甚至不需要知道这些变化。
这一点的延伸就是安全性。通过存储过程限制对基础数据的访问，减少了数据讹误（无意识的或别的原因所导致的数据讹误）的机会。
因为存储过程通常以编译过的形式存储，所以 DBMS 处理命令的工作较少，提高了性能。
存在一些只能用在单个请求中的 SQL 元素和特性，存储过程可以使用它们来编写功能更强更灵活的代码。
换句话说，使用存储过程有三个主要的好处，即简单、安全、高性能。显然，它们都很重要。不过，在将 SQL 代码转换为存储过程前，也必须知道它的一些缺陷。
不同 DBMS 中的存储过程语法有所不同。事实上，编写真正的可移植存储过程几乎是不可能的。不过，存储过程的自我调用（名字以及数据如何传递）可以相对保持可移植。因此，如果需要移植到别的 DBMS，至少客户端应用代码不需要变动。
一般来说，编写存储过程比编写基本 SQL 语句复杂，需要更高的技能，更丰富的经验。因此，许多数据库管理员把限制存储过程的创建作为安全措施（主要受上一条缺陷的影响）。
尽管有这些缺陷，存储过程还是非常有用的，并且应该使用。事实上，多数 DBMS 都带有用于管理数据库和表的各种存储过程。更多信息请参阅具体的 DBMS 文档。
说明：不能编写存储过程？你依然可以使用
大多数 DBMS 将编写存储过程所需的安全和访问权限与执行存储过程所需的安全和访问权限区分开来。这是好事情，即使你不能（或不想）编写自己的存储过程，也仍然可以在适当的时候执行别的存储过程。
触发器是特殊的存储过程，它在特定的数据库活动发生时自动执行。触发器可以与特定表上的 insert、update 和 delete 操作（或组合）相关联。
与存储过程不一样（存储过程只是简单的存储 SQL 语句），触发器与单个的表相关联。与 Orders 表上的 insert 操作相关联的触发器只在 Orders 表中插入行时执行。类似地，Customers 表上的 insert 和 update 操作的触发器只在表上出现这些操作时执行。
触发器内的代码具有以下数据的访问权：
insert 操作中的所有新数据；
update 操作中的所有新数据和旧数据；
delete 操作中删除的数据。
根据所使用的 DBMS 的不同，触发器可在特定操作执行之前或之后执行。
下面是触发器的一些常见用途。
保证数据一致。例如，在 insert 或 update 操作中将所有州名转换为大写。
基于某个表的变动在其他表上执行活动。例如，每当更新或删除一行时将审计跟踪记录写入某个日志表。
进行额外的验证并根据需要回退数据。例如，保证某个顾客的可用资金不超限定，如果已经超出，则阻塞插入。
计算计算列的值或更新时间戳。
不同 DBMS 的触发器创建语法差异很大，更详细的信息请参阅相应的文档。
© 2022 刘士. All rights reserved.
本站访客数