MySQL数据库，数据处理之增删改、数据类型

数据处理：增删改

添加数据：

方式一：一条一条地添加数据

①：

INSERT  INTO  表名

VALUES (字段值，字段值，……);

注：一定要按照声明的字段的先后顺序添加。

例：

INSERT INTO emp1

VALUES(1,'TOM','2000-12-21',3400);

②：指定字段的顺序

INSERT  INTO  表名(字段名，字段名，……)

VALUES (字段值，字段值，……);

注：可以在表名后的括号中指定字段的顺序，在VALUES后面的括号中添加数据时，就要按表名后括号中的顺序添加数据。推荐使用②。

在表名后的括号中也可以不指明某个字段，此字段就可以不参与添加数据，默认便为NULL（没使用约束的情况下）。

例：

INSERT INTO emp1( `name`, id , hire_date , salary )

VALUES('liergou' , 2 , '2004-05-09' , 70000);

③：同时插入多条记录（推荐）

INSERT  INTO  表名(字段名，字段名，……)

VALUES (字段值，字段值，……)

,(字段值，字段值，……),……;

注：以上所有的VALUES都可以写作VALUE，但是VALUES是规范。

例：

INSERT INTO emp1( `name`, id , hire_date , salary )

VALUES('钟离' , 3 , '1999-03-09' , 10) ,

('丘丘人', 4 , '2000-01-03' , 2000);

方式二：将查询的结果插入表中

INSERT  INTO  表名(字段名，字段名，……)

SELECT  字段 , 字段 , ……

FROM  表名

……（查询语句的操作）

注：

查询的字段一定要与添加到的表中的字段一一对应。

要添加的表中的字段的长度或精度不能低于查询操作中的表中的字段的长度或精度。

例：

INSERT INTO emp1(id , `name`, hire_date , salary )

SELECT empno , ename , hiredate , sal

FROM emp

WHERE deptno = 30;

更新数据（即修改数据）

UPDATE  表名 

SET  字段 = 新字段值 , 字段 = 新字段值……

WHERE  指定修改范围的条件;

注：

WHERE语句可以指定一条或多条记录进行修改，如果没有WHERE语句，就是对整个表中的所有记录进行修改。

例：

UPDATE emp1

SET salary = 30000

WHERE id = 3;

删除数据

DELETE  FROM  表名

WHERE  指定删除范围的条件;

注：

WHERE语句可以指定一条或多条记录进行删除，如果没有WHERE语句，就是对整个表中的所有记录进行删除。

例：

DELETE FROM emp1

WHERE id = 7900;

MySQL8的新特性：计算列

计算列即某一列的值是通过别的列计算来的。使用GENERATED ALWAYS AS (计算式) VIRTUAL来实现。

例：

CREATE TABLE test1 (

a INT,

b INT,

c INT GENERATED ALWAYS AS (a + b) VIRTUAL

);

例如如上代码，c字段的值不需赋值，而是由a和b的值决定，当a或b的值更新，c的值也会更新为更新后的a+b。

MySQL数据类型

常见数据类型的属性：

关于属性：CHARACTER SET 字符集

- 创建数据库的时候指定字符集：

CREATE DATABASE IF NOT EXISTS 数据库名 CHARACTER SET 字符集;

- 创建表的时候，指定表的字符集;

CREATE TABLE 表名

……

) CHARACTER SET 字符集;

- 创建表时，指明表中的字段时，可以指定字段的字符集：

CREATE TABLE 表名(

字段名 数据类型 CHARACTER SET 字符集

……

);

整形类型：

可选属性：

M：

• 表示显示宽度，当数据宽度小于宽度时在数字前面需要用字符填满宽度。该项功能需要配合“ZEROFILL”使用，表示用“0”填满宽度，否则指定显示宽度无效。

• 整形数据类型可以在定义表结构时指定所需要的宽度，若不指定，则系统为每一种类型指定的默认的宽度值。

• 如果设置了显示宽度，插入的数据宽度超过显示宽度限制，不会对插入的数据有任何影响，还是按照类型的实际宽度进行保存，即显示宽度与类型可以存储的值范围无关。

• 从MySQL8之后，整数数据类型不推荐使用显示宽度属性。

UNSIGNED

无符号类型（即非负），所有的整数类型都有一个可选的属性UNSIGNED（无符号属性），无符号整数类型的最小取值为0。使用时添加到数据类型的后面。

