实际开发中的SQL

删除重复记录只保留其中一条

---创建一个表
CREATE TABLE `student` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '学生ID',
  `name` varchar(20) DEFAULT NULL COMMENT '学生ID',
  `age` int(10) DEFAULT NULL COMMENT '学生年龄',
  `sex` char(1) DEFAULT NULL COMMENT '性别：0女|1男',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COMMENT='学生信息表';
insert  into `student`(`id`,`name`,`sex`) values 
(1,'郭德纲',10,'1'),
(2,'宋茜',8,'0'),
(3,'宋茜',8,'0'),
(3,'郭德纲',10,'1'),
(4,'蒋方舟',3,'0');
---查询重复的name
SELECT name FROM student GROUP BY name HAVING count(name) > 1;
---查询重复的name中最小的id
SELECT min(id) FROM student GROUP BY name HAVING count(name) > 1;
---查询重复的name中非最小的id
SELECT id FROM student WHERE name in(
    SELECT name FROM student GROUP BY name HAVING count(name) > 1)
    AND id  not IN(SELECT min(id) FROM student GROUP BY name HAVING count(name) > 1);
---删除重复的name数据，只保留id最小的一条namename
DELETE FROM student WHERE id IN (
    SELECT id from (
        SELECT id FROM student WHERE name in(
            SELECT name FROM student GROUP BY namename HAVING count(namename) > 1)
            AND id  not IN(SELECT min(id) FROM student GROUP BY name HAVING count(name) > 1)
    )as t
);

若要保留最大的id数据，则把关键词 **min** 改为**max** 即可

连表更新

UPDATE user a, room b SET a.num=a.num+1 WHERE a.room_id=b.id

连表删除

DELETE user FROM user,black WHERE user.id=black.id