MySQL学习笔记(2)—— 数据类型和列类型

以下的文章主要向大家介绍的是MySQL数据类型,MySQL数据类型主要可以分成四种其中包括数值型、字符串)型与日期和时间型与NULL值。如果你对这些数据库类型十分感兴趣的话,你就可以浏览以下的文章了。

关于mysql的数据类型1

MySQL支持大量的列类型,它可以被分为3类:数字类型、日期和时间类型以及字符串(字符)类型。首先给出可用类型的一个概述,并且总结每个列类型的存储需求,然后提供每个类中的类型性质的更详细的描述。

1. MySQL数据类型

 

MySQL的基本数据类型

在MySQL中有如下几种数据类型:

  1. mysql的数据类型 
     

数字

MySQL中的数字是类似于100或3.1415926这样的值。MySQL支持说明为整数(无小数部分)或浮点数(有小数部分)的值:

  • 整数由数字序列组成;浮点数由一个阿拉伯数字序列、一个小数点和另一个阿拉伯数字序列组成。两个阿拉伯数字序列可以分别为空,但不能同时为空。
  • MySQL支持科学表示法,科学表示法由整数或浮点数后跟“e”或“E”、一个符号(“+”或“-”,必须具有)和一个整数指数来表示。
    数值前可放一个负号“-”以表示负值。

1)数值型

在mysql中有如下几种数据类型: 
 

十六进制数

MySQL支持十六进制值。以十六进制形式表示的整数由“0x”后跟一个或多个十六进制数字(”0”到“9”及“a”到“f”)组成。十六进制数字不区分大小写,但其前缀“0x”不能为“0X”。

数值是诸如32 或153.4 这样的值。MySQL
支持科学表示法,科学表示法由整数或浮点数后跟“e”或“E”、一个符号“+”或“-”)和一个整数指数来表示。1.24E+12
和23.47e-1 都是合法的科学表示法表示的数。而1.24E12
不是合法的,因为指数前的符号未给出。

(1)数值型   www.2cto.com  

日期和时间值

日期和时间值是一些类似于“1999-06-17”或“12:30:43”这样的值。MySQL还支持日期/时间的组合,如“1999-06-17
12:30:43”。需要注意的是,MySQL是按年-月-日的顺序表示日期的,这可能与你学过的高
级语言不同。

浮点数由整数部分、一个小数点和小数部分组成。整数部分和小数部分可以分别为空,但不能同时为空。

数值是诸如32 或153.4 这样的值。mysql
支持科学表示法,科学表示法由整数或浮点数后跟“e”或“e”、一个符号(“+”或“-”)和一个整数指数来表示。1.24e+12
和23.47e-1 都是合法的科学表示法表示的数。而1.24e12
不是合法的,因为指数前的符号未给出。 

字符串

字符串既可以是用双引号括起来,也可以是用单引号。例如:“I like
MySQL”或’MySQL is powerful’。

表格 转意序列列表

符号 含义 符号 含义
\0 ASCII 0(NUL)字符 \n 换行
\r 回车 \t 定位符
\b 退格 \ 反斜杠
单引号 " 双引号
% 百分号 _ 下划线

数值前可放一个负号“-”以表示负值。

浮点数由整数部分、一个小数点和小数部分组成。整数部分和小数部分可以分别为空,但不能同时为空。 

NULL值

NULL值可适用于各种列类型,它通常用来表示“没有值”、
“无数据”等意义,并且不同于例如数字类型的0为或字符串类型的空字符串。

注意 NUL字节与NULL值不同:NUL为一个零值字节,而 NULL 代表没有值。

2)字符串)型

数值前可放一个负号“-”以表示负值。 
 

MySQL的列类型

数据库中的每个表都是由一个或多个列构成的。可以用 CREATE TABLE
语句创建一个表,创建表时要为每列指定一个类型。列的类型与数据类型相对应,但是比数据类型更为具体,用列类型描述表列可能包含的值的种类以及范围,列的值必须符合规定,不能包含对应的数据类型所允许的所有值。例如,CHAR(16)就规定了存储的字符串值必须是16位。当然不是你必须存储16个字符,而是指列在表中要占16个字符的宽度的。

