mysql之表操作

阅读目录

　　数值类型
　　日期时间类型
　　字符串类型
　　ENUM和SET类型

返回顶部

数值类型

MySQL支持所有标准SQL数值数据类型。

这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL和NUMERIC)，以及近似数值数据类型(FLOAT、REAL和DOUBLE PRECISION)。

关键字INT是INTEGER的同义词，关键字DEC是DECIMAL的同义词。

MySQL支持的整数类型有TINYINT、MEDIUMINT和BIGINT。下面的表显示了需要的每个整数类型的存储和范围。

对于小数的表示，MYSQL分为两种方式：浮点数和定点数。浮点数包括float(单精度)和double(双精度),而定点数只有decimal一种，在mysql中以字符串的形式存放，比浮点数更精确，适合用来表示货币等精度高的数据。

BIT数据类型保存位字段值，并且支持MyISAM、MEMORY、InnoDB和BDB表。

类型	大小	范围（有符号）	范围（无符号）unsigned约束	用途
TINYINT	1 字节	(-128，127)	(0，255)	小整数值
SMALLINT	2 字节	(-32 768，32 767)	(0，65 535)	大整数值
MEDIUMINT	3 字节	(-8 388 608，8 388 607)	(0，16 777 215)	大整数值
INT或INTEGER	4 字节	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	大整数值
BIGINT	8 字节	(-9 233 372 036 854 775 808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)	极大整数值
FLOAT	4 字节 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)	单精度浮点数值
DOUBLE	8 字节 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)	双精度浮点数值
DECIMAL	对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2 double(65,30)	依赖于M和D的值	依赖于M和D的值	小数值

# 创建表一个是默认宽度的int，一个是指定宽度的int(5)
mysql> create table t1 (id1 int,id2 int(5));
Query OK, 0 rows affected (0.02 sec)
像t1中插入数据1，1
mysql> insert into t1 values (1,1);

Query OK, 1 row affected (0.01 sec)
可以看出结果上并没有异常
mysql> select * from t1;

+------+------+

| id1  | id2  |

+------+------+

|    1 |    1 |

+------+------+

1 row in set (0.00 sec)
那么当我们插入了比宽度更大的值，会不会发生报错呢？
mysql> insert into t1 values (111111,111111);

Query OK, 1 row affected (0.00 sec)
答案是否定的，id2仍然显示了正确的数值，没有受到宽度限制的影响
mysql> select * from t1;

+------------+--------+

| id1        | id2    |

+------------+--------+

| 0000000001 |  00001 |

| 0000111111 | 111111 |

+------------+--------+

2 rows in set (0.00 sec)
修改id1字段 给字段添加一个unsigned表示无符号
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)
当给id1添加的数据大于214748364时，可以顺利插入
mysql> insert into t1 values (2147483648,2147483647);

Query OK, 1 row affected (0.00 sec)
当给id2添加的数据大于214748364时，会报错
mysql> insert into t1 values (2147483647,2147483648);

ERROR 1264 (22003): Out of range value for column 'id2' at row 1

int整数示例

# 创建表的三个字段分别为float，double和decimal参数表示一共显示5位，小数部分占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，结果正常
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，会发现4都被截断了
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发现数据虽然被截断，但是遵循了四舍五入的规则
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)
建新表去掉参数约束
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)
发现decimal默认值是(10,0)的整数
mysql> select * from t3;

+-------+-------+------+

| id1   | id2   | id3  |

+-------+-------+------+

| 1.234 | 1.234 |    1 |

+-------+-------+------+

1 row in set (0.00 sec)
当对小数位没有约束的时候，输入超长的小数，会发现float和double的区别
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)

小数示例

返回顶部

日期和时间类型

表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。

每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。

TIMESTAMP类型有专有的自动更新特性，将在后面描述。

类型	大小 (字节)	范围	格式	用途
DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD	年月日
TIME	3	'-838:59:59'/'838:59:59'	HH:MM:SS	时分秒
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	年月日时分秒
TIMESTAMP	4	1970-01-01 00:00:00/2038 结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日凌晨 03:14:07	YYYYMMDD HHMMSS	混合日期和时间值，时间戳

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)
插入数据null，会自动插入当前时间的时间
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)
添加一列 默认值是'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
手动修改新的列默认值为当前时间
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时间的下限是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时间的上限是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。该节描述了这些类型如何工作以及如何在查询中使用这些类型。

类型	大小	用途
CHAR	0-255字节	定长字符串
VARCHAR	0-65535 字节	变长字符串
TINYBLOB	0-255字节	不超过 255 个字符的二进制字符串
TINYTEXT	0-255字节	短文本字符串
BLOB	0-65 535字节	二进制形式的长文本数据
TEXT	0-65 535字节	长文本数据
MEDIUMBLOB	0-16 777 215字节	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16 777 215字节	中等长度文本数据
LONGBLOB	0-4 294 967 295字节	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295字节	极大文本数据

CHAR 和 VARCHAR 类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。

CHAR列的长度固定为创建表是声明的长度,范围(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)
在检索的时候char数据类型会去掉空格
mysql> select * from t9;

+------+------+

| v    | c    |

+------+------+

| ab   | ab   |

+------+------+

1 row in set (0.00 sec)
来看看对查询结果计算的长度
mysql> select length(v),length(c) from t9;

+-----------+-----------+

| length(v) | length(c) |

+-----------+-----------+

|         4 |         2 |

+-----------+-----------+

1 row in set (0.00 sec)
给结果拼上一个加号会更清楚
mysql> select concat(v,'+'),concat(c,'+') from t9;

