离线下载
PDF版 ePub版

极客学院团队出品 · 更新于 2018-09-20 06:00:38

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)
    );

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

上一篇: 概览 下一篇: 数据库