跳转到内容

结构化查询语言/SELECT：基础

Add links
来自维基教科书，开放的书籍，开放的世界
示例数据库数据 结构化查询语言
SELECT：基础 		SELECT：连接操作


SELECT 命令从一个或多个表或视图中检索数据。它通常包含以下语言元素

SELECT   <things_to_be_displayed>  -- the so called 'Projection' - mostly a list of columnnames
FROM     <tablename>               -- table or view names and their aliases
WHERE    <where_clause>            -- the so called 'Restriction' or 'search condition'
GROUP BY <group_by_clause>
HAVING   <having_clause>
ORDER BY <order_by_clause>
OFFSET   <offset_clause>
FETCH    <fetch_first_or_next_clause>;

除了前两个元素之外，其他所有元素都是可选的。语言元素的顺序是强制性的。在命令的某些位置，可能会以递归的方式开始新的 SELECT 命令。

投影（指定结果列）

[编辑 | 编辑源代码]

在 SELECT 命令的投影部分，您指定一个列列表、对列进行操作、函数、固定值或新的 SELECT 命令。

-- C/Java style comments are possible within SQL commands
SELECT id,                          /* the name of a column   */
       concat(firstname, lastname), /* the concat() function  */
       weight + 5,                  /* the add operation      */
       'kg'                         /* a value                */
FROM   person;

DBMS 将检索十行，每行包含四列。

我们可以以任何顺序混合列的顺序，或者多次检索它们。

SELECT id, lastname, lastname, 'weighs', weight, 'kg'
FROM   person;

星号 '*' 是所有列列表的缩写。

SELECT * FROM person;

对于数字列，我们可以应用通常的数字运算符 +、-、* 和 /。还有许多根据数据类型预定义的函数：幂、平方根、模、字符串函数、日期函数。

使用 DISTINCT 关键字进行唯一性

[编辑 | 编辑源代码]

可以使用 DISTINCT 关键字压缩结果，使其具有唯一值。在这种情况下，所有将是相同的行将压缩成一个行。换句话说：重复项将被消除 - 就像在集合论中一样。

-- retrieves ten rows
SELECT lastname
FROM   person;
-- retrieves only seven rows. Duplicate values are thrown away.
SELECT DISTINCT lastname
FROM   person;
-- Hint:
-- The keyword 'DISTINCT' refers to the entirety of the resulting rows, which you can imagine as
-- the concatenation of all columns. It follows directly behind the SELECT keyword.
-- The following query leads to ten rows, although three persons have the same lastname.
SELECT DISTINCT lastname, firstname
FROM   person;
-- again only seven rows
SELECT DISTINCT lastname, lastname
FROM   person;

列名的别名

[编辑 | 编辑源代码]

有时我们希望为结果列指定更具描述性的名称。我们可以在投影中选择一个别名来做到这一点。此别名是结果集中的新名称。GUI 将别名显示为列标签。

-- The keyword 'AS' is optional
SELECT lastname AS family_name, weight AS weight_in_kg
FROM   person;

函数

[编辑 | 编辑源代码]

有一些预定义的函数可用于投影（以及其他一些位置）。最常用的函数是

  • count(<columnname>|'*'): 统计结果行的数量。
  • max(<columnname>): 结果集中 <column> 中的最高值。也适用于字符串。
  • min(<columnname>): 结果集中 <column> 中的最低值。也适用于字符串。
  • sum(<columnname>): 数字列中所有值的总和。
  • avg(<columnname>): 数字列的平均值。
  • concat(<columnname_1>, <columnname_2>): 两列的连接。或者，该函数可以用 '||' 运算符表示：<columnname_1> || <columnname_2>

标准 SQL 和每个 DBMS 都提供更多函数。

我们必须区分那些每行返回一个值的函数（如 concat()）和那些每个完整结果集只返回一行的函数（如 max()）。前者可以与列名以任何组合混合使用，如本页最开始的第一个示例所示。对于后者，存在一个问题：如果我们将它们与常规列名混合使用，DBMS 会识别出查询中的矛盾。一方面，它应该检索一个精确的值（在一个行中），另一方面，它应该检索很多值（在很多行中）。DBMS 的反应因供应商而异。一些在运行时抛出错误消息 - 符合 SQL 标准 -，另一些则提供可疑的结果。

