Tài liệu SQLite
Tài liệu tham khảo SQLite ngắn gọn, đi kèm ví dụ. Đọc khái niệm, xem code, rồi luyện tập trong một hành trình Coddy.
Bắt đầu khóa họcBắt đầu
- SQLite là gìSQLite là cơ sở dữ liệu SQL không cần máy chủ, lưu trong một tệp và chạy ngay bên trong ứng dụng: bản chất của nó, điểm khác với máy chủ cơ sở dữ liệu và khi nào nên dùng.
- Danh sách lệnh SQLNhững lệnh SQL bạn dùng hằng ngày, chia nhóm theo chức năng (DDL, DML, DQL và TCL), mỗi lệnh kèm một ví dụ bạn có thể chạy ngay trong SQLite.
- SQLite vs MySQLSQLite và MySQL thực sự khác nhau ở đâu: kiến trúc, xử lý đồng thời, kiểu dữ liệu và loại dự án mà mỗi hệ được tạo ra để phục vụ.
- SQLite vs PostgresSQLite và PostgreSQL thực sự khác nhau ở đâu: kiến trúc, xử lý đồng thời, kiểu dữ liệu và loại dự án mà mỗi hệ phù hợp nhất.
- Khi nào dùng SQLiteHướng dẫn thực tế để biết khi nào SQLite là cơ sở dữ liệu phù hợp, và khi nào bạn nên chọn Postgres hoặc MySQL.
- Cài đặt SQLiteCách cài SQLite trên Windows, macOS và Linux: tải công cụ dòng lệnh, thêm vào PATH và kiểm tra mọi thứ chạy đúng.
- SQLite CLICách dùng shell dòng lệnh sqlite3: mở cơ sở dữ liệu, chạy truy vấn, các lệnh chấm, nhập và xuất dữ liệu mà không cần rời terminal.
- Công cụ duyệt SQLiteGiới thiệu thực tế các công cụ GUI để mở, duyệt và sửa tệp cơ sở dữ liệu SQLite: DB Browser for SQLite, SQLiteStudio và các trình xem online.
- Tạo cơ sở dữ liệuCách tạo cơ sở dữ liệu SQLite: mô hình dựa trên tệp, lệnh `sqlite3`, và vì sao không có câu lệnh `CREATE DATABASE`.
- Cơ sở dữ liệu trong bộ nhớCách cơ sở dữ liệu trong bộ nhớ của SQLite hoạt động, khi nào nên dùng `:memory:`, và nó khác gì với cơ sở dữ liệu lưu trong tệp mà bạn giữ lại được.
Bảng và kiểu dữ liệu
- CREATE TABLECách tạo bảng trong SQLite: định nghĩa cột, ràng buộc, IF NOT EXISTS, bảng tạm và CREATE TABLE AS SELECT.
- Kiểu dữ liệuCách SQLite lưu giá trị: năm lớp lưu trữ, vì sao kiểu dữ liệu là động, và những cái bẫy hay làm khó người chuyển từ Postgres hoặc MySQL sang.
- Type affinityCách hệ thống type affinity của SQLite hoạt động: năm loại affinity, quy tắc chọn affinity từ khai báo cột, và vì sao cột INTEGER vẫn chứa được chuỗi.
- Bảng STRICTCách bảng STRICT của SQLite tắt cơ chế lưu kiểu linh hoạt, từ chối giá trị sai kiểu và mang lại việc kiểm tra kiểu mà có lẽ bạn đã trông đợi từ đầu.
- Khóa chínhCách khóa chính hoạt động trong SQLite: INTEGER PRIMARY KEY đặc biệt, khóa ghép, AUTOINCREMENT và những điểm kỳ quặc hay làm người mới vấp.
- ROWIDROWID thật ra là gì trong SQLite, khi nào INTEGER PRIMARY KEY biến nó thành cột của bạn, và vì sao bảng WITHOUT ROWID tồn tại.
- NOT NULL & DEFAULTCách NOT NULL và DEFAULT hoạt động trong SQLite: chúng thật sự bắt buộc điều gì, mẹo CURRENT_TIMESTAMP và những điểm cần lưu ý khi thêm chúng vào bảng đã có.
- Ràng buộc CHECKCách dùng ràng buộc CHECK trong SQLite để áp quy tắc lên giá trị của cột: kiểm tra một cột, kiểm tra nhiều cột, ràng buộc có tên và những điểm dễ mắc lỗi.
- Ràng buộc UNIQUECách ràng buộc UNIQUE hoạt động trong SQLite: cấp cột, cấp bảng, khóa kết hợp nhiều cột, điểm lạ với NULL và cách xử lý khi ràng buộc bị vi phạm.
- DROP & ALTER TABLECách xóa, đổi tên và sửa bảng trong SQLite: ALTER TABLE làm được gì, không làm được gì, và mẹo tạo lại bảng cho những thay đổi nó không hỗ trợ.
Thêm và sửa dữ liệu
- INSERTCách INSERT hoạt động trong SQLite: thêm một hàng, thêm nhiều hàng, INSERT...SELECT, giá trị mặc định và các tùy chọn xử lý xung đột OR IGNORE / OR REPLACE.
- Lệnh UPDATE trong SQLCách sửa các hàng có sẵn trong SQLite: cú pháp UPDATE, mệnh đề WHERE giúp bạn an toàn, cập nhật nhiều cột và UPDATE ... FROM để sửa dữ liệu giữa các bảng.
- DELETECách DELETE hoạt động trong SQLite: viết mệnh đề WHERE an toàn, xóa mọi hàng, xóa dây chuyền sang bảng liên quan và lấy lại các hàng đã xóa bằng RETURNING.
- UPSERTCách UPSERT hoạt động trong SQLite: mệnh đề ON CONFLICT, bảng excluded, DO NOTHING so với DO UPDATE, và khác biệt với INSERT OR REPLACE.
- Mệnh đề RETURNINGCách mệnh đề RETURNING hoạt động trong SQLite: lấy về những hàng mà INSERT, UPDATE hoặc DELETE vừa tác động mà không cần truy vấn thứ hai.
- Nhập CSVCách nhập tệp CSV vào SQLite bằng lệnh .import: xử lý dòng tiêu đề, bảng có sẵn, ký tự phân tách tùy chỉnh và những lỗi hay gặp nhất.
- Xuất dữ liệuCách xuất dữ liệu ra khỏi SQLite: CSV có tiêu đề cột, JSON, bản dump SQL đầy đủ và sao lưu từng bảng bằng shell dòng lệnh sqlite3.
Truy vấn dữ liệu
- SELECT cơ bảnCách câu lệnh SELECT hoạt động trong SQLite: chọn cột, tính biểu thức, đặt bí danh cho kết quả, và cách hình dung đằng sau mọi truy vấn bạn sẽ viết.
- Mệnh đề WHERECách mệnh đề WHERE lọc hàng trong SQLite: toán tử so sánh, AND/OR, LIKE, IN, BETWEEN, và cái bẫy NULL mà ai cũng từng mắc.
- Toán tử & NULLCách các toán tử SQLite xử lý NULL: vì sao `=` và `<>` không hoạt động như bạn nghĩ, và các công cụ IS, IS NOT, COALESCE, IFNULL làm được điều đó.
- ORDER BYCách ORDER BY hoạt động trong SQLite: sắp xếp tăng dần và giảm dần, phân định các hàng trùng giá trị bằng nhiều cột, xử lý NULL và sắp xếp không phân biệt chữ hoa chữ thường.
- LIMIT & OFFSETCách LIMIT và OFFSET hoạt động trong SQLite: giới hạn số hàng, bỏ qua hàng, phân trang an toàn và cái bẫy hiệu năng cần tránh với bảng lớn.
- DISTINCTCách SELECT DISTINCT hoạt động trong SQLite: một cột, nhiều cột, cách xử lý NULL, COUNT(DISTINCT) và khi nào GROUP BY là công cụ tốt hơn.
- Câu lệnh CASE trong SQLCách CASE hoạt động trong SQLite: dạng đơn giản và dạng tìm kiếm, dùng trong SELECT, ORDER BY và WHERE, cùng lúc nào nên dùng IIF thay thế.
Hàm và hàm tổng hợp
- Hàm chuỗiCác hàm chuỗi thực dụng trong SQLite: nối chuỗi, SUBSTR, INSTR, REPLACE, TRIM, cùng các mẫu làm sạch và biến đổi văn bản ngay trong truy vấn.
- Hàm số họcCách tính toán trong SQLite: ROUND, ABS, CEIL, FLOOR, MOD, POWER, SQRT, RANDOM, và cái bẫy chia số nguyên mà ai cũng mắc ít nhất một lần.
- Ngày và giờCách SQLite lưu và xử lý ngày tháng: năm hàm ngày giờ, chuỗi định dạng, modifier và cách chọn kiểu lưu trữ để truy vấn luôn nhanh.
- Hàm tổng hợp SQLCách các hàm tổng hợp trong SQLite gộp nhiều hàng thành một giá trị: COUNT, SUM, AVG, MIN, MAX, GROUP_CONCAT, cùng DISTINCT, FILTER và các quy tắc với NULL.
- GROUP BY & HAVINGCách GROUP BY gộp các hàng thành từng nhóm trong SQLite, và cách HAVING lọc các nhóm đó sau khi tổng hợp, kèm ví dụ cụ thể về khác biệt giữa WHERE và HAVING.
Quan hệ và join
- Khóa ngoạiCách khóa ngoại hoạt động trong SQLite: khai báo REFERENCES, bật kiểm tra bằng PRAGMA và chọn hành vi ON DELETE phù hợp.
- INNER JOINCách INNER JOIN hoạt động trong SQLite: cách hình dung, mệnh đề ON, nối ba bảng và cú pháp rút gọn USING.
- LEFT JOINCách LEFT JOIN hoạt động trong SQLite: giữ các hàng không khớp, đọc giá trị NULL, lọc an toàn và nối nhiều hơn hai bảng.
- Self JoinCách self join hoạt động trong SQLite: ghép các hàng từ cùng một bảng bằng alias, kèm ví dụ cụ thể cho nhân viên/quản lý và dữ liệu phân cấp.
- SubqueryCách lồng một câu SELECT vào trong câu khác ở SQLite: subquery vô hướng, IN/EXISTS, bảng dẫn xuất, subquery tương quan và khi nào JOIN dễ đọc hơn.
- CTECách Common Table Expression hoạt động trong SQLite: dùng WITH để đặt tên cho truy vấn con, nối nhiều CTE và viết truy vấn đọc được từ trên xuống dưới.
- CTE đệ quyCách CTE đệ quy hoạt động trong SQLite: cấu trúc anchor và phần đệ quy, duyệt cây cha con, sinh dãy giá trị và tránh vòng lặp vô hạn.
Chỉ mục và hiệu năng
- IndexCách index hoạt động trong SQLite, khi nào chúng giúp ích, khi nào gây hại, và cách xem bộ lập kế hoạch có thực sự dùng chúng hay không.
- Chỉ mục kết hợpCách chỉ mục nhiều cột hoạt động trong SQLite, vì sao thứ tự cột quan trọng, khi nào chỉ mục kết hợp giúp tăng tốc và khi nào chỉ tốn chỗ.
- Partial indexCách partial index hoạt động trong SQLite: chỉ đánh chỉ mục những hàng bạn thật sự truy vấn, và các mẫu (xóa mềm, duy nhất một phần, tập con hay truy cập) giúp nó phát huy hiệu quả.
- EXPLAIN QUERY PLANCách dùng EXPLAIN QUERY PLAN trong SQLite để xem truy vấn có dùng index hay không, SCAN và SEARCH nghĩa là gì, và cách đọc kế hoạch của phép join.
- ANALYZE và VACUUMCách `ANALYZE` và `VACUUM` giữ cho cơ sở dữ liệu SQLite nhanh và gọn: mỗi lệnh thực sự làm gì, khi nào nên chạy và những biến thể đáng biết.
Tính năng nâng cao
- Giao dịchCách giao dịch hoạt động trong SQLite: BEGIN, COMMIT, ROLLBACK, autocommit và các chế độ DEFERRED/IMMEDIATE/EXCLUSIVE quyết định khi nào khóa được lấy.
- SavepointCách savepoint hoạt động trong SQLite: các mốc có tên bên trong một giao dịch mà bạn có thể quay lại mà không phải vứt bỏ toàn bộ.
- ViewCách view hoạt động trong SQLite: lưu truy vấn thành bảng ảo, khi nào dùng view tạm thời, và vì sao view của SQLite mặc định chỉ đọc được.
- TriggerCách trigger hoạt động trong SQLite: BEFORE và AFTER, INSTEAD OF trên view, tham chiếu hàng OLD và NEW, và khi nào trigger là công cụ phù hợp.
- Hỗ trợ JSONCách SQLite lưu và truy vấn JSON: lấy trường, cập nhật giá trị, tách mảng với json_each và tạo chỉ mục trên đường dẫn JSON để tăng tốc.
- Tìm kiếm toàn vănCách thêm tìm kiếm toàn văn vào SQLite bằng FTS5: tạo bảng ảo, toán tử MATCH, xếp hạng BM25 và giữ index đồng bộ với dữ liệu.
- Window functionCách window function hoạt động trong SQLite: OVER, PARTITION BY, các hàm xếp hạng, LAG/LEAD và mệnh đề frame để tính tổng lũy kế.
- Cột sinh tự độngCách cột sinh tự động hoạt động trong SQLite: khai báo, chọn giữa VIRTUAL và STORED, và đánh index để tra cứu nhanh.
- ATTACH DATABASECách ATTACH DATABASE cho phép bạn mở nhiều tệp SQLite trong cùng một kết nối, truy vấn chéo giữa chúng bằng tiền tố schema và tách ra khi đã dùng xong.
An toàn và tích hợp ứng dụng
- Prepared statementPrepared statement trong SQLite là gì, vì sao nó tồn tại, và vòng đời prepare/bind/step/finalize mà mọi driver đều bọc lại.
- Gắn tham sốCách gắn tham số (parameter binding) hoạt động trong SQLite: placeholder theo vị trí, tham số có tên và các quy tắc để truyền giá trị an toàn từ ứng dụng.
- SQL injectionVì sao nối chuỗi lại nguy hiểm, SQL injection thật sự hoạt động thế nào, và truy vấn tham số hóa trong SQLite chặn đứng nó ra sao.
- Kết nối từ ứng dụngCách ứng dụng mở và dùng một cơ sở dữ liệu SQLite: connection string, đường dẫn tệp, driver ở nhiều ngôn ngữ và những cài đặt nên làm đúng ngay từ ngày đầu.
- MigrationCách thay đổi schema SQLite an toàn theo thời gian bằng PRAGMA user_version, các script migration có thứ tự và transaction để có thể hoàn tác.
- WAL và đồng thờiCách write-ahead logging của SQLite thay đổi chuyện truy cập đồng thời: đọc và ghi không còn chặn nhau, và các tệp -wal, -shm thực sự làm gì.
Vận hành thực tế
- Sao lưu và khôi phụcCách sao lưu và khôi phục cơ sở dữ liệu SQLite an toàn: lệnh .backup, VACUUM INTO, API sao lưu online, và vì sao sao chép tệp là cách làm sai.
- Thiết lập PRAGMANhững câu lệnh PRAGMA thật sự quan trọng: journal_mode, synchronous, foreign_keys, busy_timeout, cache_size, cùng các giá trị nên dùng trên production.
- Lỗi thường gặpNhững lỗi SQLite bạn sẽ thực sự gặp trên production như database is locked, readonly database, disk image malformed, vi phạm ràng buộc, và cách sửa từng lỗi.
