SQL语法

The syntax of the SQL programming language is defined and maintained by ISO/IEC SC 32 as part of ISO/IEC 9075. This standard is not freely available. Despite the existence of the standard, SQL code is not completely portable among different database systems without adjustments.

语言元素

图表显示了SQL语言元素组成的一个语句

SQL语言分为语言元素，包括：

• 子句，是语句和查询的组成成分。（在某些情况下，这些都是可选的。）
• 表达式，可以产生任何标量值，或由列和行的数据库表
• 谓词，给需要评估的SQL三值逻辑（3VL）（true/false/unknown）或布尔真值指定条件，并限制语句和查询的效果，或改变程序流程。
• 查询，基于特定条件检索数据。这是SQL的一个重要组成部分。
• 语句，可以持久地影响纲要和数据，也可以控制数据库事务、程序流程、连接、会话或诊断。
  • SQL语句也包括分號（";"）语句终结符。尽管并不是每个平台下都必需，但它是作为SQL语法的标准部分定义的。
• 无意义的空白在SQL语句和查询中一般会被忽略，更容易格式化SQL代码便于阅读。

运算子

运算子	描述	例子
=	等于	Author = 'Alcott'
<>	不等于（许多DBMS在<>以外还支持!=）	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具备case/when/then/else/end表达式，是在SQL-92中引入的。其最一般的形式，就是SQL标准中所谓的“searched case”，它的工作原理与其他编程语言中的else if类似：

CASE WHEN n > 0 
          THEN 'positive' 
     WHEN n < 0 
          THEN 'negative'
     ELSE 'zero'
END

SQL以WHEN条件在源代码中出现的顺序测试这些条件。如果源代码中没有指定ELSE表达式，SQL默认为ELSE NULL。SQL标准中有一个叫做“简单case”的简短语法—反映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之间相等。

查询

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 升序排列。The asterisk (*) in the 选择列表中的星号（*）表明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

Under the precondition that isbn is the only common column name of the two tables and that a column named title only exists in the Books table, the above query could be rewritten in the following form:

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

However, many vendors either do not support this approach, or require certain column naming conventions for natural joins to work effectively.

SQL includes operators and functions for calculating values on stored values. SQL allows the use of expressions in the select list to project data, as in the following example, which returns a list of books that cost more than 100.00 with an additional sales_tax column containing a sales tax figure calculated at 6% of the price.

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

子查询

Queries can be nested so that the results of one query can be used in another query via a relational operator or aggregation function. A nested query is also known as a subquery. While joins and other table operations provide computationally superior (i.e. faster) alternatives in many cases, the use of subqueries introduces a hierarchy in execution that can be useful or necessary. In the following example, the aggregation function AVG receives as input the result of a subquery:

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

A subquery can use values from the outer query, in which case it is known as a correlated subquery.

Since 1999 the SQL standard allows named subqueries called common table expression (named and designed after the IBM DB2 version 2 implementation; Oracle calls these subquery factoring). CTEs can also be recursive by referring to themselves; the resulting mechanism allows tree or graph traversals (when represented as relations), and more generally fixpoint computations.

Inline View

An Inline view is the use of referencing an SQL subquery in a FROM clause. Essentially, the inline view is a subquery that can be selected from or joined to. Inline View functionality allows the user to reference the subquery as a table. The inline view also is referred to as a derived table or a subselect. Inline view functionality was introduced in Oracle 9i.

In the following example, the SQL statement involves a join from the initial Books table to the Inline view "Sales". This inline view captures associated book sales information using the ISBN to join to the Books table. As a result, the inline view provides the result set with additional columns (the number of items sold and the company that sold the books):

Select b.isbn, b.title, b.price, sales.items_sold sales.company_nm
from Book b,
(select SUM(Items_Sold) Items_Sold, Company_Nm, ISBN
from Book_Sales
group by Company_Nm, ISBN) sales
WHERE sales.isbn = b.isbn

Null or three-valued logic (3VL)

主条目：Null (SQL)

The concept of Null was introduced into SQL to handle missing information in the relational model. The word NULL is a reserved keyword in SQL, used to identify the Null special marker. Comparisons with Null, for instance equality (=) in WHERE clauses, results in an Unknown truth value. In SELECT statements SQL returns only results for which the WHERE clause returns a value of True; i.e., it excludes results with values of False and also excludes those whose value is Unknown.

Along with True and False, the Unknown resulting from direct comparisons with Null thus brings a fragment of three-valued logic to SQL. The truth tables SQL uses for AND, OR, and NOT correspond to a common fragment of the Kleene and Lukasiewicz three-valued logic (which differ in their definition of implication, however SQL defines no such operation).

p AND q p True False Unknown q True True False 未知 False False False False Unknown 未知 False 未知

p OR q p True False Unknown q True True True True False True False 未知 Unknown True 未知 未知

p = q p True False Unknown q True True False 未知 False False True 未知 Unknown 未知 未知 未知

q NOT q True False False True Unknown 未知

