SQL語法

SQL程式語言的語法是由ISO/IEC 9075標準中的ISO/IEC SC 32委員會所定義和維護的。儘管存在標準，不過SQL代碼仍然無法在不進行修改的前提下在不同的資料庫系統中直接移植。

## 語言元素

SQL語言分成了幾種要素，包括：

- 子句，是語句和查詢的組成成分。（在某些情況下，這些都是可選的。）
- 表達式，可以產生任何標量值，或由列和行的資料庫表
- 謂詞，給需要評估的SQL三值邏輯（3VL）（true/false/unknown）或布爾真值指定條件，並限制語句和查詢的效果，或改變程序流程。
- 查詢，基於特定條件檢索數據。這是SQL的一個重要組成部分。
- 語句，可以持久地影響綱要和數據，也可以控制資料庫事務、程序流程、連接、會話或診斷。
- SQL語句也包括分號（";"）語句終結符。儘管並不是每個平台都必需，但它是作為SQL語法的標準部分定義的。
- 無意義的空白在SQL語句和查詢中一般會被忽略，更容易格式化SQL代碼便於閱讀。

## 運算符

運算子｜描述｜例子

等於

不等於（許多資料庫管理系統除了支持以外還支持）｜Dept <> 'Sales'

大於｜Hire_Date > '2012-01-31'

小於｜Bonus < 50000.00

大於等於｜Dependents >= 2

小於等於｜Rate <= 0.05

BETWEEN｜在一個範圍內｜Cost BETWEEN 100.00 AND 500.00

LIKE｜字符模式匹配｜First_Name LIKE 'Will%'

IN｜等於多個可能的值之一｜DeptCode IN (101, 103, 209)

IS 或 IS NOT｜與空值（數據缺失）比較｜Address IS NOT NULL

IS NOT DISTINCT FROM｜等於值或均為空值（數據缺失）｜Debt IS NOT DISTINCT FROM - Receivables

AS｜用於在查看結果時更改欄位名稱｜SELECT employee AS 'department1'

有人也提議實現其他運算子，例如輪廓運算子（尋找那些不比任何其他記錄「糟糕」的記錄）。

### 條件（CASE）表達式

SQL在SQL-92標準中引入了`CASE/WHEN/THEN/ELSE/END`語句。通常情況下所稱的「搜索CASE語句」例子如下：

```sql
CASE WHEN n > 0
          THEN '正'
     WHEN n < 0
          THEN '负'
     ELSE '零'
END
```

SQL按照`WHEN`條件在原始碼中出現的順序進行判斷。如果原始碼中沒有指定`ELSE`表達式，SQL默認為`ELSE NULL`。SQL標準中還有一種「簡單CASE語句」，類似C語言的switch：

```sql
CASE n WHEN 1
            THEN 'one'
       WHEN 2
            THEN 'two'
       ELSE 'I cannot count that high'
END
```

該語法是隱式相等條件。通常情況下，遇到與空值比較的情況會發出警告。

對於Oracle資料庫的SQL語法，還可以用`DECODE`函數簡化：

```oracle11
SELECT DECODE(n, 1, 'one',
                 2, 'two',
                    'i cannot count that high')
FROM   some_table;
```

最後一個值是可選的，若無指定，默認為`NULL`。另外，與「簡單CASE」不同的是，Oracle的`DECODE`會認為兩個`NULL`之間相等。

## 查詢

SQL中最常見的操作是查詢，它是通過陳述性`SELECT`語句執行的。`SELECT`從一個或多個表或表達式中檢索數據。標準的`SELECT`不會對資料庫有持久影響。`SELECT`的一些非標準的實現可以有持久影響，如一些資料庫中有`SELECT INTO`語法。

查詢允許用戶描述所需的數據，將查詢計劃、查詢優化器以及執行用以產生它選取的結果的物理操作交給資料庫管理系統（DBMS）負責。

查詢包含一系列含有最終結果的欄位, 緊跟`SELECT`關鍵詞。星號（"`*`"）也可以用來指定查詢應當返回查詢表所有欄位。`SELECT`是最複雜的SQL語句，可選的關鍵詞和子句包括：

- `FROM`子句指定了選擇的數據表。`FROM`子句也可以包含`JOIN` 二層子句來為數據表的連接設置規則。
- `WHERE`子句後接一個比較謂詞以限制返回的行。`WHERE`子句僅保留返回結果里使得比較謂詞的值為True的行。
- `GROUP BY`子句用於將若干含有相同值的行合併。`GROUP BY`通常與SQL聚合函數連用，或者用於清除數據重複的行。`GROUP BY`子句要用在`WHERE`子句之後。
- `HAVING`子句後接一個謂詞來過濾從`GROUP BY`子句中獲得的結果，由於其作用於`GROUP BY`子句之上，所以聚合函數也可以放到其謂詞中。
- `ORDER BY`子句指明將哪個欄位用作排序關鍵字，以及排序順序(升序/降序)，如果無此子句，那麼返回結果的順序不能保證有序。