适用场景：

浮点类型：

可以处理小数的类型是浮点类型和定点类型，也可以将整数看作小数的特例。MySQL支持的浮点数类型分别是FLOAT、DOUBLE、REAL。

• FLOAT表示单精度浮点数，DOUBLE表示双精度浮点数。

• 关于FLOAT(M,D)和DOUBLE(M,D)：M是精度，D是标度。M = 整数位+小数位，D = 小数位。D<=M<=255,0<=D<=30。此种写法是非标准语法。

• 如果小数的位数超出范围，会进行四舍五入到最大限度位。

定点数类型：

• 使用DECIMAL(M,D)的方式表示高精度小数。D是标度。M = 整数位+小数位，D = 小数位。D<=M<=65,0<=D<=30。

• DECIMAL(M,D)的最大取值范围与DOUBLE一致。但是定点数的存储空间要由精度决定，在精度要求不高时，浮点数的数值范围更大。

• 如果小数的位数超出范围，会进行四舍五入到最大限度位。

• 定点数在MySQL内部是以字符串的形式存储的，这就决定其精准性。

位类型：

BIT类型中存储的是二进制值。

• BIT类型如果没有指定(M),默认是一位。表示只能存一位的二进制值。

• 用BIT类型的数据+0即可变为显示十进制。

日期时间类型：

YEAR类型

YEAR类型用来表示年份，占一个字节的存储空间。

在MySQL中，YEAR有以下几种储存格式：

DATE类型：

• DATE类型表示日期，没有时间部分。格式为YYYY-MM-DD，其中，YYYY表示年份，MM表示月份，DD表示日期。占三个字节的内存空间。

• 年份的输入与YEAR类型的规则一致。

• 使用CURRENT_DATE或NOW函数，会插入当前系统的日期。

TIME类型：

TIME类型用来表示时间，没有日期部分。占三个字节的内存空间。

可以用“HH:MM:SS”格式来表示TIME类型，其中，HH表示小时，MM表示分钟，SS表示秒。

在MySQL中，向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，而不是00:12:10。

• 可以使用不带有冒号的字符串或者数字，格式为'HHMMSS'或者HHMMSS。如果插入一个不合法的字符串或者数字，MySQL在存储数据时，会将其自动转化为00:00:00进行存储。比如1210，MySQL会将最右边的两位解析成秒，表示00:12:10，而不是12:10:00。

• 使用CURRENT_TIME或者NOW，会插入当前系统的时间。

DATETIME类型：

DATETIME类型在所有的日期时间类型中占用的存储空间最大，总共需要8个字节的存储空间。在格式上为DATE类型和TIME类型的组合，可以表示为YYYY-MM-DD HH:MM：SS，其中YYYY表示年份，MM表示月份，DD表示日期,HH表示小时，MM表示分钟，SS表示秒。

在向DATETIME类型的字段插入数据时，同样需要满足一定的格式条件。

• 以YYYY-MM-DD HH：MM：SS格式或者YYYYMMDDHHMMSS格式的字符串插入DATETIME类型的字段时，最小值为1000-01-01 00:00:00，最大值为9999-12-0323:59:59。

• 以YYYYMMDDHHMMSS格式的数字插入DATETIME类型的字段时，会被转化为YYYY-MM-DDHH:MM:SS格式。

• 以YY-MM-DD HH：MM：SS格式或者YYMMDDHHMMSS格式的字符串插入DATETIME类型的字段时，两位数的年份规则符合YEAR类型的规则，00到69表示2000到2069；70到99表示1970到1999。

• 使用函数 CURRENT_TIMESTAMP( )和NOW( )，可以向DATETIME类型的字段插入系统的当前日期和时间。

TIMESTAMP类型：

TIMESTAMP类型也可以表示日期时间，其显示格式与DATETIME类型相同，都是YYYY-MM-DD HH:MM:SS，需要4个字节的存储空间。但是TIMESTAMP存储的时间范围比DATETIME要小很多，只能存储“1970-01-01  00:00:01 UTC”到“2038-01-19  03:14:07 UTC”之间的时间。其中，UTC表示世界统一时间，也叫作世界标准时间。

• 存储数据的时候需要对当前时间所在的时区进行转换，查询数据的时候再将时间转换回当前的时区。因此，使用TIMESTAMP存储的同一个时间值，在不同的时区查询时会显示不同的时间。

