MySQL数据库(三)—MySQL数据库数据类型和运算符
一、 MySQL数据类型介绍
MySQL支持多种数据类型,主要有数值类型
、日期类型
、时间类型
和字符串类型
。
整数类型
数据类型主要用来存储数字
,MySQL提供多种可供选择的整数类型,用整数作为数据类型的字段可以设置auto_increment自增约束条件.
浮点和定点数据类型
MySQL中使用浮点数和定点数来表示小数
。浮点有分为单浮点和双浮点。
日期与时间类型
(1) YEAR
YEAR类型是一个单字节类型
,用于表示年,在存储时只需要1个字节。可以使用各种格式指定YEAR值,如下所示:
-
以4位字符串或者4位数字格式表示的YEAR,范围为1901~2155。输入格式’YYYY’或者YYYY。
-
以2位字符串格式表示的YEAR,范围为00到99。00~69转换为2000~2069;70~99转换为1970~1999;
-
以2位数字表示的YEAR值,范围为1~99。1~69值是2001~2069;70~99范围值转换为1970~1999,而在这里0值被转换为0000;
(2) TIME
TIME类型用于表示时间信息,在存储时需要3个字节。表示某一事件过去的时间和两个事件之间的时间间隔。可以大于24甚至为负值
。 ·
-
‘D HH:MM:SS’格式的字符串。有’HH:MM’,‘D HH:MM’ ,‘D HH’ ,‘SS’。D表示日,可以取0~34之间的值。在插入数据库时D被转换为小时保存(D*24+HH)。
-
'HHMMSS’格式的数值,会自动分成HH:MM:SS但各自有取值范围,超过则输入不合法。
(3) DATE
DATE只用于输入日期,没有时间,在存储时需要3个字节。日期格式为:‘YYYY-MM-DD’ 。
-
以’YYYY-MM-DD’或者’YYYYMMDD’字符串格式表示日期,取值范围是’1000-01-01’~‘9999-12-03’。
-
以’YY-MM-DD’或者’YYMMDD’字符串格式表示日期,取值范围与年数相关,00~69年值范围是2000~2069,70~99年值范围是1970~1999。
-
以YY-MM-DD或者YYMMDD数字格式表示日期,取值范围与年数相关,00~69年值范围是2000~2069,70~99年值范围是1970~1999。
-
使用current_date或者now(),插入当前系统日期。
(4) DATETIME
DATETIME用于同时输入日期和时间信息,在存储时需要8个字节。格式为:’’YYYY-MM-DD HH:MM:SS’, 只要符合格式使用字符串和数字类型均可。
-
以’YYYY-MM-DD HH:MM:SS’或者’YYYYMMDDHHMMSS’字符串格式表示日期,取值范围是’1000-01-01 00:00:00’~‘9999-12-03 23:59:59’ 。
-
以’YY-MM-DD HH:MM:SS’或者’YYMMDDHHMMSS’字符串格式表示日期,取值范围与年数相关,00~69年值范围是2000~2069,,70~99年值范围是1970~1999。
-
以YYYYMMDDHHMMSS或者YYMMDDHHMMSS数字格式表示日期,取值范围与年数相关,00~69年值范围是2000~2069,,70~99年值范围是1970~1999。
-
使用now(),插入当前系统日期时间。
(5) TIMESTAMP
TIMESTAMP的显示格式与DATETIME相同,显示宽度是19个字符。存储需要4个字节,格式为:‘YYYY-MM-DD HH:MM:SS’ 。但是TIMESTAMP列的取值范围小于DATETIME的取值范围,为’1970-01-01 00:00:01’UTC~‘2038-01-19 03:14:07’ UTC。其中UTC(Coordinated Universal Time)世界标准时间,因此在插入数据时要保证在合法取值范围内。
字符串类型
字符串类型除了可以存储字符串数据以外,还可以存储其他数据,比如图片、声音和二进制数据
。字符串可以进行区分或或者不区分大小写的串比较。
MySQL中的字符串char
、varchar
、binary
、varbinary
、blob
、tinytext
、text
、enum
和set
。
(1) char和varchar类型
char(M)为固定长度字符串
,在定义时要指定字符串列长。当保存时在右侧填充空格以达到指定长度。M表示列长度,当检索时实际值尾部空格会自动删除。
varchar(M)为长度可变的字符串
,M表示列最大长度,但是保存时实际占用空间为字符串实际长度加1。不管是保存还是检索varchar的值时实际值尾部的空格都会保留。
(2) text类型
text列保存非二进制字符串,如文章、评论等等。当保存和查询text列值时,不删除尾部空格。Text类型分为四种:tinytext、text、mediumtext和longtext。不同的text类型存储空间和数据长度不同。
-
tinytext:最大长度255(28~1)个字符的text列。
-
text:最大长度为65535(216~1)个字符的text列。
-
mediumtext:最大长度为16777215(224~1)个字符的text列。
-
longtext:最大长度为4294967295或4GB(232~1)个字符的text列。
(3) enum类型
enum是一个字符串对象,其值为创建时在列规定中枚举的一列值。Enum类型的字段取值时只能在指定的枚举列表中取,而且一次只能取一个。如果创建的成员中有空格,则其尾部的空格将自动删除。Enum值在内部用整数表示,每个枚举值均有一个索引值:列表值所允许的成员值从1开始编号,MySQL存储的就是这个索引编号。枚举最多可以有65535个元素。
Enum 值依照列索引顺序排列,并且空字符排在非空字符串之前,null值排在其他所有枚举值之前。
4) set类型
set类型是一个字符串对象,可以有一个或者多个值,set列最多可以有64个成员,其值为创建表时规定的一系列值。Set成员各值之间用逗号,隔开。语法格式如下:
set(‘值1’,‘值2’,…‘值n’)
与enum类型相同,set的值在内部用整数表示,每一个值都有一个索引编号。当创建表时,set成员值得尾部空格将自动删除。但与enum类型不同的是,enum类型的字段只能从定义的列值中选择一个插入,而set类型的列可以从定义的列值中选择多个字符联合插入。
如果插入set字段的列值有重复,则MySQL自动删除重复值;插入set字段值顺序并不重要,MySQL会在存入数据库时,按照定义的顺序显示;如果插入了不正确的值,默认情况下,MySQL将忽视这些值,并给出警告。
二进制类型
MySQL支持两类字符型数据:文本字符串和二进制字符串。前面讲解了存储文本的字符串类型,今天将讲解MySQL中存储二进制数据的数据类型。
MySQL中的二进制数据类型有:bit、binary、varbinary、tinyblob、blob、mediumblob和longblob
.
(1) bit类型
bit是位字段类型。M表示值得位数,范围为1~64。如果M被省略,默认值为1。如果为bit(M)列分配的值长度小于M位,在值得左边用零填充。如bit(5)列分配一个值b’101’,其效果与分配b’00101’相同。Bit数据类型用来保存位字段值,如以二进制的形式保存数据13,13的二进制为1101,在这里需要位数至少4位的bit类型,既可以定义列类型为bit(4)。大于位数(M)的二进制数是不能插入bit(M)类型字段中的。
(2) binary和varbinary类型
binary和varbinary类型类似于char和varchar,不同的是他们包含二进制字节字符串。使用语法如下:
列名称
binary(M)或者varbinary(M)
Binary的长度是固定的,指定长度之后,不足最大长度的,将在右边填充“\0”补齐以达到指定长度(M)。
Varbinary类型的长度是可变的,其长度可以在0到最大值之间。如制定列数据类型为varbinary(20),如果插入的值得长度只有10,则实际存储空间为10加1,即其实际占用的空间为字符的实际长度加1。
可以看到b字段的数据长度为3,而vb字段的数据长度仅为插入的一个字符长度1。B字段和vb字段的长度截然不同,因为b字段不足的空间填充了"\0",而vb字段则没有填充。
(3) blob类型
blob是一个二进制的大对象,用来存储可变数量的数据。Blob类型分为四种:tinyblob、blob、mediumblob和longblob,他们可以容纳的最大长度不同。
Blob列存储的是二进制字符串(字节字符串);text列存储的是非二进制字符串(字符字符串)。Blob列没有字符集,并且排序和比较基于列值字节的数值,text列有一个字符集,并且根据字符集对值进行排序和比较。
二、 选择数据类型的方法
MySQL提供大量的数据类型,为了优化存储,提高数据库性能,在任何情况下都应使用最精确的类型。即选择占用存储空间最少的类型。
1. 整数和浮点数
有小数用浮点数,没有就用整数。但注意浮点数存储小数时会自动进行四舍五入
。浮点包括float和double。double比float要求的精度更高,所以当要求存储精度高时用double。
2. 浮点数和定点数
浮点数float和double相对于定点数decimal来说优点是:在长度一定的情况下浮点数表示的范围更大。但由于浮点数容易产生误差,因此,如果对精度要求比较高则用decimal来存储
。
Decimal在MySQL中是以字符串存储的,用于定义货币等对精度要求比较高的数据。注意定点数decimal标准定义格式只有decimal(M,D),其他的像float(M,D)是不标准的,在数据库迁移时会出问题。
3. 日期与时间类型
仅仅记录年份用year即可,记录时间time即可,都记录timestamp和datetime都可以,但如果存储较大日期用相对范围大的datetime类型。
但是如果插入记录同时需要记录插入记录时的时间,那么使用timestamp是最方便的
。
4. char与varchar
char存储固定长度字符,varchar存储可变长度字符。Char会自动删除插入数据的尾部空格,varchar不会删除尾部空格。
char采用固定长度,所以它的处理速度比varchar要快,但它的缺点就是浪费空间。所以随存储长度不大,但在检索速度上有要求的数据可以使用char类型,反之可以使用varchar来实现。
对于MyISAM存储引擎,最好使用固定长度的数据代替可变长度,这样可以使整个表静态化,从而使数据检索更快,用空间换时间。
对于InnoDB存储引擎,使用可变长度数据列。因为InnoDB数据表的存储区格式不分固定长度和可变长度,由于varchar按实际长度存储节省空间,所以对磁盘I/O和独具存储总量说比较好。
5. enum和set
enum只能取单值
,他的数据列是一个枚举集合。它的合法取值列表最多允许有65535个成员。因此,在需要从多个值中选取一个时,可以使用enum。例如性别字段适合定义为enum类型。
Set可以取多个值
。它的合法取值列表最多允许有64个成员。空字符串可是一个合法的set值。在需要取多个值时适合使用set类型。如一个人的兴趣爱好。
Enum和set的值是以字符串的形式出现的,但在内部,MySQL以数值的形式存储它们。
6. blob和text
blob是二进制字符串,text是非二进制字符串,两者均可存放大量的信息。Blob主要存储图片、音频信息等,而text只能存储纯文本文件。
三、 常见运算符介绍
运算符是告诉MySQL执行特殊算术或逻辑操作的符号。MySQL的内部运算符很丰富,主要有四大类:算数运算符
、比较运算符
、逻辑运算符
和位操作运算符
。
1.运算符概述
a) 算术运算符
算术运算符用于各种数值运算,包括加(+)、减(-)、乘(*)、除(/)、求余(或称模运算,%)
。
b) 比较运算符
比较运算符用于比较运算,包括大于(>)、小于(<)、等于(=)、大于等于(>=)、小于等于(<=)、不等于(!=)以及IN、BETWEEN AND、IS NULL、GREATEST、LEAST、LIKE、REGEXP
等。
c) 逻辑运算符
逻辑运算符的求值结果均为1(TRUE)、0(FALSE),这类运算有逻辑非(NOT或者!)、逻辑与(AND或者&&)、逻辑或(OR或者||)、逻辑异或(XOR)
。
d) 位操作运算符
参与运算的操作数,按二进制位进行运算。包括位与(&)、位或(|)、位非(~)、位异或(^)、左移(<<)、右移(>>)
6种。
2.算术运算符
3.比较运算符
4.逻辑运算符
在SQL中所有的逻辑运算符的求值结果均为TRUE,FALSE或NULL。在MySQL中体现为1(TRUE)、0(FALSE)和NULL。
5.位运算符
6.运算符的优先级
运算符的优先级决定了不同运算符在表达式中计算的先后顺序,下表给出了各类运算符的优先级,越靠前优先级越高,但一般我们采用圆括号””()”来改变优先级顺序
。