MySQL基礎(二)：列類型、列屬性

一、列類型

數據類型方便對數據進行統一分類，從系統的角度來說方便使用統一的方式進行管理，更好的利用有限空間；SQL中數據類型分為三大類：數值類型、字元串類型和時間日期型。

數值型數據都是數值，分為整數型和小數型。

1.整型

存放整型數據：在SQL中因為更多要考慮如果節省磁碟空間，所以系統將整型又細分為5類：

Tinyint:迷你整型，使用1個位元組存儲

Smallint:小整型，使用2個位元組存儲

Mediumint:中整型，使用3個位元組存儲

Int:標準整型，使用4個位元組存儲(常用)

Bigint:大整型，使用8個位元組存儲

舉例：創建一張整型表

USE mydatabase;CREATE TABLE my_int(int_1 TINYINT,int_2 SMALLINT,int_3 MEDIUMINT,int_4 INT,int_5 BIGINT)CHARSET GBK;INSERT INTO my_int VALUES(128, 100, 100, 100, 100);

查看錶結構的時候，發現每個欄位的數據類型之後都會有自帶一個括弧，裡面有指定的數字

如：指定欄位中數據為無符號(0-255)

ALTER TABLE my_int ADD int_6 TINYINT UNSIGNED;

顯示寬度的意義：當數據不夠顯示寬度的時候，會自動讓數據變成對應的顯示寬度，通常需要搭配一個前導0來增加寬度，不改變值的大小：zerofill(零填充)會導致數值自動變成無符號。

如：指定欄位中數據的顯示寬度為3且零填充

ALTER TABLE my_int ADD int_7 TINYINT(3) ZEROFILL;

2.浮點型

小數點浮動，精度有限，而且會丟失精度（會自動四捨五入）

舉例：創建一張浮點數表float(M,D) M代表總長度 D代表小數部分長度 整數部分長度為M-D

CREATE TABLE my_float(f1 FLOAT,f2 FLOAT(10,2),f3 FLOAT(6,3))CHARSET GBK;INSERT INTO my_float VALUES(1.16, 1.2451, 1.2315);SELECT * FROM my_float;DESC my_float;

浮點型數據的插入整數部分不能超出長度，小數部分超出長度會自動四捨五入；如果是因為系統進位導致整數部分超出指定的長度則沒有問題。

3.定點型

絕對的保證整數部分不會被四捨五入（不會丟失精度），小數部分有可能（理論上小數部分也不會丟失精度）

舉例：創建一張定點數表

USE mydatabase;CREATE TABLE my_decimal(f1 FLOAT(10,2),d2 DECIMAL(10,2))CHARSET GBK;INSERT INTO my_decimal VALUES(12345.90, 12345.90);INSERT INTO my_decimal VALUES(1234.123456, 1234.12345678);SELECT * FROM my_decimal;DESC my_decimal;

浮點數如果進位導致整數長度溢出沒有問題，但是定點數不行！

4.時間日期型

datatime: YYYY-mm-dd HH:ii:ss, 表示範圍從1000到9999年，有0值：0000-00-00 00-00-00

date:是datetime中的date部分

time:時間段，指定的某個區間之間 -時間到+時間

timestamp:時間戳，從1970年開始的YYYY-mm-dd HH:ii:ss格式與datetime完全一致

year:年份兩種形式year(2)和year(4):1901-2156

舉例：創建一張時間日期型表

USE mydatabase;CREATE TABLE my_datetime(d1 DATETIME,d2 DATE,d3 TIME,d4 TIMESTAMP,d5 YEAR)CHARSET GBK;INSERT INTO my_datetime VALUES(2017-6-2 16:33:14, 2017-6-2, 16:41:23, 2017-6-2 16:42:19, 2017);SELECT * FROM my_datetime;DESC my_datetime;

timestamp:只要當前所在的記錄被更新，該欄位一定會自動更新成當前時間

5.定長+變長字元串

在SQL中，將字元串類型分成6類：char,varchar,text,blob,eum,set

1.定長字元串：char 磁碟（二維表）在定義結構的時候，就已經確定了最終數據的存儲長度

char(L):L代表Length,可以存儲的長度，單位為字元，最大長度值可以為255

char(4):在UTF8環境下需要 4 * 3 = 12 個位元組

2.變長字元串：varchar 在分配空間的時候，按照最大的空間分配，但實際使用大小由數據確定，另外會使用1-2個位元組來存儲數據的實際長度；但是實際上如果長度超過255,會既不用定長也不用變長，會使用文本字元串text

varchar(L):L表示字元長度，理論長度是65536個字元

varchar(10):當存儲了10個漢字(utf8)，會使用10 * 3 + 1 = 31(bytes)

