阅读(4.2k) 书签赞(3) 我要纠错

关系型数据库管理系统

2018-08-12 22:09 更新

SQL 关系型数据库管理系统

什么是关系型数据库管理系统？

RDBMS 是关系型数据库管理系统（Relational DataBase Management System)的缩写，它是 SQL 以及所有现代数据库系统，例如 MS SQL Server、IBM DB2、Oracle、MySQL 和 MS Access等的基础。

关系型数据库管理系统（RDBMS）是一种基于 E.F. 科德提出的关系模型的数据库管理系统。

什么是表？

RDBMS 中的数据存储在被称作表的数据库对象中。表是相互关联的数据记录的集合，由一系列的行和列组成。

谨记，表是关系型数据库中最常见也是最简单的数据存储形式。下面是一个客户信息表的例子：

    +----+----------+-----+-----------+----------+
    | ID | NAME     | AGE | ADDRESS   | SALARY   |  
    +----+----------+-----+-----------+----------+  
    |  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |  
    |  2 | Khilan   |  25 | Delhi     |  1500.00 |  
    |  3 | kaushik  |  23 | Kota      |  2000.00 |  
    |  4 | Chaitali |  25 | Mumbai    |  6500.00 |  
    |  5 | Hardik   |  27 | Bhopal    |  8500.00 |  
    |  6 | Komal    |  22 | MP        |  4500.00 |  
    |  7 | Muffy    |  24 | Indore    | 10000.00 |  
    +----+----------+-----+-----------+----------+

什么是字段？

每张表都能够划分成更小的实体——字段。例如，上面的客户信息表中有 ID、NAME、AGE、ADDRESS 和 SALARY 五个字段。

一个字段限定了数据表中的列，被用来维护表中所有记录的特定信息。

什么是记录或者数据行？

记录或者说数据行是存在于数据表中的独立条目。例如，上面的客户信息表中有 7 条记录。下面是客户信息表中的一条记录：

    +----+----------+-----+-----------+----------+  
    |  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |  
    +----+----------+-----+-----------+----------+

记录就是表中水平排列的数据构成的实体。

什么是列？

列是表中竖直排列的实体，它包含了表中与某一特定字段相关的所有信息。

例如，上面的客户信息表中有字段为 ADDRESS 的列，存储了客户的地址，其内容如下所示：

    +-----------+  
    | ADDRESS   |  
    +-----------+  
    | Ahmedabad |  
    | Delhi     |  
    | Kota      |  
    | Mumbai    |  
    | Bhopal    |  
    | MP        |  
    | Indore    |  
    +----+------+

什么是 NULL 值？

NULL 值是表中以空白形式出现的值，表示该记录在此字段处没有设值。

一定要明白 NULL 值同 0 值或者包含空格的字段是不同的。值为 NULL 的字段是在记录创建的时候就被留空的字段。

SQL 约束

约束是表中的数据列必须遵守的规则，用于限制表中数据的类型。约束保证了数据库中数据的精确性和可靠性。

约束可以限制列或者表。列级的约束只限制单一的列，而表级的约束作用于整个表。

以下是 SQL 中常见的约束：

NOT NULL 约束：保证列中数据不能有 NULL 值
DEFAULT 约束：提供该列数据未指定时所采用的默认值
UNIQUE 约束：保证列中的所有数据各不相同
主键：唯一标识数据表中的行/记录
外键：唯一标识其他表中的一条行/记录
CHECK 约束：此约束保证列中的所有值满足某一条件
索引：用于在数据库中快速创建或检索数据

NOT NULL 约束

默认情况下，数据表中的字段接受 NULL 值。如果你不想让某个字段接受 NULL 值，那么请为该字段定义此约束，以指明该字段不接受 NULL 值。

NULL 并不是指没有数据，而是指该字段数据未知。

示例：

例如，下述 SQL 语句创建了一个新的数据表 CUSTOMERS，并添加了五个字段，其中三个字段——ID、NAME 和 AGE——被指定为 NOT NULL：

    CREATE TABLE CUSTOMERS(
           ID   INT              NOT NULL,
           NAME VARCHAR (20)     NOT NULL,
           AGE  INT              NOT NULL,
           ADDRESS  CHAR (25) ,
           SALARY   DECIMAL (18, 2),       
           PRIMARY KEY (ID)
    );