There are however disputes about the semantic interpretation of Nulls in SQL because of its treatment outside direct comparisons. As seen in the table above direct equality comparisons between two NULLs in SQL (e.g. NULL = NULL) returns a truth value of Unknown. This is in line with the interpretation that Null does not have a value (and is not a member of any data domain) but is rather a placeholder or "mark" for missing information. However, the principle that two Nulls aren't equal to each other is effectively violated in the SQL specification for the UNION and INTERSECT operators, which do identify nulls with each other. Consequently, these set operations in SQL may produce results not representing sure information, unlike operations involving explicit comparisons with NULL (e.g. those in a WHERE clause discussed above). In Codd's 1979 proposal (which was basically adopted by SQL92) this semantic inconsistency is rationalized by arguing that removal of duplicates in set operations happens "at a lower level of detail than equality testing in the evaluation of retrieval operations." However, computer science professor Ron van der Meyden concluded that "The inconsistencies in the SQL standard mean that it is not possible to ascribe any intuitive logical semantics to the treatment of nulls in SQL."

Additionally, since SQL operators return Unknown when comparing anything with Null directly, SQL provides two Null-specific comparison predicates: IS NULL and IS NOT NULL test whether data is or is not Null. Universal quantification is not explicitly supported by SQL, and must be worked out as a negated existential quantification. There is also the "<row value expression> IS DISTINCT FROM <row value expression>" infixed comparison operator, which returns TRUE unless both operands are equal or both are NULL. Likewise, IS NOT DISTINCT FROM is defined as "NOT (<row value expression> IS DISTINCT FROM <row value expression>)". SQL:1999 also introduced BOOLEAN type variables, which according to the standard can also hold Unknown values. In practice, a number of systems (e.g. 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”。

 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 ...])

Transaction controls

Transactions, if available, wrap DML operations:

• START TRANSACTION (or BEGIN WORK, or BEGIN TRANSACTION, depending on SQL dialect) marks the start of a database transaction, which either completes entirely or not at all.
• SAVE TRANSACTION (or SAVEPOINT) saves the state of the database at the current point in transaction

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 makes all data changes in a transaction permanent.
• ROLLBACK discards all data changes since the last COMMIT or ROLLBACK, leaving the data as it was prior to those changes. Once the COMMIT statement completes, the transaction's changes cannot be rolled back.

COMMIT and ROLLBACK terminate the current transaction and release data locks. In the absence of a START TRANSACTION or similar statement, the semantics of SQL are implementation-dependent. The following example shows a classic transfer of funds transaction, where money is removed from one account and added to another. If either the removal or the addition fails, the entire transaction is rolled back.

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;

数据类型

一张SQL表中的每个字段要声明该字段会包含的类型。ANSI SQL包括下列数据类型。

字符串

• CHARACTER(n)或CHAR(n)：确定宽度的n-字符的字符串，根据需要用空格填充
• CHARACTER VARYING(n)或VARCHAR(n)：最大为n个字符的可变宽度字符串
• NATIONAL CHARACTER(n)或NCHAR(n)：支持国际字符集的固定宽度字符串
• NATIONAL CHARACTER VARYING(n)或NVARCHAR(n)：可变宽度的NCHAR字符串

位串

• BIT(n): an array of n bits
• BIT VARYING(n): an array of up to n bits

数

• INTEGER, SMALLINT and BIGINT
• FLOAT, REAL and DOUBLE PRECISION
• NUMERIC(precision, scale) or DECIMAL(precision, scale)

For example, the number 123.45 has a precision of 5 and a scale of 2. The precision is a positive integer that determines the number of significant digits in a particular radix (binary or decimal). The scale is a non-negative integer. A scale of 0 indicates that the number is an integer. For a decimal number with scale S, the exact numeric value is the integer value of the significant digits divided by 10^S.

SQL provides a function to round numerics or dates, called TRUNC (in Informix, DB2, PostgreSQL, Oracle and MySQL) or ROUND (in Informix, SQLite, Sybase, Oracle, PostgreSQL and Microsoft SQL Server)

日期与时间

• DATE: 日期值 （例如 2011-05-03）
• TIME: 时间值 （例如 15:51:36）。 时间值的粒度通常是一个'滴答（tick）'（100纳秒）。
• TIME WITH TIME ZONE或TIMETZ: 相同于TIME，但是，包括有关问题的时区的详细信息。
• TIMESTAMP: 这是把一个DATE和一个TIME一起共同放在一个变量中 （例如 2011-05-03 15:51:36）。
• TIMESTAMP WITH TIME ZONE 或 TIMESTAMPTZ: 相同于TIMESTAMP，但是， 包括有关问题的时区的详细信息。

SQL提供了从日期/时间字符串生成日期/时间变量的多种功能(TO_DATE, TO_TIME, TO_TIMESTAMP), 以及从这些变量的提取的各个成员（例如，秒）。数据库服务器的当前系统日期/时间可通过使用类似被称为 NOW的函数来被调用。对于需要亚秒的精度系统，在IBM Informix实现提供了EXTEND和FRACTION的函数，提高了时间的精确度。

数据控制

資料控制語言 (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;