當存儲了3個漢字(utf8)，會使用3 * 3 + 1 = 10(bytes)

如何選擇定長或者變長字元串？

定長磁碟空間比較浪費，但是效率高：當數據長度基本一致，如手機號等

變長的磁碟空間利用率高，但是效率低：當數據長度不易確定，如地址等

6.文本字元竄

如果數據量過大，如超過255個字元就會使用文本字元串，文本字元串根據存儲的數據的格式進行分類：text和blob

text:存儲文字（二進位數據實際上都是存儲路徑）

blob:存儲二進位數據（通常不用）

7.枚舉字元串

枚舉：enum，事先列舉出所有可能的數據，存儲的數據必須是其中之一

定義：enum(可能出現的數據)； //如enum(male, female, unknow)

使用：存儲數據，只能存儲枚舉中的數據

舉例：創建一張枚舉表

USE mydatabase;CREATE TABLE my_enum(gender ENUM(male, female, unknow))CHARSET GBK;INSERT INTO my_enum VALUES(male), (2);SELECT * FROM my_enum;DESC my_enum;

枚舉的作用：規範數據格式 and 節省存儲空間（枚舉實際存儲的是數值，而不是字元串本身）

枚舉的原理：枚舉在定義的時候，系統會自動建立編號與元素的對應關係並存放到log中；後續數據插入時，系統會將字元串轉換成對應的編號存儲，查看數據時，系統會將編號轉換成對應的字元串顯示。

8.集合字元串

集合與枚舉類似，實際存儲的是數值，而不是字元串（集合是多選）

集合的使用方式：

定義：set(元素列表)

使用：可以使用元素列表中的元素（多個），使用逗號分隔

舉例：創建一張集合表

USE mydatabase;CREATE TABLE my_set(hobby SET(tennis, us_tv, beauty))CHARSET GBK;INSERT INTO my_set VALUES(beauty, tennis), (2);SELECT * FROM my_set;DESC my_set;

集合中每一個元素對應一個二進位位，被選中為1沒有則為0，最後結果從右至左取；插入數據時不講究順序，系統會自動從集合中尋找到對應的元素將其二進位位置1

集合的作用：規範數據格式 and 節省存儲空間（集合實際存儲的是數值，而不是字元串本身）

9.MySQL記錄長度

MySQL中規定：任何一條記錄最長不能超過65535個位元組（varchar永遠達不到理論值）

utf8編碼時varchar最長不超過21844字元(PyCharm2017.2.3中測試MySQL 5.7.18是32680)

