SQL中的数据类型和运算符

Forlogen
119 阅读4分钟

数据类型

数值类型

整数类型字节
TINYINT1
SMALLINT2
MEDIUMINT3
INT、INTEGER4
BIGINT8
浮点数类型
FLOAT4
DOUBLE8
定点数类型
DEC(M,D)M+2
DECIMAL(M,D)M+2
位类型
BIT(M)1~8

时间日期类型

日期和时间类型字节描述零值表示
DATE4年月日0000-00-00
DATETIME8年月日时分秒0000-00-00 00:00:00
TIMESTAMP4当前系统时间00000000000000
TIME3时分秒00:00:00
YEAR1年0000
mysql> CREATE TABLE TEST (D DATE, T TIME, DT DATETIME, TS TIMESTAMP,Y YEAR);
Query OK, 0 rows affected (0.03 sec)

mysql> DESC TEST;
+-------+-----------+------+-----+---------+-------+
| Field | Type      | Null | Key | Default | Extra |
+-------+-----------+------+-----+---------+-------+
| D     | date      | YES  |     | NULL    |       |
| T     | time      | YES  |     | NULL    |       |
| DT    | datetime  | YES  |     | NULL    |       |
| TS    | timestamp | YES  |     | NULL    |       |
| Y     | year      | YES  |     | NULL    |       |
+-------+-----------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql> INSERT INTO TEST VALUES(NOW(), NOW(), NOW(), NOW(), NOW());
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> SELECT * FROM TEST;
+------------+----------+---------------------+---------------------+------+
| D          | T        | DT                  | TS                  | Y    |
+------------+----------+---------------------+---------------------+------+
| 2020-04-15 | 08:35:42 | 2020-04-15 08:35:42 | 2020-04-15 08:35:42 | 2020 |
+------------+----------+---------------------+---------------------+------+
1 row in set (0.00 sec)

字符串类型

字符串类型描述
CHAR(M)固定长度的字符串。最多 8,000 个字符
VARCHAR(M)可变长度的字符串。最多 8,000 个字符
TINYBLOB用于 BLOBs (Binary Large OBjects)。存放最多 255 字节的数据
BLOB用于 BLOBs (Binary Large OBjects)。存放最多 65,535 字节的数据
MEDIUMBLOB用于 BLOBs (Binary Large OBjects)。存放最多 16,777,215 字节的数据
LONGBLOB用于 BLOBs (Binary Large OBjects)。存放最多 4,294,967,295 字节的数据
TINYTEXT存放最大长度为 255 个字符的字符串
TEXT可变长度的字符串。最多 2GB 字符数据
MEDIUMTEXT存放最大长度为 16,777,215 个字符的字符串
LONGTEXT存放最大长度为 4,294,967,295 个字符的字符串
VARBINARY(M)可变长度的字符串。最多 2GB 字符数据
BINARY(M)固定长度的二进制数据。最多 8,000 字节

  • CHAR和VARCHAR的区别：两者的区别在于存储方式的不同，CHAR列的长度固定为创建表声明时的长度，VARCHAR列中的值为可变长字符串；在检索的时候，CHAR会删除尾部的空格，而VARCHAR则会保留尾部的空格。

    mysql> CREATE TABLE STRING (C CHAR(5),V VARCHAR(5));
Query OK, 0 rows affected (0.04 sec)

mysql> DESC STRING;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| C     | char(5)    | YES  |     | NULL    |       |
| V     | varchar(5) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> INSERT INTO STRING VALUES('111', '111');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM STRING;
+------+------+
| C    | V    |
+------+------+
| 111  | 111  |
+------+------+
1 row in set (0.00 sec)

mysql> INSERT INTO STRING VALUES('1111111111', '1111111111');
ERROR 1406 (22001): Data too long for column 'C' at row 1

  • BINARY和VARBINARY：它们类似于CHAR和VARCHAR，不同之处在于它们存储的是二进制字符串。

    mysql> CREATE TABLE T (B BINARY(3));
Query OK, 0 rows affected (0.04 sec)

mysql> DESC T;
+-------+-----------+------+-----+---------+-------+
| Field | Type      | Null | Key | Default | Extra |
+-------+-----------+------+-----+---------+-------+
| B     | binary(3) | YES  |     | NULL    |       |
+-------+-----------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> INSERT INTO T SET B = 'a';
Query OK, 1 row affected (0.01 sec)

mysql> SELECT *, HEX(B), B='a', B='a\0', B='a\0\0' FROM T;
+------------+--------+-------+---------+-----------+
| B          | HEX(B) | B='a' | B='a\0' | B='a\0\0' |
+------------+--------+-------+---------+-----------+
| 0x610000   | 610000 |     0 |       0 |         1 |
+------------+--------+-------+---------+-----------+
1 row in set (0.01 sec)

  • ENUM：ENUM是一个字符串对象，其值来自表创建时在列规定中显式枚举的一列值。可以插入空字符串""和NULL：

    • 如果你将一个非法值插入ENUM(也就是说，允许的值列之外的字符串)，将插入空字符串以作为特殊错误值。该字符串与“普通”空字符串不同，该字符串有数值值0。
    • 如果将ENUM列声明为允许NULL，NULL值则为该列的一个有效值，并且默认值为NULL。如果ENUM列被声明为NOT NULL，其默认值为允许的值列的第1个元素。
    mysql> CREATE TABLE SHIRTS(NAME VARCHAR(20), SIZE ENUM('X-SAMALL', 'SMALL','MEDIUM', 'LARGE'));
Query OK, 0 rows affected (0.04 sec)

mysql> DESC SHIRTS;
+-------+-------------------------------------------+------+-----+---------+-------+
| Field | Type                                      | Null | Key | Default | Extra |
+-------+-------------------------------------------+------+-----+---------+-------+
| NAME  | varchar(20)                               | YES  |     | NULL    |       |
| SIZE  | enum('X-SAMALL','SMALL','MEDIUM','LARGE') | YES  |     | NULL    |       |
+-------+-------------------------------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> INSERT INTO SHIRTS VALUES('DRESS','LARGE');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM SHIRTS;
+-------+-------+
| NAME  | SIZE  |
+-------+-------+
| DRESS | LARGE |
+-------+-------+
1 row in set (0.00 sec)

  • SET：SET是一个字符串对象，可以有零或多个值，其值来自表创建时规定的允许的一列值。指定包括多个SET成员的SET列值时各成员之间用逗号(‘,’)间隔开。

    mysql> INSERT INTO myset (col) VALUES ('a,d'), ('d,a'), ('a,d,a'), ('a,d,d'), ('d,a,d');
Query OK, 5 rows affected (0.02 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM MYSET;
+------+
| col  |
+------+
| a,d  |
| a,d  |
| a,d  |
| a,d  |
| a,d  |
+------+
5 rows in set (0.00 sec)

运算符

算数运算符描述
+加
-减
*乘
/，DIV除
%, MOD取余
比较运算符描述
=等于
!=, <>不等于
<=>NULL安全的等于
<小于
<=小于等于
大于
>=大于等于
BETWEEN存在于指定范围
IN存在于指定集合
IS NULL为NULL
IS NOT NULL不为NULL
LIKE通配符匹配
REGEXP, RLIKE正则表达式匹配
逻辑运算符描述
NOT, !逻辑非
&&, AND逻辑与
||, OR逻辑或
XOR逻辑异或
位运算符描述
&位与
|位或
位异或
~取反
>>左移
<<右移
avatar
文章
阅读
粉丝