MySQL_18_数据类型和系统函数

数据类型

数据类型：整型，小数型（浮点型，定点型），字符型，日期型，二进制型

整型

整数型：表示正数时为无符号整数（负整数时为有符号整数）

类型名称	存储需求	说明
TINYINT	1个字节	很小的整数
SMALLINT	2个宇节	小的整数
MEDIUMINT	3个字节	中等大小的整数
INT	4个字节	普通大小的整数
BIGINT	8个字节	大整数

1）只希望表示零和正整数时，在其后用关键字“UNSIGNED”进行修饰

小数型

小数型：浮点型和定点型可用（M，D）表示

类型名称	存储需求	说明
FLOAT	4个字节	单精度浮点数
DOUBLE	8个字节	双精度浮点数
DECIMAL(M,D)	M+2个字节	定点数

1）M称为精度；D称为标度（表示小数的位数）

2）FLOAT/DOUBLE实现的是近似运算；

3）DECIMAL实现的精确运算；

//FLOAT/DOUBLE中M和D是可选的

小数型须知：

1）浮点型相较于定点型的具有更快的运算速度

2）MySQL内部使用DOUBLE作为浮点/定点的计算类型

3）MySQL将小数型数字打包存储至二进制字符串中（每4个字节存9个数字）

//小数点本身占1个字节

//建议只指定数据类型而不制定精度（MySQL会隐式转化）

字符型

字符型：字符串类型的数据需要用单引号或双引号括起来

CHAR/VARCHAR类型须知：

1）VARCHAR需额外占用1或2个字节记录字符长度

2）VARCHAR类型数据被更新时，可能导致分裂页或不同片段存储；

3）InnoDB存储引擎会将过长的VARCHAR类型存储为BLOB类型；

4）CHAR类型会舍弃字符串末尾的空格（VARCHAR类型会保留）；

ENUM和SET创建字符串对象格式：

1）字段名 ENUM(‘值1’, ‘值2’,…,‘值n’) //枚举列表，一次只能取一个值

2）字段名 SET(‘值1’, ‘值2’,…,‘值n’) //集合列表，一次能取多个值

//一般用在建表时对表的类型设置（如：设置gender字段只能选’男’或’女’）

//避免使用SET，字段的更新代价较高同时无法使用索引查找

日期型

日期型：日期类型的数据需要用单引号或双引号括起来

类型名称	日期格式	存储需求
YEAR	YYYY	1 个字节
TIME	HH:MM:SS	3 个字节
DATE	YYYY-MM-DD	3 个字节
DATETIME	YYYY-MM-DD HH:MM:SS	8 个字节
TIMESTAMP	YYYY-MM-DD HH:MM:SS	4 个字节

TIMETAMP类型：记录从1970/01/01至今的秒数

1）范围1970年~2038年

2）TIMESTAMP会根据时区变化，且具有特殊的自动更新功能；

二进制型

二进制型：以上类型的二进制存储方式

类型名称	存储需求	说明
BIT(n)	约 (n+7)/8 字节	位字段类型
BINARY(n)	n 字节	固定长度二进制字符串
VARBINARY (n)	L+1 字节 在此，L<216	可变长度二进制字符串
TINYBLOB (n)	L+1 字节 在此，L<28	非常小的BLOB 存放较短的二进制数
BLOB (n)	L+2 字节 在此，L<216	小 BLOB 存放图片、声音等文件
MEDIUMBLOB (n)	L+3 字节 在此，L<224	中等大小的BLOB 存放图片、声音、视频等文件
LONGBLOB (n)	L+4 字节 在此，L<232	非常大的BLOB 存放图片、声音、视频等文件

1）BIT类型的行为较为难理解，尽量使用CHAR（1）代替；

系统函数

字符串函数

字符串函数	包含
字符串基本信息函数	CHARSET（X） 返回X的字符集
	COLLATION（X） 返回X的字符序
	CONVERT（X USING CHARSET） 返回X的CHARSET字符集数据
	CHAR_LENGTH(X) 获取字符串长度
	LENGTH（X） 获取字符串占用的字节数
加密函数	PASSWORD（X） 对X进行加密，返回41位加密字符串
	MD5（X） 对X进行加密，返回32为加密字符串
	ENCODE（X，KEY）和 DECODE（PASSWORD，KEY）
	AES_ENCRYPT（X，KEY）和AES_DECRYPT(PASSWORD,KEY)
