MySQL优化系列（一）--库与表基本操作以及数据增删改

最新推荐文章于 2024-09-15 08:03:51 发布

七分饱先生

最新推荐文章于 2024-09-15 08:03:51 发布

阅读量118

点赞数

文章标签：数据库

<div class="markdown_views">
                           
                           <svg xmlns="http://www.w3.org/2000/svg" style="display: none;"><path stroke-linecap="round" d="M5,0 0,2.5 5,5z" id="raphael-marker-block" style="-webkit-tap-highlight-color: rgba(0, 0, 0, 0);"></path></svg>
                           <h3 id="数据库后端开发者必学而且现在以mysql居多这个系列将系统化mysql一些高级用法打算先讲很多人关注的使用方式增删改查以及其优化然后就讲数据库和表的操作很多我们学习忽略的地方接着就是引擎还有更高级的查询等等ok不多说lets-go">数据库，后端开发者必学，而且现在以MySQL居多。这个系列将系统化MySQL一些高级用法。打算先讲很多人关注的使用方式（增删改查以及其优化），然后就讲数据库和表的操作（很多我们学习忽略的地方），接着就是引擎还有更高级的查询等等。OK，不多说，Let’s go!</h3>

<pre class="prettyprint" name="code"><code class="hljs lasso has-numbering">本系列会逐步更新，从基本知识总结，到sql优化，索引设计优化，存储过程使用优化<span class="hljs-attribute">...</span><span class="hljs-attribute">...</span>并且到后面会给出一些方案设计，比如集群，主从<span class="hljs-attribute">...</span><span class="hljs-attribute">...</span>

敬请期待！！
本人能力有限，有写得不对的，麻烦在下面指出，十分感谢！</code><ul class="pre-numbering" style=""><li style="color: rgb(153, 153, 153);">1</li><li style="color: rgb(153, 153, 153);">2</li><li style="color: rgb(153, 153, 153);">3</li><li style="color: rgb(153, 153, 153);">4</li></ul></pre>

<h4 id="一mysql优化笔记一库与表基本操作以及数据增删改">（一）<a href="http://blog.csdn.net/jack__frost/article/details/71194208" rel="nofollow" target="_blank">MySQL优化笔记（一）–库与表基本操作以及数据增删改</a></h4>

<h4 id="二mysql优化笔记二查找优化1非索引设计">（二）<a href="http://blog.csdn.net/jack__frost/article/details/71512404" rel="nofollow" target="_blank">MySQL优化笔记（二）–查找优化（1）（非索引设计）</a></h4>

<h4 id="三mysql优化笔记二查找优化2外连接多表联合查询以及查询注意点">（三）<a href="http://blog.csdn.net/jack__frost/article/details/71698285" rel="nofollow" target="_blank">MySQL优化笔记（二）–查找优化（2）（外连接、多表联合查询以及查询注意点）</a></h4>

<h4 id="四-mysql优化笔记三索引的使用原理和设计优化">（四）<a href="http://blog.csdn.net/jack__frost/article/details/72571540" rel="nofollow" target="_blank"> MySQL优化笔记（三）–索引的使用、原理和设计优化</a></h4>

<h4 id="五-mysql优化笔记四表的设计与优化单表多表">（五）<a href="http://blog.csdn.net/jack__frost/article/details/72672252" rel="nofollow" target="_blank"> MySQL优化笔记（四）–表的设计与优化（单表、多表）</a></h4>

<h4 id="六mysql优化笔记五数据库存储引擎">（六）<a href="http://blog.csdn.net/jack__frost/article/details/72904318" rel="nofollow" target="_blank">MySQL优化笔记（五）–数据库存储引擎</a></h4>

<h3 id="本文章结构一操作数据操作语句优化认识二insert语句三delete语句四update语句五replace语句六库与表基本操作"><a name="t1"></a>本文章结构：（一）操作数据操作语句优化认识；（二）INSERT语句；（三）DELETE语句；（四）UPDATE语句；（五）REPLACE语句；（六）库与表基本操作</h3>

<hr>

<h2 id="一操作数据语句优化认识"><a name="t2"></a>一、操作数据语句优化认识：</h2>

<h3 id="通常情况下当访问某张表的时候读取者首先必须获取该表的锁如果有写入操作到达那么写入者一直等待读取者完成操作查询开始之后就不能中断因此允许读取者完成操作当读取者完成对表的操作的时候锁就会被解除如果写入者正在等待的时候另一个读取操作到达了该读取操作也会被阻塞block因为默认的调度策略是写入者优先于读取者当第一个读取者完成操作并解放锁后写入者开始操作并且直到该写入者完成操作第二个读取者才开始操作">通常情况下，当访问某张表的时候，读取者首先必须获取该表的锁，如果有写入操作到达，那么写入者一直等待读取者完成操作（查询开始之后就不能中断，因此允许读取者完成操作）。当读取者完成对表的操作的时候，锁就会被解除。如果写入者正在等待的时候，另一个读取操作到达了，该读取操作也会被阻塞（block），因为默认的调度策略是写入者优先于读取者。当第一个读取者完成操作并解放锁后，写入者开始操作，并且直到该写入者完成操作，第二个读取者才开始操作。</h3>

