溫馨提示×

您好，登錄后才能下訂單哦！

密碼登錄×
登錄注冊(cè)×
其他方式登錄
點(diǎn)擊 登錄注冊(cè) 即表示同意《億速云用戶服務(wù)條款》

2009-05-19--05-20 MySQL學(xué)習(xí)筆記02

發(fā)布時(shí)間：2020-08-12 12:06:08 來源：ITPUB博客 閱讀：146 作者：oraclecaicai 欄目：MySQL數(shù)據(jù)庫

1.整數(shù)類型

TINYINT 1字節(jié)
SMALLINT 2字節(jié)
MEDIUMINT 3字節(jié)
INT或INTEGER 4字節(jié)
BIGINT 8字節(jié)

整數(shù)類型后面圓括號(hào)中的數(shù)字代表該整型字段的顯示寬度，如果數(shù)字位不足就自動(dòng)用空格填充，但這不會(huì)影響該字段的大小和可存儲(chǔ)的值的范圍。
UNSIGNED修飾符規(guī)定字段只保存正值；ZEROFILL修飾符規(guī)定用0來代替空格用于填補(bǔ)輸出值，使用它的字段也不能存儲(chǔ)負(fù)值。

[@more@]mysql> create table int_test
-> (
-> num1 tinyint,
-> num2 tinyint(3),
-> num3 tinyint unsigned,
-> num4 tinyint(3) zerofill
-> );
Query OK, 0 rows affected (0.12 sec)

mysql> desc int_test;
+-------+------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------------------+------+-----+---------+-------+
| num1 | tinyint(4) | YES | | NULL | |
| num2 | tinyint(3) | YES | | NULL | |
| num3 | tinyint(3) unsigned | YES | | NULL | |
| num4 | tinyint(3) unsigned zerofill | YES | | NULL | |
+-------+------------------------------+------+-----+---------+-------+
4 rows in set (0.03 sec)

mysql> insert into int_test values(1,1,1,1);
Query OK, 1 row affected (0.43 sec)

mysql> insert into int_test values(-1,-1,-1,-1);
ERROR 1264 (22003): Out of range value for column 'num3' at row 1
mysql> insert into int_test values(-1,-1,1,-1);
ERROR 1264 (22003): Out of range value for column 'num4' at row 1
mysql> insert into int_test values(127,127,127,127);
Query OK, 1 row affected (0.59 sec)

mysql> insert into int_test values(128,128,128,128);
ERROR 1264 (22003): Out of range value for column 'num1' at row 1
mysql> insert into int_test values(127,127,128,127);
Query OK, 1 row affected (0.04 sec)

mysql> select * from int_test;
+------+------+------+------+
| num1 | num2 | num3 | num4 |
+------+------+------+------+
| 1 | 1 | 1 | 001 |
| 127 | 127 | 127 | 127 |
| 127 | 127 | 128 | 127 |
+------+------+------+------+
3 rows in set (0.03 sec)


2.浮點(diǎn)類型

FLOAT 4字節(jié)
DOUBLE或REAL DOUBLE PRECISION 8字節(jié)
DECIMAL或DEC NUMERIC 對(duì)DECIMAL(M,D)，如果M>D，為M+2，否則為D+2。

浮點(diǎn)類型后面圓括號(hào)中的兩個(gè)數(shù)字分別為一個(gè)顯示寬度指示器和一個(gè)小數(shù)點(diǎn)指示器。
UNSIGNED和ZEROFILL修飾符同樣可以用于浮點(diǎn)類型，效果與用于整數(shù)類型時(shí)類似。
MySQL以二進(jìn)制格式保存DECIMAL數(shù)據(jù)類型，用于保存必須為確切精度的值。

mysql> create table float_test
-> (
-> num1 float,
-> num2 float(5,2),
-> num3 float unsigned,
-> num4 float(5,2) zerofill,
-> num5 decimal,
-> num6 decimal(5,2)
-> );
Query OK, 0 rows affected (0.58 sec)

