MySQL中复杂的增删改语句

目录

一、INSERT 插入

二、DELETE 删除

三、UPDATE 修改

MySql学习专栏

1. MySQL基础架构详解

2. MySQL索引底层数据结构与算法

3. MySQL5.7开启binlog日志，及数据恢复简单示例

4. MySQL日志模块

5. MySQL的MVCC及实现原理

6. MySQL索引优化

7. MySQL——通过EXPLAIN分析SQL的执行计划

8. MySQL执行语句性能优化

9. MySQL中的关联查询(内连接，外连接，自连接)

10. MySQL中复杂的增删改语句

11. 面试必问的 MySQL，你懂了吗？

一、INSERT 插入

需求：把一个表某个字段内容复制到另一张表的某个字段

表结构和插入SQL如下

create TABLE table1
(
id int(11) Not null
AUTO_INCREMENT,
a varchar(10),
b varchar(10),
c varchar(10),
PRIMARY KEY ("id")
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='table1';
INSERT INTO `table1`(`id`, `a`, `b`, `c`) VALUES (1, '赵', 'zhao', '90');
INSERT INTO `table1`(`id`, `a`, `b`, `c`) VALUES (2, '钱', 'qian', '100');
INSERT INTO `table1`(`id`, `a`, `b`, `c`) VALUES (3, '孙', 'sun', '80');
INSERT INTO `table1`(`id`, `a`, `b`, `c`) VALUES (4, '李', 'li', NULL);

方案一

Insert into table2(field1,field2,…) select value1,value2,… from table1
insert into table2 (id,a,b,c) select id,a,b,c from table1

表结构相同

insert into table2 select * from table1

注意
（1）要求目标表table2必须存在，并且字段field,field2…也必须存在

（2）注意table2的主键约束，如果Table2有主键而且不为空，则 field1， field2…中必须包括主键

（3）注意语法，不要加values，和插入一条数据的sql混了，不要写成:

Insert into Table2(field1,field2,…) values (select value1,value2,… from Table1)

复制表包含数据

create table table3 as select * from table2

只复制表结构到新表

create table table4 as select * from table2 where 1= 2

二、DELETE 删除

需求：删除重复的数据，只保留最小的那一份数据

添加测试重复数据

INSERT INTO `table1`(`id`, `a`, `b`, `c`) VALUES (5, '赵', 'zhao', '35');
INSERT INTO `table1`(`id`, `a`, `b`, `c`) VALUES (7, '孙', 'sun', '43');

删除重复数据SQL思路

1. 先查询到重复数据
2. 在根据table1把相同的字段ID查询出来
3. 过滤最小ID那份数据
4. 在根据表关联级别删除

SELECT
b.id FROM ( SELECT id FROM table1 WHERE a IN
( SELECT a FROM table1 GROUP BY a HAVING count( a ) > 1 ) ) b 

select min(id) id from table1 GROUP BY a HAVING count(a) > 1;

DELETE
FROM
table1
WHERE
id IN (
SELECT
b.id FROM ( SELECT id FROM table1 WHERE a IN
( SELECT a FROM table1 GROUP BY a HAVING count( a ) > 1 ) ) b
)
AND id NOT IN
( SELECT id FROM ( SELECT min( id ) id FROM table1 GROUP BY a HAVING count( a ) > 1 ) c )

三、UPDATE 修改

需求：把员工表部门名称字段根据部门表名称进行更新

表结构数据

CREATE TABLE "employee" (
"id" int(11) NOT NULL AUTO_INCREMENT,
"user_name" varchar(32) CHARACTER SET utf8 NOT NULL DEFAULT '' COMMENT '姓名',
"age" int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
"gender" tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '性别 (0-男；1-女；2：未知)',
"dept_id" int(11) DEFAULT NULL COMMENT '部门ID',
"boss_id" int(11) DEFAULT NULL COMMENT 'bossId',
"position" varchar(32) CHARACTER SET utf8 NOT NULL DEFAULT '' COMMENT '职位',
"create_time" datetime DEFAULT NULL COMMENT '创建时间',
"status" tinyint(1) DEFAULT NULL COMMENT '状态',
"dept_name" varchar(32) DEFAULT NULL,
PRIMARY KEY ("id")
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=REDUNDANT;
CREATE TABLE "dept" (
"id" int(3) NOT NULL AUTO_INCREMENT,
"dept_name" varchar(20) DEFAULT NULL,
PRIMARY KEY ("id")
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COMMENT='部门表';
INSERT INTO `employee`(`id`, `user_name`, `age`, `gender`, `dept_id`, `boss_id`, `position`, `create_time`, `status`) VALUES (1, 'change', 28, 0, 1, NULL, 'java开发', '2021-02-04 13:21:37', 0);
INSERT INTO `employee`(`id`, `user_name`, `age`, `gender`, `dept_id`, `boss_id`, `position`, `create_time`, `status`) VALUES (2, 'Lilei', 27, 0, 2, 2, 'payton开发', '2021-02-03 13:22:16', 1);
INSERT INTO `employee`(`id`, `user_name`, `age`, `gender`, `dept_id`, `boss_id`, `position`, `create_time`, `status`) VALUES (3, 'Bill', 23, 0, 3, 3, 'PHP开发', '2021-01-06 13:22:49', 2);
INSERT INTO `employee`(`id`, `user_name`, `age`, `gender`, `dept_id`, `boss_id`, `position`, `create_time`, `status`) VALUES (4, 'Liping.Zou', 28, 0, 4, 4, 'java开发', '2021-02-04 20:46:02', 2);
INSERT INTO `employee`(`id`, `user_name`, `age`, `gender`, `dept_id`, `boss_id`, `position`, `create_time`, `status`) VALUES (5, 'sundy', 28, 0, 5, 5, 'java开发', '2021-02-04 20:46:02', 0);
INSERT INTO `employee`(`id`, `user_name`, `age`, `gender`, `dept_id`, `boss_id`, `position`, `create_time`, `status`) VALUES (6, 'smile', 30, 0, NULL, 6, 'java开发', '2021-02-04 20:46:02', 1);
INSERT INTO `dept`(`id`, `dept_name`) VALUES (1, '软件开发部门');
INSERT INTO `dept`(`id`, `dept_name`) VALUES (2, '市场部门');
INSERT INTO `dept`(`id`, `dept_name`) VALUES (3, '运营部门');
INSERT INTO `dept`(`id`, `dept_name`) VALUES (4, '人力资源部门');
INSERT INTO `dept`(`id`, `dept_name`) VALUES (6, '总裁部门');

update employee e inner join dept d set e.dept_name = d.dept_name where e.dept_id = d.id;

最后

以上就是老实大象为你收集整理的MySQL中复杂的增删改语句的全部内容，希望文章能够帮你解决MySQL中复杂的增删改语句所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。