<h4 id="通过lock-tables和unlock-tables语句可以显式地获取或释放锁但是在通常情况下服务器的锁管理器会自动地在需要的时候获取锁在不再需要的时候释放锁获取的锁的类型依赖于客户端是写入还是读取操作">通过LOCK TABLES和UNLOCK TABLES语句可以显式地获取或释放锁，但是在通常情况下，服务器的锁管理器会自动地在需要的时候获取锁，在不再需要的时候释放锁。获取的锁的类型依赖于客户端是写入还是读取操作。</h4>

<h4 id="对某张表进行写入操作的客户端必须拥有独占的排他的访问权的锁操作在进行的过程中该数据表处于不一致的inconsistent状态因为数据记录在删除添加或修改的时候数据表上的索引也可能需要更新以相互匹配允许两个客户端同时写入一张数据表是不利的因为这样的操作会很快使数据表中的信息成为一堆无用的垃圾同时允许客户端读取变化之中的数据表也不正确因为正在读取的位置中的数据可能正在变化修改读取的结果可能并不是真实的因此对某张表执行读取操作的客户端也必须获取一个锁防止在读取的过程中其它的客户端写入或改变表但是这个锁不需要独占的访问权因为读取操作不会改变数据因此没有理由让某个读取者阻止其它的读取者访问这张表故读取锁可允许其它的客户端在同一时刻读取这张表">对某张表进行写入操作的客户端必须拥有独占的（排他的）访问权的锁。操作在进行的过程中，该数据表处于不一致的（inconsistent）状态，因为数据记录在删除、添加或修改的时候，数据表上的索引也可能需要更新以相互匹配。允许两个客户端同时写入一张数据表是不利的，因为这样的操作会很快使数据表中的信息成为一堆无用的垃圾。同时允许客户端读取变化之中的数据表也不正确，因为正在读取的位置中的数据可能正在变化（修改），读取的结果可能并不是真实的。因此对某张表执行读取操作的客户端也必须获取一个锁，防止在读取的过程中，其它的客户端写入或改变表。但是这个锁不需要独占的访问权。因为读取操作不会改变数据，因此没有理由让某个读取者阻止其它的读取者访问这张表。故读取锁可允许其它的客户端在同一时刻读取这张表。</h4>

<h4 id="虽然通过锁机制可以实现多线程同时对某个表进行操作但当某个线程作更新操作时首先要获得独占的访问权在更新的过程中所有其它想要访问这个表的线程必须要等到其更新完成为止此时就会导致锁竞争的问题从而导致用户等待时间的延长">虽然通过锁机制，可以实现多线程同时对某个表进行操作，但当某个线程作更新操作时，首先要获得独占的访问权。在更新的过程中，所有其它想要访问这个表的线程必须要等到其更新完成为止。此时就会导致锁竞争的问题，从而导致用户等待时间的延长。</h4>

<h3 id="因此要提高mysql的更新插入效率应首先考虑降低锁的竞争减少写操作的等待时间-本系列在后面才讨论表设计的优化"><a name="t4"></a>因此：要提高MySQL的更新/插入效率，应首先考虑降低锁的竞争，减少写操作的等待时间。（本系列在后面才讨论表设计的优化）</h3>

<hr>

<h3 id="使用前说明偷点懒用之前写出来的数据库-innodb存储引擎请导入数据库文件数据库说明数据库文件"><a name="t5"></a>使用前说明：偷点懒，用之前写出来的数据库-INNODB存储引擎。（请导入数据库文件）。<a href="http://blog.csdn.net/jack__frost/article/details/68947868" rel="nofollow" target="_blank">数据库说明</a>。<a href="https://github.com/FuZhucheng/SSM" rel="nofollow" target="_blank">数据库文件</a>。</h3>

