SQLite Các chế độ xem, chỉ mục và trình kích hoạt kèm ví dụ.

Trong việc sử dụng hàng ngày của SQLite, bạn sẽ cần một số công cụ quản trị cơ sở dữ liệu của mình. Bạn cũng có thể sử dụng chúng để truy vấn cơ sở dữ liệu hiệu quả hơn bằng cách tạo chỉ mục hoặc có thể tái sử dụng nhiều hơn bằng cách tạo dạng xem.

SQLite Xem

Lượt xem rất giống với bảng. Nhưng Chế độ xem là các bảng logic; chúng không được lưu trữ vật lý như bảng. Một khung nhìn bao gồm một câu lệnh chọn.

Bạn có thể định nghĩa chế độ xem cho các truy vấn phức tạp của mình và có thể sử dụng lại các truy vấn này bất cứ khi nào bạn muốn bằng cách gọi trực tiếp chế độ xem thay vì phải viết lại các truy vấn.

TẠO câu lệnh XEM

Để tạo chế độ xem trên cơ sở dữ liệu, bạn có thể sử dụng câu lệnh CREATE VIEW theo sau là tên chế độ xem, sau đó đặt truy vấn bạn muốn sau đó.

Ví dụ: Trong ví dụ sau, chúng ta sẽ tạo một View có tên là “AllStudentsView” trong cơ sở dữ liệu mẫu “TutorialsSampleDB.db” như sau:

Bước 1) Mở My Computer và điều hướng đến thư mục “C:\sqlite”, sau đó mở tệp “sqlite3.exe”:

Bước 2) Mở cơ sở dữ liệu “TutorialsSampleDB.db” bằng lệnh sau:

Bước 3) Sau đây là cú pháp cơ bản của lệnh sqlite3 để tạo View

CREATE VIEW AllStudentsView
AS
  SELECT 
    s.StudentId,
    s.StudentName,
    s.DateOfBirth,
    d.DepartmentName
FROM Students AS s
INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;

Sẽ không có đầu ra từ lệnh như thế này:

Bước 4) Để đảm bảo chế độ xem được tạo, bạn có thể chọn danh sách chế độ xem trong cơ sở dữ liệu bằng cách chạy lệnh sau:

SELECT name FROM sqlite_master WHERE type = 'view';

Bạn sẽ thấy kết quả trả về là "AllStudentsView":

Bước 5) Bây giờ chế độ xem của chúng tôi đã được tạo, bạn có thể sử dụng nó như một bảng bình thường như thế này:

SELECT * FROM AllStudentsView;

Lệnh này sẽ truy vấn chế độ xem “AllStudents” và chọn tất cả các hàng từ chế độ xem đó như được hiển thị trong ảnh chụp màn hình sau:

Lượt xem tạm thời

Chế độ xem tạm thời là chế độ xem tạm thời cho kết nối cơ sở dữ liệu hiện tại được sử dụng để tạo ra nó. Sau đó, nếu bạn đóng kết nối cơ sở dữ liệu, tất cả các chế độ xem tạm thời sẽ tự động bị xóa. Chế độ xem tạm thời được tạo bằng một trong các lệnh sau:

• TẠO CHẾ ĐỘ XEM NHIỆT ĐỘ, hoặc
• TẠO XEM TẠM THỜI.

Chế độ xem tạm thời rất hữu ích nếu bạn muốn thực hiện một số thao tác trong thời gian đó và không cần chế độ xem đó là chế độ xem vĩnh viễn. Vì vậy, bạn chỉ cần tạo chế độ xem tạm thời, sau đó xử lý bằng chế độ xem đó. Later khi bạn đóng kết nối với cơ sở dữ liệu, nó sẽ tự động bị xóa.

Ví dụ:

Trong ví dụ sau, chúng ta sẽ mở kết nối cơ sở dữ liệu, sau đó tạo chế độ xem tạm thời.

Sau đó, chúng tôi sẽ đóng kết nối đó và kiểm tra xem chế độ xem tạm thời có còn tồn tại hay không.

Bước 1) Mở sqlite3.exe từ thư mục “C:\sqlite” như đã hướng dẫn ở trên.

Bước 2) Mở kết nối đến cơ sở dữ liệu “TutorialsSampleDB.db” bằng cách chạy lệnh sau:

.open TutorialsSampleDB.db

Bước 3) Hãy viết lệnh sau để tạo một khung nhìn tạm thời có tên “AllStudentsTempView”:

