mysql之表操作

印度阿三17 2019-06-20

展開全文

閱讀目錄

　　數(shù)值類型
　　日期時(shí)間類型
　　字符串類型
　　ENUM和SET類型

數(shù)值類型

MySQL支持所有標(biāo)準(zhǔn)SQL數(shù)值數(shù)據(jù)類型。

這些類型包括嚴(yán)格數(shù)值數(shù)據(jù)類型(INTEGER、SMALLINT、DECIMAL和NUMERIC)，以及近似數(shù)值數(shù)據(jù)類型(FLOAT、REAL和DOUBLE PRECISION)。

關(guān)鍵字INT是INTEGER的同義詞，關(guān)鍵字DEC是DECIMAL的同義詞。

MySQL支持的整數(shù)類型有TINYINT、MEDIUMINT和BIGINT。下面的表顯示了需要的每個(gè)整數(shù)類型的存儲(chǔ)和范圍。

對(duì)于小數(shù)的表示，MYSQL分為兩種方式：浮點(diǎn)數(shù)和定點(diǎn)數(shù)。浮點(diǎn)數(shù)包括float(單精度)和double(雙精度),而定點(diǎn)數(shù)只有decimal一種，在mysql中以字符串的形式存放，比浮點(diǎn)數(shù)更精確，適合用來表示貨幣等精度高的數(shù)據(jù)。

BIT數(shù)據(jù)類型保存位字段值，并且支持MyISAM、MEMORY、InnoDB和BDB表。

?

類型	大小	范圍（有符號(hào)）	范圍（無符號(hào)）unsigned約束	用途
TINYINT	1 字節(jié)	(-128，127)	(0，255)	小整數(shù)值
SMALLINT	2 字節(jié)	(-32 768，32 767)	(0，65 535)	大整數(shù)值
MEDIUMINT	3 字節(jié)	(-8 388 608，8 388 607)	(0，16 777 215)	大整數(shù)值
INT或INTEGER	4 字節(jié)	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	大整數(shù)值
BIGINT	8 字節(jié)	(-9 233 372 036 854 775 808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)	極大整數(shù)值
FLOAT	4 字節(jié) float(255,30)	(-3.402 823 466 E 38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E 38)	0，(1.175 494 351 E-38，3.402 823 466 E 38)	單精度浮點(diǎn)數(shù)值
DOUBLE	8 字節(jié) double(255,30)	(-1.797 693 134 862 315 7 E 308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E 308)	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E 308)	雙精度浮點(diǎn)數(shù)值
DECIMAL	對(duì)DECIMAL(M,D) ，如果M>D，為M 2否則為D 2 double(65,30)	依賴于M和D的值	依賴于M和D的值	小數(shù)值

# 創(chuàng)建表一個(gè)是默認(rèn)寬度的int，一個(gè)是指定寬度的int(5)
mysql> create table t1 (id1 int,id2 int(5));
Query OK, 0 rows affected (0.02 sec)

# 像t1中插入數(shù)據(jù)1，1
mysql> insert into t1 values (1,1);
Query OK, 1 row affected (0.01 sec)

# 可以看出結(jié)果上并沒有異常
mysql> select * from t1;
 ------ ------ 
| id1  | id2  |
 ------ ------ 
|    1 |    1 |
 ------ ------ 
1 row in set (0.00 sec)

# 那么當(dāng)我們插入了比寬度更大的值，會(huì)不會(huì)發(fā)生報(bào)錯(cuò)呢？
mysql> insert into t1 values (111111,111111);
Query OK, 1 row affected (0.00 sec)

# 答案是否定的，id2仍然顯示了正確的數(shù)值，沒有受到寬度限制的影響
mysql> select * from t1;
 ------------ -------- 
| id1        | id2    |
 ------------ -------- 
| 0000000001 |  00001 |
| 0000111111 | 111111 |
 ------------ -------- 
2 rows in set (0.00 sec)

# 修改id1字段 給字段添加一個(gè)unsigned表示無符號(hào)
mysql> alter table t1 modify id1 int unsigned;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t1;
 ------- ------------------ ------ ----- --------- ------- 
| Field | Type             | Null | Key | Default | Extra |
 ------- ------------------ ------ ----- --------- ------- 