下面是一個返回昂貴的書籍列表的`SELECT`查詢的例子。查詢會從 Book 表中檢索所有 price 的值大於 100.00 的行。結果按 title 升序排列。選擇列表中的星號（*）表明Book表中所有欄位都包含在結果集中。

```sql
SELECT *
 FROM  Book
 WHERE price > 100.00
 ORDER BY title;
```

下面的例子演示了通過返回與每本書相關聯的書籍和作者來多表查詢、分組和聚集。

```sql
SELECT Book.title AS Title,
       count(*) AS Authors
 FROM  Book
 JOIN  Book_author
   ON  Book.isbn = Book_author.isbn
 GROUP BY Book.title;
```

輸出可能類似於下面的例子：

```
Title                  Authors
SQL Examples and Guide 4
The Joy of SQL         1
An Introduction to SQL 2
Pitfalls of SQL        1
```

在isbn是兩個表中唯一通用的列名，且名為title的列僅存在於Books表中的前提下，上述查詢可以用以下形式重寫：

```sql
SELECT title,
       count(*) AS Authors
 FROM  Book
 NATURAL JOIN Book_author
 GROUP BY title;
```

然而，許多廠商或者不支持這種方法，或者需要某些列命名約定來實現自然聯接。

SQL包含有用於計算存儲值的值的運算符和函數。SQL允許在選擇列表中使用表達式來投影數據，如下例所示，它返回成本超過100.00的書籍列表，另外一列sales_tax包含以price的6％計算的銷售稅數據。

```sql
SELECT isbn,
       title,
       price,
       price * 0.06 AS sales_tax
 FROM  Book
 WHERE price > 100.00
 ORDER BY title;
```

### 子查詢

查詢可以嵌套，以便一個查詢的結果可以通過關係運算符或聚合函數在另一個查詢中使用。嵌套查詢也稱為子查詢。雖然連接和其他表操作在許多情況下提供了計算上優越（即更快）的替代方案，但是子查詢的使用引入了在執行中會很有用或很必要的等級。在下例中，聚合函數`AVG`接收子查詢的結果作為輸入：

```sql
SELECT isbn,
       title,
       price
 FROM  Book
 WHERE price < (SELECT AVG(price) FROM Book)
 ORDER BY title;
```

子查詢可以使用外部查詢的值，在這種情況下，它被稱為相關子查詢。

自1999年以來，SQL標準允許稱為公共表表達式的命名子查詢（在IBM DB2版本2中實現之後命名和設計; Oracle把它叫做子查詢部分）。CTE還可以通過自身引用來遞歸；SQL中的分層和遞歸查詢允許樹或圖遍歷，以及更一般的不動點計算。

### 派生表

派生表是在FROM子句中引用SQL子查詢的用法。基本上，派生表是可以從中選擇或連接到的子查詢。派生表功能允許用戶將子查詢引用為表。派生表也稱為內聯視圖或子選擇。

在下例中，SQL語句涉及從初始「Book」表到派生表「sales」的連接。此派生表使用ISBN捕獲關聯的圖書銷售資訊以加入「Book」表。因此，派生表提供的結果集包含附加列（銷售的商品數量和銷售圖書的公司）：

```sql
SELECT b.isbn, b.title, b.price, sales.items_sold, sales.company_nm
FROM Book b
  JOIN (SELECT SUM(Items_Sold) Items_Sold, Company_Nm, ISBN
        FROM Book_Sales
        GROUP BY Company_Nm, ISBN) sales
  ON sales.isbn = b.isbn
```

### 空值與三值邏輯

SQL中引入了空值的概念，用來處理關係模型中缺少資訊的情況。`NULL`一詞表示空白值，是SQL中的保留詞。如果Null進行比較，例如在WHERE子句中使用「=」判斷相等，那麼會返回未知值，而SELECT語句只會返回WHERE子句條件為真（TRUE）的結果，不會返回條件為假（FALSE）或未知的結果。

「真」、「假」以及與空值直接比較時所得到的「未知」共同組成了SQL的three-valued logic。SQL所用的真值表與Kleene和Lukasiewicz三值邏輯的共同部分對應 (它們對內涵的定義不同，然而SQL沒有定義這樣的操作)。

