SQL: 結(jié)構(gòu)化查詢語言（Structured Query Language）

SQL 是由 IBM 公司首先開發(fā)產(chǎn)生飞主，它是關(guān)系型數(shù)據(jù)庫最早出現(xiàn)的商用語言之一赡突。1974年，IBM 公司 San Jose, California 研究實驗室的 D.D.Chamberlin 和 R.F. Boyce 研制出一套規(guī)范語言 SEQUEL （Structured English Query Language），并在1976年11月的 IBM Journal of R&D 上公布新版本的 SQL （叫 SWQUEL/2）睬隶。

• 1980年改名為 SQL。
• 1986年页徐，美國ANSI采用SQL作為關(guān)系數(shù)據(jù)庫管理系統(tǒng)的標準語言（ANSI X3. 135-1986）苏潜，后為國際標準化組織（ISO）采納為國際標準。
• 1989年变勇，美國ANSI采納在ANSI X3.135-1989報告中定義的關(guān)系數(shù)據(jù)庫管理系統(tǒng)的SQL標準語言做粤，稱為ANSI SQL 89惧盹，該標準替代ANSI X3.135-1986版本丽柿。
• 1999年恰聘，SQL 繼續(xù)擴展并劃分出核心功能并根據(jù)這些功能劃分出不同的封裝。
• 2003年豌熄，SQL 繼續(xù)擴展授嘀，并可以允許 XML 支持。
• 2011年锣险，SQL 繼續(xù)擴展蹄皱，改善了對臨時數(shù)據(jù)庫的支持览闰。

跟關(guān)系型數(shù)據(jù)模型不同的是，關(guān)系型數(shù)據(jù)庫主要處理對象的集合巷折，即集合中不能出現(xiàn)有重復的對象压鉴；而 SQL 主要處理的是多集，即 SQL 允許一種數(shù)據(jù)關(guān)系中包含重復的對象锻拘。

SQL 為關(guān)系型數(shù)據(jù)庫提供了許多接口油吭，是一種高級的非過程化編程語言，其中包括：

• 數(shù)據(jù)定義語言 （DDL: Data Definition Language）
• 數(shù)據(jù)操縱語言 （DML: Data Manipulation Language）
• 數(shù)據(jù)控制語言 （DCL: Data Control Language）
• 事務控制語言 （TCL: transaction Control Language）

需要強調(diào)的是署拟，在SQL語言的使用過程中婉宰，我們常將關(guān)鍵字用大寫字母來表示，而其他變量名等推穷，一般用小寫字母表示或者按照其自身含義具體實現(xiàn)心包。

數(shù)據(jù)定義語言 （DDL: Data Definition Language）

1. 創(chuàng)建表 （CREATE TABLE）

使用 CREATE TABLE 可以創(chuàng)建一個新的關(guān)系模式，聲明它的表名馒铃，屬性蟹腾，還可以指定每個屬性的限制條件以及表自身的限制條件∏睿基本格式如：

CREATE TABLE table_name
    (attr_name data_type [attr constraints],
     ...,
     attr_name data_type [attr constraints],
     [table constraints]);

舉幾個例子：

CREATE TABLE Student
    (StudentID INT,
     Name VARCHAR(50),
     DoB Date,
     Email VARCHAR(100));

CREATE TABLE Course
    (No VARCHAR(20),
     Cname VARCHAR(50),
     Unit SMALLINT);

CREATE TABLE Enrol
    (StudentID INT,
     CourseNo VARCHAR(20),
     Semester VARCHAR(50),
     Status VARCHAR(50));

2. 屬性的數(shù)據(jù)類型

常用的屬性數(shù)據(jù)類型有4中娃殖，分別是數(shù)值型（Numeric）、字符串型（String）议谷、日期與時間（Date and time）以及布爾型（Boolean）炉爆。

• 數(shù)值型
  • 整型：INT，SMALLINT
  • 浮點型： FLOAT柿隙，REAL叶洞，DOUBLE PRECISION
  • 指定顯示位數(shù)：NUMERIC(i, j)鲫凶，DECIMAL(i, j)禀崖。其中 i 為精度，表示總共顯示的數(shù)字的位數(shù)螟炫；j 為范圍波附，表示小數(shù)點后顯示的數(shù)字的位數(shù)。