-- works fine
SELECT lastname, concat(weight, ' kg')
FROM   person;
-- check the reaction of your DBMS. It should throw an error message.
SELECT lastname, avg(weight)
FROM   person;

-- a legal mixture of functions resulting in one row with 4 columns
SELECT min(weight), max(weight), avg(weight) as average_1, sum(weight) / count(*) as average_2
FROM   person;

SELECT within SELECT

[编辑 | 编辑源代码]

如果我们确实希望在多行中看到结果集面向函数的结果与列的组合，我们可以在一个位置启动一个全新的 SELECT - 在简单的案例中，列名会出现在这里。第二个 SELECT 是一个完全独立的命令。请注意：它将针对第一个 SELECT 的每个结果行执行！

-- retrieves 10 rows; notice the additional parenthesis to delimit the two SELECTs from each other.
SELECT lastname, (SELECT avg(weight) FROM person)
FROM   person;
-- Compute the percentage of each persons weight in relation to the average weight of all persons
SELECT lastname,
       weight,
       weight * 100 / (SELECT avg(weight) FROM person) AS percentage_of_average
FROM   person;

表名

[编辑 | 编辑源代码]

FROM 关键字用于指定命令将操作的表。此表名可以用作标识符。在最初的简单示例中，可以用表名标识符作为前缀来命名列，但不是必需的。在后面的更复杂的命令中，表名标识符是一个必需的功能。

SELECT person.firstname, person.lastname
FROM   person;
-- Define an alias for the table name (analog to column names). To retain overview we usually
-- abbreviate tables by the first character of their name.
SELECT p.firstname, p.lastname
FROM   person AS p;  -- Hint: not all systems accept keyword 'AS' with table aliases. Omit it in these cases!
-- The keyword 'AS' is optional again.
SELECT p.firstname, p.lastname
FROM   person p;

限制（指定结果行）

[编辑 | 编辑源代码]

在 WHERE 子句中，我们指定一些“搜索条件”，这些条件在命名的表或视图中。此条件的评估通常是 SELECT 命令执行过程中的第一步。在对任何行进行排序或显示之前，它必须满足子句中的条件。

如果省略该子句，将检索表的全部行。否则，行数将根据指定的条件减少。例如，如果我们指定 'weight < 70'，则仅检索 weight 列存储的值小于 70 的行。限制通过评估列值来作用于表的行（有时它们会作用于其他内容，例如行的存在，但目前我们关注的是基本原理）。结果，我们可以想象“where 子句”的评估生成了一系列行。此系列行将在后续步骤中进行处理，例如排序、分组或显示某些列（投影）。

比较

[编辑 | 编辑源代码]

我们像在不同的编程语言中一样比较变量、常量值和函数调用的结果。唯一的区别是，我们使用列名而不是变量。比较运算符必须与它们必须操作的给定数据类型匹配。比较的结果是一个布尔值。如果结果为“true”，则相应行将被进一步处理。一些示例

  • 'weight = 70' 将 'weight' 列与常量值 '70' 进行比较，查看该列是否等于常量值。
  • '70 = weight'：与之前相同。
  • 'firstname = lastname' 将两列 - 同一行的每列 - 进行比较，查看是否相等。像 'Frederic Frederic' 这样的名称将评估为 true。
  • 'firstname < lastname' 是根据字符串的词典顺序对两列进行的公平比较。
  • 'LENGTH(firstname) < 5' 将函数调用的结果与常量值 '5' 进行比较。LENGTH() 函数对字符串进行操作并返回一个数字。

布尔逻辑

[编辑 | 编辑源代码]

通常我们希望指定多个搜索条件，例如，在旧金山出生且姓氏为 Baker 的人是否存在？为了做到这一点，我们需要独立地指定每个必要的比较条件，并使用布尔运算符 AND 或 OR 将它们连接起来。

