您现在的位置是:首页 > 数据库 > MySQLMySQL

数据类型和运算符

第十三双眼睛2019-06-05【MySQL】人已围观

简介数据库表由多列字段构成,每一个字段可以指定不同的数据类型,指定字段的数据类型之后,也就指定了向字段中插入数据的内容,例如,当用存储数值的时候,可以将他们存储为整数类型,也可以将他们存储成为字符串类型,不同的数据类型决定了他们存储时的方式,本章主要介绍MySQL中的数据类型和常见的运算符

MySQL数据类型介绍
MySQL支持的数据类型很多,主要包括数值型,日期时间型,字符串类型
1数值型:数值型数据主要包括TINYINT,SMALLTIN,MKEDIUMINT,INT,BIGINT,浮点型数据包括FLOAT,DOUBLE两种,定点小数类型DECIMAL.
2日期时间型:包括YEAR,TIME,DATE,DATETIME,TIMESTAMP.
3字符串类型:包括CHAR,VARCHAR,BINARY,VARBINARY,BLOB,TEXT,ENUM,SET.字符串类型又分为文本字符串和二进制字符串类型。

整数类型
数值型数据类型主要用来存储数字,MySQL提供了多种数值类型,不同的数据类型有不同的取值范围,可以存储的值越大,需要占用的内存空间也越大,MySQL提供的整数类型包括以下几种:TINYIINT,SMALLINT,MEDIUNINT,INT,BIGINT.以下是他们占用的内存字节数:
TINYINT:1个字节
SMALLINT:2个字节
MEDIUMINT:3个字节
INT:4个字节
BIGINT8:个字节
整数类型的字段上可以加自增加属性
在创建表的过程中,在整型字段后指定的数字,表示的是该数据的显示宽度,例如声明一个字段YEAR INT(4),表名此字段的显示宽度是4,此时插入一条数据,该字段是19999,是可以正常插入的,查询显示的时候,显示的19999,而不是只有4位数字。也就是说,只有数据本来的位数小于定义的位数时,才会起作用。如果定义时不指定显示宽度,则系统会位每一种数据类型指定默认的显示宽度,默认的显示宽度以能够显示该类型最大值位准

浮点数类型和定点数类型
MySQL中使用浮点数和定点数来表示小数,浮点类型有两种,单精度类型FLOAT和双精度类型DOUBLE,定点数类型只有一种,浮点类型和定点类型都可以用(M,N)来表示,其中M表示精度,代表总共的数据位数,N称为标度,代表小数的位数。
FLOAT:4个字节
DOUBLE:8个字节
MECIMAL(M,D):M+2个字节
DECIMAL是以串存放的,它的存储空间不是固定的,而是由精度决定,占用M+2个字节。
FLOAT和DOUBLE不指定精度时,默认会按照实际的精度,DECIMAL如果没有指定精度,默认为(10,0)。
浮点数相对于定点数的优点时在长度一样的情况下,浮点数能够表示更大的数据范围,它的缺点是会引起精度问题。

日期与时间类型
MySQL有多种表示日期的类型,主要有DATETIME,DATE,TIMESTAMP,TIME,YEAR,当只记录年的时候,就可以用YEAR,而没有比要使用DATE.

YEAR:占用1个字节来存储,可以使用不同的格式来指定YEAR值:
1:以4位字符串或者4位数字来表示YEAR,范围是1901-2155,超过范围的值无法插入,会报错。
2:以2位字符串格式表示YEAR,范围位00-99,00-69和70-99范围内的值会被分别转换为2000-2069和1970-1999内的值,插入超过取值范围的值将被转换为2000
3:以2位数字表示的YEAR,范围是1-99,1-69和70-99范围内的值分别被转换为2001-2069和1970-1999内的值,非法值会被转换为0000.而不是2000。
如:创建表CREATE TABLE tmp3(y YEAR);
然后向表中插入数据,INSERT INTO tmp3 values(2010),('2010')
再向表中插入数据,INSERT INTO tmp3 values(266);
那么,前一条语句是可以正常插入的,但是后面一条不行,会报错,因为最大允许的值是2155。

