MySQL自学篇（三）

MySQL数据类型介绍

MySQL支持多种数据类型，主要有数值类型、日期类型、字符串类型

数值类型

MySQL主要提供的整数类型有：TINYINT/SMALLINT/MEDIUMINT/INT(INTEGER)/BIGINT

整数类型的属性字段可以添加AUTO_INCREMENT自增约束条件。

1、MySQL中整数类型

整数类型的取值范围

2、浮点数类型和定点数类型

MySQL中使用浮点数和定点数表示小数。浮点数类型有两种，单精度浮点数（FLOAT）和双精度浮点数（DOUBLE）。定点类型只有DECIMAL。浮点类型和定点类型都可以用（M，N）来表示，其中M成为精度，表示总共的位数；N成为标度，表示小数的位数。

不论是定点还是浮点数，如果用户指定的精度超出精度范围，则会四舍五入进行处理

范例：

创建表test1，其中字段x，y，z数据类型依次为FLOAT(5,1)/DOUBLE(5,1)/DECIMAL(5,1)向表中插入数据5.51，5.12，5.123

create table test1 

(

  x FLOAT(5,1),

  y DOUBLE(5,1),

  z DECIMAL(5,1)

);

插入数据：

 insert into test1 values (5.51,5.12,5.123);

查看数据：

select * from test1;

3、日期与时间类型

MySQL中有多种表示日期的数据类型，主要有：DATETIME,DATE,TIMESTAMP,TIME,YEAR。

A、YEAR

YEAR类型是一个单字节类型用于表示年，在存储是只要一个字节。可以使用各种格式指定YEAR的值

（1）以4位字符串或者4位数字格式表示的YEAR，范围是1901-2155.输入格式为：‘YYYY’或者YYYY。例如输入‘2010’或2010

（2）以2位字符串格式表示的YEAR，范围是‘00’——‘99’。‘00’——‘69’范围的值分别被转换为2000—2069和1970—1999范围的YEAR值。插入超过取值范围的值将被转换为2000

（3）以2位数字表示的YEAR，范围1~99.1~69和70~99范围的值分别被转换为2001~2069和1970~1999范围的YEAR值。0将被转换为0000而不是2000

范例:

创建数据表test2,定义数据类型为YEAR的字段y，插入2014，‘2014’，‘2166’

Insert into test2 values(2014),(‘2014’),(‘2166’);

B、TIME

TIME类型用在只需要时间信息的值，在存储时需要3个字节。格式为‘HH：MM：SS’。HH表示小时；MM表示分钟；SS表示秒。使用各种格式指定TI ME值。

（1）采用‘D HH:MM:SS’格式的字符串。

也可以使用下面任何一种非严格的语法：‘HH:MM:SS’/‘HH:MM’/‘D HH:MM’/‘D HH’/‘SS’。这里D表示是日，可以去0-34之间的值。插入数据库时，D被转换为小时保存，格式为“D*24+HH”

（2）采用‘HHMMSS’格式的，没有间隔符的字符串或者HHMMSS格式的数值。

范例：

创建test3表，定义数据类型为TIME的字段t，向其中插入‘11：05：05’，‘23：23’，‘2 10:10’,‘3 02’，‘10’。

insert into test3 values (‘11:05:05‘),(‘23:23‘),(‘2 10:10‘);

C、DATE类型

DATE类型用在仅仅需要日期的时候，没有时间部分，在存储时需要3个字节。日期格式为‘YYYY-MM-DD’

(1)以‘YYYY-MM-DD’或者‘YYYYMMDD’字符串格式表示日期。

(2)以‘YY-MM-DD’或者‘YYMMDD’字符串表示日期，在这里YY表示两位的年。‘00~69’范围转换为‘2000~2069’；‘70~99’范围转换为‘1970~1999’。

(3)以YY-MM-DD或者YYMMDD格式表示日期。与前面类似，00-69表示2000-2069；70-99转换为1970-1999

(4)使用CURRENT_DATE或者NOW()插入当前系统日期。

范例：

