SQLite 3資料庫管理及使用

SQLite 3是一個輕量級的嵌入式關聯型資料庫管理系統，廣泛應用於移動應用、嵌入式系統和小型桌面應用中。SQLite 3無需獨立的伺服器進程，數據存儲在單個文件中，易於管理和使用。本文將詳細介紹如何在ServBay中管理和使用SQLite 3資料庫，包括安裝、配置、備份、恢復和性能優化等方面。

安裝和配置SQLite 3

SQLite 3通常不需要單獨安裝，因為它已經包含在大多數操作系統和編程環境中。ServBay自帶SQLite 3，您只需確保其可用。

驗證SQLite 3安裝

您可以透過以下命令驗證SQLite 3是否已安裝：

sqlite3 --version

如果顯示版本資訊，則表示SQLite 3已安裝。

創建和連接SQLite 3資料庫

SQLite 3資料庫存儲在單個文件中，默認路徑為 /Applications/ServBay/db/sqlite。您可以使用命令行工具sqlite3或編程語言的SQLite庫來創建和連接資料庫。

使用命令行工具連接

1. 創建或連接資料庫：

   sqlite3 /Applications/ServBay/db/sqlite/your_database.db

2. 退出SQLite 3：

   .exit

使用編程語言連接

PHP範例

使用SQLite3類連接SQLite 3：

<?php
// Connect to SQLite 3 database
$db = new SQLite3('/Applications/ServBay/db/sqlite/your_database.db');

// Create table
$db->exec("CREATE TABLE IF NOT EXISTS mytable (id INTEGER PRIMARY KEY, name TEXT)");

// Insert data
$db->exec("INSERT INTO mytable (name) VALUES ('Alice')");

// Query data
$result = $db->query("SELECT * FROM mytable");
while ($row = $result->fetchArray()) {
    echo "ID: " . $row['id'] . " Name: " . $row['name'] . "\n";
}

// Close database connection
$db->close();
?>

Node.js範例

使用sqlite3庫連接SQLite 3：

const sqlite3 = require('sqlite3').verbose();

// Connect to SQLite 3 database
let db = new sqlite3.Database('/Applications/ServBay/db/sqlite/your_database.db', (err) => {
  if (err) {
    console.error(err.message);
  }
  console.log('Connected to the SQLite database.');
});

// Create table
db.run(`CREATE TABLE IF NOT EXISTS mytable (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT
)`);

// Insert data
db.run(`INSERT INTO mytable (name) VALUES (?)`, ['Alice'], function(err) {
  if (err) {
    return console.error(err.message);
  }
  console.log(`A row has been inserted with rowid ${this.lastID}`);
});

// Query data
db.all(`SELECT * FROM mytable`, [], (err, rows) => {
  if (err) {
    throw err;
  }
  rows.forEach((row) => {
    console.log(row.id, row.name);
  });
});

// Close database connection
db.close((err) => {
  if (err) {
    console.error(err.message);
  }
  console.log('Close the database connection.');
});

資料庫管理

基本操作

1. 創建表：

   CREATE TABLE mytable (id INTEGER PRIMARY KEY, name TEXT);

2. 插入資料：

   INSERT INTO mytable (name) VALUES ('Alice');

3. 查詢資料：

   SELECT * FROM mytable;

4. 更新資料：

   UPDATE mytable SET name = 'Bob' WHERE id = 1;

5. 刪除資料：

   DELETE FROM mytable WHERE id = 1;

備份和恢復

備份資料庫

SQLite 3資料庫存儲在單個文件中，備份只需複製該文件即可。建議將備份文件存放在以下目錄：

/Applications/ServBay/backup/sqlite

使用命令行工具複製資料庫文件進行備份：

cp /Applications/ServBay/db/sqlite/your_database.db /Applications/ServBay/backup/sqlite/your_database_backup.db

恢復資料庫

將備份文件複製回原始位置進行恢復：

cp /Applications/ServBay/backup/sqlite/your_database_backup.db /Applications/ServBay/db/sqlite/your_database.db

性能優化

SQLite 3提供了多種性能優化選項，以下是一些常見的優化方法。

索引優化

確保對常用查詢的欄位建立索引。例如：

CREATE INDEX idx_name ON mytable(name);

查詢優化

使用EXPLAIN命令分析查詢性能，並進行相應優化。例如：

EXPLAIN QUERY PLAN SELECT * FROM mytable WHERE name = 'Alice';

配置優化

調整SQLite 3的PRAGMA參數以提升性能。例如，啟用同步模式以提高寫入性能：

PRAGMA synchronous = OFF;

安全管理

確保資料庫的安全性是非常重要的，以下是一些安全管理的建議。

文件權限

確保SQLite 3資料庫文件的權限設置正確，防止未經授權的訪問。例如：

chmod 600 /Applications/ServBay/db/sqlite/your_database.db

數據加密

SQLite 3支持資料庫加密，您可以使用SQLite加密擴展（如SQLCipher）對資料庫進行加密。

常見問題及解決方案

無法連接到SQLite 3資料庫

1. 檢查資料庫文件路徑： 確保資料庫文件路徑正確且文件存在。

2. 檢查文件權限： 確保資料庫文件的權限允許讀寫操作。

資料庫鎖定

1. 檢查並發訪問： 確保沒有其他進程正在訪問資料庫，SQLite 3在寫操作時會鎖定資料庫。

2. 使用WAL模式： 啟用WAL（Write-Ahead Logging）模式以提高並發性能：

   PRAGMA journal_mode = WAL;

總結

SQLite 3是一個強大且靈活的嵌入式資料庫管理系統，ServBay自帶SQLite 3，使得資料庫管理和使用變得更加方便。通過本文的介紹，您可以輕松地進行SQLite 3的安裝、配置、連接、管理、備份、恢復和性能優化操作，確保資料庫的高效運行和安全性。