跳转到内容

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”