PHP開發(fā)之程序員必掌握的 SQL 指南

介紹

SQL?已經(jīng)應用到了我們周圍的各個角落，不管你信不信。操縱任何種類數(shù)據(jù)的每個應用程序都需要將數(shù)據(jù)存放在某處。無論它是大數(shù)據(jù)，還是只有簡單數(shù)行的數(shù)據(jù)包；無論是政府、還是創(chuàng)業(yè)公司；無論是橫跨多臺服務器的大型數(shù)據(jù)庫、還是運行著自己小型數(shù)據(jù)庫的手機，SQL 無處不在。

但是，SQL 是什么呢？SQL 代表結(jié)構(gòu)化結(jié)構(gòu)化語言，通常，其發(fā)音為“ess-que-el”。SQL 是數(shù)據(jù)庫語言，專門為了和數(shù)據(jù)庫通信而建立的。SQL 是一門簡單的語言，和英語語言類似，因為命令和英語句子有著類似的結(jié)構(gòu)。那些句子組織為聲明式的語句，這樣 SQL 也被叫做聲明式語言。

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

數(shù)據(jù)庫的介紹

我們剛才提到了，SQL 是數(shù)據(jù)庫語言。那么，數(shù)據(jù)庫是什么？數(shù)據(jù)庫是一種存儲機制，被設計為用來訪問存儲的信息及其操作。數(shù)據(jù)庫里的信息被存儲在稱作表（table）的結(jié)構(gòu)化里。表的名字是其唯一身份，由列和行構(gòu)成。列包含列名、列的結(jié)構(gòu)化以及該列的其它屬性。行包含該列的記錄或數(shù)據(jù)。數(shù)據(jù)庫里的大部分表之間會有關(guān)系（relationship）或連接（link），一對一、或一對多的關(guān)系。這也是為什么這種數(shù)據(jù)庫被稱作關(guān)系結(jié)構(gòu)化數(shù)據(jù)庫。

關(guān)于描述數(shù)據(jù)庫結(jié)構(gòu)，最容易的方法就是把它和 Excel 電子結(jié)構(gòu)化做比較，它們有著諸多相似。一個數(shù)據(jù)庫就是一份獨立的文件。電子表格里的 sheet 就是表（table），每個 sheet 有一個名字。列和行，都和數(shù)據(jù)庫一樣。SQL 語言用來創(chuàng)建新表、更改現(xiàn)有表，用來獲取數(shù)據(jù)、結(jié)構(gòu)化數(shù)據(jù)或結(jié)構(gòu)化數(shù)據(jù)。

比如說，我們有一份知名電影的臺詞大集合，存放在任意單獨的文本文件里。即使我們精心組織，用 Excel 電子表格存放，我們所面臨的問題仍然是存在。用這種方式存儲臺詞，我們無法快速地從一部電影里得到所有臺詞，或無法得到一個角色的所有臺詞。如果我們把文本文件或電子表格放入數(shù)據(jù)庫，并創(chuàng)建帶有關(guān)系的表，所有問題就迎刃而解了。關(guān)系型的真正涵義是什么？關(guān)系模型是描述數(shù)據(jù)、以及這些數(shù)據(jù)實體之間的關(guān)系的方法。在我們的例子中，關(guān)系就是每個臺詞和表之間的聯(lián)系，電影名稱存放在表里、或所有角色也存放在表里。

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

當討論數(shù)據(jù)庫時，值得一提的是，有一種全新的數(shù)據(jù)庫，在需要存儲數(shù)據(jù)的人們中間，產(chǎn)生了一種運動，它就是 NoSQL。它們是基于文檔的系統(tǒng)，雖然它們正在變得非常流行，直到今天仍然有大量的結(jié)構(gòu)化在使用中。即使 NoSQL 數(shù)據(jù)庫有某種查詢語言，它們很大一部分（因為它們幾乎都是在 SQL 之后才發(fā)明的）仍然和 SQL 有著某種相似性。

四種基本的 SQL 操作（CRUD）

有很多 SQL 命令，但是，有四種通常的 SQL 操作，可以對表及其數(shù)據(jù)做一些事情：