SELECT *
FROM   person
WHERE  place_of_birth = 'San Francisco'
AND    lastname = 'Baker';

比较的结果是一个布尔值。它可以通过一元运算符 NOT 在 'true' 和 'false' 之间切换。

SELECT  *
FROM    person
WHERE   place_of_birth = 'San Francisco'
AND NOT lastname = 'Baker'; -- all except 'Baker'
-- for clarification: The NOT in the previous example is a 'unary operation' on the result of the
--                    comparison. It's not an addition to the AND.  
SELECT  *
FROM    person
WHERE   place_of_birth = 'San Francisco'
AND (NOT (lastname = 'Baker'));   --  same as before, but explicit notated with parenthesis

优先级 比较和布尔逻辑的优先级如下：

  1. 所有比较
  2. NOT 运算符
  3. AND 运算符
  4. OR 运算符
-- AND (born in SF and lastname Baker; 1 hit as an intermediate result) will be processed before
-- OR  (person Yorgos; 1 hit)
-- 1 + 1 ==> 2 rows
SELECT *
FROM   person
WHERE  place_of_birth = 'San Francisco' -- 4 hits SF
AND    lastname = 'Baker'               -- 1 hit Baker
OR     firstname = 'Yorgos'             -- 1 hit Yorgos
;

-- Same example with parentheses added to make the precedence explicit.
-- AND gets processed before OR.
-- results ==> same 2 rows as above
SELECT *
FROM   person
WHERE  (place_of_birth = 'San Francisco' -- 4 hits SF
AND    lastname = 'Baker')              -- 1 hit Baker
OR     firstname = 'Yorgos'             -- 1 hit Yorgos
;

-- AND (person Yorgos Baker; no hit as an intermediate result) will be processed before
-- OR  (born in SF; 4 hits)
-- 0 + 4 ==> 4 rows
SELECT *
FROM   person
WHERE  place_of_birth = 'San Francisco' -- 4 hits SF
OR     firstname = 'Yorgos'             -- 1 hit Yorgos
AND    lastname = 'Baker'               -- 1 hit Baker
;

-- Same example with parentheses added to make the precedence explicit.
-- AND gets processed before OR.
-- results ==> same 4 rows as above
SELECT *
FROM   person
WHERE  place_of_birth = 'San Francisco'  -- 4 hits SF
OR     (firstname = 'Yorgos'             -- 1 hit Yorgos
AND    lastname = 'Baker')               -- 1 hit Baker
;

-- We can modify the sequence of evaluations by specifying parentheses.
-- Same as the first example, adding parentheses, one row.
SELECT *
FROM   person
WHERE  place_of_birth = 'San Francisco' -- 4 hits SF
AND   (lastname = 'Baker'               -- 1 hit Baker
OR     firstname = 'Yorgos')            -- 1 hit Yorgos
;

两个缩写

有时我们使用 BETWEEN 关键字来缩短语法。它定义了上下限，主要用于数字和日期值，但也适用于字符串。

SELECT *
FROM   person
WHERE  weight >= 70
AND    weight <= 90;
-- An equivalent shorter and more expressive wording
SELECT *
FROM   person
WHERE  weight BETWEEN 70 AND 90; -- BETWEEN includes the two cutting edges

为了比较一列或函数与多个值，我们可以使用简短的 IN 表达式。

SELECT *
FROM   person
WHERE  lastname = 'de Winter'
OR     lastname = 'Baker';
-- An equivalent shorter and more expressive wording
SELECT *
FROM   person
WHERE  lastname IN ('de Winter', 'Baker');

FETCH: 选择某些行

[编辑 | 编辑源代码]

有时我们对所有结果行不感兴趣，例如：我们可能只想查看前 3 行或 10 行。这可以通过 OFFSET 和 FETCH 子句来实现。OFFSET 指定要跳过的行数（从结果集的开头开始计算），而 FETCH 指定行数，超过这个数字后将停止传递行。

