20141230 mysql數實值型別和列屬性一

標籤：基礎知識   預設   display   比較   原因   注意   填充   磁碟空間   左右   

回顧

資料庫基礎知識，關係型資料庫（行/記錄，列/欄位，SQL）

基本SQL操作：庫操作，表操作（欄位）和資料操作

字元集

校對集

1. 什麼是校對集？

校對集就是指在同樣的字元集下的資料按照什麼樣的方式進行比較，比較的規範的集合就叫校對集。

校對集分類

_bin：二進位比較，區分大小寫

_ci：case insensitive，大小寫不敏感，不區分大小寫

_cs：case sensitive，大小寫敏感，區分大小寫

查看資料庫所支援的校對集有哪些

show collation;

一共有197種校對集

校對集的實現：在建立資料表（庫）的時候指定校對集：collate = 校對集

插入資料

校對集的使用：在需要對資料進行比較的時候會自動的啟用

對資料進行排序的時候一定會用到校對集。

文法：在where條件之後，使用order by 要排序的欄位 [asc|desc]

asc：升序，是預設的

desc：降序

雖然中文也可以比較，但是沒有比較的意義，gbk支援中文比較，utf8不支援，gbk比較的並不是中文本身，只能比拼音。

結論：一張表的校對集應該在建立表的時候就指定，而不能在表已經存在的情況下（資料）再去嘗試修改校對集，修改無效。

亂碼問題解決

出現亂碼的原因：兩個資料存在的字元集不一致。

理想的解決方案：三碼合一

儲存引擎

mysql提供了很多的儲存引擎可以選擇，但是常用的是myisam和InnoDB，因為這兩個免費。

全部儲存引擎

5.5以前的儲存引擎預設的是myisam，5.5之後都是InnoDB了。

甲骨文收購sun之後得到mysql就不再對myisam儲存引擎進行支援人員，把所有團隊都用來支援InnoDB

InnoDB和myisam區別

1. 檔案的儲存形式不一樣

2. myisam的批量插入和查詢效率高，而InnoDB的修改和刪除效率高

3. 事務安全只有InnoDB支援，myisam不支援

4. InnoDB支援外鍵，myisam不支援

欄位（列）類型

1. 什麼是欄位類型？

所謂的欄位類型指的是當前欄位所能儲存的資料的類型

SQL語言是一種強型別語言，所以必須在表建立的時候，給欄位指定對應的資料類型，而且在資料存放區的時候，只能儲存的指定的資料類型。

資料類型分類

在SQL中，資料類型同樣分為三類：數值型，字串型，日期時間型

數值型：整型，小數型（浮點型和定點型）

字串型：定長，不定長，文本，集合，枚舉

日期時間型：時間日期，日期，時間，年，時間戳記

s

數值型 整型

存放整數的資料類型。生活中有不同的需求來使用不同範圍內的整型，而SQL就需要根據不同的需求來設定不同的整型（分配不同記憶體）來記錄，提高空間的利用率。

整型分為以下五種

tinyint：迷你整型，1一個位元組儲存，最多256個資料

smallint：小整型，2個位元組儲存，最多65536個資料

mediumint：中整型，3個位元組儲存

int：標準整型，4個位元組儲存

bigint：大整型，8個位元組儲存

建立整形表

插入資料

在SQL中，數值資料預設的都是有符號的，所以有正負之分，應該將資料一分為二

類型	位元組	最小值（有符號/無符號）	最大值（有符號/無符號）
TINYINT	1	-128/0	127/255
SMALLINT	2	-32768/0	32767/65535
MEDIUMINT	3	-8388608/0	8388607/16777215
INT/INTEGE	4	-2147483648/0	2147483647 /4294967295
BIGINT	8	-9223372036854775808/0	9223372036854775807/18446744073709551615

無符號

文法：在資料類型之後，使用unsigned關鍵字

查看資料結構

要做到不改變原來數值大小的情況下，只能在資料的左邊進行0填充

預設的系統不會自動的使用0填充，需要根據指令進行0填充，zerofill

文法：資料類型之後 zerofill

效果

顯示寬度注意事項

小數型

浮點型：有可能丟失精度的小數型

float：單精確度浮點型，7個精確值左右

double：雙精確度浮點型，15位左右

浮點型使用文法

欄位 float/float(M,D)

M表示整個長度，D表示小數部分的長度

建立表

插入資料

浮點數資料查詢：系統會自動丟失精度之後的所有資料，四捨五入

浮點數的應用：如果資料量特別大但是對精度要求不高的資料才會使用浮點型。

定點型

在指定範圍內不會丟失精度的小數。

文法：decimal(M,D)，M總長度，D小數部分長度

定點型應用

字串類型

字串類型分為：定長字串，變長字串，文本字串，集合字串和枚舉類型

定長字串

定長指的是磁碟所分配的空間是固定長度的儲存空間，但是裡面儲存的資料可以沒有空間長。

定長：char

文法：欄位名 char(L)，L表示可以儲存的字元長度，定長的L長度最大值是255

變長字串

變長指的是磁碟會根據實際儲存的內容來進行磁碟空間的分配。

變長：varchar

文法：欄位名 varchar(L)，L表示可以儲存的字元長度，L的最大值理論上是65535，因為varchar是變長，所以真實儲存的資料的長度不可得知，所以任何變長資料都需要開闢額外的1或2個位元組來儲存資料的長度。

定長與變長對比：utf8

字串	Char(4)	Varchar(4)	Char實際位元組長度	Varchar實際位元組長度
A	A	A	4 * 3(utf8) = 12	1 * 3(utf8) + 1 = 4
ABCD	ABCD	ABCD	4 * 3(utf8) = 12	4 * 3(utf8) + 1 = 13

定長和變長的應用

1. 長度固定的較小的字串使用定長，手機號，身份證，md5加密等

2. 長度不固定的較小的字串使用變長，姓名，籍貫，家庭住址等

3. 如果字串長度較長（通常超過255個字元）使用檔案字串

定長與變長的優劣

1. 定長：浪費空間，但是讀取效率高

2. 變長：節省空間的，但是效率降低

在wamp中，mysql使用的精簡模式，不是使用strict 模式來約束資料，但是獨立安裝的mysql使用的strict 模式。

區別

1. 錯誤處理方式不一樣：精簡模式下，如果出現錯誤，系統會嘗試去減少錯誤代價（資料太長進行截取），在strict 模式下，直接報錯。

2. 對於Null 字元串的處理不一樣：主鍵自增長的時候

文本字串

文本字串分為兩種：字元型文本，二進位文本

文本字串：text

二進位字串：blob

圖片或者檔案等都可以進行儲存，但是不建議直接儲存，而是儲存對應的檔案的位置（路徑）

一般認為超過255個字元的資料都使用文本字串來進行儲存。

20141230 mysql數實值型別和列屬性一