SQL資料函式庫資料型別深入解析

在 SQL 資料函式庫設計中，選擇合適的資料型別至關重要，它直接影響資料函式庫的效能、儲存空間以及資料的完整性。本文將會詳細介紹 SQL 中常用的字元型別和數值型別，並以 PostgreSQL 資料函式庫為例，說明不同資料型別的特性、使用場景以及注意事項。此外，還會探討浮點數運算的潛在問題以及如何選擇正確的數字型別，並示範如何在 PostgreSQL 中設定自動遞增整數。

綜合運用

清單 3-10 展示了一個綜合運用了 WHERE 和 ORDER BY 子句的查詢範例。

SELECT first_name, last_name, school, hire_date, salary
FROM teachers
WHERE school LIKE '%Roos%'
ORDER BY hire_date DESC;

這個查詢傳回在 Roosevelt High School 任教的老師，並按照聘用日期從新到舊排序。

練習題

1. 請寫出一個查詢，按照學校名稱的字母順序列出每個學校的老師，並按照老師的姓氏字母順序排序。
2. 請寫出一個查詢，找出第一個名字以字母 “S” 開頭且薪水超過 $40,000 的老師。
3. 請寫出一個查詢，按照薪水從高到低的順序列出自 2010 年 1 月 1 日以來聘用的老師。

資料型別的重要性

瞭解資料型別對於建立可用的資料函式庫和進行準確的分析至關重要。在本章中，我們將探討 SQL 中的資料型別，並瞭解如何檢查資料函式庫中的資料型別。這些知識將有助於你更好地理解資料函式庫結構，並為後續的學習打下基礎。

資料函式庫中的字元資料型別

在SQL資料函式庫中，每個欄位（column）只能儲存一種資料型別，這種資料型別是在CREATE TABLE陳述式中定義的。在以下的簡單範例表格中，我們可以看到三種不同的資料型別：日期（date）、整數（integer）和文字（text）。

CREATE TABLE eagle_watch (
    observation_date date,
    eagles_seen integer,
    notes text
);

這個名為eagle_watch的表格（用於假設的禿鷹清單），我們宣告observation_date欄位儲存日期值，eagles_seen欄位儲存整數值，而notes欄位則儲存字元。

瞭解字元資料型別

字元字串型別是通用型別，適合儲存任何文字、數字和符號的組合。字元型別包括以下幾種：

char(n)

一個固定長度的欄位，其字元長度由n指定。設定為char(20)的欄位會在每個列中儲存20個字元，無論你插入多少字元。如果在任何列中插入少於20個字元，PostgreSQL會用空格填充該欄位的其餘部分。這種型別是標準SQL的一部分，也可以用較長的名稱character(n)來指定。如今，char(n)的使用頻率很低，主要是遺留電腦系統的產物。

varchar(n)

一個可變長度的欄位，其最大長度由n指定。如果插入的字元少於最大長度，PostgreSQL不會儲存額外的空格。例如，字串blue會佔用4個空格，而字串123則會佔用3個空格。在大型資料函式庫中，這種做法可以節省相當大的空間。這種型別包含在標準SQL中，也可以使用較長的名稱character varying(n)來指定。

text

一個無限長度的可變長度欄位。（根據PostgreSQL檔案的說明，你可以儲存的最長字元字串大約是1GB。）text型別不是SQL標準的一部分，但你會在其他資料函式庫系統中找到類別似的實作，包括Microsoft SQL Server和MySQL。

根據PostgreSQL檔案的說明，這三種型別在效能上沒有顯著差異。但如果你使用的是其他資料函式倉管理器，那麼檢查檔案是明智的做法。varchar和text的彈性和潛在空間節省似乎使它們佔有優勢。但如果你搜尋網上的討論，有些使用者建議使用char來定義一個總是具有相同字元數的欄位，以此來表示它應該包含的資料。例如，你可能會看到char(2)被用於美國州郵政縮寫。

注意事項

你不能對儲存在字元欄位中的數字執行數學運算。只有當數字代表程式碼（如美國郵政ZIP程式碼）時，才將數字儲存為字元型別。

#### 內容解密：