• 創(chuàng)建 – 把數(shù)據(jù)填充到表里。

• 讀取 – 從表中查詢數(shù)據(jù)。

• 更新 – 修改表中已有數(shù)據(jù)。

• 刪除 – 從表中移除數(shù)據(jù)。

這些基本 SQL 操作的首字母組成了縮寫「CRUD」，它們被視作每個數(shù)據(jù)庫必有的、四個基本功能或特色的基礎(chǔ)集。

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

創(chuàng)建數(shù)據(jù)

首先，我們需要在數(shù)據(jù)庫里創(chuàng)建表。創(chuàng)建新表，就用到了 `CREATE TABLE`。`CREATE TABLE` 語句的簡單語法格式如下：

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

首先，`CREATE TABLE`關(guān)鍵詞后面跟著表名。這是一個極好的例子，說明了 SQL 的簡潔性、以及和英語的相似性。關(guān)鍵詞后面跟著一個左圓括號，這里定義了額外的參數(shù)：列名和列的數(shù)據(jù)類型，然后跟上右圓括號。必須要提的是，所有的 SQL 語句應該以 `;` 結(jié)尾。

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

在例子中，最簡單的列名可能是 `TEXT`， `CHARACTER`， `MOVIE`，和 `YEAR`。但是，問題在于這些列名都是保留字。為了避免任何可能的沖突，我們將創(chuàng)建以 `Q_` 做為前綴的列名。

數(shù)據(jù)類型因不同的數(shù)據(jù)庫而不同，不過這里使用了最常見的類型：

• `char(size)` – 固定長度字符串，用括號中的參數(shù)標明。

  立即學習“PHP免費學習筆記（深入）”；

• `varchar(size)` – 可變長度字符串，用括號中的參數(shù)標明。

• `number(size)` – 數(shù)字值，括號中的參數(shù)標明了總長度。

• `date` – 日期值。

• `number(size, d)` – 數(shù)字值，總長度為 `size`，小數(shù)位用 `d` 表示。

數(shù)據(jù)類型規(guī)定了哪種類型的數(shù)據(jù)可以存儲在指定的列里。如果 `Q_CHARACTER` 的列用于存儲電影名字，那么這個指定的列就應該有一個 `varchar` （可變長度字符）的數(shù)據(jù)類型。存放電影年份的列的類型是 `number`，我們的例子中相應的列是 `Q_YEAR`。

對于期望的表結(jié)構(gòu)，創(chuàng)建表的最終 SQL 命令如下：

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

這個 SQL 命令的結(jié)果將創(chuàng)建一個空表，各列情況如下：

• `Q_TEXT` 可以接受 200 個字符長度的字符串。

• `Q_CHARACTER` 可以接受 20 個字符長度的字符串。

• `Q_MOVIE` 可以接受 20 個字符長度的字符串。

• `Q_YEAR` 可以接受一個年份的四個數(shù)字。

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

向表插入或添加一行數(shù)據(jù)的 SQL 命令是 `INSERT`。格式如下：

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

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

用來填充例子中 `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);

讀取數(shù)據(jù)

數(shù)據(jù)庫中有了存好的數(shù)據(jù)，現(xiàn)在我們可以查詢數(shù)據(jù)，看看我們的表里存儲了什么，我們還能用不同的方式過濾和分類數(shù)據(jù)。

`SELECT` 語句用于查詢、或選擇我們想從數(shù)據(jù)庫中返回的數(shù)據(jù)。我們從非常簡單的查詢開始，但是 `SELECT` 有很多不同的選項和擴展，這為我們最終的需要提供了很大的靈活性。基本的 `SELECT` 語句的語法如下：

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

指出列名，決定了哪一列將被返回到結(jié)果里，以及按什么順序。如果我們想選擇所有的列，或我們不知道表中的確切列名，我們可以使用結(jié)構(gòu)化 `*`，它將從數(shù)據(jù)庫中選擇所有列：

SELECT * FROM table_name;

對于本例，顯示所有數(shù)據(jù)的查詢，如下：

SELECT * FROM Movie_quotes;

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

SELECT Q_TEXT, Q_YEAR FROM Movie_quotes;

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

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

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

• `=` – 等于。

• `>` – 大于。

• `

