安裝 sqlite3
<span class="line"><span style="color: #616E88"># 在 ubuntu linux 上安裝 sqlite3</span></span>
<span class="line"><span style="color: #D8DEE9FF">apt</span><span style="color: #81A1C1">-</span><span style="color: #D8DEE9FF">get install sqlite3</span></span>
<span class="line"></span>
<span class="line"><span style="color: #616E88"># 查看版本</span></span>
<span class="line"><span style="color: #D8DEE9FF">sqlite3 </span><span style="color: #D8DEE9">--</span><span style="color: #D8DEE9FF">version</span></span>開啟一個名為 sample.db 的資料庫
<span class="line"><span style="color: #D8DEE9FF">sqlite3 sample</span><span style="color: #ECEFF4">.</span><span style="color: #D8DEE9FF">db</span></span>基本的操作
<span class="line"><span style="color: #616E88"># 查看幫助文件</span></span>
<span class="line"><span style="color: #ECEFF4">.</span><span style="color: #D8DEE9FF">help</span></span>
<span class="line"></span>
<span class="line"><span style="color: #616E88"># 查看目前的資料庫檔案</span></span>
<span class="line"><span style="color: #ECEFF4">.</span><span style="color: #D8DEE9FF">database</span></span>
<span class="line"></span>
<span class="line"><span style="color: #616E88"># 顯示所有表格</span></span>
<span class="line"><span style="color: #ECEFF4">.</span><span style="color: #D8DEE9FF">tables</span></span>
<span class="line"></span>
<span class="line"><span style="color: #616E88"># 建立一個 categories 資料表</span></span>
<span class="line"><span style="color: #D8DEE9FF">CREATE TABLE </span><span style="color: #88C0D0">categories</span><span style="color: #ECEFF4">(</span><span style="color: #88C0D0">id</span><span style="color: #D8DEE9FF"> INTEGER PRIMARY KEY</span><span style="color: #ECEFF4">,</span><span style="color: #D8DEE9FF"> name text</span><span style="color: #ECEFF4">)</span><span style="color: #D8DEE9">;</span></span>
<span class="line"></span>
<span class="line"><span style="color: #616E88"># 查看 categories 的結構</span></span>
<span class="line"><span style="color: #ECEFF4">.</span><span style="color: #D8DEE9FF">schema categories</span></span>
<span class="line"></span>
<span class="line"></span>讀入準備好的 SQL 檔
create_articles_table.sql
<span class="line"><span style="color: #D8DEE9FF">CREATE TABLE </span><span style="color: #88C0D0">articles</span><span style="color: #D8DEE9FF"> </span><span style="color: #ECEFF4">(</span></span>
<span class="line"><span style="color: #D8DEE9FF"> </span><span style="color: #88C0D0">id</span><span style="color: #D8DEE9FF"> INTEGER PRIMARY KEY</span><span style="color: #ECEFF4">,</span></span>
<span class="line"><span style="color: #D8DEE9FF"> title TEXT</span><span style="color: #ECEFF4">,</span></span>
<span class="line"><span style="color: #D8DEE9FF"> slug TEXT</span></span>
<span class="line"><span style="color: #ECEFF4">)</span><span style="color: #D8DEE9">;</span></span><span class="line"><span style="color: #ECEFF4">.</span><span style="color: #D8DEE9FF">read create_articles_table</span><span style="color: #ECEFF4">.</span><span style="color: #D8DEE9FF">sql</span></span>調整輸出格式
<span class="line"><span style="color: #616E88"># 調整顯示模式為 column</span></span>
<span class="line"><span style="color: #ECEFF4">.</span><span style="color: #D8DEE9FF">mode column</span></span>
<span class="line"></span>
<span class="line"><span style="color: #616E88"># 在表頭顯示欄位名稱</span></span>
<span class="line"><span style="color: #ECEFF4">.</span><span style="color: #D8DEE9FF">headers on</span></span>
<span class="line"></span>
<span class="line"><span style="color: #616E88"># 設定欄位寬度</span></span>
<span class="line"><span style="color: #ECEFF4">.</span><span style="color: #D8DEE9FF">width </span><span style="color: #B48EAD">5</span><span style="color: #D8DEE9FF"> </span><span style="color: #B48EAD">15</span><span style="color: #D8DEE9FF"> </span><span style="color: #B48EAD">10</span></span>
<span class="line"></span>
<span class="line"><span style="color: #616E88"># 重置欄位寬度設定</span></span>
<span class="line"><span style="color: #ECEFF4">.</span><span style="color: #D8DEE9FF">witdh </span><span style="color: #B48EAD">0</span></span>執行 SHELL 指令
<span class="line"><span style="color: #ECEFF4">.</span><span style="color: #D8DEE9FF">shell clear</span></span>
<span class="line"></span>index 操作
<span class="line"><span style="color: #616E88"># 顯示 index</span></span>
<span class="line"><span style="color: #ECEFF4">.</span><span style="color: #D8DEE9FF">indexes</span></span>
<span class="line"></span>
<span class="line"><span style="color: #616E88"># 建立 index</span></span>
<span class="line"><span style="color: #D8DEE9FF">CREATE INDEX </span><span style="color: #81A1C1"><</span><span style="color: #D8DEE9FF">INDEX_NAME</span><span style="color: #81A1C1">></span><span style="color: #D8DEE9FF"> ON </span><span style="color: #88C0D0">article</span><span style="color: #ECEFF4">(</span><span style="color: #D8DEE9FF">slug</span><span style="color: #ECEFF4">)</span><span style="color: #D8DEE9">;</span></span>
<span class="line"></span>
<span class="line"><span style="color: #616E88"># 刪除 index</span></span>
<span class="line"><span style="color: #D8DEE9FF">DROP INDEX </span><span style="color: #81A1C1"><</span><span style="color: #D8DEE9FF">INDEX_NAME</span><span style="color: #81A1C1">></span></span>
Comments