本段落說明瞭在SQL資料函式庫中定義欄位時使用的不同字元資料型別。主要介紹了三種字元型別：char(n)、varchar(n)和text，並且討論了它們之間的區別和使用場景。其中，char(n)是固定長度的，而varchar(n)和text則是可變長度的。選擇合適的字元型別對於資料函式庫的效能和儲存空間有著重要的影響。

實際操作：字元資料型別的範例

為了檢視這三種字元型別的實際運作，請執行清單4-1所示的指令碼。此指令碼將建立並載入一個簡單的表格，然後將資料匯出到電腦上的文字檔中。

CREATE TABLE char_data_types (
    char_column char(10),
    varchar_column varchar(10),
    text_column text
);

INSERT INTO char_data_types
VALUES
    ('abc', 'abc', 'abc'),
    ('defghi', 'defghi', 'defghi');

COPY char_data_types TO 'C:\\YourDirectory\\typetest.txt'
WITH (FORMAT CSV, HEADER, DELIMITER '|');

#### 內容解密：

這段程式碼首先建立了一個名為char_data_types的表格，包含三個字元欄位：char_column、varchar_column和text_column。然後，它向這個表格中插入了兩行資料，每行的三個欄位分別插入了相同的字串。最後，使用COPY命令將表格中的資料匯出到一個名為typetest.txt的文字檔中，並使用管道符（|）作為分隔符。

檢視輸出結果

開啟你儲存在第1章中安裝的文字編輯器中的typetest.txt檔案（不是Word或Excel，或其他試算表應用程式）。內容應該如下所示：

char_column|varchar_column|text_column
abc       |abc|abc
defghi    |defghi|defghi

即使你為char和varchar欄位都指定了10個字元，只有char欄位在兩行中都輸出10個字元，用空格填充未使用的字元。 varchar和text欄位只儲存你插入的字元。

#### 內容解密：

本段落展示了執行清單4-1指令碼後得到的輸出結果。可以看到，對於char(10)型別的欄位，如果插入的字串少於10個字元，則會用空格填充到10個字元。而對於varchar(10)和text型別的欄位，則只會儲存實際插入的字串，不會進行填充。這說明瞭不同字元型別在儲存資料時的差異。

資料函式庫中的數字型別與自動遞增整數

在設計資料函式庫時，選擇適當的資料型別對於儲存效率和資料完整性至關重要。數字型別是常見的資料型別之一，用於儲存整數、固定點數和浮點數。本篇文章將探討SQL中的數字型別，並介紹如何在PostgreSQL中實作自動遞增整數。

數字型別的選擇

SQL提供了多種數字型別，包括整數、固定點數和浮點數。選擇正確的數字型別取決於資料的性質和運算需求。

• 整數型別：用於儲存整數，包括正整數、負整數和零。常見的整數型別包括smallint、integer和bigint。
• 固定點數和浮點數：用於儲存小數，分別採用固定點表示法和浮點數表示法。

整數型別的比較

| 資料型別 | 儲存大小 | 範圍 | |

– |

• |

• | | smallint | 2位元組 | -32768至+32767 | | integer | 4位元組 | -2147483648至+2147483647 | | bigint | 8位元組 | -9223372036854775808至+9223372036854775807 |

在選擇整數型別時，應根據資料的範圍和儲存需求進行選擇。bigint型別提供了最大的範圍，但佔用最多的儲存空間。如果資料範圍在integer之內，使用integer可以節省儲存空間。當資料範圍較小，如月份或年份，使用smallint可以進一步減少儲存需求。

自動遞增整數

自動遞增整數是一種特殊的整數型別，用於在插入新資料列時自動生成唯一的識別碼。在PostgreSQL中，可以使用serial或IDENTITY關鍵字實作自動遞增整數。

使用serial實作自動遞增

在PostgreSQL中，serial是一種特殊的資料型別，實際上是對整數型別的封裝。使用serial可以輕鬆實作自動遞增整數。

CREATE TABLE people (
    id serial,
    person_name varchar(100)
);

在上述範例中，id欄位被定義為serial型別，每次插入新資料列時，id將自動遞增。

#### 內容解密：

1. CREATE TABLE people：建立一個名為 people 的新資料表。
2. id serial：定義 id 欄位並設為 serial 型別，這是一種特殊的整數型別，能夠自動遞增。每當插入新的資料列時，PostgreSQL 將自動為 id 欄位生成一個唯一的值，從1開始遞增，直到達到該整數型別的最大值。
3. person_name varchar(100)：定義 person_name 欄位，用於儲存人名，最大長度為100個字元。

