SQLite CLI: dùng shell dòng lệnh sqlite3

CLI là gì

sqlite3 là shell dòng lệnh chính thức đi kèm SQLite. Nó mở một tệp cơ sở dữ liệu, cho bạn một dấu nhắc lệnh và cho phép chạy SQL trên tệp đó. Không máy chủ, không đăng nhập, không cấu hình: chỉ một tệp thực thi và một tệp dữ liệu.

Bạn sẽ dùng nó để kiểm tra nhanh, chạy truy vấn lẻ, xuất schema và nhập dữ liệu. Đây là cách nhanh nhất để xem xét một cơ sở dữ liệu mà không cần viết code ứng dụng.

$ sqlite3 mydata.db
SQLite version 3.45.0
Enter ".help" for usage hints.
sqlite>

Nếu mydata.db chưa tồn tại, SQLite sẽ tạo nó ngay khi bạn thực hiện thay đổi đầu tiên. Chạy sqlite3 mà không có tên tệp sẽ mở một cơ sở dữ liệu trong bộ nhớ dùng một lần.

Chạy SQL tại dấu nhắc

Khi đã vào shell, hãy gõ SQL và kết thúc mỗi câu lệnh bằng dấu chấm phẩy. Shell chờ dấu chấm phẩy đó: nếu bạn nhấn Enter mà không có nó, shell chỉ hiện một dấu nhắc tiếp nối.

CREATE TABLE books (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  year INTEGER
);

INSERT INTO books (title, year) VALUES ('Dune', 1965);
INSERT INTO books (title, year) VALUES ('Neuromancer', 1984);

SELECT * FROM books;

Dấu chấm phẩy là tín hiệu "xong rồi, chạy đi". Quên nó thì shell sẽ đứng đó với dấu nhắc ...> và chờ. Gõ ; trên một dòng mới để kết thúc.

Lệnh chấm không phải SQL

Mọi thứ bắt đầu bằng . là một lệnh chấm (dot command): chỉ dẫn cho chính shell, không phải cho bộ máy SQL. Chúng không cần dấu chấm phẩy và chỉ hoạt động bên trong shell sqlite3.

sqlite> .tables
books

sqlite> .schema books
CREATE TABLE books (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  year INTEGER
);

sqlite> .help

Vài lệnh bạn sẽ dùng thường xuyên:

• .tables: liệt kê mọi bảng trong cơ sở dữ liệu hiện tại.
• .schema [table]: hiển thị câu lệnh CREATE TABLE của một bảng hoặc của tất cả các bảng.
• .databases: liệt kê các tệp cơ sở dữ liệu đang được gắn vào.
• .mode <format>: đổi cách hiển thị kết quả truy vấn.
• .headers on: hiển thị tên cột phía trên kết quả truy vấn.
• .read file.sql: thực thi một tệp SQL.
• .exit hoặc .quit: thoát khỏi shell.

.help in ra danh sách đầy đủ. Có đến hàng chục lệnh, nhưng bạn sẽ chỉ hay dùng khoảng mười lệnh.

Làm cho đầu ra dễ đọc

Đầu ra mặc định phân tách bằng dấu gạch đứng và không có tiêu đề cột: ổn cho script, nhưng khó đọc. Hai lệnh chấm sẽ khắc phục điều đó:

sqlite> .headers on
sqlite> .mode column
sqlite> SELECT * FROM books;
id  title        year
--  -----------  ----
1   Dune         1965
2   Neuromancer  1984

Các chế độ hữu ích khác:

• .mode box: vẽ bảng có viền Unicode. Đẹp khi chụp màn hình.
• .mode csv: phân tách bằng dấu phẩy, tốt để chuyển sang công cụ khác.
• .mode json: đầu ra JSON, mỗi hàng một đối tượng.
• .mode line: mỗi cột một dòng, tiện khi hàng quá rộng.

Các thiết lập sẽ đặt lại mỗi lần bạn mở shell. Để giữ chúng lâu dài, hãy đưa chúng vào tệp ~/.sqliterc và sqlite3 sẽ nạp tệp này khi khởi động.

Nhập và xuất CSV

CLI hỗ trợ CSV sẵn. Chuyển sang chế độ CSV, rồi dùng .import để nhập một tệp vào bảng:

sqlite> .mode csv
sqlite> .import authors.csv authors
sqlite> SELECT * FROM authors LIMIT 3;

Nếu bảng chưa tồn tại, .import sẽ tạo bảng mới, lấy hàng đầu tiên làm tên cột. Nếu bảng đã có, hàng đầu tiên được coi là dữ liệu, trừ khi bạn truyền --skip 1.

Theo chiều ngược lại, hãy chuyển hướng đầu ra vào một tệp:

sqlite> .headers on
sqlite> .mode csv
sqlite> .output books.csv
sqlite> SELECT * FROM books;
sqlite> .output stdout

.output filename gửi mọi kết quả truy vấn sau đó vào tệp này cho đến khi bạn chuyển lại bằng .output stdout. Cơ chế này giống nhau dù bạn xuất CSV, JSON hay bản dump schema thô.

Chạy tệp SQL

Với bất cứ thứ gì dài hơn vài câu lệnh, hãy đặt SQL vào một tệp rồi chạy nó. Bên trong shell:

sqlite> .read setup.sql

Hoặc từ terminal bình thường, không cần vào shell:

$ sqlite3 mydata.db < setup.sql
$ sqlite3 mydata.db "SELECT count(*) FROM books;"

Cách thứ hai chạy một truy vấn duy nhất rồi thoát, rất hợp cho shell script hoặc kiểm tra nhanh. Kết hợp với các thiết lập .mode qua cờ -cmd nếu bạn cần đầu ra có định dạng:

$ sqlite3 -header -column mydata.db "SELECT * FROM books;"

Xem xét một cơ sở dữ liệu lạ

Khi ai đó đưa bạn một tệp .db và bạn không biết bên trong có gì, đây là quy trình:

$ sqlite3 mystery.db
sqlite> .tables
sqlite> .schema
sqlite> .indexes
sqlite> SELECT count(*) FROM some_table;

.schema không có tham số sẽ in ra mọi CREATE TABLE, CREATE INDEX và CREATE TRIGGER trong tệp. .indexes liệt kê index theo từng bảng. Năm phút trong shell thường cho bạn biết về một cơ sở dữ liệu nhiều hơn bất kỳ tệp README nào.

Để có một bản chụp dạng văn bản đầy đủ mà bạn có thể tạo lại ở nơi khác, hãy dùng .dump:

sqlite> .output backup.sql
sqlite> .dump
sqlite> .output stdout

Lệnh này ghi ra một script SQL tạo lại toàn bộ cơ sở dữ liệu, cả schema lẫn dữ liệu, khi được chạy lại qua sqlite3.

Thoát khỏi shell

Có ba cách thoát, đều như nhau:

sqlite> .exit
sqlite> .quit

Hoặc nhấn Ctrl+D trên macOS và Linux (Ctrl+Z rồi Enter trên Windows). Các thay đổi của bạn đã được lưu sẵn: SQLite commit từng câu lệnh ngay khi chạy, trừ khi bạn đã chủ động bắt đầu một giao dịch.

Tiếp theo: công cụ GUI cho SQLite

CLI nhanh và dễ viết script, nhưng đôi khi bạn muốn bấm qua các bảng, sắp xếp cột và sửa hàng một cách trực quan. Có vài công cụ GUI tốt cho SQLite, và đó là nội dung của trang tiếp theo.