麻辣家族討論版版 » PHP » PHP+MySQL 入門實作 » 7-4 建立資料表(7-4-1 資料表的結構)

小誌 發表於 2011-4-20 09:03

7-4 建立資料表(7-4-1 資料表的結構)

[color=DarkRed][size=4][b]7-4   建立資料表[/b][/size][/color]
資料庫只是一個儲存資料的空間，要如何讓儲存於其中的資料能格式化分門別類的放置？那就必須透過資料表「Table」來處理了，例如學生的聯絡資料存放在「學生通訊錄」資料表、客戶的訂單資料存放在「訂單」資料表中，將不同類別的資料集中放置於不同的資料表裡加以管理！

[color=DarkRed][b][size=4]7-4-1 資料表的結構[/size][/b][/color]資料表中有兩項基礎的單元：欄位（Column）與列（Row），欄位用以儲存不同性質的資料，例如學號、姓名…等，而每一列中的各個欄位則構成一筆資料記錄（Record）：
[attach]5612[/attach]
圖7-30  欄位與資料記錄。

小誌 發表於 2011-4-20 09:11

要資料表能夠存放資料，首先要定義資料表的規格，所謂的規格就是決定資料表中有幾個存放資料的欄位，還有這些欄位存放資料時的資料型態、資料長度…等限制規定。

[b][size=3][color=RoyalBlue]欄位的命名[/color][/size][/b]
每張資料表可有多個欄位，每個欄位負責存放不同類型的資料，為了辨識這些欄位，所以必須為資料表中的欄位訂立一個識別名稱，命名基本原則如下：
1.同一張資料表中的欄位名稱不可重複。
2.欄位名稱可用中文（任何字符，除了「/」或「.」）。
3.欄位名稱最多為64個字元。
4.欄位名稱不得為MySQL內的Transact-SQL保留字（保留字列表詳見附錄）。

小誌 發表於 2011-4-20 09:15

[color=royalblue][size=3][b]欄位的資料型態與儲存大小[/b][/size][/color]
在MySQL 中提供了多種資料型態：