gbk編碼時varchar最長不超過32766字元((PyCharm2017.2.3中測試MySQL 5.7.18中是21787)

USE mydatabase;CREATE TABLE my_gbk(name VARCHAR(32766))CHARSET GBK;CREATE TABLE my_utf8(name VARCHAR(21844))CHARSET utf8;

如需用完65535個位元組長度，增加一個tinyint欄位即可

MySQL記錄中：如果有任何一個欄位允許為空，那麼系統會自動從整個記錄中保留一個位元組來存儲NULL(若想釋放NULL所佔用的位元組，必須保證所有的欄位都不允許為空)

USE mydatabase;CREATE TABLE my_gbk2(age TINYINT NOT NULL,name VARCHAR(32766) NOT NULL)CHARSET GBK;CREATE TABLE my_utf2(age TINYINT NOT NULL,name VARCHAR(21844) NOT NULL)CHARSET utf8;

MySQL中text文本字元串，不佔用記錄長度，額外存儲。但是text文本字元串也是屬於記錄的一部分：一定需要佔據記錄中的部分長度：10個位元組

二、列屬性

列屬性：真正約束欄位的是數據類型，但是數據類型的的約束很單一，需要一些額外的約束來更加保證數據的合法性。列屬性有：NULL/NOT NULL,default,primary key,unique key,auto_increment,comment

1.空屬性 null

兩個值：NULL(默認的)和NOT NULL(不為空)；默認情況下資料庫中的欄位都為空，但實際開發中都會儘可能保證數據不為空，因為空數據沒有意義，也不能參與運算。

2.列描述 comment

列描述：沒有實際含義，用於描述欄位，會根據數據表創建語句保存。

3.默認值 default

某欄位中數據經常出現一具體值，系統可以將其設置為默認，在需要的時候選擇性使用

默認值的使用：數據插入的時候不給該欄位賦值即為默認值

使用默認值時不一定需要指定欄位列表，可以使用default代替默認值

4.增加主鍵 primary key

一張表中只能有一個欄位使用對應的鍵，用來唯一的約束改欄位裡面的數據，欄位中數據不可能重複，一張表中只能有最多一個主鍵。SQL操作中大致有三種方式增加主鍵：

1.在創建表的時候，直接在欄位之後，跟primary key關鍵字（主鍵本身不允許為空）

優點：非常直接；缺點：只能使用一個欄位作為主鍵

2.在創建表的時候，在所有的欄位之後，使用primary key(主鍵欄位列表)

3.當表創建好之後再額外追加主鍵，可以通過修改表的欄位屬性或直接追加

alter table 表名 add primary key(欄位列表);

5.主鍵約束

主鍵欄位中的數據不允許重複，否則SQL操作將失敗（增和改）

6.主鍵刪除

主鍵必須先刪除才能增加(同時要先刪除自增長)

alter table 表名 drop primary key;

7.主鍵分類

在創建數據表的過程中，很少使用實際業務數據作為主鍵欄位（業務主鍵，如學號，課程號）；大部分情況下是使用邏輯性的欄位，這種欄位的主鍵稱為邏輯主鍵。

8.新增自增長 auto_increment

當對應的欄位沒有賦值或為默認值或為NULL時，會自動被系統觸發，系統會從當前欄位中已有的最大值再進行+1操作，得到一個新的不同的欄位。 自增長通常跟主鍵搭配。

自增長特點：

1.自增長欄位必須本身是索引（key一欄有值）

2.自增長欄位必須是數字（整型）

3.一張數據表只能有一個自增長

9.自增長使用

當自增長欄位中數據的值為默認或NULL時會觸發自增長；自增長欄位如果被賦予了值，那麼自增長失效，但是下一次還是能夠正確的自增長（從最大值+1）

可以通過查看創建表語句確定下一次自增長的初始值：

10.修改自增長

自增長如果涉及到欄位改變，必須先刪除自增長後增加（一張表只能有一個自增長）

修改當前自增長已近存在的值，只能比最大值大

ALTER TABLE my_auto AUTO_INCREMENT = 10; #減小，向下修改 --不生效！ALTER TABLE my_auto AUTO_INCREMENT = 20; #增加，向上修改為什麼自增長是從1開始且每次自增量為1？

所有系統的表現（字符集、校對集）都是有系統內部的變數進行控制的

查看自增長變數：shwo varialbe like auto_increment%;

可以修改自增長變數實現不同的效果，修改是對整個資料庫修改而不是單張數據表（修改是會話級別）

set auto_increment_increment = 5; #表示每次自增量為5

* 修改之後會使原來自增長值+1，然後加新的自增量

11.刪除自增長

自增長是欄位的屬性，通過modify使欄位沒有auto_increment即可

alter table 表名 modiy 欄位 類型;

12.增加唯一鍵 unique key

數據表中可能有多個欄位數據具有唯一性，但是一張表只能有一個主鍵，唯一鍵可以解決表中多個欄位需要唯一性約束的問題；

唯一鍵的本質與主鍵差不多：唯一鍵默認的允許自動為空，而且可以多個為空（空欄位不參與唯一性比較）。

增加唯一鍵的方式：

1.在創建數據表的SQL語句後加unique/unique key;

2.在所有的欄位之後增加unique key(欄位列表);

之所以會顯示成PRI，因為剛好是一個不為空的唯一鍵（主鍵性質一樣），該表沒有主鍵；即當系統發現數據表沒有主鍵的時候會使用唯一鍵代替。

3.在創建表之後增加唯一鍵

alter table 表名 add unique key(欄位);

13.唯一鍵約束

唯一鍵與主鍵本質相同，唯一的區別就是唯一鍵的數據默認允許為空，而且是多個為空；

如果唯一鍵數據也不允許為空，則與主鍵的約束作用一致。

14.刪除唯一鍵

先刪除後新增（唯一鍵可以有多個：可以不刪除），刪除唯一鍵：

alter table 表名 drop index 索引名字; --唯一鍵默認使用欄位名做索引名字

15.索引

系統根據某種演算法，將已有的數據及未來可能新增的數據單獨建立一個文件，文件能夠實現快速的匹配數據，並且能夠快速的找到對應數據表中的記錄。幾乎所有的索引都是建立在欄位之上。

索引的意義：

1.提升數據的查詢效率

2.約束數據的有效性、唯一性等

增加索引的前提條件：索引本身會產生索引文件（有可能比數據文件大），會耗費磁碟空間。如果某個欄位需要作為查詢的條件經常使用，那麼可以使用索引；如果某個欄位需要進行數據的有效性約束，也可能使用索引（主鍵、唯一鍵）。

MySQL中提供了多種索引

1.主鍵索引 primary key

2.唯一索引 unique key

3.全文索引 fulltext index

4.普通索引 index

全文索引：針對文章內部的關鍵字進行索引，重點在於如何確定關鍵字。英文很容易，因為單詞之間有空格；中文很難，沒有空格且字詞可以隨意組合（分詞：sphinx）