每個新手程式設計師必看的 SQL 指南

介紹

SQL 已經應用到了我們周圍的各個角落，不管你信不信。操縱任何種類資料的每個應用程式都需要將資料存放在某處。無論它是大資料，還是隻有簡單數行的資料包；無論是政府、還是創業公司；無論是橫跨多臺伺服器的大型資料庫、還是執行著自己小型資料庫的手機，SQL 無處不在。

但是，SQL 是什麼呢？SQL 代表結構化查詢語言，通常，其發音為“ess-que-el”。SQL 是資料庫語言，專門為了和資料庫通訊而建立的。SQL 是一門簡單的語言，和英語語言類似，因為命令和英語句子有著類似的結構。那些句子組織為宣告式的語句，這樣 SQL 也被叫做宣告式語言。

在視覺化地編寫 SQL 查詢語句方面，已經有很多可用的工具了，為什麼還要學習一個全新的語言呢？當你用某些 SQL 工具時，重要的是理解 SQL 語言、理解視覺化工具正在做什麼、以及為什麼那樣做。有時候，需要手動寫一些 SQL 語句，不僅因為這是最快的方法，而且這更強大、經常是完成預定目標的唯一方法。

資料庫的介紹

我們剛才提到了，SQL 是資料庫語言。那麼，資料庫是什麼？資料庫是一種儲存機制，被設計為用來訪問儲存的資訊及其操作。資料庫裡的資訊被儲存在稱作表（table）的物件裡。表的名字是其唯一身份，由列和行構成。列包含列名、列的資料型別以及該列的其它屬性。行包含該列的記錄或資料。資料庫裡的大部分表之間會有關係（relationship）或連線（link），一對一、或一對多的關係。這也是為什麼這種資料庫被稱作關係模型資料庫。

關於描述資料庫結構，最容易的方法就是把它和 Excel 電子表格做比較，它們有著諸多相似。一個資料庫就是一份獨立的檔案。電子表格裡的 sheet 就是表（table），每個 sheet 有一個名字。列和行，都和資料庫一樣。SQL 語言用來建立新表、更改現有表，用來獲取資料、更新資料或刪除資料。

比如說，我們有一份知名電影的臺詞大集合，存放在任意單獨的文字檔案裡。即使我們精心組織，用 Excel 電子表格存放，我們所面臨的問題仍然是存在。用這種方式儲存臺詞，我們無法快速地從一部電影裡得到所有臺詞，或無法得到一個角色的所有臺詞。如果我們把文字檔案或電子表格放入資料庫，並建立帶有關係的表，所有問題就迎刃而解了。關係型的真正涵義是什麼？關係模型是描述資料、以及這些資料實體之間的關係的方法。在我們的例子中，關係就是每個臺詞和表之間的聯絡，電影名稱存放在表裡、或所有角色也存放在表裡。

下面是一個簡化處理的例子，只有一個表做示例，表名叫「Movie_quotes」。它有四列，一個列表示臺詞文字、一個列表示說臺詞的演員角色，一個表示電影，還有年份。我們收錄了八句電影臺詞，我們的示例表看起來像是這個樣子：

Movie_quotes
Q_TEXT	Q_CHARACTER	Q_MOVIE	Q_YEAR
I’ll be back	The Terminator	The Terminator	1984
I find your lack of faith disturbing.	Darth Vader	Star Wars	1977
It’s a trap!	Admiral Ackbar	Star Wars	1983
Never tell me the odds.	Han Solo	Star Wars	1980
Do. Or do not. There is no try.	Yoda	Star Wars	1980
Stupid is as stupid does.	Forrest Gump	Forrest Gump	1994
My mama always said: Life was like a box of chocolates.You never know what you’re gonna get.	Forrest Gump	Forrest Gump	1994
Run, Forrest! Run!	Jenny Curran	Forrest Gump	1994

當討論資料庫時，值得一提的是，有一種全新的資料庫，在需要儲存資料的人們中間，產生了一種運動，它就是 NoSQL。它們是基於文件的系統，雖然它們正在變得非常流行，直到今天仍然有大量的關係型資料庫在使用中。即使 NoSQL 資料庫有某種查詢語言，它們很大一部分（因為它們幾乎都是在 SQL 之後才發明的）仍然和 SQL 有著某種相似性。

四種基本的 SQL 操作（CRUD）

有很多 SQL 命令，但是，有四種通常的 SQL 操作，可以對錶及其資料做一些事情：

• 建立 – 把資料填充到表裡。
• 讀取 – 從表中查詢資料。
• 更新 – 修改表中已有資料。
• 刪除 – 從表中移除資料。

這些基本 SQL 操作的首字母組成了縮寫「CRUD」，它們被視作每個資料庫必有的、四個基本功能或特色的基礎集。

