数据类型
mysql常用數(shù)據(jù)類型：
#1. 數(shù)字：
整型：tinyinit int bigint
小數(shù)：
float ：在位數(shù)比較短的情況下不精準(zhǔn)
double ：在位數(shù)比較長的情況下不精準(zhǔn)
0.000001230123123123
存成：0.000001230000
decimal：（如果用小數(shù)，則用推薦使用decimal）
精準(zhǔn)
內(nèi)部原理是以字符串形式去存
#2. 字符串：
char（10）：簡單粗暴，浪費(fèi)空間，存取速度快
root存成root000000
varchar：精準(zhǔn)，節(jié)省空間，存取速度慢
sql優(yōu)化：創(chuàng)建表時(shí)，定長的類型往前放，變長的往后放
比如性別 比如地址或描述信息
>255個(gè)字符，超了就把文件路徑存放到數(shù)據(jù)庫中。
比如圖片，視頻等找一個(gè)文件服務(wù)器，數(shù)據(jù)庫中只存路徑或url。
#3. 時(shí)間類型：
最常用：datetime
#4. 枚舉類型與集合類型
數(shù)值類型：
整數(shù)類型：tinyint , smallint, mediumint ,int , bigint
tinyint[(m)] [unsigned] [zerofill]
小整數(shù)，數(shù)據(jù)類型用于保存一些范圍的整數(shù)數(shù)值范圍：
有符號(hào)：
-128 ～ 127
無符號(hào)：
0 ～ 255
PS： MySQL中無布爾值，使用tinyint(1)構(gòu)造。
========================================
int[(m)][unsigned][zerofill]
整數(shù)，數(shù)據(jù)類型用于保存一些范圍的整數(shù)數(shù)值范圍：
有符號(hào)：
-2147483648 ～ 2147483647
無符號(hào)：
0 ～ 4294967295
========================================
bigint[(m)][unsigned][zerofill]
大整數(shù)，數(shù)據(jù)類型用于保存一些范圍的整數(shù)數(shù)值范圍：
有符號(hào)：
-9223372036854775808 ～ 9223372036854775807
無符號(hào)：
0 ～ 18446744073709551615
驗(yàn)證1：有符號(hào)和無符號(hào)tinyint:
============有符號(hào)tinyint============== # 創(chuàng)建數(shù)據(jù)庫db4 create database db4 charset utf8; # 切換到當(dāng)前db4數(shù)據(jù)庫 mysql> use db4; # 創(chuàng)建t1 規(guī)定x字段為tinyint數(shù)據(jù)類型（默認(rèn)是有符號(hào)的） mysql> create table t1(x tinyint); # 驗(yàn)證，插入-1這個(gè)數(shù) mysql> insert into t1 values(-1); # 查詢 表記錄，查詢成功（證明默認(rèn)是有符號(hào)類型） mysql> select * from t1; +------+ | x | +------+ | -1 | +------+ #執(zhí)行如下操作，會(huì)發(fā)現(xiàn)報(bào)錯(cuò)。因?yàn)橛蟹?hào)范圍在(-128,127) mysql> insert into t1 values(-129),(128); ERROR 1264 (22003): Out of range value for column 'x' at row 1 ============無符號(hào)tinyint============== # 創(chuàng)建表時(shí)定義記錄的字符為無符號(hào)類型（0,255） ，使用unsigned mysql> create table t2(x tinyint unsigned); # 報(bào)錯(cuò)，超出范圍 mysql> insert into t2 values(-129); ERROR 1264 (22003): Out of range value for column 'x' at row 1 # 插入成功 mysql> insert into t2 values(255); Query OK, 1 row affected (0.00 sec)
驗(yàn)證2：int類型后面的存儲(chǔ)是顯示寬度，而不是存儲(chǔ)寬度
mysql> create table t3(id int(1) unsigned); #插入255555記錄也是可以的 mysql> insert into t3 values(255555); mysql> select * from t3; +--------+ | id | +--------+ | 255555 | +--------+ ps:以上操作還不能夠驗(yàn)證，再來一張表驗(yàn)證用zerofill 用0填充 # zerofill 用0填充 mysql> create table t4(id int(5) unsigned zerofill); mysql> insert into t4 value(1); Query OK, 1 row affected (0.00 sec) #插入的記錄是1，但是顯示的寬度是00001 mysql> select * from t4; +-------+ | id | +-------+ | 00001 | +-------+ 1 row in set (0.00 sec)
注意：為該類型指定寬度時(shí)，僅僅只是指定查詢結(jié)果的顯示寬度，與存儲(chǔ)范圍無關(guān)，存儲(chǔ)范圍如下.
其實(shí)我們完全沒必要為整數(shù)類型指定顯示寬度，使用默認(rèn)的就可以了.
默認(rèn)的顯示寬度，都是在最大值的基礎(chǔ)上加1.
int 的存儲(chǔ)寬度是4哥bytes,即32個(gè)bit 即 2**32.
無符號(hào)最大值為：4294967296-1
有符號(hào)最大值：2147483648-1
有符號(hào)和無符號(hào)的最大數(shù)字需要的顯示寬度均為10，而針對(duì)有符號(hào)的最小值則需要11位才能顯示完全，所以int類型默認(rèn)的顯示寬度為11是非常合理的
最后：整形類型，其實(shí)沒有必要指定顯示寬度，使用默認(rèn)的就ok
浮點(diǎn)型：
-------------------------FLOAT-------------------
FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]
#參數(shù)解釋：單精度浮點(diǎn)數(shù)（非準(zhǔn)確小數(shù)值），M是整數(shù)部分總個(gè)數(shù)，D是小數(shù)點(diǎn)后個(gè)數(shù)。M最大值為255，D最大值為30
#有符號(hào)：
-3.402823466E+38 to -1.175494351E-38,
1.175494351E-38 to 3.402823466E+38
#無符號(hào)：
1.175494351E-38 to 3.402823466E+38
#精確度：
**** 隨著小數(shù)的增多，精度變得不準(zhǔn)確 ****
-------------------------DOUBLE-----------------------
DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]
#參數(shù)解釋： 雙精度浮點(diǎn)數(shù)（非準(zhǔn)確小數(shù)值），M是整數(shù)部分總個(gè)數(shù)，D是小數(shù)點(diǎn)后個(gè)數(shù)。M最大值為255，D最大值為30
#有符號(hào)：
-1.7976931348623157E+308 to -2.2250738585072014E-308
2.2250738585072014E-308 to 1.7976931348623157E+308
#無符號(hào)：
2.2250738585072014E-308 to 1.7976931348623157E+308
#精確度：
****隨著小數(shù)的增多，精度比float要高，但也會(huì)變得不準(zhǔn)確 ****
======================================
--------------------DECIMAL------------------------
decimal[(m[,d])] [unsigned] [zerofill]
#參數(shù)解釋：準(zhǔn)確的小數(shù)值，M是整數(shù)部分總個(gè)數(shù)（負(fù)號(hào)不算），D是小數(shù)點(diǎn)后個(gè)數(shù)。 M最大值為65，D最大值為30。
#精確度：
**** 隨著小數(shù)的增多，精度始終準(zhǔn)確 ****
對(duì)于精確數(shù)值計(jì)算時(shí)需要用此類型
decaimal能夠存儲(chǔ)精確值的原因在于其內(nèi)部按照字符串存儲(chǔ)。
驗(yàn)證三種類型建表：
#1驗(yàn)證FLOAT類型建表： mysql> create table t5(x float(256,31)); ERROR 1425 (42000): Too big scale 31 specified for column 'x'. Maximum is 30. mysql> create table t5(x float(256,30)); ERROR 1439 (42000): Display width out of range for column 'x' (max = 255) mysql> create table t5(x float(255,30)); #建表成功 Query OK, 0 rows affected (0.03 sec) #2驗(yàn)證DOUBLE類型建表： mysql> create table t6(x double(255,30)); #建表成功 Query OK, 0 rows affected (0.03 sec) #3驗(yàn)證deimal類型建表： mysql> create table t7(x decimal(66,31)); ERROR 1425 (42000): Too big scale 31 specified for column 'x'. Maximum is 30. mysql> create table t7(x decimal(66,30)); ERROR 1426 (42000): Too big precision 66 specified for column 'x'. Maximum is 65. mysql> create table t7(x decimal(65,30)); #建表成功 Query OK, 0 rows affected (0.00 sec)
驗(yàn)證三種類型的精度：
# 分別對(duì)三張表插入相應(yīng)的記錄 mysql> insert into t5 values(1.1111111111111111111111111111111);#小數(shù)點(diǎn)后31個(gè)1 Query OK, 1 row affected (0.01 sec) mysql> insert into t6 values(1.1111111111111111111111111111111); Query OK, 1 row affected (0.01 sec) mysql> insert into t7 values(1.1111111111111111111111111111111); Query OK, 1 row affected, 1 warning (0.00 sec) # 查詢結(jié)果 mysql> select * from t5; #隨著小數(shù)的增多，精度開始不準(zhǔn)確 +----------------------------------+ | x | +----------------------------------+ | 1.111111164093017600000000000000 | +----------------------------------+ 1 row in set (0.00 sec) mysql> select * from t6; #精度比float要準(zhǔn)確點(diǎn)，但隨著小數(shù)的增多，同樣變得不準(zhǔn)確 +----------------------------------+ | x | +----------------------------------+ | 1.111111111111111200000000000000 | +----------------------------------+ 1 row in set (0.00 sec) mysql> select * from t7; #精度始終準(zhǔn)確,d為30，于是只留了30位小數(shù) +----------------------------------+ | x | +----------------------------------+ | 1.111111111111111111111111111111 | +----------------------------------+ 1 row in set (0.00 sec)
日期類型：
date , time , datetime , timestamp , year
語法：
YEAR
YYYY（1901/2155）
DATE
YYYY-MM-DD（1000-01-01/9999-12-31）
TIME
HH:MM:SS（'-838:59:59'/'838:59:59'）
DATETIME
YYYY-MM-DD HH:MM:SS（1000-01-01 00:00:00/9999-12-31 23:59:59 Y）
TIMESTAMP
YYYYMMDD HHMMSS（1970-01-01 00:00:00/2037 年某時(shí)）
1,year
mysql> create table t8(born_year year);#無論year指定何種寬度，最后都默認(rèn)是year(4)
Query OK, 0 rows affected (0.03 sec)
#插入失敗，超出范圍（1901/2155）
mysql> insert into t8 values
-> (1900),
-> (1901),
-> (2155),
-> (2156);
ERROR 1264 (22003): Out of range value for column 'born_year' at row 1
mysql> select * from t8;
Empty set (0.01 sec)
mysql> insert into t8 values
-> (1905),
-> (2018);
Query OK, 2 rows affected (0.00 sec) #插入記錄成功
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from t8;
+-----------+
| born_year |
+-----------+
| 1905 |
| 2018 |
+-----------+
2 rows in set (0.00 sec)
2 date , year , datetime
#創(chuàng)建t9表 mysql> create table t9(d date,t time,dt datetime); Query OK, 0 rows affected (0.06 sec) #查看表的結(jié)構(gòu) mysql> desc t9; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | d | date | YES | | NULL | | | t | time | YES | | NULL | | | dt | datetime | YES | | NULL | | +-------+----------+------+-----+---------+-------+ 3 rows in set (0.14 sec) # 調(diào)用mysql自帶的now()函數(shù)，獲取當(dāng)前類型指定的時(shí)間 如下結(jié)構(gòu) mysql> insert into t9 values(now(),now(),now()); Query OK, 1 row affected, 1 warning (0.01 sec) mysql> select * from t9; +------------+----------+---------------------+ | d | t | dt | +------------+----------+---------------------+ | 2018-06-09 | 09:35:20 | 2018-06-09 09:35:20 | +------------+----------+---------------------+ 1 row in set (0.00 sec)
timestamp:
mysql> create table t10(time timestamp); Query OK, 0 rows affected (0.06 sec) mysql> insert into t10 values(); Query OK, 1 row affected (0.00 sec) mysql> insert into t10 values(null); Query OK, 1 row affected (0.00 sec) mysql> select * from t10; +------+ | time | +------+ | NULL | | NULL | +------+ mysql> insert into t10 values(now()); Query OK, 1 row affected (0.01 sec) mysql> select * from t10; +---------------------+ | time | +---------------------+ | 2018-06-09 09:44:48 | +---------------------+ 1 row in set (0.01 sec)
datetime與timestamp的區(qū)別
在實(shí)際應(yīng)用的很多場景中，MySQL的這兩種日期類型都能夠滿足我們的需要，存儲(chǔ)精度都為秒，但在某些情況下，會(huì)展現(xiàn)出他們各自的優(yōu)劣。 下面就來總結(jié)一下兩種日期類型的區(qū)別。 1.DATETIME的日期范圍是1001——9999年，TIMESTAMP的時(shí)間范圍是1970——2038年。 2.DATETIME存儲(chǔ)時(shí)間與時(shí)區(qū)無關(guān)，TIMESTAMP存儲(chǔ)時(shí)間與時(shí)區(qū)有關(guān)，顯示的值也依賴于時(shí)區(qū)。在mysql服務(wù)器， 操作系統(tǒng)以及客戶端連接都有時(shí)區(qū)的設(shè)置。 3.DATETIME使用8字節(jié)的存儲(chǔ)空間，TIMESTAMP的存儲(chǔ)空間為4字節(jié)。因此，TIMESTAMP比DATETIME的空間利用率更高。 4.DATETIME的默認(rèn)值為null；TIMESTAMP的字段默認(rèn)不為空（not null）,默認(rèn)值為當(dāng)前時(shí)間（CURRENT_TIMESTAMP）， 如果不做特殊處理，并且update語句中沒有指定該列的更新值，則默認(rèn)更新為當(dāng)前時(shí)間。
注意事項(xiàng)：
============注意啦，注意啦，注意啦===========
#1. 單獨(dú)插入時(shí)間時(shí)，需要以字符串的形式，按照對(duì)應(yīng)的格式插入
#2. 插入年份時(shí)，盡量使用4位值
#3. 插入兩位年份時(shí)，<=69，以20開頭，比如50, 結(jié)果2050
>=70，以19開頭，比如71，結(jié)果1971
create table t12(y year);
insert into t12 values (50),(71);
select * from t12;
+------+
| y |
+------+
| 2050 |
| 1971 |
+------+
綜合練習(xí)：
創(chuàng)建一張學(xué)生表（student），要求有id,姓名，出生年份，出生的年月日，進(jìn)班的時(shí)間，以及來老男孩學(xué)習(xí)的現(xiàn)在具體時(shí)間。
1，create database db1; 2，use db1; 3，create table student(id int,name varchar(50),born_year year,birth date,c_time datetime,n_time datetime); 4，insert into student values(1,'顧清秋',96,'1996-7-12','18-3-15 8:0:0',now());
字符類型：
#官網(wǎng)：https://dev.mysql.com/doc/refman/5.7/en/char.html
#注意：char和varchar括號(hào)內(nèi)的參數(shù)指的都是字符的長度
#char類型：定長，簡單粗暴，浪費(fèi)空間，存取速度快
字符長度范圍：0-255（一個(gè)中文是一個(gè)字符，是utf8編碼的3個(gè)字節(jié)）
存儲(chǔ)：
存儲(chǔ)char類型的值時(shí)，會(huì)往右填充空格來滿足長度
例如：指定長度為10，存>10個(gè)字符則報(bào)錯(cuò)，存<10個(gè)字符則用空格填充直到湊夠10個(gè)字符存儲(chǔ)
檢索：
在檢索或者說查詢時(shí)，查出的結(jié)果會(huì)自動(dòng)刪除尾部的空格，除非我們打開pad_char_to_full_length SQL模式（設(shè)置SQL模式：SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH';
查詢sql的默認(rèn)模式：select @@sql_mode;）
#varchar類型：變長，精準(zhǔn)，節(jié)省空間，存取速度慢
字符長度范圍：0-65535（如果大于21845會(huì)提示用其他類型 。mysql行最大限制為65535字節(jié)，字符編碼為utf-8：https://dev.mysql.com/doc/refman/5.7/en/column-count-limit.html）
存儲(chǔ)：
varchar類型存儲(chǔ)數(shù)據(jù)的真實(shí)內(nèi)容，不會(huì)用空格填充，如果'ab ',尾部的空格也會(huì)被存起來
強(qiáng)調(diào)：varchar類型會(huì)在真實(shí)數(shù)據(jù)前加1-2Bytes的前綴，該前綴用來表示真實(shí)數(shù)據(jù)的bytes字節(jié)數(shù)（1-2Bytes最大表示65535個(gè)數(shù)字，正好符合mysql對(duì)row的最大字節(jié)限制，即已經(jīng)足夠使用）
如果真實(shí)的數(shù)據(jù)<255bytes則需要1Bytes的前綴（1Bytes=8bit 2**8最大表示的數(shù)字為255）
如果真實(shí)的數(shù)據(jù)>255bytes則需要2Bytes的前綴（2Bytes=16bit 2**16最大表示的數(shù)字為65535）
檢索：
尾部有空格會(huì)保存下來，在檢索或者說查詢時(shí)，也會(huì)正常顯示包含空格在內(nèi)的內(nèi)容
length()：查看字節(jié)數(shù) char_length():查看字符數(shù)
1.char填充空格來滿足固定長度，但是在查詢時(shí)卻會(huì)很不要臉地刪除尾部的空格（裝作自己好像沒有浪費(fèi)過空間一樣），然后修改sql_mode讓其現(xiàn)出原形。
# 創(chuàng)建t1表,分別指明字段x為char類型，字段y為varchar類型
mysql> create table t1(x char(5),y varchar(4));
Query OK, 0 rows affected (0.16 sec)
# char存放的是5個(gè)字符，而varchar存4個(gè)字符
mysql> insert into t1 values('你瞅啥 ','你瞅啥 ');
Query OK, 1 row affected (0.01 sec)
# 在檢索時(shí)char很不要臉地將自己浪費(fèi)的2個(gè)字符給刪掉了，裝的好像自己沒浪費(fèi)過空間一樣，而varchar很老實(shí)，存了多少，就顯示多少
mysql> select x,char_length(x),y,char_length(y) from t1;
+-----------+----------------+------------+----------------+
| x | char_length(x) | y | char_length(y) |
+-----------+----------------+------------+----------------+
| 你瞅啥 | 3 | 你瞅啥 | 4 |
+-----------+----------------+------------+----------------+
1 row in set (0.02 sec)
#略施小計(jì)，讓char現(xiàn)原形
mysql> SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH';
Query OK, 0 rows affected (0.00 sec)
#查看當(dāng)前mysql的mode模式
mysql> select @@sql_mode;
+-------------------------+
| @@sql_mode |
+-------------------------+
| PAD_CHAR_TO_FULL_LENGTH |
+-------------------------+
1 row in set (0.00 sec)
#原形畢露了吧。。。。
mysql> select x,char_length(x) y,char_length(y) from t1;
+-------------+------+----------------+
| x | y | char_length(y) |
+-------------+------+----------------+
| 你瞅啥 | 5 | 4 |
+-------------+------+----------------+
1 row in set (0.00 sec)
# 查看字節(jié)數(shù)
#char類型：3個(gè)中文字符+2個(gè)空格=11Bytes
#varchar類型:3個(gè)中文字符+1個(gè)空格=10Bytes
mysql> select x,length(x),y,length(y) from t1;
+-------------+-----------+------------+-----------+
| x | length(x) | y | length(y) |
+-------------+-----------+------------+-----------+
| 你瞅啥 | 11 | 你瞅啥 | 10 |
+-------------+-----------+------------+-----------+
1 row in set (0.02 sec)
枚舉類型和集合類型：
字段的值只能在給定范圍中選擇，如單選框，多選框
enum 單選 只能在給定的范圍內(nèi)選一個(gè)值，如性別 sex 男male/女female
set 多選 在給定的范圍內(nèi)可以選擇一個(gè)或一個(gè)以上的值（愛好1,愛好2,愛好3...）
mysql> create table consumer();
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1
mysql> create table consumer();
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1
mysql> create table consumer(id int,name varchar(50),sex enum('male','female'),fav set('play','music','study'));
Query OK, 0 rows affected (0.05 sec)
mysql> insert into consumer values(1,'顧清秋','other','study');
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> select * from consumer;
+------+-----------+------+-------+
| id | name | sex | fav |
+------+-----------+------+-------+
| 1 | 顧清秋 | | study |
+------+-----------+------+-------+
1 row in set (0.00 sec)
mysql> insert into consumer(2,'qingqiu','male','music');
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '2,'qingqiu','male','music')' at line 1
mysql> insert into consumer(2,'qingqiu','male','music')
-> c
mysql> insert into consumer(2,'qingqiu','male','music');
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '2,'qingqiu','male','music')' at line 1
mysql> insert into consumer values(2,'qingqiu','male','music');
Query OK, 1 row affected (0.00 sec)
mysql> select * from consumer;
+------+-----------+------+-------+
| id | name | sex | fav |
+------+-----------+------+-------+
| 1 | 顧清秋 | | study |
| 2 | qingqiu | male | music |
+------+-----------+------+-------+
2 rows in set (0.00 sec)
mysql> insert into consumer values(3,'xiaobai','female','music,play');
Query OK, 1 row affected (0.01 sec)
mysql> select * from consumer;
+------+-----------+--------+------------+
| id | name | sex | fav |
+------+-----------+--------+------------+
| 1 | 顧清秋 | | study |
| 2 | qingqiu | male | music |
| 3 | xiaobai | female | play,music |
+------+-----------+--------+------------+
3 rows in set (0.00 sec)
注意：若你寫的內(nèi)容不在選項(xiàng)中，則會(huì)顯示空。
總結(jié)
- 上一篇： Python中的链表简介
- 下一篇： 部门管理制度、规范的建议