SQLite

前言

• 軟體屬於公共財（public domain）

• SQLite可說是某種「美德軟體」（virtueware）

• 作者本人放棄著作權，而給使用SQLite的人以下的「祝福」（blessing）：

  • May you do good and not evil. 願你行善莫行惡
  • May you find forgiveness for yourself and forgive others. 願你原諒自己寬恕他人
  • May you share freely, never taking more than you give. 願你寬心與人分享，所取不多於你所施予

• 支援大多數的SQL指令（下面會簡單介紹）

• 一個檔案就是一個資料庫。不需要安裝資料庫伺服器軟體

• 完整的Unicode支援（因此沒有跨語系的問題）。

• 速度很快

---

1. 連到SQLiteExpert
2. 點下方的[SQLite Expert Personal 64bit]
3. 安裝

---

安裝GUI工具 - DB Browser for SQLite

1. 連到db browser for sqlite
2. 點 Windows.exe
3. 安裝

---

DB Browser for SQLite特色

• 開放原始碼
• 只能連SQLite

資料庫概念

資料庫 -> 資料表 -> 資料列 -> 資料欄

---

新增資料庫

1. 點【新建資料庫】
2. 設定資料庫檔案儲存位置、檔名

新增資料表

1. 點【Create Table】
2. 輸入資料表名稱
3. 點【加入欄位】
4. 點【OK】

修改資料表

1. 點【Database Structure】
2. 在要修改的資料表點右鍵
3. 點【Modify Table】
4. 調整欄位上下順序、新增資料欄、刪除資料欄
5. 點【OK】
6. ctrl + s 儲存更動資料!!!

刪除資料表

1. 點【Database Structure】
2. 在要修改的資料表點右鍵
3. 點【刪除資料表】
4. ctrl + s 儲存更動資料!!!

---

欄位資料類型

• null: 空值
• integer: 整數資料
• text: 文字資料
• blob: 二進位資料
• real: 浮點數資料
• numeric:

欄位特性

• 非空: 一定要有資料
• PK: 主索引鍵
• AI: 自動加1
• U: 唯一值，所有紀錄中該欄位資料不可重複

---

新增/修改資料

1. 點【Browse】
2. 下拉要處理的資料表
3. 點【新建紀錄】
4. ctrl + s 儲存更動資料!!!

篩選資料

1. 點【Browse】
2. 下拉要處理的資料表
3. 在【過濾格】輸入要篩選的資料

刪除紀錄

1. 點【Browse】
2. 下拉要處理的資料表
3. 用滑鼠框選要刪除的紀錄
4. 點【刪除記錄】
5. ctrl + s 儲存更動資料!!!

---

執行SQL指令

1. 點【Execute】
2. 輸入SQL指令
3. 點【執行】
4. 下方就是執行結果

---

匯出資料庫

1. 打開資料庫檔
2. 點【檔案/匯出/Database to SQL file】
3. 點選要匯出的資料表
4. 點【OK】，輸入檔名

匯入資料庫

1. 點【檔案/匯入/Database from SQL file】
2. 選擇要匯入的sql檔
   a. Yes: 匯入到新的資料庫
   b. No: 匯入到目前資料庫

---

正規化

資料庫為什麼需要正規化

• 降低資料的重複性
• 避免更新異常

(註一)

---

SQL語法：資料表處理

• CREATE TABLE 新增資料表

CREATE TABLE user (
        'id'	INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
        'name'	TEXT NOT NULL,
        'role'	INTEGER NOT NULL DEFAULT 1
);

• ALTER TABLE .. RENAME TO .. 修改資料表名稱

ALTER TABLE book RENAME TO books;

• ALTER TABLE .. ADD .. 新增欄位

ALTER TABLE book ADD note text;

• 刪除欄位

CREATE TABLE book2 AS SELECT id, name, price FROM book;
    DROP TABLE book;
    ALTER TABLE book2 RENAME TO book;

• DROP TABLE 刪除資料表

DROP TABLE book2;

SQL語法：資料處理

• SELECT: 選取紀錄

select * from book;
select name, price from book;

• DISTINCT: 不顯示重複內容

select distinct name FROM book;

• WHERE: 資料篩選
  • = 相同
  ​​​​select * from book where name = '咖啡';
  

  • <> 不相同
  ​​​​select * from book where name <> '咖啡';
  

  • > 大於
  ​​​​select * from book where price > 150;
  

  • < 小於
  ​​​​select * from book where price < 200;
  

  • >= 大於等於
  ​​​​select * from book where price >= 150;
  

  • <= 小於等於
  ​​​​select * from book where price <= 200;
  

  • BETWEEN 介於
  ​​​​select * from book where price between 150 and 190;
  

  • LIKE 符合樣式
  ​​​​select * from book where name like '%咖%';
  

  • IN 在…之中
  ​​​​select * from book where id in (1, 2);
  

• And, OR 並且, 或者

select * from book where price > 180 and name like '%美%'
select * from book where price > 180 or name like '%美%'

• ORDER BY 排序
  • ASC 由小到大
  • DESC 由大到小
  ​​​​select * from book order by price; 
  ​​​​select * from book order by price desc;
  ​​​​select * from user where role_id = 1 order by name desc;
  

• INSERT 新增資料

insert into book (name, price) values ('美食之旅', 230);

• UPDATE 更新資料

update book set name = '美食之旅2', price = 250 where id = 4;

• DELETE 刪除資料

delete from book where name = '美食之旅2';

• JOIN 結合一個以上的table的資料

SELECT book.id, book.name, book.price, user.name from book inner join user on book.user_id = user.id;
SELECT user.id, user.name, user.account, role.name from user inner join role on user.role_id = role.id;

---

python

import sqlite3

conn = sqlite3.connect(r'C:\Users\install\Desktop\書籍管理.db')
books = conn.execute('select * from book')
for book in books:
    print(book)

import sqlite3

conn = sqlite3.connect(r'C:\Users\student\Desktop\test.db')

add_user_sql = "insert into user (name, account, password) values ('{0}', '{1}', '{2}')"
check_user_sql = "select * from user where account='{0}' and password='{1}';"

# try:
#     conn.execute(add_user_sql.format('mos', '[e-mail]', 'qqqqqqqq'))
#     conn.commit()
# except Exception as e:
#     print(e)

try:
    account = input('請輸入帳號: ')
    password = input('請輸入密碼: ')

    res = conn.execute(check_user_sql.format(account, password)).fetchone()
    if(res == None):
        print('帳號或密碼錯誤，請重新輸入')
    else:
        print('歡迎使用本系統')
except Exception as e:
    print(e)
    

---

參考資料:
資料庫正規化練習檔

資料庫正規化 Database normalization
為何使用資料庫儲存資料時，需要先執行正規化？
說明資料庫正規化基本概念
資料庫正規化
關聯式資料庫系統的規劃
註一：資料庫正規化設計題目

SQLite Tutorial
SQLite學習手冊
SQLite 語法

Python 速查手冊12.6 資料庫 sqlite3
11.13. sqlite3 — DB-API 2.0 interface for SQLite databases