这是 MySQL 系列第 5 篇.
环境: mysql5.7.25,cmd 命令中进行演示.
DML(Data Manipulation Language)数据操作语言, 以 INSERT,UPDATE,DELETE 三种指令为核心, 分别代表插入, 更新与删除, 是必须要掌握的指令, DML 和 SQL 中的 select 熟称 CRUD(增删改查).
文中涉及到的语法用 [] 包含的内容属于可选项, 下面做详细说明.
插入操作
插入单行 2 种方式
方式 1
insert into 表名[(字段, 字段)] values (值, 值);
说明:
值和字段需要一一对应
如果是字符型或日期类型, 值需要用单引号引起来; 如果是数值类型, 不需要用单引号
字段和值的个数必须一致, 位置对应
字段如果不能为空, 则必须插入值
可以为空的字段可以不用插入值, 但需要注意: 字段和值都不写; 或字段写上, 值用 null 代替
表名后面的字段可以省略不写, 此时表示所有字段, 顺序和表中字段顺序一致.
方式 2
insert into 表名 set 字段 = 值, 字段 = 值;
方式 2 不常见, 建议使用方式 1
批量插入 2 种方式
方式 1
insert into 表名 [(字段, 字段)] values (值, 值),(值, 值),(值, 值);
方式 2
insert into 表 [(字段, 字段)]
数据来源 select 语句;
说明:
数据来源 select 语句可以有很多种写法, 需要注意: select 返回的结果和插入数据的字段数量, 顺序, 类型需要一致.
关于 select 的写法后面文章会详细介绍.
如:
-- 删除 test1
drop table if exists test1;
-- 创建 test1
create table test1(a int,b int);
-- 删除 test2
drop table if exists test2;
-- 创建 test2
create table test2(c1 int,c2 int,c3 int);
-- 向 test2 中插入数据
insert into test2 values (100,101,102),(200,201,202),(300,301,302),(400,401,402);
-- 向 test1 中插入数据
insert into test1 (a,b) select 1,1 union all select 2,2 union all select 2,2;
-- 向 test1 插入数据, 数据来源于 test2 表
- insert into test1 (a,b) select c2,c3 from test2 where c1>=200;
- select * from test1;
- MySQL> select * from test1;
- +------+------+
- | a | b |
- +------+------+
- | 1 | 1 |
- | 2 | 2 |
- | 2 | 2 |
- | 201 | 202 |
- | 301 | 302 |
- | 401 | 402 |
- MySQL> select * from test2;
- +------+------+------+
- | c1 | c2 | c3 |
- +------+------+------+
- | 100 | 101 | 102 |
- | 200 | 201 | 202 |
- | 300 | 301 | 302 |
- | 400 | 401 | 402 |
- +------+------+------+
- 4 rows in set (0.00 sec)
数据更新
单表更新
语法:
update 表名 [[as] 别名] set [别名.]字段 = 值,[别名.]字段 = 值 [where 条件];
有些表名可能名称比较长, 为了方便操作, 可以给这个表名起个简单的别名, 更方便操作一些.
如果无别名的时候, 表名就是别名.
示例:
- MySQL> update test1 t set t.a = 2;
- Query OK, 4 rows affected (0.00 sec)
- Rows matched: 6 Changed: 4 Warnings: 0
- MySQL> update test1 as t set t.a = 3;
- Query OK, 6 rows affected (0.00 sec)
- Rows matched: 6 Changed: 6 Warnings: 0
- MySQL> update test1 set a = 1,b=2;
- Query OK, 6 rows affected (0.00 sec)
- Rows matched: 6 Changed: 6 Warnings: 0
多表更新
可以同时更新多个表中的数据
语法:
update 表 1 [[as] 别名 1], 表名 2 [[as] 别名 2]
set [别名.]字段 = 值,[别名.]字段 = 值
[where 条件]
示例:
-- 无别名方式
update test1,test2 set test1.a = 2 ,test1.b = 2, test2.c1 = 10;
-- 无别名方式
update test1,test2 set test1.a = 2 ,test1.b = 2, test2.c1 = 10 where test1.a = test2.c1;
-- 别名方式更新
update test1 t1,test2 t2 set t1.a = 2 ,t1.b = 2, t2.c1 = 10 where t1.a = t2.c1;
-- 别名的方式更新多个表的多个字段
update test1 as t1,test2 t2 set t1.a = 2 ,t1.b = 2, t2.c1 = 10 where t1.a = t2.c1;
使用建议
建议采用单表方式更新, 方便维护.
删除数据操作
使用 delete 删除
delete 单表删除
delete [别名] from 表名 [[as] 别名] [where 条件];
注意:
如果无别名的时候, 表名就是别名
如果有别名, delete 后面必须写别名
如果没有别名, delete 后面的别名可以省略不写.
示例
-- 删除 test1 表所有记录
delete from test1;
-- 删除 test1 表所有记录
delete test1 from test1;
-- 有别名的方式, 删除 test1 表所有记录
delete t1 from test1 t1;
-- 有别名的方式删除满足条件的记录
delete t1 from test1 t1 where t1.a>100;
上面的 4 种写法, 大家可以认真看一下.
多表删除
可以同时删除多个表中的记录, 语法如下:
delete [别名 1, 别名 2] from 表 1 [[as] 别名 1], 表 2 [[as] 别名 2] [where 条件];
说明:
别名可以省略不写, 但是需要在 delete 后面跟上表名, 多个表名之间用逗号隔开.
示例 1
delete t1 from test1 t1,test2 t2 where t1.a=t2.c2;
删除 test1 表中的记录, 条件是这些记录的字段 a 在 test.c2 中存在的记录
看一下运行效果:
-- 删除 test1
drop table if exists test1;
-- 创建 test1
create table test1(a int,b int);
-- 删除 test2
drop table if exists test2;
-- 创建 test2
create table test2(c1 int,c2 int,c3 int);
-- 向 test2 中插入数据
insert into test2 values (100,101,102),(200,201,202),(300,301,302),(400,401,402);
-- 向 test1 中插入数据
insert into test1 (a,b) select 1,1 union all select 2,2 union all select 2,2;
-- 向 test1 插入数据, 数据来源于 test2 表
- insert into test1 (a,b) select c2,c3 from test2 where c1>=200;
- MySQL> select * from test1;
- +------+------+
- | a | b |
- +------+------+
- | 1 | 1 |
- | 2 | 2 |
- | 2 | 2 |
- | 201 | 202 |
- | 301 | 302 |
- | 401 | 402 |
- MySQL> select * from test2;
- +------+------+------+
- | c1 | c2 | c3 |
- +------+------+------+
- | 100 | 101 | 102 |
- | 200 | 201 | 202 |
- | 300 | 301 | 302 |
- | 400 | 401 | 402 |
- +------+------+------+
- 4 rows in set (0.00 sec)
- MySQL> delete t1 from test1 t1,test2 t2 where t1.a=t2.c2;
- Query OK, 3 rows affected (0.00 sec)
- MySQL> select * from test1;
- +------+------+
- | a | b |
- +------+------+
- | 1 | 1 |
- | 2 | 2 |
- | 2 | 2 |
- +------+------+
- 3 rows in set (0.00 sec)
从上面的输出中可以看到 test1 表中 3 条记录被删除了.
示例 2
delete t2,t1 from test1 t1,test2 t2 where t1.a=t2.c2;
同时对 2 个表进行删除, 条件是 test.a=test.c2 的记录
看一下运行效果:
-- 删除 test1
drop table if exists test1;
-- 创建 test1
create table test1(a int,b int);
-- 删除 test2
drop table if exists test2;
-- 创建 test2
create table test2(c1 int,c2 int,c3 int);
-- 向 test2 中插入数据
insert into test2 values (100,101,102),(200,201,202),(300,301,302),(400,401,402);
-- 向 test1 中插入数据
insert into test1 (a,b) select 1,1 union all select 2,2 union all select 2,2;
-- 向 test1 插入数据, 数据来源于 test2 表
- insert into test1 (a,b) select c2,c3 from test2 where c1>=200;
- MySQL> select * from test1;
- +------+------+
- | a | b |
- +------+------+
- | 1 | 1 |
- | 2 | 2 |
- | 2 | 2 |
- | 201 | 202 |
- | 301 | 302 |
- | 401 | 402 |
- +------+------+
- 6 rows in set (0.00 sec)
- MySQL> select * from test2;
- +------+------+------+
- | c1 | c2 | c3 |
- +------+------+------+
- | 100 | 101 | 102 |
- | 200 | 201 | 202 |
- | 300 | 301 | 302 |
- | 400 | 401 | 402 |
- +------+------+------+
- 4 rows in set (0.00 sec)
- MySQL> delete t2,t1 from test1 t1,test2 t2 where t1.a=t2.c2;
- Query OK, 6 rows affected (0.00 sec)
- MySQL> select * from test1;
- +------+------+
- | a | b |
- +------+------+
- | 1 | 1 |
- | 2 | 2 |
- | 2 | 2 |
- +------+------+
- 3 rows in set (0.00 sec)
- MySQL> select * from test2;
- +------+------+------+
- | c1 | c2 | c3 |
- +------+------+------+
- | 100 | 101 | 102 |
- +------+------+------+
- 1 row in set (0.00 sec)
从输出中可以看出 test1 和 test2 总计 6 条记录被删除了.
平时我们用的比较多的方式是 delete from 表名这种语法, 上面我们介绍了再 delete 后面跟上表名的用法, 大家可以在回顾一下, 加深记忆.
使用 truncate 删除
语法
truncate 表名;
drop,truncate,delete 区别
drop (删除表): 删除内容和定义, 释放空间, 简单来说就是把整个表去掉, 以后要新增数据是不可能的, 除非新增一个表.
drop 语句将删除表的结构被依赖的约束 (constrain), 触发器(trigger) 索引(index), 依赖于该表的存储过程 / 函数将被保留, 但其状态会变为: invalid.
如果要删除表定义及其数据, 请使用 drop table 语句.
truncate (清空表中的数据): 删除内容, 释放空间但不删除定义(保留表的数据结构), 与 drop 不同的是, 只是清空表数据而已.
注意: truncate 不能删除具体行数据, 要删就要把整个表清空了.
delete (删除表中的数据):delete 语句用于删除表中的行. delete 语句执行删除的过程是每次从表中删除一行, 并且同时将该行的删除操作作为事务记录在日志中保存, 以便进行进行回滚操作.
truncate 与不带 where 的 delete : 只删除数据, 而不删除表的结构(定义)
truncate table 删除表中的所有行, 但表结构及其列, 约束, 索引等保持不变.
对于由 foreign key 约束引用的表, 不能使用 truncate table , 而应使用不带 where 子句的 delete 语句. 由于 truncate table 记录在日志中, 所以它不能激活触发器.
delete 语句是数据库操作语言(dml), 这个操作会放到 rollback segement 中, 事务提交之后才生效; 如果有相应的 trigger, 执行的时候将被触发.
truncate,drop 是数据库定义语言(ddl), 操作立即生效, 原数据不放到 rollback segment 中, 不能回滚, 操作不触发 trigger.
如果有自增列, truncate 方式删除之后, 自增列的值会被初始化, delete 方式要分情况(如果数据库被重启了, 自增列值也会被初始化, 数据库未被重启, 则不变)
如果要删除表定义及其数据, 请使用 drop table 语句
安全性: 小心使用 drop 和 truncate, 尤其没有备份的时候, 否则哭都来不及
删除速度, 一般来说: drop> truncate> delete
drop | truncate | delete | |
---|---|---|---|
条件删除 | 不支持 | 不支持 | 支持 |
删除表结构 | 支持 | 不支持 | 不支持 |
事务的方式删除 | 不支持 | 不支持 | 支持 |
触发触发器 | 否 | 否 | 是 |
MySQL 系列目录
第 1 天: MySQL 基础知识
第 2 天: 详解 MySQL 数据类型(重点)
第 3 天: 管理员必备技能(必须掌握)
第 4 天: DDL 常见操作
MySQL 系列大概有 20 多篇, 喜欢的请关注一下!
来源: https://www.cnblogs.com/itsoku123/p/11475460.html