SQL UPDATE 更新语句用法(单列与多列)
<p><strong>Update 语句</strong></p><p>Update 语句用于修改表中的数据。</p>
<p><strong>语法：</strong></p>
<p><code>UPDATE</code> 表名称<code> SET </code>列名称 = 新值 <code>WHERE</code> 列名称 = 某值</p>
<p>例如：</p>
<p>Person表:</p>
<p>
</p>
<table><tbody>
<tr>
<th>LastName</th>
<th>FirstName</th>
<th>Address</th>
<th>City</th>
</tr>
<tr>
<td>Gates</td>
<td>Bill</td>
<td>Xuanwumen 10</td>
<td>Beijing</td>
</tr>
<tr>
<td>Wilson</td>
<td> </td>
<td>Champs-Elysees</td>
<td> </td>
</tr>
</tbody></table>
<p></p>
<p><strong>更新某一行中的一个列</strong></p>
<p>我们为 lastname 是 "Wilson" 的人添加 firstname：</p>
<div class="jb51code">
<div><div id="highlighter_915023" class="syntaxhighlightersql">
<div class="toolbar"><span>?</span></div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter"><div class="line number1 index0 alt2">1</div></td>
<td class="code"><div class="container"><div class="line number1 index0 alt2">
<code class="sql keyword">UPDATE</code> <code class="sql plain">Person </code><code class="sql keyword">SET</code> <code class="sql plain">FirstName = </code><code class="sql string">'Fred'</code> <code class="sql keyword">WHERE</code> <code class="sql plain">LastName = </code><code class="sql string">'Wilson'</code>
</div></div></td>
</tr></tbody></table>
</div></div>
<div class="codetool" id="codetool"><div class="code_n"><textarea></textarea></div></div>
</div>
<p>结果：</p>
<p>
</p>
<table><tbody>
<tr>
<th>LastName</th>
<th>FirstName</th>
<th>Address</th>
<th>City</th>
</tr>
<tr>
<td>Gates</td>
<td>Bill</td>
<td>Xuanwumen 10</td>
<td>Beijing</td>
</tr>
<tr>
<td>Wilson</td>
<td>Fred</td>
<td>Champs-Elysees</td>
<td> </td>
</tr>
</tbody></table>
<p></p>
<p><strong>更新某一行中的若干列</strong></p>
<p>我们会修改地址（address），并添加城市名称（city）：</p>
<div class="jb51code">
<div><div id="highlighter_513343" class="syntaxhighlightersql">
<div class="toolbar"><span>?</span></div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">1</div>
<div class="line number2 index1 alt1">2</div>
</td>
<td class="code"><div class="container">
<div class="line number1 index0 alt2">
<code class="sql keyword">UPDATE</code> <code class="sql plain">Person </code><code class="sql keyword">SET</code> <code class="sql plain">Address = </code><code class="sql string">'Zhongshan 23'</code><code class="sql plain">, City = </code><code class="sql string">'Nanjing'</code>
</div>
<div class="line number2 index1 alt1">
<code class="sql keyword">WHERE</code> <code class="sql plain">LastName = </code><code class="sql string">'Wilson'</code>
</div>
</div></td>
</tr></tbody></table>
</div></div>
<div class="codetool" id="codetool"><div class="code_n"><textarea></textarea></div></div>
</div>
<p>结果：</p>
<p>
</p>
<table><tbody>
<tr>
<th>LastName</th>
<th>FirstName</th>
<th>Address</th>
<th>City</th>
</tr>
<tr>
<td>Gates</td>
<td>Bill</td>
<td>Xuanwumen 10</td>
<td>Beijing</td>
</tr>
<tr>
<td>Wilson</td>
<td>Fred</td>
<td>Zhongshan 23</td>
<td>Nanjing</td>
</tr>
</tbody></table>
<p></p>
<p>SQL的更新语句update（更新表中数据）</p>
<p>1. 更新表中所有行</p>
<p>2.更新表中特定行 ，更新时候一定不要省略WHERE子句，否则会更新所有行。</p>
<p>更新update语句分为三部分，格式如下：</p>
<p>1 更新的表</p>
<p>2 列名和新的值</p>
<p>3 确定更新哪些行的过滤条件</p>
<p>单个列：</p>
<div class="jb51code">
<div><div id="highlighter_236723" class="syntaxhighlightersql">
<div class="toolbar"><span>?</span></div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">1</div>
<div class="line number2 index1 alt1">2</div>
<div class="line number3 index2 alt2">3</div>
</td>
<td class="code"><div class="container">
<div class="line number1 index0 alt2">
<code class="sql keyword">UPDATE</code> <code class="sql plain">Customers</code>
</div>
<div class="line number2 index1 alt1">
<code class="sql keyword">SET</code> <code class="sql plain">cust_email = </code><code class="sql string">' kim@qq.com'</code>
</div>
<div class="line number3 index2 alt2">
<code class="sql keyword">WHERE</code> <code class="sql plain">cust_id = </code><code class="sql string">'10000005'</code><code class="sql plain">;</code>
</div>
</div></td>
</tr></tbody></table>
</div></div>
<div class="codetool" id="codetool"><div class="code_n"><textarea></textarea></div></div>
</div>
<p>多个列：</p>
<div class="jb51code">
<div><div id="highlighter_228986" class="syntaxhighlightersql">
<div class="toolbar"><span>?</span></div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">1</div>
<div class="line number2 index1 alt1">2</div>
<div class="line number3 index2 alt2">3</div>
</td>
<td class="code"><div class="container">
<div class="line number1 index0 alt2">
<code class="sql keyword">UPDATE</code> <code class="sql plain">Customers</code>
</div>
<div class="line number2 index1 alt1">
<code class="sql keyword">SET</code> <code class="sql plain">cust_email = </code><code class="sql string">'kim@qq.com'</code> <code class="sql plain">, cust_contact =</code><code class="sql string">'Sam Roberts'</code>
</div>
<div class="line number3 index2 alt2">
<code class="sql keyword">WHERE</code> <code class="sql plain">cust_id = </code><code class="sql string">'10000005'</code><code class="sql plain">;</code>
</div>
</div></td>
</tr></tbody></table>
</div></div>
<div class="codetool" id="codetool"><div class="code_n"><textarea></textarea></div></div>
</div>
<p>即 更新多个列时，只需要使用一条SET命令，每个 “列=值”对之间用逗号分隔，最后一列不用逗号。</p>
<p><span>Update 警告！</span></p>
<p>在更新记录时要格外小心！在上面的实例中，如果我们省略了 WHERE 子句，如下所示：</p>
<div class="jb51code">
<div><div id="highlighter_937720" class="syntaxhighlightersql">
<div class="toolbar"><span>?</span></div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">1</div>
<div class="line number2 index1 alt1">2</div>
</td>
<td class="code"><div class="container">
<div class="line number1 index0 alt2">
<code class="sql keyword">UPDATE</code> <code class="sql plain">Customers</code>
</div>
<div class="line number2 index1 alt1">
<code class="sql keyword">SET</code> <code class="sql plain">cust_email = </code><code class="sql string">'kim@qq.com'</code>
</div>
</div></td>
</tr></tbody></table>
</div></div>
<div class="codetool" id="codetool"><div class="code_n"><textarea></textarea></div></div>
</div>
<p>执行以上代码会将 Customers 表中所有数据的 cust_email 都改成了kim@qq.com。<br>
执行没有 WHERE 子句的 UPDATE 要慎重，再慎重。一般只有批量替换我们才会这么做。</p>
<p><span>小编：强烈建议一定要先备份再操作。</span></p>
頁:
[1]