MySQL
的列类型是一种手段,通过这种手段可以描述一个表列包含什么类型的值,这又决定了
MySQL
怎样处理这些值。例如,数据值既可用数值也可用串的列类型来存放,但是根据存放这些值的类型,MySQL
对它们的处理将会有些不同。每种列类型都有几个特性如下:

  • 其中可以存放什么类型的值
  • 值要占据多少空间,以及该值是否是定长的或可变长的
  • 该类型的值怎样比较和存储
  • 该类型是否允许 NULL 值
  • 该类型是否可以索引

下面是创建一个表的例子:

CREATE TABLE Teacher
(
    ID TINYINT UNSIGNED NOT NULL,  
    Name CHAR(16) NOT NULL,
    Tel NUMERIC(8),
    Sex ENUM("F","M") DEFAULT "M"
)

由上面这个例子可以知道,创建列类型的语法是:
<pre><code>
col_name col_type [col_attributes][general_attributes]
</code></pre>

其中:

  • col_name 列的名字
  • col_type 列类型,控制存储在列中的数据类型
  • col_attributes 专用属性,只能应用于制定列
  • general_attributes 通用属性,例如上面提到了NULL、NOT NULL和DEFAULT

为方便使用,下面以文字说明和表格的形式展示MySQL的常见列类型。

字符型也叫字符串型,简称串)是诸如“Hello,
world!”或“一个馒头引起的血案”这样的值,或者是电话号码87398413这样的值。既可用单引号也可用双引号将串值括起来。

(2)字符(串)型 

数字列类型

MySQL支持所有的ANSI/ISO SQL92的数字类型。这些类型包括准确数字的数据类型
(NUMERIC, DECIMAL, INTEGER和 SMALLINT),也包括近似数字的数据类型(FLOAT,
REAL,和 DOUBLE PRECISION)。关键词 INT 是 INTEGER 的一个同义词,而关键词
DEC 是 DECIMAL 一个同义词。

MySQL 的数字列类型有两种:

  • 整型 MySQL 提供了五种整型 TINYINT、SAMLLINT、INT、 MEDIUMINT 和
    BIGINT。整数列可以用 UNSIGNED 禁用负数值。
  • 浮点型 MySQL提供了三种浮点型,FLOAT、DOUBLE和DECIMAL。

表格 MySQL的数字列类型——整数类型

类型名 取值范围(有符号或无符号) 占用存储(字节)
TINYINT -128~127或0~2^8-1) 1
SMALLINT -32768~32767或0~2^16-1 2
MEDIUMINT -223~223-1或0~2^24-1 3
INT -231~231-1或0~2^32-1 4
BIGINT -263~263-1或0~2^64-1 8

例如,建立一个数字列表格,输入以下代码:

CREATE TABLE number
(
    tiny TINYINT,u_tiny TINYINT UNSIGNED,
    small SMALLINT,u_small SMALLINT UNSIGNED,
    medium MEDIUMINT,u_medium MEDIUMINT UNSIGNED,
    num INT,u_num INT UNSIGNED,
    big BIGINT,u_big BIGINT UNSIGNED
)

然后显示这个表的结构

<pre><code>
DESCRIBE number
</pre></code>

得到表格输出结果如下:

+----------+-----------------------+
|  Field   |        Type           |
+----------+-----------------------+
| tiny     | tinyint(4)            |
| u_tiny   | tinyint(3) unsigned   |
| small    | smallint(6)           |
| u_small  | smallint(5) unsigned  |
| medium   | mediumint(9)          |
| u_medium | mediumint(8) unsigned |
| num      | int(11)               |
| u_num    | int(11) unsigned      |
| big      | bigint(20)            |
| u_big    | bigint(20) unsigned   |
+----------+-----------------------+

表格 MySQL的数字列类型——浮点类型

类型名 占用存储(字节)
FLOAT 4
DOUBLE 8
DECIMAL(M) M+2 (ver >3.23)

在为列选择了使用某种数值类型时,除了要考虑数据的类型外,还应该注意所要表示的值的范围和存储需求,只需选择能覆盖要取值的范围的最小类型即可。

初学者往往分不清数值87398143和字符串87398143的区别。都是数字啊,怎么一
个要用数值型,一个要用字符型呢?关键就在于:数值型的87398143是要参与计算的,比如它是金融中的一个货款总额;而字符型的87398143是不
参与计算的,只是表示电话号码,这样的还有街道号码、门牌号码等等,它们都不参与计算。

