第4章　數據表的創建與操作

本章內容提要

數據實際存儲在數據表中，可見在數據庫中，數據表是數據庫中最重要、最基本的操作對象，是數據存儲的基本單位。本章就來介紹數據表的創建與操作，包括創建數據表、修改數據表、查看數據表結構與刪除數據表等。

本章知識點

• 數據表中存放的數據類型。
• 創建數據表。
• 查看數據表的結構。
• 修改數據表。
• 刪除數據表。

4.1　數據表中能存放的數據類型

MySQL支持多種數據類型，大致可以分為三類，分別是數值類型、日期和時間類型、字符串（字符）類型。

4.1.1　數值類型

MySQL支持所有標準SQL數值數據類型。這些類型包括嚴格數值數據類型（INTEGER、SMALLINT、TINYINT、MEDIUMINT和BIGINT），近似數值數據類型（FLOAT、REAL和DOUBLE），以及定點數類型（DECIMAL）。

注意：關鍵字INT是INTEGER的同義詞，關鍵字DEC是DECIMAL的同義詞。

1. 整數類型

MySQL提供多種整數類型，不同的數據類型提供的取值范圍不同，可以存儲的值的范圍越大，其所需要的存儲空間也就越大，因此要根據實際需求選擇適合的數據類型。表4-1顯示了每個整數類型的存儲需求和取值范圍。

表4-1　MySQL中的整數型數據類型

MSQL支持選擇在該類型關鍵字后面的括號內指定整數值的顯示寬度（例如INT(4)）。INT(M)中的M指示最大顯示寬度，最大有效顯示寬度是4，需要注意的是，顯示寬度與存儲大小或類型包含的值的范圍無關。

例如，假設聲明一個INT類型的字段：

    id INT（4）

該聲明指出，在id字段中的數據一般只顯示4位數字的寬度。假如向id字段中插入數值10000，當使用select語句查詢該列值的時候，MySQL顯示的是完整的帶有5位數字的10000，而不是4位數字。

注意：其他整數數據類型也可以在定義表結構時指定所需要的顯示寬度，如果不指定，則系統為每一種類型指定默認的寬度值。另外，不同的整數類型的取值范圍不同，所需的存儲空間也不同，因此，在定義數據表的時候，要根據實際需求選擇最合適的類型，這樣做有利于節約存儲空間，還有利于提高查詢效率。

2. 浮點數類型

現實生活中很多情況需要存儲帶有小數部分的數值，這就需要浮點數類型，如FLOAT和DOUBLE。其中，FLOAT為單精度浮點數類型；DOUBLE為雙精度浮點數類型。浮點數類型可以用（M，D）來表示，其中M稱為精度，表示總共的位數；D稱為標度，表示小數的位數。表4-2顯示了每個浮點數類型的存儲需求和取值范圍。

表4-2　MySQL中的浮點數數據類型

注意：M和D在FLOAT和DOUBLE中是可選的，FLOAT和DOUBLE類型將被保存為硬件所支持的最大精度。

3. 定點數類型

MySQL中，除使用浮點數類型表示小數外，還可以使用定點數表示小數，定點數類型只有一種：DECIMAL。定點數類型也可以用（M，D）來表示，其中M稱為精度，表示總共的位數；D稱為標度，表示小數的位數。DECIMAL的默認D值為0，M值為10。表4-3顯示了定點數類型的存儲需求和取值范圍。

DECIMAL類型不同于FLOAT和DECIMAL，DECIMAL實際是以字符串存儲的。DECIMAL的有效取值范圍由M和D的值決定。如果改變M而固定D，則其取值范圍將隨M的變大而變大。如果固定M而改變D，則其取值范圍將隨D的變大而變小（但精度增加）。由此可見，DECIMAL的存儲空間并不是固定的，而是由其精度值M決定，占用M+2字節。

表4-3　MySQL中的定點數數據類型

4.1.2　日期和時間類型

MySQL中，表示時間值的日期和時間類型為DATETIME、DATE、TIMESTAMP、TIME和YEAR。例如，只需記錄年份信息時，可以只用YEAR類型，而沒有必要使用DATE。每一種類型都有合法的取值范圍，當插入不合法的值時，系統會將“零”值插入到字段中。表4-4列出了MySQL中的日期和時間類型。

表4-4　日期和時間數據類型

4.1.3　字符串類型

