MySQL-SQL-DML 数据操作语言

13.2 Data Manipulation Statements
https://dev.mysql.com/doc/refman/5.7/en/sql-data-manipulation-statements.html

insert

on duplicate key update

ON DUPLICATE KEY UPDATE 为Mysql特有语法，作用是当insert已经存在的记录时，执行Update

如果在INSERT语句末尾指定了 ON DUPLICATE KEY UPDATE，并且插入行后会导致在一个UNIQUE索引或PRIMARY KEY中出现重复值，则在出现重复值的行执行UPDATE；如果不会导致唯一值列重复的问题，则插入新行。
例如，如果列 a 为主键或拥有UNIQUE索引，并且包含值1，则以下两个语句具有相同的效果：

INSERT INTO TABLE (a,c) VALUES (1,3) ON DUPLICATE KEY UPDATE c=c+1;
UPDATE TABLE SET c=c+1 WHERE a=1;

insert 多条记录

INSERT INTO user(user_id, name_en, name_cn) 
VALUES
  ( 8, "llll", "小拉"),
  ( 9, "zsan", "张三"),
  (10, "lisi", "李四"),
  (27, "wwu",  "王五");

insert select 表间数据复制

数据插入时某个id实时查询获取

向 user 表插入数据，但 class_id 不能写死，需要动态去查 ‘高三八班’ 的 class_id。
可用 insert select 实现，select 语句从 class 表查，只取id结果，其他都是 user 常量。

INSERT INTO user (name, gender, class_id)
SELECT '小明', '男', id FROM class WHERE name='高三八班';

同一数据库内insert select

1、表结构相同的表，且在同一数据库（如，table1,table2)

# 完全复制
insert into table1 select * from table2;
# 不复制重复纪录
insert into table1 select distinct * from table2;
# 前五条纪录
insert into table1 select top 5 * from table2;

不同数据库间insert select

2、不在同一数据库中（如，db1 table1,db2 table2)

# 完全复制
insert into db1.table1 select * from db2.table2;
# 不复制重复纪录
insert into db1.table1 select distinct * from db2.table2;
# 前五条纪录
insert into tdb1.able1 select top 5 * from db2.table2;

表结构不相同insert select

如果表tb1和tb2只有部分字段是相同的，要实现将tb1中的部分字段导入到tb2中相对应的相同字段中，则使用以下命令：

insert into user_address(user_id, province_id, city_id, province, city)
select user_id, province_id, city_id, province, city
    from user_region
    where user_id=12345;

Mysql-两个表之间复制数据
https://blog.csdn.net/qingwuh/article/details/81350470

truncate

https://dev.mysql.com/doc/refman/5.7/en/truncate-table.html

truncate 是 ddl 语句，需要 drop 权限。

清空表truncate和delete区别

truncate table wp_comments;
delete from wp_comments;

这两者都是将wp_comments表中数据清空，不过也是有区别的，如下：

truncate 是整体删除（速度较快），delete 是逐条删除（速度较慢）。
truncate 不写服务器log，delete 写服务器log，也就是 truncate 效率比delete高的原因。
truncate 不激活trigger(触发器)
truncate 会重置 auto_increment 字段的值为 1。delete 删除后，auto_increment 依旧是接着被删除的最近的那一条记录ID加1后进行记录。
truncate 不走事务，无法回滚。delete 有事务，可以回滚。
truncate 也不会生产大量日志写入日志文件。delete 会产生大量行日志。

关于加锁：

TRUNCATE TABLE 命令会获取一个表级别的排它锁（X锁），这意味着其他会话无法同时对同一张表执行写操作，包括插入、更新和删除等操作。这样做是为了确保 TRUNCATE TABLE 命令能够安全地截断表并清空所有数据。由于它通常比 DELETE 操作更快，所以锁定时间相对较短。
DELETE 语句通常会以行级别的方式获取锁。它会逐行删除表中的数据，并在每个被删除的行上获取锁。这意味着其他会话仍然可以同时对表执行读操作，但在删除大量数据时可能会导致锁冲突和性能问题。

delete

外键导致删除失败

比如从user表中删除数据，如果还有其他表引用此行数据会导致删除失败：

[23000][1451] Cannot delete or update a parent row: a foreign key constraint fails (`uds`.`table_name`, CONSTRAINT `user_fk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`))

如果忽略数据一致性，强行删除的话，可以暂时关闭外键检查：