使用IDENTITY實作自動遞增

從PostgreSQL 10版本開始，支援ANSI SQL標準的IDENTITY關鍵字，用於實作自動遞增整數。與serial相比，IDENTITY提供了更多的控制選項，例如防止使用者手動插入值。

CREATE TABLE people (
    id integer GENERATED ALWAYS AS IDENTITY,
    person_name varchar(100)
);

在上述範例中，id欄位被定義為具有GENERATED ALWAYS AS IDENTITY屬性的整數型別，這意味著PostgreSQL將始終自動為該欄位生成值。

#### 內容解密：

1. id integer GENERATED ALWAYS AS IDENTITY：定義 id 欄位為整數型別，並設定為 GENERATED ALWAYS AS IDENTITY，表示該欄位的值將由資料函式庫系統自動生成並遞增。
   • GENERATED ALWAYS AS IDENTITY 保證了該欄位的值總是由系統自動產生，使用者無法手動插入或更新該欄位的值，除非明確使用特定的語法來覆寫此行為。
2. 自動遞增邏輯由資料函式庫內部維護，每次插入新資料列時，系統會根據當前最大值自動遞增 id 的值，從而確保每個資料列具有唯一的 id。
3. 這種做法避免了手動管理主鍵的麻煩，並減少了因重複或錯誤的主鍵值而導致的資料一致性問題。

使用小數

小數代表一個整數加上整數的一部分；這個部分由小數點後的數字表示。在SQL資料函式庫中，小數由固定點和浮點數資料型別來處理。例如，從我家到最近的雜貨店的距離是6.7英里；我可以將6.7插入固定點或浮點數欄位，PostgreSQL不會有任何抱怨。唯一的差異是電腦如何儲存資料。接下來，你會看到這有重要的影響。

瞭解固定點數字

固定點型別，也稱為任意精確度型別，是numeric(precision,scale)。你需要提供precision作為小數點左右兩邊的最大數字位數，以及scale作為小數點右邊允許的數字位數。另外，你也可以使用decimal(precision,scale)來指定此型別。兩者都是ANSI SQL標準的一部分。如果你省略指定scale值，則scale將被設為零；實際上，這會建立一個整數。如果你省略指定precision和scale，則資料函式庫將儲存任意精確度和比例的值，直到允許的最大值。（根據PostgreSQL檔案，https://www.postgresql.org/docs/current/datatype-numeric.html，小數點前的最大位數為131,072，小數點後的位數為16,383。）

例如，假設你正在收集幾個當地機場的降雨總量——這不是一個不尋常的資料分析任務。美國國家氣象服務提供的資料中，降雨量通常測量到小數點後兩位。（而且，如果你像我一樣，你會有小學數學老師解釋小數點後兩位是百分位的模糊記憶。）

要在資料函式庫中記錄降雨量，使用總共五位數字（精確度）和小數點右邊最多兩位數字（比例），你需要將其指定為numeric(5,2)。無論你是否輸入包含兩位小數的數字，資料函式庫總是會傳回小數點右邊的兩位數字，例如1.47、1.00和121.50。

瞭解浮點數型別

兩種浮點數型別是real和double precision，兩者都是SQL標準的一部分。兩者的區別在於它們儲存的資料量。real型別允許精確度達到小數點後六位，而double precision則達到小數點後15位，兩者都包括小數點兩邊的數字位數。這些浮點數型別也被稱為可變精確度型別。資料函式庫將數字儲存在代表數字和指數的部分——即小數點所屬的位置。因此，與我們指定固定精確度和比例的numeric不同，給定欄位中的小數點可以根據數字而「浮動」。

使用固定點和浮點數型別

每種型別對總位數或精確度都有不同的限制，如表4-3所示。

表4-3：固定點和浮點數資料型別

要了解這三種資料型別如何處理相同的數字，請建立一個小型表格並插入各種測試案例，如清單4-2所示。

CREATE TABLE number_data_types (
    numeric_column numeric(20,5),
    real_column real,
    double_column double precision
);