+---------------+---------------+

| concat(v,'+') | concat(c,'+') |

+---------------+---------------+

| ab  +         | ab+           |

+---------------+---------------+

1 row in set (0.00 sec)
当存储的长度超出定义的长度，会截断
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，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。

BLOB 是一个二进制大对象，可以容纳可变数量的数据。有 4 种 BLOB 类型：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们区别在于可容纳存储范围不同。

有 4 种 TEXT 类型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。对应的这 4 种 BLOB 类型，可存储的最大长度不同，可根据实际情况选择。

返回顶部

ENUM和SET类型

ENUM中文名称叫枚举类型，它的值范围需要在创建表时通过枚举方式显示。ENUM只允许从值集合中选取单个值，而不能一次取多个值。

SET和ENUM非常相似，也是一个字符串对象，里面可以包含0-64个成员。根据成员的不同，存储上也有所不同。set类型可以允许值集合中任意选择1或多个元素进行组合。对超出范围的内容将不允许注入，而对重复的值将进行自动去重。

类型

大小

用途

ENUM

对1-255个成员的枚举需要1个字节存储;

对于255-65535个成员，需要2个字节存储;

最多允许65535个成员。

单选：选择性别

SET

1-8个成员的集合，占1个字节

9-16个成员的集合，占2个字节

17-24个成员的集合，占3个字节

25-32个成员的集合，占4个字节

33-64个成员的集合，占8个字节

多选：兴趣爱好

mysql> create table t10 (name char(20),gender enum('female','male'));
Query OK, 0 rows affected (0.01 sec)
选择enum('female','male')中的一项作为gender的值，可以正常插入
mysql> insert into t10 values ('nezha','male');

Query OK, 1 row affected (0.00 sec)
不能同时插入'male,female'两个值，也不能插入不属于'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('抽烟','喝酒','烫头','翻车')中的项，并自带去重功能
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('抽烟','喝酒','烫头','翻车')中的项，
mysql> insert into t11 values ('alex','烫头,翻车,看妹子');

ERROR 1265 (01000): Data truncated for column 'hobby' at row 1

posted @ 2019-06-20 20:37 Pyter 阅读(150) 评论(0) 编辑收藏举报

刷新页面返回顶部

pyter

如冰虽不冻

mysql之表操作

阅读目录

数值类型

像t1中插入数据1，1

可以看出结果上并没有异常

那么当我们插入了比宽度更大的值，会不会发生报错呢？

答案是否定的，id2仍然显示了正确的数值，没有受到宽度限制的影响

修改id1字段给字段添加一个unsigned表示无符号

当给id1添加的数据大于214748364时，可以顺利插入

当给id2添加的数据大于214748364时，会报错

向表中插入1.23，结果正常

向表中插入1.234，会发现4都被截断了

向表中插入1.235发现数据虽然被截断，但是遵循了四舍五入的规则

建新表去掉参数约束

分别插入1.234

发现decimal默认值是(10,0)的整数

当对小数位没有约束的时候，输入超长的小数，会发现float和double的区别

日期和时间类型

插入数据null，会自动插入当前时间的时间

添加一列默认值是'0000-00-00 00:00:00'

手动修改新的列默认值为当前时间

timestamp时间的下限是19700101080001

timestamp时间的上限是2038-01-19 11:14:07

字符串类型

在检索的时候char数据类型会去掉空格

来看看对查询结果计算的长度

给结果拼上一个加号会更清楚

当存储的长度超出定义的长度，会截断

ENUM和SET类型

选择enum('female','male')中的一项作为gender的值，可以正常插入

不能同时插入'male,female'两个值，也不能插入不属于'male,female'的值

可以任意选择set('抽烟','喝酒','烫头','翻车')中的项，并自带去重功能

不能选择不属于set('抽烟','喝酒','烫头','翻车')中的项，

公告

pyter

如冰虽不冻

mysql之表操作

阅读目录

数值类型

像t1中插入数据1，1

可以看出结果上并没有异常

那么当我们插入了比宽度更大的值，会不会发生报错呢？

答案是否定的，id2仍然显示了正确的数值，没有受到宽度限制的影响

修改id1字段 给字段添加一个unsigned表示无符号

当给id1添加的数据大于214748364时，可以顺利插入

当给id2添加的数据大于214748364时，会报错

向表中插入1.23，结果正常

向表中插入1.234，会发现4都被截断了

向表中插入1.235发现数据虽然被截断，但是遵循了四舍五入的规则

建新表去掉参数约束

分别插入1.234

发现decimal默认值是(10,0)的整数

当对小数位没有约束的时候，输入超长的小数，会发现float和double的区别

日期和时间类型

插入数据null，会自动插入当前时间的时间

添加一列 默认值是'0000-00-00 00:00:00'

手动修改新的列默认值为当前时间

timestamp时间的下限是19700101080001

timestamp时间的上限是2038-01-19 11:14:07

字符串类型

在检索的时候char数据类型会去掉空格

来看看对查询结果计算的长度

给结果拼上一个加号会更清楚

当存储的长度超出定义的长度，会截断

ENUM和SET类型

选择enum('female','male')中的一项作为gender的值，可以正常插入

不能同时插入'male,female'两个值，也不能插入不属于'male,female'的值

可以任意选择set('抽烟','喝酒','烫头','翻车')中的项，并自带去重功能

不能选择不属于set('抽烟','喝酒','烫头','翻车')中的项，

公告

修改id1字段给字段添加一个unsigned表示无符号

添加一列默认值是'0000-00-00 00:00:00'