《数据库管理与应用》课件项目二MySQL数据类型

目录页任务实施任务准备熟悉数值类型熟悉日期时间类型熟悉字符串类型010203

MySQL数据类型简介01

数值数据类型02

日期和时间数据类型03

字符串数据类型04

MySQL数据类型的选择05如何选取数据类型04任务实施③更改文字内容④更改文字内容任务准备1.MySQL数据类型简介数据类型（data_type）是指系统中所允许的数据的类型。数据类型确定了数据的解释方式，对应着MySQL在内存或磁盘上开辟存储空间的大小和存储的规则，以及访问、显示、更新数据的方式。MySQL的数据类型可以分为五大类：数值数据类型、日期和时间类型、字符串类型、空间类型、Json类型等，前三类为基本数据类型，后两类为复合数据类型，其中字符串类型又可分为文本字符串和二进制字符串。任务实施③更改文字内容④更改文字内容任务准备1.MySQL数据类型简介通常情况下，由列内数据的内容确定该列的数据类型CREATETABLE`会员表`(`会员id`int(4)UNSIGNED

NOTNULLAUTO_INCREMENT,`会员编号`char(8),`会员昵称`varchar(20),`生日`date,`头像`blob,`会员简介`text,`首充金额`decimal(12,2),`创建时间`timestamp(3),PRIMARYKEY(`会员id`))任务实施③更改文字内容④更改文字内容任务准备2.数值数据类型MySQL支持所有标准SQL数值数据类型，包括：整数类型、浮点数类型和定点数类型。整数类型包括tinyint、smallint、mediumint、int、bigint，浮点数类型包括float和double，定点数类型为decimal。数据类型又可以分为严格数值数据类型（tinyint、smallint、mediumint、int、bigint和decimal）和近似数值数据类型（float、double）。MySQL数值常量可以分为整数、定点数和近似数值数据，整数用数字表示，定点数以带小数点的数字表示，近似数值数据以科学记数法表示。例如：1、3、5，3.0、.5、-6.78，1.2E3、1.2E-3、-1.2E0等。任务实施③更改文字内容④更改文字内容任务准备2.数值数据类型MySQL的整数类型包括tinyint、smallint、mediumint、int、bigint。int和integer是同义词。整数类型的定义形式为：类型[(M)][UNSIGNED|SIGNED][ZEROFILL]