p AND q｜p 真｜假｜未知 q｜真｜真｜假｜未知 假｜假｜假｜假 未知｜未知｜假｜未知｜p OR q｜p 真｜假｜未知 q｜真｜真｜真｜真 假｜真｜假｜未知 未知｜真｜未知｜未知

p 真｜假｜未知 q｜真｜真｜假｜未知 假｜假｜真｜未知 未知｜未知｜未知｜未知｜q｜NOT q 真｜假 假｜真 未知｜未知

然而，由於在直接比較之外的處理，在SQL中對Null的語義解釋存在爭議。如上表所示，SQL中的兩個NULL之間的直接等式比較（例如`NULL = NULL`）返回真值「未知」。這符合Null不具有值（並不是任何數據域的成員）的解釋，而是缺失資訊的占位符或「標記」。但是，在`UNION`和`INTERSECT`操作符的SQL規範中，兩個空值不相等的原則在實際上標識了null。因此，與涉及NULL的顯式比較（例如上述`WHERE`子句中的那些）的操作不同，SQL中的這些集合運算可能產生表示不確定信息的結果。在Codd的1979年提案中（該提案基本被SQL92採納），這種語義上的不一致被合理化了，他認為在集合操作中刪除重複的操作發生在 "比檢索操作求值中的相等驗證更低的細節層次上"。然而，電腦科學教授Ron van der Meyden認為「SQL標準的不一致意味著不可能將任何直觀的邏輯語義歸結為SQL中的null處理。」

另外，由於直接與空值比較會返回未知，因此SQL又提供了兩個用於測試空值的語句：`IS NULL`和`IS NOT NULL`，前者用於判斷是否為空，後者相反。SQL不明確支持全稱量化，必須將其定義為否定存在量化。還有「IS DISTINCT FROM」插入比較運算符，除非兩個操作數相等或兩者都為NULL，否則返回TRUE。同樣，`IS NOT DISTINCT FROM`定義為「NOT ( IS DISTINCT FROM )」。SQL:1999還引入了`BOOLEAN`類型變量，根據標準也可以是未知值。實踐中一些資料庫系統（例如PostgreSQL）會把 implement the BOOLEAN Unknown as a BOOLEAN NULL.

## 數據操作

資料操縱語言（DML）是SQL用於添加、更新和刪除數據的子集：

- `INSERT`添加行（正式名稱為元組）到一個現有的表，例如：

```sql
INSERT INTO example
 (field1, field2, field3)
 VALUES
 ('test', 'N', NULL);
```

- `UPDATE`修改現有的表中一些行，例如：

```sql
UPDATE example
 SET field1 = 'updated value'
 WHERE field2 = 'N';
```

- `DELETE`從表中刪除現有的行，如：

```sql
DELETE FROM example
 WHERE field2 = 'N';
```

- `MERGE`用來合併多個表的數據。它結合了`INSERT`和`UPDATE`元素。它是在SQL:2003標準中定義的；在那之前，一些資料庫也以不同的語法提供了相似的功能，又是叫做「upsert」。

```sql
 MERGE INTO table_name USING table_reference ON (condition)
 WHEN MATCHED THEN
 UPDATE SET column1 = value1 [, column2 = value2 ...]
 WHEN NOT MATCHED THEN
 INSERT (column1 [, column2 ...]) VALUES (value1 [, value2 ...])
```

## 事務控制

如果資料庫系統支持事務，那麼可用以下語句：

- `START TRANSACTION`（或`BEGIN WORK`、`BEGIN TRANSACTION`，取決於具體資料庫系統的規定）表示資料庫事務開始。
- `SAVE TRANSACTION`（或`SAVEPOINT`）命令會記錄事務本身的狀態，即保存點。

```sql
CREATE TABLE tbl_1(id int);
 INSERT INTO tbl_1(id) VALUES(1);
 INSERT INTO tbl_1(id) VALUES(2);
COMMIT;
 UPDATE tbl_1 SET id=200 WHERE id=1;
SAVEPOINT id_1upd;
 UPDATE tbl_1 SET id=1000 WHERE id=2;
ROLLBACK to id_1upd;
 SELECT id from tbl_1;
```

- `COMMIT`會令事務過程中進行數據修改正式生效。
- `ROLLBACK`會放棄上次`COMMIT`或`ROLLBACK`之後的修改，使數據恢復到前一狀態。不過一旦`COMMIT`語句結束，事務所產生的修改將無法回退。

`COMMIT`和`ROLLBACK`會中止當前事務並釋放鎖。在沒有`START TRANSACTION`或類似語句的情況下，SQL的語義與實現有關的。

下面例子展示了把一個帳戶的金額轉移到另一個帳戶上面的過程。只要表示減少和增加的兩個UPDATE語句中有一個失敗，整個事務就會回退，更改也不會保存到資料庫中。