TIME
TIME类型适用于只需要表示时间的值,存储时需要3个字节,格式为HH:MM:SS,HH表示小时,MM表示分钟,SS表示秒。
如果为TIME分配简写值时要注意,如果没有冒号,MySQL解释值时,会把最右边的看作秒,比如,1122会看作00:11:22,而不是11:22:00,如果有冒号的话,最左边的会被看作时小时,比如11:22表示11:22:00.

DATE
DATE类型用在仅需要日期不需要时间时,存储时需要3个字节,格式为YYYY-MM-DD,YYYY表示年,MM表示月,DD表示天,在给日期类型的字段赋值时,可以使用字符串类型或者数字类型,只要符合日期的格式即可。
1使用‘YYYY-MM-DD’格式的字符串,取值的范围为‘1000-01-01’到‘9999-12-31’,如,输入2012-12-31,或者2012-12-31,插入数据库以后都是2012-12-31.
2以格式‘YY-MM-DD’或者YY-MM-DD格式的字符串或者数字来表示,其中YY的范围在0-69时表示2000-2069,当YY的范围时70-99时,表示1970-1999.
3以CURRENT_DATE或者NOW(),表示当前时间,CURRENT_DATE只返回当前时间的日期部分,没有时间部分,NOW()返回了当前时间的全部,在保存到数据库的时候,只保留了日期部分。
MySQL允许不严格的语法,任何标点符号都可以作为日期部分的分隔符,比如,98-11-31,98.11.31,98/11/31,98@11@31都可以正确的插入数据库中。

DATETIME
DATETIME类型用在需要同时包含日期和时间的时候,存储的时候需要8个字节,格式为‘YYYY-MM-DD HH:MM:SS',在给DATETIME类型的字段插入数据时,可以使用字符串或者数字类型。
1以‘YYYY-MM-DD HH:MM-SS’或者‘YYYYMMDDHHMMSS’表示时间时,范围为‘1000-01-01 00:00:00’到‘9999-12-31 23:59:59’
2以‘YY-MM-DD HH:MM:SS’或者‘YYMMDDHHMMSS’字符串表示。
3以   YYYYMMDDHHMMSS或者YYYYMMDDHHMMSS数字格式来保存。
4使用NOW()函数来插入数据库

TIMESTAMP
TIMESTAMP与DATETIME相同,显示宽度为19位,但是存储时需要4个字节,TIMESTAMP的取值范围小于DATETIME的取值范围,为'1970-01-01 00:00:01'到
‘2038-01-19 03:14:07’。
TIMESTAMP与DATETIME在存储字节不同,和表示范围不同,还有一个区别,DATETIME在存储数据时,按实际输入的格式进行存储,输入什么就存储什么,与时区无关,而TIMESTAMP的话,存储时先对当前时区进行转换,查询时再转换为当前时区,当前时区不同,显示的结果也不同。

文本字符串类型
字符串类型用来存储字符串类型,除了可以存储字符串数据之外,还可以存储其他数据,比如图片,声音等,MySQL支持两种字符串。文本字符串,和二进制字符串。
CHAR和VARCHAR类型
CAHR为固定长度字符串,再定义时指定字符串长度,当保存时,再右侧填充空格以达到指定长度,范围为0-255。
VARCHAR是长度可以改变的字符串,可以定义最大长度,范围为0-65535,VARCHAR的最大实际长度由最长的行的大小和使用的字符集确定,实际占用的空间为字符串长度加1,如,定义了一个VARCHAR(10),如果插入了5个字符,那么实际存储的字符串为5个字符加一个结束标志。

TEXT类型
TEXT类型用来保存非二进制字符串,如文章,评论。TEXT分为4种,TINYTEXT,TEXT,MEDIUMTEXT,LONGTEXT.不同类型的TEXT存储空间不同,数据长度也不同
TINYTEXT的最大长度为255
TEXT的最大长度为65535
MEDIUMTEXT的最大长度为2的24次幂
LONGTEXT的最大长度为2的32次幂

ENUM类型
ENUM是一个字符串对象,其值为表创建时在列规定中枚举的一列值,具体语法为:字段名 ENUM ('值1',‘值2’,...,'值n'),ENUM类型的字段在取值时,只能在枚举的列表中取值,而且一次只能取一个,ENUM值在内部用整数表示,每一个值都有一个索引值,枚举最多可以有65535个值。ENUM总有一个默认值,如果声明ENUM列可以为空,则默认值就是NULL,如果不允许为NULL,则值默认为允许值的第一个。