int（4）；bigint（25）UNSIGNEDZEROFILL；int等都是定义整型的语法。1）整数类型任务实施③更改文字内容④更改文字内容任务准备2.数值数据类型在整数类型中，[(M)]指定显示宽度。显示宽度是指如果数值的位数小于M,则在数值在左侧用'0'或空格补齐，如果数值的位数大于等于M，则显示数值的本身。例如定义一个列为int（5）ZEROFILL，当列的值为3时，则显示值为00003，当列值为7483647时，则显示数值的本身即7483647。如果不指定（M），则系统为每一种类型指定默认的宽度值。[UNSIGNED|SIGNED]参数表示是否为无符号数，缺省值是有符号的,所以通常省略SIGNED。[ZEROFILL]指定显示宽度的补齐方式。表示如果位数不足，则用前导0补足，否则用空格补足。如果使用了ZEROFILL参数，则系统会自动给这一列加上UNSIGNED属性；ZEROFILL在表达式中和Union查询时不会生效。1）整数类型任务实施③更改文字内容④更改文字内容任务准备2.数值数据类型BIT数据类型也可以看成是特殊的整数。BIT类型的定义形式为BIT[(M)],M表示位数，可以从1到64，如果为1位则M可以省略。bit数据可以用二进制数如b’00101’字赋值，也可直接用其他数字如十进制5赋值。1）整数类型BOOL（BOOLEAN）数据类型是TINYINT(1)的同义词，0表示FALSE，非零表示TRUE。例如而IF(2,'true','false')返回的结果为真。逻辑常量TRUE和FALSE是1和0的别名，注意2=TRUE的结果为假。SERIAL类型表示自动增长类型，它是BIGINTUNSIGNEDNOTNULLAUTO_INCREMENTUNIQUE的别名。任务实施③更改文字内容④更改文字内容任务准备2.数值数据类型MySQL中小数分为浮点数和定点数。浮点类型又分两种，分别是单精度浮点数（FLOAT）和双精度浮点数（DOUBLE）；定点类型只有一种，就是DECIMAL。（1）定点类型定点类型定义形式为：DECIMAL[(M[,D])][UNSIGNED][ZEROFILL]M表示总长度（不包括负号和小数点），D表示小数部分的长度，M的最大值为65，D的最大值为30，且不大于M；M的默认值为10、D的默认值为0。DECIMAL的存储长度取决于（M,D），它是按二进制的形式存储的。DECIMAL是精确数值数据，适用于货币数据等需要精确数值的场景。2）小数类型DEC[(M[,D])][UNSIGNED][ZEROFILL],NUMERIC[(M[,D])][UNSIGNED][ZEROFILL]，FIXED[(M[,D])][UNSIGNED][ZEROFILL]都是DECIMAL的同义词。任务实施③更改文字内容④更改文字内容任务准备2.数值数据类型（2）浮点类型声明浮点类型定义的语法为：FLOAT[(M,D)][UNSIGNED][ZEROFILL]FLOAT(p)[UNSIGNED][ZEROFILL]DOUBLE[(M,D)][UNSIGNED][ZEROFILL]其中M称为精度，表示总共的位数；D称为标度，表示小数的位数。浮点数类型的取值范围为M（1～255）和D（1～30，且不能大于M-2），分别表示显示宽度和小数位数。2）小数类型FLOAT类型大小为4个字节，理论上取值范围为-3.402823466E+38~-1.175494351E-38，0，1.175494351E-38~3.402823466E+38，大致相当于7位数的DECIMAL。实际取值范围取决于系统和硬件。FLOAT(p)是为ODBC兼容性提供的语法。p表示以位为单位的精度，如果p是从0到24，则按FLOAT数据类型处理。如果p是从25到53，按DOUBLE数据类型处理。任务实施③更改文字内容④更改文字内容任务准备2.数值数据类型（2）浮点类型DOUBLE类型大小为8个字节，理论上取值范围为-1.7976931348623157E+308~-2.2250738585072014E-308,0,2.2250738585072014E-308~1.7976931348623157E+308，大致相当于15位数的DECIMAL。实际取值范围取决于系统和硬件。DOUBLEPRECISION[(M,D)][UNSIGNED][ZEROFILL]，REAL[(M,D)][UNSIGNED][ZEROFILL]是DOUBLE的同义词。REAL的类型受REAL_AS_FLOAT的值影响，如果REAL_AS_FLOAT=1，则REAL表示为FLOAT。2）小数类型任务实施③更改文字内容④更改文字内容任务准备2.数值数据类型（2）浮点类型FLOAT类型大小为4个字节，理论上取值范围为-3.402823466E+38~-1.175494351E-38，0，1.175494351E-38~3.402823466E+38，大致相当于7位数的DECIMAL。实际取值范围取决于系统和硬件。FLOAT(p)是为ODBC兼容性提供的语法。p表示以位为单位的精度，如果p是从0到24，则按FLOAT数据类型处理。如果p是从25到53，按DOUBLE数据类型处理。MySQL在存储值时执行四舍五入，例如一个列被定义为FLOAT(7,4),如果将999.00009插入这个列，则存储的结果为999.0001。浮点值受平台或实现依赖性的影响，浮点型数据比较大小可能会出现问题。进行浮点数比较的正确方法是首先确定数字之间差异的可接受容差，然后与容差值进行比较。例如，如果我们同意浮点数在万分之一(0.0001)的精度内相同时应视为相同。整数类型定义为UNSIGNED（无符号）时，域值范围会发生变化，但浮点数定义为UNSIGNED时，列值上限范围不会变更，但不允许插入负值。浮点型数据也可以为自动增长类型。2）小数类型任务实施③更改文字内容④更改文字内容任务准备3.日期和时间数据类型日期和时间数据类型用于表示时间值，有DATE，TIME，DATETIME，TIMESTAMP和YEAR等五种类型。任务实施③更改文字内容④更改文字内容任务准备4.字符串数据类型字符串数据类型有CHAR，VARCHAR，BINARY，VARBINARY，BLOB，TEXT，ENUM和SET字符串类型又可分为文本字符串（CHAR、VARCHAR和TEXT）和二进制字符串（BINARY、VARBINARY和BLOB类型），二进制字符串也称为字节字符串，文本字符串用来存储字符串数据，二进制字符串用来存储图片和声音等二进制数据。任务实施③更改文字内容④更改文字内容任务准备4.字符串数据类型在MySQL中，除了常见的字符之外，我们还会遇到一些特殊的字符，如换行符、回车符等。这些符号无法用字符来表示，因此需要使用某些特殊的字符来表示特殊的含义，这些字符就是转义字符。任务实施③更改文字内容④更改文字内容任务准备4.字符串数据类型ENUM类型ENUM是一个字符串对象，存储的值为表创建时列规定中枚举的一个值。其定义形式如下：<字段名>ENUM('值1','值2',…,'值n')例如，某会员表中的会员级别有“青铜会员”、“白银会员”、“铂金会员”等，则可建立ENUM类型的字段。字段定义为：

