ลองใช้ SQLite ด้วยการรันคำสั่ง sqlite3

SQLite เป็นโปรแกรมฐานข้อมูลที่มีขนาดเล็กมาก (ไม่ถึง 1MB)  เก็บฐานข้อมูลเป็นไฟล์โดยไม่จำเป็นต้องมีเซิร์ฟเวอร์ ทำให้ถูกใช้ในหลายๆ โปรแกรมหรือถูกติดตั้งลงไปในอุปกรณ์พกพาหลายชนิดๆ เช่น iPhone, Android เพื่อใช้ในการเก็บข้อมูล

บอกตามตรงไม่เคยใช้เลย ใช้แต่ระบบฐานข้อมูล MySQL แต่เมื่อมีโอกาสทำโครงการเก็บข้อมูลลงในอุปกรณ์ที่มีขนาดเล็ก ฮาร์ดดิสก์และหน่วยความจำมีขนาดจำกัด เลยลองหัดใช้ SQLite ดู แล้วนำมาแชร์กัน เผื่อเป็นประโยชน์กับผู้อ่าน

ในบทความนี้ขอแสดงวิธีใช้ SQLite แบบรันเป็นคำสั่ง (command line) คือรันคำสั่ง sqlite3 เพื่อเข้าใช้งาน ส่วนอีกวิธีคือเรียกผ่าน library ผ่านการเขียนโปรแกรม PHP, Python ขอเป็นบทความต่อไป

หลายๆ OS เช่น Linux, MacOS จะติดตั้ง SQLite มาเป็นดีฟอลต์เลย เราสามารถรันคำสั่ง sqlite3 เพื่อใช้งานได้เลย

[user1@cent6-dev ~]$ sqlite3
SQLite version 3.6.20
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>