字符型(也叫字符串型,简称串)是诸如“hello,
world!”或“一个馒头引起的血案”这样的值,或者是电话号码87398413这样的值。既可用单引号也可用双引号将串值括起来。
初学者往往分不清数值87398143和字符串87398143的区别。都是数字啊,怎么一个要用数值型,一个要用字符型呢?关键就在于:数值型的87398143是要参与计算的,比如它是金融中的一个货款总额;而字符型的87398143是不参与计算的,只是表示电话号码,这样的还有街道号码、门牌号码等等,它们都不参与计算。 

日期和时间列类型

表格 MySQL的日期和时间列类型

类型名 含义 类型名 含义
DATE 日期, ‘YYYY-MM-DD’ TIME 时间, ‘HH:MM:SS’
DATETIME 时间和时间组合, ‘YYYY-MM-DD HH:MM:SS’ TIMESTAMP 时间戳,以 YYYMMDDHHMMSS 格式来显示

举例,生成一个日期和时间表,并插入2个元素:

CREATE TABLE my_test
(
    id INT,
    ts TIMESTAMP
)
INSERT my_test VALUES(1,20010101000000)
INSERT my_test(id) VALUES(2)

SELECT * from my_test

显示得到结果输出如下:

+------+----------------+
|  id  |     ts         |
+------+----------------+
|  1   | 20010101000000 |
|  2   | 20010113165713 |
+------+----------------+

3)日期和时间型

(3)日期和时间型 

字符串列类型

MySQL 提供的字符串类型包括 CHAR、VARCHAR、BLOB、TEXT、ENUM 和 SET。
对这些类型作一个简要的叙述如下:

表格 MySQL的字符串列类型

类型名 含义 类型名 含义
CHAR 定长字符串 VARCHAR 变长字符串
TINYBLOB BLOB(2^8-1) TINYTEXT TEXT(2^8-1)
BLOB BLOB(2^16-1) TEXT TEXT(2^16-1)
MEDIUMBLOB BLOB(2^24-1) MEDIUMTEXT TEXT(2^24-1)
LONGBLOB BLOB(2^32-1) LONGTEXT TEXT(2^32-1)
ENUM(‘value1′,’value2’,…) 枚举:列只能赋值为某个枚举成员或NULL SET(‘value1′,’value2’,…) 集合:列可以赋值为多个集合成员或NULL

表格中,BLOB是一个能保存可变数量的数据的二进制的大对象。在某种意义上,串实际是一种非常“通用”类型,因为可用它们来表示任意值,不仅仅是字符串。例如,可用串类型来存储二进制数据,如图像、视频或音频。


日期和时间是一些诸如“2006-07-12”或“12:30:43”这样的值。MySQL还支持日期/时间的组合,如“2006-07-12
12:30:43”。

日期和时间是一些诸如“2006-07-12”或“12:30:43”这样的值。mysql还支持日期/时间的组合,如“2006-07-12
12:30:43”。 
 

小结

本节对 MySQL 的数据类型和列类型进行了简单的描述,是我们继续学习 SQL
语言的基础。文中出现大量的 SQL
语句,尤其是建表的语句,你可能现在还不知道如何完成这些查询,你只需要理解其中的含义就可以了,因为阅读了后面的章节你就会理解这
一切。

4)NULL值

(4)null值 

NULL表示未知值。比如填写表格中通讯地址不清楚留空不填写,这就是NULL值。

null表示未知值。比如填写表格中通讯地址不清楚留空不填写,这就是null值。 

我们用Create
Table语句创建一个表参看前面的章节),这个表中包含列的定义。例如我们在前面创建了一个joke表,这个表中有content和writer两个列:

我们用createtable语句创建一个表(参看前面的章节),这个表中包含列的定义。例如我们在前面创建了一个joke表,这个表中有content和writer两个列:

定义一个列的语法如下:

create table ( content varchar(20) not null, writer varchar(10) null )

其中列名由col_name
给出。列名可最多包含64个字符,字符包括字母、数字、下划线及美元符号。列名可以名字中合法的任何符号包括数字)开头。但列名不能完全由数字组成,因
为那样可能使其与MySQL数据类型分不开。MySQL保留诸如SELECT、DELETE和CREATE这样的词,这些词不能用做列名,但是函数名如POS
和MIN)是可以使用的。

 

列类型col_type表示列可存储的特定值。列类型说明符还能表示存放在列中的值的最大长
度。对于某些类型,可用一个数值明确地说明其长度。而另外一些值,其长度由类型名蕴含。例如,CHAR(10)
明确指定了10个字符的长度,而TINYBLOB值隐含最大长度为255个字符。