'会员级别'ENUM('青铜会员','白银会员','铂金会员')任务实施③更改文字内容④更改文字内容任务准备4.字符串数据类型SET类型SET是一个字符串的对象，可以有零或多个值，SET列最多可以有64个成员，值为表创建时规定的一列值。定义形式如下：SET('值1','值2',…,'值n')例如，会员标签字段有下列值：“1、善良2、低调3、挚友4、处事洒脱5、短发6、混头7、文字8、可爱9、犹豫不决10、正义正直11、睿智12、孤独13、伤感”等等。则字段的定义为：'会员标签'set('善良','低调','挚友','处事洒脱'...)SET是集合行为，可存储多个值，各值之间用逗号隔开，如（1,4,6）(1,2,5)等。任务实施③更改文字内容④更改文字内容任务准备5.MySQL数据类型的选择数据类型选取原则：

（1）每一列选用的数据类型要尽可能小和尽可能简单。因为这样可以用更少的磁盘、内容、CPU缓存，大大减少IO开销。

（2）数型值的要考虑域值范围，尽可能刚好满足。

（3）字符串类型是通用的数据类型，任何内容都可以保存在字符串中，数字和日期都可以表示成字符串形式。一般情况下，数值类型性能优于字符串类型，因此在数值类型性和字符串类型都可以的情况下优先选择数值类型。

（4）对于学号、电话号码、信用卡号和社会保险号最好使用字符串。这些数字实际上都不是用来计算的，使用字符可以避免丢失开头的'0'。

（5）MySQL提供日期和时间数据类型比较丰富，数据类型可以依据需要尽可能小和尽可能简单。要注意TIMESTAMP列的取值范围小于DATETIME的取值范围。

（6）如果某个字符串列的值是某种固定集合，那么可以考虑使用数据类型ENUM或SET。