<pre class="prettyprint" name="code"><code class="hljs sql has-numbering"><span class="hljs-operator"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> <span class="hljs-string">`score`</span> (
<span class="hljs-string">`id`</span> BIGINT(<span class="hljs-number">20</span>) <span class="hljs-keyword">NOT</span> <span class="hljs-keyword">NULL</span> AUTO_INCREMENT,
<span class="hljs-string">`change_type`</span> <span class="hljs-keyword">VARCHAR</span>(<span class="hljs-number">255</span>) <span class="hljs-keyword">NULL</span> <span class="hljs-keyword">DEFAULT</span> <span class="hljs-keyword">NULL</span>,
<span class="hljs-string">`create_time`</span> <span class="hljs-keyword">TIMESTAMP</span> <span class="hljs-keyword">NOT</span> <span class="hljs-keyword">NULL</span> <span class="hljs-keyword">DEFAULT</span> <span class="hljs-keyword">CURRENT_TIMESTAMP</span>,
<span class="hljs-string">`score`</span> <span class="hljs-keyword">INT</span>(<span class="hljs-number">11</span>) <span class="hljs-keyword">NULL</span> <span class="hljs-keyword">DEFAULT</span> <span class="hljs-keyword">NULL</span>,
<span class="hljs-string">`user_id`</span> BIGINT(<span class="hljs-number">20</span>) <span class="hljs-keyword">NULL</span> <span class="hljs-keyword">DEFAULT</span> <span class="hljs-keyword">NULL</span>,
<span class="hljs-keyword">PRIMARY</span> <span class="hljs-keyword">KEY</span> (<span class="hljs-string">`id`</span>)
)
<span class="hljs-keyword">COLLATE</span>=<span class="hljs-string">'utf8_general_ci'</span>;</span>
//外键在以后再聊
<span class="hljs-operator"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> <span class="hljs-string">`gag`</span> (
<span class="hljs-string">`id`</span> BIGINT(<span class="hljs-number">20</span>) <span class="hljs-keyword">NOT</span> <span class="hljs-keyword">NULL</span> AUTO_INCREMENT,
<span class="hljs-string">`create_time`</span> <span class="hljs-keyword">TIMESTAMP</span> <span class="hljs-keyword">NOT</span> <span class="hljs-keyword">NULL</span> <span class="hljs-keyword">DEFAULT</span> <span class="hljs-keyword">CURRENT_TIMESTAMP</span>,
<span class="hljs-string">`gag_time`</span> <span class="hljs-keyword">DATE</span> <span class="hljs-keyword">NULL</span> <span class="hljs-keyword">DEFAULT</span> <span class="hljs-keyword">NULL</span>,
<span class="hljs-string">`user_id`</span> BIGINT(<span class="hljs-number">20</span>) <span class="hljs-keyword">NULL</span> <span class="hljs-keyword">DEFAULT</span> <span class="hljs-keyword">NULL</span>,
<span class="hljs-keyword">PRIMARY</span> <span class="hljs-keyword">KEY</span> (<span class="hljs-string">`id`</span>)
)
<span class="hljs-keyword">COLLATE</span>=<span class="hljs-string">'utf8_general_ci'</span>;</span></code><ul class="pre-numbering" style=""><li style="color: rgb(153, 153, 153);">1</li><li style="color: rgb(153, 153, 153);">2</li><li style="color: rgb(153, 153, 153);">3</li><li style="color: rgb(153, 153, 153);">4</li><li style="color: rgb(153, 153, 153);">5</li><li style="color: rgb(153, 153, 153);">6</li><li style="color: rgb(153, 153, 153);">7</li><li style="color: rgb(153, 153, 153);">8</li><li style="color: rgb(153, 153, 153);">9</li><li style="color: rgb(153, 153, 153);">10</li><li style="color: rgb(153, 153, 153);">11</li><li style="color: rgb(153, 153, 153);">12</li><li style="color: rgb(153, 153, 153);">13</li><li style="color: rgb(153, 153, 153);">14</li><li style="color: rgb(153, 153, 153);">15</li><li style="color: rgb(153, 153, 153);">16</li><li style="color: rgb(153, 153, 153);">17</li><li style="color: rgb(153, 153, 153);">18</li></ul></pre>

<h2 id="二insert语句"><a name="t6"></a>二、INSERT语句：</h2>

<h3 id="insert-into-表名-字段列表-values-值列表-值列表"><a name="t7"></a>INSERT [INTO] 表名 [(字段列表)] VALUES (值列表)[, (值列表), …]</h3>

<h4 id="如果要插入的值列表包含所有字段并且顺序一致则可以省略字段列表">如果要插入的值列表包含所有字段并且顺序一致，则可以省略字段列表。</h4>

<h4 id="可同时插入多条数据记录">可同时插入多条数据记录！</h4>

<h4 id="replace-与-insert-完全一样可互换">REPLACE 与 INSERT 完全一样，可互换。</h4>

<h4 id="1当我们需要批量插入数据的时候这样的语句却会出现性能问题例如说如果有需要插入100000条数据那么就需要有100000条insert语句每一句都需要提交到关系引擎那里去解析优化然后才能够到达存储引擎做真的插入工作上述所说的同时插入多条就是一种优化经测试大概10条同时插入是最高效的">（1）当我们需要批量插入数据的时候，这样的语句却会出现性能问题。例如说，如果有需要插入100000条数据，那么就需要有100000条insert语句，每一句都需要提交到关系引擎那里去解析，优化，然后才能够到达存储引擎做真的插入工作。上述所说的同时插入多条就是一种优化。（经测试，大概10条同时插入是最高效的）</h4>

<pre class="prettyprint" name="code"><code class="hljs sql has-numbering"><span class="hljs-operator"><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> score (change_type,score,user_id) <span class="hljs-keyword">VALUES</span> (<span class="hljs-string">'吃饭'</span>,<span class="hljs-number">10</span>,<span class="hljs-number">1</span>),(<span class="hljs-string">'喝茶'</span>,<span class="hljs-number">10</span>,<span class="hljs-number">1</span>),(<span class="hljs-string">'喝茶'</span>,<span class="hljs-number">10</span>,<span class="hljs-number">1</span>);</span> </code><ul class="pre-numbering" style=""><li style="color: rgb(153, 153, 153);">1</li></ul></pre>

<h4 id="2将进程线程数控制在2倍于cpu数目相对合适">（2）将进程/线程数控制在2倍于CPU数目相对合适</h4>

<h4 id="3采用顺序主键策略例如自增主键或者修改业务逻辑让插入的记录尽可能顺序主键">（3）采用顺序主键策略（例如自增主键，或者修改业务逻辑，让插入的记录尽可能顺序主键）</h4>

<h4 id="4考虑使用replace-语句代替insert语句replace语句请参考下文有详细讲述">（4）考虑使用replace 语句代替insert语句。（REPLACE语句请参考下文，有详细讲述）</h4>