CREATE TEMP VIEW AllStudentsTempView
AS
  SELECT 
    s.StudentId,
    s.StudentName,
    s.DateOfBirth,
    d.DepartmentName
FROM Students AS s
INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;

Bước 4) Hãy đảm bảo rằng khung nhìn tạm thời “AllStudentsTempView” được tạo bằng cách chạy lệnh sau:

SELECT name FROM sqlite_temp_master WHERE type = 'view';

Bước 5) Đóng sqlite3.exe và mở lại.

Bước 6) Mở kết nối đến cơ sở dữ liệu “TutorialsSampleDB.db” bằng lệnh sau:

.open TutorialsSampleDB.db

Bước 7) Chạy lệnh sau để lấy danh sách chế độ xem tạm thời được tạo trên cơ sở dữ liệu:

SELECT name FROM sqlite_temp_master WHERE type = 'view';

Bạn sẽ không thấy bất kỳ đầu ra nào vì chế độ xem tạm thời mà chúng ta tạo ra sẽ bị xóa khi chúng ta đóng kết nối cơ sở dữ liệu ở bước trước. Nếu không, miễn là bạn giữ kết nối với cơ sở dữ liệu mở, bạn sẽ có thể thấy chế độ xem tạm thời có dữ liệu.

Ghi chú:

• Bạn không thể sử dụng các câu lệnh INSERT, DELETE hoặc UPDATE với các dạng xem, chỉ bạn mới có thể sử dụng lệnh “chọn từ các dạng xem” như được hiển thị trong bước 5 trong ví dụ TẠO dạng xem.
• Để xóa một VIEW, bạn có thể sử dụng câu lệnh “DROP VIEW”:

DROP VIEW AllStudentsView;

Để đảm bảo rằng chế độ xem đã bị xóa, bạn có thể chạy lệnh sau để biết danh sách các chế độ xem trong cơ sở dữ liệu:

SELECT name FROM sqlite_master WHERE type = 'view';

Bạn sẽ không tìm thấy chế độ xem nào được trả về khi chế độ xem đã bị xóa, như sau:

Ngoài việc tái sử dụng các truy vấn phức tạp với các view, SQLite Ngoài ra, việc truy xuất các hàng phù hợp cũng nhanh hơn thông qua các chỉ mục.

SQLite Chỉ số

Nếu bạn có một cuốn sách và muốn tìm kiếm từ khóa về cuốn sách đó. Bạn sẽ tìm kiếm từ khóa đó trong mục lục của cuốn sách. Sau đó bạn sẽ điều hướng đến số trang cho từ khóa đó để đọc thêm thông tin về từ khóa đó.

Tuy nhiên, nếu không có mục lục trên cuốn sách đó hoặc số trang, bạn sẽ phải quét toàn bộ cuốn sách từ đầu đến cuối cho đến khi tìm thấy từ khóa bạn đang tìm kiếm. Và điều này rất khó khăn, đặc biệt là khi bạn có mục lục và quá trình tìm kiếm từ khóa rất chậm.

Chỉ mục trong SQLite (và khái niệm tương tự có giá trị cho các Hệ thống Quản lý Dữ liệu cũng vậy) hoạt động theo cách tương tự như các chỉ mục được tìm thấy ở cuối sách.

Khi bạn tìm kiếm một số hàng trong một SQLite bảng với tiêu chí tìm kiếm, SQLite sẽ tìm kiếm trên tất cả các hàng của bảng cho đến khi tìm thấy những hàng bạn đang tìm phù hợp với tiêu chí tìm kiếm. Và quá trình đó trở nên rất chậm khi bạn có các bảng lớn hơn.

Các chỉ mục sẽ tăng tốc các truy vấn tìm kiếm dữ liệu và sẽ giúp thực hiện việc truy xuất dữ liệu từ các bảng. Các chỉ mục được xác định trên các cột của bảng.

Cải thiện hiệu suất với chỉ mục:

Chỉ mục có thể cải thiện hiệu suất tìm kiếm dữ liệu trên bảng. Khi bạn tạo chỉ mục trên một cột, SQLite sẽ tạo cấu trúc dữ liệu cho chỉ mục đó trong đó mỗi giá trị trường có một con trỏ tới toàn bộ hàng chứa giá trị đó.

Sau đó, nếu bạn chạy truy vấn với điều kiện tìm kiếm trên một cột là một phần của chỉ mục, SQLite sẽ tìm kiếm giá trị trên chỉ mục trước tiên. SQLite sẽ không quét toàn bộ bảng cho nó. Sau đó nó sẽ đọc vị trí các điểm giá trị cho hàng của bảng. SQLite sẽ xác định vị trí hàng trên vị trí đó và lấy nó.