[color=magenta]1.數值型態(Numeric Types)[/color]
[table]
[tr][td=1,1,139][b][size=2]資料類型[/size][/b][/td][td=1,1,95][b][size=2]儲存大小[/size][/b][/td][td=1,1,279][size=2][b]資料範圍[/b][b][/b][/size][/td][/tr]
[tr][td=1,1,139][color=#000000][size=2]TINYINT[(M)][/size][/color][/td][td=1,1,95][color=#000000][size=2]1位元組[/size][/color][/td][td=1,1,279][color=#000000][size=2]有正負號為 -128 to 127[/size][/color][color=#000000][size=2]正負號為 0 to 255[/size][/color][/td][/tr]
[tr][td=1,1,139][color=#000000][size=2]SMALLINT[(M)][/size][/color][/td][td=1,1,95][color=#000000][size=2]2位元組[/size][/color][/td][td=1,1,279][color=#000000][size=2]有正負號為 -32768 to 32767[/size][/color][color=#000000][size=2]無正負號為 0 to 65535[/size][/color][/td][/tr]
[tr][td=1,1,139][color=#000000][size=2]MEDIUMINT[(M)][/size][/color][/td][td=1,1,95][color=#000000][size=2]3位元組[/size][/color][/td][td=1,1,279][color=#000000][size=2]有正負號為 -8388608 to 8388607[/size][/color][color=#000000][size=2]無正負號為 0 to 16777215[/size][/color][/td][/tr]
[tr][td=1,1,139][color=#000000][size=2]INT[(M)][/size][/color][/td][td=1,1,95][color=#000000][size=2]4位元組[/size][/color][/td][td=1,1,279][color=#000000][size=2]有正負號為 -2147483648 to 2147483647[/size][/color][color=#000000][size=2]無正負號為 0 to 4294967295[/size][/color][/td][/tr]
[tr][td=1,1,139][color=#000000][size=2]INTERGER[(M)][/size][/color][/td][td=1,1,95][color=#000000][size=2]4位元組[/size][/color][/td][td=1,1,279][color=#000000][size=2]與INT ODBC 兼容使用[/size][/color][/td][/tr]
[tr][td=1,1,139][color=#000000][size=2]BIGINT[(M)][/size][/color][/td][td=1,1,95][color=#000000][size=2]8位元組[/size][/color][/td][td=1,1,279][color=#000000][size=2]有正負號為 -9223372036854775808 to 9223372036854775807[/size][/color][color=#000000][size=2]無正負號為 0 to[font=Courier New] [/font]18446744073709551615[/size][/color][/td][/tr]
[tr][td=1,1,139][color=#000000][size=2]FLOAT[(M,D)]
PRECISION [/size][/color][/td][td=1,1,95][color=#000000][size=2](4):4位元組[/size][/color][color=#000000][size=2](8):8位元組[/size][/color][/td][td=1,1,279][size=2][color=#000000]FLOAT(4)->FLOAT([/color][/size][size=2][color=#000000]單精準[/color][/size][color=#000000][size=2])
FLOAT(8)->DOUBLE(雙精準) [/size][/color][/td][/tr]
[tr][td=1,1,139][color=#000000][size=2]FLOAT[(M,D)][/size][/color][/td][td=1,1,95][color=#000000][size=2]4位元組[/size][/color][/td][td=1,1,279][color=#000000][size=2]-3.402823466E+38 to -1.175494351E-38及1.175494351E-38 to 3.402823466E+38[/size][/color][/td][/tr]
[tr][td=1,1,139][color=#000000][size=2]DOUBLE[(M,D)][/size][/color][/td][td=1,1,95][color=#000000][size=2]8位元組[/size][/color][/td][td=1,1,279][color=#000000][size=2]-1.7976931348623157E+308 to -2.2250738585072014E-308
2.2250738585072014E-308 to 1.7976931348623157E+308[/size][/color][/td][/tr]
[tr][td=1,1,139][color=#000000][size=2]DOUBLE[(M,D)]
PRECISION[/size][/color][/td][td=1,1,95][color=#000000][size=2]8位元組[/size][/color][/td][td=1,1,279][color=#000000][size=2]同DOUBLE[/size][/color][/td][/tr]
[tr][td=1,1,139][color=#000000][size=2]REAL[(M,D)][/size][/color][/td][td=1,1,95][color=#000000][size=2]8位元組[/size][/color][/td][td=1,1,279][color=#000000][size=2]同DOUBLE[/size][/color][/td][/tr]
[tr][td=1,1,139][color=#000000][size=2]DECIMA[(m,d)][/size][/color][/td][td=1,1,95][color=#000000][size=2]X位元組[/size][/color][/td][td=1,1,279][color=#000000][size=2]最大值同DOUBLE 實際範圍要看m及d[/size][/color][/td][/tr]
[tr][td=1,1,139][color=#000000][size=2]NUMERIC[(m,d)][/size][/color][/td][td=1,1,95][color=#000000][size=2]同DECIMAL[/size][/color][/td][td=1,1,279][color=#000000][size=2]同DECIMAL[/size][/color][/td][/tr]
[/table]

上表中字母代號意義：
[list]
[*]M：最大的顯示尺寸，最大的值為 255 。
[*]D：適用於浮點類型，用於指出跟隨在十進制小數點後的數碼的數量，最大值是30，但不可大於M-2。
[*]BIGINT[(M)]：當兩個參數是INTEGER值時，其+、-和*（加、減、乘法）將使用BIGINT運算；所有算術運算應用有符號的BIGINT或DOUBLE值完成，除了函數外，不應使用大於9223372036854775807（63位)的有符號BIGINT。
[*]FLOAT[(M,D)]：一個單精準浮點數值，不能沒有正負號。允許的值是-3.402823466E+38到-1.175494351E-38，0 和1.175494351E-38到3.402823466E+38，M是顯示寬度而D是小數的位數。
[*]DOUBLE[(M,D)] ：一個倍精準浮點數值不能無符號，允許的值是-1.7976931348623157E+308到-2.2250738585072014E-308、0和2.2250738585072014E-308到1.7976931348623157E+308，M是顯示寬度而D是小數位數。
[*]Decimal、Numeric：基本上，此兩種資料型態是一樣的，最大有效位數值為38，應用上應該使用Decimal，因為Numeric是為了向前相容的原因而存在。
[list]
[*]m：指定小數點左方及右方可儲存的十進位數總和的上限，就是指整數與小數的總合位數。
[*]d：指定小數點右方可儲存的十進位個數總和的上限，即指定小數點後的小數位數，其值可介於0~M之間。
[/list][*]Decimal與Numeric資料型態的欄位不需指定長度，其使用空間視精確度高低自動調整。
[/list]

小誌 發表於 2011-4-20 09:19

[color=magenta]2.字串型態(String Types)[/color]
[table]
[tr][td=1,1,125][p=30, 2, center][b][size=2]資料型態[/size][/b][/p][/td][td=1,1,155][p=30, 2, center][b][size=2]儲存大小[/size][/b][/p][/td][td=1,1,241][p=30, 2, center][size=2][b]資料範圍[/b][/size][/p][/td][/tr]
[tr][td=1,1,125][p=30, 2, left][color=#000000][size=2]CHAR[font=Courier New] [/font](M)[/size][/color][/p][/td][td=1,1,155][p=30, 2, left][color=#000000][size=2]M 個位元組[/size][/color][/p][p=30, 2, left][color=#000000][size=2]1<=M<=255 [/size][/color][/p][/td][td=1,1,241][p=30, 2, left][color=#000000][size=2]M為指定的資料長度[/size][/color][/p][p=30, 2, left][color=#000000][size=2]M由1到255字元 [/size][/color][/p][/td][/tr]
[tr][td=1,1,125][p=30, 2, left][color=#000000][size=2]VARCHAR[font=Courier New] [/font](M)[/size][/color][/p][/td][td=1,1,155][p=30, 2, left][color=#000000][size=2]L+1 個位元組[/size][/color][/p][p=30, 2, left][color=#000000][size=2]L<=M1<=M<=255 [/size][/color][/p][/td][td=1,1,241][p=30, 2, left][size=2][color=#000000]資料長度[/color][/size][color=#000000][size=2]<=M
M由1到255字元 [/size][/color][/p][/td][/tr]
[tr][td=1,1,125][p=30, 2, left][color=#000000][size=2]TINYTEXT、TINYBLOB[/size][/color][/p][/td][td=1,1,155][p=30, 2, left][color=#000000][size=2]L+1個位元組[/size][/color][/p][p=30, 2, left][color=#000000][size=2]L<2^8 [/size][/color][/p][/td][td=1,1,241][p=30, 2, left][color=#000000][size=2]最大長度255字元(2^8-1)[/size][/color][/p][/td][/tr]
[tr][td=1,1,125][p=30, 2, left][color=#000000][size=2]TEXT、 BLOB[/size][/color][/p][/td][td=1,1,155][p=30, 2, left][color=#000000][size=2]L+2個位元組[/size][/color][/p][p=30, 2, left][color=#000000][size=2]L<2^16 [/size][/color][/p][/td][td=1,1,241][p=30, 2, left][color=#000000][size=2]最大長度65535字元(2^16-1)[/size][/color][/p][/td][/tr]
[tr][td=1,1,125][p=30, 2, left][color=#000000][size=2]MEDIUMTEXT、MEDIUMBLOB[/size][/color][/p][/td][td=1,1,155][p=30, 2, left][color=#000000][size=2]L+3個位元組[/size][/color][/p][p=30, 2, left][color=#000000][size=2]L<2^24 [/size][/color][/p][/td][td=1,1,241][p=30, 2, left][color=#000000][size=2]最大長度16777215字元(2^24-1)[/size][/color][/p][/td][/tr]
[tr][td=1,1,125][p=30, 2, left][color=#000000][size=2]LONGTEXT、LONGBLOB[/size][/color][/p][/td][td=1,1,155][p=30, 2, left][color=#000000][size=2]L+4個位元組[/size][/color][/p][p=30, 2, left][color=#000000][size=2]L<2^32 [/size][/color][/p][/td][td=1,1,241][p=30, 2, left][color=#000000][size=2]最大長度4294967295字元(2^32-1)[/size][/color][/p][/td][/tr]
[tr][td=1,1,125][p=30, 2, left][color=#000000][size=2]ENUM[/size][/color][/p][/td][td=1,1,155][p=30, 2, left][color=#000000][size=2]1 or 2個位元組[/size][/color][/p][/td][td=1,1,241][p=30, 2, left][color=#000000][size=2]最大有65535種值[/size][/color][/p][/td][/tr]
[tr][td=1,1,125][p=30, 2, left][color=#000000][size=2]SET[/size][/color][/p][/td][td=1,1,155][p=30, 2, left][color=#000000][size=2]1,2,3 to 8 byte[/size][/color][/p][/td][td=1,1,241][p=30, 2, left][color=#000000][size=2]最大有64個值 [/size][/color][/p][/td][/tr]
[/table]

Char(M)：用來儲存固定長度且非Unicode的字元資料，一般用來儲存固定長度的資料，例如身分證字號，設定為Char(M)的欄位其使用的空間大小就是M個位元組，當輸入的資料長度未達指定的M個位元組時，將會在資料後面補上空白字元（Space）直到該欄位設定的最大長度，當值被檢索時，空格尾部（資料後面補上的空白字元）會被刪除；Char值會根據預設字元集以大小寫不區分的方式進行排序和比較。

varChar(M)：用來儲存非固定長度且非Unicode的字元資料，設定為Char(n)的欄位其使用的空間大小並非就是M個位元組，使用的空間大小視輸入的資料長度而，當輸入的資料長度未達指定的M個位元組時，不會在資料後面補上空白字元（Space），而當輸入的資料長度大於指定的M個位元組時，則多餘的部份將被截除；varChar的長度為實際資料長度外加一個位元組記錄長度；當值被檢索時，空格尾部（資料後面補上的空白字元）會被刪除；varChar值會根據預設字元集以大小寫不區分的方式進行排序和比較。

透過下表將可發現Char、varChar兩種類型的列的不同：
[table]
[tr][td=1,1,106][p=30, 2, center][b][size=2]資料[/size][/b][/p][/td][td=1,1,85][p=30, 2, center][size=2][b][font=細明體][size=12pt]CHAR(5)[/size][/font][/b][b][/b][/size][/p][/td][td=1,1,97][p=30, 2, center][size=2][b]需求[/b][b][/b][/size][/p][/td][td=1,1,120][p=30, 2, center][size=2][b][font=細明體][size=12pt]VARCHAR(5)[/size][/font][/b][b][/b][/size][/p][/td][td=1,1,95][p=30, 2, center][size=2][b]需求[/b][b][/b][/size][/p][/td][/tr]
[tr][td=1,1,106][p=30, 2, left][size=2][font=細明體][size=12pt]''[/size][/font] [/size][/p][/td][td=1,1,85][p=30, 2, left][size=2][font=細明體][size=12pt]'
'[/size][/font] [/size][/p][/td][td=1,1,97][p=30, 2, left][size=2]5 個[color=#000000]位元組[/color][/size][/p][/td][td=1,1,120][p=30, 2, left][size=2][font=細明體][size=12pt]''[/size][/font] [/size][/p][/td][td=1,1,95][p=30, 2, left][size=2]1 [color=#000000]位元組[/color][/size][/p][/td][/tr]
[tr][td=1,1,106][p=30, 2, left][size=2][font=細明體][size=12pt]'XY'[/size][/font] [/size][/p][/td][td=1,1,85][p=30, 2, left][size=2][font=細明體][size=12pt]'XY
'[/size][/font] [/size][/p][/td][td=1,1,97][p=30, 2, left][size=2]5 個[color=#000000]位元組[/color][/size][/p][/td][td=1,1,120][p=30, 2, left][size=2][font=細明體][size=12pt]'XY'[/size][/font] [/size][/p][/td][td=1,1,95][p=30, 2, left][size=2]3 個[color=#000000]位元組[/color][/size][/p][/td][/tr]
[tr][td=1,1,106][p=30, 2, left][size=2][font=細明體][size=12pt]'XYZAS'[/size][/font] [/size][/p][/td][td=1,1,85][p=30, 2, left][size=2][font=細明體][size=12pt]'XYZAS'[/size][/font] [/size][/p][/td][td=1,1,97][p=30, 2, left][size=2]5 個[color=#000000]位元組[/color][/size][/p][/td][td=1,1,120][p=30, 2, left][size=2][font=細明體][size=12pt]'XYZAS'[/size][/font] [/size][/p][/td][td=1,1,95][p=30, 2, left][size=2]6 個[color=#000000]位元組[/color][/size][/p][/td][/tr]
[tr][td=1,1,106][p=30, 2, left][size=2][font=細明體][size=12pt]'abcdefgh'[/size][/font] [/size][/p][/td][td=1,1,85][p=30, 2, left][size=2][font=細明體][size=12pt]'abcde'[/size][/font] [/size][/p][/td][td=1,1,97][p=30, 2, left][size=2]5 個[color=#000000]位元組[/color][/size][/p][/td][td=1,1,120][p=30, 2, left][size=2][font=細明體][size=12pt]'abcde'[/size][/font] [/size][/p][/td][td=1,1,95][p=30, 2, left][size=2]6 個[color=#000000]位元組[/color][/size][/p][/td][/tr]
[/table]

[color=Red]提示！！[/color]
「Unicode（萬國字元編碼）」：就是每一個字元都是以2個位元組的型式表示，不管是繁體中文字、英文字母、簡體中文字…等，都視為一個字元，存放資料的空間一律給予2個位元組的長度。

小誌 發表於 2011-4-20 09:21

[color=magenta]3.日期時間型態(Date and Time Types)[/color]
[table]
[tr][td=1,1,128][p=30, 2, center][b][size=2]資料型態[/size][/b][/p][/td][td=1,1,92][p=30, 2, center][b][size=2]儲存大小[/size][/b][/p][/td][td=1,1,282][p=30, 2, center][size=2][b]資料範圍[/b][/size][/p][/td][/tr]
[tr][td=1,1,128][p=30, 2, left][color=#000000][size=2]DATE[/size][/color][/p][/td][td=1,1,92][p=30, 2, left][color=#000000][size=2]3位元組[/size][/color][/p][/td][td=1,1,282][p=30, 2, left][color=#000000][size=2]'1000-01-01' TO '9999-12-31'[/size][/color][/p][p=30, 2, left][color=#000000][size=2]格式為YYYY-MM-DD [/size][/color][/p][/td][/tr]
[tr][td=1,1,128][p=30, 2, left][color=#000000][size=2]DATETIME[/size][/color][/p][/td][td=1,1,92][p=30, 2, left][color=#000000][size=2]8位元組[/size][/color][/p][/td][td=1,1,282][p=30, 2, left][color=#000000][size=2]'1000-01-01 00:00:00' 到[/size][/color][/p][p=30, 2, left][color=#000000][size=2]'9999-12-31 23:59:59'[/size][/color][/p][/td][/tr]
[tr][td=1,1,128][p=30, 2, left][color=#000000][size=2]TIMESTAMP[/size][/color][/p][/td][td=1,1,92][p=30, 2, left][color=#000000][size=2]4位元組[/size][/color][/p][/td][td=1,1,282][p=30, 2, left][color=#000000][size=2]'1970-01-01 00:00:00' 到 2037年[/size][/color][/p][/td][/tr]
[tr][td=1,1,128][p=30, 2, left][color=#000000][size=2]TIME[/size][/color][/p][/td][td=1,1,92][p=30, 2, left][color=#000000][size=2]3位元組[/size][/color][/p][/td][td=1,1,282][p=30, 2, left][color=#000000][size=2]'-838:59:59' 到 '838:59:59'格式為HH:MM:SS [/size][/color][/p][/td][/tr]
[tr][td=1,1,128][p=30, 2, left][color=#000000][size=2]YEAR[/size][/color][/p][/td][td=1,1,92][p=30, 2, left][color=#000000][size=2]1位元組[/size][/color][/p][/td][td=1,1,282][p=30, 2, left][color=#000000][size=2]1901到2155及0000[/size][/color][/p][p=30, 2, left][color=#000000][size=2]格式為YYYY [/size][/color][/p][/td][/tr]
[/table]

DATE：用在僅需要日期值時，沒有時間部分，以'YYYY-MM-DD'格式顯示DATE值，支援的範圍是'1000-01-01'到'9999-12-31'。
DATETIME：用在需要同時包含日期和時間資訊的值時，以'YYYY-MM-DD HH:MM:SS'格式顯示DATETIME值，支援的範圍是'1000-01-01 00:00:00'到'9999-12-31 23:59:59'。
TIMESTAMP：自動提取系統當前的日期和時間標記，特別適用INSERT或UPDATE時資料記錄的操作，TIMESTAMP值可以從1970的某時的開始一直到2037年，精度為一秒，其值以數字顯示。
TIMESTAMP值取決於顯示長度的設定，若要顯示完整的TIMESTAMP格式，則顯示長度的設定應設為14位，長度的設定與TIMESTAMP格式對照如下表：
[table]
[tr][td=1,1,257][p=30, 2, left][size=2][b]TIMESTAMP[/b][b]長度設定[/b][b] [/b][/size][/p][/td][td=1,1,224][p=30, 2, left][size=2][b]顯示格式[/b][b] [/b][/size][/p][/td][/tr]
[tr][td=1,1,257][p=30, 2, left][size=2]TIMESTAMP(14) [/size][/p][/td][td=1,1,224][p=30, 2, left][size=2]YYYYMMDDHHMMSS [/size][/p][/td][/tr]
[tr][td=1,1,257][p=30, 2, left][size=2]TIMESTAMP(12) [/size][/p][/td][td=1,1,224][p=30, 2, left][size=2]YYMMDDHHMMSS [/size][/p][/td][/tr]
[tr][td=1,1,257][p=30, 2, left][size=2]TIMESTAMP(10) [/size][/p][/td][td=1,1,224][p=30, 2, left][size=2]YYMMDDHHMM [/size][/p][/td][/tr]
[tr][td=1,1,257][p=30, 2, left][size=2]TIMESTAMP(8) [/size][/p][/td][td=1,1,224][p=30, 2, left][size=2]YYYYMMDD [/size][/p][/td][/tr]
[tr][td=1,1,257][p=30, 2, left][size=2]TIMESTAMP(6) [/size][/p][/td][td=1,1,224][p=30, 2, left][size=2]YYMMDD [/size][/p][/td][/tr]
[tr][td=1,1,257][p=30, 2, left][size=2]TIMESTAMP(4) [/size][/p][/td][td=1,1,224][p=30, 2, left][size=2]YYMM [/size][/p][/td][/tr]
[tr][td=1,1,257][p=30, 2, left][size=2]TIMESTAMP(2) [/size][/p][/td][td=1,1,224][p=30, 2, left][size=2]YY [/size][/p][/td][/tr]
[/table]

[color=Magenta] 提示！！[/color]
不合法的（非符合標準格式）DATETIME、DATE或TIMESTAMP值將會被變換到適當類型的“零”值('0000-00-00 00:00:00', '0000-00-00'或00000000000000)。


TIME：以'HH:MM:SS'為標準顯示格式，TIME值的範圍可以從'-838:59:59'到'838:59:59'，小時部分因為可以用在表示在2個事件之間經過的時間或時間間隔，所以小時部分可以是比24個小時大或是負值。
[list]
[*]TIME值允許使用任何標點符號作為時間部分的分隔符號，例如，'10:11:12'和'10.11.12'是相同的。
[*]沒有時間部分分隔符號'HHMMSS'的格式，亦可成為TIME值，例如'101112'將被視為'10:11:12'，不過若是如'108612'這樣的值將是不合法的(具有無意義的分鐘部分)，因此將被視為'00:00:00'。
[*]若為HHMMSS格式的一組數字，亦可視為一個TIME值。例如，112415將被視為'11:24:15'。
[/list]

YEAR：以YYYY格式顯示YEAR值，其範圍是1901到2155。YEAR值能用多種格式指定：
[list]
[*]範圍在'1901'到'2155'的一個4位字串。
[*]範圍在1901到2155的一個4位數字。
[*]範圍在'00'到'99'的一個2位字串，在'00'到'69'範圍的值被變換到在2000到2069範圍的YEAR值，'70'到'99'1970範圍的值被變換到1999的YEAR值。
[*]範圍在'00'到'99'的一個2位數字，在'00'到'69'範圍的值被變換到在2000到2069範圍的YEAR值，'70'到'99'1970範圍的值被變換到1999的YEAR值，但不能直接指定零作為一個數字並且把它解釋為2000。
[*]可接受函數的回傳值，例如NOW()函數。
[/list]

查看完整版本: 7-4 建立資料表(7-4-1 資料表的結構)