SET FOREIGN_KEY_CHECKS = 0;
delete from user where user_id=81681419;
SET FOREIGN_KEY_CHECKS = 1;

Can’t drop table: A foreign key constraint fails
https://stackoverflow.com/questions/11100911/cant-drop-table-a-foreign-key-constraint-fails

mysql 外键引发的删除失败
http://www.cnblogs.com/FlyAway2013/p/6864466.html

delete语句使用别名

mysql delele table语句使用别名：
语法：

delete t from table t where t.column = value;

或者不使用别名：

delete from table where column = value;

但如果使用别名，delete后一定要加别名t，否则在某些严格语法检查的情况下会报错。

删除重复行

删除所有重复行

用in

delete from table1
where user_id in (
  SELECT user_id
  FROM table1
  GROUP BY user_id
  HAVING count(*) > 1);

或者用exists

delete from table1
where exists (
  SELECT user_id
  FROM table1
  GROUP BY user_id
  HAVING count(*) > 1);

mariadb中都会报错：
[HY000][1093] Table ‘table1’ is specified twice, both as a target for ‘DELETE’ and as a separate source for data
因为同时进行查询和修改。

解决方法：查询语句外面再套一个查询形成一张临时表

delete from drive_booking
where user_id in (
    select *
    from (
      SELECT user_id
      FROM drive_booking
      GROUP BY user_id
      HAVING count(*) > 1) as temp_table
);

删除重复行只保留id最大的

删除 user_id 重复的数据，保留id最大的

DELETE FROM drive_booking
WHERE id NOT IN (
    SELECT MAX(id)
    FROM drive_booking
    GROUP BY user_id
);

报错 [HY000][1093] You can’t specify target table ‘drive_booking’ for update in FROM clause
https://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause

原因：
在MySQL中不允许在 UPDATE 或 DELETE 语句中使用子查询来操作同一个表

改为：

DELETE FROM drive_booking
WHERE id NOT IN (
    select mid from (
        SELECT MAX(id) mid
        FROM drive_booking
        GROUP BY user_id
    ) as a
);

重复行只保留一条

删除表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断，只留有 rowid 最小的记录

delete from people
where peopleId in (
  select peopleId
  from people
  group by peopleId
  having count(peopleId) > 1
)
and rowid not in (
  select min(rowid)
  from people
  group by peopleId
  having count(peopleId )>1
);

上面这条语句在mysql中执行会报错，因为同时对表进行查询和更新，mysql不支持这种方式。oracel和msserver都支持这种方式。

解决方法：查询语句外面再套一个查询形成一张临时表
删除 user_region 表中 user_id 重复的数据，只保留id最小的

delete from user_region
where user_id in (
  select user_id
  from (
    select user_id
    from user_region
    GROUP BY user_id
    HAVING count(*)>1
  ) as a
)
and id not in (
  select id
  from (
    select min(id) as id
    from user_region
    GROUP BY user_id
    having count(*)>1
  ) as b
);

问：为什么不能只用 not in min(id) 来删除呢？
答：因为这样会多删除，会把 user_id 不重复的也删掉，非常危险，使用 not in 的话必须加user_id in 条件.

当表中没有id自增主键时，有时候需要根据更新时间排重，但不是很精确。
删除 user_employee_info 表中 user_id 重复的数据，保留 update_time 最大的

select * from user_employee_info
where user_id in (
  select user_id
  from (
    select user_id
    from user_employee_info
    group by user_id
    having count(*) > 1
  ) as a
)
and update_time not in (
  select update_time
  from (
    select max(update_time) as update_time
    from user_employee_info
    group by user_id
    having count(*)>1
  ) as b
);

这个sql对于两条user_id 相同且 update_time 相同的数据就删除不了。

例如 user_address 表中有 user_id 和地址类型 type，一个user同一类型地址只能存一个，假如表上没有唯一约束造成了重复数据，删除重复数据，保留id最大的

delete from user_address
where user_id in (
  select user_id
  from (
    select user_id
    from user_address
    GROUP BY user_id, type
    HAVING count(*)>1
  ) as a
)
and id not in (
  select id
  from (
    select max(id) as id
    from user_address
    GROUP BY user_id, type
    having count(*)>1
  ) as b
);

delete join

mysql 支持 delete 语句中 join 连表查询

DELETE T1, T2
FROM T1
INNER JOIN T2 ON T1.key = T2.key
WHERE condition;