对于 Oracle 和 MySQL 来说，如果 CUSTOMERS 表已经存在，此时再要给 SALARY 字段添加 NOT NULL 约束的话，SQL 语句应当如下：

    ALTER TABLE CUSTOMERS
       MODIFY SALARY  DECIMAL (18, 2) NOT NULL;

DEFAULT 约束

DEFAULT 约束在 INSERT INTO 语句没有提供的情况下，为指定字段设置默认值。

示例：

例如，下述 SQL 语句创建了一个名为 CUSTOMERS 的新表，并添加了五个字段。这里，SALARY 字段的默认值为 5000。因此，如果 INSERT INTO 没有为该字段提供值的话，该字段就为默认值 5000。

    CREATE TABLE CUSTOMERS(
           ID   INT              NOT NULL,
           NAME VARCHAR (20)     NOT NULL,
           AGE  INT              NOT NULL,
           ADDRESS  CHAR (25) ,
           SALARY   DECIMAL (18, 2) DEFAULT 5000.00,       
           PRIMARY KEY (ID)
    );

如果 CUSTOMERS 表已经存在，此时再要给 SALARY 字段添加 DEFAULT 约束的话，你需要类似下面的语句：

    ALTER TABLE CUSTOMERS
       MODIFY SALARY  DECIMAL (18, 2) DEFAULT 5000.00;

删除 DEFAULT 约束：

要删除 DEFAULT 约束的话，请使用下面的 SQL 语句：

    ALTER TABLE CUSTOMERS
       ALTER COLUMN SALARY DROP DEFAULT;

UNIQUE 约束

UNIQUE 约束使得某一字段对任意两条记录来说都不能相同。例如，在 CUSTOMERS 表中，你或许想让任何人的年龄（age）都不相同。

示例：

例如，下述 SQL 语句创建了一个名为 CUSTOMERS 的新表，并添加了五个字段，其中 AGE 字段被设为 UNIQUE，于是任意两条记录的 AGE 都不同：

    CREATE TABLE CUSTOMERS(
           ID   INT              NOT NULL,
           NAME VARCHAR (20)     NOT NULL,
           AGE  INT              NOT NULL UNIQUE,
           ADDRESS  CHAR (25) ,
           SALARY   DECIMAL (18, 2),       
           PRIMARY KEY (ID)
    );

如果 CUSTOMERS 表已经存在，再要为 AGE 字段添加 UNIQUE 约束的话，你需要像下面这样写 SQL 语句：

    ALTER TABLE CUSTOMERS
       MODIFY AGE INT NOT NULL UNIQUE;

还可以使用如下所示的语法，该语法还支持对作用于多个字段的约束进行命名：

    ALTER TABLE CUSTOMERS
       ADD CONSTRAINT myUniqueConstraint UNIQUE(AGE, SALARY);

删除 UNIQUE 约束

要删除 UNIQUE 约束的话，请使用如下 SQL 语句：

    ALTER TABLE CUSTOMERS
       DROP CONSTRAINT myUniqueConstraint;

如果你在使用 MySQL，那么下面的语法也是可行的：

    ALTER TABLE CUSTOMERS
       DROP INDEX myUniqueConstraint;

主键

主键是数据表中唯一确定一条记录的字段。主键必须包含唯一值，并且不能为 NULL。

每张数据表只能有一个主键，不过一个主键可以包含一个或者多个字段。如果主键由多个字段组合而成，这些字段就被称作组合键。

如果一个字段被定义为了某表的主键，则任意两条记录在该字段处不能相同。

注意：在创建数据表的时候，需要用到这些概念。

创建主键：

如下是将 ID 定义为 CUSTOMERS 表主键的语法：

    CREATE TABLE CUSTOMERS(
           ID   INT              NOT NULL,
           NAME VARCHAR (20)     NOT NULL,
           AGE  INT              NOT NULL,
           ADDRESS  CHAR (25) ,
           SALARY   DECIMAL (18, 2),       
           PRIMARY KEY (ID)
    );

如果 CUSTOMERS 表已经存在了，再要将 ID 定义为主键的话，请使用下面的语句：

    ALTER TABLE CUSTOMER ADD PRIMARY KEY (ID);