• 字符串型
  • CHAR(n)：n 指定屬性字符串的長度昼钻。
  • VARCHAR(n)：n 指定不定長屬性字符串允許的最大長度掸屡。
  • BIT(n)：n 指定比特串的長度。
  • BIT VARYING(n)：n 指定不定長比特串允許的最大長度然评。
• 日期與時間
  • DATE：（年仅财，月，日）/ （year, month, day）碗淌。
  • TIME：（時盏求，分抖锥，秒）/ （hour, minute, second）。
  • TIMESTAMP：包含日期和時間碎罚，秒最少精確到6位磅废，且可以限定所選的時區(qū)。
  • INTERVAL：指定一個相對值荆烈，用于增加或者減少上述三種數(shù)據(jù)類型對應的值拯勉。
• 布爾值：包含 TRUE 和 FALSE 兩個值。

在我們創(chuàng)建屬性的時候憔购，相應的會配置屬性的數(shù)據(jù)類型宫峦。根據(jù)需求的不同，使用上述的數(shù)據(jù)類型玫鸟，有時候我們也會自定義一些新的數(shù)據(jù)類型斗遏，使用 CREATE DOMAIN 語句，如：

CREATE DOMAIN ssn_type AS CHAR(9)鞋邑；

3. 屬性的約束條件

常用于 SQL 語句中的屬性約束有以下幾種：

• NOT NULL：聲明屬性的值不能為空诵次。
• DEFAULT：為屬性指定一個默認的值。
• CHECK （attr > 0）：限定當前屬性數(shù)據(jù)的值在一個范圍以內(nèi)枚碗。
• UNIQUE：確保一個屬性的值或者一個表內(nèi)屬性的獨特性逾一，即不能出現(xiàn)重復的值或者屬性。
• PRIMARY KEY：當前屬性作為主鍵肮雨，在表中唯一識別一個元組遵堵。
• FOREIGN KEY：在兩個關(guān)系表之間執(zhí)行實現(xiàn)引用完整性。
• INDEX：使用索引更快速訪問表的特定行怨规。

其中陌宿，PRIMARY KEY 和 UNIQUE 約束的屬性自帶 NOT NULL 的約束，也就是說波丰，被約束為主鍵或者獨特的屬性壳坪，必然不能為空。

如果主鍵只包含一個屬性掰烟，那么 PRIMARY KEY 可以定義在屬性后面爽蝴；如果主鍵包含多個屬性，那么 PRIMARY KEY 要定義為表約束纫骑，如：

-- PRIMARY KEY 定義在屬性后 
CREATE TABLE Course
    (No VARCHAR(20) PRIMARY KEY,
     Cname VARCHAR(50) UNIQUE,
     Unit SMALLINT not null Default 6);

-- PRIMARY KEY 定義在表約束中
CREATE TABLE Enrol
    (StudentID INT not null CHECK (StudentID>0),
     CourseNo VARCHAR(20) not null,
     Semester VARCHAR(50) not null,
     Status VARCHAR(50),
     PRIMARY KEY(StudentID, CourseNo, Semester),
     ...);

下面是指定 FOREIGN KEY 的一個例子：

CREATE TABLE Enrol
    (StudentID INT,
     CourseNo VARCHAR(20),
     Semester VARCHAR(50),
     Status VARCHAR(50),
     FOREIGN KEY(StudentID) REFERENCES Student(StudentID),
     FOREIGN KEY(CourseNo) REFERENCES Course(No));

INDEX 的例子：

CREATE TABLE Customer
    (CustomerID INT not null CHECK (CustomerID>0),
     Name VARCHAR(50) not null,
     DOB DATE not null,
     Address VARCHAR(80),
     Phone INT CHECK (Phone>0),
     PRIMARY KEY(CustomerID));

CREATE INDEX index1 ON Customer (Name, DOB);
CREATE UNIQUE INDEX index2 ON Customer (Phone);

4. 更改與刪除表

使用 ALTER TABLE 常用來更改已經(jīng)存在的表的內(nèi)容蝎亚，可以作出的更改包括：

• 修改表名

• 添加或刪除屬性

• 修改屬性的定義

• 添加或刪除表的約束

ALTER TABLE Customer ALTER COLUMN Address SET NOT NULL;

