PHP開發之程式設計師必掌握的 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」。它有四列，一個列表示台詞文字、一個列表示說台詞的演員角色，一個表示電影，還有年份。我們收錄了八句電影台詞，我們的範例表看起來像是這樣：

我會回來的終結者終結者1984#我發現你缺乏信仰令人不安。 1983# ＃＃做。或不這樣做。沒有嘗試。為了阿甘正傳if#奔跑吧，福瑞斯特！奔跑吧！

當討論資料庫時，值得一提的是，有一個全新的資料庫，在需要儲存資料的人中間，產生了一種運動，它就是 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 时，本文中的基本知识应该能为他们开个好头。

Movie_quotes
#Q_TEXT	##Q_CHARACTER	Q_MOVIE	Q_YEAR
#星際大戰	1977		這是一個陷阱！
	永遠不要告訴我幾率。		#安息吧
1994		我媽總是說：L	就像一盒巧克力。

以上是PHP開發之程式設計師必掌握的 SQL 指南的詳細內容。更多資訊請關注PHP中文網其他相關文章！