注意：如果你要使用 ALTER TABLE 语句来添加主键，那么主键所在的列必须已经被声明为 NOT NULL 了。

要用多个字段来定义主键的话，请使用如下 SQL 语法：

    CREATE TABLE CUSTOMERS(
           ID   INT              NOT NULL,
           NAME VARCHAR (20)     NOT NULL,
           AGE  INT              NOT NULL,
           ADDRESS  CHAR (25) ,
           SALARY   DECIMAL (18, 2),        
           PRIMARY KEY (ID, NAME)
    );

如果 CUSTOMERS 表已经存在，此时再要将 ID 和 NAMES 字段定义为主键的话，请使用如下 SQL 语法：

    ALTER TABLE CUSTOMERS 
       ADD CONSTRAINT PK_CUSTID PRIMARY KEY (ID, NAME);

删除主键

你可以将主键约束从数据表中删除，语法如下：

    ALTER TABLE CUSTOMERS DROP PRIMARY KEY ;

外键

外键用于将两个数据表连接在一起，有时候也被称作“参照键”。

外键为单一字段或者多个字段的组合，并与另外一个数据表的主键相匹配。

两个表之间的关系是：一个表的主键与另一个表的外键相匹配。

示例：

考虑如下两个表的结构：

CUSTOMERS 表：

    CREATE TABLE CUSTOMERS(
           ID   INT              NOT NULL,
           NAME VARCHAR (20)     NOT NULL,
           AGE  INT              NOT NULL,
           ADDRESS  CHAR (25) ,
           SALARY   DECIMAL (18, 2),       
           PRIMARY KEY (ID)
    );

ORDERS 表：

    CREATE TABLE ORDERS (
           ID          INT        NOT NULL,
           DATE        DATETIME, 
           CUSTOMER_ID INT references CUSTOMERS(ID),
           AMOUNT     double,
           PRIMARY KEY (ID)
    );

如果 ORDERS 表已经存在，并且没有设置外键，那么可以使用下面的语法来修改数据表以指定外键。

    ALTER TABLE ORDERS 
       ADD FOREIGN KEY (Customer_ID) REFERENCES CUSTOMERS (ID);

删除外键约束：

要删除外键约束的话，语法如下所示：

    ALTER TABLE ORDERS
       DROP FOREIGN KEY;

CHECK 约束

CHECK 约束使用某一条件来对记录中的值进行检查。如果条件最终为假（false），即约束条件不能得到满足，则该记录不能写入数据表中。

示例：

例如，下述 SQL 语句创建了一个名为 CUSTOMERS 的新表，并为其添加了五个字段。在此，我们为 AGE 字段设置了 CHECK 约束，以拒绝任何年龄低于 18 的顾客：

    CREATE TABLE CUSTOMERS(
           ID   INT              NOT NULL,
           NAME VARCHAR (20)     NOT NULL,
           AGE  INT              NOT NULL CHECK (AGE >= 18),
           ADDRESS  CHAR (25) ,
           SALARY   DECIMAL (18, 2),       
           PRIMARY KEY (ID)
    );

如果 CUSTOMERS 表已经存在，再要为 AGE 字段设置 CHECK 约束的话，就需要像下面这样写 SQL 语句：

    ALTER TABLE CUSTOMERS
       MODIFY AGE INT NOT NULL CHECK (AGE >= 18 );

或者也可以使用下面的语法，该语法还支持对作用于多个字段的约束命名：

    ALTER TABLE CUSTOMERS
       ADD CONSTRAINT myCheckConstraint CHECK(AGE >= 18);

删除 CHECK 约束：

要删除 CHECK 约束的话，请使用下面的 SQL 语句，不过该语句在 MySQL 中不起作用：

    ALTER TABLE CUSTOMERS
       DROP CONSTRAINT myCheckConstraint;

索引

索引用于在数据库中快速地创建和检索数据。索引可以由表中的一个或者多个字段创建。创建索引时，每一行都会获得一个 ROWID（在数据进行排序之前）。

合理运用索引可以提高大型数据库的性能。但是，创建索引之前还是要三思而后行。为哪些字段创建索引，则取决于 SQL 查询最常用到到哪些字段。

示例：