mysql> desc float_test;
+-------+------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------------------+------+-----+---------+-------+
| num1 | float | YES | | NULL | |
| num2 | float(5,2) | YES | | NULL | |
| num3 | float unsigned | YES | | NULL | |
| num4 | float(5,2) unsigned zerofill | YES | | NULL | |
| num5 | decimal(10,0) | YES | | NULL | |
| num6 | decimal(5,2) | YES | | NULL | |
+-------+------------------------------+------+-----+---------+-------+
6 rows in set (0.05 sec)

mysql> insert into float_test values (123.321, 123.321, 123.321, 123.321, 123.32
1, 123.321);
Query OK, 1 row affected, 2 warnings (0.04 sec)

mysql> insert into float_test values (-123.3, -123.3, -123.3, -123.3, -123.3, -1
23.3);
ERROR 1264 (22003): Out of range value for column 'num3' at row 1
mysql> insert into float_test values (-123.3, -123.3, 123.3, -123.3, -123.3, -12
3.3);
ERROR 1264 (22003): Out of range value for column 'num4' at row 1
mysql> insert into float_test values (-123.3, -123.3, 123.3, 123.3, -123.3, -123
.3);
Query OK, 1 row affected, 1 warning (0.04 sec)

mysql> insert into float_test values (123.456, 123.456, 123.456, 123.456, 123.45
6, 123.456);
Query OK, 1 row affected, 2 warnings (0.03 sec)

mysql> insert into float_test values (-123.456, -123.456, 123.456, 123.456, -123
.456, -123.456);
Query OK, 1 row affected, 2 warnings (0.03 sec)

mysql> select * from float_test;
+----------+---------+---------+--------+------+---------+
| num1 | num2 | num3 | num4 | num5 | num6 |
+----------+---------+---------+--------+------+---------+
| 123.321 | 123.32 | 123.321 | 123.32 | 123 | 123.32 |
| -123.3 | -123.30 | 123.3 | 123.30 | -123 | -123.30 |
| 123.456 | 123.46 | 123.456 | 123.46 | 123 | 123.46 |
| -123.456 | -123.46 | 123.456 | 123.46 | -123 | -123.46 |
+----------+---------+---------+--------+------+---------+
4 rows in set (0.02 sec)


3.字符串類型

CHAR 0-255字節(jié)
VARCHAR 0-255字節(jié)
TINYBLOB 0-255字節(jié)
TINYTEXT 0-255字節(jié)
BLOB 0-65535字節(jié)
TEXT 0-65535字節(jié)
MEDIUMBLOB 0-16777215字節(jié)
MEDIUMTEXT 0-16777215字節(jié)
LONGBLOB 0-294967295字節(jié)
LONGTEXT 0-294967295字節(jié)

CHAR類型用于定長字符串，其后面圓括號(hào)中的數(shù)字指定要存儲(chǔ)的值的長度，范圍為0到255。比指定長度小的值會(huì)用空格適當(dāng)填補(bǔ)，比指定長度大的值將被自動(dòng)截短。
VARCHAR類型用于變長字符串，其后面圓括號(hào)中的數(shù)字指定存儲(chǔ)的值的最大長度（必須指定），范圍為0到255。比指定最大長度小的值會(huì)以其實(shí)際大小存儲(chǔ)，不會(huì)用空格填充，比指定最大長度大的值將被自動(dòng)截短。
CHAR和VARCHAR類型的值默認(rèn)情況下不區(qū)分大小寫，可以使用BINARY修飾符改變這一點(diǎn)。

mysql> create table char_test
-> (
-> string1 char,
-> string2 char(5),
-> string3 char(5) binary,
-> string4 varchar(5),
-> string5 varchar(5) binary
-> );
Query OK, 0 rows affected (0.49 sec)

