postgresql的數(shù)據(jù)類型與數(shù)據(jù)表

實驗名稱：PostgreSQL的數(shù)據(jù)類型與數(shù)據(jù)表學習目的了解數(shù)據(jù)庫的數(shù)據(jù)類型以及數(shù)據(jù)表相關操作實驗環(huán)境實驗環(huán)境的操作系統(tǒng)的版本:Ubuntu22.04安裝軟件：postgresqlpgadmin4學習內(nèi)容數(shù)據(jù)類型數(shù)字類型數(shù)值類型由2字節(jié)、4字節(jié)或8字節(jié)的整數(shù)以及4字節(jié)或8字節(jié)的浮點數(shù)和可選精度的十進制數(shù)組成。下圖列出了可用的數(shù)值類型。貨幣類型money類型存儲帶有固定小數(shù)精度的貨幣金額。numeric、int和bigint類型的值可以轉(zhuǎn)換為money，不建議使用浮點數(shù)來處理處理貨幣類型，因為存在舍入錯誤的可能性。字符類型下圖列出了PostgreSQL所支持的字符類型：二進制數(shù)據(jù)類型日期/時間類型下圖列出了PostgreSQL支持的日期和時間類型。布爾類型PostgreSQL支持標準的boolean數(shù)據(jù)類型。boolean有"true"(真)或"false"(假)兩個狀態(tài)，第三種"unknown"(未知)狀態(tài)，用NULL表示。枚舉類型枚舉（enum）類型是由一個靜態(tài)、值的有序集合構成的數(shù)據(jù)類型。它們等效于很多編程語言所支持的enum類型。枚舉類型的一個例子可以是一周中的日期，或者一個數(shù)據(jù)的狀態(tài)值集合。枚舉類型可以使用\o"CREATETYPE"CREATETYPE命令創(chuàng)建，例如：一旦被創(chuàng)建，枚舉類型可以像很多其他類型一樣在表和函數(shù)定義中使用：幾何類型幾何數(shù)據(jù)類型表示二維的平面物體。下表列出了PostgreSQL支持的幾何類型。最基本的類型：點。它是其它類型的基礎。網(wǎng)絡地址類型PostgreSQL提供用于存儲IPv4、IPv6、MAC地址的數(shù)據(jù)類型。用這些數(shù)據(jù)類型存儲網(wǎng)絡地址比用純文本類型好，因為這些類型提供輸入錯誤檢查和特殊的操作和功能。在對inet或cidr數(shù)據(jù)類型進行排序的時候，IPv4地址總是排在IPv6地址前面，包括那些封裝或者是映射在IPv6地址里的IPv4地址，比如::或::ffff:。位串類型位串就是一串1和0的字符串。它們可以用于存儲和直觀化位掩碼。我們有兩種SQL位類型：bit(n)和bitvarying(n)，這里的n是一個正整數(shù)。bit類型的數(shù)據(jù)必須準確匹配長度n，試圖存儲短些或者長一些的數(shù)據(jù)都是錯誤的。bitvarying類型數(shù)據(jù)是最長n的變長類型；更長的串會被拒絕。寫一個沒有長度的bit等效于bit(1)，沒有長度的bitvarying意思是沒有長度限制。文本搜索類型全文檢索即通過自然語言文檔的集合來找到那些匹配一個查詢的檢索。PostgreSQL提供了兩種數(shù)據(jù)類型用于支持全文檢索：UUID類型數(shù)據(jù)類型uuid存儲由RFC4122、ISO/IEC9834-8:2005以及相關標準定義的通用唯一標識符（UUID）（某些系統(tǒng)將這種數(shù)據(jù)類型引用為全局唯一標識符GUID）。這種標識符是一個128位的量，它由一個精心選擇的算法產(chǎn)生，該算法能保證在已知空間中任何其他使用相同算法的人能夠產(chǎn)生同一個標識符的可能性非常非常小。因此，對于分布式系統(tǒng)，這些標識符相比序列生成器而言提供了一種很好的唯一性保障，序列生成器只能在一個數(shù)據(jù)庫中保證唯一。一個UUID被寫成一個小寫十六進制位的序列，該序列被連字符分隔成多個組：首先是一個8位組，接下來是三個4位組，最后是一個12位組。總共的32位（十六進制位）表示了128個二進制位。一個標準形式的UUID類似于：PostgreSQL也接受另一種輸入形式：使用大寫位、標準格式被花括號包圍、忽略某些或者全部連字符、在任意4位組后面增加一個連字符。例如：Xml類型xml數(shù)據(jù)類型可以用于存儲XML數(shù)據(jù)。將XML數(shù)據(jù)存到text類型中的優(yōu)勢在于它能夠為結(jié)構良好性來檢查輸入值，并且還支持函數(shù)對其進行類型安全性檢查。要使用這個數(shù)據(jù)類型，編譯時必須使用

