mysql 常见数据处理 dml

2024-04-17 22:06:57

学习完，mysql正则表达式查询，把常见的数据处理，做一个汇总，便于查看。

数据操纵语言（Data Manipulation Language, DML）。

1，新增数据：

1，单个插入：

insert into table (id, value) 
values(1,2);

2，多个插入：

insert into table (id, value) 
values(1,2),(2,3),(3,4)...;

3，复制表格插入：

0，只复制表结构

语法：

CREATE TABLE 新表 SELECT * FROM 旧表 WHERE 1=2;
-- 或者
CREATE TABLE 新表 LIKE 旧表;

例子：

CREATE TABLE staff LIKE employee;

1，指定字段：

insert into table2(field1,field2,…) 
select value1,value2,… from table1;

2，复制全部：

insert into table2 select * from table1;

3，复制部分数据：

insert into table2 select * from table1 where 条件 ;

比如：

INSERT into WARNING_NOTICE(mod, is_sms, is_mail)  
SELECT mod, '0BT', '0BT' FROM ws_open_model WHERE state = 'A';

2，修改数据：

1，更新单个字段：

 UPDATE open_transfer_link SET on_after_create_type = 'java'  
 WHERE link_id = 8;

2，更新多个字段：

UPDATE open_transfer_link SET on_after_create_type = 'java', 
 on_after_create = "processService.turnAfter()"
WHERE link_id = 8;

3，联合更新：

join

 UPDATE table_a a
LEFT JOIN
table_b  b
ON a.id= b.id
SET a.name = b.name
WHERE a.name != '';

直接关联的

-- 写一起的方式
UPDATE staff sf, tenant t, role_assign ra 
SET ra.effect_type = t.tenant_level, ra.effect_id = t.tenant_id 
WHERE sf.staff_id = t.create_staff_id
AND sf.tenant_id = t.tenant_id;

修改这个，mysql支持直接关联的，update直接带其它表的，pg的就不支持，平时写的时候，用join的方式去写。

用子查询的方式：

把目标值查出来。用in的方式：

UPDATE fc_basic  fb SET  fb.timeout = 0
WHERE fb.id IN (SELECT fb.id FROM  sc_table sc
WHERE fb.id = sc.id
AND fb.state = 'F' 
AND fb.timeout = 1) ;

4，数据表修改优化

MySQL 的 ALTERTABLE 操作性能对大表来说是个问题。MySQL 执行修改表结构操作的方法是，用新的结构创建一个空表，从旧表中查出所有数据插入新表，然后删除旧表。

这一操作需要花费大量时间，如果内存不足而表数据很大，并且索引较多的情况，会造成长时间的锁表。有极端的情况，有些 ALTERTABLE 操作需要花费数个小时甚至数天才能完成。

这里推荐两种小技巧：

1，先把数据库拷贝到一台非生产服务器上，在上面做修改表操作，此时的修改不会影响生产库。修改完毕以后在做数据库的切换，把非生产数据库切换成生产库。不过需要注意的时候，在做表结构修改的时候，生产库会生成一些数据。这里需要通过脚本根据时间区间导入这部分数据。

2，“影子拷贝”，即生成一张表结构相同的不同名新数据表（更改数据结构以后的表）。

然后导入原表的数据到新表，导入成功以后停止数据库，修改原表和新表的名字，最终将数据访问指向新表。

在运行正常以后，将原表删除。这里有现成的工具可以协助完成上述操作，“online schema change”,”openark toolkit”

如果只是删除或者更改某一列的默认值，那么直接可以使用 Alert table modify column 和 Alert table alert column 来实现。

3，删除数据：

1，删除全部数据：

TRUNCATE TABLE 表名：是全删，主键对应的字段会重新置为1，全表删除效率比delete要快。

1，删除数据，但id不变：

delete from 表名;

2，删除数据，id重置：

TRUNCATE TABLE 表名;

2，根据条件删除数据：

DELETE FROM person WHERE id=11;

删除数据一定要注意，把条件放到一行。换行的，如果没选到，容易造成误删。

注意：

疑问1:插入记录时可以不指定字段名称吗?

不管使用哪种 INSERT语法，都必须给出 VALUES的正确数目。如果不提供字段名，则必须给每个字段提供一个值，否则将产生一条错误消息。如果要在INSERT操作中省略某些字段，这些字段需要满足一定条件：该列定义为允许空值;或者表定义时给出默认值，如果不给出值，将使用默认值。

疑问2：更新或者删除表时必须指定WHERE子句吗?

在前面章节中可以看到，所有的UPDATE和DELETE 语句全都在WHERE子句中指定了条件。如果省略WHERE子句，则UPDATE 或DELETE将被应用到表中所有的行。因此，除非确实打算更新或者删除所有记录，否则要注意使用不带WHERE子句的UPDATE或DELETE语句。建议在对表进行更新和删除操作之前，使用SELECT 语句确认需要删除的记录，以免造成无法挽回的结果。

总结：

使用ddl操作数据的时候，尤其主要删除数据。尤其是重要环境！养成好习惯，以免误操作，带来大麻烦。

上一篇：《mysql 正则表达式查询》

下一篇：《mysql 变量查询》

码农公寓