时间:2021-07-01 10:21:17 帮助过:6人阅读
- <span style="color: #008080;">/*</span><span style="color: #008080;">
- 今天身份证号为210210199901012222的读者将超限的图书20151101归还,根据描述实现如下需求:
- 1、更新借阅信息表,将借阅状态(status)更新为‘是’。
- 2、更新罚款记录信息表,更新实际还书日期和罚款金额,罚款金额为每超出一天扣0.2元。
- 3、同时更新读者信息表的余额。(在余额中扣除罚款金额)
- </span><span style="color: #008080;">*/</span>
- <span style="color: #0000ff;">UPDATE</span><span style="color: #000000;"> readerfee t1
- </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
- </span><span style="color: #0000ff;">SET</span> actual_return_date <span style="color: #808080;">=</span><span style="color: #000000;"> sysdate(),
- 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;">,
- balance </span><span style="color: #808080;">=</span> balance <span style="color: #808080;">-</span><span style="color: #000000;"> book_fee
- </span><span style="color: #0000ff;">WHERE</span><span style="color: #000000;">
- t1.book_id </span><span style="color: #808080;">=</span> <span style="color: #800000; font-weight: bold;">20151101</span>
- <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、多表删除
- <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>
- <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
- </span><span style="color: #ff0000;">[</span><span style="color: #ff0000;">WHERE where_ condition</span><span style="color: #ff0000;">]</span>
- <span style="color: #008080;">--</span><span style="color: #008080;"> 1、由于业务需求,需要删除图书类别表中在图书信息表中没有图书记录的类别。</span>
- <span style="color: #0000ff;">SELECT</span><span style="color: #000000;">
- book_id,
- book_name,
- category
- </span><span style="color: #0000ff;">FROM</span><span style="color: #000000;">
- bookcategory_bak t1
- </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
- </span><span style="color: #0000ff;">WHERE</span><span style="color: #000000;">
- parent_id </span><span style="color: #808080;"><></span> <span style="color: #800000; font-weight: bold;">0</span><span style="color: #000000;">;
- <br>
- </span><span style="color: #0000ff;">DELETE</span><span style="color: #000000;"> t1
- </span><span style="color: #0000ff;">FROM</span><span style="color: #000000;">
- bookcategory_bak t1
- </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
- </span><span style="color: #0000ff;">WHERE</span><span style="color: #000000;">
- parent_id </span><span style="color: #808080;"><></span> <span style="color: #800000; font-weight: bold;">0</span>
- <span style="color: #808080;">AND</span> book_id <span style="color: #0000ff;">IS</span> <span style="color: #0000ff;">NULL</span><span style="color: #000000;">;
- </span><span style="color: #0000ff;">SELECT</span>
- <span style="color: #808080;">*</span>
- <span style="color: #0000ff;">FROM</span><span style="color: #000000;">
- bookcategory_bak;
- </span><span style="color: #008080;">--</span><span style="color: #008080;"> 2、需要删除图书类别表的编程语言的类别,以及图书信息表中关于编程语言的图书记录。</span>
- <span style="color: #0000ff;">SELECT</span><span style="color: #000000;">
- book_id,
- book_name,
- category_id,
- category
- </span><span style="color: #0000ff;">FROM</span><span style="color: #000000;">
- bookcategory_bak t1
- </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;
- </span><span style="color: #0000ff;">DELETE</span><span style="color: #000000;"> t1,
- t2
- </span><span style="color: #0000ff;">FROM</span><span style="color: #000000;">
- bookcategory_bak t1
- </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
- </span><span style="color: #0000ff;">WHERE</span><span style="color: #000000;">
- 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、复制一张表:
- <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>
- <span style="color: #0000ff;">FROM </span><span style="color: #000000;">table1;</span>
4、将查询的结果插入到一张表中:
- <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;
- </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