字符串類型用于存儲字符串數據，MySQL支持兩類字符串數據：文本字符串和二進制字符串。文本字符串可以進行區分或不區分大小寫的串比較，也可以進行模式匹配查找。MySQL中字符串類型指的是CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT、ENUM和SET。表4-5列出了MySQL中的字符串數據類型。

表4-5　MySQL中字符串數據類型

注意：VARCHAR和TEXT類型是變長類型，它們的存儲需求取決于值的實際長度（表格中用L表示），而不是取決于類型的最大可能長度。例如，一個VARCHAR(10)字段能保存最大長度為10個字符的一個字符串，實際的存儲需求是字符串的長度L，加上1字節以記錄字符串的長度。例如，字符串“teacher”，L是7，而存儲需求是8字節。

另外，MySQL提供了大量的數據類型，為了優化存儲，提高數據庫性能，在不同情況下應使用最精確的類型。當需要選擇數據類型時，在可以表示該字段值的所有類型中，應當使用占用存儲空間最少的數據類型。因為這樣不僅可以減少存儲（內存、磁盤）空間，還可以在數據計算時減輕CPU的負載。

4.1.4　選擇數據類型

MySQL提供了大量的數據類型，為了優化存儲，提高數據庫性能，在任何情況下均應使用最精確的類型，即在所有可以表示該列值的類型中，該類型使用的存儲最少。

1. 整數和浮點數

如果不需要小數部分，則使用整數來保存數據；如果需要表示小數部分，則使用浮點數類型。對于浮點數據列，存入的數值會對該列定義的小數位進行四舍五入。例如，如果列的值的范圍為1~99999，若使用整數，則MEDIUMINT UNSIGNED是最好的類型；若需要存儲小數，則使用FLOAT類型。

浮點數類型包括FLOAT和DOUBLE類型。DOUBLE類型精度比FLOAT類型高，因此，如要求存儲精度較高時，應選擇DOUBLE類型。

2. 浮點數和定點數

浮點數FLOAT和DOUBLE相對于定點數DECIMAL的優勢是：在長度一定的情況下，浮點數能表示更大的數據范圍。但是由于浮點數容易產生誤差，因此對精確度要求比較高時，建議使用DECIMAL來存儲。DECIMAL在MySQL中是以字符串存儲的，用于定義貨幣等對精確度要求較高的數據。在數據遷移中，FLOAT(M,D)是非標準SQL定義，數據庫遷移可能會出現問題，最好不要這樣使用。另外，兩個浮點數進行減法和比較運算時也容易出問題，因此在進行計算的時候，一定要小心。如果進行數值比較，最好使用DECIMAL類型。

3. 日期和時間類型

MySQL對于不同種類的日期和時間有很多的數據類型，比如YEAR和TIME。如果只需要記錄年份，則使用YEAR類型即可；如果只記錄時間，只需使用TIME類型。

如果同時需要記錄日期和時間，則可以使用TIMESTAMP或者DATETIME類型。由于TIMESTAMP列的取值范圍小于DATETIME的取值范圍，因此存儲范圍較大的日期最好使用DATETIME。

TIMESTAMP也有一個DATETIME不具備的屬性。默認的情況下，當插入一條記錄但并沒有指定TIMESTAMP這個列值時，MySQL會把TIMESTAMP列設為當前的時間。因此當需要在插入記錄的同時插入當前時間時，使用TIMESTAMP是方便的，另外TIMESTAMP在空間上比DATETIME更有效。

4. CHAR與VARCHAR之間的特點與選擇

CHAR和VARCHAR的區別如下：

（1）CHAR是固定長度字符，VARCHAR是可變長度字符；

（2）CHAR會自動刪除插入數據的尾部空格，VARCHAR不會刪除尾部空格；

（3）CHAR是固定長度，所以它的處理速度比VARCHAR的速度要快，但是它的缺點就是浪費存儲空間。所以對存儲不大，但在速度上有要求的可以使用CHAR類型；反之，可以使用VARCHAR類型來實現。

存儲引擎對于選擇CHAR和VARCHAR的影響：

（1）對于MyISAM存儲引擎：最好使用固定長度的數據列代替可變長度的數據列。這樣可以使整個表靜態化，從而使數據檢索更快，用空間換時間。

（2）對于InnoDB存儲引擎：使用可變長度的數據列，因為InnoDB數據表的存儲格式不分固定長度和可變長度，因此使用CHAR不一定比使用VARCHAR更好，但由于VARCHAR是按照實際的長度存儲，比較節省空間，所以對磁盤I/O和數據存儲總量比較好。