SET类型
SET是一个字符串对象,可以有零或者多个值,最多有64个值,在创建表时指定,用逗号隔开,具体语法如下:
SET('值1','值2','值3','值4'...'值5')
与ENUM一样,SET值再内部用整数表示,列表中每个值都有一个索引编号。与ENUM不同的是,ENUM再插入数据时,只能选择一个值插入,但是SET类型可以选择多个值插入。
如,创建表tmp
CREATE TABLE tmp(
         s SET('a','b','c','d')
)
INSERT INTO tmp values('a'),('a,b,a'),('c,a,d');
INSERT INTO tmp values('a';'x');
那么第一句会插入成功,但是重复的值会被去掉,第二句会插入失败,因为x再定义时候不存在。

二进制字符串类型
MySQL中支持的二进制数据类型有:BIT,BINARY,VARBINARY,TINYBLOB,BLOB,MEDIUMBLOB,LONGBLOB
BIT类型
BIT类型是位字段类型,M表示每个值得位数,范围是1-64,如果M被省略,则默认为1,如果为BIT位分配的值小于M位,则在值得左边用0填充,例如,BIT(6)存储一个‘101’,则会显示000101,最多是111111,大于这个值的数无法保存在数据库中。
创建表
CREATE TABLE tmp(
         b BIT(4)
)
INSERT INTO tmp values(2),(9),(15)
SELECT BIN(b+0) FROM tmp
查询显示:10,1001,1111

BINARY和VARBINARY
BINARY和VARBINARY类型类似于CHAR和VARCHAR,不同的是,他们存储二进制字符串,使用时,语法如下:
列名称  BINARY(M) 或者VARBINARY(M)
BINARY是长度固定的,指定长度之后,不足最大长度的,在右边补充'\0',以达到指定长度。
VARBINARY存储的长度是可变的,指定好长度之后,其真实存储的内容的长度在0与最大值之间,,但其所占用的实际长度位M+1

BLOB
BLOB是一个二级制大对象,用来存储可变数量的数据,BLOB分为4种,TINYBLOB,BLOB,MEDIUMBLOB,LONGBLOB,
BLOB存储的是二进制字符串,没有字符集,TEXT存储的是非二进制字符串,有一个字符集,并且根据字符集进行排序和比较。

如何选择数据类型
MySQL提供了很多种数据类型,为了优化存储,提高数据库性能,在任何情况下均应使用最精确的类型。
整数和浮点数
如果不需要小数部分,则使用整数来保存数据,如果需要小数部分,则使用浮点类型,对于浮点类型数据,存入的数值会对该列定义的小数位进行4舍5入,例如,要存储1-99999,则用MEDIUMINT UNSIGNED,是最好的类型,如果要存储小数,则选择float类型。
浮点数包括单精度和双精度两种,如果要求的精度比较高,则选择DOUBLE类型。

日期与时间类型
MySQL对于不同种类的日期和时间有很多的数据类型,比如YEAR和TIME,如果只需要记录年份,则使用YEAR类型,如果只需要记录时间,则是要TIME类型
如果要记录日期和时间,则可以使用TIMESTAMP和DATETIME类型,因为DATETIME类型的存储范围比TIMESTAMP大,所以,要存储时间范围比较大的值时,用DATETIME.

浮点数和定点数
浮点数FLOAT,DOUBLE和定点数DECIMAL的优势是,在长度一定的情况下浮点数能表示更大的数据范围,但是由于浮点数有误差,因此对存储精度要求比较高时,要用
DECIMAL,

CHAR与VARCHAR
CHAR是固定长度的字符串,会自动删除插入数据的尾部的空格,VARHAR是边长的,不会删除尾部的空格。
CHAR是固定长度的,处理速度比VARCHAR要快,但是缺点就是浪费空间,所以,在不计较空间的情况下,可以选择CHAR,反之可以使用VARHCAR。
对于MyISM来讲,最好使用固定长度的数据列代替可变长度的数据列,这样可以使整个表静态化,从而使整个表检索更快,用空间换时间。
对于InnoDB来讲,使用可变长度的数据列,因为InnoDB数据表的存储格式不分固定长度和可变长度,因此使用CHAR不一定比使用VARCHAR更好,但由于VARCHAR是按照实际的长度存储,比较节省空间,所以对磁盘I/O和数据存储总量比较好