通過介紹基本特色，我們將會介紹基本的、以及最重要的 SQL 命令：`CREATE`, `INSERT`, `SELECT`, `UPDATE`, `DELETE`, and `DROP`。

建立資料

首先，我們需要在資料庫裡建立表。建立新表，就用到了 `CREATE TABLE`。`CREATE TABLE` 語句的簡單語法格式如下：

CREATE TABLE table_name
(column_1 data_type,
column_2 data_type,
column_3 data_type);

首先，`CREATE TABLE`關鍵詞後面跟著表名。這是一個極好的例子，說明了 SQL 的簡潔性、以及和英語的相似性。關鍵詞後面跟著一個左圓括號，這裡定義了額外的引數：列名和列的資料型別，然後跟上右圓括號。必須要提的是，所有的 SQL 語句應該以 `;` 結尾。

需要遵守的規則並不多。表名和列名必須以字母打頭，後面可以跟上字母、數字、或下劃線。它們的字元長度不能超過 30 個。用 SQL 保留字做為表名或列名（比如 `select`, `create`, `insert` 等）是被禁止的。

在例子中，最簡單的列名可能是 `TEXT`， `CHARACTER`， `MOVIE`，和 `YEAR`。但是，問題在於這些列名都是保留字。為了避免任何可能的衝突，我們將建立以 `Q_` 做為字首的列名。

資料型別因不同的資料庫而不同，不過這裡使用了最常見的型別：

• `char(size)` – 固定長度字串，用括號中的引數標明。
• `varchar(size)` – 可變長度字串，用括號中的引數標明。
• `number(size)` – 數字值，括號中的引數標明瞭總長度。
• `date` – 日期值。
• `number(size, d)` – 數字值，總長度為 `size`，小數位用 `d` 表示。

資料型別規定了哪種型別的資料可以儲存在指定的列裡。如果 `Q_CHARACTER` 的列用於儲存電影名字，那麼這個指定的列就應該有一個 `varchar` （可變長度字元）的資料型別。存放電影年份的列的型別是 `number`，我們的例子中相應的列是 `Q_YEAR`。

對於期望的表結構，建立表的最終 SQL 命令如下：

CREATE TABLE Movie_quotes
(‘Q_TEXT’ varchar(200),
‘Q_CHARACTER’ varchar(20),
‘Q_MOVIE’ varchar(20),
‘Q_YEAR’ number(4));

這個 SQL 命令的結果將建立一個空表，各列情況如下：

• `Q_TEXT` 可以接受 200 個字元長度的字串。
• `Q_CHARACTER` 可以接受 20 個字元長度的字串。
• `Q_MOVIE` 可以接受 20 個字元長度的字串。
• `Q_YEAR` 可以接受一個年份的四個數字。

接下來，用我們的電影臺詞資料填充這張表。有很多可用的 GUI 工具，來管理資料庫中的表和資料。不過，寫一個 SQL 指令碼常常更快，該指令碼基本上是 SQL 命令的集合，將被順序執行。當你需要用大量資料填充表時，這種方式尤為方便。

向表插入或新增一行資料的 SQL 命令是 `INSERT`。格式如下：

INSERT INTO table_name
(column_1, column_2, ... column_n)
VALUES (value_1, value_2, ... value_n);

為了向表插入一行資料， `INSERT` 關鍵字跟著 `INTO` 關鍵字和表名。然後是列名，放在圓括號裡，用逗號隔開，這是可選的，但是，指明要插入的列，以確保正確的資料插入相應的列，這是一種良好實踐。最後一部分，用 `VALUES` 關鍵字定義了要插入的那些資料，資料列表以圓括號結束。請注意，字串應該放在單引號裡，數字不應如此。

用來填充例子中 `Movie_quotes` 表的 SQL 指令碼，如下：

INSERT INTO Movie_quotes
(Q_TEXT, Q_CHARACTER, Q_MOVIE, Q_YEAR)
VALUES ('I’ll be back', 'The Terminator', 'The Terminator', 1984);
INSERT INTO Movie_quotes
(Q_TEXT, Q_CHARACTER, Q_MOVIE, Q_YEAR)
VALUES ('I find your lack of faith disturbing.', 'Darth Vader', 'Star Wars', 1977);
INSERT INTO Movie_quotes
(Q_TEXT, Q_CHARACTER, Q_MOVIE, Q_YEAR)
VALUES ('It’s a trap!', 'Admiral Ackbar', 'Star Wars', 1983);
INSERT INTO Movie_quotes
(Q_TEXT, Q_CHARACTER, Q_MOVIE, Q_YEAR)
VALUES ('Never tell me the odds.', 'Han Solo', 'Star Wars', 1980);
INSERT INTO Movie_quotes
(Q_TEXT, Q_CHARACTER, Q_MOVIE, Q_YEAR)
VALUES ('Do. Or do not. There is no try.', 'Yoda', 'Star Wars', 1980);
INSERT INTO Movie_quotes
(Q_TEXT, Q_CHARACTER, Q_MOVIE, Q_YEAR)
VALUES ('Stupid is as stupid does.', 'Forrest Gump', 'Forrest Gump', 1994);
INSERT INTO Movie_quotes
(Q_TEXT, Q_CHARACTER, Q_MOVIE, Q_YEAR)
VALUES ('My mama always said: Life was like a box of chocolates. You never know what you’re gonna get.', 'Forrest Gump', 'Forrest Gump', 1994);
INSERT INTO Movie_quotes
(Q_TEXT, Q_CHARACTER, Q_MOVIE, Q_YEAR)
VALUES ('Run, Forrest! Run!', 'Jenny Curran', 'Forrest Gump', 1994);

