###### tags: `python`,`SQLite`
# SQLite
來自 老師的網站https://hackmd.io/@amostsai [SQLite 資料庫](https://hackmd.io/@wootu/Bk_pYqWIM?type=view)
## 前言
* 軟體屬於公共財（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支援（因此沒有跨語系的問題）。
* 速度很快
---
## 安裝GUI工具 - DB Browser for SQLite
1. 連到[db browser for sqlite](http://sqlitebrowser.org)
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】
2. 點選要匯出的資料表
3. 點【OK】，輸入檔名
## 匯入資料庫
1. 點【檔案/匯入/Database from SQL file】
2. 選擇要匯入的sql檔
a. Yes: 匯入到新的資料庫
b. No: 匯入到目前資料庫
---
## 正規化
資料庫為什麼需要正規化
* 降低資料的重複性
* 避免更新異常
(註一)
----
## SQL語法：資料表處理
* **CREATE TABLE 新增資料表**
```sql=
CREATE TABLE user (
'id' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
'name' TEXT NOT NULL,
'role' INTEGER NOT NULL DEFAULT 1
);
```
* **ALTER TABLE .. RENAME TO .. 修改資料表名稱**
```sql=
ALTER TABLE book RENAME TO books;
```
* **ALTER TABLE .. ADD .. 新增欄位**
```sql=
ALTER TABLE book ADD note text;
```
* **刪除欄位**
```sql=
CREATE TABLE book2 AS SELECT id, name, price FROM book;
DROP TABLE book;
ALTER TABLE book2 RENAME TO book;
```
* **DROP TABLE 刪除資料表**
```sql=
DROP TABLE book2;
```
## SQL語法：資料處理
* **SELECT: 選取紀錄**
```sql=
select * from book;
select name, price from book;
```
* **DISTINCT: 不顯示重複內容**
```sql=
select distinct name FROM book;
```
* **WHERE: 資料篩選**
* **= 相同**
```sql=
select * from book where name = '咖啡';
```
* **<> 不相同**
```sql=
select * from book where name <> '咖啡';
```
* **\> 大於**
```sql=
select * from book where price > 150;
```
* **< 小於**
```sql=
select * from book where price < 200;
```
* **\>= 大於等於**
```sql=
select * from book where price >= 150;
```
* **<= 小於等於**
```sql=
select * from book where price <= 200;
```
* **BETWEEN 介於**
```sql=
select * from book where price between 150 and 190;
```
* **LIKE 符合樣式**
```sql=
select * from book where name like '%咖%';
```
* **IN 在...之中**
```sql=
select * from book where id in (1, 2);
```
* **And, OR 並且, 或者**
```sql=
select * from book where price > 180 and name like '%美%'
select * from book where price > 180 or name like '%美%'
```
* **ORDER BY 排序**
* ASC 由小到大
* DESC 由大到小
```sql=
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 新增資料**
```sql=
insert into book (name, price) values ('美食之旅', 230);
```
* **UPDATE 更新資料**
```sql=
update book set name = '美食之旅2', price = 250 where id = 4;
```
* **DELETE 刪除資料**
```sql=
delete from book where name = '美食之旅2';
```
* **JOIN 結合一個以上的table的資料**
```sql=
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
```python=
import sqlite3
conn = sqlite3.connect(r'C:\Users\install\Desktop\書籍管理.db')
books = conn.execute('select * from book')
for book in books:
print(book)
```
```python=
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', 'mos@kkk.com', '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)
```
----
參考資料:
[資料庫正規化練習檔](https://drive.google.com/open?id=1lJTm-FLZvJiO26i7zbHE0TAiNX5V3o0U)
[資料庫正規化 Database normalization](https://www.mysql.tw/2017/04/database-normalization.html)
[為何使用資料庫儲存資料時，需要先執行正規化？](https://www.ithome.com.tw/node/47440)
[說明資料庫正規化基本概念](https://support.microsoft.com/zh-tw/help/283878/description-of-the-database-normalization-basics)
[資料庫正規化](http://cc.cust.edu.tw/~ccchen/doc/db_04.pdf)
[關聯式資料庫系統的規劃](https://www.slideshare.net/SimonHuang4/ss-31199783)
註一：[資料庫正規化設計題目](https://yamol.tw/tfulltext-資料庫正規化設計題目.htm)
[SQLite Tutorial](https://www.quackit.com/sqlite/tutorial/)
[SQLite學習手冊](https://wizardforcel.gitbooks.io/sqlite-learning-manual/content/1.html)
[SQLite 語法](http://tw.gitbook.net/sqlite/sqlite_syntax.html)
[Python 速查手冊12.6 資料庫 sqlite3](http://kaiching.org/pydoing/py/python-library-sqlite3.html)
[11.13. sqlite3 — DB-API 2.0 interface for SQLite databases](https://docs.python.org/2/library/sqlite3.html)
S20200530 YTC
Google Drive
Gist
Clipboard
Sign in with Wallet