定义一个列的语法如下:

有的类型说明符允许指定最大的显示宽度即显示值时使用多少个字符)。浮
点类型允许指定小数位数,所以能控制浮点数的精度值为多少。

col_name col_typy [col_attributes] [general_attributes]

可以在列类型之后指定可选的类型说明属性,以及指定更多的常见属性。属性起修饰类型的作用,并更改其处理列值的方式,属性有以下类型:

   www.2cto.com  

1)专用属性用于指定列。例如,UNSIGNED
属性只针对整型,而BINARY属性只用于CHAR 和VARCHAR。

其中列名由col_name
给出。列名可最多包含64个字符,字符包括字母、数字、下划线及美元符号。列名可以名字中合法的任何符号(包括数字)开头。但列名不能完全由数字组成,因为那样可能使其与数据分不开。mysql保留诸如select、delete和create这样的词,这些词不能用做列名,但是函数名(如pos
和min)是可以使用的。 
 

2)通用属性除少数列之外可用于任意列。可以指定NULL 或NOT NULL
以表示某个列是否能够存放NULL。还可以用DEFAULT,def_value
来表示在创建一个新行但未明确给出该列的值时,该列可赋予值def_value。def_value
必须为一个常量;它不能是表达式,也不能引用其他列。不能对BLOB 或TEXT
列指定缺省值。

列类型col_type表示列可存储的特定值。列类型说明符还能表示存放在列中的值的最大长度。对于某些类型,可用一个数值明确地说明其长度。而另外一些值,其长度由类型名蕴含。例如,char(10)
明确指定了10个字符的长度,而tinyblob值隐含最大长度为255个字符。有的类型说明符允许指定最大的显示宽度(即显示值时使用多少个字符)。浮点类型允许指定小数位数,所以能控制浮点数的精度值为多少。 
 

如果想给出多个列的专用属性,可按任意顺序指定它们,只要它们跟在列类型之后、通用属性之前即可。类似地,如果需要给出多个通用属性,也可按任意顺序给出它们,只要将它们放在列类型和可能给出的列专用属性之后即可。

可以在列类型之后指定可选的类型说明属性,以及指定更多的常见属性。属性起修饰类型的作用,并更改其处理列值的方式,属性有以下类型: 

2. MySQL的列字段)类型

(1)专用属性用于指定列。例如,unsigned
属性只针对整型,而binary属性只用于char 和varchar。 

数据库中的每个表都是由一个或多个列字段)构成的。在用CREATE
TABLE语句创建一个表时,要为每列字段)指定一个类型。列字段)的类型比MySQL数据类型更为细化,它精确地描述了给定表列字段)可能包含的值的种类,如是否带小数、是否文字很多。

(2)通用属性除少数列之外可用于任意列。可以指定null 或not null
以表示某个列是否能够存放null。还可以用default,def_value
来表示在创建一个新行但未明确给出该列的值时,该列可赋予值def_value。def_value
必须为一个常量;它不能是表达式,也不能引用其他列。不能对blob 或text
列指定缺省值。 

如果想给出多个列的专用属性,可按任意顺序指定它们,只要它们跟在列类型之后、通用属性之前即可。类似地,如果需要给出多个通用属性,也可按任意顺序给出它们,只要将它们放在列类型和可能给出的列专用属性之后即可。
 

  1. mysql的列(字段)类型 

数据库中的每个表都是由一个或多个列(字段)构成的。在用create
table语句创建一个表时,要为每列(字段)指定一个类型。列(字段)的类型比数据类型更为细化,它精确地描述了给定表列(字段)可能包含的值的种类,如是否带小数、是否文字很多。 

2.1数值列类型
mysql有整数和浮点数值的列类型,如表1所示。整数列类型可以有符号也可无符号。有一种特殊的属性允许整数列值自动生成,这对需要唯一序列或标识号的应用系统来说是非常有用的。
 www.2cto.com  

类型

说明

tinyint
非常小的整数

smallint
较小整数

mediumint
中等大小整数

int 标准整数

bigint
较大整数

float
单精度浮点数

double
双精度浮点数

decimal
一个串的浮点数

表1:数值列类型

每种数值类型的名称和取值范围如表2所示。

 

类型说明

取值范围