MySQL資料庫應用總結（六）—MySQL資料庫數據類型和運算符（上）

SQL語法預覽：

創建表欄位數據類型：【create table 表名(欄位名稱 數據類型);】

插入欄位值：【insert into 表名 values (值1,值2…值n);】

查看格式化值：【select * 或【格式1,格式2…格式n】 from 表名;】

詳解：

一、 MySQL數據類型介紹

MySQL支持多種數據類型，主要有數值類型、日期類型、時間類型和字元串類型。

1. 整數類型

數據類型主要用來存儲數字，MySQL提供多種可供選擇的整數類型，用整數作為數據類型的欄位可以設置auto_increment自增約束條件。

案例：創建資料庫db_demo2，創建表tb_temp1，其中包含欄位a、b、c、d、e的數據類型分別為tinyint、smallint、mediumint、int(Integer)、bigint，SQL語句如下：

create database db_demo2;

use db_demo2;

create table tb_temp1(a tinyint,b smallint,c mediumint,d int,e bigint);

desc temp1;

執行結果如下：

2. 浮點和定點數據類型

MySQL中使用浮點數和定點數來表示小數。浮點有分為單浮點和雙浮點。

案例：創建數據表tb_temp2，其中包含欄位x、y、z的數據類型依次為float(5,1)、double(5,1)和decimal(5,1)，向表中插入數據5.12、5.15和5.123，SQL語句：

create table tb_temp2(x float(5,1),y double(5,1),z decimal(5,1));

insert into tb_temp2 values(5.12,5.15,5.123);

執行結果如下：

3. 日期與時間類型

（1） YEAR

YEAR類型是一個單位元組類型，用於表示年，在存儲時只需要1個位元組。可以使用各種格式指定YEAR值，如下所示：

·以4位字元串或者4位數字格式表示的YEAR，範圍為1901~2155。輸入格式YYYY或者YYYY。

·以2位字元串格式表示的YEAR，範圍為00到99。00~69轉換為2000~2069；70~99轉換為1970~1999；

·以2位數字表示的YEAR值，範圍為1~99。1~69值是2001~2069；70~99範圍值轉換為1970~1999，而在這裡0值被轉換為0000；

案例：創建數據表tb_temp3，定義欄位類型為YEAR的欄位y，向表中插入數值2017，2017，0，00，77，10 ，78和11，SQL語句：

create table tb_temp3(y YEAR);

