1. 数据类型
1.1 整数类型
整数类型一共有 5 种,包括 TINYINT、SMALLINT、MEDIUMINT、INT(INTEGER)和 BIGINT。
具体的取值范围如下:
整数类型 | 字节 | 有符号数取值范围 | 无符号数取值范围 |
---|---|---|---|
TINYINT | 1 | -128 ~127 | 0~255 |
SMALLINT | 2 | -32768~32767 | 0~65535 |
MEDIUMINT | 3 | -8388608~8388607 | 0~16777215 |
INT、INTEGER | 4 | -2147483648~2147483647 | 0~4294967295 |
BIGINT | 8 | -9223372036854775808~9223372036854775807 | 0~18446744073709551615 |
- TINYINT :一般用于枚举数据,比如系统设定取值范围很小且固定的场景。
- SMALLINT :可以用于较小范围的统计数据,比如统计工厂的固定资产库存数量等。
- MEDIUMINT :用于较大整数的计算,比如车站每日的客流量等。
- INT、INTEGER :取值范围足够大,一般情况下不用考虑超限问题,用得最多。比如商品编号。
- BIGINT :只有当你处理特别巨大的整数时才会用到。比如双十一的交易量、大型门户网站点击量、证券公司衍生产品持仓等。
1.2 日期与时间类型
类型 | 名称 | 字节 | 日期格式 | 最小值 | 最大值 |
---|---|---|---|---|---|
YEAR | 年 | 1 | YYYY或YY | 1901 | 2155 |
TIME | 时间 | 3 | HH:MM:SS | -838:59:59 | 838:59:59 |
DATE | 日期 | 3 | YYYY-MM-DD | 1000-01-01 | 9999-12-03 |
DATETIME | 日期时间 | 8 | YYYY-MM-DD HH:MM:SS | 1000-01-01 00:00:00 | 9999-12-31 23:59:59 |
TIMESTAMP | 日期时间 | 4 | YYYY-MM-DD HH:MM:SS | 1970-01-01 00:00:00 UTC | 2038-01-19 03:14:07UTC |
1.2.1 YEAR类型
上表中的最大最小值表示的是四位字符串 或者数字表示的YEAR,
以2位字符串格式表示YEAR类型,最小值为00,最大值为99。
- 当取值为01到69时,表示2001到2069;
- 当取值为70到99时,表示1970到1999;
- 当取值整数的0或00添加的话,那么是0000年;
- 当取值是日期/字符串的’0’添加的话,是2000年。
从MySQL5.5.27开始,2位格式的YEAR已经不推荐使用。
1.2.2 DATE类型
表示日期,格式为YYYY-MM-DD
向DATE类型的字段插入数据时:
- 以 YYYY-MM-DD 格式或者 YYYYMMDD 格式表示的字符串日期,其最小取值为1000-01-01,最大取值为9999-12-03。YYYYMMDD格式会被转化为YYYY-MM-DD格式。
- 以 YY-MM-DD 格式或者 YYMMDD 格式表示的字符串日期,此格式中,年份为两位数值或字符串满足YEAR类型的格式条件为:当年份取值为00到69时,会被转化为2000到2069;当年份取值为70到99时,会被转化为1970到1999。
- 使用 CURRENT_DATE() 或者 NOW() 函数,会插入当前系统的日期。
1.2.3 TIME类型
TIME类型用来表示时间, 可以使用“HH:MM:SS”格式来表示TIME类型
向TIME类型的字段插入数据时:
- 可以使用带有冒号的字符串,比如’ D HH:MM:SS’ 、’ HH:MM:SS ‘、’ HH:MM’、 'D HH:MM ‘、’ D HH ‘或’ SS '格式,都能被正确地插入TIME类型的字段中。其中D表示天,其最小值为0,最大值为34。如果使用带有D格式的字符串插入TIME类型的字段时,D会被转化为小时,计算格式为D*24+HH。
- 当使用带有冒号并且不带D的字符串表示时间时,表示当天的时间,比如12:10表示12:10:00
- 可以使用不带有冒号的字符串或者数字,格式为’ HHMMSS '或者 HHMMSS 。比如1210,MySQL会将最右边的两位解析成秒,表示00:12:10
- 如果插入一个不合法的字符串或者数字,MySQL在存储数据时,会将其自动转化为00:00:00进行存储。
- 使用 CURRENT_TIME() 或者 NOW() ,会插入当前系统的时间。
1.2.4 DATETIME类型
- 以 YYYY-MM-DD HH:MM:SS 格式或者 YYYYMMDDHHMMSS 格式的字符串插入DATETIME类型的字段时,最小值为1000-01-01 00:00:00,最大值为9999-12-03 23:59:59。
- 以 YY-MM-DD HH:MM:SS 格式或者 YYMMDDHHMMSS 格式的字符串插入DATETIME类型的字段时,两位数的年份规则符合YEAR类型的规则,00到69表示2000到2069;70到99表示1970到1999。
- 使用函数 CURRENT_TIMESTAMP() 和 NOW() ,可以向DATETIME类型的字段插入系统的当前日期和时间。
1.2.5 TIMESTAMP 类型
TIMESTAMP类型也可以表示日期时间,显示格式为: YYYY-MM-DD HH:MM:SS ,需要4个字节的存储空间。但是TIMESTAMP只能存储“1970-01-01 00:00:01 UTC”到“2038-01-19 03:14:07 UTC”之间的时间。其中,UTC表示世界统一时间,也叫
作世界标准时间。
存储数据的时候需要对当前时间所在的时区进行转换,查询数据的时候再将时间转换回当前的时区。因此,使用TIMESTAMP存储的同一个时间值,在不同的时区查询时会显示不同的时间。
DATETIME 类型保存了完整的日期和时间,且取值范围较大,因此建议在开发时使用DATETIME类型,但是一般注册时间等建议使用TIMESTAMP时间戳类型,便于计算
1.3 文本类型
MySQL中,文本字符串总体上分为 CHAR 、 VARCHAR 、 TINYTEXT 、 TEXT 、 MEDIUMTEXT 、 LONGTEXT 、 ENUM 、 SET 等类型。
1.3.1 CHAR类型
- CHAR(M) 类型一般需要预先定义字符串长度。如果不指定(M),则表示长度默认是1个字符。
- 如果保存时,数据的实际长度比CHAR类型声明的长度小,则会在 右侧填充 空格以达到指定的长度。当MySQL检索CHAR类型的数据时,CHAR类型的字段会去除尾部的空格。
- 定义CHAR类型字段时,声明的字段长度即为CHAR类型字段所占的存储空间的字节数。
1.3.2 VARCHAR类型
- VARCHAR(M) 定义时, 必须指定 长度M,否则报错。
- MySQL4.0版本以下,varchar(20):指的是20字节,如果存放UTF8汉字时,只能存6个(每个汉字3字节) ;MySQL5.0版本以上,varchar(20):指的是20字符。
- 检索VARCHAR类型的字段数据时,会保留数据尾部的空格。VARCHAR类型的字段所占用的存储空间为字符串实际长度加1个字节。
char 与 varchar的选择
- 存储很短的信息建议使用char,因为varchar还需要一个字节存储字符串的实际长度
- 固定长度的,建议使用char类型
- 对于列频繁改变的场景,建议使用char,因为使用varchar每次存储需要额外的长度计算
- 具体存储引擎相关:
· MyISAM 数据存储引擎和数据列:MyISAM数据表,最好使用固定长度(CHAR)的数据列代替可变长度(VARCHAR)的数据列。这样使得整个表静态化,从而使 数据检索更快 ,用空间换时间。
· MEMORY 存储引擎和数据列:MEMORY数据表目前都使用固定长度的数据行存储,因此无论使用CHAR或VARCHAR列都没有关系,两者都是作为CHAR类型处理的。
· InnoDB 存储引擎,建议使用VARCHAR类型。因为对于InnoDB数据表,内部的行存储格式并没有区分固定长度和可变长度列(所有数据行都使用指向数据列值的头指针),而且主要影响性能的因素是数据行使用的存储总量,由于char平均占用的空间多于varchar,所以除了简短并且固定长度的,其他考虑varchar。这样节省空间,对磁盘I/O和数据存储总量比较好。
1.4 TEXT类型
TEXT用来保存文本类型的字符串,总共包含4种类型,分别为TINYTEXT、TEXT、
MEDIUMTEXT 和 LONGTEXT 类型。
在向TEXT类型的字段保存和查询数据时,系统自动按照实际长度存储,不需要预先定义长度
1.5 二进制字符串类型
MySQL中的二进制字符串类型主要存储一些二进制数据,比如可以存储图片、音频和视频等二进制数据。
MySQL中支持的二进制字符串类型主要包括BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB类型。
1.6 JSON 类型
JSON是一种轻量级的数据交换格式,JSON 可以将 JavaScript 对象中表示的一组数据转换为字符串,然后就可以在网络或者程序之间轻松地传递这个字符串,并在需要的时候将它还原为各编程语言所支持的数据格式。
通过“->”和“->>”符号,从JSON字段中正确查询出了指定的JSON数据的值。
阿里巴巴《Java开发手册》之MySQL数据库:
- 任何字段如果为非负数,必须是 UNSIGNED
- 【 强制 】小数类型为DECIMAL,禁止使用 FLOAT 和 DOUBLE。
说明:在存储的时候,FLOAT 和 DOUBLE都存在精度损失的问题,很可能在比较值的时候,得到不正确的结果。如果存储的数据范围超过 DECIMAL 的范围,建议将数据拆成整数和小数并分开存储。- 【 强制 】如果存储的字符串长度几乎相等,使用 CHAR 定长字符串类型。
- 【 强制 】VARCHAR是可变长字符串,不预先分配存储空间,长度不要超过 5000。如果存储长度大 于此值,定义字段类型为TEXT,独立出来一张表,用主键来对应,避免影响其它字段索引效率
2. 运算符
2.1 算术运算符
- 算数运算符包括:加、减、乘、除、取余
· 整数类型的值与浮点数进行加减操作,结果是一个浮点数。
· mysql中+表示数值相加,如果遇到非数值类型,会先尝试转成数值, 如果转化失败 就按0计算
· 一个数除以一个整数,无论是否除尽,结果都为一个浮点数。
. 一个数除以另一个数,除不尽时结果为浮点数,且保留小数点后四位。
. Mysql中一个数除以0 结果为 Null
2.2 比较运算符
比较运算符用来对表达式左边的操作数和右边的操作数进行比较,比较的结果为真则返回1,比较的结果为假则返回0,其他情况则返回NULL。
-
等号运算符
. 如果等号两边的值都为字符串(字符串或字符串表达式)则mysql会比较字符串中的字符的ANSI编码是否相等
. 如果等号两边的值一个是整数,另一个是字符串,则mysql会将字符串转化为数字进行比较
. 如果等号两边有一个null,则比较结果为Null.
<=> 安全等于运算符,非null值 <=>null 结果为0,null <=>null 结果为1 -
不等于运算符 != <>
. 如果两边相等,则返回0,不相等则返回1,但是不能用来判断null值,两边如果存在一个null值,则结果为null。 -
非符号类型的比较运算符
运算符 | 作用 | 描述 |
---|---|---|
is null 或isnull() | 判空 | 如果为null 返回1,否则返回0 |
is not null | 判非空 | 如果不为null 返回 1,否则返回0 |
least | 多个值中返回最小值 | 当参数是整数或浮点数时,返回最小值;当参数为字符串时,返回字母表中顺序靠前的字符;当存在null值时,直接返回null |
greatest | 多个值中返回最大值 | 与least 类似,返回最大值,当存在null 时直接返回null |
between and | 两值之间的运算符 | 返回时包含左右边界值 |
in | 判断一个值是否为列中的任意一个值 | 如果给定值为 null 则返回 null |
not in | 判断一个值是否不是一个列表中的任意一个值 | |
like | 模糊匹配运算符 | 用来匹配字符串,如果给定值为null,则返回null; 通配符说明: ‘%’匹配0个或多个字符、 ‘-‘匹配一个字符 |
escape | 回避特殊转义符 | 如果使用 ''作为转义符,则不适用escape,如果使用其他符号做转义,则需要使用 |
regexp | 判断一个值是否符合正则表达式的规则 | ‘^’匹配以该字符后面的字符开头的字符串; '$'匹配以该字符前面的字符结尾的字符串; ‘-’匹配任何一个单字字符 ; ‘ [ …]'匹配在方括号内的任何字符’也可以使用 - 匹配一定范围的数据; ‘ * ’匹配 0个或多个在它前面的字符; ‘+’匹配该符号前面的字符一次或多次; [^]匹配不在括号中的任何字符; 字符串{n,m} 匹配字符串最少n 次,最多 m次 |
rlike | 判断一个值是否符合正则表达式的规则 |
regexp 和 like 都可以 用来匹配字符串,like 如果要匹配的字符在字符串中间 则不返回,而repexp 在这种情况下时返回的。
2.3 逻辑运算符
逻辑运算符 包含 not(!) 、and(&&) 、or(||) 、xor
and 优先级高于 or 如果 and or 出现时,会先对 and 两边进行操作
2.4 位运算符
位运算符和会先将操作数转为二进制数,然后再进行位运算,运算完成后的结果再从2进制转为10进制
运算符 | 作用 |
---|---|
& | 按位与 |
| | 按位或 |
^ | 按位异或 |
~ | 按位取反 |
>> | 按位右移 |
<< | 按位左移 |
3. 函数
3.1 字符串函数
函数名 | 描述 |
---|---|
ASCII(s) | 返回字符串S中的第一个字符的ASCII码值 |
CHAR_LENGTH(s) | 返回字符串s的字符数。作用与CHARACTER_LENGTH(s)相同 |
LENGTH(s) | 返回字符串s的字节数,和字符集有关 |
CONCAT(s1,s2,…,sn) | 连接s1,s2,…,sn为一个字符串 |
INSERT(str, idx, len,replacestr) | 将字符串str从第idx位置开始,len个字符长的子串替换为字符串replacestr 字符串索引是从1开始的 |
REPLACE(str, a, b) | 用字符串b替换字符串str中出现a的字符串 |
UPPER(s) 或 UCASE(s) | 将字符串s的所有字母转成大写字母 |
LOWER(s) 或LCASE(s) | 将字符串s的所有字母转成小写字母 |
LEFT(str,n) | 返回字符串str最左边的n个字符 |
RIGHT(str,n) | 返回字符串str最右边的n个字符 |
LPAD(str, len, pad) | 用字符串pad对str最左边进行填充,直到str的长度为len个字符 |
RPAD(str ,len, pad) | 用字符串pad对str最右边进行填充,直到str的长度为len个字符 |
LTRIM(s) | 去掉字符串s左侧的空格 |
RTRIM(s) | 去掉字符串s右侧的空格 |
TRIM(s) | 去掉字符串s开始与结尾的空格 |
TRIM(s1 FROM s) | 去掉字符串s开始与结尾的s1 |
TRIM(LEADING s1 FROM s) | 去掉字符串s开始处的s1 |
TRIM(TRAILING s1 FROM s) | 去掉字符串s结尾处的s1 REPEAT(str, n) 返回str重复n次的结果 |
SPACE(n) | 返回n个空格 |
STRCMP(s1,s2) | 比较字符串s1,s2的ASCII码值的大小 |
SUBSTR(s,index,len) | 返回从字符串s的index位置其len个字符,作用与SUBSTRING(s,n,len)、 MID(s,n,len)相同 |
LOCATE(substr,str) | 返回字符串substr在字符串str中首次出现的位置,作用于POSITION(substr IN str)、INSTR(str,substr)相同。未找到,返回0 |
ELT(m,s1,s2,…,sn) | 返回指定位置的字符串,如果m=1,则返回s1,如果m=2,则返回s2,如果m=n,则返回sn |
FIELD(s,s1,s2,…,sn) | 返回字符串s在字符串列表中第一次出现的位置 |
FIND_IN_SET(s1,s2) | 返回字符串s1在字符串s2中出现的位置。其中,字符串s2是一个以逗号分隔的字符串 |
REVERSE(s) | 返回s反转后的字符串 |
NULLIF(value1,value2) | 比较两个字符串,如果value1与value2相等,则返回NULL,否则返回value1 |
3.2 日期和时间
3.2.1 获取日期或时间
函数 | 用法 |
---|---|
CURDATE(),CURRENT_DATE() | 返回当前日期,只包含年月日 |
CURTIME() , CURRENT_TIME() | 返回当前时间,包含时分秒 |
NOW() / SYSDATE() / CURRENT_TIMESTAMP() / LOCALTIME() / LOCALTIMESTAMP() | 返回当前系统的日期和时间 |
UTC_DATE()/ UTC_TIME() | 返回UTC(世界标准时间)日期/时间 |
3.2.2 日期和时间戳的转换
函数 | 用法 |
---|---|
UNIX_TIMESTAMP() | 以UNIX时间戳的形式返回当前时间。 |
UNIX_TIMESTAMP(date) | 将时间date以UNIX时间戳的形式返回。 |
FROM_UNIXTIME(timestamp) | 将UNIX时间戳的时间转换为普通格式的时间 |
3.2.3 获取月份、星期、星期数、天数等函数
函数 | 用法 |
---|---|
YEAR(date) / MONTH(date) / DAY(date) | 返回具体的日期值 |
HOUR(time) / MINUTE(time) / SECOND(time) | 返回具体的时间值 |
MONTHNAME(date) | 返回月份:January,… |
DAYNAME(date) | 返回星期几:MONDAY,TUESDAY…SUNDAY |
WEEKDAY(date) | 返回周几,注意,周1是0,周2是1,。。。周日是6 |
QUARTER(date) | 返回日期对应的季度,范围为1~4 WEEK(date) |
WEEKOFYEAR(date) | 返回一年中的第几周 |
DAYOFYEAR(date) | 返回日期是一年中的第几天 |
DAYOFMONTH(date) | 返回日期位于所在月份的第几天 |
DAYOFWEEK(date) | 返回周几,注意:周日是1,周一是2,。。。周六是7 |
EXTRACT(type FROM date) | 返回指定日期中特定的部分,type指定返回的值 |
3.2.4 时间和秒钟转换的函数
函数 | 用法 |
---|---|
SEC_TO_TIME(seconds) | 将 seconds 描述转化为包含小时、分钟和秒的时间 |
TIME_TO_SEC(time) | 将 time 转化为秒并返回结果值;转化的公式为:小时*3600+分钟 *60+秒 |
3.2.5 计算日期和时间的函数
函数 | 用法 |
---|---|
DATE_ADD(datetime, INTERVAL expr type), ADDDATE(date,INTERVAL expr type) | 返回与给定日期时间相差INTERVAL时 间段的日期时间 |
DATE_SUB(date,INTERVAL expr type), SUBDATE(date,INTERVAL expr type) | 返回与date相差INTERVAL时间间隔的日期 |
举例如下:
ADDDATE('2021-10-21 23:32:12',INTERVAL 1 SECOND) AS col3,
DATE_ADD('2021-10-21 23:32:12',INTERVAL '1_1' MINUTE_SECOND) AS col4,
DATE_ADD(NOW(), INTERVAL -1 YEAR) AS col5
函数 | 用法 |
---|---|
ADDTIME(time1,time2) | 返回time1加上time2的时间。当time2为一个数字时,代表的是秒 ,可以为负数 |
SUBTIME(time1,time2) | 返回time1减去time2后的时间。当time2为一个数字时,代表的是秒 ,可以为负数 |
DATEDIFF(date1,date2) | 返回date1 - date2的日期间隔天数 |
TIMEDIFF(time1, time2) | 返回time1 - time2的时间间隔 |
FROM_DAYS(N) | 返回从0000年1月1日起,N天以后的日期 |
TO_DAYS(date) | 返回日期date距离0000年1月1日的天数 |
LAST_DAY(date) | 返回date所在月份的最后一天的日期 |
MAKEDATE(year,n) | 针对给定年份与所在年份中的天数返回一个日期 |
MAKETIME(hour,minute,second) | 将给定的小时、分钟和秒组合成时间并返回 |
PERIOD_ADD(time,n) | 返回time加上n后的时间 |
如 : 查询7天内的新增用户
SELECT COUNT(*) as num FROM new_user WHERE TO_DAYS(NOW())-TO_DAYS(regist_time)<=7
3.2.6 日期的格式化与解析
函数 | 用法 |
---|---|
DATE_FORMAT(date,fmt) | 按照字符串fmt格式化日期date值 |
TIME_FORMAT(time,fmt) | 按照字符串fmt格式化时间time值 |
GET_FORMAT(date_type,format_type) | 返回日期字符串的显示格式 |
STR_TO_DATE(str, fmt) | 按照字符串fmt对str进行解析,解析为一个日期 |
如:
SELECT STR_TO_DATE('20140422154706','%Y%m%d%H%i%s') FROM DUAL;
# 输出为: 2014-04-22 15:47:06
3.3 流程处理函数
MySQL中的流程处理函数主要包括IF()、IFNULL()和CASE()函数。
函数 | 用法 |
---|---|
IF(value,value1,value2) | 如果value的值为TRUE,返回value1,否则返回value2 |
IFNULL(value1, value2) | 如果value1不为NULL,返回value1,否则返回value2 |
CASE WHEN 条件1 THEN 结果1 WHEN 条件2 THEN 结果2 … [ELSE resultn] END | 相当于Java的if…else if…else… |
CASE expr WHEN 常量值1 THEN 值1 WHEN 常量值1 THEN 值1 … [ELSE 值n] END | 相当于Java的switch…case… |
简单举例:
SELECT employee_id,salary,
CASE WHEN salary>=15000 THEN 'A'
WHEN salary>=10000 THEN 'B'
WHEN salary>=8000 THEN 'C'
ELSE 'D'
END "描述"
FROM employees;
3.4 加密与解密函数
主要用于对数据库中的数据进行加密和解密处理,以防止数据被他人窃取
函数 | 用法 |
---|---|
PASSWORD(str) | 返回字符串str的加密版本,41位长的字符串。加密结果不可逆 常用于用户的密码加密 |
MD5(str) | 返回字符串str的md5加密后的值,也是一种加密方式。若参数为NULL,则会返回NULL |
SHA(str) | 从原明文密码str计算并返回加密后的密码字符串,当参数为NULL时,返回NULL。 SHA加密算法比MD5更加安全 。 |
ENCODE(value,password_seed) | 返回使用password_seed作为加密密码加密value |
DECODE(value,password_seed) | 返回使用password_seed作为加密密码解密value |
3.5 Mysql信息函数
函数 | 用法 |
---|---|
VERSION() | 返回当前MySQL的版本号 |
CONNECTION_ID() | 返回当前MySQL服务器的连接数 |
DATABASE(),SCHEMA() | 返回MySQL命令行当前所在的数据库 |
USER(),CURRENT_USER()、SYSTEM_USER(), SESSION_USER() | 返回当前连接MySQL的用户名,返回结果格式为 “主机名@用户名” |
COLLATION(value) | 返回字符串value的比较规则 |
BENCHMARK(n,expr) | 将表达式expr重复执行n次。用于测试MySQL处理expr表达式所耗费的时间 |