SQL 關(guān)系型數(shù)據(jù)庫管理系統(tǒng)

什么是關(guān)系型數(shù)據(jù)庫管理系統(tǒng)？

RDBMS 是關(guān)系型數(shù)據(jù)庫管理系統(tǒng)（Relational DataBase Management System)的縮寫，它是 SQL 以及所有現(xiàn)代數(shù)據(jù)庫系統(tǒng)，例如 MS SQL Server、IBM DB2、Oracle、MySQL 和 MS Access等的基礎(chǔ)。

關(guān)系型數(shù)據(jù)庫管理系統(tǒng)（RDBMS）是一種基于 E.F. 科德提出的關(guān)系模型的數(shù)據(jù)庫管理系統(tǒng)。

什么是表？

RDBMS 中的數(shù)據(jù)存儲在被稱作表的數(shù)據(jù)庫對象中。表是相互關(guān)聯(lián)的數(shù)據(jù)記錄的集合，由一系列的行和列組成。

謹記，表是關(guān)系型數(shù)據(jù)庫中最常見也是最簡單的數(shù)據(jù)存儲形式。下面是一個客戶信息表的例子：

    +----+----------+-----+-----------+----------+
    | 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 五個字段。

一個字段限定了數(shù)據(jù)表中的列，被用來維護表中所有記錄的特定信息。

什么是記錄或者數(shù)據(jù)行？

記錄或者說數(shù)據(jù)行是存在于數(shù)據(jù)表中的獨立條目。例如，上面的客戶信息表中有 7 條記錄。下面是客戶信息表中的一條記錄：

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

記錄就是表中水平排列的數(shù)據(jù)構(gòu)成的實體。

什么是列？

列是表中豎直排列的實體，它包含了表中與某一特定字段相關(guān)的所有信息。

例如，上面的客戶信息表中有字段為 ADDRESS 的列，存儲了客戶的地址，其內(nèi)容如下所示：

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

什么是 NULL 值？

NULL 值是表中以空白形式出現(xiàn)的值，表示該記錄在此字段處沒有設(shè)值。

一定要明白 NULL 值同 0 值或者包含空格的字段是不同的。值為 NULL 的字段是在記錄創(chuàng)建的時候就被留空的字段。

SQL 約束

約束是表中的數(shù)據(jù)列必須遵守的規(guī)則，用于限制表中數(shù)據(jù)的類型。約束保證了數(shù)據(jù)庫中數(shù)據(jù)的精確性和可靠性。

約束可以限制列或者表。列級的約束只限制單一的列，而表級的約束作用于整個表。

以下是 SQL 中常見的約束：

• NOT NULL 約束：保證列中數(shù)據(jù)不能有 NULL 值
• DEFAULT 約束：提供該列數(shù)據(jù)未指定時所采用的默認值
• UNIQUE 約束：保證列中的所有數(shù)據(jù)各不相同
• 主鍵：唯一標識數(shù)據(jù)表中的行/記錄
• 外鍵：唯一標識其他表中的一條行/記錄
• CHECK 約束：此約束保證列中的所有值滿足某一條件
• 索引：用于在數(shù)據(jù)庫中快速創(chuàng)建或檢索數(shù)據(jù)

NOT NULL 約束

默認情況下，數(shù)據(jù)表中的字段接受 NULL 值。如果你不想讓某個字段接受 NULL 值，那么請為該字段定義此約束，以指明該字段不接受 NULL 值。

NULL 并不是指沒有數(shù)據(jù)，而是指該字段數(shù)據(jù)未知。

示例：

例如，下述 SQL 語句創(chuàng)建了一個新的數(shù)據(jù)表 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 表已經(jīng)存在，此時再要給 SALARY 字段添加 NOT NULL 約束的話，SQL 語句應(yīng)當如下：

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

DEFAULT 約束

DEFAULT 約束在 INSERT INTO 語句沒有提供的情況下，為指定字段設(shè)置默認值。

示例：

例如，下述 SQL 語句創(chuàng)建了一個名為 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 表已經(jīng)存在，此時再要給 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 語句創(chuàng)建了一個名為 CUSTOMERS 的新表，并添加了五個字段，其中 AGE 字段被設(shè)為 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 表已經(jīng)存在，再要為 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;

主鍵

主鍵是數(shù)據(jù)表中唯一確定一條記錄的字段。主鍵必須包含唯一值，并且不能為 NULL。

每張數(shù)據(jù)表只能有一個主鍵，不過一個主鍵可以包含一個或者多個字段。如果主鍵由多個字段組合而成，這些字段就被稱作組合鍵。