insert into tb_temp3 values(2017),(2017),(0),(00,(77),(10),(78),(11);

執行結果如下：

（2） TIME

TIME類型用於表示時間信息，在存儲時需要3個位元組。表示某一事件過去的時間和兩個事件之間的時間間隔。可以大於24甚至為負值。 ·

·D HH:MM:SS』格式的字元串。有HH:MM，D HH:MM ,D HH ,SS。D表示日，可以取0~34之間的值。在插入資料庫時D被轉換為小時保存（D*24+HH）。

·HHMMSS格式的數值，會自動分成HH:MM:SS但各自有取值範圍，超過則輸入不合法。

案例：創建數據表tb_temp4，定義欄位類型為TIME的欄位t，向表中插入數值10:05:05 ，23:23，2 10:10，3 02 ，10 ，101112，0，和106010，SQL語句：

create table tb_temp4(t TIME);

insert into tb_temp4 values(』10:05:05』),(,23:23,),(2 10:10),(3 02),(10),(101112),(0),(106010);

執行結果如下：

（3） DATE

DATE只用於輸入日期，沒有時間，在存儲時需要3個位元組。日期格式為：YYYY-MM-DD 。

·以YYYY-MM-DD或者YYYYMMDD字元串格式表示日期，取值範圍是1000-01-01』~9999-12-03。

·以YY-MM-DD或者YYMMDD字元串格式表示日期，取值範圍與年

數相關，00~69年值範圍是2000~2069，70~99年值範圍是1970~1999。

·以YY-MM-DD或者YYMMDD數字格式表示日期，取值範圍與年數

相關，00~69年值範圍是2000~2069，70~99年值範圍是1970~1999。

·使用current_date或者now()，插入當前系統日期。

案例：創建數據表tb_temp5，定義欄位類型為DATE的欄位d，向表中插入數值2017-12-02、20101010，99-09-09 ，000101 ，111111 ，121212和171203 ，SQL語句：

create table tb_temp5(d DATE);

insert into tb_temp5 values(2017-12-02),(20101010),(99-09-09』),(000101),(111111),(121212),(171203);

執行結果如下：

（4） DATETIME

DATETIME用於同時輸入日期和時間信息，在存儲時需要8個位元組。格式為：』』YYYY-MM-DD HH:MM:SS』, 只要符合格式使用字元串和數字類型均可。

·以YYYY-MM-DD HH:MM:SS或者YYYYMMDDHHMMSS字元串格式表示日期，取值範圍是1000-01-01 00:00:00~9999-12-03 23:59:59 。

·以YY-MM-DD HH:MM:SS或者YYMMDDHHMMSS字元串格式表示日期，取值範圍與年數相關，00~69年值範圍是2000~2069，,70~99年值範圍是1970~1999。

·以YYYYMMDDHHMMSS或者YYMMDDHHMMSS數字格式表示日期，取值範圍與年數相關，00~69年值範圍是2000~2069，,70~99年值範圍是1970~1999。

·使用now()，插入當前系統日期時間。

案例：創建數據表tb_temp6，定義欄位類型為DATETIME的欄位dt，向表中插入數值2017-12-02 20:33:33，20101010101010，09-09-09 09:09:09，121212121212』 和111111111111 SQL語句：

create table tb_temp6(dt DATETIME);

insert into tb_temp6 values(2017-12-02 20:33:33),(20101010101010),(09-09-09 09:09:09),(121212121212),(111111111111);

執行結果如下：

（5） TIMESTAMP

TIMESTAMP的顯示格式與DATETIME相同，顯示寬度是19個字元。存儲需要4個位元組，格式為：YYYY-MM-DD HH:MM:SS 。但是TIMESTAMP列的取值範圍小於DATETIME的取值範圍，為1970-01-01 00:00:01UTC~2038-01-19 03:14:07 UTC。其中UTC（Coordinated Universal Time）世界標準時間，因此在插入數據時要保證在合法取值範圍內。

案例：創建數據表tb_temp7，定義欄位類型為TIMESTAMP的欄位ts，向表中插入數值20170101010101 ，20050607080910，08-08-08 09:09:09，12@12@12 12@ 12@12，111111111111和now()，SQL語句：

create table tb_temp7(ts TIMESTAMP);

insert into tb_temp7 values(20170101010101),(20050607080910),(08-08-08

執行結果如下：

4. 字元串類型

字元串類型除了可以存儲字元串數據以外，還可以存儲其他數據，比如圖片、聲音和二進位數據。字元串可以進行區分或或者不區分大小寫的串比較。

MySQL中的字元串char、varchar、binary、varbinary、blob、tinytext、text、enum和set。

（1） char和varchar類型

char(M)為固定長度字元串，在定義時要指定字元串列長。當保存時在右側填充空格以達到指定長度。M表示列長度，當檢索時實際值尾部空格會自動刪除。

varchar(M)為長度可變的字元串，M表示列最大長度，但是保存時實際佔用空間為字元串實際長度加1。不管是保存還是檢索varchar的值時實際值尾部的空格都會保留。

案例：創建數據表tb_temp8，定義欄位ch和vch的數據類型依次為char(4)和varchar(4)，向表中插入數據」ab 「 ，SQL語句：

create table tb_temp8(ch char(4),vch varchar(4));

insert into tb_temp8 values(ab 『,ab );

執行結果如下：

（2） text類型

text列保存非二進位字元串，如文章、評論等等。當保存和查詢text列值時，不刪除尾部空格。Text類型分為四種：tinytext、text、mediumtext和longtext。不同的text類型存儲空間和數據長度不同。

·tinytext：最大長度255（28~1）個字元的text列。

·text：最大長度為65535（216~1）個字元的text列。

·mediumtext：最大長度為16777215（224~1）個字元的text列。

·longtext：最大長度為4294967295或4GB（232~1）個字元的text列。

（3） enum類型

enum是一個字元串對象，其值為創建時在列規定中枚舉的一列值。Enum類型的欄位取值時只能在指定的枚舉列表中取，而且一次只能取一個。如果創建的成員中有空格，則其尾部的空格將自動刪除。Enum值在內部用整數表示，每個枚舉值均有一個索引值：列表值所允許的成員值從1開始編號，MySQL存儲的就是這個索引編號。枚舉最多可以有65535個元素。

例如：定義enum類型的列（null,",first,second,third）,該列可以取值和每個值的索引如下表所示。

Enum 值依照列索引順序排列，並且空字元排在非空字元串之前，null值排在其他所有枚舉值之前。

案例：創建表tb_temp9，定義枚舉類型的列enum(first ,second』,third)，查看列成員的索引值，SQL語句如下：

create table tb_temp9(enm enum(first,second,third));

insert into tb_temp9 values(first),(second),(third),(null);

執行結果如下：

（4） set類型

set類型是一個字元串對象，可以有另個或者多個值，set列最多可以有64個成員，其值為創建表時規定的一系列值。Set成員各值之間用逗號,隔開。語法格式如下：

set(值1,值2,…值n)

與enum類型相同，set的值在內部用整數表示，每一個值都有一個索引編號。當創建表時，set成員值得尾部空格將自動刪除。但與enum類型不同的是，enum類型的欄位只能從定義的列值中選擇一個插入，而set類型的列可以從定義的列值中選擇多個字元聯合插入。

如果插入set欄位的列值有重複，則MySQL自動刪除重複值；插入set欄位值順序並不重要，MySQL會在存入資料庫時，按照定義的順序顯示；如果插入了不正確的值，默認情況下，MySQL將忽視這些值，並給出警告。

案例：創建表tb_temp10，定義set類型欄位s，取值列表為(a,b,c,d)，插入數據(a)、(a,b,a)、(c,a,d)、(a,x,b,y)。SQL語句如下：

create table tb_temp10(s set(a,b,c,d));

insert into tb_temp10 values(a),(a,b,a),(c,a,d);

執行結果如下：

5. 二進位類型

MySQL支持兩類字元型數據：文本字元串和二進位字元串。前面講解了存儲文本的字元串類型，今天將講解MySQL中存儲二進位數據的數據類型。

MySQL中的二進位數據類型有：bit、binary、varbinary、tinyblob、blob、mediumblob和longblob，今天講解這些二進位類型的特點和使用方法。

(1) bit類型

bit是位欄位類型。M表示值得位數，範圍為1~64。如果M被省略，默認值為1。如果為bit(M)列分配的值長度小於M位，在值得左邊用零填充。如bit(5)列分配一個值b101，其效果與分配b00101相同。Bit數據類型用來保存位欄位值，如以二進位的形式保存數據13,13的二進位為1101，在這裡需要位數至少4位的bit類型，既可以定義列類型為bit(4)。大於位數(M)的二進位數是不能插入bit(M)類型欄位中的。

案例：創建表tb_temp11，定義bit(4)類型欄位b,向表中插入數據3、10、18。SQL語句如下：

create table tb_temp11(b bit(4));

insert into tb_temp11 values(3),(10),(18);

執行結果如下：

(2) binary和varbinary類型

binary和varbinary類型類似於char和varchar，不同的是他們包含二進位位元組字元串。使用語法如下：

列名稱

Binary的長度是固定的，指定長度之後，不足最大長度的，將在右邊填充「」補齊以達到指定長度（M）。

Varbinary類型的長度是可變的，其長度可以在0到最大值之間。如制定列數據類型為varbinary(20)，如果插入的值得長度只有10，則實際存儲空間為10加1，即其實際佔用的空間為字元的實際長度加1。

案例：創建表tb-temp12,dingyi binary(3)類型額欄位b和varbinary(3)類型的欄位vb，並向表中插入數據"5"，比較兩個欄位的存儲空間。SQL語句如下：

create table tb_temp12(b binary(3),vb varbinary(3));

insert into tb_temp12 values(5,5);

執行結果如下：

可以看到b欄位的數據長度為3，而vb欄位的數據長度僅為插入的一個字元長度1。B欄位和vb欄位的長度截然不同，因為b欄位不足的空間填充了""，而vb欄位則沒有填充。

(3) blob類型

blob是一個二進位的大對象，用來存儲可變數量的數據。Blob類型分為四種：tinyblob、blob、mediumblob和longblob，他們可以容納的最大長度不同。

Blob列存儲的是二進位字元串（位元組字元串）；text列存儲的是非二進位字元串（字元字元串）。Blob列沒有字符集，並且排序和比較基於列值位元組的數值，text列有一個字符集，並且根據字符集對值進行排序和比較。

未完待續...