SQL修改语法语句梳理总结

美好的幸福生活 發表於 2023-6-19 00:00:00

<div id="navCategory"><h5 class="catalogue">目录</h5><ul class="first_class_ul"><li>
INSERT</li><li>
UPDATE</li><li>
DELETE</li></ul></div><div id="navCategory">
<h5 class="catalogue">
目录</h5>
<ul class="first_class_ul">
<li>
INSERT</li>
<li>
UPDATE</li>
<li>
DELETE</li>
</ul>
</div>
<p class="maodian">
</p>
<p class="maodian"></p><h2>
INSERT</h2>
<p>
当我们需要向数据库表中插入一条新记录时，就必须使用<code>INSERT</code>语句。</p>
<p>
<code>INSERT</code>语句的基本语法是：</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_925426">
<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">INSERT</code> <code class="sql keyword">INTO</code> <code class="sql plain"><表名> (字段1, 字段2, ...) </code><code class="sql keyword">VALUES</code> <code class="sql plain">(值1, 值2, ...);</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
例如，我们向<code>students</code>表插入一条新记录，先列举出需要插入的字段名称，然后在<code>VALUES</code>子句中依次写出对应字段的值：</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_771408">
<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">INSERT</code> <code class="sql keyword">INTO</code> <code class="sql plain">students (class_id, </code><code class="sql keyword">name</code><code class="sql plain">, gender, score) </code><code class="sql keyword">VALUES</code> <code class="sql plain">(2, </code><code class="sql string">'大牛'</code><code class="sql plain">, </code><code class="sql string">'M'</code><code class="sql plain">, 80);</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
还可以一次性添加多条记录，只需要在<code>VALUES</code>子句中指定多个记录值，每个记录是由(...)包含的一组值：</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_215720">
<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">INSERT</code> <code class="sql keyword">INTO</code> <code class="sql plain">students (class_id, </code><code class="sql keyword">name</code><code class="sql plain">, gender, score) </code><code class="sql keyword">VALUES</code>
</div>
<div class="line number2 index1 alt1">
<code class="sql spaces"> </code><code class="sql plain">(1, </code><code class="sql string">'大宝'</code><code class="sql plain">, </code><code class="sql string">'M'</code><code class="sql plain">, 87),</code>
</div>
<div class="line number3 index2 alt2">
<code class="sql spaces"> </code><code class="sql plain">(2, </code><code class="sql string">'二宝'</code><code class="sql plain">, </code><code class="sql string">'M'</code><code class="sql plain">, 81);</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p class="maodian">
</p>
<p class="maodian"></p><h2>
UPDATE</h2>
<p>
如果要更新数据库表中的记录，我们就必须使用<code>UPDATE</code>语句。</p>
<p>
<code>UPDATE</code>语句的基本语法是：</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_178613">
<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"><表名> </code><code class="sql keyword">SET</code> <code class="sql plain">字段1=值1, 字段2=值2, ... </code><code class="sql keyword">WHERE</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>
在<code>UPDATE</code>语句中，更新字段时可以使用表达式。例如，把所有80分以下的同学的成绩加10分：</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_30067">
<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">students </code><code class="sql keyword">SET</code> <code class="sql plain">score=score+10 </code><code class="sql keyword">WHERE</code> <code class="sql plain">score<80;</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
如果<code>WHERE</code>条件没有匹配到任何记录，<code>UPDATE</code>语句不会报错，也不会有任何记录被更新。</p>
<p>
最后，要特别小心的是，<code>UPDATE</code>语句可以没有<code>WHERE</code>条件，例如：</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_579290">
<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">students </code><code class="sql keyword">SET</code> <code class="sql plain">score=60;</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
这时，整个表的所有记录都会被更新。所以，在执行<code>UPDATE</code>语句时要非常小心，最好先用<code>SELECT</code>语句来测试<code>WHERE</code>条件是否筛选出了期望的记录集，然后再用<code>UPDATE</code>更新。</p>
<p class="maodian">
</p>
<p class="maodian"></p><h2>
DELETE</h2>
<p>
如果要删除数据库表中的记录，我们可以使用<code>DELETE</code>语句。</p>
<p>
<code>DELETE</code>语句的基本语法是：</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_581819">
<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">DELETE</code> <code class="sql keyword">FROM</code> <code class="sql plain"><表名> </code><code class="sql keyword">WHERE</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>
例如，我们想删除<code>students</code>表中<code>id=1</code>的记录，就需要这么写：</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_821904">
<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">DELETE</code> <code class="sql keyword">FROM</code> <code class="sql plain">students </code><code class="sql keyword">WHERE</code> <code class="sql plain">id=1;</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
<code>DELETE</code>语句的<code>WHERE</code>条件也是用来筛选需要删除的行，因此和<code>UPDATE</code>类似，<code>DELETE</code>语句也可以一次删除多条记录：</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_875914">
<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">DELETE</code> <code class="sql keyword">FROM</code> <code class="sql plain">students </code><code class="sql keyword">WHERE</code> <code class="sql plain">id>=5 </code><code class="sql color1">AND</code> <code class="sql plain">id<=7;</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
如果<code>WHERE</code>条件没有匹配到任何记录，<code>DELETE</code>语句不会报错，也不会有任何记录被删除。</p>
<p>
最后，要特别小心的是，和<code>UPDATE</code>类似，不带<code>WHERE</code>条件的<code>DELETE</code>语句会删除整个表的数据：</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_511651">
<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">DELETE</code> <code class="sql keyword">FROM</code> <code class="sql plain">students;</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
这时，整个表的所有记录都会被删除。所以，在执行<code>DELETE</code>语句时也要非常小心，最好先用<code>SELECT</code>语句来测试<code>WHERE</code>条件是否筛选出了期望的记录集，然后再用<code>DELETE</code>删除。</p>
<p>
以上就是SQL修改语法语句梳理总结的详细内容，更多关于SQL修改语法总结的资料请关注其它相关文章！</p>
<p>
原文链接：https://blog.csdn.net/weixin_38526306/article/details/87459409</p>

頁: [1]

琼殿技术论坛's Archiver

SQL修改语法语句梳理总结