创建数据表test4,定义数据类型DATE的字段为d，插入‘YYYY-MM-DD’和‘YYYYMMDD’字符串格式的日期

insert into test4 values (‘2014-12-11‘),(20141211);

插入当前系统时间：

insert into test4 values (current_date),(now());

D、DATETIME

DATETIME类型用在需要同时包含日期和时间的值。在存储时需要8字节。日期格式为‘YYYY-MM-DD HH:MM:SS’，在给DATETIME类型的字段赋值时，可以使用字符串类型或数字类型的数据插入。

（1）以‘YYYY-MM-DD HH:MM:SS’或者’YYYYMMDDHHMMSS’字符串格式的值，其取值范围‘1001-01-01 00：00：00’~‘9999-12-3 23：59：59’

（2）以‘YY-MM-DD HH:MM:SS’或者‘YYMMDDHHMMSS’字符串格式表示日期。

（3）以YYYYMMDDHHMMSS或者YYMMDDHHMMSS的数字格式表示日期和时间

范例：

insert into test5 values (‘2014-12-11 15:27:12‘),(‘20141211152712‘),(20141211152712);

E、TIMESTAMP

TIMESTAMP显示格式与DATETIME相同，显示宽度固定在19个字符，日期格式为YYYY-MM-DD HH:MM:SS，存储时需要4字节。但是TIMESTAMP列的取值范围小于DATETIME的取值范围，为‘1970-01-01 00：00：01’UTC ~‘2038-01-19 03：14：07’UTC。UTC为世界标准时间。

范例：

创建test7，定义数据类型为TIMESTAMP的字段ts，向表中插入值‘19950101010101’，‘950505050505’，now().

insert into test6 values (‘19950101010101‘),(950505050505),(now());

4、字符串类型

MySQL中字符串类型指的：CHAR,VARCHAR,BINARY,VARBINARY,BLOB,TEXT,ENUM,SET

1、CHAR和VARCHAR类型

CHAR(M)为固定长度字符串，在定义时指定字符串列长。保存时在右侧填充空格以达到指定的长度。M表示列长，范围0~255个字符。例如：CH AR(4)定义了一个固定长度的字符串列，其包含的字符个数最大为4.当检索到CHAR值时，尾部的空空格将被删除。

VARCHAR(M)是长度可变的字符串，M表示列最大长度，M的范围0~65535。VARCHAR的最大实际长度由最长的行的大小和使用的字符集确定，而其实际占用的空间为字符串的实际长度+1。例如VARCHAR(50)定义了一个最大长度为50的字符串。如果插入的字符串只有10个字符，则实际存储的字符串为10个字符串+一个结束字符串。VARCHAR在值保存和检索时，尾部的空格仍保留。

2、TEXT类型

TEXT列保存非二进制字符串，如文章，评论等。当保存或查询TEXT列的值时，不删除尾部空格。TEXT类型分为4中：TINYTEXT/TEXT/MEDIUMTEXT/LONGTEXT。不同的TEXT类型的存储空间和数据长度不同。

（1）TINYTEXT最大长度为255字符的TEXT列

（2）TEXT最大长度为65535字符的TEXT列

（3）MEDIUMTEXT最大长度为16777215字符的TEXT列

（4）LONGTEXT最大长度为4294967295(4GB)字符的列

3、ENUM类型

ENUM是一个字符串对象，其值为表创建时在列规定中枚举的一列值。语法格式如下：

字段名 ENUM(‘值1’，‘值2’...‘值n’)

字段名指将要定义的字段，值n指枚举列表中的第n个值。ENUM类型的字段在取值时，只能在指定的枚举列表中取值，而且一次只能取一个。如果创建的成员中有空格时，其尾部的空格将自动被删除。ENUM值在内部用整数表示，每个枚举值均有一个索引：列表值所允许的成员值从1开始编号，MySQL存储的就是这个索引编号。枚举最多可以有65535个元素。

例如：定义ENUM类型的列（‘first’,’second’,’third’）,该列可以取的值和每个值的索引如下：