当前位置:Gxlcms > 数据库问题 > mysql多表更新删除

mysql多表更新删除

时间:2021-07-01 10:21:17 帮助过:6人阅读

table1 {[INNER]JOIN | {LEFT | RIGHT}[OUTER]JOIN} table2 ON conditional_ expr SET col1 = {expr1 | DEFAULT} [,col2={expr2 | DEFAUL}]... [WHERE where_ condition]
  1. <span style="color: #008080;">/*</span><span style="color: #008080;">
  2. 今天身份证号为210210199901012222的读者将超限的图书20151101归还,根据描述实现如下需求:
  3. 1、更新借阅信息表,将借阅状态(status)更新为‘是’。
  4. 2、更新罚款记录信息表,更新实际还书日期和罚款金额,罚款金额为每超出一天扣0.2元。
  5. 3、同时更新读者信息表的余额。(在余额中扣除罚款金额)
  6. </span><span style="color: #008080;">*/</span>
  7. <span style="color: #0000ff;">UPDATE</span><span style="color: #000000;"> readerfee t1
  8. </span><span style="color: #808080;">JOIN</span> readerinfo t2 <span style="color: #0000ff;">ON</span> t1.card_id <span style="color: #808080;">=</span><span style="color: #000000;"> t2.card_id
  9. </span><span style="color: #0000ff;">SET</span> actual_return_date <span style="color: #808080;">=</span><span style="color: #000000;"> sysdate(),
  10. book_fee </span><span style="color: #808080;">=</span> <span style="color: #ff00ff;">datediff</span>(sysdate(), return_date) <span style="color: #808080;">*</span> <span style="color: #800000; font-weight: bold;">0.2</span><span style="color: #000000;">,
  11. balance </span><span style="color: #808080;">=</span> balance <span style="color: #808080;">-</span><span style="color: #000000;"> book_fee
  12. </span><span style="color: #0000ff;">WHERE</span><span style="color: #000000;">
  13. t1.book_id </span><span style="color: #808080;">=</span> <span style="color: #800000; font-weight: bold;">20151101</span>
  14. <span style="color: #808080;">AND</span> t1.card_id <span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">210210199901012222</span><span style="color: #ff0000;">‘</span>;

