时间:2021-07-01 10:21:17 帮助过:26人阅读
为了防止不符合规范的数据进入数据库,在用户对数据进行插入、修改、删除等操作时,DBMS自动按照一定的约束条件对数据进行监测,使不符合规范的数据不能进入数据库,以确保数据库中存储的数据正确、有效、相容。
约束条件与数据类型的宽度一样,都是可选参数,主要分为以下几种:
返回顶部
- # <span style="color: #0000ff;">NOT</span> <span style="color: #0000ff;">NULL</span><span style="color: #000000;"> :非空约束,指定某列不能为空;
- # </span><span style="color: #0000ff;">UNIQUE</span><span style="color: #000000;"> : 唯一约束,指定某列或者几列组合不能重复
- # </span><span style="color: #0000ff;">PRIMARY</span> <span style="color: #0000ff;">KEY</span><span style="color: #000000;"> :主键,指定该列的值可以唯一地标识该列记录
- # </span><span style="color: #0000ff;">FOREIGN</span> <span style="color: #0000ff;">KEY</span> :外键,指定该行记录从属于主表中的一条记录,主要用于参照完整性
是否可空,null表示空,非字符串
not null - 不可空
null - 可空
not null示例
- 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;">);
- 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)
- <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>
- 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>
- <p>mysql<span style="color: #808080;">></span> <span style="color: #0000ff;">desc</span><span style="color: #000000;"> t12;<br>
- </span><span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">-----+---------+------+-----+---------+-------+</span><br>
- <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>
- <span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">-----+---------+------+-----+---------+-------+</span><br>
- <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>
- <span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">-----+---------+------+-----+---------+-------+</span><br>
- <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>
- <h1 id="不能向id列插入空元素。">不能向id列插入空元素。</h1>
- <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>
- 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>
- <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>
- 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 示例
- 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;">);
- 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)
- <p>mysql<span style="color: #808080;">></span> <span style="color: #0000ff;">desc</span><span style="color: #000000;"> t13;<br>
- </span><span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">-----+---------+------+-----+---------+-------+</span><br>
- <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>
- <span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">-----+---------+------+-----+---------+-------+</span><br>
- <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>
- <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>
- <span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">-----+---------+------+-----+---------+-------+</span><br>
- <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>
- <h1 id="只向id1字段添加值,会发现id2字段会使用默认值填充">只向id1字段添加值,会发现id2字段会使用默认值填充</h1>
- <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>
- 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>
- <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>
- </span><span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">---+-----+</span><br>
- <span style="color: #808080;">|</span> id1 <span style="color: #808080;">|</span> id2 <span style="color: #808080;">|</span><br>
- <span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">---+-----+</span><br>
- <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>
- <span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">---+-----+</span><br>
- <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>
- <h1 id="id1字段不能为空,所以不能单独向id2字段填充值;">id1字段不能为空,所以不能单独向id2字段填充值;</h1>
- <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>
- 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>
- <h1 id="向id1,id2中分别填充数据,id2的填充数据会覆盖默认值">向id1,id2中分别填充数据,id2的填充数据会覆盖默认值</h1>
- <p>mysql> insert into t13 (id1,id2) values (112,223);<br>
- Query OK, 1 row affected (0.00 sec)</p>
- <p>mysql> select * from t13;<br>
- +-----+-----+<br>
- | id1 | id2 |<br>
- +-----+-----+<br>
- | 111 | 222 |<br>
- | 112 | 223 |<br>
- +-----+-----+<br>
- 2 rows in set (0.00 sec)</p></span>
not null不生效 返回顶部
- <span style="color: #000000;">设置严格模式:
- 不支持对not null字段插入null值
- 不支持对自增长字段插入”值
- 不支持text字段有默认值
- <p>直接在mysql中生效(重启失效):<br>
- 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>
- <p>配置文件添加(永久失效):<br>
- 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>
唯一约束,指定某列或者几列组合不能重复
unique示例
- <span style="color: #000000;">方法一:
- </span><span style="color: #0000ff;">create</span> <span style="color: #0000ff;">table</span><span style="color: #000000;"> department1(
- id </span><span style="color: #0000ff;">int</span><span style="color: #000000;">,
- 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;">,
- comment </span><span style="color: #0000ff;">varchar</span>(<span style="color: #800000; font-weight: bold;">100</span><span style="color: #000000;">)
- );
- <p>方法二:<br>
- <span style="color: #0000ff;">create</span> <span style="color: #0000ff;">table</span><span style="color: #000000;"> department2(<br>
- id </span><span style="color: #0000ff;">int</span><span style="color: #000000;">,<br>
- name </span><span style="color: #0000ff;">varchar</span>(<span style="color: #800000; font-weight: bold;">20</span><span style="color: #000000;">),<br>
- comment </span><span style="color: #0000ff;">varchar</span>(<span style="color: #800000; font-weight: bold;">100</span><span style="color: #000000;">),<br>
- </span><span style="color: #0000ff;">unique</span><span style="color: #000000;">(name)<br>
- );</span></p>
- <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>
- 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>
- 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>
- 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>
not null 和unique的结合
- 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;">);
- 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)
- <p>mysql<span style="color: #808080;">></span> <span style="color: #0000ff;">desc</span><span style="color: #000000;"> t1;<br>
- </span><span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">-----+---------+------+-----+---------+-------+</span><br>
- <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>
- <span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">-----+---------+------+-----+---------+-------+</span><br>
- <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>
- <span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">-----+---------+------+-----+---------+-------+</span><br>
- <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>
联合唯一 返回顶部
- <span style="color: #0000ff;">create</span> <span style="color: #0000ff;">table</span><span style="color: #000000;"> service(
- 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,
- name </span><span style="color: #0000ff;">varchar</span>(<span style="color: #800000; font-weight: bold;">20</span><span style="color: #000000;">),
- 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;">,
- port </span><span style="color: #0000ff;">int</span> <span style="color: #808080;">not</span> <span style="color: #0000ff;">null</span><span style="color: #000000;">,
- </span><span style="color: #0000ff;">unique</span><span style="color: #000000;">(host,port) #联合唯一
- );
- <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>
- <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>
- </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>
- </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>
- </span><span style="color: #808080;">-></span><span style="color: #000000;"> ;<br>
- 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>
- 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>
- <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>
- 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>
主键为了保证表中的每一条数据的该字段都是表格中的唯一值。换言之,它是用来独一无二地确认一个表格中的每一行数据。
主键可以包含一个字段或多个字段。当主键包含多个栏位时,称为组合键 (Composite Key),也可以叫联合主键。
主键可以在建置新表格时设定 (运用 CREATE TABLE 语句),或是以改变现有的表格架构方式设定 (运用 ALTER TABLE)。
主键必须唯一,主键值非空;可以是单一字段,也可以是多字段组合。
1.单字段主键
单字段主键
- <span style="color: #808080;">============</span>单列做主键<span style="color: #808080;">===============</span><span style="color: #000000;">
- #方法一:</span><span style="color: #808080;">not</span> <span style="color: #0000ff;">null</span><span style="color: #808080;">+</span><span style="color: #0000ff;">unique</span>
- <span style="color: #0000ff;">create</span> <span style="color: #0000ff;">table</span><span style="color: #000000;"> department1(
- 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;">, #主键
- 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;">,
- comment </span><span style="color: #0000ff;">varchar</span>(<span style="color: #800000; font-weight: bold;">100</span><span style="color: #000000;">)
- );
- <p>mysql<span style="color: #808080;">></span> <span style="color: #0000ff;">desc</span><span style="color: #000000;"> department1;<br>
- </span><span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">-------+--------------+------+-----+---------+-------+</span><br>
- <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>
- <span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">-------+--------------+------+-----+---------+-------+</span><br>
- <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>
- <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>
- <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>
- <span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">-------+--------------+------+-----+---------+-------+</span><br>
- 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>
- <h1 id="方法二:在某一个字段后用primary-key">方法二:在某一个字段后用primary <span style="color: #0000ff;">key</span></h1>
- <p><span style="color: #0000ff;">create</span> <span style="color: #0000ff;">table</span><span style="color: #000000;"> department2(<br>
- 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>
- name </span><span style="color: #0000ff;">varchar</span>(<span style="color: #800000; font-weight: bold;">20</span><span style="color: #000000;">),<br>
- comment </span><span style="color: #0000ff;">varchar</span>(<span style="color: #800000; font-weight: bold;">100</span><span style="color: #000000;">)<br>
- );</span></p>
- <p>mysql<span style="color: #808080;">></span> <span style="color: #0000ff;">desc</span><span style="color: #000000;"> department2;<br>
- </span><span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">-------+--------------+------+-----+---------+-------+</span><br>
- <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>
- <span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">-------+--------------+------+-----+---------+-------+</span><br>
- <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>
- <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>
- <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>
- <span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">-------+--------------+------+-----+---------+-------+</span><br>
- 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>
- <h1 id="方法三:在所有字段后单独定义primary-key">方法三:在所有字段后单独定义primary <span style="color: #0000ff;">key</span></h1>
- <p><span style="color: #0000ff;">create</span> <span style="color: #0000ff;">table</span><span style="color: #000000;"> department3(<br>
- id </span><span style="color: #0000ff;">int</span><span style="color: #000000;">,<br>
- name </span><span style="color: #0000ff;">varchar</span>(<span style="color: #800000; font-weight: bold;">20</span><span style="color: #000000;">),<br>
- comment </span><span style="color: #0000ff;">varchar</span>(<span style="color: #800000; font-weight: bold;">100</span><span style="color: #000000;">),<br>
- </span><span style="color: #0000ff;">primary</span> <span style="color: #0000ff;">key</span><span style="color: #000000;">(id); #创建主键并为其命名pk_name</span></p>
- <p>mysql<span style="color: #808080;">></span> <span style="color: #0000ff;">desc</span><span style="color: #000000;"> department3;<br>
- </span><span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">-------+--------------+------+-----+---------+-------+</span><br>
- <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>
- <span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">-------+--------------+------+-----+---------+-------+</span><br>
- <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>
- <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>
- <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>
- <span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">-------+--------------+------+-----+---------+-------+</span><br>
- 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>
- <h1 id="方法四:给已经建成的表添加主键约束">方法四:给已经建成的表添加主键约束</h1>
- <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>
- </span><span style="color: #808080;">-></span> id <span style="color: #0000ff;">int</span><span style="color: #000000;">,<br>
- </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>
- </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>
- 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>
- <p>mysql<span style="color: #808080;">></span> <span style="color: #0000ff;">desc</span><span style="color: #000000;"> department4;<br>
- </span><span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">-------+--------------+------+-----+---------+-------+</span><br>
- <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>
- <span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">-------+--------------+------+-----+---------+-------+</span><br>
- <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>
- <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>
- <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>
- <span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">-------+--------------+------+-----+---------+-------+</span><br>
- <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>
- <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>
- 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>
- 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>
- <p>mysql<span style="color: #808080;">></span> <span style="color: #0000ff;">desc</span><span style="color: #000000;"> department4;<br>
- </span><span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">-------+--------------+------+-----+---------+-------+</span><br>
- <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>
- <span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">-------+--------------+------+-----+---------+-------+</span><br>
- <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>
- <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>
- <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>
- <span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">-------+--------------+------+-----+---------+-------+</span><br>
- <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.多字段主键
多字段主键
- <span style="color: #808080;">==================</span>多列做主键<span style="color: #808080;">================</span>
- <span style="color: #0000ff;">create</span> <span style="color: #0000ff;">table</span><span style="color: #000000;"> service(
- ip </span><span style="color: #0000ff;">varchar</span>(<span style="color: #800000; font-weight: bold;">15</span><span style="color: #000000;">),
- port </span><span style="color: #0000ff;">char</span>(<span style="color: #800000; font-weight: bold;">5</span><span style="color: #000000;">),
- 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;">,
- </span><span style="color: #0000ff;">primary</span> <span style="color: #0000ff;">key</span><span style="color: #000000;">(ip,port)
- );
- <p>mysql<span style="color: #808080;">></span> <span style="color: #0000ff;">desc</span><span style="color: #000000;"> service;<br>
- </span><span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">------------+-------------+------+-----+---------+-------+</span><br>
- <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>
- <span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">------------+-------------+------+-----+---------+-------+</span><br>
- <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>
- <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>
- <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>
- <span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">------------+-------------+------+-----+---------+-------+</span><br>
- <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>
- <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>
- <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>
- </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>
- </span><span style="color: #808080;">-></span><span style="color: #000000;"> ;<br>
- 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>
- 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>
- <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>
- 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>
约束字段为自动增长,被约束的字段必须同时被key约束
设置auto_increment
- <span style="color: #000000;">#不指定id,则自动增长
- </span><span style="color: #0000ff;">create</span> <span style="color: #0000ff;">table</span><span style="color: #000000;"> student(
- 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,
- name </span><span style="color: #0000ff;">varchar</span>(<span style="color: #800000; font-weight: bold;">20</span><span style="color: #000000;">),
- 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;">
- );
- <p>mysql<span style="color: #808080;">></span> <span style="color: #0000ff;">desc</span><span style="color: #000000;"> student;<br>
- </span><span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">-----+-----------------------+------+-----+---------+----------------+</span><br>
- <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>
- <span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">-----+-----------------------+------+-----+---------+----------------+</span><br>
- <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>
- <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>
- <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>
- <span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">-----+-----------------------+------+-----+---------+----------------+</span><br>
- 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>
- <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>
- </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>
- </span><span style="color: #808080;">-></span><span style="color: #000000;"> ;</span></p>
- <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>
- </span><span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">--+------+------+</span><br>
- <span style="color: #808080;">|</span> id <span style="color: #808080;">|</span> name <span style="color: #808080;">|</span> sex <span style="color: #808080;">|</span><br>
- <span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">--+------+------+</span><br>
- <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>
- <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>
- <span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">--+------+------+</span><br>
- <span style="color: #000000;"></span></p>
- <h1 id="也可以指定id">也可以指定id</h1>
- <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>
- 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>
- <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>
- 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>
- <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>
- </span><span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">--+------+--------+</span><br>
- <span style="color: #808080;">|</span> id <span style="color: #808080;">|</span> name <span style="color: #808080;">|</span> sex <span style="color: #808080;">|</span><br>
- <span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">--+------+--------+</span><br>
- <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>
- <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>
- <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>
- <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>
- <span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">--+------+--------+</span><br>
- <span style="color: #000000;"></span></p>
- <h1 id="对于自增的字段,在用delete删除后,再插入值,该字段仍按照删除前的位置继续增长">对于自增的字段,在用delete删除后,再插入值,该字段仍按照删除前的位置继续增长</h1>
- <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>
- 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>
- <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>
- 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>
- <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>
- 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>
- </span><span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">--+------+------+</span><br>
- <span style="color: #808080;">|</span> id <span style="color: #808080;">|</span> name <span style="color: #808080;">|</span> sex <span style="color: #808080;">|</span><br>
- <span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">--+------+------+</span><br>
- <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>
- <span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">--+------+------+</span><br>
- <span style="color: #000000;"></span></p>
- <h1 id="应该用truncate清空表,比起delete一条一条地删除记录,truncate是直接清空表,在删除大表时用它">应该用truncate清空表,比起delete一条一条地删除记录,truncate是直接清空表,在删除大表时用它</h1>
- <p>mysql<span style="color: #808080;">></span> <span style="color: #0000ff;">truncate</span><span style="color: #000000;"> student;<br>
- 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>
- <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>
- 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>
- <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>
- </span><span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">--+------+------+</span><br>
- <span style="color: #808080;">|</span> id <span style="color: #808080;">|</span> name <span style="color: #808080;">|</span> sex <span style="color: #808080;">|</span><br>
- <span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">--+------+------+</span><br>
- <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>
- <span style="color: #808080;">+</span><span style="color: #008080;">--</span><span style="color: #008080;">--+------+------+</span><br>
- 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>
了解知识
- <span style="color: #000000;">#在创建完表后,修改自增字段的起始值
- mysql</span><span style="color: #808080;">></span> <span style="color: #0000ff;">create</span> <span style="color: #0000ff;">table</span><span style="color: #000000;"> student(
- </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,
- </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;">),
- </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>
- <div class=" "="">
- <ul class="m-news-opt fix">
- <li class="opt-item">
- <a href="/sql_question-384343.html" target="_blank"><p>< 上一篇</p><p class="ellipsis">MySQL 索引</p></a>
- </li>
- <li class="opt-item ta-r">
- <a href="/sql_question-384345.html" target="_blank"><p>下一篇 ></p><p class="ellipsis">SqlServer优化之批量插入(SqlBulkCopy、表值参数)</p></a>
- </li>
- </ul>
- </span>