# 增删改索引 - 基本语句 ``` <pre class="calibre14">``` mysql <span class="token">-</span>u root <span class="token">-</span>p 登录数据库 create database DB 创建数据库db drop database DB<span class="token2">;</span> 删除数据库 use DB 选择数据库 show databases<span class="token2">;</span> 显示所有数据库 create table <span class="token5">if</span> not exists `runoob_tbl`<span class="token2">(</span> `runoob_id` int unsigned not <span class="token5">null</span> auto_increment comment <span class="token4">"描述"</span><span class="token2">,</span> `runoob_title` <span class="token1">varchar</span><span class="token2">(</span><span class="token3">100</span><span class="token2">)</span> default <span class="token4">"0"</span> not <span class="token5">null</span> <span class="token2">,</span> `submission_date` date<span class="token2">,</span> primary key <span class="token2">(</span> `runoob_id` <span class="token2">)</span> <span class="token2">)</span>engine<span class="token">=</span>InnoDB default charset<span class="token">=</span>utf8<span class="token2">;</span><span class="token6">// Myisam</span> drop table <span class="token5">if</span> exists T1<span class="token2">,</span>T2<span class="token2">;</span><span class="token6">//删除表</span> show tables 显示所有表 desc T1 显示表结构 show create table T1 显示表结构SQL<span class="token2">;</span> alter table T1 rename NEWT1 修改表名 alter table biao modify id <span class="token1">int</span><span class="token2">(</span><span class="token3">5</span><span class="token2">)</span> 修改表字段类型 alter table T1 add qqq <span class="token1">varchar</span><span class="token2">(</span><span class="token3">10</span><span class="token2">)</span> not <span class="token5">null</span> 添加字段 alter table T1 change id id1 <span class="token1">char</span><span class="token2">(</span><span class="token3">32</span><span class="token2">)</span> not <span class="token5">null</span> 修改字段名 alter table T1 drop id 删除字段 ``` ``` -增删改 ``` <pre class="calibre14">``` insert into <span class="token1">Table</span><span class="token2">(</span>id<span class="token2">,</span>name<span class="token2">)</span> values <span class="token2">(</span><span class="token5">null</span><span class="token2">,</span><span class="token4">'Li'</span><span class="token2">)</span> <span class="token2">;</span>插入数据 insert into <span class="token1">T</span><span class="token2">(</span>id<span class="token2">,</span>name<span class="token2">)</span> values <span class="token2">(</span><span class="token5">null</span><span class="token2">,</span><span class="token4">'Li'</span><span class="token2">)</span><span class="token2">,</span><span class="token2">(</span><span class="token5">null</span><span class="token2">,</span><span class="token4">"Zhao"</span><span class="token2">)</span><span class="token2">;</span> insert into T values <span class="token2">(</span><span class="token5">null</span><span class="token2">,</span><span class="token4">"Li"</span><span class="token2">,</span><span class="token5">null</span><span class="token2">,</span><span class="token4">"数据"</span><span class="token2">)</span><span class="token2">;</span>插入所有列 insert into <span class="token1">T</span><span class="token2">(</span>id<span class="token2">,</span>name<span class="token2">)</span> select id<span class="token2">,</span>name from T2<span class="token2">;</span> 查询插入 insert ignore into <span class="token1">Table</span><span class="token2">(</span>id<span class="token2">,</span>name<span class="token2">)</span> values <span class="token2">(</span><span class="token5">null</span><span class="token2">,</span><span class="token4">'Li'</span><span class="token2">)</span> <span class="token2">;</span> 唯一索引插入 有效防止重复数据 update T set name<span class="token">=</span><span class="token4">'Li'</span> where id<span class="token">=</span><span class="token3">1</span><span class="token2">;</span> 更新 update t set num<span class="token">=</span>num<span class="token">+</span><span class="token3">1</span> where id<span class="token">=</span><span class="token3">1</span> 自动加一 update T left join T1 on T<span class="token2">.</span>id<span class="token">=</span>T1<span class="token2">.</span>id set u<span class="token">=</span><span class="token4">'1'</span><span class="token2">,</span>u1<span class="token">=</span><span class="token4">'2'</span> where id<span class="token">=</span><span class="token3">1</span><span class="token2">;</span>多表更新 update T set user<span class="token">=</span><span class="token1">replace</span><span class="token2">(</span>user<span class="token2">,</span><span class="token4">'a'</span><span class="token2">,</span><span class="token4">'a1'</span><span class="token2">)</span> 替换表字段 update bleA a inner join B b on a<span class="token2">.</span>id <span class="token">=</span> b<span class="token2">.</span>id set a<span class="token2">.</span>name <span class="token">=</span> b<span class="token2">.</span>name 查询更新 delete from T where id <span class="token">=</span><span class="token3">1</span><span class="token2">;</span>删除 delete from T where id <span class="token5">in</span><span class="token2">(</span><span class="token3">1</span><span class="token2">,</span><span class="token3">2</span><span class="token2">,</span><span class="token3">3</span><span class="token2">)</span><span class="token2">;</span>删除多条 truncate table T 清空表 ``` ``` - 索引 ``` <pre class="calibre16">``` create unique index i on <span class="token1">test</span><span class="token2">(</span>openid<span class="token2">,</span>rid<span class="token2">)</span><span class="token2">;</span> unique是唯一索引<span class="token2">,</span>默认不是<span class="token2">,</span>大于字符串字段需要指定长度 alter table test add unique index <span class="token1">ii</span><span class="token2">(</span>rid<span class="token2">)</span> 同上 alter table testadd primary <span class="token1">key</span><span class="token2">(</span>id<span class="token2">)</span><span class="token2">;</span> 主索引 alter table test drop index i<span class="token2">;</span>删除索引 drop index ion test 删除索引 先改后添加 show index from test<span class="token2">;</span> ``` ```