2、多表删除

  1. <span style="color: #0000ff;">DELETE</span> table1<span style="color: #ff0000;">[</span><span style="color: #ff0000;">.*</span><span style="color: #ff0000;">]</span>, table2<span style="color: #ff0000;">[</span><span style="color: #ff0000;">.*</span><span style="color: #ff0000;">]</span>
  2. <span style="color: #0000ff;">FROM</span> table1 {<span style="color: #ff0000;">[</span><span style="color: #ff0000;">INNER</span><span style="color: #ff0000;">]</span><span style="color: #808080;">JOIN</span> <span style="color: #808080;">|</span> {<span style="color: #808080;">LEFT|</span> <span style="color: #808080;">RIGHT</span>}<span style="color: #ff0000;">[</span><span style="color: #ff0000;">OUTER</span><span style="color: #ff0000;">]</span><span style="color: #808080;">JOIN</span>} table2 <span style="color: #0000ff;">ON</span><span style="color: #000000;"> conditional_ expr
  3. </span><span style="color: #ff0000;">[</span><span style="color: #ff0000;">WHERE where_ condition</span><span style="color: #ff0000;">]</span>
  1. <span style="color: #008080;">--</span><span style="color: #008080;"> 1、由于业务需求,需要删除图书类别表中在图书信息表中没有图书记录的类别。</span>
  2. <span style="color: #0000ff;">SELECT</span><span style="color: #000000;">
  3. book_id,
  4. book_name,
  5. category
  6. </span><span style="color: #0000ff;">FROM</span><span style="color: #000000;">
  7. bookcategory_bak t1
  8. </span><span style="color: #808080;">LEFT</span> <span style="color: #808080;">JOIN</span> bookinfo_bak t2 <span style="color: #0000ff;">ON</span> t1.category_id <span style="color: #808080;">=</span><span style="color: #000000;"> t2.book_category_id
  9. </span><span style="color: #0000ff;">WHERE</span><span style="color: #000000;">
  10. parent_id </span><span style="color: #808080;"><></span> <span style="color: #800000; font-weight: bold;">0</span><span style="color: #000000;">;
  11. <br>
  12. </span><span style="color: #0000ff;">DELETE</span><span style="color: #000000;"> t1
  13. </span><span style="color: #0000ff;">FROM</span><span style="color: #000000;">
  14. bookcategory_bak t1
  15. </span><span style="color: #808080;">LEFT</span> <span style="color: #808080;">JOIN</span> bookinfo_bak t2 <span style="color: #0000ff;">ON</span> t1.category_id <span style="color: #808080;">=</span><span style="color: #000000;"> t2.book_category_id
  16. </span><span style="color: #0000ff;">WHERE</span><span style="color: #000000;">
  17. parent_id </span><span style="color: #808080;"><></span> <span style="color: #800000; font-weight: bold;">0</span>
  18. <span style="color: #808080;">AND</span> book_id <span style="color: #0000ff;">IS</span> <span style="color: #0000ff;">NULL</span><span style="color: #000000;">;
  19. </span><span style="color: #0000ff;">SELECT</span>
  20. <span style="color: #808080;">*</span>
  21. <span style="color: #0000ff;">FROM</span><span style="color: #000000;">
  22. bookcategory_bak;
  23. </span><span style="color: #008080;">--</span><span style="color: #008080;"> 2、需要删除图书类别表的编程语言的类别,以及图书信息表中关于编程语言的图书记录。</span>
  24. <span style="color: #0000ff;">SELECT</span><span style="color: #000000;">
  25. book_id,
  26. book_name,
  27. category_id,
  28. category
  29. </span><span style="color: #0000ff;">FROM</span><span style="color: #000000;">
  30. bookcategory_bak t1
  31. </span><span style="color: #0000ff;">INNER</span> <span style="color: #808080;">JOIN</span> bookinfo_bak t2 <span style="color: #0000ff;">ON</span> t1.category_id <span style="color: #808080;">=</span><span style="color: #000000;"> t2.book_category_id;
  32. </span><span style="color: #0000ff;">DELETE</span><span style="color: #000000;"> t1,
  33. t2
  34. </span><span style="color: #0000ff;">FROM</span><span style="color: #000000;">
  35. bookcategory_bak t1
  36. </span><span style="color: #0000ff;">INNER</span> <span style="color: #808080;">JOIN</span> bookinfo_bak t2 <span style="color: #0000ff;">ON</span> t1.category_id <span style="color: #808080;">=</span><span style="color: #000000;"> t2.book_category_id
  37. </span><span style="color: #0000ff;">WHERE</span><span style="color: #000000;">
  38. t1.category_id </span><span style="color: #808080;">=</span> <span style="color: #800000; font-weight: bold;">3</span>;

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

3、复制一张表:

  1. <span style="color: #0000ff;">CREATE</span> <span style="color: #0000ff;">TABLE</span> table1_bak<br> <span style="color: #0000ff;">AS<br></span><span style="color: #0000ff;">SELECT</span> <span style="color: #808080;">*</span>
  2. <span style="color: #0000ff;">FROM </span><span style="color: #000000;">table1;</span>

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

  1. <span style="color: #0000ff;">insert</span> <span style="color: #0000ff;">into</span> table1 <span style="color: #0000ff;">select</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">from</span><span style="color: #000000;"> table1_bak;
  2. </span><span style="color: #0000ff;">insert</span> <span style="color: #0000ff;">into</span> table1(字段1,字段2) <span style="color: #0000ff;">select</span> 字段m,字段n <span style="color: #0000ff;">from</span> table2;

 

mysql多表更新删除

标签:targe   inf   日期   sele   turn   status   删除   inner   mys   

人气教程排行