讀取資料

資料庫中有了存好的資料，現在我們可以查詢資料，看看我們的表裡儲存了什麼，我們還能用不同的方式過濾和分類資料。

`SELECT` 語句用於查詢、或選擇我們想從資料庫中返回的資料。我們從非常簡單的查詢開始，但是 `SELECT` 有很多不同的選項和擴充套件，這為我們最終的需要提供了很大的靈活性。基本的 `SELECT` 語句的語法如下：

SELECT column_1, column_1, ... column_n
FROM table_name;

指出列名，決定了哪一列將被返回到結果裡，以及按什麼順序。如果我們想選擇所有的列，或我們不知道表中的確切列名，我們可以使用萬用字元 `*`，它將從資料庫中選擇所有列：

SELECT * FROM table_name;

對於本例，顯示所有資料的查詢，如下：

SELECT * FROM Movie_quotes;

僅僅顯示電影臺詞、年份的查詢，如下：

SELECT Q_TEXT, Q_YEAR FROM Movie_quotes;

有時候我們不想從表中返回所有資料。當表中有大量資料、或我們在搜尋匹配某些標準的特定資料時，就屬於這種情況。對此，我們可以使用 `WHERE` 語句。`WHERE` 語句將過濾記錄，限制從資料庫中獲取哪些記錄、以滿足具體定義的標準：

SELECT column_1, column_1, ... column_n
FROM table_name
WHERE column_name operator value;

注意，`WHERE` 語句是可選的，但是如果我們決定用到它，下面的操作符是可用的：

• `=` – 等於。
• `>` – 大於。
• `<` – 小於。
• `>=` – 大於或等於。
• `<=` – 小於或等於。
• `<>` – 不等於。
• `BETWEEN` – 在兩個值之間。
• `LIKE` – 搜尋一種模式。
• `IN` – 針對一個列的多種可能值。

數學操作符無需解釋了。`BETWEEN` 操作符搜尋兩個宣告值的、中間的值，包括等於兩端的情況。`LIKE` 模式匹配操作符是非常強大的操作符，支援選擇和我們的規定類似的行。百分號 `%` 被用做萬用字元，以匹配任何可能字元，它可出現在具體字串的前面或後面。

例如，為了得到來自電影《Stars Wars》中的臺詞，我們可以這樣寫：

SELECT * FROM Movie_quotes
WHERE Q_MOVIE = ‘Star Wars’;

請注意，`WHERE` 語句是大小寫敏感的，下面的 SQL 語句將不會返回結果：

SELECT * FROM Movie_quotes
WHERE Q_MOVIE = ‘STAR WARS’;

除了 `WHERE` 子句，還可組合邏輯運算子 `AND` 和 `OR`。如果我們對相同列使用多個 `AND` 邏輯操作符，那麼我們應該考慮使用 `IN` 子句替代。

做為示例，我們返回來自電影《Star Wars》和《The Terminator》中的所有電影臺詞：

SELECT * FROM Movie_quotes
WHERE Q_MOVIE = ‘Star Wars’ AND Q_MOVIE = ‘The Terminator’;

就上面的例子，更好的寫法就是使用 `IN` 語句替代：

SELECT * FROM Movie_quotes
WHERE Q_MOVIE IN (‘Star Wars’, ‘The Terminator’);

至此，我們一直在討論如何從資料庫中過濾資料。返回的行將按照它們進入（提交到）資料庫的順序進行排序。為了控制資料顯示的順序，我們可以通過包含 `ORDER BY` 子句來過濾輸出資料。`ORDER BY` 子句包含了指定分類順序的一個、或多個列名：

SELECT column_1, column_1, ... column_n
FROM table_name
WHERE column_name operator value
ORDER BY column_name;

為了擴充套件我們剛才《Star Wars》電影臺詞的例子，現在按照年份排序：

