跳到主要内容

[toc]

mysql数据类型和SQL语句分类

1.SQL语句分类​

iShot_2024-08-22_12.25.48

DDL(Data Definition Language)数据定义语言

  • create、alter、drop

DML(Data Manipulation Language)数据操纵语言

  • insert、update、delete

DCL(Data Control Language)数据控制语言

  • grant、revoke

DQL(Data Query Language)数据查询语言

  • select、show

2.mysql数据类型​

2.1 数值类型​

2.1.1 整型​

类型大小范围（有符号）范围（无符号）unsigned约束用途
TINYINT1 字节(-128，127)(0，255)小整数值
SMALLINT2 字节(-32 768，32 767)(0，65 535)大整数值
MEDIUMINT3 字节(-8 388 608，8 388 607)(0，16 777 215)大整数值
INT或INTEGER4 字节(-2 147 483 648，2 147 483 647)(0，4 294 967 295)大整数值
BIGINT8 字节(-9 233 372 036 854 775 808，9 223 372 036 854 775 807)(0，18 446 744 073 709 551 615)极大整数值
2.1.1.1 int和tinyint使用示例​

常用的数据类型是tinyint和int

#创建表t1
mysql> create table t1(t tinyint,i int);
Query OK, 0 rows affected (0.02 sec)

//向表中插入数据，有符号的tinyint下限是-128，因此插入-129报错
mysql> insert into t1 values(-129,-129);
ERROR 1264 (22003): Out of range value for column 't' at row 1
mysql> insert into t1 values(-128,-129);
Query OK, 1 row affected (0.01 sec)


#创建表t2
mysql> create table t2(t tinyint(3),i int(3));
Query OK, 0 rows affected (0.08 sec)

//向表t2中插入数据
mysql> insert into t2 values(1000,1000);
ERROR 1264 (22003): Out of range value for column 't' at row 1
mysql> insert into t2 values(999,1000);
ERROR 1264 (22003): Out of range value for column 't' at row 1
mysql> insert into t2 values(128,999);
ERROR 1264 (22003): Out of range value for column 't' at row 1
mysql> insert into t2 values(127,999);
Query OK, 1 row affected (0.01 sec)

2.1.2 浮点型​

类型大小范围(有符号)范围(无符号)用途
FLOAT4 字节float(255,30)(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)0，(1.175 494 351 E-38，3.402 823 466 E+38)单精度 浮点数值
DOUBLE8 字节double(255,30)(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)双精度 浮点数值
DECIMAL对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2double(65,30)依赖于M和D的值依赖于M和D的值小数值

float、double、decimal的格式相同

float(m,n)

double(m,n)

decimal(m,n)

其中m表示一共有m位，有n位小数

#创建t1表，三个字段分别为float，double和decimal参数表示一共显示5位，小数部分占2位
mysql> create table t1(id1 float(5,2),id2 double(5,2),id3 decimal(5,2));
Query OK, 0 rows affected (0.01 sec)

mysql> desc t1;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id1   | float(5,2)   | YES  |     | NULL    |       |
| id2   | double(5,2)  | YES  |     | NULL    |       |
| id3   | decimal(5,2) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

//向表中插入1.11，结果正常
mysql> insert into t1 values(1.11,1.11,1.11);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+------+------+------+
| id1  | id2  | id3  |
+------+------+------+
| 1.11 | 1.11 | 1.11 |
+------+------+------+
1 row in set (0.00 sec)

//向表中插入1.234发现只能保留2位小数
mysql> insert into t1 values(1.234,1.234,1.234);
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> select * from t1;
+------+------+------+
| id1  | id2  | id3  |
+------+------+------+
| 1.11 | 1.11 | 1.11 |
| 1.23 | 1.23 | 1.23 |
+------+------+------+
2 rows in set (0.00 sec)

//mysql> insert into t1 values(1.236,1.236,1.236);
Query OK, 1 row affected, 1 warning (0.02 sec)

mysql> select * from t1;
+------+------+------+
| id1  | id2  | id3  |
+------+------+------+
| 1.11 | 1.11 | 1.11 |
| 1.23 | 1.23 | 1.23 |
| 1.24 | 1.24 | 1.24 |
+------+------+------+
3 rows in set (0.00 sec).236，虽然只能保留2位小数，但是有四舍五入的规则




#新建t2表
mysql> create table t2(f float,do double,de decimal);
Query OK, 0 rows affected (0.08 sec)

mysql> desc t2;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| f     | float         | YES  |     | NULL    |       |
| do    | double        | YES  |     | NULL    |       |
| de    | decimal(10,0) | YES  |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+
3 rows in set (0.00 sec)


//分别插入1.2345，查看表可以看到decimal默认是整型
mysql> insert into t2 values(1.2345,1.2345,1.2345);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from t2;
+--------+--------+------+
| f      | do     | de   |
+--------+--------+------+
| 1.2345 | 1.2345 |    1 |
+--------+--------+------+
1 row in set (0.00 sec)

