目录
关于数据操纵语言 (DML)
数据操纵语言DML(Data Manipulation Language)
对象：纪录(行)
关键词：insert update delete
插入：insert into student values(01,'tonbby',99); (插入所有的字段)
insert into student(id,name) values(01,'tonbby'); (插入指定的字段)
更新：update student set name = 'tonbby',score = '99' where id = 01;
删除：delete from tonbby where id = 01;
在定义数据类型时，如果确定是 整数 ，就用 INT ； 如果是 小数 ，一定用定点数类型 DECIMAL(M,D) ； 如果是日期与时间，就用 DATETIME 。
这样做的好处是，首先确保你的系统不会因为数据类型定义出错。不过，凡事都是有两面的，可靠性好，并不意味着高效。比如，TEXT 虽然使用方便，但是效率不如 CHAR(M) 和 VARCHAR(M)。
阿里巴巴《Java开发手册》之MySQL数据库：
-
任何字段如果为非负数，必须是 UNSIGNED
-
【强制】小数类型为 DECIMAL，禁止使用 FLOAT 和 DOUBLE。
-
说明：在存储的时候，FLOAT 和 DOUBLE 都存在精度损失的问题，很可能在比较值的时候，得到不正确的结果。如果存储的数据范围超过 DECIMAL 的范围，建议将数据拆成整数和小数并分开存储。
-
-
【强制】如果存储的字符串长度几乎相等，使用 CHAR 定长字符串类型。
-
【强制】VARCHAR 是可变长字符串，不预先分配存储空间，长度不要超过 5000。如果存储长度大于此值，定义字段类型为 TEXT，独立出来一张表，用主键来对应，避免影响其它字段索引效率。
注意：
开发中很少使用delete,删除有物理删除和逻辑删除，其中逻辑删除可以通过给表添加一个字段(isDel)，若值为1，代表删除；若值为0，代表没有删除。
此时，对数据的删除操作就变成了update操作了。
truncate和delete的区别：
truncate(清空)是删除表，再重新创建这个表。属于DDL，delete是一条一条删除表中的数据，属于DML。
注意：
varchar类型的长度是可变的，创建表时指定了最大长度，定义时，其最大值可以取0-65535之间的任意值，但记录在这个范围内，使用多少分配多少，
varchar类型实际占用空间为字符串的实际长度加1。这样，可有效节约系统空间。varchar是mysql的特有的数据类型。
char类型的长度是固定的，在创建表时就指定了，其长度可以是0-255之间的任意值。虽然char占用的空间比较大，但它的处理速度快。
添加数据 -- INSERT
- 方式1：VALUES的方式添加
一条一条的添加数据
# ① 没有指明添加的字段
#正确的
INSERT INTO emp1
VALUES (1,'Tom','2000-12-21',3400); #注意：一定要按照声明的字段的先后顺序添加
#错误的
INSERT INTO emp1
VALUES (2,3400,'2000-12-21','Jerry');
# ② 指明要添加的字段 （推荐）
INSERT INTO emp1(id,hire_date,salary,`name`)
VALUES(2,'1999-09-09',4000,'Jerry');
# 说明：没有进行赋值的hire_date 的值为 null
INSERT INTO emp1(id,salary,`name`)
VALUES(3,4500,'shk');
# ③ 同时插入多条记录 （推荐）
INSERT INTO emp1(id,NAME,salary)
VALUES
(4,'Jim',5000),
(5,'张俊杰',5500);- 方式2：将查询结果插入到表中
在 INSERT 语句中加入子查询。不必书写 VALUES 子句。子查询中的值列表应与 INSERT 子句中的列名对应
将查询结果插入到表中
SELECT * FROM emp1;
INSERT INTO emp1(id,NAME,salary,hire_date)
#查询语句
SELECT employee_id,last_name,salary,hire_date # 查询的字段一定要与添加到的表的字段一一对应
FROM employees
WHERE department_id IN (70,60);
DESC emp1;
DESC employees;修改数据 -- UPDATE
更新数据 （或修改数据）
# UPDATE .... SET .... WHERE ...
# 可以实现批量修改数据的。
UPDATE emp1
SET hire_date = CURDATE()
WHERE id = 5; #约束，否则会全部删除
SELECT * FROM emp1;
#同时修改一条数据的多个字段
UPDATE emp1
SET hire_date = CURDATE(),salary = 6000
WHERE id = 4;
#题目：将表中姓名中包含字符a的提薪20%
UPDATE emp1
SET salary = salary * 1.2
WHERE NAME LIKE '%a%';删除数据
删除数据 DELETE FROM .... WHERE....
DELETE FROM emp1
WHERE id = 1;
#在删除数据时，也有可能因为约束的影响，导致删除失败
DELETE FROM departments
WHERE department_id = 50;
#小结：DML操作默认情况下，执行完以后都会自动提交数据。
# 如果希望执行完以后不自动提交数据，则需要使用 SET autocommit = FALSE.#在修改、删除数据时，也有可能因为约束的影响，导致失败
MySQL8新特性：计算列 -- VIRTUAL
计算列是由某一列的值是通过别的列计算得来的。
MySQL8的新特性：计算列
USE atguigudb;
CREATE TABLE test1(
a INT,
b INT,
c INT GENERATED ALWAYS AS (a + b) VIRTUAL #字段c即为计算列
);
INSERT INTO test1(a,b)
VALUES(10,20);
SELECT * FROM test1;
UPDATE test1
SET a = 100;数据类型精讲
常见的数据类型：
常见数据类型的属性：
整数类型
UNSIGNED -- 表示无符号 ZEROFILL-- 0填充
CREATE TABLE test_int2(
f1 INT,
f2 INT(5),
f3 INT(5) ZEROFILL #自动会添加UNSIGNED
#① 显示宽度为5。当insert的值不足5位时，
#使用0填充。 ②当使用ZEROFILL时，自动会添加UNSIGNED
)
- 适用场景
TINYINT ：一般用于枚举数据，比如系统设定取值范围很小且固定的场景。SMALLINT ：可以用于较小范围的统计数据，比如统计工厂的固定资产库存数量等。MEDIUMINT ：用于较大整数的计算，比如车站每日的客流量等。INT 、 INTEGER ：取值范围足够大，一般情况下不用考虑超限问题，用得最多。比如商品编号。BIGINT ：只有当你处理特别巨大的整数时才会用到。比如双十一的交易量、大型门户网站点击量、证券公司衍生产品持仓等。
选择类型要考虑 存储空间 和 可靠性 的平衡问题，系统故障产生的成本远远超过增加几个字段存储空间所产生的成本。
浮点类型
问题：为什么浮点数类型的无符号数取值范围，只相当于有符号数取值范围的一半，也就是只相当于有符号数取值范围大于等于零的部分呢？
MySQL 存储浮点数的格式为： 符号 (S) 、 尾数 (M) 和 阶码 (E) 。因此，无论有没有符号， MySQL 的浮点数都会存储表示符号的部分。因此， 所谓的无符号数取值范围，其实就是有符号数取值范围大于等于零的部分。
数据精度说明
#3.浮点类型
CREATE TABLE test_double1(
f1 FLOAT,
f2 FLOAT(5,2),
f3 DOUBLE,
f4 DOUBLE(5,2)
);
INSERT INTO test_double1(f3,f4)
VALUES(123.45,123.456); #存在四舍五入 小数只允许2位数，f4=123.46
#Out of range value for column 'f4' at row 1
INSERT INTO test_double1(f3,f4)
VALUES(123.45,1234.456);
#Out of range value for column 'f4' at row 1
INSERT INTO test_double1(f3,f4)
VALUES(123.45,999.995);
定点数类型 DECIMAL (使用较多)
MySQL中的定点数类型只有 DECIMAL 一种类型。
“由于 DECIMAL 数据类型的精准性，在我们的项目中，除了极少数（比如商品编号）用到整数类型外，其他的数值都用的是 DECIMAL，原因就是这个项目所处的零售行业，要求精准，一分钱也不能差。 ” ——来自某项目经理
- 位类型：BIT(了解)
二进制：BIN(f1), 十进制：HEX(f1), 十进制的方式显示数据: f1 + 0
时间日期类型
YEAR类型
-
在MySQL中，YEAR有以下几种存储格式：
-
以4位字符串或数字格式表示YEAR类型，其格式为YYYY，最小值为1901，最大值为2155。
-
以2位字符串格式表示YEAR类型，最小值为00，最大值为99。
-
当取值为01到69时，表示2001到2069；
-
当取值为70到99时，表示1970到1999；
-
当取值整数的0或00添加的话，那么是0000年；
-
当取值是日期/字符串的'0'添加的话，是2000年。
-
-
DATE类型、TIME类型
CREATE TABLE test_date1(
f1 DATE
);
DESC test_date1;
#添加指定的年月日
INSERT INTO test_date1
VALUES ('2020-10-01'), ('20201001'),(20201001);
#添加当前的年月日
INSERT INTO test_date1
VALUES (CURDATE()),(CURRENT_DATE()),(NOW());- TIME类型
CREATE TABLE test_time1(
f1 TIME
);
DESC test_time1;
INSERT INTO test_time1
VALUES('2 12:30:29'), ('12:35:29'), ('12:40'), ('2 12:40'),('1 05'), ('45');
#60:30:29 12:35:29 12:40:00 60:40:00 29:00:00 00:00:45
INSERT INTO test_time1
VALUES (NOW()), (CURRENT_TIME()),(CURTIME());DATETIME类型(使用较多)
CREATE TABLE test_datetime1(
dt DATETIME
);
INSERT INTO test_datetime1
VALUES ('2021-01-01 06:50:30'), ('20210101065030');#会自动转换为前面的格式
INSERT INTO test_datetime1
VALUES (CURRENT_TIMESTAMP()), (NOW()),(SYSDATE());
TIMESTAMP类型
CREATE TABLE test_timestamp1(
ts TIMESTAMP
);
INSERT INTO test_timestamp1
VALUES ('1999-01-01 03:04:50'), ('19990101030405');
INSERT INTO test_timestamp1
VALUES (CURRENT_TIMESTAMP()), (NOW());
#Incorrect datetime value 超出时间范围
INSERT INTO test_timestamp1
VALUES ('2038-01-20 03:14:07');#修改当前的时区
SET time_zone = '+9:00';
SELECT * FROM temp_time;-
TIMESTAMP存储空间比较小，表示的日期时间范围也比较小
-
底层存储方式不同，TIMESTAMP底层存储的是毫秒值，距离1970-1-1 0:0:0 0毫秒的毫秒值。
-
两个日期比较大小或日期计算时，TIMESTAMP更方便、更快。
-
TIMESTAMP和时区有关。TIMESTAMP会根据用户的时区不同，显示不同的结果。而DATETIME则只能反映出插入时当地的时区，其他时区的人查看数据必然会有误差的。
总结
mysql> SELECT UNIX_TIMESTAMP();
文本字符串类型
MySQL中，文本字符串总体上分为 CHAR 、 VARCHAR 、 TINYTEXT 、 TEXT 、 MEDIUMTEXT 、LONGTEXT 、 ENUM 、 SET 等类型。
CHAR与VARCHAR类型(重点)
CHAR类型：
CHAR(M) 类型一般需要预先定义字符串长度。如果不指定(M)，则表示长度默认是1个字符，一旦创建长度不可变。创建就会占用内存，可能存在内存空间的浪费。当MySQL检索CHAR类型的数据时，CHAR类型的字段会去除尾部的空格。
CREATE TABLE test_char1(
c1 CHAR,
c2 CHAR(5)
);
INSERT INTO test_char1(c1)
VALUES('a');
#Data too long for column 'c1' at row 1
INSERT INTO test_char1(c1)
VALUES('ab');
INSERT INTO test_char1(c2)
VALUES('尚硅谷教育');
#Data too long for column 'c2' at row 1
INSERT INTO test_char1(c2)
VALUES('尚硅谷IT教育');VARCHAR类型：
Variable Char 可变化字符
#Column length too big for column 'name' (max = 21845); use BLOB or TEXT instead
CREATE TABLE test_varchar2(
NAME VARCHAR(65535)
);
CREATE TABLE test_varchar3(
NAME VARCHAR(5)
);
INSERT INTO test_varchar3
VALUES('尚硅谷'),('尚硅谷教育');
#Data too long for column 'NAME' at row 1
INSERT INTO test_varchar3
VALUES('尚硅谷IT教育');
哪些情况使用 CHAR 或 VARCHAR 更好？
TEXT类型
当文本量较大时，我们可以考虑使用TEXT类型 。
CREATE TABLE test_text(
tx TEXT
);
INSERT INTO test_text
VALUES('atguigu ');
SELECT CHAR_LENGTH(tx)
FROM test_text; #10 不会去除空格ENUM类型、SET类型
-
枚举 (ENUM)
ENUM类型
CREATE TABLE test_enum(
season ENUM('春','夏','秋','冬','unknow')
);
INSERT INTO test_enum
VALUES('春'),('秋');
SELECT * FROM test_enum;
#Data truncated for column 'season' at row 1
INSERT INTO test_enum
VALUES('春,秋');
#Data truncated for column 'season' at row 1
INSERT INTO test_enum
VALUES('人');
INSERT INTO test_enum
VALUES('unknow');
#忽略大小写的
INSERT INTO test_enum
VALUES('UNKNOW');
#可以使用索引进行枚举元素的调用
INSERT INTO test_enum
VALUES(1),('3'); #春|秋
# 没有限制非空的情况下，可以添加null值
INSERT INTO test_enum
VALUES (NULL);
-
SET 集合类型
SET类型
CREATE TABLE test_set(
s SET ('A', 'B', 'C')
);
INSERT INTO test_set (s) VALUES ('A'), ('A,B');
#插入重复的SET类型成员时，MySQL会自动删除重复的成员
INSERT INTO test_set (s) VALUES ('A,B,C,A'); #A|B|C
#向SET类型的字段插入SET成员中不存在的值时，MySQL会抛出错误。
#Data truncated for column 's' at row 1
INSERT INTO test_set (s) VALUES ('A,B,C,D');
ENUM SET联合使用
CREATE TABLE temp_mul(
gender ENUM('男','女'),
hobby SET('吃饭','睡觉','打豆豆','写代码')
);
INSERT INTO temp_mul
VALUES('男','睡觉,打豆豆');二进制字符串类型(了解)
BINARY与VARBINARY类型(使用较少)
BINARY (M)为固定长度的二进制字符串，如果未指定(M)，表示只能存储 1个字节 。
VARBINARY (M)为可变长度的二进制字符串，VARBINARY类型 必须指定(M) ，否则报错。
test_binary1;
INSERT INTO test_binary1(f1,f2)
VALUES('a','abc');
SELECT * FROM test_binary1;
#Data too long for column 'f1' at row 1
INSERT INTO test_binary1(f1)
VALUES('ab');BLOB类型
MySQL中的BLOB类型包括TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB 4种类型，它们可容纳值的最大长度不同。可以存储一个二进制的大对象，比如 图片 、 音频 和 视频 等。
BLOB是一个 二进制大对象 ，可以容纳可变数量的数据。 在实际工作中，往往不会在MySQL数据库中使用BLOB类型存储大对象数据，通常会将图片、音频和视频文件存储到 服务器的磁盘上 ，并将图片、音频和视频的访问路径存储到MySQL中。
Blob类型
CREATE TABLE test_blob1(
id INT,
img MEDIUMBLOB
);
INSERT INTO test_blob1(id)
VALUES (1001);(img 此处用图形化工具添加的)
SELECT *
FROM test_blob1;JSON 类型
JSON（JavaScript Object Notation）是一种轻量级的 数据交换格式 。简洁和清晰的层次结构使得 JSON 成为理想的数据交换语言。它易于人阅读和编写，同时也易于机器解析和生成，并有效地提升网络传输效率。JSON 可以将 JavaScript 对象中表示的一组数据转换为字符串，然后就可以在网络或者程序之间轻松地传递这个字符串，并在需要的时候将它还原为各编程语言所支持的数据格式。
JSON类型
CREATE TABLE test_json(
js json
);
INSERT INTO test_json (js)
VALUES ('{"name":"songhk", "age":18, "address":{"province":"beijing", "city":"beijing"}}');
SELECT * FROM test_json;
#键值对方式 key取出？
SELECT js -> '$.name' AS NAME,js -> '$.age' AS age ,js -> '$.address.province' AS province, js -> '$.address.city' AS city
FROM test_json;
教程来源：宋红康 -- 数据类型精讲 MySQL数据库教程天花板，mysql安装到mysql高级，强！硬！_哔哩哔哩_bilibili
951
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