SELECT *
FROM   person
WHERE  place_of_birth = 'San Francisco'
ORDER BY firstname
FETCH FIRST 2 ROWS ONLY  -- only the first 2 rows
;

SELECT *
FROM   person
ORDER BY id              -- the WHERE clause (and the ORDER BY clause) are optional
OFFSET 5 ROWS
FETCH FIRST 2 ROWS ONLY  -- only the 6th and 7th row (according to the ORDER BY)
;

请注意，OFFSET 和 FETCH 子句是 SELECT 命令的独立部分。一些实现将此功能作为 WHERE 子句的一部分处理，或者使用不同的关键字（ROWNUM、START、SKIP、LIMIT）。

OFFSET 和 FETCH 的功能可以通过 窗口函数及其更通用的语法来实现。

分组

[编辑 | 编辑源代码]

我们将在后面的章节中介绍 GROUP BY 子句与 HAVING 子句的结合使用。 下一章节.

排序

[编辑 | 编辑源代码]

DBMS 可以自由地以任意顺序传递结果行。行可以按照主键的顺序返回，按照它们存储到数据库中的时间顺序返回，按照 B 树组织的内部键的顺序返回，甚至按照随机顺序返回。对于传递行的顺序，DBMS 可以做它想做的事情。不要期望任何东西。

如果我们期望以特定顺序返回行，则必须明确表达我们的愿望。我们可以在 ORDER BY 子句中做到这一点。在那里，我们指定一个列名列表，并结合升序或降序排序选项。

-- all persons in ascending (which is the default) order of their weight
SELECT *
FROM   person
ORDER BY weight;
-- all persons in descending order of their weight
SELECT *
FROM   person
ORDER BY weight desc;

在上面的结果中，有两行在 weight 列中具有相同的值。由于这种情况会导致随机结果，因此我们可以指定更多列。这些后续列仅对所有先前列中具有相同值的那些行进行处理。

-- All persons in descending order of their weight. In ambiguous cases order the 
-- additional column place_of_birth ascending: Birmingham before San Francisco.
SELECT *
FROM   person
ORDER BY weight desc, place_of_birth;

在 ORDER BY 子句中，我们可以指定处理表的任何列。我们不限于通过投影返回的那些列。

-- same ordering as above
SELECT firstname, lastname
FROM   person
ORDER BY weight desc, place_of_birth;

 

组合语言元素

[编辑 | 编辑源代码]

SELECT 命令中只有前两个元素是必需的：直到第一个表（或视图）名称的部分。所有其他元素都是可选的。如果我们还指定可选元素，则必须牢记它们的预定顺序。但它们可以根据我们的需要组合。

-- We have seen on this page: SELECT / FROM / WHERE / ORDER BY
SELECT p.lastname,
       p.weight,
       p.weight * 100 / (SELECT avg(p2.weight) FROM person p2) AS percentage_of_average
FROM   person p
WHERE  p.weight BETWEEN 70 AND 90
ORDER BY p.weight desc, p.place_of_birth;

更多信息

[编辑 | 编辑源代码]

关于 SELECT 命令的附加选项，还有更多信息。

练习

[编辑 | 编辑源代码]

显示爱好表中的 hobbyname 和 remark。

点击查看解决方案
SELECT hobbyname, remark
FROM   hobby;

显示爱好表中的 hobbyname 和 remark。按 hobbyname 对结果进行排序。

点击查看解决方案
SELECT hobbyname, remark
FROM   hobby
ORDER BY hobbyname;

显示爱好表中的 hobbyname 和 remark。将 'Hobby' 作为第一列名，'Short_Description_of_Hobby' 作为第二列名。

点击查看解决方案
SELECT hobbyname as Hobby, remark as Short_Description_of_Hobby
FROM   hobby;
-- columnname without underscore: Use quotes
SELECT hobbyname as "Hobby", remark as "Short Description of Hobby"
FROM   hobby;

显示在旧金山出生的人的 firstname 和 lastname。

点击查看解决方案
SELECT firstname, lastname
FROM   person
WHERE  place_of_birth = 'San Francisco';

显示所有姓氏为 'de Winter' 的人的信息项。

