当前位置：Gxlcms > 数据库问题 > mysql—表的完整性约束

mysql—表的完整性约束

时间：2021-07-01 10:21:17 帮助过：26人阅读

  • 　　not null
  • 　　unique
  • 　　primary key
  • 　　foreign key
    • 返回顶部

    概览

    　　为了防止不符合规范的数据进入数据库，在用户对数据进行插入、修改、删除等操作时，DBMS自动按照一定的约束条件对数据进行监测，使不符合规范的数据不能进入数据库，以确保数据库中存储的数据正确、有效、相容。 

    　　约束条件与数据类型的宽度一样，都是可选参数，主要分为以下几种：

    1. # <span style="color: #0000ff;">NOT</span> <span style="color: #0000ff;">NULL</span><span style="color: #000000;"> ：非空约束，指定某列不能为空； 
    2. # </span><span style="color: #0000ff;">UNIQUE</span><span style="color: #000000;"> : 唯一约束，指定某列或者几列组合不能重复
    3. # </span><span style="color: #0000ff;">PRIMARY</span> <span style="color: #0000ff;">KEY</span><span style="color: #000000;"> ：主键，指定该列的值可以唯一地标识该列记录
    4. # </span><span style="color: #0000ff;">FOREIGN</span> <span style="color: #0000ff;">KEY</span> ：外键，指定该行记录从属于主表中的一条记录，主要用于参照完整性
    返回顶部

    NOT NULL

    是否可空，null表示空，非字符串
    not null - 不可空
    null - 可空 

    技术图片 
    1. mysql<span style="color: #808080;">></span> <span style="color: #0000ff;">create</span> <span style="color: #0000ff;">table</span> t12 (id <span style="color: #0000ff;">int</span> <span style="color: #808080;">not</span> <span style="color: #0000ff;">null</span><span style="color: #000000;">);
    2. Query OK, </span><span style="color: #800000; font-weight: bold;">0</span> rows affected (<span style="color: #800000; font-weight: bold;">0.02</span><span style="color: #000000;"> sec)
    3. <p>mysql<span style="color: #808080;">></span> <span style="color: #0000ff;">select</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">from</span><span style="color: #000000;"> t12;<br>
    4. Empty </span><span style="color: #0000ff;">set</span> (<span style="color: #800000; font-weight: bold;">0.00</span><span style="color: #000000;"> sec)</span></p>
    5. <p>mysql<span style="color: #808080;">></span> <span style="color: #0000ff;">desc</span><span style="color: #000000;"> t12;<br>
    6. </span><span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">-----+---------+------+-----+---------+-------+</span><br>
    7. <span style="color: #808080;">|</span> Field <span style="color: #808080;">|</span> Type    <span style="color: #808080;">|</span> <span style="color: #0000ff;">Null</span> <span style="color: #808080;">|</span> <span style="color: #0000ff;">Key</span> <span style="color: #808080;">|</span> <span style="color: #0000ff;">Default</span> <span style="color: #808080;">|</span> Extra <span style="color: #808080;">|</span><br>
    8. <span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">-----+---------+------+-----+---------+-------+</span><br>
    9. <span style="color: #808080;">|</span> id    <span style="color: #808080;">|</span> <span style="color: #0000ff;">int</span>(<span style="color: #800000; font-weight: bold;">11</span>) <span style="color: #808080;">|</span> NO   <span style="color: #808080;">|</span>     <span style="color: #808080;">|</span> <span style="color: #0000ff;">NULL</span>    <span style="color: #808080;">|</span>       <span style="color: #808080;">|</span><br>
    10. <span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">-----+---------+------+-----+---------+-------+</span><br>
    11. <span style="color: #800000; font-weight: bold;">1</span> row <span style="color: #808080;">in</span> <span style="color: #0000ff;">set</span> (<span style="color: #800000; font-weight: bold;">0.00</span><span style="color: #000000;"> sec)</span></p>
    12. <h1 id="不能向id列插入空元素。">不能向id列插入空元素。</h1>
    13. <p>mysql<span style="color: #808080;">></span> <span style="color: #0000ff;">insert</span> <span style="color: #0000ff;">into</span> t12 <span style="color: #0000ff;">values</span> (<span style="color: #0000ff;">null</span><span style="color: #000000;">);<br>
    14. ERROR </span><span style="color: #800000; font-weight: bold;">1048</span> (<span style="color: #800000; font-weight: bold;">23000</span>): <span style="color: #0000ff;">Column</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">id</span><span style="color: #ff0000;">‘</span> cannot be <span style="color: #0000ff;">null</span><span style="color: #000000;"></span></p>
    15. <p>mysql<span style="color: #808080;">></span> <span style="color: #0000ff;">insert</span> <span style="color: #0000ff;">into</span> t12 <span style="color: #0000ff;">values</span> (<span style="color: #800000; font-weight: bold;">1</span><span style="color: #000000;">);<br>
    16. Query OK, </span><span style="color: #800000; font-weight: bold;">1</span> row affected (<span style="color: #800000; font-weight: bold;">0.01</span> sec)</p></span>

    not null示例

    DEFAULT

    我们约束某一列不为空，如果这一列中经常有重复的内容，就需要我们频繁的插入，这样会给我们的操作带来新的负担，于是就出现了默认值的概念。

    默认值，创建列时可以指定默认值，当插入数据时如果未主动设置，则自动添加默认值

    技术图片 
    1. mysql<span style="color: #808080;">></span> <span style="color: #0000ff;">create</span> <span style="color: #0000ff;">table</span> t13 (id1 <span style="color: #0000ff;">int</span> <span style="color: #808080;">not</span> <span style="color: #0000ff;">null</span>,id2 <span style="color: #0000ff;">int</span> <span style="color: #808080;">not</span> <span style="color: #0000ff;">null</span> <span style="color: #0000ff;">default</span> <span style="color: #800000; font-weight: bold;">222</span><span style="color: #000000;">);
    2. Query OK, </span><span style="color: #800000; font-weight: bold;">0</span> rows affected (<span style="color: #800000; font-weight: bold;">0.01</span><span style="color: #000000;"> sec)
    3. <p>mysql<span style="color: #808080;">></span> <span style="color: #0000ff;">desc</span><span style="color: #000000;"> t13;<br>
    4. </span><span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">-----+---------+------+-----+---------+-------+</span><br>
    5. <span style="color: #808080;">|</span> Field <span style="color: #808080;">|</span> Type    <span style="color: #808080;">|</span> <span style="color: #0000ff;">Null</span> <span style="color: #808080;">|</span> <span style="color: #0000ff;">Key</span> <span style="color: #808080;">|</span> <span style="color: #0000ff;">Default</span> <span style="color: #808080;">|</span> Extra <span style="color: #808080;">|</span><br>
    6. <span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">-----+---------+------+-----+---------+-------+</span><br>
    7. <span style="color: #808080;">|</span> id1   <span style="color: #808080;">|</span> <span style="color: #0000ff;">int</span>(<span style="color: #800000; font-weight: bold;">11</span>) <span style="color: #808080;">|</span> NO   <span style="color: #808080;">|</span>     <span style="color: #808080;">|</span> <span style="color: #0000ff;">NULL</span>    <span style="color: #808080;">|</span>       <span style="color: #808080;">|</span><br>
    8. <span style="color: #808080;">|</span> id2   <span style="color: #808080;">|</span> <span style="color: #0000ff;">int</span>(<span style="color: #800000; font-weight: bold;">11</span>) <span style="color: #808080;">|</span> NO   <span style="color: #808080;">|</span>     <span style="color: #808080;">|</span> <span style="color: #800000; font-weight: bold;">222</span>     <span style="color: #808080;">|</span>       <span style="color: #808080;">|</span><br>
    9. <span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">-----+---------+------+-----+---------+-------+</span><br>
    10. <span style="color: #800000; font-weight: bold;">2</span> rows <span style="color: #808080;">in</span> <span style="color: #0000ff;">set</span> (<span style="color: #800000; font-weight: bold;">0.01</span><span style="color: #000000;"> sec)</span></p>
    11. <h1 id="只向id1字段添加值，会发现id2字段会使用默认值填充">只向id1字段添加值，会发现id2字段会使用默认值填充</h1>
    12. <p>mysql<span style="color: #808080;">></span> <span style="color: #0000ff;">insert</span> <span style="color: #0000ff;">into</span> t13 (id1) <span style="color: #0000ff;">values</span> (<span style="color: #800000; font-weight: bold;">111</span><span style="color: #000000;">);<br>
    13. Query OK, </span><span style="color: #800000; font-weight: bold;">1</span> row affected (<span style="color: #800000; font-weight: bold;">0.00</span><span style="color: #000000;"> sec)</span></p>
    14. <p>mysql<span style="color: #808080;">></span> <span style="color: #0000ff;">select</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">from</span><span style="color: #000000;"> t13;<br>
    15. </span><span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">---+-----+</span><br>
    16. <span style="color: #808080;">|</span> id1 <span style="color: #808080;">|</span> id2 <span style="color: #808080;">|</span><br>
    17. <span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">---+-----+</span><br>
    18. <span style="color: #808080;">|</span> <span style="color: #800000; font-weight: bold;">111</span> <span style="color: #808080;">|</span> <span style="color: #800000; font-weight: bold;">222</span> <span style="color: #808080;">|</span><br>
    19. <span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">---+-----+</span><br>
    20. <span style="color: #800000; font-weight: bold;">1</span> row <span style="color: #808080;">in</span> <span style="color: #0000ff;">set</span> (<span style="color: #800000; font-weight: bold;">0.00</span><span style="color: #000000;"> sec)</span></p>
    21. <h1 id="id1字段不能为空，所以不能单独向id2字段填充值；">id1字段不能为空，所以不能单独向id2字段填充值；</h1>
    22. <p>mysql<span style="color: #808080;">></span> <span style="color: #0000ff;">insert</span> <span style="color: #0000ff;">into</span> t13 (id2) <span style="color: #0000ff;">values</span> (<span style="color: #800000; font-weight: bold;">223</span><span style="color: #000000;">);<br>
    23. ERROR </span><span style="color: #800000; font-weight: bold;">1364</span> (HY000): Field <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">id1</span><span style="color: #ff0000;">‘</span> doesn‘t have a default value</p>
    24. <h1 id="向id1，id2中分别填充数据，id2的填充数据会覆盖默认值">向id1，id2中分别填充数据，id2的填充数据会覆盖默认值</h1>
    25. <p>mysql> insert into t13 (id1,id2) values (112,223);<br>
    26. Query OK, 1 row affected (0.00 sec)</p>
    27. <p>mysql> select * from t13;<br>
    28. +-----+-----+<br>
    29. | id1 | id2 |<br>
    30. +-----+-----+<br>
    31. | 111 | 222 |<br>
    32. | 112 | 223 |<br>
    33. +-----+-----+<br>
    34. 2 rows in set (0.00 sec)</p></span>

    not null + default 示例 技术图片 
    1. <span style="color: #000000;">设置严格模式：
    2.     不支持对not null字段插入null值
    3.     不支持对自增长字段插入”值
    4.     不支持text字段有默认值
    5. <p>直接在mysql中生效(重启失效):<br>
    6. mysql>set sql_mode=<span style="color: #800000;">"</span><span style="color: #800000;">STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION</span><span style="color: #800000;">"</span><span style="color: #000000;">;</span></p>
    7. <p>配置文件添加(永久失效)：<br>
    8. sql-mode=<span style="color: #800000;">"</span><span style="color: #800000;">STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION</span><span style="color: #800000;">"</span></p></span>

    not null不生效 返回顶部

    UNIQUE

    唯一约束，指定某列或者几列组合不能重复

    技术图片 
    1. <span style="color: #000000;">方法一：
    2. </span><span style="color: #0000ff;">create</span> <span style="color: #0000ff;">table</span><span style="color: #000000;"> department1(
    3. id </span><span style="color: #0000ff;">int</span><span style="color: #000000;">,
    4. name </span><span style="color: #0000ff;">varchar</span>(<span style="color: #800000; font-weight: bold;">20</span>) <span style="color: #0000ff;">unique</span><span style="color: #000000;">,
    5. comment </span><span style="color: #0000ff;">varchar</span>(<span style="color: #800000; font-weight: bold;">100</span><span style="color: #000000;">)
    6. );
    7. <p>方法二：<br>
    8. <span style="color: #0000ff;">create</span> <span style="color: #0000ff;">table</span><span style="color: #000000;"> department2(<br>
    9. id </span><span style="color: #0000ff;">int</span><span style="color: #000000;">,<br>
    10. name </span><span style="color: #0000ff;">varchar</span>(<span style="color: #800000; font-weight: bold;">20</span><span style="color: #000000;">),<br>
    11. comment </span><span style="color: #0000ff;">varchar</span>(<span style="color: #800000; font-weight: bold;">100</span><span style="color: #000000;">),<br>
    12. </span><span style="color: #0000ff;">unique</span><span style="color: #000000;">(name)<br>
    13. );</span></p>
    14. <p>mysql<span style="color: #808080;">></span> <span style="color: #0000ff;">insert</span> <span style="color: #0000ff;">into</span> department1 <span style="color: #0000ff;">values</span>(<span style="color: #800000; font-weight: bold;">1</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">IT</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">技术</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">);<br>
    15. Query OK, </span><span style="color: #800000; font-weight: bold;">1</span> row affected (<span style="color: #800000; font-weight: bold;">0.00</span><span style="color: #000000;"> sec)<br>
    16. mysql</span><span style="color: #808080;">></span> <span style="color: #0000ff;">insert</span> <span style="color: #0000ff;">into</span> department1 <span style="color: #0000ff;">values</span>(<span style="color: #800000; font-weight: bold;">1</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">IT</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">技术</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">);<br>
    17. ERROR </span><span style="color: #800000; font-weight: bold;">1062</span> (<span style="color: #800000; font-weight: bold;">23000</span>): Duplicate entry <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">IT</span><span style="color: #ff0000;">‘</span> <span style="color: #0000ff;">for</span> <span style="color: #0000ff;">key</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">name</span><span style="color: #ff0000;">‘</span></p></span>

    unique示例 技术图片 
    1. mysql<span style="color: #808080;">></span> <span style="color: #0000ff;">create</span> <span style="color: #0000ff;">table</span> t1(id <span style="color: #0000ff;">int</span> <span style="color: #808080;">not</span> <span style="color: #0000ff;">null</span> <span style="color: #0000ff;">unique</span><span style="color: #000000;">);
    2. Query OK, </span><span style="color: #800000; font-weight: bold;">0</span> rows affected (<span style="color: #800000; font-weight: bold;">0.02</span><span style="color: #000000;"> sec)
    3. <p>mysql<span style="color: #808080;">></span> <span style="color: #0000ff;">desc</span><span style="color: #000000;"> t1;<br>
    4. </span><span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">-----+---------+------+-----+---------+-------+</span><br>
    5. <span style="color: #808080;">|</span> Field <span style="color: #808080;">|</span> Type    <span style="color: #808080;">|</span> <span style="color: #0000ff;">Null</span> <span style="color: #808080;">|</span> <span style="color: #0000ff;">Key</span> <span style="color: #808080;">|</span> <span style="color: #0000ff;">Default</span> <span style="color: #808080;">|</span> Extra <span style="color: #808080;">|</span><br>
    6. <span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">-----+---------+------+-----+---------+-------+</span><br>
    7. <span style="color: #808080;">|</span> id    <span style="color: #808080;">|</span> <span style="color: #0000ff;">int</span>(<span style="color: #800000; font-weight: bold;">11</span>) <span style="color: #808080;">|</span> NO   <span style="color: #808080;">|</span> PRI <span style="color: #808080;">|</span> <span style="color: #0000ff;">NULL</span>    <span style="color: #808080;">|</span>       <span style="color: #808080;">|</span><br>
    8. <span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">-----+---------+------+-----+---------+-------+</span><br>
    9. <span style="color: #800000; font-weight: bold;">1</span> row <span style="color: #808080;">in</span> <span style="color: #0000ff;">set</span> (<span style="color: #800000; font-weight: bold;">0.00</span> sec)</p></span>

    not null 和unique的结合 技术图片 
    1. <span style="color: #0000ff;">create</span> <span style="color: #0000ff;">table</span><span style="color: #000000;"> service(
    2. id </span><span style="color: #0000ff;">int</span> <span style="color: #0000ff;">primary</span> <span style="color: #0000ff;">key</span><span style="color: #000000;"> auto_increment,
    3. name </span><span style="color: #0000ff;">varchar</span>(<span style="color: #800000; font-weight: bold;">20</span><span style="color: #000000;">),
    4. host </span><span style="color: #0000ff;">varchar</span>(<span style="color: #800000; font-weight: bold;">15</span>) <span style="color: #808080;">not</span> <span style="color: #0000ff;">null</span><span style="color: #000000;">,
    5. port </span><span style="color: #0000ff;">int</span> <span style="color: #808080;">not</span> <span style="color: #0000ff;">null</span><span style="color: #000000;">,
    6. </span><span style="color: #0000ff;">unique</span><span style="color: #000000;">(host,port) #联合唯一
    7. );
    8. <p>mysql<span style="color: #808080;">></span> <span style="color: #0000ff;">insert</span> <span style="color: #0000ff;">into</span> service <span style="color: #0000ff;">values</span><br>
    9. <span style="color: #808080;">-></span> (<span style="color: #800000; font-weight: bold;">1</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">nginx</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">192.168.0.10</span><span style="color: #ff0000;">‘</span>,<span style="color: #800000; font-weight: bold;">80</span><span style="color: #000000;">),<br>
    10. </span><span style="color: #808080;">-></span> (<span style="color: #800000; font-weight: bold;">2</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">haproxy</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">192.168.0.20</span><span style="color: #ff0000;">‘</span>,<span style="color: #800000; font-weight: bold;">80</span><span style="color: #000000;">),<br>
    11. </span><span style="color: #808080;">-></span> (<span style="color: #800000; font-weight: bold;">3</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">mysql</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">192.168.0.30</span><span style="color: #ff0000;">‘</span>,<span style="color: #800000; font-weight: bold;">3306</span><span style="color: #000000;">)<br>
    12. </span><span style="color: #808080;">-></span><span style="color: #000000;"> ;<br>
    13. Query OK, </span><span style="color: #800000; font-weight: bold;">3</span> rows affected (<span style="color: #800000; font-weight: bold;">0.01</span><span style="color: #000000;"> sec)<br>
    14. Records: </span><span style="color: #800000; font-weight: bold;">3</span>  Duplicates: <span style="color: #800000; font-weight: bold;">0</span>  Warnings: <span style="color: #800000; font-weight: bold;">0</span><span style="color: #000000;"></span></p>
    15. <p>mysql<span style="color: #808080;">></span> <span style="color: #0000ff;">insert</span> <span style="color: #0000ff;">into</span> service(name,host,port) <span style="color: #0000ff;">values</span>(<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">nginx</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">192.168.0.10</span><span style="color: #ff0000;">‘</span>,<span style="color: #800000; font-weight: bold;">80</span><span style="color: #000000;">);<br>
    16. ERROR </span><span style="color: #800000; font-weight: bold;">1062</span> (<span style="color: #800000; font-weight: bold;">23000</span>): Duplicate entry <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">192.168.0.10-80</span><span style="color: #ff0000;">‘</span> <span style="color: #0000ff;">for</span> <span style="color: #0000ff;">key</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">host</span><span style="color: #ff0000;">‘</span></p></span>

    联合唯一 返回顶部

    PRIMARY KEY

    主键为了保证表中的每一条数据的该字段都是表格中的唯一值。换言之，它是用来独一无二地确认一个表格中的每一行数据。
    主键可以包含一个字段或多个字段。当主键包含多个栏位时，称为组合键 (Composite Key),也可以叫联合主键。
    主键可以在建置新表格时设定 (运用 CREATE TABLE 语句)，或是以改变现有的表格架构方式设定 (运用 ALTER TABLE)。
    主键必须唯一，主键值非空；可以是单一字段，也可以是多字段组合。

    1.单字段主键

    技术图片 
    1. <span style="color: #808080;">============</span>单列做主键<span style="color: #808080;">===============</span><span style="color: #000000;">
    2. #方法一：</span><span style="color: #808080;">not</span> <span style="color: #0000ff;">null</span><span style="color: #808080;">+</span><span style="color: #0000ff;">unique</span>
    3. <span style="color: #0000ff;">create</span> <span style="color: #0000ff;">table</span><span style="color: #000000;"> department1(
    4. id </span><span style="color: #0000ff;">int</span> <span style="color: #808080;">not</span> <span style="color: #0000ff;">null</span> <span style="color: #0000ff;">unique</span><span style="color: #000000;">, #主键
    5. name </span><span style="color: #0000ff;">varchar</span>(<span style="color: #800000; font-weight: bold;">20</span>) <span style="color: #808080;">not</span> <span style="color: #0000ff;">null</span> <span style="color: #0000ff;">unique</span><span style="color: #000000;">,
    6. comment </span><span style="color: #0000ff;">varchar</span>(<span style="color: #800000; font-weight: bold;">100</span><span style="color: #000000;">)
    7. );
    8. <p>mysql<span style="color: #808080;">></span> <span style="color: #0000ff;">desc</span><span style="color: #000000;"> department1;<br>
    9. </span><span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">-------+--------------+------+-----+---------+-------+</span><br>
    10. <span style="color: #808080;">|</span> Field   <span style="color: #808080;">|</span> Type         <span style="color: #808080;">|</span> <span style="color: #0000ff;">Null</span> <span style="color: #808080;">|</span> <span style="color: #0000ff;">Key</span> <span style="color: #808080;">|</span> <span style="color: #0000ff;">Default</span> <span style="color: #808080;">|</span> Extra <span style="color: #808080;">|</span><br>
    11. <span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">-------+--------------+------+-----+---------+-------+</span><br>
    12. <span style="color: #808080;">|</span> id      <span style="color: #808080;">|</span> <span style="color: #0000ff;">int</span>(<span style="color: #800000; font-weight: bold;">11</span>)      <span style="color: #808080;">|</span> NO   <span style="color: #808080;">|</span> PRI <span style="color: #808080;">|</span> <span style="color: #0000ff;">NULL</span>    <span style="color: #808080;">|</span>       <span style="color: #808080;">|</span><br>
    13. <span style="color: #808080;">|</span> name    <span style="color: #808080;">|</span> <span style="color: #0000ff;">varchar</span>(<span style="color: #800000; font-weight: bold;">20</span>)  <span style="color: #808080;">|</span> NO   <span style="color: #808080;">|</span> UNI <span style="color: #808080;">|</span> <span style="color: #0000ff;">NULL</span>    <span style="color: #808080;">|</span>       <span style="color: #808080;">|</span><br>
    14. <span style="color: #808080;">|</span> comment <span style="color: #808080;">|</span> <span style="color: #0000ff;">varchar</span>(<span style="color: #800000; font-weight: bold;">100</span>) <span style="color: #808080;">|</span> YES  <span style="color: #808080;">|</span>     <span style="color: #808080;">|</span> <span style="color: #0000ff;">NULL</span>    <span style="color: #808080;">|</span>       <span style="color: #808080;">|</span><br>
    15. <span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">-------+--------------+------+-----+---------+-------+</span><br>
    16. rows <span style="color: #808080;">in</span> <span style="color: #0000ff;">set</span> (<span style="color: #800000; font-weight: bold;">0.01</span><span style="color: #000000;"> sec)</span></p>
    17. <h1 id="方法二：在某一个字段后用primary-key">方法二：在某一个字段后用primary <span style="color: #0000ff;">key</span></h1>
    18. <p><span style="color: #0000ff;">create</span> <span style="color: #0000ff;">table</span><span style="color: #000000;"> department2(<br>
    19. id </span><span style="color: #0000ff;">int</span> <span style="color: #0000ff;">primary</span> <span style="color: #0000ff;">key</span><span style="color: #000000;">, #主键<br>
    20. name </span><span style="color: #0000ff;">varchar</span>(<span style="color: #800000; font-weight: bold;">20</span><span style="color: #000000;">),<br>
    21. comment </span><span style="color: #0000ff;">varchar</span>(<span style="color: #800000; font-weight: bold;">100</span><span style="color: #000000;">)<br>
    22. );</span></p>
    23. <p>mysql<span style="color: #808080;">></span> <span style="color: #0000ff;">desc</span><span style="color: #000000;"> department2;<br>
    24. </span><span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">-------+--------------+------+-----+---------+-------+</span><br>
    25. <span style="color: #808080;">|</span> Field   <span style="color: #808080;">|</span> Type         <span style="color: #808080;">|</span> <span style="color: #0000ff;">Null</span> <span style="color: #808080;">|</span> <span style="color: #0000ff;">Key</span> <span style="color: #808080;">|</span> <span style="color: #0000ff;">Default</span> <span style="color: #808080;">|</span> Extra <span style="color: #808080;">|</span><br>
    26. <span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">-------+--------------+------+-----+---------+-------+</span><br>
    27. <span style="color: #808080;">|</span> id      <span style="color: #808080;">|</span> <span style="color: #0000ff;">int</span>(<span style="color: #800000; font-weight: bold;">11</span>)      <span style="color: #808080;">|</span> NO   <span style="color: #808080;">|</span> PRI <span style="color: #808080;">|</span> <span style="color: #0000ff;">NULL</span>    <span style="color: #808080;">|</span>       <span style="color: #808080;">|</span><br>
    28. <span style="color: #808080;">|</span> name    <span style="color: #808080;">|</span> <span style="color: #0000ff;">varchar</span>(<span style="color: #800000; font-weight: bold;">20</span>)  <span style="color: #808080;">|</span> YES  <span style="color: #808080;">|</span>     <span style="color: #808080;">|</span> <span style="color: #0000ff;">NULL</span>    <span style="color: #808080;">|</span>       <span style="color: #808080;">|</span><br>
    29. <span style="color: #808080;">|</span> comment <span style="color: #808080;">|</span> <span style="color: #0000ff;">varchar</span>(<span style="color: #800000; font-weight: bold;">100</span>) <span style="color: #808080;">|</span> YES  <span style="color: #808080;">|</span>     <span style="color: #808080;">|</span> <span style="color: #0000ff;">NULL</span>    <span style="color: #808080;">|</span>       <span style="color: #808080;">|</span><br>
    30. <span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">-------+--------------+------+-----+---------+-------+</span><br>
    31. rows <span style="color: #808080;">in</span> <span style="color: #0000ff;">set</span> (<span style="color: #800000; font-weight: bold;">0.00</span><span style="color: #000000;"> sec)</span></p>
    32. <h1 id="方法三：在所有字段后单独定义primary-key">方法三：在所有字段后单独定义primary <span style="color: #0000ff;">key</span></h1>
    33. <p><span style="color: #0000ff;">create</span> <span style="color: #0000ff;">table</span><span style="color: #000000;"> department3(<br>
    34. id </span><span style="color: #0000ff;">int</span><span style="color: #000000;">,<br>
    35. name </span><span style="color: #0000ff;">varchar</span>(<span style="color: #800000; font-weight: bold;">20</span><span style="color: #000000;">),<br>
    36. comment </span><span style="color: #0000ff;">varchar</span>(<span style="color: #800000; font-weight: bold;">100</span><span style="color: #000000;">),<br>
    37. </span><span style="color: #0000ff;">primary</span> <span style="color: #0000ff;">key</span><span style="color: #000000;">(id); #创建主键并为其命名pk_name</span></p>
    38. <p>mysql<span style="color: #808080;">></span> <span style="color: #0000ff;">desc</span><span style="color: #000000;"> department3;<br>
    39. </span><span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">-------+--------------+------+-----+---------+-------+</span><br>
    40. <span style="color: #808080;">|</span> Field   <span style="color: #808080;">|</span> Type         <span style="color: #808080;">|</span> <span style="color: #0000ff;">Null</span> <span style="color: #808080;">|</span> <span style="color: #0000ff;">Key</span> <span style="color: #808080;">|</span> <span style="color: #0000ff;">Default</span> <span style="color: #808080;">|</span> Extra <span style="color: #808080;">|</span><br>
    41. <span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">-------+--------------+------+-----+---------+-------+</span><br>
    42. <span style="color: #808080;">|</span> id      <span style="color: #808080;">|</span> <span style="color: #0000ff;">int</span>(<span style="color: #800000; font-weight: bold;">11</span>)      <span style="color: #808080;">|</span> NO   <span style="color: #808080;">|</span> PRI <span style="color: #808080;">|</span> <span style="color: #0000ff;">NULL</span>    <span style="color: #808080;">|</span>       <span style="color: #808080;">|</span><br>
    43. <span style="color: #808080;">|</span> name    <span style="color: #808080;">|</span> <span style="color: #0000ff;">varchar</span>(<span style="color: #800000; font-weight: bold;">20</span>)  <span style="color: #808080;">|</span> YES  <span style="color: #808080;">|</span>     <span style="color: #808080;">|</span> <span style="color: #0000ff;">NULL</span>    <span style="color: #808080;">|</span>       <span style="color: #808080;">|</span><br>
    44. <span style="color: #808080;">|</span> comment <span style="color: #808080;">|</span> <span style="color: #0000ff;">varchar</span>(<span style="color: #800000; font-weight: bold;">100</span>) <span style="color: #808080;">|</span> YES  <span style="color: #808080;">|</span>     <span style="color: #808080;">|</span> <span style="color: #0000ff;">NULL</span>    <span style="color: #808080;">|</span>       <span style="color: #808080;">|</span><br>
    45. <span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">-------+--------------+------+-----+---------+-------+</span><br>
    46. rows <span style="color: #808080;">in</span> <span style="color: #0000ff;">set</span> (<span style="color: #800000; font-weight: bold;">0.01</span><span style="color: #000000;"> sec)</span></p>
    47. <h1 id="方法四：给已经建成的表添加主键约束">方法四：给已经建成的表添加主键约束</h1>
    48. <p>mysql<span style="color: #808080;">></span> <span style="color: #0000ff;">create</span> <span style="color: #0000ff;">table</span><span style="color: #000000;"> department4(<br>
    49. </span><span style="color: #808080;">-></span> id <span style="color: #0000ff;">int</span><span style="color: #000000;">,<br>
    50. </span><span style="color: #808080;">-></span> name <span style="color: #0000ff;">varchar</span>(<span style="color: #800000; font-weight: bold;">20</span><span style="color: #000000;">),<br>
    51. </span><span style="color: #808080;">-></span> comment <span style="color: #0000ff;">varchar</span>(<span style="color: #800000; font-weight: bold;">100</span><span style="color: #000000;">));<br>
    52. Query OK, </span><span style="color: #800000; font-weight: bold;">0</span> rows affected (<span style="color: #800000; font-weight: bold;">0.01</span><span style="color: #000000;"> sec)</span></p>
    53. <p>mysql<span style="color: #808080;">></span> <span style="color: #0000ff;">desc</span><span style="color: #000000;"> department4;<br>
    54. </span><span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">-------+--------------+------+-----+---------+-------+</span><br>
    55. <span style="color: #808080;">|</span> Field   <span style="color: #808080;">|</span> Type         <span style="color: #808080;">|</span> <span style="color: #0000ff;">Null</span> <span style="color: #808080;">|</span> <span style="color: #0000ff;">Key</span> <span style="color: #808080;">|</span> <span style="color: #0000ff;">Default</span> <span style="color: #808080;">|</span> Extra <span style="color: #808080;">|</span><br>
    56. <span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">-------+--------------+------+-----+---------+-------+</span><br>
    57. <span style="color: #808080;">|</span> id      <span style="color: #808080;">|</span> <span style="color: #0000ff;">int</span>(<span style="color: #800000; font-weight: bold;">11</span>)      <span style="color: #808080;">|</span> YES  <span style="color: #808080;">|</span>     <span style="color: #808080;">|</span> <span style="color: #0000ff;">NULL</span>    <span style="color: #808080;">|</span>       <span style="color: #808080;">|</span><br>
    58. <span style="color: #808080;">|</span> name    <span style="color: #808080;">|</span> <span style="color: #0000ff;">varchar</span>(<span style="color: #800000; font-weight: bold;">20</span>)  <span style="color: #808080;">|</span> YES  <span style="color: #808080;">|</span>     <span style="color: #808080;">|</span> <span style="color: #0000ff;">NULL</span>    <span style="color: #808080;">|</span>       <span style="color: #808080;">|</span><br>
    59. <span style="color: #808080;">|</span> comment <span style="color: #808080;">|</span> <span style="color: #0000ff;">varchar</span>(<span style="color: #800000; font-weight: bold;">100</span>) <span style="color: #808080;">|</span> YES  <span style="color: #808080;">|</span>     <span style="color: #808080;">|</span> <span style="color: #0000ff;">NULL</span>    <span style="color: #808080;">|</span>       <span style="color: #808080;">|</span><br>
    60. <span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">-------+--------------+------+-----+---------+-------+</span><br>
    61. <span style="color: #800000; font-weight: bold;">3</span> rows <span style="color: #808080;">in</span> <span style="color: #0000ff;">set</span> (<span style="color: #800000; font-weight: bold;">0.01</span><span style="color: #000000;"> sec)</span></p>
    62. <p>mysql<span style="color: #808080;">></span> <span style="color: #0000ff;">alter</span> <span style="color: #0000ff;">table</span> department4 modify id <span style="color: #0000ff;">int</span> <span style="color: #0000ff;">primary</span> <span style="color: #0000ff;">key</span><span style="color: #000000;">;<br>
    63. Query OK, </span><span style="color: #800000; font-weight: bold;">0</span> rows affected (<span style="color: #800000; font-weight: bold;">0.02</span><span style="color: #000000;"> sec)<br>
    64. Records: </span><span style="color: #800000; font-weight: bold;">0</span>  Duplicates: <span style="color: #800000; font-weight: bold;">0</span>  Warnings: <span style="color: #800000; font-weight: bold;">0</span><span style="color: #000000;"></span></p>
    65. <p>mysql<span style="color: #808080;">></span> <span style="color: #0000ff;">desc</span><span style="color: #000000;"> department4;<br>
    66. </span><span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">-------+--------------+------+-----+---------+-------+</span><br>
    67. <span style="color: #808080;">|</span> Field   <span style="color: #808080;">|</span> Type         <span style="color: #808080;">|</span> <span style="color: #0000ff;">Null</span> <span style="color: #808080;">|</span> <span style="color: #0000ff;">Key</span> <span style="color: #808080;">|</span> <span style="color: #0000ff;">Default</span> <span style="color: #808080;">|</span> Extra <span style="color: #808080;">|</span><br>
    68. <span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">-------+--------------+------+-----+---------+-------+</span><br>
    69. <span style="color: #808080;">|</span> id      <span style="color: #808080;">|</span> <span style="color: #0000ff;">int</span>(<span style="color: #800000; font-weight: bold;">11</span>)      <span style="color: #808080;">|</span> NO   <span style="color: #808080;">|</span> PRI <span style="color: #808080;">|</span> <span style="color: #0000ff;">NULL</span>    <span style="color: #808080;">|</span>       <span style="color: #808080;">|</span><br>
    70. <span style="color: #808080;">|</span> name    <span style="color: #808080;">|</span> <span style="color: #0000ff;">varchar</span>(<span style="color: #800000; font-weight: bold;">20</span>)  <span style="color: #808080;">|</span> YES  <span style="color: #808080;">|</span>     <span style="color: #808080;">|</span> <span style="color: #0000ff;">NULL</span>    <span style="color: #808080;">|</span>       <span style="color: #808080;">|</span><br>
    71. <span style="color: #808080;">|</span> comment <span style="color: #808080;">|</span> <span style="color: #0000ff;">varchar</span>(<span style="color: #800000; font-weight: bold;">100</span>) <span style="color: #808080;">|</span> YES  <span style="color: #808080;">|</span>     <span style="color: #808080;">|</span> <span style="color: #0000ff;">NULL</span>    <span style="color: #808080;">|</span>       <span style="color: #808080;">|</span><br>
    72. <span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">-------+--------------+------+-----+---------+-------+</span><br>
    73. <span style="color: #800000; font-weight: bold;">3</span> rows <span style="color: #808080;">in</span> <span style="color: #0000ff;">set</span> (<span style="color: #800000; font-weight: bold;">0.01</span> sec)</p></span>

    单字段主键

    2.多字段主键

    技术图片 
    1. <span style="color: #808080;">==================</span>多列做主键<span style="color: #808080;">================</span>
    2. <span style="color: #0000ff;">create</span> <span style="color: #0000ff;">table</span><span style="color: #000000;"> service(
    3. ip </span><span style="color: #0000ff;">varchar</span>(<span style="color: #800000; font-weight: bold;">15</span><span style="color: #000000;">),
    4. port </span><span style="color: #0000ff;">char</span>(<span style="color: #800000; font-weight: bold;">5</span><span style="color: #000000;">),
    5. service_name </span><span style="color: #0000ff;">varchar</span>(<span style="color: #800000; font-weight: bold;">10</span>) <span style="color: #808080;">not</span> <span style="color: #0000ff;">null</span><span style="color: #000000;">,
    6. </span><span style="color: #0000ff;">primary</span> <span style="color: #0000ff;">key</span><span style="color: #000000;">(ip,port)
    7. );
    8. <p>mysql<span style="color: #808080;">></span> <span style="color: #0000ff;">desc</span><span style="color: #000000;"> service;<br>
    9. </span><span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">------------+-------------+------+-----+---------+-------+</span><br>
    10. <span style="color: #808080;">|</span> Field        <span style="color: #808080;">|</span> Type        <span style="color: #808080;">|</span> <span style="color: #0000ff;">Null</span> <span style="color: #808080;">|</span> <span style="color: #0000ff;">Key</span> <span style="color: #808080;">|</span> <span style="color: #0000ff;">Default</span> <span style="color: #808080;">|</span> Extra <span style="color: #808080;">|</span><br>
    11. <span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">------------+-------------+------+-----+---------+-------+</span><br>
    12. <span style="color: #808080;">|</span> ip           <span style="color: #808080;">|</span> <span style="color: #0000ff;">varchar</span>(<span style="color: #800000; font-weight: bold;">15</span>) <span style="color: #808080;">|</span> NO   <span style="color: #808080;">|</span> PRI <span style="color: #808080;">|</span> <span style="color: #0000ff;">NULL</span>    <span style="color: #808080;">|</span>       <span style="color: #808080;">|</span><br>
    13. <span style="color: #808080;">|</span> port         <span style="color: #808080;">|</span> <span style="color: #0000ff;">char</span>(<span style="color: #800000; font-weight: bold;">5</span>)     <span style="color: #808080;">|</span> NO   <span style="color: #808080;">|</span> PRI <span style="color: #808080;">|</span> <span style="color: #0000ff;">NULL</span>    <span style="color: #808080;">|</span>       <span style="color: #808080;">|</span><br>
    14. <span style="color: #808080;">|</span> service_name <span style="color: #808080;">|</span> <span style="color: #0000ff;">varchar</span>(<span style="color: #800000; font-weight: bold;">10</span>) <span style="color: #808080;">|</span> NO   <span style="color: #808080;">|</span>     <span style="color: #808080;">|</span> <span style="color: #0000ff;">NULL</span>    <span style="color: #808080;">|</span>       <span style="color: #808080;">|</span><br>
    15. <span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">------------+-------------+------+-----+---------+-------+</span><br>
    16. <span style="color: #800000; font-weight: bold;">3</span> rows <span style="color: #808080;">in</span> <span style="color: #0000ff;">set</span> (<span style="color: #800000; font-weight: bold;">0.00</span><span style="color: #000000;"> sec)</span></p>
    17. <p>mysql<span style="color: #808080;">></span> <span style="color: #0000ff;">insert</span> <span style="color: #0000ff;">into</span> service <span style="color: #0000ff;">values</span><br>
    18. <span style="color: #808080;">-></span> (<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">172.16.45.10</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">3306</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">mysqld</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">),<br>
    19. </span><span style="color: #808080;">-></span> (<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">172.16.45.11</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">3306</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">mariadb</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">)<br>
    20. </span><span style="color: #808080;">-></span><span style="color: #000000;"> ;<br>
    21. Query OK, </span><span style="color: #800000; font-weight: bold;">2</span> rows affected (<span style="color: #800000; font-weight: bold;">0.00</span><span style="color: #000000;"> sec)<br>
    22. Records: </span><span style="color: #800000; font-weight: bold;">2</span>  Duplicates: <span style="color: #800000; font-weight: bold;">0</span>  Warnings: <span style="color: #800000; font-weight: bold;">0</span><span style="color: #000000;"></span></p>
    23. <p>mysql<span style="color: #808080;">></span> <span style="color: #0000ff;">insert</span> <span style="color: #0000ff;">into</span> service <span style="color: #0000ff;">values</span> (<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">172.16.45.10</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">3306</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">nginx</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">);<br>
    24. ERROR </span><span style="color: #800000; font-weight: bold;">1062</span> (<span style="color: #800000; font-weight: bold;">23000</span>): Duplicate entry <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">172.16.45.10-3306</span><span style="color: #ff0000;">‘</span> <span style="color: #0000ff;">for</span> <span style="color: #0000ff;">key</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">PRIMARY</span><span style="color: #ff0000;">‘</span></p></span>

    多字段主键

    AUTO_INCREMENT

    约束字段为自动增长，被约束的字段必须同时被key约束

    技术图片 
    1. <span style="color: #000000;">#不指定id，则自动增长
    2. </span><span style="color: #0000ff;">create</span> <span style="color: #0000ff;">table</span><span style="color: #000000;"> student(
    3. id </span><span style="color: #0000ff;">int</span> <span style="color: #0000ff;">primary</span> <span style="color: #0000ff;">key</span><span style="color: #000000;"> auto_increment,
    4. name </span><span style="color: #0000ff;">varchar</span>(<span style="color: #800000; font-weight: bold;">20</span><span style="color: #000000;">),
    5. sex enum(</span><span style="color: #ff0000;">‘</span><span style="color: #ff0000;">male</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">female</span><span style="color: #ff0000;">‘</span>) <span style="color: #0000ff;">default</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">male</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">
    6. );
    7. <p>mysql<span style="color: #808080;">></span> <span style="color: #0000ff;">desc</span><span style="color: #000000;"> student;<br>
    8. </span><span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">-----+-----------------------+------+-----+---------+----------------+</span><br>
    9. <span style="color: #808080;">|</span> Field <span style="color: #808080;">|</span> Type                  <span style="color: #808080;">|</span> <span style="color: #0000ff;">Null</span> <span style="color: #808080;">|</span> <span style="color: #0000ff;">Key</span> <span style="color: #808080;">|</span> <span style="color: #0000ff;">Default</span> <span style="color: #808080;">|</span> Extra          <span style="color: #808080;">|</span><br>
    10. <span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">-----+-----------------------+------+-----+---------+----------------+</span><br>
    11. <span style="color: #808080;">|</span> id    <span style="color: #808080;">|</span> <span style="color: #0000ff;">int</span>(<span style="color: #800000; font-weight: bold;">11</span>)               <span style="color: #808080;">|</span> NO   <span style="color: #808080;">|</span> PRI <span style="color: #808080;">|</span> <span style="color: #0000ff;">NULL</span>    <span style="color: #808080;">|</span> auto_increment <span style="color: #808080;">|</span><br>
    12. <span style="color: #808080;">|</span> name  <span style="color: #808080;">|</span> <span style="color: #0000ff;">varchar</span>(<span style="color: #800000; font-weight: bold;">20</span>)           <span style="color: #808080;">|</span> YES  <span style="color: #808080;">|</span>     <span style="color: #808080;">|</span> <span style="color: #0000ff;">NULL</span>    <span style="color: #808080;">|</span>                <span style="color: #808080;">|</span><br>
    13. <span style="color: #808080;">|</span> sex   <span style="color: #808080;">|</span> enum(<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">male</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">female</span><span style="color: #ff0000;">‘</span>) <span style="color: #808080;">|</span> YES  <span style="color: #808080;">|</span>     <span style="color: #808080;">|</span> male    <span style="color: #808080;">|</span>                <span style="color: #808080;">|</span><br>
    14. <span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">-----+-----------------------+------+-----+---------+----------------+</span><br>
    15. mysql<span style="color: #808080;">></span> <span style="color: #0000ff;">insert</span> <span style="color: #0000ff;">into</span> student(name) <span style="color: #0000ff;">values</span><br>
    16. <span style="color: #808080;">-></span> (<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">egon</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">),<br>
    17. </span><span style="color: #808080;">-></span> (<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">alex</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">)<br>
    18. </span><span style="color: #808080;">-></span><span style="color: #000000;"> ;</span></p>
    19. <p>mysql<span style="color: #808080;">></span> <span style="color: #0000ff;">select</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">from</span><span style="color: #000000;"> student;<br>
    20. </span><span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">--+------+------+</span><br>
    21. <span style="color: #808080;">|</span> id <span style="color: #808080;">|</span> name <span style="color: #808080;">|</span> sex  <span style="color: #808080;">|</span><br>
    22. <span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">--+------+------+</span><br>
    23. <span style="color: #808080;">|</span>  <span style="color: #800000; font-weight: bold;">1</span> <span style="color: #808080;">|</span> egon <span style="color: #808080;">|</span> male <span style="color: #808080;">|</span><br>
    24. <span style="color: #808080;">|</span>  <span style="color: #800000; font-weight: bold;">2</span> <span style="color: #808080;">|</span> alex <span style="color: #808080;">|</span> male <span style="color: #808080;">|</span><br>
    25. <span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">--+------+------+</span><br>
    26. <span style="color: #000000;"></span></p>
    27. <h1 id="也可以指定id">也可以指定id</h1>
    28. <p>mysql<span style="color: #808080;">></span> <span style="color: #0000ff;">insert</span> <span style="color: #0000ff;">into</span> student <span style="color: #0000ff;">values</span>(<span style="color: #800000; font-weight: bold;">4</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">asb</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">female</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">);<br>
    29. Query OK, </span><span style="color: #800000; font-weight: bold;">1</span> row affected (<span style="color: #800000; font-weight: bold;">0.00</span><span style="color: #000000;"> sec)</span></p>
    30. <p>mysql<span style="color: #808080;">></span> <span style="color: #0000ff;">insert</span> <span style="color: #0000ff;">into</span> student <span style="color: #0000ff;">values</span>(<span style="color: #800000; font-weight: bold;">7</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">wsb</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">female</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">);<br>
    31. Query OK, </span><span style="color: #800000; font-weight: bold;">1</span> row affected (<span style="color: #800000; font-weight: bold;">0.00</span><span style="color: #000000;"> sec)</span></p>
    32. <p>mysql<span style="color: #808080;">></span> <span style="color: #0000ff;">select</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">from</span><span style="color: #000000;"> student;<br>
    33. </span><span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">--+------+--------+</span><br>
    34. <span style="color: #808080;">|</span> id <span style="color: #808080;">|</span> name <span style="color: #808080;">|</span> sex    <span style="color: #808080;">|</span><br>
    35. <span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">--+------+--------+</span><br>
    36. <span style="color: #808080;">|</span>  <span style="color: #800000; font-weight: bold;">1</span> <span style="color: #808080;">|</span> egon <span style="color: #808080;">|</span> male   <span style="color: #808080;">|</span><br>
    37. <span style="color: #808080;">|</span>  <span style="color: #800000; font-weight: bold;">2</span> <span style="color: #808080;">|</span> alex <span style="color: #808080;">|</span> male   <span style="color: #808080;">|</span><br>
    38. <span style="color: #808080;">|</span>  <span style="color: #800000; font-weight: bold;">4</span> <span style="color: #808080;">|</span> asb  <span style="color: #808080;">|</span> female <span style="color: #808080;">|</span><br>
    39. <span style="color: #808080;">|</span>  <span style="color: #800000; font-weight: bold;">7</span> <span style="color: #808080;">|</span> wsb  <span style="color: #808080;">|</span> female <span style="color: #808080;">|</span><br>
    40. <span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">--+------+--------+</span><br>
    41. <span style="color: #000000;"></span></p>
    42. <h1 id="对于自增的字段，在用delete删除后，再插入值，该字段仍按照删除前的位置继续增长">对于自增的字段，在用delete删除后，再插入值，该字段仍按照删除前的位置继续增长</h1>
    43. <p>mysql<span style="color: #808080;">></span> <span style="color: #0000ff;">delete</span> <span style="color: #0000ff;">from</span><span style="color: #000000;"> student;<br>
    44. Query OK, </span><span style="color: #800000; font-weight: bold;">4</span> rows affected (<span style="color: #800000; font-weight: bold;">0.00</span><span style="color: #000000;"> sec)</span></p>
    45. <p>mysql<span style="color: #808080;">></span> <span style="color: #0000ff;">select</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">from</span><span style="color: #000000;"> student;<br>
    46. Empty </span><span style="color: #0000ff;">set</span> (<span style="color: #800000; font-weight: bold;">0.00</span><span style="color: #000000;"> sec)</span></p>
    47. <p>mysql<span style="color: #808080;">></span> <span style="color: #0000ff;">insert</span> <span style="color: #0000ff;">into</span> student(name) <span style="color: #0000ff;">values</span>(<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">ysb</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">);<br>
    48. mysql</span><span style="color: #808080;">></span> <span style="color: #0000ff;">select</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">from</span><span style="color: #000000;"> student;<br>
    49. </span><span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">--+------+------+</span><br>
    50. <span style="color: #808080;">|</span> id <span style="color: #808080;">|</span> name <span style="color: #808080;">|</span> sex  <span style="color: #808080;">|</span><br>
    51. <span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">--+------+------+</span><br>
    52. <span style="color: #808080;">|</span>  <span style="color: #800000; font-weight: bold;">8</span> <span style="color: #808080;">|</span> ysb  <span style="color: #808080;">|</span> male <span style="color: #808080;">|</span><br>
    53. <span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">--+------+------+</span><br>
    54. <span style="color: #000000;"></span></p>
    55. <h1 id="应该用truncate清空表，比起delete一条一条地删除记录，truncate是直接清空表，在删除大表时用它">应该用truncate清空表，比起delete一条一条地删除记录，truncate是直接清空表，在删除大表时用它</h1>
    56. <p>mysql<span style="color: #808080;">></span> <span style="color: #0000ff;">truncate</span><span style="color: #000000;"> student;<br>
    57. Query OK, </span><span style="color: #800000; font-weight: bold;">0</span> rows affected (<span style="color: #800000; font-weight: bold;">0.01</span><span style="color: #000000;"> sec)</span></p>
    58. <p>mysql<span style="color: #808080;">></span> <span style="color: #0000ff;">insert</span> <span style="color: #0000ff;">into</span> student(name) <span style="color: #0000ff;">values</span>(<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">egon</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">);<br>
    59. Query OK, </span><span style="color: #800000; font-weight: bold;">1</span> row affected (<span style="color: #800000; font-weight: bold;">0.01</span><span style="color: #000000;"> sec)</span></p>
    60. <p>mysql<span style="color: #808080;">></span> <span style="color: #0000ff;">select</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">from</span><span style="color: #000000;"> student;<br>
    61. </span><span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">--+------+------+</span><br>
    62. <span style="color: #808080;">|</span> id <span style="color: #808080;">|</span> name <span style="color: #808080;">|</span> sex  <span style="color: #808080;">|</span><br>
    63. <span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">--+------+------+</span><br>
    64. <span style="color: #808080;">|</span>  <span style="color: #800000; font-weight: bold;">1</span> <span style="color: #808080;">|</span> egon <span style="color: #808080;">|</span> male <span style="color: #808080;">|</span><br>
    65. <span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">--+------+------+</span><br>
    66. row <span style="color: #808080;">in</span> <span style="color: #0000ff;">set</span> (<span style="color: #800000; font-weight: bold;">0.00</span> sec)</p></span>

    设置auto_increment

    了解知识

    技术图片 
    1. <span style="color: #000000;">#在创建完表后，修改自增字段的起始值
    2. mysql</span><span style="color: #808080;">></span> <span style="color: #0000ff;">create</span> <span style="color: #0000ff;">table</span><span style="color: #000000;"> student(
    3.     </span><span style="color: #808080;">-></span> id <span style="color: #0000ff;">int</span> <span style="color: #0000ff;">primary</span> <span style="color: #0000ff;">key</span><span style="color: #000000;"> auto_increment,
    4.     </span><span style="color: #808080;">-></span> name <span style="color: #0000ff;">varchar</span>(<span style="color: #800000; font-weight: bold;">20</span><span style="color: #000000;">),
    5.     </span><span style="color: #808080;">-></span> sex enum(<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">male</span><span style="color: #ff0000;">‘</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">female</span><span style="color: #ff0000;">‘</span>) <span style="color:                    </div>
    6.                   
    7.  
    8.                     <div class=" "="">
    9.                         <ul class="m-news-opt fix">
    10.                             <li class="opt-item">
    11.                                 <a href="/sql_question-384343.html" target="_blank"><p>< 上一篇</p><p class="ellipsis">MySQL 索引</p></a>
    12.                             </li>
    13.                             <li class="opt-item ta-r">
    14.                                  <a href="/sql_question-384345.html" target="_blank"><p>下一篇 ></p><p class="ellipsis">SqlServer优化之批量插入（SqlBulkCopy、表值参数）</p></a>
    15.                             </li>
    16.                         </ul>
    17.                     </span>

    人气教程排行

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