mysql> desc char_test;
+---------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+------------+------+-----+---------+-------+
| string1 | char(1) | YES | | NULL | |
| string2 | char(5) | YES | | NULL | |
| string3 | char(5) | YES | | NULL | |
| string4 | varchar(5) | YES | | NULL | |
| string5 | varchar(5) | YES | | NULL | |
+---------+------------+------+-----+---------+-------+
5 rows in set (0.01 sec)

mysql> insert into char_test values ('abc', 'abc', 'abc', 'abc', 'abc');
ERROR 1406 (22001): Data too long for column 'string1' at row 1

這里發(fā)生了錯(cuò)誤，要插入的記錄第一個(gè)字段的長度超過了string1字段默認(rèn)指定的長度1，這是因?yàn)?a title="服務(wù)器" target="_blank" href="http://kemok4.com/">服務(wù)器運(yùn)行在嚴(yán)格模式。查看my.ini中的相關(guān)設(shè)置：

# Set the SQL mode to strict
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

將這部分修改為：

# Set the SQL mode to strict
#sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

重啟服務(wù)器，再重新嘗試：

mysql> insert into char_test values ('abc', 'abc', 'abc', 'abc', 'abc');
Query OK, 1 row affected, 1 warning (0.05 sec)

這次記錄順利插入char_test表。繼續(xù)之前的實(shí)驗(yàn)：

mysql> insert into char_test values ('abc', 'abc', 'abc', 'abc', 'abc');
Query OK, 1 row affected, 1 warning (0.05 sec)

mysql> insert into char_test values ('abcde', 'abcde', 'abcde', 'abcde', 'abcde'
);
Query OK, 1 row affected, 1 warning (0.44 sec)

mysql> select * from char_test;
+---------+---------+---------+---------+---------+
| string1 | string2 | string3 | string4 | string5 |
+---------+---------+---------+---------+---------+
| a | abc | abc | abc | abc |
| a | abcde | abcde | abcde | abcde |
+---------+---------+---------+---------+---------+
2 rows in set (0.00 sec)

mysql> select * from char_test where string2 = 'ABC';
+---------+---------+---------+---------+---------+
| string1 | string2 | string3 | string4 | string5 |
+---------+---------+---------+---------+---------+
| a | abc | abc | abc | abc |
+---------+---------+---------+---------+---------+
1 row in set (0.41 sec)

mysql> select * from char_test where string3 = 'ABC';
Empty set (0.00 sec)

mysql> select * from char_test where string4 = 'ABC';
+---------+---------+---------+---------+---------+
| string1 | string2 | string3 | string4 | string5 |
+---------+---------+---------+---------+---------+
| a | abc | abc | abc | abc |
+---------+---------+---------+---------+---------+
1 row in set (0.00 sec)

mysql> select * from char_test where string5 = 'ABC';
Empty set (0.00 sec)


BLOB和TEXT（TEXT類型是不區(qū)分大小寫的BLOB類型?）類型用于存儲(chǔ)比較長的可變長字符串，但不能像VARCHAR類型那樣為它們指定最大長度。每一種BLOG或TEXT類型的最大長度都是固定的，超過的值將被自動(dòng)截短。如果不確定應(yīng)該使用哪種BLOB或TEXT類型，可以在BLOB或TEXT（其它的不可以）后面加一個(gè)圓括號(hào)，并在括號(hào)中輸入字符串的最大長度，這樣系統(tǒng)會(huì)自動(dòng)選擇合適的類型。

mysql> create table blob_text_test
-> (
-> string1 blob(255),
-> string2 text(255),
-> string3 blob(65535),
-> string4 text(65535),
-> string5 blob(16777215),
-> string6 text(16777215),
-> string7 blob(294967295),
-> string8 text(294967295)
-> );
Query OK, 0 rows affected (0.52 sec)