SELECT * FROM Movie_quotes
WHERE Q_MOVIE = ‘Star Wars’
ORDER BY Q_YEAR;

一個列的排序，預設是按照從最低值到最高值升序排列。為了把列的排序改為降序，我們可以在列名後面加上 `DESC` 關鍵字：

SELECT * FROM Movie_quotes
WHERE Q_MOVIE = ‘Star Wars’
ORDER BY Q_YEAR DESC;

`ORDER BY` 語句不限於單個列。你可以包含逗號分隔的、列的清單來排序。返回的行將根據第一個指定列，然後按順序根據接下來指定的列排序。切記，用來排序的列不必包含在被選擇列的清單裡。我們可以像這樣來寫查詢：

SELECT Q_TEXT, Q_CHARACTER, Q_MOVIE FROM Movie_quotes
WHERE Q_MOVIE = ‘Star Wars’
ORDER BY Q_YEAR DESC;

更新資料

在我們開始插入資料之後，並沒有被限制為只能讀取資料。我們能夠對任何行裡的、任何列下的、任何資料進行修改。`UPDATE` 語句用於更新或修改記錄。

`UPDATE` 的語法如下：

UPDATE table_name
SET column_name = new_value
WHERE column_name operator value;

當我們使用 `UPDATE` 時，慎重地構造一個 `WHERE` 子句是十分重要的。`WHERE` 子句指定了哪一條記錄或哪些記錄應該被更新。如果我們在執行 `UPDATE` 語句時、而沒有使用 `WHERE` 子句，我們將更新指定列的所有資料。

讓我們看看 `Movie_quotes` 表裡的電影臺詞。我們讓所有的臺詞以標點符號結束，《The Terminator》除外。對於如何使用 `UPDATE` 語句，這是一個極好的例子：

UPDATE Movie_quotes
SET Q_TEXT = ‘I’ll be back!’
WHERE Q_MOVIE = ‘The Terminator’;

之前解釋了，如果我們不小心遺漏了 `WHERE` 子句，或我們故意把所有的臺詞行更新為「I’ll be back!」。通過單單選中電影《The terminator》所在行，我們就可以更新指定行的一列資料。

刪除資料

當資料庫被大量使用時，從資料庫中移除陳舊的資料，遲早會變得有必要。我們能夠只刪除表中的一些行、或刪除整個表。

`DELETE` 語句用於刪除表中的行。該命令的語法如下：

DELETE FROM table_name
WHERE column_name operator value;

重申，和 `UPDATE` 語句一樣，`WHERE` 子句指定了哪一條記錄或哪些記錄應該被刪除。如果沒有指定 `WHERE` 子句，所有的行和列將被刪除：

DELETE FROM Movie_quotes;

假設我們不再喜歡電影《Forrest Gump》了，想從電影中刪除其臺詞。為了從電影中刪除所有臺詞，我們可以編寫如下 SQL 命令：

DELETE FROM Movie_quotes
WHERE Q_MOVIE = ‘Forrest Gump’;

最終，我們有了足夠多的電影。我們對電影臺詞不再感興趣了，我們想把興趣移到音樂上。我們開始收集歌詞。根據我們目前所學到的 SQL 知識，修改資料庫是非常簡單的。

首先，我們需要清空資料庫裡、不再感興趣的資料。為了刪除包含所有行的表，我們可以使用 `DROP TABLE` 語句。切記 `DROP TABLE` 語句不同於使用 `DELETE` 語句，和刪除表裡的所有記錄也不同。刪除表裡的所有記錄，會留給我們表本身及其定義的所有表結構；包括列的資料型別定義和該表的其它相關的資料庫資訊。`DROP TABLE` 移除了表、移除表的定義，還有所有的行。

`DROP TABLE` 語句的語法如下：

DROP TABLE table_name;

為了從資料庫中刪除 `Movie_quotes`，我們可以這樣寫：

DROP TABLE Movie_quotes;

現在我們的資料庫是空的，準備接受新資料。我們從所有的 CRUD 過程開始，建立名為 `Song_Lyrics` 的新表，根據我們新收藏的歌曲，建立一個歌詞資料庫。

結論

本文我們瀏覽了涵蓋 CRUD 四個基本的資料庫功能：如何建立新資料、讀取資料、更新我們想要修改的資料、以及最後的如何刪除不想要的資料。這包含了基本的、但是最重要的 SQL 命令，比如：`CREATE TABLE`， `INSERT INTO`， `SELECT`， `UPDATE`， `DELETE` 和 `DROP`。

這些基本的 SQL 命令支援大量的資料管理，但是每個介紹到的命令都有很多選項和額外的功能，有些是本文沒有介紹的，要注意這一點。總之，當 SQL 開發人員新手在開始資料庫工作、以及使用一門新語言 SQL 時，本文中的基本知識應該能為他們開個好頭。