mysql多表更新与多表删除

准备数据:
员工表:

CREATE TABLE `employee` (
  `id` int(11) NOT NULL,
  `employee_name` varchar(255) DEFAULT NULL,
  `dept_id` int(11) DEFAULT NULL,
  `salary` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `employee` VALUES ('1', 'zhnagsan', '1', '10000');
INSERT INTO `employee` VALUES ('2', 'lisi', '2', '13000');
INSERT INTO `employee` VALUES ('3', 'wangwu', '2', '12000');
INSERT INTO `employee` VALUES ('4', 'zhaoliu', '3', '12000');

部门表:

DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept` (
  `id` int(11) NOT NULL,
  `dept_name` varchar(255) DEFAULT NULL,
  `leader_name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `dept` VALUES ('1', '研发', 'zhangsan');
INSERT INTO `dept` VALUES ('2', '财务', 'lisi');
INSERT INTO `dept` VALUES ('3', '运维', 'zhaoliu');
INSERT INTO `dept` VALUES ('4', '安全', '');

查看量表关联后的结果:

select * from employee a left join dept b 
on a.dept_id = b.id  

多表更新:
update tableA ,tableB set xxx where xxxx
或者
update tableA inner/left/right join tableB on xxxx set xxxxx where xxx;

需求:
王五升值成为部门领导,并且涨了工资.
更改wangwu的工资为15000并且将王五所在部门(2)的领导更改为wangwu

update employee a left join dept b 
on a.dept_id = b.id 
set a.salary=15000, b.leader_name='wangwu'
where  a.employee_name='wangwu';

select * from employee a left join dept b 
on a.dept_id = b.id  ;

结果:

多表删除:
delete a,b from table1 as a,table2 as b where xxxx
或者
delete a,b from table1 as a inner/left/right join table2 as b on xxx where xxx

删除运维部门以及运维部门中的员工

delete a,b from dept a left join employee b on a.id=b.dept_id where a.id=3

结果

同时删除两张表的数据:

delete a,b from employee a , dept b;
或者
delete a,b from employee a join dept  b;