mysql> desc blob_text_test
-> ;
+---------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+------------+------+-----+---------+-------+
| string1 | tinyblob | YES | | NULL | |
| string2 | tinytext | YES | | NULL | |
| string3 | blob | YES | | NULL | |
| string4 | text | YES | | NULL | |
| string5 | mediumblob | YES | | NULL | |
| string6 | mediumtext | YES | | NULL | |
| string7 | longblob | YES | | NULL | |
| string8 | longtext | YES | | NULL | |
+---------+------------+------+-----+---------+-------+
8 rows in set (0.03 sec)


4.日期和時(shí)間類型

類型 大小 范圍 格式 用途
DATE 3 1000-01-01 ~ 9999-12-31 YYYY-MM-DD 日期值
TIME 3 '-838:59:59' ~ '838:59:59' HH:MM:SS 時(shí)間值或持續(xù)時(shí)間
YEAR 1 1901 ~ 2155 YYYY 年份值
DATETIME 8 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59 YYYY-MM-DD HH:MM:SS 混合日期和時(shí)間值
TIMESTAMP 8 1970-01-01 00:00:00 ~ 2037年的某個(gè)時(shí)候 YYYYMMDDHHMMSS 混合日期和時(shí)間，時(shí)間戳

這些類型可以描述為字符串或不帶分隔符的整數(shù)序列，如果描述為字符串，應(yīng)該按照上表“范圍”列中的格式。

mysql> create table date_time_test
-> (
-> dt1 date,
-> dt2 time,
-> dt3 year,
-> dt4 datetime,
-> dt5 timestamp
-> );
Query OK, 0 rows affected (0.49 sec)