```sql
START TRANSACTION;
 UPDATE Account SET amount=amount-200 WHERE account_number=1234;
 UPDATE Account SET amount=amount+200 WHERE account_number=2345;

IF ERRORS=0 COMMIT;
IF ERRORS<>0 ROLLBACK;
```

## 數據定義

資料定義語言（DDL）管理表和索引結構。DDL的最基本是`CREATE`、`ALTER`、`RENAME`、`DROP`和`TRUNCATE`語句：

- `CREATE`在資料庫中創建一個對象（例如一張表），舉例來說：

```sql
CREATE TABLE example(
 column1 INTEGER,
 column2 VARCHAR(50),
 column3 DATE NOT NULL,
 PRIMARY KEY (column1, column2)
);
```

- `ALTER`以不同方式修改現有對象的結構，例如向現有的表或約束添加欄位：

```sql
ALTER TABLE example ADD column4 NUMBER(3) NOT NULL;
```

- `TRUNCATE`以一種非常快速的方式刪除表中的所有數據，刪除表內的數據而不是表本身。這通常意味著後續的COMMIT操作, 即，它不能被回滾（與DELETE不同，數據不會為之後回滾而寫入日誌）。

```sql
TRUNCATE TABLE example;
```

- `DROP`刪除資料庫中的對象，通常無法挽回的，即，它不能被回滾，如：

```sql
DROP TABLE example;
```

## 數據類型

一張表中的每個欄位都要定義該欄位的類型。ANSI SQL包括下列數據類型：

### 字串

- `CHARACTER(<var>n</var>)`或`CHAR(<var>n</var>)`：寬度為n的定長字串。如果內容長度不足，則以空格填充。
- `CHARACTER VARYING(<var>n</var>)`或`VARCHAR(<var>n</var>)`：最長為n個字符的可變寬度字串。
- `NATIONAL CHARACTER(<var>n</var>)`或`NCHAR(<var>n</var>)`：支持國際字符集的固定寬度字串。
- `NATIONAL CHARACTER VARYING(<var>n</var>)`或`NVARCHAR(<var>n</var>)`：可變寬度的`NCHAR`字串。

### Bit類型

Bit是一種存儲0或1的整數類型，一個Bit值需要一個字節。

- `BIT(<var>n</var>)`：n比特Bit類型
- `BIT VARYING(<var>n</var>)`：最大長度為n的Bit類型

### 數值

- 整數：包括`SMALLINT`、`INTEGER`和`BIGINT`，可表示的數據範圍從小到大。
- 小數：包括`FLOAT`, `REAL`和`DOUBLE PRECISION`，可表示的數據範圍從小到大。
- 定點數：包括`NUMERIC(长度, 精度)` or `DECIMAL(长度, 精度)`。

定點數類型包含兩個要素：長度、精度。長度表示數字的最大個數，包括小數點左面和右面的數字。精度是非負整數，精度為零意味著數值只能是整數。以123.45為例，它的長度為5，精度為2。

SQL提供了除去小數部分、只保留整數部分的函數，叫做`TRUNC`（Informix、DB2、PostgreSQL、Oracle和MySQL）或`ROUND`（Informix、SQLite、Sybase、Oracle、PostgreSQL和Microsoft SQL Server）

### 日期與時間

- `DATE`：日期值（例如 `2011-05-03`）
- `TIME`：時間值（例如 `15:51:36`）。時間值的粒度通常是100納秒。
- `TIME WITH TIME ZONE`或`TIMETZ`：與`TIME`相同，但包含時區資訊。
- `TIMESTAMP`：時間戳，同時包含日期和時間（例如 `2011-05-03 15:51:36`）。
- `TIMESTAMP WITH TIME ZONE`或`TIMESTAMPTZ`：與`TIMESTAMP`相同，但包含時區資訊。
- `INTERVAL`

SQL提供了多個在日期時間類型和字串類型之間互相轉換的函數，例如`TO_DATE`、`TO_TIME`、`TO_TIMESTAMP`等。可以通過`NOW`函數來獲取資料庫伺服器的時間。

## 數據控制

資料控制語言 (Data Control Language, DCL) 授權的使用者訪問和操作的數據。

它的兩個主要的語句是：

- `GRANT` 授權的一個或多個使用者執行在一個對象上的一個操作或者一組操作。
- `REVOKE` 消除了授權，其可以是默認的授權。

例如:

```sql
GRANT SELECT, UPDATE
 ON example
 TO some_user, another_user;

REVOKE SELECT, UPDATE
 ON example
 FROM some_user, another_user;
```