跳轉到內容

結構化查詢語言/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://wikibook.tw/w/index.php?title=Structured_Query_Language/SELECT:_Fundamentals&oldid=4355264"
華夏公益教科書