ALTER TABLE Customer ADD UNIQUE(Phone);

ALTER TABLE Customer ADD CONSTRAINT positive_id CHECK (CustomerID > 0);

ALTER TABLE Enrol ADD FOREIGN KEY(StudentID) REFERENCES Student(StudentID);

ALTER TABLE Customer ADD Email VARCHAR(100);

使用 DROP TABLE 常用于移除一個已創(chuàng)建的表

DROP TABLE Enrol;

DROP TABLE IF EXISTS Customer;

數(shù)據(jù)操縱語言 （DML: Data Manipulation Language）

 數(shù)據(jù)操縱語言主要包括四種命令：INSERT，UPDATE先馆，DELETE发框，SELECT。

1. INSERT 插入命令

INSERT 命令常用于向一個表添加新的元組或者新的項煤墙，其主要格式為：

INSERT INTO table_name
    [(attribute_name,...,attribute_name)]
    VALUES (value,...,value),...,(value,...,value);

該系列的前一篇文章里曾經(jīng)提到過關(guān)系型數(shù)據(jù)庫完整性約束的內(nèi)容：當我們執(zhí)行 INSERT梅惯，UPDATE顾患，DELETE 命令時，可能會導致違反完整性約束的情況个唧。關(guān)于完整性約束的內(nèi)容江解，可以去看這篇博客：【數(shù)據(jù)庫】數(shù)據(jù)庫入門（二）: 關(guān)系型數(shù)據(jù)庫。

違反主鍵約束

主鍵作為在一個表中獨立識別一個元組的標志徙歼，在不同的元組中犁河，其主鍵對應的值不能相同，否則違反了主鍵約束魄梯。例如在下表中：

如果在這個表中桨螺，我們再執(zhí)行以下插入命令：

INSERT INTO Student(StudentID, Name, DoB, Email)
    VALUES (456, 'Smith', '27/08/1989', 'smith@gmail.com');

即插入一個主鍵同樣為456，但其余值不同的一個新的元組酿秸。如果該元組能添加到表中灭翔，則存在兩個元組的主鍵即 StudentID 為456，那么我們通過主鍵 StudentID 并不能識別出一個獨立的元組辣苏，因此違反了主鍵約束肝箱，故上面這條插入命令不會被正確執(zhí)行。DBMS 不允許兩個主鍵值相同的元組存在于一個表中稀蟋。

違反外鍵約束

違反外鍵約束常常出現(xiàn)在多個表相互引用的情況下煌张，即在一個表中新添加的元組，其外鍵對應的值并沒有出現(xiàn)在另一個表中時退客，就會導致違反外鍵約束骏融。例如有以下兩個表：

然后我們執(zhí)行以下插入命令：

INSERT INTO Enrol(StudentID, CourseNo, Semester, Status)
    VALUES (460, 'COMP2400', '2016 S2', 'active');

可以想象這么一個現(xiàn)實情況：在某一個學期里數(shù)據(jù)庫儲存了一個學生在該學期參加某個課程的注冊信息，然而該學生卻并沒有個人記錄在學校的數(shù)據(jù)中萌狂，這種情況是非常不合理的档玻。注冊信息表必須依賴于學生信息表而存在，因此上面這條命令不能執(zhí)行成功茫藏。DBMS 不會允許一個表中存在這么一個元組：其外鍵對應的值误趴，在外鍵對應的引用表中卻不存在。

2. UPDATE 命令

UPDATE 命令常用于修改一個或者多個元組中相應屬性的值刷允，其主要格式為：

UPDATE table_name
    SET attribute_name = value,...,attribute_name = value
    [WHERE selection_condition];

3. DELETE 命令

DELETE 命令常用于從表中移除存在的元組冤留，其主要格式為：

DELETE FROM table_name
    [WHERE selection_condition];

執(zhí)行刪除命令可以根據(jù)具體的條件進行刪除操作，也可以刪除表中所有的元組树灶。

-- Delete the tuple whose studentID equals to 456.
DELETE FROM Student WHERE StudentID=456;

-- Delete all tuple in Student relation.
DELETE FROM Student;