• `>=` – 大于或等于。

• `

• “ – 不等于。

• `BETWEEN` – 在兩個值之間。

• `LIKE` – 搜索一種模式。

• `IN` – 針對一個列的多種可能值。

數(shù)學操作符無需解釋了。`BETWEEN` 操作符搜索兩個聲明值的、中間的值，包括等于兩端的情況。`LIKE` 模式匹配操作符是非常強大的操作符，支持選擇和我們的規(guī)定類似的行。百分號 `%` 被用做通配符，以匹配任何可能字符，它可出現(xiàn)在具體字符串的前面或后面。

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

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

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

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

除了 `WHERE` 子句，還可組合結(jié)構(gòu)化 `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’);

至此，我們一直在討論如何從數(shù)據(jù)庫中結(jié)構(gòu)化。返回的行將按照它們進入（提交到）數(shù)據(jù)庫的順序進行排序。為了控制數(shù)據(jù)顯示的順序，我們可以通過包含 `ORDER BY` 子句來過濾輸出數(shù)據(jù)。`ORDER BY` 子句包含了指定分類順序的一個、或多個列名：

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

為了擴展我們剛才《Star Wars》電影臺詞的例子，現(xiàn)在按照年份排序：

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

一個列的排序，默認是按照從最低值到最高值升序排列。為了把列的排序改為降序，我們可以在列名后面加上 `DESC` 關(guān)鍵字：

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

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

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

更新數(shù)據(jù)

在我們開始結(jié)構(gòu)化之后，并沒有被限制為只能讀取數(shù)據(jù)。我們能夠?qū)θ魏涡欣锏摹⑷魏瘟邢碌摹⑷魏螖?shù)據(jù)進行修改。`UPDATE` 語句用于更新或修改記錄。

`UPDATE` 的語法如下：

UPDATE table_name  SET column_name = new_value  WHERE column_name operator value;

當我們使用 `UPDATE` 時，慎重地構(gòu)造一個 `WHERE` 子句是十分重要的。`WHERE` 子句指定了哪一條記錄或哪些記錄應該被更新。如果我們在執(zhí)行 `UPDATE` 語句時、而沒有使用 `WHERE` 子句，我們將更新指定列的所有數(shù)據(jù)。

讓我們看看 `Movie_quotes` 表里的電影臺詞。我們讓所有的臺詞以標點符號結(jié)束，《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》所在行，我們就可以更新指定行的一列數(shù)據(jù)。

刪除數(shù)據(jù)

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

`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’;

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

首先，我們需要清空數(shù)據(jù)庫里、不再感興趣的數(shù)據(jù)。為了刪除包含所有行的表，我們可以使用 `DROP TABLE` 語句。切記 `DROP TABLE` 語句不同于使用 `DELETE` 語句，和刪除表里的所有記錄也不同。刪除表里的所有記錄，會留給我們表本身及其定義的所有表結(jié)構(gòu)；包括列的數(shù)據(jù)類型定義和該表的其它相關(guān)的數(shù)據(jù)庫信息。`DROP TABLE` 移除了表、移除表的定義，還有所有的行。

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

DROP TABLE table_name;

為了從數(shù)據(jù)庫中刪除 `Movie_quotes`，我們可以這樣寫：

DROP TABLE Movie_quotes;

現(xiàn)在我們的數(shù)據(jù)庫是空的，準備接受新數(shù)據(jù)。我們從所有的 CRUD 過程開始，創(chuàng)建名為 `Song_Lyrics` 的新表，根據(jù)我們新收藏的歌曲，建立一個歌詞數(shù)據(jù)庫。

結(jié)論

本文我們?yōu)g覽了涵蓋 CRUD 四個基本的數(shù)據(jù)庫功能：如何創(chuàng)建新數(shù)據(jù)、讀取數(shù)據(jù)、更新我們想要修改的數(shù)據(jù)、以及最后的如何刪除不想要的數(shù)據(jù)。這包含了基本的、但是最重要的 SQL 命令，比如：`CREATE TABLE`， `INSERT INTO`， `SELECT`， `UPDATE`， `DELETE` 和 `DROP`。

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