如果一個字段被定義為了某表的主鍵，則任意兩條記錄在該字段處不能相同。

注意：在創(chuàng)建數(shù)據(jù)表的時候，需要用到這些概念。

創(chuàng)建主鍵：

如下是將 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 表已經(jīng)存在了，再要將 ID 定義為主鍵的話，請使用下面的語句：

    ALTER TABLE CUSTOMER ADD PRIMARY KEY (ID);

注意：如果你要使用 ALTER TABLE 語句來添加主鍵，那么主鍵所在的列必須已經(jīng)被聲明為 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 表已經(jīng)存在，此時再要將 ID 和 NAMES 字段定義為主鍵的話，請使用如下 SQL 語法：

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

刪除主鍵

你可以將主鍵約束從數(shù)據(jù)表中刪除，語法如下：

    ALTER TABLE CUSTOMERS DROP PRIMARY KEY ;

外鍵

外鍵用于將兩個數(shù)據(jù)表連接在一起，有時候也被稱作“參照鍵”。

外鍵為單一字段或者多個字段的組合，并與另外一個數(shù)據(jù)表的主鍵相匹配。

兩個表之間的關(guān)系是：一個表的主鍵與另一個表的外鍵相匹配。

示例：

考慮如下兩個表的結(jié)構(gòu)：

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 表已經(jīng)存在，并且沒有設(shè)置外鍵，那么可以使用下面的語法來修改數(shù)據(jù)表以指定外鍵。

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

刪除外鍵約束：

要刪除外鍵約束的話，語法如下所示：

    ALTER TABLE ORDERS
       DROP FOREIGN KEY;

CHECK 約束

CHECK 約束使用某一條件來對記錄中的值進行檢查。如果條件最終為假（false），即約束條件不能得到滿足，則該記錄不能寫入數(shù)據(jù)表中。

示例：

例如，下述 SQL 語句創(chuàng)建了一個名為 CUSTOMERS 的新表，并為其添加了五個字段。在此，我們?yōu)?AGE 字段設(shè)置了 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 表已經(jīng)存在，再要為 AGE 字段設(shè)置 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;

索引

索引用于在數(shù)據(jù)庫中快速地創(chuàng)建和檢索數(shù)據(jù)。索引可以由表中的一個或者多個字段創(chuàng)建。創(chuàng)建索引時，每一行都會獲得一個 ROWID（在數(shù)據(jù)進行排序之前）。

合理運用索引可以提高大型數(shù)據(jù)庫的性能。但是，創(chuàng)建索引之前還是要三思而后行。為哪些字段創(chuàng)建索引，則取決于 SQL 查詢最常用到到哪些字段。

示例：

例如，下面的 SQL 語句創(chuàng)建了一個名為 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)
    );

現(xiàn)在，你就可以使用下面的語法來為一個或者多個字段創(chuàng)建索引了：

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

例如，可以在 AGE 字段上創(chuàng)建索引，以優(yōu)化對特定年齡的顧客的查詢，其語法如下所示：

    CREATE INDEX idx_age
        ON CUSTOMERS ( AGE );

刪除索引約束：

要刪除索引約束的話，可以使用下面的 SQL 語句：

    ALTER TABLE CUSTOMERS
       DROP INDEX idx_age;

數(shù)據(jù)完整性

下面幾類數(shù)據(jù)完整性存在于各個 RDBMS 中：

• 實體完整性：表中沒有重復的行
• 域完整性：通過限制數(shù)據(jù)類型、格式或者范圍來保證給定列的數(shù)據(jù)有效性
• 參照完整性：不能刪除被其他記錄引用的行
• 用戶定義完整性：施加某些不屬于上述三種完整性的業(yè)務(wù)規(guī)則

數(shù)據(jù)庫規(guī)范化

數(shù)據(jù)庫規(guī)范化指的是對數(shù)據(jù)庫中的數(shù)據(jù)進行有效組織的過程。對數(shù)據(jù)庫進行規(guī)范化主要有兩個目的：

• 消除冗余數(shù)據(jù)，例如相同數(shù)據(jù)出現(xiàn)在不同的表中。
• 保證數(shù)據(jù)依賴性合理。

這兩個目標都值得我們努力，因為它們可以減少數(shù)據(jù)的空間占用，并確保了數(shù)據(jù)的邏輯完備。規(guī)范化包含一系列的指導方針，以幫助你創(chuàng)建出優(yōu)良的數(shù)據(jù)庫結(jié)構(gòu)。