例如，下面的 SQL 语句创建了一个名为 CUSTOMERS 的新表，并为其添加了五个字段：

    CREATE TABLE CUSTOMERS(
           ID   INT              NOT NULL,
           NAME VARCHAR (20)     NOT NULL,
           AGE  INT              NOT NULL,
           ADDRESS  CHAR (25) ,
           SALARY   DECIMAL (18, 2),       
           PRIMARY KEY (ID)
    );

现在，你就可以使用下面的语法来为一个或者多个字段创建索引了：

    CREATE INDEX index_name
        ON table_name ( column1, column2.....);

例如，可以在 AGE 字段上创建索引，以优化对特定年龄的顾客的查询，其语法如下所示：

    CREATE INDEX idx_age
        ON CUSTOMERS ( AGE );

删除索引约束：

要删除索引约束的话，可以使用下面的 SQL 语句：

    ALTER TABLE CUSTOMERS
       DROP INDEX idx_age;

数据完整性

下面几类数据完整性存在于各个 RDBMS 中：

实体完整性：表中没有重复的行
域完整性：通过限制数据类型、格式或者范围来保证给定列的数据有效性
参照完整性：不能删除被其他记录引用的行
用户定义完整性：施加某些不属于上述三种完整性的业务规则

数据库规范化

数据库规范化指的是对数据库中的数据进行有效组织的过程。对数据库进行规范化主要有两个目的：

消除冗余数据，例如相同数据出现在不同的表中。
保证数据依赖性合理。

这两个目标都值得我们努力，因为它们可以减少数据的空间占用，并确保了数据的逻辑完备。规范化包含一系列的指导方针，以帮助你创建出优良的数据库结构。

规范化指导方针分为几种范式（form），你可以把范式想做是数据库的格式或者其结构的布局方式。使用范式的目标是对数据库结构进行整理，从而使其遵循第一范式，接着是第二范式，最终遵循第三范式。

要不要更进一步到达第四范式、第五范式甚至更高的范式取决于你。一般来说，第三范式足矣。

第一范式（1NF）

第一范式设定了对数据库进行组织的最基本的规范：

定义需要的数据项，因为这些项将会成为数据表中的字段。将相关的数据项放在一个表中。
保证不存在重复的数据。
保证有一个主键。

1NF 的第一规则：

你必须定义所需的数据项。这意味着查看要存储的数据，按照字段对其进行组织，定义各个字段的数据类型，最终将相关的字段放在同一个表中。

例如，将所有与会议地点相关的字段放在 Location 表中，将所有同与会成员相关的字段放在 MemberDetails 表中等等。

1NF 的第二规则：

下一步是保证不存在重复的数据集合。考虑如下的数据表：

    CREATE TABLE CUSTOMERS(
           ID   INT              NOT NULL,
           NAME VARCHAR (20)     NOT NULL,
           AGE  INT              NOT NULL,
           ADDRESS  CHAR (25),
           ORDERS   VARCHAR(155)
    );

如果我们用同一个顾客的多笔订单来填充该表，将会得到类似下面的数据表：

ID	NAME	AGE	ADDRESS	ORDERS
100	Sachin	36	Lower West Side	Cannon XL-200
100	Sachin	36	Lower West Side	Battery XL-200
100	Sachin	36	Lower West Side	Tripod Large

但是，按照 1NF 我们必须保证没有重复的数据集合。所以，可以将上表分成两部分，然后使用一个键将两个表连接起来。

CUSTOMERS 表：

    CREATE TABLE CUSTOMERS(
           ID   INT              NOT NULL,
           NAME VARCHAR (20)     NOT NULL,
           AGE  INT              NOT NULL,
           ADDRESS  CHAR (25),
           PRIMARY KEY (ID)
    );

表中记录如下：

ID	NAME	AGE	ADDRESS
100	Sachin	36	Lower West Side

ORDERS 表：

    CREATE TABLE ORDERS(
           ID   INT              NOT NULL,
           CUSTOMER_ID INT       NOT NULL,
           ORDERS   VARCHAR(155),
           PRIMARY KEY (ID)
    );

表中记录如下：

ID	CUSTOMER_ID	ORDERS
10	100	Cannon XL-200
11	100	Battery XL-200
12	100	Tripod Large

1NF 的第三规则：

第一范式的最后一条规则是，为每一个数据表创建一个主键。

第二范式（2NF）

