Python學透資料庫所有知識——SQLite！

SQLite是一個輕量級的資料庫，非常适合嵌入式系統、移動應用和小型項目。Python的sqlite3子產品為SQLite資料庫提供了強大的支援，使得在Python中進行資料庫操作變得簡單快捷。通過掌握上述的SQLite語句和Python代碼執行個體，你可以有效地在Python項目中使用SQLite資料庫進行資料存儲和管理。

1. 建立和連接配接資料庫

在Python中使用SQLite的第一步是建立和連接配接到資料庫。可以使用sqlite3子產品來完成這個任務。

import sqlite3

# 連接配接到資料庫，如果資料庫不存在則建立
conn = sqlite3.connect('mydatabase.db')           

2. 建立表格

建立表格是資料庫操作的基礎。在SQLite中，可以使用CREATE TABLE語句來建立一個新的表格。

cursor = conn.cursor()

# 建立一個名為"students"的表格
cursor.execute('''CREATE TABLE IF NOT EXISTS students (
                  id INTEGER PRIMARY KEY,
                  name TEXT NOT NULL,
                  age INTEGER
              )''')

conn.commit()           

3. 插入資料

插入資料到表格中可以使用INSERT INTO語句。

# 插入一名學生的資訊
cursor.execute("INSERT INTO students (name, age) VALUES (?, ?)", ('Alice', 25))           

4. 查詢資料

查詢資料是資料庫操作中最常用的功能之一。SELECT語句用于從表格中檢索資料。

# 查詢所有學生的資訊
cursor.execute("SELECT * FROM students")
rows = cursor.fetchall()
for row in rows:
    print(row)           

5. 更新資料

UPDATE語句用于更新表格中的現有資料。

# 更新名為Alice的學生的年齡
cursor.execute("UPDATE students SET age = ? WHERE name = ?", (26, 'Alice'))           

6. 删除資料

DELETE語句用于從表格中删除資料。

# 删除名為Bob的學生記錄
cursor.execute("DELETE FROM students WHERE name = 'Bob'")           

7. 事務處理

SQLite支援事務，可以確定資料庫操作的完整性。

# 開始事務
conn.execute("BEGIN")

# 執行一些操作
# ...

# 送出事務
conn.commit()           

8. 建立索引

為了提高查詢效率，可以在表格上建立索引。

# 建立索引
cursor.execute("CREATE INDEX idx_name ON students (name)")           

9. 資料庫備份和恢複

SQLite提供了簡單的備份和恢複機制。

# 備份資料庫
conn.execute("BACKUP DATABASE ? TO ?", ('mydatabase.db', 'mydatabase_backup.db'))

# 恢複資料庫
conn.execute("RESTORE DATABASE ? FROM ?", ('mydatabase.db', 'mydatabase_backup.db'))           

10. 使用函數和觸發器

SQLite支援使用函數和觸發器來自動化某些操作。

# 建立一個觸發器
cursor.execute("CREATE TRIGGER update_age BEFORE INSERT ON students FOR EACH ROW BEGIN UPDATE students SET age = NEW.age + 1 WHERE name = NEW.name; END")

# 使用函數
cursor.execute("SELECT length(name) FROM students")           

11. 資料庫優化

使用ANALYZE和VACUUM語句可以優化資料庫性能。

# 分析表格
cursor.execute("ANALYZE students")

# 清理并優化資料庫
conn.execute("VACUUM")           

12. 常用的SQLite語句

以下是一些常用的SQLite語句：

1. SELECT - 選擇資料
2. INSERT INTO - 插入資料
3. UPDATE - 更新資料
4. DELETE - 删除資料
5. CREATE TABLE - 建立新表格
6. ALTER TABLE - 修改表格結構
7. DROP TABLE - 删除表格
8. CREATE INDEX - 建立索引
9. DROP INDEX - 删除索引
10. SELECT DISTINCT - 選擇唯一記錄
11. ORDER BY - 排序查詢結果
12. GROUP BY - 按列分組
13. HAVING - 過濾分組後的資料
14. LIMIT - 限制查詢結果數量
15. JOIN - 連接配接表格
16. LEFT JOIN - 左連接配接
17. RIGHT JOIN - 右連接配接
18. FULL OUTER JOIN - 全外連接配接
19. UNION - 合并兩個查詢結果
20. EXCEPT - 排除兩個查詢結果中的重複行