• 向TIMESTAMP类型的字段插入数据时，当插入的数据格式满足YY-MM-DD HH:MM:SS和YYMMDDHHMMSS时，两位数值的年份同样符合YEAR类型的规则条件，只不过表示的时间范围要小很多。

• 如果向TIMESTAMP类型的字段插入的时间超出了TIMESTAMP类型的范围，则MySQL会抛出错误信息。

DATETIME类型和TIMESTAMP类型的区别：

• TIMESTAMP存储空间比较小，表示的日期时间范围也比较小

• 底层存储方式不同，TIMESTAMP底层存储的是毫秒值，距离1970-1-10:0:00毫秒的毫秒值。

• 两个日期比较大小或日期计算时，TIMESTAMP更方便、更快。

• TIMESTAMP和时区有关。TIMESTAMP会根据用户的时区不同，显示不同的结果。而DATETIME则只能反映出插入时当地的时区，其他时区的人查看数据必然会有误差的。

文本字符串类型：

CHAR和VARCHAR类型：

• CHAR(M)类型一般需要预先定义字符串长度。如果不指定(M)，则表示长度默认是一个字符。

• 如果保存时，数据的实际长度比CHAR类型声明的长度小，则会在右侧填充空格以达到指定的长度。当MySQL检索CHAR类型的数据时，CHAR类型的字段会去除尾部的空格。

• 定义CHAR类型字段时，声明的字段长度即为CHAR类型字段所占的存储空间的字节数。

• VARCHAR类型的字段必须指明(M)。

CHAR和VARCHAR的区别：

当存储很短的信息、固定长度的信息、行的改变较频繁，使用CHAR更好。其他的情况使用VARCHAR更好。

TEXT类型：

TEXT类型用来存储文本类型的字符串，在向TEXT类型的字段保存和查询数据时，系统自动按照实际长度存储，不需要预先定义长度。

由于实际存储的数据的长度不确定，MySQL不允许TEXT类型的字段做主键。

TEXT文本类型可以存储较大的文本，搜索速度较慢，如果不是较大的内容，建议使用CHAR和VARCHAR类型。

ENUM类型：

ENUM类型也叫枚举类型，ENUM的取值范围需要在定义字段时进行指定。设置字段时，ENUM类型只允许从成员中选取单个值，不能一次选取多个值。

所需要的存储空间由定义ENUM类型时指定的成员个数决定。

• 当包含1~255个成员时需要一个字节的存储空间。

• 当包含256~65535个成员时需要两个字节的存储空间。

关于ENUM类型的定义：

例：

CREATE TABLE test(

season ENUM('春','夏','秋','冬'),

……

);

season的字段的值只能是春、夏、秋、冬其中一个。

也可以使用索引的方式进行调用枚举的元素，比如1就是春，2就是夏。

SET类型：

SET表示一个字符串对象，可以包括0个或多个成员，成员上限是64。设置字段时，可以取取值范围内的0个或多个值。

例：

创建表：

CREATE TABLE test_set(

s SET('a','b','c')

);

插入记录：

INSERT INTO test_set

VALUES('a,b'),

('a'),

('a,b,c,a,b');

表：

当插入的值中有重复的SET类型成员时，会自动删除值中重复的成员。

不能出现插入的SET的值中存在SET中不存在的成员。

SET和ENUM的区别：

SET的值可以包含多个成员，ENUM的值只能包含一个成员。

例：

创建表：

CREATE TABLE set_enum(

ss SET('玩原神','玩崩铁','玩抖音'),

ee ENUM('玩原神','玩崩铁','玩抖音')

);

插入记录：

INSERT INTO set_enum

VALUES('玩原神,玩崩铁,玩抖音'   ,   '玩原神'),

('玩原神,玩崩铁'   ,   '玩抖音'),

('玩崩铁'   ,   '玩原神');

二进制字符串类型：

二进制字符串类型主要存储一些二进制数据（比如图片、音频、视频等）。

BINARY类型与VARBINARY类型：

（打字好累，截图偷懒）

BLOB类型：

JSON类型：

JSON可以将JavaScript对象中表示的一组数据转换为字符串，然后就可以在网络或者程序之间轻松地传递这个字符串，并在需要的时候将它还原为各个编程语言所支持的数据格式。

关于数据类型的选择，

以阿里的开发手册为例：

如果是整型，使用INT。小数，使用定点数类型DECIMAL(M,D)。日期，使用DATETIME。