| id1   | int(10) unsigned | YES  |     | NULL    |       |
| id2   | int(5)           | YES  |     | NULL    |       |
 ------- ------------------ ------ ----- --------- ------- 
2 rows in set (0.01 sec)

# 當(dāng)給id1添加的數(shù)據(jù)大于214748364時(shí)，可以順利插入
mysql> insert into t1 values (2147483648,2147483647);
Query OK, 1 row affected (0.00 sec)

# 當(dāng)給id2添加的數(shù)據(jù)大于214748364時(shí)，會(huì)報(bào)錯(cuò)
mysql> insert into t1 values (2147483647,2147483648);
ERROR 1264 (22003): Out of range value for column 'id2' at row 1

int整數(shù)示例

# 創(chuàng)建表的三個(gè)字段分別為float，double和decimal參數(shù)表示一共顯示5位，小數(shù)部分占2位
mysql> create table t2 (id1 float(5,2),id2 double(5,2),id3 decimal(5,2));
Query OK, 0 rows affected (0.02 sec)

# 向表中插入1.23，結(jié)果正常
mysql> insert into t2 values (1.23,1.23,1.23);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t2;
 ------ ------ ------ 
| id1  | id2  | id3  |
 ------ ------ ------ 
| 1.23 | 1.23 | 1.23 |
 ------ ------ ------ 
1 row in set (0.00 sec)

# 向表中插入1.234，會(huì)發(fā)現(xiàn)4都被截?cái)嗔?mysql> insert into t2 values (1.234,1.234,1.234);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from t2;
 ------ ------ ------ 
| id1  | id2  | id3  |
 ------ ------ ------ 
| 1.23 | 1.23 | 1.23 |
| 1.23 | 1.23 | 1.23 |
 ------ ------ ------ 
2 rows in set (0.00 sec)

# 向表中插入1.235發(fā)現(xiàn)數(shù)據(jù)雖然被截?cái)?，但是遵循了四舍五入的?guī)則
mysql> insert into t2 values (1.235,1.235,1.235);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from t2;
 ------ ------ ------ 
| id1  | id2  | id3  |
 ------ ------ ------ 
| 1.23 | 1.23 | 1.23 |
| 1.23 | 1.23 | 1.23 |
| 1.24 | 1.24 | 1.24 |
 ------ ------ ------ 
3 rows in set (0.00 sec)

# 建新表去掉參數(shù)約束
mysql> create table t3 (id1 float,id2 double,id3 decimal);
Query OK, 0 rows affected (0.02 sec)

# 分別插入1.234
mysql> insert into t3 values (1.234,1.234,1.234);
Query OK, 1 row affected, 1 warning (0.00 sec)

# 發(fā)現(xiàn)decimal默認(rèn)值是(10,0)的整數(shù)
mysql> select * from t3;
 ------- ------- ------ 
| id1   | id2   | id3  |
 ------- ------- ------ 
| 1.234 | 1.234 |    1 |
 ------- ------- ------ 
1 row in set (0.00 sec)

# 當(dāng)對(duì)小數(shù)位沒有約束的時(shí)候，輸入超長的小數(shù)，會(huì)發(fā)現(xiàn)float和double的區(qū)別
mysql> insert into t3 values (1.2355555555555555555,1.2355555555555555555,1.2355555555555555555555);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from t3;
 --------- -------------------- ------ 
| id1     | id2                | id3  |
 --------- -------------------- ------ 
|   1.234 |              1.234 |    1 |
| 1.23556 | 1.2355555555555555 |    1 |
 --------- -------------------- ------ 
2 rows in set (0.00 sec)

小數(shù)示例返回頂部

日期和時(shí)間類型

表示時(shí)間值的日期和時(shí)間類型為DATETIME、DATE、TIMESTAMP、TIME和YEAR。

每個(gè)時(shí)間類型有一個(gè)有效值范圍和一個(gè)"零"值，當(dāng)指定不合法的MySQL不能表示的值時(shí)使用"零"值。

TIMESTAMP類型有專有的自動(dòng)更新特性，將在后面描述。