Tuy nhiên, nếu cột bạn đang tìm kiếm không phải là một phần của chỉ mục, SQLite sẽ thực hiện quét các giá trị cột để tìm dữ liệu bạn đang tìm kiếm. Quá trình này thường sẽ chậm hơn nếu không có chỉ mục.

Hãy tưởng tượng một cuốn sách không có mục lục và bạn cần tìm kiếm một từ cụ thể. Bạn sẽ quét toàn bộ cuốn sách từ trang đầu tiên đến trang cuối cùng để tìm từ đó. Tuy nhiên, nếu bạn có chỉ mục về cuốn sách đó, bạn sẽ tìm từ trên đó trước tiên. Lấy số trang nơi nó nằm và sau đó điều hướng đến nó. Việc này sẽ nhanh hơn nhiều so với việc quét toàn bộ cuốn sách từ đầu đến cuối.

SQLite TẠO CHỈ SỐ

Để tạo chỉ mục trên một cột, bạn nên sử dụng lệnh CREATE INDEX. Và bạn nên xác định nó như sau:

• Bạn phải chỉ định tên của chỉ mục sau lệnh CREATE INDEX.
• Sau tên chỉ mục bạn phải đặt từ khóa “ON”, theo sau là tên bảng sẽ tạo chỉ mục.
• Sau đó là danh sách tên cột được sử dụng cho chỉ mục.
• Bạn có thể sử dụng một trong các từ khóa sau “ASC” hoặc “DESC” sau bất kỳ tên cột nào để chỉ định thứ tự sắp xếp được sử dụng để sắp xếp dữ liệu chỉ mục.

Ví dụ:

Trong ví dụ sau, chúng ta sẽ tạo chỉ mục “StudentNameIndex” trên bảng students trong cơ sở dữ liệu “Students” như sau:

Bước 1) Điều hướng đến thư mục “C:\sqlite” như đã hướng dẫn trước đó.

Bước 2) Mở sqlite3.exe.

Bước 3) Mở cơ sở dữ liệu “TutorialsSampleDB.db” bằng lệnh sau:

.open TutorialsSampleDB.db

Bước 4) Tạo chỉ mục mới “StudentNameIndex” bằng lệnh sau:

CREATE INDEX StudentNameIndex ON Students(StudentName);

Bạn sẽ không thấy kết quả nào cho việc này:

Bước 5) Để đảm bảo rằng chỉ mục đã được tạo, bạn có thể chạy truy vấn sau, truy vấn này sẽ cung cấp cho bạn danh sách các chỉ mục đã được tạo trong bảng Students:

PRAGMA index_list(Students);

Bạn sẽ thấy chỉ mục chúng tôi vừa tạo được trả về:

Ghi chú:

• Các chỉ mục có thể được tạo không chỉ dựa trên các cột mà còn cả các biểu thức. Một cái gì đó như thế này:

CREATE INDEX OrderTotalIndex ON OrderItems(OrderId, Quantity*Price);

“OrderTotalIndex” sẽ dựa trên cột OrderId và cũng dựa trên phép nhân của giá trị cột Số lượng và giá trị cột Giá. Vì vậy, mọi truy vấn về “OrderId” và “Quantity*Price” sẽ hiệu quả vì truy vấn sẽ sử dụng chỉ mục.

• Nếu bạn chỉ định mệnh đề WHERE trong câu lệnh CREATE INDEX, chỉ mục sẽ là chỉ mục một phần. Trong trường hợp này, sẽ có các mục trong chỉ mục chỉ dành cho các hàng khớp với các điều kiện trong mệnh đề WHERE. Ví dụ, trong chỉ mục sau:

  CREATE INDEX OrderTotalIndexForLargeQuantities ON OrderItems(OrderId, Quantity*Price)
  WHERE Quantity > 10000;

  (Trong ví dụ trên, chỉ mục sẽ là chỉ mục một phần vì có mệnh đề WHERE được chỉ định. Trong trường hợp này, chỉ mục sẽ chỉ được áp dụng cho những đơn hàng có giá trị số lượng lớn hơn 10000. Lưu ý rằng, chỉ mục này được gọi là một phần chỉ mục vì mệnh đề WHERE chứ không phải biểu thức được sử dụng trên đó. Tuy nhiên, bạn có thể sử dụng các biểu thức có chỉ mục thông thường.)