規(guī)范化指導方針分為幾種范式（form），你可以把范式想做是數(shù)據(jù)庫的格式或者其結(jié)構(gòu)的布局方式。使用范式的目標是對數(shù)據(jù)庫結(jié)構(gòu)進行整理，從而使其遵循第一范式，接著是第二范式，最終遵循第三范式。

要不要更進一步到達第四范式、第五范式甚至更高的范式取決于你。一般來說，第三范式足矣。

第一范式（1NF）

第一范式設(shè)定了對數(shù)據(jù)庫進行組織的最基本的規(guī)范：

• 定義需要的數(shù)據(jù)項，因為這些項將會成為數(shù)據(jù)表中的字段。將相關(guān)的數(shù)據(jù)項放在一個表中。
• 保證不存在重復的數(shù)據(jù)。
• 保證有一個主鍵。

1NF 的第一規(guī)則：

你必須定義所需的數(shù)據(jù)項。這意味著查看要存儲的數(shù)據(jù)，按照字段對其進行組織，定義各個字段的數(shù)據(jù)類型，最終將相關(guān)的字段放在同一個表中。

例如，將所有與會議地點相關(guān)的字段放在 Location 表中，將所有同與會成員相關(guān)的字段放在 MemberDetails 表中等等。

1NF 的第二規(guī)則：

下一步是保證不存在重復的數(shù)據(jù)集合?？紤]如下的數(shù)據(jù)表：

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

如果我們用同一個顧客的多筆訂單來填充該表，將會得到類似下面的數(shù)據(jù)表：

但是，按照 1NF 我們必須保證沒有重復的數(shù)據(jù)集合。所以，可以將上表分成兩部分，然后使用一個鍵將兩個表連接起來。

CUSTOMERS 表：

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

表中記錄如下：

ORDERS 表：

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

表中記錄如下：

1NF 的第三規(guī)則：

第一范式的最后一條規(guī)則是，為每一個數(shù)據(jù)表創(chuàng)建一個主鍵。

第二范式（2NF）

第二范式規(guī)定，數(shù)據(jù)表必須符合第一范式，并且所有字段與主鍵之間不存在部分依賴關(guān)系。

考慮顧客與訂單之間的關(guān)系，你可能會想要存儲顧客 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)
    );

該表符合第一范式，因為它滿足第一范式的所有規(guī)則。表中的主鍵有 CUST_ID 和 ORDER_ID。二者一起作為主鍵，我們假定同一個顧客不會購買相同的東西。

然而，該表不符合第二范式，因為表中的字段和主鍵之間存在部分依賴關(guān)系。CUST_NAME 依賴于 CUST_ID，而 CUST_NAME 和所購物品之間沒有直接的聯(lián)系。訂單明細和購買日期依賴于 ORDER_ID，但是他們并不依賴于 CUST_ID，因為 CUST_ID 和 ORDER_DETAIL 以及 SALE_DATE 之間并不存在聯(lián)系。

要使該表遵守第二范式，你需要將其分為三個數(shù)據(jù)表。

首先，創(chuàng)建如下的數(shù)據(jù)表來保存客戶詳情：

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

接著創(chuàng)建一個表來存儲每個訂單的詳細信息：

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

最后，創(chuàng)建一個表來存儲 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）

一個數(shù)據(jù)表符合第三范式，當其滿足：

• 符合第二范式；
• 所有的非主鍵字段都依賴于主鍵；

非主鍵字段之間的依賴關(guān)系存在于數(shù)據(jù)之中。例如下表中，街道（street）、城市（city）和省份（state）顯然與郵政編碼（zip Code）之間存在密不可分的關(guān)系。

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

郵政編碼和地址之間的關(guān)系稱作傳遞相關(guān)性（transitive dependency）。要使得數(shù)據(jù)表符合第三范式，需要將街道、城市、省份等字段移到另一張表中，可以稱其為 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)
    );

移除傳遞相關(guān)性可以起到事半功倍的效果。首先是數(shù)據(jù)冗余度降低了，數(shù)據(jù)庫體積因此縮小。第二個好處是保證數(shù)據(jù)完整性。當重復數(shù)據(jù)改變的時候，很有可能只更新部分數(shù)據(jù)，尤其是當其分布在數(shù)據(jù)庫的各個地方的情況下。例如，如果地址和郵政編碼分別存儲在三個或者四個不同的數(shù)據(jù)表中，那么任何對郵編的改變，都需要對這三個或者四個表同時進行更改。