跳转到内容

SQL语法

本页使用了标题或全文手工转换
维基百科，自由的百科全书

这是本页的一个历史版本，由175.167.146.218​（留言）在2017年10月28日 (六) 14:57 （→运算符）编辑。这可能和当前版本存在着巨大的差异。

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三值逻辑的共同部分对应 (which differ in their definition of implication, however SQL defines no such operation).[4]

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

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.[5] 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."[4] 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."[5]

另外，由于直接与空值比较会返回未知，因此SQL又提供了两个用于测试空值的语句：IS NULL和IS NOT NULL，前者用于判断是否为空，后者相反[6]。 Universal quantification is not explicitly supported by SQL, and must be worked out as a negated existential quantification.[7][8][9] 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. 实践中一些数据库系统（例如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会中止当前事务并释放锁。In the absence of a START TRANSACTION or similar statement, the semantics of SQL are implementation-dependent.

下面例子展示了把一个账户的金额转移到另一个账户上面的过程。只要表示减少和增加的两个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;

数据类型

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

字符串

  • 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 10S.

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)[11]

日期与时间

  • 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的函数，提高了时间的精确度[12]。

数据控制

資料控制語言 (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]. 
  3. ^ Transact-SQL Reference. SQL Server Language Reference. SQL Server 2005 Books Online. Microsoft. 2007-09-15 [2007-06-17]. 
  4. ^ 4.0 4.1 doi:10.1007/3-540-36596-6_7
    {{cite doi}}已停用，请参见{{cite journal}}。
  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.  (proposed revised text of DIS 9075).
  11. ^ Arie Jones, Ryan K. Stephens, Ronald R. Plew, Alex Kriegel, Robert F. Garrett (2005), SQL Functions Programmer's Reference. Wiley, 127 pages.
  12. ^ http://publib.boulder.ibm.com/infocenter/idshelp/v111/index.jsp?topic=/com.ibm.sqlr.doc/sqlr150.htm
检索自“https://zh.wikipedia.org/w/index.php?title=SQL语法&oldid=46750386”