ENUM和SET
ENUM只能取单值,它的数据列表是一个枚举集合,它的合法取值列表最多有65535个成员,因此,在需要从多个值种选择一个值时,用ENUM,比如性别字段,每次只能取一个值。
SET
SET可以取多个值,它的合法取值列表最多有64个,在需要选择多个值得时候,可以选择SET类型,如用户注册时,存储用户得爱好。ENUM和SET的值是以字符串的形式出现的,但是在内部,MySQL以数字的形式来存储他们。

BLOB和TEXT
BLOB是二进制字符串,TEXT是非二进制字符串,两者均可以存储大量的数据,BLOB可以存储图片,视频等信息,而TEXT只能存储文本文件。

运算符
运算符是告诉MySQL进行特定运算的符号,主要有4大类,算术运算符,比较运算符,逻辑运算符,位操作符。

算术运算符
算术运算符用于各类数值运算,宝括加,减,乘,除,余。

比较运算符
比较运算符用于比较,包括大于,小于,等于,大于等于,小于等于,不等于。以及IN,BETWEEN AND ,IS NULL,GREATEST,LEAST,LIKE,REGEXP.

逻辑运算符
逻辑运算符的求值结果位1(true)或者0(false),这类运算符有NOT,AND ,OR ,XOR.

位操作符
位操作运算符将参与的操作数进行二进制位运算,宝括,按位与,按位或,按位非,异或,左移,右移。

安全等于运算符
整个操作符和=操作符执行相同的比较操作,不过<=>可以用来判断NULL值,在这两个操作数均为NULL时,其返回值位1而不位NULL,当其中一个操作数为NULL时,返回值为0而不为NULL.

LEAST运算符
语法格式为LEAST(值1,值2,值3,。。。。值n),返回最小值,假如任意一个值为NULL,则LEAST的返回值为NULL.

GREATEST运算符
语法格式为GREATEST(值1,值2,值3,。。。。值n),返回最大值,,假如任意一个值为NULL,则GREATEST的返回值为NULL.

REGEXP
REGEXP用来匹配字符串,语法格式为expr REGEXP,如果匹配格式则返回1,如果不匹配则返回0,常用的通配符有如下几种:
^,匹配以该字符后面的字符开头的字符串。
$,匹配以该字符前面的字符结尾的字符串。
.匹配任何一个单字符
[......]匹配在方括号种的任意字符,为了命名字符的范围,用一个“-”如,[a-z]匹配任何小写字母,[0-9]匹配数字。
*匹配0个或者多个在它前面的字符,比如x*匹配任何数量的*,[0-9]匹配任何数量的数字。*匹配任何数量的任何字符。

MySQL种如何使用特殊字符
如单引号',双引号“,使用时不能直接输入,而要用转义字符"\"

MySQL可以存储文件吗
MySQL种的BLOB和TEXT都可以存储很大的数据,可以用他们来存储比较图片,视频,大文本,但是这些字段会降低数据库的性能,如果并非比要,可以选择存储文件的路径。

MySQL中如何执行区分大小写的字符串比较
在WINDOWS平台下,MySQL是不分大小写的,如果想进行大小写的比较,可以在字符串前面加BINARY关键字。如默认情况下,a='A'返回1,如果使用BINARY关键字,
BINARY ‘a’='A',结果为0.






 

Tags:MySQL

很赞哦! ()

上一篇:数据表的基本操作

下一篇:MySQL函数

文章评论

    共有条评论来说两句吧...

    用户名:

    验证码:

本站推荐

站点信息

  • 网站名称:JavaStudy
  • 建站时间:2019-1-14
  • 网站程序:帝国CMS7.5
  • 文章统计64篇文章
  • 标签管理标签云
  • 统计数据百度统计
  • 微信公众号:扫描二维码,关注我们