第二范式规定，数据表必须符合第一范式，并且所有字段与主键之间不存在部分依赖关系。

考虑顾客与订单之间的关系，你可能会想要存储顾客 ID、顾客姓名、订单 ID、订单明细以及购买日期：

    CREATE TABLE CUSTOMERS(
           CUST_ID    INT              NOT NULL,
           CUST_NAME VARCHAR (20)      NOT NULL,
           ORDER_ID   INT              NOT NULL,
           ORDER_DETAIL VARCHAR (20)  NOT NULL,
           SALE_DATE  DATETIME,
           PRIMARY KEY (CUST_ID, ORDER_ID)
    );

该表符合第一范式，因为它满足第一范式的所有规则。表中的主键有 CUST_ID 和 ORDER_ID。二者一起作为主键，我们假定同一个顾客不会购买相同的东西。

然而，该表不符合第二范式，因为表中的字段和主键之间存在部分依赖关系。CUST_NAME 依赖于 CUST_ID，而 CUST_NAME 和所购物品之间没有直接的联系。订单明细和购买日期依赖于 ORDER_ID，但是他们并不依赖于 CUST_ID，因为 CUST_ID 和 ORDER_DETAIL 以及 SALE_DATE 之间并不存在联系。

要使该表遵守第二范式，你需要将其分为三个数据表。

首先，创建如下的数据表来保存客户详情：

    CREATE TABLE CUSTOMERS(
           CUST_ID    INT              NOT NULL,
           CUST_NAME VARCHAR (20)      NOT NULL,
           PRIMARY KEY (CUST_ID)
    );

接着创建一个表来存储每个订单的详细信息：

    CREATE TABLE ORDERS(
           ORDER_ID   INT              NOT NULL,
           ORDER_DETAIL VARCHAR (20)  NOT NULL,
           PRIMARY KEY (ORDER_ID)
    );

最后，创建一个表来存储 CUST_ID 和 ORDER_ID 来记录同一顾客的所有订单：

    CREATE TABLE CUSTMERORDERS(
           CUST_ID    INT              NOT NULL,
           ORDER_ID   INT              NOT NULL,
           SALE_DATE  DATETIME,
           PRIMARY KEY (CUST_ID, ORDER_ID)
    );

第三范式（3NF）

一个数据表符合第三范式，当其满足：

符合第二范式；
所有的非主键字段都依赖于主键；

非主键字段之间的依赖关系存在于数据之中。例如下表中，街道（street）、城市（city）和省份（state）显然与邮政编码（zip Code）之间存在密不可分的关系。

    CREATE TABLE CUSTOMERS(
           CUST_ID       INT              NOT NULL,
           CUST_NAME     VARCHAR (20)      NOT NULL,
           DOB           DATE,
           STREET        VARCHAR(200),
           CITY          VARCHAR(100),
           STATE         VARCHAR(100),
           ZIP           VARCHAR(12),
           EMAIL_ID      VARCHAR(256),
           PRIMARY KEY (CUST_ID)
    );

邮政编码和地址之间的关系称作传递相关性（transitive dependency）。要使得数据表符合第三范式，需要将街道、城市、省份等字段移到另一张表中，可以称其为 Zip Code 表：

    CREATE TABLE ADDRESS(
           ZIP           VARCHAR(12),
           STREET        VARCHAR(200),
           CITY          VARCHAR(100),
           STATE         VARCHAR(100),
           PRIMARY KEY (ZIP)
    );

接着，按照如下方式更改 CUSTOMERS 表：

    CREATE TABLE CUSTOMERS(
           CUST_ID       INT              NOT NULL,
           CUST_NAME     VARCHAR (20)      NOT NULL,
           DOB           DATE,
           ZIP           VARCHAR(12),
           EMAIL_ID      VARCHAR(256),
           PRIMARY KEY (CUST_ID)
    );

移除传递相关性可以起到事半功倍的效果。首先是数据冗余度降低了，数据库体积因此缩小。第二个好处是保证数据完整性。当重复数据改变的时候，很有可能只更新部分数据，尤其是当其分布在数据库的各个地方的情况下。例如，如果地址和邮政编码分别存储在三个或者四个不同的数据表中，那么任何对邮编的改变，都需要对这三个或者四个表同时进行更改。

以上内容是否对您有帮助：

写笔记

我要补充