แต่ถ้าบน Windows ต้องดาวน์โหลดไฟล์โปรแกรมมาก่อน โดยเข้าเว็บไซต์ SQLite Download (http://www.sqlite.org/download.html)

ตัวอย่างหน้าเว็บไซต์ SQLite Download Page เลือกดาวน์โหลดภายใต้หัวข้อ Precompiled Binaries for Windows เลือกแบบ command-line shell

แตกไฟล์ที่ดาวน์โหลดมาจะได้ไฟล์ sqlite3.exe สามารถคลิ้กใช้งานได้เลย (ลองดูขนาดหลังจากแตกไฟล์แล้ว มีขนาดไฟล์ประมาณ 500 Kbytes)

เข้า sqlite ด้วยคำสั่ง sqlite3

[user1@cent6-dev ~]$ sqlite3
SQLite version 3.6.20
Enter ".help" for instructions
Enter SQL statements terminated with a ";"

คำสั่งใน sqlite (ที่ไม่ใช่ SQL) จะขึ้นต้นด้วยเครื่องหมายจุด “.” หากต้องการดูคำสั่ง และวิธีการใช้ ให้พิมพ์คำสั่ง .help

sqlite> .help
.backup ?DB? FILE      Backup DB (default "main") to FILE
.bail ON|OFF           Stop after hitting an error.  Default OFF
.databases             List names and files of attached databases
.dump ?TABLE? ...      Dump the database in an SQL text format
                         If TABLE specified, only dump tables matching
                         LIKE pattern TABLE.
.echo ON|OFF           Turn command echo on or off
.exit                  Exit this program
...

ลองสร้างตารางแล้วลองใส่ข้อมูล ด้วย SQL

sqlite> CREATE TABLE users (id int, name varchar(255));

sqlite> INSERT INTO users VALUES (1, 'Alice');
sqlite> INSERT INTO users VALUES (2, 'Ben');

sqlite> SELECT * FROM users;
1|Alice
2|Ben
sqlite>

ถ้ารันคำสั่ง sqlite3 อย่างเดียวโดยไม่ได้ระบุไฟล์ฐานข้อมูลแบบนี้ หลังจากสร้าง table ใส่ข้อมูลแล้ว ฐานข้อมูลที่สร้างจะถูกเก็บไว้ในหน่วยความจำชั่วคราว ถ้าพิมพ์คำสั่ง .exit ออกจาก sqlite เลย ข้อมูล table ที่สร้างนี้จะหายไป เราต้อง

บันทึกฐานข้อมูลลงเป็นไฟล์ด้วยคำสั่ง .backup

sqlite> .backup test1.db

พิมพ์คำสั่ง .exit ออกจาก sqlite

sqlite> .exit
[user1@cent6-dev ~]$

ลอง ls ดู จะมีไฟล์ test1.db ถูกสร้างขึ้นมา

[user1@cent6-dev ~]$ ls -l test1.db
-rw-r--r--. 1 user1 users 2048 Jun 29 15:22 test1.db

ใช้คำสั่ง file เพื่อดูชนิดของไฟล์ที่บันทึกได้

[user1@cent6-dev ~]$ file test1.db
test1.db: SQLite 3.x database

ไฟล์นี้แหละเป็นไฟล์ที่ SQLite ใช้เก็บเป็นฐานข้อมูล ทีนี้ก็ขึ้นอยู่กับเราแล้วว่าจะเก็บไฟล์นี้ไว้ที่ไหน ตั้งชื่อไฟล์ว่าอะไร เราก็แค่เรียกคำสั่ง sqlite3 แล้วตามด้วยชื่อไฟล์นี้ ก็สามารถดึงข้อมูลที่เราเก็บได้ ตัวอย่างเช่น

[user1@cent6-dev ~]$ sqlite3 test1.db
SQLite version 3.6.20
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> SELECT * FROM users;
1|Alice
2|Ben

ถ้าไฟล์ที่ระบุไม่มีอยู่ sqlite จะสร้างไฟล์นั้นขึ้นมาโดยอัตโนมัติ

สามารถใช้คำสั่ง .databases เพื่อดูว่าตอนนี้เราเรียกใช้ฐานข้อมูลที่เก็บไว้ในไฟล์ไหนอยู่

sqlite> .databases
seq  name             file
---  ---------------  ----------------------------------------------------------
0    main             /home/user1/test1.db

การแก้ไขข้อมูลจะถูกบันทึกลงไปในไฟล์นี้ /home/user1/test1.db ทันที

sqlite> INSERT INTO users VALUES (3, 'Cat');

แต่ถ้าไม่ได้ระบุไฟล์ตอนรันคำสั่ง ต้องการจะโหลดไฟล์ขึ้นมาทีหลัง สามารถทำได้โดยใช้คำสั่ง .restore

[user1@cent6-dev ~]$ sqlite3
SQLite version 3.6.20
Enter ".help" for instructions
Enter SQL statements terminated with a ";"

sqlite> SELECT * FROM users;
Error: no such table: users

sqlite> .restore test1.db

sqlite> SELECT * FROM users;
1|Alice
2|Ben
3|Cat
sqlite>

แต่วิธีการใช้ .restore แบบนี้ข้อมูลที่แก้ไขจะไม่ถูกบันทึกกลับไปในไฟล์ที่ระบุ

ลองใช้คำสั่ง .databases ดู

sqlite> .databases
seq  name             file
---  ---------------  ----------------------------------------------------------
0    main

ถ้าแก้ไขแล้วออกจากคำสั่ง sqlite3 ข้อมูลที่แก้ไขก็จะหายไป

ถ้าต้องการบันทึกข้อมูลที่แก้ไข ต้องใช้คำสั่ง .backup เพื่อบันทึกกลับไปในไฟล์ก่อนแล้วค่อย .exit ออกมา

 

 

คำสั่ง .tables แสดงชื่อ table ที่มีอยู่

sqlite> .tables
users

คำสั่ง .schema ตามด้วยชื่อ table เพื่อแสดงคำสั่ง SQL ที่ใช้สร้าง table นั้น

sqlite> .schema users
CREATE TABLE users (id int, name varchar(255));

เปลี่ยนวิธีการแสดงผล ดีฟอลต์การแสดงผลจะเป็นแบบนี้

sqlite> SELECT * FROM users;
1|Alice
2|Ben
3|Cat

ใช้คำสั่ง .mode ตามด้วยชนิดของการแสดงผลเช่น column

sqlite> .mode column
sqlite> SELECT * FROM users;
1           Alice
2           Ben
3           Cat

หากต้องการให้แสดงชื่อ column ด้วย ให้ใช้คำสั่ง .headers on

sqlite> .header ON
sqlite> SELECT * FROM users;
id          name
----------  ----------
1           Alice
2           Ben
3           Cat

หากต้องการเซฟเก็บเป็นคอนฟิกเก็บไว้ ไม่ต้องมาเปลี่ยนโหมดทุกครั้ง สามารถทำได้โดยสร้างเป็บคอนฟิกไฟล์ .sqliterc อยู่ในไดเร็คทอรี HOME

[user1@cent6-dev ~]$ cat .sqliterc
.headers on
.mode column

[user1@cent6-dev ~]$ sqlite3 test1.db
-- Loading resources from /home/user1/.sqliterc
SQLite version 3.6.20
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> SELECT * FROM users;
id          name
----------  ----------
1           Alice
2           Ben
3           Cat
sqlite>

ใช้คำสั่ง .dump เพื่อแสดง SQL ทั้งหมด ที่ใช้สร้างฐานข้อมูล

sqlite> .dump users
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE users (id int, name varchar(255));
INSERT INTO "users" VALUES(1,'Alice');
INSERT INTO "users" VALUES(2,'Ben');
INSERT INTO "users" VALUES(3,'Cat');
COMMIT;

เซฟเป็นไฟล์ด้วยคำสั่ง .output

sqlite> .output users-dump.sql
sqlite> .dump users
sqlite> .exit

[user1@cent6-dev ~]$ cat users-dump.sql
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE users (id int, name varchar(255));
INSERT INTO "users" VALUES(1,'Alice');
INSERT INTO "users" VALUES(2,'Ben');
INSERT INTO "users" VALUES(3,'Cat');
COMMIT;

ใช้คำสั่ง .read เพื่อโหลด SQL ในไฟล์ที่ได้จากคำสั่ง .dump

[user1@cent6-dev ~]$ sqlite3
SQLite version 3.6.20
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>
sqlite> .tables
sqlite> .read users-dump.sql
sqlite> .tables
users
sqlite> SELECT * FROM users;
1|Alice
2|Ben
3|Cat
sqlite>

ไฟล์ที่ sqlite3 สร้างขึ้นและใช้เก็บเป็นฐานข้อมูล สามารถ copy ไปยังเครื่องอื่นๆ ที่เป็นคนละ OS ได้ และสามารถใช้งานได้เลย เช่นถ้า copy ไฟล์ test1.db ที่ได้จากการสร้างบนลีนุกซ์ไปเปิดบน Windows

 

 

ทดลองเปิดสองหน้าจอพร้อมกัน รันคำสั่ง sqlite3 ให้เรียกไฟล์ฐานข้อมูลเดียวกัน

เพื่อให้เห็นความแตกต่างของสองหน้าจอ จะใช้คำสั่ง prompt เพื่อเปลี่ยนพร้อมต์ของหน้าจอเป็น sqlite-1 และ sqlite-2

หน้าจอที่ 1

[user1@cent6-dev ~]$ sqlite3 test1.db
-- Loading resources from /home/user1/.sqliterc
SQLite version 3.6.20
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .prompt "sqlite-1> "
sqlite-1>

 

หน้าจอที่ 2

[user1@cent6-dev ~]$ sqlite3 test1.db
-- Loading resources from /home/user1/.sqliterc
SQLite version 3.6.20
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .prompt "sqlite-2> "
sqlite-2>

ใช้คำสั่ง SELECT ดูข้อมูลทั้งสองหน้าจอก่อนแก้ไข

sqlite-1> SELECT * FROM users;
id          name
----------  ----------
1           Alice
2           Ben
3           Cat

sqlite-2> SELECT * FROM users;
id          name
----------  ----------
1           Alice
2           Ben
3           Cat

เพิ่มข้อมูลในหน้าจอ 1

sqlite-1> INSERT INTO users VALUES (4, 'Dean');

sqlite-1> SELECT * FROM users;
id          name
----------  ----------
1           Alice
2           Ben
3           Cat
4           Dean

เมื่อใช้ SELECT ในหน้าจอที่ 2 ผลลัพธ์ที่ได้ก็จะเปลี่ยนไปด้วย

sqlite-2> SELECT * FROM users;
id          name
----------  ----------
1           Alice
2           Ben
3           Cat
4           Dean
sqlite-2>

ข้อมูลอ้างอิง

• SQLite Home Page