<h2 id="三delete语句"><a name="t9"></a>三、DELETE语句：</h2>

<h3 id="delete-from-表名-删除条件子句"><a name="t10"></a>DELETE FROM 表名[ 删除条件子句]</h3>

<h3 id="没有条件子句则会删除全部"><a name="t11"></a>没有条件子句，则会删除全部</h3>

<pre class="prettyprint" name="code"><code class="hljs sql has-numbering"><span class="hljs-operator"><span class="hljs-keyword">DELETE</span> <span class="hljs-keyword">FROM</span> gag;</span> 删除全部
<span class="hljs-operator"><span class="hljs-keyword">DELETE</span> <span class="hljs-keyword">FROM</span> gag <span class="hljs-keyword">WHERE</span> id=<span class="hljs-number">1</span>;</span></code><ul class="pre-numbering" style=""><li style="color: rgb(153, 153, 153);">1</li><li style="color: rgb(153, 153, 153);">2</li></ul></pre>

<h3 id="补充mysql中truncate-table和delete语句都可以删除表里面所有数据但是在一些情况下有些不同"><a name="t12"></a><strong>补充：Mysql中truncate table和delete语句都可以删除表里面所有数据，但是在一些情况下有些不同！</strong></h3>

<pre class="prettyprint" name="code"><code class="hljs sql has-numbering"><span class="hljs-operator"><span class="hljs-keyword">truncate</span> <span class="hljs-keyword">table</span> gag;</span></code><ul class="pre-numbering" style=""><li style="color: rgb(153, 153, 153);">1</li></ul></pre>

<h4 id="1truncate-table速度要更快一些但truncate删除后不记录mysql日志不可以恢复数据">（1）truncate table速度要更快一些，但truncate删除后不记录mysql日志，不可以恢复数据。</h4>

<h4 id="2如果没有外键关联innodb执行truncate是先drop-table原始表再创建一个跟原始表一样空表速度要远远快于delete逐条删除行记录">（2）如果没有外键关联，innodb执行truncate是先drop table(原始表),再创建一个跟原始表一样空表,速度要远远快于delete逐条删除行记录。</h4>

<h4 id="3如果使用innodbfilepertable参数truncate-table-能重新利用释放的硬盘空间在innodb-plugin中truncate-table为自动回收如果不是用innodb-plugin那么需要使用optimize-table来优化表释放空间">（3）如果使用innodb_file_per_table参数，truncate table 能重新利用释放的硬盘空间,在InnoDB Plugin中，truncate table为自动回收，如果不是用InnoDB Plugin,那么需要使用optimize table来优化表，释放空间。</h4>

<h4 id="truncate-table删除表后optimize-table尤其重要特别是大数据数据库表空间可以得到释放">truncate table删除表后，optimize table尤其重要，特别是大数据数据库，表空间可以得到释放！</h4>

<h4 id="4表有外键关联truncate-table删除表数据为逐行删除如果外键指定级联删除delete-cascade关联的子表也会会被删除所有表数据如果外键未指定级联cascdetruncate-table逐行删除数据如果是父行关联子表行数据将会报错">（4）表有外键关联，truncate table删除表数据为逐行删除，如果外键指定级联删除(delete cascade)，关联的子表也会会被删除所有表数据。如果外键未指定级联(cascde),truncate table逐行删除数据，如果是父行关联子表行数据，将会报错。</h4>

<h4 id="5autoincrement计数器在truncate-table后会重置为0与是否有外键关联没有关系">（5）auto_increment计数器在truncate table后会重置为0.与是否有外键关联没有关系。</h4>

<h4 id="一个大的-delete-或-insert-操作要非常小心因为这两个操作是会锁表的表一锁住其他操作就进不来了因此我们要交给dba去拆分重整数据库策略比如限制处理1000条">一个大的 DELETE 或 INSERT 操作，要非常小心，因为这两个操作是会锁表的，表一锁住，其他操作就进不来了。因此，我们要交给DBA去拆分，重整数据库策略，比如限制处理1000条。</h4>

<h4 id="mysql官方手-册得知删除数据的速度和创建的索引数量是成正比的所以在超大型数据库中删除时处理好索引关系非常重要推荐的折中方法在删除数据之前删除这那几个索引然后删除其中无用-数据删除完成后重新创建索引">MySQL官方手册得知删除数据的速度和创建的索引数量是成正比的。所以在超大型数据库中，删除时处理好索引关系非常重要。推荐的折中方法：在删除数据之前删除这那几个索引，然后删除其中无用数据，删除完成后重新创建索引。</h4>

<h2 id="四update语句"><a name="t14"></a>四、UPDATE语句：</h2>

<h3 id="update-表名-set-字段名新值-字段名新值-更新条件"><a name="t15"></a>UPDATE 表名 SET 字段名=新值[, 字段名=新值] [更新条件]</h3>

<pre class="prettyprint" name="code"><code class="hljs sql has-numbering"><span class="hljs-operator"><span class="hljs-keyword">UPDATE</span> score <span class="hljs-keyword">SET</span> change_type=<span class="hljs-string">'洗澡'</span> <span class="hljs-keyword">WHERE</span> id=<span class="hljs-number">2</span>;</span></code><ul class="pre-numbering" style=""><li style="color: rgb(153, 153, 153);">1</li></ul></pre>