//分别插入超长的数，查看3者的区别，可以看到double的精度更高
mysql> insert into t2 values(1.234567890123456789,1.234567890123456789,1.234567890123456789);
Query OK, 1 row affected, 1 warning (0.02 sec)

mysql> select * from t2;
+---------+--------------------+------+
| f       | do                 | de   |
+---------+--------------------+------+
|  1.2345 |             1.2345 |    1 |
| 1.23457 | 1.2345678901234567 |    1 |
+---------+--------------------+------+
2 rows in set (0.00 sec)

结论：
在不指定长度的情况下，decimal默认是整型存储
double的精度要比float高

2.2 字符串类型​

类型大小用途
CHAR0-255字节定长字符串
VARCHAR0-65535 字节变长字符串
TINYBLOB0-255字节不超过 255 个字符的二进制字符串
TINYTEXT0-255字节短文本字符串
BLOB0-65 535字节二进制形式的长文本数据
TEXT0-65 535字节长文本数据
MEDIUMBLOB0-16 777 215字节二进制形式的中等长度文本数据
MEDIUMTEXT0-16 777 215字节中等长度文本数据
LONGBLOB0-4 294 967 295字节二进制形式的极大文本数据
LONGTEXT0-4 294 967 295字节极大文本数据

2.2.1 char与varchar使用示例​

CHAR 和 VARCHAR 类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。

CHAR列的长度固定为创建表是声明的长度,范围(0-255);而VARCHAR的值是可变长字符串范围(0-65535)。

#创建表t1
mysql> create table t1(c char(4),v varchar(4));
Query OK, 0 rows affected (0.09 sec)

//向t1表中插入数据
mysql> insert into t1 values('ab  ','ab  ');
Query OK, 1 row affected (0.01 sec)

mysql> select * from t1;
+------+------+
| c    | v    |
+------+------+
| ab   | ab   |
+------+------+
1 row in set (0.00 sec)

//检索的时候char会去掉空格
mysql> select length(c),length(v) from t1;
+-----------+-----------+
| length(c) | length(v) |
+-----------+-----------+
|         2 |         4 |
+-----------+-----------+
1 row in set (0.00 sec)

//给查询结果加上一个符号会看的更清晰，char在检索的时候会去掉空格
mysql> select concat(c,'+'),concat(v,'+') from t1;
+---------------+---------------+
| concat(c,'+') | concat(v,'+') |
+---------------+---------------+
| ab+           | ab  +         |
+---------------+---------------+
1 row in set (0.01 sec)

char存储的时候是定长，例如，char(5)，但是只插入了一个字符a，则会在字符a后边补全4个空格

varchar存储的时候是变长，例如，varchar(5)，但是只插入了一个字符a，则存储a后还会在a的位置后边加一个a的位置编号

2.3 日期和时间类型​

类型大小 (字节)范围格式用途
DATE31000-01-01/9999-12-31YYYY-MM-DD年月日
TIME3'-838:59:59'/'838:59:59'HH:MM:SS时分秒
YEAR11901/2155YYYY年份值
DATETIME81000-01-01 00:00:00/9999-12-31 23:59:59YYYY-MM-DD HH:MM:SS年月日时分秒
TIMESTAMP41970-01-01 00:00:00/2038结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07YYYYMMDD HHMMSS混合日期和时间值，时间戳

⚠️⚠️⚠️IMESTAM只支持到2038年的时间，因此用的比较少

2.3.1 DATE、TIME、DATETIME使用示例​

#创建表t2，3个列数据类型依次为date、time、datetime
mysql> create table t2(d date,t time,dt datetime);
Query OK, 0 rows affected (0.02 sec)

//向t2表中插入数据，这里使用now()函数
mysql> insert into t2 values(now(),now(),now());
Query OK, 1 row affected, 1 warning (0.00 sec)

//查看t2表中的数据
列d的数据类型是date	年月日
列t的数据类型是time	时分秒
列dt的数据类型是datetime	年月日时分秒
mysql> select * from t2;
+------------+----------+---------------------+
| d          | t        | dt                  |
+------------+----------+---------------------+
| 2018-10-28 | 17:32:33 | 2018-10-28 17:32:33 |
+------------+----------+---------------------+
1 row in set (0.00 sec)


//向t2表中插入数据，这里手动指定，有两种方法
方法一
mysql> insert into t2 values('2018-08-08','08:08:08','2016-06-06 06:06:06');
Query OK, 1 row affected (0.01 sec)

方法二
mysql> insert into t2 values('20180808','080808','20160606060606');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t2;
+------------+----------+---------------------+
| d          | t        | dt                  |
+------------+----------+---------------------+
| 2018-08-08 | 08:08:08 | 2016-06-06 06:06:06 |
| 2018-08-08 | 08:08:08 | 2016-06-06 06:06:06 |
+------------+----------+---------------------+
2 rows in set (0.00 sec)


⚠️只有TIMESTAMP类型插入数据为空时会自动添加当前时间，其余数据类型插入为空就是空
mysql> insert into t2 values(null,null,null);
Query OK, 1 row affected (0.04 sec)