字符串连接函数	CONCAT（X1，X2，。。。，Xn） 将X1到Xn等字符串连接成一个新字符串
	CONCAT_WS（X1，X2，。。。，Xn） 用X1将X2到Xn等字符串连接成一个新字符串
修剪函数	LTRIM（X） 去掉字符串X开头的所有空格字符
	RTRIM（X） 去掉字符串X结尾的所有空格字符
	TRIM（X） 去掉字符串X开头和结尾的所有空格字符
	TRIM（[LEADING|BOTH|TRAILING]X1 FROM X2） 从X2字符串的前后以及后缀中去掉字符串X1
	LEFT（X，N） 返回字符串X的前N个字符
	RIGHT（X，N） 返回字符串X的后N个字符
	UPPER（X）和UCASE（X） 将字符串X中所有字母变成大写
	LOWER（X）和LCASE（X） 将字符串X中所有字母编程小写
	LPAD（X1，len，X2） 将字符X2加到X1的开头，使X1字符串长度达到len
	RPAD（X1，len，X2） 将字符X2加到X1的结尾，使x1字符串长度达到len
子字符串操作函数	SUBSTRING（X，N，LENGTH） 和MID（X，N，LENGTH） 从字符串X的第N个位置开始获取LENGTH长度的字符串
	LOCATE（X1，X2） POSITION（X1 IN X2）和INSTR（X2，X1） 从字符串X2中获取X1的开始位置
	INSERT（X1，N，len，X2） 从字符串X1的N位置开始，将长度为len的字符串替换为X2
	REPLACE（X1，X2，X3） 用字符串X3替换X1中所有出现的字符串X2，返回新的X1
字符串复制函数	REPEAT（X，N） 产生一个新字符串，内容为X的N次复制
	SPACE（N） 产生一个新字符串，内容为空格字符的N次复制
字符串比较函数	STRCMP（X1，X2） 比较字符串X1和X2 X1>X2，返回值为1 X1=X2，返回值为0 X1<X2，返回值为-1
字符串逆序函数	REVERSE（X） 返回一个新字符串（字符串X的逆序）

1）相同函数对同一个字符串操作

2）若字符集或字符型设置不同，导致操作结果可能不同

3）PASSWORD（X）和MD5为不可逆加密函数，后两者为加密—解密函数

4）FIND_IN_SET(X1,X2)也可以获取字符串X2中X1的开始位置，但不同的是该函数获取的字符串必须使用逗号进行分割才能查询出

数据类型转换函数	含义
CONVERT（X，TYPE）	以TYPE数据类型返回X数据，X本身不发生变化
CAST（X AS TYPE）	以TYPE数据类型返回X数据，X本身不发生变化
UNHEX（X）	将十六进制字符串X转换为十六进制的数值

条件控制函数	含义
IF（condition，v1，v2）	condition为条件表达式 当condition为TRUE时，返回v1的值 当condition为FALSE时，返回v2的值
IFNULL（v1，v2）	若v1的值为NULL，则返回v2的值 若v1的值不为NULL，则返回v1的值
CASE	与“when”连用

系统信息函数	含义
VERSION（）	获取当前MySQL的版本号 返回的值与@@VERSION静态变量的值相同
CONNECTION_ID（）	获取当前MySQL服务器连接的ID 返回值与@@PSEUDO_THREAD_ID系统变量值相同
DATABASE（）和 SCHEMA（）	获取当前操作的数据库
USER（）	获取通过那一台主机和账户名连接MySQL服务器
CURRENT_USER（）	获取该账户名允许通过哪些登录主机连接MySQL服务器

//SESSION_USER()和SYSTEM()函数是USER（）函数的别名

时间函数

日期：yyyy-mm-dd

时间：hh-mm-ss

//两者具有前导0的形式