<h3 id="优化更新多条记录往后会结合mybatics写个实例"><a name="t16"></a>优化：更新多条记录（往后会结合MyBatics写个实例）</h3>

<h4 id="更新多条记录的多个值">更新多条记录的多个值</h4>

<pre class="prettyprint" name="code"><code class="hljs sql has-numbering"><span class="hljs-operator"><span class="hljs-keyword">Update</span> score
<span class="hljs-keyword">SET</span> change_type = <span class="hljs-keyword">CASE</span> id
<span class="hljs-keyword">WHEN</span> <span class="hljs-number">1</span> <span class="hljs-keyword">THEN</span> <span class="hljs-string">'value1'</span>
<span class="hljs-keyword">WHEN</span> <span class="hljs-number">2</span> <span class="hljs-keyword">THEN</span> <span class="hljs-string">'value2'</span>
<span class="hljs-keyword">WHEN</span> <span class="hljs-number">3</span> <span class="hljs-keyword">THEN</span> <span class="hljs-string">'value3'</span>
<span class="hljs-keyword">END</span> ,
score = <span class="hljs-keyword">CASE</span> id
<span class="hljs-keyword">WHEN</span> <span class="hljs-number">1</span> <span class="hljs-keyword">THEN</span> <span class="hljs-number">1</span>
<span class="hljs-keyword">WHEN</span> <span class="hljs-number">2</span> <span class="hljs-keyword">THEN</span> <span class="hljs-number">2</span>
<span class="hljs-keyword">WHEN</span> <span class="hljs-number">3</span> <span class="hljs-keyword">THEN</span> <span class="hljs-number">3</span>
<span class="hljs-keyword">END</span>
<span class="hljs-keyword">WHERE</span> id <span class="hljs-keyword">IN</span> (<span class="hljs-number">1</span>,<span class="hljs-number">2</span>,<span class="hljs-number">3</span>) </span></code><ul class="pre-numbering" style=""><li style="color: rgb(153, 153, 153);">1</li><li style="color: rgb(153, 153, 153);">2</li><li style="color: rgb(153, 153, 153);">3</li><li style="color: rgb(153, 153, 153);">4</li><li style="color: rgb(153, 153, 153);">5</li><li style="color: rgb(153, 153, 153);">6</li><li style="color: rgb(153, 153, 153);">7</li><li style="color: rgb(153, 153, 153);">8</li><li style="color: rgb(153, 153, 153);">9</li><li style="color: rgb(153, 153, 153);">10</li><li style="color: rgb(153, 153, 153);">11</li><li style="color: rgb(153, 153, 153);">12</li></ul></pre>

<h4 id="1-尽量不要修改主键字段">(1). 尽量不要修改主键字段。</h4>

<h4 id="2-当修改varchar型字段时尽量使用相同长度内容的值代替">(2). 当修改VARCHAR型字段时，尽量使用相同长度内容的值代替。</h4>

<h4 id="3-尽量最小化对于含有update触发器的表的update操作">(3). 尽量最小化对于含有UPDATE触发器的表的UPDATE操作。</h4>

<h4 id="4-避免update将要复制到其他数据库的列">(4). 避免UPDATE将要复制到其他数据库的列。</h4>

<h4 id="5-避免update建有很多索引的列">(5). 避免UPDATE建有很多索引的列。</h4>

<h4 id="6-避免update在where子句条件中的列">(6). 避免UPDATE在WHERE子句条件中的列。</h4>

<hr>

<h2 id="五replace语句"><a name="t17"></a>五、REPLACE语句：</h2>

<h3 id="根据应用情况可以使用replace-语句代替insertupdate语句例如如果一个表在一个字段上建立了唯一索引当向这个表中使用已经存在的键值插入一条记录将会抛出一个主键冲突的错误如果我们想用新记录的值来覆盖原来的记录值时就可以使用replace语句">根据应用情况可以使用replace 语句代替insert/update语句。例如：如果一个表在一个字段上建立了唯一索引，当向这个表中使用已经存在的键值插入一条记录，将会抛出一个主键冲突的错误。如果我们想用新记录的值来覆盖原来的记录值时，就可以使用REPLACE语句。</h3>

<h4 id="使用replace插入记录时如果记录不重复或往表里插新记录replace功能与insert一样如果存在重复记录replace就使用新记录的值来替换原来的记录值使用replace的最大好处就是可以将delete和insert合二为一形成一个原子操作这样就可以不必考虑同时使用delete和insert时添加事务等复杂操作了">使用REPLACE插入记录时，如果记录不重复（或往表里插新记录），REPLACE功能与INSERT一样，如果存在重复记录，REPLACE就使用新记录的值来替换原来的记录值。使用REPLACE的最大好处就是可以将DELETE和INSERT合二为一，形成一个原子操作。这样就可以不必考虑同时使用DELETE和INSERT时添加事务等复杂操作了。</h4>

<h4 id="在使用replace时表中必须有唯一有一个primary-key或unique索引否则使用一个replace语句没有意义">在使用REPLACE时，表中必须有唯一有一个PRIMARY KEY或UNIQUE索引，否则，使用一个REPLACE语句没有意义。</h4>

<h4 id="1同insert">（1）同INSERT</h4>

