tags: python,SQLite

SQLite

來自 老師的網站https://hackmd.io/@amostsai 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支援（因此沒有跨語系的問題）。

  • 速度很快

安裝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', '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)

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

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

SQLite Tutorial
SQLite學習手冊
SQLite 語法

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

S20200530 YTC

Select a repo