• Bạn có thể sử dụng câu lệnh CREATE UNIQUE INDEX thay vì CREATE INDEX để ngăn các mục nhập trùng lặp cho các cột và do đó tất cả các giá trị cho cột được lập chỉ mục sẽ là duy nhất.
• Để xóa một chỉ mục, sử dụng lệnh DROP INDEX theo sau là tên chỉ mục để xóa.

Mặc dù chỉ mục giúp đọc dữ liệu nhanh hơn, nhưng trình kích hoạt cho phép SQLite Tự động phản hồi mỗi khi dữ liệu thay đổi.

SQLite Cò súng

Giới thiệu về SQLite Cò súng

Trigger là các hoạt động được xác định trước tự động được thực hiện khi một hành động cụ thể xảy ra trên một bảng cơ sở dữ liệu. Trigger có thể được xác định để kích hoạt bất cứ khi nào một trong các hành động sau xảy ra trên một bảng:

• INSERT vào một bảng.
• XÓA các hàng khỏi một bảng.
• CẬP NHẬT một trong các cột của bảng.

SQLite hỗ trợ kích hoạt CHO MỖI HÀNG để các hoạt động được xác định trước trong kích hoạt sẽ được thực thi cho tất cả các hàng liên quan đến các hành động xảy ra trên bảng (cho dù là chèn, xóa hay cập nhật).

SQLite TẠO KÍCH HOẠT

Để tạo một TRIGGER mới, bạn có thể sử dụng câu lệnh CREATE TRIGGER như sau:

• Sau TẠO TRIGGER, bạn nên chỉ định tên trình kích hoạt.
• Sau tên trình kích hoạt, bạn phải chỉ định thời điểm chính xác tên trình kích hoạt sẽ được thực thi. Bạn có ba lựa chọn:
  • TRƯỚC – trigger sẽ được thực thi trước câu lệnh INSERT, UPDATE hoặc delete được chỉ định.
  • After – trigger sẽ được thực thi sau câu lệnh INSERT, UPDATE hoặc delete được chỉ định.
  • THAY THẾ – Nó sẽ thay thế hành động đã xảy ra kích hoạt trình kích hoạt bằng câu lệnh được chỉ định trong TRIGGER. Trình kích hoạt INSTEAD OF không áp dụng được với bảng, chỉ với chế độ xem.
• Sau đó, bạn phải chỉ định loại hành động, trình kích hoạt sẽ kích hoạt khi nó xảy ra. XÓA, CHÈN hoặc CẬP NHẬT.
• Bạn có thể chọn tên cột tùy chọn để trình kích hoạt không kích hoạt trừ khi hành động xảy ra trên cột đó.
• Sau đó, bạn phải chỉ định tên bảng mà trình kích hoạt sẽ được tạo.
• Bên trong phần thân của trình kích hoạt, bạn nên chỉ định câu lệnh sẽ được thực thi cho mỗi hàng khi trình kích hoạt được kích hoạt.

Trình kích hoạt sẽ chỉ được kích hoạt (kích hoạt) tùy thuộc vào loại câu lệnh được chỉ định trong lệnh tạo trình kích hoạt. Ví dụ:

• Trình kích hoạt BEFORE INSERT sẽ được kích hoạt (kích hoạt) trước bất kỳ câu lệnh chèn nào.
• Trình kích hoạt SAU CẬP NHẬT sẽ được kích hoạt (kích hoạt) sau bất kỳ câu lệnh cập nhật nào, v.v.

Bên trong trigger, bạn có thể tham chiếu đến các giá trị mới được chèn bằng từ khóa “new”. Ngoài ra, bạn có thể tham chiếu đến các giá trị đã xóa hoặc cập nhật bằng từ khóa old. Như sau:

• Bên trong trình kích hoạt INSERT – có thể sử dụng từ khóa mới.
• Trình kích hoạt CẬP NHẬT bên trong - có thể sử dụng từ khóa mới và cũ.
• Bên trong trình kích hoạt DELETE – từ khóa cũ có thể được sử dụng.

Ví dụ

Trong phần tiếp theo, chúng ta sẽ tạo một trigger sẽ được kích hoạt trước khi thêm một sinh viên mới vào bảng “Sinh viên”.

Nó sẽ ghi lại thông tin sinh viên mới được thêm vào bảng “StudentsLog” với dấu thời gian tự động là ngày giờ hiện tại khi câu lệnh chèn được thực thi. Cụ thể như sau:

