mysql 常见数据处理 dml

矫情吗；* 2024-04-20 22:21 45阅读 0赞

学习完，[mysql正则表达式查询][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]》

下一篇： 《mysql 变量查询》

[mysql]: https://blog.csdn.net/qq_35461948/article/details/136967431