這里需要注意區(qū)別 DELETE 和 DROP 作用是不一樣的。DELETE FROM Student 會清空 Student 表內(nèi)的所有元組糯而，執(zhí)行命令后該表依然存在天通，且為空表；而 DROP TABLE Student 不僅清空所有元組熄驼，還會刪除 Student 這個表像寒，執(zhí)行命令后該表將不再存在烘豹。

引用表之間的行為

由于關(guān)系型數(shù)據(jù)庫存在表間的相互引用關(guān)系，所以我們會希望在修改一個表中的內(nèi)容時诺祸，其具有引用關(guān)系的表中對應內(nèi)容也能夠跟隨著同步改變携悯。所以 SQL 提供了以下的關(guān)鍵字：

• NO ACTION（default）：當修改或者刪除一個存在引用關(guān)系的元組時，系統(tǒng)會拋出一個錯誤提示 error筷笨。
• CASCADE：刪除或者更新操作會隨著所有引用關(guān)系同步進行憔鬼。
• SET NULL：執(zhí)行刪除或者更新操作后，存在引用關(guān)系的元組的對應屬性會置為 NULL胃夏。
• SET DEFAULT：執(zhí)行刪除或者更新操作后轴或，存在引用關(guān)系的元組的對應屬性會置為一個指定的默認值。

以上的關(guān)鍵字常用于定義表中作為外鍵的屬性仰禀，下面是一個例子：

CREATE TABLE Enrol
    (StudentID INT,
     CourseNo VARCHAR(20),
     Semester VARCHAR(50),
     Status VARCHAR(50),
     FOREIGN KEY(StudentID) REFERENCES Student(StudentID)
         ON DELETE NO ACTION ,
     FOREIGN KEY(CourseNo) REFERENCES Course(No));

4. SELECT 命令

SELETE 命令常用于從數(shù)據(jù)庫中檢索數(shù)據(jù)照雁，其主要格式如下：

SELECT attribute_list
    FROM table_list
    [WHERE condition]
    [GROUP BY attribute_list [HAVING group_condition]]
    [ORDER BY attribute_list [ASC | DESC]];

image.gif

SELETE 后面跟的是想要顯示的屬性，如果使用星號 “*”答恶，代表要獲取指定表的所有屬性的值饺蚊。

FROM 后面跟的是所要獲取的表的名字。如果是多個表用逗號 “悬嗓，” 隔開卸勺，則取的是兩個表所有元組分別進行笛卡爾積后結(jié)合的結(jié)果。如果使用 JOIN 關(guān)鍵字烫扼，則會根據(jù)具體的 JOIN 的形式將多個表進行結(jié)合曙求，并從中選出目標屬性值。

WHERE 關(guān)鍵字后跟的是取值條件映企，條件通常為判斷某個屬性是否為某個指定值或者是否滿足數(shù)值上的大小關(guān)系等悟狱。

GROUP BY 關(guān)鍵字會按照后面指定屬性進行分組，即該屬性值相同的元組將會歸為一個組堰氓，后面可以使用 HAVING 關(guān)鍵字挤渐，其功能與 WHERE 類似，不過它指定的所劃分的組中所有元組需要滿足的條件双絮。GROUP BY 關(guān)鍵字通常會跟以下的內(nèi)置函數(shù)結(jié)合使用：

• COUNT：統(tǒng)計值不為 NULL 的條目的個數(shù)浴麻。
• AVG：求指定參數(shù)的平均值。
• MIN：求指定參數(shù)的最小值囤攀。
• MAX：求指定參數(shù)的最大值软免。
• SUM：求指定參數(shù)的總和。

ORDER BY 對檢索的內(nèi)容按照給定屬性的值進行排序焚挠，默認按照升序排序膏萧，也可以使用關(guān)鍵字 ASC 指定升序或者 DESC 指定降序。

關(guān)于 SELETE 命令的深入內(nèi)容，在下一篇文章中會進行詳細介紹榛泛，并且提供一些例子的講解蝌蹂。

【數(shù)據(jù)庫】數(shù)據(jù)庫入門（四）: SQL查詢 - SELETE的進階使用

參考資料

維基百科：https://zh.wikipedia.org/wiki/SQL

數(shù)據(jù)庫基礎(chǔ)：Fundamentals of Database Systems, 7th Edition (Global Edition), R. Elmasri and S. Navathe, 2017