Bước 1) Điều hướng đến thư mục “C:\sqlite” và chạy sqlite3.exe.

Bước 2) Mở cơ sở dữ liệu “TutorialsSampleDB.db” bằng cách chạy lệnh sau:

.open TutorialsSampleDB.db

Bước 3) Tạo trình kích hoạt “InsertIntoStudentTrigger” bằng cách chạy lệnh sau:

CREATE TRIGGER InsertIntoStudentTrigger 
       BEFORE INSERT ON Students
BEGIN
  INSERT INTO StudentsLog VALUES(new.StudentId, datetime(), 'Insert');
END;

Hàm “datetime()” sẽ cung cấp cho bạn dấu thời gian hiện tại khi câu lệnh INSERT được thực thi. Nhờ đó, chúng ta có thể ghi lại giao dịch INSERT với dấu thời gian tự động được thêm vào mỗi giao dịch.

Lệnh sẽ chạy thành công và bạn không nhận được kết quả nào:

Trigger “InsertIntoStudentTrigger” sẽ được kích hoạt mỗi khi bạn thêm một sinh viên mới vào bảng sinh viên. Từ khóa “new” đề cập đến các giá trị sẽ được thêm vào. Ví dụ, “new.StudentId” sẽ là mã số sinh viên được thêm vào.

Bây giờ, chúng ta sẽ kiểm tra xem trình kích hoạt hoạt động như thế nào khi chúng ta chèn một học sinh mới.

Bước 4) Viết lệnh sau để chèn một sinh viên mới vào bảng students:

INSERT INTO Students VALUES(11, 'guru11', 1, '1999-10-12');

Bước 5) Hãy viết lệnh sau để chọn tất cả các hàng từ bảng “StudentsLog”:

SELECT * FROM StudentsLog;

Bạn sẽ thấy một hàng mới được trả về cho học sinh mới mà chúng ta vừa chèn vào:

Hàng này được chèn bằng trình kích hoạt trước khi chèn học sinh mới có id 11.

Trong ví dụ này, chúng ta đã sử dụng trình kích hoạt “InsertIntoStudentTrigger” mà chúng ta đã tạo để tự động ghi lại bất kỳ giao dịch chèn nào vào bảng “StudentsLog”. Tương tự, bạn cũng có thể ghi lại bất kỳ câu lệnh cập nhật hoặc xóa nào.

Ngăn chặn các cập nhật ngoài ý muốn bằng trình kích hoạt:

Sử dụng trình kích hoạt TRƯỚC CẬP NHẬT trên một bảng, bạn có thể ngăn các câu lệnh cập nhật trên một cột dựa trên một biểu thức.

Ví dụ

Trong ví dụ sau, chúng tôi sẽ ngăn chặn bất kỳ câu lệnh cập nhật nào cập nhật cột “studentname” trong bảng Students:

Bước 1) Điều hướng đến thư mục “C:\sqlite” và chạy sqlite3.exe.

Bước 2) Mở cơ sở dữ liệu “TutorialsSampleDB.db” bằng cách chạy lệnh sau:

.open TutorialsSampleDB.db

Bước 3) Tạo một trigger mới có tên “preventUpdateStudentName” trên bảng “Students” bằng cách chạy lệnh sau:

CREATE TRIGGER preventUpdateStudentName
BEFORE UPDATE OF StudentName ON Students
FOR EACH ROW
BEGIN
    SELECT RAISE(ABORT, 'You cannot update studentname');
END;

Lệnh “RAISE” sẽ báo lỗi với thông báo “Bạn không thể cập nhật studentname”, và sau đó sẽ ngăn chặn câu lệnh cập nhật được thực thi.

Bây giờ, chúng ta sẽ xác minh rằng trình kích hoạt hoạt động tốt và ngăn chặn mọi cập nhật cho cột tên sinh viên.

Bước 4) Chạy lệnh cập nhật sau, lệnh này sẽ cập nhật tên học sinh “Jack” thành “Jack1”.

UPDATE Students SET StudentName = 'Jack1' WHERE StudentName = 'Jack';

Bạn sẽ nhận được thông báo lỗi mà chúng tôi đã chỉ định trong trình kích hoạt, cho biết “Bạn không thể cập nhật tên sinh viên” như sau:

Bước 5) Chạy lệnh sau để chọn danh sách tên học sinh từ bảng students.

SELECT StudentName FROM Students;

Bạn sẽ thấy tên sinh viên “Jack” vẫn giữ nguyên và không thay đổi:

Câu Hỏi Thường Gặp