获取日期或时间具体信息函数	含义
NOW（）	返回当前日期和时间
YEAR（X）	获取日期时间X的年信息
MONTH（X）	获取日期时间X的月信息
DAYOFMONTH（X）	获取日期时间X的日信息
HOUR（X）	获取日期时间X的时信息
MINUTEd（X）	获取日期时间X的分信息
SECOND（X）	获取日期时间X的秒信息
MICROSECOND（X）	获取日期时间X的微妙信息
EXTRACT（TYPE FROM X）	获取日期时间X的TYPE信息 TYPE可以为YEAR、MONTH、DAY、HOUR、MINUTE、SECOND、MICROSECOND
MONTHNAME（X）	获取日期时间X的月份名称
DAYNAME（X）	获取日期时间X的星期名称
WEEKDAY（X）	获取日期时间X在本星期是第几天 （星期一是“0”，星期天是“6”）
DAYOFWEEK（X）	获取日期时间X是本星期的第几天 （星期天作为第一天开始计算）
QUARTER（X）	获取日期时间X在本年是第几季度
WEEK（X）、 WEEKOFYEAR（X）	获取日期时间X在本年是第几个星期
DAYOFYEAR（X）	获取日期时间X在本年是第几天
TIME_TO_SEC（X）	获取时间X在当天的秒数
SEC_TO_TIME（X）	获取当天秒数X对应的时间
TO_DAYS（X）	计算日期X距离0000/1/1
FROM_DAYS（X）	计算从0000/1/1开始X天后的日期
DATEDIFF（X1，X2）	计算日期X1和X2之间的相隔天数
ADDDATE（D，N）	计算日期D加上N天后的日期
SUBDATE（D，N）	计算日期D减去N天后的日期

//YEAR(CURDATE())-YEAR(出生时间); //根据出生年龄计算年龄

系统日期和时间函数：

日期和时间函数	含义
CURDATE（） CURRENT_DATE（）	获取MySQL服务器当前日期
CURTIME（） CURRENT_TIME（）	获取MySQL服务器当前时间
NOW（[X]） CURRENT_TIMESTAMP（[X]） LOCALTIME（[X]） SYSDATE（[X]）	获取MySQL服务器当前日期/时间 可以选择一个1~6的参数，获取更精确的时间信息（秒后面的小数点位数，若没有参数，默认不显示）
UNIX_TIMESTAMP（）	MySQL服务器当前UNIX时间截
UNIX_TIMESTAMP （yyyy-mm-dd）	将日期时间yyyy-mm-dd 以UNIX时间截返回
FROM_UNIXTIME（TIMESTAMP）	UNIX时间截以日期时间格式返回

数值函数

数学函数	包含
三角函数	PI（） 计算圆周率
	RADIANS（X） 将角度X转换为弧度
	DEGREES（X） 将弧度X转换为角度
	SIN（X）、COS（X）、TAN（X）、COT（X） ASIN（X）、ACOS（X）、ATAN（X）
指数函数	SQRT（） 平方根函数
	POW（X，Y） 幂运算函数（X的Y次方）
	EXP（X） 计算E的X次方
	LOG Y（X） 计算X的自然对数
求近似值函数	ROUND（X） 计算离X最近的整数
	ROUND（X，Y） 计算离X最近的小数（小数点后保留Y位）
	TRUNCATE（X，Y） 返回小数点后保留Y位的X
	FROMAT（X，Y） 返回小数点后保留Y位的X
	CEIL（X） 返回大于等于X的最小整数
	FLOOR（X） 返回小于等于X的最大整数
随机函数	RAND（） 返回随机数
进制函数	BIN（X） 返回X的二进制
	OCT（X） 返回X的八进制
	HEX（X） 返回X的十六进制
	ASCII（X） 返回字符X的ASCII码
	CHAR（X1，…，Xn） 将X1到Xn的ASCII码转换为字符，并形成字符串
	CONV（X，CO1，CO2） 将CO1进制的X变为CO2进制的数

//TRUNCATE不进行四舍五入，而FROMAT进行四舍五入

其他函数	含义
LAST_INSERT_ID（）	返回当前MySQL会话最后一次调用INSERT或UPDATE语句后自增字段的最后一个值
INET_ATON（IP）	将IP地址（字符串数据）转换为整数
INET_NTOA（N）	将整数转换为IP地址（字符串数据）
UUID（）	生成一个128位的通用唯一识别码

1）LAST_INSERT_ID（）：仅能用于INSERT或UPDATE语句设置的自增字段值，且返回值应与系统会话变量@@LAST_INSERT_ID的值一致；若自增字段值是用户自己指定，而不是自动生成的，返回值为0；若一个INSERT语句插入多行记录，只返回第一条记录自增字段值

2）UUID（）由5个段构成：前3个段与服务器主机的时间有关（精确到微妙），第4段是一个随机数（MySQL服务期间随机数不变化，除非重启MySQL服务），第5段是通过网卡MAC地址转换得到