SQLite Trình kích hoạt, Chế độ xem & Lập chỉ mục với Ví dụ
Marcus Allen
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à “Tất cả sinh viênXem” trong cơ sở dữ liệu mẫu “Hướng dẫnSampleDB.db” như sau:
Bước 1) Mở My Computer và điều hướng đến thư mục sau “C:\sqlite” rồi mở”sqlite3.exe":
Bước 2) Mở cơ sở dữ liệu “Hướng dẫnSampleDB.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 nên xem quang cảnh “Tất cả sinh viênXem" Được trả lại:
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ư đã giải thích trước đó.
Bước 2) Mở kết nối tới cơ sở dữ liệu “Hướng dẫnSampleDB.db” bằng cách chạy lệnh sau:
.open TutorialsSampleDB.db
Bước 3) Viết lệnh sau để tạo chế độ xem tạm thời “Tất cảSinh viênTempView"
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) Đảm bảo rằng chế độ xem tạm thời “Tất cảSinh viênTempView” được tạo ra 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 tới cơ sở dữ liệu “Hướng dẫnSampleDB.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:
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 một chỉ mục “Danh mục tên sinh viên” trên bàn học sinh trong “Sinh viên” cơ sở dữ liệu như sau:
Bước 1) Điều hướng đến thư mục “C:\sqlite” như đã giải thích trước đó.
Bước 2) Mở sqlite3.exe.
Bước 3) Mở cơ sở dữ liệu “Hướng dẫnSampleDB.db” bằng lệnh sau:
.open TutorialsSampleDB.db
Bước 4) Tạo chỉ mục mới “Tên sinh viênChỉ số” bằng cách sử dụ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.
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.
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ụ
Sau đây, chúng tôi sẽ tạo một trình kích hoạt sẽ kích hoạt trước khi chèn một học sinh mới vào “Sinh viên" bàn.
Nó sẽ đăng nhập học sinh mới được chèn vào bảng “Sinh viênNhật ký” với dấu thời gian tự động cho ngày giờ hiện tại khi câu lệnh chèn xảy ra. 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 “Hướng dẫnSampleDB.db” bằng cách chạy lệnh sau:
.open TutorialsSampleDB.db
Bước 3) tạo trình kích hoạt “Chèn vàoStudentTrigger” 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;
Các chức năng "ngày giờ()" sẽ cung cấp cho bạn dấu thời gian ngày hiện tại khi câu lệnh chèn xảy ra. Để chúng tôi có thể ghi nhật ký giao dịch chèn 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:
Trình kích hoạt “Chèn vàoStudentTrigger” sẽ kích hoạt mỗi khi bạn chèn một học sinh mới vào bảng học sinh. Các "mớiTừ khóa ” đề cập đến các giá trị sẽ được chèn vào. Ví dụ: “mới.StudentId” sẽ là id sinh viên sẽ được chèn 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) Viết lệnh sau để chọn tất cả các hàng từ “Sinh viênNhật ký" bàn:
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 tôi đã sử dụng trình kích hoạt “ Chèn vàoStudentTrigger ” chúng tôi đã tạo để ghi lại bất kỳ giao dịch chèn nào vào bảng “Sinh viênNhật ký” tự động. Tương tự như cách bạn có thể ghi lại bất kỳ cập nhật nào hoặc xóa các câu lệnh.
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 “Hướng dẫnSampleDB.db” bằng cách chạy lệnh sau:
.open TutorialsSampleDB.db
Bước 3) Tạo trình kích hoạt mới “ngăn chặnUpdateStudentName" trên bàn "Sinh viên” 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;
Các "NÂNG CAO” lệnh sẽ đưa ra lỗi với thông báo lỗi “ Bạn không thể cập nhật tên sinh viên “, và sau đó nó sẽ ngăn việc thực thi câu lệnh cập nhật.
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" được "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 trên trình kích hoạt, nói rằng “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:
Tổng kết
Chế độ xem, chỉ mục và trình kích hoạt là những công cụ rất mạnh để quản trị SQLite cơ sở dữ liệu. Bạn có thể theo dõi các hoạt động sửa đổi dữ liệu khi chúng diễn ra trên một bảng. Bạn cũng có thể tối ưu hóa thao tác truy xuất dữ liệu cơ sở dữ liệu bằng cách tạo chỉ mục.
