跳至內容

SQL語法

本頁使用了標題或全文手工轉換
維基百科，自由的百科全書

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

語言元素

圖表顯示了SQL語言元素組成的一個陳述式

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

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

運算子

運算子 描述 例子
= 等於 Author = 'Alcott'
<> 不等於（許多資料庫管理系統除了支援<>以外還支援!=） 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'

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

條件（CASE）表達式

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

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

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

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

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

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

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

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

查詢

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

查詢允許用戶描述所需的資料，將計劃（英語：query plan）、最佳化（英語：query optimizer）以及執行用以產生它選取的結果的物理操作交給資料庫管理系統（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表中所有欄位都包含在結果集中。 

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

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

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表中的前提下，上述查詢可以用以下形式重寫： 

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

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

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

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

子查詢

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

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

子查詢可以使用外部查詢的值，在這種情況下，它被稱為相關子查詢（英語：correlated subquery）。

自1999年以來，SQL標準允許稱為公共表表達式（英語：common table expression）的命名子查詢（在IBM DB2版本2中實現之後命名和設計; Oracle把它叫做子查詢部分（英語：subquery factoring））。CTE還可以通過自身參照來遞歸；得到的機制（英語：Hierarchical and recursive queries in SQL）允許樹或圖遍歷，以及更一般的不動點計算。

衍生表

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

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

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

空值與三值邏輯

主條目：Null (SQL)和三值邏輯

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

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

p AND q p
真 假 未知
q 真 真 假 未知
假 假 假 假
未知 未知 假 未知
p OR q p
真 假 未知
q 真 真 真 真
假 真 假 未知
未知 真 未知 未知
p = q p
真 假 未知
q 真 真 假 未知
假 假 真 未知
未知 未知 未知 未知
q NOT q
真 假
假 真
未知 未知

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

另外，由於直接與空值比較會返回未知，因此SQL又提供了兩個用於測試空值的陳述式：IS NULL和IS NOT NULL，前者用於判斷是否為空，後者相反[6]。 SQL不明確支援全稱量化，必須將其定義為否定存在量化。[7][8][9] 還有「<行值表達式> 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添加行（正式名稱為元組）到一個現有的表，例如：
INSERT INTO example
 (field1, field2, field3)
 VALUES
 ('test', 'N', NULL);
  • UPDATE修改現有的表中一些行，例如：
UPDATE example
 SET field1 = 'updated value'
 WHERE field2 = 'N';
  • DELETE從表中刪除現有的行，如：
DELETE FROM example
 WHERE field2 = 'N';
  • MERGE用來合併多個表的資料。它結合了INSERT和UPDATE元素。它是在SQL:2003標準中定義的；在那之前，一些資料庫也以不同的語法提供了相似的功能，又是叫做「upsert（英語：upsert）」。
 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）命令會記錄事務本身的狀態，即儲存點。
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陳述式中有一個失敗，整個事務就會回退，更改也不會儲存到資料庫中。 

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在資料庫中建立一個對象（例如一張表），舉例來說：
CREATE TABLE example(
 column1 INTEGER,
 column2 VARCHAR(50),
 column3 DATE NOT NULL,
 PRIMARY KEY (column1, column2)
);
  • ALTER以不同方式修改現有對象的結構，例如向現有的表或約束添加欄位：
ALTER TABLE example ADD column4 NUMBER(3) NOT NULL;
  • TRUNCATE以一種非常快速的方式刪除表中的所有資料，刪除表內的資料而不是表本身。這通常意味着後續的COMMIT操作, 即，它不能被轉返（與DELETE不同，資料不會為之後轉返而寫入紀錄檔）。
TRUNCATE TABLE example;
  • DROP刪除資料庫中的對象，通常無法挽回的，即，它不能被轉返，如：
DROP TABLE example;

資料類型

一張表中的每個欄位都要定義該欄位的類型。ANSI SQL包括下列資料類型：[10][11]

字串

  • CHARACTER(n)或CHAR(n)：寬度為n的定長字串。如果內容長度不足，則以空格填充；
  • CHARACTER VARYING(n)或VARCHAR(n)：最長為n個字元的可變寬度字串；
  • NATIONAL CHARACTER(n)或NCHAR(n)：支援國際字元集的固定寬度字串；
  • NATIONAL CHARACTER VARYING(n)或NVARCHAR(n)：可變寬度的NCHAR字串；

Bit類型

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

  • BIT(n)：n位元Bit類型
  • BIT VARYING(n)：最大長度為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）[12]

日期與時間

  • 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 消除了授權，其可以是預設的授權。

例如: 

GRANT SELECT, UPDATE
 ON example
 TO some_user, another_user;

REVOKE SELECT, UPDATE
 ON example
 FROM some_user, another_user;

參考文獻

  1. ^ ANSI/ISO/IEC International Standard (IS). Database Language SQL—Part 2: Foundation (SQL/Foundation). 1999.
  2. ^ DECODE. Docs.oracle.com. [2013-06-14]. （原始內容存檔於2013-06-28）. 
  3. ^ Transact-SQL Reference. SQL Server Language Reference. SQL Server 2005 Books Online. Microsoft. 2007-09-15 [2007-06-17]. （原始內容存檔於2008-04-30）. 
  4. ^ 4.0 4.1 Klein Hans-Joachim. Null Values in Relational Databases and Sure Information Answers. Springer, Berlin, Heidelberg: 119–138. 2001-01-07 [2018-04-02]. ISBN 3540365966. doi:10.1007/3-540-36596-6_7. （原始內容存檔於2018-06-05） （英語）. 
  5. ^ 5.0 5.1 Ron van der Meyden, "Logical approaches to incomplete information: a survey （頁面存檔備份，存於互聯網檔案館）" in Chomicki, Jan; Saake, Gunter (Eds.) Logics for Databases and Information Systems, Kluwer Academic Publishers ISBN 978-0-7923-8129-7, p. 344; PS preprint （頁面存檔備份，存於互聯網檔案館） (note: page numbering differs in preprint from the published version)
  6. ^ ISO/IEC. ISO/IEC 9075-2:2003, "SQL/Foundation". ISO/IEC. 
  7. ^ M. Negri, G. Pelagatti, L. Sbattella (1989) Semantics and problems of universal quantification in SQL[失效連結].
  8. ^ Fratarcangeli, Claudio (1991). Technique for universal quantification in SQL. Retrieved from ACM.org.
  9. ^ Kawash, Jalal (2004) Complex quantification in Structured Query Language (SQL): a tutorial using relational calculus - Journal of Computers in Mathematics and Science Teaching ISSN 0731-9258 Volume 23, Issue 2, 2004 AACE Norfolk, Virginia. Retrieved from Thefreelibrary.com （頁面存檔備份，存於互聯網檔案館）.
  10. ^ Information Technology: Database Language SQL. CMU. [2017-10-28]. （原始內容存檔於2006-06-21）.  (proposed revised text of DIS 9075).
  11. ^ C. J. Date with Hugh Darwen: A Guide to the SQL standard : a users guide to the standard database language SQL, 4th ed., Addison Wesley, USA 1997, ISBN 978-0-201-96426-4
  12. ^ Arie Jones, Ryan K. Stephens, Ronald R. Plew, Alex Kriegel, Robert F. Garrett (2005), SQL Functions Programmer's Reference. Wiley, 127 pages.
取自 "https://zh.wikipedia.org/w/index.php?title=SQL语法&oldid=74784252"