1、多表更新

UPDATEtable1 {[INNER]JOIN | {LEFT | RIGHT}[OUTER]JOIN} table2 ON conditional_ expr SET col1 = {expr1 | DEFAULT}[,col2={expr2 | DEFAUL}]...[WHERE where_ condition]

/*今天身份证号为210210199901012222的读者将超限的图书20151101归还，根据描述实现如下需求：1、更新借阅信息表，将借阅状态（status）更新为‘是’。2、更新罚款记录信息表，更新实际还书日期和罚款金额，罚款金额为每超出一天扣0.2元。3、同时更新读者信息表的余额。（在余额中扣除罚款金额）*/UPDATE readerfee t1JOIN readerinfo t2 ON t1.card_id = t2.card_idSET actual_return_date = sysdate(), book_fee = datediff(sysdate(), return_date) * 0.2, balance = balance - book_feeWHERE t1.book_id = 20151101AND t1.card_id = ‘210210199901012222‘;

2、多表删除

DELETE table1[.*], table2[.*]FROM table1 {[INNER]JOIN | {LEFT| RIGHT}[OUTER]JOIN} table2 ON conditional_ expr[WHERE where_ condition]

-- 1、由于业务需求，需要删除图书类别表中在图书信息表中没有图书记录的类别。SELECT book_id, book_name, categoryFROM bookcategory_bak t1LEFT JOIN bookinfo_bak t2 ON t1.category_id = t2.book_category_idWHERE parent_id <> 0;
DELETE t1FROM bookcategory_bak t1LEFT JOIN bookinfo_bak t2 ON t1.category_id = t2.book_category_idWHERE parent_id <> 0AND book_id IS NULL;SELECT *FROM bookcategory_bak;-- 2、需要删除图书类别表的编程语言的类别，以及图书信息表中关于编程语言的图书记录。SELECT book_id, book_name, category_id, categoryFROM bookcategory_bak t1INNER JOIN bookinfo_bak t2 ON t1.category_id = t2.book_category_id;DELETE t1, t2FROM bookcategory_bak t1INNER JOIN bookinfo_bak t2 ON t1.category_id = t2.book_category_idWHERE t1.category_id = 3;

参考文档：https://blog.csdn.net/zdw19861127/article/details/80540124

3、复制一张表：

CREATE TABLE table1_bak
 AS
SELECT *FROM table1;

4、将查询的结果插入到一张表中：

insert into table1 select * from table1_bak;insert into table1(字段1，字段2） select 字段m,字段n from table2;