INSERT INTO number_data_types
VALUES
    (.7, .7, .7),
    (2.13579, 2.13579, 2.13579),
    (2.1357987654, 2.1357987654, 2.1357987654);

SELECT * FROM number_data_types;

清單4-2：數字資料型別在運作中

我們建立了一個表格，每種分數資料型別都有一個欄位，並將三行資料載入表格。每行在所有三個欄位中重複相同的數字。當指令碼的最後一行執行並從表格中選取所有資料時，我們得到以下結果：

注意發生了什麼。設定了五位小數比例的numeric欄位，無論是否插入那麼多位數，都儲存了小數點後五位數字。如果少於五位，則用零填充。如果超過五位，則進行四捨五入——就像第三行數字有10位小數一樣。

real和double precision欄位沒有新增填充。在第三行，你可以看到PostgreSQL在這兩個欄位中的預設行為，即使用最短的精確十進製表示法輸出浮點數字，而不是顯示整個值。請注意，舊版本的PostgreSQL可能會顯示稍微不同的結果。

浮點數學運算中的問題

如果你認為“以浮點數儲存的數字看起來就像以固定點儲存的數字”，那麼請謹慎行事。電腦儲存浮點數字的方式可能導致意外的數學錯誤。看看我們對這些數字進行一些計算時會發生什麼。執行清單4-3中的指令碼。

SELECT
    numeric_column * 10000000 AS fixed,
    real_column * 10000000 AS floating
FROM number_data_types
WHERE numeric_column = .7;

清單4-3：浮點數欄位的四捨五入問題

在這裡，我們將numeric_column和real_column乘以1000萬，並使用WHERE子句過濾出第一行。我們應該得到兩個計算結果相同的結果，對吧？以下是查詢傳回的結果：

天啊！難怪浮點數型別被稱為“不精確”。幸好我不是用這個數學來發射火星任務或計算聯邦預算赤字。

浮點數學運算產生這種錯誤的原因是電腦試圖將大量資訊擠入有限的位元中。這個主題有很多相關的文章，超出了本文的範圍，但如果你感興趣，可以在https://www.nostarch.com/practical-sql-2nd-edition/找到一個很好的綜述連結。

選擇你的數字資料型別

目前，在處理數字資料型別時，有三個指導原則需要考慮：

1. 盡可能使用整數。除非你的資料使用小數，否則請堅持使用整數型別。

2. 如果你正在處理小數資料並且需要計算精確（例如處理金錢），請選擇numeric或其等價型別。

3. numeric資料型別所需的儲存空間是可變的，並且根據指定的精確度和比例，numeric可能比浮點數型別佔用更多的空間。如果你正在處理數百萬行，那麼值得考慮是否可以接受相對不精確的浮點數學運算。

程式碼解析：

清單4-2程式碼解析：

CREATE TABLE number_data_types (
    numeric_column numeric(20,5),
    real_column real,
    double_column double precision
);

此段程式碼建立了一個名為 number_data_types 的表格，包含三個欄位： numeric_column、real_column 和 double_column。

• numeric_column 使用 numeric(20,5) 型別，表示總共有20位數字，其中有5位是小數。
• real_column 使用 real 型別，表示一個單精確度的浮點數。
• double_column 使用 double precision 型別，表示一個雙精確度的浮點數。

INSERT INTO number_data_types
VALUES
    (.7, .7, .7),
    (2.13579, 2.13579, 2.13579),
    (2.1357987654, 2.1357987654, 2.1357987654);

此段程式碼向 number_data_types 表格中插入了三行資料，每行包含相同的數字，分別插入到 numeric_column、real_column 和 double_column 中。

• 第一個值是0.7。
• 第二個值是2.13579。
• 第三個值是2.1357987654，具有較多的小數位。

SELECT * FROM number_data_types;

此查詢陳述式選取 number_data_types 表格中的所有資料，並顯示結果。

清單4-3程式碼解析：

SELECT
    numeric_column * 10000000 AS fixed,
    real_column * 10000000 AS floating
FROM number_data_types
WHERE numeric_column = .7;

此查詢陳述式從 number_data_types 表格中選取資料，並進行計算。

• 將 numeric_column 和 real_column 分別乘以10000000，並將結果命名為 fixed 和 floating。
• 使用 WHERE 子句篩選出 numeric_column 等於0.7的行。