類型	大小 (字節(jié))	范圍	格式	用途
DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD	年月日
TIME	3	'-838:59:59'/'838:59:59'	HH:MM:SS	時(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	4	1970-01-01 00:00:00/2038 結(jié)束時(shí)間是第?2147483647?秒，北京時(shí)間?2038-1-19 11:14:07，格林尼治時(shí)間 2038年1月19日凌晨 03:14:07	YYYYMMDD HHMMSS	混合日期和時(shí)間值，時(shí)間戳

mysql> create table t4 (d date,t time,dt datetime);
Query OK, 0 rows affected (0.02 sec)

mysql> desc t4;
 ------- ---------- ------ ----- --------- ------- 
| Field | Type     | Null | Key | Default | Extra |
 ------- ---------- ------ ----- --------- ------- 
| d     | date     | YES  |     | NULL    |       |
| t     | time     | YES  |     | NULL    |       |
| dt    | datetime | YES  |     | NULL    |       |
 ------- ---------- ------ ----- --------- ------- 
3 rows in set (0.01 sec)

mysql> insert into t4 values (now(),now(),now());
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> select * from t4;
 ------------ ---------- --------------------- 
| d          | t        | dt                  |
 ------------ ---------- --------------------- 
| 2018-09-21 | 14:51:51 | 2018-09-21 14:51:51 |
 ------------ ---------- --------------------- 
1 row in set (0.00 sec)

mysql> insert into t4 values (null,null,null);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t4;
 ------------ ---------- --------------------- 
| d          | t        | dt                  |
 ------------ ---------- --------------------- 
| 2018-09-21 | 14:51:51 | 2018-09-21 14:51:51 |
| NULL       | NULL     | NULL                |
 ------------ ---------- --------------------- 
2 rows in set (0.00 sec)

date/time/datetime示例

mysql> create table t5 (id1 timestamp);
Query OK, 0 rows affected (0.02 sec)

mysql> desc t5;
 ------- ----------- ------ ----- ------------------- ----------------------------- 
| Field | Type      | Null | Key | Default           | Extra                       |
 ------- ----------- ------ ----- ------------------- ----------------------------- 
| id1   | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
 ------- ----------- ------ ----- ------------------- ----------------------------- 
1 row in set (0.00 sec)

# 插入數(shù)據(jù)null，會(huì)自動(dòng)插入當(dāng)前時(shí)間的時(shí)間
mysql> insert into t5 values (null);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t5;
 --------------------- 
| id1                 |
 --------------------- 
| 2018-09-21 14:56:50 |
 --------------------- 
1 row in set (0.00 sec)

#添加一列 默認(rèn)值是'0000-00-00 00:00:00'
mysql> alter table t5 add id2 timestamp;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table t5 \G;
*************************** 1. row ***************************
       Table: t5
Create Table: CREATE TABLE `t5` (
  `id1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `id2` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

ERROR: 
No query specified

# 手動(dòng)修改新的列默認(rèn)值為當(dāng)前時(shí)間
mysql> alter table t5 modify id2 timestamp default current_timestamp;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table t5 \G;
*************************** 1. row ***************************
       Table: t5
Create Table: CREATE TABLE `t5` (
  `id1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `id2` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql> insert into t5 values (null,null);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t5;
 --------------------- --------------------- 
| id1                 | id2                 |
 --------------------- --------------------- 
| 2018-09-21 14:56:50 | 0000-00-00 00:00:00 |
| 2018-09-21 14:59:31 | 2018-09-21 14:59:31 |
 --------------------- --------------------- 
2 rows in set (0.00 sec)

timestamp示例

mysql> create table t6 (t1 timestamp);
Query OK, 0 rows affected (0.02 sec)

mysql> desc t6;
 ------- ----------- ------ ----- ------------------- ----------------------------- 
| Field | Type      | Null | Key | Default           | Extra                       |
 ------- ----------- ------ ----- ------------------- ----------------------------- 
| t1    | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
 ------- ----------- ------ ----- ------------------- ----------------------------- 
1 row in set (0.01 sec)

mysql> insert into t6 values (19700101080001);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t6;
 --------------------- 
| t1                  |
 --------------------- 
| 1970-01-01 08:00:01 |
 --------------------- 
1 row in set (0.00 sec)
# timestamp時(shí)間的下限是19700101080001
mysql> insert into t6 values (19700101080000);
ERROR 1292 (22007): Incorrect datetime value: '19700101080000' for column 't1' at row 1

mysql> insert into t6 values ('2038-01-19 11:14:07');
Query OK, 1 row affected (0.00 sec)
# timestamp時(shí)間的上限是2038-01-19 11:14:07
mysql> insert into t6 values ('2038-01-19 11:14:08');
ERROR 1292 (22007): Incorrect datetime value: '2038-01-19 11:14:08' for column 't1' at row 1
mysql>

timestamp示例2

mysql> create table t7 (y year);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t7 values (2018);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t7;
 ------ 
| y    |
 ------ 
| 2018 |
 ------ 
1 row in set (0.00 sec)

year示例

mysql> create table t8 (dt datetime);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t8 values ('2018-9-26 12:20:10');
Query OK, 1 row affected (0.01 sec)

mysql> insert into t8 values ('2018/9/26 12 20 10');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t8 values ('20180926122010');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t8 values (20180926122010);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t8;
 --------------------- 
| dt                  |
 --------------------- 
| 2018-09-26 12:20:10 |
| 2018-09-26 12:20:10 |
| 2018-09-26 12:20:10 |
| 2018-09-26 12:20:10 |
 --------------------- 
4 rows in set (0.00 sec)

datetime示例返回頂部

字符串類型

字符串類型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。該節(jié)描述了這些類型如何工作以及如何在查詢中使用這些類型。

類型	大小	用途
CHAR	0-255字節(jié)	定長字符串
VARCHAR	0-65535 字節(jié)	變長字符串
TINYBLOB	0-255字節(jié)	不超過 255 個(gè)字符的二進(jìn)制字符串
TINYTEXT	0-255字節(jié)	短文本字符串
BLOB	0-65 535字節(jié)	二進(jìn)制形式的長文本數(shù)據(jù)
TEXT	0-65 535字節(jié)	長文本數(shù)據(jù)
MEDIUMBLOB	0-16 777 215字節(jié)	二進(jìn)制形式的中等長度文本數(shù)據(jù)
MEDIUMTEXT	0-16 777 215字節(jié)	中等長度文本數(shù)據(jù)
LONGBLOB	0-4 294 967 295字節(jié)	二進(jìn)制形式的極大文本數(shù)據(jù)
LONGTEXT	0-4 294 967 295字節(jié)	極大文本數(shù)據(jù)

CHAR 和 VARCHAR 類型類似，但它們保存和檢索的方式不同。它們的最大長度和是否尾部空格被保留等方面也不同。在存儲(chǔ)或檢索過程中不進(jìn)行大小寫轉(zhuǎn)換。

CHAR列的長度固定為創(chuàng)建表是聲明的長度,范圍(0-255);而VARCHAR的值是可變長字符串范圍(0-65535)。

mysql> create table t9 (v varchar(4),c char(4));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t9 values ('ab  ','ab  ');
Query OK, 1 row affected (0.00 sec)

# 在檢索的時(shí)候char數(shù)據(jù)類型會(huì)去掉空格
mysql> select * from t9;
 ------ ------ 
| v    | c    |
 ------ ------ 
| ab   | ab   |
 ------ ------ 
1 row in set (0.00 sec)

# 來看看對(duì)查詢結(jié)果計(jì)算的長度
mysql> select length(v),length(c) from t9;
 ----------- ----------- 
| length(v) | length(c) |
 ----------- ----------- 
|         4 |         2 |
 ----------- ----------- 
1 row in set (0.00 sec)

# 給結(jié)果拼上一個(gè)加號(hào)會(huì)更清楚
mysql> select concat(v,' '),concat(c,' ') from t9;
 --------------- --------------- 
| concat(v,' ') | concat(c,' ') |
 --------------- --------------- 
| ab            | ab            |
 --------------- --------------- 
1 row in set (0.00 sec)

# 當(dāng)存儲(chǔ)的長度超出定義的長度，會(huì)截?cái)?mysql> insert into t9 values ('abcd  ','abcd  ');
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> select * from t9;
 ------ ------ 
| v    | c    |
 ------ ------ 
| ab   | ab   |
| abcd | abcd |
 ------ ------ 
2 rows in set (0.00 sec)

char/varchar示例

BINARY 和 VARBINARY 類似于 CHAR 和 VARCHAR，不同的是它們包含二進(jìn)制字符串而不要非二進(jìn)制字符串。也就是說，它們包含字節(jié)字符串而不是字符字符串。這說明它們沒有字符集，并且排序和比較基于列值字節(jié)的數(shù)值值。

BLOB 是一個(gè)二進(jìn)制大對(duì)象，可以容納可變數(shù)量的數(shù)據(jù)。有 4 種 BLOB 類型：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它們區(qū)別在于可容納存儲(chǔ)范圍不同。

有 4 種 TEXT 類型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。對(duì)應(yīng)的這 4 種 BLOB 類型，可存儲(chǔ)的最大長度不同，可根據(jù)實(shí)際情況選擇。

返回頂部

ENUM和SET類型

ENUM中文名稱叫枚舉類型，它的值范圍需要在創(chuàng)建表時(shí)通過枚舉方式顯示。ENUM只允許從值集合中選取單個(gè)值，而不能一次取多個(gè)值。

SET和ENUM非常相似，也是一個(gè)字符串對(duì)象，里面可以包含0-64個(gè)成員。根據(jù)成員的不同，存儲(chǔ)上也有所不同。set類型可以允許值集合中任意選擇1或多個(gè)元素進(jìn)行組合。對(duì)超出范圍的內(nèi)容將不允許注入，而對(duì)重復(fù)的值將進(jìn)行自動(dòng)去重。

類型

大小

用途

ENUM

對(duì)1-255個(gè)成員的枚舉需要1個(gè)字節(jié)存儲(chǔ);

對(duì)于255-65535個(gè)成員，需要2個(gè)字節(jié)存儲(chǔ);

最多允許65535個(gè)成員。

單選：選擇性別

SET

1-8個(gè)成員的集合，占1個(gè)字節(jié)

9-16個(gè)成員的集合，占2個(gè)字節(jié)

17-24個(gè)成員的集合，占3個(gè)字節(jié)

25-32個(gè)成員的集合，占4個(gè)字節(jié)

33-64個(gè)成員的集合，占8個(gè)字節(jié)

多選：興趣愛好

mysql> create table t10 (name char(20),gender enum('female','male'));
Query OK, 0 rows affected (0.01 sec)

# 選擇enum('female','male')中的一項(xiàng)作為gender的值，可以正常插入
mysql> insert into t10 values ('nezha','male');
Query OK, 1 row affected (0.00 sec)

# 不能同時(shí)插入'male,female'兩個(gè)值，也不能插入不屬于'male,female'的值
mysql> insert into t10 values ('nezha','male,female');
ERROR 1265 (01000): Data truncated for column 'gender' at row 1

mysql> create table t11 (name char(20),hobby set('抽煙','喝酒','燙頭','翻車'));
Query OK, 0 rows affected (0.01 sec)

# 可以任意選擇set('抽煙','喝酒','燙頭','翻車')中的項(xiàng)，并自帶去重功能
mysql> insert into t11 values ('yuan','燙頭,喝酒,燙頭');
Query OK, 1 row affected (0.01 sec)

mysql> select * from t11;
 ------ --------------- 
| name | hobby        |
 ------ --------------- 
| yuan | 喝酒,燙頭     |
 ------ --------------- 
1 row in set (0.00 sec)

# 不能選擇不屬于set('抽煙','喝酒','燙頭','翻車')中的項(xiàng)，
mysql> insert into t11 values ('alex','燙頭,翻車,看妹子');
ERROR 1265 (01000): Data truncated for column 'hobby' at row 1

set/enum示例

?

來源：https://www./content-2-253501.html

本站是提供個(gè)人知識(shí)管理的網(wǎng)絡(luò)存儲(chǔ)空間，所有內(nèi)容均由用戶發(fā)布，不代表本站觀點(diǎn)。請(qǐng)注意甄別內(nèi)容中的聯(lián)系方式、誘導(dǎo)購買等信息，謹(jǐn)防詐騙。如發(fā)現(xiàn)有害或侵權(quán)內(nèi)容，請(qǐng)點(diǎn)擊一鍵舉報(bào)。