configure--with-libxml。xml可以存儲由XML標準定義的格式良好的"文檔"，以及由XML標準中的

XMLDecl?content

定義的"內(nèi)容"片段，大致上，這意味著內(nèi)容片段可以有多個頂級元素或字符節(jié)點。xmlvalueISDOCUMENT表達式可以用來判斷一個特定的xml值是一個完整的文件還是內(nèi)容片段。創(chuàng)建XML值使用函數(shù)xmlparse:來從字符數(shù)據(jù)產(chǎn)生xml類型的值：JSON類型json數(shù)據(jù)類型可以用來存儲JSON（JavaScriptObjectNotation）數(shù)據(jù)，這樣的數(shù)據(jù)也可以存儲為text，但是json數(shù)據(jù)類型更有利于檢查每個存儲的數(shù)值是可用的JSON值。此外還有相關的函數(shù)來處理json數(shù)據(jù)：數(shù)組類型PostgreSQL允許將字段定義成變長的多維數(shù)組。數(shù)組類型可以是任何基本類型或用戶定義類型，枚舉類型或復合類型。15.1聲明數(shù)組創(chuàng)建表的時候，我們可以聲明數(shù)組，方式如下：pay_by_quarter為一維整型數(shù)組、schedule為二維文本類型數(shù)組。我們也可以使用"ARRAY"關鍵字，如下所示：15.2插入值插入值使用花括號{}，元素在{}使用逗號隔開：15.3訪問數(shù)組現(xiàn)在我們可以在這個表上運行一些查詢。首先，我們演示如何訪問數(shù)組的一個元素。這個查詢檢索在第二季度薪水變化的雇員名：數(shù)組的下標數(shù)字是寫在方括弧內(nèi)的。15.4修改數(shù)組我們可以對數(shù)組的值進行修改：或者使用ARRAY構造器語法：15.5數(shù)組中檢索要搜索一個數(shù)組中的數(shù)值，你必須檢查該數(shù)組的每一個值。比如：另外，你可以用下面的語句找出數(shù)組中所有元素值都等于10000的行：或者，可以使用generate_subscripts函數(shù)。例如：復合類型復合類型表示一行或者一條記錄的結(jié)構；它實際上只是一個字段名和它們的數(shù)據(jù)類型的列表。PostgreSQL允許像簡單數(shù)據(jù)類型那樣使用復合類型。比如，一個表的某個字段可以聲明為一個復合類型。16.1聲明復合類型下面是兩個定義復合類型的簡單例子：語法類似于CREATETABLE，只是這里只可以聲明字段名字和類型。定義了類型，我們就可以用它創(chuàng)建表：16.2復合類型值輸入要以文本常量書寫復合類型值，在圓括弧里包圍字段值并且用逗號分隔他們。你可以在任何字段值周圍放上雙引號，如果值本身包含逗號或者圓括弧，你必須用雙引號括起。復合類型常量的一般格式如下：一個例子是:16.3訪問復合類型要訪問復合類型字段的一個域，我們寫出一個點以及域的名字，非常類似從一個表名字里選出一個字段。實際上，因為實在太像從表名字中選取字段，所以我們經(jīng)常需要用圓括弧來避免分析器混淆。比如，你可能需要從on_hand例子表中選取一些子域，像下面這樣：這樣將不能工作，因為根據(jù)SQL語法，item是從一個表名字選取的，而不是一個字段名字。你必須像下面這樣寫：或者如果你也需要使用表名字(比如，在一個多表查詢里)，那么這么寫：現(xiàn)在圓括弧對象正確地解析為一個指向item字段的引用，然后就可以從中選取子域。范圍類型范圍數(shù)據(jù)類型代表著某一元素類型在一定范圍內(nèi)的值。例如，timestamp范圍可能被用于代表一間會議室被預定的時間范圍。PostgreSQL內(nèi)置的范圍類型有：int4range—integer的范圍int8range—bigint的范圍numrange—numeric的范圍tsrange—timestampwithouttimezone的范圍tstzrange—timestampwithtimezone的范圍daterange—date的范圍此外，你可以定義你自己的范圍類型。范圍值的輸入必須遵循下面的格式：圓括號或者方括號顯示下邊界和上邊界是不包含的還是包含的。注意最后的格式是空，代表著一個空的范圍（一個不含有值的范圍）。對象標識符類型PostgreSQL在內(nèi)部使用對象標識符(OID)作為各種系統(tǒng)表的主鍵。同時，系統(tǒng)不會給用戶創(chuàng)建的表增加一個OID系統(tǒng)字段(除非在建表時聲明了WITHOIDS或者配置參數(shù)default_with_oids設置為開啟)。oid類型代表一個對象標識符。除此以外oid還有幾個別名：regproc,regprocedure,regoper,regoperator,regclass,regtype,regconfig,和regdictionary。偽類型PostgreSQL類型系統(tǒng)包含一系列特殊用途的條目，它們按照類別來說叫做偽類型。偽類型不能作為字段的數(shù)據(jù)類型，但是它可以用于聲明一個函數(shù)的參數(shù)或者結(jié)果類型。偽類型在一個函數(shù)不只是簡單地接受并返回某種SQL數(shù)據(jù)類型的情況下很有用。下表列出了所有的偽類型：數(shù)據(jù)表1.創(chuàng)建數(shù)據(jù)表創(chuàng)建數(shù)據(jù)表需要使用SQL中的CREATETABLE語句，該語句的基本語法如下：其中，table_name為表名，column_name為列名，datatype為列的數(shù)據(jù)類型，constraint為列約束。下面是一個創(chuàng)建用戶信息表的示例：該語句將創(chuàng)建一個名為userinfo的數(shù)據(jù)表，包含了五個列：id、name、age、email和birthday。其中，id列是主鍵，并且使用了SERIAL數(shù)據(jù)類型，可以自動遞增；name列使用了VARCHAR數(shù)據(jù)類型，長度限制為50，且不能為空；age列使用了SMALLINT數(shù)據(jù)類型；email列使用了VARCHAR數(shù)據(jù)類型，長度限制為50，并且是唯一的；birthday列使用了DATE數(shù)據(jù)類型。2.刪除數(shù)據(jù)表刪除數(shù)據(jù)表需要使用SQL中的DROPTABLE語句，該語句的基本語法如下：其中，table_name為要刪除的表名。下面是一個示例刪除userinfo表的語句：執(zhí)行該語句后，將會刪除名為userinfo的數(shù)據(jù)表。3.插入數(shù)據(jù)向數(shù)據(jù)表中插入數(shù)據(jù)需要使用SQL中的INSERTINTO語句，該語句的基本語法如下：其中，table_name為要插入數(shù)據(jù)的表名，column1、column2、column3等為要插入的列名，value1、value2、value3等為要插入的值。下面是一個向userinfo表中插入一條數(shù)據(jù)的示例：該語句將會向名為userinfo的數(shù)據(jù)表中插入一條數(shù)據(jù)，包含了name、age、email和birthday四個列的值。其中，name列的值為'張三'，age