<pre class="prettyprint" name="code"><code class="hljs sql has-numbering">//含义一：与普通<span class="hljs-operator"><span class="hljs-keyword">INSERT</span>一样功能
<span class="hljs-keyword">REPLACE</span> <span class="hljs-keyword">INTO</span> score (change_type,score,user_id) <span class="hljs-keyword">VALUES</span> (<span class="hljs-string">'吃饭'</span>,<span class="hljs-number">10</span>,<span class="hljs-number">1</span>),(<span class="hljs-string">'喝茶'</span>,<span class="hljs-number">10</span>,<span class="hljs-number">1</span>),(<span class="hljs-string">'喝茶'</span>,<span class="hljs-number">10</span>,<span class="hljs-number">1</span>);</span>
//含义二：找到第一条记录，用后面的值进行替换
<span class="hljs-operator"><span class="hljs-keyword">REPLACE</span> <span class="hljs-keyword">INTO</span> score (id,change_type,score,user_id) <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">1</span>,<span class="hljs-string">'吃饭'</span>,<span class="hljs-number">10</span>,<span class="hljs-number">1</span>)</span></code><ul class="pre-numbering" style=""><li style="color: rgb(153, 153, 153);">1</li><li style="color: rgb(153, 153, 153);">2</li><li style="color: rgb(153, 153, 153);">3</li><li style="color: rgb(153, 153, 153);">4</li></ul></pre>

<h4 id="此语句的作用是向表table中插入3条记录如果主键id为1或2不存在就相当于插入语句">此语句的作用是向表table中插入3条记录。如果主键id为1或2不存在就相当于插入语句：</h4>

<h4 id="insertinto-score-changetypescoreuserid-values-吃饭101喝茶101喝茶101">INSERTINTO score (change_type,score,user_id) VALUES (‘吃饭’,10,1),(‘喝茶’,10,1),(‘喝茶’,10,1);</h4>

<h4 id="如果存在相同的值则不会插入数据">如果存在相同的值则不会插入数据。</h4>

<h4 id="2replaceobject-search-replace把object中出现search的全部替换为replace">（2）replace(object, search, replace)，把object中出现search的全部替换为replace。</h4>

<pre class="prettyprint" name="code"><code class="hljs ruleslanguage has-numbering"><span class="hljs-comment">//用法一：并不是修改数据，而只是单纯做局部替换数据返还而已。</span>
<span class="hljs-keyword">SELECT</span> REPLACE(<span class="hljs-string">'喝茶'</span>,<span class="hljs-string">'茶'</span>,<span class="hljs-string">'喝'</span>)
<span class="hljs-comment">//结果：喝喝</span></code><ul class="pre-numbering" style=""><li style="color: rgb(153, 153, 153);">1</li><li style="color: rgb(153, 153, 153);">2</li><li style="color: rgb(153, 153, 153);">3</li></ul></pre>

<h4 id="用法二修改表数据啦对应下面就是根据changetype字段找到做任务的数据用bb来替换">用法二：修改表数据啦，对应下面就是，根据change_type字段找到做任务的数据，用bb来替换</h4>

<pre class="prettyprint" name="code"><code class="hljs sql has-numbering"><span class="hljs-operator"><span class="hljs-keyword">UPDATE</span> score <span class="hljs-keyword">SET</span> change_type=<span class="hljs-keyword">REPLACE</span>(change_type,<span class="hljs-string">'做任务'</span>,<span class="hljs-string">'bb'</span>)</span></code><ul class="pre-numbering" style=""><li style="color: rgb(153, 153, 153);">1</li></ul></pre>

<h3 id="在此做下对比update和replace的区别"><a name="t20"></a>在此，做下对比：UPDATE和REPLACE的区别：</h3>

<h4 id="1update在没有匹配记录时什么都不做而replace在有重复记录时更新在没有重复记录时插入">1）UPDATE在没有匹配记录时什么都不做，而REPLACE在有重复记录时更新，在没有重复记录时插入。</h4>

<h4 id="2update可以选择性地更新记录的一部分字段而replace在发现有重复记录时就将这条记录彻底删除再插入新的记录也就是说将所有的字段都更新了">2）UPDATE可以选择性地更新记录的一部分字段。而REPLACE在发现有重复记录时就将这条记录彻底删除，再插入新的记录。也就是说，将所有的字段都更新了。</h4>

<h4 id="其实replace更像insert与delete的结合">其实REPLACE更像INSERT与DELETE的结合。</h4>

<h2 id="六库与表基本操作"><a name="t21"></a>六、库与表基本操作：</h2>

<h3 id="一库的基本操作"><a name="t22"></a>（一）库的基本操作：</h3>

<h4 id="1查看所有数据库以及使用数据库">（1）查看所有数据库以及使用数据库：</h4>

<pre class="prettyprint" name="code"><code class="hljs php has-numbering">show databases;

<span class="hljs-keyword">use</span> <span class="hljs-title">table</span>;</code><ul class="pre-numbering" style=""><li style="color: rgb(153, 153, 153);">1</li><li style="color: rgb(153, 153, 153);">2</li><li style="color: rgb(153, 153, 153);">3</li></ul></pre>

<h4 id="2查看当前数据库">（2）查看当前数据库</h4>

<pre class="prettyprint" name="code"><code class="hljs sql has-numbering"> <span class="hljs-operator"><span class="hljs-keyword">select</span> <span class="hljs-keyword">database</span>();</span></code><ul class="pre-numbering" style=""><li style="color: rgb(153, 153, 153);">1</li></ul></pre>

