SQL语句大全

Select
（选择）
SQL 里面最常用的命令是SELECT 语句，用于检索数据。语法是：
SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
* | expression [ AS output_name ] [, ...]
[ INTO [ TEMPORARY | TEMP ] [ TABLE ] new_table ]
[ FROM from_item [, ...] ]
[ WHERE condition ]
[ GROUP BY expression [, ...] ]
[ HAVING condition [, ...] ]
[ { UNION | INTERSECT | EXCEPT [ ALL ] } select ]
[ ORDER BY expression [ ASC | DESC | USING operator ] [, ...]
]
[ FOR UPDATE [ OF class_name [, ...] ] ]
[ LIMIT { count | ALL } [ { OFFSET | , } start ]]
现在我们将通过不同的例子演示SELECT 语句复杂的语法。用于这些例子的表在供应商
和部件数据库里定义。
1.4.1.1.
简单的Select
这里是一些使用SELECT 语句的简单例子：
Example
1-4.
带有条件的简单查询
要从表PART 里面把字段PRICE 大于10 的所有记录找出来， 我们写出下面查询：
SELECT * FROM PART
WHERE PRICE > 10;
然后得到表：
PNO | PNAME | PRICE
-----+---------+--------
3 | Bolt | 15
4 | Cam | 25
在SELECT 语句里使用"*" 将检索出表中的所有属性。如果我们只希望从表PART 中检
索出属性PNAME 和PRICE， 我们使用下面的语句：
SELECT PNAME, PRICE
FROM PART
WHERE PRICE > 10;
这回我们的结果是：
PNAME | PRICE
--------+--------
Bolt | 15
Cam | 25
请注意SQL 的SELECT 语句对应关系演算里面的"projection" （映射），而不是
"selection"（选择）（参阅关系演算获取详细信息）。
WHERE 子句里的条件也可以用关键字OR，AND，和NOT 逻辑地连接起来：
SELECT PNAME, PRICE
FROM PART
WHERE PNAME = 'Bolt' AND
(PRICE = 0 OR PRICE <= 15);
这样将生成下面的结果：
PNAME | PRICE
--------+--------
Bolt | 15
目标列表和WHERE 子句里可以使用算术操作。例如， 如果我们想知道如果我们买两个部
件的话要多少钱， 我们可以用下面的查询：
SELECT PNAME, PRICE * 2 AS DOUBLE
FROM PART
WHERE PRICE * 2 < 50;
这样我们得到：
PNAME | DOUBLE
--------+---------
Screw | 20
Nut | 16
Bolt | 30
请注意在关键字AS 后面的DOUBLE 是第二个列的新名字。这个技巧可以用于目标列表
里的每个元素，给它们赋予一个在结果列中显示的新的标题。这个新的标题通常称为别名。
这个别名不能在该查询的其他地方使用。
1.4.1.2.
Joins
（连接）
下面的例子显示了SQL 里是如何实现连接的。
要在共同的属性上连接三个表SUPPLIER，PART 和SELLS， 我们通常使用下面的语句：
SELECT S.SNAME, P.PNAME
FROM SUPPLIER S, PART P, SELLS SE
WHERE S.SNO = SE.SNO AND
P.PNO = SE.PNO;
而我们得到的结果是：
SNAME | PNAME
-------+-------
Smith | Screw
Smith | Nut
Jones | Cam
Adams | Screw
Adams | Bolt
Blake | Nut
Blake | Bolt
Blake | Cam
在FROM 子句里，我们为每个关系使用了一个别名， 因为在这些关系间有着公共的命名属
性（SNO 和PNO）。现在我们可以区分不同表的公共命名属性， 只需要简单的用每个关
系的别名加上个点做前缀就行了。联合是用与一个内部联接里显示的同样的方法计算的。
首先算出笛卡儿积SUPPLIER × PART × SELLS 。然后选出那些满足WHERE 子句里给
出的条件的记录（也就是说，公共命名属性的值必须相等）。最后我们映射出除S.SNAM
E 和P.PNAME 外的所有属性。
另外一个进行连接的方法是使用下面这样的SQL JOIN 语法：
select sname, pname from supplier
JOIN sells USING (sno)
JOIN part USING (pno);
giving again:
sname | pname
-------+-------
Smith | Screw
Adams | Screw
Smith | Nut
Blake | Nut
Adams | Bolt
Blake | Bolt
Jones | Cam
Blake | Cam
(8 rows)
一个用JOIN 语法创建的连接表，是一个出现在FROM 子句里的， 在任何WHERE，GRO
UP BY 或HAVING 子句之前的表引用列表项． 其它表引用，包括表名字或者其它JOIN
子句，如果用逗号分隔的话， 可以包含在FROM 子句里． 连接生成的表逻辑上和任何其
它在FROM 子句里列出的表都一样．
SQL JOIN 有两种主要类型，CROSS JOIN (无条件连接) 和条件连接．条件连接还可以
根据声明的连接条件(ON，USING，或NATURAL)和它应用的方式(INNER 或OUTER
连接)进一步细分．
连接类型
CROSS JOIN
{ T1 } CROSS
JOIN
{ T2 }
一个交叉连接（cross join）接收两个分别有N 行和M 行的表T1 和T2，然
后返回一个包含交叉乘积NxM 条记录的连接表． 对于T1 的每行R1，T2 的每
行R2 都与R1 连接生成连接的表行JR，JR 包含所有R1 和R2 的字段． CR
OSS JOIN 实际上就是一个INNER JOIN ON TRUE．
条件JOIN
{ T1 } [ NATURAL ] [ INNER | { LEFT | RIGHT | FULL } [ OUTER
] ] JOIN
{ T2 } { ON search condition | USING ( join column
list ) }
一个条件JOIN 必须通过提供一个(并且只能有一个) NATURAL，ON，或者USIN
G 这样的关键字来声明它的连接条件． ON 子句接受一个search conditio
n， 它与一个WHERE 子句相同．USING 子句接受一个用逗号分隔的字段名列表，
连接表中必须都有这些字段， 并且用那些字段连接这些表，生成的连接表包含每个
共有字段和两个表的所有其它字段． NATURAL 是USING 子句的缩写，它列出
两个表中所有公共的字段名字．使用USING 和NATURAL 的副作用是每个连接
的字段都只有一份拷贝出现在结果表中(与前面定义的关系演算的JOIN 相比
较)．
[ INNER ] JOIN
对于T1 的每行R1，连接成的表在T2 里都有一行满足与R1 一起的连接条件．
对于所有JOIN 而言，INNER 和OUTER 都是可选的．INNER 是缺省． LEFT，
RIGHT，和FULL 只用于OUTER JOIN．
LEFT [ OUTER ] JOIN
首先，执行一次INNER JOIN． 然后，如果T1 里有一行对任何T2 的行都不满
足连接条件，那么返回一个连接行，该行的T2 的字段为null．
小技巧:
连接成的表无条件地包含T1 里的所有行．
RIGHT [ OUTER ] JOIN
首先，执行一次INNER JOIN． 然后，如果T2 里有一行对任何T1 的行都不满
足连接条件，那么返回一个连接行，该行的T1 的字段为null．
小技巧:
连接成的表无条件地包含T2 里的所有行．
FULL [ OUTER ] JOIN
首先，执行一次INNER JOIN． 然后，如果T1 里有一行对任何T2 的行都不满
足连接条件，那么返回一个连接行，该行的T1 的字段为null． 同样，如果
T2 里有一行对任何T1 的行都不满足连接条件，那么返回一个连接行，该行的
T2 的字段为null．
小技巧:
连接成的表无条件地拥有来自T1 的每一行和来自T2 的每一
行．
所有类型的JOIN 都可以链接在一起或者嵌套在一起， 这时T1 和T2 都可以是连接生
成的表．我们可以使用圆括弧控制JOIN 的顺序，如果我们不主动控制，那么连接顺序是
从左到右．
1.4.1.3.
聚集操作符
SQL 提供以一些聚集操作符（如， AVG，COUNT，SUM，MIN，MAX），这些聚集操作符以
一个表达式为参数。只要是满足WHERE 子句的行，就会计算这个表达式， 然后聚集操作
符对这个输入数值的集合进行计算． 通常，一个聚集对整个SELECT 语句计算的结果是
生成一个结果．但如果在一个查询里面声明了分组， 那么数据库将对每个组进行一次独立
的计算，并且聚集结果是按照各个组出现的(见下节)．
Example
1-5.
聚集
果我们想知道表PART 里面所有部件的平均价格，我们可以使用下面查询：
SELECT AVG(PRICE) AS AVG_PRICE
FROM PART;
结果是：
AVG_PRICE
-----------
14.5
如果我们想知道在表PART 里面存储了多少部件，我们可以使用语句：
SELECT COUNT(PNO)
FROM PART;
得到：
COUNT
-------
4
1.4.1.4.
分组聚集
SQL 允许我们把一个表里面的记录分成组。然后上面描述的聚集操作符可以应用于这些组
上（也就是说，聚集操作符的值不再是对所有声明的列的值进行操作， 而是对一个组的所
有值进行操作。这样聚集函数是为每个组独立地进行计算的。）
对记录的分组是通过关键字GROUP
BY
实现的，GROUP
BY
后面跟着一个定义组的构成
的属性列表。如果我们使用语句GROUP
BY
A
1
,
&tdot;,
A
k
我们就把关系分成了组，
这样当且仅当两条记录在所有属性A1, &tdot;, Ak 上达成一致，它们才是同一组的。
Example
1-6.
聚集
如果我们想知道每个供应商销售多少个部件，我们可以这样写查询：
SELECT S.SNO, S.SNAME, COUNT(SE.PNO)
FROM SUPPLIER S, SELLS SE
WHERE S.SNO = SE.SNO
GROUP BY S.SNO, S.SNAME;
得到：
SNO | SNAME | COUNT
-----+-------+-------
1 | Smith | 2
2 | Jones | 1
3 | Adams | 2
4 | Blake | 3
然后我们看一看发生了什么事情。首先生成表SUPPLIER 和SELLS 的连接：
S.SNO | S.SNAME | SE.PNO
-------+---------+--------
1 | Smith | 1
1 | Smith | 2
2 | Jones | 4
3 | Adams | 1
3 | Adams | 3
4 | Blake | 2
4 | Blake | 3
4 | Blake | 4
然后我们把那些属性S.SNO 和S.SNAME 相同的记录放在组中：
S.SNO | S.SNAME | SE.PNO
-------+---------+--------
1 | Smith | 1
| 2
--------------------------
2 | Jones | 4
--------------------------
3 | Adams | 1
| 3
--------------------------
4 | Blake | 2
| 3
| 4
在我们的例子里，我们有四个组并且现在我们可以对每个组应用聚集操作符COUNT，生成
上面给出的查询的最终结果。
请注意如果要让一个使用GROUP BY 和聚集操作符的查询的结果有意义， 那么用于分组
的属性也必须出现在目标列表中。所有没有在GROUP BY 子句里面出现的属性都只能通
过使用聚集函数来选择。否则就不会有唯一的数值与其它字段关联．
还要注意的是在聚集上聚集是没有意义的，比如，AVG(MAX(sno))， 因为SELECT 只做
一个回合的分组和聚集．你可以获得这样的结果， 方法是使用临时表或者在FROM 子句中
使用一个子SELECT 做第一个层次的聚集．
1.4.1.5.
Having
HAVING 子句运做起来非常象WHERE 子句， 只用于对那些满足HAVING 子句里面给出
的条件的组进行计算。其实，WHERE 在分组和聚集之前过滤掉我们不需要的输入行， 而
HAVING 在GROUP 之后那些不需要的组． 因此，WHERE 无法使用一个聚集函数的结果．
而另一方面，我们也没有理由写一个不涉及聚集函数的HAVING． 如果你的条件不包含
聚集，那么你也可以把它写在WHERE 里面， 这样就可以避免对那些你准备抛弃的行进行
的聚集运算．
Example
1-7.
Having
如果我们想知道那些销售超过一个部件的供应商，使用下面查询：
SELECT S.SNO, S.SNAME, COUNT(SE.PNO)
FROM SUPPLIER S, SELLS SE
WHERE S.SNO = SE.SNO
GROUP BY S.SNO, S.SNAME
HAVING COUNT(SE.PNO) > 1;
and get:
SNO | SNAME | COUNT
-----+-------+-------
1 | Smith | 2
3 | Adams | 2
4 | Blake | 3
1.4.1.6.
子查询
在WHERE 和HAVING 子句里，允许在任何要产生数值的地方使用子查询（子选择）。
这种情况下，该值必须首先来自对子查询的计算。子查询的使用扩展了SQL 的表达能力。
Example
1-8.
子查询
如果我们想知道所有比名为'Screw' 的部件贵的部件，我们可以用下面的查询：
SELECT *
FROM PART
WHERE PRICE > (SELECT PRICE FROM PART
WHERE PNAME='Screw');
结果是：
PNO | PNAME | PRICE
-----+---------+--------
3 | Bolt | 15
4 | Cam | 25
当我们检查上面的查询时会发现出现了两次SELECT 关键字。第一个在查询的开头- 我
们将称之为外层SELECT - 而另一个在WHERE 子句里面，成为一个嵌入的查询- 我们
将称之为内层SELECT。对外层SELECT 的每条记录都必须先计算内层SELECT。在完
成所有计算之后， 我们得知名为'Screw' 部件的记录的价格， 然后我们就可以检查那
些价格更贵的记录了。(实际上，在本例中，内层查询只需要执行一次， 因为它不依赖于
外层查询高等状态．)
如果我们想知道那些不销售任何部件的供应商（比如说，我们想把这些供应商从数据库中
删除），我们用：
SELECT *
FROM SUPPLIER S
WHERE NOT EXISTS
(SELECT * FROM SELLS SE
WHERE SE.SNO = S.SNO);
在我们的例子里，结果列将是空的，因为每个供应商至少销售一个部件。请注意我们在W
HERE 子句的内层SELECT 里使用了来自外层SELECT 的S.SNO。正如前面所说的，
子查询为每个外层查询计算一次，也就是说， S.SNO 的值总是从外层SELECT 的实际记
录中取得的。
1.4.1.7.
在FROM
里面的子查询
一种有些特别的子查询的用法是把它们放在FROM 子句里． 这个特性很有用，因为这样的
子查询可以输出多列和多行， 而在表达式里使用的子查询必须生成一个结果． FROM 里的
子查询还可以让我们获得多于一个回合的分组/聚集特性， 而不需要求助于临时表．
Example
1-9.
FROM
里面的子查询
如果我们想知道在所有我们的供应商中的最高平均部件价格的那家， 我们不能用MAX(AV
G(PRICE))，但我们可以这么写：
SELECT MAX(subtable.avgprice)
FROM (SELECT AVG(P.PRICE) AS avgprice
FROM SUPPLIER S, PART P, SELLS SE
WHERE S.SNO = SE.SNO AND
P.PNO = SE.PNO
GROUP BY S.SNO) subtable;
这个子查询为每个供应商返回一行（因为它的GROUP BY） 然后我们在外层查询对所有行
进行聚集．
1.4.1.8.
Union,
Intersect,
Except
（联合，相交，相异）
这些操作符分别计算两个子查询产生的元组的联合，相交和集合理论里的相异。
Example
1-10.
Union,
Intersect,
Except
下面的例子是UNION 的例子：
SELECT S.SNO, S.SNAME, S.CITY
FROM SUPPLIER S
WHERE S.SNAME = 'Jones'
UNION
SELECT S.SNO, S.SNAME, S.CITY
FROM SUPPLIER S
WHERE S.SNAME = 'Adams';
产生结果：
SNO | SNAME | CITY
-----+-------+--------
2 | Jones | Paris
3 | Adams | Vienna
下面是相交（ INTERSECT）的例子：
SELECT S.SNO, S.SNAME, S.CITY
FROM SUPPLIER S
WHERE S.SNO > 1
INTERSECT
SELECT S.SNO, S.SNAME, S.CITY
FROM SUPPLIER S
WHERE S.SNO < 3;
产生结果：
SNO | SNAME | CITY
-----+-------+--------
2 | Jones | Paris
两个查询都会返回的元组是那条SNO=2 的
最后是一个EXCEPT 的例子：
SELECT S.SNO, S.SNAME, S.CITY
FROM SUPPLIER S
WHERE S.SNO > 1
EXCEPT
SELECT S.SNO, S.SNAME, S.CITY
FROM SUPPLIER S
WHERE S.SNO > 3;
结果是：
SNO | SNAME | CITY
-----+-------+--------
2 | Jones | Paris
3 | Adams | Vienna
1.4.2.
数据定义
在SQL 语言里包含一套用于数据定义的命令。
1.4.2.1.
创建表
数据定义的最基本的命令是创建一个新关系（新表）的命令。CREATE TABLE 命令的语
法如下：
CREATE TABLE table_name
(name_of_attr_1 type_of_attr_1
[, name_of_attr_2 type_of_attr_2
[, ...]]);
Example
1-11.
创建表
要创建供应商和部件数据库里的表，要使用下面的SQL 语句：
CREATE TABLE SUPPLIER
(SNO INTEGER,
SNAME VARCHAR(20),
CITY VARCHAR(20));
CREATE TABLE PART
(PNO INTEGER,
PNAME VARCHAR(20),
PRICE DECIMAL(4 , 2));
CREATE TABLE SELLS
(SNO INTEGER,
PNO INTEGER);
1.4.2.2.
SQL
里的数据类型
下面是SQL 一些数据类型的列表：
 INTEGER：有符号全长二进制整数（31 位精度）。
 SMALLINT：有符号半长二进制整数（15 位精度）。
 DECIMAL (p[,q]): 有符号的封装了的十进制小数，最多有p 位数，并假设有