点击查看解决方案
SELECT *
FROM   person
WHERE  lastname = 'de Winter';

联系表中存储了多少行？

点击查看解决方案
SELECT count(*)
FROM   contact;
9

联系表中存储了多少个电子邮件？

点击查看解决方案
SELECT count(*)
FROM   contact
WHERE  contact_type = 'email';
3

在旧金山出生的人的平均体重是多少？

点击查看解决方案
SELECT avg(weight)
FROM   person
WHERE  place_of_birth = 'San Francisco';
71.25

查找出生日期在 1979-12-31 之后且体重超过/低于 50 公斤的人。

点击查看解决方案
SELECT *
FROM   person
WHERE  date_of_birth > DATE '1979-12-31'
AND    weight > 50;
--
SELECT *
FROM   person
WHERE  date_of_birth > DATE '1979-12-31'
AND    weight < 50;

查找出生在伯明翰、孟买、上海或雅典的人，按照他们的 firstname 排序。

点击查看解决方案
SELECT *
FROM   person
WHERE  place_of_birth = 'Birmingham'
OR     place_of_birth = 'Mumbai'
OR     place_of_birth = 'Shanghai'
OR     place_of_birth = 'Athens'
ORDER BY firstname;
-- equivalent:
SELECT *
FROM   person
WHERE  place_of_birth IN ('Birmingham', 'Mumbai', 'Shanghai', 'Athens')
ORDER BY firstname;

查找出生在伯明翰、孟买、上海或雅典，且出生于 21 世纪的人。

点击查看解决方案
SELECT *
FROM   person
WHERE  (   place_of_birth = 'Birmingham'
        OR place_of_birth = 'Mumbai' 
        OR place_of_birth = 'Shanghai'
        OR place_of_birth = 'Athens'
       )
AND    date_of_birth >= DATE '2000-01-01';
-- equivalent:
SELECT *
FROM   person
WHERE  place_of_birth IN ('Birmingham', 'Mumbai', 'Shanghai', 'Athens')
AND    date_of_birth >= DATE '2000-01-01';

查找出生在达拉斯和里士满之间的人（'之间' 不是指地理区域，而是指城市名的词典顺序）。

点击查看解决方案
-- strings have a lexical order. So we can use some operators known
-- from numeric data types.
SELECT *
FROM   person
WHERE  place_of_birth >= 'Dallas'
AND    place_of_birth <= 'Richland'
ORDER BY place_of_birth;
-- equivalent:
SELECT *
FROM   person
WHERE  place_of_birth BETWEEN 'Dallas' AND 'Richland'
ORDER BY place_of_birth;

联系表中存储了哪种联系方式？（每种值只显示一行。）

点击查看解决方案
SELECT DISTINCT contact_type
FROM   contact;
fixed line
email
icq
mobile

联系表中存储了多少种不同的联系方式？（提示：计算上面查询的结果行数。）

点击查看解决方案
SELECT count(DISTINCT contact_type)
FROM   contact;
4

显示 contact_type、contact_value 和一个形如 '总联系方式数量: <x>' 的字符串，其中 <x> 是所有现有联系方式的数量。

点击查看解决方案
SELECT contact_type, contact_value,
       (SELECT concat('total number of contacts: ', count(*)) FROM contact)
FROM   contact;
-- Some systems need explicit type casting from numeric to string
SELECT contact_type, contact_value,
       (SELECT concat('total number of contacts: ', cast(count(*) as char)) FROM contact)
FROM   contact;
-- The '||' operator is some kind of 'syntactical sugar'. It's an abbreviation for the concat() function.
-- The operator is part of the SQL standard, but not implemented by all vendors.
SELECT contact_type, contact_value,
       (SELECT 'total number of contacts: ' || count(*) FROM contact)
FROM   contact;


示例数据库数据 结构化查询语言
SELECT：基础 		SELECT：连接操作
检索自 "https://wikibooks.cn/w/index.php?title=Structured_Query_Language/SELECT:_Fundamentals&oldid=4355264"
华夏公益教科书