学习随笔(二):《MySQL深入浅出》-数据类型和运算符
Ⅰ 、数值类型:
MySQL支持所有标准SQL中的数值类型,其中包括严格数值类型(INTEGER、SMALLINT、DECIMAL和NUMERIC),以及近似数字类型(FLOAT、REAL、DOUBLE、PRECISION),拓展增加了TINYIN、MEDIUMINT和BIGINT这3种长度不同的整型,并增加了BIT类型,存放位数据。
整数类型 | 字节 | 最小值 | 最大值 |
TINYINT | 1 | 有符号-128 无符号 0 |
有符号 127 无符号 255 |
SMALLINT | 2 | 有符号-32768 无符号 0 |
有符号 32767 无符号 65535 |
MEDIUMINT | 3 | 有符号- 8388608 无符号 0 |
有符号 8388607 无符号 1677215 |
INT、INTEGER | 4 | 有符号- 2147483648 无符号 0 |
有符号 2147483647 无符号 4294967295 |
BIGINT | 8 | 有符号-9223372036854775808 无符号 0 |
有符号 9223372036854775807 无符号 18446744073709551615 |
浮点数类型 | 字节 | 最小值 | 最大值 |
FLOAT | 4 | ±1.175494351E-38 | ±3.402823466E+38 |
DOUBLE | 8 | ±2.2250738585072014E-308 | ±1.7976931348623157E+308 |
定点数类型 | 字节 | 描述 | |
DEC(M,D), DECIMAL(M,D) |
M+2 | 最大取值范围与 DOUBLE 相同,给定 DECIMAL 的有效取值范围由 M 和 D决定 | |
位类型 | 字节 | 最小值 | 最大值 |
BIT(M) | 1~8 | BIT(1) | BIT(64) |
tips:
1、所有的整数类型都有一个可选属性 UNSIGNED(无符号),如果需要在字段里面保存非负数或者需要较大的上限值时,可以用此选项,它的取值范围是正常值的下限取 0,上限取原值的 2 倍
2、整数类型还有一个属性:AUTO_INCREMENT。在需要产生唯一标识符或顺序值时,可利用此属性,这个属性只用于整数类型。
3、对于小数的表示,MySQL 分为两种方式:浮点数和定点数。类型名称后加“(M,D)”的方式来进行表示,表示该值一共显示 M 位数字(整数位+小数位),其中 D 位位于小数点后面。
浮点数包括 float(单精度)和 double(双精度),而定点数则只有 decimal 一种表示。定点数在 MySQL 内部以字符串形式存放,比浮点数更精确,适合用来表示货币等精度高的数据。
4、对于 BIT(位)类型,用于存放位字段值,BIT(M)可以用来存放多位二进制数,M 范围从 1~64,如果不写则默认为 1 位。
对于位字段,直接使用 SELECT 命令将不会看到结果,可以用bin()(显示为二进制格式)或者 hex()(显示为十六进制格式)函数进行读取。
Ⅱ、日期时间类型
MySQL表示日期和时间的数据类型很多,区别如下:
· 如果要用来表示年月日,通常用 DATE 来表示。
· 如果要用来表示年月日时分秒,通常用 DATETIME 表示。
· 如果只用来表示时分秒,通常用 TIME 来表示。
· 如果需要经常插入或者更新日期为当前系统时间,则通常使用 TIMESTAMP 来表示。
· 如果只是表示年份,可以用 YEAR 来表示,它比 DATE 占用更少的空间。YEAR 有 2 位或4 位格式的年。默认是 4 位格式。
日期和时间类型 | 字节 | 最小值 | 最大值 |
DATE | 4 | 1000-01-01 | 9999-12-31 |
DATETIME | 8 | 1000-01-01 00:00:00 | 9999-12-31 23:59:59 |
TIMESTAMP | 4 | 19700101080001 | 2038 年的某个时刻 |
TIME | 3 | -838:59:59 | 838:59:59 |
YEAR | 1 | 1901 | 2155 |
数据类型 | 零值表示 |
DATETIME | 0000-00-00 00:00:00 |
DATE | 0000-00-00 |
TIMESTAMP | 00000000000000 |
TIME | 00:00:00 |
YEAR | 0000 |
tips:
1、DATETIME是DATE和TIME的组合,用户可以根据不同的需要,来选择不同的日期或时间类型以满足不同的应用。
2、TIMESTAMP支持的时间范围较小,其取值范围从19700101080001到2038年的某个时间,而DATETIME是从1000-01-01 00:00:00到9999-12-31 23:59:59,范围更大。
3、表中的第一个TIMESTAMP列自动设置为系统时间。如果在一个TIMESTAMP列中插入NULL,则该列值将自动设置为当前的日期和时间。超过范围会溢出为“0000-00-00 00:00:00”来填补。
4、TIMESTAMP的插入和查询都受当地时区的影响,更能反应出实际的日期。而DATETIME则只能反应出插入时当地的时区,其他时区的人查看数据必然会有误差。
5、正确地插入到对应的日期字段中。以DATETIME为例
YYYY-MM-DD HH:MM:SS 或 YY-MM-DD HH:MM:SS 格式的字符串;YYYYMMDDHHMMSS 或 YYMMDDHHMMSS 格式的没有间割符的字符串或者数字;函数返回的结果,如NOW()或CURRENT_DATE。
Ⅲ、字符串类型
MySQL 包括了 CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM 和 SET 等多种字符串类型。
字符串类型 | 字节 | 描述及存储需求 |
CHAR(M) | M | M 为 0~255 之间的整数 |
VARCHAR(M) | M 为 0~65535 之间的整数,值的长度+1 个字节 | |
TINYBLOB | 允许长度 0~255 字节,值的长度+1 个字节 | |
BLOB | 允许长度 0~65535 字节,值的长度+2 个字节 | |
MEDIUMBLOB | 允许长度 0 ~167772150 字节,值的长度+3 个字节 | |
LONGBLOB | 允许长度 0~4294967295 字节,值的长度+4 个字节 | |
TINYTEXT | 允许长度 0~255 字节,值的长度+2 个字节 | |
TEXT | 允许长度 0~65535 字节,值的长度+2 个字节 | |
MEDIUMTEXT | 允许长度 0 ~167772150 字节,值的长度+3 个字节 | |
LONGTEXT | 允许长度 0~4294967295 字节,值的长度+4 个字节 | |
VARBINARY(M) | 允许长度 0~M 个字节的变长字节字符串,值的长度+1 个字节 | |
BINARY(M) | M | 允许长度 0~M 个字节的定长字节字符串 |
* CHAR和VARCHAR 区别:
存储方式,CHAR 列的长度固定为创建表时声明的长度,长度为 0~255 的任何值;而 VARCHAR 列中的值为可变长字符串,长度可以指定为 0~255 (5.0.3以前)或者 65535 (5.0.3以后)之间的值。
检索的时候,CHAR 列删除了尾部的空格,而 VARCHAR 则保留这些空格。
* BINARY和VARBINARY 区别:
类似于 CHAR 和 VARCHAR,不同的是它们包含二进制字符串,而不包含非二进制字符串。当保存 BINARY 值时,在值的最后通过填充“0x00”(零字节)以达到指定的字段定义长度。
* ENUM :枚举类型,它的值范围需要在创建表时通过枚举方式显式指定,对 1~255 个成员的枚举需要 1 个字节存储;对于 255~65535 个成员,需要 2 个字节存储。
ENUM 类型是忽略大小写的都转成了大写,对于插入不在 ENUM 指定范围内的值时,并没有返回警告,而是插入了 enum类型声明的第一值。并且ENUM类型只允许一次从值集合中选取单个值。
* SET: 与ENUM类似,是字符串对象,里面可以包含 0~64 个成员。根据成员的不同,存储上也有所不同。
1~8 成员的集合,占 1 个字节; 9~16 成员的集合,占 2 个字节; 17~24 成员的集合,占 3 个字节; 25~32 成员的集合,占 4 个字节; 33~64 成员的集合,占 8 个字节。
SET类型允许一次去多个成员的值。
Ⅰ 、算术运算符
运算符 | 作用 |
+ | 加法 |
- | 减法 |
* | 乘法 |
/, div | 除法,返回商 |
%, mod | 除法,返回余数 |
Ⅱ、比较运算符
运算符 | 作用 |
= | 等于 |
<> 或 != | 不等于 |
<=> | null安全的等于(NULL-safe) |
< | 小于 |
<= | 小于等于 |
> | 大于 |
>= | 大于等于 |
BETWEEN | 存在于指定范围 |
IN | 存在与指定集合 |
IS NULL | null |
IS NOT NULL | 不null |
LIKE | 通配符匹配 |
REGEXP 或 RLIKE | 正则表达式匹配 |
Tips:比较运算符可以用于比较数字、字符串和表达式。数字作为浮点数比较,而字符串以不区分大小写的方式进行比较。
Ⅲ、逻辑运算符
运算符 | 作用 |
NOT 或 ! | 逻辑非 |
AND 或 && | 逻辑与 |
OR 或 || | 逻辑或 |
XOR | 逻辑异或 |
Ⅳ、位运算符
运算符 | 作用 |
& | 位与 |
| | 位或 |
^ | 位异或 |
~ | 位取反 |
>> | 位右移 |
<< | 位左移 |
Ⅴ、运算符的优先级
优先级顺序 | 运算符 |
1 | := |
2 | ||, OR, XOR |
3 | &&, AND |
4 | NOT |
5 | BETWEEN, CASE, WHEN, THEN, ELSE |
6 | =, <=>, >=, >, <=, <, !=, IS, LIKE, REGEXP, IN |
7 | | |
8 | & |
9 | <<, >> |
10 | -, + |
11 | *, /, DIV, %, MOD |
12 | ^ |
13 | -(一元减号), ~(一元比特反转) |
14 | ! |