q 位在小数点右边。如果省略q ，则认为是0。
 FLOAT：有符号双字浮点数。
 CHAR(n)： 长度为n 的定长字符串。
 VARCHAR(n)： 最大长度为n 的变长字符串。
1.4.2.3.
创建索引
索引用于加速对关系的访问。如果关系R 有一个建立在属性A 上的索引，那么我们检索
满足t(A) = a 的所有元组t 用的时间和这样的元组数t 成比例，而不是与R 的大小
成比例。
在SQL 里创建一个索引， 使用CREATE INDEX 命令。语法是：
CREATE INDEX index_name
ON table_name ( name_of_attribute );
Example
1-12.
创建索引
要在关系SUPPLIER 的属性SNAME 上创建一个名为I 的索引， 我们使用下面语法：
CREATE INDEX I ON SUPPLIER (SNAME);
所创建的索引是自动维护的，也就是说，当向关系SUPPLIER 插入新的记录时， 索引I
相应做调节。请注意有索引存在时， 用户能感觉到的唯一的变化就是速度的提升。
1.4.2.4.
创建视图
一个视图可以看作一个虚拟表， 也就是说，数据库里的一个物理上不存在的，但是用户看
起来却存在的表。与之相比，当我们谈到一个基本表时， 则是在物理存储中的确物理地
存放着表中每一行的内容。
视图没有它们自身的，物理上分开的，可区分的存储区。实际上，系统把视图的定义（也
就是说，为视图物化的应如何访问物理上存储在基本表中内容的规则）存放在系统表里的
某个地方（参阅系统表）。关于实现视图的不同技巧，请参考SIM98.
在SQL 里，使用CREATE
VIEW
命令定义视图。语法是：
CREATE VIEW view_name
AS select_stmt
这里select_stmt 是一个与定义在Select（选择） 里一样的有效选择语句。请注意
在视图创建时并不执行select_stmt。它只是存储在系统表里，当对视图进行查询时
将执行。
假设给出下面的视图定义（我们再次使用来自供应商和部件数据库里的表）：
CREATE VIEW London_Suppliers
AS SELECT S.SNAME, P.PNAME
FROM SUPPLIER S, PART P, SELLS SE
WHERE S.SNO = SE.SNO AND
P.PNO = SE.PNO AND
S.CITY = 'London';
现在我们可以把这个虚拟的关系London_Suppliers 当作是另外一个基本表：
SELECT * FROM London_Suppliers
WHERE PNAME = 'Screw';
将返回下面的表：
SNAME | PNAME
-------+-------
Smith | Screw
要计算这个结果，数据库系统必须先对基本表SUPPLIER，SELLS 和PART 先进行一次
隐藏的访问。它是通过对这些基本表运行该视图定义里面给出的查询实现隐藏访问的。
然后，余下的附加条件（在查询里给出的对视图的条件）就可以应用上来， 最后获取结果
表。
1.4.2.5.
删除表，删除索引，删除视图
要删除表（包括该表存储的所有记录），使用DROP TABLE 命令：
DROP TABLE table_name;
要删除SUPPLIER 表，使用下面语句：
DROP TABLE SUPPLIER;
DROP INDEX 命令用于删除一个索引：
DROP INDEX index_name;
最后用DROP VIEW 删除一个给出的视图：
DROP VIEW view_name;
1.4.3.
数据操作
1.4.3.1.
插入数据
一旦表创建完成（参阅创建表），就可以用命令INSERT
INTO
向里面填充元组。语法
是：
INSERT INTO table_name (name_of_attr_1
[, name_of_attr_2 [,...]])
VALUES (val_attr_1 [, val_attr_2 [, ...]]);
要向关系SUPPLIER 中插入第一条元组（来自供应商和部件数据库），我们使用下面语
句：
INSERT INTO SUPPLIER (SNO, SNAME, CITY)
VALUES (1, 'Smith', 'London');
要向关系SELLS 插入第一条元组，我们用：
INSERT INTO SELLS (SNO, PNO)
VALUES (1, 1);
1.4.3.2.
Update
（更新）
要修改元组中的一个或者多个属性的值，使用UPDATE 命令。语法是：
UPDATE table_name
SET name_of_attr_1 = value_1
[, ... [, name_of_attr_k = value_k]]
WHERE condition;
要修改表关系PART 中部件'Screw' 的属性PRICE 的值，我们用：
UPDATE PART
SET PRICE = 15
WHERE PNAME = 'Screw';
记录名为'Screw' 的属性PRICE 的新值现在是15。
1.4.3.3.
Delete
（删除）
要从一个表中删除一条记录，使用DELETE FROM 命令。语法是：
DELETE FROM table_name
WHERE condition;
要删除SUPPLIER 表中名为'Smith' 的供应商，使用下面的语句：
DELETE FROM SUPPLIER
WHERE SNAME = 'Smith';
1.4.4.
系统表
在所有SQL 数据库系统里面， 系统表都被用于追踪表， 视图索引等在数据库中定义的东
西。这些系统表可以象普通关系一样检索。例如，有一个系统表用于视图的定义。这个表
存储来自视图定义的查询。每当对视图进行查询时， 系统在处理用户查询之前首先从该表
中取出视图定义查询并且执行该查询（参阅Simkovics, 1998 获取更详细的描述）。
关于系统表的更多信息，请参考Date, 1994。
1.4.5.
嵌入的SQL
在这一节里，我们将勾勒如何把SQL 嵌入到一种宿主语言里（例如， C）。我们需要从
一种宿主语言里使用SQL 的原因主要有两个：
 有一些查询不可能用纯SQL 表示（比如，递归查询）。要执行这样的查询，我们
需要一种比SQL 更有表达能力的宿主语言。
 我们只是希望能从用宿主语言写的应用里面访问数据库（例如，一个图形用户界面
的门票预定系统可能用C 写， 而余票状况存储在一个可以用嵌入SQL 访问的数
据库里面）。
一个在宿主语言里面使用嵌入SQL 的程序包含宿主语言的语句和嵌入SQL （ESQL）的
语句。每条ESQL 语句以关键字EXEC
SQL
开始。ESQL 语句被一个预编译器（它通
常向源文件中插入库过程的调用以执行各种SQL 命令）转换成宿主语言。
当我们观察例子Select（选择） 时， 我们认识到一个查询的结果大多是记录的集合。
大多数宿主语言不是为集合而设计的， 因此我们需要一个机制用来访问一条SELECT 语
句返回结果集中的每一条记录。这个机制可以通过定义一个游标来实现。之后我们就可以
使用FETCH 命令检索一条记录并把游标定义到下一条记录



都是一些很常用的SQL语句。