# 增刪改索引 - 基本語句 ``` <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> ``` ```