（7）数据类型的选择还应该考虑数据操作和应用处理的要求，比如数型类型更适合于加快数据操作。任务实施④更改文字内容任务准备子任务1熟悉MySQL数值类型1.使用数字表示bool类型数据执行下列语句：1、selectif(0,'true','false');2、selectif(1,'true','false');3、selectif(2,'true','false');4、selectif(0=FALSE,'true','false');5、selectif(1=TRUE,'true','false'),if(2=TRUE,'true','false');观察输出结果。任务实施④更改文字内容任务准备子任务1熟悉MySQL数值类型2.定点和浮点数计算（1）计算表达式.1E0,.2E0,.3E0,(.1e0+.2E0)（2）计算表达式(.1+.2),(.1E0+.2E0),(.1+.2E0)（3）计算表达式(.1+.2)=.3,(.1E0+.2E0)=.3E0（4）计算表达式abs((.1E0+.2E0)-.3E0)<0.00001任务实施④更改文字内容任务准备2.定点和浮点数计算（5）设sakila库有以下存储过程p，它向DECIMAL类型变量d和float类型变量f累加1,000次0.0001。CREATEPROCEDUREp()BEGINDECLAREiINTDEFAULT0;DECLAREdDECIMAL(10,4)DEFAULT0;DECLAREfFLOATDEFAULT0;WHILEi<10000DOSETd=d+.0001;SETf=f+.0001E0;SETi=i+1;ENDWHILE;SELECTd,f;END;运行结果如下：mysql>usesakila;--切换到sakila数据库mysql>callp;--执行存储过程p+-----------+------------+|d|f|+----------+-------------+|1.0000|1.00005|+-----------+-----------+1rowinset(0.07sec)QueryOK,0rowsaffected(0.07sec)任务实施④更改文字内容任务准备子任务2熟悉MySQL日期时间类型1.使用日期时间类型字段设会员表中有关日期时间的字段定义如表3-10所示，在MySQl中创建该表并录入部分记录。任务实施④更改文字内容任务准备子任务2熟悉MySQL日期时间类型1.使用日期时间类型字段（1)使用命令行客户端登陆MySQL服务器。选择开始菜单“开始->程序->MySQL->MySQLServer5.7->MySQL5.7CommandLineClient”命令，输入密码登陆服务器。（2)执行语句：usesakila;打开数据库sakila。mysql>usesakila;Databasechanged（3）执行建表SQL语句，设表名为会员表_DateTime：mysql>CREATETABLE`sakila`.`会员表_DateTime`(->`id`int(0)UNSIGNEDNOTNULLAUTO_INCREMENT,->`会员生日`dateNULLDEFAULTNULL,->`游戏时长`time(0)NULLDEFAULT0,->`注册时间`datetime(0)NOTNULL,->`最后修改时间`timestampNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP,->PRIMARYKEY(`id`)->);执行过程如下：任务实施④更改文字内容任务准备子任务2熟悉MySQL日期时间类型1.使用日期时间类型字段（4）查看会员表_DateTime。mysql>showtableslike'会%';执行过程如下：（5）插入数据：mysql>INSERTINTO`会员表_datetime`VALUES(1,'1990-07-27','00:00:00','2021-07-2700:04:24','2021-07-2700:04:30');mysql>INSERTINTO`会员表_datetime`(会员生日,游戏时长,注册时间)VALUES('2007-09-12','10:00:00','2021-07-2700:04:24');（6）查看数据:mysql>select*from会员表_datetime;任务实施④更改文字内容任务准备子任务2熟悉MySQL日期时间类型2.日期时间类型计算（1）datetime,timestamp向date类型隐式转换时会根据秒后的小数位四舍五入。例如'1999-12-3123:59:59.499'结果是'1999-12-31',而'1999-12-3123:59:59.500'则是'2000-01-01'，但如果是显式转换则是直接舍弃时间部分。mysql>selectcast('1999-12-3123:59:59.499'asdate);（2）将日期时间类型转换为数字类型。这里展示了直接加0的结果，如果日期时间类型不包含小数，则将它直接转换为整数，否则转换为decimal类型，再进行加零运算。mysql>SELECTCURTIME(),CURTIME()+0,CURTIME(3)+0;任务实施④更改文字内容任务准备子任务3熟悉MySQL字符串类型1.CHAR、VARCHAR对比CHAR类型的字段在存储过程中会在尾部添加空格达到指定长度，但是在读取记录时丢掉尾部空格，不区分这些空格是手工输入的还是系统自动添加的；VARCHAR则会保留手工输入的空格。（1）准备实验表。

mysql>CREATETABLEnames(mynameCHAR(10));mysql>INSERTINTOnamesVALUES('Jones');（2）查看结果。mysql>SELECTmyname='Jones',myname='Jones'FROMnames;--'Jones'在比较时忽略掉尾部空格，成为'Jones'，所以myname='Jonesmysql>SELECTmynameLIKE'Jones',mynameLIKE'Jones'FROMnames;--LIKE不会忽略尾部空格，myname='Jones'，mynameLIKE'Jones'的结果为假。任务实施④更改文字内容任务准备子任务3熟悉MySQL字符串类型2.字符串比较时尾部空格处理CHAR、VARCHAR和TEXT类型在比较时（指"="、"!="）会忽略尾部空格，但LIKE不会忽略尾部空格，验证过程如下。（1)使用命令行客户端登陆MySQL服务器。选择开始菜单“开始->程序->MySQL->MySQLS