<h4 id="3显示当前时间用户名数据库版本">（3）显示当前时间、用户名、数据库版本</h4>

<pre class="prettyprint" name="code"><code class="hljs sql has-numbering"><span class="hljs-operator"><span class="hljs-keyword">select</span> now(), <span class="hljs-keyword">user</span>(), version();</span></code><ul class="pre-numbering" style=""><li style="color: rgb(153, 153, 153);">1</li></ul></pre>

<pre class="prettyprint" name="code"><code class="hljs sql has-numbering"><span class="hljs-operator"><span class="hljs-keyword">create</span> <span class="hljs-keyword">database</span>[ <span class="hljs-keyword">if</span> <span class="hljs-keyword">not</span> <span class="hljs-keyword">exists</span>] 数据库名数据库选项
数据库选项：
<span class="hljs-keyword">CHARACTER</span> <span class="hljs-keyword">SET</span> charset_name
<span class="hljs-keyword">COLLATE</span> collation_name</span></code><ul class="pre-numbering" style=""><li style="color: rgb(153, 153, 153);">1</li><li style="color: rgb(153, 153, 153);">2</li><li style="color: rgb(153, 153, 153);">3</li><li style="color: rgb(153, 153, 153);">4</li></ul></pre>

<h4 id="5查看当前库信息">（5）查看当前库信息</h4>

<pre class="prettyprint" name="code"><code class="hljs sql has-numbering"><span class="hljs-operator"><span class="hljs-keyword">show</span> <span class="hljs-keyword">create</span> <span class="hljs-keyword">database</span> 数据库名</span></code><ul class="pre-numbering" style=""><li style="color: rgb(153, 153, 153);">1</li></ul></pre>

<h4 id="6修改库的选项信息">（6）修改库的选项信息</h4>

<pre class="prettyprint" name="code"><code class="hljs sql has-numbering"><span class="hljs-operator"><span class="hljs-keyword">alter</span> <span class="hljs-keyword">database</span> 库名选项信息</span></code><ul class="pre-numbering" style=""><li style="color: rgb(153, 153, 153);">1</li></ul></pre>

<pre class="prettyprint" name="code"><code class="hljs sql has-numbering"><span class="hljs-operator"><span class="hljs-keyword">drop</span> <span class="hljs-keyword">database</span>[ <span class="hljs-keyword">if</span> <span class="hljs-keyword">exists</span>] 数据库名
同时删除该数据库相关的目录及其目录内容</span></code><ul class="pre-numbering" style=""><li style="color: rgb(153, 153, 153);">1</li><li style="color: rgb(153, 153, 153);">2</li></ul></pre>

<h3 id="二表的基本操作"><a name="t23"></a>（二）表的基本操作：</h3>

<pre class="prettyprint" name="code"><code class="hljs sql has-numbering"> <span class="hljs-operator"><span class="hljs-keyword">create</span> [<span class="hljs-keyword">temporary</span>] <span class="hljs-keyword">table</span>[ <span class="hljs-keyword">if</span> <span class="hljs-keyword">not</span> <span class="hljs-keyword">exists</span>] [库名.]表名 ( 表的结构定义 )[ 表选项]
每个字段必须有数据类型
最后一个字段后不能有逗号
<span class="hljs-keyword">temporary</span> 临时表，会话结束时表自动消失
对于字段的定义：
字段名数据类型 [<span class="hljs-keyword">NOT</span> <span class="hljs-keyword">NULL</span> | <span class="hljs-keyword">NULL</span>] [<span class="hljs-keyword">DEFAULT</span> default_value] [AUTO_INCREMENT] [<span class="hljs-keyword">UNIQUE</span> [<span class="hljs-keyword">KEY</span>] | [<span class="hljs-keyword">PRIMARY</span>] <span class="hljs-keyword">KEY</span>] [COMMENT <span class="hljs-string">'string'</span>]</span></code><ul class="pre-numbering" style=""><li style="color: rgb(153, 153, 153);">1</li><li style="color: rgb(153, 153, 153);">2</li><li style="color: rgb(153, 153, 153);">3</li><li style="color: rgb(153, 153, 153);">4</li><li style="color: rgb(153, 153, 153);">5</li><li style="color: rgb(153, 153, 153);">6</li></ul></pre>

<h5 id="alter-table-表名字-drop-列名称">ALTER TABLE 【表名字】 DROP 【列名称】</h5>

<h5 id="alter-table-表名字-add-列名称-int-not-null-comment-注释说明">ALTER TABLE 【表名字】 ADD 【列名称】 INT NOT NULL COMMENT ‘注释说明’</h5>

<h4 id="3修改列的类型信息">[3]修改列的类型信息</h4>

<h5 id="alter-table-表名字-change-列名称新列名称这里可以用和原来列同名即可-bigint-not-null-comment-注释说明">ALTER TABLE 【表名字】 CHANGE 【列名称】【新列名称（这里可以用和原来列同名即可）】 BIGINT NOT NULL COMMENT ‘注释说明’</h5>

<h5 id="alter-table-表名字-change-列名称新列名称-bigint-not-null-comment-注释说明">ALTER TABLE 【表名字】 CHANGE 【列名称】【新列名称】 BIGINT NOT NULL COMMENT ‘注释说明’</h5>