mysql> insert into date_time_test values ('2008-05-12', '014:28:57', '2008', '20
08-05-12 14:28:57', '2008-05-12 14:28:57');
Query OK, 1 row affected (0.03 sec)

mysql> insert into date_time_test values (20080512, 0142857, 2008, 2008051214285
7, 20080512142857);
Query OK, 1 row affected (0.03 sec)

mysql> select * from date_time_test;
+------------+----------+------+---------------------+---------------------+
| dt1 | dt2 | dt3 | dt4 | dt5 |
+------------+----------+------+---------------------+---------------------+
| 2008-05-12 | 14:28:57 | 2008 | 2008-05-12 14:28:57 | 2008-05-12 14:28:57 |
| 2008-05-12 | 14:28:57 | 2008 | 2008-05-12 14:28:57 | 2008-05-12 14:28:57 |
+------------+----------+------+---------------------+---------------------+
2 rows in set (0.00 sec)

可以看到，使用字符串和整數(shù)序列描述方式插入的記錄的是完全相同的。當(dāng)輸入的值格式不標(biāo)準(zhǔn)時(shí)，MySQL盡可能地去“理解”它們：

mysql> insert into date_time_test values ('2008-5-12', '3:4:5', '08', '2008-5-12
3:4:5', '08-5-12 3:4:5');
Query OK, 1 row affected (0.04 sec)

mysql> insert into date_time_test values (080512, 142857, 008, 2008512345, 08051
21428);
Query OK, 1 row affected, 1 warning (0.44 sec)

mysql> insert into date_time_test values ('2008-05', 1428, '8', '8-5-12 14:28',
'2008-05 14:28');
Query OK, 1 row affected, 2 warnings (0.04 sec)

mysql> select * from date_time_test;
+------------+----------+------+---------------------+---------------------+
| dt1 | dt2 | dt3 | dt4 | dt5 |
+------------+----------+------+---------------------+---------------------+
| 2008-05-12 | 14:28:57 | 2008 | 2008-05-12 14:28:57 | 2008-05-12 14:28:57 |
| 2008-05-12 | 14:28:57 | 2008 | 2008-05-12 14:28:57 | 2008-05-12 14:28:57 |
| 2008-05-12 | 03:04:05 | 2008 | 2008-05-12 03:04:05 | 2008-05-12 03:04:05 |
| 2008-05-12 | 14:28:57 | 2008 | 0000-00-00 00:00:00 | 2000-08-05 12:14:28 |
| 0000-00-00 | 00:14:28 | 2008 | 0008-05-12 14:28:00 | 0000-00-00 00:00:00 |
+------------+----------+------+---------------------+---------------------+
5 rows in set (0.00 sec)

對(duì)于TIMESTAMP類型，當(dāng)被指定為NULL，或作為記錄中的第一個(gè)字段而未被明確指定值時(shí)，MySQL將會(huì)用當(dāng)前的日期和時(shí)間自動(dòng)填充它，而DATETIME類型則不行。

mysql> create table ts_dt_test
-> (
-> dt1 timestamp,
-> dt2 timestamp,
-> dt3 datetime
-> );
Query OK, 0 rows affected (0.52 sec)

mysql> desc ts_dt_test;
+-------+-----------+------+-----+---------------------+------------------------
-----+
| Field | Type | Null | Key | Default | Extra
|
+-------+-----------+------+-----+---------------------+------------------------
-----+
| dt1 | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMES
TAMP |
| dt2 | timestamp | NO | | 0000-00-00 00:00:00 |
|
| dt3 | datetime | YES | | NULL |
|
+-------+-----------+------+-----+---------------------+------------------------
-----+
3 rows in set (0.01 sec)


mysql> insert into ts_dt_test values (NULL, NULL, NULL);
Query OK, 1 row affected (0.03 sec)

mysql> insert into ts_dt_test (dt3) values (NOW());
Query OK, 1 row affected (0.06 sec)

mysql> insert into ts_dt_test (dt2, dt3) values (NOW(), NOW());
Query OK, 1 row affected (0.03 sec)

mysql> select * from ts_dt_test;
+---------------------+---------------------+---------------------+
| dt1 | dt2 | dt3 |
+---------------------+---------------------+---------------------+
| 2009-05-20 15:09:20 | 2009-05-20 15:09:20 | NULL |
| 2009-05-20 15:10:20 | 0000-00-00 00:00:00 | 2009-05-20 15:10:20 |
| 2009-05-20 15:10:59 | 2009-05-20 15:10:59 | 2009-05-20 15:10:59 |
+---------------------+---------------------+---------------------+
3 rows in set (0.00 sec)

最后，嘗試插入不合法的日期和時(shí)間值，MySQL會(huì)自動(dòng)將其置0。

mysql> insert into ts_dt_test values ('2009-02-30', '2009-05-32', '2009-01-01 24
:00:00');
Query OK, 1 row affected, 3 warnings (0.03 sec)

mysql> select * from ts_dt_test;
+---------------------+---------------------+---------------------+
| dt1 | dt2 | dt3 |
+---------------------+---------------------+---------------------+
| 2009-05-20 15:09:20 | 2009-05-20 15:09:20 | NULL |
| 2009-05-20 15:10:20 | 0000-00-00 00:00:00 | 2009-05-20 15:10:20 |
| 2009-05-20 15:10:59 | 2009-05-20 15:10:59 | 2009-05-20 15:10:59 |
| 2009-05-20 15:26:44 | 2009-05-20 15:20:11 | NULL |
| 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
+---------------------+---------------------+---------------------+
5 rows in set (0.00 sec)


5.復(fù)合類型

MySQL有兩個(gè)復(fù)合類型ENUM和SET，它們擴(kuò)展了SQL的規(guī)范。ENUM類型必須從一個(gè)允許值的集合中選擇單個(gè)值，而SET類型可以從允許值的集合中選擇任意多個(gè)值。詳細(xì)用法這里不再贅述。

向AI問一下細(xì)節(jié)

免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如果涉及侵權(quán)請(qǐng)聯(lián)系站長郵箱：is@yisu.com進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權(quán)內(nèi)容。

AI
助
手