mysql> select * from t2;
+------------+----------+---------------------+
| d          | t        | dt                  |
+------------+----------+---------------------+
| 2018-10-28 | 17:32:33 | 2018-10-28 17:32:33 |
| NULL       | NULL     | NULL                |
+------------+----------+---------------------+
2 rows in set (0.00 sec)

2.3.2 YEAR使用示例​

#创建t3表，列y的类型为year
mysql> create table t3(y year);
Query OK, 0 rows affected (0.02 sec)

//插入数据，这里使用now()方法
mysql> insert into t3 values(now());
Query OK, 1 row affected (0.01 sec)

//插入数据，这里手动指定
mysql> insert into t3 values('2020');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t3;
+------+
| y    |
+------+
| 2019 |
| 2020 |
+------+
2 rows in set (0.00 sec)

2.3.3 TIMESTAMP使用示例​

#创建t1表
mysql> create table t1(t timestamp);
Query OK, 0 rows affected (0.03 sec)

#描述t1表，可以看到timestamp有默认的CURRENT_TIMESTAMP和on update CURRENT_TIMESTAMP，即更新当前的时间
mysql> desc t1;
+-------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type      | Null | Key | Default           | Extra                       |
+-------+-----------+------+-----+-------------------+-----------------------------+
| t     | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+-------------------+-----------------------------+
1 row in set (0.00 sec)

//向t1表中插入数据,使用now()函数
mysql> insert into t1 values(now());
Query OK, 1 row affected (0.01 sec)

//向t1表中插入空，timestamp会自动补全当前时间
mysql> insert into t1 values(null);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+---------------------+
| t                   |
+---------------------+
| 2018-10-28 17:54:32 |
| 2018-10-28 17:56:07 |
+---------------------+
2 rows in set (0.00 sec)



timestamp时间上限及下限
mysql> desc t1;
+-------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type      | Null | Key | Default           | Extra                       |
+-------+-----------+------+-----+-------------------+-----------------------------+
| t     | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+-------------------+-----------------------------+
1 row in set (0.00 sec)


//timestamp时间上限是2038-01-19 11:14:07
mysql> insert into t1 values('2038-01-19 11:14:08');
ERROR 1292 (22007): Incorrect datetime value: '2038-01-19 11:14:08' for column 't' at row 1
    
mysql> insert into t1 values('2038-01-19 11:14:07');
Query OK, 1 row affected (0.01 sec)


//timestamp时间下限是1970-01-01 08:00:01
mysql> insert into t1 values('1970-01-01 08:00:00');
ERROR 1292 (22007): Incorrect datetime value: '1970-01-01 08:00:00' for column 't' at row 1
    
mysql> insert into t1 values('1970-01-01 08:00:01');
Query OK, 1 row affected (0.00 sec)

2.4 ENUM和SET类型​

类型大小用途
ENUM对1-255个成员的枚举需要1个字节存储;对于255-65535个成员，需要2个字节存储;最多允许65535个成员。单选：选择性别
SET1-8个成员的集合，占1个字节9-16个成员的集合，占2个字节17-24个成员的集合，占3个字节25-32个成员的集合，占4个字节33-64个成员的集合，占8个字节多选：兴趣爱好

2.4.1 ENUM(枚举)使用示例​

ENUM中文名称叫枚举类型，它的值范围需要在创建表时通过枚举方式显示。ENUM只允许从值集合中选取单个值，而不能一次取多个值

//创建t1表，性别设置为ENUM类型
mysql> create table t1(id int,name char(10),sex enum('F','M'));
Query OK, 0 rows affected (0.02 sec)

//向t1表中插入数据，性别是在ENUM中定义的就可插入成功
mysql> insert into t1 values(1,'小明','M');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 values(1,'小红','F');
Query OK, 1 row affected (0.01 sec)

//性别插入ENUM中没有定义的就会报错
mysql> insert into t1 values(1,'小红','A');
ERROR 1265 (01000): Data truncated for column 'sex' at row 1

2.4.2 SET(集合)使用示例​

SET和ENUM非常相似，也是一个字符串对象，里面可以包含0-64个成员。根据成员的不同，存储上也有所不同。set类型可以允许值集合中任意选择1或多个元素进行组合。对超出范围的内容将不允许注入，而对重复的值将进行自动去重。

//创建表t1，爱好列设置为SET类型
mysql> create table t1(id int,name char(10),hobby set('篮球','足球','看电影'));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t1 values(1,'小明','篮球');
Query OK, 1 row affected (0.01 sec)

mysql> insert into t1 values(2,'小洲','篮球,足球');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 values(3,'小丽','篮球,足球,篮球,看电影,足球');
Query OK, 1 row affected (0.00 sec)

//查询结果可以看到SET可以只选择一个爱好，选择多个爱好，并且可以去重
mysql> select * from t1;
+------+--------+-------------------------+
| id   | name   | hobby                   |
+------+--------+-------------------------+
|    1 | 小明   | 篮球                    |
|    2 | 小洲   | 篮球,足球               |
|    3 | 小丽   | 篮球,足球,看电影        |
+------+--------+-------------------------+
3 rows in set (0.00 sec)