Mysql关联更新、删除重复、复制

准备数据

-- 1 test_user 表
DROP TABLE IF EXISTS `test_user`;
CREATE TABLE `test_user`  (
  `id` int(11) NOT NULL,
  `name` varchar(255)  DEFAULT NULL COMMENT '姓名',
  `phone` varchar(11)  DEFAULT NULL COMMENT '号码',
  `create_time` datetime(0) NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP(0),
  PRIMARY KEY (`id`) USING BTREE
);
INSERT INTO `test_user` VALUES (1, '11', NULL, '2020-06-30 20:49:39');
INSERT INTO `test_user` VALUES (2, '22', NULL, '2020-06-30 20:49:53');
INSERT INTO `test_user` VALUES (3, '33', NULL, '2020-06-29 20:49:55');
INSERT INTO `test_user` VALUES (4, '33', NULL, '2020-07-02 20:52:47');
INSERT INTO `test_user` VALUES (5, '22', NULL, '2020-07-02 20:53:15');
-- 2 student表
​
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `stu_name` varchar(255)  DEFAULT NULL COMMENT '姓名',
  `stu_phone` varchar(11)  DEFAULT NULL COMMENT '联系方式',
  `sex` int(1) NULL DEFAULT NULL COMMENT '性别',
  `u_id` int(11) NULL DEFAULT NULL COMMENT '用户id',
  PRIMARY KEY (`id`) USING BTREE
);
INSERT INTO `student` VALUES (1, 'x', '111', 1, 1);

获取各用户名中最新一条

select * from test_user t1
where (select count(1) 
from test_user t2 where t1.name=t2.name and t2.create_time>=t1.create_time ) <=1;

删除重复数据，保留第一个

这里需要注意将查询出的结果再包一层，否则会报错：[HY000][1093] You can’t specify target table ‘test_user’ for update in FROM clause

delete from test_user
where id in (select t.id from (select id from test_user where name in
 (select name  from test_user group by name   having count(name) > 1) 
 and  id not in (select max(id) from test_user group by name   having count(name)>1)) as t)

关联更新：将test_user的name更新到student中

update student stu inner join test_user u on stu.u_id = u.id
set stu_name = u.name where u.id = 1;

复制数据:从test_user复制数据到student

insert into student(stu_name, stu_phone, u_id) select name,phone,id from test_user

replace into

REPLACE就是可以将DELETE和INSERT合二为一，形成一个原子操作。存在就会先删除再插入。需要根据主键或唯一索引操作

replace into test_user(id, name, phone) values (1,'小米','18248503302');

INSERT…ON DUPLICATE KEY UPDATE存在则更新

不同于replace into,该sql是存在则更新，不存在就插入。

insert into  test_user(id, name, phone) values (2,'小红','13348503302') 
on duplicate key update name ='小红',phone='13348503302';

mysql 查询如何区分大小写

mysql 查询条件默认是不区分大小写的，但有时候我们需要区分，这时可以加 binary。

select * from RES_POOL_INFO where binary db_type = 'oracle';