当前位置：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   

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！