delete t1 from table1 t1 left join table2 t2 on t1.t2_id=t2.id
where t1.id in (1,2,3)
and (t2.status='disable' or t2.id is null);

alter table 重建表释放磁盘

有大量增删改操作的表在 delete 删除数据后磁盘空间也无法释放，通过 alter table t_name engine=Innodb; 重建表释放磁盘空间。

内部流程：
建立临时文件tmp_file，把表的B+树存储到临时文件中。若此时有对表的操作，则会记录在row log文件中。
把数据从原表全部刷到临时文件后，此时临时文件的数据就跟原表的数据一致。
最后用临时文件替换表A的数据文件。

update

update多列

update user u
set u.name = '张三', u.mobile = '13613661366'
where u.id = 23;

update replace 替换更新

把 name 里的张都替换成王

update table1
set name = replace(name, '张', '王‘)
where name like '%张%';

还可以结合 regexp 操作符正则匹配，把姓张的都替换成王

update table1
set name = replace(name, '张', '王‘)
where name regexp '^张';

update join 跨表关联更新

在 MySQL 中, 我们也可以在 UPDATE 语句中使用 JOIN 子句执行跨表更新

UPDATE T1, T2,
[INNER JOIN | LEFT JOIN] T1 ON T1.C1 = T2. C1
SET T1.C2 = T2.C2,
    T2.C3 = expr
WHERE condition

当你希望更新一批值，且值是通过select条件查询出来时
解决思路 1：
使用 INNER JOIN (最简洁)

UPDATE A a INNER JOIN B b ON b.key_id = a.key_id
SET a.val = 2 
WHERE b.satisfy = 1

解决思路 2：

UPDATE A a, (SELECT A.id from A
             LEFT JOIN B ON B.key_id= A.key_id
             WHERE B.satisfy = 1) b
SET a.val = 2
WHERE a.id = b.id

根据无关表 table2 中的某些信息去更新 table1 表

UPDATE `table1` AS `dest`,
    ( SELECT * FROM `table2` WHERE `id` = x ) AS `src`
SET `dest`.`col1` = `src`.`col1`
WHERE `dest`.`id` = x ;

https://stackoverflow.com/questions/1262786/mysql-update-query-based-on-select-query

例如：

update user_info as ui join user as u on ui.user_id = u.id
set ui.ext_info='', ui.address='beijing'
where u.name like '%ST' and ui.user_id in (
  SELECT * FROM (
         SELECT user_id
         FROM user_info
         WHERE ext_info like '%特殊%'
       ) AS special_user
);

update app_user_relationship as aur join user_account_info as uai on aur.account1_id=uai.account_id
set aur.user1_id=uai.user_id
where aur.user1_id != uai.user_id;

-- join 两个表
UPDATE user u join user_address ua on u.id=ua.user_id join user_address ua2 on ua.pid=ua2.id
set u.city_id=cr2.id, u.province_id=cr2.pid
where ua.satisfy = 1;

update case when 条件表达式更新

需求：
user 表的 identity 字段是当前用户身份，新加的字段
user_role_transaction 保存的是用户身份变更历史，每变动一次插入一条新数据
之前只有 user_role_transaction 历史表，user 表中没有 identity 字段，现在刚加上，要用 user_role_transaction 的 user_identity 字段初始化 user 表的 identity 字段，规则是：
对于 user_id 在 user_role_transaction 表中的 user，把 user 表的 identity 字段设置为此 user_id 在 user_role_transaction 表中的最新一个值（即id最大的）
对于 user_id 不在 user_role_transaction 表中的 user，把 user 表的 identity 字段设置为固定值 1

sql 如下：

UPDATE user u left join user_role_transaction urt on urt.user_id = u.id
set u.identity = case when urt.id is not null then  urt.user_identity else 1 end
WHERE urt.id is null or (urt.user_id = u.id AND NOT exists(
    SELECT *
    FROM user_role_transaction urt2
    WHERE urt2.user_id = urt.user_id AND urt2.id > urt.id)
);

解释：
使用了 update join 根据查询条件更新，由于 user 表中的数据有可能不在 user_role_transaction 表中，使用了 left join 左外连接来保留 user 中非关联数据
筛选条件是 urt.id 为空（即左外关联后 user_role_transaction 表对应列是空的，即不在 user_role_transaction 表中的 user），或者关联后 user_role_transaction 中 id 最大的（这里用了个 not exists 筛选没有比他 id 更大的）
set 赋值语句使用了 case when，对于在 user_role_transaction 表中的 user（即 urt.id is not null）设置为选出的 urt.user_identity，否则（即不在 user_role_transaction 表中的 user）设置为 1

specified twice both target and source

当要更新的目标表同时也在查询条件中时，会报错：
Table name is specified twice, both as a target for ‘UPDATE’ and as a separate source for data in mysql
解决方法是把作为条件的目标表查询为一个中间表。

例如：

UPDATE MYTABLE
SET COL=COL+1
WHERE ID IN (
    SELECT ID
    FROM MYTABLE
    WHERE OTHERCOL=0
  );

DELETE FROM MYTABLE
WHERE ID IN (
  SELECT ID
  FROM MYTABLE
  WHERE OTHERCOL=0
);

改为：

UPDATE MYTABLE
SET COL=COL+1
WHERE ID IN (
  SELECT *
  FROM (
    SELECT ID
    FROM MYTABLE
    WHERE OTHERCOL=0
  ) AS TEMP
);

DELETE FROM MYTABLE
WHERE ID IN (
  SELECT *
  FROM (
    SELECT ID
    FROM MYTABLE
    WHERE OTHERCOL=0
  ) AS TEMP
);

例如：
FROM manager AS m2
改为
FROM (select * from manager) AS m2

Mysql: Table ‘name’ is specified twice, both as a target for ‘UPDATE’ and as a separate source for data
https://www.cnblogs.com/liusonglin/p/4387543.html

Table is specified twice, both as a target for ‘UPDATE’ and as a separate source for data in mysql
https://stackoverflow.com/questions/44970574/table-is-specified-twice-both-as-a-target-for-update-and-as-a-separate-source

Every derived table must have its own alias

意思是每个派生出来的表（或者叫子查询）必须有一个自己的别名。
一般是在多表查询或者子查询的时候会出现这个错误，因为在嵌套查询中，子查询的结果是作为一个派生表给上一级进行查询，所以子查询的结果必须有一个别名。

例如

SELECT id
FROM (
    SELECT id
    FROM user
);

会报错 Every derived table must have its own alias.
改为

SELECT id
FROM (
    SELECT id
    FROM user
) as temp;

在子查询的后面增加一句 as temp，相当于给子查询的结果集派生表取别名为temp，问题就解决了。

但是下面这条sql就不会报错

-- 删除地址表中user_id不存在的脏数据
delete from user_address
where user_id not in (select id from user);

What is the error “Every derived table must have its own alias” in MySQL?
https://stackoverflow.com/questions/1888779/what-is-the-error-every-derived-table-must-have-its-own-alias-in-mysql

Mysql错误：Every derived table must have its own alias
https://chenzhou123520.iteye.com/blog/2041684

update返回值与useAffectedRows

比如如下mybatis sql语句：

@Update({"UPDATE user SET name = null WHERE id = #{id}"})
void updateUserProfileById(@Param("id") long id);

默认情况下，mybatis 的 update 操作的返回值是 matched 的记录数，并不是受影响的记录数。
严格意义上来将，这并不是 mybatis 的返回值，mybatis 仅仅只是返回的数据库连接驱动（通常是 JDBC ）的返回值

通过对 JDBC URL 显式的指定 useAffectedRows选项，我们将可以得到受影响的记录的条数：
jdbc:mysql://${jdbc.host}/${jdbc.db}?useAffectedRows=true

那么有没有办法让 mybatis 的 update 操作的返回值是受影响的行数呢。因为我们业务逻辑中有时会根据这个返回值做业务判断。答案当然是有的。
修改数据库链接配置为：增加了 useAffectedRows 字段信息。

JDBC默认返回的是符合的行数Rows matched，如果想返回修改过（ Changed）的行数，需要使用useAffectedRows参数
useAffectedRows的含义：是否用受影响的行数替代查找到的行数来返回数据

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/ssm?useAffectedRows=true
jdbc.username=root
jdbc.password=123456

mybatis 中的 update 返回值你真的明白吗
https://www.jianshu.com/p/80270b93082a

聊聊Mybatis Update操作返回值
https://notes.wanghao.work/2017-09-06-%E8%81%8A%E8%81%8AMybatis-Update%E6%93%8D%E4%BD%9C%E8%BF%94%E5%9B%9E%E5%80%BC.html

当前位置 : 首页 » 文章分类 : 开发 » MySQL-SQL-DML 数据操作语言