<h5 id="alter-table-表名字-rename-表新名字">ALTER TABLE 【表名字】 RENAME 【表新名字】</h5>

<h4 id="6删除表中主键">[6]删除表中主键</h4>

<h5 id="alter-table-表名字-drop-primary-key">Alter TABLE 【表名字】 drop primary key</h5>

<h5 id="alter-table-sjresourcecharges-add-constraint-pksjresourcecharges-primary-key-residresfromid">ALTER TABLE sj_resource_charges ADD CONSTRAINT PK_SJ_RESOURCE_CHARGES PRIMARY KEY (resid,resfromid)</h5>

<h5 id="alter-table-sjresourcecharges-add-index-indexname-name">ALTER TABLE sj_resource_charges add index INDEX_NAME (name);</h5>

<h4 id="9添加唯一限制条件索引">[9]添加唯一限制条件索引</h4>

<h5 id="alter-table-sjresourcecharges-add-unique-empname2cardnumber">ALTER TABLE sj_resource_charges add unique emp_name2(cardnumber);</h5>

<h5 id="alter-table-tablename-drop-index-empname">Alter table tablename drop index emp_name;</h5>

<h3 id="3查看所有表"><a name="t26"></a>（3）查看所有表</h3>

<pre class="prettyprint" name="code"><code class="hljs sql has-numbering"><span class="hljs-operator"><span class="hljs-keyword">SHOW</span> TABLES；</span></code><ul class="pre-numbering" style=""><li style="color: rgb(153, 153, 153);">1</li></ul></pre>

<h3 id="4查看表机构"><a name="t27"></a>（4）查看表机构：</h3>

<pre class="prettyprint" name="code"><code class="hljs sql has-numbering"> <span class="hljs-operator"><span class="hljs-keyword">SHOW</span> <span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> 表名（信息更详细）
<span class="hljs-keyword">DESC</span> 表名 / <span class="hljs-keyword">DESCRIBE</span> 表名 / EXPLAIN 表名 / <span class="hljs-keyword">SHOW</span> COLUMNS <span class="hljs-keyword">FROM</span> 表名 [<span class="hljs-keyword">LIKE</span> <span class="hljs-string">'PATTERN'</span>]
<span class="hljs-keyword">SHOW</span> <span class="hljs-keyword">TABLE</span> STATUS [<span class="hljs-keyword">FROM</span> db_name] [<span class="hljs-keyword">LIKE</span> <span class="hljs-string">'pattern'</span>]</span></code><ul class="pre-numbering" style=""><li style="color: rgb(153, 153, 153);">1</li><li style="color: rgb(153, 153, 153);">2</li><li style="color: rgb(153, 153, 153);">3</li></ul></pre>

<h3 id="5修改表对表进行重命名"><a name="t28"></a>（5）修改表：对表进行重命名</h3>

<pre class="prettyprint" name="code"><code class="hljs ruleslanguage has-numbering"> RENAME <span class="hljs-keyword">TABLE</span> 原表名 <span class="hljs-keyword">TO</span> 新表名
RENAME <span class="hljs-keyword">TABLE</span> 原表名 <span class="hljs-keyword">TO</span> 库名.表名（可将表移动到另一个数据库）
-- RENAME可以交换两个表名</code><ul class="pre-numbering" style=""><li style="color: rgb(153, 153, 153);">1</li><li style="color: rgb(153, 153, 153);">2</li><li style="color: rgb(153, 153, 153);">3</li></ul></pre>

<pre class="prettyprint" name="code"><code class="hljs sql has-numbering"><span class="hljs-operator"><span class="hljs-keyword">DROP</span> <span class="hljs-keyword">TABLE</span>[ <span class="hljs-keyword">IF</span> <span class="hljs-keyword">EXISTS</span>] 表名</span></code><ul class="pre-numbering" style=""><li style="color: rgb(153, 153, 153);">1</li></ul></pre>

<h3 id="7清空表数据"><a name="t30"></a>（7）清空表数据</h3>

<pre class="prettyprint" name="code"><code class="hljs sql has-numbering"><span class="hljs-operator"><span class="hljs-keyword">TRUNCATE</span> [<span class="hljs-keyword">TABLE</span>] 表名</span></code><ul class="pre-numbering" style=""><li style="color: rgb(153, 153, 153);">1</li></ul></pre>

<h3 id="8复制表结构"><a name="t31"></a>（8）复制表结构</h3>

<hr>

<h3 id="好了mysql优化系列一库与表基本操作以及数据增删改讲完了现在罗列给大家这是积累的必经一步我会继续出这个系列文章分享经验给大家欢迎在下面指出错误共同学习你的点赞是对我最好的支持"><a name="t32"></a>好了，MySQL优化系列（一）–库与表基本操作以及数据增删改讲完了，现在罗列给大家，这是积累的必经一步，我会继续出这个系列文章，分享经验给大家。欢迎在下面指出错误，共同学习！！你的点赞是对我最好的支持！！</h3>

<h3 id="更多内容可以访问jackfrost的博客"><a name="t33"></a>更多内容，可以访问<a href="http://blog.csdn.net/jack__frost?viewmode=contents" rel="nofollow" target="_blank">JackFrost的博客</a></h3> </div>